Контрольные вопросы
Формулы. Общие сведения. Вкладка Формулы.
Создание формул.
Функции. Способы ввода функций. Примеры.
Виды операторов. Примеры операторов.
Копирование и перемещение формул.
Ошибки в формулах.
Трассировка связей между формулами и ячейками.
Лабораторная работа № 4 Ссылки в ms Excel Методические рекомендации
Ссылки являются одним из основных инструментов, с помощью которого осуществляется обмен информацией между различными структурными единицами MS Excel.
Ячейка однозначно идентифицируется своим адресом. Адрес ячейки состоит из имени столбца (А,В,С и т.д.) и номера строки (1,2,3 и т.д.), например, D1, F15 и т.д.
Ссылки позволяют использовать в формулах содержимое других ячеек, расположенных:
в том же листе, что и формула;
в различных листах, относящихся к одной рабочей книге;
в листе, расположенном в другой рабочей книге.
В общем случае ссылки можно разделить на пять видов.
Ссылки на ячейки текущего рабочего листа
Пример: =А1 =В5+С2
Ссылки на другие листы той же книги
Пример: =Лист2!А3 =Экзамен!D5 =‘32эи’!H3
Имя листа, начинающегося с числа, заключается в апострофы. Эти ссылки можно набирать вручную (использовать только английский регистр) или вводить с помощью мыши (щелкая по другому листу или ячейке левой кнопкой мыши).
Ссылки на листы других книг
Возможны 2 случая:
а) если две книги открыты и ссылка создается путем выделения ячейки
Пример: =[Имя книги.xls]Лист5!$A$3 т.е. по умолчанию создается абсолютная ссылка.
б) если книга, на которую указывает ссылка закрыта, то в ссылке необходимо указать полный путь доступа к папке, где хранится книга
Пример: =‘C:\Имя папки\[Имя книги.xls]Лист5’!$A$3.
Объемные ссылки
Объемные (трехмерные) ссылки – это ссылки на ячейки диапазона листов в книге.
Пример:
Создано 100 листов в книге. Необходимо определить сумму значений из диапазона (В1:В15), расположенного во всех ста листах.
=СУММ(Лист1:Лист100!В1:В15) или =СУММ(‘29 эи:32 эи’!В1:В15).
При работе с объемными ссылками можно использовать до 11 функций, например: СУММ, СРЗНАЧ, МАКС, МИН.
Циклические ссылки
Циклическая ссылка – это ссылка, которая зависит от своего собственного значения.
Пример:
В ячейку А1 введена формула =В5+С3·А1.
MS Excel выдаст сообщение об ошибке, которую необходимо устранить.
Абсолютная, относительная и смешанная адресация.
По умолчанию ссылки на ячейки в формулах относительные, то есть адрес ячейки определяется на основе расположения этой ячейки относительно ячейки с формулой. При копировании ячейки с формулой относительная ссылка автоматически изменяется. Именно возможность использования относительных ссылок и позволяет копировать формулы. Абсолютные, относительные и смешанные ссылки являются частным случаем рассмотренных выше пяти видов ссылок (таблица 10).
Таблица 10 – Типы ссылок
Тип ссылки |
Описание |
Пример |
Абсолютная |
Использует для указания на ячейку ее фиксированное положение на листе. В абсолютной ссылке перед именем столбца и номером строки ставится знак $. |
=$A$5 |
Относительная |
Указывает на ячейку, основываясь на ее положении относительно ячейки, в которой находится формула, например, на две строки выше. |
=А1 |
Смешанная |
Содержит относительную и абсолютную ссылки. В смешанной ссылке знак $ ставится перед абсолютной частью. Смешанные ссылки используются, чтобы при копировании зафиксировать только часть ссылки на ячейку. |
=$A1 |
На рис. 16 показан результат копирования формулы, содержащей абсолютную (а), относительную (б) или смешанную (в, г) ссылки. Исходная формула расположена в ячейке C2 и ссылается на ячейку А1. Затем формула в ячейке C2 копируется в ячейку D5. Серым цветом показаны ячейки на которые ссылается исходная формула (в ячейке C2) и формула полученная после копирования (в ячейке D5).
Рис. 16. Результат копирования формулы