Перейти до основного матеріалу

Як динамічно витягувати список унікальних значень із діапазону стовпців у Excel?

Для діапазону стовпців, значення яких регулярно змінюються, і завжди потрібно отримувати всі унікальні значення з діапазону, незалежно від того, як він змінювався. Як скласти динамічний список унікальних значень? Ця стаття покаже вам, як з цим боротися.

Динамічно витягуйте список унікальних значень із діапазону стовпців із формулою
Динамічно витягуйте список унікальних значень із діапазону стовпців із кодом VBA


Динамічно витягуйте список унікальних значень із діапазону стовпців із формулою

Як показано на знімку екрана, вам потрібно динамічно витягувати список унікальних значень із діапазону B2: B9. Спробуйте наступну формулу масиву.

1. Виділіть порожню комірку, таку як D2, введіть в неї формулу нижче та натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у клавіші одночасно. (B2: B9 - це дані стовпця, для яких ви хочете отримати унікальні значення, D1 - це наведена вище комірка, де знаходиться ваша формула)

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

2. Продовжуйте обирати комірку D2, а потім перетягніть ручку заповнення вниз, щоб отримати всі унікальні значення із зазначеного діапазону.

Тепер усі унікальні значення в діапазоні стовпців B2: B9 витягуються. Коли значення в цьому діапазоні змінюються, унікальний список значень буде динамічно змінюватися негайно.

Легко вибирайте та виділяйте всі унікальні значення в діапазоні в Excel:

Команда Виберіть Повторювані та унікальні комірки корисність Kutools для Excel може допомогти вам легко вибрати та виділити всі унікальні значення (включаючи перші дублікати) або унікальні значення, які з’являються лише один раз, а також повторювані значення, як вам потрібно, як показано на знімку екрана нижче.
Завантажте Kutools для Excel зараз! (30 днів безкоштовно)


Динамічно витягуйте список унікальних значень із діапазону стовпців із кодом VBA

Ви також можете динамічно витягувати список унікальних значень із діапазону стовпців із таким кодом VBA.

1. прес інший + F11 клавіші одночасно, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модулі. Потім скопіюйте та вставте наведений нижче код 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. Поверніться до аркуша, натисніть Insert > форми > Прямокутник. Дивіться знімок екрана:

4. Намалюйте прямокутник на своєму аркуші, а потім введіть кілька слів, які потрібно відобразити на ньому. Потім клацніть правою кнопкою миші та виберіть Призначити макрос з меню, що клацне правою кнопкою миші. В Призначити макрос діалоговому вікні, виберіть CreateUniqueList в Назва макросу , а потім клацніть на OK кнопку. Дивіться знімок екрана:

5. Тепер натисніть кнопку прямокутника, a Kutools для Excel з'явиться діалогове вікно, виберіть діапазон, що містить унікальні значення, які потрібно витягти, а потім клацніть на OK кнопки.

Відтепер ви можете повторити вищевказаний крок 5, щоб автоматично оновити унікальний список значень.


Статті по темі:

Найкращі інструменти продуктивності офісу

Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

вкладка kte 201905


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 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