ตอนนี้เป็นตอนต่อจาก The Mask Function : หน้ากากฟังก์ชัน ! มาทายกันนี่คือฟังก์ชันอะไร?? ซึ่งฟังก์ชันทั้ง 4 ตัวได้โชว์ฝีมือในการแข่งขัน แข่ง Lookup ข้อมูลแบบ Multiple Criteria ไปแล้ว ทุกตัวต่างหาผลลัพธ์ได้ถูกต้องคือ ราคา 60 บาทถูกต้องเหมือนกันหมด!

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

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

ส่วนฟังก์ชันอีกอันที่จะเข้ารอบไป ผมขอเลือกเป็นตัวแทนสาย D แล้วกันครับ เนื่องจากความยุ่งยากในการเขียนสูตรที่ยาวมากๆ เรียกได้ว่าเป็นขั้วตรงข้ามกับตัวแทนสาย C โดยสิ้นเชิงเลย

ใบ้เพิ่มเติม

ทั้งตัวแทนสาย C และ D ที่เข้ารอบไปแล้วฝากบอกมาว่า ถึงค่าที่จะ Lookup จะไม่ใช่ตัวเลข ตัวเองก็ไม่กลัวเลย สามารถทำงานได้ สบายหายห่วง!
ต่างจากตัวแทนกลุ่ม A และ B ที่หากจ้อง Lookup ค่าที่เป็นตัวหนังสือกลับมาคงจะลำบากสุดๆ ครับ

ดังนั้นวันนี้ผมจะทำการถอดหน้ากาก ตัวแทนสาย A และ B ซะก่อนครับ !!

ถอดหน้ากากตัวแทนสาย A

ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMIFS ครับ!!

มีหลายคนเดาถูกด้วย แต่หลายคนก็อาจจะงงว่าทำไมฟังก์ชัน SUMIFS ถึงเอามา Lookup ค่าราคาได้ด้วย? มันเป็นฟังก์ชันที่เอาไว้หาผลรวมตามเงื่อนไขไม่ใช่เหรอ?

ใช่ครับ SUMIFS มีหน้าที่หลักคือเอาไว้หาผลรวมตามเงื่อนไขที่กำหนด … แต่ถ้าค่าที่จะ Lookup เป็นตัวเลข การใช้ SUMIFS ก็เป็นอีกวิธีที่เข้าท่ามากๆ ในการทำ Lookup แบบ Multiple Criteria

=SUMIFS(sum_range,criteria_range,criteria,…)

  • sum_range คือ range ที่เราจะหาผลรวม ในที่นี้ก็คือ range ที่จะ lookup ค่ามา ซึ่งก็คือ C2:C7 นั่นเอง
  • criteria_range1 คือ range ที่จะมีข้อมูลเงื่อนไขตัวแรกอยู่ ซึงคือ product A2:A7
  • criteria1 คือ เงื่อนไขตัวแรก ในที่นี้คือ product ข (A10)
  • criteria_range2 คือ range ที่จะมีข้อมูลเงื่อนไขตัวแรกอยู่ ซึงคือ Type B2:B7
  • criteria2 คือ เงื่อนไขตัวแรก ในที่นี้คือ Type 2 (B10)

ข้อควรระวัง

ถ้ามีค่า Criteria ซ้ำๆ หลายบรรทัด เช่น มี ข 2 หลายอัน การใช้ AVERAGEIFS ก็อาจจะเป็นทางเลือกที่ดีกว่า เพราะจะได้ค่าเฉลี่ยกลับไป แทนที่จะได้ผลรวมหากใช้ SUMIFS

SUMIFS : กรณีมี Criteria ซ้ำกันหลายบรรทัด จะได้ (60+60)

AVERAGEIFS : กรณีมี Criteria ซ้ำกันหลายบรรทัด จะได้ (60+60) /2

ถอดหน้ากากตัวแทนสาย B

ปรากฏว่าตัวแทนสาย A คือฟังก์ชัน SUMPRODUCT ครับ!!

มีคนเดาถูกเช่นกัน และหลายคนก็คงจะงงอีกเช่นกัน เพราะปกติ SUMPRODUCT จะเอาไว้คำนวณการจับคู่คูณกันแล้วค่อยหาผลรวมของทุกคู่

เช่นรูปนี้ มันจะเอา (100*60%) + (200* 50%) + (300*40%)

 

ซึ่งถ้าสังเกตจะเห็นว่า Range สีน้ำเงิน และ Range สีแดง อยู่แยกกันคนละ Argument (Array1 กับ Array2) มันก็เลยจับคู่มาคูณกันแล้วหาผลรวม

แต่กลับมายังโจทย์การแข่งขันของเรา ทั้ง (C2:C7) และ (B2:B7=B10) และ (A2:A7=A10) มันอยู่ Argument Array1  เหมือนกันหมดเลย!!

ซึ่งการเขียนสูตรแบบเอา Range หลายอันบวกลบคูณหารกันนั้น เป็นการเขียนสูตรแบบ Array Formula ซึ่งปกติต้องกดปุ่ม Control+Shift+Enter ถึงจะใช้งานได้ แต่ฟังก์ชัน SUMPRODUCT สามารถทำงานได้เลยโดยกดปุ่ม Enter ธรรมดา

หากลองวิเคราะห์ละเอียดโดยลากแถบดำในสูตร แล้วกด F9 ดูในองค์ประกอบแต่ละส่วนจะได้ดังนี้

=SUMPRODUCT({10;50;20;60;30;70}*({1;2;1;2;1;2}=2)*({“ก”;”ก”;”ข”;”ข”;”ค”;”ค”}=”ข”))

=SUMPRODUCT({10;50;20;60;30;70}*({FALSE;TRUE;FALSE;TRUE;FALSE;TRUE})*({FALSE;FALSE;TRUE;TRUE;FALSE;FALSE}))

แปลว่า 10 จับคู่กับ FALSE และ FALSE

50 จับคู่กับ TRUE และ FALSE … ไปเรื่อยๆ

=SUMPRODUCT({10;50;20;60;30;70}*{0;0;0;1;0;0})

=SUMPRODUCT({0;0;0;60;0;0})

=60

โชว์เหนือ Lookup 2 แกน

สูตร SUMPRODUCT ฝากบอกมาว่า ถึงตารางจะพลิกไปเป็นอีกแบบ มันก็ยังทำงานได้นะ (เหมือน Lookup 2 แกน)

ซึ่งสูตรนี้ทำงานได้ เป็นการคูณ Array แบบมิติ 2 ที่มี Range ไม่เท่ากัน นั่นคือ

  • A3:A5 มีมิติ 3R x 1C
  • B2:C2 มีมิติ 1R x 2C

คูณกัน ได้ผลลัพธ์เป็น Range ที่มีมิติ = 3R x 2C ซึ่งมีมิติเท่ากับ Range ของข้อมูล Price B3:C5 พอดี จึงทำการคูณกันในตอนจบได้ ตามขั้นตอนข้างล่าง

=SUMPRODUCT((B3:C5)*(A3:A5=A10)*(B2:C2=B10))

=SUMPRODUCT({10,50;20,60;30,70}*({“ก”;”ข”;”ค”}=”ข”)*({1,2}=2))

=SUMPRODUCT(({10,50;20,60;30,70})*({FALSE;TRUE;FALSE})*({FALSE,TRUE}))

=SUMPRODUCT(({10,50;20,60;30,70})*{0,0;0,1;0,0})

=SUMPRODUCT({0,0;0,60;0,0})

= 60

ตอนต่อไป

วันศุกร์หน้า พบกับความสามารถของฟังก์ชั่นทั้งสองที่เข้ารอบไป ว่าจะแสดงอะไรเจ๋งๆ ให้พวกเราได้ดูกันครับ