Как закрепить ячейку в формуле Excel

Часто пользователи сталкиваются с необходимостью закрепить ячейку в формуле. Например, это происходит в ситуациях, когда вы хотите скопировать формулу, но чтобы ссылка не перемещалась вверх и вниз на то же количество ячеек, на которое она была скопирована из исходного места.

В этом случае вы можете исправить ссылку на ячейку в Excel. И сделать это можно сразу несколькими способами. Давайте подробнее рассмотрим, как достичь этой цели.

Что такое ссылка Excel

Лист состоит из клеток. Каждый из них содержит конкретную информацию. Другие ячейки могут использовать его в вычислениях. Но как они поймут, откуда брать данные? Это помогает им создавать ссылки.

Каждая ссылка обозначает ячейку с одной буквой и одной цифрой. Буква представляет столбец, а число представляет строку. 

Существует три типа ссылок: абсолютные, относительные и смешанные. Второй установлен по умолчанию. Абсолютная ссылка — это ссылка, которая имеет фиксированный адрес как столбца, так и столбца. Соответственно, смешанным считается тот, где закреплен либо отдельный столбец, либо строка.

1 метод

Чтобы сохранить адреса столбцов и строк, выполните следующие действия:

1. Нажмите на ячейку, содержащую формулу.
2. Нажмите на строку формул той ячейки, которая нам нужна.
3. Нажмите F4.

В результате ссылка на ячейку изменится на абсолютную. Его можно узнать по характерному знаку доллара. Например, если вы щелкните ячейку B2, а затем нажмите F4, ссылка будет выглядеть так: $B$2.

Что означает знак доллара перед частью адреса ячейки?

1. Если он помещен перед буквой, это означает, что ссылка на столбец остается неизменной независимо от того, куда была перемещена формула.
2. Если знак доллара находится перед числом, это означает, что строка закреплена. 

2 метод

Этот способ практически аналогичен предыдущему, только вам нужно дважды нажать F4. например, если у нас была ячейка B2, то после этого она станет B$2. Говоря простыми словами, таким образом нам удалось закрепить линию. При этом буква столбца изменится.

Это очень удобно, например, в таблицах, где нужно вывести в нижнюю ячейку содержимое второй сверху ячейки. Вместо того, чтобы повторять такую ​​формулу много раз, достаточно зафиксировать строку и позволить столбцу измениться.

3 метод

Это полностью то же самое, что и предыдущий способ, только вам нужно трижды нажать клавишу F4. Тогда только ссылка на столбец будет абсолютной, а строка останется фиксированной.

4 метод

Предположим, у нас есть абсолютная ссылка на ячейку, но здесь нужно было сделать ее относительной. Для этого нажмите клавишу F4 столько раз, чтобы в ссылке не осталось знаков $. Тогда она станет относительной, и при перемещении или копировании формулы изменится и адрес столбца, и адрес строки.

Закрепляющие ячейки для большого диапазона

Мы видим, что описанные выше способы не представляют никакой сложности в выполнении. Но задачи конкретные. А, например, что делать, если у нас имеется сразу несколько десятков формул, ссылки в которых нужно превратить в абсолютные. 

К сожалению, стандартные методы Excel не достигнут этой цели. Для этого вам необходимо использовать специальный аддон под названием VBA-Excel. Он содержит множество дополнительных функций, которые позволяют гораздо быстрее выполнять стандартные задачи с Excel.

Он включает в себя более сотни пользовательских функций и 25 различных макросов, а также регулярно обновляется. Он позволяет улучшить работу практически по любому аспекту:

1. Ячейки.
2. Macro.
3. Функции разных типов.
4. Ссылки и массивы.

В частности, эта надстройка позволяет исправлять ссылки сразу в большом количестве формул. Для этого необходимо выполнить следующие шаги:

1. Выберите диапазон.
2. Откройте вкладку VBA-Excel, которая появится после установки. 
3. Откройте меню «Функции», где находится опция «Блокировать формулы».
   

   

4. Далее появится диалоговое окно, в котором необходимо указать необходимый параметр. Этот аддон позволяет закрепить столбец и столбец отдельно, вместе, а также удалить уже существующее закрепление с помощью пакета. После того, как необходимый параметр выбран с помощью соответствующего переключателя, необходимо подтвердить свои действия, нажав «ОК».

Пример

Давайте рассмотрим пример, чтобы было более понятно. Допустим, у нас есть информация, описывающая стоимость товара, его общее количество и выручку от реализации. И перед нами стоит задача сделать так, чтобы таблица по количеству и стоимости автоматически определяла, сколько денег нам удалось заработать без вычета потерь.

В нашем примере для этого нужно ввести формулу =Б2*С2. Как видите, это довольно просто. На ее примере очень легко описать, как можно исправить адрес ячейки или ее отдельного столбца или строки. 

Конечно, в этом примере можно попробовать перетащить формулу вниз с помощью маркера автозаполнения, но в этом случае ячейки будут автоматически изменены. Итак, в ячейке D3 появится еще одна формула, где цифры будут заменены соответственно на 3. Далее по схеме – D4 – формула примет вид = B4*C4, D5 – аналогично, но с номер 5 и так далее.

Если это необходимо (в большинстве случаев так и получается), то проблем нет. А вот если вам нужно закрепить формулу в одной ячейке, чтобы она не менялась при перетаскивании, то это будет несколько сложнее. 

Предположим, нам нужно определить долларовую выручку. Поместим это в ячейку B7. Давайте немного поностальгируем и укажем стоимость 35 рублей за доллар. Соответственно, чтобы определить выручку в долларах, необходимо сумму в рублях разделить на курс доллара.

Вот как это выглядит в нашем примере.

Если мы, аналогично предыдущему варианту, попытаемся прописать формулу, то у нас ничего не получится. Аналогично формула изменится на подходящую. В нашем примере это будет так: =E3*B8. Отсюда мы можем видеть. что первая часть формулы превратилась в Е3, и мы ставим перед собой эту задачу, а вторую часть формулы менять на В8 нам не нужно. Поэтому нам необходимо превратить ссылку в абсолютную. Сделать это можно не нажимая клавишу F4, просто поставив знак доллара.

После того как мы превратили ссылку на вторую ячейку в абсолютную, она стала защищена от изменений. Теперь вы можете безопасно перетаскивать его с помощью маркера автозаполнения. Все фиксированные данные останутся неизменными независимо от положения формулы, а незафиксированные данные будут гибко меняться. Во всех ячейках выручка в рублях, описанная в этой строке, будет разделена на один и тот же курс доллара.

Сама формула будет выглядеть так:

=D2/$B$7

Внимание! Мы указали два знака доллара. Таким образом мы показываем программе, что нужно зафиксировать и столбец, и строку.

Ссылки на ячейки в макросах

Макрос — это подпрограмма, позволяющая автоматизировать действия. В отличие от стандартного функционала Excel, макрос позволяет сразу задать конкретную ячейку и выполнить определенные действия всего за несколько строк кода. Полезно для пакетной обработки информации, например, если нет возможности установить дополнения (например, используется корпоративный компьютер, а не личный).

Для начала нужно понять, что ключевое понятие макроса — это объекты, которые могут содержать в себе другие объекты. За электронную книгу (то есть документ) отвечает объект Workbooks. Он включает объект Sheets, который представляет собой коллекцию всех листов открытого документа. 

Соответственно, ячейки — это объект Cells. Он содержит все ячейки конкретного листа.

Каждый объект уточняется аргументами в скобках. В случае ячеек на них ссылаются в этом порядке. Сначала указывается номер строки, а затем номер столбца или буква (оба формата приемлемы).

Например, строка кода, содержащая ссылку на ячейку C5, будет выглядеть так:

Рабочие книги("Book2.xlsm").Sheets("List2").Cells(5, 3)

Рабочие книги("Book2.xlsm").Sheets("List2").Cells(5, "C")

Вы также можете получить доступ к ячейке с помощью объекта Аккуратный. В целом он предназначен для того, чтобы дать ссылку на диапазон (элементы которого, кстати, тоже могут быть абсолютными или относительными), но можно просто дать имя ячейки, в том же формате, что и в документе Excel.

В этом случае линия будет выглядеть так.

Рабочие книги("Book2.xlsm").Sheets("List2").Range("C5")

Может показаться, что этот вариант удобнее, но преимущество первых двух вариантов в том, что вы можете использовать переменные в скобках и давать ссылку уже не абсолютную, а что-то вроде относительной, от которой будут зависеть результаты расчеты.

Таким образом, макросы можно эффективно использовать в программах. По сути, все ссылки на ячейки или диапазоны здесь будут абсолютными, а значит, их тоже можно зафиксировать. Правда, это не так удобно. Использование макросов может быть полезно при написании сложных программ с большим количеством шагов алгоритма. В целом стандартный способ использования абсолютных или относительных ссылок гораздо удобнее. 

Выводы

Мы разобрались, что такое ссылка на ячейку, как она работает, для чего нужна. Мы поняли разницу между абсолютными и относительными ссылками и разобрались, что нужно сделать, чтобы превратить один тип в другой (простыми словами, исправить адрес или открепить его). Мы разобрались, как можно сделать это сразу с большим количеством значений. Теперь у вас есть возможность использовать эту функцию в нужных ситуациях.