Лабораторно-практические работы MS Excel 2003 - табличный процессор Связанные таблицы в MS Excel 2003

• Связывать ячейки и таблицы одного рабочего листа.

• Связывать различные листы рабочей книги.

• Автоматизировать расчеты данных на примере работы мебельного салона.

Для правильной организации работы в электронных таблицах Excel 2003 сформируйте макет своей будущей рабочей книги, для этого продумайте состав рабочей книги (количество, имена и порядок расположения рабочих листов, их предполагаемое содержание, наличие графиков, диаграмм и т.п.).
При создании таблиц определите подчиненность их между собой, а также общий вид, наличие заголовков и других составляющих (наименование шапки, наличие нумерации граф и т.д.).
Данные делятся на две категории: исходные и расчетные.
Исходные данные вводятся непосредственно с клавиатуры, формируя исходную базу данных.
Расчетные являются результатом формульной зависимости связанных ячеек непосредственно с ячейками исходных (первоначальных) данных, либо с ячейками промежуточных расчетов, либо и тех и других.

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

! Для оформления общего заголовка таблицы воспользуйтесь кнопкой "Объединить и поместить в центре" , для размещения заголовков таблицы во всех столбцах следует выделить всю 3-ю строку, затем щелкнуть на ней правой кнопкой мыши, из контекстного меню выберите команду Формат ячеек, откройте закладку Выравнивание, установите флажок в поле Переносить по словам, выберите в полях По вертикали – По центру, По горизонтали – По центру, затем нажмите OK.

! Перед вводом данных задайте форматы ячеек таблицы – Числовой, число десятичных знаков 0, выравнивание – по центру. Наименование предметов – формат Текстовый. Выравнивание – по левому краю (отступ) – 1.

! В ячейках H4:Н8 – введите формулы суммирования по строкам (обратите внимание на предлагаемый диапазон суммируемых ячеек, если диапазон не захватывает все исходные данные – укажите мышью весь необходимый диапазон, затем нажмите Enter).

Сохраните промежуточные результаты своей работы. Для этого выберите команду Сохранить как... в меню Файл, затем в диалоговом окне "Сохранение документа" найдите и откройте свою папку, присвойте файлу имя – Расчет дохода и добавьте свою фамилию. Нажмите кнопку Сохранить.

Указание. В таблицах задайте форматы ячеек, выравнивание данных в них и обрамление ячеек, как показано на рисунке 4.
Обратите внимание, что в таблице Выручка от реализации за 1 квартал число, отражающее количество проданного товара, совпадает с количеством проданного товара на листе Реализация, но только за январь, февраль и март месяцы.

1. Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – Софа. Проверьте, произошло изменение наименования данного товара на остальных листах или оно осталось прежним?
2. Измените на листе Реализация количество проданных u1089 стульев в феврале с 18 на 50. Проверьте, произошли соответствующие изменения на других рабочих листах?
3. Измените на листе Цена в таблице Расходы на закупку в графе Цена закупки (ячейка В4) число 2 000,00 р. на новую цену закупки – 5 000,00 р. Проверьте, произошли соответствующие изменения в соседней таблице Расчет цен, и на других рабочих листах где используется данный показатель?

Таким образом, при изменении содержимого в исходной ячейке, не происходит никаких изменений в ячейках содержащих аналогичную информацию в других таблицах, как бы "логически" связанных между собой или как кажется на первый взгляд имеющих общий исходный компонент.
Изменения происходят только в тех ячейках, которые имеют прямую связь с исходной ячейкой например, при выполнении п.3 на листе Цена изменяется ячейка D4, т.к. содержит формулу со ссылкой на ячейку В4, и как следствие изменяется итоговая сумма в ячейке D9.
Возникает необходимость установить связь между таблицами, расположенными как на одном рабочем листе, так и на разных рабочих листах.

Если на одном рабочем листе используются данные из другого листа, то эти листы считаются связанными. С помощью связывания можно свести воедино значения ячеек из нескольких разных таблиц на одном рабочем листе.
Изменение содержимого ячейки на одном листе или таблице (источнике) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.
В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

1 способ – "Прямое связывание ячеек"

Прямое связывание листов используется непосредственно при вводе формулы в ячейку, когда в качестве одного из элементов формулы используется ссылка на ячейку другого листа. Например, если в ячейке таблицы В4 на рабочем Листе2 содержится формула, которая использует ссылку на ячейку А4 другого рабочего листа (например, Листа1) и оба листа загружены данными, то такое связывание листов называется “прямым”.
Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!".

Для указания ссылки на ячейки и листы, находящиеся в незагруженных (неоткрытых) рабочих книгах, в формуле нужно без пробелов задать полный путь местонахождения файла. Путь задается в одинарных кавычках, где указывается название диска, каталога (папки), имя рабочей книги (имя файла) в квадратных скобках и имя листа, на который идет ссылка.

Связывание через команду СПЕЦИАЛЬНАЯ ВСТАВКАпроизводится, если какая либо ячейка таблицы на одном рабочем листе должна содержать значение ячейки из другого рабочего листа.
Чтобы отразить в ячейке С4 на листе Цена значение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать. На листе Цена поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка– Вставить связь (см рис. 6). Тогда на листе Цена появится указание на ячейку исходного листа Закупка, например: = Закупка!$4
При таком связывании Excel 2003 автоматически использует абсолютный адрес на ячейку, т.к. относительный адрес обращения может привести к ошибкам, особенно если обращаться к незагруженным файлам (рабочим книгам).

1. Макарова Н.В. Программа по информатике (системно-информационная концепция). К комплекту учебников по информатике 5-11 класс. Санкт-Петербург: Питер.2000г.
2. Информатика. 5-11 класс. /Под ред. Н.В. Макаровой. – СПб.: Питер, 2001
3. Коляда М.Г. Окно в удивительный мир информатики. ИКФ «Сталкер»,1997
4. Шафрин Ю.А. Основы компьютерной технологии. Учебное пособие для 7 – 11 классов по курсу «Информатика и вычислительная техника» - Москва: ABF,1996
5. Ефимова О.В., Моисеева М.В., Ю.А. Шафрин Практикум по компьютерной технологии. Примеры и упражнения. Пособие по курсу «Информатика и вычислительная техника» - Москва: ABF,1997
6. Горячев А., Шафрин Ю. Практикум по информационным технологиям. М.: Лаборатория базовых знаний, 2001
7. Семакин И.Г., Шеина Т.Ю. Преподавание курса информатики в средней школе. М.: Лаборатория базовых знаний, 2002
8. Симонович С.В., Евсеев Г.А. Практическая информатика. Учебное пособие для средней школы. Универсальный курс. – Москва: АСТ-ПРЕСС: Информ-Пресс, 1998
9. Симонович С.В. Компьютер в вашей школе. М.: АСТ-ПРЕСС: Информком-Пресс, 2001
10. Симонович С.В., Евсеев Г.А. Занимательный компьютер. Книга для детей, учителей и родителей. Москва: АСТ-ПРЕСС: Информком-Пресс, 2002

Добавить комментарий