Часто пользователи сталкиваются с необходимостью заблокировать ячейку в формуле. Например, это происходит в ситуациях, когда вам нужно скопировать формулу, но так, чтобы ссылка не перемещала то же количество ячеек вверх и вниз, как она была скопирована из исходного положения.
В этом случае вы можете заблокировать ссылку на ячейку в Excel. Кроме того, это можно сделать несколькими способами одновременно. Давайте подробнее рассмотрим, как этого добиться.
Что такое ссылка Excel
Лист состоит из ячеек. Каждый из них содержит определенную информацию. Другие ячейки могут использовать его в расчетах. Но как они узнают, откуда брать данные? Это помогает им строить ссылки.
Каждая ссылка обозначает ячейку с использованием буквы и числа. Буква представляет столбец, а число — строку.
Ссылки бывают трех типов: абсолютные, относительные и смешанные. Второй установлен по умолчанию. Абсолютная ссылка — это ссылка, которая имеет фиксированный адрес столбца и столбца. Следовательно, смешанный — это тот, в котором столбец или строка закреплены отдельно.
Метод 1
Чтобы сохранить адреса столбцов и строк, необходимо выполнить следующие действия:
- Щелкните ячейку, содержащую формулу.
- Щелкните строку формул на нужной нам ячейке.
- Нажмите F4.
В результате ссылка на ячейку полностью изменится. Его можно будет узнать по характерному символу доллара. Например, если вы щелкните ячейку B2, а затем нажмите F4, ссылка будет выглядеть так: $ B $ 2.
Что означает знак доллара перед адресом ячейки?
- Если он находится перед буквой, это означает, что ссылка на столбец остается неизменной независимо от того, куда была перемещена формула.
- Если знак доллара стоит перед числом, это означает, что строка заблокирована.
Метод 2
Этот способ почти такой же, как и предыдущий, только нужно дважды нажать F4, например, если у нас была ячейка B2, потом она станет B $ 2. Проще говоря, таким образом нам удалось исправить строку. Это изменит букву столбца.
это очень удобно, например, в таблицах, где нужно вывести содержимое второй ячейки сверху в нижнюю. Вместо того, чтобы запускать эту формулу много раз, просто заморозьте строку и позвольте изменить столбец.
Метод 3
Это полностью аналогично предыдущему способу, вам просто нужно трижды нажать клавишу F4. Таким образом, только ссылка на столбец будет абсолютной, а строка останется фиксированной.
Метод 4
Предположим, у нас есть абсолютная ссылка на ячейку, но здесь мы должны были сделать ее относительной. Для этого нажмите клавишу F4 столько раз, чтобы на ярлыке не было знаков $. Затем он станет относительным, и когда вы переместите или скопируете формулу, изменится и адрес столбца, и адрес строки.
Закрепление ячеек для большого диапазона
Мы видим, что описанные выше методы не представляют сложности для исполнения. Но задачи специфические. И, например, что делать, если у нас сразу несколько десятков формул, ссылки на которые нужно преобразовать в абсолютные.
К сожалению, стандартными методами Excel этого добиться не удастся. Для этого вам необходимо использовать специальную надстройку под названием VBA-Excel. Он содержит множество дополнительных функций, которые позволяют намного быстрее выполнять стандартные задачи с Excel.
Он включает более сотни пользовательских функций и 25 различных макросов, а также регулярно обновляется. Это улучшает производительность практически по всем параметрам:
- Ячейки.
- Макро.
- Функции разного типа.
- Ссылки и массивы.
В частности, эта надстройка позволяет блокировать ссылки в большом количестве формул одновременно. Для этого выполните следующие действия:
- Выберите интервал.
- Откройте вкладку VBA-Excel, которая появится после установки.
- Откройте меню «Функции», где находится опция «Формулы привязки».
- После этого появится диалоговое окно, в котором нужно указать требуемый параметр. Этот аддон позволяет добавлять столбец и столбец по отдельности вместе, а также удалять существующий вывод с помощью пакета. После выбора необходимого параметра с помощью соответствующей радиокнопки, необходимо подтвердить действия, нажав «ОК».
Пример
Для наглядности возьмем пример. Допустим, у нас есть информация о стоимости товара, его общем количестве и выручке от реализации. И перед нами стоит задача сделать так, чтобы таблица, исходя из количества и стоимости, автоматически определяла, сколько денег вы заработали, без вычета убытков.
В нашем примере для этого нужно ввести формулу = B2 * C2. Как видите, это довольно просто. Его пример очень легко использовать для описания того, как исправить адрес ячейки или ее отдельного столбца или строки.
Конечно, в этом примере вы можете попробовать перетащить формулу вниз с помощью индикатора автозаполнения, но в этом случае ячейки будут изменены автоматически. Итак, в ячейке D3 будет другая формула, в которой числа будут заменены соответственно на 3. Далее по схеме — D4 — формула примет вид = B4 * C4, D5 — точно так же , но с цифрой 5 и так далее.
Если так и должно быть (в большинстве случаев это работает), то проблем нет. Но если вам нужно исправить формулу в ячейке, чтобы она не менялась при перетаскивании, это будет немного сложнее.
Предположим, нам нужно определить выручку в долларах. Поместим его в ячейку B7. Сделаем небольшую ностальгию и укажем стоимость 35 рублей за доллар. Соответственно, для определения долларовой выручки необходимо сумму в рублях разделить на курс доллара.
Вот как это выглядит в нашем примере.
Если мы попытаемся написать формулу, аналогичную предыдущей версии, мы проиграем. Точно так же формула изменится на подходящую. В нашем примере это будет выглядеть так: = E3 * B8. Отсюда мы видим, что первая часть формулы стала E3, и мы поставили перед собой эту задачу, но нам не нужно менять вторую часть формулы на B8. Следовательно, мы должны преобразовать ссылку в абсолютную. Вы можете сделать это, не нажимая клавишу F4, просто поставив знак доллара.
После превращения ссылки на вторую ячейку в абсолютную она была защищена от изменений. Теперь вы можете безопасно перетащить его с помощью маркера автозаполнения. Все записанные данные останутся неизменными, независимо от положения формулы, а неустановленные данные будут гибко изменяться. Во всех ячейках выручка в рублях, указанная в этой строке, будет разделена на один и тот же курс доллара.
Сама формула будет выглядеть так:
= D2 / $ B $ 7
Внимание! Мы указали два знака доллара. Это показывает программу для захвата как столбца, так и строки.
Ссылки на ячейку в макросах
Макрос — это процедура, которая автоматизирует действия. В отличие от стандартных функций Excel, макрос позволяет сразу указать конкретную ячейку и выполнить определенные действия всего в нескольких строках кода. Полезно для пакетной обработки информации, например, если нельзя установить надстройки (например, используется корпоративный неперсональный компьютер).
Во-первых, вам нужно понять, что ключевое понятие макроса — это объекты, которые могут содержать другие объекты. Объект Workbooks отвечает за электронную книгу (то есть документ). Включает объект Sheets, который представляет собой набор всех листов в открытом документе.
Следовательно, ячейки являются объектом Cells. Содержит все ячейки определенного листа.
Каждый объект квалифицируется аргументами в круглых скобках. В случае ячеек ссылки на них даются в этом порядке. Сначала указывается номер строки, затем номер столбца или буква (допустимы оба формата).
Например, строка кода, содержащая ссылку на ячейку C5, будет выглядеть так:
Рабочие папки («Book2.xlsm») Листы («Sheet2») Ячейки (5, 3)
Рабочие папки («Book2.xlsm») Листы («Sheet2») Ячейки (5, «C»)
Вы также можете получить доступ к ячейке с помощью объекта Range. В общем, мы намерены предоставить ссылку на диапазон (элементы которого, кстати, также могут быть абсолютными или относительными), но вы можете просто дать ему имя ячейки в том же формате, что и документ Excel.
В этом случае линия будет выглядеть так.
Рабочие папки («Book2.xlsm») Листы («Sheet2») Диапазон («C5»)
Может показаться, что этот вариант более удобен, но преимущество первых двух вариантов заключается в том, что вы можете использовать переменные в круглых скобках и давать ссылку, которая больше не является абсолютной, а что-то вроде относительной, которая будет зависеть от результатов расчеты.
Таким образом, макросы можно эффективно использовать в программах. Фактически, все ссылки на ячейки или диапазоны здесь будут абсолютными и, следовательно, также могут использоваться для их захвата. Правда, это не так удобно. Использование макросов может быть полезно при написании сложных программ с большим количеством шагов в алгоритме. В общем, стандартный способ использования абсолютных или относительных ссылок намного удобнее.
Выводы
Мы понимаем, что такое ссылка на ячейку, как она работает и для чего предназначена. Мы поняли разницу между абсолютными и относительными ссылками и поняли, что нужно сделать, чтобы преобразовать один тип в другой (другими словами, исправить адрес или отсоединить его). Мы разобрались, как это сделать сразу с большим количеством значений. Теперь у вас есть возможность использовать эту функцию в нужных ситуациях.
