Note: The other languages of the website are Google-translated. Back to English

Як динамічно витягувати список унікальних значень із діапазону стовпців у 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:

повне г, повне г,, показали, від, номер, XNUMX Виберіть Повторювані та унікальні комірки корисність 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, щоб автоматично оновити унікальний список значень.


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


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

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2021 і 365. Підтримує всі мови. Легке розгортання на вашому підприємстві чи в організації. 30-денна безкоштовна пробна версія повних функцій. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Коментарі (35)
Поки немає оцінок. Оцініть першим!
Цей коментар був мінімізований модератором на сайті
Дякую за підручник. Використовуючи метод формули, як би ви змінили формулу, якщо б хотіли додати кваліфікатор категорії? Скажіть, що в стовпці C ви розрізняєте, чи є товар фруктом чи овочем. Як би ви змінили код, щоб сортувати лише унікальні фрукти та виключати овочі? Я спробував замінити COUNTIF на COUNTIFS, використовуючи другий критерій countifs (LIST RANGE, "CATEGORY"), але він повертає пусте. Чи потрібно було б розширити свій масив і включити VLOOKUP?
Цей коментар був мінімізований модератором на сайті
Я добре володію Excel, але я дійсно намагаюся зрозуміти, як і чому працює вищенаведена формула (вона працює для того, для чого я її використовую, але я повинен зрозуміти, чому). Іноді я трохи плутаю, використовуючи масиви, тому будь-яке пояснення в термінах ідіотів було б надзвичайно корисним. З повагою
Цей коментар був мінімізований модератором на сайті
Ця формула застаріла і не працює. Я буквально просто встановив цей точний аркуш Excel, щоб побачити, чи зможу я змусити цю формулу працювати, але вона не працює.
Цей коментар був мінімізований модератором на сайті
Гей, хлопець,
Яку версію Office ви використовуєте?
Цей коментар був мінімізований модератором на сайті
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} - виявлено, що це працює з іншого сайту...

Використовуйте Ctrl+Shift+Enter, щоб отримати функцію масиву (фігурні дужки). Перетягніть скопіюйте та вставте формули, доки не відобразиться #NA. Мій набір даних був у Column-Q, його порівняли, щоб побачити, чи існує він у списку унікальних у Column-V, який постійно простягається вздовж цього самого стовпця.
Цей коментар був мінімізований модератором на сайті
Хороший день.
Будь ласка, перерахуйте всі унікальні значення стовпця Q з формулою abobv, а потім використовуйте його формулу =IF(D2=V1,"Match","No match"), щоб порівняти, чи унікальні значення в стовпці Q порівнюються зі стовпцем V в тому самому рядку .
Цей коментар був мінімізований модератором на сайті
Привіт і дякую за допомогу.

Мені потрібна саме ця функція, але мій список «унікальних значень» має поширюватися на стовпці, а не на рядки, тому список, що розгортається вниз по рядках, не працюватиме для мене.

Як я можу змінити цю формулу, щоб розширити список «унікальних значень», коли я перетягую його по стовпцях?

Зсув()?
Транспонувати()?
Indirect() з рядком абсолютних посилань, об’єднаних із посиланням на стовпець замість рядка?


Ще раз спасибі!
Цей коментар був мінімізований модератором на сайті
Шановний Райан,
Ця формула =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$2:D2, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter може допомогти вам вирішити проблему.
Дивіться скріншот нижче:
Цей коментар був мінімізований модератором на сайті
Крім того, з будь-якої причини оригінальна формула передбачала:
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),"")

повертає попередження про "кругову довідку" і не обчислює..
Цей коментар був мінімізований модератором на сайті
Шановний Райан,
Яку версію Office ви використовуєте? Формула добре працює в моєму Office 2016 і 2013.
Цей коментар був мінімізований модератором на сайті
У мене це траплялося раніше - моє виправлення полягало в тому, що я вводив формулу в клітинку D1 (еквівалент на аркуші, який я використовував). Незалежно від того, якій клітинці відповідає $D:$1, її потрібно ввести в клітинку нижче - D2. Вибачте, якщо ви отримали помилку не через це
Цей коментар був мінімізований модератором на сайті
Будь-які поради щодо того, як отримати опцію VBA для роботи з Excel 2016 для macOS? Я виконав кроки; однак, коли я запускаю макрос, взагалі нічого не відбувається. Спасибі!
Цей коментар був мінімізований модератором на сайті
Даер Джонс,
Будь ласка, спробуйте наведений нижче код VBA і повідомте мені, чи працює він для вас. Дякую!

Sub CreateUniqueList()
Dim xRng As Range
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I As Integer
' При помилці Відновити далі
Встановіть xRng = Application.InputBox("Виберіть діапазон:", "Kutools для Excel", Selection.Address, , , , , 8)
Якщо xRng — це нічого, вийдіть із 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 = Клітинки(Рядки.Кількість, "B"). Кінець(xlUp).Рядок
Для I = 1 до xLastRow2
Якщо ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Тоді
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Delete
End If
Далі
End Sub
Цей коментар був мінімізований модератором на сайті
Привіт Кристал,
Я намагаюся використати версію VB зі списку унікальних значень і виникаю проблемою.
Діапазон, з якого я хочу створити стовпець унікальних значень, — це всі формули, які посилаються на різні вкладки.
Як отримати значення для передачі замість формули?
Цей коментар був мінімізований модератором на сайті
Дорогий Майк,
Будь ласка, перетворіть посилання на формули в абсолютні, а потім застосуйте сценарій VB.
Цей коментар був мінімізований модератором на сайті
У мене така ж проблема, за винятком того, що моя формула посилається на імена стовпців і не може перетворитися на абсолютну.
Як змінити vba, щоб вставити значення, а не формулу?
Цей коментар був мінімізований модератором на сайті
Як би ви додали кілька критеріїв, наприклад, якщо ви хочете додати лише до динамічного списку, якщо дата була лише 9 вересня?

Я пробую "&" у формулі MATCH, але це не працює.

Наприклад, на основі вашого прикладу:
=IFERROR(INDEX($B$2:$B$9, MATCH(0 & B4,COUNTIF($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Це викликає помилку або створює дублікати.

Крім того, я читав, що "+" може працювати, хоча я не можу змусити його працювати. Або за допомогою SMALL.

Ідеї?
Цей коментар був мінімізований модератором на сайті
Шановний Зак,
На жаль, я не можу допомогти з цим, ви можете опублікувати своє запитання на нашому форумі: https://www.extendoffice.com/forum.html щоб отримати додаткову підтримку Excel від нашого професіонала.
Цей коментар був мінімізований модератором на сайті
Як би ви додали другу змінну? Наприклад, я хочу, щоб усі унікальні елементи в одному стовпці також мали подібне значення в іншому стовпці. У вашому прикладі уявіть 3-й стовпець під назвою «Відділ», який матиме такі значення, як продукт, м’ясо тощо. Я розумію, що все це продукція, але, сподіваюся, ви зрозуміли мою думку. Ви б змінили формулу CountIF на COUNTIFS чи змінили б її іншим способом?
Цей коментар був мінімізований модератором на сайті
Привіт Метт
Спробуйте цю формулу =IF(ISNA(VLOOKUP(A2,$C$2:$C$13,1,FALSE)),"Так","").
Припустимо, що двома порівнюваними списками є стовпець A і стовпець C, якщо унікальні значення залишаються лише в стовпці A, але не в стовпці C, вони відображатимуться так у стовпці B; тоді як якщо нічого не повертати в стовпці B, це означає, що відповідне значення залишається як у стовпці A, так і в стовпці C.
Цей коментар був мінімізований модератором на сайті
Дякую за відповідь.. але гаряче витягти це унікальне значення, якщо воно відображається ТАК.. Не могли б ви порадити мені формулу, щоб витягти унікальне значення в інший стовпець.
Цей коментар був мінімізований модератором на сайті
Якщо я роблю це для аркуша Excel з тисячою рядків на останній версії Excel на Mac, він ніколи не повертається. Перший рядок працює, але коли я дублюю вниз, Excel переходить у режим обчислення, який уже більше двох годин не повертає значення.

Будь-які думки про те, як це зробити для великих списків (до 2 тис. рядків), які повертатимуть 50 або 60 унікальних значень?

Я зобразив це в додатку «Числа», і там він працює ідеально, і обчислення займає всього пару хвилин. Це займає так багато часу в Excel, що мені цікаво, чи він коли-небудь завершиться. Я планую дати йому «попрацювати» на ніч, щоб побачити, що станеться.
Цей коментар був мінімізований модератором на сайті
Перевірте параметри обчислення. Його потрібно встановити на автоматичний режим. Файл > Параметри > Формули > Параметри обчислення > Розрахунок робочої книги (автоматичний вибір)
Цей коментар був мінімізований модератором на сайті
Я намагаюся перетягнути формулу вниз за мої фактичні дані, щоб я міг вводити набори даних різного розміру і нічого не коригувати. Однак останній рядок після закінчення моїх фактичних даних завжди повертає "0". Я використовую унікальні значення для чогось іншого в сусідньому стовпці, а 0 викликає повторення останнього значення (коли я видаляю 0, значення більше не повторюється). У вас є ідея, як це виправити? Також я використовую Office 365 Business
Цей коментар був мінімізований модератором на сайті
Привіт, дякую за допомогу.
Тепер, як я можу також відсортувати мої значення в алфавітному порядку? (Я не хочу використовувати фільтр у своїй головній таблиці)
Чи варто використовувати COUNTIFS замість COUNTIF?
Будь ласка, допоможіть
Цей коментар був мінімізований модератором на сайті
Привіт Алексіс,
На жаль, не вдається відсортувати витягнуте значення за алфавітом одночасно з формулою. Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
Я використовую цю формулу =IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($D$1:D1, $B$2:$B$9), 0)),""), яка чудова для одного стовпця, але мої дані розподілені в діапазоні стовпців і рядків. Чи можу я відредагувати формулу, щоб включити всю область? Мої дані живуть від AC4 до AR60...
Цей коментар був мінімізований модератором на сайті
Я пробую код VBA і формулу. Код VBA працює дуже добре, але я не можу зберегти файл з макросом. Але проблема в тому, що я не можу змусити формулу працювати. У когось була ідея? Дякую
Цей коментар був мінімізований модератором на сайті
Привіт, Шарлотта,
Дякуємо за коментар. Ви можете зберегти файл із макросом для подальшого використання, збереживши книгу як книгу Excel з підтримкою макросів.
Для проблеми формули, надайте, будь ласка, знімок екрана ваших даних? Дякуємо за коментар.
Цей коментар був мінімізований модератором на сайті
спасибо большое
Цей коментар був мінімізований модератором на сайті
як зробити так, щоб код vba працював для діапазону, де використовувалася інша формула?у стовпці BI є формула, що посилається на стовпці D і E.
Якщо я використовую застосувати код до стовпця L (скажімо), (очевидно, правильно змінюючи клітинки в коді), макрос повертає формулу, застосовану до стовпців M і N... Тоді це працює, але не так, як я хочу! Як зберегти значення в стовпці B? Дякую
Цей коментар був мінімізований модератором на сайті
Ik heb gemerkt dat de 'formule'-methode erg traag werkt bij grote datasets. Een goed alternative 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.
Цей коментар був мінімізований модератором на сайті
Я хотів би мати можливість робити те саме, за винятком використання двох окремих діапазонів стовпців (B2:B9), а також (D2:D9), чи це можливо?
Цей коментар був мінімізований модератором на сайті
Привіт Ентоні,
Результати можна помістити в той же стовпець, що й вихідні дані. У цьому випадку, наприклад, стовпець B.
Але вам потрібно посилатися на верхню комірку комірки результату у формулі наступним чином.
=IFERROR(INDEX($B$2:$B$9, MATCH(0,COUNTIF($B$11:B11, $B$2:$B$9), 0)),"") + Ctrl + Shift + Enter
Цей коментар був мінімізований модератором на сайті
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. визначте критерій фільтрації у файлі d56 у неправильному заголовку списку, а засіб видалення дублікатів у файлі d57 la dejo en blanco
3. una vez ejecutado se muestran los datos en la fila destino, que en mi caso fue la d59

Діапазон ("A59:A239"). Дія AdvancedFilter:=xlFilterCopy, CriteriaRange:=Діапазон _
("D56:D57"), CopyToRange:=Діапазон("D59"), Unique:=True
There are no comments posted here yet
Залишайте свої коментарі
Публікація як гість
×
Оцініть цю публікацію:
0   Персонажі
Рекомендовані місця

Слідуй за нами

Copyright © 2009 - WWW.extendoffice.com. | Всі права захищені. На основі ExtendOffice. | Карта сайту
Microsoft та логотип Office є товарними знаками або зареєстрованими товарними знаками Microsoft Corporation у США та / або інших країнах.
Захищений Sectigo SSL