Большинству людей знакомо использование относительных ссылок в Excel. Это связано с тем, что ссылки на ячейки в таблицах Excel по умолчанию используют метод относительных ссылок.
Однако бывают случаи, когда относительные ссылки раздражают. Он будет изменять ссылки на ячейки каждый раз, когда вы копируете ячейки или заполняете столбцы и строки. Если вы не хотите, чтобы ссылки менялись, вам нужно использовать абсолютные ссылки или смешанные ссылки (включая относительные и абсолютные ссылки).
В абсолютной ссылке ссылки на столбцы и строки «заблокированы», поэтому ни одна из них не изменяется при копировании или заполнении этой ячейки.
В этой статье мы рассмотрим, как использовать абсолютные ссылки, чтобы заставить Excel вести себя с данными так, как вы хотите.
Как работают относительные ссылки в Excel
Когда вы вводите значения в электронную таблицу Excel, каждой ячейке присваивается определенная буква и цифра. Это представляет столбец и строку этой ячейки.
Например, значение «1» в таблице ниже находится в столбце A и строке 2. Таким образом, «ссылка» на эту ячейку — A2 .
Если вы хотите выполнить расчет в следующей ячейке на основе этой ячейки, то, добавив к ней 1, вы напишете следующую формулу:
=A2+1
Эта формула вставит значение из A2 в формулу, вычислит его, а затем выведет результат в ячейку, где находится эта формула.
Когда вы нажмете Ввод , вы увидите результат.
При относительной ссылке вам не придется вводить эту формулу в каждую вторую ячейку. Все, что вам нужно сделать, это перетащить угол ячейки с исходной формулой вниз настолько далеко, насколько вам хочется.
В следующей ячейке ссылка на A2 станет A3. В ячейке ниже A3 станет A4. Другими словами, Excel знает, что вы хотите добавить 1 к предыдущей ячейке, поэтому Excel соответствующим образом обновляет число (ссылку на строку) при перетаскивании вниз.
Это работает таким же образом, если вы перетаскиваете формулу по столбцам. Вместо обновления числа Excel обновит последнюю часть ссылки (столбец), чтобы всегда ссылаться на ячейку над ней.
Столбец справа содержит B2, справа — C2 и т. д.
Это простой пример того, как относительная адресация работает как для ссылок на столбцы, так и на ячейки.
Как работают абсолютные ссылки в Excel
.Абсолютные ссылки в Excel позволяют ссылаться на одну и ту же ячейку, а не разрешать Excel автоматически обновляется ссылку на строку или столбец за вас. «Смешанная» ссылка — это когда вы блокируете только строку или столбец, а «абсолютная ссылка» — когда вы блокируете обе.
Давайте рассмотрим несколько примеров.
Предположим, в вашей электронной таблице в верхней строке есть цифра «10», и вы хотите, чтобы каждая строка под ней умножала это число на число в ячейке слева.
Для этого вам нужно ввести формулу следующего вида:
=B$2*A3
Это блокирует ссылку «2», поэтому ссылка на строку не изменится, если вы перетащите ячейку с этой формулой вниз в ячейки под ней. Поскольку A3 остается «разблокированным», ссылка на строку и столбец по-прежнему будет меняться автоматически и всегда ссылаться на ячейку слева.
Вы заметите, что это работает только потому, что вы проводите перетаскивание вниз по ячейкам в том же столбце. Поэтому вам не нужно блокировать столбец (B), помещая перед ним знак доллара ($).
Проблема в том, что если вы хотите использовать ту же формулу справа от исходной формулы, ссылка «B» изменится, и формула больше не будет ссылаться на B2, как предполагалось.
Давайте посмотрим, как использовать абсолютные ссылки вместо смешанных, чтобы заполнение в обоих направлениях работало правильно.
Правильное использование абсолютных ссылок в Excel
Чтобы использовать правильные ссылки в этой формуле, вы должны точно учитывать, что вы пытаетесь сделать.
В данном случае нам нужно следующее поведение при заполнении справа.
Глядя на это поведение, вы теперь знаете, что вам нужно «заблокировать», а что нет. И «B», и «2» должны быть заблокированы (без изменений). Кроме того, столбец A необходимо заблокировать.
Итак, ваша формула в B3 должна выглядеть так: =$B$2*$A3
Теперь, когда вы перетаскиваете одну и ту же ячейку вниз или вверх, формула работает так, как задумано.
Правильное использование абсолютных ссылок может оказаться непростой задачей, поэтому важно уделить время тщательному обдумыванию того, как Excel будет обновлять формулу при заполнении столбцы или строки в любом направлении.
Циклическое переключение ссылочных типов в Excel
.Вы можете ускорить этот процесс, когда вам ввод формул, используя абсолютные ссылки, нажав клавишу F4 , что сделает ссылку на ячейку абсолютной.
<с>14Курсор может находиться по обе стороны от ссылки на ячейку (или даже в ее середине), когда вы нажимаете F4, и он все равно преобразует эту единственную ссылку в абсолютную.
Если вам не нужен абсолютный (например, смешанный), продолжайте нажимать F4, пока ссылка не будет выглядеть так, как вы хотите.
Если вы хотите добавить какую-либо ссылку на другие ячейки в формуле, просто поместите туда курсор и снова начните нажимать F4.
После того как вы настроили формулу, просто нажмите Enter и начните заполнять таблицу в любом направлении. Если вы правильно настроили ссылки, все должно работать так, как ожидалось.
.