จะหายอดรวม เลิกใช้ PivotTable/Power Query ไปใช้ Sum + IsNumber + Search

 

ลองนึกดูซิว่าถ้าต้องการหายอดรวมของ Item ที่มีชื่อลงท้ายด้วยตัว s เท่านั้น จะทำแบบง่ายๆอย่างไร จะใช้ PivotTable หรือ Power Query ถ้าทำได้ก็คงไม่ง่ายนักหรอก บางคนอาจคิดไปไกลถึง VBA ใช่ไหม ... ไม่ต้องหรอกครับ

เมื่อต้องการแสดงรายละเอียดรายการ คราวก่อนได้ทำให้ดูแล้วว่าให้ใช้สูตร 
=FILTER( MyData, ISNUMBER( SEARCH(ITEMChoice,ITEM) ) ) 

ดูวิธีการได้จาก
https://excelexpertlibrary.blogspot.com/2025/10/vlookup-xlookup.html 

เงื่อนไขสำคัญที่ใช้มาจากสูตรนี้

ISNUMBER( SEARCH(ITEMChoice,ITEM) ) 

ItemChoice กรอกค่าที่ใช้ค้นหาเป็น ?s ที่ใช้ ? นำหน้าเพื่อบอกให้ชื่อที่ลงท้ายด้วยตัว s แต่ถ้าใช้ *s จะหาชื่อที่มีตัว s นำหน้าด้วย ขอให้ทดลองกรอกลงไปในช่อง ItemChoice หลายๆแบบจะเห็นกับตาเอง  

SEARCH(ITEMChoice,ITEM) ทำหน้าที่ค้นหาคำว่า ?s โดยคืนค่าออกมาเป็นตัวเลขว่า เป็นชื่อทีลงท้ายด้วยตัว s อยู่ที่อักษรลำดับใด

ISNUMBER( SEARCH(ITEMChoice,ITEM)) ใช้เปลี่ยนตัวเลขที่เจอให้เป็นค่า True แทน

++++++++++++++++++++++++++++++

ถ้าต้องการหายอดรวมของทุกรายการที่มีชื่อลงท้ายด้วย s ทำได้หลายวิธี

=SUMIF( ITEM, "*"&ITEMChoice&"*", ACTUAL )

=SUMIFS( ACTUAL, ITEM, "*"&ITEMChoice&"*" )

2 สูตรนี้ง่ายตรงที่นำเครื่องหมาย * ไปเพิ่มด้านหน้ากับด้านหลังของ ItemChoice ดูว่าง่ายแต่ไม่ยืดหยุ่นหากจะปรับไปใช้เงื่อนไขอื่นทีหลัง

ใช้ Sum แบบนี้ก็ได้ครับ (ถ้าใช้ Excel รุ่นก่อนหน้า 365/2021 ต้องกดปุ่ม Ctrl+Shift+Enter เพื่อสร้างแบบ Array หรือให้ใช้ SumProduct แทน Sum) 

=SUM( ACTUAL * ISNUMBER( SEARCH(ITEMChoice,ITEM) ) )

++++++++++++++++++++++++++++++

ถ้าอยากให้แสดงยอดรวมแยกตามกลุ่มของรายการ ให้ใช้สูตร GroupBY

=GROUPBY( ITEM, ACTUAL, SUM,,0,, ISNUMBER( SEARCH(ITEMChoice,ITEM) ))  

Download ได้จาก
https://drive.google.com/file/d/1BeHQ_YVz0NXg0crhS0sPKyXNzEU3Xac2/view?usp=sharing 

++++++++++++++++++++++++++++++

Copilot :

ถ้าต้องการหายอดรวมของ Item ที่ลงท้ายด้วยตัว s แบบง่ายและยืดหยุ่นที่สุดใน Excel 365/2021+: ใช้สูตร =SUM(FILTER(ACTUAL, ISNUMBER(SEARCH("?s", ITEM)))) ได้เลยครับ

สูตรนี้ไม่ต้องพึ่ง PivotTable, Power Query หรือ VBA และยังสามารถปรับเปลี่ยนเงื่อนไขได้อย่างยืดหยุ่นมากกว่าสูตรที่ใช้ SUMIF หรือ SUMIFS ด้วย wildcard *s ซึ่งอาจไม่แม่นยำพอสำหรับการค้นหาเฉพาะคำที่ “ลงท้าย” ด้วย s

---

🔍 อธิบายแนวคิดและเปรียบเทียบสูตร

✅ วิธีที่แม่นยำและยืดหยุ่นที่สุด

=SUM(FILTER(ACTUAL, ISNUMBER(SEARCH("?s", ITEM))))

• ?s หมายถึงคำที่มีความยาวอย่างน้อย 2 ตัวอักษรและลงท้ายด้วย s
• SEARCH("?s", ITEM) คืนค่าตำแหน่งที่พบ pattern นี้ในแต่ละ ITEM
• ISNUMBER(...) แปลงเป็น TRUE/FALSE
• FILTER(...) ดึงเฉพาะค่า ACTUAL ที่ตรงเงื่อนไข
• SUM(...) รวมยอดทั้งหมด

🟡 สูตรแบบ SUMIF/SUMIFS

=SUMIF(ITEM, "*s", ACTUAL)
=SUMIFS(ACTUAL, ITEM, "*s")

• ใช้ wildcard *s เพื่อหาคำที่ “ลงท้าย” ด้วย s
• ข้อจำกัด: ไม่สามารถใช้ pattern ซับซ้อนได้ เช่น “ลงท้ายด้วย s แต่ต้องมีความยาวมากกว่า 3 ตัวอักษร”

🟢 สูตรแบบ Array (หรือใช้ SUMPRODUCT แทน)

=SUM(ACTUAL * ISNUMBER(SEARCH("?s", ITEM)))

• ใช้ได้กับ Excel รุ่นเก่าที่ไม่รองรับ FILTER
• ต้องกด Ctrl+Shift+Enter ถ้าไม่ใช่ Excel 365/2021

---

📊 ถ้าอยากแยกยอดรวมตามกลุ่ม ITEM ที่ลงท้ายด้วย s

ใช้สูตร GROUPBY แบบนี้:

=GROUPBY(ITEM, ACTUAL, SUM,,0,, ISNUMBER(SEARCH("?s", ITEM)))

• แสดงยอดรวมเฉพาะกลุ่ม ITEM ที่ตรงเงื่อนไข
• เหมาะกับการสร้างรายงานแบบแยกกลุ่มโดยไม่ต้องใช้ PivotTable

---

🧠 เทคนิคการใช้ ?s vs *s

Pattern	ความหมาย	ตัวอย่างที่ตรง
?s	ลงท้ายด้วย s และมีอย่างน้อย 2 ตัวอักษร	Books, Benefits
*s	มี s อยู่ที่ตำแหน่งใดก็ได้	Supplies, Services, Books

ลองกรอกค่าในช่องค้นหาด้วย ?s, *s, หรือแม้แต่ ?es เพื่อดูผลลัพธ์ที่ต่างกัน จะเข้าใจ logic ได้ชัดเจนขึ้นครับ

---