Одной из основных функций Excel является работа с формулами, которые могут содержать как конкретные числовые значения, так и ссылки на другие ячейки таблицы. Ссылки могут быть относительными или абсолютными. В этой статье вы узнаете, чем они отличаются и почему они полезны.
Относительная ссылка - это ссылка, в которой координаты ячейки изменяются при копировании формулы в другую ячейку.
Абсолютная ссылка - это ссылка, в которой координаты ячейки фиксированы и не изменяются при копировании.
Относительные ссылки
См. также "Как переставить строки в Excel".
Чтобы было понятнее, давайте разобьем примеры и начнем с относительных ссылок как более распространенных.
Допустим, у нас есть таблица, заполненная столбцами цены за единицу продукции и количества проданного товара. Наша задача - подсчитать сумму по каждому пункту в соответствующей колонке.
Вот что нам нужно сделать:
- Перейдите в самую верхнюю ячейку получившегося столбца (не считая заголовка таблицы), поставьте знак "равно" ('=') и запишите там формулу: =B2*C2.
- Когда выражение будет готово, нажмите Enter на клавиатуре, и результат появится в ячейке с формулой.
- Это позволяет выполнять аналогичные вычисления в других ячейках столбца. Конечно, если таблица небольшая, можно перейти к следующей ячейке и выполнить шаги 1-2 выше. Однако что делать, если данных слишком много? В конце концов, ручной ввод формул во все ячейки занял бы много времени. На этот случай в Excel есть очень полезная функция, позволяющая копировать формулы в другие ячейки. Для этого нужно подвести курсор к правому нижнему углу ячейки результата и, когда появится маленький черный крестик (маркер заполнения), нажать левую кнопку мыши и перетащить его вниз, тем самым скопировав формулу в другие ячейки.
- Когда вы отпустите кнопку мыши, вы получите результаты во всех ячейках столбца, на которые вы растянули формулу.
- Если, например, перейти к ячейке D3, то в строке формул вы увидите следующие формулы: =B3*C3.
Это означает, что при копировании изменились координаты ячеек, задействованных в исходной формуле, которую мы написали в ячейке D2. Это результат того, что ссылки являются относительными.
Возможные ошибки при работе с относительными ссылками
Несомненно, относительные ссылки значительно упрощают многие вычисления в Excel. Однако они не всегда помогают решить проблему.
Например, нам нужно рассчитать долю каждого товара в общем объеме продаж.
- Мы переходим к первой ячейке столбца для расчета, где вводим формулу: =D2/D13.
- Нажмите Enter, чтобы получить результат. После копирования формулы в другие ячейки столбца вместо результата мы видим следующую ошибку: #DEL/0!
Проблема в том, что поскольку все ссылки на ячейки в формуле, которую мы скопировали, являются относительными, координаты в последующих ячейках сместились. То есть для ячейки E3 формула выглядит следующим образом: =D3/D14. Но, как мы видим, ячейка D14 пуста, поэтому программа выдает ошибку, сообщая нам, что мы не можем делить на 0.
Поэтому нам нужно написать формулу таким образом, чтобы при копировании координаты целочисленной ячейки (D13) оставались неизменными. Абсолютные ссылки помогают нам в этом.
Абсолютные ссылки
См. также "Функция ИНДЕКС в Excel: примеры, использование с функцией ПОИСК".
Как мы уже видели, абсолютные ссылки позволяют зафиксировать координаты ячейки. Давайте посмотрим, как это работает в нашем примере.
По умолчанию все ссылки в формулах Excel являются относительными, поэтому, чтобы сделать их абсолютными, выполните следующие действия:
- Сначала мы запишем формулу в ее обычном виде в нужной ячейке. В нашем случае это выглядит следующим образом: =D2/D13.
- Когда формула будет готова, не спешите нажимать Enter. Теперь нам нужно определить координаты ячейки D13. Для этого введите "$" перед именем столбца и номером строки. В качестве альтернативы можно просто нажать F4 на клавиатуре после ввода адреса (курсор может находиться до, после или внутри координат). В результате формула должна выглядеть следующим образом: D2/$D$13.
- Теперь вы можете нажать Enter, чтобы напечатать результат в ячейке.
- Осталось только скопировать формулу в нижние строки с помощью маркера заливки. На этот раз, поскольку мы зафиксировали ячейку с суммой, результат появится и в других ячейках.
Смешанные ссылки
Помимо рассмотренных выше ссылок, Excel также предусматривает смешанные ссылки - когда мы копируем формулу, изменяется одна из координат ячейки (номер столбца или строки).
- Если мы запишем ссылку как "$G5", это означает, что строка будет меняться, а столбец будет постоянным.
- Если мы укажем "G$5", то в этом случае номер строки будет фиксированным, а столбец будет меняться.
Другими словами, в принципе, решение вышеуказанной проблемы определения доли каждого товара в общем объеме продаж возможно только путем фиксации номера строки, поскольку столбец все равно не изменится, даже в случае относительной ссылки.
Примечание: вместо ручного ввода символов '$', тип ссылки (абсолютная, относительная, смешанная) можно задать с помощью функциональной клавиши F4. Курсор должен находиться в пределах координат ячейки, на которую вы хотите воздействовать.
Заключение
С помощью относительных, абсолютных и смешанных ссылок в Excel выполняется огромное количество различных вычислений. Поэтому для успешной работы в программе важно досконально их понять, чтобы выполнять последующие задания максимально эффективно и результативно.
