Как сделать ссылку на неизменяемую ячейку в excel?

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

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

Пример. Вот данные, которые получены в столбце «D» суммированием данных из столбцов «В» и «С», разделив результат на данные из столбца «J», чтобы преобразовать в евро.

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

1) И вот какое решение существует — преобразовать относительные ссылки в абсолютные.

1. Этот способ имеет одно неудобство, что приходится вручную работать с данными, подставляя каждый раз.
2. 2) Тогда можно попробовать другой способ — «дезактивировать» формулы, то есть сделать так, чтобы в документе «Excel» не воспринимались формулы — «формулами», а воспринимались, словно это обычный текст.
3. Тогда знак «=» на какое-то время следует заменить любым другим символом, например, решеткой — «#» или парой амперсандов — «&&».
4. Воспользуемся горячими клавишами.

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

На вкладке «Формулы» надо найти «Показать формулы» — режим проверки формул, тогда в ячейках вместо результатов программа показывает формулы, по которым вычислили эти результаты. Можно пользоваться горячими клавишами, алгоритм следующий:

Дальше надо скопировать диапазон из Exel в блокнот —

4) Четвертый способ подходит тем, кто постоянно выполняет копирование, перенося каждый раз. Можно воспользоваться макросом — здесь все проще всего, все задано заранее, надо только создать макрос.

• Чтобы работать с макросами, надо перейти на вкладку «Разработчик» или применить сочетание горячих клавишь Alt+F8.
• Запустив макрос, необходимо показать программе исходный диапазон, откуда копируют и куда вставляют.

В авторском ролике Николай Павлов, специалист по Exel, расскажет еще более подробно и наглядно.

Источник: http://www.bolshoyvopros.ru/questions/1371527-excel-kak-sdelat-chtoby-jachejki-v-formule-ne-menjalis-pri-kopirovanii.html

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

При работе с таблицами Excel 2010, 2013, 2016 иногда необходимо сделать так, чтобы при перемещении формулы по ячейкам ссылка оставалась неизменной.

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

В таблице Excel предусмотрена специальная активная кнопка для фиксации, как ее активировать будет рассмотрено в данной статье.

Как зафиксировать ячейку в формуле в таблицах Excel – вариант №1

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

В результате ссылка будет зафиксирована с помощью $ (знака доллара). Например, если у вас в формуле было значение С3, то после того, как вы проведете вышеописанную процедуру, ссылка обязана стать такого формата — $С$3.

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

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

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

Как в Экселе закрепить ячейку в формуле – вариант №3

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

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

Если по каким-либо причинам в таблицах Excel 2016, 2013, 2010 необходимо отменить фиксацию ссылки определенной ячейки, это легко можно сделать. Для этого кликните по формуле левой кнопкой мыши, чтобы она выделилась. Затем нажимайте F4 столько раз, сколько необходимо пока не пропадут все знаки доллара.

Источник: https://pced.ru/kak-zafiksirovat-yachejku-v-excel-v-formule/

Как изменить стиль ссылок R1C1 на обычные A1 в программе Excel

Адреса ячеек в программе «Excel» могут отображаться в двух разных форматах:

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

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

Для изменения вида адресов следует выполнить следующую последовательность действий:

• войти во вкладку «Файл»;
• выбрать меню «Параметры»;
• далее выбрать вкладку «Формулы»;
• во вкладке «Формулы» убрать «галочку» (флажок) напротив параметра «Стиль ссылок R1C1»;
• нажать кнопку «Ok».

После выполнения указанной последовательности действий адреса ячеек приобретут привычный вид : A1; B1; C1 и т.д.

Источник: http://RuExcel.ru/r1c1/

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

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

  Итак, рассмотрим более детально все варианты как закрепляется ячейка. Есть три варианта фиксации:

Полная фиксация ячейки

      Полная фиксация ячейки — это когда закрепляется значение по вертикали и горизонтали (пример, $A$1), здесь значение никуда не может сдвинутся, так называемая абсолютная формула. Очень удобно такой вариант использовать, когда необходимо ссылаться на значение в ячейке, такие как курс валют, константа, уровень минимальной зарплаты, расход топлива, процент доплат, кофициент и т.п.

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

Когда мы в ячейку Е4 пропишем формулу =D4/D1, то в результате копирования, ячейки поменяют адреса и сдвинутся ниже, пропуская, так необходимый нам обменный курс.

А вот если внести изменения и зафиксировать значение в формуле простым символом доллара («$»), то мы получим следующий результат =D4/$D$1 и в этом случае, сдвигая и копируя, формулу мы получаем нужный нам результат во всех ячейках диапазона;

Фиксация формулы в Excel по вертикали

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

Фиксация формул по горизонтали

Следующее закрепление будет по горизонтали (пример, A$1). И все правила остаются действительными как и предыдущем пункте, но немножко наоборот. Рассмотрим данный пример подробнее.

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

Итак, мы фиксируем горизонтальную строку $1 и вертикальный столбец $J и в ячейке К4 прописываем формулу =$J4*K$1 и после ее копирование во все ячейки вычисляемого диапазона и получаем нужный результат без каких-либо сдвигов в формуле.

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

    Что бы постоянно не переключать раскладку клавиатуры при прописании знака «$» для закрепления значение в формуле, можно использовать «горячую» клавишу F4.

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

При повторном нажатии, добавится только для столбцов, еще раз нажать, будет только для строк и 4-е нажатие снимет все закрепления, формула вернется к первоначальному виду.

      Скачать пример можно здесь.

      А на этом у меня всё! Я очень надеюсь, что вы поняли все варианты как возможно зафиксировать ячейку в формуле. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

    Не забудьте поблагодарить автора!

Деньги — нерв войны. Марк Туллий Цицерон

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Источник: http://topexcel.ru/prostoj-sposob-zafiksirovat-znachenie-v-formule-excel/

Как зафиксировать формулу в MS Excel

При работе с формулами MS Excel, особенно если таблица сложная, а формул много, весьма легко ошибиться. Одна из самых распространенных (и чаще всего фатальных) ошибок связана с копированием формул в другие ячейки.

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

Спустя время нам вновь понадобились старые расчеты, мы копируем ячейку со «старой» формулой и вставляем её на несколько ячеек «поближе».

Создаем самую обычную формулу в Excel

И не замечаем, что «старая» формула вдруг стала «новой» — сместилось не только ячейка в которой выводился результат формулы, но и, на то же число ячеек, сместились  исходные данные! Хорошо если «новые» ячейки не заполнены — тогда, увидев вместо результата «0», мы поймем ошибку. А если заполнены, причем похожими данными? Так можно и доходы с расходами перепутать и долго оптом искать концы — формула-то работала правильно!

… а теперь копируем её. Обратите внимание — вместе с местоположением ячейки с формулой, сдвинулись и ячейки-источники данных

Впрочем, есть отличное средство, которое гарантировано защитит вас от подобных проблем. Дело в том, что любую введенную на лист MS Excel форму можно зафиксировать, и тогда, даже если её скопировать в другое место, исходные данные от этого не пострадают.

Фиксируем формулу в ячейке Excel

Зафиксировать формулу в ячейке до смешного просто: достаточно поставить перед каждым из её членов значок доллара. Да, вот так всё просто: ставим перед $ перед любым именем ячейки и фиксируем его от случайного изменения.

Обратите внимание:

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

Значок «доллара» позволяет зафиксировать ячейку в Excel

На практике эта особенность означает, что формулу можно зафиксировать только частично:

• Если $ стоит только перед буквами — формула будет зафиксирована по горизонтали (по строкам).
• Если $ стоит только перед числами — формула будет зафиксирована по вертикали (по столбцам)

Особенности фиксации формул в MS Excel

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

• Одно нажатие F4 при вводе формулы ставит значки доллара ко всем составляющим адреса ячейки (то есть для ячейки D4 это будет $D$4)
• Два нажатия F4 при вводе формулы ставит значки доллара ТОЛЬКО перед цифрами адреса ячейки (для ячейки D4 это будет D$4)
• Три нажатия F4 при вводе формулы ставит значки доллара ТОЛЬКО перед буквами составляющим адреса ячейки (для ячейки D4 это будет $D4)
• Четыре нажатия F4 отменяют расстановку «долларов» и снимают фиксацию.

Источник: http://bussoft.ru/tablichnyiy-redaktor-excel/kak-zafiksirovat-formulu-v-ms-excel.html

Как в 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, то закрепится только столбец, еще раз — только строка, еще раз — все вернется к первоначальному виду.

Источник: https://sirexcel.ru/osvaivaem-excel/osnovy/kak-v-excel-zakrepit-zafiksirovat-yachejku-v-formule/

Как разрешить изменять только выбранные ячейки?

Хитрости » 1 Май 2011       Дмитрий       139371 просмотров

Для данных на листе от изменений в Excel существует такая команда как Защитить лист(Protect sheet). Найти её можно:

• в Excel 2003 — Сервис—Защита—Защитить лист
• в Excel 2007-2013 — вкладка Рецензирование(Review)—Защитить лист(Protect sheet)

Все возможности защиты и видеоурок по защите листов в Excel можно посмотреть на этой странице: Защита листов и ячеек в MS Excel

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

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

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

• Выделяем ВСЕ ячейки листа (это можно сделать так:щелкаете левой кнопкой мыши на пересечении заголовков строки и столбцов):
  
• Формат ячеек(Format cells)-вкладка Защита(Protection). Снимаем галочку с пункта Защищаемая ячейка(Locked)
• выделяем нужные ячейки (если ячейки не «в одной кучке», а по отдельности, то выделить их можно по одной, зажав клавишу Ctrl)
• Формат ячеек(Format cells)-вкладка Защита(Protection). Ставим галочку Защищаемая ячейка(Locked)

После этого устанавливаете защиту на лист(как см. в самом начале статьи) и вуаля! Изменять можно только те ячейки, у которых снята галка с «Защищаемая ячейка»(Locked).

При этом, если при защите листа снять галочку с пункта выделение заблокированных ячеек(Select locked cells) — выделять можно будет только те ячейки, которые разрешены для редактирования.

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

Так же на вкладке Защита(Protection) есть пункт Скрыть формулы(Hidden).

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

Полезно, если хотите оставить возможность вводить какие-то параметры, а расчеты формулами оставить «за кадром».

Также см.:
Защита листов и ячеек в MS Excel
Защита листов/снятие защиты
Как защитить лист от пользователя, но не от макроса?
Как оставить возможность работать с группировкой/структурой на защищенном листе?

Статья помогла? Поделись ссылкой с друзьями!

Источник: https://www.excel-vba.ru/chto-umeet-excel/kak-razreshit-izmenyat-tolko-vybrannye-yachejki/

Замена ссылок на значения

Используйте пошаговые руководства:

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

К примеру, на листе есть формула: =A1*A14+(5+C13)*C14/B11 Нужно понять, какие значения скрываются за ссылками на ячейки. Другими словами, из приведенной выше формулы надо получить: =10*5,2+(5+5)*10/7,8

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

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

Метод вывода:

• в комментарии к ячейкам с формулами – преобразованная формула будет записана в примечание к той же ячейке, в которой размещается. Пожалуй, самый удобный метод, так как и формула остается неизменной, и ячейки рядом. Но, если в ячейке до этого уже было какое-либо примечание – оно будет перезаписано. То есть старое примечание исчезнет без возможности восстановления;
• в ячейки правее ячеек с формулами – преобразованная формула будет выведена в ячейку, которая расположена правее ячейки с исходной формулой. С одной стороны, это вполне наглядно, но с другой стороны – не всегда удобно. Если в ячейках правее есть какие-то значения, то они будут удалены и на их место будет записана расшифровка формулы;
• с заменой ссылки на ее значение прямо в формуле – преобразование ссылок на ячейки в значения выполняется непосредственно внутри формулы. Может пригодиться, если необходимо оставить и формулу, и возможность проследить этапы вычислений. Но обратите внимание, что в этом случае теряется возможность проследить, из каких ячеек взяты те или иные значения. При использовании этого метода действия команды по преобразованию формул можно отменить, нажав кнопку отмены действия на панели Excel или сочетание клавиш Ctrl+Z;
• Выводить значения с точностью как на экране – если установлен этот флаг, то значения ссылок выводятся так же, как они отображаются в ячейках. К примеру, если в ячейке A14 отображается значение «5,2», это не всегда означает, что само значение ячейки равно «5,2». Если к ячейке применен формат «Числовой» с количеством знаков после запятой 1, а в ячейке значится «5,159», то это значение тоже будет отображаться как «5,2». Если флаг не установлен, то в преобразованной формуле будут использованы реальные значения ячеек, несмотря на примененные к ним числовые форматы.

Примечания

Если в какой-либо из ячеек не будет ссылок на другие ячейки, а просто текстовая формула, то как результат отобразится сама формула и за ней текст: «[ссылок на другие ячейки нет]»

Если в формуле применяются функции (ВПР, СЧЕТЕСЛИ, МИН, МАКС и т. д.), то их имена будут отображены без искажений (например =СУММ(5,2;7,8)+ЦЕЛОЕ(5/11))

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

Источник: https://www.fd.ru/articles/158070-16-m1-01-01-2016-spravka-k-nadstroyke-v-excel

Защита ячеек в Excel от изменения, редактирования и ввода ошибочных данных

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

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

Защита выделенного диапазона от изменения

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

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

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

В следующем диалоговом окне переходим на вкладку «Защита» и снимаем галочку с пункта «Защищаемая ячейка». Нажмите «ОК».

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

После этого поставим ограничения для изменений. Для примера давайте запретим редактирование блоков, которые находятся в диапазоне B2:D7. Выделяем указанный диапазон, кликаем по нему правой кнопкой мыши и выбираем из меню «Формат ячеек». Дальше перейдите на вкладку «Защита» и поставьте галочку в поле «Защищаемая…». Нажмите «ОК».

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

После этого, любой пользователь сможет работать с информацией на странице. В примере введены пятерки в Е4. Но при попытке изменить текст или числа в диапазоне В2:D7, появится сообщение, что ячейки защищены.

Ставим пароль

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

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

Сделаем так, чтобы другие пользователи могли редактировать всё на листе, кроме диапазона B2:D7. А Вы, зная пароль, могли редактировать и блоки в B2:D7.

Теперь нужно выделить диапазон, для которого будет установлен пароль, в примере это B2:D7. Потом опять зайдите «Формат ячеек» и поставьте галочку в поле «Защищаемая…».

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

Затем переходим на вкладку «Рецензирование» и нажимаем кнопочку «Разрешить изменение диапазонов». Откроется соответствующее диалоговое окно. Нажмите в нем кнопочку «Создать».

Имя диапазона и ячейки, которые в него входят, уже указаны, поэтому просто введите «Пароль», подтвердите его и нажмите «ОК».

Возвращаемся к предыдущему окну. Нажмите в нем «Применить» и «ОК». Таким образом, можно создать несколько диапазонов, защищенных различными паролями.

Теперь нужно установить пароль для листа. На вкладке «Рецензирование» нажимаем кнопочку «Защитить лист». Введите пароль и отметьте галочками, что можно делать пользователям. Нажмите «ОК» и подтвердите пароль.

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

Защищаем блоки от неверных данных

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

Например, в таблице есть столбец «Класс». Здесь не может стоять число больше 11 и меньше 1, имеются ввиду школьные классы. Давайте сделаем так, чтобы программа выдавала ошибку, если пользователь введет в данный столбец число не от 1 до 11.

Выделяем нужный диапазон ячеек таблицы – С3:С7, переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных».

В следующем диалоговом окне на вкладке «Параметры» в поле «Тип…» выберите из списка «Целое число». В поле «Минимум» введем «1», в поле «Максимум» – «11».

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

На вкладке «Сообщение об ошибке» введем сообщение, которое будет появляться, если пользователь попробует ввести неправильную информацию. Нажмите «ОК».

Теперь если выделить что-то из диапазона С3:С7, рядом будет высвечиваться подсказка. В примере при попытке написать в С6 «15», появилось сообщение об ошибке, с тем текстом, который мы вводили.

(1

Источник: http://comp-profi.com/zashita-yacheek-v-excel-ot-izmeneniya-redaktirovaniya-i-vvoda-oshibochnyh-dannyh/

excel: Как отключить автоматическое изменение ссылки на ячейку в Excel после копирования / вставки?

excel excel-formula reference cell copy-paste

У меня есть большая таблица Excel 2003, над которой я работаю. Есть много очень больших формул с множеством ссылок на ячейки. Вот простой пример.

='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49

Большинство из них более сложные, но это дает хорошее представление о том, с чем я работаю. Немногие из этих ссылок на ячейки являются абсолютными ($ s). Я хотел бы иметь возможность скопировать эти ячейки в другое место без изменения ссылок на ячейки.

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

Есть ли способ временно отключить изменение ссылки на ячейку при копировании-вставке / заполнении, не делая ссылки абсолютными?

РЕДАКТИРОВАТЬ: Я только что узнал, что вы можете сделать это с VBA, скопировав содержимое ячейки в виде текста вместо формулы. Я хотел бы не делать этого, потому что я хочу скопировать целые строки / столбцы сразу. Есть ли простое решение, которое мне не хватает?

Bat Masterson Источник Размещён: 12.07.2011 05:25

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

Я бы начал с преобразования каждой формулы на листе в текст примерно так:

Dim r As Range

For Each r In Worksheets(«Sheet1»).UsedRange
If (Left$(r.Formula, 1) = «=») Then
r.Formula = «'ZZZ» & r.Formula
End If
Next r

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

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

For Each r In Worksheets(«Sheet1»).UsedRange
If (Left$(r.Formula, 3) = «ZZZ») Then
r.Formula = Mid$(r.Formula, 4)
End If
Next r

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

barrowc Размещён: 12.07.2011 06:50 Решение

От http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas_take_2 :

1. Переведите Excel в режим просмотра формул. Самый простой способ сделать это — нажать Ctrl+ `(этот символ является «обратным апострофом») и обычно находится на той же клавише, что и ~ (тильда).
2. Выберите диапазон для копирования.
3. Нажмите Ctrl+C
4. Запустите Windows Notepad
5. Нажмите Ctrl+, Vчтобы вставить скопированные данные в Блокнот
6. В блокноте нажмите Ctrl+, Aа затем Ctrl+, Cчтобы скопировать текст
7. Активируйте Excel и активируйте верхнюю левую ячейку, куда вы хотите вставить формулы. И убедитесь, что лист, на который вы копируете, находится в режиме просмотра формул.
8. Нажмите Ctrl+, Vчтобы вставить.
9. Нажмите Ctrl+, `чтобы выйти из режима просмотра формул.

Примечание.

Если операция вставки обратно в Excel не работает должным образом, есть вероятность, что вы недавно использовали функцию Excel для преобразования текста в столбцы, и Excel пытается помочь вам, вспомнив, как вы в последний раз анализировали данные. Вам нужно запустить Мастер преобразования текста в столбцы. Выберите параметр с разделителями и нажмите «Далее». Снимите все флажки параметра Разделитель, кроме Tab.

Или с http://spreadsheetpage.com/index.php/tip/making_an_exact_copy_of_a_range_of_formulas/ :

If you're a VBA programmer, you can simply execute the following code:
With Sheets(«Sheet1»)
.Range(«A11:D20»).Formula = .Range(«A1:D10»).Formula
End With
Whit Kemmey Размещён: 22.04.2013 06:54

• Очень простое решение состоит в том, чтобы выбрать диапазон, который вы хотите скопировать, затем найдите и замените ( Ctrl + h), изменив =на другой символ, который не используется в вашей формуле (например #), — таким образом, помешая ему стать активной формулой.
• Затем скопируйте и вставьте выбранный диапазон в новое местоположение.
• Наконец, найдите и замените, чтобы #вернуться =в исходный и новый диапазоны, таким образом восстанавливая оба диапазона, чтобы они снова были формулами.

Alistair Collins Размещён: 25.04.2014 09:40

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

Предположим, у вас есть необработанные данные в столбцах A и B и формула в C (например, C = A + B), и вы хотите использовать ту же формулу в столбце F, но копирование из C в F приводит к F = D + E.

1. Скопируйте содержимое C в любой пустой временный столбец, скажем R. Относительные ссылки изменятся на R = P + Q, но игнорируйте это, даже если он помечен как ошибка.
2. Вернитесь к C и переместите (не копируйте) его к F. Формула должна быть неизменной, поэтому F = A + B.
3. Теперь перейдите к R и скопируйте его обратно в C. Относительная ссылка вернется к C = A + B
4. Удалить временную формулу в R.
5. Боб твой дядя.

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

JimH Размещён: 13.08.2014 01:43

1. Не проверял в Excel, но это работает в Libreoffice4:
2. Вся перезапись адреса происходит во время последовательной (a1) вырезки (a2) вставки
3. Вам нужно прервать последовательность, поместив что-то промежуточное: (b1) вырезать (b2) выбрать несколько пустых ячеек (более 1) и перетащить (переместить) их (b3) вставить

Шаг (b2) — это то, где ячейка, которая собирается обновить себя, останавливает отслеживание. Быстро и просто.

user1501439 Размещён: 30.06.2015 03:09

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

Источник: https://issue.life/questions/6668383