Ревчук И.Н. Практикум по MS Excel для экономистов

Подождите немного. Документ загружается.

141 142

Чтобы «вложить» или вставить итог для групп, находя-

щихся внутри уже существующих групп, необходимо выпол-

нить следующие операции:

1). отсортировать список по двум или более столбцам, для ко-

торых необходимо подвести итоги;

2). вставить автоматические итоги для первого столбца, со-

держащего группы суммируемых данных. Этот столбец при

сортировке списка должен быть

указан в поле Сортировать

по;

3). после создания автоматических итогов в первом столбце

повторить процедуру для следующего столбца;

4). установить курсор в списке;

5). выбрать команду Данные/Итоги;

6). выбрать следующий столбец, по которому необходимо

подвести итоги, из списка При каждом изменении В;

7). снять флажок Заменить текущие итоги, а

затем нажать

кнопку ok;

8). повторять шаги с 4 по 7 для каждого столбца, по которому

необходимо подвести итоги.

Для вычисления значений итогов используется итоговая

функция, например, СУММ или СРЗНАЧ. Промежуточные

итоги могут быть отображены в списке с помощью нескольких

типов вычислений одновременно.

Общие итоги подводятся с помощью детальных данных,

а не с

помощью значений промежуточных итогов. Например,

итоговая функция СРЗНАЧ возвращает значение для всех де-

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

значений.

Значения общих и промежуточных итогов пересчитыва-

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

ных.

Примеры решения задач

Задача 6.1. На рисунке 118 приведен фрагмент списка Авто-

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

сколько в списке машин каждой марки.

Рисунок 118

Решение. Отсортируем список по полю Марка машины, а затем

выполним команду Данные/Итоги. Заполнение диалогового окна

для списка выполним так, как показано на рисунке 119.

Резуль-

тат применения команды приведен в виде фрагментов на ри-

сунке

120.

Рисунок 119

143 144

Рисунок 120

При выполнении команды список был структурирован.

Щелчок мыши на символе строки уровня 1 дает возможность

просмотреть только общие итоги (рис. 121), на символе строки

уровня 2 – вывести только промежуточные и общие итоги (рис.

122), скрыв сам список, и увидеть все уровни детализации – на

символе строки уровня 3.

Рисунок 121

Рисунок 122

Чтобы удалить промежуточные итоги и структуру, мож-

но использовать кнопку «Убрать все» диалогового окна

«Промежуточные итоги». Для применения нескольких ито

го-

вых функций достаточно повторить команду «Итоги» для новой

итоговой функции и снять флажок «Заменить текущие итоги»

диалогового окна «Промежуточные итоги».

145 146

Задача 6.2. На рисунке 123 приведен фрагмент списка. Под-

вести промежуточные итоги, по которым можно определить

распределение должностей по половому признаку.

Фамилия Имя Отчество

Возраст

Должность

Оклад пол

Антонович Татьяна Васильевна 46 бухгалтер 272 ж

Богдан Елена Николаена 18 директор 168 ж

Гресь Елена Сергеевна 36

зав.лабора-

торией 219 ж

Гришук Татьяна Владимирова 41

зам.ди-

ректора

282 ж

Гресь Елена Сергеевна 45 лаборант 175 ж

Гресь Маруся Евгеньевна 24 менеджер 50 ж

Данилова Ольга Васильевна 36 менеджер 42 ж

Дешко Ольга Алексеевна 44 менеджер 119 ж

Храмова Татьяна Ивановна 39 менеджер 282 ж

Врублевская Елена Олеговна 47 оператор 131 ж

Башкатова Наталья Сергеевна 19

програм-

мист

218 ж

Рисунок 123

Решение. Отсортируем список по столбцам Пол и Должность.

При этом окно диалога Сортировка диапазона должно выгля-

деть так, как на рисунке 124. Вставляем автоматические итоги

для столбца пол (рис. 125–126). Повторяем процедуру для

столбца Должность (рис. 127–128).

Рисунок 124

Рисунок 125

147 148

Рисунок 126

Рисунок 127

Рисунок 128

Рисунок 129

149 150

Задачи для самостоятельного решения

1. Список содержит фамилию, должность, пол, год рождения

и телефон сотрудников предприятия.

1.1. Сколько Ивановых работают в фирме и кто из них са-

мый пожилой?

1.2. Каков средний возраст мужчин и женщин, работаю-

щих в фирме?

1.3. Сколько в фирме менеджеров, инженеров, водителей и

представителей других должностей?

2. Список содержит фамилии

студентов некоторого факуль-

тета, номера курсов, групп и наименования предметов, выне-

сенных в сессию.

2.1. Отсортировать список так, чтобы предметы (названия

полей) располагались в алфавитном порядке. Скопиро-

вать список на отдельный лист. На этом листе транс-

понируйте таблицу. Дополните таблицу строкой

«Средний балл» и заполните ее, введя соответствую-

щую

формулу.

2.2. Выполнить сортировки по столбцам. Перед каждой

сортировкой копировать таблицу на новый лист.

2.2.1. Переставьте столбцы так, чтобы фамилии сту-

дентов расположились в обратном алфавитном

порядке.

2.2.2. Переставьте столбцы так, чтобы в первых ко-

лонках были худшие студенты (с минимальным

средним баллом).

2.2.3. Переставьте столбцы так, чтобы в первых ко

лонках были студенты, хорошо успевающие по

математическому анализу, но с провалами по про-

граммированию.

2.2.4. Переставьте столбцы так, чтобы в начале табли-

цы оказались отличники по математическому ана-

лизу, причем первыми должны идти те, у кого

наилучший средний балл.

2.2.5. Переставьте столбцы так, чтобы фамилии сту-

дентов расположились в алфавитном

порядке,

снова транспонируйте таблицу и поместите ее на

Листе 1, начиная с клетки A12.

7. Анализ данных с помощью сводных таблиц

Для всестороннего и эффективного анализа данных боль-

ших таблиц в Excel используются т.н. сводные таблицы. Функ-

ции работы с СТ относятся к одному из самых мощных инстру-

ментов Excel.

Главные достоинства СТ – это представление больших

объемов информации в концентрированном и удобном для ана-

лиза виде, широкие возможности для группировки данных, а

также

возможность получения промежуточных и общих итогов,

которые помещаются в таблицу автоматически.

Применять этот инструмент следует для больших таблиц,

где имеются различные повторения значений в столбцах и (или)

строках.

Сводные таблицы являются «трехмерными», т.к. к при-

вычным атрибутам таблиц: строка и столбец, добавляется атри-

бут – страница

Для построения и модификации

сводной таблицы исполь-

зуется Мастер сводных таблиц, вызываемый с помощью пункта

меню Данные /Сводная таблица.

На первом шаге предлагается выбрать источник данных

для построения таблицы. Создать сводную таблицу можно на

основе данных, находящихся в одном из четырех источников

(рис. 130).

На втором шаге в диалоговом окне указывается интервал

клеток списка или

базы данных, которые должны быть сведены

(рис. 131).

На третьем шаге в макете таблицы создается структура СТ

и определяются ее функции. Макет представлен в центре окна и

состоит из областей: строка, столбец, страница и данные. Справа

от макета отображаются все имена полей (заголовки столбцов) в

заданном интервале исходной таблицы (рис. 130).

Размещение

полей в определенную область макета выпол-

няется путем их «перетаскивания» при нажатой левой кнопке

мыши. Чтобы удалить поле из области макета, его перетаски-

вают за пределы макета.

151 152

Рисунок 130

Рисунок 131

Каждое поле в областях столбец, строка, страница мо-

жет размещаться только один раз, а в области данные оно мо-

жет и повториться с разными итоговыми функциями.

Каждая область макета, куда размещаются поля исход-

ной таблицы, имеет свое назначение, определяющее внешний

вид сводной таблицы и ее функции:

Строка – поля этой области формируют

заголовки строк

сводной таблицы; если таких полей несколько, то они разме-

щаются в макете сверху вниз, обеспечивая группирование

данных сводной таблицы по иерархии полей, где для каждого

элемента внешнего поля, элементы внутреннего поля повторя-

ются

Рисунок 132

Рисунок 133

153 154

Столбец – поля в этой области формируют заголовки

столбцов сводной таблицы; если таких полей несколько, то

они в макете размещаются слева направо, обеспечивая группи-

рование данных сводной таблицы по иерархии полей;

Страница – поля в этой области выступают в качестве

фильтров и позволяют просматривать «на отдельных страни-

цах» данные сводной таблицы, соответствующие разным

зна-

чениям поля, помещенного в эту область; поле страницы в ре-

жиме просмотра располагается на две строки выше основной

части сводной таблицы в крайнем левом столбце. В соседней

справа клетке выводится (Все) и рядом раскрывающая стрелка

для выбора другого элемента поля; в области страница может

быть размещено несколько полей, между

которыми устанавли-

вается иерархическая связь – сверху вниз;

Данные – обязательно определяемая область для разме-

щения полей, по которым подводятся итоги, согласно выбран-

ной итоговой функции; размещаемые здесь поля могут быть

произвольных типов.

Примеры решения задач

Задача 7.1. Пусть исходная база данных содержит следующие

поля: Дата, Фамилия, Проект, Код работ, Часы, Стоимость,

Вид работы. Пользуясь имеющимися данными, требуется ото-

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

Решение. Запускаем Мастер создания сводных таблиц, выпол-

нив команду Данные/Сводная таблица. На рисунке 134 приве-

ден макет сводной таблицы.

Заполнение макета производится перетаскиванием

мышью названий полей базы в соответствующие области

макета.

Рисунок 134

На рис.

135 приведен результат работы Мастера создания

сводных таблиц.

Рисунок 135

В полученной сводной таблице вычислено суммарное коли-

чество часов, отработанных каждым сотрудником по каждому

проекту, по всем проектам, а также общий итог по всем про-

ектам. По такой таблице трудно решить поставленную выше

задачу, так как не видно, какие виды работ может выполнять

каждый сотрудник. Преобразуем полученную сводную таб-

155 156

лицу, перемещая поля следующим образом (это можно вы-

полнить непосредственно в сводной таблице и на этапе по-

строения макета сводной таблицы) (рис.

136).

Рисунок 136

Получаем новую сводную таблицу, в которой отражена

нужная информация (рис.

137).

Рисунок 137

Содержимое сводной таблицы на рисунке 137 – время, отра-

ботанное каждым сотрудником по всем проектам и с разбив-

кой по отдельным кодам работ.

Задача 7.2. Сгруппировать информацию в таблице на рисунке

137 по Петрову и Беловой.

Решение. Выделяем в сводной таблице соответствующие фа-

милии и выполняем группировку,

например так, как показано

на рисунках

138–139.

Рисунок 138

Рисунок 139

157 158

На рисунке 140 приведена сводная таблица, в которой Ива-

нов и Белова объединены в одну группу со стандартным на-

званием Группа1.

Рисунок 140

Выполнив двойной щелчок по Группа1, получаем таблицу,

приведенную на рисунке

141.

Рисунок 141

Вместо стандартного названия Группа1 можно обычным

способом ввести более подходящее название, например,

Объединение (рис.

142).

Рисунок 142

Задача 7.3. Пусть сводная таблица преобразована к виду, как

на рисунке 143. Выполнить группирование по месяцам.

Рисунок 143

Решение. Щелкнув по полю Дата, выделяем весь ряд с дата-

ми и выполняем группировку по месяцам, например, с по-

мощью кнопки на панели инструментов Сводная таблица. На

рисунках

144–145 приведены этапы выполнения группировки.

Рисунок 144

Задача 7.4. Выполнить группировку по кодам работ в сводной

таблице, приведенной на рисунке 142.

Решение. Выполним группирование по кодам работ (они число-

вые). Щелкнув по полю Код работ, выделяем весь ряд с кодами

работ и выполняем группировку по числовому диапазону (рис.

146).

159 160

Рисунок 145

Рисунок 146

На рисунке 147 приведен результат выполнения указанных

действий.

Задача 7.5. Пусть исходная база данных содержит

информацию о продаже продуктов питания в разные годы.

Получить сводную таблицу, отражающую прирост по отноше-

нию к предыдущему году.

Решение. Построим сводную таблицу, в которой на этапе соз-

дания макета в поле Данные помещены два поля Объем

(рис. 148).

Рисунок 147

Рисунок 148

Выполнив двойной щелчок по Сумма по полю Объем2 в области

Данные, получим окно диалога, в котором воспользуемся кноп-

кой Дополнительно (рис. 149–150).