Lectia 9 (O problema de repartitie cu folosirea functiei IF

Scopul lectiei

Problema de repartizare a câstigurilor

Treizeci de piese vor fi prelucrate de 3 muncitori, efectuându-se 7 operatii la fiecare piesa. Fiecare operatie are tariful ei. O operatie poate fi executata de unul, de doi sau de trei muncitori. Câti lei i se cuvin fiecarui muncitor? În final sa ajungem la Fig 9.4.

Fig 9.1 (Repartizarea lotului de piese pe operatii, lucru stabilit de maistrul respectiv)

Fig 9.2 (Transpunerea în tabel EXCEL si repartizarea pe muncitori. )

Avem costul fiecarei operatii si executantul ei. Cât i se cuvine fiecaruia? Tabelul din Fig 9.2 se va transforma în tabelul din Fig 9.3, adaugându-i-se înca 3 coloane.

Fig 9.3 (În coloanele muncitorilor sa apara sumele cuvenite)

Rezolvarea problemei de repartizare

În coloanele F, G, H trebuie introduse formule care trebuie scrise în asa fel încât prin copiere sa muncim cât mai putin si EXCEL sa faca calculele automat. Aici se foloseste semnul dolar $ cu discernamânt, în sensul ca nu e nevoie de "fixarea" unei celule, ci numai a unei coloane sau a unui rând. În final tabelul va arata ca în Fig 9.4.

Fig 9.4 (Situatia finala)

Formulele necesare sunt cele din Fig 9.5. Trebuie înteleasa folosirea semnului dolar $, care fixeaza o celula în care este numele muncitorului, pentru ca pentru în fiecare rând din coloanele F2 albu, G2 ion, H2 maria este testata celula, din coloana E, unde se afla numele lor.

Fig 9.5 (Coloanele A, B, C au fost ascunse, pentru a micsora figura)

A trebuit sa se scrie de trei ori câte o formula în F5, G5 si H5 care a fost copiata ÎN JOS prin glisare. Procedeul este corect, dar se putea scrie altfel. Sa se scrie formula în celula F5 de asa maniera, încât prin glisare pe verticala si pe orizontala sa rezulte toate celelalte formule cu acelasi rezultat corect ca în Fig 9.5. În Fig 9.6 acest deziderat este îndeplinit.

Fig 9.6 (Coloanele A, B, C au fost ascunse, pentru a micsora figura)

Utilizatorul este cel care folosindu-se de aceste 2 reguli îsi aranjeaza de asa maniera formulele ca:

Sa nu greseasca

Sa munceasca cât mai putin (sa copieze cât mai mult prin glisare)

În continuare sunt câteva exercitii pentru exemplificarea afirmatiilor de mai sus

Fig 9.7

Pentru ca numele muncitorilor se afla în coloana E este necesar ca aceasta coloana sa ramâna fixa la copiere prin glisare pe orizontala. I se va pune semnul dolar în fata.

=IF($E5=F$4; 1*$D5; " ")

F$4 reprezinta coloana lui ion

G$4 reprezinta coloana lui albu deci e normal ca atunci când copiem, prin glisare pe orizontala, coloana sa gliseze, dar rândul sa ramâna fix pentru ca numele muncitorilor în partea de repartitie a sumelor de bani, adica partea dreapta din Fig 9.4 se afla pe rândul 4!

1*$D5 înseamna copierea valorii existente în coloana D (valoarea tarifare a operatiei înmultita cu numarul de piese pe care le-a prelucrat un muncitor, la un moment dat).

Nu era nevoie sa se scrie 1*$D5 era suficient si $D5.

Alta problema care cere IF cu doua conditii

În Fig 9.9 avem doua moduri de calcul a situatiei scolare. Acelasi elev elev 02 în coloana F apare "PROMOVAT" iar coloana G apare "corigent".

Corect el, este "corigent" pentru ca are la o materie nota 3.

Fig 9.8

În Fig 9.9 avem functia scrisa gresit din punct de vere logic pentru ca semantic este corecta, iar în Fig 9.10 avem functia scrisa corect si semnatic si logic.

În Fig 9.10 avem un IF cu doua conditii.

Observati aici o alta forma a functiei IF, atunci când conditia are 2 parti[1] adica în acelasi timp si nota din C3 si cea din D3 sunt testate daca sunt mai mari sau egale cu 5

Un elev nu poate fi considerat promovat, daca are o corigenta.

În Fig 9.8 elevul 02 apare ca promovat, chiar daca are nota 3 la mat. Logic numai formulele din Fig 9.10 sunt corecte! Urmariti comparativ formulele din cele doua figuri (Fig 9.9 si Fig 9.10), pentru ca sa vedeti diferentele.

Retineti faptul ca exista doua forme de scriere a functiei IF când avem o conditie complexa, asa numitele conditii compuse:

Fig 9.11

Fig 9.12

Observatii:

Caracterul nu exista pe tastatura (ca simbol, exista numai pentru WORD). El nu este recunoscut de EXCEL. Acest caracter îl putem folosi în texte tehnoredactate, dar nu în formule.

Acest lucru este valabil pentru multe alte limbaje de programare. Pentru a arata relatia "mai mic sau egal" scriem < = .

Ordinea celor doua caractere < si = este stricta. Ele nu pot fi inversate. La fel si în cazul în care folosim > si =

Semnul * (asterisc) din a doua formula din fata lui AND nu este separator si nici semnul înmultirii. El marcheaza faptul ca AND este un operator logic.

Prima forma este cea pe care o recomand. Aici am avut doua conditii, dar puteam avea mai multe si folosirea celei de a doua forme devine într-adevar greoaie.

Câte paranteze sunt deschise "(" tot atâtea trebuie sa fie si închise ")".

Functia IF este într-adevar greoaie. Problemele pe care le vom rezolva cu EXCEL trebuie de asa maniera gândite, încât sa rezulte functii IF cât mai putine si cât mai simple. Nu este imposibil.

Posibile greseli la folosirea functiei IF

Propunem urmatoarea problema: într-un depozit exista numai 3 coduri: 101, 102 si 103! Tastarea unui cod din cele 3 în coloana B duce automat la afisarea denumirii corespunzatoare în coloana C. Cât timp nu se greseste lucrurile sunt bune! Dar când se greseste si se tasteaza cod = 104 apare denumirea "Bere" ca si când s-ar fi tastat 103. Tastarea oricarui cod mai mare decât 103 va provoca aceiasi eroare. Greseala este de logica. Functia IF nu a fost corect scrisa pentru acest caz! Atentie! Ea este corecta sintactic, nu este corecta logic.

Aici este greseala. Acest al doilea IF nu poate face mai mult.

Ce cauta Bere aici ? Codul berei este 103.

Fig 9.13

S-a strecurat o greseala de logica în alcatuirea generala a functiei IF. S-a pornit de la ideea gresita:

• daca codul este 101 denumirea este Faina,

• daca codul este 102 denumirea este Malai,
• în caz contrar codul este 103 deci denumirea este Bere. Aici este greseala!

Cine garanteaza ca nefiind codul nici 101, nici 102 este automat 103 ? Codul poate fi 104 sau orice numar mai mare decât 103. Functia IF asa cum, gresit a fost construita, pentru orice cod mai mare de 103 afiseaza Bere. În schimb, daca modificam functia IF ca în Fig 9.14 prin introducerea a înca unui IF, în momentul în care se tasteaza un cod mai mare ca 103, apare mesajul "EROARE", ca în Fig 9.15.

Analizati aceasta a doua forma a functiei IF care are înca doua IF-uri imbricate si vedeti ca este aidoma schemei din Fig 8.5.

Fig 9.14

Observati faptul ca nu este nevoie ca acele coduri sa fie în ordine crescatoare, chiar daca asa se obisnuieste.

Fig 9.15 (Codul eronat este semnalat, indiferent de ordinea codurilor)

Daca am avea mai multe marfuri ar trebui construita o functie IF imbricata destul de greoaie, chiar si pentru cine este obisnuit cu programarea.

Exemplu:

Dar nu putem avea decât 7 functii IF imbricate (cuprinse una în cealalta).

Aceste IF-uri imbricate trebuie evitate. Gestiunile chiar cele mai mici au sute de marfuri si limita de 7 IF-uri imbricate face imposibila rezolvarea problemei. Rezolvarea în Lectia 10.

O problema mai complicata

În Fig 9.16 este o lista de livrari, unde denumirea produsului si pretul produsului, apar automat atunci când tastam codul. Nu este nevoie sa le mai tastam! Dar pentru asta trebuie sa fie o lista de tip nomenclator, cum este cea din coloanele I, J, K unde fiecare produs este trecut cu 3 elemente: cod, denumire si pret.

Fig 9.16

Fig 9.17 (Formula din Fig 9.16 celula D2)

O descriere mai "pe întelesul tuturor" a formulei de mai sus, în tabelul de mai jos:

Fig 9.18 (Explicarea formulei din celula D2 Fig 9.16)

Atentie:

Se naste întrebarea: daca codul nu este nici 111, nici 112,. nici 117, ce se întâmpla? Aici trebuia sa mai fie o ramura care sa afiseze "EROARE". Am evitat scrierea acestei ramuri, pentru ca am folosit asa numita VALIDARE[4], operatie tratata pe larg în lectia urmatoare.

Când se tasteaza codul 121 apare mesajul din Fig 9.19

Lista din Fig 9.16 este o lista bazata pe un nomenclator. Nomenclatorul fiind mica lista din coloanele I, J, K, mai precis domeniul $I$1:$K$8. Avantajul prim este acela ca nu se mai tasteaza denumirea produsului si pretul sau. De aici decurge al doilea avantaj: nu se mai greseste. Daca admitem ca s-a gresit pretul unui produs, se face corectia în nomenclator si se va modifica pretul acelui produs, ori unde apare într-o lista gata facuta. La fel si pentru denumire.

Exista situatii când în nomenclator apar mai multe informatii. În nomenclator se poate gasi pe lânga cod:

Totul depinde de cum gândim problema.