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





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

Конспекты

Курсы

Практикум


Скачать файл
Теги: Бесплатный курс 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
Общие принципы, лежащие в основе любого инструмента программы Excel
Как понять программу Excel? Это несложно, список правил и настроек приведён в этом уроке.

Настройка искомого значения в числовом и текстовом форматах, с помощью символа звёздочки. Вычисление номера столбца.
Функция ВПР - профессиональные хитрости
Функция ВПР может научиться игнорировать формат ячеек и даже высчитывать номер столбца в таблице с помощью ПОИСКПОЗ.

Строки, столбцы, листы - перемещение по документу и управление количеством и положением его элементов
Операции со строками, столбцами и листами
В этом материале подробно описаны операции вставки, удаления, скрытия, отображения, копирования и перемещения строк, столбцов и листов в Excel.

Заказчики

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

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

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

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



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