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