Snasui Excel Forum

ปกติใช้สูตร Countif อยู่เพื่อแยกจำนวนในแต่ละแผนกว่ามีจำนวนเท่าใด

แต่ตอนนี้พยายาม แยกไปอีกว่า ในแต่ละแผนกมีแต่ละสัญชาติเท่าไหร่ อยู่ในแต่ละ SHIFT เท่าไหร่ และหักจากคนที่มี status ว่าลาออก ออกไปด้วยครับ
ดังไฟล์ตัวอย่างเลยครับ ที่ไฮไลต์เหลืองไว้คือผลลัพธ์ที่ต้องการให้สูตรแสดงออกมาครับ
รบกวนหน่อยนะครับ พยายามคิดเท่าไหร่ก็ไม่ได้สักทีครับ

เงื่อนไขค่อนข้างเยอะครับ

ผมลองเขียนสูตรหาคำตอบ โดยขั้นตอนแรกทำการยกเลิกการ Merge เซลล์ J3:J9 ก่อนครับ

ที่ L3 คีย์
=SUM(IF($B$3:$B$13=$J3,IF($C$3:$C$13=$K3,IF($D$3:$D$13=TRIM(SUBSTITUTE(L$2,LEFT(L$2,FIND(" ",L$2)),"")),1))))-SUM(IF($B$3:$B$13=$J3,IF($C$3:$C$13=$K3,IF($D$3:$D$13=TRIM(SUBSTITUTE(L$2,LEFT(L$2,FIND(" ",L$2)),"")),IF($E$3:$E$13="ลาออก",1)))))
กด Ctrl+Shift+Enter คัดลอกไปทางขวามือ แล้วคัดลอกลงล่างพร้อมกัน

ปล. คำตอบเดิมของผู้ถาม ผิดอยู่หรือเปล่าครับ ผมนับด้วยมือแล้วนำมาเีทียบดู ผมคิดว่าน่าจะผิดครับ

ขอบคุณมากๆครับ เงื่อนไขเยอะจริงๆหล่ะครับ
แต่การมานั่งหยอดตัวเลขเองทำให้ผิดพลาดมาอย่างที่ท้วงผมมาเลยครับ

ขอถามเพิ่มครับ ทำไมถึง Merge ไม่ได้ครับ ในเมื่อค่าที่ Merge นั่นเท่ากันอยู่ดีครับ

Merge Cells ได้ครับ แต่จะส่งผลให้ต้องใช้สูตรยากขึ้นเรื่อย ๆ

ยกตัวอย่างอีกสูตรไม่ต้องปลด Merge Cells ที่ L3 คีย์

=SUMPRODUCT(--(LOOKUP(CHAR(255),$J$3:$J3)=$B$3:$B$12),--(SUBSTITUTE(L$2,"SHIFT ","")=$D$3:$D$12),--($C$3:$C$12=$K3),--($E$3:$E$12<>"ลาออก"))

Enter > Copy ไปทางขวาและลงด้านล่าง

snasui wrote: Merge Cells ได้ครับ แต่จะส่งผลให้ต้องใช้สูตรยากขึ้นเรื่อย ๆ

ยกตัวอย่างอีกสูตรไม่ต้องปลด Merge Cells ที่ L3 คีย์

=SUMPRODUCT(--(LOOKUP(CHAR(255),$J$3:$J3)=$B$3:$B$12),--(SUBSTITUTE(L$2,"SHIFT ","")=$D$3:$D$12),--($C$3:$C$12=$K3),--($E$3:$E$12<>"ลาออก"))

Enter > Copy ไปทางขวาและลงด้านล่าง

สูตรอาจารย์สั้นกว่าเยอะครับ แนะนำผู้ถามสามารถใช้สูตรของอาจารย์เลยครับ

ขอบคุณมากครับ ตอนนี้เริ่มใช้งานได้บ้างแล้วครับ กำลังนั่งทำความเข้าใจเพิ่มเติม

ตอนนี้ผมใช้สูตรตามอาจารย์หน่ะครับ แต่สูตรที่คุณ bank9597 เขียนมาไม่สูญเปล่าแน่ๆครับ ทำให้ผมได้รู้อะไรเพิ่มเติม ยังไงก็ขอบคุณมากมากนะครับ

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

ขอแจมหน่อยครับ อยากทราบว่าถ้าเปลี่ยนจากการใช้ Sumproduct ของอาจารย์มาใช้ Sumifs แทนได้ไหมครับและใช้อย่างไรครับ

กรณีใช้ Countifs สามารถใช้สูตรที่เซลล์ L3 ตามด้านล่างครับ

=COUNTIFS($B$3:$B$12,LOOKUP(CHAR(255),$J$3:$J3),$D$3:$D$12,SUBSTITUTE(L$2,"SHIFT ",""),$C$3:$C$12,$K3,$E$3:$E$12,"<>ลาออก")

Enter > Copy ไปด้านขวาและลงด้านล่าง

snasui wrote:กรณีใช้ Countifs สามารถใช้สูตรที่เซลล์ L3 ตามด้านล่างครับ

=COUNTIFS($B$3:$B$12,LOOKUP(CHAR(255),$J$3:$J3),$D$3:$D$12,SUBSTITUTE(L$2,"SHIFT ",""),$C$3:$C$12,$K3,$E$3:$E$12,"<>ลาออก")

Enter > Copy ไปด้านขวาและลงด้านล่าง

ทำไมต้องมี lookup(char(255) ด้วยครับเพราะ ข้อความเหมือนกัน

เนื่องจากเป็นข้อความที่ไม่ต่อเนื่องมีการเว้นเอาไว้ Lookup(Char(255),Range) จะหาข้อความตัวสุดท้ายใน Range

สามารถทดสอบโดยเปลี่ยน Char(255) เป็น =Lookup(Cell,Range) แล้วสังเกตดูผลครับ