Использование смешанных ссылок

ЛАБОРАТОРНАЯ РАБОТА №13

«Создание и оформление таблиц в Microsoft Excel 2016»

Цель работы: приобретение навыков использования функций, изучение возможностей визуального форматирования и связывания электронных таблиц Microsoft Excel 2016.

Использование смешанных ссылок

В Microsoft Excel 2013 существуют смешанные ссылки, в которых только одна часть адреса (относящаяся либо к столбцу, либо к ячейке) является абсолютной. В этом случае знак доллара ставится перед номером строки или буквой столбца, в зависимости от того, на что ссылка должна быть абсолютной. Такая ссылка необходима, например, в следующей ситуации.

Задание 1:

1. Создайте новую рабочую книгу Microsoft Excel 2016.

2. На Листе1 создайте таблицу табл. 1.

3. Отформатируйте таблицу согласно рисунку

Рис. 1

4. В ячейку С4 введите формулу: =C$3*$B4. Данная формула содержит две смешанные ссылки на ячейки. В ссылке С$3 координата столбца является относительной, а координата строки – абсолютной. В ссылке $B4, наоборот, координата столбца является абсолютной, координата строки – относительной.

5. Методом автозаполнения скопируйте эту формулу в ячейки столбца С, затем ячейки строки 4. Просмотрите, как изменились ссылки на ячейки.

Рис.2

6. Этим же способом заполните всю таблицу.

Применение ссылок на ячейки из других рабочих листов

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

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

Имя_Листа.Адрес_Ячейки

Другими словами, перед адресом ячейки нужно вставить имя рабочего листа с восклицательным знаком. Например, формула, в которой используется ссылка на ячейку из рабочего листа «Лист2», имеет вид:

=А2+Лист2.А1.

В этой формуле к значению, находящемуся в ячейке А2 текущего рабочего листа, прибавляется значение из ячейки А1, которая расположена в рабочем листе под названием «Лист2».

Примечание: Если имя рабочего листа в ссылке содержит один или несколько пробелов, то вы должны заключить это имя в одинарные кавычки. Например, =А2*’Все отделы’.А1.

Задание 2:

1. Перейдите на Лист2 исоздайте таблицу табл. 2.

2. Отформатируйте таблицу согласно рисунку

Рис. 3

3. Установите табличный курсор в ячейке D2 (оклад).

4. В строку формул введите знак равенства.

5. Переключитесь на лист «Лист1». Обратите внимание, что в строке формул появилась ссылка на этот лист.

Рис. 4

6. Щелкните указателем мыши на ячейке со значением оклада 11-разряда. В строке формул появилась ссылка на эту ячейку рис.5.

Рис.5

7. Не переключаясь на другие листы, нажмите клавишу «Enter».

8. Убедитесь, что на листе « Лист2 » в ячейке D2 появилось такое же значение, как и на листе « Лист1 ».

Рис. 6

9. Таким же образом введите значения окладов для остальных сотрудников.

10. Аналогично введите значения надбавок, учитывая стаж работы.

11. Рассчитайте суммы зарплат.

12. Отформатируйте таблицу согласно рисунку

Рис. 7

13. Сохраните рабочую книгу

Создание таблицы имен

Иногда возникает необходимость в создании списка всех имен, определенных в рабочей книге. Это может быть полезно при отслеживании ошибок или при документировании вашей работы.

Задание 5:

1. Установите курсор в ячейку Н2 – с этого места будет создаваться таблица имен.

2. Выполните команду Формула-Использовать в формуле-Вставить Имена-Вставить все.

3. Отформатируйте полученную таблицу рис. 15.

Рис. 15

Использование функций

Шаблоны сложных числовых форматов

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