Подсчет количества заполненных ячеек в Excel

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

Метод 1: счетчик в строке состояния

См. также "Как присвоить имя ячейке (диапазону) в электронной таблице Excel".

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

Этот счетчик называется "Count" и скрыт до тех пор, пока пользователь не выберет по крайней мере две заполненные ячейки. Счетчик показывает количество всех ячеек, в которые были введены данные любого типа: символ, дата, число и т.д.

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

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

• щелкните правой кнопкой мыши в любом месте строки состояния;
• Найдите строку "Количество" в отображаемом списке;
• Выберите поле (щелкнув по нему), а затем проверьте счетчик.

Метод 2: использование функции СЧЁТЗ

См. также "Как скрыть столбцы в таблице Excel".

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

1. Выделите ячейку, в которой вы хотите отобразить результат. Затем найдите кнопку "Вставить функцию" (fx) слева от панели формул и нажмите ее.

    

   

    

2. В открывшемся окне Мастера функций:
   • Выберите "Полный алфавитный список" в качестве категории;
   • В поле "Выбрать функцию" найдите строку "SCHEDULE" и нажмите на нее;
   • нажмите OK.

      

     

      

3. Откроется окно аргументов функции SETTZ, где необходимо ввести диапазоны ячеек, в которых будет подсчитываться количество заполненных элементов. Координаты диапазона можно ввести вручную, но гораздо проще следовать приведенному ниже алгоритму:
   • нажмите на поле, чтобы заполнить данные напротив аргумента 'Value1';
   • Щелкнув левой кнопкой мыши в таблице, выберите нужный диапазон ячеек;
   • если вы хотите подсчитать количество ячеек из другого (не перекрывающегося) диапазона, нажмите "Value2" и выберите следующий диапазон ячеек. В нашем случае эти диапазоны соседствуют друг с другом, и мы просто показываем, что такая операция возможна;
   • при необходимости повторите операцию, пока не будут выбраны все диапазоны ячеек.
   • Когда вы будете готовы, нажмите OK.

      

     

      

     Примечание: Общий синтаксис функции выглядит следующим образом
     =CHOTZ(value1;value2;...).
     Более опытные пользователи могут ввести формулу непосредственно в нужную ячейку, вручную введя необходимые аргументы без использования Мастера функций. Когда формула будет готова, нажмите Enter.

      

     

      

4. После выполнения описанной выше процедуры результат появится в выбранной ячейке как количество ячеек с заполненными данными в указанном диапазоне (диапазонах).

    

   

    

Метод 3: использование функции СЧЁТ

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

1. Как было описано ранее, перейдите к ячейке для отображения результата и откройте Мастер функций:
   • Выберите категорию "Полный алфавитный список";
   • В поле "Выбрать функцию" нажмите на строку "COUNT";
   • а затем нажмите кнопку OK.

      

     

      

2. Появится окно аргументов функции CALCULATION, в котором необходимо ввести диапазоны ячеек. Их можно ввести вручную или выбрать непосредственно из таблицы (подробная процедура описана во втором способе выше). Когда все аргументы будут заполнены, нажмите кнопку OK.

    

   

    

   Примечание: Формула для функции выглядит следующим образом:
   =Count(value1;value2;...).
   Вы можете ввести его непосредственно в нужную ячейку, не обращаясь к Мастеру функций.

    

   

    

3. В результате будет получен результат подсчета, включающий только ячейки, содержащие числовые значения.

    

   

    

Метод 4: применение функции СЧЁТЕСЛИ

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

• >100;
• 0;
• = "да".

Опишем подробный алгоритм использования функции.

1. Как и в предыдущих методах, мы встаем в ячейку для вычисления результата и запускаем Мастер функций.
   • в списке категорий выберите "Полный алфавитный список";
   • в списке операторов выберите функцию 'CALCULATE', а затем нажмите OK.

      

     

      

2. Отображается окно настроек аргумента функции SETTINGSLI:
   • Как и в предыдущих методах, укажите координаты области ячейки в качестве значения аргумента "Range" (вручную или щелкнув левой кнопкой мыши на самой таблице).
   • Аргумент 'Criterion' определяет условия отбора ячеек данных для включения в общий подсчет.
   • Нажмите OK, когда все будет заполнено.

      

     

      

     Примечание: При желании можно пропустить мастер функций и ввести необходимую формулу для функции в выбранную ячейку. Сама формула выглядит следующим образом:
     =CHOTHESLI(range;criterion).

      

     

      

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

    

   

    

Метод 5: функция СЧЁТЕСЛИМН

Функция SCATESLYMN - это еще более гибкий инструмент подсчета клеток, который позволяет задать количество совпадений по нескольким критериям. Каждому диапазону может быть присвоен свой критерий, и всего может быть 126 таких комбинаций.

1. Вы выделяете ячейку для вывода конечного результата и нажимаете кнопку "Вставить функцию", после чего открывается мастер функций.
   • Остановитесь на категории "Полный алфавитный список";
   • нажмите на оператор "COUNTERSLIMN" в списке доступных функций и подтвердите действие нажатием на OK.

      

     

      

2. Теперь нам нужно настроить аргументы оператора SCHETESLIMN. Введите адрес первого диапазона ячеек, затем в следующем поле введите условие для этого диапазона. За ним последует поле для ввода координат второго диапазона условий, затем условий для него, и так далее. Как отмечалось выше, всего может быть 126 таких диапазонов с назначенными им критериями отбора (в нашем случае мы ограничимся двумя). После завершения нажмите OK.

    

   

    

   Примечание: Как и в случае с функциями выше, формулу с заполненными аргументами можно ввести непосредственно в результирующую ячейку.
   =CountLIMN(range_condition1;condition1; range_condition2;condition2;...).

    

   

    

3. В результате в данной ячейке мы видим величины, удовлетворяющие условиям функции.

    

   

    

Заключение

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