Microsoft Excel Tips and Tricks: หาค่าสุดท้าย

คิดว่าหลายท่านคงเจอปัญหากับการหาค่าสุดท้ายในบรรทัด หรือในคอลัมน์กันบ้าง ซึ่งคงต้องปวดหัวมากหากต้องมาหาด้วยตาเองทุกครั้่ง การหาค่าสุดท้ายในบรรทัดหรือคอลัมน์นั้นก็มีให้เลือกหลายสูตรครับ แต่ผมขอแนะนำเพื่อให้นำไปใช้งานได้แบบง่าย ๆ กันสัก 3 สูตรแล้วค่อยประยุกต์ให้เข้ากับงานของตนต่อไปครับ

1. การหาตัวเลขสุดท้าย
   =Lookup(9.99999999999999e307,A:A)เมื่อ A คือคอลัมน์ที่ต้องการหาค่าสุดท้าย และ
   =Lookup(9.99999999999999e307,5:5)
   เมื่อบรรทัดที่ 5 คือบรรทัดที่ต้องการหาค่าสุดท้าย


2. การหาตัวอักษรสุดท้าย
   =Lookup(Char(255),A:A)เมื่อต้องการหาค่าสุดท้ายที่เป็นตัวอักษรในคอลัมน์ A และ
   =Lookup(Char(255),5:5)
   เมื่อต้องการหาค่าสุดท้ายที่เป็นตัวอักษรในบรรทัดที่ 5


3. หากต้องการหาค่าสุดท้ายโดยไม่สนใจว่าค่านั้นเป็นตัวเลขหรืออักขระ
   =Lookup(2,1/(A1:A65535<>""),A1:A65535)
   เมื่อต้องการหาค่าสุดท้ายในคอลัมน์ A และ
   =Lookup(2,1/(5:5<>""),5:5)
   เมื่อต้องการหาค่าสุดท้ายในบรรทัดที่ 5

ซึ่งคิดว่าคงจะเป็นแนวทางเพื่อให้ทุกท่านได้ใช้หาค่าสุดท้ายสำหรับคอลัมน์หรือบรรทัดได้โดยสะดวก ไม่ต้องหาด้วยตากันให้เมื่อยต่อไปครับ 

Read More

Microsoft Excel Tips and Tricks: การป้องกันการคีย์ค่าซ้ำ

บางข้อมูลอาจจำเป็นต้องจัดเก็บไว้เพียง Record เดียวเท่านั้น หากเราไม่ป้องกันการคีย์ค่าซ้ำไว้ จะเสียเวลามาตรวจสอบเสมอว่ามีค่าที่กำลังบันทึกอยู่แล้วหรือไม่ แต่ถ้ากำหนดได้ว่าถ้าซ้ำแล้วให้มีข้อความเตือน จะทำให้ประหยัดเวลาในการตรวจสอบได้มาก

ยกตัวอย่างต้องการป้องกันการคีย์ค่าซ้ำในเซลล์ A2:A20

เราสามารถป้องกันการคีย์ค่าซ้ำได้ดังนี้

1. คลุมช่วงข้อมูลที่ต้องคีย์ เช่น A2:A20


2. เข้าเมนู Data เลือก Validation


3. เลือกแถบ Settings


4. ที่ Allow เลือก Custom


5. ที่ Formula box คีย์สูตร


6. =COUNTIF($A$2:$A$20,A2)=1


7. เลือกแถบ Error Alert


8. ตรง Title box กรอก "คีย์รายการซ้ำ"


9. ในช่อง Error message box กรอก "ค่าที่คุณกรอกมีอยู่แล้ว"


10. OK

ทดลองคีย์ค่าใด ๆ และทดลองคีย์ค่าซ้ำกัน สังเกตดูผล

Read More

Microsoft Excel Tips and Tricks: แทรกบรรทัดผลรวมสูตรไม่ขยายตามจะทำอย่างไร?

ปกติเมื่อเราแทรกบรรทัดที่เป็นผลรวมแล้วมีการกรอกตัวเลขเพิ่ม จะต้องมาแก้ไขสูตรผลรวมใหม่เพื่อให้รวมบรรทัดที่แทรกด้วยเสมอ ยกตัวอย่างเช่น A1:A5 มีข้่อมูลดังนี้

5
2
3
4
8

และที่ A6 เขียนสูตร

=Sum(A1:A5)

หากเราแทรกที่บรรทัด A6 แล้วคีย์ตัวเลขใด ๆ สูตรใน A7 จะไม่รวมตัวเลขที่เพิ่งคีย์ไปล่าสุด และยังคงแสดงเป็น

=Sum(A1:A5)
แต่ทั้งนี้เราต้องการให้แสดงเป็น

=Sum(A1:A6)
หากต้องการให้สูตรขยายตามและรวมยอดที่คีย์เข้ามาล่าสุดให้ด้วย ต้องแทรกในบรรทัดก่อนบรรทัดยอดรวมเสมอ ซึ่งอาจไม่สะดวกในการทำงานบางงาน และเนื่องจากมีการแทรกบรรทัดเพิ่มเข้ามาแล้วไม่ว่าจะแทรกบรรทัดใด สูตรผลรวมควรจะต้องขยายให้ด้วย เราสามารถแก้ไขสูตรที่ A6 เสียใหม่เป็น

=Sum(Offset(A$1,0,0,Row()-Row(A$1),1))

จะทำให้สูตรขยายให้เสมอเมื่อมีการแทรกบรรทัดครับ

Read More

Microsoft Excel Tips and Tricks: การปัดเศษตามจำนวนที่ต้องการ

การปัดเศษแบบปกติใน Excel สามารถใช้ฟังก์ชั่นเกี่ยวกับการปัดเศษ ซึ่งได้แก่

Round
Roundup
Rounddown
Ceiling
Floor
Int
Trunc

แต่หากต้องการปัดตามจำนวนที่ต้้องการ ยกตัวอย่างเช่นปัดคราวละ 5 หมายถึงว่าหากหลักหน่วยมากกว่าหรือเท่ากับ 2.5 ให้ปัดเป็น 5 ถ้าน้อยกว่านั้นให้ปัดเป็น 0 สามารถประยุกต์ใช้ฟังก์ชั่น Round ได้ดังนี้ครับ

สมมุติค่าที่ต้องการปัดอยู่ที่ A1 ที่เซลล์ B1 คีย์

=Round(A1/5,0)*5

สูตรด้านบนนี้ต่างจากสูตร

=Ceiling(A1,5)

เนื่องจาก Ceiling จะปัดขึ้นเป็นจำนวนเท่าของ 5 เสมอ

และต่างจาก

=Floor(A1,5)

เนื่องจาก Floor จะปัดลงเป็นจำนวนเท่าของ 5 เสมอ

สำหรับฟังก์ชั่นการปัดคราวละตามที่กำหนดสามารถใช้ Mround ได้อีกฟังก์ชั่นหนึ่ง เช่น

=Mround(A1,5)

ผลลัพธ์ที่ได้จะเท่ากับ Round(A1/5,0)*5 เพียงแต่การใช้ Mround ต้องติดตั้ง Analysis Toolpak สำหรับ Excel 2003 สามารถทำการติดตั้งโดย

เข้าเมนู Tools เลือก Add-ins > ทำเครื่องหมายที่ Analysis Toolpak > OK

Read More

Microsoft Excel Tips and Tricks: เทคนิคระดับเทพ

ผมทราบว่าหลายท่านรอคอยที่จะให้ผมเขียนทิป เทคนิคในการทำงานกับ Excel และนี่ก็เป็นบางส่วนที่ช่วยให้การทำงานกับ Excel เต็มไปด้วยความสนุกสนาน และหากใครต้องการเป็นผู้เชี่ยวชาญด้าน Microsoft Excel ความรู้ตามด้านล่างนี้เป็นสิ่งที่ท่านควรทราบ เพราะจะทำให้ท่านทำงานได้อย่างรวดเร็วในการจัดการกับข้อมูลใน "ระดับเทพ" สำหรับเทคนิคตามด้านล่างนี้ใช้กับ Excel 2003 เป็นหลักครับ

 การเลือกข้อมูล

1. เราสามารถเลือกเซลล์ที่ไม่ติดกันโดยคลิกเซลล์แรกของข้อมูลแล้วกดแป้น Ctrl ในขณะที่คลิกเซลล์อื่น ๆ หรือลากคลุมช่วงเซลล์อื่น ๆ


2. การเลือกเซลล์ว่างในพื้นที่ใด ๆ ให้คลุมพื้นที่แล้วคลิกเมนู Edit > Go To > Special > Blanks > OK


3. การเลือกพื้นที่ที่มีขนาดใหญ่อย่างง่าย ๆ เพียงแต่คลิกเซลล์ที่อยู่มุมสุดของด้านใดด้านหนึ่ง แล้วเลื่อนหน้าจอไปหาตำแหน่งมุมตรงกันข้าม กดแป้น Shift ค้างไว้ แล้วคลิกเซลล์ที่อยู่มุมสุด


4. เราสามารถเลือกได้ว่าหลัง จาก Enter แล้วจะให้เลื่อนไปยังเซลล์ใด โดยการเข้าไปที่เมนู Tools > Options > Edit > ภายใต้ Move selection after Enter หัวข้อ Direction สามารถเลือกกำหนดได้ตามต้องการ


5. การเข้าถึงข้อมูลที่เฉพาะเจาะจง เช่น พื้นที่ว่าง วัตถุ หรือเซลล์สุดท้าย สามารถคลิกเมนู Edit > Go To > Special > เลือก Option ที่ต้องการ

 การแก้ไขข้อมูล

1. เราสามารถแก้ไขข้อมูลได้ด้วยการดับเบิ้ลคลิกลงในเซลล์แทนการแก้ไขจาก Formula bar


2. หากไม่ต้องการแก้ไขโดยตรงในเซลล์ ให้คลิกเมนู Tools > Options > Edit > ปลดเครื่องหมายตรง Edit directly in cell


3. การ Run ตัวเลขทำได้โดยเลือกเซลล์ที่มีตัวเลข > กดแป้น Ctrl ค้างไว้แล้วคลิกจุดจับเติม(มุมขวาล่างของเซลล์) แล้วลากตามต้องการ


4. การ Run สัปดาห์, ไตรมาส หรือรายการอื่น ๆ สามารถเลือกรายการแรกแล้วคลิกจุดจับเติมค้างไว้แล้วลากตามต้องการ


5. เราสามารถสร้างรายการให้ Excel เติมข้อมูลอัตโนมัติเมื่อคลิกลากโดย เข้าเมนู Tools > Options > Custom Lists


6. มหัศจรรย์การคลิกขวา เราสามารถคลิกขวาที่เซลล์, วัตถุ, กราฟ หรือพื้นที่ใด ๆ บนหน้าต่าง Excel จะมีเมนูคำสั่งให้เลือก ซึ่งมีประโยชน์อย่างมากมาย สุดยอดครับ ลองเล่นดู


7. เราสามารถกำหนดให้ Excel เติมข้อมูลให้ตามต้องการโดยการคลิกขวาค้างที่จุดจับเติมแล้วลากเมื่อปล่อยเมาส์จะเห็นเมนูคำสั่งให้เลือก


8. เมื่อทำการเติมข้อมูลอัตโนมัติ Excel สามารถที่จะเพิ่มทั้่งไตรมาสและปีได้ในขณะเดียวกัน เช่น 1Q93, Q193, 1st Qtr 93, 1st Quarter 1993 เจ๋งสุด ๆ


9. เราสามารถลบช่วงข้อมูลได้ง่าย ๆ ด้วยเมาส์ด้วยการกด Shift ขณะที่ลากจุดจับเติมขึ้นบนหรือไปทางซ้ายของช่วงข้อมูลที่เลือกไว้


10. เราสามารถแยกข้อมูลที่อยู่ในเซลล์เดียวกันเป็นหลายคอลัมน์ด้วยการเข้าเมนู Data > Text to Colums > เลือก Option ในการแยกข้อมูล อันนี้ทดลองเล่นเยอะ ๆ เซียนจะเรียกท่านว่าเพื่อน (แค่เพื่อนก่อน)


11. การแทรกเซลล์ว่างอย่างรวดเร็วสามารถกดแป้น Shift ขณะคลิกลากจุดจับเติมลงด้านล่าง


12. สามารถดูชื่อทั้งหมดที่เรากำหนดให้กับช่วงข้อมูลโดยการคลิกที่ Name box บน Formula bar ยกเว้นชื่อที่ท่านเขียนแบบกำหนดให้ยืดหยุ่นได้ตามปริมาณข้อมูล


13. สามารถตรวจสอบการสะกดได้โดยการกดปุ่ม F7


14. นอกจากปุ่ม Cut แล้วเราสามารถเลื่อนเซลล์ไปยังตำแหน่งที่ต้องการด้วยการคลิกค้างที่ขอบเซลล์ แล้วลาก ถ้าต้องการ Copy ก็ให้กด Ctrl ค้างไว้


15. สามารถแก้ไขเซลล์ได้พร้อมกันหลาย Sheet ด้วยการกดแป้น Ctrl ค้างไว้แล้วเลือก Sheet ที่ต้องการแล้วแก้ไขข้อมูล


16. การ Back up ข้อมูลทุกครั้งที่ Save ในหน้าต่าง Save as เลือก Tools > General Options > เลือก Always create backup

 การกำหนดรูปแบบข้อมูล

1. การย่อหน้าสามารถสามารถใช้คลิกปุ่ม Increase Indent และ Decrese Indent จากปุ่ม Indent บน Formatting toolbar ถ้าไม่ทราบว่าปุ่มไหน เล็งไว้สักปุ่ม ลองเอาเมาส์ชี้จะเห็น Tools tip แล้วเชิญเลือกอันที่ใช่ครับ


2. วิธีการง่าย ๆ ในการกำหนด Style โดยใช้ไฟล์อื่นเป็นต้นแบบ คือการ Copy รูปแบบเซลล์มาวางในไฟล์ที่สร้างใหม่


3. คลิกขวาที่หัวคอลัมน์หรือหัวแถวจะมีคำสั่งมาให้เลือก สามารถกำหนดความกว้าง ความสูง ซ่อน ยกเลิกการซ่อน


4. ถ้าดับเบิ้ลคลิกปุ่ม Format Painter จะสามารถเลือกกำหนดรูปแบบพื้นที่อื่นไปได้เรื่อย ๆ ไม่ต้องทำไปคลิกไป


5. การตรึงแถวเพื่อให้เห็นหัวคอลัมน์สามารถเลือกแถวด้านล่างแล้วเข้าเมนู Window > Freeze Panses


6. ใครมีตารางฟังทางนี้ ท่านสามารถเลือกใช้รูปแบบตารางโดยใช้รูปแบบที่ Excel มีมาให้ด้วยการเข้าเมนู Format แล้วเลือก AutoFormat จะได้ไม่ต้องกำหนดเองให้เมื่อย ถ้าไม่เมื่อยก็แล้วไป ไม่ว่ากัน


7. การกำหนดรูปแบบตัวอักษรในการเริ่มต้นทำงานกับ Excel เข้าเมนู tools > Options > General > กำหนดตามต้องการเลยครับพี่น้อง


8. การเปลี่ยนรูปแบบตัวอักษรเสียใหม่ทั้่งไฟล์ให้เข้าเมนู Format > Style > เปลี่ยน Normal Style เป็นรูปแบบที่ท่านพึงปรารถนา


9. การ แปลงรูปแบบข้อมูลที่กำหนดไว้ล่วงหน้าจาก HTML ให้เลือกข้อมูล > เข้าเมนู Data > Text To Tolumns > ดำเนินการไปตามที่่่ท่านเห็นว่าสมควร


10. การจัดกลุ่มแถวและ คอลัมน์อย่างง่าย ๆ ทั้งแสดงและซ่อน ให้เลือกแถวและคอลัมน์ จากนั้นเข้าเมนู Data > Group and Outline > Group > Rows/Column (หมายถึงเลือกอย่างใดอย่างหนึ่ง อย่าพาซื่อไปหาเมนู Rows/Column ล่ะ)


11. การกำหนดตัวเลขให้แสดงเป็นหลักพันหรือหลักล้าน เลือกเซลล์หรือกลุ่มเซลล์ที่ต้องการ > เข้าเมนู Fomat > Cells > Custom > ในช่อง Type: คีย์ #, หรือ 0, เพื่อแสดงหลักพัน หากคีย์ #,, หรือ 0,, จะแสดงหลักล้าน


12. ใครทำงานกับบริษัท Inter มักจะกำหนด Dollar sign ให้กับตัวเลข วิธีการง่าย ๆ คือคีย์ $ แล้วตามด้วยตัวเลข ไม่ต้องเข้าไปกำหนดทีหลังให้เมื่อย


13. คีย์ตัวเลขแล้วตามด้วย % ก็จะเป็นการกำหนดรูปแบบ % ให้ตัวเลขเช่นกัน


14. การกำหนดตัวเลขที่เป็นลบให้เป็นสีแดงให้เข้าเมนู Format > Cells > แถบ Number เลือก Number หรือ Currency แล้วเลือก red format ภายใต้ Negative numbers

 ตารางและรายงานกราฟใน PivotTable

1. เปลี่ยนชื่อให้กับ Field ใน PivotTable ด้วยการคลิก Field แล้วก็คีย์เปลี่ยนเลยครับ ง่ายสุด ๆ


2. การเลื่อน Legend บน PivotChart ให้เข้าเมนู Chart > Chart Options > บนแถบ Legend เลือกตำแหน่งที่ต้องการ


3. สามารถ กำหนดรายงาน PivotTable โดยการคลิกเลือก Format Report จากเครื่องมือ PivotTable ถ้าไม่มีให้เข้าเมนู View > Toolbars > PivotTable


4. เราสามารถลาก Field ใน PivotTable ไปในตำแหน่งที่้ต้องการได้โดยไม่ต้องเข้าเมนู PivotTable Wizard


5. สามารถลดพื้นที่ของ PivotTable โดยการลากบางแถวหรือบางคอลัมน์ไปไว้ที่ Page field


6. การแสดงหรือซ่อนรายละเอียดข้อมูลใน data area ของ PivotTable ด้วยการดับเบิ้ลคลิกเซลล์ในตารางที่ต้องการจะซ่อนหรือแสดงรายละเอียด


7. สามารถย้ายบรรทัดหรือคอลัมน์ได้โดยการคลิกขอบเซลล์แล้วลาก


8. การเปลี่ยนแปลงข้อมูลในฐานข้อมูลสามารถทำให้ตาราง PivotTable เป็นปัจจุบันได้โดยคลิกขวาในตาราง PivotTable แล้วเลือก Refresh หรือคลิกปุ่ม Refresh ที่ PivotTable toolbar

 ฟังก์ชั่นและสูตร

1. ถ้าต้องการตัวช่วยในการเลือกฟังก์ชั่นและระบุส่วนประกอบฟังก์ชั่นมาให้พร้อม ให้คลิก fx บน Formula bar


2. เราสามารถกดแป้น Alt+เครื่องหมาย = แทนการคลิกเครื่องหมาย AutoSum


3. การ Copy สูตรในเซลล์ปัจจุบันไปยังเซลล์อื่น ๆ ที่เืลือกไว้ให้กดแป้น F2 ตามด้วย Ctrl+Enter


4. การทำ Subtotal สามารถทำได้ง่าย ๆ ด้วยการเลือกเซลล์ใด ๆ ในช่วงข้อมูล > เข้าเมนู Data > Subtotal


5. หาก ต้องการลดค่าของเซลล์ในพื้นที่ที่เลือกลง 10 เปอร์เซ็นต์ ให้คีย์ 0.9 ในเซลล์ใด ๆ > เข้าเมนู Edit > Copy > คลุมพื้นที่เป้าหมาย > เข้าเมนู Edit > Paste Special > Multiply

หลังจากที่อ่านมาจนถึงบรรทัดนี้ โปรดทราบว่า เซียนเรียกท่านว่า "พี่" ไปในช่วงที่ท่านกำลังอ่านถึงกลาง ๆ หน้าแล้วครับ 

Read More

Microsoft Excel Tips and Tricks: การรวมค่าจากการปัดเศษด้วยสูตรเดียว

ปกติหากเราต้องการหาค่ารวมจากการปัดเศษ จะต้องเขียนสูตร Round ก่อนแล้วค่อย Sum ค่าที่ได้ทั้งหมดออกมาอีกที

การใช้วิธีลัดเพื่อรวมค่าที่ปัดเศษทีเดียวทั้งหมดโดยไม่ต้องเขียนสูตร Round ไว้ก่อนจะทำให้ประหยัดเวลา ประหยัดเซลล์ได้มาก ยกตัวอย่างเช่น A2:A6 ประกอบด้วย

A2: 2.354
A3: 3.653
A4: 3.765
A5: 9.228
A6: 4.773

หากปัดให้เหลือทศนิยม 2 หลักทั้งหมดก่อนแล้วค่อยรวม ที่เซลล์ใด ๆ สามารถใช้สูตร Array ดังนี้

=Sum(Round(A2:A6,2))

Ctrl+Shift+Enter

หรือหากไม่ต้องการใช้สูตร Array แบบกำหนดเอง ก็สามารถใช้สูตร Array ธรรมชาติแบบด้านล่างเพื่อไม่ต้องกด Ctrl+Shift+Enter

=Sumproduct(Round(A2:A6,2))

Enter

Read More

Microsoft Excel Tips and Tricks: การเติมข้อมูลด้วยเซลล์ก่อนหน้า

การเติมช่องว่างด้วยเซลล์ก่อนหน้ามีประโยชน์มากกับท่านที่กำลังจะทำฐานข้อมูล เนื่องจากข้อมูลที่ได้มาอาจไม่อยู่ในรูปแบบฐานข้อมูลที่สมบูรณ์ อันได้แก่ข้อมูลที่ได้มาจากการดึง SAP, Oracle, Web หรือโปรแกรมอื่น ๆ ซึ่งได้จัดกลุ่มหรือดึงมาแบบมีเงื่อนไข ทำให้เกิดเซลล์ว่างอยู่ภายใต้หัวข้อ และเราจำเป็นต้องเติมช่องว่างด้วยหัวข้อดังกล่าว สามารถทำใน Excel ด้วยวิธีการตามด้านล่าง

สมมุติข้อมูล A1:A10 ดังนี้

A1: Apple
A3:
A4: Banana
A5:
A6: Coconut
A7:
A8:
A9: Orange
A10:

A. วิธีการเติมข้อมูลด้วยเซลล์ก่อนหน้า

1. เลือก A1:A10


2. เข้าเมนู Edit > Go to หรือกดแป้น F5


3. Special > Blanks > OK


4. คีย์ =a1 (หรือกดแป้น = แล้วกดแป้นลูกศร ↑) > CTRL+ENTER

B. การเปลี่ยนสูตรให้เป็นค่าคงที่

1. คลุม A1:A10


2. คลิกขวาข้อมูลที่คลุมไว้ > Copy


3. คลิกขวาข้อมูลที่คลุมอีกครั้ง > Paste Special


4. ภายในกลุ่ม Paste เลือก Values > OK

สูตรทั้งหลายจะเปลี่ยนเป็นค่าคงที่ตามต้องการ

Read More

Microsoft Excel Tips and Tricks: การสั่งให้คำนวณแผ่นงานหรือสมุดงาน

ในการสั่งให้ Excel ทำการคำนวณสามารถใช้ Shortcut ดังข้างล่างนี้

1. กด F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายในสมุดงานที่เปิดอยู่ทั้งหมด ถ้าสมุดงานถูกตั้งค่าเป็นการคำนวณอัตโนมัติ ก็ไม่จำเป็นต้องกดเป็น F9 เพื่อคำนวณ


2. กด SHIFT+F9 จะทำการคำนวณสูตรที่มีการเปลี่ยนแปลงหลังจากการคำนวณครั้งสุดท้ายบนแผ่นงานปัจจุบัน


3. กด CTRL+ALT+F9 จะทำการคำนวณสูตรทั้งหมดในสมุดงานทั้งหมดทุกสมุดงานที่กำลังทำงาน โดยไม่สนใจว่าจะมีการเปลี่ยนแปลงจากครั้งสุดท้ายที่มีการคำนวณหรือไม่


4. กด CTRL+SHIFT+ALT+F9 จะทำการตรวจสอบสูตรที่อ้างถึงอีกครั้ง จากนั้นจะทำการคำนวณสูตรทั้งหมดในสมุดงานที่เปิดอยู่ทั้งหมด โดยไม่คำนึงถึงว่ามีเปลี่ยนมาจากครั้งที่แล้วหรือไม่

Read More

Microsoft Excel Tips and Tricks: การแสดงรายการโดยไม่รวมค่าที่ซ้ำ

กรณี ที่เรามีข้อมูลซึ่งประกอบด้วยค่าที่ซ้ำกันจำนวนมาก หากต้องการที่จะให้โปรแกรมแสดงรายการทั้งหมดโดยไม่รวมค่าที่ซ้ำ สามารถใช้ Advanced Filter ทำได้ดังนี้

1. สมมุติข้อมูล A1:A10 มีดังต่อไปนี้
   A1: Fruits
   A2: Apple
   A3: Cherry
   A4: Pear
   A5: Cherry
   A6: Plum
   A7: Apple
   A8: Apple
   A9: Pear
   A10: Apple


2. คลิกเมนู Data > Filter > Advanced Filter


3. ตรง Action เลือก Copy to another location


4. คลิกในช่อง List Range ลากคลุม $A$1:$A$10


5. คลิก Unique records only


6. คลิกในช่อง Copy to และคลิกเซลล์ B1


7. OK

โปรแกรมจะแสดงข้อมูลที่คอลัมน์ B ดังข้างล่างนี้

B1: Fruits
B2: Apple
B3: Cherry
B4: Pear
B5: Plum

การ ใช้ Advanced Filter เพื่อเลือกค่าที่ไม่ซ้ำสามารถใช้ได้กับหลาย ๆ คอลัมน์ และหากต้องการให้กรองและแล้วแสดงในฐานข้อมูลเดิม สามารถเว้นข้อ 6 ด้านบนไว้ได้

Read More

Microsoft Excel Tips and Tricks: เกี่ยวกับการคำนวณในสมุดงาน

การคำนวณเป็นกระบวนการในการคำนวณสูตรแล้วแสดงผลลัพธ์เป็นค่าในเซลล์ที่มีสูตร

เมื่อใดที่ Microsoft Excel มีการคำนวณ

หากสูตรที่มีอยู่แล้วถูกคำนวณใหม่ Excel จะทำการคำนวณเซลล์ที่อ้างถึงเซลล์อื่นใหม่อีกครั้งเมื่อเซลล์ที่ถูกอ้างถึง นั้นมีการเปลี่ยนแปลงค่า การคำนวณชนิดนี้ช่วยหลีกเลี่ยงการคำนวณที่ไม่จำเป็นต่างๆ นอกจากนี้ Excel ยังคำนวณสมุดงานทุกครั้งที่สมุดงานถูกเปิดอีกด้วย

การคำนวณบนพื้นหลัง

ขณะที่ทำการคำนวณ คุณสามารถเลือกคำสั่งหรือกระทำการต่างๆ เช่นป้อนตัวเลขหรือสูตรได้ ซึ่ง Excel จะหยุดการคำนวณไว้ชั่วขณะเพื่อปฏิบัติตามคำสั่งหรือการกระทำเหล่านั้นก่อน แล้วจึงเริ่มการคำนวณต่อ กระบวนการในการคำนวณอาจใช้เวลานานขึ้นถ้า

1. สมุดงานมีสูตรอยู่เป็นจำนวนมาก


2. แผ่นงานมีตารางข้อมูล มีฟังก์ชันที่จะคำนวณใหม่อัตโนมัติทุกครั้งที่สมุดงานถูกคำนวณใหม่


3. แผ่นงานมีการเชื่อมโยงไปยังแผ่นงานหรือสมุดงานอื่นๆ

คุณสามารถควบคุมได้ว่าจะให้การคำนวณเกิดขึ้นเมื่อใด โดยการเปลี่ยนแปลงกระบวนการคำนวณเป็นการคำนวณด้วยตัวเอง โดยเข้าเมนู Tools > Options > Calcualtion > Manaul

ความถูกต้องของการคำนวณ

Excel จะมีความความถูกต้องของการคำนวณจุดทศนิยมได้ถึง 15 หลัก ซึ่งจะคำนวณจากค่าที่เก็บไว้ ส่วนค่าที่แสดงและค่าที่พิมพ์ออกมานั้น ขึ้นกับวิธีการเลือกที่จะจัดรูปแบบให้แสดงค่าที่เก็บไว้ ตัวอย่างเช่น เซลล์ที่แสดงวันที่เป็น "6/22/2008" ยังมีเลขลำดับที่เป็นค่าที่เก็บไว้สำหรับวันที่ในเซลล์ ซึ่งไม่ใช่ค่าที่แสดงให้เห็น คุณสามารถเปลี่ยนการแสดงวันที่ให้เป็นรูปแบบอื่นได้ (ตัวอย่างเช่น ถึง "22-Jun-2008") แต่ค่าบนแผ่นงานไม่ได้เปลี่ยนค่าที่เก็บไว้

การเปลี่ยนความถูกต้อง (precision) ของการคำนวณ

เมื่อสูตรทำการคำนวณ Excel มักจะใช้ค่าที่เก็บอยู่ในเซลล์ซึ่งถูกอ้างอิงโดยสูตรดังกล่าว ตัวอย่างเช่น ถ้าเซลล์สองเซลล์บรรจุค่า 10.005 และเซลล์ดังกล่าวได้รับการจัดรูปแบบให้แสดงค่าในรูปแบบสกุลเงิน ค่าจะได้รับการแสดงเป็น $10.01 ในแต่ละเซลล์ ถ้าคุณบวกเซลล์ทั้งสองเข้าด้วยกัน ผลลัพธ์คือ $20.01 เนื่องจาก Excel บวกค่าที่เก็บไว้คือ 10.005 และ 10.005 ไม่ใช่ค่าที่แสดงอยู่ คุณสามารถเปลี่ยนความถูกต้องของการคำนวณ เพื่อให้ Excel ใช้ค่าที่แสดงแทนที่จะใช้ค่าที่เก็บไว้เมื่อคำนวณสูตรใหม่อีกครั้ง โดยเข้าเมนู Tools > Options > Calculation > Precision as displayed

การเปลี่ยนจำนวนครั้งที่ Excel คำนวณหรือทำซ้ำ (Iteration)

Excel ไม่สามารถคำนวณสูตรที่อ้างถึงเซลล์ที่มีสูตรนั้นอยู่ ไม่ว่าจะโดยตรงหรือโดยทางอ้อม ซึ่งเรียกว่าการอ้างอิงแบบวงกลม ถ้าสูตรๆ หนึ่งอ้างย้อนกลับไปยังเซลล์ที่บรรจุสูตรนั้นเอง คุณต้องกำหนดจำนวนครั้งที่สูตรควรจะคำนวณใหม่ โดยเข้าเมนู Tools > Options > Calculation > Iteration

Read More

Microsoft Excel Tips and Tricks: การ List รายชื่อ Sheet

เนื่อง จากมีคำถามเกี่ยวกับวิธีการ List รายชื่อ Sheet ค่อนข้างบ่อย และ Pantip เองก็มีการลบกระทู้ค่อนข้างบ่อย ผมก็เลยต้องตอบคำถามเดิม ๆ ค่อนข้างบ่อยเป็นลูกโซ่ เลยนำมาเขียนไว้ที่นี่เพื่อจะได้ใช้อ้างอิง ไม่ต้องเขียนใหม่ หรือ Copy มาแปะทุกครั้งที่เจอคำถาม

สำหรับ วิธีการนี้ใช้ Excel 4 Macro Function มาช่วย ซึ่งต้องกำหนด Security ในเมนู Tools > Macro > Security เป็นระดับ Medium หรือ Low ถึงจะใช้ได้

วิธีการทำดังนี้ครับ

1. เปิดไฟล์ที่ต้องการ List รายชื่อ Sheet โดยทำการเพิ่ม 1 Sheet คือ Sheet ที่ต้องการให้แสดงรายชื่อ Sheet ทั้งหมด


2. เข้าเมนู Insert > Name > Define >
   
   
   1. ช่อง Names in workbook: คีย์ CountSheet > ช่อง Refers to: คีย์ =Get.Workbook(4) > Add
   
   
   2. ช่อง Names in workbook: คีย์ ListSheet > ช่อง Refers to: คีย์
      =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) > OK


3. ใน Sheet ที่เพิ่มเข้ามาใหม
   
   
   1. เซล A1 คีย์
      =CountSheetEnter
   
   
   2. เซล A2 คีย์
      =IF(ROWS($A$2:A2)<=$A$1,INDEX(ListSheet,ROWS($A$2:A2)),"")
      Enter แล้ว Copy ลงด้านล่าง

Read More

Microsoft Excel Tips and Tricks: เราจะซ้อน IF มากกว่า 7 ชั้นได้อย่างไร?

Excel ตั้งแต่เวอร์ชั่น 2003 ลงไปสามารถซ้อน IF ได้แค่ 7 ชั้นซึ่งบางคนก็อยากได้มากกว่านั้น Excel 2007 จึงให้มาถึง 64 ชั้น และคิดว่าน่าจะเพียงพอแก่การงง 

และแม้ว่า Excel 2003 จะสามารถซ้อน IF ได้แค่ 7 ชั้นก็ตาม เรายังสามารถประยุกต์ให้สามารถซ้อนได้เกิน 7 ชั้นได้อย่างไม่ลำบาก แค่เพียงเราเข้าใจสิ่งเหล่านี้ก่อน ซึ่งขอยกมาเป็นเพียงตัวอย่างคือ

1. True+0 = 1


2. False+0 = 0


3. การใช้ &

ข้อ 1. และ 2. เพิ่งรู้ใช่ไหมล่ะ อืม...ขอถามกันก่อนว่า แล้วเราจะใช้ IF เกิน 7 ชั้นไปทำไม เลือกคำตอบมา 1 ข้อ คิดไว้ในใจคนเดียว

1. บ้าพลัง


2. อยากเอาชนะข้อจำกัดของ IF


3. เราถนัดและยังไม่รู้ว่ามีฟังก์ชั่นไหนที่เหมาะกว่า ซึ่งเราต้องใช้ด่วน ไม่อยากคิดฟังก์ชั่นอื่น ได้โปรดอย่าถามอีก ขอร้องเถอะ เห็นแก่ลูกแมวตาขาวๆ 

 ก่อนที่จะถูกหมั่นไส้ไปกว่านี้ ขอพาไปทัศนาว่าเราจะใช้ IF เกิน 7 ชั้นได้อย่างไร แล้วมันเกี่ยวอะไรกันกับ True, False, &

การที่จะให้เข้าใจอะไรง่ายๆ ก็ต้องมีตัวอย่างประกอบครับ ขอยกตัวอย่างดังนี้

สมมุติโจทย์บอกว่า ถ้า

A1=1 ให้ B1=10
A1=2 ให้ B1=11
A1=3 ให้ B1=12
A1=4 ให้ B1=13
A1=5 ให้ B1=14
A1=6 ให้ B1=15
A1=7 ให้ B1=16
A1=8 ให้ B1=17
A1=9 ให้ B1=18

นอกจากเงื่อนไขข้างบนให้คำตอบเป็น 0 รวมทั้งหมด 10 เงื่อนไข

 คำถาม จะเขียนฟังก์ชั่น IF ที่ B1 อย่างไร?

เราสังเกตเห็นอะไรบ้างครับกับโจทย์นี้ ส่วนตัวผมเองสังเกตเห็นดังนี้

1. เงื่อนไขเป็นตัวเลข


2. เงื่อนไขเกินกว่า 7 ชั้น

ก่อนแก้โจทย์ที่แสนยาก เรามาซ้อมกันก่อนดีกว่า สมมุติตอนนี้ A1 = 2 ที่ B1 คีย์สูตร IF ดังนี้

=If(A1=1,True,False)+0

คำตอบได้เป็นอะไรครับ?

เอาใหม่ ที่ B1 คีย์

=If(A1=1,True)+0 <== โปรดสังเกตว่าเงื่อนไขไม่ครบ

คำตอบได้เป็นอะไรครับ? ==> รู้นะบางคนบอกว่า Excel เพี้ยนไปแล้ว ไม่เป็นไรครับ ถ้า Excel คุณเพี้ยน ของผมก็เพี้ยนเหมือนกัน สบายใจได้ มีเพื่อนแล้ว

เอ้า...มาต่อกัน ดูว่าคนควนจะบอกอะไรต่อ ทดลองแล้ว ได้คำตอบแล้ว งงแล้ว แล้วไงต่อ

ผมกำลังจะบอกว่าเราจะนำตัวอย่างที่ 2 นี่แหละครับ มาทำ IF ให้เกิน 7 ชั้น

ตามข้าพเจ้ามา ที่ B1 เขียนสูตรเพื่อแก้โจทย์ (เอาจริงแล้วนะ) ดังนี้

=IF(A1=1,10,IF(A1=2,11,IF(A1=3,12,IF(A1=4,13,IF(A1=5,14,IF(A1=6,15,IF(A1=7,16,IF(A1=8,17))))))))+IF(A1=9,18)

ที่ A1 ทดลองคีย์เลขใดๆ แล้วสังเกตดูผลลัพธ์

เราเห็นอะไรจากสูตรนี้บ้าง

1. เงื่อนไขสุดท้ายเป็น 0 เราไม่ต้องคีย์


2. If ซ้อนได้ 7 ชั้นแต่เรานับแล้วได้ 8 แสดงว่า IF ชั้นนอกสุดไม่นับ

ถ้าให้คืนค่าเป็นอักขระบ้างล่ะ จะซ้อนเกิน 7 ชั้นได้อย่างไร เพราะคงเอา Text มาบวก Text ไม่ได้แน่นอน

โอเค...ได้ ตามใจผู้ชมเสมอ สมมุติโจทย์ใหม่ดังนี้ ถ้า

A1=1 ให้ B1=A
A1=2 ให้ B1=B
A1=3 ให้ B1=C
A1=4 ให้ B1=D
A1=5 ให้ B1=E
A1=6 ให้ B1=F
A1=7 ให้ B1=G
A1=8 ให้ B1=H
A1=9 ให้ B1=I
A1=10 ให้ B1 = J

นอกจากเงื่อนไขข้างบนให้คำตอบเป็น ค่าว่าง รวมทั้งหมด 11 เงื่อนไข (แน่ะ...มีการแถม)

 คำถาม จะเขียนฟังก์ชั่น IF ที่ B1 อย่างไร?

 คำตอบ ที่ B1 คีย์

=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"H",""))))))))&IF(A1=9,"I",IF(A1=10,"J",""))

เราสังเกตเห็นอะไรบ้างจากสูตรนี้

1. เมื่อเป็น Text เงื่อนไขสุดท้ายต้องให้คืนค่าเป็นค่าว่าง ("")


2. การเชื่อมให้เกินกว่า 7 ชั้นใช้ &

อ้อ...เป็นอย่างนี้นี่เอง 

ลองประยุกต์สัก 20 หรือ 30 เงื่อนไขดูครับว่า จะใช้ IF อย่างไร ซึ่งผมแนะนำว่าหากเงื่อนไขหลายๆ ชั้นควรใช้ฟังก์ชั่นอื่นที่เหมาะสมกว่ามาแก้ปัญหาครับ เช่น Lookup, Vlookup, Index+Match เป็นต้น

Read More

Microsoft Excel Tips and Tricks: วัน เวลา ใน Excel

จั่วหัวเรื่องวันเวลาเป็นเรื่องที่น่าสนใจและไม่ใช่เรื่องง่าย ๆ สำหรับเราชาวพลพรรค Excel เพราะจะต้องเข้าใจตามหัวข้อเหล่านี้เป็นตัวอย่าง

1. วัน เวลา ถูกเก็บอยู่ในรูปแบบตัวเลข


2. Excel จะเริ่มรับรู้วันแรกคือ 01/01/1900 ซึ่งเริ่มนับลำดับ 1 สำหรับวันปัจจุบันเป็นลำดับที่เท่าไร ทดสอบง่ายๆ คือในเซลล์ใดๆ คีย์ =Today() แล้วจัดรูปแบบเซลล์เป็น General


3. การนำวัน เวลา มาบวกลบกันสามารถทำได้เหมือนตัวเลขธรรมดา แต่หากนำวันที่ที่น้อยกว่าเป็นตัวตั้ง แล้วนำวันที่ที่มากกว่ามาเป็นตัวหัก ผลลัพธ์จะได้เป็นค่าลบ ซึ่ง Excel จะแสดงเครื่องหมาย # จนเต็มความกว้างเซลล์ อย่าเพิ่งตกใจ เพราะนี่เกิดจากการที่ Excel จำรูปแบบที่เป็นวันที่ เมื่อบวกลบกันแล้วก็จะจัดรูปแบบเป็นวันที่ให้เลย หากต้องการให้แสดงเป็นตัวเลขธรรมดา สามารถจัดรูปแบบเป็น General ได้
   
   สมมุติ A1=01/01/2008, A2=05/02/2008 หากอยากทราบผลต่างของวันกรณีนำวันทีที่น้อยกว่า ลบ วันที่ที่มากกว่าและหากว่าไม่ต้องการให้แสดง # จนเต็มความกว้างเซลล์ ให้คลิกขวาที่เซลล์ผลลัพธ์ > Format Cells > แถบ Number > General เป็นอันเรียบร้อย


4. การทำวันเดือนปีที่เป็น พ.ศ.ให้เป็น ค.ศ. สามารถนำตัวเลข 198327 ไปหักออกได้ เช่นมีวันที่ที่เป็น พ.ศ. อยู่ที่คอลัมน์ A และต้องการเปลี่ยนให้เป็นค.ศ. ทั้งหมด สามารถคีย์ 198327 ไว้ที่เซลล์ใดๆ > คลิกขวา > Copy > คลุมข้อมูลในคอลัมน์ A > คลิกขวา > Paste Special > Value > Subtract > OK สังเกตผลลัพธ์ที่ได้
   
   แต่หากข้อมูลมีความหลากหลาย การหักด้วย 198327 จะพบว่าวันที่จะคลาดเคลื่อนไปได้ในบางช่วงของบางปี แต่สามารถประยุกต์ 198327 มาใช้ได้ ยกตัวอย่างเช่น ต้องการปรับวันที่ที่คีย์เป็น พ.ศ. ให้เป็น ค.ศ. โดยสมมุติ A1:A65536 คือเซลล์ที่ต้องการแปลงวันที่
   
   
   
   1. ใช้ Datedif เช่น
      
      =Datedif(A1-198327,A1,"md")+A1-198327
      
      Enter > Copy ลงด้านล่าง
      
      การใช้สูตรนี้ เมื่อแปลงเป็น ค.ศ. แล้ว ปีใดเดือน ก.พ. มี 29 วัน วันที่ 1 ของเดือน มี.ค.จะถูกละไว้ ซึ่งจะเป็นการ Run วันที่ให้ใหม่ ไม่ต้องหาว่าปีใดเดือน ก.พ.มี 29 วัน
   
   
   2. หากต้องการวัน เดือน ตรงกับวันที่ที่ต้องการแปลง ไม่สนใจเรื่อง 29 วันแต่ต้องการให้วันและเดือนตรงกัน ใช้ฟังก์ชั่น Date
      
      =Date(Year(A1)-543,Month(A1),Day(A1))
      
      Enter แล้ว Copy ลงด้านล่าง
      
      การ ใช้สูตรนี้ หากเดือน ก.พ.ของ "ปีต้องการแปลง" มี 29 วัน แต่เดือน ก.พ.ของ "ปีที่แปลงมาเป็น" มีแค่ 28 วัน วันที่ 1 มี.ค. จะซ้ำ 2 ครั้ง เช่นปี พ.ศ. ...2504, 2508, 2512, 2516, 2520 ฯลฯ
   
   
   3. ตัวอย่างสูตรการแปลงวันที่ในคอลัมน์ A โดยไม่เอาค่าซ้ำ
      
      =(Text(A1,"d/m/")&Text(A,"yyyy")-543)+0
      
      Enter แล้ว Copy ลงด้านล่าง
      
      สูตรนี้หากแปลงมาเป็นวันที่ที่ไม่มีอยู่จริงจะเกิดค่าผิดพลาด ซึ่งสามารถประยุกต์ค่าผิดพลาดเป็นค่าใด ๆ ตามต้องการ
   
   
   4. กรณีวันที่ต้องการแปลงอยู่ในช่วงปี 2499 ถึง 2562 สามารถใช้สูตร
      
      =A1-198327+(Day(A1-198327)<>Day(A1)) 
      และหากต้องการทำวันเวลาที่เป็น ค.ศ.ให้เป็น พ.ศ.ทำอย่างไร? อย่าเพิ่งดีใจ จะบอกว่าเอา 198327 ไปบวกใช่ไหม ผิด! การคีย์วันที่ควรคีย์เป็น ค.ศ. หากต้องการเป็น พ.ศ. ให้จัดรูปแบบเอาครับพี่น้องครับ ไม่ใช่ไปบวกด้วย 198327 ครับ
      โปรดคำนึงว่าการแปลงวันที่ที่เป็น พ.ศ. ให้เป็น ค.ศ. นี่คือการแก้ไข เพราะวันที่ควรคีย์เป็น ค.ศ. และการนำ 198327 มาใช้แล้ว ได้วันที่ที่ตรงกันหรือไม่ ปัญหาไม่ใช่อยู่ที่ตัวเลข 198327 แต่โอกาสที่นำ 198327 ไปหักแล้วได้วันที่ที่ตรงกันนั้นมีมาก สำคัญที่การประยุกต์ใช้ เช่นถ้าได้วันที่ที่ไม่ตรงแล้วจะทำอย่างไร ใช้สูตรหรือฟังก์ชั่นใดช่วยได้ และถ้าใช้ 198327 หักแล้วได้วันที่ตรงกันก็ไม่จำเป็นต้องไปคิดฟังก์ชั่นให้ซับซ้อน
   
   
   
   


5. มาในเรื่องของเวลาบ้างใน 1 วันหรือ 24 ชม. แทนด้วยเลข 1 หรือก็คือ 24/24 หากคีย์ 1/24 แล้วจัดรูปแบบเป็นเวลาจะได้ 1:00:00 ทดลองคีย์เลขอื่นๆ สังเกตดูผล
   ทบทวนกันหน่อย
   
   
   1. 1 วัน 24 ชม.
   
   
   2. 1 ชม. 60 นาที
   
   
   3. 1 นาที 60 วินาที
   
   
   4. 1 วัน 1440 นาที (24 ชม. * 60 นาที)
   
   
   5. 1 วัน 86400 วินาที (24 ชม. * 60 นาที * 60 วินาที)
   
   
   
   


6. การคีย์เวลาต้องคีย์ตัวเลขคั่นด้วย : เสมอ เมื่อคีย์เวลาแล้วอยากรู้ว่าเป็นเลข Serial ใดก็ให้จัดรูปแบบเป็น General


7. การรวมเวลาที่เกิน 24 ชม. ต้องจัดรูปแบบของเซลล์ผลลัพธ์เป็น [h]:mm:ss หากไม่เอาส่วนที่เป็นนาที วินาที สามารถละไว้ได้


8. การแปลงเวลาในรูปแบบทศนิยมให้เป็นรูปแบบเวลา ยกตัวอย่างเช่น
   
   A1=1.5 ต้องการแปลงเป็น 1:30 ใช้สูตรดังนี้
   
   =Int(A1)/24+(A1-Int(A1))*60/1440
   
   ที่ต้องนำส่วนของนาทีคูณด้วย 60 ก่อนเพราะว่า 0.5 คือครึ่งหนึ่งของชม. เทียบบัญญัติไตรยางค์ได้ดังนี้
   
   
   1. 1 ชม. เท่ากับ 60 นาที
   
   
   2. 0.5 ชม. เท่ากับ 60/1*0.5 นาที ผลลัพธ์ คือ 30 นาที
   
   
   3. การทำนาทีให้เป็นวัน ก็ขึ้นไปอ่านจากหัวข้อที่ 5 ก็จะทราบว่าต้องนำ 1440 ไปหาร ก็เป็นอันเรียบร้อย
      
       เอ๋า...เอาเข้าไป ก็ในเมื่อรู้ว่า 1 วัน คือ 24/24 และรู้ว่า 1 ชม. คือ 1/24 แล้วทำไมไม่รู้ว่า 1.50 ชม. นั่นก็คือ 1.50/24 ไปทำให้ยากทำไมครับท่านผู้ชมครับ
      
      แล้วถ้า 1.30 ชม.ล่ะ จะแปลงเป็น 1:30 ชม. ได้อย่างไร?
      
      ง่ายมาก ก็เปลี่ยน . ให้เป็น : ครับ มาดูตัวอย่างกัน
      
      =Substitute(Text(A1,"0.00"),".",":")+0 หรือ
      
      =Int(A1)/24+(A1-Int(A1))*100/1440
      
      เนื่องจากเราทราบว่า 0.30 คือ 30 นาที ก็นำ 100 ไปคูณเพื่อให้เป็น 30 จากนั้นก็ทำให้เป็นวันโดยการหารด้วย 1440
      
      สังเกตเห็นอะไรไหมหนอ?
      
      
      1. ถ้าไม่เห็นก็โปรดสังเกตว่า ส่วนของชม.เราก็ทำให้เป็นวัน ส่วนของนาทีเราก็ทำให้เป็นวัน
      
      
      2. หลังจากคำนวณได้แล้วต้องจัดรูปแบบเป็นเวลา
      
      
      
      
   
   
   
   


9. ฟังก์ชั่นที่ควรศึกษาเกี่ยวกับวันเวลาได้แก่
   Datedif(), Hour(), Minute(), Secound(), Weekday(), Day(), Today(), Date(), Month(), Year() เป็นต้น

Read More

Microsoft Excel Tips and Tricks: ลำดับการคำนวณใน Excel

ตัวดำเนินการในการคำนวณ

การทราบถึงตัวดำเนินการและลำดับความสำคัญของตัวดำเนินการที่ใช้ในการคำนวณของ Excel จะทำให้เราใช้ฟังก์ชั่นและสูตรด้วยความเข้าใจ สามารถที่จะลดความยาวของสูตรหรือฟังก์ชั่นได้ เช่น ลดวงเล็บให้เหลือเท่าที่จำเป็น เป็นต้น

ตัวดำเนินการเป็น ตัวกำหนดรูปแบบของการคำนวณที่ใช้ในสูตร ซึ่ง Excel มีรูปแบบตัวดำเนินการ 4 รูปแบบที่แตกต่างกันคือ การคำนวณตัวเลข, การเปรียบเทียบ, อักขระ, และการอ้างอิง

รูปแบบของตัวดำเนินการ

 รูปแบบในการคำนวณตัวเลขหรือตัวดำเนินการทางคณิตศาสตร์ เช่น การบวก , ลบ, คูณ, หาร และการหาผลลัพธ์ที่เป็นตัวเลข จะใช้ตัวดำเนินการต่อไปนี้

ตัวดำเนินการ	ความหมาย	ตัวอย่าง
+ (เครื่องหมายบวก)	การบวก	=3+3
– (เครื่องหมายลบ)	การลบ	=3–1, =–1
* (เครื่องหมายคูณ)	การคูณ	=3*3
/ (เครื่องหมายหาร)	การหาร	=-3/3
% (เครื่องหมายเปอร์เซ็นต์)	เปอร์เซ็นต์	=20% --> ความหมายคือ 20/100 หรือ 0.2
^ (เครื่องหมายยกกำลัง)	การยกกำลัง	=3^2

 เครื่องหมายที่ใช้ในการเปรียบเทียบ สามารถเปรียบเทียบค่า 2 ค่าได้ตามเครื่องหมายด้านล่าง ซึ่งผลลัพธ์ที่ได้จะเป็นตรรกะในลักษณะใช่หรือไม่ใช่ หรือ True, False

ตัวดำเนินการเปรียบเทียบ	ความหมาย	ตัวอย่าง
= (เครื่องหมายเท่ากับ)	ความเท่ากับ	=A1=B1
> (เครื่องหมายมากกว่า)	ความมากกว่า	=A1>B1
< (เครื่องหมายน้อยกว่า)	ความน้อยกว่า	=A1< B1
>= (เครื่องหมายมากกว่าหรือเท่ากับ)	ความมากกว่าหรือเท่ากับ	=A1>=B1
<= (เครื่องหมายน้อยกว่าหรือเท่ากับ)	ความน้อยกว่าหรือเท่ากับ	=A1<=B1
<> (เครื่องหมายไม่เท่ากับ)	ความไม่เท่ากับ	=A1<>B1

 ตัวดำเนินการเกี่ยวกับอักขระ ใช้เครื่องหมาย & ในการเชื่อมข้อความ ซึ่งจะทำให้ื่เชื่อมข้อความจากหลายส่วนให้เป็นส่วนเดียวกัน

ตัวดำเนินการเกี่ยวกับอักขระ	ความหมาย	ตัวอย่าง
& (ampersand)	การเชื่อมคำ, เป็นการเชื่อมคำสองคำหรือมากกว่าให้เป็นข้อความเดียวกัน	="ผม"&" รักคุณ"

 ตัวดำเนินการในการอ้างอิง เป็นการรวมช่วงของข้อมูล ดังเครื่องหมายด้านล่าง

ตัวดำเนินการอ้างอิง	ความหมาย	ตัวอย่าง
: (เครื่อง หมายโคลอน, หรือเครื่องหมายทวิภาค)	เป็นเครื่องหมายใช้แทนช่วงข้อมูล ซึ่งเป็นการอ้างอิงถึงช่วงแทนการอ้างอิงแบบเซลล์ต่อเซลล์	B5:B15 เป็นการอ้างอิงเซลล์จาก B5 จนถึง B15
, (เครื่องหมายคอมม่า หรือเครื่องหมายจุลภาค)	เป็นเครื่องหมายใช้ในการรวมช่วงเซลล์	=SUM(B5:B15,D5:D15) เป็นการรวมค่าในเซลล์ B5 ถึง B15 และ D5 ถึง D15 ซึ่งเป็น 2 ช่วงเซลล์
(ช่อง ว่าง) เครื่องหมายอินเตอร์เซกชัน	เป็นการอ้างอิงถึง 2 ช่วงเซลล์ซึ่งมีพื้นที่เหลื่อมกัน	=SUM(B7:D7 C6:C8) ความหมายคือ SUM(C6:D7)

ลำดับ (ศักดิ์) ในการคำนวณของตัวดำเนินการ

สูตร ที่ใช้ในการคำนวณสามารถกำหนดให้เจาะจงได้ว่าให้คำนวณเช่นไร (ส่วนประกอบใดกระทำกับส่วนประกอบใด) การสร้างสูตรการคำนวณจะต้องขึ้นด้วยเครื่องหมาย = เสมอ เครื่องหมายเท่ากับ เป็นการบอกให้ Excel รับรู้ว่าจะมีการคำนวณ ส่วนประกอบที่ตามหลังเครื่องหมายเท่ากับคือ ส่วนประกอบที่จะทำการคำนวณ ซึ่งแบ่งแต่ละส่วนประกอบด้วยตัวดำเนินการในการคำนวณ ซึ่ง Excel จะทำการคำนวณจากซ้ายไปขวาในแต่ละส่วนประกอบของฟังก์ชั่นหรือสูตร

เมื่อมีตัวดำเนินการหลายตัวในสูตรหนึ่งๆ Excel จะคำนวณตามลำดับเครื่องหมายด้านล่าง และหากว่าตัวดำเนินการมีศักดิ์เท่ากัน Excel จะคำนวณจากซ้ายไปขวา

ลำดับที่	ตัวดำเนินการ	ความหมาย	ตัวอย่าง
1.	:	โคลอน	=Sum(A1:B10)
2.		ช่องว่างเดี่ยว	=Sum(A1:C10 B1:D10)
3.	,	คอมม่า	=Sum(A1,B1)
4.	–	ลบ	–1
5.	%	เปอร์เซ็นต์	25%
6.	^	ยกกำลัง	=2^3
7.	* และ /	คูณและหาร	=2*3/4
8.	+ และ –	บวกและหัก	=2-3+1
9.	&	เชื่อมข้อความเป็นข้อความเดียว	="ผม"&" รักคุณ"
10.	= < > <= >= <>	เครื่องหมายเปรียบเทียบ	=A1<C2

การใช้วงเล็บ

การเปลี่ยนลำดับการคำนวณ สามารถที่จะนำวงเล็บมากำหนดเพื่อให้ส่วนประกอบสูตรคำนวณกันก่อนแล้วค่อยไปคำนวณกับส่วนประกอบอื่น
เช่น

=5+2*3 คำตอบจะได้ 11 เนื่องจากเครื่องหมาย * มีศักดิ์สูงกว่าเครื่องหมาย + จึงมีการดำเนินการก่อน แต่หาก กำหนดเป็น
=(5+2)*3 คำตอบจะได้ 21 เนื่องจากกำหนดให้ 5 กับ 2 ทำการบวกกันก่อน โดยใช้เครื่องหมายวงเล็บ จากนั้นนำผลลัพธ์ในเครื่องหมายวงเล็บไปดำเนินการต่อไป

จาก ตัวอย่างด้านล่าง เป็นการนำ B4 บวกกับ 25 ก่อน แล้วค่อยนำ SUM(D5:F5) มาหาร ไม่เช่นนั้นโปรแกรมจะนำ 25 หารด้วย SUM(D5:F5) ก่อนแล้วค่อยบวกกับ B4

=(B4+25)/SUM(D5:F5)

Read More

Microsoft Excel Tips and Tricks: ถ้ามีค่าผิดพลาดจะรวมยอดอย่างไร?

ปัญหานี้ก็น่าสนใจมาก ในการทำงานของเรามักพบว่า บ่อยครั้งที่ผลลัพธ์ตามฟังก์ชั่นที่เราเขียนขึ้นเป็น #N/A หรือ #DIV/0!

การหาผลรวมโดยใช้ฟังก์ชั่น Sum ธรรมดาก็ไม่สามารถหาผลลัพธ์ได้ เอ... แล้วเราจะทำอย่างไรกันดี 

ในเมื่อไม่สามารถหาด้วยฟังก์ชั่น Sum ได้ เราก็ลองหาฟังก์ชั่นอื่นหรือหาทางประยุกต์ดูครับ ลองนึกสิครับว่าฟังก์ชั่นไหนบ้างที่เป็นการรวมตามเงื่อนไข 

หลังจากนึกถึงการประยุกต์ใช้ฟังก์ชั่นจนหน้ามืดได้ที่แล้ว ก็มาดูตัวอย่างการประยุกต์ฟังก์ชั่น Sum กับ Sumif เพื่อเป็นแนวทางดูครับ 

สมมุติตัวอย่าง ข้อมูลอยู่ที่ A1:A5 เป็นข้อมูลด้านล่างตามลำดับ อยากได้ผลรวมที่ช่อง A6 เป็น 45 จะต้องใช้สูตรอย่างไร?

10
#N/A
20
#DIV/0!
15

มาดูตัวอย่างการใช้ สูตรที่ A6 ตามนี้ครับ

1. =Sum(Sumif(A1:A5,{"<0",">0"})) หรือ


2. =Sumif(A1:A5,"<=9.99999999999999e307")

ทั้งสองสูตรด้านบน Enter ธรรมดาครับ นอกจากนี้ยังมีสูตร Array ที่พวกเราชอบมากๆ มาช่วยเราได้อีกได้แก่

=Sum(If(Isnumber(A1:A5),A1:A5))

สูตรนี้ต้องกด 3 แป้นครับ เพื่อทำเป็นสูตร Array คือ Ctrl+Shift+Enter

ต่อไปนี้เราก็ไม่ต้องกลัวเรื่องการรวมยอดที่มีข้อผิดพลาดอีกแล้วครับ 

Read More

Microsoft Excel Tips and Tricks: การ Copy หลายๆ ครั้งแล้ว Paste

Microsoft Office 2003 เป็นต้นมา เพิ่มความสามารถที่โดดเด่นหลายประการ ประการหนึ่งที่สำคัญมาก คือ การเก็บข้อมูลไว้ใน Clipboard ซึ่งเป็นเสมือนความจำสำรอง โดยสามารถเก็บความจำที่ทำการ Copy ไว้ได้ถึง 24 ครั้ง เป็นประโยชน์ต่อการทำงาน ช่วยประหยัดเวลาในทำงานได้มากทีเดียว

การใช้งานก็สามารถพลิกแพลงได้มากมาย เป็นต้นว่าสามารถ Copy จากการ Filter เฉพาะข้อมูลที่ต้องการ แล้วค่อยวางทีเดียว หรือ Copy จาก Microsoft Office ด้วยกัน เช่น Word แล้วไปวางใน PowerPoint หรือ Excel หรือกลับกัน

นอกจากนี้ยังสามารถ Copy จาก Application ใดๆ ก็ได้เพื่อที่จะนำไปวางใน Microsoft Office เพียงแต่ต้องเปิด Clipboard ไว้ก่อนเท่านั้น

การเปิด Clipboard สามารถทำได้ดังนี้

1. เข้า Microsoft Office > เลือกข้อมูลที่ต้องการ > กดแป้น Ctrl+C,C


2. คลิกเมนู Edit > Office Clipboard

จากนั้นก็สามารถ Copy ข้อมูล, ภาพ ได้เรื่อย ๆ ไม่เกิน 24 ครั้ง หากเกิน 24 ครั้ง ข้อมูลที่ Copy มาในครั้งแรกสุดจะถูกทับด้วยข้อมูลครั้งถัดมา โดยทุกการ Copy จะถูกเก็บไว้ใน Clipboard เราสามารถเลือกข้อมูลที่ Copy มาวางใน Microsoft Office ใด ๆ ได้ตามต้องการโดย ลดการ Copy และ Paste เป็นครั้ง ๆ ไปเนื่องจากสามารถเลือกวางทุกข้อมูลที่เก็บอยู่ใน Clipboard ได้พร้อมกันทีเดียว

ลองทดสอบเล่นดูครับ 

Read More

Microsoft Excel Tips and Tricks: Edit > Go to > Special

สำหรับ Excel 2003 แล้ว เมนู Edit เป็นเมนูที่สำคัญทีเดียวสำหรับผู้ที่จะใช้ Ms Excel อย่างมืออาชีพ สำหรับ Edit > Go to > Special เป็นการเข้าถึงข้อมูลตามที่ต้องการ ได้แก่

1. Comment หมายถึงการเข้าถึงเซลทุกเซลล์หรือเฉพาะเซลล์ในพื้นที่ที่ถูกเลือก ซึ่งมี Comment ปรากฏอยู่


2. Constants หมายถึงการเข้าถึงค่าคงที่ในเซลล์ทุกเซลล์ หรือเฉพาะเซลล์ในพื้นที่ที่ถูกเลือก ซึ่งมีค่าคงที่ปรากฏอยู่ ค่าคงที่ดังกล่าวมี Option ให้เลือก 4 แบบว่าจะเลือกค่าคงที่ที่เป็น




1. ตัวเลข


2. อักขระ


3. ค่าตรรรกะ


4. ค่าผิดพลาด




3. Formulas หมายถึงการเข้าถึงเซลล์ทุกเซลล์หรือเฉพาะพื้นที่ที่ถูกเลือก ที่มีสูตรหรือที่มีฟังก์ชั่นปรากฏอยู่ ซึ่งมี 4 ตัวเลือกคือ ฟังก์ชั่นหรือสูตรที่ให้ผลลัพธ์เป็น




1. ตัวเลข


2. อักขระ


3. ค่าตรรรกะ


4. ค่าผิดพลาด




4. Blanks หมายถึงการเข้าถึงเซลล์ทุกเซลล์หรือเฉพาะพื้นที่ที่ถูกเลือกที่เป็นเซลล์ว่าง


5. Current region หมายถึงการเข้าถึงพื้นที่ที่มีข้อมูลและติดกับเซลล์ที่เลือก


6. Current array หมายถึงการเข้าถึงเซลล์ทุกเซลล์ที่เป็น Array เดียวกันกับเซลที่เลือก ซึ่งเกิดจากการคีย์สูตรพร้อม ๆ กันทีเดียวทุกเซลแล้วกด Ctrl+Shift+Enter เพื่อทำเป็นสูตร Array


7. Objects หมายถึงการเข้าถึงวัตถุต่าง ๆ ที่สร้างขึ้นจากพื้นที่ทั้งหมด


8. Row differences หมายถึงการเข้าถึงเซลล์ที่แตกต่างจากเซลอื่นในแถวที่เลือก


9. Column differences หมายถึงการเข้าถึงเซลล์ที่แตกต่างจากเซลล์อื่นในคอลัมน์ที่เลือก


10. Precedents หมายถึงการเข้าถึงเซลล์ก่อนหน้าที่เป็นที่มาของสูตร หรือเซลล์ที่เลือกนำมาใช้อ้างอิง ซึ่งมีให้เลือก 2 ระดับ คือ




1. เฉพาะเซลล์ก่อนหน้า


2. ทุกเซลล์ก่อนหน้าที่นำผลลัพธ์มาใช้




11. Dependents หมายถึงการเข้าถึงเซลล์ถัดไปที่นำผลลัพธ์จากเซลล์ที่เลือกไปใช้อ้างอิง ซึ่งมีให้เลือก 2 ระดับ




1. เฉพาะเซลล์ถัดไป


2. ทุกเซลล์ถัดไปที่นำผลลัพธ์ในเซลล์ที่เลือกไปอ้างอิง




12. Last cell หมายถึงการเข้าถึงเซลล์สุดท้ายของแผ่นงานที่เคยเข้าถึงในการทำงาน


13. Visible cells only หมายถึงการเข้าถึงเซลล์ทุกเซลล์หรือเฉพาะพื้นที่ที่ถูกเลือก ที่สามารถมองเห็นได้ ซึ่งไม่รวมพื้นที่ที่ถูกซ่อนไว้


14. Condition formats หมายถึงการเข้าถึงเซลล์หรือพื้นที่ที่มีการกำหนด Condition formats ซึ่งมี 2 ระดับให้เลือก




1. ทุกเซลล์ที่มีการกำหนด Condition fomats


2. เซลที่มีการกำหนด Condition formats เหมือนเซลล์ที่เลือก




15. Data validation หมายถึงการเข้าถึงเซลล์หรือพื้นที่ที่มีการกำหนด Validation ซึ่งมี 2 ระดับ คือ




1. ทุกเซลล์ที่มีการกำหนด Validation


2. เฉพาะเซลล์ที่มีการกำหนด Validation เหมือนเซลล์ที่เลือก

Read More

Microsoft Excel Tips and Tricks: การส่งอีเมลด้วย Excel

ปกติเวลาส่งอีเมล เรามักจะเปิดโปรแกรมสำหรับรับ-ส่งอีเมลโดยเฉพาะขึ้นมา แล้วค่อยส่งอีเมล หรือส่งอีเมลทางเวบเมลต่างๆ

ใน Excel 2003 หรือ Ms Office 2003 อื่นๆ สามารถส่งเมลจากตัวโปรแกรมได้เลย ไม่ต้องเสียเวลาเปิดโปรแกรมรับ-ส่งเมล โดยเข้าไปที่เมนู File > Send To > เลือกว่าจะส่งวิธีไหน

1. Mail Receipient สามารถส่งแบบ Attach ทั้งไฟล์ หรือเฉพาะ Sheet ที่กำลังเปิด ซึ่งใช้ดูอย่างเดียว


2. Mail Receipient (For Review) เป็นการส่งเมลเพื่อให้ตรวจสอบ และแก้ไขได้ ซึ่งต้องกำหนดไฟล์ให้เป็นแบบ Share ไฟล์ก่อน


3. Mail Receipient (as Attachment) เป็นการส่งเป็นไฟล์แนบ

Read More

Microsoft Excel Tips and Tricks: เทคนิคการกรอกเวลาที่มากกว่า 9999:59:59

ปกติการกรอกเวลาที่มากกว่า 9999:59:59 จะไม่สามารถแสดงเป็นเวลาได้ แต่แสดงเป็น Text แทน เนื่องจากเป็นข้อจำกัดหนึ่งของ Excel การกรอกเวลาที่เกินกว่านั้นจำเป็นต้องมีเทคนิคครับ เนื่องจากว่า Excel สามารถแสดงค่าที่มากกว่า 9999:59:59 ได้ เพียงแต่ค่านั้นต้องมาจากการคำนวณไม่ใช่กรอกเข้าไปโดยตรง เมื่อทราบเช่นนี้แล้ว เราก็สามารถประยุกต์การกรอกได้ง่าย ๆ เลย และต้องไม่ลืมจัดรูปแบบเซลล์เป้าหมายเป็น [h]:mm:ss

เช่นต้องการให้แสดงเวลา 654321:40:35 ที่เซลล์ A1

สามารถกรอกค่าที่ A1 ดังนี้

=654321/24+40/1440+35/86400
Enter

24, 1440 และ 86400 มาจากไหน สามารถดูเพิ่มเติ็มได้ที่นี่ครับ วัน เวลาใน Excel

Read More

Microsoft Excel Tips and Tricks: การเชื่อมข้อความแล้วให้ขึ้นบรรทัดใหม่

การเชื่อมข้อความเข้าด้วยกันสามารถใช้เครื่องหมาย & ช่วยในการเชื่อมได้ แต่หากเชื่อมแล้วต้องการให้ข้อความที่เชื่อมกันนั้นขึ้นบรรทัดใหม่ สามารถใช้ Char(10) เข้ามาคั่นกลางระหว่างแต่ละข้อความอีกทีครับ เช่น ต้องการเชื่อมข้อความ A1, B1 และ C1 เข้าด้วยกัน และต้องการให้แต่ละข้อความขึ้นบรรทัดใหม่

สามารถทำได้ดังนี้

1. ที่เซลล์ใด ๆ คีย์
   =A1&Char(10)&B1&Char(10)&C1


2. จากนั้นจัดข้อความโดยคลิกขวาลงในเซลล์ตามข้อ 1 เลือก Format cells แล้วคลิก Tab Alignment ทำเครื่องหมายที่ Wraptext > OK

จากนั้นกำหนดความสูงและความกว้างเซลล์ตามต้องการ

Read More

Microsoft Excel Tips and Tricks: เทคนิคการลบเซลล์ด้วยเมาส์

ปกติเมื่อต้องการลบเนื้อหาในเซลล์สามารถกดแป้น Delete หรือคลิกเมนู Edit > Clear > Contents

เราสามารถลบเนื้อหาในเซลล์ด้วยเมาส์ได้เช่นกันครับ โดย

1. คลุมข้อมูลที่ต้องการลบ


2. คลิกซ้ายค้างที่จุดจับเติม (มุมขวาล่างของเซลล์หรือกลุ่มเซลล์)


3. ลากไปยังทางซ้ายหากต้องการลบเนื้อหาเซลล์ไปทางซ้าย ลากขึ้นด้านบนหากต้องการลบเนื้อหาเซลล์ข้างบน

ตามข้อ 3. หากกดแป้น Shift ร่วมด้วยในขณะลากไปทางซ้ายหรือขึ้นด้านบน จะเป็นการลบเซลล์และขยับเซลล์ด้านขวาหรือด้านล่างมาแทนเซลล์เดิม แต่ถ้าหากลากลงด้านล่างหรือด้านขวาจะเป็นการแทรกเซลล์ หรือลากไปทางซ้ายให้เลยเซลล์ที่คลุมไว้ตามข้อ 1. หรือลากขึ้นด้านบนให้เลยเซลล์ที่คลุมไว้ตามข้อ 1. ก็จะเป็นการแทรกเซลล์เช่นกัน

ทดลองเล่นดูครับ 

Read More