Excel: Stvorite referencu na drugu proračunsku tablicu

Korisnici Microsoft Excela cijene mogućnost stvaranja referenci na druge proračunske tablice u aktivnoj mapi. Osobito kod velikih proračunskih tablica s mnogo formula, podataka i funkcija, korisno je stvoriti reference kako biste mogli učinkovito raditi sa svim podacima. Ovo štedi dosadan ručni dvostruki unos ili kopiranje i lijepljenje, što je dugotrajno i podložno pogreškama.

U ovom ćete članku naučiti kako možete povezati proračunske tablice programa Excel sa samo nekoliko klikova i tako izvršiti izračune u više proračunskih tablica.

Kako stvoriti referencu na drugu proračunsku tablicu?

Stvarate vezu između dvije Excel proračunske tablice pomoću formule i vanjske reference u ciljnoj proračunskoj tablici.

U našem primjeru, tablica 1 pokazuje koliko zaposlenika tvrtka ima na različitim lokacijama u Njemačkoj i drugim europskim zemljama. U drugoj tablici treba ispisati samo broj zaposlenika podružnice u Passauu. Veza između dvije tablice osigurava da se podaci u tablici 2 automatski ažuriraju ako se podaci u tablici 1 promijene.

Da biste stvorili referencu na drugu proračunsku tablicu, prvo otvorite obje proračunske tablice u programu Microsoft Excel. Zatim u ciljnoj tablici kliknite ciljnu ćeliju u kojoj želite prikazati podatke iz izvorne datoteke. U našem praktičnom primjeru, ovo je ciljna stanica B1.Formula za vezu uvijek počinje znakom jednakosti. Upišite "="u prethodno odabranu ćeliju.

U sljedećem koraku prijeđite na tablicu s podacima o zaposlenicima i označite broj 55 u ćeliji C9. To je broj zaposlenih u podružnici u Passauu.

Pritisnite Enter za prekid prečaca. Broj od 55 zaposlenika sada se pojavljuje u tablici 2, jer ste efektivno međusobno povezali ćelije oba radna lista.

Ako želite kreirati referencu ručno pomoću tipkovnice, koristite sljedeću sintaksu:

=naziv lista!ćelija

Za naziv lista unesite naziv lista na čiju se ćeliju želite referirati. U našem primjeru, naziv lista je "Sheet1." Oznaka ćelije navodi referencu na ćeliju(e) u Sheet1 na koju se želite referirati.

Kako napraviti izračune u više Excel proračunskih tablica?

Često se događa da se izračuni moraju provesti u nekoliko proračunskih tablica. U našem uzorku radne knjige postoji nekoliko proračunskih tablica koje imaju identičnu strukturu. Tablica opisuje troškove električne energije za različite pogone poduzeća u jednoj godini, raščlanjene po mjesecima. Radna knjižica sadrži podatke za godine od 2016. do 2021. Ukupne i maksimalne vrijednosti treba izračunati u sažetku.

Korisnici koji sporadično rade s Excelom znaju kako izvesti izračune na više radnih listova u radnoj knjizi. Malo ljudi zna da se pri računanju preko više proračunskih tablica mogu prenijeti područja koja znatno skraćuju formule i čine ih jasnijima.

Važno: Ključno je da sve proračunske tablice koje uključite u izračun budu strukturirane identično prema istom obrascu. Samo u ovom slučaju formule će dati točne rezultate. Osobito kod opsežnih proračunskih tablica s puno podataka, ne mora nužno biti vidljivo u rezultatu ako se područje podataka u jednoj od proračunskih tablica pomakne za redak ili stupac. Istovremeno, rezultat je pogrešan.

U sljedećem primjeru treba zbrojiti troškove električne energije u posljednjih nekoliko godina. Ilustracija prikazuje strukturu pojedinačnih listova:

Uz ujednačenu strukturu tablica važan je raspored listova u radnoj bilježnici. Provjerite jesu li radni listovi koji su vam potrebni u radnoj bilježnici jedan pored drugog.

Sljedeća slika prikazuje raspored radnih listova u primjeru radne bilježnice:

Kada su pripreme gotove, možete početi upisivati formule.

Kako izračunati zbroj više radnih listova u Excelu?

U našoj tablici primjera, ukupni rezultat svih godišnjih ukupnih iznosa treba odrediti u sažetku. Zbrojevi pojedinačnih godina nalaze se u odgovarajućim tablicama u ćeliji H16.

Formula potrebna za zbrajanje ima sljedeću strukturu:

=SUM('Prvi list:Zadnji list'!Raspon)

" S FirstSheet prosljeđujete naziv prve stranice koja će biti uključena u izračun. Argument lastsheet definira naziv zadnjeg lista za izračun. Svi listovi koji leže između ova dva lista također se uzimaju u obzir.Površina definira površinu koju želite zbrojiti."

Upotrijebite sljedeću formulu za izračun zbroja vrijednosti u ćelijama H16 radnih listova 2016 do 2021:

=SUM('2016:2021'!H16)

Sljedeća slika prikazuje upotrebu ove formule u oglednoj tablici:

Prilagođavanjem ove jednostavne formule, moguće je postavljati upite i drugim ćelijama. Na primjer, ako želite znati koliko je električne energije potrošeno u tvornici u Frankfurtu u travnju od 2016. do 2021., zamijenite referencu na ćeliju H16 ćelijom F7 u formuli.

Kada računate u više proračunskih tablica, niste ograničeni na pojedinačne ćelije u proračunskim tablicama. Cijeli raspon možete proslijediti funkciji u bilo kojem trenutku.

Kako izračunati maksimalnu vrijednost više radnih listova u Excelu?

U našoj tablici primjera, u kojoj se uspoređuju troškovi električne energije različitih postrojenja u godinama 2016. do 2021., treba odrediti apsolutnu maksimalnu vrijednost svih pojedinačnih vrijednosti svih godina. Pojedinačne vrijednosti nalaze se u tablicama 2016 do 2021 u ćelijama B4:G15.

Upotrijebite sljedeću formulu za određivanje maksimalne vrijednosti svih pojedinačnih vrijednosti u pet proračunskih tablica odgovarajućih godina:

=MAX('2016:2021'!B4:G15)

Sljedeća slika prikazuje upotrebu ove formule u oglednoj tablici:

Kako izračunavate srednju vrijednost za različite proračunske tablice?

Osim maksimalnih vrijednosti, srednja vrijednost u više proračunskih tablica također se može izračunati pomoću jedne formule. Da biste to učinili, koristite sljedeću sintaksu.

=PROSJEK('početni list:završni list'!raspon ćelija)

Formulom prosljeđujete ukupno tri argumenta:

• S početnim listom prosljeđujete naziv prvog lista koji treba biti uključen u izračun.
• Ime završnog lista koristi se za definiranje naziva posljednjeg lista za izračun. Svi listovi koji se nalaze između ova dva lista uključeni su u izračun.
• CellRange se koristi za definiranje raspona ćelija za izračun.

Za našu oglednu tablicu, formula i rezultat izgledaju ovako:

=PROSJEK('2016:2021'!B4:G15)

Kako se mogu povezati vrijednosti iz drugih dokumenata?

Ako često radite s Excelom, ponekad morate povezati reference ćelija iz proračunske tablice koje nisu u istom dokumentu.Ovo funkcionira bez ikakvih problema dodavanjem naziva dokumenta u gore opisanu referencu ćelije. Ovo je označeno uglatim zagradama (" [" ili "]" ).

Primjer:=[Dokument1]Porijeklo!G9

Upotrijebite ovu varijantu ako je otvorena druga mapa s nazivom “Document1”. S druge strane, ako dokument nije otvoren, koristite stazu na kojoj je spremljen.

Primjer:=C:\Files\FolderName\[Document1]Origin!G9

Kako povezati reference ćelija pomoću miša?

Reference ćelija na druge dokumente ne morate nužno unositi ručno, jer ćelije možete jednostavno povezati sa samo nekoliko klikova mišem. (UMETNI SLIKU)

Kako popraviti pogrešku reference ćelije?

Prilikom povezivanja proračunskih tablica i korištenja referenci, Microsoft Excel može prikazati pogrešku u referenci ćelije. Ako je to slučaj, formula sadrži pogrešku ili je došlo do pogreške prilikom odabira područja mišem

Pogreška reference također se javlja ako je referenca ćelije unesena na drugi radni list koji je izbrisan ili preimenovan. Ova se pogreška može usporediti s dostavom pisma na određenu adresu. Čim se poštanski broj promijeni, pismo se više ne može isporučiti jer je adresa pogrešna.

Ukratko, često ima smisla raditi s referencama u Excelu. Glavna prednost rada s vezama je da svoj posao možete obavljati učinkovitije i brže.

Veze vam omogućuju pristup postojećim podacima bez ponovnog upisivanja. To štedi vrijeme i sprječava pogreške u prijenosu. U Excelu se reference također mogu koristiti za izvođenje izračuna. Reference se mogu skovati na druge ćelije unutar radne knjige ili na ćelije u drugoj radnoj knjizi. Korisni su za organiziranje podataka i izvođenje izračuna.Veze se također mogu koristiti za filtriranje i sortiranje informacija. Stoga su reference moćan alat u Excelu koji vam pomaže da svoj posao obavite učinkovitije.