Сегодня мы изучим разные типы ссылок на ячейки в ваших формулах Excel. А если вы ещё не читали прошлую статью, о том как использовать мастер функций, то переходите по ссылке.
Когда мы ссылаемся в своей формуле на другую ячейку, то ссылка может быть двух типов: относительной или абсолютной. Это очень важная для понимания вещь, без которой у вас не получится в дальнейшем успешно использовать формулы. Давайте подробно разберёмся в каких случаях и какие мы будем использовать типы ссылок.
Относительные ссылки
Предположим у нас есть таблица с ценами на товары и мы решили устроить распродажу и уценить все товары на 25%. Для этого мы должны умножить цену на 0,75. Образец таблицы можете скачать по ссылке.
Начнем с первого товара. Для этого введем в ячейку C4 такую формулу:
=B4*E2
Эта формула умножила цену товара 1 (B4) на нужный нам коэффициент скидки (E2). Всё получилось как надо. Теперь нам необходимо уценить оставшиеся товары. Конечно, мы не будем вводить вручную формулы для каждого товара, ведь их может быть тысячи, для этого в Excel есть средства автоматизации. Для этого зажмите курсором нижний правый уголок ячейки с уже введённой формулой и протяните её вниз до конца таблицы с данными.
Получилось вот что:
Хм, явно в наши планы не входило раздавать товары бесплатно. Но почему же для первого товара всё сработало превосходно, а напротив остальных нули?
Дело в том, что формула в ячейке C4 использует относительные ссылки на ячейки. И когда эта формула протягивается вниз, то ссылки изменяются вслед за формулой.
Давайте выделим ячейку C5 и посмотрим, куда ссылается формула.
Получилось так, что формула на одну ячейку ниже ссылается также на ячейки на одну ниже соответственно. Хотя в первой формуле =B4*E2 мы ввели адреса ячеек, на самом деле она производила вычисления так: значение ячейки на один столбец левее умножить на значение ячейки двумя строками выше и двумя столбцами правее.
Чтобы быть уверенным, что вы точно разобрались в этом вопросе, можно привести аналогию. Предположим, вы идете по улице и случайный прохожий спрашивает вас, как пройти к библиотеке. Вы отвечаете ему, что надо пройти два дома прямо, а затем повернуть направо и пройти ещё один дом. Прохожий благодарит вас и находит по вашей инструкции библиотеку. Вы идёте по улице дальше, проходите один дом и другой прохожий снова спрашивает вас, как ему пройти к библиотеке. И вы снова отвечаете: надо пройти два дома прямо, а затем повернуть направо и пройти ещё один дом. Очевидно, что второй прохожий так до библиотеки и не доберётся.
То же самое относится и к ссылкам в Excel. Поэтому они и называются относительными. И хотя в формуле мы видим конкретные адреса ячеек, что облегчает нам их чтение и запись, но под капотом Excel происходят совершенно другие процессы.
Возвращаясь к нашей аналогии. Как бы вы объяснили любому прохожему в любой точке города, где находится библиотека? Вы бы сказали её адрес!
И в Excel есть аналог адресов. Это:
Абсолютные ссылки
В нашем примере, нам необходимо, чтобы ссылка на ячейку E2 была абсолютным адресом, вместо относительной ссылки. Это оставит ссылку на неё неизменной при протягивании формулы.
Чтобы сделать ссылку абсолютной, перед буквой столбца и номером строки ссылки добавляются знаки доллара ($), то есть ссылка будет выглядеть так: $E$2. Вы можете это сделать просто набрав знаки доллара на клавиатуре (на английской раскладке с зажатым Shift нажать клавишу с цифрой 4) или нажав на клавишу F4.
Изменим формулу и протянем её снова. Вот, что получилось:
Эта ссылка полностью абсолютная. Это значит, что она не изменится, куда бы мы не протянули формулу: влево, вправо, вверх или вниз.
В нашем примере мы заполняем формулу вниз. Для этого нам было бы достаточно сделать абсолютной только строку. То есть для нашей формулы достаточно было бы ввести E$2. Но обычно большинство пользователей если нет иных требований делают полностью абсолютные ссылки.
Кстати, маленький лайфхак: каждый раз, когда вы нажимаете на клавишу F4, ссылка циклически перебирает различные доступные варианты: $E$2, E$2, $E2 и E2.
Смешанные ссылки
Смешанная ссылка — это ссылка, которая сочетает в себе относительную и абсолютную ссылку. То есть когда вы делаете абсолютной ссылку только на столбец или строку ячейки.
Давайте посмотрим пример, в котором нам могли бы пригодится смешанные ссылки. Допустим, мы хотим заполнить таблицу умножения от 2 до 10:
Начнем с ячейки B2. Используем формулу =A2*B1, то есть умножим 2 на 2. Но если мы заполним эту формулу протягиванием до столбца и строки 10, то таблица умножения у нас не получится:
Поскольку мы заполняем формулу в двух направлениях, нам нужно сделать абсолютными (или зафиксировать) столбец или строку каждой ссылки в формуле.
Введём формулу
=$A2*B$1
и получим верно заполненную таблицу умножения.
В первой части формулы столбец A делается абсолютным, чтобы сохранить ссылку на него, когда мы будем протягивать формулу до столбца J. Но при этом нам нужно менять числа в рамках столбца A.
Во второй части формулы строка 1 делается абсолютной, чтобы не дать ей измениться при протягивании формулы до строки 10. Тем не менее, нам нужно использовать другие буквы в строке 1.
Поздравляю! Вы сами сделали действительно полезную вещь - таблицу умножения, которая теперь всегда у вас будет под рукой.
Если у вас остались вопросы про относительные и абсолютные ссылки, задавайте их в комментариях. В следующей статье мы изучим ссылки на другие листы или книги Excel.
Не переключайтесь;)