В Excel есть два типа ссылок: абсолютные и относительные. Эти ссылки ведут себя по-разному при копировании и заполнении ячеек. Относительные ссылки изменяются, когда вы копируете формулу из одной ячейки в другую, тогда как абсолютные ссылки, с другой стороны, не изменяются независимо от того, куда вы их копируете.
Относительные ссылки в Excel
По умолчанию все ссылки в Excel являются относительными. Когда вы копируете ссылку из одной ячейки в другую, она автоматически изменяется относительно позиции столбца и строки новой ячейки на ячейку, из которой вы скопировали ссылку. Например, если скопировать формулу =A1+B1 из строки 1 в строку 2, формула изменится на =A2+B2. Относительные ссылки полезны, когда нам нужно повторить одни и те же вычисления для нескольких столбцов и строк.
Как создать и скопировать формулу с относительными ссылками
В приведенном ниже примере мы хотим создать формулу, которая поможет рассчитать окончательный счет за заказ в ресторане. Мы хотим рассчитать сумму к оплате за каждое блюдо в столбце «Всего», исходя из его стоимости за единицу и количества. Для этого создадим формулу с относительной ссылкой и скопируем ее во все строки таблицы. Для этого мы выполним следующие шаги:
- Выделим первую ячейку в столбце «Итого», в которой будет создана наша формула:
- Подставим в ячейку D2 формулу, умножающую цену блюда на количество: =B2*C2.
- Нажмите клавишу «Ввод» на клавиатуре. Формула рассчитает, и вы увидите ее результат в ячейке D2.
- Удерживая левую кнопку мыши, перетащите ячейку D2 за нижний правый угол через весь диапазон ячеек D3:D12. Таким образом, он скопирует формулу из ячейки D2 и перенесет ее в каждую ячейку диапазона.
- Чтобы убедиться, что формулы скопированы правильно, дважды щелкните любую ячейку в диапазоне, в котором была растянута формула, и вы увидите формулу умножения ячеек.
Абсолютные ссылки в Excel
Часто при расчетах нам нужно, чтобы при копировании формул ссылки на ячейки не менялись. В отличие от относительных ссылок, абсолютные ссылки позволяют исправлять ячейки в определенных строках и столбцах во время вычислений, делая процесс вычислений в таблицах проще и эффективнее.
Знак доллара «$» используется для создания абсолютной ссылки. С его помощью вы можете вносить изменения в столбец, строку или во все сразу:
-
$A$2: столбец и строка не меняются при копировании формулы;
-
A$2: При копировании формулы не меняется только строка;
-
$A2: столбец не изменяется при копировании формулы.
Используя абсолютные ссылки в Excel, вы можете быстро изменить параметры закрепления столбцов и строк с помощью клавиши F4. Для этого дважды щелкните ячейку с формулой, затем левой кнопкой мыши установите курсор на значение ячейки и с помощью клавиши F4 установите фиксацию строки и столбца.
Как создать и скопировать формулу с абсолютными ссылками
В нашем примере мы будем использовать ячейку E1 — 18% в качестве значения НДС для расчета налога на имущество в столбце D. Для правильного расчета нам нужно будет использовать абсолютную ссылку $E$1 в нашей формуле, так как это важно для Нам известно, что стоимость каждого товара умножается на ставку НДС, указанную в ячейке E1. Далее мы увидим, как мы это будем делать:
- Выбираем ячейку, в которую мы хотим вставить формулу для расчета налога. В нашем примере это ячейка D3.
- Напишем формулу, вычисляющую сумму налога для каждого товара с учетом его стоимости и количества =(B3*C3)*$E$1.
- Распространим полученную формулу на все ячейки в диапазоне D4:D13.
- Дважды щелкните любую ячейку в диапазоне D4:D13 и убедитесь, что формула работает правильно. Важно убедиться, что вы правильно указали ячейку $E$1 в абсолютном формате.
Как создать ссылки на другие листы в Excel
Часто нам нужно использовать в расчетах данные с разных листов файла Excel. Для этого при создании ссылки на ячейку с другого листа необходимо использовать название листа и завершающий восклицательный знак (!). Например, если вы хотите создать ссылку на ячейку A1 на Листе 1, ссылка на эту ячейку будет выглядеть так:
=Лист1!A1
ВАЖНЫЙ! Если в названии листа, на который вы ссылаетесь, есть пробелы, то название этого листа в ссылке должно быть заключено в кавычки (»). Например, если имя вашего листа — Окончательный бюджет, ссылка на ячейку A1 будет выглядеть так:
‘Окончательный бюджет’!A1
В приведенном ниже примере мы хотим добавить в таблицу ссылку на ячейку, в которой уже производились расчеты между двумя листами файла Excel. Это позволит нам использовать одно и то же значение на двух разных листах без перезаписи формулы или копирования данных между листами. Для этого мы выполним следующие шаги:
- Давайте выделим ячейку, на которую мы хотим сослаться, и обратим внимание на название листа. В нашем случае это ячейка E14 на вкладке «Меню»:
- Переходим на лист и выбираем ячейку, в которую хотим поставить ссылку. В нашем примере это ячейка B2.
- В ячейку B2 введите формулу, которая ссылается на ячейку E14 листа «Меню»: =Menu!E14
- Нажимаем клавишу «Enter» на клавиатуре и видим в ячейке B2 значение ячейки E14 листа «Меню».
Если позже вы измените имя листа, на который ссылались, система автоматически обновит формулу.
Узнайте больше полезных советов по работе со списками данных и функциями в Excel в практическом курсе от новичка до мастера Excel. Смело регистрируйтесь по ссылке!