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.

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

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

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.