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

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

Примерная таблица данных

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

Пример используемой таблицы для подсчета уникальных значений в Google Таблицах

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Метод 1: Использование функции UNIQUE

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

Выделите ячейку, в которой хотите разместить список уникальных значений. Например, выделите ячейку E2.
Введите формулу =UNIQUE(B2:B9), чтобы вывести уникальные значения из диапазона «Имя». В результате отобразятся подходящие под условия имена: Иван, Елена, Анна, Петр и Ольга.
Чтобы подсчитать количество уникальных значений, можно использовать функцию COUNTA, которая считает количество непустых ячеек. Объедините UNIQUE с COUNTA, чтобы получить количество уникальных значений: =COUNTA(UNIQUE(B2:B9)).
В ячейке отобразится цифра 5, что соответствует пяти неповторяющимся именам в столбце «Имя».

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

Метод 2: Комбинация функций COUNTIF, ARRAYFORMULA и FILTER

Этот метод подходит для более гибкого подсчета уникальных значений, особенно если требуется добавить условия или подсчитывать значения по разным столбцам. COUNTIF (СЧЁТЕСЛИ) позволяет считать ячейки, удовлетворяющие условиям, а ARRAYFORMULA и FILTER добавляют возможность обработки диапазонов, что делает этот способ универсальным.

Выделите ячейку, например E2, и введите формулу для подсчета по критерию (например по имени) =ARRAYFORMULA(СУММ(ЕСЛИ(СЧЁТЕСЛИ(B2:B9; B2:B9)=1; 1; 0))).
Здесь функция СЧЁТЕСЛИ сравнивает каждое значение с остальными в диапазоне и возвращает только уникальные, а СУММ суммирует их, что дает общее количество уникальных имен.
Если необходимо подсчитать неповторяющиеся значения только по категории, можно применить формулу с FILTER, например: =СЧЁТЗ(UNIQUE(FILTER(B2:B9;C2:C9="Офис"))).
Эта формула отфильтрует значения в столбце «Имя», соответствующие категории «Офис», и затем вернет количество уникальных имен.

Применение комбинаций СЧЁТЕСЛИ, ARRAYFORMULA и FILTER более гибкое и позволяет использовать условия. Этот метод хорошо подходит для таблиц, где нужно учитывать несколько условий, но требует более сложного синтаксиса. Если задача требует учета множества критериев, такой подход является оптимальным выбором.

Метод 3: Подсчет уникальных значений с помощью Google Apps Script

Google Apps Script позволяет создавать скрипты для автоматизации задач в Google Таблицах. Этот метод может быть полезен, если нужно регулярно подсчитывать уникальные значения и обновлять их автоматически. Кроме того, скрипты Google предоставляют больше гибкости, чем стандартные формулы, позволяя обрабатывать данные по сложным критериям и условиям. Сначала предлагаю ознакомиться с кодом, который будет использоваться в методе. Можете сохранить скрипт самостоятельно, если уже знаете, как с ним взаимодействовать.

function countUniqueValues(range) {

    let values = range.flat();

    let uniqueValues = [...new Set(values)];

    return uniqueValues.length;

}

Откройте Google Таблицы и перейдите в «Расширения» – «Apps Script».
Вставьте приведенный выше код для подсчета уникальных значений. Редактировать его не нужно, разве только вы разбираетесь в работе с кодом и знаете, как улучшить его для выполнения узконаправленных задач.
Для удобства переименуем название скрипта в то, которое вам будет удобно использовать в Таблицах. Для этого вызовите меню действий и выберите «Переименовать».
Перед выходом обязательно сохраните изменения, затем можно закрыть вкладку редактирования скрипта.
В ячейке введите формулу =countUniqueValues(B2:B9), чтобы получить количество уникальных значений. Соответственно, название скрипта замените на то, которое вы указали самостоятельно.
Этот скрипт проходит по значениям диапазона, выделяет неповторяющиеся и возвращает их количество.

Использование Google Apps Script – это мощный метод, позволяющий автоматизировать подсчет уникальных значений. Он особенно полезен для сложных задач, где стандартные функции недостаточно эффективны или слишком громоздки. Скрипты удобны, но требуют базовых знаний JavaScript.

Метод 4: Использование функции QUERY

Функция QUERY – полезная функция для работы с данными, напоминающий SQL-запросы, но требует определенных знаний для использования. Она может применяться и для извлечения уникальных значений с учетом условий и сортировки. Метод полезен, если данные динамически обновляются или если нужно фильтровать по нескольким параметрам.

Выберите ячейку свободную ячейку, возле которой будет развернута отдельная таблица с подходящими значениями, после чего используйте формулу =QUERY(B2:C9; "SELECT B, COUNT(B) WHERE B IS NOT NULL GROUP BY B").
Эта формула извлекает данные из столбца «Имя» и подсчитывает их количество.
Чтобы посчитать уникальные категории, можно модифицировать запрос, например: =QUERY(B2:C9; "SELECT C, COUNT(C) WHERE C IS NOT NULL GROUP BY C"). В результате вы получите вывод уникальных категорий с подсчетом значений, относящихся к ним.

Функция QUERY особенно полезна для более сложного анализа, поскольку позволяет задавать точные условия. Однако она требует базовых знаний SQL-запросов и может быть сложна для новичков. В сложных таблицах с многочисленными условиями QUERY может оказаться незаменимым инструментом.

Каждый из рассмотренных методов для подсчета уникальных значений имеет свои преимущества. Использование функции UNIQUE – это быстрый и простой способ, подходящий для простых задач. Комбинация СЧЁТЕСЛИ, ARRAYFORMULA, и FILTER дает гибкость для подсчета по условиям, но требует знания более сложных формул. Google Apps Script – идеальный выбор для автоматизации, особенно если данные обновляются динамически. Функция QUERY предлагает мощные возможности для анализа больших объемов данных и сложных запросов. Рекомендуется выбирать метод в зависимости от конкретной задачи и требований.