Snasui Excel Forum

ต้องการนำข้อมูลในตารางซ้ายมือมาแสดงในตารางขวามือ (ไฟล์ที่แนบมา)
โดยเราสามารถกำหนดวันเริ่มต้นและสิ้นสุด และเลือกการชำระได้
เช่น ถ้าต้องการดูการชำระเงินสด ก็ให้แสดงเฉพาะเงินสดในวันที่ต้องการ
หรือถ้าต้องการดูการชำระทุกอย่าง (เลือกทั้งหมด) ก็ให้แสดงทุกรายการในวันนั้นๆ
และรวมยอดด้วย (จำนวนบรรทัดแต่ละวันอาจจะไม่เท่ากัน)

J14

=IFERROR(INDEX(A$3:A$45,SMALL(IF(($K$9=$B$3:$B$45)+($K$10=$B$3:$B$45),ROW(A$1:A$43)),ROW(A1))),"")
Array formula {}

Copy right + down

J14 ลากลง
=IFERROR(AGGREGATE(15,6,$A$3:$A$45/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(SEARCH(IF($K$11="ทั้งหมด","*",$K$11),$E$3:$E$45)),ROWS(J$14:J14)),"")

K14:N14 ลากลง
=IFERROR(VLOOKUP($J14,$A:$E,COLUMNS($J14:K14),),"")

อีกแบบปรับของคุณ Supachok
ที่ J14=IFERROR(INDEX(A$3:A$45,SMALL(IF(($B$3:$B$45>=$K$9)*($B$3:$B$45<=$K$10)*IF($K$11="ทั้งหมด",$E$3:$E$45=$E$3:$E$45,($E$3:$E$45=$K$11)),ROW(A$1:A$43)),ROW(A1))),"")
กด Ctrl+Shift+Enter คัดลอกไปทางขวาและลงล่างครับ

ขอบพระคุณทั้ง 3 ท่านมากๆ เลยครับ ทำให้ได้ผลลัพธ์ตามต้องการ

แต่ของคุณ Supachok ผลลัพธ์ยังไม่ตรงนัก แต่คุณ puriwutpokin มาปรับให้ ก็ถือว่าสมบูรณ์ครับ

Bo_ry wrote: Fri May 03, 2019 6:46 pm J14 ลากลง
=IFERROR(AGGREGATE(15,6,$A$3:$A$45/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(SEARCH(IF($K$11="ทั้งหมด","*",$K$11),$E$3:$E$45)),ROWS(J$14:J14)),"")

K14:N14 ลากลง
=IFERROR(VLOOKUP($J14,$A:$E,COLUMNS($J14:K14),),"")

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

puriwutpokin wrote: Fri May 03, 2019 9:54 pm อีกแบบปรับของคุณ Supachok
ที่ J14=IFERROR(INDEX(A$3:A$45,SMALL(IF(($B$3:$B$45>=$K$9)*($B$3:$B$45<=$K$10)*IF($K$11="ทั้งหมด",$E$3:$E$45=$E$3:$E$45,($E$3:$E$45=$K$11)),ROW(A$1:A$43)),ROW(A1))),"")
กด Ctrl+Shift+Enter คัดลอกไปทางขวาและลงล่างครับ

ปรับจากสูตรต่อยอดเดิม
1.insert column N รายการ
2.
=IFERROR(INDEX(A$3:A$45,SMALL(IF(($B$3:$B$45>=$L$9)*($B$3:$B$45<=$L$10)*IF($L$11="ทั้งหมด",$E$3:$E$45=$E$3:$E$45,($F$3:$F$45=$L$11)),ROW(A$1:A$43)),ROW(A1))),"")

3.O12
=SUBTOTAL(9,O14:O49)

คุณ Supachok ครับ
ที่คุณตอบมาในความเห็นที่ 7 น่าจะใช้ไฟล์เดิมใช่ไหมครับ

ผมขอส่งไฟล์ใหม่ที่ผมแก้ไขเงื่อนไขเข้าไปเพิ่มครับ
ไฟล์ใหม่ ผมแนบมานี้นะครับ หรือไฟล์ที่แนบมาในความเห็นที่ 6 ครับ

:
ต้องการนำข้อมูลในตารางซ้ายมือมาแสดงในตารางขวามือ
โดยเราสามารถกำหนดวันเริ่มต้นและสิ้นสุด และเลือกการชำระได้
เช่น ถ้าต้องการดูการชำระเงินสด ก็ให้แสดงเฉพาะเงินสดในวันที่ต้องการ
หรือถ้าต้องการดูการชำระทุกอย่าง (เลือกทั้งหมด) ก็ให้แสดงทุกรายการในวันนั้นๆ
(จำนวนบรรทัดแต่ละวันคงไม่เท่ากัน)
กรณีที่มีใบเสร็จเลขเดียวกัน แต่มีการชำระมากกว่า 1 ช่องทาง ก็ให้แสดงผลตามช่องทางการชำระด้วย

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


ส่วนที่คุณ Supachok แนะนำ (ความเห็นที่ 7) ลองแทรกเซลล์ตามที่แนะนำแล้ว ก็ยังไม่ได้ครับ

จึงขอแนบไฟล์มาเพื่ออธิบายให้ทุกท่านได้เข้าใจชัดยิ่งขึ้นครับ

ตามแนบครับ แต่ไม่ได้รวมยอดตามโจทย์
คงที่ท่านอื่นมาช่วยรวมให้.

ปรับเป็นตามคุณBo_ry
ที่ J14=IFERROR(INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(IF($K$11="ทั้งหมด",$F$3:$F$45=$F$3:$F$45,($F$3:$F$45=$K$11)))/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),"") คัดลอกไปถึง L14 และคัดลอกไปที่ N14 ด้วย และคัดลอกลงครับ
ที่ M14=SUMIFS($E$3:$E$45,$A$3:$A$45,J14,$F$3:$F$45,N14) คัดลอกลงครับ

แก้ไขครับที่
๋J14=IFERROR(IF($K$11="ทั้งหมด",INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0))),"")
แล้วคัดลอกไปที่คอลัมน์ K,L,N และคัดลอกลงครับ
ที่ M14=IFERROR(1/(1/IF($K$11="ทั้งหมด",SUMIFS($E$3:$E$45,$A$3:$A$45,J14,$F$3:$F$45,N14),INDEX($E$3:$E$45,AGGREGATE(15,6,(ROW($E$3:$E$45)-ROW($E$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(L$14:L14))))),"") คัดลอกลงครับ

puriwutpokin wrote: Tue May 07, 2019 7:51 pm แก้ไขครับที่
๋J14=IFERROR(IF($K$11="ทั้งหมด",INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0))),"")
แล้วคัดลอกไปที่คอลัมน์ K,L,N และคัดลอกลงครับ
ที่ M14=IFERROR(1/(1/IF($K$11="ทั้งหมด",SUMIFS($E$3:$E$45,$A$3:$A$45,J14,$F$3:$F$45,N14),VLOOKUP($J14,$A:$F,5,))),"") คัดลอกลงครับ

ได้แล้วครับ ขอบคุณครับ

อีกแบบ J14
=IFERROR(IF(J$13="จำนวนเงิน",SUMIFS($E:$E,$A:$A,$J14,$B:$B,$K14,$C:$C,$L14,$F:$F,$N14),INDEX($A$3:$F$99,SMALL(IF(FREQUENCY(IF(($B$3:$B$99>=$K$9)*($B$3:$B$99<=$K$10)*IF($K$11="ทั้งหมด",1,$F$3:$F$99=$K$11),MATCH($A$3:$A$99&$F$3:$F$99,INDEX($A$3:$A$99&$F$3:$F$99,),)),ROW($F$1:$F$99)),ROW($F$1:$F$99)),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,))),"")

กด Ctrl+Shift+Enter ลากไป N14 ลากลง

Bo_ry wrote: Tue May 07, 2019 8:59 pm อีกแบบ J14
=IFERROR(IF(J$13="จำนวนเงิน",SUMIFS($E:$E,$A:$A,$J14,$B:$B,$K14,$C:$C,$L14,$F:$F,$N14),INDEX($A$3:$F$99,SMALL(IF(FREQUENCY(IF(($B$3:$B$99>=$K$9)*($B$3:$B$99<=$K$10)*IF($K$11="ทั้งหมด",1,$F$3:$F$99=$K$11),MATCH($A$3:$A$99&$F$3:$F$99,INDEX($A$3:$A$99&$F$3:$F$99,),)),ROW($F$1:$F$99)),ROW($F$1:$F$99)),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,))),"")

กด Ctrl+Shift+Enter ลากไป N14 ลากลง

ใช้ได้เลยครับ
แต่ถ้า ไม่เป็นสูตร array ต้องแก้ไขอย่างไรครับ
และเซลล์ที่เหลือบรรทัดถัดไปที่ไม่มีข้อมูล ให้แสดงผลเป็นช่องว่าง (ไม่แสดงเป็นเลข 0)

Student01 wrote: Tue May 07, 2019 7:58 pm

puriwutpokin wrote: Tue May 07, 2019 7:51 pm แก้ไขครับที่
๋J14=IFERROR(IF($K$11="ทั้งหมด",INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0))),"")
แล้วคัดลอกไปที่คอลัมน์ K,L,N และคัดลอกลงครับ
ที่ M14=IFERROR(1/(1/IF($K$11="ทั้งหมด",SUMIFS($E$3:$E$45,$A$3:$A$45,J14,$F$3:$F$45,N14),VLOOKUP($J14,$A:$F,5,))),"") คัดลอกลงครับ

ได้แล้วครับ ขอบคุณครับ

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

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

รบกวนแก้ไขให้อีกหน่อยนะครับ (ผมแนบไฟล์มาให้ดูด้วยครับ)

ต้องขอโทษด้วยครับที่ตอนแรกบอกว่า ใช้ได้แล้วนะครับ

ปรับเป็นที่
J14=IFERROR(IF(J$13="จำนวนเงิน",IFERROR(1/(1/SUMIFS($E$3:$E$45,$A$3:$A$45,$J14,$F$3:$F$45,$N14)),""),IF($K$11="ทั้งหมด",INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)))),"") คัดลอกไปทางขวาและลงล่างครับ

puriwutpokin wrote: Tue May 07, 2019 10:17 pm ปรับเป็นที่
J14=IFERROR(IF(J$13="จำนวนเงิน",IFERROR(1/(1/SUMIFS($E$3:$E$45,$A$3:$A$45,$J14,$F$3:$F$45,$N14)),""),IF($K$11="ทั้งหมด",INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/($F$3:$F$45=$K$11),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0)))),"") คัดลอกไปทางขวาและลงล่างครับ

ผลลัพธ์ ยังไม่ได้ครับ

เมื่อเลือก เงินสด
จะแสดงผลออกมา 153 บาท 3 ช่องเลยครับ
และเมื่อเลือกบัตรเครดิต ก็ออกมา 3 ช่องเหมือนกันครับ

J14
=IFERROR(IF((J$13="จำนวนเงิน"),IFERROR(1/(1/SUMIFS($E:$E,$A:$A,$J14,$B:$B,$K14,$C:$C,$L14,$F:$F,$N14)),""),INDEX($A$3:$F$99,AGGREGATE(15,6,ROW($A$1:$A$99)/(FREQUENCY(($B$3:$B$99>=$K$9)*($B$3:$B$99<=$K$10)*ISNUMBER(SEARCH(IF($K$11="ทั้งหมด","*",$K$11),$F$3:$F$99))*MATCH($A$3:$A$99&$F$3:$F$99,INDEX($A$3:$A$99&$F$3:$F$99,),),ROW($A$1:$A$99)-1)>0)-1,ROWS(J$13:J14)),MATCH(J$13,$A$2:$F$2,))),"")

ไม่ต้องกด Ctrl+Shift+Error แต่ยังคำนวณแบบ array

อีกตัวอย่างครับ
ที่J14=IFERROR(IF(J$13="จำนวนเงิน",IFERROR(1/(1/SUMIFS($E$3:$E$45,$A$3:$A$45,$J14,$F$3:$F$45,$N14,$B$3:$B$45,$K14)),""),INDEX($A$3:$F$45,AGGREGATE(15,6,(ROW($A$3:$A$45)-ROW($A$2))/($B$3:$B$45>=$K$9)/($B$3:$B$45<=$K$10)/IF($K$11="ทั้งหมด",$F$3:$F$45=$F$3:$F$45,INDEX($F$3:$F$45=$K$11,0))/(FREQUENCY(MATCH($A$3:$A$45&$F$3:$F$45,$A$3:$A$45&$F$3:$F$45,0),ROW($F$3:$F$45)-ROW($F$2))>0),ROWS(J$14:J14)),MATCH(J$13,$A$2:$F$2,0))),"") คัดลอกไปทางขวาและลงล่างครับ