Centralizare tabele Excel #PowerQuery

Radu, cum pot sa centralizez datele din mai multe tabele in unul singur?

Intrebarea de mai sus am primit-o in mai multe forme prin acest site, hai sa dam un raspuns care de data asta nu implica Macro-uri dar care foloseste o optiune foarte tare numita Power Query.

Centralizare tabele excel

Radu, tu nu ai auzit de COPY – PASTE?

Da, stiu ca poti centraliza datele relativ usor folosind COPY PASTE. Problema e daca trebuie sa faci asta din tabele care primesc informatii noi in mod constant. Daca apar randuri noi zilnic, nu mai e atat de “distractiv” sa tot COPY PASTE.

Centralizare tabele Excel

In video-ul de mai jos poti vedea cum am centralizat 3 tabele din 3 sheet-uri diferite in unul singur folosind Power Query.

Parte dintr-un curs online

Da, video-ul asta face parte din cursul meu online, MS Excel Extended. E un video pe care l-am facut zilele trecute, desi am peste 5 ore de continut video acolo, periodic mai adaug lucruri noi, pe masura ce le descopar.

Power Query

Radu, dar ce e aia Power Query?

E un set de optiuni care iti permite ca sa importi date in Excel din diverse surse, alte fisiere, baze de date, chiar si de pe diverse site-uri. Mai mult decat atat, iti permite si sa le modelezi, adica sa pui filtre, sa stergi coloane de care nu ai nevoie sau sa creezi altele noi.

Daca folosesti Office 2016 sau 365, deja ai Power Query inclus in tab-ul Data. Daca folosesti Office 2010 SP1 sau 2013, il poti instala folosind acest link.

Aveam si inainte de Power Query optiuni de import in Excel. Erau insa, cum sa ma exprim, nashpa. Nu doar ca nu aveai toate optiunile pe care le avem acum, dar si partea de “user experience” aducea a Windows 98, daca il mai tii minte.

In articolul de mai jos (unul mai vechi) poti vedea ce inseamna optiunea de import fara Power Query.

Download

Vrei sa incerci si tu? vezi aici fisierul pe care l-am folosit si eu, spor.

Cum inghet randul de sus in Excel

Radu, cum “inghet” randul de sus ca sa pot vedea capul de tabel chiar si cand fac scroll in jos?

Vezi in video-ul de mai jos un raspuns scurt. Daca nu suporti sa ma mai auzi preferi sa citesti, vezi mai jos. πŸ™‚

Freeze panes in Excel

Nu va ganditi laΒ freon, frigidere sau combine frigorifice, vorbim de Excel. Raspunsul meu e simplu, folosesti optiunea de freeze panes din tab-ul de view, exista chiar si optiunea de “freeze top row”, adica inghetarea randului de sus.

Ingheata randul de susRezultatul e acesta, atunci cand fac scroll in jos capul de tabel ramane vizibil.Ingheata randul de sus

Inghetarea primei coloane

Pot sa inghet si prima coloana astfel incat atunci cand fac scroll in dreapta, aceasta sa ramana vizibila: freeze first column.

Inghetata cu frisca si capsuni

Titlul probabil ca te baga putin in ceata, foarte bine πŸ™‚ . Sunt si situatii in careΒ nu vreau neaparat sa inghet primul rand sau prima coloana, poate ma intereseaza primele 2 randuri sau primele 2 randuri si primele 3 coloane.

Putem face si asta, de data asta nu folosim nici freeze top row, nici freeze first column, folosim freeze panes.

Ingheata randul de sus

Cu mentiunea ca inainte sa dam un click aici e nevoie sa stim un lucru:

Freeze panes va ingheta toate randurile de deasupra si coloanele din stanga celulei selectate.

Am facut cu bold, underline si rosu pentru ca sa fie suficient de clar ca atunci cand lucrezi cu freeze panes, nu selectezi randuri / coloane ci te gandesti la urmatorul lucru:

Care e celula pe care trebuie sa o selectez avand in vedere ca imi va ingheta tot ce e la stanga si deasupra ei.

Poza mai jos:

Ingheata randul de sus

In imaginea de mai sus putem vedea primele 3 randuri inghetate si primele 2 coloane.

Cam atat, ai permisiunea mea sa le spui si colegilor despre asta :), spor in ce faci.

Tabele in Excel: tot ce trebuie sa stii

Ce sunt tabelele in Excel? Cum lucrez cu tabele in Excel?

Pentru asta folosim Excel-ul, nu? Creem tabele si apoi le analizam cu un pivot, facem un grafic sau poate calculam cateva lucruri folosind niste formule. Ceea ce interesant insa e ca ceea ce crezi tu ca este un tabel in Excel cel mai probabil nu este. πŸ™‚

Hai sa facem putina lumina.

Tabele in excel - tot ce trebuie sa stii

Tabele in Excel

Radu, cum adica, tu vrei sa spui ca imaginea de mai jos nu este un tabel in Excel?

Tabele in Excel 1

Da, ceea ce vedem mai sus NU este un tabel in Excel, este o zona de celule sau “range of cells” cum ar spune bunica mea. πŸ™‚

In Excel avem posibilitatea de a insera un tabel, in tab-ul insert, chiar langa Pivot Table.

Tabele in Excel 2

Bun Radu, si care e diferenta intre ce credeam eu ca este un tabel in Excel si ceea ce spui tu ca e un tabel?

Foarte buna intrebare? Da-mi voie sa iti dau un raspuns mai cuprinzator in video-ul de mai jos.

Tabele in Excel: ce aduc in plus

Tabelele in Excel ne aduc cateva facilitati interesante, in video iti vorbesc despre urmatoarele:

  • Aplica filtre automat
  • Adauga formatare dinamica
  • Imi permit sa adaug un rand de total si imi adauga subtotaluri
  • Copiaza automat formulele pe randurile noi ce apar (cel mai util lucru daca ma intrebi pe mine)

 

Avand in vedere ca video-ul are peste 10 minute, cel mai probabil ca vorbesc si despre alte lucruri, nu mai retin exact, te las sa vezi tu. πŸ™‚

Pana cand ma mai gandesc, hai sa punem o poza cu un tabel.

Tabele in Excel 4

Named ranges in Excel

Mi-am amintit πŸ™‚ in video vorbesc si despre “named ranges”. Pe scurt, e o optiune in Excel care imi permite sa dau un nume anumitor zone de celule in Excel.

Parte dintr-un curs online

Da, video-ul de mai sus face parte dintr-un curs online, MS Excel Extended. E un curs online in care mi-am propus sa iti arat cam tot ce stiu despre Excel. Contine multe tutoriale video destul de cuprinzatoare si l-am gandit sa fie un fel de librarie la care sa apelezi atunci cand vrei sa inveti Excel.

E un curs care la momentul in care scriu acest articol, e inca in lucru. Pana acum am finalizat prima parte, cel mai probabil ca il voi lansa in cateva saptamani.

Mai multe detalii aici.

Alta intrebare?! Ai o intrebare?

Te astept in zona de comentarii, iar daca nu e la subiect pe forum.

 

Elimina randuri libere Excel: 2 moduri

Cum pot sa elimin repede toate randurile libere dintr-un tabel in Excel?

In video-ul de mai jos poti vedea 2 moduri in care poti sa faci asta, unul implica filtre si altul “go to special”. Hai sa vedem:

2 moduri

O prima varianta ar fi sa aplicam un filtru pe tot tabelul si apoi sa selectam randurile “blank” in filtru. Aici doar sa ai grija ce selectezi inainte sa aplici filtrul, vrei sa te asiguri ca ai optiunea de blanks acolo. Vezi mai multe in video.

O a doua varianta ar fi sa folosesti F5 – go to special.

Go to special

Doar ca ar fi bine aici sa ai grija ce fel de date ai in tabel. Ar fi bine sa nu ai celule libere, vezi mai multe in video.

Power Pivot in Excel – Introducere

Ce e oare Power Pivot? Si mai important, la ce ma poate ajuta pe mine?

Hai sa vorbim despre Power Pivot, o unealta foarte buna de analiza, sau un fel de Pivot Table cu steroizi.

Power Pivot Introducere Web

Ce e Power Pivot?

Pe scurt, e o extensie a Excel-ului. Asa, mai pe lung, e o facilitate care duce la urmatorul nivel facilitatile de analiza pe care le avem deja intr-un Pivot Table standard.

Bun, dar mai concret?

As aminti 3 lucruri in special:

  1. Putem analiza mai multe tabele in acelasi Pivot
  2. Se misca mult mai repede, pe seturi mari de date
  3. Nu mai avem limita de 1.000.000 de randuri

Daca vrei si mai concret, vezi aici:

Parte dintr-un curs online

Video-ul de mai sus e parte din cursul meu online, MS Excel Extended. L-am scurtat putin inainte sa il urc pe YouTube.

curs online - ms excel extended
MS Excel Extended

Cum sa rulezi un macro – 6 moduri

Radu, cum pot rula un macro in Excel?

Challenge accepted, vezi in video-ul de mai jos 6 moduri in care poti rula un macro in Excel.Β 

Mai exact

In video ti-am aratat urmatoarele moduri:

  1. View tab: acolo unde ai dat record
  2. Scurtatura din taste: daca ai definit-o atunci cand ai inregistrat acel macro
  3. Buton: pe care il poti crea din developer toolbar
  4. Shape: poti asocia un macro pe orice “shape” nu doar pe un buton
  5. Meniu ribbon: poti crea daca vrei un tab separat in care sa adaugi macro-urile pe care le folosesti frecvent
  6. Visual Basic Editor: poti rula usor un macro in timp ce scrii codul

Ce mi-a scapat?

Te astept in zona de comentarii, spune-mi (daca stii) alte moduri in care poti rula un macro in Excel.Β 

Despre macro-uri

Daca citesti acest video si poate inca nu stii despre ce scriu eu aici, arunca un ochi peste acest articol.Β 

Macro-uri in Excel: Introducere

Hai sa vorbim despre macro-uri, o facilitate in Excel despre care stim ca exista dar parca nu ne-am luat timp sa vedem cu ce se mananca mai exact. Imi propun ca in acest articol sa te ajut sa intelegi ce este un marco si sa vezi o situatie in care ar putea fi util.

Macro

Ce o fi oare un Macro?

Un Macro este de fapt un set de comenzi care poate fi executat la un simplu click. Stiu, nu ai inteles, incerc inca o data. Hai sa ne imaginam ca ai de facut anumite sarcini care se repeta si care urmeaza acelasi tipar. Un macro iti permite ca sa inregistrezi secventa de sarcini si apoi sa le executi pe toate la un singur click, intr-un mod “magic”.

Video

Trebuie doar sa ii dai play. πŸ™‚

Dar daca nu iti place vocea mea sau Youtube sau pur si simplu simti nevoia sa listezi acest articol, vezi mai jos in forma scrisa.

Un exemplu

Ca sa intelegem si mai bine luam un exemplu. Rulezi in fiecare saptamana un export dintr-o baza de date. De cele mai multe ori cu exporturile mai ai de lucru, mai stergi cateva randuri irelevante, mai faci un text to columns, etc.

Daca lucrezi pe acel export periodic, poate nu ar fi o idee rea sa inregistrezi un macro care sa faca toti pasii pentru tine.

Macro Enabled Workbook

Inainte sa incepem e important sa stim ca e indicat sa lucram in formatul de fisier Macro Enabled Workbook. Daca il salvam intr-un alt format e s-ar putea sa avem probleme.

Dar unde ai ascuns-o?

O prima problema de care ne lovim e ca nu gasim optiunea Macro in meniu foarte usor, parca e ascunsa in mod intentionat. Ca sa o vedem trebuie sa activam tab-ul Developer din zona de optiuni, vezi imaginea de mai jos.

Macro

It’s showtime

Acum ca am inteles asta “hai sa intram in pita”, dupa cum spuneam, vreau sa prelucram tabelul exportat. Asta inseamna sa stergem cateva randuri, sa facem un text to columns si o suma pe o coloana.

Incepem cu un click pe butonul de record macro, am facut un desen mai jos:

Macro Butonul magic de mai sus ne duce la un ecran unde “completam cerficatul de nastere pentru Macro”, ii dam un nume, un domiciliu si o descriere.

Macro

De acum incolo gata cu vorbaria, trecem la fapte pentru ca Excel inregistreaza si ia aminte. Ceea ce vrem noi sa facem se traduce in urmatoarele actiuni:

  • stergem randurile 1-3, 5
  • facem un text to columns pe coloana A
  • facem o suma pe coloanele A-C

Download

Daca vrei, poti sa descarci fisierul pe care am lucrat aici.

Tu ce zici?

Da-mi idei de alte situatii in care e util sa lucrezi cu Macro-uri in sectiunea de comentarii.

Compara tabele folosind CONSOLIDATE

Din cand in cand mai trebuie sa comparam si tabele in Excel. Ca si dusul gunoiului, e unul din acele lucruri pe care trebuie sa le facem si care tare mult ne place. πŸ™‚

Am invatat recent un mod mai ciudat de a compara tabele in Excel, folosind optiunea CONSOLIDATE, hai sa vedem cum.

compara tabele folosind consolidate

Dar CONSOLIDATE nu era facut pentru altceva?

Daca nu e prima data cand auzi de CONSOLIDATE, probabil ca suna ciudat ca sa o folosim pentru comparare. Asa m-am gandit si eu cand la un curs in Cluj cineva mi-a sugerat asta.

Vezi in video-ul de mai jos cum putem compara tabele folosind CONSOLIDATE.

Download

Daca vrei sa incerci si tu, vezi aici fisierul pe care am lucrat si eu.

Metode alternative

Daca tot suntem la subiectul dusul gunoiului comparare tabele in Excel, sa stii ca am mai scris despre asta si in trecut. Vezi mai jos 2 metode alternative, folosind VLOOKUP sau SUMIF.

Identifica duplicate in Excel

Ai avut vreodata o lista luunga unde ai vrut sa vezi daca anumite valori se repeta? poate e vorba de o lista de CNP-uri sau poate numere de marca ale angajatilor carora vrei sa le iei un cadou de craciun si unde ar fi important sa vezi dublurile.

Hai sa vedem cat e de simplu sa identifici dublurile intr-un tabel.

Duplicate Excel web

Radu, nu ai mai scris despre asta?

Am mai scris pe acest site despre cum poti sa elimini valorile care se repeta, folosind remove duplicates.

De data asta nu vreau sa le sterg automat, vreau sa vad elementele care se repeta, si vedem apoi ce facem cu ele.

Identifica duplicate in Excel

Daca tot am depasit 1000 de abonati pe YouTube, acest articol l-am facut in format video, te invit sa il urmaresti, iar daca ti-a fost de folos la final sa imi dai un like. πŸ™‚

In video-ul de mai sus o sa vezi prezentate 2 abordari.

Formatare conditionata

Probabil ca ai vazut in video ca un mod de a vedea duplicatele este folosind formatare conditionala. Acest mod este si cel mai simplu, iar daca il folosim impreuna cu filter by color, putem foarte repede sa vedem valorile pe care le cautam.

Functia COUNTIF

Un al doilea mod de a proceda este folosind functia COUNTIF. Aceasta functie imi permite sa vad de cate ori apare o valoare pe o coloana. Folosind aceasta functie pot sa vad (de exemplu) doar valorile care se repeta de 3,4,5 ori.

A treia abordare

Evident ca mai exista o varianta, poti verifica manual fiecare din cele 2000 de valori ca sa vezi de cate ori apare. Aceasta abordare e foarte potrivita mai ales in situatia cand nu prea ai de lucru si vrei sa pari ocupat(a).

Si daca e important sa pari ocupat(a) sa stii ca am un articol foarte util pentru tine, vezi mai jos. πŸ™‚

Aprobari in Excel (fisier inclus)

Cum as putea oare sa fac un fisier in Excel care sa imi permita gestionez aprobarile de care am nevoie pentru anumite lucruri.

Chiar am lucrat recent la ceva similar, hai sa vedem ce a iesit.

Aprobari excel web

Dar ce ar trebui mai exact sa faca acest fisier?

Acest articol a pornit de la un fisier la care am dat si eu o mana de ajutor.

Pe scurt, era nevoie de un fisier care sa fie pus pe un server, mai multa lume sa aiba acces, dar doar anumite persoane sa completeze un unele campuri.

Da ai ghicit, doar anumite persoane completeaza in campurile de aprobare.

Pe langa asta, am vrut ca in functie de ce scrie pe o coloana sa vedem ce nivel de aprobare e necesar.

Daca tot nu ai inteles, vezi video-ul de mai jos.

Asta a iesit

De fapt, nu chiar, nu am pus pe site acel fisier, era confidential. Dar am facut ceva asemanator, vezi ce a iesit, sunt sigur ca vei invata nou din acest video.

Download

Costul fisierului este de aproximativ 499 Euro, dar, pentru tine am o super oferta, il poti lua pentru doar 30 bani si poti plati in pufuleti. Link de download aici.

Vezi, nici nu te-am obligat sa te abonezi la lista mea de email. πŸ™‚

Radu, dar pe unde ai disparut, nu ai mai publicat nimic de ceva vreme?

Daca vrei sa vezi pe unde mai umblu, ce mai fac, aboneaza-te la lista mea de email, povestim mai multe pe acolo.