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.

”Educația este o descoperire progresivă a propriei noastre ignoranțe!” (Will Durant)

Au trecut câțiva ani de la ultima postare din această categorie. De ce nu am mai scris? Poate pentru că nu am mai avut motivația necesară, sau inspirația, sau poate pur și simplu am considerat că orice experiment poate avea un final. Nu neg, că undeva într-un colț al minții am început să am îndoieli cu privire la capacitatea studenților de a fi altfel de la generație la generație și aceea că aș mai putea fi surprins cu ceva: toți vor să treacă examenele indiferent de notă sau nu și puțini chiar ar vrea să rămână cu ceva în cap după. Și atunci de ce să-ți mai bați capul? Păguboasă gândire… care-i duce pe mulți în rutina de a nu mai face nimic. Și ce-i mai rău pentru o instituție academică decât a veni la Universitate ca la Fabrică?

În drumul decadent al delăsării, sunt unii mai norocoși care se întâlnesc întâmplător, sau știu să ceară, să primească și să accepte un feedback corect.

Revenind, cred ca de la o generație la alta studenții sunt la fel de valoroși. Există o serie de diferențe culturale, absolut normale, o serie de diferențe de motivație… normale cred… și cel mai important o diferență de vârstă. Suntem tentați să ne ”judecăm”, ”clasificăm” și ”etichetăm” studenții prin prisma propriilor noastre valori și, mai rău de atât, prin raportare la un context economico-social diferit (din trecut). Cantitatea de neuroni nu scade odată cu timpul, statistic ea chiar crește. Accepțiunea generală conform căreia studenții nu mai citesc, nu mai sunt curioși, nu aprofundează pe un domeniu, nu combat argumentat, cred că ar trebui să ne dea de gândit că, în fapt, ei sunt victimele sistemului creat, întreținut și propovăduit de majoritatea dintre noi, în timp, punctual sau în general.

Rar îi este dat unui profesor să vadă acea lumină a înțelegerii în ochii studentului, pentru că, de cele mai multe ori nu o caută. Dar dacă o vedem, e mare păcat să nu dăm o șansă. Și astfel am ajuns la cele de mai jos…

Context: Evaluare de la distanță.

Obiectiv: Evaluare corectă a cunoștințelor pentru două capitole de curs (unul recapitulativ și unul nou).

Număr studenți: >100
Timp alocat: 60 minute
Interval orar de susținere: 21:00 cu ultima intrare la 23:00.
Riscuri potențiale:
– căutarea și găsirea răspunsurilor în suportul de curs;
– lucru în grupuri pentru răspuns mai exact;
Format întrebări: un singur răspuns și răspunsuri multiple. Nu există întrebări fără răspuns și nici întrebări cu toate variantele de răspuns valabile. All or nothing – dacă la întrebările multiple nu au bifat toate variantele corecte întrebarea nu era luată în calcul.
Bază de întrebări: >200
Formatul de alocare a întrebărilor: aleator din baza de întrebări.

și acum bomba…

Numărul de întrebări per test: 100!!!

Conform celor mai bune practici, nu trebuie să aloci mai puțin de 1 minut per întrebare. Există examene profesionale în care se poate merge și până la o întrebare la 30 de secunde, dar acelea sunt teste la un alt nivel decât cel așteptat în cadrul primului ciclu de studii universitare.

În discuțiile cu studenții, înainte de test, o parte dintre ei au fost surprinși de numărul mare de întrebări, dar unii au luat-o ca pe o provocare, fapt care a dus la numele acestui experiment: The Challenge. Am pornit de la premisa că sigur nu vor reuși să citească toate întrebările și să mai răspundă și corect la majoritatea. În context așteptările mele erau la un maxim de 50-60 de puncte, având în vedere că pe aceleași format de întrebări alocam doar 30 în 60 de minute la alte examene. Fără a face public, având drept justificare natura experimentală a examenului, decisesem deja ca top 10 indiferent de scoruri să fie avansat la punctajul maxim și celorlalți să li se mărească notele proporțional.

În final am avut surpriza să constat că mi-am subestimat studenții! Mai mult de 10 studenți au reușit să obțină un punctaj apropiat de 90.

Clopotul lui Gauss, atât de aplaudat de unii și contestat de alții, este suficient de elocvent, și nu știu de ce, încep să cred că nu e tocmai de contestat această formă de reprezentare a distribuțiilor.

La primul curs le-am mulțumit studenților pentru surpriza plăcută și i-am întrebat pe cei din Top 10, cum au reușit? Răspunsul a fost simplu: ”Am citit cursurile de 2 ori înainte!”

Scorurile mici nu m-au interesat prea mult. Ele fac parte din statistică. :)

Dacă aveți comentarii, contestații sau propuneri de îmbunătățire vă mulțumesc anticipat!

Categorie: Educație

Excel IF() – Insane Level

Update 10.03.2013 după ora 22:00

Câteva explicații teoretice:

O singură dimensiune

Două dimensiuni

Folosind algoritmul în două dimensiuni

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

Future

UPDATE 11.03.2013 după ora 9:30

Exerciții Rețele Locale de Calculatoare (video)

Exercițiul 1

Exercițiul 2

Exercițiul 3 – IIS

Experiment de e-learning: The Challenge