:D snasui.com ยินดีต้อนรับ :D
ยินดีต้อนรับสู่กระดานถามตอบ Excel and VBA และอื่น ๆ ที่เป็นมิตรกับทุกท่าน มีไฟล์แนบมหาศาล ช่วยให้ท่านค้นหาและติดตามศึกษาได้โดยง่าย :thup: สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ :arrow: ระบุ Version ของ Excel
:!: โปรดทราบ :!:
  1. กรุณาอ่านกฎการใช้บอร์ด (Forum rules) ในตำแหน่งด้านบนของแต่ละบอร์ด
  2. การสมัครสมาชิก การ Login การกู้คืนรหัสผ่าน
    1. สมัครสมาชิกดูขั้นตอนได้ที่ :arrow: สมัครสมาชิก
    2. Login เข้าระบบโดยคลิกปุ่ม Login ตรงมุมขวาบนของหน้านี้ :roll:
    3. การ Login ผ่าน Facebook ดูวิธีที่ :arrow: Login ผ่าน Facebook
    4. ลืมรหัสผ่านสามารถรับรหัสใหม่ได้ที่ :arrow: Reset รหัสผ่าน
  3. มีปัญหาการใช้งาน แจ้งผู้ดูแลระบบได้ที่ :arrow: ติดต่อผู้ดูแลระบบ
  4. กำหนดการตั้งค่าส่วนตัว เช่นตั้งค่าภาษาเป็นไทยหรืออังกฤษดูได้ที่ :arrow: ตั้งค่าส่วนตัว
  5. การตั้งและตอบกระทู้ดูได้ที่ :arrow: วิธีการตั้งและตอบกระทู้
  6. การจัดรูปแบบตัวอักษรด้วย bbcode ในช่องแสดงความคิดเห็นดูได้ที่ :arrow: จัดรูปแบบตัวอักษร
  7. กำหนดขนาดตัวอักษรใน Browser ดูได้ที่ :arrow: กำหนดขนาดตัวอักษรใน Browser

เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

ฟอรัมถาม-ตอบปัญหาการใช้งาน MS Excel and VBA
Forum rules
  1. ไม่อนุญาตให้ใช้ภาษาแชทในการถามและตอบปัญหา ไม่ใช้คำว่า "คับ" หรือ "อ่ะครับ" แทนคำว่า "ครับ" ไม่ใช้คำว่า "เด๋ว" แทนคำว่า "เดี๋ยว" เป็นต้น เนื่องจากเมื่อแปลเป็นภาษาต่างประเทศแล้วจะให้ความหมายผิดไปจากที่ควรจะเป็น
  2. ห้ามถามโดยระบุชื่อผู้ตอบและต้องตั้งชื่อกระทู้ให้สื่อถึงปัญหาที่จะถาม ไม่ตั้งชื่อว่า ช่วยด้วยครับ, มีปัญหามาปรึกษาครับ เป็นต้น
  3. กรุณาอธิบายปัญหาและระบุคำตอบที่ต้องการมาในกระทู้ด้วยเสมอถึงแม้จะอธิบายไว้ในไฟล์แนบแล้วก็ตาม ทั้งนี้เพื่ออำนวยความสะดวกแก่เพื่อนสมาชิกในการค้นหาข้อมูล
  4. กรุณาแนบไฟล์ตัวอย่างพร้อมแสดงคำตอบที่ถูกต้องมาในไฟล์ด้วยเพื่อให้ง่ายต่อการทำความเข้าใจและสะดวกต่อการตอบคำถาม (ขนาดไฟล์ไม่เกิน 500Kb ขนาดภาพไม่เกิน 800*600 Pixel) ไม่แนบเป็น Link มาจากแหล่งอื่นที่อาจจะถูกลบทิ้งไปโดยต้นทางในภายหลัง นอกจากนี้ไม่ควรแนบไฟล์ที่มีข้อมูลสำคัญอันก่อให้เกิดความเสียหายกับตนเองและผู้อื่น
  5. กรณีเป็นคำถามเกี่ยวกับ Programming เช่น VBA, VB.Net, C#, SQL ฯลฯ ต้องลองเขียนมาเองก่อนเสมอ ถามเฉพาะที่ติดปัญหา ระบุ Module, Procedure ที่ติดปัญหาให้ชัดเจน กรุณาโพสต์ Code ให้แสดงเป็น Code คือเปิดด้วย [code] และปิดด้วย [/code] ตัวอย่างเช่น [code]dim r as range[/code] เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)
  6. กรุณาแจ้งผลการใช้งานเมื่อได้รับคำตอบว่าตรงกับความต้องการหรือไม่
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#21

Post by Nutta »

ขอบคุณค่ะ ได้ผลลัพธ์ตามที่ต้องการค่ะ
ใน 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 คือค่าอะไรค่ะ :)
User avatar
snasui
Site Admin
Site Admin
Posts: 30736
Joined: Sun Jan 24, 2010 12:33 pm
Location: Songkhla, Thailand
Excel Ver: 2010, 365
Contact:

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#22

Post by snasui »

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

สำหรับค่า 9.99999999999999e307 คือค่าที่ใหญ่ที่สุดที่สามารถคีย์ลงในเซลล์ใด ใช้กับ Lookup เพื่อหาตัวเลขสุดท้ายในช่วงเซลล์ที่กำหนดครับ
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#23

Post by Nutta »

ขอบคุณค่ะ ปรับสูตรเพื่อหาคำตอบได้ตามที่ต้องการแล้วค่ะ :D
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#24

Post by Nutta »

จากครั้งที่แล้วที่ได้สอบถามไป ตอนนี้มีคำถามเพิ่มเติมค่ะ
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) และให้ดึงข้อมูลของลูกค้าคนนั้น ๆ ขึ้นมา ในข้อนี้สามารถใช้สูตรในการหาผลลัพธ์ได้หรือเปล่าค่ะ
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#25

Post by DhitiBank »

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

ที่ 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 นี่งงครับ ไม่รู้คิดยังไง :?:
You do not have the required permissions to view the files attached to this post.
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#26

Post by Nutta »

ขอบคุณ คุณ 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 ค่ะ
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#27

Post by DhitiBank »

2016-03-16 17_31_28-Microsoft Excel - Book1_re0 (1).xlsx.png
จากไฟล์ตัวอย่างล่าสุดนะครับ ถ้าหากผมใส่ข้อมูลต่อ (ดูเฉพาะ Nuta นะครับ) จะได้แบบรูปหรือเปล่าครับ ส่วนสีเขียว :?:
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#28

Post by DhitiBank »

2016-03-16 18_34_41-Settings.png
ปรับสูตรใหม่ในส่วนของคอลัมน์ 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 อีกทีเรื่องรูปที่ผมถามในโพสต์บนครับ
You do not have the required permissions to view the files attached to this post.
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#29

Post by Nutta »

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

และในส่วนของช่อง balance จากรูป col X ในส่วนสีเขียว ต้องแสดงค่าเป็น 2 ทั้งสองช่องค่ะ เพราะจะไม่นำสิ่งของที่เก็บมาจากคนละรอบวันที่มารวมกันค่ะ จะรวมกันเฉพาะที่เก็บมาจากรอบวันที่เดียวกันค่ะ
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#30

Post by DhitiBank »

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

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

แล้วคัดลอกสูตรทั้งหมดลงล่างครับ
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#31

Post by Nutta »

ขอบคุณค่ะ คุณ DhitiBank
แต่ติดปัญหาอีกนิดนึงค่ะ คือ ถ้าลูกค้านำสิ่งของที่มาฝากไว้ไปใช้หมดแล้วในรอบวันที่นั้นๆ จะไม่มีการแสดงผลลัพธ์ใน sheet2 ค่ะ เช่น ถ้า Nuta นำของที่เก็บในวันที่ 1/3/2014 ไปใช้หมดแล้ว ใน sheet2 จะไม่มีข้อมูลของ Nuta ในวันที่ 1/3/2014 มาแสดงอีกค่ะ ตามไฟล์แนบใน sheet2 col P - col X
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#32

Post by DhitiBank »

^ ^¡

มีเงื่อนไขอื่นอีกไหมครับ (รวมถึงกรณีอื่นๆ ที่อาจจะเกิดขึ้นได้) วันนี้ผมอยู่ข้างนอก ได้เข้าบ้านสักสามทุ่ม เดี๋ยวดูให้นะครับ
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#33

Post by DhitiBank »

คงต้องเพิ่มคอลัมน์ช่วยใน Sheet1 อีก และปรับสูตรในคอลัมน์ E, G, L ใน Sheet2 ครับ ดังนี้
:arrow: 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 -> คัดลอกลงล่าง

:arrow: Sheet2
L3

Code: Select all

=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,IF(Sheet1!$Y$4:$Y$26,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)))
E3

Code: Select all

=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,IF(Sheet1!$Y$4:$Y$26,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))))
G3

Code: Select all

=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,IF(Sheet1!$Y$4:$Y$26,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))))
ทั้ง 3 สูตรต้องกด Ctrl+Shift+Enter เพื่อรับสูตร -> คัดลอกลงล่างครับ

จริงๆ แล้วผมไม่อยากเพิ่มคอลัมน์ช่วยมากเกินไปและไม่อยากให้สูตรอาร์เรย์มันยาวมาก เพราะหากข้อมูลเริ่มเยอะ จะทำให้คำนวณช้ามากๆ แต่นึกวิธีอื่นไม่ออกจริงๆ ครับ ถ้าหากออกแบบตารางฐานข้อมูลใน Sheet1 ให้มีลักษณะเป็น database มากขึ้นได้ ผมว่าน่าจะใช้สูตรง่ายขึ้นนะครับ ผมบอกแนวคิดและเงื่อนไขเอาไว้ดีกว่า เผื่ออาจารย์และเพื่อนๆ ท่านอื่นจะเข้ามาช่วยแนะนำให้รวบรัดขึ้นครับ เพราะหากจะทำตามนี้ คุณ Nutta ก็ต้องมานั่งแก้สูตรหากข้อมูลใน Sheet1 มีแถวมากขึ้นเรื่อยๆ (ต้องแก้ในสูตรทุกสูตรที่มีช่วงอ้างอิงแถว 4:26 และต้องเพิ่มคอลัมน์ช่วยใน Sheet1 ตามข้อมูลที่มากขึ้น) แต่หากจะใส่สูตรเผื่อเอาไว้ก่อนมากๆ ก็ไม่ดีแน่ เพราะสูตรอาร์เรย์ยิ่งเยอะก็จะยิ่งช้าครับ
Sheet1.png
Sheet2.png
////เงื่อนไขรายการใน 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 ครับ
You do not have the required permissions to view the files attached to this post.
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#34

Post by Nutta »

ขอบคุณคุณ DhitiBank ค่ะ
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#35

Post by Nutta »

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

ขอบคุณคุณ DhitiBank ค่ะสำหรับคำแนะนำเรื่องการจัดทำ database พอดีต้องรีบใช้ข้อมูลในส่วนนี้เลยจำเป็นต้องใช้จากไฟล์ที่มีการเก็บข้อมูลไว้อยู่แล้วค่ะ และก็กำลังหาวิธีที่ทำให้ทางคนที่เก็บข้อมูลทำงานได้ง่ายที่สุดค่ะ :D
You do not have the required permissions to view the files attached to this post.
User avatar
DhitiBank
Gold
Gold
Posts: 1676
Joined: Mon Oct 15, 2012 12:07 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#36

Post by DhitiBank »

ปรับสูตรที่ 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 -> คัดลอกลงล่างครับ
Nutta
Member
Member
Posts: 24
Joined: Fri Nov 27, 2015 9:34 am

Re: เช็คสถานะรายชื่อ และแสดงผลใน sheet อื่น

#37

Post by Nutta »

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