De la challenge la o noua formula - PERCENTILE

Buna,

Am avut o saptamana mai mult decat incarcata, in care in fiecare zi regretam ca nu am apucat sa scriu nici macar un cuvant din urmatorul articol. Astazi mi-am citit mailul si hopa challenge de la Chandoo. Cum puteam sa refuz asa ceva si m-am apucat de citit, iar dupa 5 min i-am spus sotului ca pe asta chiar nu stiu sa o fac. Uitandu-ma pe solutiile date de ceilalti am dat peste Percentile.

Deci astazi in loc sa va prezint o noua formula din excel 2010 pe care am inceput sa o folosesc foarte des, va prezint una pe care nu am folosit-o in viata mea. Revenim cu cealalta formula data viitoare :D.

Provocarea

Problema pusa de Chandoo a venit din partea unui tipe din HR care lucra intr-o imensitatea de companie. In urma evaluarilor angajatilor, pe fiecare departament (700 spunea tipa) trebuia sa grupaze angajatii in functie de 5 categorii:

1. Primii 5%
2. Urmatorii 15%
3. Urmatorii 65%
4. Urmatorii 10%
5. Ultimii 5%

Provocarea data de Chandoo consta in aplicarea optiunii Conditional formatting pe un tabel dat utilizand regulile de mai sus.

 

 

 

 

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.

Exemple de setare a criteriilor de filtrare [Partea 2/4]

Buna,

Astazi vom continua cu a doua parte din seria de articole despre Advanced Filter:

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

Filtrarea inregistrarilor unice

 Printre alte optiuni, Advanced Filter ofera posibilitatea filtrarii valorilor unice sau extragerea acestora dintr-o lista. In acest exemplu am copiat coloana de clienti din tabelul de data trecuta. Din aceasta lista lista dorim sa extragem doar inregistrarile unice.

• Selectati celulele pe care doriti sa le filtrati;
• In bara de meniu, selectati tab-ul Data si apoi optiunea Advanced Filter;
• In fereastra aparuta, selectati Copy to another location;
• Lasati casuta pentru Criteria Range goala;
• In casuta Copy to selectati prima celula din zona unde vreti sa se copieze datele;
• Bifati optiunea Unique Records;
• Apasati ok.

Filtrarea datelor intr-un alt sheet


Advanced Filter ofera posibilitatea filtrarii datelor si copierea acestora pe alt sheet decat tabelul initial. Insa pentru acest lucru este nevoie sa se urmeze urmatorii pasi:

• Selectati o celula in sheet-ul unde doriti sa copiati datele;
• In bara de meniu, selectati tab-ul Data si apoi optiunea Advanced Filter;
• In fereastra aparuta, selectati Copy to another location;
• In caseta List Range selectati tabelul pe care doriti sa il filtrati. Acest tabel trebuie sa fie in alt sheet decat cel care era activ cand ati selectat optiunea Advanced Filter;
• In caseta Criteria Range selectati celulele in care ati definit criteriul de filtrare (optional);
• In caseta Copy To selectati prima celula din zona in care doriti sa copiati datele filtrate. Aceasta celula trebuie sa fie din sheet-ul care era activ cand ati selectat optiunea Advanced Filter;
• Apasati ok.

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).