Как изменить относительные адреса ячеек на абсолютные в Excel
Обновлено: 21.11.2024
При работе с электронными таблицами необходимо знать об относительных и абсолютных ссылках на ячейки.
Вот проблема: когда вы КОПИРУЕТЕ ФОРМУЛУ, содержащую ссылки на ячейки, что происходит со ссылками на ячейки?
Обычно ССЫЛКИ НА КЛЕТКИ ИЗМЕНЯЮТСЯ! Если скопировать формулу на 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 изменяет только ссылку на ячейку непосредственно слева от точки вставки.
Я надеюсь, что этот учебник сделал эти типы ссылок на ячейки «абсолютно» понятными!
Формулы электронных таблиц по умолчанию являются относительными ссылками на ячейки. Когда формула из ячейки копируется в другую ячейку, она изменяется в соответствии с новой ячейкой. В некоторых ситуациях может потребоваться, чтобы формула оставалась неизменной и не менялась, что называется абсолютной ссылкой на ячейку.
Изменить ячейку с относительной ссылки на абсолютную можно, выполнив следующие действия.
- Откройте Microsoft Excel.
- Выделите ячейку, содержащую формулу, которую вы хотите изменить на абсолютную или относительную ссылку.
- Нажмите на поле формулы (показано ниже) или выделите формулу и нажмите клавишу F4, чтобы переключиться между абсолютной и относительной ссылкой на ячейку.
Вы также можете выделить части формулы и нажать F4, чтобы получить частичную абсолютную ссылку.
Если вы хотите написать вручную или создать абсолютную ссылку, используйте в формуле символ "$". Ниже приведен базовый пример, демонстрирующий разницу между базовой относительной и абсолютной ссылкой.
Относительная ссылка
Приведенная выше базовая формула должна быть знакома большинству пользователей. он складывает значения ячеек с A1 по A3.
Абсолютная ссылка
Чтобы изменить указанную выше относительную ссылку на абсолютную, добавьте символ "$" перед столбцом и строкой.
Частичная абсолютная ссылка
Вы также можете создать частичную абсолютную ссылку, которая обеспечивает гибкость формул и функциональность электронной таблицы.
В этом первом примере только столбец (A) является абсолютной ссылкой, а строка — относительной ссылкой. Когда вы копируете эту формулу в другой столбец и строку, она по-прежнему ссылается на столбец A, но изменяет строку в зависимости от того, куда она скопирована.
В этом втором примере только строки (с 1 по 3) являются абсолютной ссылкой, а столбец — относительной ссылкой. Когда вы копируете эту формулу в другой столбец и строку, она по-прежнему ссылается на строки с 1 по 3, но изменяет столбец в зависимости от того, куда она скопирована.
поиск меню
Урок 15. Относительные и абсолютные ссылки на ячейки
Введение
Существует два типа ссылок на ячейки: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются, когда формула копируется в другую ячейку. С другой стороны, абсолютные ссылки остаются постоянными независимо от того, куда они копируются.
Посмотрите видео ниже, чтобы узнать больше о ссылках на ячейки.
Относительные ссылки
По умолчанию все ссылки на ячейки являются относительными. При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.
Чтобы создать и скопировать формулу с использованием относительных ссылок:
В следующем примере мы хотим создать формулу, которая будет умножать цену каждого товара на количество. Вместо создания новой формулы для каждой строки мы можем создать одну формулу в ячейке D4, а затем скопировать ее в другие строки. Мы будем использовать относительные ссылки, чтобы формула правильно вычисляла сумму для каждого элемента.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D4.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Относительные ссылки на ячейки должны быть разными для каждой ячейки в зависимости от их строк.
Абсолютные ссылки
Возможны случаи, когда вы не хотите, чтобы ссылка на ячейку менялась при заполнении ячеек. В отличие от относительных ссылок, абсолютные ссылки не изменяются при копировании или заполнении. Вы можете использовать абсолютную ссылку, чтобы сохранить строку и/или столбец постоянными.
Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.
В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.
При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными, абсолютными и смешанными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.
Чтобы создать и скопировать формулу с использованием абсолютных ссылок:
В приведенном ниже примере мы собираемся использовать ячейку E2 (которая содержит налоговую ставку 7,5%) для расчета налога с продаж для каждого элемента в столбце D. Чтобы убедиться, что ссылка на налоговую ставку остается постоянной — даже когда формула копируется и заполняется другими ячейками — нам нужно сделать ячейку $E$2 абсолютной ссылкой.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D4.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Абсолютная ссылка должна быть одинаковой для каждой ячейки, а остальные ссылки относятся к строке ячейки.
Обязательно добавляйте знак доллара ($) всякий раз, когда делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Из-за этого Excel интерпретировал ее как относительную ссылку, что приводило к неверному результату при копировании в другие ячейки.
Использование ссылок на ячейки с несколькими листами
Excel позволяет вам ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на определенное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.
Обратите внимание, что если имя рабочего листа содержит пробел, его необходимо заключить в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.
Чтобы ссылаться на ячейки на листах:
В приведенном ниже примере мы будем ссылаться на ячейку с вычисленным значением между двумя листами.Это позволит нам использовать одно и то же значение на двух разных листах, не переписывая формулу и не копируя данные.
-
Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе «Порядок меню».
Если вы переименуете свой рабочий лист позже, ссылка на ячейку будет автоматически обновлена, чтобы отразить новое имя рабочего листа.
поиск меню
Урок 4. Относительные и абсолютные ссылки на ячейки
Введение
Существует два типа ссылок на ячейки: относительные и абсолютные. Относительные и абсолютные ссылки ведут себя по-разному при копировании и заполнении других ячеек. Относительные ссылки изменяются, когда формула копируется в другую ячейку. С другой стороны, абсолютные ссылки остаются постоянными независимо от того, куда они копируются.
Необязательно: загрузите файл примера для этого урока.
Посмотрите видео ниже, чтобы узнать больше о ссылках на ячейки.
Относительные ссылки
По умолчанию все ссылки на ячейки являются относительными. При копировании в несколько ячеек они изменяются в зависимости от относительного положения строк и столбцов. Например, если вы скопируете формулу =A1+B1 из строки 1 в строку 2, формула станет =A2+B2. Относительные ссылки особенно удобны, когда вам нужно повторить одни и те же вычисления в нескольких строках или столбцах.
Чтобы создать и скопировать формулу с использованием относительных ссылок:
В следующем примере мы хотим создать формулу, которая будет умножать цену каждого товара на количество. Вместо создания новой формулы для каждой строки мы можем создать одну формулу в ячейке D2, а затем скопировать ее в другие строки. Мы будем использовать относительные ссылки, чтобы формула правильно вычисляла итоговую сумму для каждого элемента.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D2.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Относительные ссылки на ячейки должны быть разными для каждой ячейки в зависимости от ее строки.
Абсолютные ссылки
Возможны случаи, когда вы не хотите, чтобы ссылка на ячейку менялась при заполнении ячеек. В отличие от относительных ссылок, абсолютные ссылки не изменяются при копировании или заполнении. Вы можете использовать абсолютную ссылку, чтобы сохранить строку и/или столбец постоянными.
Абсолютная ссылка обозначается в формуле добавлением знака доллара ($) перед столбцом и строкой. Если он предшествует столбцу или строке (но не обоим), он называется смешанной ссылкой.
В большинстве формул вы будете использовать относительный (A2) и абсолютный ($A$2) форматы. Смешанные ссылки используются реже.
При написании формулы в Microsoft Excel вы можете нажать клавишу F4 на клавиатуре, чтобы переключаться между относительными, абсолютными и смешанными ссылками на ячейки, как показано в видео ниже. Это простой способ быстро вставить абсолютную ссылку.
Чтобы создать и скопировать формулу с использованием абсолютных ссылок:
В нашем примере мы будем использовать ставку налога с продаж в размере 7,5 % в ячейке E1 для расчета налога с продаж для всех товаров в столбце D. Нам потребуется использовать абсолютную ссылку на ячейку $E$1 в нашей формуле. Поскольку в каждой формуле используется одна и та же налоговая ставка, мы хотим, чтобы эта ссылка оставалась постоянной, когда формула копируется и заполняется другими ячейками в столбце D.
-
Выберите ячейку, которая будет содержать формулу. В нашем примере мы выберем ячейку D3.
Вы можете дважды щелкнуть заполненные ячейки, чтобы проверить правильность их формул. Абсолютная ссылка должна быть одинаковой для каждой ячейки, а остальные ссылки относятся к строке ячейки.
Обязательно добавляйте знак доллара ($) всякий раз, когда делаете абсолютную ссылку на несколько ячеек. В приведенном ниже примере знаки доллара были опущены. Это привело к тому, что электронная таблица интерпретировала ее как относительную ссылку, что дало неверный результат при копировании в другие ячейки.
Использование ссылок на ячейки с несколькими листами
Большинство программ для работы с электронными таблицами позволяют ссылаться на любую ячейку на любом листе, что может быть особенно полезно, если вы хотите сослаться на конкретное значение с одного листа на другой. Для этого вам просто нужно начать ссылку на ячейку с имени рабочего листа, за которым следует восклицательный знак (!). Например, если вы хотите сослаться на ячейку A1 на Листе1, ссылка на эту ячейку будет Лист1!А1.
Обратите внимание: если имя рабочего листа содержит пробел, вам нужно будет заключить имя в одинарные кавычки (' '). Например, если вы хотите сослаться на ячейку A1 на листе с названием "Бюджет на июль", ее ссылкой на ячейку будет "Июльский бюджет"!A1.
Чтобы ссылаться на ячейки на листах:
В приведенном ниже примере мы будем ссылаться на ячейку с вычисленным значением между двумя листами. Это позволит нам использовать одно и то же значение на двух разных листах, не переписывая формулу и не копируя данные между листами.
-
Найдите ячейку, на которую вы хотите сослаться, и обратите внимание на ее рабочий лист. В нашем примере мы хотим сослаться на ячейку E14 на листе «Порядок меню».
Если вы переименуете свой рабочий лист позже, ссылка на ячейку будет автоматически обновлена, чтобы отразить новое имя рабочего листа.
Как изменить относительную ссылку на абсолютную ссылку в Excel?
По умолчанию ссылки на ячейки настроены как относительные ссылки на рабочих листах, что означает, что когда мы копируем формулу из одной ячейки в другую на текущем рабочем листе, относительные ссылки будут автоматически изменены на ее ссылку на ячейку. В некоторых случаях относительная ссылка полезна, но когда вам нужно скопировать формулу в другие ячейки без изменения ссылок на ячейки в формулах, нам может потребоваться изменить относительную ссылку на абсолютную ссылку перед перемещением формулы.
Как мы все знаем, мы можем нажать F4, чтобы переключить относительный адрес на абсолютный адрес, или вручную добавить знак доллара ($) перед строкой и столбцом, чтобы изменить относительный адрес на абсолютный в Excel. Но когда нам нужно скопировать несколько формул без изменения ссылок на ячейки, что нам делать?
- Повторное использование всего: добавляйте наиболее часто используемые или сложные формулы, диаграммы и другие объекты в избранное и быстро используйте их повторно в будущем.
- Более 20 текстовых функций: извлечение числа из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты объединения: несколько книг и листов в одну; Объединение нескольких ячеек/строк/столбцов без потери данных; Объединить повторяющиеся строки и суммировать.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Одна рабочая книга для нескольких файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить, пропуская скрытые/отфильтрованные строки; Подсчет и сумма по цвету фона; Массовая рассылка персонализированных электронных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировка по неделям, дням, частоте и т. д.; Фильтр по полужирному шрифту, формулам, комментариям.
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Потрясающе! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Сэкономьте 50 % своего времени и сократите количество кликов мышью каждый день!
Предположим, что в столбце C используется формула (=A1*B1) для умножения столбца A на столбец B. Когда мы копируем столбец C в другие ячейки, результаты вычислений изменятся, поскольку формула (=A1*B1) использует относительную ссылку на ячейку. Если мы хотим скопировать столбец C без изменения ссылок на ячейки формул, мы можем изменить ссылки на ячейки с относительных на абсолютные перед перемещением.
После перемещения столбца C в столбец E.
Все значения изменены.
В этой ситуации мы можем нажать F4, чтобы переключить относительные ссылки на абсолютные ссылки.
Поместите курсор за A1 в строке формул, нажмите F4 один раз, он станет $A$1. Смотрите скриншот:
Затем поместите курсор за B1 в строке формул, нажмите F4 один раз, и он станет $B$1. Смотрите скриншот:
После изменения ссылки на ячейку формулы на абсолютный адрес мы можем скопировать формулу и переместиться в другую ячейку без изменения ссылки на ячейку.
Нажмите на ячейку с формулой, которую хотите изменить, и добавьте знак $ доллара перед строкой и столбцом. Затем нажмите «Ввод», и ссылки на ячейки в формулах будут изменены с относительных ссылок на абсолютные.
Вышеуказанные методы позволяют легко изменить одну или две ссылки на ячейки в формулах, но если необходимо изменить ряд формул, эти методы могут быть хлопотными и трудоемкими. Kutools for Excel может помочь вам быстро и удобно изменить ссылки на ячейки формулы диапазона.
Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас
После установки Kutools for Excel вы можете быстро изменить относительную ссылку на абсолютную ссылку, выполнив следующие шаги.
Шаг 1. Выберите диапазон, содержащий формулы, ссылки на которые вы хотите изменить на листе.
Шаг 2. Нажмите Kutools > Преобразовать ссылки в группе Формула. Отобразится диалоговое окно «Преобразовать ссылки на формулы». Смотрите скриншот:
|
Затем выберите параметр «В абсолютное значение» в меню «Преобразовать». Затем нажмите «ОК». Он преобразует ссылки на ячейки в формулах в абсолютные ссылки. Теперь мы можем копировать и перемещать формулу в любые ячейки текущего листа, не изменяя ссылки на ячейки в формулах. После изменения ссылок на ячейки в формулах копирование и перемещение формул в другие ячейки на текущем листе не приведет к изменению значений. Скопируйте и вставьте столбец C в столбец G текущей рабочей таблицы. Читайте также:
|