Функции для анализа данных - это функции, возвращающие информацию о данных таблицы, которые удовлетворяют некоторым условиям.
Индивидуальные задания для вычисления различных функций приведены в табл.6.
Таблица 6. Функции для анализа данных
№ | Задание |
Подсчитать средний оклад мужчин старше 50 лет | |
Подсчитать минимальный оклад у женщин, работающих в отделе Отдел | |
Подсчитать количество человек, принятых на работу после даты Дата | |
Подсчитать количество сотрудников отдела Отдел | |
Подсчитать количество студентов, обучающихся на факультете Факультет | |
Подсчитать, сколько студентов группы Группа по предмету Предмет оценку Оценка | |
Подсчитать средний балл в группе Группа по предмету Предмет | |
Подсчитать средний балл студента Фамилия по всем предметам | |
Подсчитать, сколько курсовых работ у группы Группа | |
Подсчитать общую нагрузку преподавателя Преподаватель | |
Определить, сколько лекционных курсов у преподавателя Преподаватель | |
Подсчитать, какой объем времени отводится преподавателю Преподаватель на проведение курсовых работ | |
Определить, на какую сумму был поставлен товар Товар от клиента Клиент | |
Определить, на какую сумму был отпущен товар Товар клиенту Клиент | |
Определить среднюю цену, по которой поставлялся товар Товар | |
Определить максимальную цену, по которой был продан товар Товар | |
Определить общую стоимость товара, перевозимого от поставщика Поставщик способом перевозки Способ _ перевозки | |
Определить среднюю стоимость транспортных расходов для поставщика Поставщик | |
Определить среднюю стоимость транспортных расходов для способа перевозки Способ _ перевозки | |
Определить максимальную стоимость товара, перевозимого от поставщика Поставщик |
При выполнении заданий ОБЯЗАТЕЛЬНО использовать соответствующие функции, встроенные в MS Excel.
Во многих заданиях надо будет использовать функции СЧЕТЕСЛИ и СУММЕСЛИ. Они позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения.
Функции СЧЕТЕСЛИ имеет синтаксис
=СЧЕТЕСЛИ (диапазон; условие).
Аргумент диапазон задает диапазон, в котором подсчитывается количество значений, удовлетворяющих критерию. Критерий задается вторым аргументом условие, представляющим собой текстовое значение. Эта функция относится к категории статических функций.
Функция СУММЕСЛИ имеет синтаксис
= СУММЕСЛИ (диапазон; условие; диапазон_суммирования).
Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических.
Пример. Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Сергей (рис. 8).
Рис. 8. Использование функции СУММЕСЛИ
В некоторых заданиях необходимо использовать функции баз данных. Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.
У всех Д-функций один и тот же синтаксис:
=Дфункция (база_данных; поле; критерий).
Аргумент база_данных задает весь список, а не отдельный столбец. Второй аргумент поле определяет столбец, в котором производятся вычисления (суммирование, усреднение и т.п.). Аргумент критерий задает диапазон критериев. Диапазон критериев формируется так же, как при использовании расширенного фильтра.
В табл. 7 приведено краткое описание функций баз данных.
Таблица 7. Функции баз данных
Функция | Описание |
ДСРЗНАЧ | Вычисляет среднее значение в столбце списка или базы данных среди значений, удовлетворяющих заданным условиям |
БСЧЕТ | Подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных среди записей, удовлетворяющих заданным условиям |
БСЧЕТА | Подсчитывает все непустые ячейки, которые удовлетворяют заданным условиям |
БИЗВЛЕЧЬ | Извлекает отдельное значение, которое удовлетворяет заданным условиям |
ДМАКС | Возвращает наибольшее число, которое удовлетворяет заданным условиям |
ДМИН | Возвращает наименьшее число, которое удовлетворяет заданным условиям |
БДПРОИЗВЕДЕН | Перемножает значения, которые удовлетворяют заданным условиям |
БДСУММ | Суммирует числа, которые удовлетворяют заданным условиям |
ДСТАНДОТКЛ | Оценивает стандартное отклонение на основе выборки из генеральной совокупности |
ДСТАНДОТКЛП | Вычисляет стандартное отклонение генеральной совокупности |
БДДИСП | Оценивает дисперсию генеральной совокупности по выборке |
БДДИСПП | Вычисляет дисперсию генеральной совокупности |
Пример. Необходимо вычислить минимальный оклад у работников ПФО с именем Николай. На рис. 9 продемонстрировано решение этой задачи.
Рис. 9. Пример использования функции баз данных