Вопрос 7. Технология связывания таблиц

Связанными называются таблицы, в которых данные из одних таблиц используются в других таблицах или участвуют в вычислениях, производимых в других таблицах. Связь устанавливается в результате задания внешней ссылки на другие листы той же или другой рабочей книги. Связь играет большую роль в тех случаях, когда значения одной таблицы зависят от значений в другой таблице. При изменении значений в одной таблице изменяются и значения в тех ячейках, которые содержат ссылки на эту таблицу, т.е. речь идет о динамической связи. Внешняя ссылка на ячейку, находящуюся на другом листе этой же рабочей книги состоит из имени листа и адреса ячейки, разделенных восклицательным знаком:

Имя_листа!Адрес_ячейки

Например, Товары!В5. Ссылка на ячейку, находящуюся в другой книге имеет вид:

[Имя_книги]Имя_листа!Адрес_ячейки

Рассмотрим порядок подготовки ведомости реализации товаров, в которой, используя данные таблиц на листах Товары и Цены, необходимо вычислить:

· сумму реализации каждого товара;

· сумму остатка каждого товара;

· общие суммы реализации и остатка материалов.

1. Откройте рабочую книгу УЧЕТ.XLS.

2. Перейдите на новый лист и дайте ему имя Реализация.

Примечание. Вставить новый лист можно командой Вставка►Лист.

3. Подготовьте таблицу, представленную на рис. 5.24:

· выделите ячейки А1:С9 и установите для них размер шрифта – 12 пт;

· объедините ячейки А1:С1 и введите заголовок таблицы Ведомость реализации;

· Оформите шапку таблицы. Для этого выделите ячейки А3:С3 и установите для них в окне Формат ячеек на вкладке Выравнивание флажок Переносить по словам. В ячейки А3, В3 и С3 введите соответствующие заголовки. Если необходимо, увеличьте ширину столбцов вручную.

4. Заполните столбец Код материала, используя значения соответствующих ячеек листа Товары,установив ссылки на ячейки B4:B8 листа Товары. Для этого выполните действия:

· на листе Реализация активизируйте ячейку А4 и введите знак "=" для определения формулы;

· перейдите на лист Товары. Ярлык листа Реализация выделится подсветкой, а в строке формул появится ссылка на лист-источник в следующем виде: =Товары!

· выполните щелчок левой клавишей мыши на ячейке В4 (в строке формул появится ссылка на лист-источник в следующем виде: =Товары!B4)

· нажмите клавишу [Enter], не переходя на лист Реализация;

· в результате на листе Реализация в ячейке А4 отобразится код первого товара – 101;

· скопируйте формулу из ячейки А4 в ячейки А5:А8.

Примечание. Если нужная информация находится в другой книге (например в книге с именем Отчет, на листе Ведомость, в ячейке В4), то необходимо перейти в нее с помощью команды меню Окно (книга Отчет должна быть заранее открыта),затем перейти на лист Ведомость и выделить ячейку В5. В строке формул появится ссылка вида: =[Отчет.xls]Ведомость!В4

5. Рассчитайте суммы реализации товаров путем перемножения значений столбца Расход (лист Товары) на значения столбца Цена розничная (лист Цены). Введите формулы для вычисления сумм реализации товаров. Для этого выполните следующую последовательность действий:

· на листе Реализация, активизируйте ячейку В4 и введите знак "=";

· перейдите на лист Товары, выполните щелчок на ячейке Е4 и введите с клавиатуры знак умножения «*»;

· перейдите на лист Цены, щелкните на ячейке D4; нажмите [Enter];

· В результате на листе Реализация в ячейке В4 появится значение, рассчитанное по формуле:

=Товары!E4*Цены!D4

· скопируйте формулу из ячейки В4 в ячейки В5:В8.

6. Рассчитайте суммы остатка товаров. Для этого выполните следующую последовательность действий:

· на листе Реализация активизируйте ячейку С4 и введите знак "=";

· перейдите на лист Товары, выполните щелчок на ячейке F4 и введите с клавиатуры знак умножения «*»;

· перейдите на лист Цены, щелкните на ячейке D4; нажмите [Enter];

· В ячейке С4 листа Реализация появится значение, рассчитанное по формуле:

=Цены!D4*Товары!F4

· скопируйте формулу из ячейки С4 в ячейки С5:С8.

7. Рассчитайте итоговые значения столбцов и поместите результаты в ячейки В9 и С9.

8. Перейдите в режим формул данной таблицы (Сервис►Параметры►вкладкаВид►установитефлажокФормулы). Сравните результат с таблицей, представленной на рис. 5.25.

Рис.5.25. таблица Ведомость реализации в режиме формул