Какая кнопка клавиатуры изменяет относительную ссылку на абсолютную

Обновлено: 21.11.2024

Лист в 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 и скопируйте ее во все ячейки:

Обратите внимание, что в ссылке на ячейку с комиссией есть два знака доллара ($) — $G$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) — в этой ссылке знак доллара стоит прямо перед обозначением столбца, но не перед номером строки. Это означает, что когда вы копируете формулу в ячейки справа, ссылка останется такой же, как фиксированный столбец. Например, если вы скопируете формулу из E4 в F4, эта ссылка не изменится. Однако при копировании номер строки изменится, поскольку он не заблокирован.
  • E$2 — в этой ссылке знак доллара стоит прямо перед номером строки, а в обозначении столбца знак доллара отсутствует. Это означает, что при копировании формулы вниз по ячейкам ссылка не изменится, поскольку номер строки заблокирован. Однако если вы скопируете формулу вправо, алфавит столбца изменится, поскольку он не заблокирован.

Как изменить ссылку с относительной на абсолютную (или смешанную)?

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

Например, A1 — это относительная ссылка на ячейку, и она станет абсолютной, если вы сделаете ее $A$1.

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

Однако это можно сделать быстрее, нажав клавишу F4.

Когда вы выбираете ссылку на ячейку (в строке формул или в ячейке в режиме редактирования) и нажимаете F4, ссылка меняется.

При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.

Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?

Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 2 строки вправо, то ссылки на ячейки в формуле сместятся на 2 ячейки вправо. Если скопировать формулу на 3 строки вниз и на 1 строку влево, то ссылки на ячейки в формуле сместятся на 3 строки вниз и на 1 строку влево. Такие ссылки называются «относительными» ячейками, поскольку они изменяются относительно того места, куда вы копируете формулу.

Если вы не хотите, чтобы ссылки на ячейки менялись при копировании формулы, сделайте эти ссылки на ячейки абсолютными ссылками на ячейки. Поместите «$» перед буквой столбца, если вы хотите, чтобы она всегда оставалась неизменной. Поместите «$» перед номером строки, если вы хотите, чтобы он всегда оставался неизменным. Например, «$C$3» относится к ячейке C3, а «$C$3» будет работать точно так же, как «C3», если вы скопируете формулу. Примечание: при вводе формул вы можете использовать клавишу F4 сразу после ввода ссылки на ячейку для переключения между различными относительными/абсолютными версиями этого адреса ячейки.

Хитрость при создании электронных таблиц заключается в том, чтобы перед копированием формулы решить, какие ячейки в формуле должны быть относительными, а какие — абсолютными. Если некоторые ссылки на ячейки относятся к входным ячейкам в электронной таблице, вы обычно хотите, чтобы эти ячейки были абсолютными.

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

Относительные и абсолютные ссылки на ячейки

Карин Стилл

Excel использует два типа ссылок на ячейки для создания формул.У каждого своя цель. Читайте дальше, чтобы определить, какой тип ссылки на ячейку использовать для формулы.

Относительные ссылки на ячейки

Это наиболее широко используемый тип ссылки на ячейку в формулах. Относительные ссылки на ячейки — это базовые ссылки на ячейки, которые корректируются и изменяются при копировании или использовании автозаполнения.

=СУММ(B5:B8), как показано ниже, изменяется на =СУММ(C5:C8) при копировании в следующую ячейку.

Абсолютные ссылки на ячейки

Возникают ситуации, когда ссылка на ячейку должна оставаться неизменной при копировании или при использовании автозаполнения. Знаки доллара используются для сохранения постоянной ссылки на столбец и/или строку.

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

Более сложный пример:

Предположим, что вам нужно рассчитать цены товаров на складе с двумя разными ценовыми скидками. Взгляните на рабочий лист ниже.

Проверьте формулу в ячейке E4. Сделав ссылку на первую ячейку $C4, вы предотвратите изменение столбца при копировании, но позволите строке измениться при копировании вниз, чтобы приспособиться к снижению цен на различные товары. Сделав ссылку на последнюю ячейку A$12, вы предотвратите изменение номера строки при копировании вниз, но позволите столбцу измениться и отразить скидку B при копировании. Смущенный? Посмотрите на рисунок ниже и результаты ячеек.

Теперь вы можете подумать, а почему бы просто не использовать 10% и 15% в реальных формулах? Разве это не было бы проще? Да, если вы уверены, что процент скидки никогда не изменится, что крайне маловероятно. Скорее всего, в конечном итоге эти проценты необходимо будет скорректировать. Ссылаясь на ячейки, содержащие 10% и 15%, а не на фактические числа, когда процент изменяется, все, что вам нужно сделать, это изменить процент один раз в ячейке A12 и/или B12 вместо того, чтобы перестраивать все ваши формулы. Excel автоматически обновит цены со скидками, чтобы отразить изменение процента скидки.

Краткий обзор использования абсолютных ссылок на ячейки:

$A1 Разрешает изменение ссылки на строку, но не ссылка на столбец.
A$1 Разрешает изменять ссылку на столбец, но не на строку.
$A$1 Не позволяет изменять ни столбец, ни ссылку на строку.

Существует ярлык для размещения абсолютных ссылок на ячейки в ваших формулах!

При вводе формулы после ввода ссылки на ячейку нажмите клавишу F4. Excel автоматически делает ссылку на ячейку абсолютной! Продолжая нажимать F4, Excel будет циклически перебирать все возможности абсолютной ссылки. Например, в первой формуле абсолютной ссылки на ячейку в этом руководстве, =B4*$B$10, я мог бы ввести =B4*B10, а затем нажать клавишу F4, чтобы изменить B10 на $B$10. Продолжая нажимать F4, вы получите 10 B$, затем B10 и, наконец, B10. Нажатие F4 изменяет только ссылку на ячейку непосредственно слева от точки вставки.

Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!

Недавно Хейзел купила новый компьютер с Windows 10. Она обнаружила, что больше не может использовать клавишу F4 для переключения ссылки на ячейку с помощью абсолютных/относительных параметров. Вместо этого F4 открывает боковую панель о проецировании на второй монитор. Поскольку у Хейзел только один монитор, но множество требований для установки абсолютной ссылки на ячейку и обратно, это сводит ее с ума. Она хотела бы, чтобы клавиша F4 работала так же, как и в Excel.

Тестирование в системе Windows 10 показывает, что клавиша F4 по-прежнему циклически переключает абсолютные/относительные версии ссылок. Однако есть предостережение: вам нужно убедиться, что вы находитесь в «режиме редактирования», когда дело доходит до использования F4 с формулами. Другими словами, выберите нужную ячейку, нажмите F2, чтобы войти в режим редактирования, используйте клавиши со стрелками влево и вправо для перемещения по формуле и нажимайте F4 всякий раз, когда вы хотите прокрутить ссылку через различные перестановки абсолютной и относительной записи.< /p>

Если клавиша F4 по-прежнему работает не так, как вы ожидаете, вполне вероятно, что вы используете портативный компьютер или супер-пупер многофункциональную клавиатуру с настольной системой. На этих типах клавиатур функциональные клавиши обычно выполняют специальные функции, такие как регулировка громкости, регулировка яркости экрана и т. д. Чтобы перейти к нормальной работе определенной функциональной клавиши, найдите клавишу, помеченную как FN (сокращение от «функция»). ") или F Lock (для "блокировки функций"). Нажмите или удерживайте эту клавишу (это зависит от системы), когда вы нажимаете клавишу F4, и она должна работать так, как вы ожидаете.

ExcelTips – это экономичное обучение работе с Microsoft Excel. Этот совет (13235) относится к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

БОЛЬШЕ ОТ АЛЛЕНА

Размещение таблиц на полях

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

Преобразование активных URL в обычный текст

Преобразуйте эти URL в обычный текст! Это легко сделать, следуя инструкциям, описанным в этом совете.

Ярлык для объединения ячеек

Нужно регулярно объединять несколько ячеек? Вам понравятся два макроса в этом совете, которые могут сократить .

Руководство по профессиональному развитию! Четыре разработчика мирового класса предлагают полное руководство по созданию мощных, надежных и безопасных приложений с помощью Excel. Авторы показывают, как последовательно принимать правильные проектные решения и максимально эффективно использовать мощные функции Excel. Ознакомьтесь с Профессиональной разработкой Excel сегодня!

Дополнительные советы по Excel (лента)

Проблемы со вставкой информации на лист

Что делать, если при вставке информации на лист Excel оказывается на коленях? В этом совете рассматриваются лишь некоторые из ваших идей .

Выбор предложения с помощью клавиатуры

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

Ваш лист или книга не всегда пересчитываются так, как вы ожидаете? Если да, то некоторые идеи из этого совета могут .

Подписаться

БЕСПЛАТНАЯ СЛУЖБА. Получайте подобные советы каждую неделю в ExcelTips, бесплатном информационном бюллетене по повышению эффективности. Введите свой адрес и нажмите "Подписаться".

На предыдущих уроках вы узнали, как использовать абсолютные, относительные и смешанные ссылки в Excel. Что делать, если вы хотите переключаться между ссылками и не хотите вводить знак доллара ($) вручную?

Ярлык F4 для Windows и Command + T для Mac.

Ярлык Windows

Ярлык для Mac

Ярлык для переключения между ссылками

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

A1 -> $A$1 -> A$1 -> $A1 -> A1

Если вы нажмете клавишу F4

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

Примените описанный выше цикл, чтобы не вводить знак доллара ($) вручную.

Переключение между ссылками в формуле Excel

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

Выберите один из способов выше:

Режим редактирования ячейки: выберите ячейку, содержащую формулу. Используйте ярлык F2, чтобы войти в режим редактирования ячейки. Вы увидите мигающий курсор и формулу, а не значение ячейки.

Поместите курсор рядом с ссылкой, которую вы хотите преобразовать, затем нажмите F4.

Редактировать формулу с помощью панели формул:

Дополнительные ресурсы

Иштван является соучредителем Excelkid. Он пишет сообщения в блогах и помогает людям достичь вершин в Excel.

Читайте также: