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

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

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

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

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

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

Используя базисный фильтр в Excel с настройкой «не равно», вы сможете просто отыскать эти типы данных.

Щелкните Фильтр на вкладке Данные.

  1. Изберите столбцы, к которым вы желаете применить фильтр на собственном листе.
  2. Перейти к Данные вкладку и щелкните Фильтр в разделе ленты «Сортировка и фильтр». Это приведет к применению стрелки к заголовку столбца для фильтра.
  3. Щелкните значок стрелка чтоб показать фильтр и переместите курсор на Число, Датировать, либо же Текстовые фильтры зависимо от типа данных.
  4. Во всплывающем меню изберите Не равно. Если вы не видите эту опцию, изберите Пользовательский фильтр понизу.
  5. Подтвердите 1-ое раскрывающееся меню не равно а потом введите значение в поле справа.
  6. Нажмите В ПОРЯДКЕ.

Пользовательский автофильтр в Excel

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

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

А когда вы закончите применять фильтр, просто опять нажмите клавишу «Фильтр» на ленте, чтоб выключить его. Опосля этого ваш лист возвратится в обычное состояние.

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

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

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

Нажмите Advanced на вкладке Data.

  1. Выделите ячейки на листе, к которым вы желаете применить фильтр. Имейте в виду, что если вы выберете весь столбец, это отфильтрует заголовок столбца, если он у вас есть.
  2. Перейти к Данные вкладку и щелкните Передовой в разделе ленты «Сортировка и фильтр».
  3. Во всплывающем окне «Расширенный фильтр» изберите пространство для отфильтрованных данных в разделе «Действие». Вы сможете выбрать Отфильтровать перечень на месте либо же Копировать в другое пространство (подробнее см. ниже). Если вы решили скопировать в другое пространство, спуститесь к Скопировать в поле и введите положение. К примеру, вы сможете выбрать спектр ячеек на право, перетаскивая эти ячейки.
  4. Подтвердите Спектр перечня чтоб убедиться, что вы фильтруете конкретно те данные, которые для вас необходимы.
  5. Установите флаг для Лишь уникальные записи. Это важнейшая часть фильтра для поиска этих отличительных значений.
  6. Нажмите В ПОРЯДКЕ.

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

Фильтрация уникальных значений на месте

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

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

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

Резвый поиск данных при фильтрации неповторимых значений в Excel

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

А если вы используете Гугл Таблицы в дополнение к Excel, узнайте, как создавать и применять представления фильтров в Таблицах.

Excel формула уникальные значения из столбца

Здравствуй почетаемый юзер!

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

Интересно почитать:  Как добавить строку в таблице excel между строками

Перечень неповторимых значений может быть сделать 6-ю методами:

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

Это весьма обычный метод для хозяев Excel выше 2007 версии как произвести отбор неповторимых значений. Для вас необходимо на вкладке «Данные», в разделе «Работа с данными», применять специальную команду «Удалить дубликаты».

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

Сделать перечень неповторимых значений при помощи расширенного фильтра

Это также не непростой метод произвести отбор неповторимых значений в таблице. Употреблять этот инструмент может быть на вкладке «Данные», позже выбрать «Фильтр», и в конце концов «Расширенный фильтр», этот путь подступает для Excel 2003, а вот обладатели наиболее молодых версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Добавочно». Большой плюс этого метода в том, что вы сможете сделать новейший перечень неповторимых значений в другом месте. Опосля возникновения диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пт «Скопировать итог в другое пространство», позже указываем спектр с вашими данными в поле «Начальный спектр», по мере необходимости указываем аспект отбора, но для общего отсева поле оставляем пустым «Спектр критериев», в 3-ем поле «Поместить итог в спектр» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Лишь уникальные записи» и жмем «Ок». Если же для вас не надо никуда переносить ваши данные, то просто установите флаг для пт «Фильтровать перечень на месте», данные не пострадают, произойдет наложение обычного фильтра.

Внимание! Если программка воспрещает для вас переносить отфильтрованные данные на иной лист, вы просто запустите «Расширенный фильтр» на том листе, куда для вас нужно перенести отобранные уникальные значения.

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

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

Пример 1. Для вас необходимо пронумеровать, уникальные, значение в перечне значений, для этого необходимо применять функцию ЕСЛИ в формуле последующего вида:

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;"«)

Сущность формулы в том, что она инспектирует сколько раз, текущее значение встречается в вашем спектре (начиная с начала), и если это значение равно 1, другими словами это 1-ое неповторимое значение, формула ставит поочередно ворачивающий номер по порядку. Сейчас можно произвести отбор неповторимых значений, которые были ранее пронумерованы. Создать это может быть в любом из примыкающих столбиков используя функцию ВПР и копируя ее вниз:

=ЕСЛИ(МАКС(A1:A100)

С ростом богатства вырастают и заботы. Гораций

Информация о веб-сайте

Инструменты и опции

Excel Windows
и
Excel Macintosh

Вопросцы и решения

Работа и общение

Работа форума и веб-сайта

Функции листа Excel

= Мир MS Excel/Статьи о Excel

  • 1
  • 2
  • 3

Из него Для вас нужно извлечь все уникальные фамилии продавцов. Т.е. должен получиться таковой перечень:

ПРОБЛЕМА: Как формулами извлечь уникальные значения?

РЕШЕНИЕ: Формула массива (вводится нажатием Ctrl+Shift+Enter):

КАК ЭТО РАБОТАЕТ: Функция ПОИСКПОЗ, сравнивающая два массива возвращает ИСТИНА лишь в этом случае, если вхождение искомого значения в массив является первым. В итоге работы ПОИСКПОЗ мы получаем массив: <ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ:ИСТИНА:ЛОЖЬ и т.д.>.
При помощи функции ЕСЛИ мы сравниваем получившийся массив с массивом <1:2:3:4:5:6:7:8 и т.д.>, приобретенном в итоге работы функции СТРОКА с данным спектром и в случае если ПОИСКПОЗ возвратил ИСТИНА получаем номер строчки вхождения.

Фактически говоря задачка решена. Сейчас остаётся лишь оформить результат перечнем. Для этого используем функцию НАИБОЛЬШИЙ, которая создаст вариативный ряд поначалу из чисел, позже из значений ЛОЖЬ и функцию ИНДЕКС, которая вернёт нам нужные текстовые значения из соответственного массива. В жёлтых ячейках результат:

МИНУСЫ: Формулы массивов очень замедляют скорость пересчёта листа.

ОБЛАСТЬ ПРИМЕНЕНИЯ: Неважно какая версия Excel

ПРИМЕЧАНИЯ: Для устранения значения ошибки можно применять проверку на ошибку согласно Вашей версии Excel либо применять Условное Форматирование

Метод 1. Штатная функция в Excel 2007

Начиная с 2007-й версии функция удаления дубликатов является обычной – отыскать ее можно на вкладке Данные – Удаление дубликатов (Data – Remove Duplicates) :

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

Метод 2. Расширенный фильтр

Если у вас Excel 2003 либо старше, то для удаления дубликатов и вытаскивания из перечня неповторимых (неповторяющихся) частей можно применять Расширенный фильтр (Advanced Filter) из меню (вкладки) Данные (Data) .

Интересно почитать:  Как в эксель вставить строку между строк в таблицу

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

Избираем в меню Данные – Фильтр – Расширенный фильтр (Data – Filter – Advanced Filter) . Получаем окно:

  • Выделяем наш перечень компаний в Начальный спектр (List Range) .
  • Ставим переключатель в положение Скопировать итог в другое пространство (Copy to another location) и указываем пустую ячейку.
  • Включаем (самое основное!) флаг Лишь уникальные записи(Uniqe records only) и нажимаем ОК.

Получите перечень без дубликатов:

Если требуется находить дубликаты не по одному, а по нескольким столбцам, то можно за ранее склеить их в один, сделав, собственного рода, составной ключ при помощи функции СЦЕПИТЬ (CONCATENATE) :

Тогда предстоящая задачка будет сводиться к поиску дубликатов уже в одном столбце.

Метод 3. Подборка неповторимых записей формулой

Чуток наиболее непростой метод, чем 1-ые два, но зато – динамический, т.е. с автоматическим пересчетом, т.е. если перечень редактируется либо в него дописываются еще элементы, то они автоматом проверяются на неповторимость и отбираются. В прошлых методах при изменении начального перечня необходимо будет поновой запускать Расширенный фильтр либо нажимать на клавишу Удаление дубликатов.

Итак, опять имеем перечень хаотично циклических частей. К примеру, таковой:

1-ая задачка – пронумеровать всех неповторимых представителей перечня, дав любому собственный номер (столбец А на рисунке). Для этого вставляем в ячейку А2 и копируем потом вниз до упора последующую формулу:

В британской версии это будет:

Эта формула инспектирует сколько раз текущее наименование уже встречалось в перечне (считая с начала), и если это количество =1, т.е. элемент повстречался 1-ый раз – дает ему поочередно растущий номер.

Для упрощения адресации дадим нашим спектрам (к примеру, исходя из того, что в перечне быть может до 100 частей) имена. Это можно создать в новейших версиях Excel на вкладке Формулы – Диспетчер имен (Formulas – Name manager) либо в старенькых версиях – через меню Вставка – Имя – Присвоить (Insert – Name – Define) :

  • спектру номеров (A1:A100) – имя NameCount
  • всему списку с номерами (A1:B100) – имя NameList

Сейчас осталось выбрать из перечня NameList все элементы имеющие номер – это и будут наши уникальные представители. Создать это можно в хоть какой пустой ячейке примыкающих столбцов, введя туда вот такую формулу с известной функцией ВПР (VLOOKUP) и скопировав ее вниз на весь столбец:

=ЕСЛИ(МАКС(NameCount) либо в британской версии Excel:

Эта формула проходит сверху вниз по столбцу NameCount и выводит все позиции перечня с номерами в отдельную таблицу:

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

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

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

Удалить повторяющиеся данные из столбца

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

Скопируйте данные на новейший лист (чтоб случаем не удалить нужные данные). Изберите значения либо столбец, из которого вы желаете удалить повторяющиеся значения. В разделе Data Tools на вкладке Data изберите Remove Duplicates . Это удаляет все повторяющиеся данные и оставляет лишь уникальные значения.

Этот же процесс работает, если информация разбита меж 2-мя столбцами. Разница в том, что для вас необходимо выбрать оба столбца. В нашем примере у нас есть столбец для имени и 2-ой для фамилии.

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

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

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

Если вы просто ищете ответ, используйте эту формулу, заменяя любой экземпляр A2: A13 ячейками, которые вы желаете применять:

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

Разъяснение функции массива

Давайте поначалу начнем с разъяснения того, что такое массив. Массив – это одна переменная, которая содержит несколько значений. Это похоже на одновременное воззвание к кучке ячеек Excel заместо воззвания к каждой ячейке по отдельности.

С нашей точки зрения, это странноватое различие. Если мы скажем формуле, чтоб она смотрела на ячейки A2: A13 в обыкновенном режиме либо в виде массива, данные для нас смотрятся идиентично. Разница в том, как Excel обрабатывает данные за кулисами. Это так узкая разница, что новые версии Excel больше не делают различий меж ними, хотя старенькые версии это делают.

Для наших целей наиболее принципиально знать, как мы можем применять массивы. Если у вас установлена ​​крайняя версия Excel, она автоматом сохраняет данные в виде массива, когда это наиболее отлично. Если у вас наиболее древняя версия, когда вы закончите писать формулу, нажмите Ctrl + Shift + Enter . Как вы это сделаете, формула будет окружена фигурными скобками, чтоб показать, что она находится в режиме массива.

Интересно почитать:  В эксель группировка строк

Знакомство с функцией FREQUENCY

Функция ЧАСТОТА докладывает нам, сколько раз число возникает в перечне. Это замечательно, если вы работаете с числами, но наш перечень – текст. Чтоб применять эту функцию, мы должны поначалу отыскать метод конвертировать наш текст в числа.

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

Внедрение функции ПОИСКПОЗ

Функция ПОИСКПОЗ возвращает позицию первого вхождения значения. Мы можем применять это для преобразования нашего перечня имен в числовые значения. Ему нужно знать три части инфы:

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

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

В приведенном выше примере мы отыскиваем в нашем перечне (A2: A13) Тиа Галлахер (A2) и желаем получить четкое совпадение. 0 в крайнем поле показывает, что это обязано быть четкое совпадение. Наш итог гласит нам, где в перечне имя возникло первым. В этом случае это было 1-ое имя, потому итог равен 1.

Неувязка в том, что мы заинтересованы во всех наших клиентах, а не только лишь в Тиа. Но если мы попытаемся отыскать A2: A13 заместо A2, мы получим ошибку. Вот тут и понадобятся функции массива. 1-ый параметр может принимать лишь одну переменную, по другому он возвратит ошибку. Но с массивами обращаются как с единственной переменной.

Сейчас наша функция докладывает Excel, что необходимо инспектировать совпадения для всего массива. Но подождите, наш итог не поменялся! Он как и раньше гласит 1. Что тут происходит?

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

Вы сможете убедиться в этом сами. Если вы измените 1-ый спектр на A3: A13, итог поменяется на 2. Это поэтому, что имя Eiliyah стоит на втором месте в перечне, и сейчас это значение сохраняется первым в массиве. Если вы измените 1-ый спектр на A7: A13, вы опять получите 1, поэтому что имя Тиа в первый раз возникает в первой позиции проверяемого набора данных.

Внедрение функции ЧАСТОТА

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

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

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

Сейчас наш итог – 2. И опять наша функция возвращает массив. Он возвращает массив, в каком обозначено количество возникновений всякого неповторимого значения. В ячейке отображается 1-ое значение в массиве. В этом случае имя Тиа возникает два раза, потому ворачивается частота 2.

Внедрение функции ЕСЛИ

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

Мы можем сделать функцию ЕСЛИ, которая изменяет все значения выше нуля на 1. Тогда все значения будут равны 1.

Для этого мы желаем, чтоб наша функция IF инспектировала, больше ли значения в нашем массиве FREQUENCY нуля. Если true, он должен возвратить значение 1. Вы заметите, что сейчас 1-ое значение в массиве ворачивается как единое целое.

Внедрение функции СУММ

Мы на крайней стадии! Крайний шаг – СУММИРОВАТЬ массив.

Оберните предшествующую функцию в функцию СУММ. Законченный! Итак, наша окончательная формула:

Подсчет неповторимых записей в Excel

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

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

Ссылка на основную публикацию
Adblock
detector