Snasui Excel Forum

ผมมีข้อมูลอยู่ 3 sheet โดย
Sheet1 เป็นไฟล์ตั้งต้นที่มีการเปลี่ยนแปลงข้อมูลอยู่เสมอ โดย user คีย์ข้อมูลลงไป

Sheet2 เป็นลิสต์รายการสินค้าที่มีทั้งหมดและรหัสสินค้าเพื่อไว้อ้างอิงใน sheet3

Sheet3 คือตารางสรุปข้อมูลที่ได้จาก sheet1 และsheet2 โดยต้องการให้ดึงจำนวนสินค้าแบบแยก pack size และวันที่เริ่มผลิต จาก sheet1 และดึงรหัสสินค้าจาก sheet2 ครับ

คำถามคือ ผมจะทำอย่างไร หากมีการเปลี่ยนแปลงข้อมูลใน sheet1 และต้องการให้ sheet3 เปลี่ยนแปลงไปด้วย หรือผมควรจะจัดตารางข้อมูลใน sheet1,2 ให้เหมาะสมก่อนดึงมาครับ รบกวนผู้รู้ด้วยครับ

แบบนี้ใช่ตามที่ต้องการหรือเปล่าครับ ?

หมายเหตุ : ผมใช้ Array Formula ไม่เป็น ก็เลยเขียนได้แต่แบบนี้
(หากใช้ได้ สูตรอาจจะกระชับมากกว่านี้ครับ)

อีกตัวอย่างสูตรที่ Sheet3 ครับ

1. เซลล์ A3 คีย์สูตร
   =Sheet2!C3
   Enter > Copy ลงด้านล่าง
2. เซลล์ B3 คีย์สูตร
   =INDEX(Sheet1!$C$3:$D$8,MATCH(INDEX(Sheet2!$A$3:$A$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$B$3:$B$8,0),MATCH(INDEX(Sheet2!$B$3:$B$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$C$2:$D$2,0))
   Enter > Copy ลงด้านล่าง
3. เซลล์ C3 คีย์สูตร
   =INDEX(Sheet1!$A$3:$A$8,MATCH(INDEX(Sheet2!$A$3:$A$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$B$3:$B$8,0))
   Enter > Copy ลงด้านล่าง

menem wrote:แบบนี้ใช่ตามที่ต้องการหรือเปล่าครับ ?

หมายเหตุ : ผมใช้ Array Formula ไม่เป็น ก็เลยเขียนได้แต่แบบนี้
(หากใช้ได้ สูตรอาจจะกระชับมากกว่านี้ครับ)

ขอบคุณครับ ตรงกับความต้องการเลย เดี๋ยวจะเอาไปปรับใช้กับไฟล์จริงดู แต่อยากจะขอคอนเซปของสูตรที่ใส่มาหน่อยได้ไหมครับ มีแนวคิดอย่างไร

snasui wrote: อีกตัวอย่างสูตรที่ Sheet3 ครับ

1. เซลล์ A3 คีย์สูตร
   =Sheet2!C3
   Enter > Copy ลงด้านล่าง
2. เซลล์ B3 คีย์สูตร
   =INDEX(Sheet1!$C$3:$D$8,MATCH(INDEX(Sheet2!$A$3:$A$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$B$3:$B$8,0),MATCH(INDEX(Sheet2!$B$3:$B$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$C$2:$D$2,0))
   Enter > Copy ลงด้านล่าง
3. เซลล์ C3 คีย์สูตร
   =INDEX(Sheet1!$A$3:$A$8,MATCH(INDEX(Sheet2!$A$3:$A$8,MATCH($A3,Sheet2!$C$3:$C$8,0)),Sheet1!$B$3:$B$8,0))
   Enter > Copy ลงด้านล่าง

ขอบคุณมากๆครับ เดี๋ยวผมขอไปลองก่อนนะครับ

menem wrote:แบบนี้ใช่ตามที่ต้องการหรือเปล่าครับ ?

หมายเหตุ : ผมใช้ Array Formula ไม่เป็น ก็เลยเขียนได้แต่แบบนี้
(หากใช้ได้ สูตรอาจจะกระชับมากกว่านี้ครับ)

ผมลองเอาสูตรของคุณ menem ไปปรับใช้กับข้อมูลจริงพบว่าค่อนข้างโอเคครับ แต่ติดนิดเดียวที่ว่า ในไฟล์ของคุณ menem หากวันไหนมีผลิตสินค้าชนิดหนึ่งๆ เพียง pack size เดียว อีก pack size นึงที่ไม่ผลิตก็จะไม่โชว์บนตาราง แต่ไฟล์ของผมกลับโชว์สินค้าขึ้นมาโดยแสดงจำนวนเป็น 0 ครับ (จริงๆแล้วไม่ควรจะโชว์หากไม่มีผลิต pack size นั้นๆ) แบบนี้แล้วต้องปรับแก้ในสูตร cell ไหนครับ

เอาหลักการคิดของผมก่อนนะครับ
ผมถือเสมอว่า บรรทัดที่ 3 ต้องมีข้อมูล เพียงแต่ต้องหาให้เจอก่อนว่า คอลัมน์ไหนมีข้อมูลกันแน่
สูตร F2=IF(Sheet1!C3>0,"C",IF(Sheet1!D3>0,"D","Err"))

จากนั้นให้ถือว่าคอลัมน์เริ่มต้น คือค่าตาม F2 ( สูตรใน F3=F2 )
และกำหนดว่า บรรทัดเริ่มแรกที่มีข้อมูลคือ 3 ( สูตรใน E3=3 )

จากนั้นให้หาว่าบรรทัดถัดไปคืออะไร โดย
- ถ้า Column ก่อนหน้าเป็น C ให้เช็ค D บรรทัดเดียวกันว่า > 0 ถ้าใช่ เลขบรรทัดถัดไปเท่ากับบรรทัดเดิม
ถ้าไม่ ให้เลขบรรทัดถัดไปเท่ากับ + 1
- ถ้า Column ก่อนหน้าเป้น D ให้เลขบรรทัดถัดไปเท่ากับ + 1

และหาว่า Column ต่อไปคือ C หรือ D
- ถ้า Column ก่อนหน้าคือ C ให้ดูว่า D > 0 ถ้าใช่ ให้เป็น D , ถ้าไม่ใช่ ให้เป็น C [ ตรงจุดนี้เลขบรรทัดถัดไปจะเพิ่มไปพร้อม ๆ กัน ]
- ถ้า Column ก่อนหน้าคือ D ให้ดูว่า C ถัดไป > 0 ถ้าใช่ ให้เป็น C ถ้าไม่ ให้เป็น D

ย้ำว่า ทุกบรรทัดต้องมีค่าการผลิต ช่องใดช่องหนึ่งเสมอ

อีกแนวหนึ่งครับ ที่ Sheet3

1. E3 คีย์ (เพื่อแสดงวันที่ตามฐานข้อมูลใน Sheet1)
=Sheet1!A3
Enter -> คัดลอกลงล่างเผื่อเอาไว้

2. F3 คีย์ (เพื่อนับว่าแต่ละวันใน Sheet1 มีการใส่จำนวนในคอลัมน์ C และ D กี่ช่อง)
=COUNTIF(Sheet1!C3:D3,">"&0)
Enter -> คัดลอกลงล่างเผื่อเอาไว้

3. G3 คีย์ (เพื่อระบุว่าวันที่นั้นๆ จะให้ลงข้อมูลในแถวไหน)
=SUM(F$3:F3)-F3+1
Enter -> คัดลอกลงล่างเผื่อเอาไว้

4. F1 คีย์ (เพื่อดูว่า เมื่อเอามาเรียงแล้วจะมีกี่แถว)
=SUM(F3:F500)

5. C3 คีย์
=IF(ROWS(C$3:C3)>$F$1,"",LOOKUP(ROWS(C$3:C3),$G$3:$G$10,$E$3:$E$10))
Enter -> คัดลอกลงจนเห็นค่าว่าง

6. B3 คีย์
=IF(C3="","",INDEX(Sheet1!$C$3:$D$8,MATCH(C3,Sheet1!$A$3:$A$8,0),MATCH(TRUE,INDEX((INDEX(Sheet1!$C$3:$D$8,MATCH(C3,Sheet1!$A$3:$A$8,0),0)>0),0),0)+COUNTIF(C$3:C3,C3)-1))
Enter -> คัดลอกลงจนเห็นค่าว่าง

7. A3 คีย์
=IF(C3="","",SUMIFS(Sheet2!$C$3:$C$8,Sheet2!$A$3:$A$8,INDEX(Sheet1!$B$3:$B$8,MATCH(C3,Sheet1!$A$3:$A$8,0)),Sheet2!$B$3:$B$8,INDEX(Sheet1!$C$2:$D$2,MATCH(TRUE,INDEX((INDEX(Sheet1!$C$3:$D$8,MATCH(C3,Sheet1!$A$3:$A$8,0),0)>0),0),0)+COUNTIF(C$3:C3,C3)-1)))
Enter -> คัดลอกลงจนเห็นค่าว่าง

>>เวลาเอาไปใช้ก็ปรับช่วงอ้างอิงในสูตร 5 ให้สอดคล้องกับช่วงสูตรในคอลัมน์ E, G
>>และปรับสูตร 6, 7 ให้สอดคล้องกับข้อมูลจริงใน Sheet1 และ Sheet2 ครับ