зміст
Проблема
Припустімо, що ми маємо таку просту таблицю, у якій суми розраховуються за кожен місяць у двох містах, а потім загальна сума конвертується в євро за курсом у жовтій клітинці J2.
Проблема полягає в тому, що якщо ви скопіюєте діапазон D2:D8 з формулами в інше місце на аркуші, то Microsoft Excel автоматично виправить посилання в цих формулах, перемістивши їх на нове місце і припинивши підрахунок:
Завдання: скопіювати діапазон із формулами так, щоб формули не змінювалися і залишалися незмінними, зберігаючи результати обчислень.
Спосіб 1. Абсолютні посилання
Спосіб 2. Тимчасово вимкніть формули
Щоб запобігти зміні формул під час копіювання, потрібно (тимчасово) переконатися, що Excel перестає розглядати їх як формули. Це можна зробити, замінивши знак рівності (=) будь-яким іншим символом, який зазвичай не зустрічається у формулах, наприклад знаком решітки (#) або парою амперсандів (&&) для часу копіювання. Для цього:
- Виберіть діапазон із формулами (у нашому прикладі D2:D8)
- Натисніть Ctrl + H, на клавіатурі або на вкладці Головна – Знайти та вибрати – Замінити (Домашня сторінка — Знайти&Вибрати — Замінити)
- У діалоговому вікні, що з’явиться, введіть те, що ми шукаємо і чим замінюємо, і в параметри (Параметри) не забудьте уточнити Область пошуку – Формули. Натискаємо Replace all (Замінити все).
- Скопіюйте отриманий діапазон з деактивованими формулами в потрібне місце:
- заміщати # on = назад, використовуючи те саме вікно, повертаючи функціональність формулам.
Спосіб 3: Скопіюйте через Блокнот
Цей спосіб набагато швидший і простіший.
Натисніть комбінацію клавіш Ctrl+Ё або кнопку Показати формули таб формула (Формули — Показати формули), увімкнути режим перевірки формул – замість результатів у комірках відображатимуться формули, за якими вони обчислені:
Скопіюйте наш діапазон D2:D8 і вставте його в стандарт ноутбук:
Тепер виділіть все вставлене (Ctrl + A), знову скопіюйте в буфер обміну (Ctrl + C) і вставте на аркуш в потрібне вам місце:
Залишається тільки натиснути кнопку Показати формули (Показати формули)щоб повернути Excel у звичайний режим.
Примітка: цей метод іноді не працює зі складними таблицями з об’єднаними клітинками, але в переважній більшості випадків він працює добре.
Спосіб 4. Макрос
Якщо вам часто доводиться робити таке копіювання формул без зміщення посилань, то має сенс використовувати для цього макрос. Натисніть комбінацію клавіш Alt + F11 або кнопку Візуальний Бейсік таб розробник (розробник), вставте новий модуль через меню Вставка – модуль і скопіюйте туди текст цього макросу:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Виберіть комірки з формулами для копіювання.", _ "Копіювати формули точно", Default:=Selection.Address, Type := 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