Selecteaza celulele vizibile in Excel

Daca ai vrut la un moment dat sa copiezi cateva randuri dintr-un tabel filtrat, probabil ca ai observat ca Excel-ul e generos si atunci cand dai paste, iti da si randurile de care nu ai nevoie.

Selecteaza celule vizibile Excel

Ce, stai, cum?

Daca voi copia tabelul de mai jos, voi constata ca atunci cand dau paste, Excel-ul imi va da toate cele 15 randuri din tabel si nu randul 2 si 15 cum as vrea eu.

Copiaza celule vizibile excel

Daca te intrebi ce informatii contine tabelul de mai sus, raspunsul meu este: “Da”. ­čÖé

Selecteaza celulele vizibile in Excel

Bun, acum ca am inteles care e “buba” hai sa vedem cum putem selecta doar celulele vizibile, vezi video-ul de mai jos.

Daca vrei sa ma ajuti sa imi creasca ego-ul vezi mai multe video-uri de acest fel, urmareste-ma si pe YouTube.

Radu, eu nu vreau sa dau play, doarme copilul si vocea ta cam il sperie

As rezuma acest video intr-un singur cuvant din 2 litere: F5. Spor!

Macro-uri in Excel: Introducere

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

Macro

Ce o fi oare un Macro?

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

Video

Trebuie doar sa ii dai play. ­čÖé

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

Un exemplu

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

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

Macro Enabled Workbook

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

Dar unde ai ascuns-o?

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

Macro

It’s showtime

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

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

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

Macro

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

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

Download

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

Tu ce zici?

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

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. ­čÖé

Cum faci un Excel sa mearga mai repede

Daca lucrezi in Excel-uri cu multe linii si coloane dar si multe functii de cautare printre ele, cel mai probabil stii ce inseamna sa astepti ca sa se calculeze formulele. Vei vedea in acest articol ca o schimbare de detaliu poate face diferenta dintre calculul intr-un minut, respectiv 0,3 secunde.

Cum faci un Excel sa mearga mai repede web

Problema mea

Recent am pus pe site un model de urmarire a concediilor, pe care, apropo, il poti descarca gratuit. Nu intru in detalii aici, ideea generala a fisierului era ca sa genereze un calendar automat bazat pe ceea ce scrii intr-un tabel simplu.

In acest fel aveai si o lista, dar si o reprezentare grafica care te ajuta sa vezi daca oamenii tai si-au pus cu totii concediile in aceeasi saptamana.

Singura problema era ca mergea foooarrrtttee inceeeet. Motivul principal, formula de mai jos:

=COUNTIFS(Concedii!$A:$A; Calendar!$A5;Concedii!$B:$B; “<=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)); Concedii!$C:$C;”>=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)))

Nu conteaza neaparat ce calcula “chestia” de mai sus, ideea era ca aceasta formula multiplicata pe suficient de multe celule a pus modelul cu “botul pe labe” facand-ul sa mearga extrem de incet – aveam nevoie de aproape 1 minut ca sa il pot deschide.

Radu, dar fisierul meu arata diferit …

Indiferent de cum arata fisierul tau, cred ca sunt sanse foarte mari ca sa il poti sa ruleze mai repede, folosind abordarea mea, simpla de altfel.

Si atunci, revenim la intrebarea din titlul articolului:

Cum faci un Excel sa mearga mai repede?

Vezi raspunsul meu in acest video.

Urmareste-ma pe YouTube.

Pe scurt

Am transformat formula initiala:

=COUNTIFS(Concedii!$A:$A; Calendar!$A5;Concedii!$B:$B; “<=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)); Concedii!$C:$C;”>=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)))

in

=COUNTIFS(Concedii!$A$1:$A$1999; Calendar!$A5; Concedii!$B$1:$B$1999; “<=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)); Concedii!$C$1:$C$1999; “>=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)))

Diferenta: de la mai mult de un minut, calcularea se face acum in mai putin de o secunda.

De parcurs

Daca ai curiozitatea sa vezi mai multe despre functiile pe care le-am folosit mai sus, iti recomand sa parcurgi urmatoarele articole.

Organigrama in Excel

Am primit recent urmatoarea intrebare:

Ma ajuti, te rog, cand ai timp cu un articol (pe Excelninja.ro, bineinteles) despre cum sa alcatuiesti o Organigrama in Excel? ­čÖé┬áMa descurc sa dau insert in Excel la Hierarchy din Smart Art, dar intampin dificultati la partea de formatare si editare a textului in rubrici sau in dreptul pozelor, in cazul in care vreau sa pun si poze.

organigrama in Excel

Mersi de intrebare, hai sa vedem mai multe despre cum poti crea organigrame in Excel fara sa iti iasa peri albi.

De ce in Excel

Ceea ce o sa va arat eu foloseste facilitatea de Smart Art din pachetul Office. Aceasta facilitate o gasim si in Word si PowerPoint. Avantajul pe care il avem atunci cand folosim Excel este ca nu suntem limitati de spatiul unei pagini (Word) sau a unui slide (PowerPoint). E adevarat totusi ca lucrurile se complica putin atunci cand vrem sa printam, asta e, nu putem fi cu toate.

Smart Art

Daca tot va spuneam despre Smart Art, gasiti aceasta facilitate in tab-ul insert.

Organigrama in Excel 1

Avem acolo chiar mai multe tipuri de organigrame, cu / fara poza, cerculete / bastonase, etc.

Organigrama in Excel 2

Exemplul nostru

Presupunem ca suntem o firma care vinde pantofi si avem in jur de 30 de angajati. Vrem sa facem o organigrama care sa contina urmatoarele informatii:

  • Nume
  • Pozitie
  • Poza

2 niveluri ierarhice

O organigrama cu 2 niveluri ierarhice nu cred ca pune probleme multor persoane. Excel o genereaza automat si noi trebuie doar sa completam spatiile libere.

Organigrama in Excel 3

Adaugare persoane noi

Putem adauga persoane noi in mai multe moduri. Varianta cea mai simpla din punctul meu de vedere e sa adaugam folosind text pane, adica chestia in care putem scrie pozitionata in partea stanga.

Organigrama in Excel 4Putem privi organigrama ca si o lista simpla, vedem ca fiindca pe Ion l-am adaugat sub Maricica, el va aparea ca si subordonat. E adevarat ca imaginea de mai sus nu arata foarte bine, stai linistit ca ajung si acolo.

Modificari

Am inteles dar cum fac in cazul in care vreau sa promovez sau sa retrogradez pe cineva. Nici o problema, fac asta din meniul contextual. Pe langa mutari ierarhice, pot sa schimb si ordinea pe orizontala folosind butoanele de move up / down.

Organigrama in Excel 5Layout

Folosind optiunea de layout putem alege cum sa fie reprezentare persoanele subordonate unui anumit individ. In imaginea de mai jos puteti vedea destul de bine la ce ma refer.

Organigrama in Excel 6Recomandari

  • Atunci cand creem organigrama, folosim text pane din partea stanga si nu ne punem sa copy paste fiecare bloc in parte si apoi sa mutam si linia
  • Atunci cand editam organigrama folosim promote si demote pentru miscari ierarhice si move up / down pentru miscari pe orizontala
  • Atunci cand vorbim de layout, in functie de numarul de persoane subordonate unui individ,┬ásunt mai potivite anumite layout-uri, nu exista o solutie universala
  • Cand vine vorba de formatare si editare a textului, eu prefer┬áori sa las setarile implicite, ori sa aplic formatarea la toata organigrama

Download

Aici gasesti exemplul pe care am lucrat si eu: organigrama in Excel

Intrebari

Pentru orice fel de intrebari, te astept in sectiunea de comentarii.

Model urmarire concedii in Excel

Daca ai avea nevoie de un model in Excel pentru urmarirea concediilor in compania ta, cum ai proceda? Intrebarea asta o aveam in minte si eu saptamanile trecute, motiv pentru care am repornit uzina de Excel-uri de la Oradea pentru a crea un atfel de model, hai sa vedem ce a iesit.

model urmarire zile concediu excel web

Demo

Fara alte introduceri, te invit sa urmaresti in acest scurt video ce a iesit din incercarea mea de a realiza un model de urmarire a concediilor in Excel.

Te provoc sa imi spui in zona de comentarii de cate ori am spus concediu in video. ­čÖé

Download

Modelul e gratuit, ca sa il descarci foloseste butonul de mai jos.

Viteza

Voiam de ceva vreme sa pun fisierul aici pe site, am dat insa de o problema din cauza formulei de mai jos: dupa ce schimbam ceva, avea nevoie de 30 de secunde pentru a recalcula formulele. Am gasit insa o solutie despre care o sa iti spun mai multe in saptamanile care vin.

Poate ai si tu fisiere in care dupa ce dai ENTER te duci sa bei o cafea pana termina recalcularea.

Formula cheie

In mare fisierul e destul de simplu. Partea mai complexa tine de faptul ca Excel-ul coloreaza automat pe un calendar zilele libere pe care le scrii in tabel.

=COUNTIFS(Concedii!$A$1:$A$1999; Calendar!$A5; Concedii!$B$1:$B$1999; “<=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)); Concedii!$C$1:$C$1999; “>=”&DATE(YEAR(Calendar!B$3); MONTH(Calendar!B$3); DAY(Calendar!B$3)))

An dinamic

Am uitat sa spun asta in video, o scriu aici. Anul este generat in mod automat in functie de coloana pe care suntem si de ceea ce scrie in B2.

model urmarire zile concediu excel 2

Ca sa schimb anul trebuie doar sa scriu altceva in B1.

Dar Radu, nu era mai simplu pur si simplu sa scrii acolo o data din care sa scoti anul?

Ba da, insa varianta mea imi arata in mod automat care sunt zile de weekend si care nu, un lucru util atunci cand vorbim despre planificarea concediilor.

Modelul tau

Poate ca dupa ce ai vazut ce am facut eu, prima reactie a fost:

Bai Radu, eu modele din astea faceam in clasa a 3-a, sa vezi acum ce racheta de planificare a concediilor am construit.

Daca vrei sa ne arati si noua performanta “distileriei tale de Excel-uri” te provoc sa imi trimiti fisierul prin email ca sa il pun aici. Hai, pune-te si cauta fisierul, astept. ­čÖé

Alte modele

Am tot “cioplit” modele in Excel pentru diverse scopuri, de la program de facturare, la gestiune stocuri, la model de pontaj si acum, chiar si model de urmarire a concediilor.

Spune-mi in zona de comentarii daca ar trebui sa construiesc si alte unelte.

Medie Ponderata in Excel

Radu, cum facem o medie ponderata in Excel?

Globul meu de plastic cristal de acasa imi spune ca macar o persoana dintre cei care ma urmaresc prin email, feisbuc sau iutub si-a pus vreodata intrebarea aceasta. Hai sa dam un raspuns.

Medie Ponderata Excel Web

Medie ponderata

Stii la ce ma refer nu? Ai de exemplu o lista de note si vrei sa calculezi media generala stiind ca fiecare nota are o pondere diferita. Un exemplu in imaginea de mai jos.

Medie ponderata excel 1

Da, am dat matematicii cea mai mare pondere. Efectul a 4 ani de liceu cimitir al tineretii mele in care am mancat matematica pe paine, sub paine, in ceai, in cafea, etc. ­čÖé

Video

Sii fiindca mi-am cumparat un nou program de captare ecran, era musai sa fac acest articol in format video. Daca nu iti place vocea mea, pot sa inteleg, vezi mai jos in forma scrisa.

Te astept si pe YouTube, am auzit ca primesc o punga de sticks-uri daca ajung la 10.000 de abonati. ­čÖé

Ponderi exprimate procentual

Daca ai noroc, s-ar putea ca sa ai deja exprimate ponderile in format procentual, ca si in imaginea de mai jos:

Medie ponderata excel 1

Atunci e destul de simplu, aplici urmatoarea formula:

=SUMPRODUCT(B2:B8;C2:C8)

Alfel de ponderi

Sunt cazuri in care nu gasesti vreun procent nici daca-l cauti cu lanterna. Poate ca datele tale arata mai degraba asa:

Media ponderata excel 2

Daca ai folosi aceeasi formula ca si mai sus, ai obtine un pret mediu de achizitie cu mult mai mare decat toate preturile.

In situatia asta folosim urmatoarea formula:

=SUMPRODUCT(B2:B6;C2:C6)/SUM(C2:C6)

Radu, dar ce ce?

Alta intrebare!? 

Ca sa putem folosi prima formula ar fi trebuit sa transformam numarul de bucati in ponderea din total. Fiindca eu sunt mai lenes de fel, am facut transformarea asta direct in formula impartind SUM(C2:C6).

Radu, vad ca ai folosit SUMPRODUCT, ce face functia asta mai exact?

Pe scurt, ceea ce ii spune si numele, aduna produsele dintre 2 coloane. Daca vrei o explicatie mai ampla vezi acest articol.

Download

A, sa nu uit, aici gasesti fisierul pe care am lucrat eu.

Introducere in Power BI

Daca job-ul tau are o legatura cat de mica cu zona de raportare, musai trebuie sa parcurgi acest articol. Vreau sa iti fac cunostinta cu Power BI o unealta foarte tare de analiza a datelor in Excel.

Introducere in PowerBI Desktop - web

Un exemplu

Vezi in imaginea de mai jos un “dashboard” pe care l-am construit in mai putin de 5 minute. Nu am mai folosit Power BI pana acum, dar totul pare foarte intuitiv.

Power BI desktop 1

Hai sa creem un raport, pas cu pas

As putea sa scriu multe despre acest subiect sau sa dau copy paste de pe site-ul Microsoft. Am profitat insa de faptul ca nu mai sunt racit (cel putin pentru moment) si am facut un video in care poti sa vezi mai multe.

Nu mi-am propus ca sa fac un ghid complet, ci sa te ajut sa incepi. Stii ca mie imi plac video-urile scurte.

Urmareste-ma pe YouTube

Download

De aici poti descarca Power BI, nu ai nevoie de crack-uri sau de carti de credit ­čÖé e gratuit.

Si daca tot suntem la capitolul download, aici poti gasi raportul facut de mine.

Mai departe

Probabil ca nu va fi ultimul articol pe acest subiect, am ramas placut impresionat de aceasta unealta. Astept si varianta pentru Mac OS.

Ai o intrebare legata de acest subiect? Te astept in zona de comentarii, ajuta-ma sa fac o lista pentru urmatoarele articole ce vor aparea aici.

Obtine zecimal din numar in Excel

zecimale din numar 2

Radu imi poti arata cum sa obtin valoarea zecimala dintr-un numar “cu virgula”?

Da, vezi mai jos:

zecimale din numar 1

Daca nu ai vazut formula in imaginea de mai sus, o mai pun o data aici:

=ABS(A2-TRUNC(A2))

Bun Radu, dar nu am inteles ce ai facut tu acolo …

Ok, hai sa luam formula pas cu pas.

O singura functie

=ABS(A2-TRUNC(A2))

Am folosit combinatia asta ca sa ma dau rotund pentru ca nu am gasit o singura functie care sa imi dea valoarea de dupa zecimala pentru un anumit numar. Poate stii tu vreuna, daca da, scrie-mi in zona de comentarii.

Evident ca ar fi fost mult mai simplu de scris o functie de genul:

=HOCUSPOCUSZECIMALE(A2)

Functia TRUNC

Si atunci, daca nu am gasit o functie care sa imi dea valoarea zecimala, m-am gandit sa folosesc o alta functie, care imi da valoarea intreaga si sa fac o diferenta intre ele.

Pentru a obtine partea intreaga, am folosit functia TRUNC.

TRUNC(1,234)=1

Iar partea zecimala o obtin asa:

1,234-TRUNC(1,234)=0,234

Functia ABS

Radu, pentru ce ai mai adaugat si functia ABS?

In lista mea de numere sunt si numere negative. Nu as fi vrut sa am rezultate de genul -0,234 si de asta am folosit functia ABS care calculeaza modulul fiecarui numar.

Stii cum se zice, e bine sa ramai pozitiv. ­čÖé

Incearca si tu

Vezi aici fisierul pe care am lucrat si eu.

Ai o intrebare?

Ai o intrebare legata de Excel, te invit sa o pui pe forum. Cine stie poate scriu chiar si un articol pornind de la intrebarea ta.

Grafic Sunburst Excel

Cum mai sunt doar cateva zile si vara se cam apropie de final, m-am gandit ca e cam ultima sansa acum sa vorbesc despre un tip de grafic in Excel care are legatura cu soarele, graficul de tip Sunburst. Hai sa vedem la ce ne poate ajuta.

Grafic Sunburst Excel

Grafic ierarhic

Ca si treemap, Sunburst este un grafic de tip ierarhic. Asta inseamna ca iti permite sa prezinti intr-un mod relativ inteligibil date aranjate pe mai multe nivele.

Altfel spus, e un fel de pie chart pe steroizi.

Si daca tot am ajuns la placinte, am luat ca si studiu de caz o placintarie “high-tech” care vrea sa isi analizeze vanzarile.

Video

Nu as vrea sa iti stimulez imaginatia mai mult decat e nevoie, vezi in video-ul de mai jos cum se creaza, cum trebuie structurate datele si cum ar trebui sa deslusesti acest tip de grafic.

Urmareste-ma pe YouTube

Un mic detaliu ­čÖé

Am uitat sa spun asta in video, era sa uit si cand scriu textul, graficul de tip Sunburst a fost introdus in Office 2016, nu il gasesti in versiuni mai vechi.

Alte tipuri de grafice

Daca vrei sa devii mai “ninja” cand vine vorba de grafice in Excel, gasesti aici articole care te vor tine ocupat(a) cateva saptamani de acum incolo. ­čÖé

Apropo, ai un grafic mai complex pe care ai vrea sa il fac aici pe site? spune-mi mai multe in sectiunea de comentarii sau pe forum.