Содержание:
Проблема
Предположим, у нас есть такая простая таблица, в которой суммы рассчитываются за каждый месяц в двух городах, а затем сумма пересчитывается в евро по курсу из желтой ячейки J2.
Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь еще на листе, то Microsoft Excel автоматически исправит ссылки в этих формулах, переместив их на новое место и перестанет считать:
Задача: скопировать диапазон с формулами так, чтобы формулы не менялись и оставались прежними, сохраняя результаты вычислений.
Способ 1. Абсолютные ссылки
Способ 2: временно отключить формулы
Чтобы формулы не менялись при копировании, необходимо (временно) убедиться, что Excel перестает обрабатывать их как формулы. Это можно сделать, заменив знак равенства (=) любым другим символом, который обычно не встречается в формулах, например знаком решетки (#) или парой амперсандов (&&) для обозначения времени копирования. Для этого:
- Выделите диапазон с формулами (в нашем примере D2:D8)
- Нажмите Ctrl + H на клавиатуре или на вкладке Главная – Найти и выбрать – Заменить (Главная страница — Найти и выбрать — Заменить)
- В появившемся диалоговом окне вводим то, что ищем и чем заменяем, и в параметры (Параметры) не забудь уточнить Область поиска – формулы. Мы нажимаем Заменить все (Заменить все).
- Скопируйте полученный диапазон с деактивированными формулами в нужное место:
- Замените # on = обратно, используя то же окно, возвращая функциональность формулам.
Способ 3: копирование через блокнот
Этот метод намного быстрее и проще.
Нажмите сочетание клавиш Ctrl+Ё или кнопка Показать формулы таб формула (Формулы — Показать формулы), включить режим проверки формул – вместо результатов в ячейках будут отображаться формулы, по которым они рассчитаны:
Скопируйте наш диапазон D2:D8 и вставьте его в стандарт. ноутбук:
Теперь выделите все вставленное (Ctrl+A), снова скопируйте в буфер обмена (Ctrl+C) и вставьте на лист в нужное вам место:
Осталось только нажать кнопку Показать формулы (Показать формулы)чтобы вернуть Excel в обычный режим.
Примечание: этот метод иногда не работает в сложных таблицах с объединенными ячейками, но в подавляющем большинстве случаев он работает нормально.
Способ 4. Макрос
Если вам часто приходится делать такое копирование формул без смещения ссылок, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt + F11 или кнопка Визуальный Бейсик таб разработчик (Разработчик), вставляем новый модуль через меню Вставка – Модуль и скопируйте туда текст этого макроса:
Sub Copy_Formulas() Затемнять copyRange As Range, PasteRange As Range При ошибке Возобновить следующий набор copyRange = Application.InputBox("Выберите ячейки с формулами для копирования.", _ "Точно скопировать формулы", По умолчанию:=Selection.Address, Type := 8) Если copyRange ничего не значит, выйдите из подмножества PasteRange = Application.InputBox("Теперь выберите диапазон вставки." & vbCrLf & vbCrLf & _ "Диапазон должен быть равен по размеру исходному диапазону ячеек " & vbCrLf & _ " для копирования." , "Точно скопировать формулы", _ По умолчанию:=Выбор.Адрес, Тип:=8) Если PasteRange.Cells.Count <> copyRange.Cells.Count Тогда MsgBox "Диапазоны копирования и вставки различаются по размеру!", vbExclamation, «Ошибка копирования» Exit Sub End Если если PasteRange ничего не значит, то Exit Sub Else PasteRange.Formula = copyRange.Formula End If End SubВы можете использовать кнопку для запуска макроса. Макрос таб разработчик (Разработчик — Макросы) или сочетание клавиш Alt + F8. После запуска макроса он попросит выбрать диапазон с исходными формулами и диапазоном вставки и автоматически скопирует формулы:
- Удобный просмотр формул и результатов одновременно
- Зачем нужен стиль ссылок R1C1 в формулах Excel
- Как быстро найти все ячейки с формулами
- Инструмент для копирования точных формул из дополнения PLEX