Математические и статистические функции
Подсчёт количества ячеек и вычисление средних показателей





Как посчитать количество ячеек, отвечающих поставленному условию. Как посчитать все заполненные или все пустые ячейки. Расчёт среднеарифметического значения.

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс Excel, самоучитель Excel, конспекты тренинга Excel, без регистрации.

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

Как посчитать количество ячеек, отвечающих поставленному условию. Как посчитать все заполненные или все пустые ячейки. Расчёт среднеарифметического значения. В этом уроке мы разберём типовые примеры использования функций Excel для выбора данных из таблиц на основании данных в ячейках: пустых или заполненных, чем именно заполненных. Помочь в этом могут функции СЧЁТ. СЧЁТЗ, СЧЁТЕСЛИ, СЧЁТЕСЛИМН.

Математические и статистические функции для подсчета количества и вычисления средних показателей

Для занятия Вам потребуется книга Excel с заготовками таблиц, по которым составлен конспект - этот файл прикреплен в самом низу страницы (без проблем откроется в Excel версии 2007 и новее)

Рассматриваемые на данном занятии функции ведут себя по аналогии с автосуммой и ее производными, которые были разобраны на прошлом уроке. Сегодня на очереди статистическая функция для подсчета количества СЧЁТ [COUNT] и математическая функция для расчета среднего арифметического значения СРЗНАЧ [AVERAGE]

 

Подсчет количества

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

Есть список сотрудников с указанием их возраста и половой принадлежности, а также данные по количеству детей в семье. А вот информация у скольких сотрудников компании есть дети – отсутствует. Но такой показатель можно посчитать самостоятельно: =СЧЁТ(D4:D23), эта функция посчитает количество ячеек с числами в указанном диапазоне (пустые ячейки игнорируются, а вот нулевое значение – это все же значение и потому посчитано будет):

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

Дальше, а сколько сотрудников в компании всего? На такой вопрос ответить при помощи функции СЧЁТ [COUNT] вряд ли получится, ведь нужно посчитать количество записей по столбцу фамилий, а это точно не числовой тип данных. Если нужно узнать количество всех заполненных (непустых) ячеек, используем другой алгоритм –СЧЁТЗ [COUNTA] и получаем нужный результат: =СЧЁТЗ(A4:A10)

Чтобы вычислить количество женщин в этом коллективе, нужно выбрать все ячейки по столбцу «Пол» с буквой «Ж», а в этом поможет функция СЧЁТЕСЛИ [COUNTIF]: =СЧЁТЕСЛИ(B4:B10;"ж")

Отобрать все ячейки со словом «новый» может эта же функция, если использовать уже знакомые с прошлого урока символы подстановки звездочки (напомню: по умолчанию программа выполняет поиск ячейки с указанным текстом, а так как знаков в ячейке больше, чем искомый фрагмент, то текст в любом количестве знаков перед искомым фрагментов и после него можно заменить всего одним знаком - *): =СЧЁТЕСЛИ(A4:A10;"*новый*")

И в завершение этого вопроса давайте посчитаем количество женщин без детей, т.е. учесть необходимо не одно условие, а два – то есть найти такое количество пар ячеек, для которых оба условия одновременно выполняются. Очевидно, потребуется функция СЧЁТЕСЛИМН [COUNTIFS]: одно условие мы уже формулировали – отобрать все ячейки с буквой «Ж», а второе будем учиться писать сейчас – как в качестве критерия отбора указать пустые ячейки. В рамках данного примера можно сказать, что пустая ячейка – это пустая текстовая строка, а для нее предусмотрено обозначение в виде двойных кавычек – открывающих и закрывающих, подряд, без каких-либо знаков между ними. В итоге формула расчета будет выглядеть следующим образом: =СЧЁТЕСЛИМН(B4:B10;"ж";D4:D10;"")

 

Расчет среднеарифметического значения

Давно я не видела план или отчет без средних показателей: среднемесячная зарплата, среднедневная отгрузка или приход денег и т.д. и т.п. Не живут без него лица, ответственные за принятие решений :-) А потому нужно дать им такой расчет. Вооружаемся прикрепленным файлом и считаем средние показатели по зарплате при помощи функции СРЗНАЧ [AVERAGE] и ее производных. Прикинуть среднеарифметическое значение по всему набору данных можно при помощи базовой функции: =СРЗНАЧ(C4:C10)

Помним о том, что среднеарифметическое рассчитывается как сумма слагаемых деленное на их количество, поэтому для усреднения стоимостей с учетом соответствующего им количества данная функция не подходит! Например, если есть данные по остаткам товаров в номенклатурной разбивке и в соседней столбце указана цена за 1 ед.изм., то никаких СРЗНАЧ [AVERAGE] не применяем: необходимо по каждой позиции количество умножить на цену, сложить полученные результаты и разделить на общее количество товара – и только при такой методике расчета получим правильный средневзвешенный показатель: =СУММПРОИЗВ(B4:B14;C4:C14)/B15

Как видите, значения получаются совсем разные, поэтому внимание (!), внимание (!) и еще раз внимание к данным, с которыми работаем. Возвращаясь к основному примеру предлагаю посчитать среднюю зарплату мужчины, для чего потребуется использовать функцию СРЗНАЧЕСЛИ [AVERAGEIF]: =СРЗНАЧЕСЛИ(B4:B10;"м";C4:C10)

Можно задачу усложнить и рассчитать, скажем, среднюю зарплату нового сотрудника среди мужчин, а так как условий два, то и функцию используем другую СРЗНАЧЕСЛИМН [AVERAGEIFS]: =СРЗНАЧЕСЛИМН(C4:C10;B4:B10;"м";A4:A10;"*новый*")

Ну и напоследок хотелось бы предложить посчитать аналогичные показатели для женщин. Только переписывать всю формулу не хочется, поэтому можно процесс расчета немного оптимизировать – за базовую формулу взять аналогичный расчет для мужчин, скопировать его в другую ячейку и внести нужные изменения. Формулу для расчета средней зарплаты мужчин копировать будем через строку формул. Активируйте ячейку C12и в строке формул выделите весь текст формулы и скопируйте его (Ctrl+C>, например):

А теперь вставьте скопированное в ячейку C14. Теперь осталось только в новой формуле заменить букву «м» на букву «ж» и расчет можно считать готовым =СРЗНАЧЕСЛИ(B4:B10;"ж";C4:C10):

Аналогичным образом посчитайте среднюю зарплату среди новеньких женщин: =СРЗНАЧЕСЛИМН(C4:C10;B4:B10;"ж";A4:A10;"*новый*")

ВАЖНО (!) При работе с конспектами помним, что все названия программных продуктов являются зарегистрированными торговыми марками. Никакая часть настоящего издания ни в каких целях не может быть воспроизведена в какой бы то ни было форме и какими бы то ни было средствами, будь то электронные и механические, включая фотокопирование и запись на магнитный носитель, если на это нет письменного разрешения автора

Похожие материалы

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

Функции для вычислений по дням
Функции даты и времени
Как посчитать календарные или рабочие дни? Можно ли прибавить к дате несколько месяцев? Как это делать правильно, используя функции РАБДЕНЬ, ЧИСТРАБДНИ, ДАТАМЕС, СЕГОДНЯ и пр.

Связи между книгами. Управление внешними ссылками.
Внешние ссылки
Составление формул без переноса данных из одной книги Excel в другую возможно! Для этого нужно научиться создавать внешние ссылки (связи) и управлять ими.

Создание кнопок ПЛЮС и МИНУС для скрытия и отображения строк и столбцов
Группировка строк и столбцов
Как сделать кнопки плюс и минус для скрытия и отображения данных в Excel? Как настроить, чтобы они были в строках сверху или в столбах слева?

Заказчики

Я сотрудничаю с многими известными компаниями. Вот некоторые из них:

ВТБ МегаФон Глобус Абсолют

Связаться с автором

Оставьте ваше имя и адрес электронной почты, в ближайшее время я свяжусь
с вами для консультации и отвечу на вопросы!



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