Excel Forum

ขอบคุณค่ะ ได้ผลลัพธ์ตามที่ต้องการค่ะ
ใน col D ของ sheet2 จากข้อมูลใน row2-6 จะเป็นข้อมูลของคนคนเดียวกัน แล้วใน col I จะเป็นการรันจำนวนสิ่งของว่าแต่ละคนมีจำนวนเท่าไร ในแต่ละวันที่ ใน col K จะเป็นการลงวันที่ที่นำสิ่งของไปใช้ ซึ่งต้องการทราบจำนวนสิ่งของว่าเหลือเท่าไรค่ะ

อย่างเช่น name ที่ชื่อ Nuta ในวันที่ 1/3/14 มีสิ่งของจำนวน 5 ชิ้น และมีการใช้ไปแล้ว 2 ชิ้น ซึ่งจำนวนสิ่งของจะเหลือ 3 ชิ้น คำตอบใน col D ของ sheet2 ก็จะแสดงค่าเท่ากับ 3 ค่ะ

ขอสอบถามเพิ่มเติมค่ะ จากสูตร LOOKUP(9.99999999999999E+307,Sheet1!$P$2:$P$1000) ค่า 9.99999999999999E+307 คือค่าอะไรค่ะ

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

สำหรับค่า 9.99999999999999e307 คือค่าที่ใหญ่ที่สุดที่สามารถคีย์ลงในเซลล์ใด ใช้กับ Lookup เพื่อหาตัวเลขสุดท้ายในช่วงเซลล์ที่กำหนดครับ

ขอบคุณค่ะ ปรับสูตรเพื่อหาคำตอบได้ตามที่ต้องการแล้วค่ะ

จากครั้งที่แล้วที่ได้สอบถามไป ตอนนี้มีคำถามเพิ่มเติมค่ะ
1.ผลลัพธ์ที่ต้องการให้แสดงจะเป็นข้อมูลแบบใน sheet2 โดยมีเงื่อนไขว่าจากข้อมูลใน sheet1 ถ้าใน col K มีการกรอกข้อมูลยังไม่ครบตามจำนวนสิ่งของใน col I โดยจะแยกเป็นชื่อลูกค้าแต่ละคนใน col E และแต่ละรอบของวันที่เริ่มเก็บสินค้าใน col B และวันที่ใน col B นับจนถึงวันที่ปัจจุบันแล้วเกิน 335 วัน ให้นำมาแสดงใน sheet2
โดยคำตอบใน sheet2 ใน col A,B,C จะเป็นข้อมูลใน col E,C,D จาก sheet1 ตามลำดับ และใน col E จะเป็นข้อมูลใน col B จาก sheet1 และ col F จะเป็นข้อมูลใน col I จาก sheet1 ซึ่งคือจำนวนสิ่งของทั้งหมดที่มีการเก็บไว้ของลูกค้าแต่ละคนแต่รอบวันที่ของการเก็บสิ่งของ และถ้าลูกค้าคนไหนมีการนำสิ่งของออกมาใช้จะแสดงผลลัพธ์ใน col G,H โดย col G จะเป็นวันที่ที่มีการนำสิ่งของออกมาใช้ และ col H จะเป็นจำนวนของสิ่งของที่นำออกมาใช้โดยแยกเป็นแต่ละวันที่ของการนำสิ้งของออกมาใช้ ใน col I จาก sheet2 คือจำนวนสิ่งของที่เหลือของลูกค้าแต่ละคนในแต่ละรอบการเก็บสิ่งของแต่ละวันที่
ตัวอย่างเช่น ลูกค้าที่ชื่อ Nuta มีการเก็บสิ่งของทั้งหมด 3 รอบ คือ วันที่ 1/3/2014, วันที่ 4/3/2014 และวันที 14/7/14 โดยจำนวนสิ่งของที่มีการเก็บในแต่ละรอบมีจำนวน 3,2,2 ชิ้น ตามลำดับ ซึ่งสิ่งของในรอบที่ 1 มีการนำออกมาใช้ทั้งหมด 2 รอบ คือวันที่ 4/8/14 และ 15/4/15 โดยนำสิ่งของออกมาใช้รอบละ 1 ชิ้น ซึ่งในรอบแรกที่นำสิ่งของออกมาใช้ 1 ชิ้น ผลลัพธ์ใน col I จะแสดงเท่ากับ 2 และในรอบที่ 2 นำมาใช้อีก 1 ชิ้น ใน col I ก็จะแสดงผลลัพธ์เท่ากับ 1 และถ้าในรอบการเก็บสินค้านั้นมีการนำสินค้าออกมาใช้หมดแล้วจะไม่มีการแสดงผลลัพธ์ใน sheet2 ค่ะ
2.ใน sheet 3 ผลลัพธ์จะเหมือนกับใน sheet2 แต่จะเป็นการสรุปข้อมูลของลูกค้าแต่ละคน โดยการกรอกชื่อของลูกค้า(B2)หรือกรอกรหัสประจำตัวของลูกค้า(H2) และให้ดึงข้อมูลของลูกค้าคนนั้น ๆ ขึ้นมา ในข้อนี้สามารถใช้สูตรในการหาผลลัพธ์ได้หรือเปล่าค่ะ

ผมทำแบบใช้คอลัมน์ช่วย ลองแบบนี้ครับ

ที่ Sheet1
1. W4 คีย์
=LOOKUP(9.99999999999E+307,B$4:B4)
Enter -> คัดลอกลงล่าง

2. X4 คีย์
=LOOKUP(CHAR(255),D$4:D4)
Enter -> คัดลอกลงล่าง

ที่ Sheet2
3. K3 คีย์
=IFERROR(INDEX(Sheet1!$D$4:$D$25,SMALL(IF(FREQUENCY(IF(Sheet1!$D$4:$D$25<>"",MATCH(Sheet1!$D$4:$D$25,Sheet1!$D$4:$D$25,0)),ROW(Sheet1!$D$4:$D$25)-ROW(Sheet1!$D$4)+1),ROW(Sheet1!$D$4:$D$25)-ROW(Sheet1!$D$4)+1),ROWS(K$3:K3))),"")
กด Ctrl+Shift ค้างแล้ว Enter

4. L3 คีย์
=IF(K3="",0,SUMPRODUCT(--(NOW()-Sheet1!$W$4:$W$25>335),--(Sheet1!$B$4:$B$25+Sheet1!$K$4:$K$25<>0),--(Sheet1!$X$4:$X$25=K3)))
Enter

5. M3 คีย์
=SUM(L$3:L3)-L3+1
Enter

คัดลอกสูตร 3-5 ลงล่างจนเห็นว่าสูตรข้อ 3 แสดงค่าว่าง

6. K1 คีย์
=SUM(L:L)
Enter

7. C3 คีย์
=IF(ROWS($C$3:$C3)>$K$1,"",LOOKUP(ROWS($C$3:$C3),$M$3:$M$11,$K$3:$K$11))
Enter

8. A3 คีย์
=IF($C3="","",INDEX(Sheet1!$E$4:$E$25,MATCH($C3,Sheet1!$D$4:$D$25,0)))
Enter

9. B3 คีย์
=IF($C3="","",INDEX(Sheet1!$C$4:$C$25,MATCH($C3,Sheet1!$D$4:$D$25,0)))
Enter

10. E3 คีย์
=IF($C3="","",INDEX(Sheet1!$W$4:$W$25,SMALL(IF(Sheet1!$X$4:$X$25=$C3,IF(NOW()-Sheet1!$B$4:$B$25>335,IF(Sheet1!$B$4:$B$25+Sheet1!$K$4:$K$25>0,ROW(Sheet1!$K$4:$K$25)-ROW(Sheet1!$K$4)+1))),COUNTIF($C$3:$C3,$C3))))
กด Ctrl+Shift ค้างแล้ว Enter

11. G3 คีย์
=IF($C3="","",INDEX(Sheet1!$K$4:$K$25,SMALL(IF(Sheet1!$X$4:$X$25=$C3,IF(NOW()-Sheet1!$B$4:$B$25>335,IF(Sheet1!$B$4:$B$25+Sheet1!$K$4:$K$25>0,ROW(Sheet1!$K$4:$K$25)-ROW(Sheet1!$K$4)+1))),COUNTIF($C$3:$C3,$C3))))
กด Ctrl+Shift ค้างแล้ว Enter

คัดลอกสูตร 7-11 ลงล่างจนเห็นว่าสูตรแสดงค่าว่างครับ

ส่วนในคอลัมน์ F, H และ I นี่งงครับ ไม่รู้คิดยังไง

ขอบคุณ คุณ DhitiBank ค่ะ
ใน col F จะเป็นจำนวนรวมของสิ่งของที่มีการฝากเอาไว้ เช่น ลูกค้าชื่อ Nuta วันที่ 1/3/14 ใน col I ใน sheet1 ในเซลล์ I4,I5,I6,I7 แสดงจำนวน 1,2,3,4 แสดงว่า Nuta มีการฝากสิ่งของไว้จำนวน 3 ชิ้น ใน col F ก็จะแสดงค่าเท่ากับ 4 และในวันที่ 4/3/14 ใน col I ใน sheet1 ในเซลล์ I8,I9 แสดงจำนวน 1,2,3 แสดงว่า Nuta มีการฝากสิ่งของไว้จำนวน 2 ชิ้น ใน col F ก็จะแสดงค่าเท่ากับ 2 (โดยตัวเลขใน col I จะหมายถึงรหัสของสิ่งของชิ้นนั้น)
ใน col H จะเป็นจำนวนรวมของสิ่งของที่มีการนำเอาออกมาใช้ เช่น ลูกค้าชื่อ Nuta มีการนำสิ่งของที่เก็บไว้ในรอบวันที่ 1/3/14 มาใช้ในวันที่ 4/8/14 จำนวน 2 ชิ้น ใน col H ก็จะแสดงค่าเท่ากับ 2 ซึ่งถ้านำสิ่งของมาใช้ในวันเดียวกันข้อมูลจะขึ้นแค่บรรทัดเดียว
ใน col I จะเป็นจำนวนสิ่งของที่เหลือ เช่น ลูกค้าชื่อ Nuta ในวันที่ 1/3/14 มีการฝากสิ่งของไว้จำนวน 4 ชิ้น และนำสิ่งของมาใช้ในวันที่ 4/8/14 จำนวน 2 ชิ้น ดังนั้น Nuta สิ่งของเหลืออยู่ 2 ชิ้น ซึ่ง col I ก็จะแสดงค่าเท่ากับ 2 และหลังจากนั้น Nuta มีการนำของมาใช้อีกในวันที่ 5/7/15 จำนวน 1 ชิ้น และถ้านำสิ่งของมาใช้หมดแล้ว ใน sheet2 จะไม่มีการนำข้อมูลมาแสดงอีก โดยผลลัพธ์จะเป็นแบบตารางใน sheet2 col P - col X ค่ะ

จากไฟล์ตัวอย่างล่าสุดนะครับ ถ้าหากผมใส่ข้อมูลต่อ (ดูเฉพาะ Nuta นะครับ) จะได้แบบรูปหรือเปล่าครับ ส่วนสีเขียว

ปรับสูตรใหม่ในส่วนของคอลัมน์ E, G, L ครับ ตามรูป

L3
=IF(K3="",0,SUM(--(FREQUENCY(IF(Sheet1!$X$4:$X$26=K3,IF(NOW()-Sheet1!$W$4:$W$26>335,IF(Sheet1!$B$4:$B$26+Sheet1!$K$4:$K$26>0,MATCH(Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,0)))),ROW(Sheet1!$X$4:$X$26)-ROW(Sheet1!$X$4)+1)>0)))
Ctrl+Shift+Enter -> คัดลอกลงล่าง

E3
=IF(C3="","",INDEX(Sheet1!$W$4:$W$26,SMALL(IF(FREQUENCY(IF(Sheet1!$X$4:$X$26=C3,IF(Sheet1!$B$4:$B$26+Sheet1!$K$4:$K$26>0,MATCH(Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,0))),ROW(Sheet1!$X$4:$X$26)-ROW(Sheet1!$X$4)+1),ROW(Sheet1!$X$4:$X$26)-ROW(Sheet1!$X$4)+1),COUNTIF(C$3:C3,C3))))
Ctrl+Shift+Enter -> คัดลอกลงล่าง

G3
=IF(C3="","",INDEX(Sheet1!$K$4:$K$26,SMALL(IF(FREQUENCY(IF(Sheet1!$X$4:$X$26=C3,IF(Sheet1!$B$4:$B$26+Sheet1!$K$4:$K$26>0,MATCH(Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,Sheet1!$W$4:$W$26&Sheet1!$X$4:$X$26&Sheet1!$K$4:$K$26,0))),ROW(Sheet1!$X$4:$X$26)-ROW(Sheet1!$X$4)+1),ROW(Sheet1!$X$4:$X$26)-ROW(Sheet1!$X$4)+1),COUNTIF(C$3:C3,C3))))
Ctrl+Shift+Enter -> คัดลอกลงล่าง

เอาทีละขั้นนะครับ ทดสอบว่าผมเข้าใจถูก ลองดูตามรูปว่าแสดงผลขาดเกินหรือไม่ (อิงตามตัวอย่างไฟล์ล่าสุด) หากถูกต้องแล้วเหลือการใส่ Balance รอคำตอบจากคุณ Nutta อีกทีเรื่องรูปที่ผมถามในโพสต์บนครับ

ผลลัพธ์ใน sheet2 ที่คุณ DhitiBank ปรับสูตรให้ใหม่ ได้ผลตามที่ต้องการค่ะ

และในส่วนของช่อง balance จากรูป col X ในส่วนสีเขียว ต้องแสดงค่าเป็น 2 ทั้งสองช่องค่ะ เพราะจะไม่นำสิ่งของที่เก็บมาจากคนละรอบวันที่มารวมกันค่ะ จะรวมกันเฉพาะที่เก็บมาจากรอบวันที่เดียวกันค่ะ

ลองแบบนี้ครับ

F3 คีย์
=IF($C3="","",IF(COUNTIFS(E$3:E3,E3,C$3:C3,C3)=1,COUNTIFS(Sheet1!$W$4:$W$26,E3,Sheet1!$X$4:$X$26,$C3,Sheet1!$I$4:$I$26,">0"),0))
Enter

H3 คีย์
=IF($C3="","",IF(COUNTIF(G$3:G3,G3)=1,COUNTIFS(Sheet1!$K$4:$K$26,G3,Sheet1!$X$4:$X$26,$C3,Sheet1!$I$4:$I$26,">0"),0))
Enter

I3 คีย์
=IF(C3="","",SUMIFS(F$3:F3,C$3:C3,C3,E$3:E3,E3)-SUMIFS(H$3:H3,C$3:C3,C3,E$3:E3,E3))
Enter

แล้วคัดลอกสูตรทั้งหมดลงล่างครับ

ขอบคุณค่ะ คุณ DhitiBank
แต่ติดปัญหาอีกนิดนึงค่ะ คือ ถ้าลูกค้านำสิ่งของที่มาฝากไว้ไปใช้หมดแล้วในรอบวันที่นั้นๆ จะไม่มีการแสดงผลลัพธ์ใน sheet2 ค่ะ เช่น ถ้า Nuta นำของที่เก็บในวันที่ 1/3/2014 ไปใช้หมดแล้ว ใน sheet2 จะไม่มีข้อมูลของ Nuta ในวันที่ 1/3/2014 มาแสดงอีกค่ะ ตามไฟล์แนบใน sheet2 col P - col X

^ ^¡

มีเงื่อนไขอื่นอีกไหมครับ (รวมถึงกรณีอื่นๆ ที่อาจจะเกิดขึ้นได้) วันนี้ผมอยู่ข้างนอก ได้เข้าบ้านสักสามทุ่ม เดี๋ยวดูให้นะครับ

คงต้องเพิ่มคอลัมน์ช่วยใน Sheet1 อีก และปรับสูตรในคอลัมน์ E, G, L ใน Sheet2 ครับ ดังนี้
Sheet1
Y4 คีย์
=--(COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4,$I$4:$I$26,">0")<>COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4,$K$4:$K$26,">0"))
Enter -> คัดลอกลงล่าง

Sheet2
L3 E3 G3 ทั้ง 3 สูตรต้องกด Ctrl+Shift+Enter เพื่อรับสูตร -> คัดลอกลงล่างครับ

จริงๆ แล้วผมไม่อยากเพิ่มคอลัมน์ช่วยมากเกินไปและไม่อยากให้สูตรอาร์เรย์มันยาวมาก เพราะหากข้อมูลเริ่มเยอะ จะทำให้คำนวณช้ามากๆ แต่นึกวิธีอื่นไม่ออกจริงๆ ครับ ถ้าหากออกแบบตารางฐานข้อมูลใน Sheet1 ให้มีลักษณะเป็น database มากขึ้นได้ ผมว่าน่าจะใช้สูตรง่ายขึ้นนะครับ ผมบอกแนวคิดและเงื่อนไขเอาไว้ดีกว่า เผื่ออาจารย์และเพื่อนๆ ท่านอื่นจะเข้ามาช่วยแนะนำให้รวบรัดขึ้นครับ เพราะหากจะทำตามนี้ คุณ Nutta ก็ต้องมานั่งแก้สูตรหากข้อมูลใน Sheet1 มีแถวมากขึ้นเรื่อยๆ (ต้องแก้ในสูตรทุกสูตรที่มีช่วงอ้างอิงแถว 4:26 และต้องเพิ่มคอลัมน์ช่วยใน Sheet1 ตามข้อมูลที่มากขึ้น) แต่หากจะใส่สูตรเผื่อเอาไว้ก่อนมากๆ ก็ไม่ดีแน่ เพราะสูตรอาร์เรย์ยิ่งเยอะก็จะยิ่งช้าครับ
////เงื่อนไขรายการใน Sheet1 ที่จะเอามาแสดง////
1. Fz.date (column B) ห่างจากวันที่ปัจจุบันเกิน 335 วัน ก็ให้เอา OPU (column D) นั้นมาแสดง: ผมเลยทำคอลัมน์ W ใน Sheet1 เพื่อให้คำนวณระยะห่างง่ายๆ (คอลัมน์ X ก็ด้วย เพื่ออ้างอิง OPU ง่ายๆ)
2. หาก OPU มี Fz.date หลายวัน ที่ห่างเกิน 335 วันก็ให้แสดงทุกวันเลย
3. และแต่ละ OPU แต่ละ Fz.date หากมี Tw.date (column K) ก็ให้แสดงวันที่ Tw.date ด้วยโดยแสดงทุกวันและไม่เอาซ้ำ
4. แต่ละ OPU ในแต่ละ Fz.date หากถูกเบิกสินค้าออกไปหมดแล้ว คือมี Tw.date กำกับทุกบรรทัด ก็ไม่ต้องเอามาแสดง: ผมเลยทำคอลัมน์ Y ใน Sheet1 เพื่อนับว่า OPU วันไหนถูกเบิกเอาไปใช้หมดแล้ว 0 คือไม่ต้องเอามาแสดง

เงื่อนไขทั้งหมดจับยัดเข้าไปในสูตร Sheet2 ทั้ง 3 สูตรดังข้างต้น ตรงที่ IF เยอะๆ ที่อยู่ในสูตร Frequency ครับ

ขอบคุณคุณ DhitiBank ค่ะ

รบกวนอีกครั้งค่ะ
พอดีเพิ่งกลับมาทำงานและเพิ่งได้นำสูตรไปใช้ในไฟล์ที่มีข้อมูลจริงแล้วติดปัญหาค่ะ
คือถ้าใน col K ของ sheet1 ที่จะต้องกรอกวันที่ที่นำสิ่งของไปใช้ แล้วสิ่งของชิ้นแรกที่มีการเก็บไว้ยังไม่ได้ถูกนำไปใช้แต่นำสิ่งของชิ้นอื่นไปใช้ก่อน เวลาแสดงผลลัพธ์ใน sheet2 ข้อมูลจะขึ้นมาสองบรรทัดค่ะ ตามที่ hilight สีเหลืองไว้ในไฟล์แนบค่ะ แต่ผลลัพธ์ที่ต้องการคือจะต้องแสดงข้อมูลในบรรทัดเดียวกันค่ะ

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

ปรับสูตรที่ Sheet1 เซลล์ Y4 เป็นด้านล่าง (ส่วนสีแดงคือที่เพิ่มเข้ามาครับ)

=IF(AND(COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4)<>COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4,$K$4:$K$26,"="),K4=0),0,--(COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4,$I$4:$I$26,">0")<>COUNTIFS($X$4:$X$26,X4,$W$4:$W$26,W4,$K$4:$K$26,">0")))

Enter -> คัดลอกลงล่างครับ

ขอบคุณ คุณ DhitiBank มากๆค่ะ ^^