Formatul PDF-ul este unul din cele mai folosite în zona de afaceri. PDF-urile pot fi vizualizate, descărcate, trimise prin e-mail sau printate cu ușurință. Dar editarea sau extragerea datelor din fișiere PDF poate fi mai dificilă, mai ales atunci când ai nevoie să extragi datele din PDF în fișiere Excel! Există mai multe situații în care ai putea să imporți un document PDF în Excel. Câteva din acestea pot fi următoarele: primești documente contabile de la parteneri pe care este necesar să le verifici cu propriile înregistrări (de ex fișe de partener), prelucrezi extrase bancare pentru import în programul contabil/ERP-ul companiei sau prelucrezi facturi de furnizori pentru întocmire NIR. Excel are o funcționalitate integrată în Power Query cu care poți importa și converti rapid un PDF în Excel. Aceasta funcționalitate detectează datele stocate într-un format de tabel sau poate extrage toate datele dintr-o pagină dacă conținutul necesar nu este într-un format de tabel. Iar împreună cu caracteristicile de curățare a datelor din Power Query poți pregăti datele înainte de a fi importate. Disponibilitate Posibilitatea de a importa fișiere PDF folosind Power Query în Excel a fost introdusă în versiunea Beta în 2020. Iar acum este disponibilă pentru toți utilizatorii de Office365. Pași necesari pentru a importa un PDF în Excel În acest articol vom parcurge împreună pașii necesari pentru importul informațiilor legate de produsele dintr-o factură PDF în Excel: Tab Data În fișierul Excel în care dorești importarea datelor din PDF selectează tab-ul Data. Get Data From File Aici selectează Get Data -> From File -> From PDF Import În fereastra care se deschide selectează fișierul PDF dorit și apasă butonul Import. Pentru exemplul din acest articol am selectat factura. Navigator În acest moment se va deschide fereastra Navigator, care va fi afișa lista cu toate seturile de date pe care Power Query le-a identificat în PDF. În prima parte a listei vei găsi tabele de date, iar în partea de jos paginile documentului PDF. Dacă dorești să imporți mai mult de un set de date, bifează caseta Select multiple items. Dacă nu ești sigur care este tabelul care conține informațiile necesare, poți selecta fiecare element din lista din stânga pentru a afișa o previzualizare a conținutului în partea din dreapta. Transform Data Selectează tabelul sau pagina dorită și apasă butonul Transform Data. În exemplul de azi am selectat Table003. Editor Power Query Datele selectate vor fi aduse în Editorul Power Query unde pot fi curățate și/sau modificate pentru a se potrivi nevoilor. La importul în Power Query, cel mai probabil, titlurile tabelului vor fi introduse pe primul rând al acestuia. Pentru a remedia acest lucru, apasă pe pictograma meniu din stânga tabelului și selectează opțiunea Use first row as header. În exemplul nostru, tabelul a fost importat cu un rând liber înainte de rândul care conține titlurile coloanelor. Astfel, mai întâi este nevoie să ștergem acest rând. Close & Load în Excel După ce prelucrarea datelor este finalizată, selectează Close & Load pentru a încărca datele extrase într-un tabel Excel. Te invit să urmărești aplicarea tuturor pașilor de mai sus în următorul video: Concluzie Funcționalitatea de import date integrată Power Query este una super utilă. În ultima perioada am ajuns să folosesc importul din PDF foarte des și m-a ajutat să extrag informțiile de care aveam nevoie foarte ușor și foarte rapid. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de importul datelor în Excel din alte documente prin Power Query.
Author: Daniela
Global Excel Summit și Chandoo live Masterclass
Astâzi vreau să vă recomand două evenimente Excel care vor avea loc zilele următoare: Chandoo free masterclass -> Top 10 Advanced Excel Tricks for Data Analysis un eveniment livestream care are loc azi de la ora 18:00 pe youtube. Global Excel Summit -> o conferință care se desfășoara online în perioada 6-8 februarie.a Top 10 Advanced Excel Tricks for Data Analysis Periodic Chandoo realizează evenimente live pe youtube în care abordează subiecte de interes din zona Excel. În livestream-ul de azi va arăta 10 trucuri pentru a face lucrurile în Excel rapid și corect: Funcționalitățoșe Dynamic Array Funcții de procesare a textului Forecasting cu un singur clic Analize rapide Formatare condiționată Cum să combinați două tabele de date Pivot tables Grafice dinamice Grafice dinamice și mai bune Cum să ai cele mai bune rezultate folosind datele tale Tot ce trebuie să faci pentru a participa la acest livestream este să accesezi acest link și să activezi reminderul youtube ( buton – Notification on). Global Excel Summit Conferința are loc între 6 și 8 februarie iar participarea în prima zi este gratuită (într-un număr limitat de locuri). Din experiența evenimentelor anterioare la care am participat, acestea conțin prezentări foarte interesante în care se prezintă funcționalitățile în lucru pentru Excel. Această conferință este pentu tine dacă dorești să afli cele mai noi tendințe și perspective cheie de la cei mai importanți experți în date, cum ar fi Leila Gharani, Chandoo, Bill Jelen, Oz du Soliel, Danielle S Fairhurst și mulți alții. Pe lângă sesiunile de la conferință vor avea loc și 9 masterclass-uri de 3 ore în care se aprofundează și mai mult subiecte avansate Excel sau PowerBI.
Tips&Tricks – Cum să utilizezi Xlookup cu mai multe criterii de căutare
Excel pune la dispoziția utilizatorului mai multe formule prin care se pot extrage informații dintr-un tabel în funcție de un singur criteriu, cum ar fi: vlookup, folosind combinația index+match sau noul xlookup. Dar există situații când ai nevoie să extragi informații dintr-un tabel în funcție de mai multe criterii. Acest lucru este unul destul de provocator în versiunile mai vechi de excel și se poate realiza folosind tot o combinație de index+match, însă în varianta array a acesteia. În versiunile actuale (Office 365 și Excel 2021) această situație se poate rezolva cu ușurință, prin utilizarea funcției Xlookup. Tips&Tricks Funcția XLOOKUP – succesorul mai puternic al VLOOKUP Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date – INDEX Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date – HLOOKUP Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date – VLOOKUP XLOOKUP – concatenarea mai multor criterii O modalitate simplă prin care poți extrage informații folosind Xlookup cu mai multe criterii este prin concatenarea tuturor criteriilor într-o singură valoare de căutare și a tuturor coloanelor de căutare corespunzătoare într-o singură matrice de căutare. Formula generală prin care se poate realiza o căutare cu criterii multiple este următoarea: XLOOKUP(lookup_value_1& lookup_value_2, lookup_array_1& lookup_array_2, return_array, [if_not_found], [match_mode], [search_mode]) Exemplu: XLOOKUP cu mai multe criterii Acum, hai să aplicăm formula de mai sus într-un exemplu practic. Să presupunem că ai un tabel cu o listă de prețuri și este nevoie să afișezi prețul care îndeplinește următoarele criterii simlultan: pentru produsul Capsator care beneficiază de discount (Da pe coloana Discount) Mai jos este formula care face acest lucru: =XLOOKUP(I7&I8,DataSet[Produs]&DataSet[Discount],DataSet[Pret]) În continuare, vom parcurge fiecare pas al formulei: Concatenarea criteriilor prin I7&I8 Prin acest pas nu facem decât să furnizăm argumentului lookup_value valoarea după care să caute, în cazul nostru – CapsatorDa. Concatenarea coloanelor de căutare prin DataSet[Produs]&DataSet[Discount] Prin acest pas, de fapt, construim în cadrul argumentului lookup_array o nouă coloană în care fiecare rând este format din numele produsului si tag-ul Da sau Nu de pe aceleași rânduri din coloanele Produs și Discount din tabelul inițial. Acest pas este cel care ne permite să căutăm toate criteriile simultan. DataSet[Pret] La final, prin DataSet[Pret], Xlookup caută valoarea CapsatorDa în noua coloană construită direct în lookup_array și returnează valoarea aferentă din coloana Pret. Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos: Download Concluzie În acest exemplu am parcurs împreună modalitatea în care poți folosi Xlookup atunci când ai mai multe criterii care trebuie îndeplinite simultan. Cu această ocazie ai putut observa cât de flexibilă este formula Xlookup și cât de ușor se poate folosi în situații mai complexe. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de situațiile în care se poate folosi Xlookup.
Cum se utilizează formula FILTER în Excel?
De cele mai multe ori pentru filtrarea unui tabel de date folosim opțiunea AutoFilter, iar în situațiile mai complexe folosim Advanced Filter. Iar o dată cu introducerea Dynamic Arrays, a fost adăugată și formula Filter. Poate te întrebi în ce situații ar fi necesară această formulă când deja există cele două opțiuni de filtrare a datelor. Din punctul meu de vedere formula Filter poate fi considerată ca una de tip lookup atunci când este necesar să returnezi toate rezultatele găsite într-un tabel. Atât Vlookup/Xlookup, cât și combinația Index+Match extrag doar prima valoare găsită într-un tabel de date. În urmă cu mai mulți ani am avut nevoie să extrag toate înregistrările dintr-un tabel care îndeplineau o condiție. Iar soluția găsită s-a bazat pe o combinație de if, countif, Index și Match. Însă, tabelul trebuia sortat de fiecare dată și mereu trebuia să verific dacă rândurile cu formulă erau suficiente pentru rezultatele afișate. Definiția și sintaxa FILTER Formula Filter permite să preiei sau să filtrezi un set de date pe baza criteriilor furnizate printr-un argument. Astfel poți crea liste dinamice, pe baza unor criterii, care se actualizează automat, atunci când datele sursă se modifică. Aceasta este disponibilă în Office365 și Excel 2021. Formula este clasificată în categoria celor de tipul Dynamic Arrays. Rezultatul este o matrice dinamică care se revarsă automat în celulele învecinate vertical sau orizontal. FILTER(array, include, [if_empty]]) Filter are doar trei argumente, primele două fiind obligatorii. array – reprezintă intervalul de celule pe care dorești să îl filtrezi. include – este un argument de tip logic în care furnizezi criteriile de filtrare. Acest argument se asemănă cu porțiunea de testare a unei funcții If; mai exact se selectează un interval de celule și se completează valoarea criteriu. În urma acestei comparații, orice înregistrare care îndeplinește criteriul specificat (TRUE) va fi inclusă în rezultatul formulei Filter. Iar orice înregistrare care nu îndeplinește criteriul (False) va fi exclusă din rezultat. [if_empty] – este un argument opțional în care poți introduce valoarea care va fi afișată atunci când nu există rezultate în urma aplicării criteriilor de la argumentul include. Caracteristici importante ale formulei FILTER Funcția FILTER se poate utiliza pentru a filtra o zonă de date atât orizontal, cât și vertical. Atunci când utilizezi FILTER între două fișiere excel este necesar ca ambele să fie deschise. Dacă nu sunt, funcția va returna eroarea #REF!. Deoarece funcția FILTER varsă rezultatul în mai multe celule, este nevoie să te asiguri că sunt suficiente celule goale în dreapta și dedesubt. Dacă nu, funcția va afișa eroarea #SPILL. Cum se utilizează FILTER? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcția Filter. Să presupunem că ai următoarea bază de date cu vânzările din anul 2022 pe produse și pe locații: Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Exemplul 1: Filtrarea valorilor în funcție de un criteriu de tip text . Este necesar să extragi fiecare locație care a realizat vânzări de Capsatoare și valoarea acestor vânzări. Mai jos este formula care face acest lucru: =FILTER(DataSet[[Magazin]:[Valoare vanzari ]],DataSet[Produs]=”Capsator”,”Not found”) Pentru primul argument am selectat doar coloanele cu locațiile și valoarea vânzării. Însă se poate selecta și tot tabelul. Când apeși Enter, Excel va afișa prima locație găsită pentru produsul Capsator în celula J11, iar valoarea vânzării în celula K11. Celelalte rezultate fiind vărsate în celulele de mai jos. Pentru baza de date din acest exemplu am folosit un tabel de tip Table: atunci când este adăugată o nouă înregistrare în tabel, Filter se actualizează automat pentru a include valoarea suplimentară în Spill Range. Exemplul 2: Filtrarea valorilor în funcție de un criteriu de tip numeric . De data aceasta, dorești să extragi produsele și locațiile care au realizat vânzări mai mari de 1000 de lei. Mai jos este formula care face acest lucru: =FILTER(DataSet2[[Produs]:[Valoare vanzari ]],DataSet2[[Valoare vanzari ]]>J8,”Not found”) În acest exemplu, am introdus criteriul în formulă folosind celula J8. Acest lucru oferă mai multă flexibilitate utilizatorului pentru a verifica mai multe situații fără a fi nevoie să modifice formula. Exemplul 3: Filtrarea valorilor folosind wildcards la criteriu . Formula FILTER nu acceptă caractere de tip wildcard, dar putem folosi o combinație de formule pentru a introduce un test logic în argumentul include. Este necesar să extragi produsele, locațiile si valoarea vânzărilor pentru toate produsele care conțin caractere “ca”. Pentru această situație vei folosi următoarea formulă: =FILTER(DataSet3[[Produs]:[Valoare vanzari ]],ISNUMBER((SEARCH(J8,DataSet3[Produs]))),”Not found”) Să parcurgem fiecare componentă a formulei și să vedem ce rol joacă fiecare. Prima oară se calculează rezultatul formulei SEARCH. Aceasta caută orice text din coloana Produs care are conține „ca”. Avem 3 astfel de produse: Capsator, Creion mecanic și Caiet dictando. Este important să reținem faptul că Search returnează doar poziția caracterului căutat. În continuare, folosim formula ISNUMBER, care are un rol foarte simplu. Aceasta verifică fiecare rezultat oferit de Search și va atribui TRUE pentru rezultatele care sunt un număr și FALSE pentru rezultatele care nu sunt un număr (adică sunt o eroare #VALUE! în cazul nostru). În continuare, Isnumber transmite rezultatul acestei verificări (adică 12 TRUE pentru numere și 12 FALSE pentru erorile #VALUE!) către formula Filter. Exemplul 4: Filtrarea valorilor prin respectarea a două criterii în același timp . Poate te întrebi dacă Filter se poate folosi și atunci când avem nevoie ca filtrarea să se realizeze în funcție de două condiții care să fie respectate simultan. Da, se poate și chiar într-un mod foarte ușor. =FILTER(DataSet4[[Produs]:[Valoare vanzari ]],(DataSet4[Produs]=J7)*(DataSet4[[Valoare vanzari ]]>J8),”Not found”) Introducem cele două criterii în argumentul include, fiecare fiind scrise intre paranteze () cu semnul înmulțirii * între condiții. Folosind „*” practic, rezultatele după verificarea celor două criterii se înmulțesc. Astfel există două posibilități: Se obține FALSE dacă expresiile din ambele paranteze returnează false (false*false) sau una dintre ele returnează false (false*true). Se obține TRUE dacă expresiile din ambele paranteze returnează true (true*true). Exemplul 5: Filtrarea valorilor care respectă cel puțin un criteriu . În acest exemplu, vom folosi Filter pentru a extrage informațiile legate de produse, locații și valoarea vânzărilor care respectă Read More
Cum utilizăm Excel Fill Series?
Fill Series este o funcționalitate foarte utilă a Excel-ului care permite completarea rapidă a valorilor în celulele selectate. Această autocompletare a valorilor se realizează în funcție de setările realizate de utilizator. Acest articol despre Fill Series te va ajuta să înțelegi modalitățile în care poți folosi această funcționalitate pentru a economisi timp atunci când lucrezi în Excel. Ce este Fill Series? Fill Series este modalitatea prin care poți introduce automat date în celule consecutive din mai multe rânduri sau coloane. Astfel, folosind funcționalitățile integrate în Excel de predicție inteligentă, Fill Series adăugă datele instantaneu în intervalul de celule selectat, iar valorile sunt separate prin intervale egale de unități, cum ar fi zile, pași, luni etc. Fill Series se poate accesa prin Fill Handle sau din tab-ul Home -> Fill -> Series Options. Fill Handle este situat în colțul din dreapta jos al oricărei celule selectate. Când trecem mouse-ul peste colțul din dreapta jos al celulei, cursorul arată un semn plus, care este acest Fill Handle. Pentru a folosi această opțiune, pur și simplu facem clic și îl tragem în sus/jos sau stânga/dreapta pentru a copia valorile sau conținutul celulelor selectate în celulele noi. Pentru a deschide fereastra Fill Series în tab-ul Home apăsăm butonul Fill și apoi apăsăm pe Series. Cum se utilizează Fill Series? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcționalitatea Fill Series. Exemplul 1: Completarea unei serii liniară într-o coloană . Să presupunem că avem o listă cu produsele de papetărie vândute de un magazin și dorim ca în prima coloană să numerotăm produsele din listă. Altfel spus, vom crea o listă de numere consecutive care pornește de la 1: Selectăm celulele în care dorim completarea listei de numere. În exemplul nostru acestea sunt D7:D17. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns, Linear și la Step Value scriem 1. Ca rezultat celulele selectate se vor completa automat cu numerele 2, 3, 4…11. O altă modalitate în care se poate completa automat lista de numere este folosind Fill Handle. De data aceasta este suficient să selectezi celula D7 care conține numărul 1, să apeși pe semnul + din colțul din dreapta jos și să tragi în jos până la celula D17. Prima oara lista se va completa automat doar cu numărul 1. Pentru a modifica acest lucru este necesar să apeși pe butonul care apare tot în colțul din dreapta jos și să alegi opțiunea Fill Series. Exemplul 2: Completarea unei serii liniară din 100 în 100 . De data aceasta vom crea o listă de numere care pornesc de la 100 și numerele cresc liniar tot cu 100. Selectăm celulele în care dorim completarea listei de numere: D20:D30. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns, Linear și la Step Value scriem 100. Dacă dorești ca lista de numere să se termine la o anumită valoare, poți adăuga acest lucru la Stop Value. Exemplul 3: Completarea unei serii progresive cu 10 . Pe lângă seriile liniare cu Fill Series poți crea și serii progresive. Vom crea o listă de numere care pornesc de la 1 și numerele cresc progresiv cu 10 pâna la 100.000. Selectăm celulele în care dorim completarea listei de numere: D7:D17. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns, Growht. La Step Value completăm 10 și la Stop Value 100,000. Ca rezultat celulele selectate se vor completa automat cu numerele 10, 100, 1000, 10000 și 100000. Exemplul 4: Completarea unei serii de date zilnice . Să presupunem că dorim să creăm o listă cu toate datele din luna ianuarie, de la 1 ianuarie 2023 până la 31 ianuarie 2023. Introducem data 01.01.2023 în celula D7. Selectăm celulele în care dorim completarea listei de numere: D7:D37. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns și Date. Excel detectează în mod automat tipul de data iar tipul Date va fi bifat când se va deschide fereastra Series. În zona Date unit ne asigurăm ca este bifat Day. La Step Value completăm 1. Exemplul 5: Completarea unei serii de zile lucrătoare . În acest exemplu ne folosim de opțiunea Weekday din zona Date unit pentru a crea o lista cu zilele lucrătoare din luna Februarie 2023. Introducem data 01.02.2023 în celula D7. Selectăm celulele în care dorim completarea listei de numere: D7:D18. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns și Date. În zona Date unit bifăm Weekday. La Step Value completăm 1. Exemplul 6: Completarea unei serii de date lunare . În acest exemplu ne folosim de opțiunea Month din zona Date unit pentru a crea o lista cu prima zi din fiecare lună din 2023. Introducem data 01.01.2023 în celula D7. Selectăm celulele în care dorim completarea listei de numere: D7:D18. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns și Date. În zona Date unit bifăm Month. La Step Value completăm 1. Exemplul 7: Completarea unei serii de date anuale . Ultima opțiune din zona Date unit este cea anuală, Year. Ne vom folosi de aceasta pentru a crea o lista cu ultimele zile din fiecare an din perioada 2023 – 2030. Introducem data 31.12.2023 în celula D7. Selectăm celulele în care dorim completarea listei de numere: D7:D14. Accesăm Fill Series din tab-ul Home. În fereastra care se deschide bifăm căsuțele Columns și Date. În zona Date unit bifăm Year. La Step Value completăm 1. Concluzie În acest articol am folosit funcționalitatea Fill Series pentru a crea automat și foarte rapid mai multe liste cu valori. Aceasta vine în ajutorul nostru când e nevoie să creâm liste de numere sau zile care urmează un anumit model. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de Fill Series.
Introducere în Power Query
Atunci când lucrezi cu seturi de date, sau cu baze de date, posibilitatea de analizare a lor este strâns legată de calitatea datelor utilizate. Altfel spus, “garbage (date) in, is garbage (analysis) out” Analizele de înaltă calitate se bazează pe date bine organizate și fără erori. Însă procesul de curățare, manipulare și de combinare a datelor este unul care presupune un efort considerabil. Primul contact pe care l-am avut cu Power Query a fost la conferința Excel Days 2019 care s-a ținut la Sofia. Ken Puls și Gasper Kamensek au avut o prezentare în care au folosit Power Query și au demonstrat cum poți realiza toată partea de transformare a datelor în Excel cu Power Query și analizarea datelor în Power BI. Vă invit să vizualizați prezentarea pe care au susținut-o la această conferință. Cei doi au prezentat fluxul activităților de la primirea setului de date, pregătirea datelor in Power Query, crearea modelului de date si a măsurilor necesare pentru analiza dorită, finalizând cu publicarea datelor si preluarea lor in Power BI unde datele au fost folosite in crearea rapoartelor dorite. Tot la Excel Days 2019 am participat la un masterclass realizat de Ken Puls în care am folosit doar Power Query. Pentru mine, a fost o super experiență pentru că am înteles potențialul de automatizare pe care îl aduce Power Query. Dar, am avut nevoie de un pic de timp în care să testez mai multe funcționalități ale Power Query și să îl adaug în activitățile mele curente. Însă acum, de fiecare dată când primesc exporturi de date din programe diferite toată partea de pregătire a datelor o fac doar în Power Query. Ce este Power Query? Power Query este un instrument prin care poți importa, transforma, pregăti datele si automatiza acest proces care este oferit de Microsoft ca parte din Excel și Power BI. Principalele avantaje oferite de Power Query sunt următoarele: Poți prelua date prin conectarea la orice tip de sursă de date Cureți, transformi și remodelezi datele în funcție de nevoile tale Poți combina mai multe seturi de date (uniuni de tabele, anexări) Tot procesul de colectare, transformare și gestionare a datelor este salvat și poate fi folosit automat (prin refresh) de fiecare dată când sursele de date se actualizează. Power Query are integrate multe funcții utile, cum ar fi adăugarea de date și crearea de relații între diferite seturi de date. De asemenea, se pot grupa și rezuma datele cu ajutorul acestui instrument. Astfel, oferă ocazia oricărei persoane cu abilități de bază Excel să efectueze importarea, remodelarea și curățarea datelor, folosind doar interfața utilizatorului prin câteva click-uri simple. Disponibilitate Power Query în Excel Power Query este disponibil ca add-on gratuit în Excel 2010 și 2013, pe care îl puteți descărca de pe site-ul Microsoft. Link-ul este disponibil aici. După instalarea add-on ului, fișierele excel vor avea un nou tab numit Power Query. Începând cu Excel 2016 si Excel 365 acesta este un instrument integrat in program, disponibil în tab-ul Data zona Get&Transform Data. Cum arată Power Query? Fereastra Power Query arată aproape la fel ca una Excel. Lucrul cheie de reținut este că Power Query este pentru conectarea, curățarea și transformarea datelor. Acest lucru se face prin aplicarea de diverși pași pe datele avute. Fereastra Power Query este împărțită în 6 zone: 1. Bara de meniu Aceasta este similară cu cea din Excel. Tab-ul care se încarcă automat este numit Home și conține diverse funcții comune de prelucrare a datelor, printre care cele de ajustare a rândurilor/coloanelor, de combinare a mai multor tabele. Bara de meniu mai conține tab-urile Transform, Add Column și View. 2. Listă Queries Aici sunt afișate toate interogările (queries) pe care le conține fișierul Excel deschis. Poți lucra pe o singură interogare o dată. 3. Vizualizare date În această zonă se afișează pasul curent al interogărilor realizate pe setul de date încărcat. Pe măsură ce se adaugă noi pași, această previzualizare este actualizată. Această zonă din Power Query este foarte asemănătoare cu un sheet din Excel. 4. Proprietăți Aici poți modifica numele unui query, vedeți toți pașii aplicați până în acel moment. Folosim acest lucru pentru a face modificări la interogare sau pentru a șterge orice pași. 5. Pași aplicați setului de date În această fereastră sunt afișați toți pașii de transformare aplicați pe setul de date. Aceștia sunt afișați în ordine cronologică. Puteți adăuga, elimina, edita sau reordona pașii dacă este necesar. 6. Bara de formule Aceasta este opțională și se poate dezactiva din tab-ul View. În mod implicit, Power Query se deschide cu bara de formule activată. Aceasta afișează codul M Language corespunzător pentru pasul curent. Cum utilizăm Power Query? (exemplu) În exemplul următor vom realiza câteva transformări simple care te vor ajuta să înțelegi mai bine cum funcționează Power Query. În acest exemplu, folosim ca bază de date un export dintr-un soft de gestiune stocuri folosit de o companie care vinde articole de papetărie. Fișierul exportat conține toate vânzările realizate într-o anumită perioadă pe fiecare articol. Lunar este nevoie să realizezi o analiză pe vânzările acestei companii. Însă informațiile din export nu pot fi folosite direct, fără alte prelucrări, pentru a realiza analiza lunară. Problema exportului sunt rândurile libere dintre produse. În următorul video poți vedea cum se încarcă un set de date în Power Query și cât de ușor se pot realiza câteva transformări simple: ștergerea rândurilor goale, ștergerea coloanelor care nu sunt utile în analiză și sortarea datelor. Mai mult decât atât, Power Query reține fiecare pas aplicat în prelucrarea datelor. În exemplul nostru, analiza vânzărilor se realizează lunar. Astfel că pentru prelucrarea datelor tot ce am de făcut este să copiez noul export de date și să apăs butonul Refresh. Pentru a descărca fișierul cu exemplu folosește butonul de mai jos: Download Surse de date pentru conectare cu Power Query În exemplul anterior, pentru importarea datelor am folosit un set de date direct din fișierul Excel în care lucram. Dar Power Query se poate conecta la diferite surse de date: Fișiere: fișiere Excel, Read More
Funcția Textjoin – cum poți combina textul din mai multe celule într-una singură
În fișierele Excel informațiile nu sunt întotdeauna structurate în funcție de nevoile tale. Adesea este posibil să dorești să împărți conținutul unei celule în celule individuale sau să faci opusul – să combini datele din două sau mai multe celule într-una singură. Cel mai simplu exemplu este combinarea într-o singură celulă a numelui cu prenumele unei persoane. Excel pune la dispoziție mai multe moduri în care poți combina textul din mai multe celule într-una singură. Iar în acest articol vom explora funcția Textjoin, care este considerată alternativa mai puternică și mai flexibilă. Definiția și sintaxa TEXTJOIN Formula Textjoin combină șiruri de text din mai multe celule sau zone de celule și separă valorile combinate folosind orice delimitator care este specificat de utilizator. De asemenea, există posibilitatea de a ignora sau de a include celule goale în rezultat afișat. Funcția este disponibilă în Excel pentru Office 365, Excel 2021 și Excel 2019. TEXTJOIN(delimiter, ignore_empty, text1, [text2], …) Textjoin are următoarele argumente, primele trei fiind obligatorii: delimiter – reprezintă separatorul care se dorește a fi folosit între fiecare valoare de text care se combină. De obicei, este furnizat ca un șir de text închis între ghilimele duble sau o referință la o celulă care conține un șir de text. ignore_empty – este un argument de tip logic care indică dacă celulele goale vor fi ignorate sau nu: true – pentru ignorarea celulelor goale. false –pentru includerea celulelor goale în rezultatul afișat. text1 – reprezintă prima valoare care va fi combinată de formulă. Poate fi furnizat ca șir de text, o referință la o celulă sau o zonă de celule. [text2], … – folosind aceste argumente opționale poți adăuga valori suplimentare de text care urmează să fie unite. Sunt permise maximum 252 de argumente text, inclusiv argumentul text1. Textjoin combină datele în ordinea în care sunt introduse în formulă. De exemplu dacă în celula A1 este textul „Salut” și în A2 „tuturor”, următoarea formulă returnează „Salut tuturor”: =TEXTJOIN(” “,TRUE,A1,A2) 6 lucruri de reținut despre TEXTJOIN Pentru a utilizarea eficientă în Excel a formulei Textjoin există câteva puncte importante pe care trebuie să le ai în vedere: Este o funcție nouă, care este disponibilă începând cu Excel 2019 – Excel 365. În versiunile anterioare de Excel, poți utiliza funcția CONCATENATE sau operatorul „&”. Eroarea #NAME? va fi returnată atunci când se utilizează Textjoin într-o versiune Excel mai veche. Orice valoare de tip număr sau dată furnizată pentru argumentul delimiter sau pentru argumentele text va fi convertită în text. Dacă delimitatorul nu este specificat sau este un șir gol (“”), valorile textului sunt combinate fără niciun delimitator. Funcția poate gestiona până la 252 de argumente text. Textul rezultat poate conține maximum 32.767 de caractere, Dacă această limită este depășită, formula Textjoin va returna eroarea #VALUE!. Cum se utilizează Textjoin? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcția Textjoin. Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Exemplul 1: Combinarea valorilor din mai multe celule într-un text separat prin virgulă . Să presupunem că ai un tabel cu hotelurile de 5 stele din București iar adresa fiecăruia se regăsește în 3 coloane: Este necesar să adaugi în tabel o coloană care să conțină pentru fiecare hotel întreaga adresă. Mai jos este formula care face acest lucru: =TEXTJOIN(“, “,TRUE,DataSet[@[Adresa 1]:[Adresa 3]]) Formula de mai sus folosește doar primul argument Text1, deoarece am selectat toate celule care conțin informațiile legate de adresa unui hotel. Exemplul 2: Combinarea valorilor dintr-o coloană într-un text separat prin virgulă . Formula Textjoin poate fi folosită și pentru a combina textul din mai multe celule aflate în aceeași coloană. De data aceasta ai un tabel cu situația meciurilor din grupa B de la campionatul mondial din 2018. Și dorești să afișezi într-o singură celulă rezultatul fiecărui meci pentru fiecare echipă. =TEXTJOIN(“,”,TRUE,E$7:E$9) Exemplul 3: Combinarea valorilor din mai multe celule folosind mai mulți delimitatori diferiți . Pot exista situații în care este nevoie ca valorile combinate să fie separate folosind delimitatori diferiți. Textjoin permite acest lucru. Delimitatorii pot fi furnizați ca o constantă de tip array sau prin scrierea fiecărui delimitator în câte o celulă separată și se utilizează zona de celule pentru argumentul delimitator. Pentru acest exemplu folosim un tabel cu numele angajaților. Tabelul are trei coloane Nume, Prenume 1 si Prenume 2. Dorești să unești celulele care conțin diferite părți de nume și să afișezi numele întreg al fiecărei persoane în acest format: Nume, Prenume Nume. Pentru această situație vei folosi următoarea formulă: =TEXTJOIN($F$7:$G$7,TRUE,DataSet3[@[Nume]:[Prenume 2]]) Am ales să introduc delimitatorii “, “ și “ “ în celulele F7 și G7. Formula care include scrierea separatorilor doriți direct în argumentul delimiter este următoarea: =TEXTJOIN({“, “,” “},TRUE,DataSet3[@[Nume]:[Prenume 2]]) Folosind separatori diferiți poți combina conținutul celulelor în o multitudine de forme. Exemplul 4: Combinarea celulelor de tip text cu cele de tip dată . De asemenea, pot exista situații când este nevoie să combini valori de tip text cu valori de tip dată. Atunci când furnizăm valori de tip dată, Textjoin le transformă automat în text. Excel stochează datele ca numere de serie, astfel încât formula va returna un număr reprezentând data: Pentru a remedia acest lucru este nevoie să convertești data într-un șir de text înainte de a o folosi în Textjoin. Pentru acest lucru funcția Text vine în ajutorul tău: =TEXTJOIN(” “,TRUE,[@Nume],TEXT([@[Data nasterii]],”mm/dd/yyyy”)) Exemplul 5: Combinarea valorilor din mai multe celule în funcție de o condiție . Datorită capacității formulei Textjoin de a gestiona array-uri care conțin șiruri de caractere, formula poate fi folosit și pentru a combina în mod condiționat conținutul a două sau mai multe celule. Pentru a realiza acest lucru vei utiliza IF în cadrul unui argumentului Text1 din Textjoin. În cadrul formulei If se va evalua zona de celule și va returna doar celulele care îndeplinesc condiția specificată. Pentru acest exemplu folosești un tabel care conține lista angajaților si asocierea lor pe departamentele Livrare și Producție. Este nevoie să afișezi într-o singură celulă angajații pentru fiecare departament. Mai jos este formula care Read More
Cum se calculează numărul de luni dintre două date în Excel?
Zilele acestea am analizat situația paginilor accesate de voi pe acest site. Cea mai accesată pagină începând cu 1 Octombrie a fost cea în care am prezentat 3 metode pentru calcularea varstei unei persoane in Excel. Pentru că interesul este așa de mare, m-am gândit să prezint și modalitățile în care se poate calcula numărul de luni dintre două date. Numărul de luni dintre două date poate fi necesar în activitățile legate de managementul proiectelor. În acest articol vei vedea câteva formule și combinații de formule pe care le poți folosi pentru a calcula numărul de luni dintre două date. 1. Folosind funcția DATEDIF (calcularea numărului de luni finalizate între două date) În cadrul acestui exemplu, vom folosi un tabel în care sunt centralizate proiectele din anul curent dintr-o companie cu data de început și data de finalizare. Dorești să calculezi numărul de luni întregi dintre data de finalizare și cea de început pentru fiecare proiect. Pentru acest lucru vom folosi formula Datedif. Aceasta se folosește pentru a calcula diferența dintre două date într-o varietate de forme, cum ar fi numărul de ani, numărul de zile sau luni dintre cele două date. Din motive necunoscute, aceasta este documentată doar în Excel 2000, dar se poate utiliza în toate versiunile Excel ulterioare acesteia. Datedif este una dintre puținele funcții nedocumentate din Excel. Astfel că nu o vei găsi în secțiunea de Help a Excelului sau în lista de formule din Insert Function. Deci, atunci când utilizezi Datedif trebuie să cunoști sintaxa formulei. Apasă aici pentru a accesa tutorialul de prezentare a formulei DATEDIF! Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Ne întoarcem la exemplul nostru pentru a calcula numărul de luni întregi dintre data de finalizare și cea de început pentru fiecare proiect folosind formula Datedif. Aceasta are 3 argumente start_date, end_date și unit. În acest caz, dorești numărul de luni, așa că la argument unit vei completa „m”: =DATEDIF([@[Data inceput]],[@[Data final]],”m”) 2. Folosind funcția DATEDIF (calcularea numărului de luni precum și numărul de zile dintre două date) Folosind același tabel, de data aceasta dorești să calculezi numărul de luni dar și numărul de zile dintre data de finalizare și cea de început pentru fiecare proiect. Pentru acest exemplu vom combina două formule Datedif: =DATEDIF([@[Data inceput]],[@[Data final]],”M”)&”M “&DATEDIF([@[Data inceput]],[@[Data final]],”MD”)&”D” Formula Datedif va exclude data de început la numărarea lunilor. De exemplu, pentru un proiect care începe pe 01 ian și se termină pe 31 ian, Datedif va avea ca rezultat 0 -> deoarece aceasta nu contorizează data de 01 ian și au fost doar 30 de zile între cele două date. 3. Folosind funcția YEARFRAC (calcularea numărului de luni finalizate între două date) O altă metodă de a obține numărul de luni dintre două date specificate este folosind formula Yearfrac. Aceasta folosește o dată de început și una de sfârșit și returnează numărul de ani care au trecut în aceste două date. Spre deosebire de Datedif, rezultatul oferit de Yearfrac este o număr cu zecimale. De exemplu, dacă data de început este 01 ianuarie 2022 și data de sfârșit este 31 ianuarie 2022, rezultatul funcției Yearfrac va fi 0,0833. Folosind valoarea anului poți să obții numărul de luni înmulțind-o cu 12. =YEARFRAC([@[Data inceput]],[@[Data final]])*12 Rezultatele obținute folosind formula de mai sus sunt numere cu zecimale. În cazul în care dorești să obții doar numărul de luni complete, formula se poate include într-o formulă Int: =INT(YEARFRAC([@[Data inceput]],[@[Data final]])*12) 4. Folosind combinația de formule YEAR și MONTH Mai există o metodă pe care o poți folosi pentru a calcula numărul lunii dintre două date. Combinând funcțiile Year și Month, poți crea următoarea formulă: =(YEAR([@[Data final]])-YEAR([@[Data inceput]]))*12+MONTH([@[Data final]])-MONTH([@[Data inceput]]) YEAR([@[Data final]])-YEAR([@[Data inceput]]))*12 În prima parte avem funcția Year pentru a extrage atât anul datei de final și cât și al datei de început. Apoi calculăm diferența dintre cei doi ani și înmulțim rezultatul cu cei doisprezece. MONTH([@[Data final]])-MONTH([@[Data inceput]] În a doua parte a formulei, extragem lunile datei de final și al datei de început folosind funcția Month. Și această parte ignoră și zilele în care avem acces. La fel ca Datedif, data de început va fi complet ignorată în calcularea numărului de luni. Poți observa pe rândul Proiect G că rezultatul oferit de formulă este 0. Dacă dorești ca rezultatul să includă și data de început, poți utiliza următoarea formulă: =(YEAR([@[Data final]])-YEAR([@[Data inceput]]))*12+MONTH([@[Data final]]+1)-MONTH([@[Data inceput]]) Concluzie Acestea sunt modalitățile prin care poți calcula lunile dintre două date în Excel. Te invit să le testezi pentru a putea alege metoda potrivită pentru tine. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de utilizarea formulelor Datedif, Yearfrac, Year și Month.
Funcția Unique – modalitatea rapidă de a extrage valorile unice în Excel
Atunci când lucrezi cu tabele mari de date unele valori se pot regăsi de mai multe ori și este posibil să fie nevoie să extragi valorile unice din anumite coloane. Există câteva variante în care poți realiza acest lucru: folosind opțiunea Remove Duplicate generând un Pivot table în care la row folosești coloana din care dorești să extragi valorile unice folosind opțiune Advanced Filter Însă, folosind variantele de mai sus, atunci când se modifica informațiile din tabel este nevoie să extragi din nou valorile unice sau să actualizezi pivot table-lul. Vestea bună este că, o dată cu introducerea Dynamic Arrays, a fost adăugată și formula Unique. În acest articol vei vedea cum poți folosi formula Unique pentru a extrage valorile unice dintr-o coloană, rând sau chiar din mai multe coloane. Definiția și sintaxa UNIQUE Formula Unique extrage o listă de valori unice dintr-un interval de date. Funcționează cu orice tip de date: text, numere, date, ore etc. Aceasta este disponibilă în Office365 și Excel 2021. Formula este clasificată în categoria celor de tipul Dynamic Arrays. Rezultatul este o matrice dinamică care se revarsă automat în celulele învecinate vertical sau orizontal. UNIQUE(array, [by_col], [exactly_once]) Unique are doar trei argumente și doar primul este obligatoriu. Array –reprezintă intervalul de celule din care se vor returna valorile unice. [by_col] – este un argument de tip logic care indică modul de comparare a datelor: true – pentru compararea datelor între coloane. false sau dacă este omis – pentru compararea datelor între rânduri. [exactly_once] – reprezintă o valoare logică care definește ce valori sunt considerate unice: true – folosit pentru a extrage valorile care se regăsesc o singură dată în intervalul de date selectat. false sau dacă este omis – folosit pentru a extrage toate valorile unice din intervalul de date selectat. Formula Unique nu face distincție între majuscule și minuscule. Unique va trata „PIX”, „Pix” și „pix” ca același text. Cum se utilizează Unique? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcția Unique. Să presupunem că ai următoarea bază de date cu vânzările din anul 2021 pe produse și pe locații: Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Exemplul 1: Extragem valorile unice de pe o coloană . Este necesar să extragi numele unice ale produselor din coloana Produs. Mai jos este formula care face acest lucru: =UNIQUE(DataSet[Produs]) Formula de mai sus folosește doar primul argument, array, deoarece valorile implicite ale celorlalte două argumente funcționează perfect în acest exemplu – se compară rândurile unul cu celălalt și se returnează toate numele diferite din interval. Când apeși Enter, Excel va afișa primul nume găsit în celula G7, revărsând celelalte nume în celulele de mai jos. Ca rezultat, vei obține toate valorile unice într-o coloană: Pentru că baza de date din acest exemplu este un Table atunci când este adăugată o nouă înregistrare în tabel Unique se actualizează automat pentru a include valoarea suplimentară în Spill Range. Exemplul 2: Extragem valorile unice din mai multe coloane . Folosirea formulei Unique nu este limitată la o singură coloană. Acum dorești să extragi atât numele produselor cât și locațiilor magazinelor în care s-au vândut acestea. Mai jos este formula care face acest lucru: =UNIQUE(DataSet2[[Produs]:[Magazin]]) Aceasta include coloanele Produs și Magazin din tabel, astfel că rezultatul formulei include valorile unice din ambele coloane. Exemplul 3: Extragem valorile unice din mai multe coloane și le concatenăm într-o singură celulă . O altă modalitate în care putem folosi extragerea valorilor unice din mai multe coloane este prin extragerea lor într-o singură coloană. Mai exact fiecare rezultat unic va fi completat într-o celulă. Pentru acest exemplu folosim o bază de date care are încă două coloane: Nume Vânzător și Prenume Vânzător. Și dorești să extragi într-o singură coloană numele si prenumele vânzătorilor. Pentru această situație vei folosi următoarea formulă: =UNIQUE(DataSet3[Nume Vanzator]&” “&DataSet3[Prenume Vanzator]) Exemplul 4: Extragem valorile care se regăsesc o singură dată într-o coloană . În acest exemplu folosim un tabel care conține situația stocurilor produselor pe furnizori. Dorești să extragi produsele care se regăsesc o singură dată pe coloana Produs. =UNIQUE(DataSet4[Produs],,TRUE) Am folosit TRUE pentru argumentul [exactly_once], prin urmare Unique va returna rezultatele care apar o singură dată în coloana Produs. Capsator, Pix albastru și Banda adeziva maro apar o dată în celulele din coloana Produs. Pix rosu și Creion Mecanic apar de două ori și aceste produse sunt excluse din rezultat. Exemplul 5: Extragem valorile unice de pe un rând . De această dată ai la dispoziție un tabel cu vânzările din anumite zile ale fiecărui magazin. Însă zilele reprezintă coloane din tabel. Dorești să extragi lunile aferente zilelor din tabel. Mai jos este formula care face acest lucru: =UNIQUE(MONTH(E6:N6),TRUE()) Pentru a extrage lunile aferente zilelor din tabel am folosit formula month. Am folosit această formulă direct in Unique, la argumentul array. De asemenea, am folosit TRUE pentru argumentul [by_col] pentru a se face compararea pe coloane. Formula UNIQUE nu funcționează Dacă folosești formula Unique și aceasta afișează o eroare, cel mai probabil este una dintre următoarele: #NAME? Apare atunci când utilizați o formulă Unique într-o versiune Excel în care această funcție nu este acceptată. Dacă folosești o versiune Excel care conține formula Unique atunci eroarea indică faptul că numele formulei este scris greșit. #SPILL! Apare atunci când nu există suficient spațiu pentru adăugarea rezultatelor sub formulă. Dacă selectezi celula cu eroare #SPILL!, Excel va afișa spill range-ul unde încearcă să returneze rezultatul formule și poți vedea care sunt celule care nu sunt complet goale. #CALC! Unique returnează această eroare dacă nu poate genera un rezultat. De exemplu, #CALC! se afișează dacă utilizezi TRUE pentru argumentul [exactly_once] pe un interval cu valori care se repetă {2,2,3,3,3,4,4,4}. Concluzie După cum ai văzut, introducerea formulei Unique a făcut ca extragerea valorilor unice în Excel să fie incredibil de ușoară. Te invit să găsești noi moduri în care poți folosi Unique împreună cu alte formule și funcții din Excel! Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate Read More
Tips & Tricks – Calculează rapid ponderea din total Quick Analysis
Programul Excel ne ajută să organizăm și să gestionăm datele cu care lucrăm. Pentru a ne ajuta, Microsoft a introdus funcția Quick Analysis. Știind cum să utilizezi Quick Analysis îți poate economisi timp și te poate ajuta să creezi grafice și tabele atractive și ușor de citit. În plus, instrumentul sugerează și metode de vizualizare care se potrivesc cel mai bine cu datele tale. În acest articol, vom folosi Quick Analysis pentru a calcula rapid ponderea din totalul pentru fiecare rând dintr-un tabel. Instrumentul Quick Analysis este disponibilă începând cu Excel 2013, Office365 și Excel for the web. Tips&Tricks Tips & Tricks – Modificarea setărilor implicite ale PivotTable [Provocari Excel] Cum putem aplica Conditional Formatting – Data Bars? 6 pasi pentru formatarea mai buna a graficelor Introducere in Conditional Formatting – repost Calculul ponderii din total (% din total) Pentru exemplul de azi vom folosi un tabel în care avem centralizate vânzările dintr-o luna la nivel de produs. Pe baza acestor date dorim să calculăm ponderea din totalul vânzărilor pentru vânzările fiecărui produs. Ponderea din total se poate calcula folosind un pivot table sau o formulă prin care se împarte fiecare valoare la total. Însă mult mai rapid este folosind Quick Analysis. În video următor poți vedea cum se realizează acest lucru: După cum ai văzut pictograma pentru a accesa instrumentul Quick Analysis, apare automat ori de câte ori selectezi mai multe celule care au date completate. Deci nu vei găsi Quick Analysis în tab-urile din bara de meniu. Este Quick Analysis customizabil? În acest moment, din păcate, nu putem modifica opțiunile din cadrul Quick Analysis. Dar chiar dacă nu putem realiza acest lucru acum, este posibil ca, pe baza feedback-ului primit de la utilizatori, pe viitor cei de la Microsoft să adăuge mai multe opțiuni utile în cadrul Quick Analysis. Instrumentul Quick Analysis nu apare în fișierele tale Excel? În cazul în care folosești o versiunea Excel care are inclus instrumentul Quick Analysis și acesta nu apare când selectezi date, cel mai probabil acesta este dezactivat. Nu îți face griji, Quick Analysis poate fi activat foarte ușor: În bara de meniu selectează tab-ul File. În colțul din stânga jos alege Options. În fereastra asigurăte că tab-ul General este selectat. Bifeaza opțiunea Show Quick Analysis options on selection. Pentru a salva configurarea apasă butonul Ok. Concluzie Quick Analysis este un instrument excelent care te ajută să fi mai eficient, economisind timp prețios. Îți recomand să testezi cât mai multe din opțiunile existente în Quick Analysis! Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de Quick Analysis.