Информатика_Excel_лаб_раб
.pdfКак только связь устанавливается, Excel копирует величину из ячейки в книге-источнике в ячейку книги назначения. Величина в ячейке назначения автоматически обновляется.
Упражнение 5.3. На основе таблиц созданных в упражнении 5.1, в другой книге сформируйте ведомость итоговых оценок за четверть.
Создайте новую книгу.
Заполните Лист1 новой книги ссылками на соответствующие ячейки другой книги. Для этого в ячейку А2 занесите формулу:
='D:\STUDENT\ВАШ_КАТАЛОГ\[tab5.xlsх]Литература! А2'
Внимание!
'D:\STUDENT \ВАШ_КАТАЛОГ\[tab5.xlsх] литepaтypa!' — путь к файлу tab5.xls и листу Литература. Этот путь обязательно должен быть заключен в одинарные кавычки. Имя файла должно быть заключено в квадратные скобки. В пути должен быть указан ваш рабочий диск и ваш личный каталог.
Размножьте формулу на последующие 5 ячеек столбца А и соответствующие ячейки столбца В используя операцию автозаполнение. В ведомости заполнятся колонки № и Фамилия учащегося.
В ячейке СЗ сформируйте формулу:
='D:\STUDENT \ ВАШ_КАТАЛОГ\[tab 6.xls] литеpaтуpa'!L3
Размножьте формулу на последующие 4 ячейки столбца. Столбец заполнился оценками по литературе, т. е. связь установлена.
Самостоятельно заполните ведомость оценок за 1-ю четверть по алгебре и геометрии. Раскройте еще два окна и разместите в них листы Алгебра и Геометрия, упорядочите окна мозаикой. Экран примет вид, представленный на рисунке 5.4.
Сохраните таблицу на диске в личном каталоге под именем tab5_1.xlsx
81
Рисунок 5.4 – Упорядочение окон каскадом
Сводная таблица
Сводная таблица представляет собой интерактивную таблицу, которую можно использовать для анализа данных уже существующих баз данных или таблиц. В случае внесения изменений в исходные данные, использованные для построения сводной таблицы, можно обновить ее. После обновления сводная таблица остается на том же листе, на котором она была создана.
При создании сводной таблицы предлагается последовательность шагов, позволяющих сформировать макет сводной таблицы. Сводная таблица создается на основе исходных данных, находящихся:
в списке, таблице или базе данных Excel;
во внешней базе данных;
в интервалах консолидации Excel;
в другой сводной таблице этой же рабочей книги. После создания сводной таблицы ее можно изменять, ана-
лизировать представленные в ней данные, перемещая ее поля при помощи мыши. Можно также включать в сводную таблицу итоги,
82
изменять суммирующую функцию для некоторого ее поля, а также выбирать для нее пользовательские вычисления.
Для создания сводной таблицы необходимо:
1)выполнить Вставка/Таблицы/Сводная таблица. Появится окно диалога Создание сводной таблицы, рисунок 5.5. В
этом окне диалога надо определить источник данных, Вы-
брать таблицу или диапазон. В поле Таблица или диапа-
зон, указать адрес анализируемых данных;
2)указать, где будет располагаться отчет по сводной таблице, на текущем или новом листе. Если данные располагаются на существующем листе, то в поле Диапазон указать адрес ячейки, начиная с которой будет располагаться сводная таблица;
Рисунок 5.5 – Окно создания сводной таблицы
3)после нажатия на кнопку Ок. Появится окно Список полей сводной таблицы и макет формирования сводной таблицы,
рисунок 5.7. С помощью окна Список полей сводной таб-
лицы создётся структура сводной таблицы, для этого перемещают поля сначала в область фильтра, затем
83
названия столбцов, названия строк и в последнюю очередь в область значений. С помощью получившегося макета можно анализировать результаты.
Рисунок 5.7 – Макет формирования сводной таблицы
Упражнение 5.4. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15. Создайте сводную таблицу по количеству проданного товара каждого наименования.
Создайте новую Книгу.
Откройте лист Товар файла tab4.xlsх (смотрите лабораторную работу 4).
Скопируйте лист Товар на любой лист новой книги.
Сохраните новый файл как tab5_2.xlsх.
Установите маркер на произвольной ячейке таблицы продаж товаров.
Выполните команду Вставка/Таблицы/Сводная таб-
лица.
84
В окне диалога Создание сводной таблицы выберите Выбрать таблицу или диапазон. В поле Таблица или диапазон,
укажите адреса ячеек, содержащих данные базы данных Товар. Укажите, где будет располагаться отчет по сводной таблице (рекомендуется на текущем листе). Если данные располагаются на существующем листе, то в поле Диапазон укажите адрес ячейки, начиная с которой будет располагаться сводная таблица. Нажмите кнопку Ок.
Сформируйте структуру сводной с помощью окна Список полей сводной таблицы. Для того переместите поле Наименование товара в область Названия строк, а поле Продано — в
область Значения, рисунок 5.8.
Рисунок 5.8 – Результат создания сводной таблицы
На экране появится созданная сводная таблица. Вместе с ней выводится вкладка Параметры Работа со сводными табли-
цами. Обратите внимание на команды группы Действия данной вкладки – Очистить, Выбрать, Переместить. Самостоятельно ознакомьтесь с назначением этих команд.
Назовите лист Товар.
85
Упражнение 5.5. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15. Создать сводную таблицу позволяющую определить, какое количество товаров каждого наименования приобрел каждый покупатель.
Перейдите на лист Товар.
Установите маркер на произвольной ячейке таблицы продаж товаров.
Выполните команду Вставка/Таблицы/Сводная таб-
лица.
Дойдите до окна создания макета сводной таблицы, пере-
местите поле Наименование товара в область Названия строк, поле Код покупателя — в область Название столбцов, а поле Продано — в область Значения.
В результате получится таблица, изображенная на рисунке
5.9.
Рисунок 5.9 - Результат создания сводной таблицы
Упражнение 5.6. На основе таблицы Товар, лабораторная работа 4, рисунок 4.15.Создайте сводную таблицу позволяющую сформировать сводную таблицу о продажах по указанным датам.
Перейдите на лист Товар.
86
Установите маркер на произвольной ячейке таблицы продаж товаров.
Выполните команду Вставка/Таблицы/Сводная таб-
лица.
Дойдите до окна создания макета сводной таблицы, переместите поле Дата продажи в область Фильтр отчета, поле
Наименование товара в область Названия строк, поле Код покупателя — в область Название столбцов, а поле Продано — в область Значения.
В области фильтра Дата продажи с помощью раскрывающегося списка выберите любую дату (создание отчета по продажам по этой дате), рисунок 5.10. Как изменилась сводная таблица?
Рисунок 5.10 – Работа с фильтром в сводных таблицах
Если в области строк или области столбцов присутствуют даты, их обычно группируют по отчетным периодам, например по неделям, месяцам или кварталам. Для этого необходимо выделить ячейку сводной таблицы, содержащую дату, выполнить
87
вкладка Параметры Работа со сводными таблицами / Группи-
ровать/ Группировка по полю. Появляется диалоговое окно
Группирование, рисунок 5.11.
Рисунок 5.11 – Группировка по периодам
В данном окне нужно указать диапазон дат, для которых применяется группировка (по умолчанию учитываются все записи исходной базы данных), и период группировки (по умолчанию месяцы). При изменении периода необходимо учитывать, что если указано более одного периода группировки (например, месяцы и кварталы), то Excel произведет вложенное сведение данных (сначала по месяцам, потом более крупно - по кварталам. Отказаться от группировки можно вкладка Параметры Работа со сводными таблицами / Группировать/ Разгруппировать.
Совместное использование программ Word и
Excel
Word - мощная многофункциональная система обработки текстов. С помощью Word можно напечатать приказ, оформить
88
служебную записку, подготовить научно-технический отчет, письмо или любой другой документ, содержащий диаграммы, таблицы, рисунки и т.д. Принятие решений сегодня невозможно без точной и своевременной информации, представленной в удобной для анализа и обработки форме. Это требует применения других приложений, входящих в состав пакета Microsoft Office, например Microsoft Excel для выполнения сложных вычислений, различных видов анализа и создания диаграмм. Использование нескольких приложений необходимо также для создания сложных составных документов, содержащих данные из различных источников.
Примером составного документа может служить подготовленный в Word бухгалтерский отчет, который должен содержать данные о продажах на сегодняшний день. Эти данные находятся в электронной таблице Microsoft Excel. В нее постоянно заносятся актуальные данные, поэтому в отчете должны учитываться все изменения. С этой целью следует создать составной документ Word и включить в него соответствующий раздел электронной таблицы Excel, образовав связи между таблицей и отчетом. В результате при каждом изменении электронной таблицы будет автоматически изменяться отчет. С помощью средств создания сводных таблиц, предоставляемых Excel, пользователь может сгруппировать необходимые данные в соответствии со своими требованиями.
Связывание и внедрение объектов
Основное отличие между связыванием и внедрением обусловлено местом хранения информации. Внедрение означает включение данных электронной таблицы, диаграммы и т.д. в документ другого приложения, например Word. Будучи внедренным, объект становится частью документа Word. Связанные же объекты хранятся в исходном файле, а документ Word содержит только ссылку на местоположение исходного файла.
1 Связывание объектов
89
Чтобы создать связь между документом Word и сводной таблицей Excel, необходимо предварительно запустить оба приложения.
Упражнение 5.7. На основе таблицы созданной в Microsoft Excel, рисунок 5.12, создать в Microsoft Word отчет об объеме продаж.
Рисунок 5.12 – Сводная таблица
Запустите Microsoft Word.
Оформите заголовочную часть отчета, рисунок 5.13.
Рисунок 5.13 – Заголовочная часть отчета
Перейдите в Microsoft Excel и создайте таблицу, рису-
нок 5.12.
90