<< Предыдущая страница

Содержание

Следующая страница >>

Раздел 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