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.

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

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

  3. Valige Andmed>Konsolideerimine (jaotises Andmeriistad).

    Exceli lindi pilt

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

    Dialoogiboks Andmete konsolideerimine

  5. Valige oma andmed.

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

    Andmete konsolideerimise Ahenda dialoogiboks

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

    Kui andmeid sisaldav tööleht, mida soovite konsolideerida, asub teises töövihikus, klõpsake töövihiku otsimiseks 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.

    Dialoogiboks Andmete konsolideerimine

  6. Klõpsake hüpikaknas Konsolideerimine nuppu Lisa. Kõigi konsolideeritavate vahemike lisamiseks korrake seda.

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

  8. 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 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 saaksime järgmist Exceli versiooni paremaks muuta? Kui on, tutvuge nende teemadega Exceli User Voice’i veebilehel.

Vt ka

Täiendage Office'i kasutamise 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.

×