นำข้อมูลจากหลายชีตมาเรียงใหม่เฉพาะค่าที่ไม่ซ้ำ

Excel มีเครื่องมือสำหรับการนำเฉพาะค่าที่ไม่ซ้ำมาแสดงคือ Data > Remove Duplicates แต่จะทำเช่นนี้ได้ข้อมูลจะต้องอยู่ในชีตเดียวกันเท่านั้น หากข้อมูลอยู่ในหลาย ๆ ชีต แต่ละชีตมีหลายตำแหน่ง จะไม่สามารถใช้เครื่องมือตัวนี้จัดการได้ในทันที จำเป็นต้องใช้ VBA เข้ามาจัดการ Video ด้านล่างนี้เป็นการนำค่าจากหลายชีตและหลายคอลัมน์และมีตำแหน่งไม่แน่นอนมาจัดเรียงใหม่เป็น Database ด้วย VBA โดยข้อมูลที่ซ้ำกันจะนำมาแสดงเพียงค่าเดียว สามารถทำซ้ำได้ตามต้องการ หากเป็นการทำซ้ำโปรแกรมจะทำการลบข้อมูลที่มีอยู่เดิมใน Database ทิ้งไปก่อนแล้วค่อย Loop เพื่อนำค่ามาแสดงใหม่

Video แสดงการนำข้อมูลหลายชีตมาเรียงใหม่เฉพาะค่าที่ไม่ซ้ำ

การหา Unique Value จากค่าที่ได้จากการกรอง

สำหรับการหาค่า Unique Value เราสามารถหาได้จากหลาย ๆ วิธีตาม Link นี้ครับ การหารายการเฉพาะที่ไม่ซ้ำ แต่หากเป็นการหาค่าที่ไม่ซ้ำจากรายการที่ได้จากการกรองต้องพึ่งสูตรที่มีความซับซ้อนมากขึ้น

ยกตัวอย่างมีข้อมูลอยู่ที่ A2:B10 หลังจากกรองแล้วให้นับเฉพาะค่าที่ไม่ซ้ำในช่วง A2:A10

เราสามารถคีย์สูตรใน D1 ได้ตามด้านล่างครับ

=SUM(IF(MATCH(IF(SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0)),A2:A10),IF(SUBTOTAL(3,OFFSET( B2,ROW( B2:B10)-ROW(B2),0)),A2:A10),0)=ROW(A2:A10)-ROW(A2)+1,SUBTOTAL(3,OFFSET(B2,ROW(B2:B10)-ROW(B2),0))))

Ctrl+Shift+Enter

ภาพตัวอย่างภาพการใช้สูตรหาค่า Unique จากค่าที่ได้จากการกรอง

การหารายการเฉพาะที่ไม่ซ้ำ

สำหรับรายการที่ซ้ำ ๆ กัน เราสามารถที่หาเฉพาะที่ไม่ซ้ำได้มาใช้งานได้ครับ ซึ่งสามารถแจกแจงได้ 5 วิธีหลัก ๆ ตามด้านล่าง นั่นคือ

1. การหารายการที่ไม่ซ้ำด้วย PivotTable
2. การหารายการที่ไม่ซ้ำด้วย Consolidation
3. การหารายการที่ไม่ซ้ำด้วย Advanced Filter
4. การหารายการที่ไม่ซ้ำด้วย Function
5. การหารายการที่ไม่ซ้ำด้วย Remove Duplicates (Excel 2007 เป็นต้นไป)

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

1. การหารายการที่ไม่ซ้ำด้วยการใช้ PivotTable

2. การหารายการที่ไม่ซ้ำด้วย Consolidation

3. การหารายการที่ไม่ซ้ำด้วย Advanced Filter

4. การหารายการที่ไม่ซ้ำด้วย Function

ซึ่งสามารถสรุปสูตรได้ดังนี้

1. สูตรที่ใช้นับว่ามีค่าที่ไม่ซ้ำกี่ค่า ที่ B1 คีย์
   =Sumproduct(1/Countif(A1:A10,A1:A10))
   Enter
2. สูตรเพื่อ List รายการเฉพาะที่ไม่ซ้ำที่ B3 คีย์
   =If(Rows($B$3:B3)>$B$1,””,Index($A$2:$A$10,Small(If(Frequency(Match($A$2:$A$10,$A$2:$A$10,0),Row($A$2:$A$10)-Row($A$2)+1),Row($A$2:$A$10)-Row($A$2)+1),Rows($B$3:B3))))
   Crtl+Shift+Enter

สำหรับวิธีการหารายการที่ไม่ซ้ำด้วยสูตรนี้ค่อนข้างจะยืดหยุ่นกรณีที่ข้อมูลไม่มากจนเกินไปและข้อมูลมีการเปลี่ยนแปลงตลอดเวลา จะทำให้เราได้ข้อมูลที่ Update อยู่เสมอโดยการเขียนสูตรในครั้งเดียว ไม่ต้องทำซ้ำ ๆ เหมือนวิธีการอื่น ๆ

5. การหารายการที่ไม่ซ้ำด้วย Remove Duplicates

การแสดงรายการโดยไม่รวมค่าที่ซ้ำ

กรณี ที่เรามีข้อมูลซึ่งประกอบด้วยค่าที่ซ้ำกันจำนวนมาก หากต้องการที่จะให้โปรแกรมแสดงรายการทั้งหมดโดยไม่รวมค่าที่ซ้ำ สามารถใช้ Advanced Filter ทำได้ดังนี้

1. สมมุติข้อมูล A1:A10 มีดังต่อไปนี้
   A1: Fruits
   A2: Apple
   A3: Cherry
   A4: Pear
   A5: Cherry
   A6: Plum
   A7: Apple
   A8: Apple
   A9: Pear
   A10: Apple
2. คลิกเมนู Data > Filter > Advanced Filter
3. ตรง Action เลือก Copy to another location
4. คลิกในช่อง List Range ลากคลุม $A$1:$A$10
5. คลิก Unique records only
6. คลิกในช่อง Copy to และคลิกเซลล์ B1
7. OK

โปรแกรมจะแสดงข้อมูลที่คอลัมน์ B ดังข้างล่างนี้

B1: Fruits
B2: Apple
B3: Cherry
B4: Pear
B5: Plum

การ ใช้ Advanced Filter เพื่อเลือกค่าที่ไม่ซ้ำสามารถใช้ได้กับหลาย ๆ คอลัมน์ และหากต้องการให้กรองและแล้วแสดงในฐานข้อมูลเดิม สามารถเว้นข้อ 6 ด้านบนไว้ได้