Am văzut în articolele anterioare o parte din instrumentele și caracteristicile interesante pe care Excel le conține. Un astfel de instrument foarte util este și Quick Access Toolbar. Aceasta ne permite să adăugăm propriile comenzi utilizate frecvent într-o locație convenabilă chiar deasupra sau sub bara de meniu principală. Comenzile pe care le puteți adăuga aici sunt complet personalizabile în funcție de preferințele fiecărui utilizator. În acest articol veți afla cum vă puteți personaliza bara Quick Access, astfel încât să puteți accesa comenzile favorite fără a fi nevoie să le găsiți în bara de meniu. Tips&Tricks Tips&Tricks – Cum să creezi liste personalizate în Excel Tips & Tricks – Cum să creezi rapoarte multiple dintr-un singur Pivot Table? Cum utilizăm Excel Fill Series? Tips & Tricks – Modificarea setărilor implicite ale PivotTable Tips & Tricks – Top 5 scurtaturi pe care le folosesc cel mai des Ce este Quick Accesss Toolbar? Quick Access Toolbar este o mică bară de instrumente care apare în partea de sus a ferestrei și conține un set de scurtături rapide către comenzile și opțiunile pe care le utilizezi cel mai des. Aceste comenzi sunt accesibile din aproape orice parte a aplicației. Acest instrumente include un meniu derulant cu un set predefinit de comenzi implicite care pot fi afișate sau ascunse. De asemenea include și opțiunea de a adăuga propriile comenzi. Nu există o limită maximă pentru numărul de comenzi din Quick Access Toolbar, însă în funcție de numărul de comenzi și dimensiunea ecranului este posibil ca o parte din acestea să nu fie vizibile. Unde se găsește Quick Accesss Toolbar în Excel? Bara Quick Access este localizată în mod implicit în colțul din stânga sus al ferestrei Excel, deasupra barei de meniu. Însă aceasta poate fi mutată sub bara de meniu pentru a fi mai aproape de zona foii de lucru. Cum să personalizezi bara Quick Access în Excel ? Quick Access Toolbar are are în mod implicit doar trei butoane: Save, Undo și Redo. Instrumentul poate fi personalizat cu următoarele elemente: Crearea propriilor comenzi Modificarea ordinii în care sunt afișate comenzile Adăugarea scurtăturilor către comenzi Macro VBA disponibile în fișier. Însă există și câteva aspecte care nu pot fi personalizate: În Quick Access Toolbar pot fi adăugate doar comenzi câte opțiune sau funcționalități din Excel. Astfel nu se pot adăuga valori specifice, de ex un anumit număr sau text. Nu se pot adăuga etichete text la comenzile adăugate. Acestea vor fi afișate doar ca pictograme. Butoanele din Quick Access Toolbar nu pot fi redimensionate. Singura modalitate de a schimba dimensiunea butoanelor este prin schimbarea rezoluției ecranului. Bara Quick Access nu poate fi afișată pe mai multe linii în același timp. Dacă sunt setate mai multe comenzi decât spațiul disponibil, o parte din acestea vor fi ascunse sub un buton de tip Drop–down. Pentru a personaliza după propriile preferințe bara Quick Access, urmează pașii de mai jos: În zona Quick Access Toolbar apasă butonul drop-down cu săgeata în jos. Din meniul care se deschide alege More Commands Se va deschide fereastra Customize the Quick Access Toolbar care conține două zone principale. Cea în care poți căuta și alege comenzile dorite și cea în care poți vizualiza și modifica comenzile deja adăugate în Quick Access Toolbar. Folosește lista de la Choose commands from drop-down list pentru a alege categoria unde se afla comanda pe care dorești să o adaugi. În mod implicit fereastra se deschide cu opțiunea Popular commands selectată. Dacă nu știi unde se afla comanda dorită alege All comands. În zona de sub la Choose commandsfrom drop-down list selectează comanda dorită. Pentru exemplul din acest articol am selectat Number Format. Apasă butonul Add dintre cele doua zone. Poți continua să adaugi și alte comenzi în Quick Access Toolbar. La final, pentru salvarea comenzilor adăugate, apasă butonul Ok. Acum poți găsi această comandă în bara Quick Access. Te invit să urmărești aplicarea tuturor pașilor de mai sus în următorul video: Concluzie Acum, poți obține acces rapid și ușor la comenzile cele mai des utilizate în Excel prin personalizărea Quick Access Toolbar. Adăugă, elimină și rearanjează comenzile așa cum dorești. Acest lucru te va ajuta să eficientizezi timpul folosit pentru căutarea comenzilor preferate în bara de meniu. Folosește zona de comentarii pentru a împărtăși comenzile favorite pe care le-ai adăugat la Quick Acces Toolbar în Excel.
Author: Daniela
Cum creăm listele dependente folosind formule de tip Dynamic Arrays
Unul din primele articole scrise pe InvatamExcel.ro a fost despre crearea listelor dependente folosind funcția Data Validation. Listele dependente conțin cel puțin două liste dropdown conectate între ele. Cu ajutorul primei liste condiționăm opțiunile care vor fi afișate în cea de-a două. Deși este ușor să creezi o listă simplă folosind funcția Data Validation, realizarea listelor dependente multi-nivel a fost întotdeauna dificilă. Vestea bună este că acest proces a devenit mult mai ușor odată cu introducerea funcțiilor Dynamic Arrays. Disponibilitate Modalitatea de a crea liste dependente prezentată în acest articol funcționează numai în Excel 365 și Excel 2021. Pași pentru a crea liste dependente folosind formule de tip Dynamic Arrays Exemplul de mai jos demonstrează abordarea generală pentru crearea unei liste dependente în Excel prin utilizarea noilor formule Dynamic Arrays. Să presupunem că avem un tabel cu departamentele, funcțiile și angajații dintr-o companie. Scopul este de a realiza într-un alt tabel câte un dropdown list pentru coloanele Departament și Funcție iar Angajatul să fie completat automat în funcție de cele două selecții. De asemenea, lista afișată pentru funcții trebuie să fie dependentă de selecția departamentului. Pentru a crea listele dependente este necesar să realizăm următorii pași: 1. Crearea tabelului cu datele de selecție pentru lista principală Pentru început, vom extrage valorile unice ale departamentelor din prima coloană a tabelului sursă. Într-un tabel nou folosim formula Unique în forma ei cea mai simplă – în primul argument array furnizăm coloana Departament din tabelul sursă și omitem argumentele opționale rămase, deoarece valorile lor implicite funcționează bine pentru noi: =UNIQUE(Tabel_sursa[Departament]) Când apăsăm Enter, Excel va afișa primul nume găsit în prima celulă, revărsând celelalte nume în celulele de mai jos. Ca rezultat, vei obține toate valorile unice din coloana Departament. 2. Crearea dropdown-ului pentru lista principală Următorul pas constă în folosirea funcției Data validation pentru configurarea listei dropdown pentru coloana principală de selecție: Selectăm celula care dorim să conțină lista dropdown pentru Departament, în exemplul nostru celula H8. În bara de meniu selectăm tab-ul Data și apasăm butonul Data validation. În fereastra care se deschide pentru câmpul Allow alegem List. Iar pentru câmpul Source introducem referința către intervalul de valori rezultat la pasul anterior. Este indicat să completăm sau selectăm prima celulă a formulei Unique pentru coloana Departament și notăm semnul # la final: =$L$8# . Pentru salvarea listei apăsăm Ok. 3. Obținerea elementelor pentru lista dependentă Acum avem nevoie să filtrăm valorile din coloana Funcție pe baza selecției realizate în primul dropdown. Pentru acest lucru folosim Filter, o altă formulă Dynamic Arrays. Pentru coloana Funcție vom folosi următoarea formulă: =FILTER(Tabel_sursa[Functie],Tabel_sursa[Departament]=H8) La primul argument, array, selectăm coloana cu funcțiile din tabelul sursă. Iar criteriul de filtrare, argumentul include, selectăm coloana Departament din tabelul sursă care trebuie să fie egală cu celula în care se selectează departamentul dorit din dropdown. Ne putem asigura că formula funcționează corect selectând o anumită valoare din dropdown-ul principal și verificăm că rezultatele afișate de filter sunt corecte: 4. Crearea celui de-al doilea dropdown Pentru a crea cel de-al doilea drodown list vom urma pașii de la punctul 2. Dar de data aceasta, în câmpul Souce vom completa prima celulă a formulei Filter urmat de semnul #: =$M$8#. Notă: Nu se poate utiliza funcția FILTER direct în câmpul Source din Data Validation List deoarece acest câmp necesită un interval de celule, iar valorile returnate de Filter sunt sub forma de array. În acest moment am finalizat configurarea celor două liste dropdown și opțiunile listei secundare depind de selecția realizată în cea primară. Pentru acest exemplu datele sursă pentru listele dependente sunt salvate într-un Table. Astfel că pe măsură ce se adaugă noi elemente sau se șterg din cele existente, informațiile din cele două liste dropdown se vor actualiza automat. La final pentru câmpul Angajat vom aplica formula Xlookup cu mai multe criterii de căutare. Mai jos este formula care face acest lucru: =XLOOKUP(H8&I8,Tabel_sursa[Departament]&Tabel_sursa[Functie],Tabel_sursa[Angajat]) Te invit să urmărești aplicarea tuturor pașilor de mai sus în următorul video: https://invatamexcel.ro/wp-content/uploads/2023/06/Demo-Dependent-Data-Validation-List-Dynamic-Arrays.mp4 Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos: Download Concluzie Capacitatea de a crea o lista dropdown care să permită utilizatorilor să selecteze o valoare dintr-o listă predefinită este una din cele mai utile caracteristici ale funcției Data Validation. Acestea sunt foarte utile pentru a ghida sau a restricționa un utilizator în ceea ce privește datele pe care acesta le poate completa în anumite celule. În articolul de azi, am văzut cât de ușor se pot crea listele dropdown dependente folosind formule de tip Dynamic Arrays. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de folosirea funcției Data Validation List.
Funcția Sequence – generarea automată a listelor de numere secvențiale în Excel
În articolul de azi vei afla cum se folosește formula Sequence și vom trece prin câteva exemple practice.
Sequence este una dintre noile formule pe care Microsoft le-a lansat o dată cu introducerea dynamic arrays. Puterea funcției vine atunci când este combinată cu alte funcții.
Această funcție folosește modificările aduse motorului de calcul Excel, care permite folosirea unei singure formule iar rezultatul se va revărsa automat în celulele învecinate vertical sau orizontal.
Tips&Tricks – Cum să utilizezi Xlookup cu mai multe criterii logice de căutare
Într-un articol anterior am văzut cum se poate folosi formula Xlookup pentru a extrage informații în funcție de mai multe criterii exacte care trebuie respectate simultan. Însă există situații în care criteriile care trebuie respectate nu sunt exacte. De exemplu, folosind tabelul de mai jos dorim să extragem primul magazin în care s-au înregistrat vânzări mai mari de 3000 de lei pentru produsul Capsator. Tips&Tricks Tips&Tricks – Cum să utilizezi Xlookup cu mai multe criterii de căutare 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 – formula generică cu mai multe criterii logice de căutare Xlookup ne ajută să construim mai multe matrice bazate pe criterii de căutare folosind argumentul lookup_array. Astfel, se pot aplica mai multe teste logice. Pentru exemplul nostru, formula generală este următoarea: XLOOKUP(1,( lookup_array_1=lookup_value_1)*( lookup_array_2> lookup_value_2), return_array, [if_not_found], [match_mode], [search_mode]) Este important de reținut că se pot adăuga mai multe condiții prin completarea la cel de-al doilea argument a mai multor seturi de lookup_array si lookup_value. Acestea se vor scrie între paranteze și cu semnul * intre seturile de criterii. De asemenea, la modalitatea de comparare poate fi folosit oricare operator logic (=, >, >=, <, <=). Rezultatul oferit de Xlookup va fi cel care respectă toate condițiile notate la cel de-al doilea argument. Exemplu: XLOOKUP cu mai multe criterii logice de căutare Acum vom aplica Xlookup pentru exemplul de mai sus și vom extrage primul magazin în care s-au înregistrat vânzări mai mari de 3000 de lei pentru produsul Capsator. Mai jos este formula care face acest lucru: =XLOOKUP(1,(DataSet[Produs]=”Capsator”)*(DataSet[[Valoare vanzari ]]>3000),DataSet[Magazin]) Evaluarea criteriilor În cadrul lookup_array am adăugat cele două criterii din exemplu: (DataSet[Produs]=”Capsator”)*(DataSet[[Valoare vanzari ]]>3000) Și, pentru că avem mai multe criterii logice, Excel evaluează fiecare set de criterii și returnează două serii care conțin valori TRUE/ FALSE: Rezultatul testului logic Produs = Capsator este -> {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE} Rezultatul testului logic Valoare vânzări >3000 este -> {FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE} Prin înmulțirea seturilor de criterii ne asigurăm că aceste condiții sunt respectate simultan: {FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}*{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE} Rezultatul înmulțirii este o singură matrice de 1 și 0, în care 1 înseamnă că există True pe acelasi rând în seturile de criterii și 0 înseamnă că nu există True pe acelasi rând în seturile de criterii. {0;0;0;0;0;0;0;1;0;0;0;0} Extragere valorii care îndeplinește criteriile logice După ce Excel realizeaza aceste calcule, formula noastră arată astfel: =XLOOKUP(1,{0;0;0;0;0;0;0;1;0;0;0;0},DataSet[Magazin]) Acum se întelege ușor că am folosit 1 la lookup_value pentru ca acesta reprezintă faptul că cele două condiții setate au fost îndeplinite simultan. În continuare Xlookup caută și găsește că se potrivește „1” în poziția a 8-a din lookup_array și returnează cea de-a opta valoare corespunzătoare din return_array, în exemplul nostru coloana Magazin a tabelului. Te invit să urmărești aplicarea formulei Xlookup cu mai multe criterii logice de căutare în următorul video: https://invatamexcel.ro/wp-content/uploads/2023/05/Demo-Xlookup_criterii-logice.mp4 Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos: Download Concluzie Până acum am parcurs două modalități în care Xlookup se poate folosi pentru căutările cu mai multe condiții. Trebuie să recunosc că varianta cu concatenarea mai multor criterii poate fi mai ușor de utilizat, însa aceasta cu criteriile logice este mult mai flexibilă si mai puternică. În cele din urmă, poți decide ce variantă să utilizezi în funcție de tipul criteriilor care trebuie îndeplinite. 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.
Tips&Tricks – Cum să creezi liste personalizate în Excel
Excel conține mai multe funcționalități utile care te pot ajuta să economisești timp și să fi mai eficient în activitatea de zi cu zi. O astfel de funcționalitate valoroasă și mai puțin cunoscută este Custom Lists. Aceasta te ajută atunci când ai un set de date pe care le utilizezi frecvent, de exemplu lista cu numele lunilor dintr-un an. Cel mai probabil pentru a le folosi într-un fișier nou le copiezi din altul. Însă poți stoca orice listă în Custom List și, apoi, poți utiliza AutoFill pentru a le adăuga într-un sheet ori de câte ori dorești. Tips&Tricks Tips&Tricks – Cum să calculezi numărul de zile lucrătoare dintre două date? Tips & Tricks – Cum să creezi rapoarte multiple dintr-un singur Pivot Table? Tips&Tricks – Cum să utilizezi Xlookup cu mai multe criterii de căutare Tips & Tricks – Modificarea setărilor implicite ale PivotTable Utilizarea unei liste predefinite Excel are integrat câteva liste personalizate care conțin numele zilelor dintr-o săptămâna și numele lunilor în engleză. De exemplu, dacă introduci “Mon” într-o celulă și “Tue” în alta, poți utiliza Fill Handle pentru a completa automat zilele rămase. Mai jos găsești listele predefinite care sunt deja încorporate în Excel. Crearea listelor personalizate completare manuală Dar să presupunem că cel mai adesea folosești numele zilelor sau a lunilor în limba română. Poți crea liste personalizate pentru acestea iar data viitoare nu va mai trebui să cauți prin fișierele vechi. Pur și simplu scrie primele două elemente din listă și trage de Fill Handle până când sunt completate toate înregistrările de care ai nevoie. Pentru a crea o listă personalizată, urmează pașii de mai jos: Deschide un fișier Excel, poate să fie și unul gol. În bara de meniu selectează tab-ul File. Pentru Excel 2007 apasă butonul Microsoft Office. În colțul din stânga jos alege Options. În fereastra care se deschide selectează Advanced și apoi parcurge opțiunile disponibile până ajungi la secțiunea General. Pentru Excel 2007 în fereastra care se deschide selectează Popular, apoi parcurge opțiunile disponibile până ajungi la secțiunea Top options for working with Excel. Aici apasă butonul Edit Custom Lists. Se va deschide o fereastra în care poți vedea listele deja definite. Pentru a crea o listă nouă apasă pe rândul New List. Completează fiecare înregistrare din listă in zona List Entry. Pentru salvarea listei apasă Add și, apoi, Ok în fereastra Options. Acum, poți începe să folosești propria listă personalizată. Selectează orice celulă și scrie o intrare din listă înainte de a o trage în jos până când sunt completate toate înregistrările de care ai nevoie. Pentru a șterge o înregistrare creată în Custom Lists este necesar să parcurgi pașii până la deschiderea ferestrei Edit Custom Lists . Aici selectează lista pe care dorești să o ștergi și apasă butonul Delete. Nu se pot șterge listele pre-definite. Crearea listelor personalizate prin import Atunci când ai o listă mai lungă sau când ai înregistrările deja completate într-un fișier Excel, poți crea lista personalizată prin import. Deschide fișierul care conține lista dorită sau completează înregistrările din listă în orice sheet Excel. Parcurge pașii de mai sus până la deschiderea ferestrei Edit Custom Lists Aici selectează caseta liberă de sub zona List Entry. Selectează zona de celule care conține înregistrările din lista dorită. Pentru salvarea listei apasă Import și, apoi, Ok în fereastra Options. Este important să știi că o listă personalizată poate fi completată automat folsind Flash Fill atât vertical cât și orizontal. De asemenea, pentru că funcționalitatea Custom Lists face parte din opțiunile Excel–ului în ansamblu și nu este specifică unui singur fișier, lista va fi disponibilă în orice fișier Excel. Concluzie Funcționalitatea Custom Lists este una fantastică care te ajuta să îți eficientizezi timpul. Folosind listele personalizate poți reutiliza foarte ușor informațile cu care lucrezi zilnic. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de utilizarea listelor personalizate în Excel.
Funcția Sort – sortarea automată a datelor în Excel utilizând o formulă
Când lucrăm în Excel, uneori avem nevoie să sortăm datele. Acest lucru ne ajută în organizarea și analiza datelor, identificarea tiparelor și evidențierea informațiilor importante. Excel include o funcționalitate puternică de sortare în tab-ul Data zona Sort&Filter. Cu toate acestea, o dată cu introducerea Dynamic Arrays, a fost introdusă și posibilitatea de sortare a datelor folosind formule. Avantajul utilizării acestei metode este că rezultatele sunt actualizate automat atunci când datele sursă se schimbă. În acest articol, vei afla cum să sortezi datele din una sau mai multe coloane utilizând funcția Sort. Definiția și sintaxa SORT Formula Sort permite să sortezi conținutul unui set de date în ordine ascendentă sau descendentă. Pentru acest lucru poți folosi una sau mai multe coloane. Formula Sort este disponibilă în Office365, Excel Online ș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. SORT(array, [sort_index], [sort_order], [by_col]) Sort are patru argumente, doar primul fiind obligatoriu: Array – reprezintă intervalul de celule pe care dorești să îl sortezi. [sort_index] – este un argument opțional de tip numeric care corespunde coloanei sau rândului folosit pentru sortarea datelor. Atunci când acesta este omis sortarea se va realiza după prima coloană sau rând. [sort_order] – este un argument opțional prin care se definește ordinea în care se realizează sortarea: 1 sau dacă este omis – pentru sortare ascendentă, adică de la cel mai mic la cel mai mare -1 – pentru sortare descendentă, adică de la cel mai mare la cel mai miccaractere. [by_col] – este un argument opțional de tip logic care indică direcția de sortare: False sau dacă este omis – pentru sortarea datelor vertical, pe rând. Aceasta fiind opțiunea de sortare cel mai des utilizată. True – pentru sortarea pe orizontală, pe coloane. Cum se utilizează SORT? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcția Sort. Să presupunem că ai următoarea o bază de date cu vânzările dintr-o companie pe produse și pe locații: Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Exemplul 1: Sortarea datelor după prima coloană în ordine ascendentă . Este necesar să sortezi tabelul de date în ordine ascendentă după coloana Produs (care este prima din table) . Mai jos este formula care face acest lucru: = SORT(DataSet) Formula de mai sus folosește doar primul argument, array, deoarece valorile implicite ale celorlalte argumente funcționează perfect în acest exemplu. Când apeși Enter, Excel va afișa toate datele din tabelul selectat sortate după coloana Produs. Pentru baza de date din acest exemplu este un Table atunci când este adăugată o nouă înregistrare în tabel Sort se actualizează automat pentru a include valoarea suplimentară în Spill Range. Exemplul 2: Sortarea datelor după o coloană în ordine descendentă . De data aceasta dorești să sortezi tabelul de date în ordine descendentă după valoarea vânzărilor. Mai jos este formula care face acest lucru: =SORT(DataSet2,3,-1) În acest exemplu pentru al doilea argument am notat numărul 3 pentru că valoarea vânzărilor este a treia coloană din tabel. Iar pentru sortarea descendentă am notat -1 pentru argumentul [sort_order]. Exemplul 3: Sortarea datelor în funcție de mai multe coloane în ordine diferită (multi-level sort) . Atunci când lucrăm cu modele complexe de date, este posibil să fie necesară o sortare pe mai multe niveluri. Acest lucru este posibil prin folosirea opțiunii de sortare din tab-ul Data zona Sort&Filter și este posibilă inclusiv în formula Sort. Să presupunem că este necesar să sortezi tabelul de date după coloana Magazin în ordine ascendentă și după valoarea vânzărilor în ordine descendentă. Pentru această situație vei folosi următoarea formulă: =SORT(DataSet3,{2,3},{1,-1}) Pentru a realiza sortarea multi-level este necesar să notăm intre acolade la argumentul [sort_index] numerele ambelor coloane în funcție de care se va realiza sortarea. Pentru exemplul nostru am notat {2,3}. Iar pentru modalitatea de sortare, la argumentul [sort_order] am scris {1,-1}, deoarece coloana Magazin trebuie sortată în ordine ascendentă, iar valoarea vânzărilor în ordine descendentă. Exemplul 4: Combinarea formulelor Sort și Filter . Poate te întrebi dacă poți combina formula Sort cu Filter pentru situațiile în care dorești să sortezi doar datele care respectă anumite criterii. Da, se poate și chiar într-un mod foarte ușor. De data aceasta dorești să sortezi datele aferente vânzărilor din Online în ordine descendentă după valoarea vânzărilor. =SORT(FILTER(DataSet4,DataSet4[Magazin]=I7),3,-1) Vei observa că am folosit formula Filter pentru a specifica argumentului array din Sort doar rândurile vânzărilor din Online. Apoi am completat 3 pentru sortarea acestora după valoarea vânzărilor și -1 pentru sortarea descendentă. Exemplul 5: Sortarea orizontală a datelor . În mod implicit sortarea datelor folosind formula Sort se realizează pe verticală. Sunt puține situațiile în care datele sunt organizate orizontal. În aceste cazuri, pe coloane avem datele de tip înregistrare și pe coloane datele de tip etichetă. Cel mai probabil o să fie nevoie de sortarea datelor. De data aceasta sortarea va fi de la stânga la dreapta. Pentru acest exemplu vom folosi tabelul din poza de mai sus, în care avem valoare vânzărilor dintr-o perioadă din fiecare magazin. Vom folosi următoarea formulă pentru a sorta setul de date în ordine descendentă după valoarea vânzărilor: =SORT($E$7:$H$8,2,-1,TRUE) Pentru a sorta un tabel organizat în acest fel a fost necesar să completăm TRUE la ultimul argument ([by_col]) al formulei Sort. Concluzie În acest articol, am văzut că formula Sort este una puternică și ușor de folosit. Însă potențial acesteia crește exponențial atunci când o folosești î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 de Sort.
Cum utilizăm funcția Fill din Power Query?
Din ce în ce mai des este necesar să folosim date în Excel din alte sisteme informatice. Uneori, exporturile din aceste sisteme nu sunt optimizate corespunzător pentru a fi folosite în Excel. Este posibil ca setul de date să conțină o singură celulă cu valoare iar celulele de mai jos să fie goale până la următoarea valoare. O variantă de a rezolva situația de mai sus, ar fi să introducem manual informațiile lipsă în celulele goale. Însă, acest lucru poate fi consumator de timp și poate genera cu ușurință erori. Power Query are integrată funcția Fill care ne permite să completăm datele cu un singur click. Pași necesari pentru a folosi funția Fill din Power Query Funcția Fill se poate folosi în două moduri: Fill Down Utilizăm această caracteristică pentru a copia valoarea de sub celulele goale în toate celulele goale de deasupra acesteia. Fill Up Utilizăm această caracteristică pentru a copia valoarea de deasupra celulelor goale în toate celulele goale de mai jos. În continuare, vom trece prin pașii necesari pentru a aduce setul de date de mai sus într-un format potrivit pentru analiza datelor: Înainte de prelucrare După Fill Down #eael-img-accordion-112c9a1 .eael-image-accordion-hover:hover { flex: 3 1 0% !important; } #eael-img-accordion-112c9a1 .eael-image-accordion-hover:hover:hover .overlay-inner * { opacity: 1; visibility: visible; transform: none; transition: all .3s .3s; } /* widget: List */ #uc_list_elementor_3630e323{ display:grid; } #uc_list_elementor_3630e323 .ue-list-item { display:flex; } #uc_list_elementor_3630e323 .ue-list-item-image img { width:100%; } #uc_list_elementor_3630e323 .ue-list-item-index { flex-grow:0; flex-shrink:0; display:flex; align-items:center; justify-content:center; line-height:1em; } #uc_list_elementor_3630e323 .ue-list-item-index svg { height:1em; width:1em; } #uc_list_elementor_3630e323 .ue-list-item-gap { flex-grow:0; flex-shrink:0; } #uc_list_elementor_3630e323 .ue-list-item-content { flex-grow:1; } Selectează orice celulă din setul de date care urmează să fie prelucrat. Mergi apoi în tab-ul Data, și apoi din zona Get Data -> From Table/Range. Dacă baza de date nu este de tip Table se va deschide o fereastră pentru transformarea într-un table. În aceasta fereastră apasă Ok pentru a trece la următorul pas. În acest moment se va deschide Editorul Power Query cu datele selectate mai devreme încărcate în acesta. Selectează coloana care conține celulele goale. În exemplul nostru selectăm coloana Magazin. În bara de sus selectează tab-ul Transform și apoi opțiunea Fill -> Fill Down. Ca rezultat, valorile de mai sus au fost copiate în toate celulele goale de mai jos pentru întreaga coloană selectată. 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: https://invatamexcel.ro/wp-content/uploads/2023/03/Power-Query-Fill-Down_demo.mp4 Un avantaj semnificativ al utilizării Power Query este faptul că poți actualiza rapid datele rezultate dacă datele inițiale se modifică. De exemplu, dacă actualizezi setul de date cu un export nou din sistemul informatic, care conține mai multe înregistrări decât cele inițiale, nu trebuie să aplici pașii de mai sus. Pentru actualizare, selectează orice celulă din tabelul rezultat după transformarea în Power Query și apasă click dreapta și selectează opțiunea Refresh. Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos: Download Concluzie Power Query este un instrument magic pentru automatizarea activităților repetitive, ușor de utilizat care produce rezultate foarte rapid. Pentru mine, funcția Fill este de mare ajutor și am integrat-o într-un flux de mai multe acțiuni de prelucrare a datelor, care mă ajută să transform în fiecare lună exporturile primite în formatul de care am nevoie. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de prelucrarea datelor în Excel folosind Power Query.
Tips&Tricks – Cum să calculezi numărul de zile lucrătoare dintre două date?
Ai avut vreodată nevoie să calculezi numărul de zile lucrătoare dintre două date? Cel mai adesea acest calcul este necesar pentru activități care au legătura cu zona de HR, ca de exemplu pentru a calcula beneficiile acumulate de angajați într-o lună. Ai putea face diferența dintre cele două date și să scazi manual numărul de zile de weekend. Dar perioada dintre două date poate include și zile libere legal și atunci calculul manual poate genera foarte ușor erori si nu este eficient. Iar cea mai complexă situație este atunci când zilele lucrătoare nu respectă zilele tradiționale de lucru (luni – vineri). Pentru toate situațiile de mai sus se poate folosi o singură formulă -> NETWORKDAYS.INTL. Tips&Tricks Cum se calculează numărul de luni dintre două date în Excel? Cum utilizam functia DATEDIF in Excel? Tips & Tricks – Transformarea unui text de tipul “20150115” in data caledaristica [Provocari Excel] Cum sa formatam data in functie de o conditie? Definiție și sintaxa NETWORKDAYS.INTL Networkdays.intl este o formula de tip Date&Time care calculează numărul de zile lucrătoare dintre două date specificate de utilizator și care permite setarea zilelor de weekend și a celor libere legal. Aceasta este disponibilă începând cu Office 2010. NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) Networkdays.intl are patru argumente, primele două fiind obligatorii: start_date și end_date – reprezintă datele pentru care urmează să fie calculată diferența. Pentru ambele argumente se vor introduce valori de tip dată. [weekend] – este un argument opțional prin care utilizatorul specifică zilele săptămânii care sunt zile de weekend și nu sunt incluse în numărul de zile lucrătoare întregi între start_date și end_date. Acest lucru se realizează prin alegerea unui cod din cele de mai jos sau prin configurarea de către utilizator. Setarea implicită este 1, care consideră zilele de sâmbătă și duminică ca fiind zilele de weekend. [holidays] – este un argument opțional care indică datele care urmează să fie excluse din calendarul zilelor lucrătoare. Calcularea numărului de zile lucrătoare prin excluderea zilelelor de weekend Pentru început, vom vedea cum se folosește Networkdays.intl, în cea mai simplă formă a sa, pentru a calcula numărul de zile lucrătoare dintr-o două date atunci când zilele de weekend sunt sâmbătă și duminică. Formula de mai sus folosește doar primele două argumente. Nu a fost nevoie să folosim argumentul [weekend] pentru că in acest exemplu zilele de weekend coincid cu cele din setarea implicită. Calcularea numărului de zile lucrătoare prin excluderea zilelelor de weekend și a celor libere legal De această dată vom calcula numărul de zile lucrătoare dintr-o două date atunci când zilele de weekend sunt sâmbătă și duminică și când există zilele libere legal care trebuie excluse din calcul. Începem prin a scrie formula din exemplul anterior. La cel de-al treilea argument [weekends] scriem codul 1, iar la ultimul argument [holidays] selectăm celulele în care am completat zilele libere legal din anul 2023 (de la celula J8 la J20). =NETWORKDAYS.INTL(D8,E8,1,$J$8:$J$20) Calcularea numărului de zile lucrătoare atunci când ai un model personalizat pentru zilele lucrătoare dintr-o săptămână Formula Networkday.intl are integrată, prin codurile de la argumentul [weekends], posibilitatea de a selecta ca zi nelucrătoare orice zi a săptămânii sau orice combinație de două zile consecutive. Dar există situații când zilele lucrătoare dintr-o săptămână au un program mai neconvențional. În acest exemplu vom calcula numărul de zile lucrătoare dintr-o două date atunci când zile lucrătoare sunt: Luni, Marți, Joi, Vineri și Duminică. Începem prin a scrie formula din primul exemplu. Iar la argumentul [weekends] ne vom folosi de următorul truc: vom crea o secvență 7 cifre folosind 0 și 1, unde zerouri reprezintă zilele lucrătoare, și unu reprezintă zile nelucrătoare. Va rezulta următoarea formulă: =NETWORKDAYS.INTL(D8,E8,”0010010″) Dacă dorești să arunci o privire mai atentă acestui exemplu poți descărca fișierul folosind butonul de mai jos: Download 3 lucruri de reținut despre Networkingdays.Intl Dacă start_date este mai mare decât end_date, funcția returnează o valoare negativă. Networkdays.intl include în calcul atât data de început, cât și data de final. Astfel dacă se introduce aceeași dată pentru cea de început și cea de final, aceasta va returna 1 sau 0 (atunci când data furnizată este aferentă unei zile de weekend sau unei zile libere legal). Dacă o dată furnizată în orice argument este una invalidă sau conține caractere nevalide, funcția returnează eroarea #VALUE!. Concluzie Funcția Networkday.intl este o formulă flexibilă cu o mulțime de aplicații. Iar dacă lucrezi des cu valori de tip dată, cu siguranță este una pe care o vei folosi în mod constant. Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile legate de formulele de tip Date&Time.
Funcția Textsplit – Cum poți să împarți textul dintr-o celulă după unul sau mai mulți delimitatori
Pot exista diverse situații în care trebuie să împarți pe coloane textul din una sau mai multe celule. Acest lucru apare cel mai des atunci când importăm în Excel din alte sisteme. Excel include deja o serie de instrumente pentru a îndeplini această sarcina, cum ar fi Text to Columns și Fill Flash. Problema acestor instrumente este că rezultatul lor nu este unul dinamic. Adică rezultatele nu se actualizează atunci când datele sursă se modifică. De curând, Microsoft a introdus și o funcție specială pentru aceast lucru, Textsplit, care poate separa un șir în mai multe celule pe coloane sau/și rânduri pe baza parametrilor specificați de utilizator. În acest articol, vom întelege cum funcționează formula Textsplit prin câteva exemple în care putem vedea abilitățile sale în acțiune. Definiția și sintaxa TEXTSPLIT Formula Textsplit împarte un șir de text după un anumit delimitator pe coloane sau/și rânduri. Rezultatul este o matrice dinamică care se revarsă automat în mai multe celule. Textsplit funcționează ca instrumentul Text-to-Columns, dar sub formă de formulă. De asemenea, putem spune că Textsplit este formula inversă a Textjoin. Aceasta este disponibilă în Office365 și Excel for the Web. TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with]) Textsplit are șase argumente, primele două fiind obligatorii: Text – reprezintă textul pe care dorești să il împarți. Acesta poate fi furnizat ca un text scris direct în formulă sau ca o referință la o celulă. Col_delimiter – folosind acest argument specifici formulei care este delimitatorul care va fi folosit pentru împărțirea textului pe coloane. Poate fi un șir format din unul sau mai multe caractere. [Row_delimiter] – este un argument opțional și reprezintă delimitatorul care va fi folosit pentru împărțirea textului pe rânduri. Poate fi un șir format din unul sau mai multe caractere. [Ignore_empty] – este un argument de tip logic care indică dacă valorile goale vor fi ignorate sau nu: true – pentru ignorarea valorilor goale, adică formula nu va crea celule goale atunci când textul conține doi sau mai mulți delimitatori consecutivi. false – reprezintă setarea implicită și formula va crea celule goale atunci când textul conține doi sau mai mulți delimitatori consecutivi. [Match_mode] – este un argument opțional care indică diferența dintre majuscule și minuscule pentru delimitator: 0 – setarea implicită și căutarea va fi case-sensitive, adică se va ține cont de majuscule. 1 – pentru a efectua o potrivire care nu ține seama de majuscule și minuscule. [Pad_with] – este un argument opțional și reprezintă valoarea afișată de formula pentru valorile lipsă atunci când împărțirea unui text se face bidimensional, atât pe coloane cât și pe. Valoarea implicită este o eroare #N/A. Cum se utilizează TEXTSPLIT? (exemple) Următoarele exemple ilustrează modul în care poți utiliza funcția Textsplit. Pentru a descărca fișierul cu exemple folosește butonul de mai jos: Download Exemplul 1: Împărțirea unui text pe coloane după un delimitator . Pentru început, vom vedea cum se folosește Textsplit în cea mai simplă formă a sa pentru a împărți un șir de text după un anumit delimitator. Să presupunem că ai următoarea bază de date cu numele și adresele angajaților dintr-o companie: Mai jos este formula care face acest lucru: =TEXTSPLIT(D7,” “) Formula de mai sus folosește doar primele doua argumente. Pentru argumentul Text am ales celula care conține numele angajaților. Și pentru că numele si prenumele sunt separate prin spațiu, pentru Col_delimiter am scris ” “. Exemplul 2: Împărțirea unui text folosind un delimitator de tip subșir . În unele situații, valorile dintr-un text sursă sunt separate printr-o succesiune de caractere, o virgulă și un spațiu fiind un exemplu tipic. Pentru a gestiona acest scenariu, vom utiliza un subșir pentru delimitator. Folosind tabelul de la exemplul anterior este necesar să împarți adresa angajaților în mai multe coloane. Mai jos este formula care face acest lucru: =TEXTSPLIT(E7,”, “) Exemplul 3: Împărțirea unui text vertical pe rânduri . Pentru a împărți un text pe mai multe rânduri, vom nota delimitatorul în cel de-al treilea argument (row_delimiter) si vom omite cel de-al doilea argument (col_delimiter). De data aceasta avem un text care conține produsele vândute de un magazin într-o singură celulă. Pentru această situație vei folosi următoarea formulă: =TEXTSPLIT(D8,,”,”) Exemplul 4: Împărțirea unui text în funcție de mai mulți delimitatori . Există situații în care informațiile din textul sursă sunt separate de mai mulți delimitatori. Cu alte cuvinte nu există o consecvență în folosirea aceluiași delimitator. Mai jos poți vedea că textul din acest exemplu este delimitat atât de “,” cât și de “;”, cu și fără spațiu după. Pentru a împărți șirul vertical în rânduri după toate cele 4 variante ale delimitatorului, formula este următoarea: =TEXTSPLIT(D8,,{“, “,”; “,”,”,”;”}) Poți observa că atunci când introducem mai mulți delimitatori aceștia trebuie să fie separați cu o virgulă și că întreaga listă de delimitatori trebuie să fie inclusă într-un set de acolade. Exemplul 5: Împărțirea unui text ignorând valorile goale . De această dată ai la dispoziție un tabel cu numele unor persoane. Doar ca unele conțin Dl sau Dna. Dorești să extragi pentru fiecare persoană doar numele și prenumele în coloane diferite. Dacă aplicăm formula de primul exemplu vom obține următorul rezultat care nu este satisfăcator: Pentru a remedia acest lucru vom considera “Dl “ si “Dna “ ca delimitatori: =TEXTSPLIT(D7,{” “,”Dl “,”Dna “}) Se poate vedea ca rezultatul formulei este unul mai bun, dar încă nu este cel dorit pentru că avem spații în celule unde erau titlurile. Acum ne vom folosi de argumentul [ignore_empty] adăugând “TRUE” pentru a ignora valorile goale. =TEXTSPLIT(D7,{” “,”Dl “,”Dna “},,TRUE) Ca rezultat, toate valorile apar în coloanele corespunzătoare. Exemplul 6: Împărțirea unui text pe coloane și rânduri . De asemenea, folosind Textsplit poți împărții textul dintr-o singură celulă în mai multe rânduri și coloane foarte ușor. Presupunem că avem următorul text care se află într-o singură celulă pe care trebuie să le împărțim într-o coloană pentru nume, una pentru materie și una pentru notă. Pentru a separa numele, materia si nota în coloane separate vom folosi delimitatorul virgulă. Iar pentru a separa datele pe Read More
Tips & Tricks – Cum să creezi rapoarte multiple dintr-un singur Pivot Table?
De multe ori folosim Pivot Tables pentru a analiza și centraliza rapid informațiile din tabele mari de date. Funcția Pivot Table are integrate mai multe opțiuni utile care ne ajută să realizăm tot felul de rapoarte și diagrame. În articolul de azi vom vedea cum se poate folosi opțiunea Report Filter din Pivot Table pentru a genera mai multe rapoarte de tip pivot. Tips&Tricks Tips & Tricks – Modificarea setărilor implicite ale PivotTable Tips & Tricks – 3 metode prin care sa actualizezi toate Pivot Table-urile dintr-un fisier Cum sa adaugam un Calculated Field intr-un Pivot Table? Introducere in Pivot Table Utilizarea Report Filters Report Filters îți permite să controlezi ce informații sunt incluse într-un raport de tip Pivot Table. De exemplu, atunci când ai un tabel de date cu vânzările dintr-o companie, poți analiza datele pentru fiecare categorie de produs selectând una sau mai multe categorii (fiind permise selecții multiple). Acest filtru se crează prin drag and drop, plasând câmpul dorit ca filtru în Pivot Table în zona Report Filters. Pentru exemplul de azi vom folosi tot un tabel cu vânzările dintr-un an pe produse și magazine. Vom crea un Pivot Table care centralizează vânzările aferente fiecărui produs pe magazine. Vom pune câmpul Produs în zona Rows, Valoare vanzari în zona Values și câmpul Magazin în zona Report Filter. Astfel, folosind filtrul se pot afișa informațiile din raportul Pivot table pe magazinele selectate. Generarea mai multor rapoarte dintr-un singur Pivot Table Sunt situații în care realizarea unui Pivot Table cu posibilitate de filtrare după un anumit câmp să nu fie suficientă. Și, de fapt, să fie nevoie să generezi câte un raport similar cu cel inițial pentru fiecare înregistrare din câmpul folosit la Filter Reports. În exemplul nostru acest lucru inseamnă generarea unui raport pentru fiecare magazin. Și pentru că am folosit câmpul Magazin în zona Report Filter putem face acest lucru foarte ușor și rapid: Selectează orice celulă din Pivot creat inițial și în bara de sus alege tab-ul PivotTable Analyze. Aici apasă săgețica de lângă butonul Options și apoi alege Show Report Filter Pages. Se va deschide o fereastra în care alegi câmpul din zona Report Filter care va folosit pentru generarea rapoartelor. Pentru că în exemplul nostru avem un singur câmp adăugat în zona Report Filter fereastra va conține doar câmpul Magazin. Dupa ce ai ales câmpul dorit apasă OK. Gata, Excel a generat automat mai multe sheet-uri, câte unul pentru fiecare înregistrare a câmpului din zona Report Filter. Concluzie Atunci când generez rapoarte de tip Pivot table folosesc adesea zona Report Filters (câteodată chiar și cu 3 câmpuri deodată). Cu toate acestea, am aflat abia anul trecut de opțiunea Show Report Filter Pages care generează rapoarte multiple folosind unul din câmpurile de la Report Filter. Recunosc că nu sunt foarte multe situații în care o poți folosi, dar pentru că poate salva timp prețios este util să știi că există. Tu ai folosit până acum Report Filters? În ce situații ți se pare utilă această funcționalitate a Pivot Table-urilor? Folosește zona de comentarii pentru a împărtăși experiența ta sau alte sfaturi utile.