Задание № 10. Цель: Научиться создавать связи между таблицами.

Цель: Научиться создавать связи между таблицами.

1. Создать три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.П.10.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием " Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.
1.1.При создании таблиц организовать связь между таблицами " Прайс-лист(Январь)" и таблицами " Прайс-лист (Февраль)" и " Прайс-лист (Март)", для чего скопировать диапазон ячеек А3: В13 январской таблицы цен в буфер, перейти в таблицу " Прайс-лист (Февраль)" и воспользоваться режимом " Правка" -" Специальная вставка-Вставить ссылку". Аналогично установить связь с таблицей " Прайс-лист(Март)".

Рис.П.10.1

1.2.Переменную часть таблиц (столбец " Цена") отредактировать согласно данным, приведенным на рис.П.10.1. Переименовать листы, дав им соответствующие имена (Январь, Февраль, Март).
1.3.Просмотреть, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотреть, как изменится соответствующая ячейка в февральской таблице. Попытаться изменить текст в ячейке А7 февральской таблицы, просмотреть сообщения и сделать выводы о направленности установленной связи.

2. Создать таблицы " Отгрузка (Январь)", " Отгрузка (Февраль)" и " Отгрузка (Март)" по образцу, приведенному на рис.П.10.2, пользуясь режимом группового заполнения, и дать листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.
2.1.В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу " Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.П.10.2 в верхней части.
2.2.Скопировать формулу в ячейки D5: D13.
2.3.Записать в ячейку D14 формулу, выполняющую суммирование по столбцу " Итого" (ячейки D4: D13).
2.4.Активизировать инструментальную панель " Зависимости", пользуясь меню " Вид" -" Панели инструментов". Отобразить и просмотреть влияющие ячейки для ячейки D14.
2.5.Установить курсор в ячейку D4 и отобразить влияющие ячейки. Пронаблюдать, как отображается зависимость от внешней таблицы " Прайс_лист (Январь)", связанной с таблицей " Отгрузка(Январь)". Обратить внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.
2.6.Сохранить созданную книгу с шестью листами под именем Имя_10_1.
2.7.Сохранить копию книги под именем Имя_10_2.
2.8.Удалить из книги Имя_10_1 листы " Отгр_ЯНВ", " Отгр_ФЕВ" и " Отгр_МАР", сохранив в ней только прайс_листы.

Рис.П.10.2

3. Оставить открытыми обе книги. Заполнить таблицу " Отгрузка(Февраль)" книги Имя_10_2, пользуясь " Прайс_листом(Февраль)" книги Имя_10_1.
3.1.В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу " Прайс_лист (Февраль)". Эта формула приведена в строке формул, показанной на рис.П.10.3, а в верхней части.
3.2.Скопировать формулу в ячейки D5: D13.

4. Закрыть книгу Имя_10_1.Заполнить таблицу " Отгрузка(Март)" книги Имя_10_2, пользуясь " Прайс_листом(Март)" книги Имя_10_1.
4.1.В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу " Прайс_лист(Март)". Эта формула приведена в строке формул, показанной на рис.П.10.3, б в верхней части.

А) б) Рис.П.10.3

4.2.Скопировать формулу в ячейки D5: D13.
4.3.Записать в ячейку D14 формулу, выполняющую суммирование по столбцу " Итого" (ячейки D4: D13).

5. Создать новую таблицу " Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации " трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.П.10.4. В создаваемой таблице записать две формулы для получения одного и того же значения, но в одной из них записать формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую " объемную" формулу. Примеры записи таких формул приведены на рис.П.10.4 непосредственно под ячейками В4, В7 и выделены курсивом.

Рис.П.10.4

6. Задание для самостоятельного выполнения.
6.1.Решить рассмотренную в п. 4 задачу, увеличив ее размерность до полугода и используя для изменяющихся цен одну таблицу " Полугодовой Прайс-лист", содержащую все изменения цен в столбцах одной таблицы. Пример такой таблицы приведен на рис.П.10.5.

Рис.П.10.5

6.2.Создать шесть таблиц " Отгрузка(Месяц)".
6.3.Определить все необходимые связи и получить результат - таблицу, в которой будут представлены как суммарный (полугодовой) итог, так и итоги по двум кварталам.
6.4.Проверить, как будет работать созданная система, если таблица " Полугодовой Прайс-лист" будет располагаться в отдельной книге и эта книга будет закрыта.

7. Предъявить результаты преподавателю.