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

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

1. С помощью специальной функции;
2. С помощью расширенного фильтра;
3. С помощью формул;
4. С помощью сводных таблиц;
5. С помощью условного форматирования;
6. С помощью возможностей макроса.

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

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

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

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

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

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

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

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

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

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

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

=ЕСЛИ(МАКС(A1:A100)<СТРОКА(1:1);"";ВПР(СТРОКА(1:1); A1:B100;2))

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

{=ИНДЕКС($A$2:$A$9;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($C$1:C1;$A$2:$A$9)=0;СТРОКА($A$1:$A$8));1))}

Как видите, что здесь была использована разметка формул массива, об этом свидетельствуют фигурные скобки, и такой тип формул вводится в ячейку горячим сочетанием клавиш Ctrl+Shift+Enter. После протяжки формулы, если вы увидите значение ошибки #ЧИСЛО, это свидетельствует о том, что уникальные значения закончились. Для избегания этой ошибки есть возможность изменить и усовершенствовать формулу, сделав ее следующего вида для Excel 2007 и выше:

{=ЕСЛИОШИБКА (ИНДЕКС ($A$2:$A$9; НАИМЕНЬШИЙ (ЕСЛИ (СЧЁТЕСЛИ ($C$1:C1; $A$2:$A$9)=0; СТРОКА( $A$1:$A$8));1));«")}

В этом случае, если у вас и получается ошибка #ЧИСЛО, то система будет ставить пустые ячейки.

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

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

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

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

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

Этот способ я тоже не буду причислять к серьезному или сложному. Для использования условного форматирования для отбора уникальных значений вам нужно перейти на вкладку «Главная», дальше в группе «Стили» выбрать кнопку меню «Условное форматирование» в предложенном списке нам нужен пункт «Создать правило». В открывшемся диалоговом окне «Создание правила форматирования» активируем пункт «Использовать формулу для определения форматируемых ячеек» и в поле «Изменить описание правила» вводим нашу формулу-условие:

=ПОИСКПОЗ(D2;$D$2:$D$9;0)=СТРОКА(D2) -СТРОКА($D$1)

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

Вот и всё, если условия выполнены вы и получите нужные вам значения.

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