Как да копирате формули с автоматично попълване в Excel 2016

Ако просто трябва да копирате една формула в Excel 2016, използвайте функцията за автоматично попълване или командите за копиране и поставяне. Този тип копиране на формула, макар и доста често, не може да се направи с плъзгане и пускане.

Не забравяйте опцията Totals в инструмента за бърз анализ. Можете да го използвате, за да създадете ред или колона с общи суми в долния или десния край на таблица с данни за миг. Просто изберете таблицата като диапазон от клетки и щракнете върху бутона Бърз анализ, последван от Общо в нейната палитра. След това щракнете върху бутона Sum в началото на палитрата, за да създадете формули, които сумират колоните в нов ред в долната част на таблицата и/или бутона Sum в края на палитрата, за да създадете формули, които сумират редовете в нова колона в десния край.

Ето как можете да използвате AutoFill, за да копирате една формула в диапазон от клетки. На тази фигура можете да видите работния лист Mother Goose Enterprises – 2016 Sales с всички компании, но този път само с една месечна сума в ред 12, която е в процес на копиране през клетка E12.

Копиране на формула в диапазон от клетки с автоматично попълване.

Следващата фигура показва работния лист след плъзгане на манипулатора за запълване в клетка B12, за да изберете диапазона от клетки C12:E12 (където тази формула трябва да бъде копирана).

Работният лист след копиране на формулата за общо месечните (и тримесечните) продажби.

Относително казано

Фигурата показва работния лист, след като формулата в клетка е копирана в диапазона от клетки C12:E12 и клетка B12 е активна. Забележете как Excel се справя с копирането на формули. Оригиналната формула в клетка B12 е, както следва:

=SUM(B3:B11)

Когато оригиналната формула се копира в клетка C12, Excel променя леко формулата, така че да изглежда така:

=SUM(C3:C11)

Excel коригира препратката към колоната, променяйки я от B на C, защото сте копирали отляво надясно през редовете.

Когато копирате формула в диапазон от клетки, който се простира надолу по редовете, Excel коригира номерата на редовете в копираните формули, а не буквите на колоните, за да отговарят на позицията на всяко копие. Например клетка E3 в работния лист Mother Goose Enterprises – 2016 Sales съдържа следната формула:

=SUM(B3:D3)

Когато копирате тази формула в клетка E4, Excel променя копието на формулата на следното:

=SUM(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 на устройство без достъп до физическа клавиатура с функционални клавиши (като таблет със сензорен екран), единственият начин да конвертирате адресите на клетките във вашите формули от относителни към абсолютни или някаква форма на смесен адрес е да отворите докоснете клавиатурата и я използвайте, добавете знаците за долар преди буквата на колоната и/или номера на реда в съответния адрес на клетка в лентата с формули.