สูตร Array กับการตัดสินใจ

สินค้ารหัส a001 มีอยู่ 2 รายการ มียอดขายรวม 400 บาท ซึ่งเกิดจากยอด 100 + 300

ประโยคคำตอบข้างต้นนี้เป็นคำตอบที่คิดตามแบบของมนุษย์ ซึ่ง Excel ก็ใช้วิธีคิดคล้ายคลึงกัน โดยแบ่งลำดับการคำนวณออกทีละขั้นดังนี้

1. จับ Range ชื่อ Id มาเทียบหาตำแหน่งที่มีค่าเท่ากับ IdChoice โดยใช้สูตร Id=IdChoice จะทำให้ Excel เปรียบเทียบค่าทีละตำแหน่ง ตำแหน่งซึ่งมีค่าเท่ากับ a001 จะกลายเป็น True ส่วนตำแหน่งที่ไม่ตรงจะกลายเป็น False
2. ผลจากการตรวจสอบตำแหน่ง คืนค่าออกมาเป็น
   True;False;True;False;False
3. เมื่อนำ Range ชื่อ Amount เข้ามาเทียบกับตำแหน่ง True/False ที่ได้ในขั้นก่อน Excel จะปรับค่าของ Amount ใหม่ให้คงตัวเลขไว้เฉพาะตำแหน่งที่เป็น True เท่านั้น
4. Amount จะมีตัวเลขเหลือเฉพาะตำแหน่งที่ต้องการเป็น
   100;False;300;False;False
5. เมื่อนำยอดที่ได้มาหายอดรวมด้วยสูตร Sum จะได้ผลลัพธ์ 400

โจทย์ข้างต้นสามารถใช้สูตร Excel คำนวณหายอดรวม 400 ได้หลายวิธี

สูตรที่ 1 : ใช้สูตร SumIF
=SumIF( Id, IdChoice, Amount )

สูตรที่ 2 : ใช้สูตร Sum If Array
{=Sum( If( Id=IdChoice, Amount ) )}

สูตรที่ 3 : ใช้สูตร Sum Array
{=Sum( ( Id=IdChoice ) * Amount )}

สูตรที่ 4 : ใช้สูตร SumProduct
=SumProduct( ( Id=IdChoice ) * Amount )  

หลักพื้นฐานซึ่งทั้ง 4 สูตรนี้ใช้คำนวณ เกิดจากการนำ Range ซึ่งต้องการตรวจสอบตำแหน่ง มาตรวจสอบกับค่าที่ต้องการว่าอยู่ที่ตำแหน่งใด จากนั้นนำ Range ของตัวเลขที่ต้องการหายอดรวม ซึ่งต้องมีขนาดตารางเท่ากันเข้ามาเทียบ (Range Id ต้องมีขนาดและจำนวนเซลล์เท่ากับ Range Amount) จะทำให้ตำแหน่งที่เป็น True คงตัวเลขเดิมไว้ ส่วนที่เป็น False จะถูกทำลายทิ้งกลายเป็น False ซึ่งเมื่อหายอดรวม จะได้ยอดตัวเลขรวมเฉพาะจากตำแหน่งที่เป็น True เท่านั้น

สูตรที่ 1 : สูตร SumIF
=SumIF( Id, IdChoice, Amount )

ข้อดี

1. สูตรนี้เป็นสูตรที่ Microsoft สร้างขึ้นเพื่อใช้งานแบบ Array โดยตรง ไม่จำเป็นต้องกด Ctrl+Shift+Enter ในตอนสร้างสูตร และคำนวณเร็วกว่าสูตร Array ที่เราต้องสร้างขึ้นเอง
2. Id กับ Amount มีขนาดไม่จำกัด สามารถใช้พื้นที่ทั้ง Column ซึ่งครอบคลุมทุก Row ที่มีอยู่ในตาราง
3. IdChoice สามารถใช้ Wild Card แทนในการค้นหารหัสที่ทราบแต่เพียงบางส่วน เช่น รหัส a001 อาจใช้ Wild Card "*1" หรือ "a??1" เพื่อค้นหาตำแหน่งได้เช่นกัน

ข้อเสีย

1. คำนวณช้ากว่าสูตร Lookup ซึ่งใช้ กับการหาค่าซึ่งมีเพียงรายการเดียว (ไม่มีรายการซ้ำ)
2. ไม่สามารถซ้อนสูตร If หรือสูตรอื่นเข้าไปเพื่อใช้ตัดสินใจหลายๆเงื่อนไข
3. ไม่สามารถใช้วิธีลากบนสูตรแล้วกดปุ่ม F9 เพื่อแกะดูบนสูตร
4. ไม่สามารถคำนวณให้ผลลัพธ์ถ้าแฟ้มต้นทางไม่ได้เปิดด้วย

สูตรที่ 2 : สูตร Sum If Array
{=Sum( If( Id=IdChoice, Amount ) )}

ข้อดี

1. สามารถซ้อนสูตร If เพื่อตัดสินใจหลายเงื่อนไข และสามารถใช้ Range ตัวเลขที่ต้องการหายอดรวมในกรณีที่เป็น False ได้ด้วย
2. สามารถเปลี่ยนสูตรจาก Sum ด้านหน้าสุดเป็นสูตรอื่นได้อิสระ เช่น
   {=Count( If( Id=IdChoice, Amount ) )}
   {=Average( If( Id=IdChoice, Amount ) )}
   {=Max( If( Id=IdChoice, Amount ) )}
   {=Min( If( Id=IdChoice, Amount ) )}
3. สามารถใช้วิธีลากบนสูตรแล้วกดปุ่ม F9 เพื่อแกะดูบนสูตร

ข้อเสีย

1. ต้องกด Ctrl+Shift+Enter ในตอนสร้างสูตร และคำนวณช้ากว่าสูตร SumIF
2. ไม่สามารถใช้พื้นที่ทั้ง Column ซึ่งครอบคลุมทุก Row ที่มีอยู่ในตาราง ต้องลดขนาดลงมาอย่างน้อย 1 Row
3. ไม่รองรับกับการใช้ Wild Card

สูตรที่ 3 : สูตร Sum Array
{=Sum( ( Id=IdChoice ) * Amount )}

สูตรนี้ใช้หลักการเพิ่มเติมว่า เมื่อนำค่า True/False ที่ได้จากการตรวจสอบตำแหน่งมากระตุ้นด้วยการคำนวณต่อ จะทำให้ True = 1 และ False = 0 จึงทำให้ True;False;True;False;False กลายเป็นลำดับของตัวเลข 1;0;1;0;0 แล้วเมื่อนำมาคูณกับลำดับตัวเลขใน Amount จะได้ผลลัพธ์เป็น 100;0;300;0;0 ซึ่งนำมาหายอดรวมเท่ากับ 400 นั่นเอง

ข้อดี

1. ไม่จำเป็นต้องใช้สูตร If ซึ่งมีข้อจำกัดว่าสามารถซ้อน If ได้อีกแค่ 7 สูตรเท่านั้น
2. ในกรณีที่ต้องการหายอดรวมของรหัสหลายๆตัว ให้นำตำแหน่ง True/False ที่ได้มาบวกกันก่อน จากนั้นจึงนำลำดับตัวเลข 1,0 ที่ได้ไปคูณกับ Amount เช่น สูตรหายอดรวมของ a001 กับ a002
   {=Sum( (( Id="a001")+(Id="a002")) * Amount )}
   หากแกะสูตร (( Id="a001")+(Id="a002")) จะได้ผลลัพธ์เป็น 1;1;1;0;0 ซึ่งเกิดจาก True;False;True;False;False บวกกับ False;True;False;False;False
3. ในกรณีที่ต้องการหายอดรวมของรหัสร่วมกับชื่อลูกค้า ให้นำตำแหน่ง True/False ที่ได้มาคูณกัน จากนั้นจึงนำลำดับตัวเลข 1,0 ที่ได้ไปคูณกับ Amount เช่น สูตรหายอดรวมของ a001 ที่ลูกค้าชื่อ c
   {=Sum( ( Id="a001")*(Name="c") * Amount )}
   หากแกะสูตร ( Id="a001")*(Name="c") จะได้ผลลัพธ์เป็น 0;0;1;0;0 ซึ่งเกิดจาก True;False;True;False;False คูณกับ False;False;True;False;False

ข้อเสีย

1. ต้องกด Ctrl+Shift+Enter ในตอนสร้างสูตร และคำนวณช้ากว่าสูตร SumIF
2. ไม่สามารถใช้พื้นที่ทั้ง Column ซึ่งครอบคลุมทุก Row ที่มีอยู่ในตาราง ต้องลดขนาดลงมาอย่างน้อย 1 Row
3. ไม่รองรับกับการใช้ Wild Card

สูตรที่ 4 : สูตร SumProduct
=SumProduct( ( Id=IdChoice ) * Amount ) หรือ

=SumProduct( --( Id=IdChoice ) , Amount )

สูตรนี้ใช้โครงสร้างภายในวงเล็บเหมือนกับสูตรแบบที่ 3 แต่ให้เปลี่ยนคำว่า Sum ด้านหน้าวงเล็บเป็น Sumproduct แทน

สูตร SumProduct ทำหน้าที่นำ array แต่ละชุดที่เราใส่ไว้ในวงเล็บแต่ละส่วน นำตำแหน่งค่าที่ตรงกันมาคูณกัน จากนั้นจึงนำผลคูณที่ได้ทั้งหมดมาบวกรวมเป็นคำตอบ

 

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

 

หากใช้สูตรแบบ =SumProduct( ( Id=IdChoice ) , Amount ) จะไม่ทำงาน เพราะส่วนของการเปรียบเทียบตำแหน่งของค่าใน ( Id=IdChoice ) ยังคงเป็นแค่ True หรือ False อยู่เท่านั้น

 

เราสามารถกระตุ้นให้ True=1, False=0 ได้โดยใส่เครื่องหมาย -- ไว้ข้างหน้าวงเล็บ หรือจะนำไป *1, /1, +0, -0 ได้เหมือนกัน เพราะทำให้มีค่าเท่าเดิม ในโครงสร้างหลากหลายแบบดังนี้

 

=SumProduct( --( Id=IdChoice ) , Amount )

=SumProduct( ( Id=IdChoice )*1 , Amount )

=SumProduct( ( Id=IdChoice )/1 , Amount )

=SumProduct( ( Id=IdChoice )+0 , Amount )

=SumProduct( ( Id=IdChoice )-0 , Amount ) 

ข้อดี

1. ไม่จำเป็นต้องกดปุ่ม Ctrl+Shift+Enter
2. แฟ้มมีขนาดเล็กกว่าสูตรแบบที่ 2 และ 3

ข้อเสีย

1. ต้องพิมพ์ชื่อสูตรยาวขึ้นเล็กน้อย
2. คำนวณช้ากว่าสูตรแบบที่ 1 และ 2
3. ในบางกรณีไม่สามารถคำนวณได้ถ้าไม่ได้สร้างโดยกดปุ่ม Ctrl+Shift+Enter
4. ถ้าใช้เครื่องหมาย , คั่นแต่ละเงื่อนไขในวงเล็บ จะไม่สามารถแกะสูตรว่าคิดได้อย่างไรโดยการลากบนสูตรแล้วกดปุ่ม F9