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