Как осуществляется подсчет уникальных значений в Excel: правила работы

08.06.2023, 16:13

Таблицы в Экселе часто большие, и данных в них много.

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

СЧЕТЕСЛИ

Подсчет уникальных значений в Excel удобно выполняется через ключевую формулу СЧЕТЕСЛИ и ряд дополнительных функций. Пользователь кликает на пустую ячейку, открывая поле ввода.

Набирает = СУММ.

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

Пользователь открывает круглую скобку, пишет ЕСЛИ (СЧЕТЕСЛИ
Вписывает диапазон, уточнение смотрите ниже.
= 1,1,0 закрывает две круглые скобки.

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

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

СЧеТЕСЛИ фиксирует появления каждого значения, то есть может «сказать», сколько же раз встретилось то или иное число. ЕСЛИ обнуляет все результаты, кроме единицы, ведь неуникальные вам не нужны.

Чтобы формула массива сработала корректно, зажимайте клавиши Ctrl, Shift и Еnter, либо постфактум поставьте курсор в строке формул и нажать эти три кнопки. О срабатывании просигнализирует появление фигурных скобок. Ставить их вручную бесполезно.

Работа с текстом

Помимо чисел имеется текст? Работать со смешанными таблицами сложнее. Поясните алгоритму, какой тип данных анализировать, подставив ЕТЕКСТ или ЕЧИСЛО. Получается такая конструкция: {= СУММ (ЕСЛИ (ЕТЕКСТ (диапазон) * СЧЕТЕСЛИ (повторяем диапазон; указываем группу ячеек снова) = 1; 1; 0))}. Звездочка для Excel означает «и», то есть действуют одновременно два «фильтра», искомая ячейка обладает буквенным содержимое которой не записано цифрами и не повторяется. В противном случае она будет проигнорирована.

Важен регистр? Добавьте дополнительный столбец и выведите на него функцию следующего вида: {= ЕСЛИ (СУММ ((— СОВПАД ($ B $ 2: $ B $ 15, B2))) = 1; «Уникальный»; «Повторяется»)}. Внимание! В скобках вы видите двойной минус, это не ошибка, обязательно скопируйте в точности. Осталось посчитать совпадения приемом, описанным в предыдущем разделе: = СЧЕТЕСЛИ (C2: C15; «Уникальный»).

ЕЧИСЛО работает аналогично, только игнорирует уже текст.

ЧАСТОТА

Работает только с числами. К тексту ее применять нельзя: при наличии буквы будет пропущена вся ячейка. Выглядит так: = СУММ (— (ЧАСТОТА (два указания диапазона, разделенные точкой с запятой)> 0)). Это уже не массив, так что выражение вставляется без зажатия клавиш и автоматической подстановки фигурных скобочек.

Если пустых ячеек нет, буквы имеются, а формулу использовать хочется, придется перевести текст в числа. Поможет следующий массив: {= СУММПРОИЗВ (— (ЧАСТОТА (ПОИСКПОЗ (диапазон; та же группа ячеек; 0); СТРОКА (снова диапазон) — СТРОКА (первая интересующая вас ячейка) +1)> 0))}.

Похожие статьи