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

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

Предположим, у вас есть несколько столбцов с несколькими значениями, некоторые значения повторяются в одном или другом столбце. И теперь вы хотите найти значения, которые присутствуют в любом столбце только один раз. Есть ли какие-нибудь уловки для извлечения уникальных значений из нескольких столбцов в Excel?


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

Вот формула массива, которая также может помочь вам извлечь уникальные значения из нескольких столбцов.

1. Предполагая, что ваши значения находятся в диапазоне A2: C9, введите следующую формулу в ячейку E2:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

Внимание: В приведенной выше формуле A2: C9 указывает диапазон ячеек, из которых вы хотите извлечь уникальные значения, E1: E1 это первая ячейка столбца, в который вы хотите поместить результат, $ 2: $ 9 стоит в рядах, содержащих ячейки, которые вы хотите использовать, и $ A: $ C указывает, что столбцы содержат ячейки, которые вы хотите использовать. Пожалуйста, измените их на свои собственные.

2, Затем нажмите Shift + Ctrl + Enter вместе, а затем перетащите маркер заполнения, чтобы извлечь уникальные значения, пока не появятся пустые ячейки. Смотрите скриншот:


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

Если вы знакомы со сводной таблицей, вы можете легко извлечь уникальные значения из нескольких столбцов, выполнив следующие действия:

1. Сначала вставьте один новый пустой столбец слева от ваших данных, в этом примере я вставлю столбец A рядом с исходными данными.

2. Щелкните одну ячейку в ваших данных и нажмите Alt + D клавиши, затем нажмите P ключ немедленно открыть Мастер сводных таблиц и диаграмм, выберите Несколько диапазонов консолидации в мастере step1 см. снимок экрана:

3. Затем нажмите Далее кнопку, проверьте Создайте для меня одностраничное поле вариант на шаге 2 мастера, см. снимок экрана:

4. Продолжайте нажимать Далее нажмите кнопку, щелкните, чтобы выбрать диапазон данных, который включает левый новый столбец ячеек, затем щелкните Добавить кнопку, чтобы добавить диапазон данных в Все диапазоны список, см. снимок экрана:

5. После выбора диапазона данных продолжайте нажимать Далее, на шаге 3 мастера выберите, где вы хотите разместить отчет сводной таблицы, как хотите.

6. Наконец, нажмите Завершить чтобы завершить работу мастера, и на текущем листе была создана сводная таблица, затем снимите отметки со всех полей в Выберите поля для добавления в отчет раздел, см. снимок экрана:

7. Затем проверьте поле Значение или перетащите Значение в Ряды label, теперь вы получите уникальные значения из нескольких столбцов следующим образом:


Извлечение уникальных значений из нескольких столбцов с кодом VBA

С помощью следующего кода VBA вы также можете извлечь уникальные значения из нескольких столбцов.

1. Удерживайте ALT + F11 ключи, и он открывает Окно Microsoft Visual Basic для приложений.

2. Нажмите Вставить > Модулии вставьте следующий код в окно модуля.

VBA: извлечение уникальных значений из нескольких столбцов

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

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

4, Затем нажмите OKпоявится другое окно подсказки, в котором вы сможете выбрать место для размещения результата, см. снимок экрана:

5. Нажмите OK чтобы закрыть это диалоговое окно, и все уникальные значения были извлечены сразу.


Извлекайте уникальные значения из одного столбца с удивительной функцией

Иногда вам нужно извлечь уникальные значения из одного столбца, вышеуказанные методы вам не помогут, здесь я могу порекомендовать полезный инструмент -Kutools for Excel, С его Извлечь ячейки с уникальными значениями (включая первый дубликат) утилита, вы можете быстро извлечь уникальные значения.

Примечание:Чтобы применить это Извлечь ячейки с уникальными значениями (включая первый дубликат), во-первых, вы должны скачать Kutools for Excel, а затем быстро и легко примените эту функцию.

После установки Kutools for Excel, пожалуйста, сделайте так:

1. Щелкните ячейку, в которой вы хотите вывести результат. (Внимание: Не щелкайте ячейку в первой строке.)

2. Затем нажмите Кутулс > Формула Помощник > Формула Помощник, см. снимок экрана:

3. В Помощник по формулам диалоговом окне выполните следующие действия:

  • Выберите Текст из файла Формула Тип раскрывающийся список;
  • Затем выберите Извлечь ячейки с уникальными значениями (включая первый дубликат) из Выберите изула список;
  • В правой Ввод аргументов В разделе выберите список ячеек, из которых вы хотите извлечь уникальные значения.

4. Затем нажмите Ok и перетащите маркер заполнения к ячейкам, в которых вы хотите отобразить все уникальные значения, пока не отобразятся пустые ячейки, см. снимок экрана:

Скачать бесплатно Kutools for Excel прямо сейчас!


Больше относительных статей:

  • Подсчитайте количество уникальных и отличных значений из списка
  • Предположим, у вас есть длинный список значений с некоторыми повторяющимися элементами, теперь вы хотите подсчитать количество уникальных значений (значения, которые появляются в списке только один раз) или отдельных значений (все разные значения в списке, это означает уникальные значения + 1-е повторяющиеся значения) в столбце, как показано на скриншоте слева. В этой статье я расскажу о том, как справиться с этой задачей в Excel.
  • Извлечение уникальных значений на основе критериев в Excel
  • Предположим, у вас есть следующий диапазон данных, в котором вы хотите перечислить только уникальные имена столбца B на основе определенного критерия столбца A, чтобы получить результат, как показано ниже. Как можно быстро и легко справиться с этой задачей в Excel?
  • Разрешить только уникальные значения в Excel
  • Если вы хотите сохранить только уникальные значения, вводимые в столбец рабочего листа, и предотвратить дублирование, в этой статье будут представлены некоторые быстрые приемы, которые помогут вам справиться с этой задачей.
  • Суммируйте уникальные значения на основе критериев в Excel
  • Например, у меня есть диапазон данных, который содержит столбцы Name и Order, теперь для суммирования только уникальных значений в столбце Order на основе столбца Name, как показано на следующем снимке экрана. Как быстро и легко решить эту задачу в Excel?

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

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

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

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

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (31)
Номинальный 5 из 5 · рейтинги 1
Этот комментарий был сведен к минимуму модератором на сайте
Is this formula complete? =INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",
Этот комментарий был сведен к минимуму модератором на сайте
Это еще не исправлено :sad:
Этот комментарий был сведен к минимуму модератором на сайте
какая пустая трата времени..... формула НЕ работает
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо!!! Я часами пытался это сделать и выяснить, что случилось с Pivot Wizard (другая статья).
Этот комментарий был сведен к минимуму модератором на сайте
Я использую ваш код VBA, но не хочу, чтобы окно появлялось. Вместо этого я хочу точно определить, какой диапазон ячеек использовать каждый раз и в какое именно поле помещать вывод. Диапазон ввода и вывод будут на двух разных листах. как мне обновить VBA для этого? Спасибо!!
Этот комментарий был сведен к минимуму модератором на сайте
Привет! Кто-нибудь знает, почему эта формула приводит к ошибке после строки 87? Например, он работает отлично, а затем в какой-то момент он просто возвращает мне ошибки для каждой строки ... что самое худшее! Потому что я так близок к тому, что мне здесь нужно...
Этот комментарий был сведен к минимуму модератором на сайте
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"" It doesn't work
Этот комментарий был сведен к минимуму модератором на сайте
привет, я хочу извлечь уникальные ячейки из первого столбца, когда я сравниваю его с другими столбцами (у меня есть три неравных столбца), как я могу это сделать?
Этот комментарий был сведен к минимуму модератором на сайте
привет, у меня есть три неравных столбца, и я хочу извлечь уникальные ячейки из первого столбца. как мне это сделать?? заранее спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Я люблю

Извлечение уникальных значений из нескольких столбцов с помощью сводной таблицы
Этот комментарий был сведен к минимуму модератором на сайте
Не могли бы вы представить правильную формулу... функция VBA работает отлично.
Просто для моего проекта я скорее использую правильную формулу.


Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
кто-нибудь знает, для вывода, как сделать его в несколько строк, а не в одну строку? (в настоящее время результат одной строки достигается с помощью worksheetfunction.transpose, но я хочу добиться (как результат), что при выборе для 3 столбцов возвращаемый результат также представляет собой 3 столбца, а не один
Этот комментарий был сведен к минимуму модератором на сайте
Эта формула массива ВЕРНА. Данные в столбцах от A до C, первая формула результата в ячейке D2... Эта формула отличается от другой формулы массива тем, что последняя копирует формулу вниз и Ctrl+Shift+Enter всю формулу. Однако эту формулу массива нужно сделать Ctrl+Shift+Enter в первой ячейке и скопировать вниз.
Этот комментарий был сведен к минимуму модератором на сайте
Большое спасибо за макрос!!! я могу использовать
Этот комментарий был сведен к минимуму модератором на сайте
я приспособился к своему листу, но возвращаю только первое значение в определенном массиве... что мне не хватает?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Коди,
Приведенная выше формула хорошо работает на моем листе, не могли бы вы привести здесь скриншот вашей проблемы с данными?
Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
Что касается версии с формулой, не могли бы вы подробнее объяснить, что делает эта часть? *100+COLUMN($A:$C),7^8)),"R0C00") В частности, какие * 100, 7 ^ 8и "R0C000" делает? Я понимаю все остальное, но я не могу понять, для чего они.
Этот комментарий был сведен к минимуму модератором на сайте
Немного поздно для моего ответа здесь, но...
ROW($2:$9)*100 - это умножение номера строки *100, поэтому, если он находится в строке 5, теперь число равно 500.
COLUMN($A:$C) — добавляется к числу row*100, поэтому, если это строка 5 col 2, то число равно 502.
7 ^ 8)), - это (я думаю) должно иметь максимальное значение для оператора min из предыдущего.
«R0C00») — форматирует текст на основе числа. В примере у нас было 502, поэтому это дает R5C02 (строка 5, столбец 02).

Если у вас много столбцов, но мало строк, вы можете изменить его на ROW($2:$9)*1000+СТОЛБЦ($A:$C),7^8)),"R0C000")
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за код. Я использую код VBA этой страницы. Есть ли способ добавить код сортировки после извлечения уникальных значений, чтобы он сортировал его автоматически?
Этот комментарий был сведен к минимуму модератором на сайте
мы можем создать функцию uniqdata вместо макроса?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Ильхан! Если вам нравится функция, определяемая пользователем, для создания формулы для решения этой проблемы, приведенный ниже код может вам помочь: после вставки кода выберите список ячеек, в которые вы хотите поместить результаты. Затем введите эту формулу:=Уникальные(A1:C4)  в строке формул.Нажмите Ctrl + Shift + Enter ключи вместе. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Этот комментарий был сведен к минимуму модератором на сайте
Czy to żart?
Этот комментарий был сведен к минимуму модератором на сайте
Формула массива вверху отлично работает при использовании с данными на одном листе, однако, когда я пытаюсь использовать ее для ссылки на те же самые данные с другого листа, формула ничего не возвращает. Я не могу понять, почему. Есть ли ограничение для функций массива, которое не позволяет вам ссылаться на диапазоны на другом листе?

Спасибо за любую информацию, которую вы можете предоставить.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Эрин,

Рад помочь. Функцию ДВССЫЛ в этой формуле сложнее использовать при ссылке на данные на других листах. Не рекомендуется использовать эту функцию при ссылке на диапазоны на разных листах.

Например: теперь данные находятся на Листе1, я хочу сослаться на содержимое ячейки C2 Листа1 на Листе2. Во-первых, в любых двух ячейках Листа2, таких как D1 и D2, введите Sheet1 и C2 соответственно. На этом этапе введите формулу в пустую ячейку Листа2:
=ДВССЫЛ("'"&D1&"'!"&D2), то может быть возвращено содержимое ячейки C2 в Sheet1.

Как видите, это усложняет задачу. Надеюсь, мое объяснение поможет. Хорошего дня.

С уважением,
Мэнди
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте! VBA творил чудеса, большое спасибо за это! Мне было интересно, если я изменю исходные данные, можно ли автоматически обновить столбец с уникальными значениями?
Номинальный 5 из 5
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Иоаннис,

Рад помочь. После изменения исходных данных VBA не может автоматически обновить результат. И самый простой способ, который я могу придумать, — это нажать Ctrl + Alt + F9, чтобы обновить все результаты на листах во всех открытых книгах. Хорошего дня.

С уважением,
Мэнди
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за эту замечательную статью.

Для людей, которые используют формула массива в неанглоязычном Excel необходимо уделить особое внимание строке текстового формата: в вашем примере: «R0C00».
Для немецкого это будет означать «Z0S00». Однако «S» — это специальный символ, обозначающий секунды для форматирования времени. Этот символ необходимо экранировать, поэтому правильная строка формата для немецкого Excel — «Z0\S00».

Надеюсь, это кому-то поможет в будущем :-)
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

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

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