Дізнайтеся, як копіювати формулу в кілька клітинок, вниз по стовпцю, у несуміжні клітинки, копіювати формули з абсолютними або змішаними посиланнями на клітинки тощо.
Копіювання формул є одним із найпоширеніших і найпростіших завдань, які ви виконуєте в звичайній електронній таблиці, яка ґрунтується переважно на формулах. Замість того, щоб знову і знову вводити ту саму формулу в Excel, ви можете просто скопіювати та вставити формулу з однієї клітинки в кілька клітинок.
Після написання формули в Excel ви можете використовувати команди копіювання та вставлення до кількох клітинок, кількох несуміжних комірок або цілих стовпців. Якщо ви не зробите це правильно, ви отримаєте жахливі помилки # REF і /DIV0. У цій статті ми покажемо вам різні методи, які можна використовувати для копіювання формул у Excel.
Як копіювати та вставляти формули в Excel
Microsoft Excel надає різні способи копіювання формул з відносними посиланнями на клітинки, абсолютними посиланнями на клітинки або змішаними посиланнями.
- Скопіюйте формулу з однієї клітинки в іншу
- Скопіюйте формулу з однієї клітинки в декілька клітинок
- Копіювання формули на весь стовпець
- Копіювання формули без форматування
- Копіювати формули в несуміжні клітинки
- Копіювати формули, не змінюючи посилання на клітинки
Як скопіювати формулу з однієї клітинки в іншу в Excel
Іноді ви можете скопіювати формулу з однієї клітинки в іншу в Excel, щоб уникнути повторного введення всієї формули і заощадити деякий час при цьому.
Скажімо, у нас є така таблиця:
Існує кілька методів копіювання формули з однієї клітинки в іншу.
Спочатку виділіть клітинку з формулою і клацніть правою кнопкою миші, а потім у контекстному меню виберіть «Копіювати», щоб скопіювати формулу. Або ви можете скористатися опцією «Копіювати» в розділі «Буфер обміну» на вкладці «Головна».
Але ви також копіюєте формули, просто натискаючи комбінацію клавіш Ctrl + C. Це більш ефективний і економічний спосіб.
Потім ми переходимо до клітинки, яку ми хочемо вставити, натискаємо ярлик Ctrl + V щоб вставити формулу. Або клацніть правою кнопкою миші клітинку, яку потрібно вставити, і виберіть параметри в розділі «Параметри вставки»: або простий параметр «Вставити (P)», або вставити як варіант «Формула (F)».
Крім того, ви також можете натиснути «Спеціальна вставка» під шістьма значками вставки, щоб відкрити діалогове вікно «Спеціальна вставка». Тут у вас є кілька варіантів, включаючи шість параметрів вставки з контекстного меню. Виберіть «Усі» або «Формули» в розділі «Вставити» і натисніть «ОК».
Тепер клітинка з вставленою формулою повинна мати ті самі формули (як і в скопійованій клітинці), але з різними посиланнями на клітинку. Адреса комірки самостійно коригується Excel, щоб відповідати номеру рядка вставленої комірки.
Скопіюйте формулу з однієї клітинки в кілька клітинок
Та сама операція вставлення працює так само, якщо ми виділяємо кілька комірок або діапазон комірок.
Виділіть клітинку з формулою і натисніть Ctrl + C щоб скопіювати формулу. Потім виділіть усі клітинки, куди потрібно вставити формулу, і натисніть Ctrl + V щоб вставити формулу, або скористайтеся одним із наведених вище методів вставки для вставки формули (як ми зробили для однієї клітинки).
Скопіюйте формулу на весь стовпець або рядок
У Excel можна швидко скопіювати формулу на весь стовпець або рядок.
Щоб скопіювати формулу в стовпець або рядок, спочатку введіть формулу в клітинку. Потім виберіть клітинку формули (D1) і наведіть курсор на маленький зелений квадрат у правому нижньому куті комірки. Під час наведення курсор зміниться на чорний знак плюса (+), який називається маркером заповнення. Клацніть і утримуйте цей маркер заповнення та перетягніть його в будь-якому напрямку (стовпець або рядок) на клітинки, щоб скопіювати формулу.
Коли ви копіюєте формулу в діапазон комірок, посилання на клітинки формули автоматично коригуються на основі відносного розташування рядків і стовпців, і формула виконуватиме обчислення на основі значень у цих посиланнях на клітинки (див. нижче).
У наведеному вище прикладі, коли формула в D1 (=A1*B1)/2) скопійована в клітинку D2, відносне посилання змінюється на основі його розташування (=A2*B2)/2) тощо.
Таким же чином можна перетягувати формулу в сусідні клітинки ліворуч, праворуч або вгору.
Інший спосіб скопіювати формулу на весь стовпець – двічі клацнути маркер заповнення, а не перетягувати його. Коли ви двічі клацнете маркер заповнення, він негайно застосовує формулу, якщо є будь-які дані до сусідньої клітинки.
Скопіюйте формулу в діапазон без копіювання форматування
Коли ви копіюєте формулу в діапазон комірок за допомогою маркера заповнення, вона також копіює форматування вихідної клітинки, наприклад колір шрифту або колір фону, валюту, відсоток, час тощо (як показано нижче).
Щоб запобігти копіювання форматування комірки, перетягніть маркер заповнення та натисніть «Параметри автозаповнення» у нижньому правому куті останньої комірки. Потім у спадному меню виберіть «Заповнити без форматування».
Результат:
Скопіюйте формулу Excel лише з числовим форматуванням
Якщо ви хочете скопіювати формулу лише з формулою та таким форматуванням, як відсотковий формат, десяткові коми тощо.
Скопіюйте формулу та виділіть усі клітинки, до яких потрібно скопіювати формулу. На вкладці «Головна» натисніть стрілку під кнопкою «Вставити» на стрічці. Потім натисніть значок «Формули та форматування чисел» (значок із % fx) у спадному меню, щоб вставити лише формулу та форматування чисел.
Цей параметр копіює лише форматування формул і чисел, але ігнорує всі інші форматування клітинок, наприклад колір фону, колір шрифту тощо.
Скопіюйте формулу в несуміжні/несуміжні клітинки
Якщо ви хочете скопіювати формулу в несуміжні клітинки або несуміжні діапазони, ви можете зробити це за допомогою Ctrl ключ.
Виділіть клітинку з формулою і натисніть Ctrl + C щоб скопіювати його. Потім виділіть несуміжні клітинки/діапазони, натискаючи й утримуючи Ctrl ключ. Потім натисніть Ctrl + V щоб вставити формулу та натиснути Введіть Завершувати.
Копіювання формул без зміни посилань на клітинки в Excel
Коли формула копіюється в іншу клітинку, Excel автоматично змінює посилання на клітинку, щоб відповідати її новому розташуванню. Ці посилання на клітинку використовують відносне розташування адреси осередку, тому вони називаються відносними посиланнями на клітинку (без $). Наприклад, якщо у вас є формула «=A1*B1» у клітинці C1, і ви скопіюєте цю формулу в клітинку C2, формула зміниться на «=A2*B2». Усі методи, які ми обговорювали вище, використовують відносні посилання.
Коли ви копіюєте формулу з відносними посиланнями на клітинки, вона автоматично змінює посилання, щоб формула посилалася на відповідні рядки та стовпці. Якщо ви використовуєте абсолютні посилання у формулі, то та сама формула копіюється без зміни посилань на клітинки.
Коли ви ставите знак долара ($) перед літерою стовпця та номером рядка клітинки (наприклад, $A$1), це перетворює клітинку в абсолютну. Тепер незалежно від того, куди ви скопіюєте формулу, яка містить абсолютне посилання на клітинку, формула ніколи не буде. Але якщо у формулі є відносне або змішане посилання на клітинку, скористайтеся будь-яким із наведених нижче методів для копіювання, не змінюючи посилання на клітинку.
Скопіюйте формулу з абсолютним посиланням на клітинку за допомогою методу копіювання та вставки
Іноді вам може знадобитися скопіювати/застосувати точну формулу вниз по стовпцю, не змінюючи посилання на клітинки. Якщо ви хочете скопіювати або перемістити точну формулу з абсолютним посиланням, зробіть це:
Спочатку виділіть клітинку з формулою, яку потрібно скопіювати. Потім клацніть на рядку формул, виберіть формулу за допомогою миші та натисніть Ctrl + C щоб скопіювати його. Якщо ви хочете перемістити формулу, натисніть Ctrl + X розрізати його. Далі натисніть на вихід клавішу, щоб вийти з рядка формул.
Або виберіть клітинку з формулою та натисніть F2 клавішу або двічі клацніть клітинку. Це переведе вибрану клітинку в режим редагування. Потім виберіть формулу в клітинці та натисніть Ctrl + C щоб скопіювати формулу в комірку як текст.
Потім виберіть комірку призначення та натисніть Ctrl + V щоб вставити формулу.
Тепер точна формула копіюється в клітинку призначення без будь-яких змін посилання на клітинку.
Копіюйте формули з абсолютними або змішаними посиланнями на клітинки
Якщо ви хочете перемістити або скопіювати точні формули, не змінюючи посилання на клітинки, вам слід змінити відносні посилання на клітинки на абсолютні. Наприклад, додавання знака ($) до відносного посилання на клітинку (B1) робить його абсолютним посиланням ($B$1), тому воно залишається статичним незалежно від того, куди скопійовано чи переміщено формулу.
Але іноді вам може знадобитися використовувати змішані посилання на клітинки ($B1 або B$1), додавши знак долара ($) перед літерою стовпця або номером рядка, щоб зафіксувати рядок або стовпець на місці.
Пояснимо на прикладі. Припустимо, що у вас є ця таблиця, яка обчислює щомісячні заощадження шляхом віднімання орендної плати (B9) із заробітку (у стовпці B) щомісяця.
У наведеному нижче прикладі формула використовує абсолютне посилання на клітинку ($B$9), щоб зафіксувати суму орендної плати в клітинці B9, і відносне посилання на клітинку B2, оскільки її потрібно коригувати для кожного рядка для відповідності кожного місяця. B9 є абсолютним посиланням на клітинку ($ B$9), тому що ви хочете відняти ту саму суму орендної плати від кожного місячного заробітку.
Скажімо, ви хочете перемістити залишки зі стовпця C в стовпець E. Якщо ви скопіюєте формулу (звичайним методом копіювання/вставлення) з клітинки C2 (=B2-$B$9), після вставки зміниться на =D2-$B$9 у комірці E2, роблячи ваші розрахунки неправильними!
У цьому випадку змініть відносне посилання на клітинку (B2) на змішане посилання на клітинку ($B2), додавши знак «$» перед літерою стовпця формули, введеної в клітинку C2.
А тепер, якщо ви скопіюєте або перемістите формулу з комірки C2 до E2 або будь-якої іншої клітинки та застосуєте формулу вниз по стовпцю, посилання на стовпець залишиться незмінним, а номер рядка буде змінено для кожної клітинки.
Скопіюйте і вставте формули Excel, не змінюючи посилання за допомогою Блокнота
Ви можете побачити кожну формулу в електронній таблиці Excel за допомогою параметрів Показати формулу. Для цього перейдіть на вкладку «Формули» та виберіть «Показати формули».
Крім того, ви можете увійти в режим перегляду формул, натиснувши Ctrl + ` ярлик, який відображає кожну формулу на аркуші. Ви можете знайти клавішу серйозного акценту (`) у верхньому лівому куті клавіатури в рядку з цифровими клавішами (під ВИХІД та перед клавішею цифри 1).
Виділіть усі клітинки з формулами, які потрібно скопіювати, і натисніть Ctrl + C скопіювати їх, або Ctrl + X розрізати їх. Потім відкрийте Блокнот і натисніть Ctrl + V щоб вставити формули в блокнот.
Далі виберіть формулу та скопіюйте (Ctrl + C) це з блокнота та вставте (Ctrl + V) у клітинку, куди потрібно скопіювати точну формулу. Ви можете скопіювати та вставити їх по одному або всі відразу.
Після вставки формул вимкніть режим перегляду формул, натиснувши Ctrl + ` або знову перейдіть до «Формули» -> «Показати формули».
Скопіюйте точні формули за допомогою програми «Знайти та замінити» в Excel
Якщо ви хочете скопіювати ряд точних формул, ви також можете скористатися інструментом «Знайти та замінити» Excel.
Виділіть усі клітинки з формулами, які потрібно скопіювати. Потім перейдіть на вкладку «Головна», натисніть «Знайти та виберіть» у групі «Редагування» та виберіть опцію «Замінити» або просто натисніть Ctrl + H щоб відкрити діалогове вікно Знайти та замінити.
У діалоговому вікні «Знайти та замінити» введіть знак рівності (=) у полі «Знайти». У полі «Замінити на» введіть символ або символ, який ще не є частиною ваших формул, наприклад # або , тощо. Потім натисніть кнопку «Замінити все».
Ви отримаєте вікно повідомлення з написом «Ми зробили 6 замін» (оскільки ми вибрали 6 комірок з формулами). Потім натисніть «ОК» і «Закрити», щоб закрити обидва діалогові вікна. Це замінює всі знаки рівності (=) на знаки хеша (#) і перетворює формули на текстові рядки. Тепер посилання на клітинки формул не будуть змінені під час копіювання.
Тепер ви можете вибрати ці клітинки, натисніть Ctrl + C щоб скопіювати їх і вставити в цільові клітинки за допомогою Ctrl + V.
Нарешті, вам потрібно змінити знаки (#) назад на знаки (=). Для цього виберіть обидва діапазони (оригінальний і скопійований) і натисніть Ctrl + H щоб відкрити діалогове вікно Знайти та замінити. Цього разу введіть хеш (#) у поле «Знайти, що» і знак рівності (=) у полі «Замінити на» і натисніть кнопку «Замінити все». Натисніть «Закрити», щоб закрити діалогове вікно.
Тепер текстові рядки перетворюються назад у формули, і ви отримаєте такий результат:
Готово!