Раздел 3. Работа с группами таблиц

Понятие списка в Excel

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

Фамилия	Возраст	Пол
Петухова	17	ж
Петров	17	м
Зайцева	17	ж
Морев	19	м
Иванов	19	м
Петрова	21	ж

Рис. 1. Пример списка Excel

В приведенной таблице Excel данные в первом и в третьем столбце имеют текстовый формат, а данные во втором столбце – числовой. Названия полей списка должны быть помещены в одну ячейку. Данные списка и другие данные на этом же листе должны быть отделены, по крайней мере, одной пустой ячейкой, то есть они не должны соприкасаться. Список Excel является типичной базой данных и к нему применимо большое число специфичных операций. Большинство таблиц, с которыми работают пользователи Excel, являются списками или могут быть приведены к виду списка. Так, если таблица, показанная на рис.2 создавалась для каждой группы студентов, то их можно было бы объединить в одну таблицу, которая также была бы списком, добавив еще одно поле «Группа».

Группа	Фамилия	Возраст	Пол	Рост	Вес
99-л-3	Петухова	17	ж	170	65
99-л-3	Петров	17	м	187	90
99-л-3	Зайцева	19	ж	180	75
97-л-1	Попов	19	м	176	80
97-л-1	Козлов	19	м	171	70

Рис.2.Объединение таблиц в список

Работа со списками в Excel

Большинство операций, предназначенных для работы со списками, сосредоточены в меню «Данные». Если список создан правильно, то достаточно выделить одну из ячеек внутри списка и нажать нужную команду в меню «Данные». Excel автоматически определит границы вашего списка.

Рис. 2. Раскрытое меню «Данные»

Пункт «Сортировка» позволяет осуществить сортировку по выбранному критерию по одному или, в порядке приоритета, по двум или даже трем полям списка.

Рис.3.Двухступенчатая сортировка списка

«Фильтр» дает возможность показывать только те записи в списке, которые удовлетворяют некоторому критерию. Так установка пользовательского автофильтра, показанная на рис.3, отобразит в списке только фамилии оканчивающиеся на буку «в».

Рис3. Использование пользовательского автофильтра

Пункт «Форма» дает возможность быстрого ввода данных в список. Пункт «Итоги» позволяет подвести суммирующие итоги под данными каждой группы в списке. На рис.4 показано диалоговое окно подведения итогов, а на рис.5 результат выполнения этой операции.

Рис.4. Подведение итогов

Группа	Фамилия	Возраст	Пол	Рост	Вес
99-л-3	Петухова	17	ж	170	65
99-л-3	Петров	17	м	187	90
99-л-3	Зайцева	19	ж	180	75
99-л-3 Всего					230
97-л-1	Попов	19	м	176	80
97-л-1	Козлов	19	м	171	70
97-л-1 Всего					150
Общий итог					380

Рис.5.Результат от подведения итогов

«Консолидация» позволяет подвести итоги по нескольким однотипным таблицам. Возможности пунктов «Консолидация» и «Итоги» полностью перекрываются мощным механизмом построения сводных таблиц Excel.

Пункт «Группа и структура» применим не только к спискам Excel и позволяет изменять уровни детализации представления информации на листе Excel. Чтобы создать простейшую структуру на листе можно выделить несколько столбцов или строк и нажать пункт «Группировать».

Рис.6.Создание структуры

Над листом появится дополнительная полоска со значком квадрата . Нажатие на этот знак скроет столбцы, над которыми проведена черная линия. Если выделить столбцы под черной линией и вновь выбрать пункт «Группировать», то будет сделана двухуровневая структура. Аналогичным образом можно группировать и строки.

Рис 7. Результат проведения операции группирования

Создание структуры дает возможность просматривать на одном экране большие таблицы Excel, скрывая ненужные в данный момент данные.

Сводные таблицы Excel

Сводные таблицы позволяют осуществлять групповые операции над данными , находящимися либо в списках, либо в нескольких диапазонах консолидации, либо во внешних базах данных. При нажатии на пункт «Сводная таблица» в меню «Данные» возникает первое диалоговое окно мастера построения сводных таблиц (см. рис.8).

Рис.8. Мастер сводных таблиц –шаг1.

Для обработки списка нужно выбрать первую из предложенных опций и перейти на второй шаг мастера. На втором шаге необходимо выбрать обрабатываемый список и перейти к третьему шагу. На третьем шаге мастера (см. рис.9.) появится конструктор обработки данных списка.

Рис.10.Мастер сводных таблиц – шаг 3.

Для конструирования групповой операции над одним из полей списка, нужно перетащить мышкой название соответствующего поля в область «Данные». Так, чтобы получить суммарный вес, нужно перетащить в область «Данные». Области «Строка», «Столбец» и «Страница» позволяют получить кроме суммарного применения групповой операции по всем записям списка еще и частичные групповые операции над записями с одинаковыми значениями полей, выбранных в области. Так, чтобы получить не только суммарный вес всех людей, но и вес людей в каждой группе, нужно перетащить в область «Строка». На рис. 11 показан результат описанного перетаскивания.

Рис.11. Конструирование групповой операции по полю «Вес»

Для удаления любого поля из области конструктора нужно просто потянуть за него мышкой и вытащить за пределы данной области.

Нажав на кнопку «Далее» можно перейти на четвертый шаг мастера. На четвертом шаге нужно выбрать лист, в который будет помещена сводная таблица. Здесь можно выбрать создание сводной таблице на новом листе и завершить работу мастера. Полученная сводная таблица отображена на рис.12.

Рис.12.Сводная таблица, полученная в результате работы мастера

Для возврата в мастер сводных таблиц для изменения запроса на групповую операцию служит кнопка на панели «Сводные таблицы». Изменим запрос, добавив поле «Фамилия» в область данных. Сводная таблица примет вид, показанный на рис.13.

Рис.13. Сводная таблица с групповой операцией по полю «Фамилия»

Вновь вернемся в мастер сводных таблиц и добавим поле «Пол» в область «Столбец», а поле «Возраст» в область «Страница». Полученная сводная таблица показана на рис 14.

Рис.14.Результирующая сводная таблица

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

Задание

Преобразовать таблицу, показанную ниже, в список или списки Excel. На основании сводных таблиц определить:

1. Площадь предприятий, подлежащих реконструкции по районам;

2. Число предриятий, подлежащих реконструкции по видам деятельности;

3. Суммарную площадь и число предприятий по направлениям;

4. Все вышеперечисленное в одной сводной таблице.

Список предприятий, подлежащих реконструкции
Аэропорт
Бытовое обслуживание
№	Организация	Вид деятельности	Площадь
1	ООО Успех	Химчистка	120
2	ЗАО Удача	Парикмахерская	200
3	ООО Игрок	Химчистка	300
Торговля
1	ООО Фиалка	Цветы	30
2	ЗАО Бублик	Хлеб	100
3	ООО Ирис	Цветы	20
Сокол
Бытовое обслуживание
1	ООО Ботинок	Ремонт обуви	30
2	ООО Волос	Парикмахерская	50
Торговля
1	ООО Мороз	Бытовая техника	300
2	ЗАО Калач	Хлеб	100
3	ООО Роман	Книги	200
4	ЗАО Селедка	Рыба	100