В этом разделе рассмотрены основные формулы подсчета, которые предоставляет в ваше распоряжение Excel. Все описанные ниже формулы достаточно просты в использовании. Они демонстрируют принципы использования функций, которые подсчитывают количество ячеек заданного диапазона, удовлетворяющих определенным условиям. На рисунке показан рабочий лист, в котором для суммирования содержимого 20-ти ячеек диапазона А1:В10 под названием Данные используются специальные формулы (столбец Е).
Несколько слов о примерах, приведенных в этом разделе
В большинстве примеров раздела в качестве аргументов функций используются именованные диапазоны. Если захотите адаптировать эти формулы к своим задачам, замените имена и адреса диапазонов.
Некоторые примеры, приведенные в разделе, содержат формулы массива. Они относятся к специальному типу формул; при отображении в строке формул они заключаются в фигурные скобки. Например:
{=Данные*2}
Чтобы сообщить Excel, что введенная формула представляет собой формулу массива, после ее ввода вместо <Enter> нажмите комбинацию клавиш <Ctrl+Shift+Enter>. Фигурные скобки вводить не нужно – Excel сделает это за вас.
Подсчет общего количества ячеек
Чтобы подсчитать общее количество ячеек в диапазоне, используйте формулу, приведенную ниже. Она возвращает количество ячеек в диапазоне Данные, умножая количество строк (возвращенное функцией ЧСТРОК) на количество столбцов (возвращенное функцией ЧИСЛСТОЛБ).
=ЧСТРОК(Данные)*ЧИСЛСТОЛБ(Данные)
Подсчет пустых ячеек
Следующая формула возвращает количество пустых ячеек в диапазоне Данные:
=СЧИТАТЬПУСТОТЫ(Данные)
Функция СЧИТАТЬПУСТОТЫ подсчитывает ячейки, которые содержат формулу, возвращающую пустую строку. Например, формула, приведенная ниже, возвращает пустую строку, если ячейка А1 содержит значение, большее 5. Если ячейка А1 соответствует этому условию, функция СЧИТАТЬПУСТОТЫ рассматривает ячейку с данной формулой как пустую.
=ЕСЛИ(А1>5;“”;А1)
Примечание
Имейте в виду, что функция СЧИТАТЬПУСТОТЫ не учитывает ячейки, содержащие нулевое значение, даже если снять флажок Показывать нули в ячейках, которые содержат нулевые значения вкладки Дополнительно диалогового окна параметров программы.
Функция СЧИТАТЬПУСТОТЫ может использовать в качестве аргумента целую строку или столбец. Например, приведенная ниже формула возвращает количество пустых ячеек в столбце А.
=СЧИТАТЬПУСТОТЫ(А:А)
Следующая формула возвращает количество пустых ячеек в пределах всего рабочего листа Лист1. Данная формула должна быть введена в другой лист, в противном случае будет создана циклическая ссылка.
=СЧИТАТЬПУСТОТЫ(Лист1!1:1048576)
Подсчет непустых ячеек
Представленная далее формула использует функцию СЧЁТЗ и возвращает количество непустых ячеек в диапазоне Данные.
=СЧЁТЗ(Данные)
Функция СЧЁТЗ подсчитывает ячейки, содержащие числовые значения, дату, время, текст и логические значения ИСТИНА или ЛОЖЬ.
Примечание
Если ячейка содержит формулу, возвращающую пустую строку, она включается в итог, возвращенный функцией СЧЁТЗ, даже несмотря на то, что внешне выглядит пустой.
Подсчет ячеек, содержащих числовые значения
Следующая формула использует функцию СЧЁТ и позволяет подсчитать количество ячеек диапазона Данные, содержащих только числовые значения.
=СЧЁТ(Данные)
Ячейки, содержащие значения даты и времени, также рассматриваются как числовые значения. Ячейки, отображающие логические значения (ИСТИНА или ЛОЖЬ), числовыми не считаются.
Подсчет ячеек, отображающих нетекстовые значения
В приведенной ниже формуле массива используется функция ЕНЕТЕКСТ. Она возвращает значение ИСТИНА в том случае, если ее аргумент ссылается на ячейку, не содержащую текстовое значение (включая также пустые ячейки). Формула возвращает количество ячеек, не содержащих текст (включая пустые ячейки).
{=СУММ(ЕСЛИ(ЕНЕТЕКСТ(Данные);1))}
Подсчет ячеек, содержащих текстовые значения
Чтобы подсчитать в диапазоне Данные количество ячеек, содержащих тест, воспользуйтесь приведенной ниже формулой массива.
{=СУММ(ЕСЛИ(ЕТЕКСТ(Данные);1))}
Подсчет логических значений
Следующая формула массива возвращает количество логических значений (ИСТИНА или ЛОЖЬ) в диапазоне Данные:
{=СУММ(ЕСЛИ(ЕЛОГИЧ(Данные);1))}
Подсчет ошибок в диапазоне
Приложение Excel предлагает три функции, которые позволяют определить, содержит ли ячейка ошибки.
• Функция ЕОШИБКА возвращает значение ИСТИНА в том случае, если ячейка содержит любое значение ошибки (#Н/Д, #ССЫЛКА!, #ЗНАЧ!, #ДЕЛ/0!, #ИМЯ?, #НУЛЬ!).
• Функция ЕОШ возвращает значение ИСТИНА, если ячейка содержит любое значение ошибки, за исключением #Н/Д.
• Функция ЕНД возвращает значение ИСТИНА, если значение равно #Н/Д.
Примечание
Обратите внимание: ошибка #Н/Д (нет данных) обрабатывается не так, как другие. В большинстве случаев это вообще не ошибка. Часто сообщение #Н/Д используется в качестве правильного заполнителя ячейки при отсутствии данных. Значение #Н/Д можно ввести непосредственно или с помощью функции НД.
Перечисленные выше функции применяются в формулах массива для подсчета значений ошибки в определенном диапазоне. Например, следующая формула массива возвращает общее количество ячеек, содержащих значения ошибки, в диапазоне Данные:
{=СУММ(ЕСЛИ(ЕОШИБКА(Данные);1))}
При необходимости вместо функции ЕОШИБКА можно использовать функции ЕОШ и ЕНД.
Чтобы подсчитать количество ошибок определенного типа, воспользуйтесь функцией СЧЁТЕСЛИ. Например, следующая формула возвращает количество ячеек с типом ошибки #ДЕЛ/0!, содержащихся в диапазоне Данные:
=СЧЁТЕСЛИ(Данные;“#ДЕЛ/0!”)