registrul

Am văzut cum să importăm date externe dintr-un singur fișier, cum să importăm toate fișierele dintr-un folder și cum să importăm date dintr-un tabel/interval denumit în același registru de lucru. Dar dacă vrem să importăm TOATE datele din același registru de lucru? Ei bine, aici ne îndreptăm în această postare.

Dacă există o bună practică Excel pe care utilizatorii o ignoră tot timpul, este aceasta - păstrați datele de același tip într-o singură filă. După cum veți vedea în fișierele noastre de exemplu, ianuarie, februarie și martie sunt același tip de date în aceeași structură, deci ar trebui să fie într-adevăr într-un singur tabel pe o filă. Majoritatea utilizatorilor Excel începători nu gândesc așa, deci există șanse mari să întâlniți aceste tipuri de registre de lucru și va trebui să utilizați această tehnică la un moment dat.

Descărcați fișierul de exemplu

Pentru a lucra împreună cu exemplele de mai jos, descărcați fișierul eșantion. Faceți clic aici, pentru a vă abona și a accesa secțiunea Descărcări.

Exemplele din acest post folosesc Exemplul 11 ​​- Import din Current Workbook.xlsx fişier.

Importați tabele din registrul de lucru curent

Deschideți Exemplul 11 ​​- Import din fișierul Current Workbook.xlsx. Apoi, vom crea cu o interogare necompletată făcând clic pe Date -> Obțineți date -> Din alte surse -> Interogare necompletată

Se va deschide Editorul de interogări Power. Există un pas în fereastra Pași aplicați, nimic în fereastra Previzualizare și majoritatea transformărilor sunt gri. În timp ce fereastra Pași aplicați arată Sursa ca pas, de fapt nu există nimic în acest pas în prezent. Aceasta este cu adevărat o interogare necompletată.

Vom scrie un cod M pentru a oferi sursa Power Query.

Dacă folosim bara de formule, am putea tasta următoarele: Vizualizare -> Bara de formule dacă bara de formule nu este vizibilă).

Sau dacă utilizați Advanced Editor (Acasă -> Editor avansat) am putea avea următoarele:

Amintiți-vă, codul M este sensibil la majuscule, deci va trebui să tastați textul exact așa cum este prezentat mai sus.

Fereastra de previzualizare va afișa tabelele în foaia de lucru.

Apasă pe Pictogramă extindere pentru a trece în structura registrului de lucru. Debifați Folosiți numele coloanei originale ca prefix, apoi apasa O.K.

Fereastra de previzualizare afișează acum datele combinate.

Completați interogarea cu următoarele transformări:

  • Eliminați coloana Nume
  • Modificați tipul de date pentru fiecare coloană
  • Dați interogării un nume adecvat (am ales CombinedTable).

Clic Închideți și încărcați pentru a împinge datele într-o nouă foaie de lucru. Nu știți încă, dar aveți o problemă, vă voi arăta.

Panoul Interogări și conexiuni arată 151 de rânduri încărcate.

Efectuați câteva modificări în tabelele din ianuarie, februarie sau martie și faceți clic Date -> Actualizați toate.

Err ... ce tocmai s-a întâmplat. Acum avem 301 de rânduri, dar nu am mai adăugat niciun rând.

Dacă reîmprospătăm datele din nou, vom avea 451 de rânduri.

Să revenim la Power Query și să vedem ce nu merge bine. În Interogări și conexiune panou faceți dublu clic pe interogare pentru a deschide Editorul de interogări Power.

Faceți clic pe Sursă pas în caseta Pași aplicați, apoi faceți clic pe Acasă -> Reîmprospătare previzualizare. Fereastra de previzualizare arată acum:

Sperăm că vedeți problema acum. Interogarea pe care am creat-o încarcă datele în Excel ca tabel, prin urmare este acum inclusă ca tabelă sursă de fiecare dată când interogarea este actualizată și este combinată cu celelalte tabele, înainte de a fi încărcată din nou în Excel. De fiecare dată când facem clic pe Reîmprospătare, tabelul devine din ce în ce mai lung și mai lung. Seamănă puțin cu versiunea Excel a filmului Inception.

Să rezolvăm această problemă chiar acum. Adăugați un pas după pasul sursă pentru a filtra interogarea combinată.

Va modifica acest filtru pentru a elimina CombinedTables sau va filtra pentru a include tblJanuarie, tblFebruarie și tblMarch? Acest lucru este important, deoarece dorim ca interogarea să se extindă pentru a include tabele noi pe măsură ce acestea sunt adăugate în registrul de lucru.

Uită-te la Formula Bar; codul M va fi:

Vestea bună este că acest cod va elimina tabelul CombinedTables, dar va permite ca orice alte tabele adăugate în registrul de lucru să fie incluse automat în sfera interogării.

Este util să aveți o convenție de denumire standard pentru tabele și interogări. De exemplu, puteți decide că toate tabelele sursă trebuie să aibă tbl la început, acest lucru ne permite să filtrăm numai pentru a include tabele care încep cu tbl. Făcând acest lucru, nu mai contează câte tabele sau interogări avem, efectul de looping poate fi controlat.

Mese sau game

Power Query va importa cu bucurie tabele și game numite. Dacă ambele există în foaia de lucru, ambele sunt listate.

Zonele de imprimare sunt un tip special de gamă denumită, deci sunt și listate. Sunt necesare convenții de denumire și filtrare corecte pentru a ne asigura că interogarea utilizează sursa pe care o dorim.

Conținutul seriei Power Query

  1. Introducere
  2. Importați date
  3. Actualizare date
  4. Editați interogările
  5. Opțiuni de închidere și încărcare
  6. Utilizarea parametrilor
  7. Transformări de bază
  8. Combinați/adăugați interogări
  9. Importați toate fișierele dintr-un folder
  10. Enumerați toate fișierele dintr-un folder și atributele fișierului
  11. Importați date din registrul de lucru curent
  12. Importați date de pe web
  13. Unpivot Data
  14. Decomprimarea datelor într-o coloană
  15. Căutați valori folosind Îmbinare
  16. Modificați locația datelor sursă
  17. Formule
  18. Dacă Declarații pentru logica condiționată
  19. Gruparea și rezumarea datelor
  20. Funcții personalizate
  21. Erori frecvente și cum să le remediați
  22. Sfaturi și trucuri

Nu uita:

Dacă ați găsit utilă această postare sau dacă aveți o abordare mai bună, vă rugăm să lăsați un comentariu mai jos.

Aveți nevoie de ajutor pentru a adapta acest lucru la nevoile dvs.?

Presupun că exemplele din această postare nu s-au potrivit exact cu situația dvs. Cu toții folosim Excel diferit, deci este imposibil să scrieți o postare care să răspundă nevoilor tuturor. Dacă vă alocați timp pentru a înțelege tehnicile și principiile din această postare (și din alte părți de pe acest site), ar trebui să le puteți adapta la nevoile dvs.

Dar, dacă încă te lupți, ar trebui:

  1. Citiți alte bloguri sau vizionați videoclipuri YouTube pe același subiect. Veți beneficia mult mai mult descoperindu-vă propriile soluții.
  2. Întrebați „Excel Ninja” din biroul dvs. Este uimitor ce știu alte persoane.
  3. Puneți o întrebare într-un forum precum Mr. Excel sau Microsoft Answers Community. Amintiți-vă, oamenii de pe aceste forumuri își oferă în general timpul gratuit. Așadar, aveți grijă să vă creați întrebarea, asigurați-vă că este clară și concisă. Enumerați toate lucrurile pe care le-ați încercat și furnizați capturi de ecran, segmente de cod și exemple de cărți de lucru.
  4. Folosiți Excel Rescue, care este partenerul meu de consultanță. Ele ajută oferind soluții la probleme Excel mai mici.

Ce urmează?
Nu mergeți încă, există multe mai multe de învățat pe Excel Off The Grid. Consultați cele mai recente postări: