Turinys
Problemos formulavimas
Viename aplanke turime kelis failus (mūsų pavyzdyje – 4 vnt., bendruoju atveju – tiek, kiek norite) Ataskaitos:
Viduje šie failai atrodo taip:
Kurioje:
- Mums reikalingas duomenų lapas visada iškviečiamas nuotraukos, bet gali būti bet kurioje darbaknygės vietoje.
- Už lapo nuotraukos Kiekviena knyga gali turėti kitus lapus.
- Lentelės su duomenimis turi skirtingą eilučių skaičių ir gali prasidėti kita eilute darbalapyje.
- Tų pačių stulpelių pavadinimai skirtingose lentelėse gali skirtis (pvz., Kiekis = Kiekis = Kiekis).
- Stulpeliai lentelėse gali būti išdėstyti kitokia tvarka.
Užduotis: surinkite pardavimo duomenis iš visų lapo failų nuotraukos į vieną bendrą lentelę, kad vėliau būtų galima joje sukurti santrauką ar bet kokią kitą analizę.
1 veiksmas. Stulpelių pavadinimų katalogo paruošimas
Pirmas dalykas, kurį reikia padaryti, yra parengti žinyną su visomis galimomis stulpelių pavadinimų parinktimis ir teisinga jų interpretacija:
Šį sąrašą konvertuojame į dinamišką „išmaniąją“ lentelę naudodami skirtuko lapo mygtuką Formatuoti kaip lentelę Pagrindinis (Pagrindinis – formatuoti kaip lentelę) arba spartusis klavišas "Ctrl"+T ir įkelkite jį į Power Query su komanda Duomenys – iš lentelės/diapazono (Duomenys – iš lentelės/diapazono). Naujausiose „Excel“ versijose ji buvo pervadinta į Su lapais (Iš lapo).
„Power Query“ užklausų rengyklės lange tradiciškai pašaliname veiksmą Pakeistas tipas ir vietoj jo pridėkite naują veiksmą spustelėdami mygtuką fxformulės juostoje (jei ji nematoma, galite ją įjungti skirtuke apžvalga) ir ten įveskite formulę integruota „Power Query“ kalba M:
=Lentelė.Eilutės(Šaltinis)
Ši komanda konvertuos ankstesniame žingsnyje įkeltą komandą Šaltinis nuorodų lentelė į sąrašą, kurį sudaro įdėtieji sąrašai (sąrašas), kurių kiekvienas savo ruožtu yra reikšmių pora Buvo-tapo iš vienos eilutės:
Šio tipo duomenų mums prireiks šiek tiek vėliau, kai masiškai pervadinsime antraštes iš visų įkeltų lentelių.
Baigę konvertuoti, pasirinkite komandas Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… ir importo rūšis Tiesiog sukurkite ryšį (Pagrindinis — Uždaryti&Įkelti — Uždaryti&Įkelti į... — Tik sukurti ryšį) ir grįžkite į „Excel“.
2 veiksmas. Įkeliame viską iš visų failų taip, kaip yra
Dabar įkelkime visų failų turinį iš aplanko – kol kas, kaip yra. Komandų pasirinkimas Duomenys – Gauti duomenis – Iš failo – Iš aplanko (Duomenys – gauti duomenis – iš failo – iš aplanko) tada aplanką, kuriame yra mūsų šaltinio knygos.
Peržiūros lange spustelėkite Konvertuoti (Transformacija) or pokytis (Redaguoti):
Tada išplėskite visų atsisiųstų failų turinį (dvejetainis) mygtuką su dvigubomis rodyklėmis stulpelio antraštėje Turinys:
Power Query pirmojo failo pavyzdyje (Vostok.xlsx) paklaus mūsų lapo, kurį norime paimti iš kiekvienos darbaknygės pavadinimo – pasirinkite nuotraukos ir paspauskite OK:
Po to (iš tikrųjų) įvyks keli vartotojui neaiškūs įvykiai, kurių pasekmės aiškiai matomos kairiajame skydelyje:
- „Power Query“ paims pirmąjį failą iš aplanko (jį turėsime Vostok.xlsx — matyti Failo pavyzdys) kaip pavyzdį ir importuoja jo turinį sukurdamas užklausą Konvertuoti pavyzdinį failą. Šioje užklausoje bus atlikti keli paprasti veiksmai, pvz Šaltinis (prieiga prie failų) Navigacija (lapų pasirinkimas) ir galbūt pavadinimų pakėlimas. Ši užklausa gali įkelti duomenis tik iš vieno konkretaus failo Vostok.xlsx.
- Remiantis šia užklausa, bus sukurta su ja susijusi funkcija Konvertuoti failą (nurodyta būdinga piktograma fx), kur šaltinio failas bus nebe konstanta, o kintamoji reikšmė – parametras. Taigi ši funkcija gali išgauti duomenis iš bet kurios knygos, kurią mes į ją įtraukiame kaip argumentą.
- Funkcija paeiliui bus taikoma kiekvienam stulpelio failui (dvejetainei). Turinys – už tai atsakingas žingsnis Iškvieskite pasirinktinę funkciją mūsų užklausoje, kuri prideda stulpelį prie failų sąrašo Konvertuoti failą su importavimo rezultatais iš kiekvienos darbaknygės:
- Papildomi stulpeliai pašalinami.
- Įdėtų lentelių turinys išplečiamas (žingsnis Išplėstinė lentelės stulpelis) – ir matome galutinius duomenų rinkimo iš visų knygų rezultatus:
3 žingsnis. Šlifavimas
Ankstesnė ekrano kopija aiškiai rodo, kad tiesioginis surinkimas „toks, koks yra“ pasirodė prastos kokybės:
- Stulpeliai yra atvirkščiai.
- Daug papildomų eilučių (tuščios ir ne tik).
- Lentelių antraštės nėra suvokiamos kaip antraštės ir sumaišomos su duomenimis.
Visas šias problemas galite išspręsti labai lengvai – tiesiog pakoreguokite užklausą Konvertuoti pavyzdinį failą. Visi koregavimai, kuriuos atliekame, automatiškai pateks į susijusią failo konvertavimo funkciją, o tai reiškia, kad jie bus naudojami vėliau importuojant duomenis iš kiekvieno failo.
Atidarius prašymą Konvertuoti pavyzdinį failą, pridėkite veiksmus, kad filtruotumėte nereikalingas eilutes (pvz., pagal stulpelį Column2) ir pakeldami antraštes mygtuku Naudokite pirmąją eilutę kaip antraštes (Naudokite pirmąją eilutę kaip antraštes). Stalas atrodys daug geriau.
Kad stulpeliai iš skirtingų failų vėliau automatiškai tilptų vienas po kito, jie turi būti pavadinti vienodai. Tokį masinį pervadinimą galite atlikti pagal anksčiau sukurtą katalogą su viena M kodo eilute. Dar kartą paspauskite mygtuką fx formulės juostoje ir pridėkite funkciją, kurią norite pakeisti:
= Lentelė.Pervardyti stulpelius (# "Paaukštintos antraštės", antraštės, trūksta lauko. Ignoruoti)
Ši funkcija paima lentelę iš ankstesnio veiksmo Paaukštintos antraštės ir pervadina visus jame esančius stulpelius pagal įdėto paieškos sąrašą Naujienos. Trečias argumentas Trūksta lauko. Ignoruoti reikalingas, kad tose antraštėse, kurios yra kataloge, bet nėra lentelėje, neatsirastų klaida.
Tiesą sakant, tai viskas.
Grįžtant prie prašymo Ataskaitos pamatysime visiškai kitokį vaizdą – daug gražesnį nei ankstesnis:
- Kas yra „Power Query“, „Power Pivot“, „Power BI“ ir kodėl jų reikia „Excel“ vartotojui
- Duomenų rinkimas iš visų failų duotame aplanke
- Duomenų surinkimas iš visų knygos lapų į vieną lentelę