9 ข้อผิดพลาดทั่วไปของฟังก์ชัน Excel—และวิธีแก้ไข

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

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

1. VLOOKUP – หยุดใช้เพื่อทุกสิ่ง

วิธีที่ผิด: ผู้ใช้จำนวนมากพึ่งพา VLOOKUP สำหรับทุกสิ่ง แม้ว่าฟังก์ชันอื่นจะเหมาะสมกว่าก็ตาม VLOOKUP ค้นหาทางด้านขวาเท่านั้น ต้องมีข้อมูลที่จัดเรียงสำหรับการจับคู่โดยประมาณ และสามารถแยกได้เมื่อแทรกคอลัมน์ลงในข้อมูลต้นฉบับ ตามค่าเริ่มต้น VLOOKUP จะใช้การจับคู่โดยประมาณ (TRUE) ซึ่งจำเป็นต้องมีการเรียงลำดับคอลัมน์แรก และมักจะส่งคืนรายการที่ไม่ถูกต้องหากข้อมูลไม่ได้รับการจัดเรียงอย่างถูกต้อง

แก้ไข: ใช้ XLOOKUP ที่ตรงกันทุกประการใน Microsoft 365 หรือใช้ INDEX+MATCH หากคุณใช้ Excel เวอร์ชันเก่า

ใช้ INDEX/MATCH เพื่อความยืดหยุ่นที่มากขึ้น:

• เลือกเซลล์และแทรกสูตรต่อไปนี้เพื่อค้นหาราคาของผลิตภัณฑ์ใดผลิตภัณฑ์หนึ่ง

=INDEX(G2:G101, MATCH(L3, E2:E101, 0))

นอกจากนี้ยังจัดการ "การค้นหาทางซ้าย" ด้วยการเลือกคอลัมน์ส่งคืนที่คุณต้องการภายใน INDEX

สำหรับผู้ใช้ Excel 365 โปรดพิจารณา XLOOKUP:

• เลือกเซลล์และแทรกสูตรต่อไปนี้

=XLOOKUP(L3, E2:E101, G2:G101, "Not found", 0)

0 บังคับให้ตรงกันทุกประการ และอาร์กิวเมนต์ที่สี่ให้ข้อความที่เป็นมิตรว่า "ไม่พบ"

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

2. SUMIF/SUMIFS – การเขียนเกณฑ์ไม่ถูกต้อง

วิธีที่ผิด: การเขียนโอเปอเรเตอร์ลงในสตริงเกณฑ์โดยตรง (เช่น “>=2025-03-01” เป็นข้อความธรรมดา) แทนที่จะเชื่อมเข้าด้วยกัน ผสมอาร์กิวเมนต์ Criteria_range และ sum_range หรือใช้การอ้างอิงคอลัมน์ทั้งหมด เช่น A:A ซึ่งอาจทำให้การคำนวณช้าลง

แก้ไข: รักษาช่วงให้สอดคล้องกัน และสร้างเกณฑ์ตัวดำเนินการโดยการต่อข้อมูล ใช้ DATE/EOMONTH สำหรับตัวกรองวันที่ที่มีประสิทธิภาพ

• ใช้ช่วงเฉพาะที่มีข้อมูลจริงของคุณ เนื่องจากรวดเร็วและแม่นยำยิ่งขึ้น

=SUMIF(D2:D101,"East",H2:H101)

• ยอดขายรวมเดือนมีนาคม 2025 สำหรับภูมิภาคตะวันตก:

=SUMIFS(
H2:H101,
D2:D101, "West",
A2:A101, ">=" & DATE(2025,3,1),
A2:A101, "<=" & EOMONTH(DATE(2025,3,1),0)
)

วิธีนี้จะช่วยหลีกเลี่ยงปัญหาเกี่ยวกับสถานที่เกิดเหตุและปัญหาวันที่ของข้อความ ตรวจสอบให้แน่ใจว่า sum_range และแต่ละเกณฑ์_ช่วงมีขนาดเท่ากันเสมอ

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

3. คำสั่ง IF – ฝันร้ายที่ซ้อนกัน

วิธีที่ผิด: การเปรียบเทียบฟังก์ชันบูลีนกับ TRUE โดยไม่จำเป็น (เช่น =IF(AND(E4=”Y”,F4=”Y”)=TRUE, …)) หรือการสร้างคำสั่ง IF ที่ซ้อนกันแบบลึกสำหรับแผนผังหมวดหมู่แบบง่าย ซึ่งอ่านและบำรุงรักษาได้ยาก

แก้ไข: ปล่อยให้ฟังก์ชัน AND/OR ส่งกลับค่าบูลีนโดยตรง สำหรับเงื่อนไขหลายรายการ ให้ใช้ IFS, CHOOSE/MATCH หรือ SWITCH เพื่อให้สูตรสะอาดขึ้นและจัดการได้ง่ายขึ้น

• การทดสอบบูลีนที่สะอาด:

=IF(AND(D2="East", F2>=3), "Bulk East", "Other")

• คะแนนหลายเงื่อนไขโดยใช้ IFS:

=IFS(F2:F101>=5, "High", F2:F101>=2, "Medium", TRUE, "Low")

• แผนผังหมวดหมู่ที่เรียบร้อยพร้อม CHOOSE/MATCH (เช่น การแปลงเกรดตัวอักษรใน J2 เป็นคะแนน GPA):

=CHOOSE(MATCH(J2, {"A","B","C","D","F"}, 0), 4,3,2,1,0)

ซึ่งง่ายกว่าและเกิดข้อผิดพลาดน้อยกว่าปิรามิด IF แบบลึก

4. เชื่อมต่อ – แนวทางที่ล้าสมัย

วิธีที่ผิด: ยังคงใช้ฟังก์ชัน CONCATENATE ที่ล้าสมัยหรือเชื่อมโยงเครื่องหมายและหลายตัวเข้าด้วยกันเพื่อรวมข้อความที่ซับซ้อน ซึ่งอาจยุ่งยากและเกิดข้อผิดพลาดได้ง่าย

แก้ไข: ใช้ TEXTJOIN ใน Excel เวอร์ชันใหม่ เนื่องจากสามารถรวมค่าหลายค่าด้วยตัวคั่นที่ระบุได้อย่างมีประสิทธิภาพ

• ใช้ TEXTJOIN สำหรับหลายค่าที่มีตัวคั่น:

=IF(L2="","", TEXTJOIN(", ", TRUE, FILTER(E$2:E$101, C$2:C$101=L2)))

ใช้ตัวดำเนินการ &สำหรับการต่อข้อมูลแบบง่าย แต่ TEXTJOIN จะสว่างขึ้นเมื่อคุณต้องการ:

• ข้ามเซลล์ว่างโดยอัตโนมัติ
• ใช้ตัวคั่นเดียวกันตลอด
• เข้าร่วมช่วงของเซลล์

5. COUNTIF – ละเว้นประสิทธิภาพหลายเกณฑ์

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

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

• นับรหัสที่ขึ้นต้นด้วย US-E:

=COUNTIF(J2:J101, "US-E*")

• นับคำสั่งซื้อที่มีผลิตภัณฑ์ที่มีคำว่า "โทรศัพท์" (ไม่คำนึงถึงตัวพิมพ์เล็กและใหญ่):

=SUMPRODUCT(--ISNUMBER(SEARCH("phone", E2:E101)))

หากคุณต้องการ “contains” โดยคำนึงถึงตัวพิมพ์เล็กและตัวพิมพ์ใหญ่ ให้แทนที่ SEARCH ด้วย FIND

6. ROUND – การปัดเศษในเวลาที่ผิด

วิธีที่ผิด: สมมติว่าการจัดรูปแบบเซลล์เป็นทศนิยมสองตำแหน่งจะปัดเศษค่าที่ซ่อนอยู่ในการคำนวณด้วย การจัดรูปแบบจะเปลี่ยนเฉพาะลักษณะที่ปรากฏ ไม่ใช่ค่าที่เก็บไว้ ซึ่งอาจทำให้เกิดความคลาดเคลื่อนในผลรวมได้

แก้ไข: ปัดเศษในขั้นตอนที่กฎเกณฑ์ทางธุรกิจกำหนด ใช้ ROUND, ROUNDUP, ROUNDDOWN หรือ MROUND เพื่อเพิ่มขั้น

• จำนวนบรรทัดที่ปัดเศษ:

=ROUND(F2:F101*G2:G101, 2)

• ปัดเศษเป็น 0.05 ที่ใกล้ที่สุด (ร่วมกับการกำหนดราคาเงินสด):

หลักการสำคัญ: ปัดเศษผลลัพธ์สำหรับการแสดงผล แต่คงความแม่นยำในการคำนวณขั้นกลาง เว้นแต่จะกำหนดไว้เป็นอย่างอื่นโดยเฉพาะ

7. การจัดรูปแบบข้อความและวันที่ที่ใช้ในการคำนวณ

วิธีที่ผิด: การแปลงค่าเป็นข้อความเพื่อการแสดงผล จากนั้นลองใช้ผลลัพธ์ตามข้อความเหล่านั้นในการคำนวณทางคณิตศาสตร์ หรือเปรียบเทียบสตริงวันที่ที่จัดรูปแบบกับค่าวันที่จริง ซึ่งมักจะเกี่ยวข้องกับการถือว่าวันที่เป็นข้อความและใช้การจัดการข้อความที่ซับซ้อนแทนฟังก์ชันวันที่เฉพาะ

แก้ไข: ทำการคำนวณค่าตัวเลขดิบ ใช้ฟังก์ชัน TEXT ในขั้นตอนการนำเสนอขั้นสุดท้ายเท่านั้น เช่น สำหรับชื่อแผนภูมิหรือป้ายกำกับรายงาน

• ใช้ฟังก์ชันวันที่ที่เหมาะสมกับค่าวันที่จริง:

=YEAR(A1)
=MONTH(A1)
=DAY(A1)

• ชื่อแดชบอร์ดที่แสดงเดือนและยอดรวมโดยไม่ทำลายตัวเลข:

="March " & YEAR(DATE(2025,3,1)) & " Sales: " &
TEXT(SUMIFS(H$2:H$101, A$2:A$101, ">="&DATE(2025,3,1), A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0)),"$#,##0")

• ตัวกรองเดือนที่มีประสิทธิภาพโดยใช้วันที่ (ไม่ต้องใช้ข้อความ):

=SUMIFS(H$2:H$101, D$2:D$101, "East",
A$2:A$101, ">="&DATE(2025,3,1),
A$2:A$101, "<="&EOMONTH(DATE(2025,3,1),0))

8. SUMPRODUCT – ลืมการบีบบังคับหรือเมื่อตัวกรองชัดเจนขึ้น

วิธีที่ผิด: ลืมบังคับอาร์เรย์บูลีน (TRUE/FALSE) เป็นตัวเลข (1/0) หรือสร้างอาร์เรย์ที่มีขนาดไม่ตรงกัน ในทางกลับกัน การใช้สูตร SUMPRODUCT ที่ซับซ้อนเมื่อชุดค่าผสม SUM(FILTER(…)) แบบธรรมดาใน Excel สมัยใหม่จะอ่านง่ายกว่า

แก้ไข: ใช้เอกนารีคู่ — หรือคูณด้วย 1 เพื่อบังคับ TRUE/FALSE ให้เป็น 1/0 ใน Microsoft 365 แนะนำให้ใช้รูปแบบ SUM และ FILTER แบบโปร่งใสสำหรับผลรวมหลายเกณฑ์

• ยอดขายรวมสำหรับภูมิภาคตะวันออก ผลิตภัณฑ์ที่มี "โทรศัพท์" และปริมาณอย่างน้อย 3 รายการที่เป็นมิตรต่อสิ่งแวดล้อม:

=SUMPRODUCT((D$2:D$101="East") * ISNUMBER(SEARCH("phone", E$2:E$101)) * (F$2:F$101>=3) * H$2:H$101)

• ตรรกะเดียวกันกับอาร์เรย์แบบไดนามิก (365/2021):

=SUM(FILTER(H$2:H$101, (D$2:D$101="East")*(ISNUMBER(SEARCH("phone", E$2:E$101)))*(F$2:F$101>=3)) )

เมื่อใช้ FILTER เกณฑ์จะคูณกันเป็น 1/0 เกต และผลลัพธ์จะยังคงอ่านได้

9. IFERROR ใช้เป็นพลาสเตอร์ปิดแผล

วิธีที่ผิด: การล้อมสูตรขนาดใหญ่และซับซ้อนใน IFERROR(…,””) เพื่อระงับข้อผิดพลาดทั้งหมด สิ่งนี้เป็นอันตรายเพราะสามารถซ่อนปัญหาที่แท้จริงได้ เช่น ชื่อช่วงที่สะกดผิด #DIV/0 ของแท้! ข้อผิดพลาดหรือข้อบกพร่องเชิงตรรกะอื่น ๆ ที่คุณควรทราบ

แก้ไข: จับเฉพาะข้อผิดพลาดเฉพาะที่คุณคาดหวัง ใช้ IFNA สำหรับฟังก์ชันการค้นหาเมื่อไม่พบค่า และใช้คำสั่ง IF เพื่อจัดการเซลล์ว่างก่อนทำการคำนวณ

• แสดงค่าว่างหากคีย์การค้นหาว่างเปล่า มิฉะนั้น ให้แสดงข้อความที่เป็นมิตร “ไม่พบ” เฉพาะเมื่อข้อความนั้นหายไปจริงๆ เท่านั้น:

=IF(L2="","", IFNA(XLOOKUP(L2, E$2:E$101, G$2:G$101),"No match"))

ว่างเปล่า:

ไม่มีการจับคู่:

• แปลงข้อความเป็นตัวเลขเฉพาะเมื่อมีการแปลง:

=IF(A2="", "", VALUE(A2))

วิธีนี้จะรักษาค่าศูนย์ที่ถูกต้องและหลีกเลี่ยงการปกปิดข้อผิดพลาดที่ไม่เกี่ยวข้อง

สรุปแนวทางปฏิบัติที่ดีที่สุด

• เลือกเครื่องมือที่เหมาะสม: อย่าใช้ฟังก์ชันที่คุ้นเคยเป็นค่าเริ่มต้นเมื่อมีทางเลือกอื่นที่ดีกว่า
• ระบุช่วงให้เฉพาะเจาะจง: หลีกเลี่ยงการอ้างอิงทั้งคอลัมน์เว้นแต่จำเป็น
• คิดถึงการบำรุงรักษา: เขียนสูตรที่ผู้อื่น (รวมถึงอนาคตของคุณด้วย) สามารถเข้าใจได้
• ใช้ประเภทข้อมูลที่เหมาะสม: ถือว่าวันที่เป็นวันที่ ตัวเลขเป็นตัวเลข
• กรณีขอบทดสอบ: พิจารณาสิ่งที่เกิดขึ้นกับเซลล์ว่าง ข้อผิดพลาด หรือข้อมูลที่ไม่คาดคิด
• ใช้ประโยชน์จากตาราง Excel: ใช้การอ้างอิงที่มีโครงสร้างสำหรับช่วงไดนามิก
• อยู่ในปัจจุบัน: เรียนรู้ฟังก์ชันใหม่ๆ ใน Excel 365 ที่สามารถแทนที่สูตรเดิมที่ซับซ้อนได้

บทสรุป

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