Как показать уникальные значения в столбце excel
Перейти к содержимому

Как показать уникальные значения в столбце excel

  • автор:

Подсчет количества уникальных значений среди повторяющихся

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

  • числа 5, 6, 7 и 6, будут найдены три уникальных значения — 5, 6 и 7;
  • строки «Руслан», «Сергей», «Сергей», «Сергей», будут найдены два уникальных значения — «Руслан» и «Сергей».

Существует несколько способов подсчета количества уникальных значений среди повторяющихся.

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

С помощью диалогового окна Расширенный фильтр можно извлечь уникальные значения из столбца данных и вставить их в новое местоположение. Затем с помощью функции ЧСТРОК можно подсчитать количество элементов в новом диапазоне.

  1. Выделите диапазон ячеек или убедитесь, что активная ячейка находится в таблице. Убедитесь в том, что диапазон ячеек содержит заголовок столбца.
  2. На вкладке Данные в группе Сортировка и фильтр нажмите кнопку Дополнительно. Появится диалоговое окно Расширенный фильтр.
  3. Установите переключатель скопировать результат в другое место.
  4. В поле Копировать введите ссылку на ячейку. В противном случае нажмите Свернуть диалоговое окно

для временного скрытия диалогового окна, выберите ячейку на листе, а затем нажмите Развернуть диалоговое окно

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

Для выполнения этой задачи используйте комбинацию функций ЕСЛИ, СУММ, ЧАСТОТА, ПОИСКПОЗ и ДЛСТР.

  • Назначьте значение 1 каждому из истинных условий с помощью функции ЕСЛИ.
  • Вычислите сумму, используя функцию СУММ.
  • Подсчитайте количество уникальных значений с помощью функции ЧАСТОТА. Функция ЧАСТОТА пропускает текстовые и нулевые значения. Для первого вхождения заданного значения эта функция возвращает число, равное общему количеству его вхождений. Для каждого последующего вхождения того же значения функция возвращает ноль.
  • Узнайте позицию текстового значения в диапазоне с помощью функции ПОИСКПОЗ. Возвращенное значение затем используется в качестве аргумента функции ЧАСТОТА, что позволяет определить количество вхождений текстовых значений.
  • Найдите пустые ячейки с помощью функции ДЛСТР. Пустые ячейки имеют нулевую длину.

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

  • Формулы, приведенные в этом примере, должны быть введены как формулы массива. Если у вас установлена текущая версия Microsoft 365, можно просто ввести формулу в верхней левой ячейке диапазона вывода и нажать клавишу ВВОД, чтобы подтвердить использование формулы динамического массива. Иначе формулу необходимо вводить с использованием прежней версии массива, выбрав диапазон вывода, введя формулу в левой верхней ячейке диапазона и нажав клавиши CTRL+SHIFT+ВВОД для подтверждения. Excel автоматически вставляет фигурные скобки в начале и конце формулы. Дополнительные сведения о формулах массива см. в статье Использование формул массива: рекомендации и примеры.
  • Чтобы просмотреть процесс вычисления функции по шагам, выделите ячейку с формулой, а затем на вкладке Формулы в группе Зависимости формул нажмите Вычислить формулу.

Описание функций

  • Функция ЧАСТОТА вычисляет частоту появления значений в диапазоне и возвращает вертикальный массив чисел. С помощью функции ЧАСТОТА можно, например, подсчитать количество результатов тестирования, попадающих в определенные интервалы. Поскольку данная функция возвращает массив, ее необходимо вводить как формулу массива.
  • Функция ПОИСКПОЗ выполняет поиск указанного элемента в диапазоне ячеек и возвращает относительную позицию этого элемента в диапазоне. Например, если диапазон A1:A3 содержит значения 5, 25 и 38, формула =ПОИСКПОЗ(25;A1:A3;0) возвращает значение 2, так как элемент 25 является вторым в диапазоне.
  • Функция ДЛСТР возвращает число символов в текстовой строке.
  • Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом выполнения другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5.
  • Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое, если условие дает в результате значение ЛОЖЬ.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

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

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

Эта конкретная формула находит уникальные значения в диапазоне ячеек A2:C11 и возвращает список уникальных значений в столбце E .

В следующем примере показано, как использовать эту формулу на практике.

Пример: поиск уникальных значений из нескольких столбцов в Excel

Предположим, у нас есть следующие три списка названий баскетбольных команд в Excel:

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

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

Мы можем ввести эту формулу в ячейку E2 и нажать Ctrl+Shift+Enter :

Затем мы можем щелкнуть и перетащить эту формулу вниз в другие ячейки в столбце E, пока не начнут появляться пустые значения:

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

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

Примечание.Чтобы использовать эту формулу с большим количеством столбцов, просто измените диапазон ячеек A2:C11 в формуле, чтобы включить больше столбцов.

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Excel:

Функция УНИК

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel для iPad Excel для iPhone Excel для планшетов с Android Excel для телефонов с Android Еще. Меньше

Функция УНИК возвращает список уникальных значений в списке или диапазоне.

Возвращение уникальных значений из списка значений

Пример использования =УНИК(B2:B11) для возврата уникального списка чисел

Возвращение уникальных имен из списка имен

Применение функции УНИК для сортировки списка имен

=УНИК(массив,[by_col],[exactly_once])

Функция УНИК имеет следующие аргументы:

Диапазон или массив, из которого возвращаются уникальные строки или столбцы

Аргумент by_col является логическим значением, указывающим, как проводить сравнение.

Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы

Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки

[exactly_once]

Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК.

Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз

Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы

  • Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.
  • Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
  • Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.

Примеры

В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.

Использование УНИК с СОРТ для возврата списка имен по возрастанию

В этом примере аргумент exactly_once имеет значение ИСТИНА, и функция возвращает только тех клиентов, которые обслуживались один раз. Это может быть полезно, если вы хотите найти людей, которые не получали дополнительное обслуживание, и связаться с ними.

Использование УНИК с аргументом occurs_once, для которого задано значение true, для возврата списка имен, которые встречаются только один раз.

В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.

Использование УНИК с несколькими диапазонами для объединения столбцов имени и фамилии в столбец полного имени.

  • Если указать диапазон имен в формате таблицы Excel, формула автоматически обновляется при добавлении или удалении имен.
  • Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))

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

Использование УНИК для возврата списка продавцов.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Поиск уникальных значений в столбце в Excel (6 методов)

Hugh West

В Microsoft Excel извлечение уникальные значения из столбца Это одна из важнейших задач. Вы можете оказаться в различных ситуациях, когда вам может понадобиться найти уникальные данные для анализа вашего набора данных. В этом уроке вы узнаете все возможные способы извлечения уникальных значений в столбце в Excel с подходящими примерами и надлежащими иллюстрациями.

Смотрите также: Как отменить сохранение в Excel (4 быстрых способа)

Скачать Практическое пособие

Уникальные значения в столбце.xlsm

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

В следующих разделах мы предоставим вам шесть эффективных способов извлечения уникальных значений из столбца в Excel. Теперь мы рекомендуем вам изучить и применить все эти методы на своих рабочих листах. Я надеюсь, что это определенно обогатит ваши знания Excel в будущем.

1. Использование расширенного фильтра для уникальных значений в Excel

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

Взгляните на следующий набор данных:

Здесь мы будем использовать расширенный фильтр для уникальных значений в столбце.

�� Шаги

  • Сначала выберите любую ячейку в столбце.

  • Затем перейдите на вкладку «Данные».
  • После этого нажмите на Расширенный .

  • Теперь из Advanced Filte r диалоговое окно, выберите Копирование в другое место Затем выберите любую ячейку, чтобы вставить новые значения. Затем установите флажок в поле Только уникальные записи .

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

Читать далее: Как извлечь уникальные элементы из списка в Excel (10 методов)

2. Фильтр для уникальных значений в столбце

Другим эффективным способом является фильтрация списка или столбца. Для этого также используется Расширенный команда из Excel Данные вкладка. Но разница в том, что она заменит весь столбец уникальными значениями.

�� Шаги

  • Сначала выделите любую ячейку в столбце.

  • отправиться в Данные Таб.
  • После этого нажмите на Расширенный .

  • Теперь из Расширенный фильтр в диалоговом окне выберите Фильтр списка, на месте Далее установите флажок Только уникальные записи .

В итоге вы увидите, что в столбце будут показаны только уникальные значения. По сути, он скрыл дублирующиеся значения.

3. удаление дубликатов из столбца в Excel

Если в ваших столбцах есть дубликаты, просто удалите эти дубликаты. После этого в Excel у вас останутся только уникальные значения в столбце.

�� Шаги

  • Сначала выделите любую ячейку в столбце.

  • Затем перейдите к Данные
  • Из Инструменты данных группу, нажмите на Удаление дубликатов .

  • Теперь из Удаление дубликатов диалогового окна, установите флажок Данные и Мои данные имеют заголовки .

Наконец, он покажет вам, сколько дубликатов он нашел и сколько уникальных значений находится в столбце в Excel.

4. Формулы для поиска уникальных значений в столбце

Теперь вы можете использовать формулы для извлечения уникальных значений в столбце. Эти методы также пригодятся вам в будущем. Поэтому мы предлагаем вам выучить эти формулы, чтобы улучшить свои знания.

4.1 Использование функций IF и COUNTIF

В этом методе мы объединяем функция ПЧ и функция COUNTIF вместе. Теперь этот метод вставляет значение в ячейку, если оно уникально. Но ориентация не будет такой, как у других методов. Вы можете сохранить это в своем арсенале, чтобы стать лучшим аналитиком.

�� Шаги

  • Сначала создайте новый столбец.

  • После этого введите следующую формулу Ячейка D5 :

=IF(COUNTIF(B$5:B5,B5)=1,B5,»»)

  • Затем нажмите Войти .
  • После этого перетащите Заправочная ручка значок над диапазоном ячеек D6:D13 Затем вы увидите следующее:

Как видно из скриншота выше, наш столбец содержит только уникальные значения в Excel.

4.2 Использование функций INDEX и MATCH

Теперь, процесс этого метода похож на предыдущий. Только мы изменим формулу. В основном, мы смешиваем функция ИНДЕКС и функция MATCH .

Кроме того, мы используем функция IFERROR и функция COUNTIF за дальнейшей помощью.

�� Шаги

  • Создайте новый столбец.

  • Затем введите следующую формулу в Ячейка D5 :

=IFERROR(INDEX($B$5:$B$13, MATCH(0,COUNTIF($D$4:D4, $B$5:$B$13), 0)),»»)

  • Далее нажмите Войти .
  • После этого перетащите Заправочная ручка значок над диапазоном ячеек D6:D13 .

  • Затем вы увидите следующее:

Таким образом, вы можете легко извлечь уникальные значения из ячейки Excel.

Читать далее: Как получить уникальные значения из диапазона в Excel (8 методов)

5. использование функции UNIQUE в Excel

Другой важный метод поиска уникальных значений в столбце — это использование функция UNIQUE . Excel UNIQUE Функция возвращает список уникальных значений в диапазоне или списке. Это очень простая в использовании функция, которую можно использовать для извлечения как уникальных, так и уникальных отличных значений. Она также помогает сравнивать столбцы со столбцами или строки со строками.

Примечание : The UNIQUE функция доступна в Excel 365 и 2021.

В следующих разделах мы покажем вам пять примеров того. UNIQUE функцию для реализации вашего рабочего листа.

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

Это основной пример UNIQUE функция. Мы найдем уникальные значения из столбца.

�� Шаги

  • Сначала создайте новый столбец.

  • Теперь введите следующую формулу Ячейка D5 :

=UNIQUE(B5:B13)

После этого он извлечет все уникальные значения из другого столбца в этот столбец.

5.2 Извлечение уникальных значений, которые встречаются только один раз

Теперь из столбца видно, что некоторые значения встречаются несколько раз, а некоторые — только один раз. Эти значения можно назвать чистыми уникальными значениями. Вы можете найти эти значения, задав третий аргумент параметра UNIQUE функция для TRUE .

�� Шаги

  • Создайте новый столбец, как показано ниже.

  • Далее введите следующую формулу в Ячейка D5 :

=UNIQUE(B5:B13,,TRUE)

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

5.3 Уникальные значения из нескольких столбцов

Теперь вы можете использовать UNIQUE функция для нескольких столбцов, в которых есть уникальные строки. На самом деле она возвращает целевые столбцы в аргументе массива.

Мы используем его для следующего набора данных:

�� Шаги

  • Введите следующую формулу в Ячейка E5 :

=UNIQUE(B5:C13)

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

5.4 Сортировка уникальных значений в алфавитном порядке

Теперь вы можете извлекать уникальные значения из столбца в алфавитном порядке. В основном мы выполняем это с помощью команды сортировки & фильтра Excel. Но функция SORT делает это за нас. Вам не нужно каждый раз заново сортировать данные после их извлечения.

�� Шаги

  • Сначала создайте новый столбец.

  • После этого введите следующую формулу Ячейка D5 :

=SORT(UNIQUE(B5:B13))

Как видите, мы успешно извлекли и отсортировали уникальные значения из столбца в Excel.

5.5 Уникальные значения на основе критериев

Теперь вы можете найти уникальные значения из столбца на основе критериев в Excel. Чтобы выполнить это, мы смешиваем UNIQUE функция с функция ФИЛЬТР в Excel.

Взгляните на следующий набор данных:

Отсюда мы собираемся найти уникальные значения, возраст которых меньше 30 лет.

�� Шаги

  • Сначала создайте два новых столбца, как показано ниже:

  • Далее введите следующую формулу в Ячейка D5 :

=UNIQUE(FILTER(B5:C13,C5:C13<30))

Теперь вы можете увидеть уникальные значения из набора данных, основанные на наших критериях, в Excel.

6. Макросы VBA для получения уникальных значений в столбце

Если вы, как и я, помешаны на VBA, то этот метод может показаться вам интересным. Этот метод на самом деле работает как метод расширенного фильтра, который мы показывали ранее. Он создает новый список в новом столбце с уникальными значениями.

�� Шаги

  • Сначала нажмите Alt+F11 чтобы открыть приложение Visual Basic.
  • После этого нажмите на Вставка> Модуль .

  • Теперь введите следующий код:
 Option Explicit Sub UniqueColumn() Dim last_row As Long last_row = Cells(Rows.Count, "B").End(xlUp).Row ActiveSheet.Range("B2:B" & last_row).AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=ActiveSheet.Range("D2"), _ Unique:=True End Sub 
  • После этого сохраните файл.
  • Теперь нажмите Alt+F8 чтобы открыть диалоговое окно Макрос.

  • Затем выберите UniqueColumn и нажмите на Запускайте .

Как видите, наши коды VBA успешно создали в Excel новый столбец с уникальными значениями.

Смотрите также: Как перемножить два столбца в Excel (5 самых простых способов)

�� О чем нужно помнить

Мы показали несколько основных примеров UNIQUE Обязательно прочитайте статью, о которой мы упоминали, чтобы получить четкое представление об этой функции.

Сайт UNIQUE функция покажет #SPILL ошибка, если в диапазоне разлива одна или несколько ячеек не являются пустыми.

Если вы не хотите перезаписывать исходные данные, сделайте их копию.

Заключение

В заключение я надеюсь, что это руководство предоставило вам полезные знания по извлечению уникальных значений в столбце в Excel. Мы рекомендуем вам изучить и применить все эти инструкции к вашему набору данных. Скачайте рабочую тетрадь и попробуйте сами. Кроме того, не стесняйтесь оставлять отзывы в разделе комментариев. Ваши ценные отзывы поддерживают нашу мотивацию создавать подобные руководства.

Не забудьте заглянуть на наш сайт ExcelWIKI.com для решения различных проблем и задач, связанных с Excel.

Продолжайте изучать новые методы и расти!

Предыдущий пост Как получить значение ячейки по строке и столбцу в Excel VBA
Следующий пост Сохраните 0 перед номером телефона в Excel (6 методов)

Hugh West

Хью Уэст — опытный тренер и аналитик Excel с более чем 10-летним опытом работы в отрасли. Он имеет степень бакалавра в области бухгалтерского учета и финансов и степень магистра делового администрирования. Хью страстно любит преподавать и разработал уникальный подход к обучению, которому легко следовать и который легко понять. Его экспертные знания Excel помогли тысячам студентов и специалистов по всему миру улучшить свои навыки и преуспеть в своей карьере. В своем блоге Хью делится своими знаниями со всем миром, предлагая бесплатные учебные пособия по Excel и онлайн-обучение, чтобы помочь отдельным лицам и компаниям полностью раскрыть свой потенциал.

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *