สอบถามกการรวมข้อมูลและจำนวนแบบมีเงื่อนไข

[aniwat2011]

เรียนสอบถามผู้รู้ครับ

กรณีที่ 1 ต้องการหาจำนวน โดยนำข้อมูลจาก Sheet2 Sheet3 และ Sheet4 ที่มีข้อมูล (ชื่อผลไม้/รหัส/หน่วยนับ/ราคาต่อหน่วย) ตรงกับตารางที่ 1 ทุกเงื่อนไข โดยที่ข้อมูลใน Sheet2 Sheet3 และ Sheet4 มีรายการผลไม้ไม่ครบใน Sheet1 (แต่ไม่เกินตามตารางที่กำหนด) และเรียงลำดับไม่ตรงกับ Sheet1

กรณีที่ 2 ต้องการหาชื่อผลไม้ รหัส หน่วยนับ ราคาต่อหน่วย ที่ข้อมูลไม่ซ้ำกัน และรวมจำนวนของรายการที่ไม่ซ้ำ
โดยที่ข้อมูลใน Sheet2 Sheet3 และ Sheet4 มีรายการผลไม้ รหัส หน่วยนับ ราคาต่อหน่วยแตกต่างกันไป

ตัวอย่างและคำตอบ ตามไฟล์แนบครับ
ขอบคุณครับ

สอบถามสูตรการรวมตัวเลขแบบมีเงื่อนไข.xlsx

[aniwat2011]

ไฟล์ที่แนบครั้งแรกข้อมูลไม่ครบครับ ขอภัยด้วยครับ

สอบถามสูตรการรวมตัวเลขแบบมีเงื่อนไข.xlsx

[snasui]

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ G5 คีย์สูตรรวมค่าจากทุกชีต
   =SUMPRODUCT(SUMIFS(INDIRECT("'"&{"Sheet2","Sheet3","Sheet4"}&"'!G5:G10"),INDIRECT("'"&{"Sheet2","Sheet3","Sheet4"}&"'!D5:D10"),D5))
   Enter > Copy ลงด้านล่าง
2. ที่ D17 คีย์สูตรแสดงรหัสจากหลายชีตที่ไม่ซ้ำกัน
   =INDEX(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Sheet2!$D$15:$D$20,Sheet3!$D$14:$D$19,Sheet4!$D$14:$D$19),",","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"),B17)
   Enter > Copy ลงด้านล่าง
3. ที่ I17 คีย์สูตรแสดงรายชื่อชีตที่พบรหัสตามข้อ 2
   =INDEX({"Sheet2","Sheet3","Sheet4"},MATCH(TRUE,COUNTIFS(INDIRECT("'"&{"Sheet2","Sheet3","Sheet4"}&"'!D14:D1000"),$D17)>0,0))
   Enter > Copy ลงด้านล่าง
4. ที่ C17 คีย์สูตรแสดงรายการที่เกี่ยวข้อง
   =INDEX(INDIRECT("'"&$I17&"'!C14:H1000"),MATCH($D17,INDIRECT("'"&$I17&"'!D14:D1000"),0),COLUMNS($C16:C16))
   Enter > Copy ลงด้านล่าง > คัดลอกไปใช้ที่คอลัมน์ E:F
5. ที่ G17 คีย์สูตรรวมยอดจากหลายชีต
   =SUMPRODUCT(SUMIFS(INDIRECT("'"&{"Sheet2","Sheet3","Sheet4"}&"'!G14:G1000"),INDIRECT("'"&{"Sheet2","Sheet3","Sheet4"}&"'!D14:D1000"),D17))
   Enter > Copy ลงด้านล่าง

[aniwat2011]

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

[aniwat2011]

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

ภาพ2.jpg

สุดยอดมากครับ

[aniwat2011]

สูตรข้างบนสามารถใช้ได้กับ Excel 2010 หรือ เวอร์ชั่นต่ำกว่า 2019 ไหมครับอาจารย์

[snasui]

ไม่สามารถใช้กับ Version ที่น้อยกว่า 2019 ได้ครับ

[aniwat2011]

อาจารย์เมตตาให้สูตรที่ใช้ร่วมกันได้กับ Excel2010 -2019 ด้วยนะครับ
ขอบคุณครับ

[snasui]

ที่ D17 คีย์สูตรแสดงรหัสจากหลายชีตที่ไม่ซ้ำกัน
=INDEX(FILTERXML("<t><s>"&SUBSTITUTE(TEXTJOIN(",",TRUE,Sheet2!$D$15:$D$20,Sheet3!$D$14:$D$19,Sheet4!$D$14:$D$19),",","</s><s>")&"</s></t>","//s[not(preceding::*=.)]"),B17)
Enter > Copy ลงด้านล่าง

ตัวอย่างสูตรสำหรับแทนสูตรเฉพาะที่ยกมาด้านบน ส่วนสูตรอื่น ๆ ใช้ของเดิมครับ

1. เซลล์ P16, Q16, R16, R17, R18, R19, R20 คีย์ ListCode, ListSheet, UniqueCount:, Total:, Sheet2, Sheet3, Sheet4 เพื่อเป็นชื่อคอลัมน์หรือชื่อรายการตามลำดับ
2. ที่ S17 คีย์สูตรเพื่อนับจำนวนของทุกชีต
   =SUMPRODUCT(COUNTIFS(INDIRECT("'"&R18:R20&"'!D14:D100"),"<>"))
   Enter
3. ที่ S18 คีย์สูตรแสดงลำดับเริ่มของชีตนั้น ๆ ที่นับต่อมาจากชีตก่อนหน้า
   =SUMPRODUCT(COUNTIFS(INDIRECT("'"&R$18:R18&"'!D14:D100"),"<>"))-SUMPRODUCT(COUNTIFS(INDIRECT("'"&R18&"'!D14:D100"),"<>"))+1
   Enter > Copy ลงด้านล่าง
4. ที่ Q17 คีย์สูตรเพื่อแสดงรายชื่อชีต
   =IF(ROWS(Q$17:Q17)>$S$17,"",LOOKUP(ROWS(Q$17:Q17),$S$18:$S$20,$R$18:$R$20))
   Enter > Copy ลงด้านล่าง
5. ที่ P17 คีย์สูตรเพื่อแสดงรหัสของทุกชีต
   =IF(ROWS(Q$17:Q17)>$S$17,"",INDIRECT("'"&Q17&"'!R"&13+COUNTIFS(Q$17:Q17,Q17)&"C4",0))
   Enter > Copy ลงด้านล่าง
6. ที่ S16 คีย์สูตรเพื่อนับค่าที่ไม่ซ้ำ
   =COUNT(AGGREGATE(15,6,ROW($P$17:$P$27)/ISNUMBER(--RIGHT($P$17:$P$27))/(0<FREQUENCY(MATCH($P$17:$P$27,$P$17:$P$27,0),ROW($P$17:$P$27)-ROW($P$17)+1)),INDEX(ROW(INDIRECT("1:"&S17)),0)))
   Enter > Copy ลงด้านล่าง
7. ที่ D17 คีย์สูตรเพื่อแสดงรายการรหัสเฉพาะที่ไม่ซ้ำ
   =IF(B17>$S$16,"",INDEX(P:P,AGGREGATE(15,6,ROW($P$17:$P$27)/ISNUMBER(--RIGHT($P$17:$P$27))/(0<FREQUENCY(MATCH($P$17:$P$27,$P$17:$P$27,0),ROW($P$17:$P$27)-ROW($P$17)+1)),B17)))
   Enter > Copy ลงด้านล่าง

[aniwat2011]

ขอบคุณอาจารย์มาก ๆ ครับ