Excel IF() – Insane Level

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:

Succes și mulțumesc pentru interes.

10/03/2013

Problemă de formatare în Excel

[Update – Rezolvarea]

Soluția câștigătoare este cea care presupune execuția cu cel mai puțin efort posibil. Așa cum am specificat în mesajul inițial, formatarea condițională era soluția cea mai la îndemână. Problema este că din câte am fost învățați formatarea condițională la nivel de bloc de căsuțe se aplică pentru o valoare fixă… în soluțiile mai evoluate pentru o valoare specificată într-o anumită celulă.

Interesant în schimb este că Excelul știe, da Excelul știe, să aplice relativitatea celulelor în formatarea condițională. Și pentru mine a fost o surpriză, de aceea am ținut să partajez cu voi această problemă, pentru că ”nu-i așa?”… poate fi fun.

Nu vă mai fierb. Soluția era așa:

Se selectează blocul de căsuțe de la B2 la F5
Conditional formatting, Highlight Cells Rules, Less than
În fereastra cu formula se scrie la Format cells that are… =$A2
Ok

Și merge!

Stupid de simplu nu? Învățămintele pe care ar trebui să le deducem: nu trebuie să căutăm soluții complicate la probleme simple! Dacă avem o cale de urmat, suntem datori să o încercăm. Necunoașterea te face să pierzi mai mult timp decât ar trebui.

Îmi cer scuze celor care ar fi dorit să răspundă și nu le-a mers adresa de e-mail pe care am specificat-o greșit… din viteză!

O să-i specific doar pe cei care au trimis soluții complete sau sefe.

Primul care a răspuns la mesaj a fost OvidiuD, din Iași. Soluție corectă.

Al doilea a fost AlexandruD din București la câteva minute după soluția lui Ovidiu.

Al treilea a fost IonutE din Brașov care a dat o soluție nu tocmai optimă dar corectă. La fel ca el am mai primit alte mesaje. Soluția lor era de a formata prima celulă din tabel și apoi cu Format Painter să copie formatarea din celula de date odată pe linie și apoi în jos. Nu e cea mai rapidă dar este corectă.

Mult mai puține soluții față de problema trecută dar una dintre ele merită premiul de soluția sefe, și vine de la CiprianS din Iași:

Se adauga codul de mai jos in Visual Basic Editor:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column <> 1 Then

        currentRow = Target.Row

        If Target.Value < Cells(currentRow, 1).Value Then

            Target.Font.ColorIndex = 3

        End If

    End If

End Sub

Acum, eu zic că mă pricep puțin și la VBA, dar… o soluție nu este completă dacă nu are și o documentație corespunzătoare. Cum se execută? De ce? :) De multe ori ”butonangiilor”, cum ne spunea profesorul Oprea, li se pare pierdere de timp să-ți explice cum să folosești un cod. Dar mai mulți decât geeks sunt utilizatorii care nu pot obține/folosi rodul muncii fără documentația completă.

Cam atât pentru astăzi. Credeți că ar merge să vă dau o problemă cu rotunjiri la nivel de fracțiuni, pentru a cărei rezolvare am beneficiat și de ceva lăzi de bere? :)

Dacă aveți probleme interesante din Excel, dați un mail și le lansăm în public.

Mulțumesc tuturor pentru implicare.

PS. De ce fac chestiile astea? De fun! ;) Asta e menirea educației în fapt: ținerea cerebelului ocupat și partajarea cunoașterii. Că eu și mulți alții o facem instituționalizat?! Asta este doar chestie de conjunctură.

[/UPDATE]

Având în vedere interesul crescut față de subiect, m-am gândit să continui poveștile cu Excel și să vă dau o nouă problemă de rezolvat.

Se dă următorul tabel:

Se observă că valorile de pe coloanele 1,2,3,4,5 care sunt mai mici decât cele de pe coloana Refer sunt evidențiate. Aparent problema este simplă și orice utilizator trecut de nivelul începător se duce cu gândul spre formatarea condițională.

Numai că avem o constrângere foarte importantă! Soluția cea mai bună este cea care va prezenta un mod cât mai rapid de rezolvare sau utilizarea câtor mai puține operațiuni, formule, comenzi.

Soluțiile care presupun adăugarea și utilizarea de coloane suplimentare vor fi depunctate, dar vor fi lăudate dacă folosesc ”cârnați” întregi de formule, sub-formule și if-oase. :)

~~Aștept rezultatele pe e-mail la adresa: valygreavu [at] hotmail.com până marți, 18 septembrie ora 22:00. Nu comentați metodele de rezolvare în public, decât după expirarea timpului.~~

Succes!

18/09/2012

Problemă cu șiruri in Excel

Sunt la un curs de Excel și mi-a venit în cap o problemă faină, zic eu, de Excel.

Așa că m-am gândit să o aduc în atenția voastră și să vă provoc la o rezolvare.

Rezultatele trebuie trimise prin e-mail la valygreavu [at] hotmail.com până joi, 13 septembrie ora 22:00.

UPDATE: Trimitere rezultate încheiată! Soluția câștigătoare:

=IF((LEFT(RIGHT(A2;3))=”.”);RIGHT(A2;2);RIGHT(A2;3))

Email	Domeniu
x@yahoo.com	com
y@yahoo.ro	ro
j@hotmail.com	com
m@x.jp	jp
m@yyyy.com	com
x@yahoo.com	com
y@yahoo.ro	ro
j@hotmail.com	com
m@x.fr	fr
m@yyyy.mx	mx
j@hotmail.com	com
m@x.jp	jp
m@yyyy.com	com

Link public la fisierul Excel cu soluțiile propuse: https://skydrive.live.com/redir?resid=6EC38785F56DAE91!2715

Cerința: Care este formula prin care obțineți valorile din dreapta?

Pentru clarificări dați comentarii. Pentru rezolvitori dați soluția pe mail. Cea mai bună soluție va fi publicată.

[UPDATE 14.09.2012 h 00:10]

Cea mai rapida solutie a venit de la Mircea P: =RIGHT(A2; LEN(A2)-SEARCH(„.”; A2; 1)) Foarte buna in contextual datelor, dar nu general valabila in contextual adreselor de tip: prenume.nume@domeniu.co.jp

Altă soluție: =MID(A2, FIND(„.”, A2) + 1, 4) nu se aplică în contextual adresei de mai sus.

Viorel L care mi-a fost student, dar nu la Excel, a spus ca problema este simpla. Si a venit cu formula: =RIGHT(A2;LEN(A2)-SEARCH(„.”;A2)) Normal ca nefunctionala pentru toate tipurile de adrese valabile de mail.

Alina C. a venit cu formula pe care o aveam si eu in minte: =IF((LEFT(RIGHT(A2;3))=”.”);RIGHT(A2;2);RIGHT(A2;3)) Se vede ca mi-a fost studenta si a facut Excel cu mine, chiar dacă la orele cu ei nu am facut așa ceva, se pare că a înțeles metoda :)

Ionut S. a vrut sa fie altfel si a trimis o solutie prin care sa scoti domeniul de mail nu domeniul de root. Formula lui arata: =LEFT(RIGHT(A2;LEN(A2)-FIND(„@”;A2));FIND(„.”;RIGHT(A2;LEN(A2)-FIND(„@”;A2)))-1) si se pare ca merge pe mai multe tipuri de adrese. De fapt pe toate pe care le-am testat eu, chiar dacă soluția originală nu este funcțională pentru a scoate domeniile de tara: =RIGHT(A2;LEN(A2)-FIND(„.”;A2))

Aceași soluție nefuncțională cu Right la început a mai venit de la alți concurenți, bună pentru exemplul dar, dar nu pentru toate adresele de e-mail valabile.

Simona C. a dat soluția corectă folosind aceeași formula ca Alina C. Si Simona a trecut pe la orele mele… deja devin suspicios.

Ovidiu D, a obținut rezultate destul de interesante în prima versiune, dar nu suficient de exacte. Formula lui: =RIGHT(A2;LEN(A2)-FIND(„.”;A2;FIND(„@”;A2;1))).

În ordinea mesajelor mi-a mai scris și Ala Posta, o soluție cu Viagra… dar nu se aplică în cadrul problemei curente :)

Ovidiu D a mai venit cu o soluție bazată pe array-uri. Din păcate formula: {=RIGHT(A2:A16;LEN(A2:A16)-FIND(„.”;A2:A16;FIND(„@”;A2:A16;1)))} returnează același rezultat, inexact ca cel al formulei de mai sus.

Mihai P a trimis o altă formă de RIGHT cu trim-uri =TRIM(RIGHT(A2;LEN(A2)-FIND(„.”;A2))) O menționez pentru că TRIM-urile pot avea un rol bun în lucrul cu șiruri… din păcate soluția se aplică doar pe datele de test nu și pe toate celelalte adrese de e-mail valabile.

Catalin D aplica un fel de hard-code prin formula sa: =MID(A2;SEARCH(„.”;A2;SEARCH(„@”;A2)+1)+1;6) Nu am înțeles cum a obținut acea valoare 6 din search, dar pare destul de matematizat pentru că ies destul de multe cazuri corect la iveala.

Andreea A propune asa după cum spune și ea o soluție mai băbească… care arată cam așa: =RIGHT(RIGHT(A2; LEN(A2)-FIND(„@”; A2)); LEN(RIGHT(A2; LEN(A2)-FIND(„@”; A2)))-FIND(„.”; RIGHT(A2; LEN(A2)-FIND(„@”; A2))))

Mie mi-a plăcut pentru că este lungă și drăguță… are atât de multe paranteze… :)))

Și în ordinea lor încă vre-o două mesaje cu right șiiii mid și right și len și search…

De departe în schimb cea mai lungă formulă îi aparține lui Constantin S:

=RIGHT(RIGHT(A2;LEN(A2) – SEARCH(„@”;A2)); LEN(RIGHT(A2;LEN(A2) – SEARCH(„@”;A2))) – SEARCH(„.”;RIGHT(A2;LEN(A2) – SEARCH(„@”;A2)))) Nefuncțională complet din păcate…

Așa cum îmi imaginam, Alexandru D. de la www.tutorialeoffice.ro a trimis cele mai multe și mai variate soluții de rezolvare a problemei cu datele de test prezentate. Din păcate nu toate exacte… făcând aceeași omisiune ca majoritatea, aceea de a rezolva problema fără a parcurge corect pașii: analiză, proiectare, implementare, testare, documentare și utilizare.

Aparent pare simplă problema dar metodologia de lucru în rezolvarea problemelor în Excel este să poți rezolva toate problemele de același tip sau cu diferite seturi de date, utilizând o singură formulă.

Felicitări Alinei și Simonei pentru soluție. Nu știu dacă este suficient pentru ele să știe că au reușit… să dea soluția corectă… pentru că trebuie să se mulțumească cu asta momentan. :)

Mulțumesc pentru implicare și sper că va plăcut!

PS. Vă provoc la o regulă de 3 simple sau la o ecuație de gradul 2 în Excel? :)

13/09/2012