Як копіювати формули з автозаповненням в Excel 2016

Якщо вам потрібно просто скопіювати одну формулу в Excel 2016, скористайтеся функцією автозаповнення або командами Копіювати та Вставити. Цей тип копіювання формули, хоча й досить поширений, не можна зробити за допомогою перетягування.

Не забудьте опцію "Підсумки" в інструменті швидкого аналізу. Ви можете використовувати його для створення рядка або стовпця підсумків у нижньому або правому краю таблиці даних. Просто виберіть таблицю як діапазон комірок і натисніть кнопку Швидкий аналіз, а потім Загальні підсумки на її палітрі. Потім натисніть кнопку «Сума» на початку палітри, щоб створити формули, які підсумовують стовпці в новому рядку в нижній частині таблиці та/або кнопку «Сума» в кінці палітри, щоб створити формули, які підсумовують рядки в новому рядку. стовпець у правому кінці.

Ось як можна використовувати автозаповнення для копіювання однієї формули в діапазон комірок. На цьому малюнку ви можете побачити робочу таблицю Mother Goose Enterprises – 2016 Sales з усіма компаніями, але цього разу лише з одним місячним підсумком у рядку 12, який зараз копіюється через клітинку E12.

Копіювання формули в діапазон комірок за допомогою автозаповнення.

На наступному малюнку показано робочий аркуш після перетягування маркера заповнення в клітинку B12, щоб вибрати діапазон комірок C12:E12 (куди слід скопіювати цю формулу).

Робочий аркуш після копіювання формули загальних місячних (і квартальних) продажів.

Умовно кажучи

На малюнку показано робочий аркуш після того, як формула в комірці скопійована в діапазон комірок C12:E12 і клітинка B12 активна. Зверніть увагу, як Excel обробляє копіювання формул. Вихідна формула в клітинці B12 виглядає так:

=СУМА(B3:B11)

Коли вихідна формула копіюється в клітинку C12, Excel трохи змінює формулу, щоб вона виглядала так:

=SUM(C3:C11)

Excel коригує посилання на стовпець, змінюючи його з B на C, оскільки ви скопіювали рядки зліва направо.

Коли ви копіюєте формулу в діапазон комірок, який розширюється вниз по рядках, Excel коригує номери рядків у скопійованих формулах, а не літери стовпців, щоб відповідати позиції кожної копії. Наприклад, клітинка E3 в робочому аркуші Mother Goose Enterprises – 2016 Sales містить таку формулу:

=СУМА(B3:D3)

Коли ви копіюєте цю формулу в клітинку E4, Excel змінює копію формули на таке:

=СУМА(B4:D4)

Excel налаштовує посилання на рядок, щоб підтримувати поточне положення нового рядка 4. Оскільки Excel коригує посилання на клітинки в копіях формули щодо напрямку копіювання, посилання на клітинки відомі як відносні посилання на клітинки .

Деякі речі абсолютні!

Усі нові створені вами формули, природно, містять відносні посилання на клітинки, якщо ви не вкажете інше. Оскільки більшість копій формул, які ви робите, вимагають коригування посилань на клітинки, вам рідко доводиться замислюватися над цим розташуванням. Потім час від часу ви стикаєтеся з винятком, який вимагає обмеження, коли і як посилання на клітинки коригуються в копіях.

Одним із найпоширеніших винятків є те, що потрібно порівняти діапазон різних значень з одним значенням. Найчастіше це трапляється, коли потрібно підрахувати, який відсоток кожна частина становить від загальної суми. Наприклад, у робочій таблиці Mother Goose Enterprises – 2016 Sales ви стикаєтеся з такою ситуацією під час створення та копіювання формули, яка обчислює, який відсоток кожний місячний підсумок (у діапазоні комірок B14:D14) становить щоквартальний підсумок у клітинці E12.

Припустимо, що ви хочете ввести ці формули в рядок 14 робочої таблиці Mother Goose Enterprises – 2016 Sales, починаючи з клітинки B14. Формула в клітинці B14 для розрахунку відсотка від продажів січня до загальної суми першого кварталу дуже проста:

=B12/E12

Ця формула ділить загальний обсяг продажів за січень у клітинці B12 на загальний квартал в E12 (що може бути простіше?). Однак подивіться, що станеться, якщо перетягнути маркер заповнення на одну клітинку вправо, щоб скопіювати цю формулу в клітинку C14:

=C12/F12

Коригування першого посилання на клітинку з B12 на C12 – це саме те, що лікар наказав. Однак коригування другого посилання на комірку з E12 на F12 є катастрофою. Ви не тільки не підраховуєте, який відсоток лютневих продажів у клітинці C12 від продажів у першому кварталі в E12, але й у підсумку ви отримаєте один із цих жахливих #DIV/0! помилки в клітинці C14.

Щоб заборонити Excel коригувати посилання на клітинку у формулі в будь-яких створених копіях, перетворіть посилання на клітинку з відносного в абсолютне. Ви можете зробити це, натиснувши функціональну клавішу F4, після того, як переведете Excel в режим редагування (F2). Excel вказує, що ви робите посилання на клітинку абсолютним, розміщуючи знаки долара перед літерою стовпця та номером рядка. Наприклад, на цьому малюнку клітинка B14 містить правильну формулу для копіювання в діапазон комірок C14:D14:

Копіювання формули для обчислення співвідношення місячних і квартальних продажів з абсолютним посиланням на клітинку.

=B12/$E$12

Подивіться на робочий аркуш після того, як ця формула буде скопійована в діапазон C14:D14 з маркером заповнення та виділена клітинка C14 (див. наступний малюнок). Зверніть увагу, що рядок формул показує, що ця клітинка містить таку формулу:

Робочий аркуш після копіювання формули з абсолютним посиланням на клітинку.

=C12/$E$12

Оскільки в оригінальній формулі E12 було змінено на $E$12, усі копії мають те саме абсолютне (незмінне) посилання.

Якщо ви помилилися та скопіювали формулу, де одне або кілька посилань на клітинку мали бути абсолютними, але ви залишили їх усі відносними, відредагуйте вихідну формулу таким чином:

Двічі клацніть клітинку з формулою або натисніть F2, щоб відредагувати її.

Розмістіть точку вставки де-небудь на посиланні, яке потрібно перетворити в абсолютне.

Натисніть F4.

Коли ви закінчите редагувати, натисніть кнопку Enter на панелі формул, а потім скопіюйте формулу в заплутаний діапазон комірок за допомогою маркера заповнення.

Обов’язково натисніть F4 лише один раз, щоб змінити посилання на клітинку на повністю абсолютне, як я описав раніше. Якщо ви натиснете функціональну клавішу F4 вдруге, ви отримаєте так зване змішане посилання, де лише частина рядка є абсолютною, а частина стовпця — відносною (як у E$12). Якщо потім знову натиснути F4, Excel відкриває інший тип змішаного посилання, де частина стовпця є абсолютною, а частина рядка — відносною (як у $E12). Якщо ви продовжите і знову натиснете F4, Excel поверне посилання на клітинку на повністю відносне (як у E12).

Після того, як ви повернетеся з того місця, де почали, ви можете продовжувати використовувати клавішу F4, щоб знову перебирати цей самий набір змін посилання на клітинку.

Якщо ви використовуєте Excel 2016 на пристрої без доступу до фізичної клавіатури з функціональними клавішами (наприклад, на планшеті з сенсорним екраном), єдиний спосіб перетворити адреси комірок у ваших формулах з відносного в абсолютні або будь-якої форми змішаної адреси – це відкрити Доторкніться клавіатури та скористайтеся нею, додайте знаки долара перед літерою стовпця та/або номером рядка у відповідній адресі клітинки на рядку формул.