Snasui Excel Forum

ต้องการดึงข้อมูลแบบ List แล้วให้ข้อมูลมาด้วนครับ

สิ่งที่เขียนไว้แล้วติดปัญหาใด ช่วยอธิบายเพิ่มด้วยครับ

ในcolumn L2 เป็น List Iterm โดยอ้างอิงจาก Buyer ID ตอนนี้ ใช้พิมพ์อยู่ครับ

สามารถใช้สูตร List ออกมาก่อนแล้วค่อยอ้างอิงมาใช้ได้ครับ

ก่อนอื่นต้องปรับขนาดของ Table ให้พอดีกับข้อมูลเสียก่อนโดยคลิกลงไปในเซลล์ใด ๆ ในตาราง > เข้าเมนู Table Design > Resize Table > ตรง Select the new data range... ปรับเป็น $A$1:$J$97

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

การเผื่อขนาด Table ไว้มากเกินไปเช่นที่มาไว้เดิมจะทำให้ไฟล์ทำงานช้า สูตรที่เขียนไว้เช่น Vlookup จะทำงานหนักครับ

ตัวอย่างการใช้สูตรแสดง List ของ Byer ID

1. ที่ P2 คีย์
   =IFERROR(INDEX(Table2[Buyer ID],AGGREGATE(15,6,ROW(Table2[Buyer ID])/(0<FREQUENCY(MATCH(Table2[Buyer ID],Table2[Buyer ID],0),ROW(Table2[Buyer ID])-MIN(ROW(Table2[Buyer ID]))+1)),ROWS(P$2:P2))),"")
   Enter > Copy ลงด้านล่าง

การนำไปแสดงเป็น List ด้วย Data Validation

1. คลิกเซลล์ L2
2. เข้าเมนู Data > Data Validation > ตรง Allow เลือก List
3. ตรง Source คีย์สูตร
   =COUNTIFS(P:P,"?*")
4. คลิก OK

คำอธิบายสูตรใน P2

1. IFERROR(...,"")
   → ถ้ามีข้อผิดพลาด (เช่น ไม่มีข้อมูลให้ดึงแล้ว) ให้แสดงค่าเป็นว่าง ("") แทนข้อความ error
2. INDEX(Table2[Buyer ID], ...)
   → ใช้ดึงค่าจากคอลัมน์ Buyer ID ของ Table2 ตามตำแหน่งแถวที่คำนวณได้จากส่วนในวงเล็บถัดไป
3. AGGREGATE(15,6, ROW(Table2[Buyer ID]) / (...), ROWS(P$2:P2))
   → ฟังก์ชัน AGGREGATE ใช้เพื่อหาลำดับที่ n ของค่าในรายการ (คล้าย SMALL)
   
   15 หมายถึงใช้ฟังก์ชัน SMALL
   
   6 หมายถึงไม่รวม error ในการคำนวณ
   
   ส่วน ROW(Table2[Buyer ID]) / (...) คือการสร้างอาเรย์ของหมายเลขแถวเฉพาะที่ผ่านเงื่อนไข
   
   ROWS(P$2:P2) ใช้กำหนดลำดับ (1, 2, 3, …) สำหรับดึงค่าไม่ซ้ำแต่ละลำดับ
4. (0 < FREQUENCY(MATCH(Table2[Buyer ID], Table2[Buyer ID], 0), ROW(Table2[Buyer ID]) - MIN(ROW(Table2[Buyer ID])) + 1))
   → ส่วนนี้สร้างอาเรย์ตรรกะ (TRUE/FALSE) เพื่อระบุว่าแถวใดคือ ค่าที่ปรากฏครั้งแรกของ Buyer ID นั้น
   
   MATCH(Table2[Buyer ID], Table2[Buyer ID], 0) คืนตำแหน่งที่พบ Buyer ID แต่ละค่า
   
   FREQUENCY(...) ใช้เพื่อตรวจนับจำนวนครั้งของแต่ละค่า (โดยคืนค่ามากกว่า 0 เฉพาะตำแหน่งแรก)
   
   (0 < FREQUENCY(...)) แปลงผลลัพธ์เป็น TRUE เฉพาะค่าที่ไม่ซ้ำ
5. เมื่อรวมทั้งหมด
   → ROW(Table2[Buyer ID]) / (0 < FREQUENCY(...)) จะได้เฉพาะหมายเลขแถวของ Buyer ID ที่ไม่ซ้ำ (เพราะ TRUE = 1, FALSE = Error)
   → AGGREGATE จะเลือกหมายเลขแถวลำดับที่ n
   → INDEX ดึงค่าจากแถวดังกล่าว
   → IFERROR กัน error ตอนหมดรายการ
6. ผลลัพธ์สุดท้าย
   → คืนค่า “Buyer ID ที่ไม่ซ้ำ” จาก Table2 ทีละค่า (เช่น รายการ Unique Buyer ID)

ขอบคุณอาจารย์มากครับ แต่ผมมีเรื่องสอบถามครับ เขียนสูตรแล้วครับ ที่ P2 ปล้วแต่ผลลัพท์ที่ได้ ยังมีข้อมูลที่ซำ้กันเกิดจากอะไรครับ

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