Большинство людей знакомы с использованием относительных ссылок в Excel^{(in Excel)} . Это связано с тем, что ссылки на ячейки в электронных таблицах Excel по умолчанию используют метод относительных ссылок.

Однако бывают случаи, когда относительные ссылки раздражают. Он будет изменять ссылки на ячейки каждый раз, когда вы копируете ячейки или заполняете столбцы и строки. Если вы не хотите, чтобы ссылки изменялись, вам нужно использовать абсолютные ссылки или смешанные ссылки (включая относительные и абсолютные ссылки).

В абсолютной ссылке ссылки на столбцы и строки «заблокированы», поэтому ни одна из них не изменяется при копировании или заполнении из этой ячейки.

В этой статье мы рассмотрим, как можно использовать абсолютные ссылки, чтобы заставить Excel работать с данными так, как вам нужно.

Как работают относительные ссылки в ^(References)Excel

Когда вы вводите значения в электронную таблицу Excel , каждой ячейке присваивается определенная буква и номер. Это представляет столбец и строку этой ячейки.

Например, значение «1» в электронной таблице ниже находится в столбце A и строке 2. Таким образом, «ссылка» на эту ячейку — A2 .

Если вы хотите выполнить вычисление в следующей ячейке на основе этой ячейки, прибавив к ней 1, вы напишете следующую формулу:

=A2+1

Эта формула будет вставлять значение из A2 в формулу, вычислять его, а затем выводить результат в ячейку, где находится эта формула.

Когда вы нажмете Enter , вы увидите результат.

При относительной ссылке вам не нужно вводить эту формулу в каждую вторую ячейку. Все, что вам нужно сделать, это перетащить угол ячейки с исходной формулой вниз, насколько вы хотите. 

В следующей ячейке ссылка на A2 станет A3. В ячейке ниже A3 станет A4. Другими словами, Excel знает, что вы хотите добавить 1 к предыдущей ячейке, поэтому Excel обновляет число (ссылку на строку) соответствующим образом при перетаскивании вниз.

Это работает так же, если вы перетаскиваете формулу по столбцам. Вместо обновления числа Excel обновит последнюю часть ссылки (столбец), чтобы всегда ссылаться на ячейку над ним.

Столбец справа содержит B2, справа от него — C2 и так далее.

Это простой пример того, как относительная адресация работает как для ссылок на столбцы, так и для ссылок на ячейки.

Как работают абсолютные ссылки в ^(References)Excel

Абсолютные^(Absolute) ссылки в Excel позволяют ссылаться на одну и ту же ячейку, а не позволяют Excel автоматически обновлять^{(Excel automatically update)} ссылки на строки или столбцы. «Смешанная» ссылка — это блокировка только строки или столбца, а «абсолютная ссылка» — блокировка обеих.

Давайте посмотрим на некоторые примеры.

Допустим, в вашей электронной таблице в верхней строке есть число «10», и вы хотите, чтобы каждая строка под ней умножала это число на число в ячейке слева. 

Для этого вы должны ввести формулу, которая выглядит следующим образом:

=B$2*A3

Это блокирует ссылку «2», поэтому она не изменит ссылку на строку, если вы перетащите ячейку с этой формулой вниз в ячейки под ней. Поскольку A3 остается «разблокированным», ссылка на строку и столбец по-прежнему будет автоматически изменяться и всегда ссылаться на ячейку слева.

Вы заметите, что это работает только потому, что вы перетаскиваете в ячейки в том же столбце. Таким образом, вам не нужно блокировать столбец (B), помещая перед ним знак доллара ($).

Проблема в том, что если вы хотите использовать ту же формулу справа от исходной формулы, ссылка «B» изменится, и формула больше не будет ссылаться на B2, как предполагалось.

Давайте посмотрим, как использовать абсолютные ссылки вместо смешанных ссылок, чтобы заполнение в обоих направлениях работало правильно.

Правильное использование абсолютных ^(Absolute) ссылок^(References) в Excel

Чтобы использовать правильные ссылки в этой формуле, вы должны точно понимать, что вы пытаетесь сделать.

В этом случае нам нужно следующее поведение при заполнении справа.

• Всегда ссылайтесь на значение в ячейке B2
• Всегда ссылайтесь на значение в столбце A
• Сместить ссылку строки для столбца A на текущую строку формулы

Глядя на это поведение, вы теперь знаете, что вам нужно «блокировать», а что нет. И «B», и «2» должны быть заблокированы (без изменений). Кроме того, столбец A должен быть заблокирован.

Итак, ваша формула в B3 должна выглядеть так: =$B$2*$A3

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

Правильное использование абсолютной ссылки может оказаться сложным, поэтому очень важно тщательно продумать, как вы хотите, чтобы Excel обновлял формулу при заполнении столбцов или строк^{(columns or rows)} в любом направлении.

Перебор ссылочных типов^{(Reference Types)} в Excel

Вы можете ускорить процесс при вводе формул^{(typing formulas)} с абсолютными ссылками, нажав клавишу F4 , которая сделает ссылку на ячейку абсолютной.

Курсор может находиться по обе стороны от ссылки на ячейку (или даже в ее середине), когда вы нажимаете F4, и он все равно преобразует эту единственную ссылку в абсолютную.

Если вам не нужен абсолютный (например, смешанный), продолжайте нажимать F4, пока ссылка не будет выглядеть так, как вы хотите.

Если вы хотите добавить какие-либо ссылки на другие ячейки в формуле, просто поместите туда курсор и снова начните циклически нажимать F4.

После того, как вы настроили формулу, просто нажмите Enter и начните заполнять электронную таблицу в любом направлении. Если вы правильно настроите свои ссылки, все должно работать так, как ожидалось.

How to Use Absolute References in Excel

Most people are familiаr with using relative references in Excel. This is because cell references in Excel spreadsheets default to the relative reference method.

However, there are times when relative references get annoying. It will change cell references any time you copy cells or fill columns and rows. If you don’t want references to change, you’ll need to go with absolute references or mixed references (including relative and absolute references).

In an absolute reference, both the column and row references are “locked,” so neither of those change when you copy or fill from that cell.

This article will look at how you can use absolute references to make Excel behave the way you want with data.

How Relative References Work in Excel

When you enter values into an Excel spreadsheet, each cell has a specific letter and number assigned to it. This represents the column and row of that cell.

For example, the value of “1” in the spreadsheet below is in column A and row 2. So the “reference” to this cell is A2.

If you want to perform a calculation in the next cell based on this cell, by adding 1 to it, you will write the following formula:

=A2+1

This formula will insert the value from A2 into the formula, calculate it, and then output the result into the cell where this formula is.

When you press Enter, you’ll see the result.

With relative referencing, you don’t have to type this formula into every other cell. All you have to do is drag the corner of the cell with the original formula down as far as you’d like. 

In the next cell, the reference to A2 will become A3. In the cell below that, A3 will become A4. In other words, Excel knows that you want to add 1 to the previous cell, so Excel updates the number (the row reference) accordingly as you drag down.

This works in the same way if you drag the formula across columns. Instead of updating the number, Excel will update the latter part of the reference (the column) to always reference the cell above it.

The column to the right contains B2, to the right of that contains C2, and so on.

This is a simple example of how relative addressing works for both column and cell references.

How Absolute References Work in Excel

Absolute references in Excel let you reference the same cell, rather than letting Excel automatically update the row or column references for you. “Mixed” referencing is if you lock only the row or the column, and “absolute referencing” is when you lock both.

Let’s look at some examples.

Let’s say your spreadsheet has a “10” in a top row, and you want every row beneath it to multiply that number by the number in the cell to the left. 

To do this, you’d enter a formula that looks like this:

=B$2*A3

This locks the “2” reference so it won’t change the row reference if you drag the cell with this formula down to the cells below it. Since A3 remains “unlocked” both the row and column reference will still change automatically and always reference the cell to the left.

You will notice that this only works because you’re dragging down into cells in the same column. So you don’t have to lock column (B) by placing a dollar sign ($) in front of it.

The problem with this is that if you want to use the same formula to the right of the original formula, the “B” reference will change, and the formula will no longer reference B2 as intended.

Let’s take a look at how to use absolute references instead of mixed references to make filling in both directions work properly.

Correctly Using Absolute References in Excel

To use the correct referencing in this formula, you must consider precisely what you’re trying to do.

In this case, we want the following behaviors when filling to the right.

• Always reference the value in cell B2
• Always reference the value in column A
• Shift the reference of the row for column A to the current row of the formula

Looking at these behaviors, you now know what you need to “lock” and what you don’t. Both “B” and “2” need to be locked (unchanged). Also, column A needs to be locked.

So your formula in B3 needs to look like this: =$B$2*$A3

Now when you drag this same cell either down or up, the formula works as intended.

Correctly using absolute referencing can get tricky, so it’s essential to take the time to carefully consider how you want Excel to update the formula as you fill columns or rows in either direction.

Cycling Through Reference Types in Excel

You can speed along the process when you’re typing formulas with absolute references by pressing the F4 key, which will make the cell reference absolute.

The cursor can be on either side of the cell reference (or even in the middle of it) when you press F4, and it’ll still convert that single reference to absolute.

If you don’t want absolute (for example, mixed instead), keep tapping F4 until the reference looks the way you want.

If you want to add any kind of referencing to other cells in the formula, just place your cursor there and start cycling through F4 again.

Once you have configured your formula, just press Enter and begin filling your spreadsheet in any direction you like. If you set up your references right, everything should work just as expected.

Related posts

• Сохранять ссылки на ячейки при копировании формулы в Excel

• Как удалить пустые строки в Excel

• Как использовать функцию «Говорить ячейки» в Excel

• Как вставить лист Excel в документ Word

• Как использовать анализ «что, если» в Excel

• Как вставить CSV или TSV в рабочий лист Excel

• Как сортировать по дате в Excel

• Как исправить строку в Excel

• Быстрое написание чисел в Word и Excel

• 5 способов преобразовать текст в числа в Excel

• Как объединить данные в нескольких файлах Excel

• Как использовать функцию PMT в Excel

• Как отследить зависимых в Excel

• Почему вы должны использовать именованные диапазоны в Excel

• 2 способа использования функции транспонирования Excel

• Что такое массив VBA в Excel и как его запрограммировать

• Как создать простую сводную таблицу в Excel

• Как быстро вставить несколько строк в Excel

• Базовая сортировка данных в один столбец и несколько столбцов в электронных таблицах Excel

• Группировать строки и столбцы на листе Excel