Excel Forum

ต้องการนำลำดับเลขที่ในช่อง A ไปวางไว้ในช่อง J5-J7 โดยเทียบข้อมูลจากช่อง AA กับ ช่อง G5-G7 และดึงเฉพาะวันที่ที่ตรงกับ ช่อง N7 ตัวอย่างตามไฟล์แนบ
รบกวนด้วยครับ ขอบคุณครับ

ตัวอย่างสูตรตามด้านล่างครับ

1. ที่ AB5 คีย์
   =IFERROR(DAY(INDEX($A:$A,AGGREGATE(15,6,ROW($AB$11:$AB$20)/($AA$11:$AA$20=$G5),COLUMNS($AB5:AB5)))),"")
   Enter > Copy ไปด้านขวาเท่าที่คิดว่าข้อมูลขยายไปถึงแล้ว Copy ลงด้านล่างถึงบรรทัดที่ 7
2. ที่ J5 คีย์
   =SUBSTITUTE(TRIM(SUBSTITUTE(AB5&","&AC5&","&AD5&","&AE5&","&AF5,","," "))," ",",")
   Enter > Copy ลงด้านล่างถึง J7

สูตรแรกประกอบด้วย IFERROR, DAY, INDEX, AGGREGATE, ROW, COLUMNS ความหมายแต่ละส่วนของสูตรมีดังนี้

1. INDEX($A$A,AGGREGATE(15,6,ROW($AB$11$AB$20)/($AA$11$AA$20=$G5),COLUMNS($AB5AB5))) ฟังก์ชันนี้จะค้นหาค่าในคอลัมน์ A โดยใช้ตำแหน่งที่ได้จากฟังก์ชัน AGGREGATE
2. AGGREGATE(15,6,ROW($AB$11$AB$20)/($AA$11$AA$20=$G5),COLUMNS($AB5AB5)) ฟังก์ชันนี้จะคืนค่าตำแหน่งที่ COLUMNS($AB5AB5) ในอาร์เรย์ที่สร้างจาก ROW($AB$11$AB$20)/($AA$11$AA$20=$G5) ฟังก์ชัน AGGREGATE ทำหน้าที่เป็น SMALL ถ้าพารามิเตอร์แรกเป็น 15 และ 6 คือการละเว้นค่าผิดพลาด
3. DAY(INDEX(...)) ฟังก์ชันนี้จะคืนค่าวันจากวันที่ที่ได้จากฟังก์ชัน INDEX
4. IFERROR(DAY(...),"") ฟังก์ชันนี้จะคืนค่าวันที่ถ้าไม่มีข้อผิดพลาด แต่ถ้ามีข้อผิดพลาดจะคืนค่าเป็นสตริงว่าง

ดังนั้น สูตรนี้จะคืนค่าวันที่จากคอลัมน์ A ที่ตรงกับเงื่อนไขในคอลัมน์ AA และ AB ถ้าไม่มีข้อผิดพลาด แต่ถ้ามีข้อผิดพลาดจะคืนค่าเป็นสตริงว่าง

สูตรที่สองประกอบด้วย SUBSTITUTE และ TRIM ร่วมกัน ความหมายแต่ละส่วนของสูตรมีดังนี้

1. AB5&","&AC5&","&AD5&","&AE5&","&AF5 ส่วนนี้จะรวมข้อมูลจากเซลล์ AB5, AC5, AD5, AE5, และ AF5 โดยแยกแต่ละเซลล์ด้วยเครื่องหมายจุลภาค (comma)
2. SUBSTITUTE(AB5&","&AC5&","&AD5&","&AE5&","&AF5,","," ") ฟังก์ชันนี้จะแทนที่เครื่องหมายจุลภาค (comma) ด้วยช่องว่าง (space)
3. TRIM(SUBSTITUTE(...)) ฟังก์ชันนี้จะลบช่องว่างที่มากเกินไปจากข้อความที่ได้จากฟังก์ชัน SUBSTITUTE
4. SUBSTITUTE(TRIM(...)," ",",") ฟังก์ชันนี้จะแทนที่ช่องว่าง (space) ด้วยเครื่องหมายจุลภาค (comma)

ดังนั้น สูตรนี้จะรวมข้อมูลจากเซลล์ AB5, AC5, AD5, AE5, และ AF5 โดยแยกแต่ละเซลล์ด้วยเครื่องหมายจุลภาค (comma) และลบช่องว่างที่มากเกินไป

ยังขาดข้อมูลส่วนที่เทียบกับวันที่ช่อง N7 ครับ ให้ดึงข้อมูลเฉพาะที่เป็นวันที่เดียวกันกับช่อง N7 เท่านั้นครับ ข้อมูลในช่อง J6 เลยไม่ถูกต้อง เพราะเลข 4 เป็นของวันที่ 10-06-24 ครับ

ตัวอย่างการปรับสูตรใน AB5 ครับ

=IFERROR(DAY(INDEX($A:$A,AGGREGATE(15,6,ROW($AB$11:$AB$20)/(($AA$11:$AA$20=$G5)*($AB$11:$AB$20=$N$7)),COLUMNS($AB5:AB5)))),"")

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

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

ปรับสูตรที่เซลล์ AB2 เป็นด้านล่างครับ

=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($AB$11:$AB$200)/(($AA$11:$AA$200=$G5)*($AB$11:$AB$200=$N$7)),COLUMNS($AB5:AB5))),"")

คัดลอกไปด้านขวาและลงด้านล่าง

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