เตรียมตัวเข้าทำงาน ข้อสอบเรื่อง Pivot Table (ห้ามทำ แต่ให้คิดเฉยๆ)

 

 

ถ้าใช้ Pivot Table หาว่า ยอดขายเฉลี่ยทั้งปีเป็นเท่าไร

 

ตารางซ้าย ตอบ ____

ตารางขวา ตอบ ____

 

เฉลย

 

คำถามนี้วัดว่า ถ้าเคยใช้ Pivot มาก่อน ต้องทราบว่า Pivot ใช้หลักการคำนวณยังไง

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

 

ตารางซ้าย ตอบ 20

ตารางขวา ตอบ 5

Warning System เตือนล่วงหน้าว่ามีรายการซ้ำหรือไม่ โดยไม่ต้องใช้สายตามองหา

ก่อนที่จะปล่อยให้สูตร Vlookup / Xlookup ทำงานพลาดทันทีที่มีรายการซ้ำ ควรหาทางทำให้ Excel เตือนขึ้นมาล่วงหน้าว่ามีรายการบันทึกซ้ำไว้หรือไม่

เริ่มจากใช้สูตร CountA นับจำนวนรหัสจากรายการทั้งหมด ซึ่งตามตัวอย่างนี้นับได้ 5 รายการ

จากนั้นใช้สูตรนับจำนวนรายการรหัสที่เป็น Unique ไม่นับรายการซ้ำ โดยใช้สูตรใน Excel 365 =Counta(Unique(IdRange)) หรือ =SumProduct(1/CountIF(IdRange,IdRange)) ซึ่งใช้ได้กับทุก Version

นับจำนวนรหัส Unique =3

ดังนั้นจำนวนรายการซ้ำ =5-3 =2 รายการ

+++++++++++++++++++++++++

พอเจอว่ามีรายการซ้ำแล้ว อย่ารีบไปใช้คำสั่ง Remove Duplicates ล่ะครับ เพราะรายการที่ซ้ำ 2 รายการที่นับมาให้นี้ ดูให้ดีจะพบว่าที่ซ้านั้นมากจากรหัสที่กรอกไว้ แต่มีข้อมูลของ Name กับ Amount ต่างกัน

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

นอกจากนี้ Conditional Format จะช่วยแสดงรายการซ้ำให้เห็นว่าอยู่ตรงไหน โดยใช้สูตรนี้เพื่อค้นหา =COUNTIF($B$3:$B3,$B3)>1 

Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1Op3xCE07Q1DBKjxxrbnfGn4o_lVDIsE4/view?usp=sharing

บันทึกย่อความรู้ Excel เพื่อเตรียมตัวเข้าทำงาน

1. แยกพื้นที่ออกเป็น 3 ส่วน คือ ตารางเก็บข้อมูล ตารางคำนวณ และตารางรายงาน
   
   
2. ตารางเก็บข้อมูลต้องมีลักษณะต่อไปนี้ จะช่วยให้สะดวกในการใช้เมนูหรือสูตรค้นหาค่า
1. หัวตารางและรายการ ต้องใช้ row เดียว
2. ห้ามเว้นข้อมูลสำคัญ เช่น รหัส วันที่ ชื่อ ต้องมีกำกับแต่ละรายการ (ห้าม merge)
3. ห้ามติดกับข้อมูลอื่นที่ไม่เกี่ยวข้อง ให้เว้น 1 เซลล์รอบข้างของตารางเก็บข้อมูล
   
   
3. ในตารางเก็บข้อมูลไม่ควรจัดรูปแบบชิดซ้ายขวาหรือกลางเซลล์ จะได้เห็นชัดว่าค่าที่ชิดขวาเป็นตัวเลขจริง ถ้าชิดซ้ายคือค่าที่เป็นตัวอักษร
   
   
4. การบันทึกข้อมูล 
1. ตรวจสอบตัวสะกดให้ถูกต้อง ห้ามใส่วรรคขาด วรรคเกิน ตัวย่อใส่จุด
2. ให้ปล่อยว่างกับเซลล์ที่ยังไม่เคยมีข้อมูล แต่เมื่อมีค่าเท่ากับ 0 จึงใส่เลข 0 ลงไป
3. วันที่ ให้กรอกในรูปแบบ วัน/เดือน/ปีค.ศ. 4 หลัก เช่น 22/3/2025 ห้ามใช้ปีพ.ศ.
   
   
5. พอเก็บข้อมูลไว้แล้ว ให้ใช้ Data > Validation แบบ List ลิงก์รหัสมาใช้ช่วยในการค้นหา โดยให้ใช้ Range Name ช่วยในการอ้างอิง จะได้ลอก List ไปใช้ข้ามชีทได้ด้วย
   
   
6. การลิงก์ข้อมูลมาใช้ ให้กดปุ่ม F4 เพื่อใส่เครื่องหมาย $ กำกับแนว row/column
   
   
7. ปุ่ม F4 ยังทำงานอีกแบบโดยใช้ทวนคำสั่งล่าสุดให้ทำงานซ้ำ
   
   
8. หลีกเลี่ยงการพิมพ์ตำแหน่งอ้างอิงเอง แต่ให้พิมพ์เท่ากับแล้วใช้เมาส์คลิกเซลล์ที่ต้องการ
   
   
9. ควรตั้งชื่อ Range Name ให้กับพื้นที่ตารางจะช่วยให้ค้นหาขอบเขตและใช้อ้างอิงในสูตรได้ง่ายขึ้นโดยการกดปุ่ม F3
   
   
10. ลำดับการคำนวณ ยกกำลัง คูณ หาร บวก ลบ และควรใส่วงเล็บเพื่อจัดลำดับการคำนวณให้ชัดเจน
    
    
11. การพิมพ์สูตรให้ใช้ตัวเล็ก พอกดปุ่ม Enter สูตรที่สะกดถูกต้องจะเปลี่ยนเป็นตัวใหญ่ให้เห็น
    
    
12. ห้ามใส่ค่าคงที่ลงไปในสูตร เช่น =2*3 แต่ให้กรอกตัวเลขลงไปในเซลล์เพื่อใช้รับตัวแปรแล้วจึงลิงก์เซลล์ไปใช้คำนวณ
    
    
13. เซลล์รับตัวแปรควรกำหนดสีฟอนต์ให้เห็นชัด แล้วใช้คำสั่ง Format Cells > Protection ตัดกาช่อง Locked ทิ้งแล้วสั่ง Review > Protect Sheet จะพบว่าเมื่อกดปุ่ม TAB จะกระโดดไปหาเซลล์รับตัวแปรให้เอง
    
    
14. ให้กดปุ่ม F5 > Special เพื่อใช้แยกแยะเซลล์ที่ใช้กรอกค่า (Constant) หรือเซลล์สูตร (Formulas)
    
    
15. การปัดตัวเลข ใช้สูตร =Round(เซลล์ตัวเลข,จำนวนหลักทศนิยม) ส่วนการใช้คำสั่ง Format ให้แสดงทศนิยมนั้นเป็นเพียงปรับการแสดงค่าให้เห็นเท่านั้น ไม่ได้เปลี่ยนค่าแต่อย่างใด
    
    
16. Excel ใช้ค่าที่แท้จริงในการคำนวณ ไม่ได้ใช้ค่าที่เห็นในการคำนวณ
    
    
17. ถ้าอยากเห็นค่าที่แท้จริง ต้องคลิกลงไปในเซลล์แล้วมองในช่อง Formula Bar
    
    
18. ถ้าอยากแกะสูตรในช่อง Formula Bar ให้ลากเลือกส่วนของสูตรที่อยากจะแกะแล้วกดปุ่ม F9 สูตรส่วนนั้นจะคำนวณให้เห็นคำตอบว่าเป็นเท่าไร พอแกะเสร็จแล้วให้กด ESC เพื่อกลับไปเป็นสูตรตามเดิม
    
    
19. สูตร VLookup(รหัส,พื้นที่ตาราง,เลขที่ column, 0) ใช้ค้นหาค่าจากตารางที่มีโครงสร้างมาตรฐาน
1. พื้นที่ตารางติดต่อกันไป
2. โครงสร้างแน่นอนแล้ว ไม่มีการเพิ่มลดหรือสลับ column จะใช้เลขที่ column ได้ตลอด
   
3. column ซ้ายสุดเก็บค่าที่ใช้ค้นหา เช่น รหัส ชื่อ วันที่
4. VLookup มองลักษณะของค่าที่ใช้ค้นหาว่ามีสถานะเป็น Text หรือเป็น Number ตรงกับค่าที่เก็บไว้หรือไม่
5. ในวงเล็บของสูตรนี้ให้ใส่เลข 0 หรือคำว่า FALSE ไว้เพื่อใช้ค้นหาแบบ Exact ซึ่งถ้าหาไม่เจอจะ error ให้เห็นทันที ถ้าไม่ใส่เลข 0 ลงไป สูตรจะทำงานแบบ Approaching/Approximate ซึ่งจะหาอื่นที่มากที่สุดแต่น้อยกว่าหรือเท่ากับค่าที่ใช้หามาให้แทน
6. กรณีที่มีค่าซ้ำ สูตร VLookup จะหาค่าที่เป็นค่าแรกที่เจอ 
   
   
20. สูตร CountIF จะช่วยตรวจสอบนับว่ามีค่าซ้ำหรือไม่ หากนับแล้วพบว่า =1 แสดงว่ามีเพียงค่าเดียว ไม่มีค่าซ้ำ
    
    
21. คำสั่ง Conditional Formatting จะช่วยเปลี่ยนสีเซลล์เพื่อบอกตำแหน่งให้เห็นชัดว่าอยู่ตรงไหน ควรใช้ร่วมกับคำสั่ง Data Validation
    
    
22. แทนการใช้ Pivot Table หายอดรวม ควรใช้สูตร SumIF หรือ SumProduct จะยืดหยุ่นกว่า

www.ExcelExpertTraining.com

ExcelExpertLibrary.blogspot.com

ถ้าใช้ 365 ควรใช้สูตร Filter แทนสูตร XLookup / VLookup

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

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

หากใช้ Excel 365 / 2021 เป็นต้นมา แนะนำให้ใช้ Filter ไปเลยจะปลอดภัยและสมเหตุผลกว่า 

👉 เซลล์ G3 สร้างสูตร =FILTER( C3:D7, B3:B7=F3)

C3:D7 เป็นส่วนของตารางคำตอบที่อยากทราบว่ามี Name กับ Amount อะไรบ้าง

B3:B7=F3 เป็นเงื่อนไขให้เทียบหารหัสจาก B3:B7 ว่าตรงไหนบ้างที่ตรงกับรหัสในเซลล์ F3

สูตรนี้ทำงานแบบ Dynamic Array ด้วย กล่าวคือ เมื่อสร้างสูตรนี้ลงไปในเซลล์ G3 สูตรจะกระจายตัวหาคำตอบให้เอง โดยไม่จำเป็นต้องลอกไปวางที่อื่นอีกแม้แต่น้อย

เชิญ Download ตัวอย่างนี้ได้จาก
https://drive.google.com/file/d/1JPnNHJYcMXmD9GdNNGufM7W0bfxMVO6j/view?usp=sharing

😎 ตัวอย่างนี้มีหลายอย่างทำไว้ที่น่าสนใจอย่างยิ่ง ขอให้ทดลองกรอกรายการในตารางด้านซ้ายเพิ่ม ซึ่งได้ปรับตารางนี้ให้ทำงานแบบ Table ไว้ด้วยแล้วจะพบว่า

1. ในช่อง F3 ที่ใช้ Data Validation แบบ list ไว้ จะมีรหัสเพิ่มตามให้เองและจะจัดการตัดรหัสที่ซ้ำทิ้งไปให้ด้วย อีกทั้งเมื่อลอกเซลล์ F3 ไปวางที่ชีทอื่นก็ยังคงทำงานได้ตามเดิม

2. ในตารางด้านซ้ายจะมีสีเหลืองพาดรายการที่มีรหัสตรงกับที่ต้องการหาตามให้ทันที ซึ่งได้มาจากการใช้คำสั่ง Conditional Formatting

 

ใช้เวลาเรียน Excel แบบไหนดีครับ เรียนครึ่งวัน หรือ 1 วัน 2 วัน 3 วัน

ขอเรียนถามว่าอยากจะเรียนแบบไหนครับ เพื่อให้คุ้มค่าได้ประโยชน์มากที่สุด

1. เรียนครึ่งวัน เรียนแบบเฉพาะกิจ เช่น เพื่อเข้างาน หรือแก้ปัญหาที่ติดใจสงสัย เป็นต้น

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

3. เรียน 2 วัน โดยมีเนื้อหาให้เรียนกันเต็มที่ แต่โอกาสถามน้อย

4. เรียน 2 วัน ลดเนื้อหาให้น้อยลง จะได้มีโอกาสได้ถามกันมากขึ้น

5. เรียน 3 วัน เฉพาะบางหลักสูตรที่ใช้สูตรยากหน่อยหรือต้องใช้ VBA

ปีนี้ผมตั้งหลักใหม่ว่า มาคนเดียวก็มาสมัครเข้าเรียนได้ อยากกำหนดเนื้อหาอะไร เรียนนานแค่ไหนก็ได้กำหนดมาได้ตามสบาย โดยมีค่าเรียนวันละ 4,250 บาทเท่ากันทุกคลาส โดยผมจะประกาศหาคนอื่นมาเข้าเรียนด้วยเต็มที่คลาสละ 6 คน ถ้าผมหาผู้เรียนเพิ่มไม่ได้ก็เรียนคนเดียวตามที่ขอมา

ถ้าพื้นฐานมีไม่มาก สามารถเลือกระยะเวลาเรียนให้นานขึ้นโดยลดเนื้อหาลงบ้าง จะได้มีเวลาทดลองและถามให้หมดข้อสงสัย

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

ชอบแบบไหนครับ ขอทราบความเห็น ผมจะได้เตรียมระบบการสมัครบนเว็บ ExcelExpertTraining.com/private ให้เหมาะ ช่วงนี้ยังไม่เปิดจองวันอบรม ขอเวลาจัดระบบถึงมีนาคม กะว่าผมจะเริ่มรับสอนตั้งแต่เมษาเป็นต้นไปครับ

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

เริ่มแบบง่ายๆ จะสร้างกราฟได้ง่ายนิดเดียว

 🧐 เริ่มแบบง่ายๆ จะสร้างกราฟได้ง่ายนิดเดียว

คิดๆอยู่ว่าที่เราอยากหันไปใช้ Power BI เพื่อสร้าง Dashboards หน้าตาสวยๆหรืออยากใช้ Pivot Chart กันนั้น มีสาเหตุมาจากที่เราสร้างกราฟไม่เป็นหรือเปล่า หรือเคยพยายามสร้างกันแล้วพบว่าสร้างยากกันเหลือเกิน

อย่าไปเอาตารางที่ไม่ได้จัดโครงสร้างไว้พร้อมไปสร้างกราฟนะครับ เริ่มต้นต้องสร้างตารางฐานข้อมูลสำหรับนำไปสร้างกราฟก่อน ซึ่งใช้หลักเดียวกับการออกแบบตารางฐานข้อมูลที่นำไปใช้กับสูตร VLookup นั่นแหละ

☝️ เท่าที่ใช้กันอยู่นั้น ตามตำราจะบอกว่าพอคลิกลงไปในตารางตรงไหนก็ได้แล้วสั่ง Insert > Chart แบบที่ต้องการ จะได้กราฟสร้างขึ้นมาให้ทันที แต่เคล็ดการสร้างกราฟให้ง่ายอยู่ตรงนี้

1. รายการในตารางข้อมูลต้องมีแนวตั้งยาวกว่าแนวนอน เช่น ภาพนี้แนวตั้งมี 5 เซลล์ แนวนอนมี 3 เซลล์ เพราะ Excel ใช้หลักว่าจะนำแนวที่ยาวกว่าไปใช้ในการกำหนดแกนของกราฟ

2. แกนตั้งซ้ายมือสุด จะถูกเลือกให้เป็นแกน X หรือแกนนอนบนกราฟเสมอ

3. แกนตั้งถัดไปทางขวา จะถูกเลือกให้ใช้เป็นตัวเส้นกราฟโดยนำค่าไปใช้เป็นแกน Y

👉 เส้นกราฟใช้สูตร
=Series(ชื่อเส้น, ที่มาของค่า X, ที่มาของค่า Y, เลขที่เส้น)

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

วิธีใช้สูตรนี้จะช่วยให้ได้กราฟเส้นใหม่แบบง่ายๆและตรงตัว อย่าไปใช้วิธี copy ตารางแล้วไปแปะบนหน้ากราฟเลยครับ

เชิญชมคลิปได้จากหลักสูตรเคล็ดการสร้างกราฟให้เห็นปุ้บเข้าใจปั้บ ซึ่งเปิดให้เรียนออนไลน์ ฟรี 1 ปี สมัครเรียนได้ที่เว็บ XLSiam.com

 

 

 

 

 

วิธีทำชีทเดียวให้แสดงหรือซ่อนรายการได้ถึง 7 ชั้น

😎 นักบัญชีหรือคนที่ทำรายงานที่มียอดรวมแยกเป็นรายการย่อยแล้วย่อยอีก แทนที่จะใช้คำสั่งซ่อน Row/Column ซึ่งจะซ่อนได้แค่ชั้นเดียว แนะนำให้ใช้คำสั่ง Data > Group จัดการซ่อนดีกว่าครับ ซ่อนได้ถึง 7 ชั้น

ให้เลือกแนว Row หรือ Column ช่วงที่อยากซ่อนแล้วใช้คำสั่ง Group จะเกิดเส้นด้านนอกตารางที่เรียกว่า Outline ตามภาพนี้แสดงไว้ในอยู่ในกรอบสีเขียว ถ้าอยากถอนออกให้ใช้คำสั่ง Ungroup

☝️ Outline นี้ใช้จัดการแสดงรายละเอียดในหน้าจอในชีทเดียวให้เหลือเท่าที่หัวหน้าแต่ละคนอยากจะดู จะได้ไม่ต้องทำชีทข้อมูลแบบเดียวกันเยอะแยะไปหมดแล้วซ่อนตรงนั้นแสดงตรงนี้ตามหัวหน้าแต่ละคน

Outline นี้ถ้านำไปใช้ร่วมกับสูตร SubTotal จะช่วยทำให้ตัวเลขยอดรวมเปลี่ยนตามการซ่อนที่ทำไว้ด้วย

ตัวเลขยอดรวมจะเปลี่ยนตามการแสดงบนจอโดยเราไม่ต้องแก้สูตรแม้แต่น้อย

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

แนะนำให้ซ่อนเส้น Outline โดยเพิ่มปุ่ม Show Outline Symbols ในกรอบสีม่วงไว้บนเมนู Quick Access Toolbar ตรงมุมซ้ายบนสุดของจอ โดยไปหาปุ่มนี้มาเพิ่มได้จาก Excel Options ตามภาพครับ

พอกดปุ่มนี้ Outline ในกรอบสีเขียวจะซ่อนหายไปทั้งหมด กดอีกทีจะแสดงกลับมา

ชมคลิปได้จาก
https://www.excelexperttraining.com/book/index.php/a-to-z/f-g-h-i-j/f-f-f-f-f/filter-custom-view-subtotal

เป็นส่วหนึ่งจากหลักสูตร Excel Expert Data Management ที่เปิดให้เรียนออนไลน์ ฟรี ที่เว็บ XLSiam.com 

 

 

 

 

 

 

 

ช่องว่าง ควรปล่อยให้ว่าง หรือจะใส่เลข 0 ลงไปดี

🤓 ในตารางฐานข้อมูล เซลล์ที่เป็นช่องว่าง ควรปล่อยให้ว่าง
หรือจะใส่เลข 0 ลงไปดี

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

ถ้าปล่อยว่างไว้ แค่ใช้สูตร Min ก็จะหาตัวเลข 10 ซึ่งเป็นตัวเลขที่ต่ำสุดซึ่งไม่ใช่เลข 0 มาให้ แต่ถ้าใส่เลข 0 ลงไป สูตร Min จะให้คำตอบเป็น 0

ถ้าจะหาตัวเลขที่ต่ำที่สุดซึ่งมากกว่า 0 ต้องใช้สูตร MinIF Array หรือ MinIFS ตามภาพ

Download ตัวอย่าง
https://drive.google.com/file/d/1pIAHwY5wuRckpf4PhAj9szvdSR443snw/view?usp=sharing

รู้อะไรไม่สู้รู้วิชา รู้รักษาตัวรอดเป็นยอดดี รู้อะไรใน Excel ที่เอาตัวรอดได้เอง

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

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

วิธีแกะสูตรที่ว่านี้ก็ไม่ยากอะไร แค่คลิกลากส่วนของตำแหน่งอ้างอิงบนสูตรแล้วกดปุ่ม F9 ทำให้ Excel แสดงออกมาให้เห็น

เช่น ในเซลล์มีสูตร =A1+A2 แล้วพบว่าบวกไม่ได้ กลายเป็น error #VALUE!

 

ให้แกะสูตรโดยลากทับ A1 บนสูตรแล้วกดปุ่ม F9 จะเห็นค่าที่แท้จริงว่ามีค่าเป็นอะไร จากนั้นลากทับ A2 แล้วทำแบบเดียวกัน

 

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

 

ชมวิธีใช้เมาส์แก้ไขตำแหน่งอ้างอิงในสูตรและไปที่เซลล์ต้นทาง

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

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

แต่ 2 วิธีนี้จะมีผลกระทบกับ $ ที่ใส่ไว้ยังไงบ้าง ลองติดตามดูในคลิปนี้

นอกจากการเปลี่ยนสีในตัวสูตรเพื่อบอกตำแหน่งเซลล์ว่าอยู่ตรงไหนแล้ว หากต้องการไปยังตำแหน่งเซลล์ที่ลิงก์มา ให้ใช้วิธีดับเบิลคริกที่เซลล์สูตร Excel จะกระโดดไปที่เซลล์ต้นทางนั้นให้ ถ้าต้นทางลิงก์มาจากแฟ้มที่ยังไม่ได้เปิด ก็จะเปิดแฟ้มนั้นให้อัตโนมัติ พร้อมกับไปที่เซลล์ต้นทางให้เลย (แต่ต้องแก้ระบบใน Excel Options > Advanced > ตัดการช่อง Allow editing directly in cells ทิ้งก่อน)

พอกระโดดไปแล้วอยากจะย้อนกลับไปที่เซลล์สูตรปลายทาง ให้กดปุ่ม F5 (ให้จำว่า F5 ห้า เอาไว้ หา)

https://vimeo.com/530741141 

คลิปนี้มาจากหลักสูตรสุดยอดเคล็ดลับและลัดของ Excel ครับ สมัครเรียนออนไลน์ฟรีได้ที่เว็บ XLSiam.com 

ผลงานความสำเร็จที่น่าภาคภูมิใจจากการใช้ Excel

สมเกียรติ ฟุ้งเกียรติ

1. ได้รับเชิญให้เป็นวิทยากรสอน Excel ให้กับสมาคมส่งเสริมเทคโนโลยี (ไทย-ญี่ปุ่น) ตั้งแต่ปีพ.ศ.2539 - 2557 มีหลักสูตร Excel สอนแทบทุกอาทิตย์ ทุกเดือน และได้รับความนิยมสนใจอยากเรียนอย่างมากจนต้องต่อคิวสมัครเรียนกันข้ามปี วิทยากรที่นี่แทบทุกคนได้รับเชิญมาโดยมุ่งให้ความรู้กับสังคมเป็นหลัก ไม่ได้ใส่ใจกับค่าตอบแทนเพียงเล็กน้อยที่ได้รับ
   
   
2. ได้รับเชิญให้จัดอบรมให้บริษัทปูนซิเมนต์ไทย (SCG) ระหว่างปีพ.ศ.2546 – 2551 และ 2558 – 2560 เพื่อสอน Excel ให้กับพนักงานของ SCG และยังได้รับโอกาสให้สร้างหลักสูตรในนามของ SCG รับบุคคลภายนอกมาเข้าเรียนด้วย
   
   
3. ได้รับรางวัลเกียรติยศจากบริษัทไมโครซอฟท์ยกย่องให้เป็น Microsoft Excel Most Valuable Professional จากการสร้างผลงานทุกปี ต่อเนื่องกันมาตั้งแต่ปีค.ศ.2007 ซึ่งรางวัลนี้จะมอบให้กับคนที่อุทิศตน สละเวลามาให้ความรู้กับสังคมและขึ้นชื่อว่าเป็นผู้เชี่ยวชาญ Excel ได้รับการยอมรับจากกลุ่มผู้ใช้ Excel โดยเป็นคนไทยคนเดียวที่ได้รับรางวัลนี้ติดต่อกันนานที่สุด
   
   
4. ได้รับเชิญสัมภาษณ์ออกรายการวิทยุสถานีอสมท ออกโทรทัศน์ช่อง 9 รายการ E-Life for Business และช่อง ITV และหนังสือพิมพ์มติชนในหัวข้อ Excel Skill : ทักษะที่เด็กจบใหม่ควรมี
   

นอกจากผลงานที่ได้รับเหล่านี้ยังมีความสำเร็จส่วนตัวที่ทำให้กับสังคม จากการสร้างเว็บ ExcelExpertTraining.com ให้ความรู้ แจกแฟ้มตัวอย่าง และมีฟอรัมถามตอบปัญหา Excel มาตั้งแต่ปีพ.ศ.2545 ซึ่งเป็นฝีมือการสร้างเว็บเองและดูแลเองคนเดียว ซึ่งน่ายินดีเป็นอย่างยิ่งที่มีผู้เชี่ยวชาญ Excel อีกหลายท่านมาช่วยตอบปัญหาในฟอรัม และยังช่วยให้ความรู้ฟรีเรื่อยมาใน facebook กลุ่มคนรัก Excel ปัจจุบันถึงต้นปีพ.ศ. 2568 นี้มีสมาชิกกว่า 63,000 คน

ในช่วงโควิดได้ปรับปรุงเว็บ ExcelExpertTraining.com ให้สามารถเข้าเรียนออนไลน์ในราคาที่ถูกมาก ไม่กี่ร้อยบาท มีผู้สนใจสมัครเรียนออนไลน์นับหมื่นคน ต่อมาในปีพ.ศ. 2567 ได้เปิดให้เรียนออนไลน์ ฟรี ทุกหลักสูตรที่เว็บ XLSiam.com ซึ่งมีระบบค้นหาคลิปที่อยากชมได้ด้วย ทำให้สามารถเลือกเรียนเรื่องอะไรก็ได้ที่ต้องการได้ทุกที่ทุกเวลาที่สะดวก

หลังจากเลิกเป็นวิทยากรให้กับสมาคมแล้ว ได้หันมาจัดอบรม Excel แบบส่วนตัวกลุ่มเล็กๆ 1-6 คนที่ห้องอบรม Home of Excel Expert Training ในบ้านรามคำแหงซอย 35 เพื่อมุ่งให้ได้รับประโยชน์จากการเรียนสูงสุด

ชมการสอนให้กับ SCG
https://www.excelexperttraining.com/online/courses/99-excel-sandbox/

ชมคลิปออกทีวี
https://vimeo.com/571470855?share=copy#t=0
 

 

 

พอกดปุ่ม Enter คุณคิดว่าควรเลื่อนลงหรือเลื่อนไปทางไหนสมเหตุผลที่สุด

ผมแนะนำให้ไม่เลื่อนไปทางไหนโดยยังคงอยู่ที่เซลล์เดิม โดยไปตัดกาช่อง After pressing Enter ใน Excel Options ตามภาพนี้ทิ้งไป ซึ่งการที่ยังคงอยู่ในเซลล์เดิมนี้เหมาะในการสร้างสูตรมากกว่าให้เลื่อนไปที่อื่น จะได้ไม่ต้องเสียเวลาไปเลื่อนกลับมาที่เซลล์เดิมหากต้องการแก้ไขเปลี่ยนแปลงสูตรอีก

หากต้องการกรอกค่าแล้วให้เลื่อนไปทางซ้ายขวาบนล่าง ให้เปลี่ยนไปกดปุ่มลูกศรแทน

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

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

ส่วนใหญ่ติดนิสัยใช้กันมานานแล้วไม่อยากเปลี่ยน ... นี่แหละเหตุผลสำคัญที่เจอครับ

ถ้ายังใช้ระบบตามเดิมที่เลื่อนลง แล้วอยากจะให้ไม่เลื่อน ให้กดปุ่ม Ctrl พร้อมกับ Enter จะอยู่ที่เดิม

 

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

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

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

จับเซลล์สีเหลืองมาบวกกับเซลล์สีเขียว สร้างสูตรแค่นี้ ทำไมง่าย ทำไมยากจัง

 

Download ตัวอย่างไปลองทำกันดูครับ

https://drive.google.com/file/d/1d32uqyiuBo4iklg22hBYYiDl2K0GCD5y/view?usp=sharing

ในเซลล์สีส้ม จงหายอดรวมของเซลล์สีเหลืองกับเซลล์สีเขียว

สูตรบน จับเซลล์สีเหลืองมาบวกกันก่อนแล้วตามด้วยเซลล์สีเขียวบวกกัน

F4 =SUM(B4:E4)+SUM(G4:J4)

สูตรล่าง จับเซลล์สีเขียวมาบวกกันก่อนแล้วตามด้วยเซลล์สีเหลืองบวกกัน

F7  =SUM(G7:J7)+SUM(B7:E7)

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

สาเหตุที่ Excel ยืดเซลล์ออกไปด้านขวามือออกไปทับเซลล์ที่ติดกันนั้น เพราะระบบที่ตั้งไว้ของ Excel กำหนดให้แก้ไขได้ในเซลล์โดยตรง (Allow editing directly in cells) หากจะทำให้สร้างสูตรได้ง่ายขึ้นก็ต้องไปตากาช่องนี้ทิ้งใน Excel Options > Advanced

หลังจากตัดกาช่องนี้ทิ้งแล้ว การสร้างสูตรหรือแก้ไขสูตรจะทำได้ง่ายขึ้นมากโดยหันไปใช้ช่อง Formula Bar แทน สูตรที่สร้างลงไปในเซลล์ไม่ยืดไปทับเซลล์ด้านข้างที่ติดกันอีกต่อไป

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