Ссылка на ячейки 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) и добавляем туда код макроса:
| 1
2 3 4 5 6 7 |
Sub ChangeCellStyleInFormulas()
Dim MyCell As Range On Error Resume Next ‘Обработка ошибки, если рассматриваемый диапазон ячеек не содержит формул For Each MyCell In Selection.SpecialCells(xlCellTypeFormulas) ‘Цикл для каждой ячейки диапазона содержащая формулу MyCell.Formula = Application.ConvertFormula(MyCell.Formula, xlA1, xlA1, xlAbsolute) ‘Меняем тип ссылки Next End Sub |
Как изменить полученную абсолютную или относительную ссылку в программе 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 «=», в квадратных скобках пишем название книги с расширением, затем название листа из этой книги, ставим «!» и адрес ячейки.
Книга Excel, на которую мы ссылаемся, должна быть открыта.
