sum ข้อมูลแบบมีเื่อนไข

[March201711]

อยาก sum data เฉพทะเดือนที่เลือกที่ cell e2 โดยดึงข้อมูลจากsheet data ที่column e โดยดึเป็นยอดรวมของ MO เฉะลพาะเดือนที่ธันวาคมค่ะ

[puriwutpokin]

ที่ E6=SUMPRODUCT(ISNUMBER(SEARCH(LOOKUP(CHAR(255),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)),data!F$3:F$35) คัดลอกไปทางขวาและลงล่างครับ

[March201711]

ค่ะ แล้ว Column B C , M/O และ M/O Name ใช้สูตรแยก text อย่างไรค่ะ ให้ดึงมาแค่ที่ไม่ซ้ำกันค่ะ

[puriwutpokin]

ปรับเป็นที่ B6=IFERROR(IF(MOD(ROW(B6),2),"",INDEX(LEFT(data!$D$3:$D$35,4),MATCH(0,COUNTIF(B$5:B5,LEFT(data!$D$3:$D$35,4)),0))),"") กด Ctrl+Shift+Enter คัดลอกลง
ปรับเป็นที่ C6=IFERROR(IF(MOD(ROW(C6),2),"",INDEX(MID(data!$D$3:$D$35,6,14),MATCH(0,COUNTIF(C$5:C5,MID(data!$D$3:$D$35,6,14)),0))),"") กด Ctrl+Shift+Enter คัดลอกลง
ปรับเป็นที่
D6=IFERROR(INDEX(data!C$3:C$35,MATCH(1,INDEX((LEFT(data!D$3:D$35,4)=B6)*(MONTH(data!E$3:E$35)=MONTH(E$2)),),)),"") คัดลอกลง
ปรับเป็นที่ E6=IF(ROWS($B$6:$B6)>COUNTIF($B$6:$B6,"*?")*2,"",SUMPRODUCT(ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)),data!F$3:F$35)) คัดลอกขวาและลงล่าง

[March201711]

ถ้าแทรก column ที่ sheet data 2 column แล้วทำไม่ sheet totaxl column F ค่า comm ยอดเป็น 0 ต้องปรับแก้อย่างไร

[puriwutpokin]

ถ้าแทรก column ที่ sheet data 2 column แล้วทำไม่ sheet totaxl column F ค่า comm ยอดเป็น 0 ต้องปรับแก้อย่างไร

แนบไฟล์ล่าสุดที่แทรกคอลัมน์มาดูครับว่า แทรกตรงไหน ครับ

[March201711]

ตามfile ที่แนบมาค่ะ

[puriwutpokin]

ปรับเป็นที่ F6=IF(ROWS($B$6:$B6)>COUNTIF($B$6:$B6,"*?")*2,"",SUMPRODUCT(ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)),data!I$3:I$35))

[March201711]

ช่วยอธิบายสูตรได้ไหมคะ จะได้นำไปประยุกต์ใช้กับงานอื่นๆค่ะ ขอบคุณมากเลยค่ะ

[puriwutpokin]

ช่วยอธิบายสูตรได้ไหมคะ จะได้นำไปประยุกต์ใช้กับงานอื่นๆค่ะ ขอบคุณมากเลยค่ะ

ตรงส่วนไหนไม่เข้าใจครับ ยกตัวอย่างมาดูครับเช่นอะไรบ้างที่ ต้องการทราบครับ

[March201711]

F6=IF(ROWS($B$6:$B6)>COUNTIF($B$6:$B6,"*?")*2,"",SUMPRODUCT(ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)),data!I$3:I$35))

ตรง COUNTIF($B$6:$B6,"*?")*2,"",
และ SUMPRODUCT(ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)),data!I$3:I$35))
แล้วทำไมต้อง*month=month ด้วยคะ
มีหลักการคิดตรรกะอย่างไร

[puriwutpokin]

ตรงCOUNTIF($B$6:$B6,"*?")*2 เนื่องจากที่ B6:B17 มีสูตรอยู่ไม่ได้เป็นเซลว่างทั้งหมด จึงต้องใช้ "*?" นับตัวที่มีจำนวนที่เห็นจริงเท่านั้น
ส่วน ที่ต้อง *2 เพื่อให้มันมีผลนับเป็น 2 บรรทัด เพราะข้อมูลที่ต้องการคือซ้ำกัน 2บรรทัด พอค่ามากว่า ROWS($B$6:$B6) ก็ให้เป็นค่าว่าง
และ LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6) เพื่อหาค่าสุดท้ายที่ไม่ใช่ค่าว่าง
และ SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35) เพื่อหาค่าที่ไม่ใช่ค่าว่างที่เป็นค่าสุดท้าย โดยนำค่านั้นมาหาค่า คอลัมน์ D
และ ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35)) เพื่อค่าใดที่เป็นตัวเลขเพื่อจะได้ค่า TRUE , FALSE ไปคูณกับค่า (MONTH(data!$E$3:$E$35)=MONTH($E$2)) เพื่อให้ได้ตรงตามเดือนที่ตรงกับเงื่อนไข
โดย ISNUMBER(SEARCH(LOOKUP(2,1/($B$6:$B6<>""),$B$6:$B6),data!$D$3:$D$35))*(MONTH(data!$E$3:$E$35)=MONTH($E$2)) เมื่อคูณกันแล้วจะได้ค่า {0;0;0;1;0;1;0;1;1;0;1;1;1;1;0;1;1;1;0;0;1;1;1;0;0;0;0;0;0;0;0;0;0} ซึ่งค่านี้จะไปคูณ เพื่อแม็ชกันค่าช่วงที่ต้องการหาคือ ช่วงdata!I$3:I$35 ตามนี้ครับ

[March201711]

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