สอบถามสูตร เพื่อใช้งานโดยมีวันที่ปัจจุบันเป็นตัวกำหนดรายการสินค้า

[rinjang5027]

สวัสดีค่ะอาจารย์ ไม่ถนัด Excel ค่ะ แต่ได้โจทย์มาว่า
ต้องการ เฉพาะสินค้าที่หมดอายุ ใน Sheet ข้อมูล Stock Balance (H7,K7,N7) มาแสดงที่รายละเอียดที่กำหนดใน Sheet ข้อมูลแสดง
โดยใช้วันที่ปัจจุบันเป็นตัวกำหนดค่ะ

[snasui]

จากตัวอย่างไฟล์ที่แนบมา ช่วยกรอกตัวอย่างคำตอบที่ต้องการมาด้วย พร้อมทั้งอธิบายมาสักสองสามเซลล์ว่า นำค่ามาจากเซลล์ใด ด้วยเงื่อนไขใด จะได้เข้าใจตรงกันครับ

[rinjang5027]

รบกวนอาจารย์แนะนำด้วยนะคะ เพื่อเป็นความรู้ไว้ศึกษาข้อมูล
ไพรินไม่มีความรู้ด้านใช้สูตรที่ซับซ้อนเลยค่ะ

[snasui]

ตัวอย่างสูตรครับ

1. ที่ชีต Data
   1. AU7 คีย์สูตร เพื่อนับว่ามีวันที่น้อยกว่าหรือเท่ากับวันที่กำหนดหรือไม่
      =COUNTIFS(G7:R7,">42000",G7:R7,"<="&ข้อมูลแสดง!$B$5)
      Enter > Copy ลงด้านล่างถึง AU175
   2. AU176 คีย์สูตร
      =SUM(AU7:AU175)
      Enter
   3. AT6 คีย์สูตร 0 เพื่อเป็นค่าเริ่มให้กับสูตรที่ AT7
      Enter
   4. AT7 คีย์สูตรเพื่อให้ลำดับค่าที่เข้าเงื่อนไข
      =IF(N(AU7),LOOKUP(9.9999999999999E+307,AT$6:AT6)+1,"")
      Enter > Copy ลงด้านล่างถึง AT175
   5. AV7 คีย์สูตรเพื่อเป็นบรรทัดเริ่มของแต่ละชุดข้อมูล
      =IF(N(AU7),SUM(AU$7:AU7)-AU7+1,"")
      Enter > Copy ลงด้านล่างถึง AV175
2. ที่ชีตข้อมูลแสดง
   1. A9 คีย์สูตร
      =IF(ROWS(A$9:A9)>Data!$AU$176,"",LOOKUP(ROWS(A$9:A9),Data!$AV$7:$AV$174,Data!B$7:B$174))
      Enter > Copy ไปด้านขวาจนถึง D9 แล้วลงด้านล่างจนเห็นเป็นค่าว่าง
   2. E9 คีย์สูตร
      =IF($A9="","",INDEX(INDEX(Data!$G$7:$Q$174,MATCH($C9,Data!$D$7:$D$174,0),0),SMALL(IF(Data!$G$5:$Q$5="วันหมดอายุ",IF(INDEX(Data!$G$7:$Q$174,MATCH($C9,Data!$D$7:$D$174,0),0)<=$B$5,COLUMN(Data!$G$5:$Q$5)-COLUMN(Data!$G$5)+1)),COUNTIF(C$9:C9,C9))))
      Ctrl+Shift+Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง
   3. F9 คีย์สูตร
      =IF($A9="","",INDEX(INDEX(Data!$G$7:$Q$174,MATCH($C9,Data!$D$7:$D$174,0),0),SMALL(IF(Data!$G$5:$Q$5="จำนวน",IF(INDEX(Data!$G$7:$Q$174,MATCH($C9,Data!$D$7:$D$174,0),0)<=$B$5,COLUMN(Data!$G$5:$Q$5)-COLUMN(Data!$G$5)+1)),COUNTIF(C$9:C9,C9))))
      Ctrl+Shift+Enter > Copy ลงด้านล่างจนเห็นเป็นค่าว่าง

[rinjang5027]

ขอบคุณมากค่ะ อาจารย์ ตรงตามข้อมูลที่ต้องนำมาใช้งานมากค่ะ

[rinjang5027]

อาจารย์ค่ะ วันที่ใน Sheet ข้อมูลแสดง มันขึ้น ไม่ตรงกับที่ Link มาต้องแก้ไขอย่างไรค่ะ
ถามเพิ่มเติมเพื่อนำมาศึกษาค่ะ
1.ที่ชีต Data
1.AU7 คีย์สูตร เพื่อนับว่ามีวันที่น้อยกว่าหรือเท่ากับวันที่กำหนดหรือไม่
=COUNTIFS(G7:R7,">42000",G7:R7,"<="&ข้อมูลแสดง!$B$5) : 42000 หมายความว่าอย่างไร เป็นค่าอะไรค่ะ
2..AT7 คีย์สูตรเพื่อให้ลำดับค่าที่เข้าเงื่อนไข
=IF(N(AU7),LOOKUP(9.9999999999999E+307,AT$6:AT6)+1,"") : 9.9999999999999E+307 หมายความว่าอย่างไร เป็นค่าอะไรค่ะ

รบกวนด้วยนะคะ

[snasui]

ปรับสูตรใหม่ที่ชีต ข้อมูลแสดงครับ

1. เซลล์ E9 คีย์
   =IF($A9="","",INDEX(INDEX(Data!$G$7:$Y$174,MATCH($C9,Data!$D$7:$D$174,0),0),SMALL(IF(Data!$G$5:$Y$5="วันหมดอายุ",IF(INDEX(Data!$G$7:$Y$174,MATCH($C9,Data!$D$7:$D$174,0),0)<>"",IF(INDEX(Data!$G$7:$Y$174,MATCH($C9,Data!$D$7:$D$174,0),0)<=$B$5,COLUMN(Data!$G$5:$Y$5)-COLUMN(Data!$G$5)+1))),COUNTIF(C$9:C9,C9))))
   Ctrl+Shift+Enter > Copy ลงด้านล่าง
2. เซลล์ F9 คีย์
   =IF($A9="","",INDEX(INDEX(Data!$G$7:$Y$174,MATCH($C9,Data!$D$7:$D$174,0),0),MATCH(E9,INDEX(Data!$G$7:$Y$174,MATCH($C9,Data!$D$7:$D$174,0),0),0)-1))
   Enter > Copy ลงด้านล่าง

42000 หมายความว่าอย่างไร เป็นค่าอะไรค่ะ

ตัวเลขนี้แทนตัวเลขวันที่ ซึ่งจะต้องมากกว่าตัวเลขของวันที่ที่มี Serial Number 42000 ขึ้นไป สามารถปรับสูตรเสียใหม่เป็นด้านล่างเพื่อจะได้เข้าใจมากขึ้นครับ

ชีต Data เซลล์ AU7 คีย์สูตร
=COUNTIFS($G$5:$Y$5,"วันหมดอายุ",G7:Y7,"<="&ข้อมูลแสดง!$B$5)
Enter > Copy ลงด่านล่าง

IF(N(AU7),LOOKUP(9.9999999999999E+307,AT$6:AT6)+1,"") : 9.9999999999999E+307 หมายความว่าอย่างไร เป็นค่าอะไรค่ะ

เป็นค่าที่ใหญ่ที่สุดที่สามารถคีย์ลงในเซลล์ได้ เป็นการประยุกต์เพื่อหาค่าตัวเลขสุดท้ายในช่วง AT$6:AT6 และบวกอีก 1 เพื่อให้เป็นลำดับถัดไปครับ

สูตรพวกนี้จะซับซ้อนและทำความเข้าใจยากมาก เนื่องจากเป็นการนำรายงานมาทำรายงาน สูตรจะง่ายลงหากข้อมูลเป็น Database ครับ

[rinjang5027]

ขอบคุณมากค่ะ อาจารย์