Check-moscow.ru

Финансы и учет
2 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

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

Здравствуй уважаемый пользователь!

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

Список уникальных значений можно создать шестью способами:

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

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

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

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

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

Читайте так же:
Как добавить запятую в конец ячейки / текста в Excel?

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

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

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

Пример 1: Вам нужно однозначно пронумеровать значения в списке значений, для этого вы используете функцию IF в следующей формуле:

=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+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?

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

И СПОЛЬЗОВАНИЕ ИНТЕРНЕТА: Формулы массива существенно замедляют преобразование листа.

Сфера применения: любая версия Excel

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

Способ 1. Штатная функция в Excel 2007

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

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

Способ 2. Расширенный фильтр

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

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

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

  • Выберите наш список компаний в поле Список.
  • Установите переключатель в положение Копировать в другое место и укажите пустую ячейку.
  • Включите (это самое важное!) флажок Только записи Uniqe и нажмите OK.

Получите не дублированный список:

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

Читайте так же:
Как добавить кавычки вокруг чисел или текста в Excel?

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

Способ 3. Выборка уникальных записей формулой

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

Итак, у нас снова есть список случайно повторяющихся элементов. Например, вот это:

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

На английском это будет :

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

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

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

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

Читайте так же:
Как добавить итоговые метки в столбчатую диаграмму с накоплением в Excel?

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

Он проходит сверху вниз по колонке NameCount и выводит номер каждого элемента в отдельную таблицу:

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector