ฟังก์ชัน 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 อยู่ในที่ที่มองเห็นได้ง่ายและดูได้ง่าย

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

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

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

ในตอนนี้ มีฟังก์ชัน IF ที่สามารถแทนที่คำสั่ง 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 Insider

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

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

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

×