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

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

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

Шаг 1: исходный список нужно отсортировать по полюОтдел(рис. 6.19).

Шаг 2: воспользоваться командойДанные|Итоги…. На экране появится диалоговое окно (рис. 6.20).

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

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

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

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

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

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

На рис. 6.21 продемонстрирован результат подведения итогов.

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

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

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

Функции СЧЕТЕСЛИ и СУММЕСЛИ

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

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

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

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

Пример.

1. Необходимо подсчитать количество сотрудников с именем Иван (рис. 6.22). Для решения задачи понадобится формула: =СЧЕТЕСЛИ(В2:В7;”Иван”). Ответ: 3.

2. Требуется определить число сотрудников, у которых оклад не превышает 2000р. (рис.6.22). Задачу можно решить с помощью формулы: =СЧЕТЕСЛИ(Е2:Е7;”<=4500”). Ответ: 1.

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

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

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

Пример.

Пусть необходимо подсчитать сумму окладов всех сотрудников с именем Иван. Первый аргумент функции (рис. 6.22) задает диапазон В2:В7, в котором осуществляется проверка условия (второй аргумент) «имя должно быть Иван» (на рисунке выполнение условия выделено жирным шрифтом). Если условие выполняется, то значение из диапазона Е2:Е7 (третий аргумент) добавляется к итоговой сумме (суммируемые значения обведены рамкой).

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

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

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

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

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

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

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

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

Пример.

Необходимо вычислить средний оклад у работников бухгалтерии с именем Иван. На рис. 6.23 продемонстрировано решение этой задачи.