Usklajevanje podatkov na več delovnih listih

Če želite povzeti in ustvariti poročilo o rezultatih iz ločenih delovnih listov, lahko podatke iz vsakega lista uskladite v glavnem delovnem listu. Listi so lahko v istem delovnem zvezku kot glavni delovni list ali v drugih delovnih zvezkih. Ko uskladite podatke, zberete podatke, tako da jih lahko po potrebi lažje posodobite in združite.

Če imate na primer delovni list s stroški za vsakega od svojih regionalnih uradov, lahko te številke uskladite v glavnem delovnem listu s stroški podjetja. Glavni delovni list lahko vsebuje tudi podatke o skupni in povprečni prodaji, trenutni zalogi in najbolje prodajanih izdelkih za celotno podjetje.

Namig: Če pogosto uskladite podatke, vam priporočamo, da ustvarite nove delovne liste iz predloge delovnega lista, ki uporablja dosledno postavitev. Če želite izvedeti več informacij o Predlogah, glejte: Ustvarite predlogo. To je tudi najboljši čas za pripravo predloge z Excelovimi tabelami.

Podatke lahko uskladite na dva načina po položaju ali kategoriji.

Usklajevanje po položaju: podatki v izvornih območjih imajo enak vrstni red in uporabljajo enake oznake. Ta način uporabite za usklajevanje podatkov iz niza delovnih listov, kot so na primer delovni listi z oddelčnimi proračuni, ki so ustvarjeni iz iste predloge.

Usklajevanje po kategoriji: Če podatki v izvornih območjih niso razporejeni v enakem vrstnem redu, toda uporabljajo enake oznake. Ta način uporabite za usklajevanje podatkov iz niza delovnih listov z različnimi postavitvami in enakimi oznakami podatkov.

Usklajevanje podatkov po kategoriji je podobno ustvarjanju vrtilne tabele. Z vrtilno tabelo lahko preprosto znova organizirate kategorije. Če potrebujete bolj prilagodljivo usklajevanje po kategorijah, priporočamo, da ustvarite vrtilno tabelo.

Opomba: Primeri v tem članku so bili ustvarjeni z Excel 2016. Čeprav se vaš pogled lahko razlikuje, če uporabljate drugo različico Excela, so koraki enaki.

Če želite uskladiti več delovnih listov v glavni delovni list, sledite tem korakom:

Če tega še niste storili, nastavite podatke na vsakem ustanovnem listu tako, da naredite to:
- Zagotovite, da je vsak obseg podatkov v obliki seznama. Vsak stolpec mora imeti oznako (glavo) v prvi vrstici in vsebovati podobne podatke. Na seznamu ne sme biti praznih vrstic ali stolpcev.
- Vsak obseg naj bo na ločenem delovnem listu, vendar ničesar ne vnašajte na glavni delovni list, kjer nameravate uskladiti podatke. Excel bo to naredil namesto vas.
- Zagotovite, da ima vsak obseg isto postavitev.
Na glavnem delovnem listu kliknite zgornjo levo celico območja, kjer naj bodo prikazani usklajeni podatki.

Opomba: Če ne želite prepisati obstoječih podatkov na glavnem delovnem listu, pustite dovolj celic desno in pod to celico za usklajene podatke.
Kliknite >usklajevanje (v skupini Podatkovna orodja).
V polju Funkcija kliknite funkcijo funkcija povzemanja, ki naj jo Excel uporabi za usklajevanje podatkov. Privzeta funkcija je SUM.

Tukaj je primer, v katerem so bili izbrani trije obsegi delovnega lista:
Izberite podatke.

Nato v polju Sklic kliknite gumb Strni , da pomanjšate ploščo in izberete podatke na delovnem listu.

Kliknite delovni list s podatki, ki jih želite uskladiti, izberite podatke in nato kliknite gumb Razširi pogovorno okno na desni, da se vrnete v pogovorno okno Usklajevanje.

Če je delovni list s podatki, ki jih želite uskladiti, v drugem delovnem zvezku, kliknite Prebrskaj in poiščite ta delovni zvezek. Ko poiščete in kliknete V redu, Excel vnese pot datoteke v polje Sklic in tej poti doda klicaj. Nato lahko nadaljujete z izbiranjem drugih podatkov.

Tukaj je primer, v katerem so bili izbrani trije obsegi delovnega lista:
V pojavnem oknu Usklajevanje kliknite Dodaj. Ponovite ta korak, če želite dodati vse obsege, ki jih uskladite.
Samodejne posodobitve proti ročnim: Če želite, da Excel samodejno posodobi tabelo za usklajevanje, ko se izvorni podatki spremenijo, preprosto potrdite polje Ustvari povezave z izvornimi podatki. Če to polje ostane počiščeno, lahko usklajevanje posodobite ročno.
Opombe:
- Povezav ne morete ustvariti, ko sta izvorno in ciljno območje na istem listu.
- Če morate spremeniti obseg obsega ali zamenjati obseg, kliknite obseg v pojavnem oknu Usklajevanje in ga posodobite po zgornjih navodilih. Tako boste ustvarili nov sklic obsega, zato boste morali izbrisati prejšnjega, preden jih znova uskladite. Preprosto izberite stari sklic in pritisnite tipko Delete.
Kliknite V redu in Excel bo ustvaril usklajevanje namesto vas. Po želji lahko uporabite oblikovanje. Oblikovanje morate oblikovati le enkrat, razen če znova zaženite usklajevanje.
- Vse oznake, ki ne ustrezajo oznakam v drugih izvornih območjih, se v usklajevanju prikažejo v ločenih vrsticah ali stolpcih.
- Zagotovite, da imajo vse kategorije, ki jih ne želite uskladiti, enolične oznake, ki so prikazane le v enem izvornem obsegu.

Če so podatki za usklajevanje v različnih celicah na različnih delovnih listih:

Vnesite formulo s sklici celice na druge delovne liste, ena za vsak delovni list posebej. Če želite na primer uskladiti podatke iz delovnih listov »Prodaja« (v celici B4), HR (v celici F5) in »Trženje« (v celici B9), v celici A2 glavnega delovnega lista, vnesite to:

Sklic formule na več listih v Excelu

Namig: Če želite vnesti sklic na celico, na primer »Prodaja!«. B4 – v formulo brez vnašanja vnesite formulo do točke, kjer potrebujete sklic, nato pa kliknite zavihek delovnega lista in nato kliknite celico. Excel bo namesto vas dokončal ime lista in naslov celice. OPOMBA: v takih primerih so lahko formule nagnjene k napakam, saj je zelo enostavno pomotoma izbrati napačno celico. Napako je morda težko prepoznati tudi po vnosu zapletene formule.

Če so podatki za usklajevanje v istih celicah na različnih delovnih listih:

Vnesite formulo s 3D-sklicem, ki uporablja sklic na obseg imen delovnega lista. Če želite na primer uskladiti podatke v celicah A2 od »Prodaja« do vključno »Trženje«, v celico E5 glavnega delovnega lista vnesite to:

Formula sklica »3D-list« v Excelu