Mitme töölehe andmete konsolideerimine

Erinevatel töölehtedel asuvate tulemite kokkuvõtete ja aruannete koostamiseks saate konsolideerida iga lehe andmed põhitöölehele. Töö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 liita.

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, on mõistlik kasutada töölehtede alusena alati sama paigutusega töölehemalli. Lisateavet mallide kohta leiate teemast Malli loomine. See on ka suurepärane hetk malli häälestamiseks Exceli tabelitega.

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

Konsolideerimine koha järgi – kui lähtealades on andmed 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, et kategooria järgi konsolideerimist paindlikumaks muuta, kaaluge hoopis PivotTable-​​liigendtabeli loomist.

Märkus. : Selle artikli näited loodi rakenduses Excel 2016, nii et olenevalt versioonist võivad kuvad erineda. Juhised on siiski täpselt samad.

Konsolideerimise juhised

  1. Kui te pole seda veel teinud, valmistage andmed igal konsolideeritavaid andmeid sisaldaval töölehel ette, toimides järgmiselt.

    • Veenduge, et kõik andmevahemikud on loendivormingus, nii et iga veeru silt kuvatakse esimeses reas, iga veerg sisaldab sarnaseid andmeid ning loendis pole tühje ridu ega veerge.

    • Sisestage iga vahemik eraldi töölehele, aga ärge sisestage midagi põhitöölehele, kuhu kavatsete andmed konsolideerida: Excel teeb seda ise.

    • Veenduge, et kõigil vahemikel on sama paigutus.

  2. Klõpsake põhitöölehel selle piirkonna ülemist vasakpoolset lahtrit, kus soovite konsolideeritud andmed kuvada.

    Märkus. : Kui te ei soovi sihttöölehel leiduvaid andmeid konsolideeritavate andmetega üle kirjutada, siis veenduge, et konsolideeritavate andmete jaoks jääb sellest lahtrist paremale ja alla piisavalt vabu lahtreid.

  3. Klõpsake menüü Andmed jaotises Andmeriistad nuppu Konsolideeri.

    Exceli lindi pilt

  4. Klõpsake väljal Funktsioon seda kokkuvõttefunktsiooni, mida soovite Excelis andmete konsolideerimiseks kasutada. Vaikefunktsioon on SUM.

    Järgmises näites on valitud kolm töölehevahemikku.

    Dialoogiboks Andmete konsolideerimine

  5. Andmete valimine

    • Kui konsolideeritavaid andmeid sisaldav tööleht asub mõnes muus töövihikus, klõpsake kõigepealt selle töövihiku otsimiseks nuppu Sirvi ja seejärel dialoogiboksi Sirvimine sulgemiseks nuppu OK. Excel sisestab väljale Viide faili tee ja seejärel hüüumärgi ning saate andmete valimisega jätkata.

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

    Andmete konsolideerimise Ahenda dialoogiboks

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

  6. Klõpsake dialoogiboksis Konsolideerimine nuppu Lisa ning korrake siis toiminguid kõigi soovitud vahemike lisamiseks.

  7. Automaatne vs käsitsi värskendamine. Kui soovite, et Excel värskendaks konsolideerimistabeli lähteandmete muutudes automaatselt, märkige ruut Loo lingid lähteandmetega. Kui ruut on tühi, saate konsolideerimise käsitsi värskendada.

    Märkmed : 

    • Kui lähte- ja sihtala on samal lehel, siis linke luua ei saa.

    • Kui pärast vahemike lisamist on vaja neid muuta, saate neist igaühte dialoogiboksis Konsolideerimine klõpsata ja kui need kuvatakse väljal Viide, neid värskendada, ja seejärel klõpsata käsku Lisa. Nii luuakse uus vahemikuviide, nii et enne järgmist konsolideerimist pole eelmist viidet vaja kustutada. Valige lihtsalt vana viide ja vajutage kustutusklahvi (Delete).

  8. Klõpsake nuppu OK ja Excel konsolideerib andmed. Tulemus on vormindamata, nii et teil tuleb seda ise teha, aga ainult ühe korra, kuni uuesti andmeid konsolideerite.

    • 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.

Valemi kasutamine andmete konsolideerimiseks

  • 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, klõpsake töölehe sakki ning seejärel soovitud lahtrit. Excel sisestab töölehe nime ja lahtri aadressi. Pange tähele, et sellised valemid on veaohtlikud, kuna kogemata valet lahtrit valida on väga lihtne. Kui valem on juba sisestatud, võib ka vea leidmine olla keeruline.

  • 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 E5of järgmist.

    Exceli 3D lehe viidetega valem

Kas teadsite?

Kui teil pole Office 365 tellimust ega Office‘i uusimat versiooni, saate seda kohe proovida:

Proovige teenusekomplekti Office 365 või Exceli uusimat versiooni

Kas teil on küsimus mõne kindla funktsiooni kohta?

Postitage oma küsimus Exceli kogukonnafoorumisse

Aidake meil Excelit täiustada

Kas teil on soovitusi selle kohta, kuidas saakime täiustada järgmist Exceli versiooni? Sellisel juhul tutvuge nende teemadega Exceli User Voice’i veebilehel.

Vt ka

Exceli valemite ülevaade

Katkiste valemite vältimine

Valemites vigade leidmine ja parandamine

Exceli kiirklahvid ja funktsiooniklahvid

Exceli funktsioonid (tähestikuliselt)

Exceli funktsioonid (kategooriate kaupa)

Täiendage oma oskusi
Tutvuge koolitusmaterjalidega
Kasutage uusi funktsioone enne teisi
Liituge Office Insideri programmiga

Kas sellest teabest oli abi?

Täname tagasiside eest!

Täname tagasiside eest! Tundub, et võiksime teid kokku viia ühega meie Office'i tugiagentidest, kes aitab teil probleemi lahendada.

×