Рабочий лист в Excel состоит из ячеек. На эти ячейки можно ссылаться, указав значение строки и значение столбца.
Например, A1 будет относиться к первой строке (обозначенной как 1) и первому столбцу (обозначенной как A). Точно так же B3 будет третьей строкой и вторым столбцом.
Сила Excel заключается в том, что вы можете использовать эти ссылки на ячейки в других ячейках при создании формул.
Теперь есть три вида ссылок на ячейки, которые вы можете использовать в Excel:
- Относительные ссылки на ячейки
- Абсолютные ссылки на ячейки
- Смешанные ссылки на ячейки
Понимание этих различных типов ссылок на ячейки поможет вам работать с формулами и сэкономить время (особенно при копировании и вставке формул).
Что такое относительные ссылки на ячейки в Excel?
Позвольте мне на простом примере объяснить концепцию относительных ссылок на ячейки в Excel.
Предположим, у меня есть набор данных, показанный ниже:
Чтобы рассчитать общую сумму для каждого элемента, нам нужно умножить цену каждого элемента на количество этого элемента.
Для первого элемента формула в ячейке D2 будет B2 * C2 (как показано ниже):
Теперь вместо того, чтобы вводить формулу для всех ячеек одну за другой, вы можете просто скопировать ячейку D2 и вставить ее во все остальные ячейки (D3: D8). Когда вы это сделаете, вы заметите, что ссылка на ячейку автоматически настраивается, чтобы ссылаться на соответствующую строку. Например, формула в ячейке D3 становится B3 * C3, а формула в D4 становится B4 * C4.
Эти ссылки на ячейки, которые настраиваются при копировании ячейки, называются относительные ссылки на ячейки в Excel.
Когда использовать относительные ссылки на ячейки в Excel?
Относительные ссылки на ячейки полезны, когда вам нужно создать формулу для диапазона ячеек, и формула должна ссылаться на относительную ссылку на ячейку.
В таких случаях вы можете создать формулу для одной ячейки и скопировать ее во все ячейки.
Что такое абсолютные ссылки на ячейки в Excel?
В отличие от относительных ссылок на ячейки, абсолютные ссылки на ячейки не меняются при копировании формулы в другие ячейки.
Например, предположим, что у вас есть набор данных, показанный ниже, где вам нужно рассчитать комиссию за общий объем продаж каждого товара.
Комиссия составляет 20% и указана в ячейке G1.
Чтобы получить размер комиссии за каждую продажу товара, используйте следующую формулу в ячейке E2 и скопируйте ее для всех ячеек:
= D2 * $ G $ 1
Обратите внимание, что в ссылке на ячейку есть два знака доллара ($), в которых указана комиссия: $г$2.
Что делает знак доллара ($)?
Символ доллара, добавленный перед номером строки и столбца, делает его абсолютным (т. Е. Предотвращает изменение номера строки и столбца при копировании в другие ячейки).
Например, в приведенном выше случае, когда я копирую формулу из ячейки E2 в E3, она изменяется с = D2 * $ G $ 1 на = D3 * $ G $ 1.
Обратите внимание, что пока D2 меняется на D3, $ G $ 1 не меняется.
Поскольку мы добавили символ доллара перед «G» и «1» в G1, это не позволит изменить ссылку на ячейку при ее копировании.
Следовательно, это делает ссылку на ячейку абсолютной.
Когда использовать абсолютные ссылки на ячейки в Excel?
Абсолютные ссылки на ячейки полезны, когда вы не хотите, чтобы ссылка на ячейку изменялась при копировании формул. Это может быть тот случай, когда у вас есть фиксированное значение, которое вам нужно использовать в формуле (например, ставка налога, ставка комиссии, количество месяцев и т. Д.)
Хотя вы также можете жестко закодировать это значение в формуле (т. Е. Использовать 20% вместо $ G $ 2), размещение его в ячейке и последующее использование ссылки на ячейку позволяет вам изменить его в будущем.
Например, если структура вашей комиссии изменится и вы теперь выплачиваете 25% вместо 20%, вы можете просто изменить значение в ячейке G2, и все формулы автоматически обновятся.
Что такое смешанные ссылки на ячейки в Excel?
Смешанные ссылки на ячейки немного сложнее, чем абсолютные и относительные ссылки на ячейки.
Могут быть два типа смешанных ссылок на ячейки:
- Строка блокируется, а столбец изменяется при копировании формулы.
- Столбец блокируется, а строка изменяется при копировании формулы.
Давайте посмотрим, как это работает, на примере.
Ниже приведен набор данных, в котором вам необходимо рассчитать три уровня комиссии на основе процентного значения в ячейках E2, F2 и G2.
Теперь вы можете использовать силу смешанной ссылки для расчета всех этих комиссий с помощью одной формулы.
Введите приведенную ниже формулу в ячейку E4 и скопируйте для всех ячеек.
= $ B4 * $ C4 * E $ 2
В приведенной выше формуле используются оба типа смешанных ссылок на ячейки (в одной заблокирована строка, а в другой заблокирован столбец).
Давайте проанализируем каждую ссылку на ячейку и поймем, как она работает:
- $ B4 (и $ C4) - В этой ссылке знак доллара стоит прямо перед обозначением столбца, но не перед номером строки. Это означает, что при копировании формулы в ячейки справа ссылка останется такой же, как и столбец. Например, если вы скопируете формулу из E4 в F4, эта ссылка не изменится. Однако, когда вы его скопируете, номер строки изменится, поскольку он не заблокирован.
- 2 канадских доллара - В этой ссылке знак доллара стоит прямо перед номером строки, а в обозначении столбца нет знака доллара. Это означает, что при копировании формулы по ячейкам ссылка не изменится, поскольку номер строки заблокирован. Однако, если вы скопируете формулу вправо, алфавит столбца изменится, поскольку он не заблокирован.
Как изменить ссылку с относительной на абсолютную (или смешанную)?
Чтобы изменить ссылку с относительной на абсолютную, необходимо добавить знак доллара перед обозначением столбца и номером строки.
Например, A1 - это относительная ссылка на ячейку, и она станет абсолютной, когда вы сделаете ее $ A $ 1.
Если у вас есть только пара ссылок, которые нужно изменить, вы можете легко изменить эти ссылки вручную. Таким образом, вы можете перейти к строке формул и отредактировать формулу (или выбрать ячейку, нажать F2, а затем изменить ее).
Однако более быстрый способ сделать это - использовать сочетание клавиш - F4.
Когда вы выбираете ссылку на ячейку (в строке формул или в ячейке в режиме редактирования) и нажимаете F4, она меняет ссылку.
Предположим, у вас есть ссылка = A1 в ячейке.
Вот что происходит, когда вы выбираете ссылку и нажимаете клавишу F4.
- Нажмите клавишу F4 один раз: Ссылка на ячейку изменится с A1 на $ A $ 1 (вместо «относительной» станет «абсолютная»).
- Дважды нажмите клавишу F4: Ссылка на ячейку изменится с A1 на A $ 1 (изменится на смешанную ссылку, где строка заблокирована).
- Трижды нажмите клавишу F4: Ссылка на ячейку изменится с A1 на $ A1 (изменится на смешанную ссылку, где столбец заблокирован).
- Нажмите клавишу F4 четыре раза: Ссылка на ячейку снова становится A1.
