วิธีสร้างสูตรช่วยการตัดสินใจ

รูปแบบสูตร IF

=IF( เงื่อนไข,   ค่ากรณีที่เงื่อนไขถูกต้อง,   ค่ากรณีเงื่อนไขไม่ถูกต้อง)

เงื่อนไข เช่น ทดสอบว่า เซลล์นั้นเท่ากับเซลล์นี้หรือไม่ เซลล์โน้นเท่ากับค่านี้หรือไม่ จะใส่สูตรคำนวณเพื่อใช้เป็นเงื่อนไขก็ได้ โดยผลลัพธ์ของเงื่อนไข จะคืนค่าออกมาเป็น TRUE หรือ FALSE อย่างใดอย่างหนึ่งเท่านั้น

เครื่องหมายในส่วนของเงื่อนไข เช่น
=
>
<
>=
<=
<> หมายถึง ไม่เท่ากับ

นอกจากนี้ เงื่อนไขยังใช้สูตรประเภททดสอบ เช่น สูตรกลุ่ม IS ต่างๆ นำมาตรวจสอบให้ได้ค่า TRUE หรือ FALSE 

ตัวอย่างสูตร

=IF(SaleChoice="n", 1000, 2000)

ถ้าเซลล์ชื่อ SaleChoice มีค่าเท่ากับตัวอักษร n ให้คืนค่าสูตรนี้เป็นตัวเลข 1000
แต่ถ้าไม่ได้มีค่าเป็นตัวอักษร n ให้คืนค่าสูตรนี้เป็นตัวเลข 2000

สังเกตว่า คำว่า SaleChoice ไม่อยู่ในเครื่องหมายคำพูด เนื่องจากเป็น Range Name ส่วนคำว่า n ต้องอยู่ในเครื่องหมายคำพูด เนื่องจากเป็น text (หากไม่ใส่ในเครื่องหมายคำพูด Excel จะพยายามหาชื่อที่ชื่อว่า n แต่หาไม่พบ ทำให้สูตร error)

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

=IF(MyMargin>0, MyMargin*0.3, 0)

ถ้าเซลล์ชื่อ MyMargin มีค่ามากกว่า 0 ให้คืนค่าเป็นผลคำนวณเท่ากับ MyMargin*0.3
มิฉะนั้นให้คืนค่าเท่ากับ 0 สูตรนี้ใช้สำหรับคำนวณภาษีต่อเมื่อมีกำไร

=IF(ตัวหาร=0, 0, ตัวตั้ง/ตัวหาร)

ถ้าเซลล์ชื่อ ตัวหาร มีค่าเท่ากับ 0 ให้คืนค่า 0 มิฉะนั้นให้คำนวณอัตราส่วนของ ตัวตั้ง/ตัวหาร สูตรนี้ใช้สำหรับเซลล์คำนวณด้วยสูตรหาร ป้องกันไม่ให้สูตรหารคืนค่าเป็น #DIV/0! Error เมื่อตัวหารมีค่าเท่ากับ 0
ให้หลีกเลี่ยงการใช้ Null Text "" แทนเลข 0 เนื่องจาก Null Text ถือเป็นตัวอักษร ไม่สามารถนำค่าไปคำนวณต่อ

หากไม่ต้องการให้แสดงเลข 0 ให้ใช้ Format แบบ 0;; ช่วยแทน จะทำให้แสดงเป็นเซลล์ว่าง เมื่อผลลัพธ์มีค่าลบหรือ 0

=IF(ISERROR(สูตรคำนวณ),  0,  สูตรคำนวณ)

ถ้าสูตรคำนวณไม่ถูกต้อง ไม่ยอมให้คืนค่าเป็น Error ให้ใช้ค่า 0 แทน แต่ถ้าไม่ Error จึงจะคำนวณตามปกติ

หากต้องการปรับค่าในเซลล์ซึ่งคำนวณแล้วเกิด error ให้แสดงด้วยช่องว่างแทน ขอให้ใช้เลข 0 แทนจะดีกว่าการใช้ Null Text โดยใช้เครื่องหมาย "" แทน เพราะหากใช้ "" ย่อมถือว่าเป็นตัวอักษร ซึ่งไม่สามารถนำค่า "" ไปคำนวณบวกลบคูณหารต่อได้อีก ทำให้ต้องใช้สูตร IsError ป้องกันในสูตรต่อๆไปโดยไม่จำเป็น

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

=IF($K15>=$D$17, $F$17, IF($K15>=$D$16, $F$16, IF($K15>=$D$15, $F$15, $F$14)))

เกิดจากการนำสูตร IF มาซ้อนกัน เพื่อช่วยตัดสินคะแนนเป็นขั้นๆ ดังนี้
=IF($K15>=$D$17, "A", "BCD")
=IF($K15>=$D$17, "A", IF($K15>=$D$16, "B", "CD"))
=IF($K15>=$D$17, "A", IF($K15>=$D$16, "B", IF($K15>=$D$15, "C", "D")))

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

นอกจากนี้ ยังใช้วิธีตั้งชื่อสูตร แล้วนำชื่อสูตรมาซ้อนในอีกชื่อหนึ่งก็ได้

ตัวอย่าง นำสูตร 7 ชั้น มาบวกกับ สูตร 7 ชั้น

สมมติว่า K6:AO6 มียอดเบิกสินค้าในแต่ละวัน เซลล์ G6 มียอดสินค้าคงคลัง ต้องการหาว่า จากการเบิกสินค้าไปเรื่อยๆนี้ จะทำให้สินค้าขาดมือในวันใด

=IF(K6>G6, 1, IF(SUM(K6:L6)>G6, 2, IF(SUM(K6:M6)>G6, 3, IF(SUM(K6:N6)>G6, 4, IF(SUM(K6:O6)>G6, 5, IF(SUM(K6:P6)>G6, 6, IF(SUM(K6:Q6)>G6, 7, 0)))))))
+IF(SUM(K6:Q6)>G6, 0, IF(SUM(K6:R6)>G6, 8, IF(SUM(K6:S6)>G6, 9, IF(SUM(K6:T6)>G6, 10, IF(SUM(K6:U6)>G6, 11, IF(SUM(K6:V6)>G6, 12, IF(SUM(K6:W6)>G6, 13, 0)))))))
+IF(SUM(K6:W6)>G6, 0, IF(SUM(K6:X6)>G6, 14, IF(SUM(K6:Y6)>G6, 15, IF(SUM(K6:Z6)>G6, 16, IF(SUM(K6:AA6)>G6, 17, IF(SUM(K6:AB6)>G6, 18, IF(SUM(K6:AC6)>G6, 19, 0)))))))
+IF(SUM(K6:AC6)>G6, 0, IF(SUM(K6:AD6)>G6, 20, IF(SUM(K6:AE6)>G6, 21, IF(SUM(K6:AF6)>G6, 22, IF(SUM(K6:AG6)>G6, 23, IF(SUM(K6:AH6)>G6, 24, IF(SUM(K6:AI6)>G6, 25, 0)))))))
+IF(SUM(K6:AI6)>G6, 0, IF(SUM(K6:AJ6)>G6, 26, IF(SUM(K6:AK6)>G6, 27, IF(SUM(K6:AL6)>G6, 28, IF(SUM(K6:AM6)>G6, 29, IF(SUM(K6:AN6)>G6, 30, IF(SUM(K6:AO6)>G6, 31, 0)))))))

ตัวอย่าง นำสูตร 7 ชั้น มาต่อร่วมกับ สูตร 7 ชั้น โดยใช้เครื่องหมาย & เชื่อมสูตร

=IF(K6>G6, 1, IF(SUM(K6:L6)>G6, 2, IF(SUM(K6:M6)>G6, 3, IF(SUM(K6:N6)>G6, 4, IF(SUM(K6:O6)>G6, 5, IF(SUM(K6:P6)>G6, 6, IF(SUM(K6:Q6)>G6, 7, "")))))))
&IF(SUM(K6:Q6)>G6, "", IF(SUM(K6:R6)>G6, 8, IF(SUM(K6:S6)>G6, 9, IF(SUM(K6:T6)>G6, 10, IF(SUM(K6:U6)>G6, 11, IF(SUM(K6:V6)>G6, 12, IF(SUM(K6:W6)>G6, 13, "")))))))
&IF(SUM(K6:W6)>G6, "", IF(SUM(K6:X6)>G6, 14, IF(SUM(K6:Y6)>G6, 15, IF(SUM(K6:Z6)>G6, 16, IF(SUM(K6:AA6)>G6, 17, IF(SUM(K6:AB6)>G6, 18, IF(SUM(K6:AC6)>G6, 19, "")))))))
&IF(SUM(K6:AC6)>G6, "", IF(SUM(K6:AD6)>G6, 20, IF(SUM(K6:AE6)>G6, 21, IF(SUM(K6:AF6)>G6, 22, IF(SUM(K6:AG6)>G6, 23, IF(SUM(K6:AH6)>G6, 24, IF(SUM(K6:AI6)>G6, 25, "")))))))
&IF(SUM(K6:AI6)>G6, "", IF(SUM(K6:AJ6)>G6, 26, IF(SUM(K6:AK6)>G6, 27, IF(SUM(K6:AL6)>G6, 28, IF(SUM(K6:AM6)>G6, 29, IF(SUM(K6:AN6)>G6, 30, IF(SUM(K6:AO6)>G6, 31, "")))))))

นอกจากวิธีข้างต้นแล้ว ยังใช้วิธีตั้งชื่อสูตรเป็น Formula Name แล้วนำสูตรมาซ้อนสูตร เป็นชื่อสูตรใหม่

วิธีใช้สูตร And และสูตร Or

=AND( เงื่อนไขที่ 1, เงื่อนไขที่ 2, เงื่อนไขที่ 3, .....30)

สูตร And จะคืนค่าเป็น False หากเงื่อนไขใดเงื่อนไขหนึ่งเป็น False
สูตร And จะคืนค่าเป็น True ต่อเมื่อทุกเงื่อนไขเป็น True ทั้งหมด

=OR( เงื่อนไขที่ 1, เงื่อนไขที่ 2, เงื่อนไขที่ 3,.....30)

สูตร Or จะคืนค่าเป็น True หากเงื่อนไขใดเงื่อนไขหนึ่งเป็น True
สูตร Or จะคืนค่าเป็น False ต่อเมื่อทุกเงื่อนไขเป็น False ทั้งหมด

เคล็ดใช้จำ

• จำว่า And แปลว่า และ เสียงออก แอ๊ะๆๆๆๆ คือ เอ็ฟ F False เป็นลักษณะเด่น
• จำว่า Or แปลว่า หรือออออ เสียงออกยาวๆ คือ True เป็นลักษณะเด่น
• ให้ดูแค่ลักษณะเด่น ในสูตรทั้งสองนี้ หากเจอลักษณะเด่นใด ก็คืนค่าตามลักษณะเด่นนั้นๆ
• สูตรที่เหี้ยม ไม่ยอมง่ายๆ ก็คือสูตร And ใช้กับเงื่อนไขที่ต้องคุมทุกเงื่อนไข จึงจะใช้ได้ ดังนั้นน่าใช้สูตร And สำหรับควบคุมให้ใช้ค่าถูกต้อง ทุกค่า ห้ามผิดแม้แต่ค่าเดียว
• สูตรใจดี ยอมง่ายๆ หยวนๆ ก็คือสูตร Or ใช้กับเงื่อนไขแบบเงื่อนไขใดเงื่อนไขหนึ่ง ก็ใช้ได้แล้ว