Урок 18
Ссылки в формулах
Теоретическая часть
п.1 Виды ссылок
Cсылка - адрес ячейки, содержимое которой используется в вычислениях.
В большинстве формул Excel используются ссылки на ячейки. Это позволяет автоматически пересчитывать формулы в случае, если значения в этих ячейках меняются. Для записи в одной ячейке, тип используемой ссылки не имеет значения. Но если нужно копировать формулу в другое место, использование разных ссылок даёт разные результаты. В Excel есть три вида ссылок:
Относительная ссылка в Excel
Относительная ссылка — это ссылка вида A1 (т.е. буква столбца + номер строки).
Основная особенность таких ссылок — при протягивании или копировании формулы в другие ячейки ссылка смещается. Другими словами, при копировании вниз ссылка A1 превратится в A2, A3 и т.д., при копировании вправо — в B1, C1 и т.д.:
В данном примере копируется ячейка D2 с формулой A2*B2.
При перемещении формулы вниз получаем A2 -> A3 -> A4 -> A5, B2 -> B3 -> B4 -> B5.
Смешанная ссылка в Excel
Смешанная ссылка — это ссылка вида $A1 или A$1.
Знак доллара ($) служит фиксированием столбца или строки. Иными словами, если мы поставим $ перед буквой столбца (например, $B5), то ссылка не будет изменяться по столбцам, но будет изменяться по строкам (при протягивании формула сместится на $B5, $B6, $B7 и т.д.). Аналогично, если знак $ поставить перед номером строки (например, B$5), то ссылка не будет изменяться по строкам, но будет изменяться по столбцам (при перемещении формула сдвинется на C$5, D$5, E$5 и т.д.).
В данном примере любая формула таблицы является произведением значений из столбца В и строки 3.
Абсолютная ссылка в Excel
Абсолютная ссылка — это ссылка вида $A$1.
Её особенность в том, что она не изменяется при копировании или протягивании формулы в другие ячейки. В данном случае знак $ ставится как перед буквой столбца, так и перед номером строки, т.е. формула полностью фиксируется. Абсолютная ссылка часто применяется, когда необходимо умножить или разделить диапазон ячеек на одно и тоже число. Например, перевести данные в рубли по определенному курсу, или перевести данные в тысячи/миллионы/миллиарды:
В данном примере любая формула таблицы является произведением значений из столбца А и ячейки В1.
п.2 Как изменить тип ссылки в Excel
Вручную – дважды кликните на ячейке со ссылкой для редактирования содержимого. Проставьте «$» перед теми координатами, которые нужно «заморозить» и нажмите Enter.
Автоматическим перебором — установите курсор на ссылке и нажимайте F4, пока не получите нужный вид ссылки. Каждое нажатие клавиши устанавливает в данной ссылке новый тип ссылки. Нажатие клавиши циклически изменяет варианты ссылок по кругу: Относительная — Абсолютная — Изменяются столбцы — Изменяются строки — Относительная… Например: B5 -> $B$5 -> B$5 ->$B5 -> B5 и т.д.
п. 3.Внешние ссылки в Excel
Ссылки могут указывать на ячейки на том же листе, на другом листе той же книги, на листе другой книги. Эти ссылки имеют свои особенности:
Ссылка на ячейку на том же листе по умолчанию ставится относительной и выглядит, как набор координат. Например: =А1
Ссылка на ячейку на другом листе активной книги, по умолчанию тоже относительная, но содержит имя листа, на котором расположена искомая ячейка. Имя листа и адрес ячейки разделяются восклицательным знаком. Например, =Лист1!А1.
Ссылка на ячейку в другом файле по умолчанию абсолютная и записывается, как комбинация: [Имя_рабочей_книги]Имя_листа!Адрес_ячейки. Например: =[Книга1.xlsx]Лист1!$А$1. И здесь нужно сделать несколько уточнений:
1. Если целевая рабочая книга закрыта, ссылка изменяет вид: Адрес_рабочей_книги[Имя_рабочей_книги]Имя_листа!Адрес_ячейки.
2. Если имя листа или книги содержит пробелы, ссылка заключается в одинарные кавычки по такой схеме: ’[Книга 1.xlsx]Лист 1’!$А$1.
Видео Ссылки в формулах
Практическая часть
Задание 1. Относительная ссылка. Создайте таблицу по образцу "Расходы на ремонт". Введите формулы для вычисления стоимости отдельных товаров и общей суммы расходов.Результат вышлите преподавателю.
Задания 2. Абсолютная ссылка. Создайте таблицу по образцу "Мусс из яблок". Введите формулы для определения расхода продуктов для приготовления блюда в зависимости от количества порций. Результат вышлите преподавателю.
