Как скопировать формулу в excel макрос

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

Проблема

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

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

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

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

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

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

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

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

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

   

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

   

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

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

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

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

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

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

Осталось только отжать кнопку Показать формулы (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. После запуска макрос попросит вас выделить диапазон с исходными формулами и диапазон вставки и произведет точное копирование формул автоматически:

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

• Удобный просмотр формул и результатов одновременно
• Зачем нужен стиль ссылок R1C1 в формулах Excel
• Как быстро найти все ячейки с формулами
• Инструмент для точного копирования формул из надстройки PLEX

Здравствуйте, может мой вопрос уже изъезжен, но не могу найти решение.
Необходимо скопировать формулу из ячейки одного документа в ячейку другого документа.
Опишу кратко, код там большой но все упирается в следующий отрезок.
Допустим в ячейке есть формула A1+B1
[vba]

[/vba]

В итоге в файл «Договоры2» копируется ячейка, а формула в ней ссылается на ячейки файла «Договоры1»
с результатом ‘C:…Договоры1′!A1+C:…Договоры1’!B1
Как быть? нужно чтобы получилось A1+B1
Я так понимаю можно какой то приписать параметр может быть?

With ActiveSheet
    .Range(.Cells(rowStarWs1, colAct), .Cells(rowLast, colAct)).Formula = .Cells(rowSuch2Ws1, colAct).Formula 
End With

2 ответа

Лучший ответ

Sub f()

    Dim f As String
    f = Range("C3").FormulaR1C1
    
    Range("C10:C13").FormulaR1C1 = f
    
End Sub

Range("C10:C13").FormulaR1C1 = Range("C3").FormulaR1C1

Cells(10, 3).Formula = Cells(3, 3).FormulaR1C1
Range("C10:C14").FillDown

Копирование формул

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

Пример. В
таблице, приведенной на рис. 18, рассчитать
сумму проданных товаров (Сумма =
Цена*Количество).

+

Рисунок
18 – Использование маркера заполнения
для
ввода формулы в ячейки столбца

Формула
для расчета суммы товаров набирается
в ячейке D2 (=В2*С2), затем с помощью маркера
заполнения она вносится в ячейки D3 и
D4.

Вычисление части формулы

При
поиске ошибок в составленной формуле
бывает удобно посмотреть результат
вычислений какой-то части формулы. Для
этого:

1. Сделайте активной ячейку, содержащую
   формулу.

2. В
   строке формул выделите часть формулы,
   которую нужно вычислить (рис. 19):

Рисунок 19 –
Вычисление части формулы

1. Поочередно
   нажмите кнопки F9
   (вычисление), Enter
   (результат
   вычисления вставить в формулу) и Esc
   (возврат формулы в исходное состояние).

Ввод ссылок на ячейки

Указать
положение ячейки можно с помощью
относительной
или
абсолютной
адресации.
Когда вы используете относительную
адресацию,
ссылки в формулах автоматически
изменяются при копировании формулы в
другое место. Например, если в ячейке
С7 содержится формула =С4*В7, то при
копировании этой формулы в ячейку С8
она преобразуется в =С5*В8.

Чтобы
ссылки в формуле не изменялись при
копировании формулы в другую ячейку,
надо использовать абсолютные
ссылки. Они обозначаются
знаком «$». Например, $С$4 – зафиксирована
ячейка С4.

Если
при копировании формулы нужно сохранить
фиксированным только один параметр,
т.е. номер строки или номер столбца,
используются смешанные
ссылки. Например, $СЗ
– номер столбца не меняется, номер
строки будет подстраиваться под новое
расположение формулы при копировании.
Для С$3 – фиксированный номер строки.

Пример
ввода ссылок на ячейки:

1. Выделить ячейку, в которую нужно ввести
   формулу.

2. Введите
   знак равенства «=». Затем введите адрес
   ячейки, которая должна быть в формуле,
   или щелкните по ней. Полученная ссылка
   будет относительной.

3. Чтобы
   из относительной ссылки получить
   абсолютную или смешанную ссылку, нажмите
   необходимое число раз клавишу F4,
   пока ссылка не отобразится в нужном
   виде.

4. Продолжайте
   ввод других значений, ссылок и операторов.

5. Если
   формула завершена, нажмите Enter.

8 Функции ms Excel

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

Вызов функций, задание аргументов

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

1 Выделить
ячейку, в которую надо поместить формулу,
и выполнить одно из следующих действий:

– из
основного меню вызовите команду Вставка
/ Функция;

– в
строке формул нажмите кнопку

(вставка функции). Откроется окно первого
шага Мастера функций
(рис. 20).

Рисунок 20 –
Мастер функций – шаг 1

2
Двигаясь по категориям и по названиям
функций, выберите необходимую функцию
(например, категория Статистические,
функция СРЗНАЧ).
Нажмите ОК.

3
Во втором окне Мастера
следует указать необходимые аргументы
функции (выделите диапазон ячеек или
отдельные ячейки) (рис. 21).

Рисунок
21 – Мастер функций – шаг 2

Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]

• #
• #
• #
• #
• #
• #
• #
• #
• #
• #
• #