Анализ списка с помощью подведения промежуточных итогов

Команда Данные\Итоги может быть использована для получения различной итоговой информации. Прежде, чем подводить итоги, нужно обязательно отсортировать список соответствующим образом. Для подведения итогов можно использовать различные функции: Сумма, Количество знаний, Среднее, Максимум, Минимум, Произведение и другие. Команда Данные\Итоги создает промежуточные и общие итоги. При выводе промежуточных итогов Excel всегда создает структуру списка; с помощью символов структуры можно отобразить список с нужным уровнем детализации данных.

Пример. Необходимо подсчитать для каждого отдела предприятия сумму окладов сотрудников. На рис.17 приведен список, с которым будем работать.

Рис. 17. Исходный список

Во-первых, необходимо отсортировать исходный список по полю Отдел (рис.18).

Рис. 18. Сортировка списка по полю Отдел

Во-вторых, воспользоваться командой Данные\Итоги. На экране появиться диалоговое окно Промежуточные итоги (рис.19).

Рис. 19. Подсчет сумм окладов сотрудников для каждого отдела

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

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

В списке Добавить итоги: нужно указать, по какому (каким) полю (полям) подводить итоги.

Если в списке неоднократно подводятся итоги, то установка флажка Заменить текущие итоги приведет к тому, что итоги полученные ранее будут заменены новыми. В том случае, если этот флажок сбросить, то каждый раз к предыдущим итогам будут добавляться новые (итоги, полученные ранее, удаляться не будут).

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

Если установлен флажок Итоги под данными, то промежуточные и общие итоги будут расположены под данными (рис. 20), а если этот флажок сброшен - то над данными (рис. 21).

Рис. 20. Промежуточные и общие итоги расположены под данными

Рис. 21. Промежуточные и общие итоги расположены над данными

Чтобы убрать все итоги, нужно вызвать окно Промежуточные итоги командой Данные\Итоги и воспользоваться кнопкой Убрать все.

Функции для анализа списка

Функции для анализа списка - это функции, возвращающие информацию об элементах списка, которые удовлетворяют некоторым условиям.

Функции СЧЕТЕСЛИ и СУММЕСЛИ предназначены для работы со списками. Они проще в использовании, поскольку позволяют задавать условия непосредственно в формуле. Но в этих функциях можно использовать только простые условия сравнения.

Функции СЧЕТЕСЛИ имеет синтаксис

=СЧЕТЕСЛИ (диапазон; условие).

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

Функция СУММЕСЛИ имеет синтаксис

= СУММЕСЛИ (диапазон; условие; диапазон_суммирования).

Первые два аргумента диапазон и условие используются так же, как и в функции СЧЕТЕСЛИ. Аргумент диапазон_суммирования задает диапазон суммируемых значений. Функция относится к категории математических.

Пример. Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Сергей (рис. 22).

Рис. 22. Использование функции СУММЕСЛИ

Функции баз данных

Функции баз данных имеют обобщенное название Д-функции. Д-функции оперируют только с элементами диапазона, которые удовлетворяют заданным условиям.

У всех Д-функций один и тот же синтаксис:

=Дфункция (база_данных; поле; критерий).

Аргумент база_данных задает весь список, а не отдельный столбец. Второй аргумент поле определяет столбец, в котором производятся вычисления (суммирование, усреднение и т.п.). Аргумент критерий задает диапазон критериев. Диапазон критериев формируется так же, как при использовании расширенного фильтра.

В табл. 1 приведено краткое описание функций баз данных.

Таблица 1. Функции баз данных

Функция	Описание
ДСРЗНАЧ	Вычисляет среднее значение в столбце списка или базы данных среди значений, удовлетворяющих заданным условиям
БСЧЕТ	Подсчитывает количество ячеек, содержащих числа, в столбце списка или базы данных среди записей, удовлетворяющих заданным условиям
БСЧЕТА	Подсчитывает все непустые ячейки, которые удовлетворяют заданным условиям
БИЗВЛЕЧЬ	Извлекает отдельное значение, которое удовлетворяет заданным условиям
ДМАКС	Возвращает наибольшее число, которое удовлетворяет заданным условиям
ДМИН	Возвращает наименьшее число, которое удовлетворяет заданным условиям
БДПРОИЗВЕДЕН	Перемножает значения, которые удовлетворяют заданным условиям
БДСУММ	Суммирует числа, которые удовлетворяют заданным условиям
ДСТАНДОТКЛ	Оценивает стандартное отклонение на основе выборки из генеральной совокупности
ДСТАНДОТКЛП	Вычисляет стандартное отклонение генеральной совокупности
БДДИСП	Оценивает дисперсию генеральной совокупности по выборке
БДДИСПП	Вычисляет дисперсию генеральной совокупности

Пример. Необходимо вычислить минимальный оклад у работников ПФО с именем Николай. На рис. 23 продемонстрировано решение этой задачи.

Рис. 23. Пример использования функции баз данных

Проверка вводимых значений

MS Excel предлагает специальное средство, позволяющее проверить, удовлетворяют ли заданным условиям вводимые в список значения. Проверке подвергаются только значения, вводимые пользователем непосредственно в ячейки. Поэтому список может содержать некорректные данные, если они оказались там, в результате операций копирования и вставки.

Чтобы задать условия проверки данных, нужно выделить диапазон ячеек, к которому должны применяться эти условия, затем воспользоваться командой Данные\Проверка. На экране появится диалоговое окно Проверка вводимых значений, содержащее три вкладки: Параметры, Сообщение для ввода, Сообщение об ошибке.

Задание типа данных и допустимых значений

Вкладка Параметры позволяет задать тип и интервал значений, которые разрешается вводить. На рис. 24 приведен пример определения типа и интервала вводимых значений.

Рис. 24. Пример определения типа и интервала вводимых значений

Чтобы задать список допустимых значений, его нужно сначала сформировать на рабочем листе, а потом, в раскрывающемся списке Тип данных выбрать вариант Список (рис. 25) и в поле Источник указать диапазон, в котором хранится список допустимых значений.

Чтобы для проверки данных Excel использовал формулу, в раскрывающемся списке Тип данных нужновыбрать вариант Другой и затем ввести нужное выражение в поле Формула.

Рис. 25. Пример задания списка допустимых значений

Сообщение для ввода

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

Рис. 26. Пример задания сообщения для ввода

Задание сообщения об ошибке

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

Кроме того, в раскрывающемся списке Вид можно выбрать тип сообщения об ошибке:

• Останов;
• Предупреждение;
• Сообщение.

Эти три варианта отличаются значками, которые выводятся рядом с текстом сообщения, а также набором кнопок.

Рис. 27. Задание сообщения об ошибке