Kako kopirati formule s automatskim popunjavanjem u Excelu 2016

Ako samo trebate kopirati jednu formulu u programu Excel 2016, upotrijebite značajku automatskog popunjavanja ili naredbe Kopiraj i zalijepi. Ova vrsta kopiranja formule, iako je prilično uobičajena, ne može se napraviti povlačenjem i ispuštanjem.

Ne zaboravite opciju Totals na alatu za brzu analizu. Možete ga koristiti za stvaranje retka ili stupca ukupnih vrijednosti na donjem ili desnom rubu podatkovne tablice u trenu. Jednostavno odaberite tablicu kao raspon ćelija i kliknite gumb Brza analiza nakon čega na paleti Ukupne vrijednosti. Zatim kliknite gumb Zbroj na početku palete da biste stvorili formule koje zbrajaju stupce u novom retku na dnu tablice i/ili gumb Zbroj na kraju palete da biste stvorili formule koje zbrajaju retke u novom stupac na desnom kraju.

Evo kako možete koristiti automatsko popunjavanje za kopiranje jedne formule u niz ćelija. Na ovoj slici možete vidjeti radni list Mother Goose Enterprises – 2016 Sales sa svim tvrtkama, ali ovaj put sa samo jednim mjesečnim ukupnim brojem u retku 12, koji je u procesu kopiranja kroz ćeliju E12.

Kopiranje formule u raspon ćelija pomoću automatskog popunjavanja.

Sljedeća slika prikazuje radni list nakon povlačenja ručke za popunjavanje u ćeliji B12 za odabir raspona ćelija C12:E12 (gdje treba kopirati ovu formulu).

Radni list nakon kopiranja formule koja daje ukupnu mjesečnu (i tromjesečnu) prodaju.

Relativno govoreći

Slika prikazuje radni list nakon što je formula u ćeliji kopirana u raspon ćelija C12:E12 i ćelija B12 je aktivna. Primijetite kako Excel obrađuje kopiranje formula. Izvorna formula u ćeliji B12 je sljedeća:

=SUM(B3:B11)

Kada se izvorna formula kopira u ćeliju C12, Excel malo mijenja formulu tako da izgleda ovako:

=SUM(C3:C11)

Excel prilagođava referencu stupca, mijenjajući je iz B u C, jer ste kopirali s lijeva na desno preko redaka.

Kada kopirate formulu u raspon ćelija koji se proteže niz retke, Excel prilagođava brojeve redaka u kopiranim formulama umjesto slova stupaca kako bi odgovarali položaju svake kopije. Na primjer, ćelija E3 u radnom listu Mother Goose Enterprises – 2016 Sales sadrži sljedeću formulu:

=SUM(B3:D3)

Kada kopirate ovu formulu u ćeliju E4, Excel mijenja kopiju formule u sljedeće:

=SUM(B4:D4)

Excel prilagođava referencu retka kako bi bila aktualna s novom pozicijom retka 4. Budući da Excel prilagođava reference ćelija u kopijama formule u odnosu na smjer kopiranja, reference ćelija poznate su kao relativne reference ćelija .

Neke stvari su apsolutne!

Sve nove formule koje izradite prirodno sadrže relativne reference ćelija osim ako ne kažete drugačije. Budući da većina kopija formula koje napravite zahtijevaju prilagodbe njihovih referenci ćelija, rijetko morate razmišljati o ovom rasporedu. Zatim, s vremena na vrijeme, naiđete na iznimku koja zahtijeva ograničenje kada i kako se reference ćelija prilagođavaju u kopijama.

Jedna od najčešćih iznimaka je kada želite usporediti raspon različitih vrijednosti s jednom vrijednošću. To se najčešće događa kada želite izračunati koliki je postotak svaki dio u ukupnom iznosu. Na primjer, u radnom listu Mother Goose Enterprises – 2016 Sales, nailazite na ovu situaciju u stvaranju i kopiranju formule koja izračunava koliki je postotak svaki mjesečni zbroj (u rasponu ćelija B14:D14) od tromjesečnog ukupnog iznosa u ćeliji E12.

Pretpostavimo da želite unijeti ove formule u redak 14 radnog lista Mother Goose Enterprises – 2016 Sales, počevši od ćelije B14. Formula u ćeliji B14 za izračun postotka siječanj-prodaje-ukupnog iznosa prvog tromjesečja vrlo je jednostavna:

=B12/E12

Ova formula dijeli ukupnu prodaju u siječnju u ćeliji B12 s ukupnim tromjesečnim brojem u E12 (što bi moglo biti lakše?). Pogledajte, međutim, što bi se dogodilo da povučete ručicu za punjenje jednu ćeliju udesno da kopirate ovu formulu u ćeliju C14:

=C12/F12

Prilagodba prve reference stanice s B12 na C12 je upravo ono što je liječnik naredio. Međutim, prilagodba druge reference ćelije s E12 na F12 je katastrofa. Ne samo da ne izračunate koliki je postotak prodaje u ćeliji C12 u veljači u odnosu na prodaju u prvom tromjesečju u E12, već ćete također završiti s jednim od onih užasnih #DIV/0! greške u ćeliji C14.

Da biste spriječili Excel da prilagođava referencu ćelije u formuli u bilo kojoj kopiji koju napravite, pretvorite referencu ćelije iz relativne u apsolutnu. To činite pritiskom na funkcijsku tipku F4, nakon što Excel stavite u način za uređivanje (F2). Excel označava da referencu ćelije činite apsolutnom postavljanjem znakova dolara ispred slova stupca i broja retka. Na primjer, na ovoj slici ćelija B14 sadrži ispravnu formulu za kopiranje u raspon ćelija C14:D14:

Kopiranje formule za izračunavanje omjera mjesečne i tromjesečne prodaje s apsolutnom referencom ćelije.

=B12/$E$12

Pogledajte radni list nakon što se ova formula kopira u raspon C14:D14 s ručkom za popunjavanje i odabranom ćelijom C14 (pogledajte sljedeću sliku). Primijetite da traka formule pokazuje da ova ćelija sadrži sljedeću formulu:

Radni list nakon kopiranja formule s apsolutnom referencom ćelije.

=C12/$E$12

Budući da je E12 promijenjen u $E$12 u izvornoj formuli, sve kopije imaju istu apsolutnu (nepromjenjivu) referencu.

Ako zabrljate i kopirate formulu u kojoj je jedna ili više referenci ćelije trebale biti apsolutne, ali ste ih sve ostavili relativne, uredite izvornu formulu na sljedeći način:

Dvaput kliknite ćeliju s formulom ili pritisnite F2 da biste je uredili.

Postavite točku umetanja negdje na referencu koju želite pretvoriti u apsolutnu.

Pritisnite F4.

Kada završite s uređivanjem, kliknite gumb Enter na traci formule, a zatim kopirajte formulu u zbrkani raspon ćelija pomoću ručke za popunjavanje.

Obavezno pritisnite F4 samo jednom da promijenite referencu ćelije u potpuno apsolutnu, kao što sam ranije opisao. Ako drugi put pritisnete funkcijsku tipku F4, završit ćete s takozvanom miješanom referencom, gdje je samo dio retka apsolutan, a dio stupca relativan (kao u E$12). Ako zatim ponovno pritisnete F4, Excel dolazi s drugom vrstom mješovite reference, gdje je dio stupca apsolutan, a dio retka relativan (kao u $E12). Ako nastavite i još jednom pritisnete F4, Excel će vratiti referencu ćelije u potpuno relativnu (kao u E12).

Nakon što se vratite tamo gdje ste započeli, možete nastaviti koristiti tipku F4 za ponovno kretanje kroz isti skup promjena referenci ćelije.

Ako koristite Excel 2016 na uređaju bez pristupa fizičkoj tipkovnici s funkcijskim tipkama (kao što je tablet s ekranom osjetljivim na dodir), jedini način da pretvorite adrese ćelija u formulama iz relativne u apsolutnu ili neki oblik mješovite adrese je otvaranje Dodirnite tipkovnicu i upotrijebite je, dodajte znakove dolara ispred slova stupca i/ili broja retka u odgovarajuću adresu ćelije na traci formule.