Использование относительных и абсолютных ссылок
По умолчанию ссылка на ячейку является относительной. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы указываете адрес ячейки в том же ряду (2), но отстоящей на два столбца влево (C минус A). Формула с относительной ссылкой изменяется при копировании из одной ячейки в другую. Например, вы можете скопировать формулу =A2+B2 из ячейки C2 в C3, при этом формула в ячейке C3 сдвинется вниз на один ряд и превратится в =A3+B3.
Если необходимо сохранить исходный вид ссылки на ячейку при копировании, ее можно зафиксировать, поставив перед названиями столбца и строки знак доллара ($). Например, при копировании формулы =$A$2+$B$2 из C2 в D2 формула не изменяется. Такие ссылки называются абсолютными.
В некоторых случаях ссылку можно сделать «смешанной», поставив знак доллара перед указателем столбца или строки для «блокировки» этих элементов (например, $A2 или B$3). Чтобы изменить тип ссылки на ячейку, выполните следующее.
- Выделите ячейку со ссылкой на ячейку, которую нужно изменить.
- В строка формул щелкните ссылку на ячейку, которую вы хотите изменить.
- Для перемещения между сочетаниями используйте клавиши +T. В следующей таблице огововодятся сведения о том, что происходит при копировании формулы в ячейке A1, содержаной ссылку. В частности, формула копируется на две ячейки вниз и на две ячейки справа, в ячейку C3.
Текущая ссылка (описание):
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)
C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)
$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)
C3 (относительная ссылка)
Изменение типа ссылки: относительная, абсолютная, смешанная
По умолчанию ссылка на ячейку является относительной ссылкой, которая означает, что ссылка относительна к расположению ячейки. Например, если вы ссылаетесь на ячейку A2 из ячейки C2, вы фактически ссылаетесь на ячейку, которая находится на два столбца слева (C минус A) в одной строке (2). При копировании формулы, содержаной относительную ссылку на ячейку, эта ссылка в формуле изменится.
Например, при копировании формулы =B4*C4 из ячейки D4 в D5 формула в ячейке D5 корректируется на один столбец вправо и становится =B5*C5. Если вы хотите сохранить исходную ссылку на ячейку в этом примере при копировании, необходимо сделать ссылку на ячейку абсолютной, предшествуя столбцам (B и C) и строке (2) знаком доллара($). Затем при копировании формулы =$B$4*$C$4 из D4 в D5 формула остается той же.
В меньшей степени может потребоваться смешанные абсолютные и относительные ссылки на ячейки, предшествуя столбецу или значению строки знаком доллара, что исправит столбец или строку (например, $B 4 или C$4).
Чтобы изменить тип ссылки на ячейку, выполните следующее.
- Выделите ячейку с формулой.
- В строке формул строка формул выделите ссылку, которую нужно изменить.
- Для переключения между типами ссылок нажмите клавишу F4. В приведенной ниже таблице по сумме обновляется тип ссылки при копировании формулы, содержащей ссылку, на две ячейки вниз и на две ячейки справа.
Копируемая формула
Первоначальная ссылка
Новая ссылка
$A$1 (абсолютный столбец и абсолютная строка)
$A$1 (абсолютная ссылка)
A$1 (относительный столбец и абсолютная строка)
C$1 (смешанная ссылка)
$A1 (абсолютный столбец и относительная строка)
$A3 (смешанная ссылка)
A1 (относительный столбец и относительная строка)
C3 (относительная ссылка)
Excel: Смешанные ссылки
Многие пользователи успешно выполняют поставленные перед ними задачи и без применения разных типов ссылок. Всегда можно записать формулу с использованием только относительных ссылок, скопировать ее, подкорректировать и еще раз скопировать и так до конца рабочего дня. А можно нажать «F4» несколько раз в нужном месте и в результате выполнить тот же объем работ, но с гораздо меньшими затратами времени.
Использование смешанных ссылок может значительным образом сократить время решения ваших задач.
Смешанные ссылки являются наполовину абсолютными и наполовину относительными.
Смешанная ссылка указывается, если при копировании и перемещении не меняется номер строки или наименование столбца. При этом символ $ в первом случае ставится перед номером строки, а во втором — перед наименованием столбца.
- В$5, D$12 – смешанная ссылка, не меняется номер строки;
- $B5, $D12 — смешанная ссылка, не меняется наименование столбца.
Изменение типа ссылки производится циклически, в результате последовательных нажатий функциональной клавиши F4 в то время, когда курсор находится в тексте ссылки. Если, например, имеется ссылка на ячейку А1, то при каждом нажатии клавиши F4 вид ссылки в строке формул будет изменяться:
А1 → $A$1 → A$1 → $А1 → А1 →$A$1 и т. д.
Применение смешанных ссылок
Пример 1
В ячейке В1 записана формула «=$A1».
Ссылка $A1 абсолютная по столбцу и относительная по строке.
Если мы потянем за Маркер заполнения эту формулу вправо, то ссылки во всех скопированных формулах будут указывать на ячейку A1, названия столбцов изменяться не будут, то есть ссылки будут вести себя как абсолютные.
Если потянем вниз — ссылки будут вести себя как относительные, то есть Excel будет пересчитывать их адрес. Таким образом, созданные формулы, будут использовать один и тот же столбец (А), но номера строк в них будут меняться (1,2,3…)
Пример 2
Предположим, нужно посчитать, сколько получит каждый работник за отработанные часы при определенной почасовой оплате труда.
Для заполнения таблицы используем смешанные ссылки.
Рассчитаем оплату труда для Андреева.
Для этого в ячейку С3 введем формулу: «=В3*С2»
Теперь необходимо скопировать формулу в строке «Андреев»
за 2 часа работы в день он получит 400 рублей
за 3 часа — 600 рублей
за 4 часа — 800 рублей
Оплата в час (200 рублей) не изменяется (значение ячейки В3). Меняется только количество отработанных часов (ячейки С2, D2, E2 …). Значит, для того, чтобы менять количество отработанных часов, надо, чтобы программа меняла название столбца, но не трогала номер строки. То есть, формула для расчета зарплаты Андреева должна быть такой: =В3*С$2
Теперь отредактируем полученную формулу, чтобы применить ее для расчета зарплаты остальных работников.
Андреев за 2 часа получит 200 рублей
Борисов за 2 часа получит 360 рублей
Сергеев за 2 часа получит 440 рублей
Из таблицы видно, что не изменяется отработанное время (значение ячейки С2). Меняется оплата за час (ячейки В3, В4, В5). Значит, для того, чтобы менять оплату за час, надо, чтобы программа меняла номер строки, но не трогала название столбца. Получаем формулу: =$В3*С$2
Введем полученную формулу в ячейку С3 , а затем скопируем ее во все ячейки таблицы.
Можно сначала протянуть формулу по строке Андреева, а потом скопировать вниз (на Борисова и Сергеева):
Можно и наоборот – сначала скопировать вниз, а потом – в сторону.
Полученные результаты в режиме просмотра формул:
Пример 3
Требуется рассчитать отпускную стоимость товара при различных наценках, с учетом, что закупочная цена фиксирована.
Для расчета Цены с наценкой для товара (артикул 12456) укажем в ячейке С3 формулу =B3*(1+C2).
Теперь необходимо внести изменения в формулу, которые позволят с помощью копирования заполнить значения во всей таблице.
При «протаскивании» формулы по столбцам нам необходимо, чтобы столбец B (с ценами) был зафиксирован, для этого в формуле перед ссылкой В3 ставим знак $ ($B3).
Аналогично, при «протаскивании» формулы по строкам, нам необходимо зафиксировать строку 2 (проценты наценки), для этого в формуле в ссылке С2 ставим знак $ перед 2 (С$2) .
В ячейке C3, таким образом, получилась формула =$B3*(1+C$2).
При протаскивании по диапазону С3 : Е7 такая формула дает правильные значения в каждой ячейке таблицы.
покупка
Как изменить / преобразовать абсолютную ссылку в относительную ссылку в Excel?
Обычно в формуле можно использовать несколько типов ссылок на ячейки, и каждый тип ссылок на ячейки может помочь вам достичь различных целей в формуле. У вас могут быть следующие типы ссылок на ячейки, такие как абсолютная ссылка на ячейку, относительная ссылка на ячейку, относительная ссылка на строку, абсолютная ссылка на столбец, абсолютная ссылка на строку и относительная ссылка на столбец. Но иногда вам может потребоваться изменить назначение формулы, изменив ссылки на ячейки в формуле. Следующие хитрые методы расскажут, как изменить абсолютную ссылку на относительную в Excel.
Измените абсолютную ссылку на относительную с помощью клавиши F4
С помощью сочетания клавиш F4 мы можем легко переключить абсолютную ссылку на относительную. Для этого выполните следующие действия:
Поместите курсор за $ A $ 1, затем нажмите F4 три раза, он последовательно станет A $ 1, $ A1, A1. Смотрите скриншот:
 |
 |
 |
|
 |
|
 |
Согласно этому шагу поместите курсор за $ B $ 1, чтобы получить B1.
И эта ссылка на ячейку станет относительной ссылкой из абсолютной ссылки.
Если необходимо изменить ссылки на ячейки нескольких формул, этот способ будет утомительным и трудоемким.
Измените абсолютную ссылку на относительную ссылку с кодом VBA
С помощью кода VBA вы можете быстро изменить диапазон ссылок на ячейки формул с абсолютных ссылок на относительные за один раз.
1. Выберите диапазон, который вы хотите изменить.
2. Нажмите Застройщик > Визуальный Бейсик или вы можете нажать Alt + F11, откроется новое окно Microsoft Visual Basic для приложений, щелкните Вставить > Модули, и введите следующий код в Модули:
VBA: преобразование абсолютной ссылки в относительную.
Sub ConverFormulaReferences() 'Updateby20140603 Dim Rng As Range Dim WorkRng As Range Dim xName As Name Dim xIndex As Integer On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Set WorkRng = WorkRng.SpecialCells(xlCellTypeFormulas) xIndex = Application.InputBox("Change formulas to?" & Chr(13) & Chr(13) _ & "Absolute = 1" & Chr(13) _ & "Row absolute = 2" & Chr(13) _ & "Column absolute = 3" & Chr(13) _ & "Relative = 4", xTitleId, 1, Type:=1) For Each Rng In WorkRng Rng.Formula = Application.ConvertFormula(Rng.Formula, XlReferenceStyle.xlA1, XlReferenceStyle.xlA1, xIndex) Next End Sub 3. Затем нажмите Нажмите кнопку, чтобы запустить код, и появится окно подсказки для выбора диапазона для преобразования, затем нажмите OK, и откроется другое диалоговое окно с запросом, какой тип вы хотите использовать. Тогда вы сможете выбрать нужный вам тип. В этом примере я вставлю 4. См. Скриншоты:
 |
 |
 |
4. Затем нажмите OK. Все выбранные абсолютные ссылки были заменены на относительные ссылки в формуле.
Это многофункциональный код VBA с этим кодом; вы также можете изменить абсолютную ссылку, изменить абсолютную строку или изменить абсолютный столбец.
Быстро изменить абсолютную ссылку на относительную с помощью Kutools for Excel
Kutools for Excel: с более чем 100 удобными надстройками Excel, которые можно бесплатно попробовать без ограничений в течение 30 дней. Получить сейчас
Kutools for Excel позволяют быстро и легко изменить абсолютную ссылку на относительную или наоборот. Пожалуйста, сделайте следующее:
Шаг 1. Выберите диапазон, содержащий формулы, ссылки на ячейки которых вы хотите изменить на листе.
Шаг 2. Нажмите Кутулс > Преобразовать ссылки. Он будет отображать Преобразование ссылок на формулы диалоговое окно. Смотрите скриншот:
 |
| |
 |
Шаг 3. Проверьте К относительной и нажмите Ok or Применить в Справочник по формулам преобразования диалог. Это изменит абсолютную ссылку на относительную.
Если вы хотите изменить ссылки на ячейки на абсолютные столбцы или абсолютные строки, установите флажок В столбец абсолютный Вариант или Гребать абсолютное опцию.
Для получения более подробной информации о Преобразовать ссылку, Пожалуйста, посетите Описание функции Convert Reference.