Template Balanta Personala - Personal Expense Tracker

Vreau sa incep acest post prin a-mi cere scuze ca nu am mai reusit sa scriu nimic in ultima luna. Daca pentru unii luna decembrie e mai libera, la mine a fost exact invers. Va promit ca in perioada urmatoare, o sa ma revansez pentru pauza de luna asta, mai ales ca am un nou laptop care sa ma ajute sa postez lucruri interesante.

Ca sa ma iertati mai usor, tot in acest post am sa va dau cadoul din partea mea de Craciun si noul an: un template balanta personala. Doresc sa va marturisesc ca noi, eu si cu sotul meu drag, folosim de doi ani un fisier in care tinem evidenta cheltuielilor si veniturilor.

Acum doi ani am inceput cu un fisier in google docs, facut de Bogdan si imbunatatit de mine :D. Trebuie sa va spun ca fisierul initial era tare rudimentar, iar toate cheltuielile erau la gramada, cu exceptia a catorva care erau lunare si fixe.

Anul acesta, prin mai, am zis ca trebuie sa facem ceva si am refacut complet balanta in excel. Ca sa putem sa vizualizam in orice clipa balanta din luna curenta, folosim si un sheet in google docs. Dar o sa vedem mai multe despre cum se foloseste mai tarziu.

Sa revenim la fisierul nostru. Templetul pe care vi-l ofer reprezinta varianta (zic eu) imbunatatita a fisierul pe care l-am facut in mai anul acesta.

Formula calcul program plata - Chandoo

Unul din blogurile de excel la care sunt abonata este http://chandoo.org/wp si aseara am primit newsletter cu noua postare de pe acest blog. In aceasta postare cititorii erau provocati sa creeze formula pentru calcularea programului de plata a unui reprezentant de vanzari. (Calculate Payment Schedule Homework)

Si cum era sa ratez asemenea provocare .... am downloadat fisierul pus la dispozitie de autor si m-am pus pe treaba. M-am chinuit cam 20 de minute pe urma am facut o pauza si l-am dat gata. Rezolvarea mea este una care nu foloseste functii prea complexe, un alt utilizator a postat o rezolvare chiar misto :D. Dar toate la timpul lor si s-o luam ca la scoala:


Datele problemei

Dupa cum am spus mai sus, provocarea este crearea formulei care calculeaza programul de plata pentru un reprezentant de vanzari. Dar cum era de asteptat sunt si conditii pentru a-si primi veniturile:

1. Trebuie sa castige cel putin 200 dolari inainte de a fi platit;
2. Trebuie sa existe o diferenta de 7 zile intre platile succesive.

Pentru aceasta problema am primit un tabel care contine urmatoarele coloane: Data (coloana B), Comisiul castigat in aceea zi (coloana C) si  Valoarea platii (coloana D). In rezolvarea problemei puteai introduce inca o coloana ajutatoare (lucru pe care l-am facut).

Sparklines - Excel 2010

Haideti sa lasam toate chestiile grele din VBA si sa facem ceva mai usor dar de care multi dintre noi nu au auzit.

Ce sunt Sparklines?

Pe langa multe alte modificari sau adaugiri, in Excel 2010 a fost introdus o noua functie pentru vizualizarea datelor numit ca "sparklines". Cu ajutorul sparkilines-urilor introducem mini grafice care sunt adaugate intr-o celula dintr-o sheet si sunt folosite pentru a afisa tendintele dintr-o serie de valori, sau pentru a evidentia valorile maxime si minime.

Pentru exemplul de azi am creat un tabel in care sunt centralizate vanzarile din anul 2011 pentru mai multe produse. La sfarsitul tabelului am adaugat coloana Evolutie in care pentru fiecare produs am creat cate un sparkline pentru a arata evolutia vanzarilor pe parcursul anului.

VBA Basic

Mi-a fost atrasa atentia ca nu am specificat in postul anterior si unde anume scriem codul VB. Vreau sa ma revansez si am ales ca in aceasta postare sa dau cateva detalii despre acest lucru.

Ce este un Macro?

Macrocomenzile reprezinta o serie de comenzi şi funcţii care pot fi apelate ori de câte ori este nevoie. Cu ajutorul lor putem sa realizam urmatoarele lucruri:

• automatizarea activitatilor - cu ajutorul macrocomenzilor se pot automatiza sarcinile care se realizeaza in mod repetat. Acest lucru va poate ajuta sa salvati timp. Un exemplu in acest sens ar fi formatarea repetata a unui sheet intr-un anumit mod. 
• Crearea propriilor funcţii şi comenzi rapide ca de exemplu: crearea unei combinatii de taste sau doar a unei taste pentru a schimba culoarea unei celule fara a mai fi nevoie sa folosesti mouse-ul.
• Crearea unui  fisier excel interactiv: Majoritatea persoanlor considera ca excelul este greu de folosit, cu ajutorul user form-urilor, a butoanelor si a altor functionalitati Macro se pot creea fisiere mult mai prietenoase
• Crearea propriilor aplicatii: La un nivel superior, exista posibilitatea de a va crea propriile aplicaţii în Excel pentru diverse scopuri.
  

VB Editor si cum ajungem la el

VB Editor reprezinta mediul care se utilizeaza pentru a creea, modifica si a gestiona macro-urile pentru programele Office. In continuare voi prezenta mai multe modalitati pentru a deschide Editor VB. 

O prima modalitate de a ajunge in VB Editor este din meniul pentru Macrocomenzi, care se afla in bara de meniu a excelului. Fata de Office 2003 si variantele anterioare, in Office 2007 si 2010 s-a creat meniu separat pentru Macro-uri. De accea va voi arata si cum deschidem VB Editor in ambele variante.

In Office 2007 si 2010 butonul care deschide editorul VB se gaseste in bara de meniu la categoria  DEVELOPER.

VBA - Filtrare Data Validation List

In aceasta vara am lucrat la un proiect in excel care mi-a testat capacitatile si datorita caruia am pornit pe calea programarii VBA. Dupa mai multe postari care au avut ca subiect diverse formule, cred ca acum este momentul potrivit pentru a va impartasi si cateva exemple cu VBA.

Am hotarat ca prima postare pe acest subiect sa fie una usoara si anume filtrarea listei create cu Data Validation. Trebuie sa va marturisesc ca eu folosesc Data Validation List in foarte multe fisiere, in general este foarta utila in cadrul formularelor care sunt folosite de catre alte persoane. Modalitatea de folosire a listei create cu Data Validation este de multe ori ingreunata de marimea mare a listei care dorim sa o afisam.

Pentru a rezolva aceasta problema putem sa reorganizam informatiile pe mai multe categorii si/sau subcategorii care sa ne ajuta sa realizam o filtrare initiala (Dependent Data Validation List). Dar nu in toate cazurile informatiile pot fi reorganizate in acest fel si pentru aceasta lucru eu am descoperit filtrarea listei cu ajutorul unui macro.

Pentru exemplul de azi am ales sa folosim o lista de clienti pe baza careia doresc sa fac o lista cu Data Validation care sa fie folosita intr-un formular (de exemplu intr-o solicitare de facturare). Pentru acest fisier avem nevoie de doua sheet-uri: Lista clienti si Formular.

Sheet-ul  Lista clienti  este un sheet ajutator in care avem lista de clienti pentru formular. Tot in acest sheet se va filtra lista de clienti pe baza a ceea ce este completat in formular.

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - INDEX

Am ramas datoare cu o ultima postare pentru interogarea tabelelor de date. Asa cum v-am promis voi prezenta functia INDEX care este favorita mea pe parte de functii Lookup. Este preferata mea pentru ca pot cauta cum vreau eu si in plus pot cauta avand mai multe conditii :D.

• Functia INDEX - se foloseste pentru a returna o valoare dintr-un tabel in functie de coloana si randul la care se gaseste acea valoare. Sintaxa acestei functii este urmatoarea: INDEX(array,row_num,column_num)

1.  Array - reprezinta tabelul din care dorim ca functia sa ne returneze rezultatul. Un mic sfat ar fi sa faceti referinta regiunea de celule daca doriti sa copiati formula si in alte celule.
2. Row_num - reprezinta numarul randului din care dorim sa fie adusa valoarea. Daca row_num este omis atunci column_num este obligatoriu. Tot in acest caz, functia index nu va returna doar o valoare ci va returna toate valorile din coloana respectiva.
3. Column_num - reprezinta numarul coloanei din care se va aduce valoarea dorita. La fel ca la mai sus, daca column_num este omis, row_num este obligatoriu.

Cea mai usoara utilizarea a functiei INDEX este atunci cand cunoastem numarul randului si coloanei, dar de cele mai multe ori interogarile se fac in tabele care sunt destul de mari. In acest caz, numarul randului sau al coloanei nu se cunoaste. La fel ca in postarea anterioara, pentru a afla aceste necunoscute folosim functia MATCH.

Pentru a intelege mai bine cum putem folosi INDEX, am creat un tabel in care avem urmatoarele coloane: Cod produs, Denumire produs si stocul existent in Depozit si Magazin:

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - HLOOKUP

Desi in postul anterior am spus ca o sa revin cu un alt post despre functiile HLOOKUP si INDEX, acum va voi scrie doar despre functia HLOOKUP pentru ca am inceput sa scriu si a iesit cam lunga povestea ca sa mai pot scrie si despre INDEX.

• Functia HLOOKUP - este similara cu VLOOKUP, insa face oposului ei. Daca VLOOKUP cauta o valoare in prima coloana a unui tabel, HLOOKUP cauta o valoare in primul rand al unui tabel de date. Sintaxa acestei functii este urmatoarea:  HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)

1. Lookup_value - reprezinta valoarea pe care dorim sa o cautam in primul rand al tabelului de date. Dca in tabelul de date nu exista valoarea cautata de hlookup atunci functia va returna eroarea #N/A.
2. Table_array - este tabelul de date in care cautam valoarea dorita. Pentru table_array se poate folosi o selectie din fisier sau o zona definita prin Named Range. Principala conditie in hlookup este ca primul rand al tabelului sa contina valorile unde cautam lookup_value.
3. Row_index - reprezinta numarul randului din care dorim sa fie returnata informatia pentru valoarea cautata. Daca row_index este mai mic ca 1, functia va returna eroare #VALUE!, iar daca row_index este un numar mai mare decat numarul de randuri din tabel, hlookup va returna eraoare #REF!.
4. Range_lookup - la fel ca la VLOOKUP, acest camp reprezinta o valoare logica ce specifica daca se doreste o potrivire exacta sau o potrivire aproximativa. Daca este scris True, 1 sau este omis functia va returna o potrivire aproximativa, iar daca este scris False sau 0 atunci va returna o potrivire exacta. 

Utilizarea functiilor LOOKUP pentru interogarea tabelelor de date - VLOOKUP

Astazi am ales sa va scriu despre functiile LOOKUP care se folosesc pentru a aduce intr-o celule informatii dintr-un tabel de date. Aceste functii sunt foarte utilizate in randul celor care folosesc in mod intens excel-ul.

Eu folosesc cel mai des functiile LOOKUP, in cadrul fisierelor in care se introduc date pentru a completa automat tabelul cu informatiile pe care le cunoastem deja. Un exemplu ar fi intr-o format de factura, in care putem folosi formula vlookup pentru a aduce, in functie de numele clientului, celelalte date ale sale ca adresa, contul bancar, CUI-ul etc.

• Functia VLOOKUP - este folosita pentru a căuta datele specificate in prima coloana a unui tabel de date. Sintaxa functiei VLOOKUP este urmatoarea: =vlookup(lookup_value,table_array,col_index_num,range_lookup)

1. Lookup_value - reprezinta ceea ce dorim sa cautam in tabelul de date. Lookup_value poate fi o valoare sau o referinta. Daca in tabelul de date nu exista valoarea cautata de vlookup atunci functia va returna eroarea #N/A. 
2. Table_array - reprezinta tabelul de date in care cautam valoarea dorita. Tabelul de date poate sa aibe doua sau mai multe coloane. Principala conditie in vlookup este ca prima coloana a tabelului sa contina valorile unde cautam lookup_value. Aceste valori pot fi text, numere sau valori logice. Textul cu litere mari este echivalent cu cel cu litere mici. 
3. Col_index_num - reprezinta numarul coloanei din table_array din care trebuie returnata valoarea potrivita. Dacă col_index_num este mai mic decât 1, functia returneaza eroarea #VALUE!, iar daca este mai mare decat numarul de coloane din tabel, returneaza eroarea #N/A.
4. Range_lookup - reprezinta o valoare logica, care specifica daca functia cauta o potrivire exacta sau o potrivire aproximativa. In general se foloseste cautarea exacta care are ca argument FALSE sau 0. In cazul cautarii aproximative (argumentul TRUE, 1 sau omis), tabelul trebuie sortat in ordine crescatoare dupa prima coloana. De asemenea, daca functia nu gaseste valoarea din lookup_value, ea returnaza valoarea cea mai mare urmatoare care este mai mica decat lookup_value. 

Crearea graficelor dinamice - Dynamic Range Charts

Am primit o cerere din partea unei colege de a prezenta o descoperire recenta de a mea: graficele dinamice. Un grafic dinamic este un grafic care se actualizeaza singur in urma adaugarii in tabelul initial de noi date. Eu am folosit acest grafic intr-un raport in care, in urma selectarii unei luni primeam lista de clienti din perioada respectiva, care era dinamica.

Pentru exemplul de azi am ales sa folosesc un tabel in care avem doua coloane: Luna si Vanzari. In tabelul initial avem informatii doar pana la luna mai, dar in timp vor aparea si alte date pe care dorim sa le integram automat in grafic. Pentru acest lucru ne vom folosi de listele dinamice (Dynamic Named Ranges), pe care le-am prezentat intr-un post anterior.

Adunarea celulelor in functie de doua sau mai multe conditii

Am revenit cu postarea despre adunarea celulelor in functie de doua sau mai multe conditii. Pentru a realiza acest lucru putem folosi functia SUMPRODUCT sau functia SUMIFS, insa cea din urma este disponibila doar pentru pachetele Office 2007 si 2010. 

• Functia SUMPRODUCT -  la baza, aceasta functia multiplica celulele corespondente ale unor zone de celule date si returneaza suma acelor multiplicari. Insa cunoscand sintaxa potrivita functia poate aduna sau numara celulele care indeplinesc doua sau mai multe criterii. Deoarece sintaxa SUMPRODUCT difera de cea de baza, pentru a intelege mai bine o sa fac o prezentare direct pe exemplu.

Pentru aceasta postare folosesc un fisier in care tabelul cu informatii se regaseste in sheet-ul Data. In tabelul alaturat puteti vedea o portiune din acesta.

Pentru exemplul nostru am ales sa calculam Venitul Total Realizat pentru fiecare Regiune si fiecare Produs. Pentru a realiza suma in functie de aceste doua criterii ne vom folosi de argumentele de tip array in cadrul functiei sumproduct.

=SUMPRODUCT(--(Data!$C$2:$C$1000=A2),--(Data!$D$2:$D$1000=B2),

--(Data!$F$2:$F$1000))

Adunarea celulelor in functie de o conditie

Am ales sa va scriu despre adunarea celulelor in functie de o conditie pentru ca am primit o cerinta sa scriu despre adunarea celulelor in functie de doua sau mai multe conditii. Insa nu va pot arata cum adunam celule dupa mai multe conditii fara a scrie cum realizam adunarea avand un singur criteriu. De asemenea, aceasta functie este folosita foarte des in realizarea rapoartelor si in sumarizarea informatiilor din diverse tabele.

Functia SUM - este una dintre cele mai simple functii care se folosesc in excel si aduna celulele dintr-o selectie de celule. Sintaxa acestei functii este  =SUM(number1, number2,...number30).  Sunt sigura ca cititorii mei folosesc in mod curent aceasta functie asa ca nu voi intra si in alte detalii.

Functia SUMIF - se foloseste pentru a aduna celulele dintr-o selectie in functie de un criteriu dat. Sintaxa acestei functii este =SUMIF(range,criteria,sum_range). 

• Range - reprezinta zona de celule care vor fi evaluate in functie de conditia din formula.  

• Criteria - reprezinta conditia care defineste celulele care vor fi adunate.

• Sum_range - reprezinta zona de celulele care vor fi adunate in cazul in care celulele din range indeplinesc condinatia din formula. Daca Sum_range este omisa, celulele din Range se folosesc atat pentru evaluarea conditiei dar si pentru adunare. 

Crearea listelor dependente - Dependent Data Validation List

In aceasta prima postare despre o functie excel, am ales sa prezint listele dependente create prin Data Validation List. Am descoperit cum se face acest lucru in urma cu 3 ani, iar cand mi s-a cerut un fisier care sa contina ceva de acest gen am zis ca este imposibil. Va puteti imagina surpriza pe care am avut-o cand am descoperit cum se face si bucuria avuta cand am realizat un fisier cu aceasta functie. Atunci mi-am dat seama ca nu folosesc excel-ul nici pe jumatate din cat as putea iar cu fiecare fisier nou creat am incercat sa descopar noi lucruri. In acest moment descoper macro-urile dar despre ele voi scrie alta data.

Exista mai multe metode prin care se pot crea listele dependente insa cea pe care o voi prezenta in continuare mi s-a parut cea mai simpla de folosit. Pentru a intelege mai bine metoda de creare a listelor am ales un exemplu in care trebuie sa alegem un departament si in functie de acest departament sa selectam un angajat din acesta.

Câteva vorbe de început

Invatam excel este un blog care se bazează pe dorinţa mea de a împărtăşi cunoştinţele şi descoperirile mele în excel persoanelor din jurul meu. Vă rog să nu credeţi că ceea ce voi posta aici este 100% creaţia mea, deoarece şi eu am găsit diferite exemple şi help-uri pe alte site-uri care m-au ajutat să înţeleg cum se pot realiza diferite lucruri în excel.

Nu voi începe să prezint lucrul cu excel-ul pentru nivelul începător ci voi posta funcţii care se adresează persoanelor care au ceva cunoştinţe de excel şi doresc să adune unele noi.

Sper ca ideea mea de blog vă place şi veţi reveni pentru a citi postările mele!