Научете как да копирате формула в множество клетки, надолу по колона, към несмежни клетки, да копирате формули с абсолютни или смесени препратки към клетки и т.н.
Копирането на формули е една от най-често срещаните и лесни задачи, които правите в типична електронна таблица, която разчита главно на формули. Вместо да въвеждате една и съща формула отново и отново в Excel, можете просто лесно да копирате и поставите формула от една клетка в няколко клетки.
След като напишете формула в Excel, можете да използвате командите за копиране и поставяне в множество клетки, множество несъседни клетки или цели колони. Ако не го направите правилно, ще се окажете с тези ужасни грешки # REF и /DIV0. В тази статия ще ви покажем различните методи, които можете да използвате за копиране на формули в Excel.
Как да копирате и поставяте формули в Excel
Microsoft Excel предоставя различни начини за копиране на формули с относителни препратки към клетки, абсолютни препратки към клетки или смесени препратки.
- Копирайте формулата от една клетка в друга
- Копирайте формула една клетка в няколко клетки
- Копиране на формула към цялата колона
- Копиране на формула без форматиране
- Копирайте формули в несъседни клетки
- Копирайте формули, без да променяте препратките към клетките
Как да копирате формула от една клетка в друга в Excel
Понякога може да искате да копирате формула от една клетка в друга в excel, за да избегнете повторно въвеждане на цялата формула отново и да спестите известно време, докато правите това.
Да кажем, че имаме тази таблица:
Има няколко метода за копиране на формула от една клетка в друга.
Първо изберете клетката с формулата и щракнете с десния бутон на мишката и в контекстното меню изберете „Копиране“, за да копирате формулата. Или можете да използвате опцията „Копиране“ в секцията „Клипборд“ на раздела „Начало“.
Но вие също така копирате формули, като просто натиснете клавишната комбинация Ctrl + C. Това е по-ефективен и спестяващ време метод.
След това отиваме в клетката, която искаме да я поставим, натискаме прекия път Ctrl + V за да поставите формулата. Или щракнете с десния бутон върху клетката, която искате да поставите, и щракнете върху опциите под „Опции за поставяне“: или проста опция „Поставяне (P)“, или поставете като опция „Формула (F)“.
Като алтернатива можете също да кликнете върху „Специално поставяне“ под шестте икони за поставяне, за да отворите диалоговия прозорец „Специално поставяне“. Тук имате няколко опции, включително шестте опции за поставяне от контекстното меню. Изберете „Всички“ или „Формули“ под секцията „Поставяне“ и щракнете върху „OK“.
Сега клетката с поставена формула трябва да има същите формули (като тази в копираната клетка), но с различни препратки към клетки. Адресът на клетката се настройва самостоятелно от 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 да го отрежа. След това натиснете Esc клавиш, за да напуснете лентата с формули.
Друга възможност е да изберете клетката с формулата и да натиснете 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, без да променяте препратките с помощта на Notepad
Можете да видите всяка формула във вашата електронна таблица на Excel, като използвате опциите за показване на формула. За да направите това, отидете в раздела „Формули“ и изберете „Покажи формули“.
Като алтернатива можете да влезете в режима за преглед на формула, като натиснете Ctrl + ` пряк път, който показва всяка формула във вашия работен лист. Можете да намерите клавиша за ударение (`) в горния ляв ъгъл на клавиатурата на реда с цифровите клавиши (под ESC и преди клавиша номер 1).
Изберете всички клетки с формулите, които искате да копирате, и натиснете Ctrl + C да ги копирате, или Ctrl + X да ги отрежа. След това отворете Notepad и натиснете Ctrl + V за да поставите формулите в бележника.
След това изберете формулата и копирайте (Ctrl + C) го от бележника и го поставете (Ctrl + V) в клетката, където искате да копирате точната формула. Можете да ги копирате и поставите един по един или всички наведнъж.
След като поставите формулите, изключете режима за преглед на формули, като натиснете Ctrl + ` или отново отидете на „Формули“ –> „Показване на формули“.
Копирайте точните формули, като използвате Find and Replace в Excel
Ако искате да копирате набор от точни формули, можете също да използвате инструмента за намиране и замяна на Excel, за да го направите.
Изберете всички клетки, които имат формулите, които искате да копирате. След това отидете на раздела „Начало“, щракнете върху „Намиране и избор“ в групата Редактиране и изберете опцията „Замяна“ или просто натиснете Ctrl + H за да отворите диалоговия прозорец Find & Replace.
В диалоговия прозорец Намиране и замяна въведете знака за равенство (=) в полето „Намерете какво“. В полето „Замяна с“ въведете символ или знак, който все още не е част от вашите формули, като # или , и т.н. След това щракнете върху бутона „Замяна на всички“.
Ще получите поле за бързо съобщение, което казва „Направихме 6 замествания“ (защото избрахме 6 клетки с формули). След това щракнете върху „OK“ и „Close“, за да затворите двата диалога. Правейки това заменя всички знаци за равенство (=) със знаци хеш (#) и превръща формулите в текстови низове. Сега препратките към клетките на формулите няма да се променят при копиране.
Сега можете да изберете тези клетки, натиснете Ctrl + C за да ги копирате и ги поставете в целеви клетки с Ctrl + V.
И накрая, трябва да промените знаците (#) обратно на знаците (=). За да направите това, изберете двата диапазона (оригинален и копиран диапазон) и натиснете Ctrl + H за да отворите диалоговия прозорец Find & Replace. Този път въведете знака хеш (#) в полето „Намерете какво“ и знака равен на (=) в полето „Замяна с“ и щракнете върху бутона „Замяна на всички“. Щракнете върху „Затвори“, за да затворите диалоговия прозорец.
Сега текстовите низове се преобразуват обратно във формулите и ще получите този резултат:
Свършен!