Beveik kiekvienoje įmonėje yra ataskaita, kuri ruošiama iš kelių failų. Filialai atsiunčia savo pardavimus, apskaitos programa pateikia eksportą, o kainos saugomos atskirame faile. Kas savaitę kas nors atidaro visus failus, kopijuoja eilutes į vieną lentelę ir tikisi, kad nieko nepraleido.
Šiame straipsnyje apžvelgsiu kelis būdus tokius duomenis sujungti: nuo paprastos formulės iki sprendimo, kuris kitą savaitę viską padarys pats.
Pirmiausia nuspręskite, kokio sujungimo reikia
„Sujungti failus“ gali reikšti du skirtingus dalykus, ir nuo to priklauso tinkamas būdas.
| Situacija | Pavyzdys | Tinkamas būdas |
|---|---|---|
| Reikia sudėti eilutes vieną po kitos | Kiekvienas filialas atsiunčia tokios pačios struktūros pardavimų failą | Power Query, duomenys iš aplanko |
| Reikia papildyti lentelę duomenimis iš kitos | Prie užsakymų reikia pridėti kainas iš kainoraščio pagal prekės kodą | XLOOKUP arba Power Query užklausų sujungimas |
Pirmu atveju failų struktūra vienoda, jų tiesiog daugėja. Antru atveju lentelės skirtingos, bet turi bendrą raktą, pavyzdžiui, prekės kodą ar kliento numerį.
1 būdas: XLOOKUP, kai reikia papildyti lentelę
Tarkime, lapo „Užsakymai“ A stulpelyje yra prekės kodai, o lape „Kainos“ A stulpelyje yra kodai, B stulpelyje kainos. Kainą prie užsakymo galima pridėti formule:
=XLOOKUP(A2;Kainos!A:A;Kainos!B:B;"Nerasta")
Formulė ieško A2 langelyje esančio kodo lapo „Kainos“ A stulpelyje ir grąžina tos pačios eilutės kainą iš B stulpelio. Jei kodo nėra, vietoje klaidos parodomas žodis „Nerasta“, todėl trūkstamus kodus lengva pastebėti.
Keli dalykai, kuriuos verta žinoti:
- Skirtukas priklauso nuo regiono nustatymų. Lietuviškuose nustatymuose argumentai skiriami kabliataškiu, o angliškuose kableliu.
- XLOOKUP veikia ne visose versijose. Ši funkcija yra Microsoft 365, Excel 2021 ir naujesnėse versijose. Senesnėje versijoje tą patį padarys INDEX ir MATCH derinys:
=INDEX(Kainos!B:B;MATCH(A2;Kainos!A:A;0)). - Dažniausia klaida yra kodų formatas. Jei viename lape kodas saugomas kaip skaičius, o kitame kaip tekstas, formulė jo neras. Taip pat trukdo nematomi tarpai kodo pradžioje ar gale. Juos pašalina funkcija TRIM.
- Jei kodas kartojasi, grąžinama pirma rasta reikšmė. Prieš jungiant verta patikrinti, ar kainoraštyje nėra dublikatų.
XLOOKUP puikiai tinka, kai lentelės nedidelės, o sujungimas daromas retai. Kai failai keičiasi kas savaitę, formulės pradeda strigti: pasikeičia lapo pavadinimas, atsiranda naujų stulpelių, kas nors perkelia lentelę.
2 būdas: Power Query, kai failai atnaujinami reguliariai
Power Query yra Excel dalis, skirta duomenims gauti ir sutvarkyti. Svarbiausia jo savybė ta, kad visus veiksmus jis įsimena. Vieną kartą nustatote, kaip sujungti ir sutvarkyti duomenis, o vėliau tereikia paspausti „Atnaujinti“.
Kaip sujungti visus failus iš vieno aplanko
- Sudėkite visus failus į vieną aplanką. Jų stulpeliai turi būti vienodi ir išdėstyti ta pačia tvarka.
- Excel skirtuke Duomenys pasirinkite Gauti duomenis → Iš failo → Iš aplanko (angliškoje versijoje Data → Get Data → From File → From Folder).
- Nurodykite aplanką ir pasirinkite Combine & Transform Data, kad failai būtų sujungti ir atvėrus redaktorių.
- Pasirinkite lapą ar lentelę, kurią reikia paimti iš kiekvieno failo.
- Power Query redaktoriuje sutvarkykite duomenis: pašalinkite tuščias eilutes, nurodykite stulpelių tipus (ypač datų) ir, jei reikia, pašalinkite dublikatus.
- Pasirinkite Close & Load. Sujungti duomenys atsiras naujame lape kaip lentelė.
Kitą savaitę į aplanką įdėkite naują failą ir skirtuke Duomenys paspauskite Refresh All. Power Query pakartos visus veiksmus su visais aplanke esančiais failais.
Kaip sujungti dvi lenteles pagal raktą
Power Query gali atstoti ir XLOOKUP. Įkėlę abi lenteles į Power Query, pasirinkite Merge Queries, nurodykite bendrą stulpelį, pavyzdžiui, prekės kodą, ir sujungimo tipą. Dažniausiai tinka Left Outer: paliekamos visos pirmos lentelės eilutės ir prie jų pridedami atitinkantys duomenys iš antros.
Skirtingai nei formulės, toks sujungimas nesugenda, kai lentelėje atsiranda naujų eilučių.
3 būdas: makrosas arba Apps Script, kai reikia daugiau nei sujungti
Kartais sujungimas yra tik vienas iš kelių žingsnių. Failai ateina el. paštu, juos reikia išsaugoti, sujungti, sukurti ataskaitą ir išsiųsti vadovui. Tokiems procesams Excel turi VBA makrosus, o Google Sheets turi Google Apps Script.
Google Sheets duomenis iš kito failo galima paimti funkcija IMPORTRANGE, o sujungtus duomenis sutvarkyti funkcija QUERY. Kai failų daug arba jie nuolat keičiasi, patikimiau parašyti Apps Script scenarijų, kuris pagal grafiką duomenis surinks pats.
Kurį būdą rinktis
- Sujungiate retai ir nedaug duomenų: užteks XLOOKUP.
- Tokios pačios struktūros failai ateina reguliariai: rinkitės Power Query.
- Sujungimas yra ilgesnio proceso dalis: verta automatizuoti visą procesą makrosu ar Apps Script scenarijumi.
- Su tais pačiais duomenimis dirba daug žmonių ir svarbu, kas ką pakeitė: tai jau ženklas, kad Excel gali nebeužtekti.