Копирование формул без сдвига ссылок
Предположим, что у нас есть вот такая несложная таблица, в которой подсчитываются суммы по каждому месяцу в двух городах, а затем итог переводится в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не изменились и остались теми же самыми, сохранив результаты расчета.
Способ 1. Абсолютные ссылки
Способ 2. Временная деактивация формул
Чтобы формулы при копировании не менялись, надо (временно) сделать так, чтобы Excel перестал их рассматривать как формулы. Это можно сделать, заменив на время копирования знак "равно" (=) на любой другой символ, не встречающийся обычно в формулах, например на "решетку" (#) или на пару амперсандов (&&). Для этого:
- Выделяем диапазон с формулами (в нашем примере D2:D8)
- Жмем Ctrl+H на клавиатуре или на вкладке Главная — Найти и выделить — Заменить (Home — Find&Select — Replace)
Способ 3. Копирование через Блокнот
Этот способ существенно быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas — Show formulas) , чтобы включить режим проверки формул — в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:
Теперь выделите все вставленное (Ctrl+A), скопируйте в буфер еще раз (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только отжать кнопку Показать формулы (Show Formulas) , чтобы вернуть Excel в обычный режим.
Примечание: этот способ иногда дает сбой на сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев — работает отлично.
Способ 4. Макрос
Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , вставьте новый модуль через меню Insert — Module и скопируйте туда текст вот такого макроса:
Для запуска макроса можно воспользоваться кнопкой Макросы на вкладке Разработчик (Developer — Macros) или сочетанием клавиш Alt+F8. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:
Как в excel при копировании формулы не менять номер ячейки
Совет: Вы также можете щелкнуть ячейки правой кнопкой мыши, чтобы вырезать и врезать формулу. 
Как зафиксировать ячейку в формуле в Excel
- Что это такое
- Как работает
- Первый способ
- Второй способ
- Третий способ
- Отменяем фиксацию
- Пример
- Вывод
- Буквой. Смещая формулу по столбцам вправо или лево, ссылка не изменится;
- Числом. Перемещая по строкам вверх или вниз, ссылка будет постоянной;
- Буквой и числом. Фиксируется столбец и строка.
- Выделите формулу;
- Кликните на адресе ячейки;
- Нажмите клавишу F4.
Кликните два раза F4. Поменяется буква столбца. 
Кликните F4 три раза. Изменится только номер строки. 
Нажимайте F4 пока «$» не исчезнет. 
Чтобы в новом Экселе (Excel) закрепить ячейку выполните аналогичные действия.
Рассчитать стоимость товара в долларах. Выделите В6, нажмите F4. 
Протяните формулу. Ссылка не изменится. 
Знак доллара можно поставить вручную.
Как скопировать строку с формулами в Excel без изменений значений
В процессе работы с Microsoft Excel у пользователей возникает необходимость внесения дополнительных данных. Для этого необходимо добавлять или копировать строки с формулами.
Кажется, это простая и понятная процедура. Но часто возникает вопрос о том, как добавить новую строку с формулами в Excel с определенными условиями. Например, без изменения адресов в формулах с относительными ссылками на ячейки и т.п. На готовых примерах рассмотрим оригинальные решения этих задач.
Как добавить новую строку с формулами в Excel?
Пускай мы добавили формулы в одну строку или в несколько. Теперь нам нужно скопировать все строки с формулами. Как это сделать при добавлении новой строки?
- Выделяем ту строку, которая содержит формулы. В нашей ситуации это строка под номером 3. Нам необходимо, чтобы остальные строки содержали в себе такие же формулы.
- Нажимаем на выделенную область правой кнопкой мышки. Из контекстного меню нужно выбрать опцию «Копировать» (или жмем CTRL+C).
- После этого следует выделить следующую одну (или несколько) нижнюю строку, куда будет вставлена новая с формулами.
- Щелкнуть по заголовку выделенной строки правой кнопкой мышки и выбрать опцию вставить скопированные ячейки (или нажать комбинацию клавиш CTRL+SHIFT+«=»).
Заметьте, значения ячеек не заменились а добавилась в середину таблицы новая строка со старыми значениями и формулами.
Как копировать без изменения формул в Excel?
Часто при копировании относительные формулы (т.е. те, в которых нет знака «$») «подставляют» другие значения. А что делать, если вы не хотите изменять значение формул?
Можно сделать все ссылки в формулах абсолютными. Такие формулы будут копироваться неизменно, без изменения данных. Чтобы сделать ссылки абсолютными, добавляем к ним знак «$».
Ставим перед буквами и перед числами знак доллара – и получаем абсолютные ссылки в формулы, значения в которых не будут изменяться при копировании.
На пример в такой таблице ссылка на наценку ($B$1) не будет изменятся при копировании на новых строках, так как она является абсолютной, а не относительной:
Но в некоторых больших и сложных таблицах слишком много формул, которые нужно скопировать и переместить. И чтобы во всех ссылках проставить знак «$» нужно потратить много времени.
Для решения данной задачи можно воспользоваться хитрым способом. До копирования заменить все знаки «=» на символ «#».
Для «активации» работы формул используется знак «равно» (или «=»). Чтобы деактивировать их на время копирования поменяем = на #. А после копирования вернем все на свои места. Это позволит нам обойти автоматическую смену адресов в относительных ссылках при копировании.
- Нажимаем Ctrl+H (вызываем окно «Главная»-«Найти и выделить»-«Заменить»).
- Необходимо в поле «Найти» указать символ =, а в поле «Заменить на» символ #. И жмем «Заменить все».
- После этого вы можете скопировать необходимую строку, а в ее формулах не изменятся адреса ссылок на ячейки, так как они временно деактивированные.
- Снова жмем комбинацию горячих клавиш «Ctrl+H», но теперь указываем в полях обратные значения: найти «#» и заменить на «=». Система вернет все на свои места
В результате при копировании во всех строках ссылки формул остались неизменными.
Как закрепить в формуле ячейку в Excel
Часто при копировании формула перестаёт работать и давать правильные результаты. В этом случае может помочь закрепление ячейки. Это универсальный метод. Он не зависит от версии программы и одинаково реализован как в MS Excel 2007, MS Excel 2010, так и в других.
Для того, чтобы закрепить ячейку в формуле в программе MicrosoftExcel достаточно добавить в её буквенно-цифровой адрес знак “$”. При этом, если первоначально имя ячейки было A1, то в зависимости от вида закрепления оно может стать $A1, A$1 или $A$1. Ячейку закрепляют для того, чтобы формула при копировании всегда обращалась именно к ней. Имена ячеек, содержащие знак “$”, называются абсолютными ссылками.
В данной таблице необходимо каждый показатель столбца Ставка умножить на коэффициент из ячейки B1 и получившееся значение поместить в столбец Зарплата. Самый правильный способ решения данной задачи: составить формулу, внести её в ячейку B3 и скопировать вниз до конца таблицы.
В электронных таблицахExcel формулы служат для автоматизации расчëтов. Они всегда начинаются со знака “=” и могут содержать числа, знаки математических действий, специальные функции и имена ячеек (ссылки на их содержимое). Использование ссылок вместо чисел позволяет программе автоматически пересчитывать результат формулы при изменении исходных данных.
Первый вариант формула может выглядеть так: =A3*1,8.
Этот вариант хорош тем, что при копировании в ячейку B4 формула автоматически примет вид =A4*1,8. Это произойдёт из-за использования в исходной формуле относительной ссылки на ячейку A3. Такая формула будет работать до тех пор, пока не понадобиться изменить коэффициент с 1,8 на 1,95 и пересчитать все значения в столбце Зарплата.
Для автоматизации процесса число 1,8 заменяем на ссылку на ячейку, его содержащую. В ячейке B3 получим формулу =A3*B1. Но при копировании в ячейку B4 формула автоматически примет вид =A4*B2 и результаты вычислений будут неправильными.
Причина этого то, что обе ссылки были относительными. Для правильных вычислений необходимо закрепить ячейку B1. Для этого надо:
1. перейти в режим редактирования формулы, выполнив двойной щелчок левой кнопкой мыши по соответствующей ячейке или одиночный щелчок по строке формул;
2. добавить знак “$” в ссылку (вручную с клавиатуры, поместив курсор мыши в нужное место в имени, или выделить ссылку и нажать на клавиатуре клавишу F4).
В зависимости от положения $, ячейки в формулах можно закрепить по столбцу, строке или по обеим позициям сразу. Рассмотрим реализацию трёх вариантов закрепления ячейки на ещё одном примере.
Закрепление ячейки по столбцу
Знак $ стоит перед названием столбца в имени ячейки, например $B2. При копировании формулы из текущей ячейки в любую другую, она всегда будет ссылаться на столбец B. При этом строка будет меняться, сохраняя отношение, которое было обозначено в формуле изначально.
Если использовать для формулы маркер автозаполнения, то протянув его по горизонтали наша ячейка останется на месте.
Закрепление ячейки по строке
Знак $ стоит перед номером строки, например C$2. Теперь при копировании формулы наша ячейка всегда останется в строке номер 2. Столбцы же будут меняться, сохраняя соотношения с формулой.
Для маркера автозаполнения при протяжке по вертикали наша ячейка останется неизменна.
Полное закрепление ячейки
Но чаще всего перед пользователем стоит задача, чтобы ячейка, на которую ссылается формула, не менялась ни при каких условиях. Тогда знак $ ставят в обоих случаях, закрепляя ячейку одновременно по столбцу и строке, например$С$7. При таком закреплении ссылка на ячейку не изменится вне зависимости от способа копирования формулы.
Спасибо за Вашу оценку. Если хотите, чтобы Ваше имя
стало известно автору, войдите на сайт как пользователь
и нажмите Спасибо еще раз. Ваше имя появится на этой стрнице.
Понравился материал?
Хотите прочитать позже?
Сохраните на своей стене и
поделитесь с друзьями
Вы можете разместить на своём сайте анонс статьи со ссылкой на её полный текст
Ошибка в тексте? Мы очень сожалеем,
что допустили ее. Пожалуйста, выделите ее
и нажмите на клавиатуре CTRL + ENTER.