Cuprins
Unul dintre cele mai vizionate videoclipuri de pe canalul meu de YouTube este un videoclip despre Flash Fill în Microsoft Excel. Esența acestui instrument este că, dacă trebuie să transformați cumva datele sursă, atunci trebuie doar să începeți să introduceți rezultatul pe care doriți să-l obțineți în coloana alăturată. După mai multe celule tastate manual (de obicei 2-3 sunt suficiente), Excel va „înțelege” logica transformărilor de care aveți nevoie și va continua automat ceea ce ați tastat, completând toată munca monotonă pentru dvs.:
Chintesența eficienței. Butonul magic „fă-l bine” pe care toți îl iubim atât de mult, nu?
De fapt, există un analog al unui astfel de instrument în Power Query - acolo se numește Coloana din exemple (Coloana din exemple). De fapt, aceasta este o mică inteligență artificială încorporată în Power Query, care poate învăța rapid din datele dvs. și apoi le poate transforma. Să aruncăm o privire mai atentă la capacitățile sale în mai multe scenarii practice pentru a înțelege unde ne poate fi util în sarcini reale.
Exemplul 1. Lipirea/decuparea textului
Să presupunem că avem un astfel de tabel „inteligent” în Excel cu date despre angajați:
Încărcați-l în Power Query în modul standard - cu butonul Din masă/gamă fila Date (Date - Din tabel/gamă).
Să presupunem că trebuie să adăugăm o coloană cu nume și inițiale pentru fiecare angajat (Ivanov SV pentru primul angajat etc.). Pentru a rezolva această problemă, puteți utiliza una dintre cele două metode:
- faceți clic dreapta pe antetul coloanei cu datele sursă și selectați comanda Adăugați o coloană din exemple (Adăugați coloană din exemple);
- selectați una sau mai multe coloane cu date și pe filă Adăugarea unei coloane alege o echipa Coloana din exemple. Aici, în lista derulantă, puteți specifica dacă toate coloanele selectate sau numai trebuie analizate.
Apoi totul este simplu – în coloana care apare în dreapta, începem să introducem exemple de rezultate dorite, iar inteligența artificială încorporată în Power Query încearcă să înțeleagă logica noastră de transformare și să continue mai departe singură:
Apropo, puteți introduce opțiunile corecte în orice celule din această coloană, adică nu neapărat de sus în jos și într-un rând. De asemenea, puteți adăuga sau elimina cu ușurință coloane din analiză mai târziu, folosind casetele de selectare din bara de titlu.
Acordați atenție formulei din partea de sus a ferestrei - aceasta este ceea ce creează Smart Power Query pentru a obține rezultatele de care avem nevoie. Aceasta, apropo, este diferența fundamentală dintre acest instrument și Umplere instantanee în Excel. Umplerea instantanee funcționează ca o „cutie neagră” – nu ne arată logica transformărilor, ci pur și simplu dau rezultate gata făcute și le luăm de la sine înțeles. Aici totul este transparent și poți întotdeauna să înțelegi absolut clar ce se întâmplă exact cu datele.
Dacă vedeți că Power Query „a prins ideea”, atunci puteți apăsa în siguranță butonul OK sau scurtătură de la tastatură Ctrl+Intrați – va fi creată o coloană personalizată cu o formulă inventată de Power Query. Apropo, poate fi editat cu ușurință ulterior ca o coloană obișnuită creată manual (cu comanda Adăugarea unei coloane – coloană personalizată) făcând clic pe pictograma roată din dreapta numelui pasului:
Exemplul 2: Cazul ca în propoziții
Dacă faceți clic dreapta pe antetul coloanei cu text și selectați comanda Transformare (Transforma), apoi puteți vedea trei comenzi responsabile pentru schimbarea registrului:
Convenabil și cool, dar în această listă, de exemplu, personal mi-a lipsit întotdeauna încă o opțiune – caz ca în propoziții, când scrierea cu majuscule (majusculă) devine nu prima literă din fiecare cuvânt, ci doar prima literă din celulă, și restul textului când Acesta este afișat cu litere mici (mici).
Această caracteristică lipsă este ușor de implementat cu inteligența artificială Coloane din exemple – introduceți doar câteva opțiuni pentru ca Power Query să continue în același spirit:
Ca formulă aici, Power Query folosește o mulțime de funcții Text.Super и Text.Jos, conversia textului în majuscule, respectiv în minuscule și funcții Text.Start и Text.Mijlociu – analogi ai funcțiilor Excel LEFT și PSTR, capabili să extragă un subșir din text din stânga și din mijloc.
Exemplul 3. Permutarea cuvintelor
Uneori, la procesarea datelor primite, devine necesară rearanjarea cuvintelor din celule într-o anumită secvență. Desigur, puteți împărți coloana în coloane de cuvinte separate prin separator și apoi o puteți lipi înapoi în ordinea specificată (nu uitați să adăugați spații), dar cu ajutorul instrumentului Coloana din exemple totul va fi mult mai ușor:
Exemplul 4: Numai numere
O altă sarcină foarte importantă este să scoți doar numere (numerele) din conținutul celulei. Ca și înainte, după încărcarea datelor în Power Query, accesați fila Adăugarea unei coloane – coloană din exemple și completați manual câteva celule, astfel încât programul să înțeleagă exact ce vrem să obținem:
Bingo!
Din nou, merită să priviți în partea de sus a ferestrei pentru a vă asigura că Query a generat corect formula - în acest caz, conține o funcție Text. Selectați, care, după cum ați putea ghici, extrage caracterele date din textul sursă conform listei. Ulterior, această listă, desigur, poate fi editată cu ușurință în bara de formule dacă este necesar.
Exemplul 5: numai text
În mod similar cu exemplul anterior, puteți extrage și invers - numai textul, ștergerea tuturor numerelor, semnelor de punctuație etc.
În acest caz, se folosește o funcție care este deja opusă ca sens – Text.Remove, care elimină caractere din șirul original conform unei liste date.
Exemplul 6: Extragerea datelor dintr-un terci alfanumeric
Power Query poate ajuta, de asemenea, în cazuri mai dificile, când trebuie să extrageți informații utile din terciul alfanumeric dintr-o celulă, de exemplu, să obțineți numărul contului din descrierea scopului plății pe un extras de cont bancar:
Rețineți că formula de conversie generată de Power Query poate fi destul de complexă:
Pentru ușurință de citire și înțelegere, poate fi convertit într-o formă mult mai sănătoasă folosind un serviciu online gratuit. Power Query Formatter:
Lucru foarte util – respect pentru creatori!
Exemplul 7: Conversia datelor
Instrument Coloana din exemple poate fi aplicat și coloanelor date sau datetime. Când introduceți primele cifre ale unei date, Power Query va afișa util o listă cu toate opțiunile de conversie posibile:
Deci, puteți converti cu ușurință data originală în orice format exotic, cum ar fi „an-lună-zi”:
Exemplul 8: Categorizare
Dacă folosim instrumentul Coloana din exemple la o coloană cu date numerice, funcționează diferit. Să presupunem că avem rezultatele testelor angajaților încărcate în Power Query (scoruri condiționate în intervalul 0-100) și folosim următoarea gradare condiționată:
- Masters – cei care au obținut mai mult de 90
- Experți – au punctat de la 70 la 90
- Utilizatori – de la 30 la 70
- Începători – cei care au obținut mai puțin de 30
Dacă adăugăm o coloană din exemple în listă și începem să aranjam aceste gradații manual, atunci foarte curând Power Query va prelua ideea noastră și va adăuga o coloană cu o formulă, în care operatorii se imbricau unul în celălalt. if logica va fi implementată, foarte asemănătoare cu ceea ce avem nevoie:
Din nou, nu puteți apăsa situația până la capăt, ci faceți clic pe OK și apoi corectați valorile pragului deja în formulă - este mai rapid astfel:
Concluzii
Cu siguranță un instrument Coloana din exemple nu este o „pilulă magică” și, mai devreme sau mai târziu, vor apărea situații nestandard sau mai ales cazuri neglijate ale unei „ferme colective” în date, când Power Query va eșua și nu va putea găsi ceea ce ne dorim. corect pentru noi. Cu toate acestea, ca instrument auxiliar, este foarte bun. În plus, studiind formulele pe care le-a generat, vă puteți extinde cunoștințele despre funcțiile limbajului M, care vă vor fi întotdeauna la îndemână în viitor.
- Analizarea textului cu expresii regulate (RegExp) în Power Query
- Căutare de text neclar în Power Query
- Umpleți flash în Microsoft Excel