Multe probleme din domenii diverse de activitate se pot rezolva in Microsoft Excel. În timp devin rutină și nu le mai dăm importanță…. sau le tratăm superficial, sau … cum îmi place mie să spun facem coloane ajutătoare… și ajutătoare și coloane ajutătoare la cele ajutătoare :)

Astăzi am o provocare pentru cei care vor să-și pună mintea la contribuție.

Se dă următorul tabel de date:

Specificați, care este formula de calcul pentru a determina procentul de AC (Adaos comercial – pentru cei care nu știu) după următorul algoritm:

Exemplu: dacă prețul este mai mic decât 10 și stoc < 1000, adaos comercial va fi 6%. Dacă Prețul este mai mare sau egal decât 15 și stocul < 1500 atunci adaosul comercial va fi de 12%.

Soluțiile le trimiteți pe e-mail până în data de 10.03.2013 ora 22:00.

Vă rog să nu puneți comentarii cu formula până nu se termină timpul alocat!

Succes!

Update 10.03.2013 după ora 22:00

Trăim într-o lume dinamică, dar cu mult prea multe aspecte statice. De multe ori spuneam că ceea ce nu vedem nu avem cum să înțelegem sau invers, așadar justific numărul mic de răspunsuri prin prezența slabă în social media într-o zi de duminică.

Câteva explicații teoretice:

Sunt conștient că vin dintr-o zonă academică, dar astfel de probleme provin din practică. Rolul cercetării ar fi acela de optimizare a metodelor de lucru din practică, și găsirea celor mai rapide metode de rezolvare a problemelor.

Așadar, cele mai comune probleme din domeniul economic, vin din zona interpretării intervalelor de valori. Mulți practicieni folosesc Excelul într-un mod… ”minunat” ( a se citi: de te crucești) dar mi-a fost dat să întâlnesc și oameni care știu clar că datele le înregistrezi în baze de date, pe baza cărora faci rapoarte, iar dacă vrei grafice și floricele, cel mai bine este să conectezi Excelul la acea sursă de date și să faci interpretări câte în lună și în stele.

Revin la problema cu intervalele:

Funcția IF() permite selectarea unei acțiuni din două posibile pe baza evaluării unei condiții.

Formatul general al funcţiei este (pentru setări ale calculatorului cu Regional Settings Romania):

=IF(conditie;actiune_adevarat;actiune_fals)

Introducerea unei funcţii IF într-o foaie de calcul se poate realiza prin una din următoarele metode:

1. Acţionarea pictogramei Insert Function din linia Formula bar. În acest caz apare fereastra pentru alegerea funcţiei dorite. Se selectează categoria de funcţii (în cazul nostru Logical) şi apoi funcţia IF;

2. Precizarea argumentelor funcției: condiția (Logical_test), acțiunea adevărat (Value_if_true) și acțiunea fals (Value_if_false);

3. Finalizarea funcției prin apăsarea butonului OK.

Altă metodă de deschidere a constructorului de funcții este utilizarea meniului Formulas, opțiunea Logical și se alege din listă formula IF sau scrierea funcţiei utilizând tastatura și, în anumite cazuri, mouse-ul.

Problemele clasice cu IF-uri sunt cele legate de intervale de valori: Discounturi, Impozite/Taxe, Ajustari.

Intervalele de valori pot fi într-o dimensiune, două dimensiuni intersectate sau mai multe. Până la această oră am reușit să determin algoritmul de calcul matematic pentru prima și a doua dimensiune.

O singură dimensiune

Dacă ai un interval de valori pentru care trebuie să faci o funcție IF atunci numărul de IF-uri dintr-o funcție Excel este egal cu numărul intervalelor minus unu.

Exemplu:

Să se calculeze procentul de discount acordat pe o comandă de produse, in funcție de următoarele criterii:

• o in cazul in care cantitatea este mai mic decât 10 – atunci procentul de discount este de 0%
• o cantitate mai mare decât 10 si mai mic decât 15 atunci discount 0,5%
• o cantitate mai mare decât 15 si mai mic decât 20 atunci discount 1%
• o cantitate intre 20 si 25 atunci discount 1,25%
• o cantitate mai mare decât 25 atunci discount 1,5%

Intervalul de valori este în cazul nostru: 0 – 10 – 15 – 20 – 25 – x adică 5 intervale de valori, ceea ce înseamnă în grafic 5-1 ==> 4 funcții IF.

Cel mai simplu este să tratezi un interval de valori într-un IF de jos în sus.

Două dimensiuni

Problemele se complică puțin când avem două dimensiuni care depind una de cealaltă și în care trebuie să tratăm toate combinațiile posibile. Doar în scop de exemplu:

Calculați prețul de vânzare fără TVA (per unitate) al produselor după următoarele constrângeri:

• dacă stocul este mai mare sau egal decât 1000, și prețul este mai mare sau egal cu 5 lei, adaosul comercial va fi 20%,
• dacă stocul este mai mare sau egal decât 1000, și prețul este mai mic de 5 lei, adaosul comercial va fi 25%,
• dacă stocul este mai mic decât 1000, și prețul este mai mare sau egal cu 5 lei, adaosul comercial va fi 25%,
• dacă stocul este mai mic decât 1000, și prețul este mai mic de 5 lei, adaosul comercial va fi 30%.

Așadar, în acest exemplu avem două intervale de valori: stocul și prețul pe care trebuie să le tratăm global:

Schematic:

• Stoc: 0 – 1000 – x
• Preț: 0 – 5 – y

Modul de calcul al IF-urilor care vor apărea în formulă este: numărul de intervale de pe prima dimensiune minus 1 + numărul de intervale de pe a doua dimensiune înmulțit cu numărul de deasupra minus 1.

Așadar:

• Stoc: 0 – 1000 – x ==> două intervale ==> 2-1 = 1 +
• Preț: 0 – 5 – y ==> două intervale ==> 2x(2-1)=2×1=2=3

Folosind algoritmul în două dimensiuni

Soluția problemei din articolul inițial este:

Asta înseamnă rezolvare corectă din punct de vedere didactico-teoretic. O soluție pe care oricine o poate înțelege, reproduce și controla. În același timp: Schema scrie formula!

Cine a câștigat competiția inițială?

Sper că toți cei care văd acest algoritm de lucru.

În fapt, din totalul celor 194 care au deschis articolul,  au trimis e-mail (ordonat după timp) următorii:

1. Cristiana R. – Soluție funcțională în 16 IF-uri

2. Andreea A. – Soluție optimă în 15 IF-uri

Soluția ei:

=IF(E2<5,IF(D2<500,1,IF(D2<1000,5,IF(D2<1500,9,13))),IF(E2<10,IF(D2<500,2,IF(D2<1000,6,IF(D2<1500,10,14))),IF(E2<15,IF(D2<500,3,IF(D2<1000,7,IF(D2<1500,11,15))),IF(D2<500,4,IF(D2<1000,8,IF(D2<1500,12,16))))))

3. Catalin D. – Soluție funcțională folosind Macro-uri… Nu face parte din concurs, dar este o soluție ingenioasă.

4. Vladut A.P. – Soluție funcțională în 20 IF-uri

5. Radu G. – Soluție nefuncțională pentru că nu utilizează IF-uri, dar deosebit de interesantă:

=OFFSET(Sheet2!$B$2; MATCH(D2; Sheet2!$B$3:$B$6; 1); MATCH(E2; Sheet2!$C$2:$F$2; 1))

Tabelul din Sheet2:

6. Ciprian a mai propus o soluție cu 96 de IF-uri…

7. Dragos si B.S. a propus o soluție funcțională cu 16 IF-uri.

Mulțumesc tuturor pentru implicare!

Future

Lucrez la algoritmul de calcul pentru 3 intervale… încă nu-mi iese… ceva…

Spor în ceea ce faceți și dacă mai aveți probleme interesante nu ezitați să le aruncați pe mail.

UPDATE 11.03.2013 după ora 9:30

Una din cele mai interesante soluții pe care am primit-o în dimineața aceasta de la Ionuț E. este cu o funcție IF ultra simplificată. Asta din cauză că procentele de AC sunt consecutive. Smart!

=IF(STOC<500,0,IF(STOC<1000,4,IF(STOC<1500,8,12)))+IF(PRET<5,1,IF(PRET<10,2,IF(PRET<15,3,4)))

Da merge!!! Că am testat. Dacă AC-ul ar fi fost altfel, dar proporțional probabil că ar fi găsit algoritmul de însumare.

De asemenea, la cererea publicului voi dezvălui soluția magică cu macro a lui Catalin D. El a creat un nou modul in VBA și a definit o nouă funcție cu numele IFS(). Codul funcției este.

Function Ifs(pret_ As Integer, stoc_ As Integer) As Double
    Application.Volatile True
    Select Case pret_
        Case Is < 5 And stoc_ < 500
                    Ifs = 0.01
        Case Is < 5 And stoc_ < 1000
                    Ifs = 0.06
        Case Is < 5 And stoc_ < 1500
                    Ifs = 0.09
        Case Is < 5 And stoc_ >= 1500
                    Ifs = 0.13
        Case Is < 10 And stoc_ < 500
                    Ifs = 0.02
        Case Is < 10 And stoc_ < 1000
                    Ifs = 0.06
        Case Is < 10 And stoc_ < 1500
                    Ifs = 0.1
        Case Is < 10 And stoc_ >= 1500
                    Ifs = 0.14
        Case Is < 15 And stoc_ < 500
                    Ifs = 0.03
        Case Is < 15 And stoc_ < 1000
                    Ifs = 0.07
        Case Is < 15 And stoc_ < 1500
                    Ifs = 0.11
        Case Is < 15 And stoc_ >= 1500
                    Ifs = 0.15
         Case Is >= 15
                Select Case stoc_
                    Case Is < 500
                         Ifs = 0.04
                    Case Is < 1000
                         Ifs = 0.08
                     Case Is < 1500
                         Ifs = 0.12
                     Case Is >= 1500
                         Ifs = 0.16
                End Select
    End Select
End Function

Pentru cei care doresc să studieze mai mult macro și VBA în Excel le recomand cărțile:

• Microsoft® Office Excel® 2007 Visual Basic® for Applications Step by Step
• Excel 2010 Power Programming with VBA

Succes și mulțumesc pentru interes.