Check-moscow.ru

Финансы и учет
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как заблокировать сразу все ссылки на ячейки в формулах в Excel?

Ссылки в Excel

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

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

Рассмотрим простой пример. Вам нужно сложить два числа. Это можно легко сделать, набрав «=» в свободной ячейке (например, внизу), а затем используя знак «+» для обозначения добавляемых ячеек. Если числа большие, лучше всего складывать их с помощью функции sum, указывая сразу весь диапазон суммы.

Простое суммирование

Как легко заметить, вместо чисел мы делаем ссылки на свернутые ячейки или на весь диапазон сразу. Excel использует ссылки вместо чисел. Чтобы они работали правильно, следует различать абсолютные и относительные ссылки.

Виды фиксации

Сразу следует сказать, что типы фиксаций в Excel могут быть совершенно разными. В целом их можно разделить на три большие группы:

  1. Заморозить адрес;
  2. Блокировать ячейки;
  3. Защитить элементы от редактирования.

Когда адрес заморожен, ссылка на ячейку не изменяется при копировании, т.е. она больше не является относительной. Блокировка ячеек позволяет им постоянно отображаться на экране, независимо от того, насколько пользователь прокручивает страницу вниз или вправо. Защита элементов от редактирования блокирует любые изменения данных в указанном элементе. Давайте рассмотрим каждый из этих вариантов подробнее.

Способ 1: заморозка адреса

Первый шаг — зафиксировать адрес ячейки. Чтобы его заморозить, из относительной ссылки, которой по умолчанию является любой адрес в Excel, нужно сделать абсолютную ссылку, которая не меняет координаты при копировании. Для каждой координаты адреса необходимо установить знак доллара ($).

Установка знака доллара совершается нажатием на соответствующий символ на клавиатуре. Он расположен на одной клавише с цифрой «4», но для выведения на экран нужно нажать данную клавишу в английской раскладке клавиатуры в верхнем регистре (с зажатой клавишей «Shift»). Существует и более простой и быстрый способ. Следует выделить адрес элемента в конкретной ячейке или в строке функций и нажать на функциональную клавишу F4. При первом нажатии знак доллара появится у адреса строки и столбца, при втором нажатии на данную клавишу он останется только у адреса строки, при третьем нажатии – у адреса столбца. Четвертое нажатие клавиши F4 убирает знак доллара полностью, а следующее запускает данную процедуру по новому кругу.

Читайте так же:
Как добавить или вычесть часы из поля даты / времени в Excel?

Рассмотрим, как работает замораживание адреса на конкретном примере.

  1. Сначала скопируем регулярную формулу в остальные элементы столбца. Для этого мы используем тег fill-tag. Поместите курсор в правый нижний угол ячейки, данные из которой вы хотите скопировать. В то же время он будет преобразован в крест, который называется маркером заполнения. Нажмите левую кнопку мыши и перетащите крест в конец стола.

Маркер заполнения в Microsoft Excel

Адрес ячейки сместился в Microsoft Excel

Установка абсолютной ссылки в Microsoft Excel

Копирвание абсолютной ссылки в Microsoft Excel

Адрес второго множителя не изменяется в Microsoft Excel

Координаты строки смещаются при копировании в Microsoft Excel

Координаты столбца смещаются при копировании в Microsoft Excel

Замораживает координаты клеток.

Способ 2: закрепление ячеек

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

Если нужная ячейка находится в верхней строке или крайнем левом столбце листа, то исправление элементарно.

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

Закрепление верхней строки в Microsoft Excel

Верхняя строка закреплена в Microsoft Excel

Аналогичным образом можно заморозить и левую колонку.

    Переходим во вкладку «Вид» и жмем на кнопку «Закрепить области». На этот раз выбираем вариант «Закрепить первый столбец».

Закрепление столбца в Microsoft Excel

Первый столбец закреплен в Microsoft Excel

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

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

Закрепление области в Microsoft Excel

Область закреплена в Microsoft Excel

При желании можно легко удалить сделанную таким образом заморозку. Алгоритм одинаков во всех случаях, независимо от того, замораживает ли пользователь строку, столбец или область. Перейдите на вкладку Вид, нажмите на значок Заморозить области, а затем в появившемся списке выберите пункт Разморозить области. Это приведет к размораживанию всех диапазонов, определенных на текущем листе.

Снятие закрепления областей в Microsoft Excel

Способ 3: защита от редактирования

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

Если таблица не динамическая и не будет меняться со временем, можно защитить весь лист, а не только отдельные ячейки. Это еще проще.

    Перемещаемся во вкладку «Файл».

Читайте так же:
Как добавить вторичную ось в сводную диаграмму в Excel?

Переход во вкладку Файл в Microsoft Excel

Переход к защите листа в Microsoft Excel

Окно Защита листа в Microsoft Excel

Повторное введение пароля в Microsoft Excel

Сообщение о невозможности редактирования ячеки в Microsoft Excel

Существует еще один способ блокировать изменения элементов листа.

    Переходим в окно «Рецензирование» и клацаем по иконке «Защитить лист», которая размещена на ленте в блоке инструментов «Изменения».

переход в окно защиты листа в Microsoft Excel

Окно защиты листа в Microsoft Excel

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

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

    Кликаем по прямоугольнику, который располагается на стыке горизонтальной и вертикальной панелей координат. Можно также, если курсор находится в любой области листа вне таблицы, нажать сочетание горячих клавиш на клавиатуре Ctrl+A. Эффект будет одинаковый – все элементы на листе выделены.

Выделение всех ячеек листа в Microsoft Excel

Переход в формат ячеек в Microsoft Excel

Снятие защиты с ячейки в Microsoft Excel

Переход в формат ячеек в программе Microsoft Excel

Включение защиты ячеек в окне форматирования в Microsoft Excel

Переход в окно защиты листа в программе Microsoft Excel

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

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

ЗакрытьМы рады, что смогли помочь Вам в решении проблемы.

Помимо этой статьи, на сайте еще 12677 инструкций.
Добавьте сайт Lumpics.ru в закладки (CTRL+D) и мы точно еще пригодимся вам.

Поблагодарите автора, поделившись данной статьей в социальных сетях.

ЗакрытьОпишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.

Типы ссылок на ячейки в формулах Excel

Если вы работаете в Excel не первый день, то наверняка видели или использовали ссылки на знак доллара в формулах и функциях Excel, например, $D$2 или F$3 и т.д. Наконец, давайте выясним, что они означают, как они работают и где они могут быть полезны в файлах.

Относительные ссылки

Это обычные ссылки в виде буква столбца-номер строки ( А1, С5, т.е. "морской бой"), встречающиеся в большинстве файлов Excel. Их особенность в том, что они смещаются при копировании формул. Т.е. C5, например, превращается в С6, С7 и т.д. при копировании вниз или в D5, E5 и т.д. при копировании вправо и т.д. В большинстве случаев это нормально и не создает проблем:

formulas-links-types1.png

Смешанные ссылки

Иногда тот факт, что ссылка в формуле при копировании "сползает" относительно исходной ячейки — бывает нежелательным. Тогда для закрепления ссылки используется знак доллара ($), позволяющий зафиксировать то, перед чем он стоит. Таким образом, например, ссылка $C5 не будет изменяться по столбцам (т.е. С никогда не превратится в D, E или F), но может смещаться по строкам (т.е. может сдвинуться на $C6, $C7 и т.д.). Аналогично, C$5 — не будет смещаться по строкам, но может "гулять" по столбцам. Такие ссылки называют смешанными:

formulas-links-types2.png

Абсолютные ссылки

Если мы добавим оба доллара в ссылку одновременно ($C$5) — то она станет абсолютной и никак не изменится при копировании, т.е. и строка, и столбец будут постоянно заполнены долларами:

Читайте так же:
Как выделить последовательные повторяющиеся ячейки из списка в Excel?

formulas-links-types3.png

Самый простой и быстрый способ преобразовать относительную ссылку в абсолютную или смешанную — выделить ее в формуле и несколько раз нажать клавишу F4. Эта клавиша перебирает четыре возможных варианта ссылок на ячейки: C5 → $C$5 → $C5 → C$5 и наоборот.

Все просто и понятно. Но есть одно "но".

Предположим, что мы хотим ссылаться на ячейку C5 абсолютным образом. Так, чтобы она ВСЕГДА ссылалась на C5, независимо от того, что пользователь делает дальше. Самое забавное в этом явлении то, что даже если мы сделаем ссылку абсолютной (т.е. В некоторых обстоятельствах она все равно будет меняться. Удалите третью и четвертую строки, и она изменится на C$3. Если вы вставите столбец слева от C, она изменится на D. Если вы вырежете ячейку C5 и вставите F7, она изменится на F7, и так далее. Что если мне нужна действительно жесткая ссылка, которая всегда ссылается на C5 и ни на что другое при любых обстоятельствах или действиях пользователя?

Действительно абсолютные ссылки

Решением является использование функции INDIRECT, которая генерирует ссылку на ячейку из строки текста.

formulas-links-types4.png

Формулы, которые вводят в ячейку:

То он всегда будет указывать на ячейку с адресом C5, независимо от последующих действий пользователя, вставки или удаления строк и т.д. Единственное небольшое осложнение заключается в том, что если целевая ячейка пуста, DVSSYL выводит 0, что не всегда практично. Однако этого можно легко избежать, используя немного более сложную конструкцию с управлением через функцию EPUSTA:

Копирование формул без сдвига ссылок

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

exact-formulas-copy1.png

При копировании диапазона D2:D8 с формулами на другой лист Excel автоматически исправляет ссылки, перемещая их в новое место, тем самым останавливая счет:

exact-formulas-copy2.png

Необходимо скопировать диапазон с формулами, чтобы сохранить их согласованность и тем самым сохранить результаты вычислений.

Способ 1. Абсолютные ссылки

exact-formulas-copy9.png

Способ 2. Временная деактивация формул

Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак "равно" (=) на любой другой символ, не встречающийся обычно в формулах, например на "решетку" (#) или на пару амперсандов (&&). Для этого:

  1. Выделяем диапазон с формулами (в нашем примере D2:D8)
  2. Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)

exact-formulas-copy3.png

exact-formulas-copy4.png

Способ 3. Копирование через Блокнот

Описанный здесь способ намного быстрее и проще.

Вкладка Формулы содержит кнопку Показать формулы (Формулы — Показать формулы), которая включает проверку формул — формулы, используемые в формулах, будут отображены в ячейках вместо результатов:

exact-formulas-copy5.png

Вам следует скопировать и вставить диапазон D2:D8 в стандартный блокнот:

exact-formulas-copy6.png

Теперь выделите (Ctrl+A), скопируйте (Ctrl+C) обратно в буфер обмена и вставьте его на лист, куда нужно:

exact-formulas-copy7.png

Excel можно вернуть в обычный режим, нажав кнопку Показать формулы.

Примечание: этот метод иногда не работает на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев он прекрасно работает.

Способ 4. Макрос

Такое копирование формул без смещения ссылок следует выполнять с помощью макросов, если вам приходится делать это часто. На вкладке Разработчик нажмите Alt+F11 или щелкните на кнопке Visual Basic, чтобы ввести новый модуль. Выберите меню Вставка — Модуль, чтобы вставить код:

Для запуска макроса можно использовать кнопку Макросы на вкладке Разработчик (Разработчик — Макросы) или сочетание клавиш Alt+F8. После запуска макрос попросит вас выбрать диапазон выделения, содержащий исходные формулы, а также диапазон вставки, и выполнит точное копирование формул на лету:

Как зафиксировать ячейку, дав ей имя.

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

Вернемся к нашему примеру со скидками. Давайте попробуем дать ячейке F2 имя, чтобы ее можно было использовать в расчетах.

Установите курсор на F2, а затем назовите адрес, как показано на рисунке выше. Допускается использование букв, цифр и знаков подчеркивания, которые могут заменять пробел. Нет необходимости включать пунктуацию или специальные символы. Я не думаю, что стоит называть это «скидкой».

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

Ранее мы зафиксировали ячейку F2 с помощью абсолютной ссылки и знака $ -.

И теперь делает то же самое с именем «скидка»:

Ячейка так же надежно закреплена, а формула стала более четкой и читаемой.

Excel понимает, что если формула содержит имя «скидка», то вместо нее нужно использовать содержимое ячейки F2.

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

Быстрое удаление пустых столбцов в Excel — В этом руководстве вы узнаете, как можно легко удалить пустые столбцы в Excel с помощью макроса, формулы и даже простым нажатием кнопки. Как бы банально это ни звучало, удаление пустых…
Чем отличается абсолютная, относительная и смешанная адресация — Важность ссылки на ячейки Excel трудно переоценить. Ссылка включает в себя адрес, из которого вы хотите получить информацию. При этом используются два основных вида адресации – абсолютная и относительная. Они…
Относительные и абсолютные ссылки – как создать и изменить — В руководстве объясняется, что такое адрес ячейки, как правильно записывать абсолютные и относительные ссылки в Excel, как ссылаться на ячейку на другом листе и многое другое. Ссылка на ячейки Excel,…
6 способов быстро транспонировать таблицу — В этой статье показано, как столбец можно превратить в строку в Excel с помощью функции ТРАНСП, специальной вставки, кода VBA или же специального инструмента. Иначе говоря, мы научимся транспонировать таблицу.…
4 способа быстро убрать перенос строки в ячейках Excel — В этом совете вы найдете 4 совета для удаления символа переноса строки из ячеек Excel. Вы также узнаете, как заменять разрывы строк другими символами. Все решения работают с Excel 2019, 2016, 2013…
Как безопасно удалить пустые ячейки в Excel и как не нужно никогда это делать — В этом руководстве вы узнаете, как правильно и безопасно удалять пустые ячейки в таблицах Excel, чтобы они выглядели четкими и профессиональными. Пустые ячейки – это неплохо, если вы намеренно оставляете…
Как быстро заполнить пустые ячейки в Excel? — В этой статье вы узнаете, как выбрать сразу все пустые ячейки в электронной таблице Excel и заполнить их значением, находящимся выше или ниже, нулями или же любым другим шаблоном. Заполнять…
Как посчитать количество пустых и непустых ячеек в Excel — Если ваша задача — заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных…
6 способов — как безопасно удалить лишние пустые строки в Excel — Это руководство научит вас нескольким простым приемам безопасного удаления нескольких пустых строк в Excel без потери информации. Пустые строки в таблице — это проблема, с которой мы все время от…
Как поменять столбцы местами в Excel? — В этой статье вы узнаете несколько методов перестановки столбцов в Excel. Вы увидите, как можно перетаскивать один или сразу несколько столбцов мышью либо с помощью «горячих» клавиш. Можно перемещать сразу несколько…

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector