Snasui Excel Forum

รบกวนท่านผู้รู้แนะนำวิธีการด้วยครับว่า excel สามารถทำให้แสดงผลอัตโนมัติตามภาพนี้ได้ไหม? ตามรูปในส่วนที่ 1 ผมจะมีฐานข้อมูลแสดงว่ารหัสของสินค้าที่เป็นชุดเซ็ทประกอบด้วยโค้ดย่อยอะไรบ้าง จำนวนเท่าไหร่
ส่วนที่ 2 ผมจะมีข้อมูลดิบสรุปประจำเดือนว่าสินค้าที่เป็นชุดเซ็ทขายออกไปได้จำนวนเท่าไหร่ในเดือนนั้นๆ
ส่วนที่ 3 ผมอยากใส่สูตรเพื่อแปลงโค้ดเซ็ทมาเป็นโค้ดย่อย พร้อมทั้งแสดงจำนวนและเดือนปีที่ขายสินค้าครับ

ขอบคุณในความเมตตาของทุกท่านล่วงหน้าครับ

ในส่วนของข้อมูลดิบ ทราบได้อย่างไรว่าบรรทัดไหน คือโค๊ดย่อยอะไรครับ ?

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

ถ้าเช่นนั้นลองดูในไฟล์ที่ผมเขียนนะครับว่าใช่ตามที่ต้องการหรือไม่
หลัก ๆ คือ ใช้วิธีการนับครั้งของการเกิดในข้อมูลดิบ x จำนวนย่อยของฐานข้อมูลหลัก

อีกตัวอย่างครับ

1. เซลล์ N2:R2 คีย์ MainCode , DB, RawData, Lines, StartLine ตามลำดับ
2. เซลล์ N3 คีย์สูตร
   =IFERROR(INDEX($E$3:$E$16,SMALL(IF(FREQUENCY(MATCH($E$3:$E$16,$E$3:$E$16,0),ROW($E$3:$E$16)-ROW($E$3)+1),ROW($E$3:$E$16)-ROW($E$3)+1),ROWS(N$3:N3))),"")
   Ctrl+Shift+Enter > Copy ลงด้านล่าง กรุณาอ่าน Note อ่านล่างอย่างละเอียดครับ
3. เซลล์ O3 คีย์สูตร
   =IF(N(N3),COUNTIF($A$3:$A$19,N3),"")
   Enter > Copy ลงด้านล่าง
4. เซลล์ P3 คีย์สูตร
   =IF(N(N3),COUNTIF($E$3:$E$16,N3),"")
   Enter > Copy ลงด้านล่าง
5. เซลล์ Q3 คีย์สูตร
   =O3*P3
   Enter > Copy ลงด้านล่างถึง Q7
6. เซลล์ Q8 คีย์สูตร
   =SUM(Q3:Q7)
   Enter
7. เซลล์ R3 คีย์สูตร
   =SUM(Q$3:Q3)-Q3+1
   Enter > Copy ลงด้านล่างถึง R7
8. เซลล์ I3 คีย์สูตร
   =IF(ROWS(I$3:I3)>$Q$8,"",LOOKUP(ROWS(I$3:I3),$R$3:$R$7,$N$3:$N$7))
   Enter > Copy ลงด้านล่าง
9. เซลล์ J3 คีย์สูตร
   =IF(N($I3),OFFSET($B$3,MATCH($I3,$A$3:$A$19,0)-1+MOD((COUNTIF($I$3:$I3,$I3)-1),LOOKUP(ROWS(J$3:J3),$R$3:$R$7,$O$3:$O$7)),0),"")
   Enter > Copy ลงด้านล่าง
10. เซลล์ K3 คีย์สูตร
    =IF(N($I3),OFFSET(F$3,MATCH($I3,$E$3:$E$16,0)-1+INT((COUNTIF($I$3:$I3,$I3)-1)/LOOKUP(ROWS(K$3:K3),$R$3:$R$7,$O$3:$O$7)),0),"")
    Enter > Copy ไป L3 แล้ว Copy ลงด้านล่าง

!

Note: Ctrl+Shift+Enter เป็นการสร้างสูตร Array โดยสามารถเลือกดำเนินการตามข้อ 1 หรือ 2 ด้านล่าง กรณีคีย์สูตรเอง เมื่อคีย์สูตรแล้ว แทนที่จะกด Enter ให้กดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วกด Enter เพื่อสร้างเป็นสูตร Array กรณี Copy สูตรไปวางให้กดแป้น F2 เพื่อทำการ Edit Cell นั้นก่อน จากนั้นกดแป้น Ctrl และ Shift พร้อมกันค้างไว้แล้วตามด้วย Enter เพื่อสร้างเป็นสูตร Array หากกดแป้นถูกต้องสูตรนั้นจะมีเครื่องหมายปีกกาครอบ เช่น {=YourFormulas(...)} ปีกกานี้ไม่สามารถคีย์เข้าไปเอง จะต้องมาด้วยการกดแป้น Ctrl+Shift+Enter เท่านั้น ถ้ายังไม่เห็นปีกกาครอบสูตรแสดงว่ากดแป้นให้รับสูตรไม่ถูกต้อง การแก้ไขเปลี่ยนแปลงสูตร Array จะต้องกดแป้นให้รับสูตรด้วย Ctrl+Shift+Enter ทุกครั้ง

ขอบคุณทุกท่านมากครับสำหรับการช่วยเหลือชี้แนะแนวทาง

ผมลองทำตามขั้นตอนของอาจารย์ Santipong แล้วแต่ยังไม่สำเร็จครับ
ไม่แน่ใจว่าผิดพลาดตรงไหนบ้าง ผมแนบไฟล์มาให้ดูด้วยครับ

วิธีการของคุณ menem เยี่ยมมากเลยครับ ขอบคุณมากครับ

Sitthichok wrote:ขอบคุณทุกท่านมากครับสำหรับการช่วยเหลือชี้แนะแนวทาง

ผมลองทำตามขั้นตอนของอาจารย์ Santipong แล้วแต่ยังไม่สำเร็จครับ
ไม่แน่ใจว่าผิดพลาดตรงไหนบ้าง ผมแนบไฟล์มาให้ดูด้วยครับ

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

ขอบคุณอาจารย์ Santipong มากเลยครับ