Тема 3. Работа с несколькими взаимосвязанными таблицами

Цель работы: научиться связывать таблицы в Excel и выполнять консолидацию данных.

СОЗДАНИЕ СВЯЗАННЫХ ТАБЛИЦ

Во многих случаях исходные данные и результаты расчетов удобнее оформлять в виде не одной, а нескольких взаимосвязанных таблиц. Excel обеспечивает возможность совместной обработ­ки таких таблиц и их хранение в одном файле. Для этого каждая таблица размещается на отдельном рабочем листе. Можно связывать таблицы, расположенные на разных рабочих листах, таким образом, чтобы в результате ввода значения в одну таблицу изме­нялось содержимое другой.

Каждый рабочий лист имеет имя, допускает форматирование и адресацию данных, выделение ячеек независимо от других листов. В новой электронной таблице может содержаться несколько листов; один из них отображается в окне Excel в виде пустографки, от остальных видны только ярлычки с именами во второй снизу строке окна.

ВЫБОР, СОЗДАНИЕ, УДАЛЕНИЕ И ПЕРЕИМЕНОВАНИЕ РАБОЧЕГО ЛИСТА

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

Чтобы вставить новый лист, следует перейти на лист, слева от которого необходимо расположить новый, и выполнить команду Вставка— Лист.

Для удаления рабочего листа используется команда Правка—Удалить лист.

Примечание. Эта операция не может быть отменена посредством кнопки на панели инструментов Стандартная, поэтому ее выполнение требует особой осторожности.

Чтобы переименовать рабочий лист, следует дважды щелкнуть левой кнопкой мыши на его ярлычке и ввести новое имя листа.

Рекомендуется присваивать листам имена, состоящие только из русских и латинских букв (любого регистра), цифр и знаков подчеркивания.

АДРЕСАЦИЯ ЯЧЕЕК, РАСПОЛОЖЕННЫХ НА ДРУГОМ ЛИСТЕ

Если в формуле требуется указать адрес ячейки, расположенной на другом листе, поступают следующим образам: записывают имя этого листа, ставят восклицательный знак, а затем указывают адрес ячейки. Например, ТаблицаЗ!АЕ56, где ТаблицаЗ — имя листа, символ ! — разделитель, АЕ56 — адрес ячейки.

Перед именем столбца или номером строки допускается использование символа $ для обеспечения абсолютной адресации, например, ТаблицаЗ!АЕ$56. Перед именем листа знак $ не используется, адресация листов всегда является абсолютной. Excel никогда не корректирует имена листов при копировании формул.

Пример записи адреса интервала, расположенного на другом листе: ТаблицаЗ!$У50:$АЕ$56; охватывающего данные нескольких смежных листов: Таблица2:Таблица5!$В5:$В10.

При переименовании листа все ссылки на него автоматически корректируются. Удаление листа при вычислении формул, содержавших ссылки на него, приводит к сообщению об ошибке.

КОНСОЛИДАЦИЯ ДАННЫХ

Консолидация данных - средство, позволяющее объединять данные из областей-источников и выводить их в область назначения. При консолидации данных могут использоваться различные функции: суммирования, расчета среднего арифметического, определения максимального и минимального значений и т.п.

Для выполнения консолидации следует:

1. выделить область назначения;

2. задать Данные-Консолидация;

3. в диалоговом окне Консолидация в поле Ссылка: задать области-источники, которые нужно консолидировать¹;

4. в списке Функция: выбрать нужную функцию;

5. нажать кнопку ОК.

Консолидацию можно выполнять без создания связей с исходными данными или с созданием связей. Во втором случае Excel автоматически корректирует итоговые данные при изменении их в таблицах-источниках. Для установления связей следует включить переключатель Создавать связи с исходными данными.

КОНТРОЛЬНЫЕ ВОПРОСЫ

1. Что такое рабочий лист?

2. Как перейти с одного листа на другой?

3. Как создать новый лист?

4. Какие функции могут использоваться при консолидации данных?

5. Какие ограничения рекомендуется соблюдать при назначении имен листов?

6. Каковы правила адресации данных на другом листе?

7. Как адресуется интервал, охватывающий данные нескольких листов?

8. Как обеспечить абсолютную адресацию ячейки на другом листе?

ЗАДАНИЯ

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

2. Скопировать введенную на Лист1 таблицу на листы Лист2, Лист3.

3. На листе Лист2 заменить данные по выпуску продукции первым цехом на данные по выпуску продукции вторым цехом, на листе Лист3 данные по выпуску продукции третьим цехом (исходные данные приведены ниже, табл. 3.5). Отредактировать названия таблиц. 3.5. Исходные данные

Шифр продукции	Получено продукции, т
	1 кв.		2 кв.		3 кв.		4 кв.
	Цех 2	Цех 3	Цех 2	Цех 3	Цех 2	Цех 3	Цех 2	Цех 3
0101	450	0	810	0	290	0	500	0
0102	870	50	1260	70	590	20	780	80
0103	2050	400	1600	860	2200	760	2900	990
0201	400	780	460	330	550	320	790	890
0202	840	0	920	0	790	60	680	60
0203	620	90	580	110	120	80	590	80
0301	430	120	360	95	120	160	450	160
0302	150	145	120	130	140	260	130	185
0303	125	140	80	170	145	120	130	160

4. Переименовать листы, присвоив листу Лист 1 -имя Цех1, листу Лист2 - имя Цех2, листу Лист3- имя Цех3.

5. Создать новый лист Лист4. Скопировать исходную таблицу на лист Лист4. Рассчитать количество полученной продукции на предприятии в целом; для этого в формулах использовать ссылки на данные листов: Цех1, Цех2, Цех3. Название таблицы должно быть: "Производство продукции (в целом по предприятию)".

6. Переименовать лист Лист4, присвоив ему имя Предприятие.

7. Создать новый лист Лист5. Скопировать на Лист5 таблицу "Производство продукции (в целом по предприятию)".

8. Преобразовать таблицу, рассчитав стоимость продукции, полученной на предприятии по кварталам и всего за год. В формулах использовать ссылки на данные листа Предприятие. Ввести новое название таблицы: "Стоимость продукции (в целом по предприятию)". Изменить названия столбцов таблицы.

9. Переименовать лист Лист5, присвоив ему имя Выручка.

10. Создать новый лист Лист6. На листе Лист6 создать следующую таблицу (табл. 3.6).

3.6. Отчет о доходах и расходах предприятия, тыс. руб.

Показатель	1 кв.	2 кв.	3кв.	4 кв.	Всего за год
Валовая выручка
Налог на добавленную стоимость (НДС)
Выручка от реализации
Затраты на производство продукции - всего	3010	3005	2640	2950
в т.ч. заработная плата	1540	1645	1470	1760
Валовая прибыль
Налог на доход предприятия
Чистый доход

11. Название таблицы записать в ячейку А1 и отцентрировать относительно столбцов таблицы.

12. Названия столбцов таблицы ввести в ячейки A2:F2.

13. Названия строк таблицы ввести в ячейки A3:Al0 (Чтобы названия показателей уместились целиком каждое в отдельной ячейке, для интервала ячеек А3:А10 нужно включить перенос по словам).

14. Выполнить обрамление таблицы.

15. Заполнить ячейки В3:Е3, используя ссылки на данные листа Выручка.

16. Рассчитать значения ячеек В4:Е5 и В8:Е10.

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

    • Выручку от реализации определить как разность между валовой выручкой и НДС.

    • Валовую прибыль определить как разность между выручкой от реализации и затратами на производство продукции - всего.

    • Налог на доход предприятия определить как процент (22 %) от суммы валовой прибыли и заработной платы.

    • Чистый доход предприятия определить как разность между валовой прибылью и налогом на доход предприятия.

17. Рассчитать значения столбца "Всего за год".

18. Установить, как изменится чистый доход предприятия, если процент НДС:

а) увеличится с 20 до 30 %;

б) уменьшится с 20 до 10 %.

11. Переименовать лист Лист 6, присвоив ему имя Отчет.

12. Сохранить файл под тем же именем..