Лабораторная работа №5 Связывание таблиц
Создать три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.6. Для каждого месяца первого квартала на отдельном листе книги Связывание_таблиц создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.
Рис.6
При создании таблиц организовать связь между таблицами "Прайс-лист(Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопировать диапазон ячеек А3:В13 январской таблицы цен в буфер, перейти в таблицу "Прайс-лист (Февраль)" и воспользоваться режимом "Правка"-"Специальная вставка - Вставить ссылку". Аналогично установить связь с таблицей "Прайс-лист(Март)".
Переменную часть таблиц (столбец "Цена") отредактировать согласно данным, приведенным на рис.6. Переименовать листы, дав им соответствующие имена (Январь, Февраль, Март).
Просмотреть, как выглядят ссылки в строке формул при активизации связанных ячеек в таблицах февраля и марта. Изменив содержимое ячейки А7 в январской таблице, просмотреть, как изменится соответствующая ячейка в февральской таблице. Попытаться изменить текст в ячейке А7 февральской таблицы, просмотреть сообщения и сделать выводы о направленности установленной связи.
Создать таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.7, пользуясь режимом группового заполнения, и дать листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.
Рис.7
В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". =С4*Январь!С4
Скопировать формулу в ячейки D5:D13.
Записать в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
Активизировать инструментальную панель "Зависимости", пользуясь меню "Вид"-"Панели инструментов". Отобразить и просмотреть влияющие ячейки для ячейки D14.
Установить курсор в ячейку D4 и отобразить влияющие ячейки. Пронаблюдать, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратить внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.
Сохранить созданную книгу с шестью листами под именем Связывание_таблиц1.
Сохранить копию книги под именем Связывание_таблиц2.
Удалить из книги Связывание_таблиц1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы, а из книги Связывание_таблиц2 удалить Прайс-листы.
Оставить открытыми обе книги. Заполнить таблицу "Отгрузка(Январь)" книги Связывание_таблиц2, пользуясь "Прайс_листом (Январь)" книги Связывание_таблиц1.
В ячейке D4 записать формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)" книги Связывание_таблиц1. При конструировании формулы для указания ссылки на адрес в другой книге использовать курсор мыши. Обратить внимание на то, как изменилась формула.
Скопировать формулу в ячейки D5:D13.
Аналогичным образом отредактировать листы отгрузок "Отгр_ФЕВ" и "Отгр_МАР".
Сохранить обе книги.
Просмотреть связи между закрытыми книгами.
Закрыть книгу Связывание_таблиц1. Просмотреть, как изменилась запись формул в ячейках D4:D13 книги Связывание_таблиц2.
Закрыть книгу Связывание_таблиц2, открыть книгу Связывание_таблиц1 и отредактировать значения Цены в Прайс-листах. Сохранить и закрыть книгу Связывание_таблиц1.
Открыть книгу Связывание_таблиц2 и проанализировать, как обновляются связи между книгами. На каждом листе “Отгрузки” записать в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).
Создать новую таблицу "Суммарный доход за три месяца" (см. рис.8)
Рис.8
В таблицу свести итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.8.
В создаваемой таблице записать две формулы для получения одного и того же значения, но в одной из них записать формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.8 непосредственно под ячейками В4, В7 и выделены курсивом.
Произвести изменения исходных данных в книгах Связывание_таблиц1 и Связывание_таблиц2, проанализировать пересчет итоговых результатов.