เทพเอ็กเซล ช่วย "ปลุกเทพ Excel ในตัวคุณ"
0
  • No products in the cart.
Excel ทั่วไปExcel Array Formula

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

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

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

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

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

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

ถ้าเรา 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 ไม่สนมันทุกอย่างนี่แหละ)

sum เฉพาะที่มองเห็นจาก filter แต่มีเงื่อนไข

ถ้าไม่ 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="อาหาร"))
วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข! 3

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

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

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

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

ถ้าเราเอา 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 เพราะคิดว่าช่องนั้นยังไงก็ต้องมีค่าเสมอ ไม่ใช่ช่องว่าง

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

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

=SUMIFS(C5:C14,B5:B14,"อาหาร",E5:E14,1)
แค่เพิ่มคอลัมน์พิเศษว่าต้องเป็น 1
=SUMPRODUCT(C5:C14*(B5:B14="อาหาร")*E5:E14)
แค่คูณคอลัมน์พิเศษเข้าไป
วิธี SUM เฉพาะค่าที่เห็นจาก Filter แต่มีเงื่อนไข! 6

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

ผมไปเจอในเว็บ 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)))

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

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

แชร์ความรู้ให้เพื่อนๆ ของคุณ
  • 1.6K
  •  
  •  
  •  
  •  
  • 1.6K
  •  
  •  
  •  
  •  
Sira Ekabut
the authorSira Ekabut
• Microsoft Office Specialist : Excel ระดับ Expert • ก่อตั้งเว็บไซต์และ แฟนเพจเทพเอ็กเซล คนติดตาม 8 หมื่นคน+, คลิปวีดีโอ 1 ล้าน Views + • หนังสือ Best Seller คอมพิวเตอร์ : Excel Level Up! , จอมเวท เทพ Excel , Excel Power Up!วิทยากรใน SkillLane.com คอร์สออนไลน์ชั้นนำ / อบรม Excel ให้บริษัทชั้นนำ

Leave a Reply