Provocare formula IF - Chandoo

Buna,

De dimineata am dat peste ultima provocare care a postat-o Chandoo la el pe blog, IF Formula Challenge. Desi initial prea avem chef de asa ceva, primul paragraf din articol m-a convins:

If I were to hire an data analyst, I would simply ask them to write a complex IF formula in Excel. If they can write it, the interview progresses, else, they are out. In other words,

=IF(person_can_write_big_fat_IF_formula=TRUE, proceed_with_interview, say_thanks_and_call_next_person)

If you are able to write IF formulas for any situation, then you are bound to be awesome in Excel.

Provocarea

Problema pusa de Chandoo a constat in realizarea unei formule, utilizand IF, care sa calculeze primele oferite intr-un departament in functie de urmatoarele conditii:

• Daca procentul de absenteism este 0% se primeste 1500;
• Daca procentul de absenteism este mai mic de 3% se primeste 1000;

• Daca timpul de rezolvarea a unei cereri telefonice este mai mic de 500 de secunde se primeste 1000;
• Daca timpul de rezolvarea a unei cereri primite pe fax este mai mic de 560 de secunde se primeste 1000;
• Nota: cele doua afirmatii nu pot fi adevarate in acelasi timp.

•  Daca angajatul primeste cel putin o recomandare, bonusul este de 1000.

• Daca rezultatul auditului de calitate este intre 98% si 100% se primeste 1500;
• Daca rezultatul auditului de calitate este intre 96% si 97.99% se primeste 1000;

• Daca toate conditiile de mai sus sunt indeplinite se primeste un bonus de 5000.

 
Solutia

 
Formula pe care am scris-o este urmatoarea (atentie este lunga :D):

=IF(AND(C4=0;D4>0.98;OR(E4<500;F4<560);AND(G4<>""; G4 >= 1));5000;(IF(C4=0;1500;IF(C4<0.03;1000;0))+IF(OR(E4<500;F4<560);1000;0)+IF(AND(G4<>""; G4 >= 1);1000;0)+IF(D4>=0.98;1500;IF(D4>0.96;500;0))))

Primul IF verifica daca toate conditiile sunt adevarate in acelasi timp, daca conditia este falsa avem cate un IF pentru a verifica fiecare indicator.
 

Va invit si pe voi sa gasiti o solutie diferita de cea prezentata de mine. Puteti sa downloadati fisierul de lucru de la urmatorul link: if formula challenge.xlsx.

Introducere in Conditional Formatting - repost

Buna,

Am luat o mica pauza cu Pastele. Totusi nu am stat degeaba si in acest timp am analizat ce subiecte interesante sa va mai prezint. Tot in aceasta mini vacanta am lucrat la o noua varianta de balanta personala mai usor de customizat.

Astazi doresc sa va fac o introducere in Conditional Formatting. Folosesc foarte des aceasta optiune. Printre modificarile care au fost aduse in versiunile de Excel 2007 si 2010, cele care mi-au placut cel mai mult este usuratrs modalitatea de aplicarea a optiunii si noile tipuri de formatarea care pot fi aplicate. Despre aceste noi optiune vom discuta in alta zi.

Ce este Conditional Formatting?

Optiunea Conditional Formatting permite formatarea celulelor dintr-o zona in functie de criterii definite de utilizator. Se poate formata fontul celulei, culoarea background-ului, a fontului etc. Criteriile pot fi definite in functie de continutul celulei sau in functie continutul altor celule.

Cateva excemple in care se poate folosi Conditional Formatting sunt pentru schimbarea culoarii fontului pentru celulele care au valori negative, sau background-ului valorilor duplicat.

Observatie: In Excel 2003, sau versiunile anterioare, se pot seta maxim 3 criterii pentru fiecare celule. Pentru a aplica mai mult de trei trebuie sa se foloseasca VBA.

Cum aplicam Conditional Formatting?

Pentru acest exemplu am folosit un tabel cu trei coloane: Nume client, Luna si Total vanzari si vom aplica Conditional Formatting pe coloana Total vanzari astfel:

• schimbarea background-ului in mov pentru celulele a caror valoare este mai mare de 25000;
• schimbarea background-ului in albastru deschis pentru celulele a caror valoare este mai mica de 20000.

Advanced Filter in VBA [Partea 4/4]

Buna,

Am revenit cu ultima partea din seria Advanced Filter. Astazi vom parcurge sintaxa vba pentru Advanced Filter si vom vedea 3 exemple pentru aceasta optiune:

• Introducere Advanced Filter
• Exemple de setare a criteriilor de filtrare
• Complex criteria in Advanced Filter
• Advanced Filter in VBA 
 

Sintaxa VBA Advanced Filter

In VBA Advanced Filter are urmatoarea sintaxa:

expression .AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)

• expression  - este camp obligatoriu si reprezinta obiectul de tip Range din VBA. Pentru Advanced Filter, acesta poate fi o coloana, o regiune de celule sau o zona definita prin optiunea Named Range;
• Action - prezinta actiunea care se doreste prin aplicarea Advanced Filter. Este un camp obligatoriu si poate fi xlFilterCopy - datele sunt copiate intr-o zona definita sau xlFilterInPlace - filtrare in acelasi tabel;
• CriteriaRange - in acest camp se scrie adresa regiunii in care sunt setate criteriile pentru filtrare. Acest camp este optional. Daca este omis, filtrarea se face fara criterii;
• CopyToRange - acest camp se foloseste in cazul xlFilterCopy si se foloseste pentru a scrie adresa regiunii unde se doreste copierea datelor dupa filtare. Acest camp este optional.
• Unique - Acest camp se foloseste atunci cand se doreste filtrarea inregistrarilor unice. Argumentele pot fi TRUE, atunci cand se doreste filtrarea unica si FALSE cand nu se doreste acest lucru. In mod implicit, campul este setat pe FALSE.

Filtrarea in acelasi tabel (Filter in place)

La fel ca in articolele anterioare am folosit acelasi tabel. Codul VBA folosit pentru filtrarea tabelului conform unor criterii este:

Public Sub FilterInPlace()

Worksheets("Advanced filter").Range("A1:C29").AdvancedFilter _
    Action:=xlFilterInPlace, _
    CriteriaRange:=Worksheets("Advanced filter").Range("F1:F2")
   
End Sub

Tips & Tricks - Top 5 scurtaturi pe care le folosesc cel mai des

Buna,

Va spuneam data trecuta ca vreau sa luam o pauza de la Advanced filter si sa vorbim putin de scurtaturi. Aproape orice are o scurtatura in Excel, si daca nu are poti face un macro pentru el :D. Trebuie sa recunosc ca eu nu folosesc cine stie ce scurtaturi dificile. Asa ca in continuare o sa va prezint 5 scurtaturi pe care le folosesc aproape zilnic.

Inainte de top, trebuie neaparat sa va aduc aminte de CTRL + S. Din propria experienta, va pot spune ca e foarte naspa sa lucrezi cu orele la cine stie ce fisier complicat si sa pice curentul sau sa se blocheze excelul. Asa ca mai bine apasati CTRL + S din cand in cand.

1. F2 - Editarea unei celule

Sunt sigura ca o folositi. Apasand F2  se poate edita celula activa, punctul de inserare fiind introdus la sfarsitul continutului din celula. Cand editarea in celula este inactiva, punctul de inserare se activeaza in bara de formule.

Nota: SHIFT+F2 se poate edita comentariul unei celule.

2. F4 - Modificarea referintei unei celule in editarea formulelor

In timp ce este scrisa sau editata o formula, se poate folosi F4 pentru a schimba referinta unei celulei pe care cursorul este activ. Apasand F4, excel schimba referinta unei celula intre relativa A2, absoluta $A$2, coloana relativa - rand absolut A$2 si coloana absoluta - rand relativ $A2.

Nota: CTRL+F4 se inchide fisierul selectat.

3. F3 - Afisarea Named Range-urilor definite

Tot mai des folosesc fisiere in care definesc multe Named Range-uri si imi este foarte greu sa imi amintesc denumirile lor cand scriu o formula. Scurtatura F3 am descoperit-o varea trecuta si de atunci o folosesc mereu. Apasand F3 vei obtine lista cu toate numele definite. Selecteaza unul din lista si va fi adaugat in locul unde este cursorul. Puteti sa il folositi in fomule, conditional formating, data validation etc.

Nota: SHIFT+F3 deschide caseta generala de inserarea a formulelor.

4. CTRL + D - Copierea continutului celulei de mai sus

CTRL+D este utilizata pentru a accesa comanda fill down, care copiaza continutul si formatul celulei de mai sus in celula sau celulele selectate.

Nota: CTRL+R utilizeaza comanda fill right pentru a copia continutul si formatul celuleli din stanga a celului sau celulelor selectate.

5. CTRL + C - Copierea continutului unei celule

CTRL+C este una din cele mai folosite scurtaturi in Windows.  In Office, Un CTRL+C urmat de un alt CTRL+C deschide fereastra Microsoft Office Clipboard. Este posibil, sa nu aveti activata aceasta optiune. In cadrul setariilor pentru clipboard se realizeaza activarea acestei scurtaturi.

Nota: CTRL+V insereaza continutul din Clipboard in punctul de inserare si inlocuieste orice selectie. Aceasta optiune este disponibila numai dupa ce s-a decupat sau copiat un text, obiect sau continutul unei celule.

         CTRL+X decupeaza celulele selectate.

He he primul articol fara nici o poza :D.

Va invit si pe voi sa ne impartasiti scurtaturile pe care le folositi cel mai des. Daca nu ati folositi scurtaturi pana acum acest articol este un prim pas spre a va obisnui cu cateva din cele mai folositoare scurtaturi.