Абсолютная ссылка в Excel: что это, как создать, чем различается от относительной
Ссылка на ячейки Excel, вроде бы просто она ни казалась, сбивает с толку почти всех юзеров. Как определяется адресок? Что такое абсолютная ссылка в Excel, чем она различается от относительной и когда следует употреблять каждую из их?
Что такое абсолютная ссылка в Excel
Ссылки Excel бывают относительными и абсолютными. Разглядим подробнее. Относительная ссылка — это ссылка, которая автоматом меняется при копировании либо перемещении формулы в другую ячейку.
Абсолютная ссылка — это ссылка в Excel на определённую (фиксированную) ячейку. Обозначается знаком $, к примеру, $А$1. При копировании данная абсолютная ссылка Excel не меняется.
Смешанная ссылка — это ссылка, которая соединяет внутри себя относительную и абсолютную ссылку. В смешанных ссылках быть может полностью адресуемый столбец и относительно адресуемая строчка $А1, либо напротив А$1.
При копировании либо перемещении формулы Excel абсолютная часть ссылки остается постоянной, а относительная изменяется в зависимость от месторасположения.
Как сделать и скопировать формулу с абсолютными ссылками в Excel
В нашем примере мы будем употреблять в ячейке E1 – 18% как значение НДС для расчета налога на продукты в колонке D. Для правильного расчета нам будет нужно употреблять абсолютную ссылку $E$1 в нашей формуле Excel, потому что нам принципиально, чтоб стоимость всякого продукта перемножалась на ставку НДС, обозначенную в ячейке E1. Ниже разглядим как мы, будем это созодать:
- Выделим ячейку Excel, в которую мы желаем вставить формулу для расчета налога. В нашем примере это D3.
- Напишем формулу Excel, рассчитывающую сумму налога для всякого продукта, с учетом его цены и количества =(B3*C3)*$E$1.
- Протянем полученную формулу на все ячейки в спектре D4:D13.
- Два раза кликните на любом элементе из спектра D4:D13и удостоверьтесь, что формула сработала корректно. Принципиально убедиться, что вы верно указали ссылку на $E$1 в абсолютном формате.
Как создать абсолютную либо относительную ссылку при помощи жаркой клавиши
Желаете стремительно создать абсолютную либо относительную ссылку в программке Excel? Для этого существует жгучая кнопка F4. Нажав на нее 1 раз, когда активна формула вы получаете абсолютную ссылку, нажав еще по разу получите смешанные ссылки.
Так при помощи клавиши F4 мы сможем стремительно поменять тип для одной ссылки, но никак не для огромного спектра. Потому чтоб сделать эту операцию вручную для огромного количества ссылок, будет нужно иной метод, в связи с чем обратимся к помощи макроса, который автоматизирует процесс и значительно убыстрит время выполнения работы.
Перебегаем в редактор VBA (для резвого перехода жмем Alt + F11), создаем новейший модуль (щелкаем правой клавишей мыши в панели проектов и избираем Insert -> Module) и добавляем туда код макроса:
Dim MyCell As Range
On Error Resume Next ‘Обработка ошибки, если рассматриваемый спектр ячеек не содержит формул
For Each MyCell In Selection.SpecialCells(xlCellTypeFormulas) ‘Цикл для каждой ячейки спектра содержащая формулу
MyCell.Formula = Application.ConvertFormula(MyCell.Formula, xlA1, xlA1, xlAbsolute) ‘Меняем тип ссылки
Как поменять полученную абсолютную либо относительную ссылку в программке Excel
Чтоб поменять адресок элемента в имеющейся формуле Excel, сделайте последующие деяния:
- Изберите клеточку, содержащую формулу, и нажмите F2, чтоб войти в режим редактирования, либо два раза щелкните саму ячейку. Это выделит каждую ячейку либо спектр, на который ссылается формула, иным цветом.
- Чтоб поменять адресок, сделайте одно из последующих действий:
- Изберите адресок в формуле Excel и вручную введите новейший заместо него.
- Выбрав ссылку, с помощью мышки укажите заместо нее иной адресок либо спектр на листе Excel.
- Чтоб включить больше либо меньше ячеек Excel, перетащите его правый нижний угол:
Нажмите Enter и проверьте итог. Если же что-то не вышло, описывайте делему на форуме.
Как создать в Excel ссылку на иной лист
Ссылка на иной лист в Excel понадобится, если Для вас необходимо употреблять в расчетах для формулы значения, которые находятся на остальных листах документа Excel.
В формуле Excel она будет смотреться последующим образом: Лист1!А1 – заглавие листа, символ восклицания, адресок ячейки. Если в заглавии листа употребляются пробелы, то его необходимо взять в одинарные кавычки: ‘Итоговые суммы’ – ‘Итоговые суммы’!А1.
К примеру, рассчитаем значение НДС для продуктов. Таблица, в какой будет рассчитываться формула, находится на Листе1, значение НДС находится на листе с заглавием Все константы.
На листе Все константы для расчета формулы нам нужно будет значение, записанное в ячейке В1.
Возвращаемся на Лист1. В ячейку С6 пишем формулу для расчета НДС: ставим «=», потом выделяем ячейку В6 и делаем ссылку на ячейку В1 с другого листа. Чтоб формула верно посчитала значения в остальных ячейках, делаем ссылку на ячейку В1 абсолютной: $В$1, и растягиваем ее по столбцу.
Если поменять заглавие листа Все константы на Все константы1111, то оно автоматом обменяется и в формуле. Буквально также, если на листе Все константы поменять значение в ячейке В1 с 20% на 22%, то формула будет пересчитана.
Для того чтоб сослаться на другую книжку Excel в формуле, возьмите ее заглавие в квадратные скобки. К примеру, создадим ссылку в ячейке А1 в книжке с заглавием Книга1 на ячейку А3 из книжки с заглавием Ссылки. Для этого ставим в ячейку А1 «=», в квадратных скобках пишем заглавие книжки с расширением, потом заглавие листа из данной для нас книжки, ставим «!» и адресок ячейки.
