Compara tabele folosind CONSOLIDATE

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

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

compara tabele folosind consolidate

Dar CONSOLIDATE nu era facut pentru altceva?

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

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

Download

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

Metode alternative

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

Identifica duplicate in Excel

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

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

Duplicate Excel web

Radu, nu ai mai scris despre asta?

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

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

Identifica duplicate in Excel

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

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

Formatare conditionata

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

Functia COUNTIF

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

A treia abordare

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

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

Aprobari in Excel (fisier inclus)

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

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

Aprobari excel web

Dar ce ar trebui mai exact sa faca acest fisier?

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

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

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

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

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

Asta a iesit

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

Download

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

Vezi, nici nu te-am obligat sa te abonezi la lista mea de email. 🙂

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

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

Functia QUERY in Google Sheets

Vreau in acest articol sa iti arat o “chestie” foarte tare in Google Sheets, mai exact functia QUERY. Am dat peste functia asta saptamana trecuta cand faceam putin research “random” despre functii in Excel. E bine sa ai din cand in cand timpi morti, inveti lucruri noi.

Query in Google Sheets

Google Sheets

Daca nu ai mai auzit pana acum despre Google Sheets, cel mai probabil te-ai intors recent din padurea amazoniana unde ai fost sa te regasesti in ultimii 15 ani. Pe scurt Google Sheets e un fel de Excel doar ca e dezvoltat de Google, e gratuit si e online, il rulezi in browser.

De mult nu am mai scris despre Google Sheets …

Functia QUERY in Google Sheets

Revenind la subiect, daca urmaresti ce mai arunc eu aici pe site, nu e prima data cand auzi cuvantul QUERY. Ti-am spus in urma cu mai multe articole despre POWER QUERY, ei bine functia QUERY face cam acelasi lucru dar in Google Sheets.

E drept ca face acelasi lucru intr-un mod diferit. Ok, ma opresc din scris aici, ca oricum tu ai dat deja play la video. 🙂

Dar de unde stiu ce sa scriu acolo? Nu prea stiu sa scriu SELECT-uri …

Nici eu nu sunt expert in SELECT-uri, desi ar trebui sa fiu, am invatat despre asta in facultate. 🙂

Poti gasi mai jos un ghid legat sintaxa, ar fi fain sa si mearga functia asta, daca tot ti-ai batut capul cu ea, nu?

https://developers.google.com/chart/interactive/docs/querylanguage

Cum de nu am auzit despre functia asta pana acum?

Exact asta ma intrebam si eu zilele trecute, e genul de functie prea tare ca sa treaca neobservata.

Uite inca o functie foarte utila despre care (probabil) nu ai auzit, IMPORTRANGE.

Functia TEXTJOIN in Excel (alternativa la CONCATENATE)

Daca ai fost nevoit(a) sa “lipesti” continutul a 2 sau mai multe celule in Excel, cel mai probabil ca ai dat peste functia CONCATENATE. In acest articol vreau sa iti arat o alternativa mai buna, mai ales daca vrei sa lipesti mai mult de 3 celule si vrei sa pui si virgula intre ele, hai sa vorbim despre TEXTJOIN.

Functia textjoin excel

Si pe asta o stiu de la un curs

Invat destul de multe de la cei care participa la cursurile mele. Nu am aflat despre TEXTJOIN de pe site-ul Microsoft sau de la oracolul din Delfi, ci de la un participant la cursurile mele.

Da, nu le stiu pe toate, daca ai dat peste o chestie interesanta in Excel, spune-mi si mie. 😉

Video

Hai sa vedem despre ce e vorba, vezi video-ul de mai jos.

Update: din pacate se pare ca functia asta e disponibila doar pe Office 365, am aflat asta abia dupa ce am publicat articolul.

Parte dintr-un curs online

Da, si sesiunea asta face parte din MS Excel Extended, cursul meu online.

Nu, nu m-am pus sa incarc tot cursul aici pe site, e o sesiune noua creata chiar azi. De cand am lansat cursul cred ca am mai adaugat minim 30 de minute de continut nou, am in plan si alte subiecte, mai e de lucru aici.

Daca vrei sa cumperi si tu acest curs, il poti lua fie de pe site-ul meu, fie de pe Udemy.com, cum e mai convenabil pentru tine.

Cumpara de pe Teachable

Cumpara de pe Udemy

Functii text

Vrei sa vezi mai multe despre functii care lucreaza cu textul in Excel? Iti recomand acest articol.

Formatare conditionata pe un rand intreg

Am primit recent o intrebare interesanta prin email:

“Radu cum as putea sa fac sa se coloreze cu verde un rand intreg in cazul in care pe o coloana din tabel am o anumita valoare?”.

Formatare conditionata rand intreg

Ce? cum? eu nu inteleg …

Daca te-am bagat complet in ceata inseamna ca nu ai auzit de formatare conditionata in Excel. Da-mi voie sa iti descriu functionalitatea asta pe scurt, putem formata / colora automat celule care indeplinesc anumite reguli, cateva exemple mai jos:

  • Mai mic de 100
  • Mai mare de 1000
  • Contine 0
  • Dubluri
  • + multe alte optiuni

Da, asta inseamna ca nu mai e nevoie sa colorezi manual cu roz toate valorile intre 75 si 100 din tabelul tau. Setezi o regula Excel-ul face asta automat.

Si care e smecheria?

E foarte simplu ca sa colorez o celula in functie de valoarea ei insasi. E mai complex sa colorez un rand intreg intr-un tabel bazat pe valoarea de pe o anumita coloana.

Asta iti prezint in video-ul de mai jos. Da-i play, apasa, apasa, apasa, apasa, apasa 🙂

Daca simti ca acest video ti-a fost de folos sa stii ca nu ma supar daca imi dai un “like” pe YouTube. Incerc sa il depasesc pe Florin Salam la trending videos. 🙂

Despre formatare conditionata

Daca vrei sa afli mai multe despre formatare conditionata, mai ales lucrurile de baza, iti recomand acest articol:

Download

Aici gasesti fisierul pe care am lucrat eu.

Spor la colorat randuri, automat. (parca suna ca si o reclama la detergent). 🙂

Model gestiune stocuri in Excel

Am facut la un moment dat un model simplu pentru gestiunea stocurilor in Excel. Am fost foarte mandru de rezultat, avea totusi o mica problema, nu functiona. 🙂

Asa ca, hai sa facem un model de gestiune a stocurilor care sa nu ne mai dea batai de cap.

Model stocuri excel

Radu, dar ce baiuri avea?

Cateodata am tendinta ca sa ma complic inutil si sa folosesc lucruri destul de complexe din Excel pentru scopuri mult mai simple. De data asta am comis-o cu optiunea de Data Model.

Data model

Acel model era construit folosind facilitatea oferita de Office 2013 sau Power Pivot de a analiza mai multe tabele in acelasi pivot table.

Excel Data Model

Ce sa mai zic, am tras cu tunul dupa muste, pentru un model simplu de gestiune a stocurilor nu era nevoie de asa ceva, ce sa mai zic ca dadea si erori.

Fisierul pe care o sa il gasesti in acest articol e mult mai simplu, si merge pe orice varianta de Excel, cred ca functioneaza si daca ii dai upload pe Google Sheets.

Voiam sa ma dau si eu mare ca poti analiza mai multe tabele in acelasi pivot, ce sa ii faci.

O varianta mai simpla

De cele mai multe ori varianta mai simpla e cea mai buna. Ca sa construim un model simplu de stocuri avem nevoie de urmatoarele:

  • Un tabel in care sa scriem intrarile si iesirile

Stocuri excel - input

  • Un pivot care sa ne ajute sa stim cate produse avem (teoretic) in stoc

Stocuri excel - analiza

  • Un tabel in care sa definim lista de produse pe care o avem

Stocuri excel - admin

Nu doar in 2013

Partea buna e ca acest fisier functioneaza pe orice fel de versiune de office, nu doar 2013, 2016 sau Office 365.

Download

Vezi daca functioneaza butonul de mai jos.

Modelul tau

Dupa ce am promovat putin acest articol am inceput sa primesc si email-uri inapoi. Unul din email-uri a fost de la Cecilia care mi-a aratat un model de gestiune stocuri la care a lucrat recent. Fiindca mi s-a parut interesant si parea chiar mai destept decat ce am facut eu, am zis ca il pun aici.

Poti descarca modelul Ceciliei aici.

Email-urile pe care le primesc de multe ori dupa ce public un articol vin sa imi demonstreze ca voi cei care vizitati acest site faceti chestii foarte faine in Excel. Ma bucur ca imi permiteti sa le impartasesc si cu altii.

Centralizare tabele Excel #PowerQuery

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

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

Centralizare tabele excel

Radu, tu nu ai auzit de COPY – PASTE?

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

Centralizare tabele Excel

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

Parte dintr-un curs online

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

Power Query

Radu, dar ce e aia Power Query?

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

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

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

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

Download

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

Scurtaturi in Excel: tot ce trebuie sa stii

Nu stiu tu cum esti, dar mie imi place sa ma misc repede cand “cioplesc” un Excel, probabil ca si tie. In acest articol vreau sa centralizez cele mai relevante scurtaturi in Excel pe care le avem la dispozitie.

scurtaturi excel

Praf de “ninja” in Excel

Sigur ai sau ai avut un coleg / colega care era biblioteca de scurtaturi in Excel. Probabil ca atingea mouse-ul de maxim 2 ori pe zi (si atunci ca sa il stearga de praf) in rest facea cam totul de la tastatura.

Invata si tu macar 30% din scurtaturile de mai jos si ii vei face concurenta. 🙂

[La final o sa te rog sa ma ajuti sa completez lista, am pus si textul asta aici in cazul in care te gandesti sa nu dai scroll mai jos.]

Un video

Hai sa incepem cu un video, vei vedea cum vei putea sa faci lucrurile de mai jos fara sa te atingi de mouse.

  1. Creaza un nou workbook
  2. Adauga data de azi
  3. Mergi la ultimul rand
  4. Selecteaza coloana
  5. Sterge coloana
  6. Ascunde coloana
  7. Adauga un autosum
  8. Adauga un rand nou intr-o celula
  9. Snap to grid
  10. Adauga comentariu

10 care incep cu CTRL

  1. CTRL + SPACE: selecteaza intreaga coloana pe care ne aflam
  2. CTRL + SHIFT + {: selecteaza toate celulele la care s-a facut referire in celula selectata
  3. CTRL + ‘: copiaza formula din celula de mai sus pentru a o putea edita
  4. CTRL + 9: ascunde randul; CTRL + 0: ascunde coloana
  5. CTRL + F1: ascunde / arata panglica (en: Ribbon) suna cam ciudat in romana
  6. CTRL + ;: insereaza data de azi
  7. CTRL + SHIFT + :: insereaza ora
  8. CTRL + –: sterge celula/randul/coloana
  9. CTRL + SHIFT + u: mareste / restrange ‘formula bar’ (nu stiu cum sa o traduc in romana)
  10. CTRL + F4: inchide workbook-ul in care lucram (Excel-ul ramane deschis)

7 care incep cu ALT

  1. ALT+SHIFT+DREAPTA/STANGA: grupeaza / degrupeaza randurile / coloanele selectate
  2. ALT+t: set top border (in engleza e mai clar), ALT+b (set bottom border), ALT+l (set left border), ALT+r (va las pe voi sa ghiciti aici)
  3. ALT+h,ar: alinere la dreapta; ALT+h,ac: aliniere pe centru; ALT+h,al: aliniere la stanga
  4. ALT+=: insereaza AUTOSUM
  5. ALT+’: arata stilul celulelor selectate
  6. ALT+F11: deschide editorul Visual Basic, asta pentru cei mai avansati
  7. ALT+F4: pe asta sigur o stiti, recomand din cand in cand

Ce mi-a scapat?

La inceputul acestui articol vorbeam despre Excel Shortcuts Ninja, poate vorbeam chiar despre tine. Spune-ne si noua ce ne-a scapat in zona de comentarii.

Autofill in Excel: tot ce trebuie sa stii

Radu, cum pot sa numerotez repede un tabel, fara sa scriu vreo 200 de numere de la tastatura?

La intrebarea de mai sus majoritatea “ninjalailor” in Excel din jurul tau ti-ar spune probabil sa “tragi in jos” si apoi sa selectezi fill. Ce inseamna asta totusi?

Daca vrei sa intelegi mai bine, vezi video-ul de mai jos:

Curs online

Da, video-ul de mai sus e parte dintr-un curs online, MS Excel Extended. Cu aproape 6 ore de continut, as spune ca e echivalentul a cel putin 3 zile de curs in format clasic. Formatul online are multe avantaje, il poti urmari oricand, de cate ori vrei, de acasa, tren sau de pe plaja de la birou.

Daca tot suntem aici

Daca folosesti un Excel mai nou de 2013, vei vedea ca mai ai o optiune cu “fill” la final, Flash Fill mai exact. E o optiune foarte interesanta prin care vedem ca Excel-ul ne citeste gandurile (cateodata).

Pentru formule

Am vazut ca formulele sunt destul de imune la autofill, orice am selecta, cam tot aia e. Cand lucram cu formule, ar trebui mai degraba sa ne concentram pe F4 si $, detalii in articolul de mai jos.

Intrebari / sugestii?

Ai o intrebare sau o sugestie pentru un articol? Te astept in zona de comentarii de mai jos.