Лабораторная работа №3. Использование в формулах абсолютных и относительных ссылок. Связывание рабочих листов.

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

Адресация, допускающая автоматическую корректировку, называется относительной, не допускающая ее – абсолютной. Иногда корректировка имен столбцов и/или номеров строк некоторых ячеек недопустима. В этих случаях перед именем столбца и/или номером строки, не подлежащим корректировке, ставится знак $. Для переключения режимов абсолютной и относительной адресации можно использовать функциональную клавишу [F4].

По умолчанию, ссылки на ячейки в формулах рассматриваются как относительные адресации. При ее использовании в формулах MS Excel запоминает расположение относительно текущей ячейки.

Для смешанной адресации символ $ ставится только перед тем показателем, который не должен изменяться, например: В$4 или $С10. Тогда при копировании один параметр адреса изменится, а другой – нет.

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

Рабочие листы (рабочие книги) могут быть связаны с помощью ссылок на ячейки другого листа (книги). Если в формуле требуется указать адрес ячейки, расположенной на другом листе, поступают следующим образом: записывают имя этого листа, ставят восклицательный знак, а затем указывают адрес ячейки. Например, Таблица3!А5, где Таблица3 – имя листа, символ «!» – разделитель, А5 – адрес ячейки. Перед именем столбца или номером строки допускается использование символа $ для обеспечения абсолютной адресации. При переименовании листа все ссылки на него автоматически корректируются.

ЗАДАНИЕ 1.

1. Создайте документ MS Ехсеl. Переименуйте листы: Лист1 на «Площади», Лист2 на «Урожайность».

2. Создайте таблицу по образцу Таблица 7, не забудьте вписать в ячейку А1 название таблицы

3. Для названий столбцов таблицы установите многострочный формат: Формат–Ячейки, выберите вкладку Выравнивание, установите параметр «переносить по словам», выровняйте текст по центру

4. В ячейках последней строки должны содержаться формулы суммирования (используйте автосуммирование)

5. В ячейку F4 введите формулу =СРЗНАЧ(В4:Е4). Обратите внимание на то, что функцию СРЗНАЧ можно использовать из библиотеки Мастера функции MS Ехсеl, которая на панели инструментов обозначается f_x или ввести непосредственно с клавиатуры.

6. В ячейку G4 (для определения доли) введите формулу =F4/$F$10*100

7. Скопируйте таблицу на лист Урожайность. Для этого выделите всю таблицу и скопируйте в буфер обмена (Копировать). Перейдите на следующий лист и выполните команду вставки для ячейки А1 (Вставить)

8. Во вставленной таблице удалите данные, но не трогайте формулы в столбце «га».

9. Сделайте необходимые изменения: измените название таблицы, удалите третью строку, удалите строку Всего, удалите столбец «%»

10. В таблицу внесите значения урожайности по годам

11. Создайте копию листа Урожайность с помощью команды рабочего листа Переместить/ скопировать лист и дайте название листу «Валовой сбор»

12. В таблице сделайте необходимые изменения: название таблицы «Валовой сбор овощей, т» и введите расчетные формулы:

• Очистите значения клеток В4:Е9

• Используя переходы между листами, введите фор­мулы для вычисления валового сбора: значения показателей листа «Площади культуры» умножить на значения показателей листа «Урожайность». Для этого установите курсор в ячейке В4 листа «Валовой сбор», вставьте знак «=», перейдите в ячейку В4 листа «Площади», вставьте знак умножения «*», перейдите в ячейку В4 листа «Урожайность» и завершите действия нажатием на Enter.

• Протяните формулы на весь диапазон ячеек В4:Е9

13. Установите защиту для всех листов. Сохраните файл.

Таблица 7 – Посевные площади овощей, га

Культуры	Капал	Аксу	Текели	Копа	В среднем по хозяйству
					га	%
Капуста	100	90	80	65
Свекла	30	20	20	12
Морковь	15	15	15	12
Лук	15	10	7	5
Огурцы	10	8	7	5
Кабачки	3	2	1	1
Всего

Таблица 8 – Урожайность овощей, ц/га

Культуры	Капал	Аксу	Текели	Копа	В среднем по хозяйству
Капуста	118	162	216	107
Свекла	88	122	160	141
Морковь	131	180	240	166
Лук	84	97	96	125
Огурцы	82	103	92	39
Кабачки	88	91,5	72	37