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:
| Stoc | Pret<5 | Pret<10 | Pret<15 | Pret>=15 |
| <500 | AC=1 | AC=2 | AC=3 | AC=4 |
| <1000 | AC=5 | AC=6 | AC=7 | AC=8 |
| <1500 | AC=9 | AC=10 | AC=11 | AC=12 |
| >=1500 | AC=13 | AC=14 | AC=15 | AC=16 |
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:
| Pret<5 | Pret<10 | Pret<15 | Pret>=15 | ||
| 0 | 5 | 10 | 15 | ||
| Stoc<500 | 0 | 1 | 2 | 3 | 4 |
| Stoc<1000 | 500 | 5 | 6 | 7 | 8 |
| Stoc<1500 | 1000 | 9 | 10 | 11 | 12 |
| Stoc>=1500 | 1500 | 13 | 14 | 15 | 16 |
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.
Valy Greavu - Live Blog 
96 IF-uri?!?
ApreciazăApreciază
dar un lookup n-ati incercat ? sa mai scurtati din if-uri?
ApreciazăApreciază
totusi, cum este acea solutie cu 96 de if-uri?
ApreciazăApreciază
Romeo, poti sa ne dai un exemplu?
Dragos: autorul a preferat ca solutia lui sa ramana privata.
ApreciazăApreciază
Si noi multumim.
ApreciazăApreciază
Tot valabila doar in cazul prezentat, cu AC consecutiv si cate 3 multipli de 5 respectiv 500 pe cele doua axe, doar cu 4 IF. Merge.
=IF(INT(B2/5)=0;INT(A2/500)+1;IF(INT(B2/5)=1;INT(A2/500)+5;IF(INT(B2/5)=2;INT(A2/500)+9;INT(A2/500)+13)))
B2 = pret
A2= stoc
ApreciazăApreciază
=IF(INT(B2/5)=0;INT(A2/500)+1;IF(INT(B2/5)=1;INT(A2/500)+5;
IF(INT(B2/5)=2;INT(A2/500)+9;INT(A2/500)+13)))
ApreciazăApreciază
pardon, chiar 3 IF
ApreciazăApreciază
Mircea… ca și Ionut E. ați speculat tabelul meu de date cu procentele de AC :) Data viitoare o sa le fac cu randbetween(1;100) :)
ApreciazăApreciază
Iar noi o sa folosim randif :)))
ApreciazăApreciază
Va salut.
Caut de ceva timp raspunsul la urmatoarea intrebare :
In excel am:
– pret intrare = 8
– pret iesire = 11
Cum calculez adaosul procentual in % . Mentionez ca preturile asa le-am gasit . Nu au fost formate de mine.
Eu impart 11 la 8 si rezulta un adaos procentual de 37.5 % .
Totusi , stiu ca exista un alt calcul matematic in excel care solutioneaza problema mea si imi rezulta direct raspunsul corect in procente.
Astept raspunsurile voastre.
Multumesc.
ApreciazăApreciază
Salut Florin,
Eu stiu ca de cand e lumea si pamantul Adaosul commercial nu se calculeaza printr-o formula built-in din Excel. In schimb rationamentul tau nu-I chiar rau, chiar daca obtii prin impartire 1,35.
Formula de baza, in care PI = Pret Intrare, AC = Adaos Comercial si PV = Pret de vanzare, este asa:
PI + PI*AC = PV
Daca stii PI si PV atunci:
AC = (PV – PI)/PI
Conform exemplului tau iti da exact 0,375 si-l faci reprezentare ca procent cu doua Zecimale in Excel.
ApreciazăApreciază
Am si eu o problema mai delicata la care eu nu reusesc sa gasesc rezolvare.
am creat un pivot table pe baza unei baze de date, iar langa pivot am inceput sa fac niste analize intre pivot si un alt tabel.
Deci pivot table e format din:CLIENT PUNCTDE LUCRU | CLIENT SEDIU SOCIAL | NUME CLIENT | Sum of VAL FACTURATA (fara TVA), iar sheetul doi cuprinde un alt tabel unde avem introduse urmatoarele: FILIALA | CLIENT PUNCT LUCRU | DENUMIRE CLIENT | Jan – TARGET | Jan – BONUS | Jan – PARTENER | Feb – TARGET | Feb – BONUS | Feb – PARTENER | Mar – TARGET | Mar – BONUS | Mar – PARTENER | Apr – TARGET | Apr – BONUS | Apr – PARTENER | May – TARGET | May – BONUS | May – PARTENER | Jun – TARGET | Jun – BONUS | Jun – PARTENER | Jul – TARGET | Jul – BONUS | Jul – PARTENER… pana in luna Dec.
Vreau ca sa creez o nou coloana langa pivot denumita target, unde sa mi se preia datele din sheet – ul 2, in functie de client si luna selectata. dar si in functie de targetul din sheet 2….
Asta ar merge: =IFERROR(INDEX(Date_Bonus!$A$2:ER559,MATCH($B9,Date_Bonus!$B$2:$B500,0),MATCH(CONCATENATE($G$5,” „,”-„,” „,LEFT($H$8,4)),LEFT(LUNILE,10),0)),”Valoare indisponibila”)
problema e ca in sheet 2 am un nivel de target progresiv in functie de vanzari…pt acelasi client am 3 sau 4 nivele de target …cum pot sa fac ca sa imi aduc in celula exact valoarea targetului corespunzatoare volumului de vanzari
Multumesc anticipat de raspuns
ApreciazăApreciază
Ca sa aduceti toate celulele aferente unui client ati putea incerca sa folositi un sumif() ?
ApreciazăApreciază
dar nu vreau sa imi insumeze toate nivelurile de target, trebuie doar sa imi aleaga nivelul de targetare in care se incadreaza condorm vanzarilor…
as putea sa va trimit un exemplu daca sunteti de acord
ApreciazăApreciază
La soluția lui Ionuț, principiul de funcționare nu e legat de proporționalitatea AC-urilor introduse ci de numerotarea în baza 4, sau mai general n, unde n sunt numărul tranșelor de preț, a cazurilor posibile. Cu If() -ul propus de el determini numărul cazului în care te afli, iar cu un CHOOSE() îți selectezi soluția dorită, oricare ar fi ea.
Ideea poate fi utilizată și pentru mai multe dimensiuni, însă cred că e mai eficient să utilizezi funcții de căutare pentru a găsi poziția în cub corespunzătoare cazului căutat.
ApreciazăApreciază