5. Обработка данных средствами excel

Чтобы дополнить Excel средствами для обработки данных (надстройки Диспетчер отчетов и Поиск решения), в диалоговом окне Надстройки меню Сервис отметьте соответствующий переключатель и нажмите OK (Рис.1).

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

Рис.1. Диалоговое окно Надстройки.

1. Составление итоговых отчетов

Цель задания: научиться составлять промежуточные итоги.

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

Таблица 1

Товар	Тип	Наименование	Цена	Кол-во	Сумма
Принтер	Матричный	Epson LX-1050	1 276р.	2	2 552р.
Принтер	Матричный	Epson LQ-100	1 456р.	1	1 456р.
Принтер	Матричный	Epson LQ-2170	2 300р.	3	6 900р.
Принтер	Струйный	Epson Stylus 1520	2 432р.	5	12 160р.
Принтер	Струйный	Epson Stylus 3000	3 200р.	1	3 200р.
Принтер	Струйный	Epson Stylus 1500	3 199р.	3	9 597р.
Принтер	Струйный	Epson Stylus Photo 700	5 038р.	1	5 038р.
Принтер	Лазерный	HP Laser Jet 4000	9 023р.	3	27 069р.
Принтер	Лазерный	HP Laser Jet 5000	10 453р.	3	31 359р.
Принтер	Лазерный	HP Laser Jet Color 8500	13 027р.	4	52 108р.
Сканер	Листовой	Paragon Page 630	540р.	1	540р.
Сканер	Листовой	Paragon Page Easy	450р.	1	450р.
Сканер	Планшетный	Paragon 800IIEP	634р.	2	1 268р.
Сканер	Планшетный	ScanExpress 6000 SP	569р.	2	1 138р.
Сканер	Планшетный	Paragon 1200 SP	730р.	2	1 460р.
Сканер	Планшетный	ScanExpress A3 P	830р.	2	1 660р.

Найдем суммы, затраченные на покупку отдельно для всех принтеров и отдельно для всех сканеров с помощью функции автоматического вычисления итогов. Для этого:

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

• Выделите таблицу или хотя бы одну ячейку таблицы;

• В меню Данные выберите команду Итоги. Откроется диалоговое окно Промежуточные итоги (Рис. 2.).

Рис. 2.

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

В результате выполнения функции таблица будет дополнена строками, в которых отобразятся итоги для каждой группы отдельно (Рис.3). В последней из вставленных в таблицу строк содержится информация об общем итоге.

Рис.3. Таблица – пример применения функции автоматического вычисления итогов.

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

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

При вычислении итогов таблица была структурирована — в этом Вы сможете убедиться, взглянув на экран. С помощью уровней структуры можно обеспечить лучший визуальный контроль данных. Чтобы отобразить на экране только итоговые данные, следует выполнить щелчок на кнопке для второго уровня структуры, вследствие чего данные третьего уровня (отдельные значения) будут скрыты (Рис.4, 5). Чтобы снова вывести на экран отдельные значения, необходимо выполнить щелчок на кнопке для третьего уровня

Рис.4. Вариант отображения итоговых данных (третий уровень скрыт).

Рис.5. Вариант отображения итоговых данных.

Чтобы удалить введенные промежуточные итоги, достаточно выбрать команду Итоги в меню Данные и щелкнуть кнопку Убрать все.

Самостоятельно вычислите: