Электронные таблицы, функции, базы данных и взаимосвязи: Диаграммы. Списки. Макросы

УДК 004.42 
М91 

Р е ц е н з е н т 
ст. нренодавательМО. Пышняк 

Муратова СЮ. 

М91 
Электронные таблицы, функции, базы данных и взаимосвязи: 

Диаграммы. Списки. Макросы: Лаб. практикум.- М.: МИСиС, 
2003.-116 с. 

Настоящий лабораторный практикум используется при изучении курса 
«Электронные таблицы, функции, базы данных и взаимосвязи» (раздел «Диаграммы. Списки. Макросы») и является продолжением одноименного практикума (раздел «Создание и форматирование таблиц. Взаимосвязи. Расчеты. 
Функции. Регрессии»). Практикум рассчитан на 34 часа аудиторных и самостоятельных занятий, что составляет половину курса. В практикум включено 
шесть работ, выполнение которых позволит приобрести навыки построения и 
редактирования диаграмм, работы с базами данных Excel, формирования 
сводных таблиц и консолидации данных, автоматизации рутинных операций 
с помощью макросов, а также обмена информацией между Excel и Word. 

© Московский государственный институт 
стали и сплавов (Технологический 
университет) (МИСиС), 2003 

СОДЕРЖАНИЕ 

Предисловие 
6 

Лабораторная работа № 6. Диаграммы. Построение диаграмм 
7 

6.1. Построение диаграмм на отдельном листе 
7 

6.2. Изменение типа диаграммы 
11 

6.3. Изменение параметров диаграммы 
12 

6.4. Удаление/добавление рядов 
13 

6.5. Построение диаграммы на листе с исходной таблицей 
14 

6.6. Форматирование диаграмм 
14 

6.6.1. Форматирование и редактирование надписей 
14 

6.6.2. Форматирование осей диаграмм 
16 

6.6.3. Форматирование области диаграмм 
16 

6.6.4. Форматирование рядов данных 
17 

6.7. Выбор типа диаграммы 
18 

6.7.1. Гистограммы и линейчатые диаграммы 
18 

6.7.2. Графики 
19 

6.7.3. Круговые диаграммы 
19 

6.7.4. Кольцевые диаграммы 
21 

6.7.5. Диаграммы с областями 
23 

6.8. Использование линий трендов 
24 

Контрольные вопросы 
30 

Лабораторная работа № 7. Списки (базы данных Excel). 
Создание и редактирование списков. Сортировка. Фильтрация. 
Создание промежуточных итогов 
29 

7.1. Создание списка 
31 

7.2. Форма данных 
31 

7.2.1. Добавление записей с помощью формы данных 
31 

7.2.2. Просмотр и редактирование записей с помощью формы 
данных 
33 

7.2.3. Удаление записей с помощью формы данных 
33 

7.2.4. Поиск записей с помощью формы данных 
34 

7.3. Сортировка списков 
35 

7.3.1. Быстрая сортировка списка 
35 

7.3.2. Сортировка списка или фрагмента таблицы 
по нескольким полям 
36 

7.3.3. Пользовательский порядок сортировки 
38 

7.4. Фильтрация данных 
39 

7.4.1. Фильтрация данных по одному полю 
40 

3 

7.4.2. Фильтрация данных по нескольким полям 
43 

7.4.3. Удаление автофильтров 
43 

7.4.4. Нахождение первых десяти значений списка 
43 

7.4.5. Более сложные условия отбора. Пользовательский 
автофильтр 
44 

7.4.6. Копирование отфильтрованных данных в другое место...46 

7.5. Расширенный фильтр 
47 

7.5.1. Задание условий отбора 
47 

7.5.1.1. Отбор записей по альтернативным критериям 
в разных столбцах 
47 

7.5.1.2. Отбор записей по альтернативным критериям 
в одном столбце 
50 

7.5.1.3. Пример одновременного использования ИЛИ и И 
51 

7.5.1.4. Вычисляемые условия 
51 

7.5.2. Копирование отфильтрованных строк в другое место 
рабочего листа 
53 

7.6. Промежуточные итоги 
57 

7.6.1. Создание промежуточных итогов 
57 

7.6.2. Сокрытие и отображение структурированных данных 
59 

7.6.3. Удаление структуры и промежуточных итогов 
60 

7.6.4. Вложенные промежуточные итоги 
61 

Контрольные вопросы 
64 

Лабораторная работа № 8. Списки (базы данных Excel). 
Сводные таблицы 
63 

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

8.1.1. Создание сводной таблицы 
63 

8.1.2. Изменение расположения полей 
67 

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

8.1.4. Использование функций в сводных таблицах 
70 

8.1.5. Исключение промежуточных итогов из сводных таблиц .73 
8.1.6. Размещение полей на оси страниц 
74 

8.1.7. Обновление сводных таблиц 
75 

8.1.8. Форматирование сводных таблиц 
76 

8.2. Отображение и сокрытие данных 
78 

8.2.1. Отображение и сокрытие элементов внутренних полей...78 
8.2.2. Вывод детальной информации для значения поля 
данных 
78 

8.3. Группировка элементов 
79 

8.3.1. Создание групп 
79 

8.3.2. Удаление групп 
79 

4 

8.4. О том, как Excel организует хранение сводных таблиц 
80 

Контрольные вопросы 
84 

Лабораторная работа № 9. Консолидация данных 
83 

9.1. Консолидация по расположению 
84 

9.2. Консолидация по категориям 
87 

9.3. Создание связей с исходными листами 
89 

9.4. Использование сводной таблицы для консолидации данных..91 

9.4.1. Консолидация данных с одним полем страниц 
91 

9.4.2. Консолидация данных с несколькими полями страниц....94 

Контрольные вопросы 
100 

Лабораторнаяработа№ 10. Макросы 
99 

10.1. Запись макроса 
99 

10.2. Абсолютные и относительные ссылки 
101 

10.3. Удаление макроса 
102 

10.4. Назначение макроса кнопке на панели инструментов 
102 

10.5. Назначение макроса графическому объекту 
104 

10.6. Личная книга макросов 
105 

10.7. О том, куда записывается макрос 
105 

Контрольные вопросы 
109 

Лабораторная работа №11. Копирование объектов Excel 
в документ редактора Word. Установление связи между ними ...108 
11.1. Копирование таблицы Excel в документ редактора Word 
обычным способом 
108 

11.2. Копирование таблицы Excel в документ редактора Word 
как внедренного объекта 
ПО 

11.3. Копирование таблицы Excel в документ редактора Word 
как связанного объекта 
112 

Контрольные вопросы 
114 

Библиографический список 
113 

5 

ПРЕДИСЛОВИЕ 

Таблицы... Какая скучища! - нарисуйте диаграмму и ваши 
данные «оживут» и станут понятными. 

Базы данных... огромное количество информации...- воспользуйтесь формой для ввода данных, фильтром - для отбора данных, а для анализа и составления сводок - сводными таблицами. 

Данные разбросаны по книгам и таблицам? - не беда! Консолидация сведет их в одну таблицу, а если это сводная таблица, то она 
найдет нам промежуточные итоги. 

Эх, все равно не то.... Взмахнуть бы волшебной палочкой, 
раз - и готово! Волшебную палочку Excel не обещает, а вот волшебную кнопочку с макросом подскажет, как сделать. Щелкнешь по 
кнопочке - и готов документ к печати, щелкнешь по другой - и она 
тебе... да что захочешь, то и сделает. Обо всем: об этом и еще кое о 
чем-в этом практикуме. 

6 

Лабораторная работа № 6 

ДИАГРАММЫ. ПОСТРОЕНИЕ ДИАГРАММ 

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

6.1. Построение диаграмм на отдельном листе 

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

Откройте файл Мои таблицы.хк (лист Население Земли). 
Отобразим на диаграмме численность населения в трех азиатских 
странах. Для этого: 

1. Выделите диапазон ячеек B20:F22. 
2. Выполните команду Вставка/Диаграмма или щелкните 

по инструменту Мастер диаграмм !»_, который расположен в панели инструментов Стандартная. 

3. При появлении на экране диалогового окна с различными 
диаграммами (рис. 6.1) выберите объемный вариант обычной гистограммы и щелкните по кнопке Далее. 

4. После того, как во втором окне Мастера диаграмм 
(рис. 6.2) появится макет диаграммы, проверьте, правильно ли Мастер диаграмм интерпретирует исходные данные (воспринимает их в 
строках или столбцах). Выделив три строки, мы тем самым указали 
три ряда данных. 

Данные, отображаемые в диаграмме, называются;;^^^^^ daumvc. 
Надпись, расположенная справа от диаграммы, называется 
легендой, в которой отражаются названия рядов. 

5. Чтобы связать с названиями рядов названия стран, щелкните по вкладке Ряды. Па экране появится окно (рис. 6.3). Выделив в 
поле Ряд значение Ряд1, щелкните мышкой в соседнем поле по 
кнопке свертывания диалогового окна. Затем щелкните в исходной 
таблице по ячейке А22 и вернитесь обратно. Надпись Ряд1 заменится 
названием страны: Китай. Проделайте эту операцию с остальными 
рядами. У вас должны появиться названия Индия и Пакистан. 

7 

Рис. 6.1. Окно с диаграммами, из которых необходимо выбрать 
объемный вариант обычной гистограммы 

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

8 

Для того чтобы убрать 
о. щелкните по этой 
пке 

I 
Значения: 
|='На1:еление Земли'!tBt2D;tF W 

Добавить I Удалить J 

Ш 
Отмена 
J 
< Назад 
Далее > 
J 
Готово 

Рис. 6.3. В этом окне задаются названия рядов и нодниси оси X 

Таким же образом задайте подписи оси X. Для этого уберите 
окно с экрана и выделите в таблице диапазон с годами B2:F2. Теперь 
наша диаграмма имеет почти законченный вид (рис. 6.4). Щелкните 
по кнопке Далее. 

'Ч^!''1' 
Л*. 
г?гп 

«апазон данных 
Ряд 

гст; ш 

TiJ I R I I rnJ 

1эео 
1ЭГ0 
1930 
1э« 

[2f 
Отмена 
< Назад 
|| Далее > 
| 
Готово 

Рис. 6.4. Диаграмма с информативными названиями рядов 
и ноднисями оси X 

6. в следующем диалоговом окне (рис. 6.5) на вкладке 
Заголовки введите название диаграммы (Изменение численности 
населения в азиатских странах), дайте расшифровку оси X (Годы) 
и оси Z (человек). Остальные вкладки просмотрите самостоятельно. 

Рис. 6.5. Дальнейшее оформление диаграммы 

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

7. На последнем шаге надо «сказать» Мастеру диаграмм, 
где ему следует разместить нашу диаграмму: вместе с таблицей или 
на отдельном листе (рис. 6.6). Включите кнопку Отдельном и вместо 
Диаграмма! введите имя листа Диаграмма Азия. Щелкните по 
кнопке Готово. 

Рис. 6.6. Размещение диаграммы на отдельном листе 

10 

в результате мы получим диаграмму, показанную на рис. 6.7. 

Рис. 6.7. Диаграмма, размещенная на отдельном листе 

6.2. Изменение типа диаграммы 

Чтобы изменить тип диаграммы, выполните команду Диаграмма/Тип диаграммы или щелкните по инструменту Тип диаграммы в панели инструментов Диаграмма (рис. 6.8). 

Диаграммы 
Б 

ИСтены 
"• ^ 
1 ^. шщщт 
•%> # 

Тип диаграммы 

Рис. 6.8. Панель инструментов Диаграмма 

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

11 

Выберите График с маркерами, помечающими точки данных. 
Диаграмма видоизменится и примет вид, представленный на рис. 6.9*. 

Рис. 6.9. Диаграмма График с маркерами, помечающими точки данных 

6.3. Изменение параметров диаграммы 

После того как наша диаграмма превратилась в график, добавим значения данных для каждой точки оси X (т.е. для каждого года, 
указанного на оси X, отобразим на графике численность населения), 
а также линии сетки. Для этого: 

1. Выполнитс команду Диаграмма/Параметры диаграммы. 
2. В уже знакомом нам окне Параметры диаграммы 
(см. рис. 6.5) щелкните по вкладке Подписи данных и включите 
кнопку Значение. 

3. Перейдите на вкладку Линии сетки. В блоке Ось X (категорий) установите флажок в полях Основные линии и Промежуточные линии. 

4. Щелкните по кнопке ОК. 
Теперь диаграмма выглядит так, как показано на рис. 6.10. 

• На самом деле диаграмма немножко другая: область ее построения у вас будет серого цвета. Для лучшего восприятия в черно-белом варианте здесь убран фон. 

12 

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

Рис. 6.10. График с линиями сетки и подписями данных 

6.4. Удаление/добавление рядов 

Чтобы удалить ряд данных (например, Пакистан), щелкните 
по нему мышкой (выделится весь ряд) и нажмите клавишу [Delete]. 

Вьшолните команду Отменить, верните исходный тип диаграммы (объемная гистограмма), уберите линии сетки и удалите ряд Пакистан 
еще раз, щелкнув предварительно по любому из его столбиков. 

Для того чтобы добавить ряд данных (тот же Пакистан): 
1. Вьшолните команду Диаграмма/Добавить данные. Па 
экране появится окно Новые данные (рис. 6.11). 

Рис. 6.11. в этом окне необходимо указать новый ряд 

13 

2. Сверните это окно, перейдите на лист Население Земли 
и выделите следующий диапазон: 

Новые данные - Диапазон: 

='Население Земли'!$A$22||FJ22| 

На экране появится новый ряд Пакистан. 

Задание для самостоятельной работы 

1. Постройте на отдельном листе диаграмму со странами 
американского континента (тип диаграммы: обычная гистограмма). 

2. Удалите любой ряд данных. 
3. Добавьте его снова. 
4. Выведите в диаграмму подписи данных. 

6.5. Построение диаграммы на листе 
с исходной таблицей 

Построим диаграмму с заголовком «Изменение численности 
населения в трех европейских странах» на листе с исходной таблицей. Эта операция выполняется точно так же, как и построение диаграммы на отдельном листе. Просто этот пример иллюстрирует случай, когда ряды данных находятся в несмежных диапазонах. Кроме 
того, обратите внимание: выделив вместе с данными названия 
стран. Excel правильно распознает их как названия рядов (так как 
текст находится рядом с числовыми данными). 

Итак, выделите ячейки A6:F6, A8:F8, A10:F10 как несмежные диапазоны, щелкните по инструменту Мастер диаграмм и постройте диаграмму на листе с таблицей. 

6.6. Форматирование диаграмм 

Если вам что-то не нравится в вашей диаграмме, ее можно 
очень просто изменить. Перейдите на лист диаграммы с азиатскими 
странами. 

6.6.1. Форматирование и редактирование надписей 

Предположим, что мы хотим ч е л о в е к изменить на ч е л о в е к 
(тысяч) , развернув при этом надпись на 90°. Для этого два
14