Подсчет уникальных значений в Excel
Здравствуй уважаемый пользователь!
В этой статье я хочу рассказать о возможности создать список уникальных значений в таблицах Excel. Эта возможность очень часто используется при работе с таблицами, так как часто возникает потребность с большого массива данных выбрать уникальные данные, которые не повторяются. Это может быть нужно для разнообразных целей, и уже вам решать каким способом и как произвести отбор нужных вам уникальных значений.
Список уникальных значений возможно создать 6-ю способами:
- С помощью специальной функции;
- С помощью расширенного фильтра;
- С помощью формул;
- С помощью сводных таблиц;
- С помощью условного форматирования;
- С помощью возможностей макроса.
Создать список уникальных значений с помощью специальной функции
Это очень простой способ для владельцев Excel выше 2007 версии как произвести отбор уникальных значений. Вам нужно на вкладке «Данные», в разделе «Работа с данными», использовать специальную команду «Удалить дубликаты».
В появившемся диалоговом окне «Удалить дубликаты», вы выделяете те столбики, где необходимо произвести отсев уникальных значений и нажимаете «Ок». В случае, когда в выделенном диапазоне размещается и заголовок таблицы, то поставьте галочку на пункте «Мои данные содержат заголовки», что бы вы случайно не удалили данные.
Внимание! Когда вы будете производить отсев уникальных значений в таблице, где столбиков больше 2 и они взаимосвязаны информацией, Excel предложит вам расширить диапазон выбора, с чем вы должны, согласится, иначе будет нарушена логическая связь с другими столбиками.
Создать список уникальных значений с помощью расширенного фильтра
Это также не сложный способ произвести отбор уникальных значений в таблице. Использовать этот инструмент возможно на вкладке «Данные», потом выбрать «Фильтр», и наконец «Расширенный фильтр», этот путь подходит для Excel 2003, а вот владельцы более юных версий, от 2007 и выше стоит пройти по пути: «Данные» — «Сортировка и фильтр» — «Дополнительно». Огромный плюс этого способа в том, что вы можете создать новый список уникальных значений в другом месте.
После появления диалогового окна «Расширенный фильтр», устанавливаем галочку напротив пункта «Скопировать результат в другое место», потом указываем диапазон с вашими данными в поле «Исходный диапазон», при необходимости указываем критерий отбора, но для общего отсева поле оставляем пустым «Диапазон критериев», в третьем поле «Поместить результат в диапазон» указываем первую ячейку куда будут помещаться наши данные, отмечаем галочкой пункт «Только уникальные записи» и нажимаем «Ок».
Если же вам не нужно никуда переносить ваши данные, то просто установите флажок для пункта «Фильтровать список на месте», данные не пострадают, произойдет наложение обыкновенного фильтра.
Внимание!Если программа запрещает вам переносить отфильтрованные данные на другой лист, вы просто запустите «Расширенный фильтр» на том листе, куда вам надо перенести отобранные уникальные значения.
Создать список уникальных значений с помощью формул
Этот способ более сложен, нежели те, что мы рассматривали ранее, но его преимущество в том, что он более динамичен и работает на постоянной основе. В разных случаях вам будут нужны разные формулы, вот и рассмотрим несколько вариантов и примеров.
Пример 1. Вам нужно пронумеровать, уникальные, значение в списке значений, для этого нужно использовать функцию ЕСЛИ в формуле следующего вида:
=ЕСЛИ(СЧЁТЕСЛИ(B$1:B2;B2)=1;МАКС(A$1:A1)+1;»»)
Суть формулы в том, что она проверяет сколько раз, текущее значение встречается в вашем диапазоне (начиная с начала), и если это значение равно 1, то есть это первое уникальное значение, формула ставит последовательно возвращающий номер по порядку.
Теперь можно произвести отбор уникальных значений, которые были ранее пронумерованы. Сделать это возможно в любом из соседних столбиков используя функцию ВПР и копируя ее вниз:
=ЕСЛИ(МАКС(A1:A100)<СТРОКА(1:1);»»;ВПР(СТРОКА(1:1); A1:B100;2))
Эта формула перебирает весь диапазон сверху вниз по столбику с номерами и все позиции значений с номерами переносит в, другую таблицу.
Пример 2. Вам нужно создать уникальный список значений, при этом, исходные, значение не изменяются, а если изменять начальные данные, то и конечные данные будут изменены. Допустим у вас список данных в диапазоне А1:А9, где А1— это заголовок. А вот полученные данные будут выводиться в столбик С, а формула для этого будет такая:
{=ИНДЕКС($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)
и выставляем формат нужных нам уникальных значений при совпадении условий, к примеру, заливка красным цветом.
Вот и всё, если условия выполнены вы и получите нужные вам значения.
