17 skyrius · 17.19
Kelių darbalapių duomenų sujungimas į vieną sąrašą
Atskiras darbalapis kiekvienai parduotuvei, mėnesiui ar darbuotojui: pildant patogu, bet sunku, kai norite vienos suvestinės ar vienos suvestinės lentelės (pivot table) apie viską. Kelių darbalapių bendra suvestinė: išlaidos, pajamos ir likutis sudeda darbalapių sumas. Čia žengiame žingsnį toliau: visos kelių darbalapių eilutės sujungiamos į vieną sąrašą, kuris atsinaujina automatiškai.
Excel programoje tai daroma su Power Query arba duomenų modeliu. Euro-Office jų neturi (žr. Excel-bestanden met Power Query of Power Pivot: wat werkt in Euro-Office en wat niet), taip pat nėra funkcijos Consolidate. Tačiau su funkcija VSTACK tai pavyksta viena formule.
Pavyzdžiui, imkime kepyklą Bakkerij De Korenaar su dviem parduotuvėmis, kurių kiekviena turi savo darbalapį: Winkel Centrum ir Winkel Noord. Abiejuose yra tie patys stulpeliai: Datum, Product, Bedrag ir Winkel.
1 žingsnis: suvienodinkite darbalapius
- Visi darbalapiai turi tuos pačius stulpelius ta pačia tvarka.
- Kiekviename darbalapyje įterpkite stulpelį su parduotuvės (arba mėnesio, arba darbuotojo) pavadinimu. Tada sujungtame sąraše vis dar matysite, iš kur kilusi kiekviena eilutė.
2 žingsnis: sudėkite vieną po kito
Sukurkite naują darbalapį Visi su ta pačia antraščių eilute 1 eilutėje. Langelyje A2 įveskite:
=VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100)
VSTACK sudeda diapazonus vieną po kito. Darbalapio pavadinimas su tarpu rašomas tarp viengubų kabučių. Diapazonus imkite su atsarga (iki 100 eilutės ar toliau), kad naujos eilutės būtų įtrauktos automatiškai.
3 žingsnis: praleiskite tuščias eilutes
Dėl plačių diapazonų VSTACK įtraukia ir tuščias eilutes; jos rodomos kaip nuliai. Pašalinkite jas su FILTER, nurodydami sąlygą, kad pirmasis stulpelis nėra tuščias:
=FILTER(VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100);CHOOSECOLS(VSTACK('Winkel Centrum'!A2:D100;'Winkel Noord'!A2:D100);1)<>"")
CHOOSECOLS(...;1) paima sujungto sąrašo pirmąjį stulpelį (datas), o FILTER palieka tik tas eilutes, kuriose jis užpildytas.

Darbalapio Visi stulpeliams formatą nustatykite patys: pirmajam stulpeliui datos formatą, o sumoms Currency. Šaltinio darbalapių formatavimas perkeliamas ne visada; be formato matysite, pavyzdžiui, 46328 vietoj 2026-11-02 ir 412,5 vietoj 412,50 €, kaip paveikslėlio dešinėje.
4 žingsnis: skaičiuokite arba sukurkite suvestinę lentelę
- Formulės, tokios kaip SUMIFS, veikia tiesiogiai su sąrašu, pavyzdžiui, abiejų parduotuvių duonos apyvarta: =SUMIFS(C2:C200;B2:B200;"Brood").
- Suvestinę lentelę iš darbalapio Visi sukuriate taip pat, kaip iš bet kurio kito sąrašo (žr. PivotTable lentelės). Suvestinė lentelė neatsinaujina automatiškai: įvedę naujų duomenų, spustelėkite suvestinėje lentelėje dešiniuoju pelės mygtuku ir pasirinkite Refresh.
Geresnė struktūra ateičiai
Jei pradedate iš naujo, viską surašykite į vieną sąrašą su stulpeliu Winkel. Jei vis dėlto norite peržiūrėti duomenis pagal parduotuvę, filtruokite pagal tą stulpelį arba naudokite atskirą lapo rodinį (žr. Filtravimas netrukdant kolegoms). Vieną sąrašą lengviau prižiūrėti, o suvestinės lentelės ir diagramos su juo veikia tiesiogiai.
Dažniausios klaidos
- Kitokia stulpelių tvarka viename darbalapyje: tada VSTACK sumas sudės po datomis. Patikrinkite antraščių eilutes.
- Pervadintas darbalapis: po to patikrinkite formulę. Paprastai darbalapio pavadinimas formulėje pasikeičia automatiškai, tačiau iš naujo sukurtas darbalapis senu pavadinimu nebėra įtrauktas.
- Formulė lieka tuščia: po formule kažkas yra, todėl sąrašas negali išsiplėsti. Kitaip darbalapį Visi palikite tuščią (žr. Sąrašai, kurie atsinaujina patys, su FILTER, UNIQUE ir SORT).
- Kolega, naudojantis Excel 2019 ar senesnę versiją, nepažįsta VSTACK ir mato #NAME?.
