Функции СЧЁТЗ и СЧИТАТЬПУСТОТЫ в Excel

Таблица данных:

Формула расчета:

= СЧИТАТЬПУСТОТЫ (B3: B10)

Описание темы:

• B3: B10 — это диапазон, в котором подсчитывается количество пустых ячеек.

Результат расчета:

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

Проверка заполнения данными таблицы с помощью функции СЧЁТЗ в Excel

Пример 2. Для решения сомнительных ситуаций при ДТП создан модуль Excel для расчета тормозного пути автомобиля. Таблица содержит 4 обязательных поля данных. Реализуйте алгоритм для отображения ошибки «Не все данные указаны», если данных недостаточно для расчета.

Исходная таблица:

Функция, отображающая ошибку, если введены не все данные:

= IF (COUNT (A2: A5) COUNT (B2: B5); «Не все данные указаны»; B4 * 0,28 * B5 + (GRADE (B4 * 0,28; 2) / (2 * B3 * B2)))

Столбец A: A объясняет данные в соседнем столбце. Если количество заполненных ячеек в ячейках A2: A5 и B2: B5 одинаково, расчет будет выполняться по формуле B4 * 0,28 * B5 + (УРОВЕНЬ (B4 * 0,28; 2) / (2 * B3 * B2)) , в противном случае будет отображаться информация об ошибке. В этом случае все поля указываются и заполняются. Результат:

Например, если время ответа не указано, будет отображаться следующий вывод:

Синтаксис функции СЧЕТЗ

СЧЁТ (значение1; [значение2]; …)

СЧЁТ (значение1; [значение2], …)

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

Значение1 (значение1) является обязательным аргументом для функции СЧЁТ, все остальные аргументы являются необязательными и могут быть опущены.

В Excel 2007 вы можете использовать до 255 аргументов, каждый из которых может содержать огромное количество данных. В более ранних версиях Excel (например, Excel 2003) функция COUNT обрабатывала только 30 аргументов.

Подготовка исходной таблицы

Чтобы использовать эту функцию, вам необходимо:

• исходная таблица имела заголовки столбцов;
• критерии были отформатированы в виде отдельной небольшой таблицы с заголовками;
• заголовки таблицы критериев были такими же, как заголовки исходной таблицы (если критерий не указан в формуле).

Синтаксис функции БСЧЁТ()

АККАУНТ (база_данных; поле; критерий)

Аргумент Database_Database — это таблица (см. Строки 8-13 на рисунке выше), один из столбцов которой используется для подсчета значений. Верхняя строка таблицы должна содержать заголовки столбцов.

Поле — это имя подсчитываемого заголовка столбца. Аргумент поля можно заполнить, набрав:

• текст с заголовком столбца в кавычках, например «Продавец» или «Продажи”,
• число (без кавычек), определяющее положение столбца в таблице (указанное в аргументе db_base): 1 для первого столбца, 2 для второго и т д
• ссылка на заголовок столбца.

Предполагается, что этот столбец содержит числа, иначе функция вернет 0.

Примечание. Если значения в этом столбце содержат числа, хранящиеся в текстовом формате, необходимо использовать функцию UNCOUNT() .

Примечание. В теории баз данных строки таблицы называются записями, а столбцы — полями (отсюда и необычное имя аргумента).

Политика: диапазон ячеек, содержащий указанные условия (т. Е. Ссылку на таблицу критериев). См. Строки 2–4 на рисунке выше. Этот диапазон ячеек аналогичен таблице для расширенного фильтра и функции BDSUMM () и, конечно же, BCOUNT() .

Задача 1 (с одним критерием)

Считаем количество продаж на сумму> 3000. Их будем считать по столбцу Продажи .

Алгоритм следующий:

• Создаем таблицу критериев (желательно над исходной таблицей, чтобы она не мешала добавлению новых данных в таблицу), например в диапазоне A2: A3 (см. Рисунок выше). Таблица должна состоять из заголовка (равного заголовку столбца исходной таблицы, к которому применяется критерий) и самого критерия (условий выбора);
• Условия выбора можно записать в текстовом формате. В нашем случае всего> 3000
• Теперь вы можете написать саму формулу = COUNT (B8: B13; B8; A2: A3) B8: B13 содержит Database_Base (исходная таблица). Столбец A (Продавец) в этом случае не может быть включен в базу данных, так как он не учитывается и не участвует в отборе. B8 — это ссылка на заголовок столбца, по которому будет производиться подсчет (Продажи), он должен содержать числовые значения. A2: A3 — Критерии эталонной пластины.

Альтернативное решение — простая формула = СЧЁТЕСЛИ (B9: B13; A3) .

Вывод: для простых однокритериальных задач сад с функцией = BACCOUNT () не должен быть огорожен.

Задача 2 (Два критерия (разные столбцы), условие И)

Подсчитаем количество продаж Белова ниже 3000.

В этом случае критерии выбора необходимо разместить под заголовками, названия которых совпадают с заголовками столбцов исходной таблицы, которые будут использоваться для подсчета (столбцы «Поставщик» и «Продажи»). Критерии должны быть в одной строке (см. Диапазон на рисунке выше A2: B3).

Саму формулу можно записать так = СЧЁТ (A8: B13; B8; A2: B3)

Альтернативное решение — простая формула = СЧЁТЕСЛИМН (A9: A13; «Белов»; B9: B13; A3) .

Задача 3 (Два критерия (один столбец), условие И)

Считаем количество продаж в диапазоне> 3000 у.е

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

Саму формулу можно записать так = СЧЁТ (B8: B13; B8; D2: E3)

Альтернативное решение — простая формула = СЧЁТЕСЛИМН (B9: B13; «> 3000 ″; B9: B13;”

Задача 4 (Два критерия (один столбец), условие ИЛИ)

Считаем количество продаж 6000.

В этом случае критерии выбора должны быть размещены под заголовком (столбец Продажи). Критерии должны быть в отдельных строках.

Саму формулу можно записать так = СЧЁТ (B8: B13; B8; A2: A4)

Альтернативное решение — формула = СЧЁТЕСЛИ (B9: B13; «6000″)

Как работает стандартное отклонение в Excel?

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

В Excel используется несколько вариантов функции отклонения:

• Функция СТАНДОТКЛОН — вычисляет отклонение от образца текстовых и логических значений. В этом случае формула приравнивает ложные логические и текстовые значения к 0, и только истинные логические значения будут равны 1;
• Функция СТАНДОТКЛОН — оценивает стандартное отклонение выборки, игнорируя текстовые и логические значения;
• Функция STDEV — оценивает отклонение для определенной генеральной совокупности и, как и в предыдущей функции, текстовые и логические значения игнорируются;
• Функция СТАНДОТКЛОНП — также вычисляет стандартное отклонение от генеральной совокупности, но с учетом текста и логических значений. Только истинные логические значения будут равны 1, а ложные логические и текстовые значения будут равны 0.

Эксперимент

Чтобы проиллюстрировать это, вот пример того, как ячейка с пустым текстом считается условным форматированием и функцией ISBLANK () (см. Файл примера).

Рассмотрим диапазон A1: D4, содержащий числа, текст, пустые ячейки и пустой текст «» (наиболее распространенный случай).

Ячейка C4 содержит значение «Пустой текст» (введенное в результате вычисления формулы = ЕСЛИ (1> 2; 1; «»)) и выделено жирной рамкой. При условном форматировании с помощью правила «Форматировать только пустые ячейки» будут выделены действительно пустые ячейки и ячейка со значением «Пустой текст !

Функция ISBLANK () не разделяет этот подход и говорит, что что-то есть в C4 (формула = ISBLANK (C4), введенная в ячейку C5, возвращает FALSE).

Функция COUNTBLANK (), как и условное форматирование, при подсчете учитывает ячейки с пустым текстовым значением вместе с пустыми ячейками.

Чтобы не запутаться с подсчетом пустых ячеек в диапазоне C2: C11 при пустых текстовых значениях, вот сводная таблица: