17. peatükk · 17.19
Mitme töölehe andmed ühte loendisse
Üks tööleht poe, kuu või töötaja kohta: täitmisel ülevaatlik, aga tülikas, kui soovid ühte ülevaadet või ühte liigendtabelit kõigest. Mitme töölehe koondülevaade: kulud, tulud ja saldo liidab töölehtede kogusummad kokku. Siin läheme sammu edasi: kõik read mitmelt töölehelt ühes loendis, mis uueneb ise.
Excelis teed seda Power Query või andmemudeliga. Neid Euro-Office'is pole (vt Power Query või Power Pivotiga Exceli failid: mis Euro-Office'is töötab ja mis mitte) ja puudub ka funktsioon Consolidate (Koonda). Funktsiooniga VSTACK saad sellega siiski hakkama, ühe valemiga.
Näiteks võtame pagariäri Bakkerij De Korenaar, millel on kaks poodi, kumbki oma töölehel: Winkel Centrum ja Winkel Noord. Mõlemal on samad veerud: Datum, Product, Bedrag ja Winkel.
1. samm: hoolitse, et töölehed oleksid ühesugused
- Kõigil töölehtedel on samad veerud samas järjekorras.
- Lisa igale töölehele veerg poe (või kuu või töötaja) nimega. Nii tead ka koondloendis, kust iga rida pärit on.
2. samm: virnasta
Loo uus tööleht Kõik, mille 1. real on sama päiserida. Sisesta lahtrisse A2:
=VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100)
VSTACK paneb vahemikud üksteise alla. Tühikuga töölehe nimi on ülakomade vahel. Võta vahemikud piisavalt suured (kuni 100. reani või kaugemale), et uued read tuleksid ise kaasa.
3. samm: jäta tühjad read välja
Suurte vahemike tõttu võtab VSTACK kaasa ka tühjad read; need ilmuvad nullidena. Jäta need FILTER abil välja tingimusel, et esimene veerg pole tühi:
=FILTER(VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100);CHOOSECOLS(VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100);1)<>"")
CHOOSECOLS(...;1) võtab virnastatud loendi esimese veeru (kuupäevad) ja FILTER jätab alles ainult need read, kus see on täidetud.

Määra töölehe Kõik veergudele ise vorming: esimesele veerule kuupäevavorming ja summadele Currency. Lähtetöölehtede vorming ei tule alati kaasa; ilma vorminguta näed näiteks 46 328 asemel 02-11-2026 ja 412,5 asemel 412,50 €, nagu pildil paremal.
4. samm: arvuta või loo liigendtabel
- Valemid nagu SUMIFS töötavad otse loendiga, näiteks mõlema poe leivamüük: =SUMIFS(C2:C200;B2:B200;"Brood").
- Liigendtabeli lood töölehest Kõik nagu mis tahes muust loendist (vt PivotTable-tabelid). Liigendtabel ei värskendu ise: pärast uute andmete lisamist paremklõpsa liigendtabelis ja vali Refresh (Värskenda).
Parem ülesehitus edaspidiseks
Kui alustad otsast peale, pane kõik ühte loendisse veeruga Winkel. Kui tahad siiski vaadata poe kaupa, filtreeri selle veeru järgi või kasuta oma lehevaadet (vt Filtreeri nii, et kolleegidele see tüli ei tee). Ühte loendit on lihtsam hallata ning liigendtabelid ja diagrammid töötavad sellega otse.
Lõksud
- Veerud teises järjekorras ühel töölehel: VSTACK paneb siis summad kuupäevade alla. Kontrolli päiseridu.
- Tööleht ümber nimetatud: kontrolli seejärel valemit. Tavaliselt muutub töölehe nimi valemis ise kaasa, aga tööleht, mille lood uuesti vana nimega, ei kuulu enam valemi juurde.
- Valem jääb tühjaks: valemi all on midagi, nii et loend ei saa laieneda. Hoia tööleht Kõik muus osas tühi (vt Iseennast uuendavad loendid funktsioonidega FILTER, UNIQUE ja SORT).
- Kolleeg, kellel on Excel 2019 või vanem, ei tunne funktsiooni VSTACK ja näeb #NAME?.
