Информатика - Борисов

15. Практическое закрепление навыковработыстаблицамивExcel

стоятельно построить нижнюю часть поверхности эллипсоида для

РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.

251

Раздел III. Современные информационные технологии

16. РАЗРАБОТКА КОМПЛЕКСНЫХ ДОКУМЕНТОВ

Вопросы:

16.1.Общие принципы работы с большими таблицами

16.2.Вставка, внедрение и связывание объектов

16.3.Связывание рабочих листов

16.1. Общие принципы работы с большими таблицами

Для углубленного анализа табличных данных в программе Excel

реализован Мастер сводных таблиц и диаграмм, который вызывается командой Меню Данные→Сводная таблица… (см. рис. 16.1).

Рис. 16.1

Сводные таблицы и диаграммы предназначены для группирова- ния данных в больших таблицах, а также извлечения обобщенных показателей для промежуточных и итоговых таблиц анализа. При этом, как правило, используется множество Листов, входящих в Книгу программы Excel. Данные с различных листов связываются с помощью символа «!» (например, в ячейке А5 на листе 'Итог' введена формула ='Л1'!D3+'Л2'!D3, которая позволяет выполнить суммиро- вание числа из ячейки D3 на листе 'Л1' и D3 на листе 'Л2'). Адрес блока (диапазона) используется вместе с идентификатором листа, что позволяет свести нужные данные с нескольких листов на итого- вый обзорный лист, содержание таблиц которого автоматически об- новляется при внесении изменений в листах книги.

252

16. Разработка комплексныхдокументов

Алгоритм Мастера сводных таблиц и диаграмм включает три шага выполнения операций. На первом шаге выбирается источник данных для сводной таблицы. Возможны варианты:

•в списке или базе данных листа Excel (список – таблица с заго- ловками в первой строке, определенная по команде Меню Дан-

ные→Список→Создать список…);

•во внешнем источнике данных (база данных, текстовый файл);

•в нескольких диапазонах консолидации (в нескольких областях различных листов Excel).

Очевидно, что таблицы на различных листах должны иметь оди- наковую структуру. Далее выбирается вид создаваемого отчета: свод- ная таблица или сводная диаграмма.

На втором шаге Мастера…(см. рис. 16.2) выбирается адрес блока (диапазона) списка (базы данных), который нужно учесть в сводной таблице.

Рис. 16.2

Полный адрес блока (диапазона) включает [Имя книги]Имя лис- та!Адрес блока. В квадратных скобках показан необязательный атри- бут – Имя книги. Если сводная таблица в той же книге, где исход- ные, то имя книги можно не указывать. Кроме того, следует посто- янно отслеживать, чтобы структура выбираемых данных с разных источников совпадала и соответствовала таблице-списку с заголов- ками.

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

На третьем шаге Мастера…(см. рис. 16.3) определяется макет сводной таблицы. Макет (структура) состоит из полей: страница, строка, столбец и данные. Справа на форме окна отображаются заго- ловки столбцов списка исходной таблицы. Для создания макета не- обходимо перетащить кнопки полей заголовков в нужные области

253

Раздел III. Современные информационные технологии

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

Выбор данных в областях макета определяет внешний вид свод- ной таблицы. В области Строка группируются данные в строках, а в области Столбец – в столбцах по иерархии выбранных полей- заголовков.

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

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

Рис. 16.3

254

16. Разработка комплексныхдокументов

Задание 16.1. Сводная таблица

По исходной таблице «Учет количества деревьев» (Таблица 16.1) построить сводную таблицу, сгруппировав данные по породе деревь- ев, номеру делянки и возрасту деревьев. Сводные итоги подсчитать с целью определения количества деревьев в указанных группах.

Таблица 16.1

Порядок выполнения задания следующий.

1.НалистеExcel «Учетдеревьев» ввестиТаблицу 16.1.

2.Превратитьвведеннуютаблицувсписок(см. рисунокниже). Для этого, выделите таблицу (исключая название), выполните команду Меню Данные→Список→Создать список…. Установить галочку «Спи-

соксзаголовками», «ОК».

255

Раздел III. Современные информационные технологии

3.Запустить Мастер сводных таблиц и диаграмм, который вызыва-

етсякомандойМенюДанные→Своднаятаблица…

4.На первом шаге определить источник данных для сводной таб- лицы, выбраввариантывспискеилибазеданныхлистаExcel.

5.На втором шаге Мастера… (см. рис. 16.2) выбрать адрес блока с данными(диапазон$A$2:$F$31).

6.Натретьемшаге Мастера… нажатькнопкуМакет… иопределить макет сводной таблицы перетаскиванием нужных полей мышью в со- ответствиисрис. 16.3, «ОК», «Готово»

7.Сравнитьготовуюсводнуютаблицустаблицейнарисункениже.

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

РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.

256

16. Разработка комплексныхдокументов

16.2. Вставка, внедрение и связывание объектов

Существует технология динамического обмена данными (DDE) между Excel и другими приложениями Windows. Рассмотрим осо- бенности этой технологии на примере установления связи между Word и Excel, проследив их в ходе выполнения Задания 16.2.

Задание 16.2. Динамический обмен данными

1.Установить динамическую связь между документом Word и таблицей Excel. Для выполнения задания потребуется таблица, соз- данная при выполнении Задания 16.1.

2.Порядок выполнения задания следующий.

3.ЗапуститеExcel иоткройте файлЗадания 16.1, созданный напре- дыдущем занятии. Переименуйте сводную таблицу и сохраните файл надискеподименем«Книга1_Своднаятабл».

4.Запустите программу Microsoft Word исоздайте новый документ. Включите в документ файл таблицы Excel «Книга1_Сводная табл», установив динамическую связь между Word и Excel. Для этого выпол- ните команду Меню Вставка→Объект…. На вкладке Вставка объекта откройте подраздел Создание из файла и щелкните кнопку Обзор для поиска файла таблицы Excel «Книга1_Сводная табл». На вкладке Соз- дание из файла включите флажок Связь с файлом. После этого таблица «Книга1_Своднаятабл» будетвставленавдокументWord.

5.Перейдите в окно Excel и измените данные в таблице Учета ко- личествадеревьев(см. таблицу16.1).

6.Убедитесь, что автоматически изменяется сводная таблица Excel «Книга1_Своднаятабл».

257

Раздел III. Современные информационные технологии

7.Перейдите в окно Word и убедитесь в том, что изменения, вне- сенные в окне Excel в файл таблицы «Книга1_Сводная табл», отобра- жаются и в динамически связанной с ним таблице, вставленной в до- кументWord.

8.Завершите работу приложений Excel и Word без сохранения до- кументов.

16.3. Связывание рабочих листов

Связывание рабочих листов книги в программе Excel рассмотрим на примере подсчета результатов продаж по кварталам, проследив особенности в ходе выполнения Задания 16.3. Связывание листов

Задание 16.3. Связывание листов

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

Порядок выполнения задания следующий.

1. Загрузите программу Excel, создайте в новой книге 5 листов и на первомлистенаберитетаблицу, образецкоторойпредставленниже.

2.Отформатируйтеячейкитаблицывстолбцах«Цена» и«Сумма», в которых будут отображаться финансовые значения, используя коман-

ду Меню Формат→Ячейки… в меню Формат ячеек→Число выбрать

Финансовый.

3.Скопировать полученную таблицу на другие листы книги, ото- бражающие расчеты продаж в I, II, III и IV кварталах и по итогам за год. Для чего выделить блок A1:D9 и скопировать таблицу на другие листы.

4.Переименоватьлисты, задавимназвания: 1кв, 2кв, 3кв, 4кв, Год.

5.Удалитьналисте«Год» столбецС(Цена).

258

16.Разработка комплексныхдокументов

6.В ячейку С3 листа «Год» введите формулу, показанную на рис.16.4. Чтобы не вводить формулу с клавиатуры, выполните сле- дующее.

•Установите курсор в ячейку С3 на листе «Год», введите «=».

•Щелкните наярлыке листа 1кв, азатем наячейкеD3 этоголиста.

•Введите с клавиатуры «+».

•Щелкните на ярлыке листа 2кв, затем на ячейке D3 этого листа.

•Введите с клавиатуры «+».

•Аналогичным образом просуммируйте ячейки D3 листов 3кв и

4 кв.

•Подтвердите ввод формулы нажатием клавиши Enter.

Рис. 16.4

7.Размножьте формулу из ячейки С3 листа «Год» автозаполнением доячейкиС9.

8.Заполнить самостоятельно произвольными данными таблицы продаж мороженого по кварталам на листах: 1кв, 2кв, 3кв, 4кв (Коли- чествоиЦеназаполняютсяизпрактическихсоображений).

9.После заполнения листа «1кв» сравните данные с листом «Год». Пример полученных таблиц показывает, что заполнение данными продажналисте«1кв» автоматическипереносятсяналистГод.

10.Поочередно открывая листы 2кв, 3кв, 4кв введите данные о продажах мороженого разных сортов. Пронаблюдайте, как на листе «Год» суммируютсяитогипродажпокварталам.

11.Постройте круговуюдиаграмму, отражающую долювыручки от продажи каждого сорта мороженого за год в процентах от общей сум- мы. Выделив диапазон данных АЗ:С8, выберите команду в Меню Вставка→Диаграмма…. Следуя указаниям Мастера диаграмм, выбери-

те Объемный вариант разрезанной круговой диаграммы и щелкните кнопку Далее. Затем уточните диапазон отображаемых данных Год!$А$3:$С$8, укажите на отображение рядов данных в столбцах, на вкладке Ряд удалите Ряд1, оставив для отображения данные только

259

Раздел III. Современные информационные технологии

Ряд2 из столбца с суммой выручки от продаж С3:С8 на листе «Год». Полученная диаграмма должна иметь вид, похожий на рисунок, при- веденныйниже.

РЕЗУЛЬТАТЫ РАБОТЫ ПРЕДЪЯВИТЬ ПРЕПОДАВАТЕЛЮ.

260