Excel Forum

เรียน อาจารย์ snasui และผู้รู้ทุกท่าน

สูตรการหาค่าผลรวมตามแถบสี ใน Sheet 1 คอลัมม์ F บรรทัดที่ 3 สูตรทึ่ใช้แสดงผลตามปกติ แต่ใน Sheet 2 คอลัมม์ F บรรทัดที่ 3 สูตรที่ใช้เหมือนกัน Sheet 1 แต่สูตรไม่แสดงผล ไม่ทราบว่าติดปัญหาตรงไหนครับ

...

F3

=SUM(E4:E226)/2

**สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ

...

เรียน คุณ norkaz

แล้วถ้า F3 ใน sheet 2 ใช้สูตร =SUMPRODUCT(--($A$2:$A$294=""),$E$2:$E$294) ตัวนี้ได้เปล่าครับ เพราะใช้แล้วผลลัพธ์ไม่ออกครับ ไม่ทราบว่าผิดพลาดตรงไหน เพราะใน sheet 1 ใช้สูตรเหมือนกันแต่ผลลัพท์ออกมาตรงครับ

talayphuket wrote: Thu Nov 09, 2023 8:33 am เรียน คุณ norkaz

แล้วถ้า F3 ใน sheet 2 ใช้สูตร =SUMPRODUCT(--($A$2:$A$294=""),$E$2:$E$294) ตัวนี้ได้เปล่าครับ เพราะใช้แล้วผลลัพธ์ไม่ออกครับ ไม่ทราบว่าผิดพลาดตรงไหน เพราะใน sheet 1 ใช้สูตรเหมือนกันแต่ผลลัพท์ออกมาตรงครับ

ขออนุญาต ตอบแทนครับสูตร ใน sheet 1 แสดงผลลัพธ์ออกมาได้เนื่องจากสูตรที่ใช้ คือ เป็นการหาผลรวมของ ช่อง "ว่าง" ในคอลัมน์ A แล้วดึงค่า ใน คอลัมน์ E มารวมกันจึงสามารถแสดงผลได้
แต่เนื่องจากใช้สูตร ลักษณะเดียวกันใน Sheet2 จะไม่สามารถทำงานได้เพราะ ค่า ที่เป็น ช่อง "ว่าง" ดึงค่ามาแล้ว ค่ามันคือ 0 จึงรวมกันได้ 0 ครับ
ถ้าต้องการให้สูตรทำงาน ต้องลบ "MERCHANT TOTAL" ออกจะเซลล์ สีชมพูทั้งหมดครับ

...

=SUMPRODUCT(--(A2:A294=""),E2:E294)
สูตรอ้างอิง ไปที่ แถวที่มีค่าว่าง ซึ่งข้อมูลที่ แถวเป็นค่าว่างไม่มี ข้อมูลใดๆ ผลลัพธ์จึงออกมาเป็น 0

หากต้องการอ้างอิง จากผลรวม ด้วยสูตรอื่นๆ ใช้แบบนี้ได้ครับ
=SUMIF(A4:A226,"*TOTAL*",E4:E226)

กรณีใช้ SUMPRODUCT ลองปรับไปแบบนี้
=SUMPRODUCT(IFERROR((SEARCH("Total",A4:A226)>0)*E4:E226,""))

แต่การใช้ SUMPRODUCT ในลักษณะนี้ อาจจะต้องกดแบบ Array เต็มรูป แบบ Ctrl + Shift + Enter
เนื่องจากต้องไป บังคับค่า ERRROR ในชุด Array

หรือ อาจจะต้องใช้ INDEX(...,0) ไปครอบชุด Array ก่อน เพื่อตัดค่า Array เต็มรูป จึงใช้ SUMPRODUCT ( ณ. ขณะนี้ ผมไม่มี เวอร์ชั่น ที่ต่ำกว่า 365 ทดสอบ) แต่เข้าใจว่า แม้ใช้ SUMPRODUCT ก็ต้องกด แบบ เต็มรูป เนื่องจาก ตัว IFERROR ในจังหวะนั้น ทำให้สถานการณ์ต้องบังคับแบบ Array เต็มรูป

และหากไม่สามารถเลี่ยงแบบเต็มรูปได้ ก็ไม่จำเป็นต้องใช้ INDEX(....,0) ไปครอบ

สามารถ ใช้ SUM ไปตรงๆ แบบ Array เต็มรูป
SUM(IFERROR((SEARCH("Total",A4:A226)>0)*E4:E226,""))
Ctrl + Shift + Enter

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

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

เช่นในกรณีนี้ ควรใช้ SUMIF SUMIFS แทน SUMPRODUCT น่าจะเป็นทางเลือกที่ดีกว่า

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

**สนับสนุนฟอรั่ม กรุณาคลิกโฆษณาที่หน้าเพจ***

แมวใหญ่ Bigcat9
RCA พระราม9 กรุงเทพฯ
...

เรียน คุณ Xcelvba

ขอบคุณสำหรับคำอธิบายครับ ทำให้เข้าใจในตัวสูตรแล้วครับ

เรียน คุณ norkaz

ขอบคุณสำหรับคำอธิบายครับ ทำให้เข้าใจมากขึ้นครับ