Как заморозить ячейку в эксель

Фиксация ячейки в Microsoft Excel

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

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

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

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

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

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

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

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

Взглянем, как работает заморозка адреса на конкретном примере.

1. Для начала скопируем обычную формулу в другие элементы столбца. Для этого воспользуемся маркером заполнения. Устанавливаем курсор в нижний правый угол ячейки, данные из которой нужно скопировать. При этом он трансформируется в крестик, который носит название маркера заполнения. Зажимаем левую кнопку мыши и тянем этот крестик вниз до конца таблицы.
2. После этого выделяем самый нижний элемент таблицы и смотрим в строке формул, как изменилась формула во время копирования. Как видим, все координаты, которые были в самом первом элементе столбца, при копировании сместились. Вследствие этого формула выдает некорректный результат. Это связано с тем фактом, что адрес второго множителя, в отличие от первого, для корректного расчета смещаться не должен, то есть, его нужно сделать абсолютным или фиксированным.
3. Возвращаемся в первый элемент столбца и устанавливаем знак доллара около координат второго множителя одним из тех способов, о которых мы говорили выше. Теперь данная ссылка заморожена.
4. После этого, воспользовавшись маркером заполнения, копируем её на диапазон таблицы, расположенный ниже.

Таким методом производится заморозка координат ячеек.

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

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

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

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

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

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

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

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

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

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

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

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

1. Перемещаемся во вкладку «Файл».
2. В открывшемся окне в левом вертикальном меню переходим в раздел «Сведения». В центральной части окна клацаем по надписи «Защитить книгу». В открывшемся перечне действий по обеспечению безопасности книги выбираем вариант «Защитить текущий лист».
3. Запускается небольшое окошко, которое называется «Защита листа». Прежде всего, в нем в специальном поле требуется ввести произвольный пароль, который понадобится пользователю, если он в будущем пожелает отключить защиту, чтобы выполнить редактирование документа. Кроме того, по желанию, можно установить или убрать ряд дополнительных ограничений, устанавливая или снимая флажки около соответствующих пунктов в перечне, представленном в данном окне. Но в большинстве случаев настройки по умолчанию вполне соответствуют поставленной задаче, так что можно просто после введения пароля клацать по кнопке «OK».
4. После этого запускается ещё одно окошко, в котором следует повторить пароль, введенный ранее. Это сделано для того, чтобы пользователь был уверен, что ввел именно тот пароль, который запомнил и написал в соответствующей раскладке клавиатуры и регистре, иначе он сам может утратить доступ к редактированию документа. После повторного введения пароля жмем на кнопку «OK».
5. Теперь при попытке отредактировать любой элемент листа данное действие будет блокировано. Откроется информационное окно, сообщающее о невозможности изменения данных на защищенном листе.

Имеется и другой способ заблокировать любые изменения в элементах на листе.

1. Переходим в окно «Рецензирование» и клацаем по иконке «Защитить лист», которая размещена на ленте в блоке инструментов «Изменения».
2. Открывается уже знакомое нам окошко защиты листа. Все дальнейшие действия выполняем точно так же, как было описано в предыдущем варианте.

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

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

1. Кликаем по прямоугольнику, который располагается на стыке горизонтальной и вертикальной панелей координат. Можно также, если курсор находится в любой области листа вне таблицы, нажать сочетание горячих клавиш на клавиатуре Ctrl+A. Эффект будет одинаковый – все элементы на листе выделены.
2. Затем клацаем по зоне выделения правой кнопкой мыши. В активированном контекстном меню выбираем пункт «Формат ячеек…». Также вместо этого можно воспользоваться набором сочетания клавиш Ctrl+1.
3. Активируется окошко «Формат ячеек». Сразу же выполняем переход во вкладку «Защита». Тут следует снять флажок около параметра «Защищаемая ячейка». Щелкаем по кнопке «OK».
4. Далее возвращаемся на лист и выделяем тот элемент или группу, в которой собираемся заморозить данные. Жмем правой кнопкой мыши по выделенному фрагменту и в контекстном меню переходим по наименованию «Формат ячеек…».
5. После открытия окна форматирования в очередной раз переходим во вкладку «Защита» и ставим флажок возле пункта «Защищаемая ячейка». Теперь можно нажать на кнопку «OK».
6. После этого устанавливаем защиту листа любым из тех двух способов, которые были описаны ранее.

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

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

Как в excel закрепить (зафиксировать) ячейку в формуле

Очень часто в Excel требуется закрепить (зафиксировать) определенную ячейку в формуле. По умолчанию, ячейки автоматически протягиваются и изменяются. Посмотрите на этот пример.

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

Чтобы это сделать мы прописываем в ячейке D2 формулу =B2*C2

Если мы далее протянем формулу вниз, то она автоматически поменяется на соответствующие ячейки. Например, в ячейке D3 будет формула =B3*C3 и так далее. В связи с этим нам не требуется прописывать постоянно одну и ту же формулу, достаточно просто ее протянуть вниз. Но бывают ситуации, когда нам требуется закрепить (зафиксировать) формулу в одной ячейке, чтобы при протягивании она не двигалась.

Взгляните на вот такой пример. Допустим, нам необходимо посчитать выручку не только в рублях, но и в долларах. Курс доллара указан в ячейке B7 и составляет 35 рублей за 1 доллар. Чтобы посчитать в долларах нам необходимо выручку в рублях (столбец D) поделить на курс доллара.

Если мы пропишем формулу как в предыдущем варианте. В ячейке E2 напишем =D2* B7 и протянем формулу вниз, то у нас ничего не получится. По аналогии с предыдущим примером в ячейке E3 формула поменяется на =E3* B8 — как видите первая часть формулы поменялась для нас как надо на E3, а вот ячейка на курс доллара тоже поменялась на B8, а в данной ячейке ничего не указано. Поэтому нам необходимо зафиксировать в формуле ссылку на ячейку с курсом доллара. Для этого необходимо указать значки доллара и формула в ячейке E3 будет выглядеть так =D2/ $B$7 , вот теперь, если мы протянем формулу, то ссылка на ячейку B7 не будет двигаться, а все что не зафиксировано будет меняться так, как нам необходимо.

Примечание: в рассматриваемом примере мы указал два значка доллара $ B $ 7. Таким образом мы указали Excel, чтобы он зафиксировал и столбец B и строку 7 , встречаются случаи, когда нам необходимо закрепить только столбец или только строку. В этом случае знак $ указывается только перед столбцом или строкой B $ 7 (зафиксирована строка 7) или $ B7 (зафиксирован только столбец B)

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

Чтобы не прописывать знак доллара вручную, вы можете установить курсор на формулу в ячейке E2 (выделите текст B7) и нажмите затем клавишу F4 на клавиатуре, Excel автоматически закрепит формулу, приписав доллар перед столбцом и строкой, если вы еще раз нажмете на клавишу F4, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.