วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข!

วันนี้เราจะมาดูวิธี SUM เฉพาะที่มองเห็นแบบมีเงื่อนไขกันครับ (Sum Filter with Criteria) ซึ่งเป็นเรื่องที่แปลกดี แต่ก็มีคนถามผมมา เลยเอามาตอบให้ทุกคนพร้อมกันเลย

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

สมมติข้อมูลก่อน Filter เป็นแบบนี้ ซึ่งรวมจำนวนชิ้นสินค้าทั้งหมดได้ 31 ชิ้น

ถ้าเรา Filter ว่าเอาเฉพาะการชำระเงินเป็นเงินสด …จะพบว่า เจ้า SUM ก็จะได้เท่าเดิมอยู่ดี…

ถ้าจะ SUM เฉพาะสิ่งที่ Filter เห็นอยู่

แต่ถ้าเราอยากจะ SUM แค่สิ่งที่ Filter อยู่เท่านั้น ต้องใช้ SUBTOTAL หรือไม่ก็ AGGREGATE มาช่วย เช่น

=SUBTOTAL(9,C5:C14)

เลข 9 ในสูตร เป็นการเลือกโหมดว่าจะให้ SUBTOTAL สรุปข้อมูลด้วยวิธีไหน? ซึ่ง 9 คือการ SUM นั่นเอง

=AGGREGATE(9,3,C5:C14)

ถ้าใช้ AGGREGATE เลข 9 ก็เหมือน SUBTOTAL แต่จะสามารถใส่ Option เพิ่มได้อีกว่าให้มันไม่สนใจอะไรบ้าง ซึ่งเลข 3 คือ ไม่สนแถวที่ซ่อนอยู่ ไม่สน error ไม่สน subtotal และ aggregate ที่ซ้อนอยู่ (พูดง่ายๆ คือ 3 ไม่สนมันทุกอย่างนี่แหละ)

ถ้าไม่ Filter แต่จะ SUM เฉพาะอาหาร

ถ้าเราไม่ได้ Filter แต่จะ SUM แค่บางอย่าง แบบนี้เรียกว่า SUM แบบมีเงื่อนไข ซึ่งสามารถทำได้หลายแบบ เช่น

ใช้ SUMIFS

=SUMIFS(C5:C14,B5:B14,"อาหาร")

ซึ่งแปลว่า ให้ SUM พื้นที่ C5:C14 โดยเงื่อนไขคือ B5:B14 เป็นคำว่าอาหาร

ใช้ SUM แบบ Array

=SUM((C5:C14)*(B5:B14="อาหาร"))

หลักการทำงานคือหากลองลากครอบ B5:B14=”อาหาร” แล้วกด F9 จะได้ TRUE/FALSE แบบนี้

=SUM((C5:C14)*({TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}))

พอ TRUE/FALSE เชื่อมกับเครื่องหมายทางคณิตศาสตร์ มันก็จะกลายเป็น 1 กับ 0 ทำให้ ได้แบบนี้

=SUM({4;0;0;5;6;0;2;0;4;0})

ถ้าเป็น Excel ที่ไม่รองรับ Dynamic Array ต้องเขียนสูตรข้างบนแล้วกด Ctrl+Shift+Enter ด้วย จะมีปีกกางอกออกมา ถ้าไม่อยากต้องกด Ctrl+Shift+Enter ก็ให้ใช้ SUMPRODUCT แทน

ใช้ SUMPRODUCT

=SUMPRODUCT((C5:C14)*(B5:B14="อาหาร"))

ถ้าจะ Filter เงินสด แต่จะ SUM เฉพาะอาหารล่ะ?

ฟังก์ชันพวก SUMIFS กับ SUMPRODUCT มันก็ดันไม่สนใจ Filter ด้วย เห็นมะว่าได้ 21 เท่าเดิมเลย

แล้วเราจะทำไงดี? ลองมั่วหลายๆ แบบดู

ถ้าเราเอา SUBTOTAL เขียนแบบ Array ล่ะ

=SUBTOTAL(9,(C5:C14)*(B5:B14="อาหาร"))

ปรากฏว่ามันไม่ยอมให้กด Enter ด้วยซ้ำ…

แล้วถ้าลองใช้ AGGREGATE ล่ะ เพราะมันก็สนใจเฉพาะตัวที่มองเห็นได้

=AGGREGATE(9,7,(C5:C14)*(B5:B14="อาหาร"))

ก็ดันขึ้น #VALUE! อีก เพราะฟังก์ชันหมายเลข 1-13 ดันไม่รองรับ Array…

แล้วถ้าเอา SUMPRODUCT ผสม SUBTOTAL ล่ะ!

=SUMPRODUCT(SUBTOTAL(9,C5:C14)*(B5:B14="อาหาร"))

คำตอบไม่ Error ด้วย แต่ออกมาได้ 135 ซึ่งผิดเห็นๆ

เพราะเกิดจาก =SUMPRODUCT(27*{TRUE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}) ซึ่งใช้ไม่ได้ เพราะมันดันคำนวณ SUBTOTAL ให้กลายเป็นค่าเดียวก่อนเลย แล้วค่อยมาคูณ กับ 1,0 ของ TRUE/FALSE

ถ้าเราทำให้ SUBTOTAL มันคิดเลขทีละตัวล่ะ แบบนี้ก็ยังพอมีความหวัง!

Solution

วิธีแบบง่ายๆ

วิธีที่ง่ายคือ เพิ่มคอลัมน์พิเศษที่เอาไว้ดักการ Filter ซะ แล้วเขียนสูตรให้เป็น 1,0 โดยให้เป็น 1 เมื่อมองเห็น ดังนี้

=SUBTOTAL(3,A5)

ผมเลือก COUNTA ไปที่ช่อง A5 เพราะคิดว่าช่องนั้นยังไงก็ต้องมีค่าเสมอ ไม่ใช่ช่องว่าง

แบบนี้เราก็จะใช้ SUMIFS หรือ SUMPRODUCT ได้แล้ว

=SUMIFS(C5:C14,B5:B14,"อาหาร",E5:E14,1)
แค่เพิ่มคอลัมน์พิเศษว่าต้องเป็น 1

=SUMPRODUCT(C5:C14*(B5:B14="อาหาร")*E5:E14)
แค่คูณคอลัมน์พิเศษเข้าไป

แต่ถ้าไม่เพิมคอลัมน์เอา ก็จะอยากขึ้นเยอะเลย!!

ผมไปเจอในเว็บ ExcelJet ทำไว้ หลักการคือเค้าใช้ OFFSET ช่วยดึงค่ามาทีละช่องแล้วค่อยส่งให้ SUBTOTAL ทำงานต่อ ซึ่งใครสนใจก็ลองเข้าไปดูได้ครับ https://exceljet.net/formula/count-visible-rows-only-with-critera

ถ้าเป็นผมทำเองด้วยหลักการคล้ายๆ กันก็จะได้สูตรแบบนี้

=SUMPRODUCT((B5:B14="อาหาร")*SUBTOTAL(9,OFFSET(C4,ROW(INDIRECT("1:"&ROWS(C5:C14))),0)))

ซึ่งยากกว่าการเพิ่มคอลัมน์พิเศษเยอะเลยเนอะ

และนี่ก็คือเทคนิคที่อยากจะแนะนำครับ บางครั้งการเพิ่มคอลัมน์พิเศษเข้าไปก็ช่วยให้สูตรเขียนง่ายขึ้นเยอะเลยนะ และมันก็ไม่ได้แย่อะไรเลยด้วยเปลืองพื้นที่เพิ่มแค่คอลัมน์เดียว แต่เขียนสูตรง่าย และเร็วขึ้นเยอะ