Savaitinė ataskaita dažniausiai ruošiama taip: atsisiunčiamas eksportas, nukopijuojami duomenys, sutvarkomi formatai, atnaujinama suvestinė, išsiunčiamas laiškas. Kas savaitę tie patys veiksmai ta pačia tvarka.
Power Query yra Excel dalis, sukurta būtent tam. Jame vieną kartą aprašote, kaip duomenys paimami ir sutvarkomi, o vėliau tereikia paspausti „Atnaujinti visus“. Žemiau aprašiau, kaip tokį failą sudėlioti, kad jis veiktų ir po pusmečio.
Atskirkite tris sluoksnius
Tvarkingas atnaujinamas failas turi tris aiškiai atskirtas dalis:
- Šaltiniai. Eksportai ir failai, kurių niekas neredaguoja ranka.
- Užklausos. Power Query žingsniai, kurie duomenis sujungia ir sutvarko.
- Rezultatas. Suvestinės lentelės, grafikai ir lapas, kurį rodote vadovui.
Svarbiausia taisyklė: rezultato lape niekas neįrašo duomenų ranka. Kiekvienas rankinis pataisymas dingsta per kitą atnaujinimą arba, dar blogiau, lieka ir tyliai iškraipo skaičius.
Prijunkite šaltinį taip, kad kelias nesikeistų
Dažniausia priežastis, dėl kurios atnaujinimas nustoja veikti, yra pasikeitęs failo pavadinimas ar vieta. Todėl:
- Jei kas savaitę gaunate naują tokios pačios struktūros failą, junkitės prie aplanko, o ne prie konkretaus failo: Duomenys → Gauti duomenis → Iš failo → Iš aplanko. Kitą savaitę užtenka įdėti naują failą į tą patį aplanką.
- Jei failas vienas ir nuolatinis, laikykite jį OneDrive ar SharePoint aplanke, o ne darbalaukyje.
- Kelią iki aplanko verta išsaugoti kaip parametrą, kad jį būtų galima pakeisti vienoje vietoje.
Tvarkykite duomenis užklausoje, ne lape
Viskas, ką įprastai darote ranka, priklauso užklausai: pašalinti tuščias eilutes, pervadinti stulpelius, nurodyti tipus, atskirti datą nuo laiko, pašalinti dublikatus. Kiekvienas veiksmas įrašomas kaip žingsnis, kurį vėliau galima peržiūrėti ar pataisyti.
Keli dalykai, kurie sutaupo daug laiko vėliau:
- Tipus nurodykite aiškiai, ypač datoms ir skaičiams. Lietuviški ir angliški regiono nustatymai skirtingai supranta kablelius ir taškus, todėl automatinis atspėjimas kartais suveikia ne taip.
- Nepasikliaukite stulpelių tvarka. Remkitės pavadinimais, nes eksporte stulpeliai kartais sukeičiami vietomis.
- Nenaudokite sujungtų langelių nei šaltinyje, nei rezultate.
Įkelkite rezultatą ir ant jo statykite ataskaitą
Baigę tvarkyti, pasirinkite Uždaryti ir įkelti arba įkelkite duomenis tiesiai į duomenų modelį, jei lentelė didelė. Suvestines lenteles ir grafikus kurkite ant šios lentelės, o ne ant nukopijuotų duomenų. Tada atnaujinimas pakeičia viską iš karto.
Atnaujinimas
Kasdieniam darbui užtenka Duomenys → Atnaujinti visus. Dar patogiau įjungti atnaujinimą atidarant failą: užklausos ypatybėse pažymėkite Atnaujinti duomenis atidarant failą.
Verta žinoti, ko Excel pats nepadaro: jis neatnaujins ataskaitos naktį, kai failas uždarytas. Jei reikia tikro grafiko, yra trys keliai: kas nors kasdien atidaro failą, atnaujinimą paleidžia atskiras įrankis, arba ataskaita perkeliama ten, kur atnaujinimas veikia serveryje. Jei ataskaitos laukia keli žmonės ir ji negali vėluoti, dažniausiai verta rinktis trečią kelią.
Kas dažniausiai sugriauna atnaujinamą ataskaitą
- Pasikeitęs failo pavadinimas arba vieta. Sprendimas yra aplankas ir parametras.
- Nauja antraščių eilutė eksporte. Sistema atnaujinama, eksporte atsiranda papildoma eilutė, ir visi stulpeliai pasislenka.
- Ranka pridėtos eilutės rezultato lape. Jos arba dingsta, arba lieka ir iškraipo sumas.
- Skirtingi regiono nustatymai. Failas veikia jūsų kompiuteryje ir nustoja veikti kolegos, nes datos suprantamos kitaip.
- Prieigos klaida prie SharePoint ar duomenų bazės. Verta iš anksto susitarti, kas turi prieigą, kad ataskaita neliktų vieno žmogaus paskyroje.
Nuo ko pradėti
Pasirinkite vieną ataskaitą, kurią ruošiate dažniausiai, ir surašykite jos žingsnius. Tie žingsniai yra jūsų užklausos planas. Jei toje ataskaitoje sujungiate kelis failus, pradėkite nuo straipsnio kaip sujungti kelis Excel failus į vieną lentelę, o paskui grįžkite prie atnaujinimo.
Jei failas jau toks didelis, kad Power Query atnaujinimas trunka minutes, o su ataskaita dirba keli žmonės vienu metu, tai ženklas, kad Excel artėja prie savo ribų.