Mitme töölehe andmete konsolideerimine

Erinevatel töölehtedel asuvate tulemite kokkuvõtete ja aruannete koostamiseks saate konsolideerida iga lehe andmed põhitöölehele. Lehed võivad olla põhitöölehega samas töövihikus või mõnes muus töövihikus. Andmete konsolideerimisel koondate andmed nii, et saate neid senisest hõlpsamalt värskendada ja koondada vastavalt vajadusele.

Kui teil on näiteks iga piirkondliku kontori kulude kohta tööleht, võiksite konsolideerimise teel need arvud koondada ettevõtte kulude põhitöölehele. Põhitöölehel võivad olla ka müügisummad ja keskmised väärtused, lao hetkeseis ja kogu ettevõtte enimmüüdavad tooted.

Näpunäide.: Kui konsolideerite andmeid sageli, võib olla mõistlik luua uus tööleht, võttes aluseks töölehemall, mis kasutab ühtset paigutust. Lisateavet mallide kohta leiate teemast Malli loomine. See on ka suurepärane hetk malli häälestamiseks Exceli tabelitega.

Andmete konsolideerimiseks on kaks võimalust: koha või kategooria järgi.

Konsolideerimine koha järgi – lähteala andmed on korraldatud samas järjestuses ja samade siltidega. Selle meetodi abil saate konsolideerida andmed töölehtede sarjas (nt osakonna eelarve töölehtedel), mis on loodud samast mallist.

Konsolideerimine kategooria järgi – kui lähtealades ei ole andmed korraldatud samas järjestuses, kuid sildid on samad. Selle meetodi abil saate konsolideerida andmed töölehtede sarjas, mille paigutus on erinev, kuid andmesildid samad.

Kategooria järgi andmete konsolideerimine sarnaneb PivotTable-liigendtabeli loomisega. PivotTable-liigendtabeli abil saate aga kategooriaid kergesti ümber korraldada. Kui soovite kategooria järgi konsolideerimist paindlikumaks muuta, kaaluge hoopis PivotTable-liigendtabeli loomist.

Märkus.: Selle artikli näited on loodud kasutades rakendust Excel 2016. Kui kasutate Exceli muud versiooni, võib vaade küll erineda, kuid juhised on samad.

Mitme töölehe konsolideerimiseks põhitöölehele järgige neid juhiseid.

Kui te pole seda veel teinud, siis häälestage iga sisalduva lehe andmed järgmiselt.
- Veenduge, et kõik andmevahemikud on loendivormingus. Igal veerul peab olema esimeses reas silt (päis) ja sisaldama sarnaseid andmeid. Loendis ei tohi olla tühje ridu või veerge.
- Sisestage iga vahemik eraldi töölehele, aga ärge sisestage midagi põhitöölehele, kuhu kavatsete andmed konsolideerida. Excel teeb seda teie eest.
- Veenduge, et kõigil vahemikel on sama paigutus.
Klõpsake põhitöölehel selle piirkonna ülemist vasakpoolset lahtrit, kus soovite konsolideeritud andmed kuvada.

Märkus.: Põhitöölehe olemasolevate andmete ülekirjutamise vältimiseks veenduge, et konsolideeritavate andmete jaoks jääb sellest lahtrist paremale ja alla piisavalt vabu lahtreid.
Valige Andmed>Konsolideerimine (jaotises Andmeriistad).
Klõpsake väljal Funktsioon seda kokkuvõttefunktsiooni, mida soovite Excelis andmete konsolideerimiseks kasutada. Vaikefunktsioon on SUM.

Siin on näide, kus on valitud kolm töölehe vahemikku.
Valige oma andmed.

Järgmisena klõpsake paani ahendamiseks ja töölehel asuvate andmete valimiseks väljal Viide nuppu Ahenda.

Klõpsake konsolideeritavaid andmeid sisaldavat töölehte, valige andmed ja klõpsake siis dialoogiboksi Konsolideerimine naasmiseks paremal nuppu Laienda dialoogiboks.

Kui konsolideeritavaid andmeid sisaldav tööleht asub mõnes muus töövihikus, klõpsake töövihiku leidmiseks nuppu Sirvi . Pärast otsimist ja nupu OK vajutamist sisestab Excel väljale Viide failitee ja lisab sellele teele hüüumärgi. Seejärel saate jätkata muude andmete valimist.

Siin on näide, kus on valitud kolm töölehe vahemikku.
Klõpsake hüpikaknas Konsolideerimine nuppu Lisa. Kõigi konsolideeritavate vahemike lisamiseks korrake seda.
Automaatne vs käsitsi värskendamine. Kui soovite, et Excel värskendaks konsolideerimistabeli lähteandmete muutudes automaatselt, märkige lihtsalt ruut Loo lingid lähteandmetega. Kui ruut jääb tühjaks, saate konsolideerimist käsitsi värskendada.
Märkused:
- Kui lähte- ja sihtala on samal lehel, siis linke luua ei saa.
- Kui soovite muuta vahemiku ulatust või vahemikku muuta, klõpsake hüpikaknas Konsolideerimine vahemikku ja värskendage seda ülaltoodud juhiste abil. Nii luuakse uus vahemikuviide, nii et enne järgmist konsolideerimist pole eelmist viidet vaja kustutada. Valige lihtsalt vana viide ja vajutage kustutusklahvi (Delete).
Klõpsake nuppu OK ja Excel konsolideerib andmed. Soovi korral saate vormindada. Kui te just ei taaskäivita konsolideerimist, on vormindada vaja ainult üks kord.
- Kõik sildid, mis ei kattu ülejäänud lähtealade siltidega, kuvatakse konsolideerimises eraldi ridade või veergudena.
- Veenduge, et kategooriad, mida te ei soovi konsolideerida, kannavad ainult ühes lähtevahemikus esinevaid üheseid silte.

Kui konsolideeritavad andmed paiknevad erinevate töölehtede erinevates lahtrites

Sisestage valem, mis sisaldab viiteid teistele töölehtedele (ühte viidet iga töölehe kohta). Näiteks andmete konsolideerimisel töölehtedelt Müük (lahtris B4), Personal (lahtris F5) ja Turundus (lahtris B9) põhitöölehe lahtrisse A2 tuleks sisestada järgmised andmed.

Exceli mitmeleheline valemiviide

Näpunäide.: Lahtriviite (nt Müük!B4) sisestamiseks valemisse ilma seda tippimata tippige valem selle kohani, kus peate viite sisestama, seejärel klõpsake töölehe sakki ning seejärel soovitud lahtrit. Excel täidab töölehe nime ja lahtri aadressi. PANGE TÄHELE: sellistel juhtudel võivad valemid olla veaohtlikud, kuna kogemata valet lahtrit valida on väga lihtne. Pärast keeruka valemi sisestamist võib ka vea leidmine olla raske.

Kui konsolideeritavad andmed paiknevad erinevate töölehtede samades lahtrites

Sisestage ruumilise viitega valem, milles kasutatakse viidet töölehenimede vahemikule. Näiteks töölehtede lahtrite A2 konsolideerimiseks alates töölehelt Müük kuni tööleheni Turundus (kaasa arvatud) peate sisestama põhitöölehe lahtrisse E5 järgmist.

Exceli 3D lehe viidetega valem

Kas vajate rohkem abi?

Kui teil on küsimusi, saate need esitada Exceli tehnikakogukonnafoorumis, kus teile vastavad asjatundjad, või teistele kasutajatele kogukonnafoorumis.