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

1. Около
2. Товары
3. Для команд

1. Переполнение стека Общественные вопросы и ответы
2. Переполнение стека для команд Где разработчики и технологи делятся частными знаниями с коллегами
3. Вакансии Программирование и связанные с ним технические возможности карьерного роста
4. Талант Нанимайте технических специалистов и создавайте свой бренд работодателя
5. Объявление

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

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

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

Как получить уникальные значения в Excel

Чтобы избежать путаницы, сначала давайте договоримся о том, что мы называем уникальными значениями в Excel. Уникальные значения - это значения, которые существуют в списке только один раз. Например:

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

Массив , формула уникальных значений (завершается нажатием Ctrl + Shift + Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2 : $ A $ 10) <> 1), 0)), "")

Обычная формула уникальных значений (заполняется нажатием Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) + (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) <> 1), 0,0), 0)), "")

В приведенных выше формулах используются следующие ссылки:

• A2: A10 - исходный список.
• B1 - верхняя ячейка уникального списка минус 1. В этом примере мы начинаем уникальный список в B2, и поэтому мы вводим B1 в формулу (B2-1 = B1). Если ваш уникальный список начинается, скажем, с ячейки C3, измените $ B $ 1: B1 на $ C $ 2: C2.

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

В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A20), а на следующем снимке экрана демонстрируется формула массива в действии:

Подробное объяснение логики формулы представлено в отдельном разделе, а вот как использовать формулу для извлечения уникальных значений на листах Excel:

• Измените одну из формул в соответствии с вашим набором данных.
• Введите формулу в первую ячейку уникального списка (в данном примере B2).
• Если вы используете формулу массива, нажмите Ctrl + Shift + Enter. Если вы выбрали обычную формулу, как обычно, нажмите клавишу Enter.
• Скопируйте формулу вниз, насколько это необходимо, перетащив маркер заполнения. Поскольку обе формулы уникальных значений инкапсулированы в функцию ЕСЛИОШИБКА, вы можете скопировать формулу до конца таблицы, и она не будет загромождать ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.

Как получить различные значения в Excel (уникальные + 1 ^st повторяющихся вхождений)

Как вы, возможно, уже догадались из заголовка этого раздела, различных значений в Excel - это разные значения в списке, т.е.е. уникальные значения и первые экземпляры повторяющихся значений. Например:

Чтобы получить отдельный список в Excel, используйте следующие формулы.

Массив , отдельная формула (требуется нажать Ctrl + Shift + Enter):

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)), "")

Обычная отличная формула:

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, ИНДЕКС (СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0, 0), 0)), "")

Где:

• A2: A10 - это список источников.
• B1 - это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.

Если исходный список содержит пустые ячейки, отдельная формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Чтобы исправить это, немного улучшите формулу:

Формула массива для извлечения различных значений, исключая пробелы :

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10 & "") + ЕСЛИ ($ A $ 2: $ A $ 10 = " ", 1,0), 0))," ")

Аналогичным образом можно получить список различных значений , исключая пустые ячейки и ячейки с номерами :

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10 & "") + IF (ISTEXT ($ A $ 2: $ A $ 10 ) = ЛОЖЬ, 1,0), 0)), "")

Напоминаем, что в приведенных выше формулах A2: A10 - это исходный список, а B1 - это ячейка прямо над первой ячейкой отдельного списка.

На следующем снимке экрана показан результат обеих формул:

При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений, чувствительных к регистру. Для этого используйте следующую формулу массива, где A2: A10 - исходный список, а B1 - это ячейка над первой ячейкой отдельного списка:

Формула массива для получения различных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter)

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, ЧАСТОТА (ЕСЛИ (ТОЧНО ($ A $ 2: $ A $ 10, ТРАНСПОЗИРОВАТЬ ($ B $ 1: B1))), MATCH (ROW ($ A $ 2 : $ A $ 10), ROW ($ A $ 2: $ A $ 10)), ""), MATCH (ROW ($ A $ 2: $ A $ 10), ROW ($ A $ 2: $ A $ 10))), 0) ), "")

Как работает уникальная формула

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

Само собой разумеется, что формулы для извлечения уникальных и различных значений в Excel не являются ни тривиальными, ни простыми. Но при более внимательном рассмотрении вы можете заметить, что все формулы основаны на одном подходе - с использованием ИНДЕКС / ПОИСКПОЗ в сочетании с функциями СЧЁТЕСЛИ или СЧЁТЕСЛИ + ЕСЛИ.

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

= ЕСЛИОШИБКА (ИНДЕКС ($ A $ 2: $ A $ 10, ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)), "")

Для начала давайте отбросим очевидную функцию ЕСЛИОШИБКА, которая используется с единственной целью, чтобы исключить ошибки # Н / Д, когда количество ячеек, в которые вы скопировали формулу, превышает количество различных значений в исходном списке.

А теперь давайте разберем основную часть нашей четкой формулы:

1. СЧЁТЕСЛИ (диапазон, критерий) возвращает количество ячеек в диапазоне, которые соответствуют указанному условию.

   В этом примере COUNTIF ($ B $ 1: B1, $ A $ 2: $ A $ 10) возвращает массив из единиц и нулей в зависимости от того, присутствует ли где-либо какое-либо из значений исходного списка ($ A $ 2: $ A $ 10). в отдельном списке ($ B $ 1: B1). Если значение найдено, формула возвращает 1, иначе - 0.

   В частности, в ячейке B2 СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10) становится:

   COUNTIF («Distinct», {«Ронни»; «Дэвид»; «Салли»; «Джереми»; «Роберт»; «Дэвид»; «Роберт»; «Том»; «Салли»})

   и возвращает:

   {0; 0; 0; 0; 0; 0; 0; 0; 0}

   , потому что ни один из элементов исходного списка ( критериев ) не появляется в диапазоне , где функция ищет совпадение.В этом случае диапазон ($ B $ 1: B1) состоит из одного элемента - «Отдельный».

2. MATCH (lookup_value, lookup_array, [match_type]) возвращает относительную позицию значения поиска в массиве.
   В этом примере lookup_value равен 0, и, следовательно,:

   ПОИСКПОЗ (0, СЧЁТЕСЛИ ($ B $ 1: B1, $ A $ 2: $ A $ 10), 0)

   превращается в:

   ПОИСКПОЗ (0, { 0 ; 0; 0; 0; 0; 0; 0; 0; 0}, 0)

   и возвращает 1

   , потому что наша функция ПОИСКПОЗ получает первое значение, в точности равное значению поиска (как вы помните, значение поиска равно 0).

3. INDEX (array, row_num, [column_num]) возвращает значение в массиве на основе указанной строки и (необязательно) номеров столбцов.

   В этом примере INDEX ($ A $ 2: $ A $ 10, 1)

   становится:

   ИНДЕКС ({"Ронни"; "Дэвид"; "Салли"; "Джереми"; "Роберт"; "Дэвид"; "Роберт"; "Том"; "Салли"}, 1)

   и возвращает «Ронни».

   Когда формула копируется вниз по столбцу, отдельный список ($ B $ 1: B1) расширяется, поскольку вторая ссылка на ячейку (B1) является относительной ссылкой, которая изменяется в зависимости от относительного положения ячейки, в которую перемещается формула.

   Итак, при копировании в ячейку B3, СЧЁТЕСЛИ ($ B $ 1: B1 , $ A $ 2: $ A $ 10) изменяется на СЧЁТЕСЛИ ($ B $ 1: B2 , $ A $ 2: $ A $ 10) и становится :

   СЧЁТЕСЛИ ({"Отлично"; "Ронни"}, {"Ронни"; "Дэвид"; "Салли"; "Джереми"; "Роберт"; "Дэвид"; "Роберт"; "Том"; "Салли") }), 0)), "")

   и возвращает:

   {1; 0; 0; 0; 0; 0; 0; 0; 0}

   , потому что один «Ронни» находится в диапазоне $ B $ 1: B2.

   И затем MATCH (0, {1; 0 ; 0; 0; 0; 0; 0; 0; 0}, 0) возвращает 2, потому что 2 - это относительное положение первого 0 в массиве.

   И, наконец, ИНДЕКС ($ A $ 2: $ A $ 10, 2) возвращает значение из строки 2 ^и , то есть «Дэвид».

Если у вас все еще возникают трудности с вычислением формулы, вы можете ознакомиться со следующим учебным пособием, в котором подробно объясняется, как работает связь ИНДЕКС / ПОИСКПОЗ: ИНДЕКС и ПОИСКПОЗ как лучшая альтернатива ВПР в Excel.

Как уже упоминалось, другие формулы, обсуждаемые в этом руководстве, основаны на той же логике, с небольшими изменениями:

Формула уникальных значений - содержит еще одну функцию СЧЁТЕСЛИ, которая исключает из уникального списка все элементы, которые появляются в исходном списке более одного раза: СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A $ 2: $ A $ 10) <> 1 .

Формула различных значений, игнорирующая пробелы - здесь вы добавляете функцию ЕСЛИ, которая предотвращает добавление пустых ячеек в отдельный список: IF ($ A $ 2: $ A $ 13 = "", 1,0) .

Формула различных текстовых значений, игнорирующая числа - вы используете функцию ISTEXT, чтобы проверить, является ли значение текстом, и функцию IF, чтобы отклонить все другие типы значений, включая пустые ячейки: IF (ISTEXT ($ A $ 2: $ A $ 13) = ЛОЖЬ, 1,0) .

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

1. Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
2. Перейдите на вкладку Data > группу Sort & Filter и нажмите кнопку Advanced :
   
3. В диалоговом окне Advanced Filter выберите следующие параметры:
   • Проверить Переключиться на другое место .
   • В поле Диапазон списка убедитесь, что исходный диапазон отображается правильно.
   • В поле Копировать в введите самую верхнюю ячейку целевого диапазона.Помните, что вы можете копировать отфильтрованные данные только на активный лист .
   • Выберите Только уникальные записи

   

4. Наконец, нажмите кнопку OK и проверьте результат:
   

Обратите внимание, что хотя опция расширенного фильтра называется « Уникальные записи только », она извлекает различных значений , то есть уникальных значений и 1 ^st экземпляров повторяющихся значений.

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

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

А теперь давайте посмотрим, как работает инструмент Duplicate Remover.

Предположим, у вас есть сводная таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что эта сводная таблица содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или отдельные строки, включая уникальные и 1 ^st повторяющихся вхождений. В любом случае, с надстройкой Duplicate Remover работа выполняется за 5 быстрых шагов.

1. Выберите любую ячейку в исходной таблице и нажмите кнопку Duplicate Remover на вкладке Ablebits Data в группе Dedupe .
   

   Мастер удаления дубликатов запустится и выберет всю таблицу. Итак, просто нажмите Next , чтобы перейти к следующему шагу.
   

2. Выберите тип значения, которое вы хотите найти, и нажмите Далее :
   • Уникальный
   • Уникальные +1 ^st находок (отдельные)

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

   Наконечник. Как вы можете видеть на приведенном выше снимке экрана, есть также 2 варианта для повторяющихся значений , просто имейте это в виду, если вам нужно вывести какой-то другой рабочий лист.

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

   В этом примере мы хотим найти уникальные строки на основе значений во всех 3 столбцах ( Номер заказа , Имя и Фамилия ), поэтому мы выбираем все.
   

4. Выберите действие, которое нужно выполнить с найденными уникальными значениями.Вам доступны следующие варианты:
   • Выделите уникальные ценности
   • Выбрать уникальные значения
   • Определить в столбце статуса
   • Копировать в другое место

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

   В этом примере давайте выберем новый лист:
   

5. Нажмите кнопку Готово , и все готово!
   

Понравился этот быстрый и простой способ получить список уникальных значений или строк в Excel? Если это так, я рекомендую вам загрузить ознакомительную версию ниже и попробовать. Duplicate Remover, а также все другие инструменты для экономии времени, которые у нас есть, включены в Ultimate Suite for Excel.

Если вы довольны результатами и решили купить лицензию, мы рады предложить вам особую возможность:

Доступные загрузки

Найти уникальные значения в Excel - образец книги (.xlsx)
Ultimate Suite - ознакомительная версия (файл .zip)

Вас также может заинтересовать

как найти, отфильтровать, выбрать и выделить

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

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

• Уникальные значения - это элементы, которые появляются в наборе данных только один раз.
• Отличные значения - это все разные элементы в списке, то есть уникальные значения и 1 ^st экземпляров повторяющихся значений.

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

Как найти уникальные / отличные значения в Excel

Самый простой способ определить уникальные и отличные значения в Excel - использовать функцию ЕСЛИ вместе с СЧЁТЕСЛИ. В зависимости от типа значений, которые вы хотите найти, может быть несколько вариантов формулы, как показано в следующих примерах.

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

Чтобы найти отдельные или уникальные значения в списке, используйте одну из следующих формул, где A2 - первая, а A10 - последняя ячейка с данными.

Как найти уникальных значений в Excel:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2) = 1, "Уникальный", "")

Как получить различных значений в Excel:

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2) = 1, «Отличный», «»)

В отдельной формуле есть только одно небольшое отклонение во второй ссылке на ячейку, которое, однако, имеет большое значение:

Найдите уникальные / отличные строки в Excel

Аналогичным образом вы можете найти уникальные строки в таблице Excel на основе значений в 2 или более столбцах. В этом случае вам необходимо использовать функцию СЧЁТЕСЛИ вместо СЧЁТЕСЛИ для оценки значений в нескольких столбцах (до 127 пар диапазон / критерий можно оценить в одной формуле).

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

Формула для получения уникальных строк :

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2, $ B $ 2: $ B $ 10, $ B2) = 1, «Уникальная строка», «»)

Формула для поиска отдельных строк :

= ЕСЛИ (СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2) = 1, "Отдельная строка", "")

Найти уникальные / уникальные значения с учетом регистра в Excel

Если вы работаете с набором данных, где регистр имеет значение, вам понадобится немного более сложная формула массива.

Поиск с учетом регистра уникальных значений :

= ЕСЛИ (СУММ ((- ТОЧНЫЙ ($ A $ 2: $ A $ 10, A2))) = 1, "Уникальный", "")

Поиск с учетом регистра различных значений :

= ЕСЛИ (СУММ ((- ТОЧНО ($ A $ 2: $ A2, $ A2))) = 1, "Отдельно", "")

Поскольку обе являются формулами массива, обязательно нажмите Ctrl + Shift + Enter, чтобы заполнить их правильно.

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

Как фильтровать уникальные и уникальные значения в Excel

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

1. Примените одну из приведенных выше формул для определения уникальных / уникальных значений или строк.
2. Выберите данные и нажмите кнопку Фильтр на вкладке Данные . Или щелкните Сортировка и фильтр > Фильтр на вкладке Домашняя страница в группе Редактирование .
3. Щелкните стрелку фильтрации в заголовке столбца, содержащего формулу, и выберите значения для просмотра:
   

Как выбрать отдельные / уникальные значения

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

Чтобы быстро выбрать уникальный или уникальный список , включая заголовки столбцов , отфильтровать уникальные значения, щелкните любую ячейку в уникальном списке, а затем нажмите Ctrl + A.

Чтобы выбрать отдельные или уникальные значения без заголовков столбцов , отфильтруйте уникальные значения, выберите первую ячейку с данными и нажмите Ctrl + Shift + End, чтобы расширить выделение до последней ячейки.

Если вам сложно запомнить такое количество ярлыков, используйте этот визуальный способ: выберите весь уникальный / отдельный список, затем перейдите на вкладку Home > Найти и выбрать > Перейдите к Special и выберите Visible cells только .

Копировать уникальные или уникальные значения в другое место

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

• Выберите отфильтрованные значения с помощью мыши или вышеупомянутых ярлыков.
• Нажмите Ctrl + C, чтобы скопировать выбранные значения.
• Выберите верхнюю левую ячейку в целевом диапазоне (она может находиться на том же или другом листе) и нажмите Ctrl + V, чтобы вставить значения.

Как выделить уникальные и уникальные значения в Excel

Всякий раз, когда вам нужно выделить что-либо в Excel на основе определенного условия, перейдите прямо к функции условного форматирования. Более подробная информация и примеры приведены ниже.

Выделение уникальных значений в столбце (встроенное правило)

Самый быстрый и простой способ выделить уникальные значения в Excel - применить встроенное правило условного форматирования:

1. Выберите столбец данных, в котором вы хотите выделить уникальные значения.
2. На вкладке Home в группе Styles щелкните Conditional Formatting > Highlight Cells Rules > Duplicate Values ​​...
   
3. В диалоговом окне Duplicate Values ​​ выберите Unique в левом поле и выберите желаемое форматирование в правом поле, затем нажмите OK .
   

Наконечник. Если вас не устраивает какой-либо из предопределенных форматов, щелкните Custom Format... (последний пункт в выпадающем списке) и установите цвет заливки и / или шрифта по своему вкусу.

Как видите, выделение уникальных значений в Excel - самая простая задача, которую можно себе представить. Однако встроенное правило Excel работает только для элементов, которые появляются в списке только один раз. Если вам нужно выделить отдельные значения - уникальные и повторяющиеся вхождения 1 ^st - вам нужно будет создать собственное правило на основе формулы. Вам также потребуется создать настраиваемое правило для выделения уникальных строк на основе значений в одном или нескольких столбцах.

Выделение уникальных и уникальных значений в Excel (настраиваемое правило)

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

Выделить уникальные ценности

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

= СЧЁТЕСЛИ ($ A $ 2: $ A $ 10, $ A2) = 1

Где A2 - первая, а A10 - последняя ячейка применяемого диапазона.

Выделение различных значений

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

= СЧЁТЕСЛИ ($ A $ 2: $ A2, $ A2) = 1

Где A2 - самая верхняя ячейка диапазона.

Как создать правило на основе формулы

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

1. Перейдите на вкладку Home > группу Styles и щелкните Условное форматирование > Новое правило> Используйте формулу, чтобы определить, какие ячейки нужно форматировать .
2. Введите формулу в поле Формат значений, где эта формула верна. .
3. Нажмите кнопку Формат ... и выберите нужный цвет заливки и / или цвет шрифта.
4. Наконец, нажмите кнопку OK , чтобы применить правило.

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

На скриншоте ниже показаны оба правила в действии:

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

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

На следующем снимке экрана показано правило, выделяющее строки на основе различных чисел в столбце A:

Как выделить уникальные строки в Excel

Если вы хотите выделить строки на основе значений в 2 или более столбцах, используйте функцию СЧЁТЕСЛИМН, которая позволяет указать несколько критериев в одной формуле.

Выделить уникальные строки

= СЧЁТЕСЛИМН ($ A $ 2: $ A $ 10, $ A2, $ B $ 2: $ B $ 10, $ B2) = 1

Выделить отдельные строки (уникальные + 1 ^st повторяющихся вхождений)

= СЧЁТЕСЛИМН ($ A $ 2: $ A2, $ A2, $ B $ 2: $ B2, $ B2) = 1

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

Быстрый и простой способ найти и выделить уникальные значения в Excel

Как вы только что видели, Microsoft Excel предоставляет множество полезных функций, которые могут помочь вам идентифицировать и выделять уникальные значения на ваших листах. Однако все эти решения сложно назвать интуитивно понятными и простыми в использовании, поскольку они требуют запоминания нескольких различных формул.Конечно, для профессионалов Excel в этом нет ничего страшного :) Для тех пользователей Excel, которые хотят сэкономить свое время и силы, позвольте мне продемонстрировать быстрый и простой способ поиска уникальных значений в Excel.

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

1. Выберите любую ячейку в таблице, в которой вы хотите найти уникальные значения, и нажмите кнопку Duplicate Remover на вкладке Ablebits Data в группе Dedupe .
   

   Мастер запустится, и вся таблица будет выбрана автоматически. Итак, просто нажмите Next , чтобы перейти к следующему шагу.
   

   Наконечник. При первом использовании надстройки имеет смысл на всякий случай поставить отметку Создать резервную копию .

2. В зависимости от вашей цели выберите один из следующих вариантов, а затем щелкните Далее :
   • Уникальный
   • Уникальных +1 ^st экземпляров (отдельных)

   

3. Выберите один или несколько столбцов, в которых вы хотите проверить значения.
   В этом примере мы хотим найти уникальные имена на основе значений в 2 столбцах (имя и фамилия), поэтому мы выбираем оба.
   

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

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

   

Нажмите кнопку Finish и получите результат в секундах:

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

Если поиск повторяющихся и уникальных значений в Excel - обычная часть вашей повседневной работы, просто попробуйте этот инструмент дедупликации, и вы будете поражены результатами! Duplicate Remover, а также другие наши инструменты для экономии времени включены в Ultimate Suite for Excel. Если вы решили получить лицензию, не упустите эту особую возможность, которую мы предоставляем только посетителям нашего блога:

Доступные загрузки

Найти уникальные значения - примеры формул (.xlsx)
Ultimate Suite - пробная версия (файл .zip)

Вас также может заинтересовать

Excel UNIQUE function - самый быстрый способ найти уникальные значения

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

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

Введение УНИКАЛЬНОЙ функции в Excel 365 изменило все! То, что раньше было ракетной наукой, становится таким же простым, как азбука. Теперь вам не нужно быть экспертом по формулам, чтобы получить уникальные значения из диапазона на основе одного или нескольких критериев и расположить результаты в алфавитном порядке. Все делается с помощью простых формул, которые каждый может прочитать и настроить под свои нужды.

УНИКАЛЬНАЯ функция Excel

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

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

Синтаксис функции Excel UNIQUE следующий:

UNIQUE (массив; [by_col]; [точно_once])

Где:

Массив (обязательно) - диапазон или массив, из которого возвращаются уникальные значения.

By_col (необязательно) - логическое значение, указывающее, как сравнивать данные:

• ИСТИНА - сравнивает данные по столбцам.
• FALSE или пропущено (по умолчанию) - сравнивает данные по строкам.

Exactly_once (необязательно) - логическое значение, определяющее, какие значения считаются уникальными:

• ИСТИНА - возвращает значения, которые встречаются только один раз, что является понятием базы данных уникальным.
• FALSE или пропущено (по умолчанию) - возвращает все различные (разные) значения в диапазоне или массиве.

Примечание. В настоящее время УНИКАЛЬНАЯ функция доступна только с подписками Office 365 . Excel 2019 и Excel 2016 не поддерживают формулы динамических массивов, поэтому функция UNIQUE недоступна в этих версиях.

Базовая УНИКАЛЬНАЯ формула в Excel

Ниже представлена ​​формула уникальных значений Excel в ее простейшей форме.

Цель состоит в том, чтобы извлечь список уникальных имен из диапазона B2: B10. Для этого в D2 вводим следующую формулу:

= УНИКАЛЬНЫЙ (B2: B10)

Обратите внимание, что 2-й и 3-й аргументы опущены, потому что в нашем случае значения по умолчанию отлично работают - мы сравниваем строки друг с другом и хотим вернуть все разные имена в диапазоне.

Когда вы нажимаете клавишу Enter для завершения формулы, Excel выводит первое найденное имя в D2, переливая другие имена в ячейки ниже. В результате у вас есть все уникальные значения в столбце:

Если ваши данные находятся в столбцах от B2 до I2, установите для аргумента 2 ^nd значение ИСТИНА, чтобы сравнить столбцы друг с другом:

= УНИКАЛЬНЫЙ (B2: I2, ИСТИНА)

Введите указанную выше формулу в B4, нажмите Enter, и результаты отобразятся по горизонтали в ячейках справа.Таким образом, вы получите уникальные значения подряд:

УНИКАЛЬНАЯ функция Excel - советы и примечания

UNIQUE - это новая функция в Excel 365, которая, как и другие функции динамического массива, имеет несколько особенностей, о которых вам следует знать:

• Если массив, возвращаемый UNIQUE, является окончательным результатом (т. Е. Не передан другой функции), Excel динамически создает диапазон подходящего размера и заполняет его результатами. Итак, убедитесь, что у вас всегда достаточно пустых ячеек внизу и / или справа от ячейки, в которую вы вводите формулу, иначе возникает ошибка #SPILL.
• Результаты обновляются автоматически при изменении исходных данных. Однако новые записи, которые добавляются за пределами указанного массива, не включаются в формулу, если вы не измените ссылку на массив . Если вы хотите, чтобы массив автоматически реагировал на изменение размера исходного диапазона, преобразуйте диапазон в таблицу Excel и используйте структурированные ссылки или создайте динамический именованный диапазон.
• Динамические массивы между разными файлами Excel работают, только когда открыты обе книги.Если исходная книга закрыта, связанная формула UNIQUE вернет #REF! ошибка.

Как найти уникальные значения в Excel - примеры формул

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

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

Чтобы получить список значений, которые появляются в указанном диапазоне ровно один раз, установите для аргумента 3 ^rd UNIQUE значение TRUE.

Например, чтобы вытащить имена из списка победителей один раз, используйте эту формулу:

= УНИКАЛЬНЫЙ (B2: B10`` ИСТИНА)

Где B2: B10 - исходный диапазон, а аргумент 2 ^nd ( by_col ) имеет значение ЛОЖЬ или опущен, поскольку наши данные организованы в строки.

Найдите различные значения, встречающиеся более одного раза

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

УНИКАЛЬНЫЙ (ФИЛЬТР (диапазон , СЧЁТЕСЛИ (диапазон , диапазон )> 1))

Например, чтобы извлечь разные имена, которые встречаются в B2: B10 более одного раза, вы можете использовать эту формулу:

= УНИКАЛЬНЫЙ (ФИЛЬТР (B2: B10, СЧЁТЕСЛИ (B2: B10, B2: B10)> 1))

Как работает эта формула:

В основе формулы функция ФИЛЬТР отфильтровывает повторяющиеся записи на основе количества вхождений, возвращаемых функцией СЧЁТЕСЛИ.В нашем случае результатом СЧЁТЕСЛИ является этот массив значений:

{4; 1; 3; 4; 4; 1; 3; 4; 3}

Операция сравнения (> 1) изменяет указанный выше массив на значения ИСТИНА и ЛОЖЬ, где ИСТИНА представляет элементы, которые появляются более одного раза:

{TRUE; FALSE; TRUE; TRUE; TRUE; FALSE; TRUE; TRUE; TRUE}

Этот массив передается в FILTER, поскольку включает аргумент , сообщающий функции, какие значения включить в результирующий массив:

{"Эндрю"; "Давид"; "Эндрю"; "Эндрю"; "Давид"; "Эндрю"; "Давид"}

Как вы можете заметить, сохраняются только значения, соответствующие TRUE.

Вышеупомянутый массив переходит в аргумент UNIQUE массива и после удаления дубликатов выводит окончательный результат:

{"Андрей"; "Давид"}

Наконечник. Аналогичным образом вы можете фильтровать уникальные значения, которые встречаются более двух раз (> 2), более трех раз (> 3) и т. Д. Для этого просто измените число в логическом сравнении.

Найти уникальные значения в нескольких столбцах (уникальные строки)

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

Например, чтобы получить уникальное имя (столбец A) и фамилию (столбец B) победителей, мы вводим эту формулу в E2:

= УНИКАЛЬНЫЙ (A2: B10)

Нажатие клавиши Enter дает следующие результаты:

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

= УНИКАЛЬНЫЙ (A2: C10)

Удивительно просто, не правда ли? :)

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

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

С введением функций динамического массива эта проблема исчезла! Что вам нужно сделать, это просто деформировать функцию СОРТИРОВКИ вокруг обычной УНИКАЛЬНОЙ формулы, например:

СОРТИРОВКА (УНИКАЛЬНЫЙ (массив))

Например, чтобы извлечь уникальные значения в столбцы от A до C и расположить результаты от A до Z, используйте эту формулу:

= СОРТИРОВАТЬ (УНИКАЛЬНО (A2: C10))

По сравнению с приведенным выше примером вывод намного легче воспринимать и работать с ним.Например, мы можем ясно видеть, что Эндрю и Дэвид были победителями в двух разных видах спорта.

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

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

Для этого вместо ссылки на весь диапазон используйте амперсанд (&), чтобы объединить столбцы и поставить желаемый разделитель между ними.

В качестве примера мы объединяем имена в A2: A10 и фамилии в B2: B10, разделяя значения пробелом (""):

= УНИКАЛЬНЫЙ (A2: A10 & "" & B2: B10)

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

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

Чтобы извлечь уникальные значения с помощью условия, используйте функции Excel UNIQUE и FILTER вместе:

• Функция ФИЛЬТР ограничивает данные только значениями, соответствующими условию.
• Функция UNIQUE удаляет дубликаты из отфильтрованного списка.

Вот общая версия формулы отфильтрованных уникальных значений:

УНИКАЛЬНЫЙ (ФИЛЬТР (массив, диапазон_критериев = критериев ))

В этом примере давайте получим список победителей в конкретном виде спорта. Для начала мы вводим интересующий вид спорта в какую-нибудь ячейку, скажем, F1. Затем используйте формулу ниже, чтобы получить уникальные имена:

= УНИКАЛЬНЫЙ (ФИЛЬТР (A2: B10, C2: C10 = F1))

Где A2: B10 - это диапазон для поиска уникальных значений, а C2: C10 - это диапазон для проверки критериев.

Фильтровать уникальные значения на основе нескольких критериев

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

УНИКАЛЬНЫЙ (ФИЛЬТР (массив, ( диапазон_критериев1 = критериев1 ) * ( диапазон_критериев2 = критериев2 )))

Результатом формулы является список уникальных записей, для которых все указанные условия истинны.В Excel это называется логикой И.

Чтобы увидеть формулу в действии, давайте получим список уникальных победителей в спорте в G1 (критерий 1) и младше возраста в G2 (критерий 2).

С исходным диапазоном в A2: B10, спортом в C2: C10 (диапазон_критериев 1) и возрастом в D2: D10 (диапазон_критерия 2) формула принимает следующий вид:

= УНИКАЛЬНЫЙ (ФИЛЬТР (A2: B10, (C2: C10 = G1) * (D2: D10