Создание и заполнение таблиц данными. Ввод формул

Связывание таблиц

Формирование рабочей книги.

o создайте рабочую книгу;

o добавьте в рабочую книгу еще два листа;

o переименуйте рабочие листы: Лист1 – Закупка, Лист2 – Реализация, Лист3 – Цена, Лист4 – Выручка, Лист5 – Доход.

Создание и заполнение таблиц данными. Ввод формул.

o на рабочем листе "Закупка" создайте таблицу и внесите данные, как показано на рис. 1.

Рис. 1. Общий вид исходной таблицы на рабочем листе Закупка.

o на рабочем листе "Реализация" внесите исходные данные в таблицу и оформите, как показано на рисунке 2.

Рис. 2. Общий вид исходной таблицы на рабочем листе Реализация.

o на рабочем листе "Цена" создайте и заполните две таблицы – Расходы на закупку и Расчет цен как показано на рисунке 3.

Рис. 3. Общий вид рабочего листа Цена.

o создайте и заполните таблицы на листе Выручка,как показано на рисунке 4.

Рис. 4. Общий вид рабочего листа Выручка.

o заполните и оформите таблицы на листе Доход(рис. 5).

Рис. 5. Общий вид рабочего листа Доход.

Однако полученные таблицы представляют собой разрозненные источники данных и не взаимосвязаны между собой, что не позволяет использовать их как единую базу данных для получения различных отчетов. Убедитесь в этом на простых примерах:

1. Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – Софа. Проверьте, произошло изменение наименования данного товара на остальных листах или оно осталось прежним?

2. Измените на листе Реализация количество проданных стульев в феврале с 18 на 50. Проверьте, произошли соответствующие изменения на других рабочих листах?

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

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

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

Возникает необходимость установить связь между таблицами, расположенными как на одном рабочем листе, так и на разных рабочих листах.

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

Изменение содержимого ячейки на одном листе или таблице (источнике) рабочей книги приводит к изменению связанных с ней ячеек в листах или таблицах (приемниках). Этот принцип отличает связывание листов от простого копирования содержимого ячеек из одного листа в другой.

В зависимости от техники исполнения связывание бывает “прямым“ и через команду СПЕЦИАЛЬНАЯ ВСТАВКА.

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

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

Термин “прямое” связывание обозначает, что пользователь сам непосредственно при вводе формулы указывает имя листа и абсолютный адрес ячейки, разделенные восклицательным знаком "!".

Примеры формул: = C5*Лист1! A4

= Лист3! В2*100%

= Лист1! A1- Лист2! A1

Примечание:

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

2 способ – Связывание ячеек через команду "Специальная вставка"

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

Чтобы отразить в ячейке С4 на листе Ценазначение ячейки Н4 на исходном листе Закупка, нужно поместить курсор на ячейку Н4 исходного листа и выполнить команду Правка–Копировать. На листе Цена поставить курсор на ячейку С4, которую необходимо связать с исходной, и выполнить команду Правка–Специальная вставка–Вставить связь (см рис. 8). Тогда на листе Цена появится указание на ячейку исходного листаЗакупка, например: = Закупка!$Н$4

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

3. Связывание данных

o свяжите ячейки С4, С5, С6, С7, С8 в таблице Расходы на закупку на листе Цена с соответствующими ячейками на листе Закупка, используя различные способысвязывания ячеек (рис. 6).

Рис. 6 Связывание ячеек различных рабочих листов

o Связывание данных на листе Цена:

· на листе Ценав таблице Расходы на закупку ячейки А4:А8 связаны с ячейками таблицы Количество закупленной продукции на листе Закупка;

· ячейки В4:В8 являются исходными, т.к. содержат первоначальные сведения о ценах закупленного товара;

· ячейки С4:С8 связаны с ячейками Н4:Н8 на листе Закупка;

· ячейки D4:D8 содержат формулы подсчета затраченных средств на приобретенный товар и ссылаются на ячейки собственной таблицы (например, формула в ячейке D4 имеет вид =В4*С4, что означает умножение цены товара на его количество);

· ячейка D9 является суммой ячеек D4:D8;

· во второй таблице Расчет цен на этом же листе ячейки А14:А18 связаны аналогично п.1;

· ячейки В14:В18 являются связанными с исходными ячейками текущего листа В4:В8;

· ячейки С4:С8 являются исходными, т.к. содержат первоначальные сведения о наценке салона на закупленный товар;

· ячейки D14:D18 содержат формулы расчета цены продажи товара и ссылаются на ячейки собственной таблицы (например, формула в ячейке D14 имеет вид =В14*С14+В14, что означает умножение закупочной цены на установленный процент наценки, что дает сумму наценки, которую надо прибавить к закупочной цене);

После выполнения всех операций с этими таблицами произведите проверку их "работоспособности":

- Измените наименование товара – Диван в ячейке А4 на листе Закупка на другое – например Софа.

- Измените количество закупленного товара Софа в июне (в ячейке G4 на листе Закупка введите число 11).

- Измените цену закупки Софы в ячейке В4 на листе Цена на другую – 2500,00 р.

- Измените процент наценки Софы в ячейке С14 на листе Ценас 50% на 32%.

- Проверьте, произошли изменения в связанных таблицах или нет?

o Выполните связывание ячеек остальных таблиц рабочей книги, используя различные способы.