ฟังก์ชัน IF คือสูตรที่ซ้อนกันและการหลีกเลี่ยงข้อผิดพลาด

ฟังก์ชัน IF คือสูตรที่ซ้อนกันและการหลีกเลี่ยงข้อผิดพลาด

ฟังก์ชัน IF ช่วยให้คุณสามารถทำการเปรียบเทียบตรรกะระหว่างค่าและสิ่งที่คุณคาดหวังไว้ โดยการทดสอบเงื่อนไข และส่งกลับผลลัพธ์ถ้าเป็น True หรือ False

  • =IF(ถ้ามีบางอย่างเป็น True ให้ดำเนินการอย่างหนึ่ง ถ้าไม่มี ให้ดำเนินการอีกอย่างหนึ่ง)

ดังนั้นข้อความ IF สามารถให้ผลลัพธ์ได้ 2 แบบ ผลลัพธ์แรกคือ ถ้าการเปรียบเทียบของคุณเป็นจริง ผลลัพธ์ที่สองจะเป็นเท็จ

คำสั่ง IF มีประสิทธิภาพอย่างมาก และสร้างพื้นฐานของรูปแบบสเปรดชีตจำนวนมาก แต่ก็ยังเป็นสาเหตุของปัญหาสเปรดชีตมากมาย โดยปกติ คำสั่ง IF ควรนำไปใช้กับเงื่อนไขที่น้อยที่สุด เช่น ชาย/หญิง ใช่/ไม่ใช่/ไม่แน่ แต่บางครั้ง คุณอาจต้องประเมินสถานการณ์ที่ซับซ้อนกว่าที่ต้องการใช้การซ้อนทับ* มากกว่าฟังก์ชัน IF 3 ฟังก์ชันรวมกัน

* “การซ้อนทับ” หมายถึงหลักปฏิบัติของฟังก์ชันหลายฟังก์ชันรวมกันในสูตรเดียว

ใช้ฟังก์ชัน IF ซึ่งเป็นหนึ่งใน ฟังก์ชันทางตรรกะ เพื่อส่งกลับหนึ่งค่าถ้าเงื่อนไขเป็น จริง และอีกค่าหนึ่งถ้าเงื่อนไขเป็น เท็จ

ไวยากรณ์

IF(logical_test, value_if_true, [value_if_false])

ตัวอย่างเช่น

  • =IF(A2>B2,"เกินงบ","ตกลง")

  • =IF(A2=B2,B4-A4,"")

ชื่ออาร์กิวเมนต์

คำอธิบาย

logical_test   

(จำเป็น)

เงื่อนไขที่คุณต้องการทดสอบ

Value_if_true   

(จำเป็น)

ค่าที่คุณต้องการให้ส่งกลับถ้าผลลัพธ์ของ logical_test เป็น TRUE

value_if_false   

(ไม่จำเป็น)

ค่าที่คุณต้องการให้ส่งกลับถ้าผลลัพธ์ของ logical_test เป็น FALSE

ข้อสังเกต

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

  • คำสั่ง IF ต้องใช้ความคิดที่รอบคอบเป็นอย่างมากเพื่อสร้างฟังก์ชันเหล่านั้นให้ถูกต้อง และตรวจสอบให้แน่ใจว่าตรรกะเหล่านั้นสามารถคำนวณตามเงื่อนไขแต่ละข้อได้ถูกต้องทุกข้อไปจนถึงข้อสุดท้าย ถ้าคุณไม่ซ้อนสูตร ให้มีความแม่นยำ 100% สูตรอาจมีโอกาสคำนวณสำเร็จ 75% แต่มีโอกาสส่งผลลัพธ์ที่ไม่ต้องการ 25% บางครั้งคุณอาจไม่อยู่ในกลุ่ม 25% นี้

  • คำสั่ง IF หลายรายการอาจทำให้การคำนวณเป็นเรื่องยากอย่างคาดไม่ถึง โดยเฉพาะเมื่อคุณย้อนกลับมาดูในภายหลังและพยายามทำความเข้าใจสิ่งที่คุณ หรืออาจเป็นคนอื่นพยายามทำ

ถ้าคุณพบคำสั่ง IF ที่ดูเหมือนไม่มีจุดสิ้นสุด คุณควรปล่อยเมาส์แล้ววางแผนกลยุทธ์ของคุณใหม่

มาดูวิธีการสร้างคำสั่ง IF แบบซ้อนทับที่ซับซ้อนโดยใช้คำสั่ง IF หลายคำสั่ง และเวลาที่ควรใช้เครื่องมืออื่นใน Excel ของคุณ

ตัวอย่าง

ต่อไปนี้คือตัวอย่างของคำสั่ง IF แบบซ้อนทับมาตรฐานเพื่อแปลงคะแนนสอบของนักเรียนให้เป็นเกรดที่เป็นตัวอักษร

คำสั่ง IF แบบซ้อนทับที่ซับซ้อน - สูตรใน E2 คือ =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-",IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))
  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

    คำสั่ง IF ที่ซ้อนกันอย่างซับซ้อนนี้ทำตามหลักตรรกะอย่างตรงไปตรงมา:

  1. ถ้าคะแนนสอบ (ในเซลล์ D2) มากกว่า 89 นักเรียนจะได้ A

  2. ถ้าคะแนนสอบมากกว่า 79 นักเรียนจะได้ B

  3. ถ้าคะแนนสอบมากกว่า 69 นักเรียนจะได้ C

  4. ถ้าคะแนนสอบมากกว่า 59 นักเรียนจะได้ D

  5. มิฉะนั้น นักเรียนจะได้ F

ตัวอย่างนี้ค่อนข้างปลอดภัยเนื่องจากความสัมพันธ์ระหว่างคะแนนสอบและเกรดที่เป็นตัวอักษรอาจจะไม่เปลี่ยนแปลง ดังนั้น จึงไม่ต้องการการจัดการมาก แต่ ถ้าคุณต้องการแบ่งส่วนเกรดระหว่าง A+, A และ A- (และอื่นๆ) คำสั่ง IF สี่เงื่อนไขของคุณจะต้องถูกเขียนใหม่ให้มี 12 เงื่อนไข! ในตอนนี้ สูตรของคุณจะมีลักษณะดังนี้:

  • =IF(B2>97,"A+",IF(B2>93,"A",IF(B2>89,"A-",IF(B2>87,"B+",IF(B2>83,"B",IF(B2>79,"B-", IF(B2>77,"C+",IF(B2>73,"C",IF(B2>69,"C-",IF(B2>57,"D+",IF(B2>53,"D",IF(B2>49,"D-","F"))))))))))))

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

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

ตัวอย่างเพิ่มเติม

ต่อไปนี้คือตัวอย่างทั่วไปของการคำนวณค่าคอมมิชชั่นยอดขายโดยยึดตามระดับความสำเร็จของรายได้

สูตรในเซลล์ D9 คือ IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))
  • =IF(C9>15000,20%,IF(C9>12500,17.5%,IF(C9>10000,15%,IF(C9>7500,12.5%,IF(C9>5000,10%,0)))))

สูตรนี้บอกว่า IF(C9 มากกว่า 15,000 จะส่งกลับ 20%, IF(C9 มากกว่า 12,500 จะส่งกลับ 17.5% และอื่นๆ...

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

เคล็ดลับ: คุณสามารถแทรกตัวแบ่งบรรทัดในแถบสูตรเพื่อทำให้อ่านสูตรที่ยาวได้ง่ายขึ้น เพียงกด ALT+ENTER ก่อนข้อความที่คุณต้องการตัดไปยังบรรทัดใหม่

นี่คือตัวอย่างของสถานการณ์ค่าคอมมิชชั่นที่มีการหยุดใช้งาน:

สูตรใน D9 ที่หยุดใช้งานคือ =IF(C9>5000,10%,IF(C9>7500,12.5%,IF(C9>10000,15%,IF(C9>12500,17.5%,IF(C9>15000,20%,0)))))

คุณเห็นสิ่งผิดปกติหรือไม่ เปรียบเทียบลำดับของการเปรียบเทียบรายได้กับตัวอย่างก่อนหน้า สูตรนี้ทำงานอย่างไร ใช่ จากล่างขึ้นบน ($5,000 ถึง $15,000) ไม่ใช่จากบนลงล่าง แต่ทำไมสิ่งนี้จึงเป็นเรื่องใหญ่ เนื่องจากสูตรไม่สามารถผ่านการคำนวณครั้งแรกสำหรับค่าใดก็ตามที่สูงกว่า $5,000 สมมติว่าคุณมีรายได้ $12,500 คำสั่ง IF จะส่งกลับ 10% เนื่องจากมากกว่า $5,000 และจะหยุดอยู่เท่านั้น สิ่งนี้อาจเป็นปัญหาอย่างมาก เนื่องจากมีหลายกรณีที่ตรวจไม่พบข้อผิดพลาดชนิดนี้จนกว่าจะเกิดผลกระทบเชิงลบ ดังนั้น เมื่อทราบว่ามีผลเสียเกี่ยวกับคำสั่ง IF แบบซ้อนทับที่ซับซ้อน คุณจะทำอย่างไรได้บ้าง ในกรณีส่วนใหญ่ คุณสามารถใช้ฟังก์ชัน VLOOKUP แทนที่จะสร้างสูตรที่ซับซ้อนด้วยฟังก์ชัน IF ในการใช้ VLOOKUP ก่อนอื่น คุณต้องสร้างตารางอ้างอิง:

สูตรในเซลล์ D2 คือ =VLOOKUP(C2,C5:D17,2,TRUE)
  • =VLOOKUP(C2,C5:D17,2,TRUE)

สูตรนี้ระบุให้ค้นหาค่าใน C2 ในช่วง C5:C17 ถ้าพบค่า จะส่งกลับค่าที่สอดคล้องกันจากแถวเดียวกันในคอลัมน์ D

สูตรในเซลล์ C9 คือ =VLOOKUP(B9,B2:C6,2,TRUE)
  • =VLOOKUP(B9,B2:C6,2,TRUE)

ในทำนองเดียวกัน สูตรนี้จะค้นหาค่าในเซลล์ B9 ในช่วง B2:B22 ถ้าพบค่า จะส่งกลับค่าที่สอดคล้องกันจากแถวเดียวกันในคอลัมน์ C

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

มีบทความที่กล่าวถึง VLOOKUP อย่างละเอียดมากขึ้นที่นี่ แต่นี่ก็ง่ายกว่าคำสั่ง IF แบบซ้อนทับ 12 ระดับที่ซับซ้อนมาก! และยังมีประโยชน์อื่นๆ อีกด้วย:

  • ตารางอ้างอิง VLOOKUP อยู่ในที่ที่มองเห็นได้ง่ายและดูได้ง่าย

  • ค่าในตารางจะถูกอัปเดตได้อย่างง่ายดายและคุณไม่จำเป็นต้องยุ่งกับสูตรอีกเลย ถ้าเงื่อนไขของคุณไม่เปลี่ยนแปลง

  • ถ้าคุณไม่ต้องการให้ผู้อื่นเห็นหรือยุ่งกับตารางอ้างอิงของคุณ ให้ใส่บนเวิร์กชีตอื่น

คุณทราบหรือไม่

ในตอนนี้ มีฟังก์ชัน IFS ที่สามารถแทนที่คำสั่ง IF แบบซ้อนทับหลายคำสั่งด้วยฟังก์ชันเดียว ดังนั้น แทนที่จะใช้ตัวอย่างเกรดของเราที่มีฟังก์ชัน IF แบบซ้อนทับ 4 ฟังก์ชัน:

  • =IF(D2>89,"A",IF(D2>79,"B",IF(D2>69,"C",IF(D2>59,"D","F"))))

ซึ่งทำได้ง่ายด้วยฟังก์ชัน IFS เพียงฟังก์ชันเดียว

  • =IFS(D2>89,"A",D2>79,"B",D2>69,"C",D2>59,"D",TRUE,"F")

ฟังก์ชัน IFS มีประโยชน์มากเนื่องจากคุณไม่ต้องกังวลเกี่ยวกับคำสั่ง IF และวงเล็บเหล่านั้นทั้งหมด

หมายเหตุ: ฟีเจอร์นี้จะพร้อมใช้งานถ้าคุณมี การสมัครใช้งาน Office 365 เท่านั้น ถ้าคุณเป็นผู้สมัครใช้งาน Office 365 ให้ตรวจสอบให้แน่ใจว่าคุณมี Office เวอร์ชันล่าสุด

ทดลองใช้ Office 365 หรือ Excel เวอร์ชันล่าสุด

ต้องการความช่วยเหลือเพิ่มเติมไหม

คุณสามารถสอบถามผู้เชี่ยวชาญใน Excel Tech Community ขอความช่วยเหลือใน Answers community หรือแนะนำฟีเจอร์ใหม่หรือการปรับปรุงบน Excel User Voiceได้เสมอ

หัวข้อที่เกี่ยวข้อง

วิดีโอ: ฟังก์ชัน IF ขั้นสูง
ฟังก์ชัน IFS (Office 365, Excel 2016 และเวอร์ชันที่ใหม่กว่า)
ฟังก์ชัน COUNTIF จะนับค่าตามเกณฑ์ข้อเดียว
ฟังก์ชัน COUNTIFS จะนับค่าตามเกณฑ์หลายข้อ
ฟังก์ชัน SUMIF จะรวมค่าตามเกณฑ์ข้อเดียว
ฟังก์ชัน SUMIFS จะรวมค่าตามเกณฑ์หลายข้อ
ฟังก์ชัน AND
ฟังก์ชัน OR
ฟังก์ชัน VLOOKUP
ภาพรวมของสูตรใน Excel
วิธีการหลีกเลี่ยงสูตรที่ใช้งานไม่ได้
ใช้การตรวจสอบข้อผิดพลาดเพื่อตรวจหาข้อผิดพลาดในสูตร
ฟังก์ชันตรรกะ
ฟังก์ชัน Excel (ตามลำดับตัวอักษร)
ฟังก์ชัน Excel (ตามประเภท)

ขยายทักษะ Office ของคุณ
สำรวจการฝึกอบรม
รับฟีเจอร์ใหม่ก่อนใคร
เข้าร่วม Office Insider

ข้อมูลนี้เป็นประโยชน์หรือไม่

ขอบคุณสำหรับคำติชมของคุณ!

ขอขอบคุณสำหรับคำติชมของคุณ! เราคิดว่าอาจเป็นประโยชน์ที่จะให้คุณได้ติดต่อกับหนึ่งในตัวแทนฝ่ายสนับสนุน Office ของเรา

×