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

Как динамически извлечь список уникальных значений из диапазона столбцов в Excel?

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

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


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

Как показано на скриншоте ниже, вам необходимо динамически извлечь список уникальных значений из диапазона B2: B9. Попробуйте следующую формулу массива.

1. Выберите пустую ячейку, например D2, введите в нее приведенную ниже формулу и нажмите Ctrl + Shift + Enter клавиши одновременно. (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 for 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. Теперь нажмите кнопку с прямоугольником. Kutools for Excel появится диалоговое окно, выберите диапазон, содержащий уникальные значения, которые необходимо извлечь, а затем щелкните значок OK кнопку.

С этого момента вы можете повторить описанный выше шаг 5 для автоматического обновления списка уникальных значений.


Статьи по теме:


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

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

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (35)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за урок. Используя метод формулы, как бы вы изменили формулу, если бы захотели добавить квалификатор категории? Скажем, в столбце C вы различаете, является ли предмет фруктом или овощем. Как бы вы изменили код, чтобы сортировать только уникальные фрукты и исключать овощи? Я попытался заменить COUNTIF на COUNTIFS, используя второй критерий countifs (LIST RANGE, «CATEGORY»), но он возвращает пустое значение. Нужно ли мне расширять массив и включать функцию ВПР?
Этот комментарий был сведен к минимуму модератором на сайте
Я неплохо разбираюсь в Excel, но я действительно пытаюсь понять, как и почему работает приведенная выше формула (она работает для того, для чего я ее использую, но я должен понять, почему). Иногда я немного запутался, используя массивы, поэтому любое объяснение в терминах идиотов было бы чрезвычайно полезным С уважением
Этот комментарий был сведен к минимуму модератором на сайте
Эта формула устарела и не работает. Я буквально только что установил этот точный лист Excel, чтобы посмотреть, смогу ли я заставить эту формулу работать, и это не так.
Этот комментарий был сведен к минимуму модератором на сайте
Эй, парень,
Какую версию Office вы используете?
Этот комментарий был сведен к минимуму модератором на сайте
{=INDEX($Q$3:$Q$263,MATCH(0,COUNTIF(V$2:V2,$Q$3:$Q$263),0))} – обнаружил, что это работает на другом сайте...

Используйте Ctrl+Shift+Enter, чтобы получить функцию массива (фигурные скобки). Перетащите и вставьте формулы, пока не отобразится #NA. Мой набор данных был в столбце-Q, его сравнивали, чтобы увидеть, существует ли он в списке уникальных в столбце-V, который постоянно растягивается вдоль этого же столбца.
Этот комментарий был сведен к минимуму модератором на сайте
Хорошего дня.
Перечислите все уникальные значения столбца Q с помощью формулы abobv, а затем используйте его формулу = ЕСЛИ (D2 = V1, «Совпадение», «Нет совпадения»), чтобы сравнить, если уникальные значения в столбце Q сравниваются со столбцом V в той же строке. .
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, и спасибо за вашу помощь.

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

Как я могу изменить эту формулу, чтобы список «уникальных значений» расширялся, когда я перетаскиваю его по столбцам?

Компенсировать()?
Транспонировать ()?
Indirect() со строкой абсолютных ссылок, объединенных со ссылкой на столбец вместо строки?


Еще раз спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Райан,
Эта формула =ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9, ПОИСКПОЗ(0,СЧЁТЕСЛИ($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 и дайте мне знать, работает ли он для вас. Благодарю вас!

Подпрограмма CreateUniqueList()
Dim xRng как диапазон
Dim xLastRow As Long
Dim xLastRow2 As Long
Dim I как целое число
' При ошибке Возобновить Далее
Установите xRng = Application.InputBox («Выберите диапазон:», «Kutools для Excel», Selection.Address, , , , , 8)
Если xRng ничего не значит, выйдите из Sub
On Error Resume Next
xRng. Диапазон копирования ("D2")
xLastRow = xRng.Rows.Count + 1
ActiveSheet.Range("D2:D" & xLastRow).RemoveDuplicates Columns:=1, Header:=xlNo
xLastRow2 = Cells(Rows.Count, "B").End(xlUp).Row
Для I = 1 Для xLastRow2
Если ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Value = "" Тогда
ActiveSheet.Range("D2:D" & xLastRow2).Cells(I).Удалить
End If
Далее
End Sub
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Кристал,
Я пытаюсь использовать VB-версию списка уникальных значений и сталкиваюсь с проблемой.
Диапазон, из которого я хочу создать столбец уникальных значений, — это все формулы, которые относятся к разным вкладкам.
Как получить значение для передачи вместо формулы?
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Майк,
Пожалуйста, преобразуйте ссылки на формулы в абсолютные, а затем примените сценарий VB.
Этот комментарий был сведен к минимуму модератором на сайте
У меня та же проблема, за исключением того, что моя формула относится к именам столбцов и не может быть преобразована в абсолютную.
Как изменить vba, чтобы вставить значения, а не формулу?
Этот комментарий был сведен к минимуму модератором на сайте
Как бы вы добавили несколько критериев, например, если бы вы хотели добавить в динамический список только дату 9 сентября?

Я пытаюсь использовать "&" в формуле ПОИСКПОЗ, но это не работает.

Например, на основе вашего примера:
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9, MATCH(0 & B4,СЧЁТЕСЛИ($D$1:D1, $B$2:$B$9) & $A$2:$A$9, 0)),"" )
Это выдает ошибку или создает дубликаты.

В качестве альтернативы я читал, что «+» может работать, хотя я не могу заставить его работать. Или используя МАЛЕНЬКИЙ.

Идеи?
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Зак,
Извините, я не могу помочь с этим, вы можете задать свой вопрос на нашем форуме: https://www.extendoffice.com/forum.html чтобы получить больше поддержки Excel от нашего профессионала.
Этот комментарий был сведен к минимуму модератором на сайте
Как бы вы добавили вторую переменную? Например, мне нужны все уникальные элементы в одном столбце, которые также имеют одинаковое значение в другом столбце. В вашем примере представьте себе 3-й столбец под названием «Отдел», в котором будут такие значения, как продукт, мясо и т. д. Я понимаю, что это все продукты, но, надеюсь, вы поняли мою точку зрения. Вы бы изменили формулу CountIF на COUNTIFS или изменили бы ее другим способом?
Этот комментарий был сведен к минимуму модератором на сайте
Привет Мэтт
Пожалуйста, попробуйте эту формулу =ЕСЛИ(ISNA(ВПР(A2,$C$2:$C$13,1,ЛОЖЬ)),"Да","").
Предположим, что два сравниваемых списка представляют собой столбец A и столбец C. Если уникальные значения остаются только в столбце A, но не в столбце C, в столбце B будет отображаться значение «Да»; в то время как если в столбце B ничего не возвращается, это означает, что соответствующее значение остается как в столбце A, так и в столбце C.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за ответ ... но жарко вытащить это уникальное значение, если оно отображается ДА ... не могли бы вы посоветовать мне формулу для извлечения уникального значения в другом столбце.
Этот комментарий был сведен к минимуму модератором на сайте
Если я сделаю это для листа Excel с тысячей строк в последней версии Excel на Mac, он никогда не вернется. Первая строка работает, но когда я дублирую ее, Excel переходит в режим вычислений, в котором значения не возвращаются уже более двух часов.

Любые мысли о том, как это сделать для больших списков (до 2 тыс. строк), которые будут возвращать 50 или 60 уникальных значений?

Я смоделировал это в приложении «Числа», и оно отлично там работает, для вычисления требуется всего пара минут. Просто в Excel это занимает так много времени, что я задаюсь вопросом, завершится ли оно когда-нибудь. Я планирую оставить его «работать» на ночь, чтобы посмотреть, что произойдет.
Этот комментарий был сведен к минимуму модератором на сайте
Проверьте параметры расчета. Его нужно настроить на автоматический. Файл> Параметры> Формулы> Параметры расчета> Расчет рабочей книги (автоматический выбор)
Этот комментарий был сведен к минимуму модератором на сайте
Я пытаюсь перетащить формулу за пределы моих фактических данных, чтобы я мог вводить наборы данных разного размера и ничего не настраивать. Однако последняя строка после окончания моих фактических данных всегда возвращает «0». Я использую уникальные значения для чего-то еще в соседнем столбце, а 0 приводит к повторению последнего значения (когда я удаляю 0, значение больше не повторяется). Есть идеи, как это исправить? Также я использую Office 365 Business
Этот комментарий был сведен к минимуму модератором на сайте
Привет, спасибо за вашу помощь.
Теперь, как я могу отсортировать свои значения в алфавитном порядке? (Я не хочу использовать фильтр на моей мастер-таблице)
Должен ли я использовать COUNTIFS вместо COUNTIF?
Пожалуйста, помогите
Этот комментарий был сведен к минимуму модератором на сайте
Привет Алексис,
Извините, не могу помочь отсортировать извлеченное значение по алфавиту одновременно с формулой. Спасибо за ваш комментарий.
Этот комментарий был сведен к минимуму модератором на сайте
Я использую эту формулу =ЕСЛИОШИБКА(ИНДЕКС($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 Alternatief - это Het Gebruik van een draaitabel. Kies дан alleen де rijlabels, je krijgt дан een lijstje встретил 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 фильтр критерия. Ook daar zijn wel weer oplossingen voor, maar dat is wat complexer.
Этот комментарий был сведен к минимуму модератором на сайте
Я хотел бы иметь возможность делать то же самое, за исключением использования двух отдельных диапазонов столбцов (B2: B9), а также (D2: D9), возможно ли это?
Этот комментарий был сведен к минимуму модератором на сайте
Привет Энтони,
Вы можете поместить результаты в тот же столбец, что и исходные данные. Например, столбец B в этом случае.
Но вам нужно сослаться на верхнюю ячейку ячейки результата в формуле следующим образом.
=ЕСЛИОШИБКА(ИНДЕКС($B$2:$B$9, СОВПАДЕНИЕ(0,СЧЁТЕСЛИ($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 remove 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 remove 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"), Уникальный:=True
Здесь еще нет комментариев
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL