5

Тема 2: Работа с электронными таблицами: Программа Microsoft excel

2.5. СВОДНЫЕ ТАБЛИЦЫ

Задание 2_5-1. На основе данных о работе трех аптек, определить:

1) выручку для каждой аптеки;

2) выручку от продажи медицинских препаратов каждого наименования;

3) общую выручку по всем аптекам;

4) выручку от продажи антигистаминных препаратов для каждой аптеки; общую выручку от продажи антигистаминных препаратов.

Для решения задачи создать файл lab2_5_Фамилия.xls таблицу сохранить на листе «Аптеки 1».

	А	В	С	D
1	Аптека	Наименование	Кол-во	Цена
2	№1	Пенталгин	80	3,40р.
3	№1	Аспирин	75	2,80р.
4	№1	Супрастин	25	5,00р.
5	№2	Пенталгин	70	3,40р.
6	№2	Аспирин	90	2,80р.
7	№2	Эриус	15	5,00р.
8	№2	Тавегил	120	2,30р.
9	«Авицена»	Супрастин	60	1,25р.
10	«Авицена»	Аспирин	8	2,80р.
11	«Авицена»	Эриус	40	5,00р.
12	«Авицена»	Тавегил	50	2,30р.

Решение

Для решения задачи создайте следующую таблицу, для заполнения столбца Е в ячейку Е1 введите заголовок столбца: Выручка, в ячейку Е2 введите формулу =C2*D2 и скопируйте ее с помощью маркера заполнения в ячейки ЕЗ:Е12.

Вызов мастера сводных таблиц: сделайте активной какую-либо ячейку таблицы с данными (например, А1); «ВСТАВКА | Сводная таблица...». В результате появится окно мастера сводных таблиц.

Для определения диапазона с исходными данными оставьте вариант по умолчанию (А1:Е12, то есть вся таблица с данными).

Определение макета сводной таблицы: в появившемся диалоговом окне: зацепите мышью элемент с текстом «Аптека» и перетащите его в область «Название Столбца», затем перетащите элемент «Наименование» в область «Название Строки» и элемент «Выручка» в область «Значения» (в результате макет сводной таблицы примет указанный ниже вид).

Примечание:

Для удаления ошибочно размещенного элемента макета следует зацепить его мышью и «стащить» с таблицы.

Установите для ячеек, по которым проводятся вычисления, формат «Денежный» и установите число десятичных знаков равным 2;

Теперь можно ответить на первые три вопроса упражнения:

1. Выручка для каждого магазина указана в нижней строке;

2. Выручка по каждому наименованию - в правом столбце;

3. Общая выручка - в правой нижней ячейке.

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

1. В столбце «Названия препаратов» сводной таблицы выделите ячейки, соответствующие антигистаминным препаратам («Супрастин», «Тавегил», «Эриус»);

2. команда «Параметры | Группа и структура | Группировать»

В результате в сводной таблице появится еще один столбец с заголовком «Наименование2», в котором всем антигистаминным препаратам будет соответствовать одна ячейка «Группа1»; для большей наглядности содержимое созданной группы следует заменить на «Антигистаминные препараты» .

В результате сводная таблица примет вид:

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

Сохраните измененный файл под прежним именем.

1. Автоматического пересчета сводной таблицы при изменении исходных данных не происходит. Для того чтобы откорректировать содержимое сводной таблицы после изменения исходных данных, надо активизировать

сводную таблицу, щелкнув на ней мышью, и нажать кнопку [Обновить] на панели «Параметры».

Задание 2_5.2. На основе данных о работе трех аптек, торгующих лекарственными препаратами (см. задание 2_5-1), определить:

1. количество препаратов, проданных в каждой аптеке;

1. общее количество проданных препаратов каждого наименования;

3 общее количество проданных препаратов (всех наименований);

4. количество антигистаминных препаратов, проданных в каждой аптеке;

5. общее количество проданных антигистаминных препаратов.

2.6. СОВМЕСТНОЕ ИСПОЛЬЗОВАНИЕ ТЕКСТОВЫХ ДОКУМЕНТОВ И ЭЛЕКТРОННЫХ ТАБЛИЦ

Задание 2_6.1. Создать документ, демонстрирующий различные способы вставки в текст данных из электронной таблицы. Данные должны быть вставлены четырьмя способами:1) как обычный текст;2) как отформатированный текст (таблица); 3) как рисунок; 4) как объект — электронная таблица.

В качестве исходных данных использовать имеющийся файл lab2_5.Фамилия.xls лист «Аптеки». Созданный документ сохранить в файле lab2_6.Фамилия.doc.

Решение.

Запустите программу Microsoft Excel, откройте имеющийся файл lab2_5.Фамилия.xls лист «Аптеки», выделите содержащуюся в нем таблицу вместе с заголовком и скопируйте ее в буфер: «Вставка | Копировать» или [Ctrl]+[C] (при этом около выделенного блока появится пунктирная рамка).

Запустите программу Microsoft Word и создайте новый документ. Созданный документ сохранить в файле lab2_6.Фамилия.doc.

Вставка электронной таблицы как обычного текста: «Главная | ВСТАВКА | Специальная вставка», в появившемся окне выберите вариант «Неформатированный текст», [ОК]. В результате подобной вставки текст из всех ячеек электронной таблицы будет скопирован в документ, причем тексты соседних ячеек будут разделяться знаком табуляции.

Неформатированный текст:

Сумма по полю Кол-во Названия аптек

Названия препаратов «Авицена» №1 №2 Общий итог

Аспирин 8 75 90 173

Пенталгин 100 70 170

Антигистаминные препараты 150 25 135 310

Общий итог 158 200 295 653

Вставка электронной таблицы как отформатированного текста: «Вставка | Специальная вставка», в появившемся окне выберите вариант «Текст в формате RTF», [OK]. В результате подобной вставки в документе появится таблица Word, содержание которой в точности соответствует содержанию электронной таблицы Excel. Копируются также форматные настройки ячеек.

Текст в формате RTF:

Сумма по полю Кол-во	Названия аптек
Названия препаратов	«Авицена»	№1	№2	Общий итог
Аспирин	8	75	90	173
Пенталгин		100	70	170
Антигистаминные препараты	150	25	135	310
(пусто)
Общий итог	158	200	295	653

Вставка электронной таблицы как рисунка: «ГЛАВНАЯ | Вставка | Специальная вставка», в появившемся окне выберите вариант «Точечный рисунок»]. В результате подобной вставки в документе появится копия изображения выделенной части электронной таблицы. Данное изображение можно настраивать с помощью панели «Работа с рисунками», а также изменять его размер.

Точечный рисунок:

Вставка электронной таблицы как объекта: «Вставка | Специальная вставка», в появившемся окне выбрать вариант «Лист Microsoft Excel (объект). Появившееся изображение очень похоже на рисунок, вставленный на предыдущем шаге; его размер также можно изменять с помощью угловых маркеров. Однако это не рисунок, а особый объект, в чем можно убедиться, выполнив на нем двойной щелчок мышью. В результате данный объект перейдет в режим редактирования; при этом вокруг него появится заштрихованная рамка, а меню и панели инструментов программы Word временно заменятся меню и панелями инструментов программы Excel. Для выхода из режима редактирования объекта достаточно щелкнуть мышью вне области, ограниченной заштрихованной рамкой.

Лист Microsoft Excel (объект):

Примечание:

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

Добавьте в текст документа задание, комментарии перед каждой вставленной копией данных и сохраните документ. Сделайте копию экрана. После этого выйдите из программ Word и Excel.

Задание 2_6-2. Создать документ, демонстрирующий способы связывания с документом данных из электронной таблицы. В качестве исходных данных использовать имеющийся файл lab2_5.Фамилия.xls.

Связывание с данными из электронной таблицы: действия, обеспечивающие сохранение связи вставленных данных с исходной таблицей Excel, совпадают с аналогичными Действиями по специальной вставке данных, описанными в задании 2-6.1, за одним исключением: при выборе способа специальной вставки дополнительно требуется указать вариант «Связать» (вместо варианта «Вставить»). Это приведет к тому, что при вставке копии данных в документ у нее сохранится связь с электронной таблицей, и при изменении исходной таблицы все ее копии, помещенные в документ, также изменятся.

Примечание:

• При отображении связанных данных в виде обычного или отформатированного текста эти данные в окне программы Word выводятся на сером фоне.

Особенности режима редактирования для объекта, связанного с источником данных: при двойном щелчке на объекте, связанном с источником данных, происходит запуск программы Excel и немедленная загрузка в нее соответствующего источника данных (в этом можно убедиться по заголовку окна, в котором будет указано имя файла lab2_5.Фамилия.xls). Заметим также, что закрывать программу Excel для возврата в документ нет необходимости: достаточно переключиться в программу Word, щелкнув на ее кнопке на панели задач.

5