Копирайте формули без изместване на връзката

Проблем

Да предположим, че имаме проста таблица като тази, в която сумите се изчисляват за всеки месец в два града и след това общата сума се конвертира в евро по курса от жълтата клетка J2.

Проблемът е, че ако копирате диапазона D2:D8 с формули някъде другаде в листа, Microsoft Excel автоматично ще коригира връзките в тези формули, премествайки ги на ново място и спирайки броенето:

Задача: копирайте диапазона с формули, така че формулите да не се променят и да останат същите, като запазите резултатите от изчислението.

Метод 1. Абсолютни връзки

Метод 2: Временно деактивирайте формулите

За да предотвратите промяна на формулите при копиране, трябва (временно) да се уверите, че Excel спира да ги третира като формули. Това може да стане чрез замяна на знака за равенство (=) с всеки друг знак, който обикновено не се среща във формулите, като например знак за решетка (#) или чифт амперсанд (&&) за време за копиране. За това:

1. Изберете диапазона с формули (в нашия пример D2:D8)
2. Кликнете Ctrl + H на клавиатурата или на раздел Начало – Търсене и избор – Замяна (Начало — Търсене и избор — Замяна)

   

3. В появилия се диалогов прозорец въвеждаме какво търсим и с какво заместваме и в параметри (Настроики) не забравяйте да поясните Обхват на търсене – Формули. Натискаме Сменете всички (Замени всички).
4. Копирайте получения диапазон с деактивирани формули на правилното място:

   

5. Сменете # on = обратно, използвайки същия прозорец, връщайки функционалността на формулите.

Метод 3: Копирайте чрез Notepad

Този метод е много по-бърз и лесен.

Натиснете клавишната комбинация Ctrl+Ё или бутон Показване на формули етикет формула (Формули — Показване на формули), за включване на режима за проверка на формулата – вместо резултатите в клетките ще се показват формулите, по които са изчислени:

Копирайте нашия диапазон D2:D8 и го поставете в стандарта Ноутбук:

Сега изберете всичко поставено (Ctrl + A), копирайте го отново в клипборда (Ctrl + C) и го поставете върху листа на мястото, от което се нуждаете:

Остава само да натиснете бутона Показване на формули (Показване на формули)за да върнете Excel в нормален режим.

Забележка: този метод понякога се проваля при сложни таблици с обединени клетки, но в по-голямата част от случаите работи добре.

Метод 4. Макро

Ако често трябва да правите такова копиране на формули без преместване на препратки, тогава има смисъл да използвате макрос за това. Натиснете клавишна комбинация Alt + F11 или бутон Visual Basic етикет предприемач (Разработчик), вмъкнете нов модул през менюто Вмъкване – Модул  и копирайте текста на този макрос там:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Изберете клетки с формули за копиране.", _ "Копирайте точно формули", По подразбиране:=Selection.Address, Тип := 8) Ако copyRange не е нищо, тогава излезте от Sub Set pasteRange = Application.InputBox("Сега изберете диапазона за поставяне." & vbCrLf & vbCrLf & _ "Диапазонът трябва да е равен по размер на оригиналния " & vbCrLf & _ " диапазон от клетки за копиране." , "Копиране на формули точно", _ По подразбиране:=Selection.Address, Тип:=8) Ако 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

Можете да използвате бутона, за да стартирате макроса. Макроси етикет предприемач (Програмист — Макроси) или клавишна комбинация Alt + F8. След стартиране на макроса, той ще ви помоли да изберете диапазона с оригиналните формули и диапазона за вмъкване и автоматично ще копира формулите:

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