ไปยังเนื้อหาหลัก

วิธีการแยกรายการค่าที่ไม่ซ้ำแบบไดนามิกจากช่วงคอลัมน์ใน Excel

สำหรับช่วงคอลัมน์ที่ค่ามีการเปลี่ยนแปลงเป็นประจำและคุณจำเป็นต้องรับค่าที่ไม่ซ้ำกันทั้งหมดจากช่วงเสมอไม่ว่าจะเปลี่ยนแปลง วิธีสร้างรายการค่าที่ไม่ซ้ำแบบไดนามิก บทความนี้จะแสดงวิธีจัดการกับมัน

แยกรายการของค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วงคอลัมน์ด้วยสูตร
ดึงรายการค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วงคอลัมน์ด้วยรหัส VBA


แยกรายการของค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วงคอลัมน์ด้วยสูตร

ดังภาพด้านล่างนี้คุณต้องดึงรายการค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วง B2: B9 โปรดลองใช้สูตรอาร์เรย์ต่อไปนี้

1. เลือกเซลล์ว่างเช่น D2 ป้อนสูตรด้านล่างลงไปแล้วกดปุ่ม Ctrl + เปลี่ยน + เข้าสู่ คีย์พร้อมกัน (B2: B9 คือข้อมูลคอลัมน์ที่คุณต้องการแยกค่าเฉพาะ D1 คือเซลล์ด้านบนที่มีสูตรของคุณอยู่)

=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

2. เลือกเซลล์ D2 จากนั้นลาก Fill Handle ลงเพื่อรับค่าที่ไม่ซ้ำกันทั้งหมดจากช่วงที่ระบุ

ตอนนี้ค่าที่ไม่ซ้ำกันทั้งหมดในช่วงคอลัมน์ B2: B9 จะถูกแยกออกมา เมื่อค่าในช่วงนี้เปลี่ยนไปรายการค่าเฉพาะจะเปลี่ยนแบบไดนามิกทันที

เลือกและเน้นค่าที่ไม่ซ้ำกันทั้งหมดในช่วงใน excel ได้อย่างง่ายดาย:

พื้นที่ เลือกเซลล์ซ้ำและไม่ซ้ำกัน ประโยชน์ของ Kutools สำหรับ Excel สามารถช่วยให้คุณเลือกและเน้นค่าที่ไม่ซ้ำกันทั้งหมดได้อย่างง่ายดาย (รวมค่าที่ซ้ำกันครั้งแรก) หรือค่าที่ไม่ซ้ำกันที่ปรากฏเพียงครั้งเดียวตลอดจนค่าที่ซ้ำกันตามที่คุณต้องการดังภาพด้านล่างที่แสดง
ดาวน์โหลด Kutools สำหรับ Excel ทันที! (เส้นทางฟรี 30 วัน)


ดึงรายการค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วงคอลัมน์ด้วยรหัส VBA

คุณยังสามารถแยกรายการค่าที่ไม่ซ้ำกันแบบไดนามิกจากช่วงคอลัมน์ด้วยรหัส VBA ต่อไปนี้

1 กด อื่น ๆ + F11 พร้อมกันเพื่อเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2 ใน Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่างคลิก สิ่งที่ใส่เข้าไป > โมดูล. จากนั้นคัดลอกและวางโค้ด VBA ด้านล่างลงในไฟล์ โมดูล หน้าต่าง

รหัส VBA: แยกรายการของค่าที่ไม่ซ้ำกันจากช่วง

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
On Error Resume Next
Set xRng = Application.InputBox("Please select range:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
On Error Resume Next
xRng.Copy Range("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
For I = 1 To xLastRow2
  If ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Then
     ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
  End If
Next
End Sub

หมายเหตุ: ในโค้ด D2 คือเซลล์ที่คุณจะค้นหารายการค่าเฉพาะ คุณสามารถเปลี่ยนได้ตามต้องการ

3. กลับไปที่แผ่นงานคลิก สิ่งที่ใส่เข้าไป > รูปร่าง > รูปสี่เหลี่ยมผืนผ้า. ดูภาพหน้าจอ:

4. วาดรูปสี่เหลี่ยมผืนผ้าในแผ่นงานของคุณจากนั้นป้อนคำที่คุณต้องการแสดงบนแผ่นงาน จากนั้นคลิกขวาและเลือก กำหนดมาโคร จากเมนูคลิกขวา ใน กำหนดมาโคร กล่องโต้ตอบเลือกไฟล์ สร้างรายการที่ไม่ซ้ำ ใน ชื่อมาโคร จากนั้นคลิกที่ไฟล์ OK ปุ่ม. ดูภาพหน้าจอ:

5. ตอนนี้คลิกที่ปุ่มสี่เหลี่ยมผืนผ้า a Kutools สำหรับ Excel กล่องโต้ตอบปรากฏขึ้นโปรดเลือกช่วงที่มีค่าที่ไม่ซ้ำกันที่คุณต้องการแยกจากนั้นคลิกที่ไฟล์ OK ปุ่ม

จากนี้ไปคุณสามารถทำซ้ำขั้นตอนที่ 5 ข้างต้นเพื่ออัปเดตรายการค่าเฉพาะโดยอัตโนมัติ


บทความที่เกี่ยวข้อง:

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์    VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่    สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์    ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

รายละเอียด


แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!
Comments (35)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
con este procedimiento de filtro se hace de forma muy rapida

1.EN ESTE EJEMPLO los datos a remover los duplicados estan en la col A de la fila 59 a la 239
2. se define un criterio de filtrado en este caso en la fila d56 el mismo titulo de la lista a remover duplicados y la d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Range("A59:A239").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
("D56:D57"), CopyToRange:=Range("D59"), Unique:=True
This comment was minimized by the moderator on the site
I would like to be able to do this exact same thing, except using two separate column ranges (B2:B9) as well as (D2:D9) is this possible?
This comment was minimized by the moderator on the site
Hi Anthony,
You can place the results in the same column as the original data. Such as column B in this case.
But you need to reference the top cell of the result cell in the formula as follows.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
This comment was minimized by the moderator on the site
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternatief is het gebruik van een draaitabel. Kies dan alleen de rijlabels, je krijgt dan een lijstje met unieke waarden. Het kan zijn dat je wat extra's krijgt "(leeg)" bijvoorbeeld. Je kunt deze er vervolgens uitfilteren. Helaas kun je maar op 1 criterium filteren. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
This comment was minimized by the moderator on the site
how to make the vba code work for a range where another formula was used?on column B I have a formula, referring to columns D and E.
If I use apply the code to column L (let's say), (obviously, properly modifying the cells in the code) the macro returns the formula applied to columns M and N... It works, then, but not as I want!How to keep the values in column B?thanks
This comment was minimized by the moderator on the site
thank you very much
This comment was minimized by the moderator on the site
I try the VBA Code and the formula. The code VBA is working very well but I can't keep a file with macro. But th problem is that I can't make the formula working. Did anybody have an idea ? Thank you
This comment was minimized by the moderator on the site
Hi Charlotte,
Thank you for your comment. You can keep the file with macro for future use by saving the workbook as an Excel Macro-Enabled Workbook.
For the formula problem, would you please provide a screenshot of your data? Thank you for your comment.
This comment was minimized by the moderator on the site
I'm using that =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"") formula which is great for one column but my data is spread across a range of columns and rows. Can I edit the formula to include the whole area? My data lives from AC4 to AR60...
This comment was minimized by the moderator on the site
Hi, thank you for your help.
Now, how can I have my values also sorted alphabetically? (I do not want to use the filter on my master table)
Should I use a COUNTIFS instead of COUNTIF?
Please HELP
This comment was minimized by the moderator on the site
Hi Alexis,
Sorry can't hep to sort the extracted value alphabetically at the same time with formula. Thank you for your comment.
This comment was minimized by the moderator on the site
I am trying to drag the formula down past my actual data so that I can input different sized data sets and not have to adjust anything. However, the last row after my actual data ends always returns a "0". I am using the unique values for something else in an adjacent column, and the 0 causes the last value to repeated (When I delete the 0 the value is no longer repeated). Any idea how to fix this? Also I am using Office 365 Business
This comment was minimized by the moderator on the site
If I do this for a thousand row excel sheet on the latest version of Excel on a Mac, it never returns. The first row works, but when I duplicate down, excel goes into a compute mode that has not returned values for over two hours now.

Any thoughts on how to do this for large lists (up to 2k rows) that will return 50 or 60 unique values?

I mocked this up in the "Numbers" app, and it's working perfectly there, taking only a couple minutes to calculate. It's just taking so long in Excel that I wonder if it will ever complete. I plan on letting it "run" overnight to see what will happen.
This comment was minimized by the moderator on the site
Check your Calculating options. It needs to be set to automatic. File > Options > Formulas > Calculating options > Workbook Calculation (Automatic selection)
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations