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

Проблема

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

exact-formulas-copy1.png

Проблема в том, что если скопировать диапазон D2:D8 с формулами куда-нибудь в другое место на лист, то Microsoft Excel автоматически скорректирует ссылки в этих формулах, сдвинув их на новое место и перестав считать:

exact-formulas-copy2.png

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

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

Как можно заметить по предыдущей картинке, Excel сдвигает только относительные ссылки. Абсолютная (со знаками $) ссылка на желтую ячейку $J$2 не сместилась. Поэтому для точного копирования формул можно временно перевести все ссылки во всех формулах в абсолютные. Нужно будет выделить каждую формулу в строке формул и нажать клавишу F4:

exact-formulas-copy9.png

При большом количестве ячеек этот вариант, понятное дело, отпадает - слишком трудоемко.

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

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

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

    exact-formulas-copy3.png

  3. В появившемся диалоговом окне вводим что ищем и на что заменяем и в Параметрах (Options) не забываем уточнить Область поиска - Формулы. Жмем Заменить все (Replace all).
  4. Копируем получившийся диапазон с деактивированными формулами в нужное место:

    exact-formulas-copy4.png

  5. Заменяем # на = обратно с помощью того же окна, возвращая функциональность формулам.

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

Этот способ существенно быстрее и проще.

Нажмите сочетание клавиш Ctrl+Ё или кнопку Показать формулы на вкладке Формулы (Formulas - Show formulas), чтобы включить режим проверки формул - в ячейках вместо результатов начнут отображаться формулы, по которым они посчитаны:

exact-formulas-copy5.png

Скопируйте наш диапазон D2:D8 и вставьте его в стандартный Блокнот:

exact-formulas-copy6.png

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

exact-formulas-copy7.png

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

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

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

Если подобное копирование формул без сдвига ссылок вам приходится делать часто, то имеет смысл использовать для этого макрос. Нажмите сочетание клавиш Alt+F11 или кнопку Visual Basic на вкладке Разработчик (Developer), вставьте новый модуль через меню Insert - Module  и скопируйте туда текст вот такого макроса:

Sub Copy_Formulas()
    Dim copyRange As Range, pasteRange As Range
    
    On Error Resume Next
    Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые надо скопировать.", _
                                "Точное копирование формул", Default:=Selection.Address, Type:=8)
    If copyRange Is Nothing Then Exit Sub
    Set pasteRange = Application.InputBox("Теперь выделите диапазон вставки." & vbCrLf & vbCrLf & _
                                          "Диапазон должен быть равен по размеру исходному " & vbCrLf & _
                                          "диапазону копируемых ячеек.", "Точное копирование формул", _
                                          Default:=Selection.Address, Type:=8)
    
    If pasteRange.Cells.Count <> copyRange.Cells.Count Then
        MsgBox "Диапазоны копирования и вставки разного размера!", vbExclamation, "Ошибка копирования"
        Exit Sub
    End If
    
    If pasteRange Is Nothing Then
        Exit Sub
    Else
        pasteRange.Formula = copyRange.Formula
    End If
End Sub

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

exact-formulas-copy8.png

Ссылки по теме

 



12.01.2014 16:22:35
Николай, спасибо большое. Позволил себе немного усовершенствовать ваш макрос. На мой взгляд неудобно высчитывать количество ячеек, которые нужно копировать, а затем вставлять. Поэтому лучше выделить одну ячейку начала вставки. В связи с этим и изменения:

Sub Copy_Formulas()
 Dim copyRange As Range, pasteRange As Range
 
 On Error Resume Next
 Set copyRange = Application.InputBox("Выделите ячейки с формулами, которые нужно скопировать", _
 "Точное копирование формул", Default:=Selection.Address, Type:=8
 If copyRange Is Nothing Then Exit Sub

 'подсчет строк и столбцов копируемого диапазона
 copyrangerows = copyRange.Cells.Rows.Count
 copyrangecols = copyRange.Cells.Columns.Count

 Set pasteRange = Application.InputBox("Выделите первую ячейку для вставки формул", "Точное копирование формул"
 Default:=Selection.Address, Type:=8 
 If pasteRange Is Nothing Then
 Exit Sub
 Else
 'изменение диапазона вставки
 Set pasteRange = pasteRange.Resize(copyrangerows, copyrangecols)
 pasteRange.Formula = copyRange.Formula
 End If
End Sub
С уважением, Аркадий Пилипенко
12.01.2014 17:07:35
Хорошая доработка, Аркадий, спасибо! :)
12.01.2014 16:38:58
Николай, а самый простой и быстрый способ?
Скопировать в строке формул формулу из D2 и вставить её в G2, после чего "протянуть" вниз

Так же можно просто дать ссылку в G2 на D2
=D2 
и скопировать её вниз?
Или сразу сослаться в G2:G8 формулой массива, введённой сразу во все ячейки =D2:D8?

Или "перетянув" D2:D8 в G2:G8 с зажатой ПКМ выбрать "Связать"?
12.01.2014 17:07:08
Serge, в приведенном примере простой случай. Протянуть можно если формулы в столбце одинаковые, а если нет?
И когда делаешь "связать", то формула не копируется, а делается ссылка на исходные ячейки.
12.01.2014 17:20:27
Твой пример сбил с толку, в нем формулы-то одинаковые :)

Верно, но результат-то получается такой же как и при копировании формул ;)
25.01.2014 12:01:01
Можно еще формулы перевести в текстовый формат и копировать куда угодно.
28.01.2014 12:47:33
Если для ячейки с формулой поставить текстовый формат, то мы увидим не формулу, а результат, но в текстовом формате. А чтобы увидеть формулу (для копирования), нужно в ячейку войти и нажать Enter. В каждую. А потом после копирования и переключения формата обратно, нужно будет также войти в каждую, чтобы переключить формулу обратно в результат.
Ужасно неудобно, хотя, конечно, тоже способ :)
28.01.2014 14:54:20
Я тренеровался на одной ячейке, поэтому не подумал, что их может 10 тыс. ))
24.02.2014 15:41:02
Я обычно в таких ситуациях делаю следующим образом:
1) копирую нужный столбец в произвольные ячейки (формулы пересчитываются)
2) вырезаю нужный столбец и вставляю его в "пункт назначения"
3) скопированные ранее ячейки копирую в начальный диапозон (все возвращается на свои места)
06.03.2014 12:14:07
Хороший макрос
01.08.2014 05:18:18
Спасибо Николай!
А как заставить данный макрос работать при копировании формул между книгами?
20.09.2014 17:43:13
Добрый день и спасибо за полезное видео!

Маленькое замечание: у меня ошибка возникает, когда запускаю данный макрос (ошибка в синтаксе). Убрал <br> в конце (т.е. оставил только End Sub) и макрос заработал. В видео правильный макрос, а в тексте <br>.
21.09.2014 09:43:07
Спасибо, Роман! Небольшой косяк верстки случился - подправили.
01.07.2015 22:25:18
Добрый день
(СЖПРОБЕЛЫ)
1.все получаетса, но мне нужен текст , а не формулы . когда я копирую формулу и вставляю спец . вставкой "значения" пробелы возвращаютса,что делать?
08.06.2017 09:26:07
А можно сделать не копирование, а перенос формулы из в определенный диапазон столбцов (с удалением формулы в исходной ячейке?)
22.12.2017 12:26:03
Стесняюсь спросить, почему нельзя просто выделить нужный диапазон и просто перетащить его мышкой?
Тот же самый эффект будет через "вырезать-вставить"...
Наверх