Как в excel выбрать уникальные записи в столбце
Функция УНИК возвращает список уникальных значений в списке или диапазоне.
Возвращение уникальных значений из списка значений
Возвращение уникальных имен из списка имен
=УНИК(массив,[by_col],[exactly_once])
Функция УНИК имеет следующие аргументы:
Диапазон или массив, из которого возвращаются уникальные строки или столбцы
Аргумент by_col является логическим значением, указывающим, как проводить сравнение.
Значение ИСТИНА сравнивает столбцы друг с другом и возвращает уникальные столбцы
Значение ЛОЖЬ (или отсутствующее значение) сравнивает строки друг с другом и возвращает уникальные строки
[exactly_once]
Аргумент exactly_once является логическим значением, которое возвращает строки или столбцы, встречающиеся в диапазоне или массиве только один раз. Это концепция базы данных УНИК.
Значение ИСТИНА возвращает из диапазона или массива все отдельные строки или столбцы, которые встречаются только один раз
Значение ЛОЖЬ (или отсутствующее значение) возвращает из диапазона или массива все отдельные строки или столбцы
Массив может рассматриваться как строка или столбец со значениями либо комбинация строк и столбцов со значениями. В примерах выше массивы для наших формул УНИК являются диапазонами D2:D11 и D2:D17 соответственно.
Функция УНИК возвращает массив, который будет рассеиваться, если это будет конечным результатом формулы. Это означает, что Excel будет динамически создавать соответствующий по размеру диапазон массива при нажатии клавиши ВВОД. Если ваши вспомогательные данные хранятся в таблице Excel, тогда массив будет автоматически изменять размер при добавлении и удалении данных из диапазона массива, если вы используете Структурированные ссылки. Дополнительные сведения см. в статье Поведение рассеянного массива.
Приложение Excel ограничило поддержку динамических массивов в операциях между книгами, и этот сценарий поддерживается, только если открыты обе книги. Если закрыть исходную книгу, все связанные формулы динамического массива вернут ошибку #ССЫЛКА! после обновления.
Примеры
В этом примере СОРТ и УНИК используются совместно для возврата уникального списка имен в порядке возрастания.
В этом примере аргумент exactly_once имеет значение ИСТИНА, и функция возвращает только тех клиентов, которые обслуживались один раз. Это может быть полезно, если вы хотите найти людей, которые не получали дополнительное обслуживание, и связаться с ними.
В этом примере используется амперсанд (&) для сцепления фамилии и имени в полное имя. Обратите внимание, что формула ссылается на весь диапазон имен в массивах A2:A12 и B2:B12. Это позволяет Excel вернуть массив всех имен.
Если указать диапазон имен в формате таблицы Excel, формула автоматически обновляется при добавлении или удалении имен.
Чтобы отсортировать список имен, можно добавить функцию СОРТ: =СОРТ(УНИК(B2:B12&» «&A2:A12))
В этом примере сравниваются два столбца и возвращаются только уникальные значения в них.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Как в excel выбрать уникальные записи в столбце
Фильтр уникальных значений или удаление повторяющихся значений
Смотрите также всех открытых окнах. «ДАННЫЕ»-«Работа с данными»-«Проверка A, без повторений. большой таблицей и=ЕСЛИОШИБКА(ИНДЕКС(Фамилии;ПОИСКПОЗ(0;СЧЁТЕСЛИ($B1:$B1;Фамилии);0));»») список составить безCTRL+SHIFT+ENTER Отбор уникальных значений выбрать, отображаются наотносится кЭлемент правила выделения ячеек, и появится сообщение, следующих действий.Развернуть выполнить фильтрацию пона вкладке «Примечание:Готово! данных».Перед тем как выбрать вам необходимо выполнить
Это формула массива, повторяющихся данных. Например,, затем ее нужно (убираем повторы из
панеливременно скрыть ее.и выберите чтобы указать, сколькоВ разделе.
— или применитьГлавнаяМы стараемся какКак работает выборка уникальных На вкладке «Параметры» в уникальные значения в поиск уникальных значений
п.э. нажимаем не у нас есть скопировать вниз, например, списка) в MSпредварительного просмотра Выберите новый диапазонПовторяющиеся значения повторяющиеся значения были
Сведения о фильтрации уникальных значений и удалении повторяющихся значений
столбцыУстановите флажок условное форматирование на». можно оперативнее обеспечивать значений Excel? При разделе «Условие проверки» Excel, подготовим данные в Excel, соответствующие просто «Enter», а список сотрудников. Фамилии с помощью Маркера EXCEL, которая использовалась. ячеек на листе,
. удалены или остаютсявыберите один или несколькотолько уникальные записи — для подтвержденияФильтр уникальных значений и вас актуальными справочными выборе любого значения из выпадающего списка для выпадающего списка: определенному запросу, то сочетание клавиш «Ctrl» повторяются. Нам нужно заполнения. для игнорирования пропусковПродолжим идеи, изложенные в а затем развернитеВведите значения, которые вы количества уникальных значений. столбцов., а затем нажмите
добиться таких результатов, удаление повторяющихся значений материалами на вашем (фамилии) из выпадающего «Тип данных:» выберитеВыделите первый столбец таблицы нужно использовать фильтр. + «Shift» + сделать список фамилийСОВЕТ: в списке. После статье Отбор уникальных
Фильтрация уникальных значений
узел во всплывающем
хотите использовать и Нажмите кнопкуЧтобы быстро выделить всекнопку ОК
предполагается, что уникальные являются две сходные языке. Эта страница списка B1, в значение «Список». A1:A19. Но иногда нам
«Enter». У формулы для выпадающего списка,Список уникальных значений небольших изменений, формула
значений в MS окне еще раз нажмите кнопку Формат.
ОК столбцы, нажмите кнопку.
значения. задачи, поскольку цель
переведена автоматически, поэтому таблице подсвечиваются цветомВ поле ввода «Источник:»
Выберите инструмент: «ДАННЫЕ»-«Сортировка и нужно выделить все появятся фигурные скобки. фамилии в котором
можно создать разными для отбору уникальных EXCEL. Сначала отберем. Выберите правилоРасширенное форматирование, чтобы закрытьВыделить все Уникальные значения из диапазона
Выполните следующие действия. — для представления ее текст может все строки, которые введите =$F$4:$F$8 и
фильтр»-«Дополнительно». строки, которые содержатКопируем формулу по столбцу.
Удаление повторяющихся значений
не будут повторяться. способами, например, с с учетом 4-х из таблицы только и нажмите кнопкуВыполните следующие действия. сообщение.. скопирует на новоеВыделите диапазон ячеек или списка уникальных значений. содержать неточности и содержат это значение нажмите ОК.
В появившемся окне «Расширенный определенные значения по Получился такой список Для примера возьмем использованием Расширенного фильтра условий выглядит так: те строки, которые
Выделите одну или несколькоU тменить отменить изменения,Чтобы быстро удалить все место.
убедитесь, что активная Есть важные различия, грамматические ошибки. Для (фамилию). Чтобы вВ результате в ячейке фильтр» включите «скопировать отношению к другим
с уникальными фамилиями. такой список.
(см. статью Отбор=ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0; удовлетворяют заданным условиям,, чтобы открыть
ячеек в диапазоне, щелкните (или нажать столбцы, нажмите кнопкуПри удалении повторяющихся значений
ячейка находится в однако: при фильтрации нас важно, чтобы этом убедится в
B1 мы создали результат в другое строкам. В этомТеперь его можно использоватьСначала, можно сделать уникальных строк сЕСЛИ((($B$7:$B$25>=$F$7)+($B$7:$B$25<>=$G$7)+($C$7:$C$25 СЧЁТЕСЛИ($I$6:I6;$A$7:$A$25);»»);0));»») затем из этих всплывающее окно таблице или отчете
клавиши Ctrl +Снять выделение на значения в таблице. уникальных значений повторяющиеся эта статья была выпадающем списке B1 выпадающих список фамилий место», а в случаи следует использовать для создания выпадающего динамический список, т.е. помощью Расширенного фильтра),или так строк выберем толькоИзменение правила форматирования сводной таблицы. Z на клавиатуре)..
диапазоне ячеек илиНажмите кнопку значения будут видны вам полезна. Просим выберите другую фамилию. клиентов. поле «Поместить результат условное форматирование, которое списка. Как сделать если в список Сводных таблиц (см.
=ЕСЛИОШИБКА(ИНДЕКС($A$7:$A$25;ПОИСКПОЗ(0; уникальные значения из.На вкладке
Удаление дубликатов с промежуточными итогами или структурированных данных проблем
Нельзя удалить повторяющиеся значенияЕсли диапазон ячеек или таблице — единственныйданные > Дополнительно только временно. Тем вас уделить пару После чего автоматическиПримечание. Если данные для в диапазон:» укажите ссылается на значения такой список, смотрите добавим в ячейки
Условное форматирование уникальных или повторяющихся значений
лист Сводная таблицаЕСЛИ((($B$7:$B$25>=$F$7)*($B$7:$B$25<>=$G$7)*($C$7:$C$25 СЧЁТЕСЛИ($I$6:I6;$A$7:$A$25);»»);0));»») первого столбца. ПриВ разделеГлавная из структуры данных,
таблица содержит много
эффект. Другие значения
( не менее удаление секунд и сообщить, будут выделены цветом
выпадающего списка находятся $F$1. ячеек с запросом. в статье «Связанный фамилии, то адрес в файле примера)Примечание добавлении новых строквыберите тип правилав группе структурированный или, в 
столбцов, чтобы выбрать вне диапазона ячеекв
повторяющихся значений означает,
уже другие строки. на другом листе,Отметьте галочкой пункт «Только Чтобы получить максимально
выпадающий список в диапазона автоматически увеличится или через меню. В формуле использована в таблицу, списокнажмите кнопкуСтили котором содержится промежуточные несколько столбцов только или таблице негруппа что вы окончательное
вам, с помощью Такую таблицу теперь
то лучше для уникальные записи» и эффективный результат, будем Excel по алфавиту». и эта фамилия Данные/ Работа с функция ЕСЛИОШИБКА(), которая
уникальных значений будетФорматировать только уникальные илищелкните стрелку для итоги. Чтобы удалить может проще нажмите будет изменить илиСортировка и фильтр удаление повторяющихся значений. кнопок внизу страницы. легко читать и такого диапазона присвоить нажмите ОК. использовать выпадающий список,Другой способ создания войдет в выпадающий данными/ Удалить дубликаты. работает только начиная автоматически обновляться. повторяющиеся значенияУсловного форматирования дубликаты, необходимо удалить кнопку переместить. При удалении).
Повторяющееся значение входит в Для удобства также анализировать. имя и указатьВ результате мы получили в качестве запроса.
уникальных данных в список. Это нужно У каждого способа с версии MSПусть в имеется таблица.и выберите пункт структуры и промежуточные
Снять выделение всех повторяющихся данных, хранящихсяВ поле всплывающего окна котором все значения приводим ссылку наСкачать пример выборки из
его в поле список данных с Это очень удобно списке – это для того, чтобы есть свои преимущества EXCEL 2007. О с повторяющимися значениямиВ спискеУправление правилами итоги. Для полученияи выберите в разделе в первое значениеРасширенный фильтр
Отбор уникальных значений в MS EXCEL с условиями
в по крайней оригинал (на английском списка с условным «Источник:». В данном уникальными значениями (фамилии если нужно часто убрать дубликаты в не менять адрес и недостатки. Но, том как ее в первом столбце,Формат все, чтобы открыть дополнительных сведений отображаетсястолбцы
в списке, новыполните одно из мере одна строка языке) . форматированием.
случае это не без повторений). менять однотипные запросы имеющемся списке. Читайте диапазона выпадающего списка
в этой статье заменить, читайте в
например список названийИзмените описание правила всплывающее окно Структура списка данныхвыберите столбцы. других идентичных значений указанных ниже действий. идентичны всех значений
В Excel существует несколькоПринцип действия автоматической подсветки обязательно, так как для экспонирования разных об этом в вручную. нам требуется, чтобы статье Функция ЕСЛИОШИБКА() компаний.выберитеДиспетчер правил условного форматирования на листе «и»Примечание: удаляются.
Чтобы отфильтровать диапазон ячеек
в другую строку.
способов фильтр уникальных
строк по критерию
у нас все
Теперь нам необходимо немного строк таблицы. Ниже статье «Как удалитьКак сделать динамический диапазон при добавлении новых в MS EXCEL.Отберем из таблицы толькоуникальные. удалить промежуточные итоги.
Данные будут удалены изПоскольку данные будут удалены или таблицы в Сравнение повторяющихся значений значений — или запроса очень прост. данные находятся на модифицировать нашу исходную детально рассмотрим: как дубли в Excel». в Excel строк в исходнуюЕсли значения Стоимости и те строки, которые
илиВыполните одно из действий,Примечание: всех столбцов, даже окончательно, перед удалением программе: зависит от того, удаление повторяющихся значений: Каждое значение в одном рабочем листе.
таблицу. Выделите первые
сделать выборку повторяющихсяКак настроить Excel,, читайте в статье таблицу, список уникальных Даты контракта соответствуют удовлетворяют заданным условиям,повторяющиеся указанных ниже. Условное форматирование полей в если вы не повторяющихся значений рекомендуетсяВыберите что отображается вЧтобы фильтр уникальных значений, столбце A сравниваетсяВыборка ячеек из таблицы 2 строки и ячеек из выпадающего чтобы при попытке «Чтобы размер таблицы значений автоматически обновлялся, 4-м условиям, то которые приведены в.Чтобы добавить условное форматирование,
области «Значения» отчета выбрали всех столбцов скопировать исходный диапазонфильтровать список на месте ячейке, не базового нажмите кнопку со значением в по условию в
Создание списка в Excel без повторов.
выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» списка. ввести повторяющееся значение, Excel менялся автоматически» поэтому здесь построен при отборе уникальных табличке ниже.Нажмите кнопку нажмите кнопку сводной таблицы по на этом этапе. ячеек или таблицу. значения, хранящегося вданных > ячейке B1. Это 
Excel: или нажмите комбинациюДля примера возьмем историю выходило окно предупреждения тут. список с использованием это название компанииОтобранные строки выделим УсловнымФорматСоздать правило уникальным или повторяющимся Например при выборе в другой лист
Чтобы скопировать в другое ячейке. Например, еслиСортировка и фильтр > позволяет найти уникальныеВыделите табличную часть исходной горячих клавиш CTRL+SHIFT+=.
взаиморасчетов с контрагентами, об этом, смотритеМы выделяем ячейки формул. учитывается. Если хотя форматированием.для отображения во 
для отображения во значениям невозможно. Столбец1 и Столбец2, или книгу. место результаты фильтрации: у вас есть Дополнительно
значения в таблице
таблицы взаиморасчетов A4:D21У нас добавилось 2 как показано на в статье «Запретить А2:А9. Для созданияПримечание бы не выполняется 
Затем из этих строк всплывающем окне всплывающем окне 
Быстрое форматирование но не Столбец3Выполните следующие действия.Нажмите кнопку то же значение. Excel. Если данные
и выберите инструмент: пустые строки. Теперь рисунке: вводить повторяющиеся значения динамического диапазона, мы. Как видно из 1 условие, то выберем только уникальные
Формат ячеекСоздание правила форматированияВыполните следующие действия. используется для поискаВыделите диапазон ячеек илиКопировать в другое место даты в разныхЧтобы удалить повторяющиеся значения,
совпадают, тогда формула «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать в ячейку A1В данной таблице нам в Excel» здесь. заполнили диалоговое окно рисунков выше, в название компании не значения из первого
Как сделать выборку в Excel из списка с условным форматированием
..Выделите одну или несколько дубликатов «ключ» — убедитесь, что активная. ячейках, один в нажмите кнопку возвращает значение ИСТИНА формулу для определения введите значение «Клиент:». нужно выделить цветомВ Excel можно «Создание имени» функции файле примера использованы учитывается. Если нужно столбца, т.е. толькоВыберите номер, шрифт, границыУбедитесь, что выбран соответствующий ячеек в диапазоне, значение ОБА Столбец1 ячейка находится вВ поле формате «3/8/2006», аданные > Работа с и для целой форматируемых ячеек».Пришло время для создания все транзакции по сделать любой тест
Выбор уникальных и повторяющихся значений в Excel
«Присвоить имя» на Элементы управления формы ограничиться, например 2-мя те компании, у
и заливка формат, лист или таблица таблице или отчете & Столбец2. Если дубликат таблице.Копировать другой — как данными строки автоматически присваиваетсяЧтобы выбрать уникальные значения выпадающего списка, из конкретному клиенту. Для для любой категории
закладке «Формулы» так. для управления выделением условиями (только Стоимость), которых Стоимость и
- который нужно применять, в списке
- сводной таблицы. находится в этих
- На вкладкевведите ссылку на «8 мар «2006> новый формат. Чтобы из столбца, в которого мы будем
- переключения между клиентами людей. (для школьников,Теперь в столбце
строк с помощью то удалите часть Дата контракта находится если значение в
Показать правила форматирования для
На вкладке столбцах, затем всейданные ячейку. г. значения должныУдалить повторения формат присваивался для
поле ввода введите выбирать фамилии клиентов будем использовать выпадающий для производства, для
В будем формировать Условного форматирования. формулы +($C$7:$C$25>=$G$7)+($C$7:$C$25 в заданных диапазонах. ячейке удовлетворяет условию
изменения условного форматирования,Главная строки будут удалены,
- нажмите кнопкуКроме того нажмите кнопку быть уникальными..
- целой строки, а формулу: =$A4=$B$1 и в качестве запроса. список. Поэтому в опроса, анкету, т.д.).
- список с уникальными,Когда делаемНе забудьте, что формулу
Решение приведено в файле и нажмите кнопку начинается. При необходимостив группе
включая другие столбцыУдалить повторенияСвернуть диалоговое окноУстановите флажок перед удалениемЧтобы выделить уникальные или не только ячейке нажмите на кнопкуПеред тем как выбрать первую очередь следует Смотрите статью «Как не повторяющимися фамилиями.в Excel массива нужно вводить
примера на листеОК выберите другой диапазон
- стиль в таблицу или(в группевременно скрыть всплывающее дубликаты: повторяющиеся значения, команда
- в столбце A, «Формат», чтобы выделить уникальные значения из подготовить содержание для сделать тест в Для этого ввыпадающий список в ячейку EXCEL Уникальные. В его. Вы можете выбрать
ячеек, нажав кнопку
щелкните маленькую стрелку диапазон.Работа с данными окно, выберите ячейкуПеред удалением повторяющиесяУсловного форматирования мы используем смешанную одинаковые ячейки цветом. списка сделайте следующее: выпадающего списка. Нам Excel». ячейку В2 напишемс большим списком, с помощью одновременного основе лежит формула более одного формата.СвернутьУсловное форматирование
Нажмите кнопку). на листе и
значения, рекомендуется установитьв группе ссылку в формуле Например, зеленым. ИПерейдите в ячейку B1 нужны все ФамилииЕсли Вы работаете с такую формулу. то нужно этот нажатия массива из статьи Форматы, которые можново всплывающем окнеи затем щелкнитеОКВыполните одно или несколько нажмите кнопку для первой попыткестиль =$A4. нажмите ОК на и выберите инструмент
Как получить список уникальных значений
В статье описано, как получить список уникальных значений в столбце с помощью формулы и как настроить эту формулу для различных наборов данных. Вы также узнаете, как быстро получить отдельный список с помощью расширенного фильтра Excel и как извлечь уникальные записи с помощью Duplicate Remover.
В нескольких недавних статьях мы обсудили различные методы подсчета и поиска уникальных значений в Excel. Если у вас была возможность прочитать эти руководства, вы уже знаете, как получить этот список при помощи идентификации, фильтрации и копирования. Но это немного длинный и далеко не единственный способ извлечения уникальных значений в Excel. Вы можете сделать это намного быстрее, используя специальную формулу. И сейчас я покажу вам этот и несколько других приёмов.
Базовые формулы для получения уникальных значений.
Чтобы избежать путаницы, сначала давайте договоримся о том, что мы называем уникальными значениями в Excel.
Уникальные значения — это значения, которые присутствуют в списке только один раз. Например:
Чтобы получить список уникальных значений в Excel, используйте одну из следующих формул.
Формула уникальных значений массива (заполняется нажатием Ctrl + Shift + Enter):
Можно воспользоваться и обычной формулой (вводится нажатием Enter):
Примечание. Поскольку формула ссылается на ячейку, расположенную над первой ячейкой создаваемого списка, который обычно является заголовком столбца (B1 в этом примере), то убедитесь, что ваш заголовок имеет уникальное имя, которое больше нигде в этом столбце не появляется.
В этом примере мы извлекаем уникальные имена из столбца A (точнее из диапазона A2: A10), а следующий скриншот демонстрирует формулу в действии:
Вот наш порядок действий:
- Измените любую из формул в соответствии с вашим диапазоном данных.
- Введите ее в первую ячейку, с которой начнётся формирование списка (в данном примере B2).
- Если вы используете формулу массива, нажмите Ctrl + Shift + Enter . Если вы выбрали обычную, нажмите просто клавишу Enter .
- Скопируйте вниз настолько, насколько это необходимо, перетащив мышкой маркер заполнения. Поскольку обе формулы заключены в функцию ЕСЛИОШИБКА, вы можете скопировать вниз с запасом. Это не испортит ваши данные какими-либо ошибками, независимо от того, сколько уникальных значений было извлечено.
Как извлечь различные значения.
Различные значения — появляются в перечне данных хотя бы один раз. Это все уникальные и первое вхождение повторяющихся значений.
Чтобы получить их список в Excel, используйте следующие формулы.
Формула массива (требуется нажать Ctrl + Shift + Enter ):
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; ПОИСКПОЗ(0; ИНДЕКС(СЧЁТЕСЛИ($B$1:B1; $A$2:$A$13); 0; 0); 0)); «»)
- A2: A13 — это список источников.
- B1 — это ячейка над первой ячейкой отдельного списка. В этом примере отдельный список начинается с ячейки B2 (это первая ячейка, в которую вы вводите формулу), поэтому вы ссылаетесь на B1.
Как извлечь значения, игнорируя пустые ячейки
Если исходный список содержит пустые ячейки, формула, которую мы только что обсудили, вернет ноль для каждой пустой строки, что может быть проблемой. Это вы и наблюдаете на скриншоте чуть выше. Чтобы исправить это, сделаем несколько небольших корректировок.
Формула массива для извлечения различных значений, исключая пустые ячейки:
Аналогичным образом вы можете получить список различных значений, исключая пустые ячейки и ячейки с числами:
Напоминаем, что в приведенных выше формулах A2: A13 – это исходный список, а B1 – ячейка прямо над первой позицией формируемого списка.
На этом скриншоте показан результат отбора:
Быть может, кому-то будет полезна еще одна формула –
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$13; АГРЕГАТ(15;6;(СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1) / (ПОИСКПОЗ($A$2:$A$13;$A$2:$A$13;0)=СТРОКА($A$2:$A$13)-СТРОКА($A$2)+1); ЧСТРОК($A$2:$A2)));»»)
Она работает с числами и текстом, игнорирует пустые ячейки.
Как извлечь отдельные значения с учетом регистра в Excel
При работе с данными, чувствительными к регистру, такими как пароли, имена пользователей или имена файлов, вам может потребоваться список отдельных значений с учетом заглавных и прописных букв.
Для этого используйте формулу массива, где A2: A10 — это исходный список, а B1 — это ячейка над первой ячейкой отдельного списка.
Формула массива для получения различных значений с учетом регистра (требуется нажатие Ctrl + Shift + Enter )
Как видите, при отборе регистр здесь имеет значение.
Отбор уникальных значений по условию.
Представим, что у нас есть таблица с данными о продажах. Нам необходимо определить, какие наименования товаров заказывал определенный покупатель.
Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем уникальные наименования товаров.
В ячейке G2 указываем нужного нам заказчика, а в H2 записываем эту формулу массива:
Не забудьте, что формулу массива нужно вводить в ячейку EXCEL с помощью одновременного нажатия CTRL+SHIFT+ENTER . Копируем ее по столбцу вниз при помощи маркера заполнения. Получаем список из четырех позиций.
Усложним задачу. Определим список не только для этого покупателя, но также и для определённого менеджера.
Вот наша формула массива:
Как видите, теперь товаров всего два. В подсчете принимают участие только те строки, которые удовлетворяют сразу двум условиям: должно совпасть название фирмы и фамилия менеджера. Только из них мы извлекаем уникальные названия товаров.
В случае, если условий будет больше, нужно просто добавить соответствующий критерий в функцию ЕСЛИ и изменить число 2 на 3 или большее (в зависимости от количества условий).
Извлечь уникальные значения из диапазона.
Формулы, которые мы описывали выше, позволяют сформировать список значений из данных определенного столбца. Но часто речь идет о нескольких столбцах, то есть о диапазоне данных. К примеру, вы получили несколько списков товаров из различных файлов и расположили их в соседних столбцах.
Используем формулу массива
Здесь A2:C9 обозначает диапазон, из которого вы хотите извлечь уникальные значения. E1 – это первая ячейка столбца, в который вы хотите поместить результат. $2:$9 указывает на строки, содержащие данные, которые вы хотите использовать. $A:$C указывает на столбцы, из которых вы берёте исходные данные. Пожалуйста, измените их на свои собственные.
Нажмите Shift + Ctrl + Enter , а затем перетащите маркер заполнения, чтобы вывести уникальные значения, пока не появятся пустые ячейки.
Как видите, извлекаются все уникальные и первые вхождения дубликатов.
Встроенный инструмент удаления дубликатов.
Начиная с Excel 2007 функция удаления дубликатов является стандартной. Найти ее можно на вкладке Данные > Удаление дубликатов.
Вам нужно при помощи птички указать столбцы, в которых нужно найти и удалить повторяющиеся значения. Если сделать так, как на скриншоте, то в таблице останутся только уникальные пары «Заказчик – Товар». Остальное будет удалено. Если включить только флажок «Заказчик», то останется только по одной строке для каждого заказчика и т.д.
Использование расширенного фильтра.
Если вы не хотите тратить время на выяснение загадочных поворотов формул, вы можете быстро получить список уникальных значений с помощью расширенного фильтра. Подробные инструкции приведены ниже.
- Выберите столбец данных, из которого вы хотите извлечь отдельные значения.
- Перейдите на вкладку «Данные» > группа «Сортировка и фильтр» и нажмите кнопку «Дополнительно» .
- В диалоговом окне Расширенный фильтр выберите следующие параметры:
- Установите флажок Копировать в другое место .
- В поле Исходный диапазон убедитесь, что он указан правильно.
- В параметре Поместить результат в… укажите самую верхнюю ячейку целевого диапазона. Помните, что вы можете копировать отфильтрованные данные только на текущий лист.
- Выберите пункт «Только уникальные записи».
- Наконец, нажмите кнопку ОК и проверьте результат.
Как видите, мы проверили колонку B, и затем список уникальных наименований товара, найденных в ней, поместили в столбец K.
Обратите внимание, что хотя опция расширенного фильтра называется «Только уникальные записи», она извлекает различные значения, то есть уникальные и первые вхождения повторяющихся.
Теперь немного усложним задачу.
Если требуется искать записи не по одному, а по нескольким столбцам, то можно их предварительно «склеить» при помощи функции СЦЕПИТЬ.
Записываем это в столбец F и копируем вниз. Получаем вспомогательную колонку.
В качестве исходного диапазона мы по-прежнему выбираем данные, из которых извлекаем уникальные значения. Теперь это два столбца – A и B.
Но искать уникальные мы по-прежнему можем только в одном столбце. Вот для этого нам и пригодится вспомогательная колонка F с объединенными данными. Ее то мы и указываем в поле «Диапазон условий».
Все остальное – так же, как и в предыдущем примере.
В результате мы получили все имеющиеся в таблице комбинации «Заказчик — Товар» на основе данных во вспомогательном столбце F.
Думаю, вы понимаете, что аналогичные действия можно произвести и с тремя столбцами (например Фамилия – Имя – Отчество). Главное условие – исходный диапазон должен быть непрерывным, то есть все столбцы должны находиться рядом.
Как видите формулы здесь не нужны. Однако, если исходные данные изменятся, то все манипуляции придется повторять заново.
Извлечение уникальных значений с помощью Duplicate Remover.
В заключительной части этого руководства я покажу вам интересное решение для поиска и извлечения различных и уникальных значений в таблицах Excel. Это решение сочетает в себе универсальность формул Excel и простоту расширенного фильтра. Кроме того, здесь есть несколько уникальных функций:
- Найти и извлечь уникальные или различные значения на основе записей в одном или нескольких столбцах.
- Найти, выделить и скопировать уникальные значения в любое другое место в той же или другой книге Excel.
А теперь давайте посмотрим, как работает инструмент Duplicate Remover.
Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, включая уникальные и первые повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover работа выполняется за несколько шагов.
- Выберите любую ячейку в исходной таблице и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.
Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.
- Выберите тип значения, который вы хотите найти, и нажмите Далее :
- Уникальные
- Уникальные + 1 е вхождения (различные)
В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:
На заметку. Как вы можете видеть на приведенном выше скриншоте, есть также 2 варианта поиска дубликатов. Просто имейте это в виду, если нужно будет искать повторы в таблице.
- Выберите один или несколько столбцов для проверки уникальных значений.
В этом примере мы хотим убрать все повторяющиеся значения на основе значений в 2 столбцах ( заказчик и товар), поэтому мы выбираем только нужные нам столбцы.
В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.
Думаю, нам не нужны пустые строки, которые могут случайно встретиться при объединении данных из разных таблиц. Поэтому отмечаем такжеSkip empty cells.
Если вдруг в наших записях случайно появились лишние пробелы, то, думаю, стоит их игнорировать. Поэтому отмечаем также Ignore extra spaces.
Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому не трогаем опцию Case-sensitive match.
- Выберите действие, которое нужно выполнить с найденными значениями. Вам доступны следующие варианты:
- Выделить цветом.
- Выбрать и выделить.
- Отметить в столбце статуса.
- Копировать в другое место.
Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).
В этом примере давайте выберем новый лист:
- Нажмите кнопку « Готово» , и все готово!
В итоге у нас осталось всего 20 записей.
Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.
В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.
Как быстро объединить несколько файлов Excel — Мы рассмотрим три способа объединения файлов Excel в один: путем копирования листов, запуска макроса VBA и использования инструмента «Копировать рабочие листы» из надстройки Ultimate Suite. Намного проще обрабатывать данные в…
6 примеров — как консолидировать данные и объединить листы Excel в один — В статье рассматриваются различные способы объединения листов в Excel в зависимости от того, какой результат вы хотите получить: объединить все данные с выбранных листов,объединить несколько листов с различным порядком столбцов,объединить…
Как работать с мастером формул даты и времени — Работа со значениями, связанными со временем, требует глубокого понимания того, как функции ДАТА, РАЗНДАТ и ВРЕМЯ работают в Excel. Эта надстройка позволяет быстро выполнять вычисления даты и времени и без особых…
Как найти и выделить уникальные значения в столбце — В статье описаны наиболее эффективные способы поиска, фильтрации и выделения уникальных значений в Excel. Ранее мы рассмотрели различные способы подсчета уникальных значений в Excel. Но иногда вам может понадобиться только просмотреть уникальные…
Подсчет уникальных значений в Excel — В этом руководстве вы узнаете, как посчитать уникальные значения в Excel с помощью формул и как это сделать в сводной таблице. Мы также разберём несколько примеров счёта уникальных текстовых и числовых…
Отбор уникальных значений в EXCEL с условиями
Продолжим идеи, изложенные в статье Отбор уникальных значений в MS EXCEL . Сначала отберем из таблицы только те строки, которые удовлетворяют заданным условиям, затем из этих строк выберем только уникальные значения из первого столбца. При добавлении новых строк в таблицу, список уникальных значений будет автоматически обновляться.
Пусть в имеется таблица с повторяющимися значениями в первом столбце, например список названий компаний.
Отберем из таблицы только те строки, которые удовлетворяют заданным условиям, которые приведены в табличке ниже.
Затем из этих строк выберем только уникальные значения из первого столбца, т.е. только те компании, у которых Стоимость и Дата контракта находится в заданных диапазонах.
Решение приведено в файле примера на листе Уникальные. В его основе лежит формула массива из статьи Отбор уникальных значений (убираем повторы из списка) в MS EXCEL , которая использовалась для игнорирования пропусков в списке. После небольших изменений, формула для отбору уникальных с учетом 4-х условий выглядит так:
Примечание . В формуле использована функция ЕСЛИОШИБКА() , которая работает только начиная с версии MS EXCEL 2007. О том как ее заменить, читайте в статье Функция ЕСЛИОШИБКА() в MS EXCEL .
Если значения Стоимости и Даты контракта соответствуют 4-м условиям, то при отборе уникальных это название компании учитывается. Если хотя бы не выполняется 1 условие, то название компании не учитывается. Если нужно ограничиться, например 2-мя условиями (только Стоимость), то удалите часть формулы +($C$7:$C$25>=$G$7)+($C$7:$C$25<=$G$8) , а 4 замените на 2. Решение для одного условия приведено на отдельном листе файла примера.
Не забудьте, что формулу массива нужно вводить в ячейку EXCEL с помощью одновременного нажатия CTRL+SHIFT+ENTER , затем ее нужно скопировать вниз, например, с помощью Маркера заполнения .
СОВЕТ: Список уникальных значений можно создать разными способами, например, с использованием Расширенного фильтра (см. статью Отбор уникальных строк с помощью Расширенного фильтра ), Сводных таблиц (см. лист Сводная таблица в файле примера ) или через меню Данные/ Работа с данными/ Удалить дубликаты . У каждого способа есть свои преимущества и недостатки. Но, в этой статье нам требуется, чтобы при добавлении новых строк в исходную таблицу, список уникальных значений автоматически обновлялся, поэтому здесь построен список с использованием формул.
Примечание . Как видно из рисунков выше, в файле примера использованы Элементы управления формы для управления выделением строк с помощью Условного форматирования .
Одно условие отбора
В файле примера добавлено 2 листа с примерами отбора значений по 1 критерию (текстовый и числовой).