Snasui Excel Forum

หาค่าผลรวม ตาม filter ที่เราเลือกต้องใช้สูตรอะไรครับ

จะมีหน้าฐานข้อมูล อยู่ที่ Sheets TP1-S

และหน้ารายงาน Sheets Total

อยากให้หน้ารายงานแสดงค่าผลรวมตาม filter ที่ เราเลือกใน sheets TP1-S ครับ

F13 =SUMPRODUCT(SUBTOTAL(3,OFFSET('TP1-S'!$G$2:$G$24,ROW('TP1-S'!$G$2:$G$24)-MIN(ROW('TP1-S'!$G$2:$G$24)),,1)),--('TP1-S'!$C$2:$C$24=total!F$12),--('TP1-S'!$B$2:$B$24=total!$F$11),('TP1-S'!$G$2:$G$24)/1440)

Copy ลากไปทางขวาครับ

ขอบคุณครับ ทำไมถึงใช้ sumproduct ด้วยครับ

titus wrote:ขอบคุณครับ ทำไมถึงใช้ sumproduct ด้วยครับ

ผมก็ยังไม่ชำนาญ sumproduct เท่าไหร่ครับ
ที่ใช้เพราะง่ายต่อการสร้าง Array Formula ครับ

กล่าวอย่างสรุป จากสูตรด้านบนที่ต้องใช่ร่วมกันเพราะเป็นการคำนวณตามเงื่อนไข ลำพัง Subtotal ไม่สามารถคำนวณตามเงื่อนไขได้ครับ

snasui wrote: กล่าวอย่างสรุป จากสูตรด้านบนที่ต้องใช่ร่วมกันเพราะเป็นการคำนวณตามเงื่อนไข ลำพัง Subtotal ไม่สามารถคำนวณตามเงื่อนไขได้ครับ

อยากให้ อธิบาย แต่ละสูตรหน่อยครับว่าหาอะไร เเล้วได้ผลลับได้ยังไง ผม ยังงงอยู๋เลยครับ

Sumproduct ประกอบด้วย ช่วงข้อมูล1,ช่วงข้อมูล2,ช่วงข้อมูล3 โดยผลลัพท์ จะเอาช่วงข้อมูล
แต่ละช่วงมาคูณกัน เป็นผลลัพท์
โดยภายใน "ช่วงข้อมูล" จะ สามารถสร้าง "ช่วงข้อมูลแบบ Array"(อ้างอิงได้มากกว่า1ผลลัพท์)
จากสูตรด้านบน ช่วงข้อมูล1 จำแนกได้ดังนี้

ช่วงข้อมูล1> SUBTOTAL(3,OFFSET('TP1-S'!$G$2:$G$24,ROW('TP1-S'!$G$2:$G$24)-MIN(ROW('TP1-S'!$G$2:$G$24)),,1))
1.1 สูตร "SUBTOTAL(3" ทดแทนสูตร Counta(นับข้อมูลที่มี) โดยนับเฉพาะข้อมูลที่ Filter(เป็นความสามารถของสูตร SUBTOTAL)
1.2 สูตร OFFSET('TP1-S'!$G$2:$G$24,ROW('TP1-S'!$G$2:$G$24)-MIN(ROW('TP1-S'!$G$2:$G$24)),,1) เป็นการเลือกช่วงข้อมูลทั้งหมดก่อนนำมา นับ ทีละ Cell
จากไฟล์แนบจะได้ผลลัพท์ดังนี้
{150;0;30;20;10;20;20;115;25;20;80;10;10;60;25;30;10;20;30;35;30;15;40}
1.3 ผลลัพท์ จาก SUBTOTAL(Mode3) + OFFSET (สร้างผลลัพท์เป็นArray เพราะทำงานใน SUMPRODUCT)
สมมติผมกรองข้อมูล เฉพาะ P1 ผลลัพท์
จะเป็น {1;0;0;0;1;1;0;0;0;1;1;1;1;1;1;1;0;1;1;1;1;1;0}
(เป็น 1 เพราะนับ ไม่ใช่ผลรวม)

Cell ที่ผลลัพท์เป็น 0 เกิดจากเรา Filter ออกไป หรือไม่มีผลลัพท์ ซึ่งพอนำไปคูณกับช่วงข้อมูลอื่นๆ ก็จะกลายเป็น 0 (ทุกจำนวนคุณด้วย0 เท่ากับ 0)

ช่วงข้อมูลที่2 > --('TP1-S'!$C$2:$C$24=total!F$12) เช่นเดียวกับสูตร OFFSETด้านบน แต่มีเงื่อนไขคือ =total!F$12 หรือไม่ หากเท่าจะได้ผลลัพท์ =1 หากไม่เท่าจะได้ผลลัพท์ =0 (ส่วนข้อมูลที่ถูกFilter ซ่อนไปก็จะได้ผลลัพท์เท่ากับ 1 แต่จากด้านบนที่ช่วงข้อมูล 1 ได้ผลลัพท์ 0ไปแล้ว ดังนั้น 1คูณ0 = 0 อยู่ดี)
สมมติ F$12 = "ชุดส่งแผ่น" ผลลัพท์จะเท่ากับ
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;1;0;0}
ส่วนเครื่องหมาย -- ด้านหน้าคือตรวจช่วงข้อมูลทุกประเภทให้เป็นตัวเลข
ทำให้เกิดผลลัพท์ 1 และ 0
1=TRUE(เป็นจริง) 0=FALSE(เป็นเท็จ)

ช่วงข้อมูลที่3 >--('TP1-S'!$B$2:$B$24=total!$F$11)
เช่นเดียวกับช่วงข้อมูลที่2 สมมติ total!$F$11 = "P1" ผลลัพท์จะได้
{1;0;0;0;1;1;0;0;0;1;1;1;1;1;1;1;0;1;1;1;1;1;0}

ช่วงข้อมูลที่4 > ('TP1-S'!$G$2:$G$24)/1440 คือการดึงผลลัพท์ทั้งหมดออกมา จึงได้เป็น {150;0;30;20;10;20;20;115;25;20;80;10;10;60;25;30;10;20;30;35;30;15;40}

พอคำนวณทุกช่วงก็จะเป็น
{150;0;30;20;10;20;20;115;25;20;80;10;10;60;25;30;10;20;30;35;30;15;40} คูณ
{1;0;0;0;1;1;0;0;0;1;1;1;1;1;1;1;0;1;1;1;1;1;0} คูณ
{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;1;0;0} คูณ
{1;0;0;0;1;1;0;0;0;1;1;1;1;1;1;1;0;1;1;1;1;1;0}

ผลลัพท์ เครื่องจักร+P1 = 30+30 = 1.00 ครับ

ขอบคุณมากครับ