Kuidas saada mitme Exceli tabeli numbrid ühte vaatesse ilma uue programmita

Saad eri failides olevast müügi-, kulu- või projektiinfost teha ühe ülevaate ning lõpetada igakuise käsitsi kopeerimise. Mitme Exceli tabeli numbrid saab ühte vaatesse tuua Exceli enda tööriistadega. Sobiv lahendus sõltub sellest, kas vajad üksikute summade võrdlust või kõigi andmeridade ühendamist.

Millal Excelist piisab

Exceli enda tööriistadest piisab tavaliselt siis kui lähteandmed on tabelites ning nende ülesehitus ei muutu pidevalt. Näiteks võivad müügiandmed olla eraldi failis iga kuu kohta või projektikulud võivad tulla eri töötajate tabelitest. Lähtefailid ei pea asuma ühes töövihikus. Need võivad jääda eraldi ning koondfail loeb sealt vajalikud andmed.

Esimene valik sõltub sellest, millist tulemust vajad. Power Query sobib mitme faili või tabeli ridade ühendamiseks. SUMIFS ja XLOOKUP aitavad siduda kindlate tingimuste või tunnuste järgi üksikuid väärtusi. Exceli konsolideerimise funktsioon sobib summade koondamiseks juhul kui tabelite paigutus on sarnane. Liigendtabel aitab juba koondatud andmeid rühmitada ja filtreerida.

Excelist võib jääda väheks siis kui andmeid muudab korraga palju inimesi või vaja on kasutajaõigusi ning automaatset ajalugu. Enne uue süsteemi valimist tasub siiski üle vaadata, kas olemasolev töö saab selgemaks ühe korralikult ehitatud koondfailiga. Samal teemal aitab vajadust hinnata lugu numbritest, mida ettevõtte juht võiks ühelt ekraanilt näha.

Valmista lähtefailid ette

Automaatne ühendamine töötab paremini siis kui kõigis lähtefailides kasutatakse samu veerunimesid ja andmetüüpe. Kui ühes failis on veeru nimi „Klient” ning teises „Kliendi nimi”, käsitleb Power Query neid esialgu eri veergudena. Kuupäev peaks igas failis olema kuupäev ning summa peaks olema number. Tekstina salvestatud summa võib arvutustest välja jääda.

• Igal veerul võiks olla üks selge pealkiri.
• Üks rida võiks kirjeldada ühte müüki, kulu või projekti.
• Tühjad read võiks andmetabelist eemaldada.
• Ühendatud lahtritest ning käsitsi lisatud vahekokkuvõtetest tasub loobuda.
• Failide nimed ja asukohad võiks hoida püsivana.

Lähteandmete korrastamine ei tähenda nende kopeerimist uude faili. Eesmärk on anda tabelitele ühesugune kuju, et Excel oskaks ridu õigesse kohta paigutada. Failide ümbernimetamine või teise kausta tõstmine võib olemasoleva ühenduse katkestada. Seetõttu tasub koondamiseks kasutatav kaust enne päringu loomist paika panna.

Kui Exceli tabel täidab juba väikese andmebaasi ülesannet, annab lisamõtteid artikkel laoarvestusest Excelis. Sama põhimõte kehtib müügi-, kulu- ja projektiandmete puhul.

Koonda failid Power Queryga

Power Query võimaldab tuua Excelisse andmeid ühest tabelist, mitmest töövihikust või tervest kaustast. Kui igal kuul tekib sama ülesehitusega uus fail, saab kausta lisatud faili järgmise värskenduse ajal automaatselt koondandmetesse kaasata. Käsitsi kopeerimist ei ole sel juhul vaja.

Ühendamine käib päringuga

Exceli menüüst Andmed saab valida andmete toomise failist või kaustast. Seejärel näitab Excel failide sisu ning võimaldab valida tabeli või lehe, kust andmeid loetakse. Enne tulemuse töölehele toomist saab eemaldada tarbetud veerud, muuta kuupäevad õigesse vormingusse ning ühtlustada veerunimed.

Kui päring on valmis, jäävad algsed failid eraldi alles. Koondvaade loeb nende sisu ega muuda lähteandmeid juhul kui päring on loodud ainult lugemiseks. Pärast lähtefailide parandamist saab koondandmeid värskendada. Automaatne värskendamine ei paranda siiski valesti sisestatud summat või puuduvaid veerge. Päring töötleb andmeid nende reeglite järgi, mis loomisel määrati.

Esimene seadistamine vajab läbimõtlemist, kuid korduva kuuaruande puhul võib see vähendada käsitsi tehtavaid samme ning kopeerimisest tekkivaid vigu.

Kasuta valemeid vajaduse järgi

Power Query ei ole iga ülesande jaoks vajalik. Kui sul on vaja leida kliendi koodi järgi tema müügisumma või ühendada projektinimega vastutaja, võib piisata XLOOKUP-i valemist. SUMIFS sobib summade arvutamiseks mitme tingimuse järgi. Nii saab näiteks näidata ühe osakonna kindla kuu kulusid või ühe teenuse müüki.

Lihtsamad koondamise viisid

Exceli konsolideerimise funktsioon võib sobida siis kui eri lehtedel on samades kohtades samad näitajad. Sellega saab koondada näiteks osakondade kogusummad. Üksikasjalike müügiridade ühendamiseks on Power Query tavaliselt selgem, sest konsolideerimine ei loo alati täielikku ridade loendit.

Liigendtabel ei ühenda iseseisvalt erineva ülesehitusega faile. See hakkab hästi tööle pärast seda kui andmed on Power Query või valemitega ühte andmestikku toodud. Seejärel saab tulemusi filtreerida kliendi, kuu, projekti või teenuse järgi.

Valemite kasutamisel tasub arvestada, et faili nimi ja asukoht võivad olla ühenduse osa. Faili teisaldamisel võib valem vajada parandamist. Kui aruandes kasutatakse maksuandmeid, jääb ametliku info allikaks Maksu- ja Tolliamet.

Loo värskendatav koondvaade

Koondvaate eesmärk on näidata otsustamiseks vajalikke numbreid ilma lähtefailides otsimata. Vaates võivad olla müük kuude kaupa, kulud liikide järgi, projektide seis ning muud näitajad, mida ettevõttes regulaarselt jälgitakse. Kõiki tabeli veerge ei pea aruandesse tooma.

• Ülemisse ossa sobivad peamised kogusummad ja perioodi valik.
• Liigendtabel võimaldab tulemusi kliendi või projekti järgi avada.
• Filtrid aitavad vaadata ainult vajalikku kuud või osakonda.
• Värskendamise kuupäev näitab, millise seisuga andmeid vaade kasutab.

Koondvaadet tasub kontrollida mõne teadaoleva summa abil. Kui lähtefaili kogusumma ja koondvaate tulemus erinevad, võib põhjus olla puuduvas failis, erinevas veerunimes või numbrina tuvastamata väärtuses. Kontroll annab kindluse, et aruandes kuvatav tulemus vastab lähteandmetele.

Kui vajad koondvaate sidumist kodulehe või ettevõtte muu tööprotsessiga, saab võimalikke lahendusi võrrelda meie tehtud tööde ja hindade lehel. Lahenduse ulatus sõltub failide ülesehitusest ning sellest, milliseid numbreid soovid näha.

Kui soovid teada, kas sinu Exceli failid saab üheks värskendatavaks vaateks ühendada, saada näidis või kirjelda praegust töökorraldust lehel kontakt ja tasuta audit.