Excel - filtrări avansate - baze de date

1. Să se calculeze data scadenţei excluzând week-end-urile şi sărbătorile legale.

Răspuns: Funcţia folosită este WORKDAY(). În sintaxa acestei funcţii, pentru că enunţul o cere în mod expres, trebuie precizată şi o plajă de celule care să cuprindă zilele de sărbătoare pe care nu trebuie să le ia în seamă în calculul datei scadente. Aşadar, s-a ales plaja de celule $C$30:$C$40.

SĂRBĂTORI LEGALE Funcţia WORKDAY( ) este o funcţie care trebuie activată astfel: Tools - > Add Ins… - >Analysis ToolPak

duminică, 01 ianuarie 2006 Selectaţi o celulă de pe plaja de celule H5:H24 şi vedeţi formula scrisă.

luni, 02 ianuarie 2006

duminică, 23 aprilie 2006 Observaţie: Dacă se cere să se calculeze data scadenţei excluzând numai week-end-urile, atunci funcţia WORKDAY va avea numai 2 parametri, şi anume: =WORKDAY(F5;G5)

luni, 24 aprilie 2006

marţi, 25 aprilie 2006

luni, 01 mai 2006

marţi, 02 mai 2006

vineri, 01 decembrie 2006

luni, 25 decembrie 2006

marţi, 26 decembrie 2006

miercuri, 27 decembrie 2006

2. Rubrica Plătit va conţine o listă derulantă, cu răspunsurile DA/NU, care va fi disponibilă pentru un cod client numeric şi non-vid

Răspuns: pentru început s-a ales o plajă de celule în care să scriem valorile din listă. Acestă plajă este: A1:C1 - 2 celule cu valorile "DA", respectiv "NU" şi o celulă vidă care va apare în listă numai dacă se încalcă condiţiile din enunţ.

Selectaţi plaja de celule J5:J24 - > Data - >Validation - > List - > Source: =IF(AND(NOT(ISBLANK(A5));ISNUMBER(A5));$A$1:$B$1;$C$1)

3. Să se formateze condiţional (Bold, albastru) acele intrări (F5:F24) unde data facturării este o zi de week-end.

Răspuns: Selectaţi plaja de celule F5:F24 - > meniul Format- > Conditional Formatting - > Formula Is: =OR(WEEKDAY(F5;2)=6;WEEKDAY(F5;2)=7)

"4. Data plăţii (plaja de celule K5:K24) va fi validată astfel:

- se completează numai dacă pe coloana ""Platit"" este valoarea ""DA""

- trebuie să fie mai mică sau egală cu data curentă şi mai mare sau egală cu data facturii"

Răspuns: selectaţi plaja de celule K5:K24 - > Data - >Validation - > Custom - > Formula: =AND(J5="DA";K5<=TODAY();K5>=F5)

"5. Să se calculeze majorările aplicate la valoarea facturii, în raport de numărul de zile întârziere, astfel:

- <=30 zile de la data scadenţei - majorare de 0,3% pe fiecare zi de întârziere;

- între 30 zile şi 90 zile – majorarea este de 0,3% pentru primele 30 de zile, iar pentru ceea ce depăşeşte 30 de zile este de 0,5% pe fiecare zi de întârziere;

- între 90 zile şi 180 zile – majorarea este de 0,3% pentru primele 30 de zile, 0,5% pentru următoarele 60 de zile, iar pentru ceea ce depăşeşte 90 de zile este de 0,7% pe fiecare zi de întârziere;

- peste 180 zile – majorarea este de 0,3% pentru primele 30 de zile, 0,5% pentru următoarele 60 de zile, 0,7% pentru următoarele 90 zile, iar pentru ceea ce depăşeşte 180 zile este de 1% pe fiecare zi de întârziere;

- dacă data scadenţei este mai mare decât data curentă, atunci majorările sunt zero pentru că nu s-a depăşit termenul de scadenţă, deci nu a început perioada de calculare a majorărilor;

- se calculează majorările indiferent dacă nu a plătit sau a plătit - în acest caz tinându-se cont de ""Data Plăţii"" - ;

- de asemenea, se va lua în calcul şi situaţia în care pe coloana ""Plătit"" să fie ""DA"", dar la ""Data Plăţii"" să nu fie scris nimic. În acest caz, utlizatorul va fi avertizat prin apariţia la majorări a textului ""EROARE"". "

Răspuns: selectaţi o celulă de pe plaja de celule L5:L24 şi vedeţi formula scrisă.

6. Să se insereze o nouă coloană în baza de date unde se vor calcula majorările de întârziere printr-o funcţie definită de utilizator, numită „Penalitati”.

"Răspuns: adăugarea unei noi coloane la sfărşitul tabelului (O4:O16) în care vom calcula majorările folosind o funţie proprie (definită de utilizator).

1. Scrierea funcţiei proprii (numită ""Penalităţi"") în editorul Visual Basic: Tools - > Macro - > Visual Basic Editor - > apare o nouă fereastră specifică Visual Basic.

Din meniul Insert al ferestrei Visual Basic se alege opţiunea Module, apare o subfereastră în care trebuie să scriem codul sursă al funcţiei noastre. Acest cod sursă este:"

Function Penalitati(Data_Scadenta, Valoare, Platit, Data_Platii)

If Data_Scadenta >= Date Then

Penalitati = 0

Else

If Platit = "DA" Then

If Data_Platii = "" Then

Penalitati = "EROARE"

Else

If Data_Platii <= Data_Scadenta Then

Penalitati = 0

Else

If (Data_Platii - Data_Scadenta) <= 30 Then

Penalitati = (Data_Platii - Data_Scadenta) * Valoare * 0.003

Else

If (Data_Platii - Data_Scadenta) <= 90 Then

Penalitati = Valoare * 30 * 0.003 + (Data_Platii - Data_Scadenta - 30) * Valoare * 0.005

Else

If (Data_Platii - Data_Scadenta) <= 180 Then

Penalitati = Valoare * 30 * 0.003 + Valoare * 60 * 0.005 + (Data_Platii - Data_Scadenta - 90) * Valoare * 0.007

Else

Penalitati = Valoare * 30 * 0.003 + Valoare * 60 * 0.005 + Valoare * 90 * 0.007 + (Data_Platii - Data_Scadenta - 180) * Valoare * 0.01

End If

End If

End If

End If

End If

Else

If (Date - Data_Scadenta) <= 30 Then

Penalitati = (Date - Data_Scadenta) * Valoare * 0.003

Else

If (Date - Data_Scadenta) <= 90 Then

Penalitati = Valoare * 30 * 0.003 + (Date - Data_Scadenta - 30) * Valoare * 0.005

Else

If (Date - Data_Scadenta) <= 180 Then

Penalitati = Valoare * 30 * 0.003 + Valoare * 60 * 0.005 + (Date - Data_Scadenta - 90) * Valoare * 0.007

Else

Penalitati = Valoare * 30 * 0.003 + Valoare * 60 * 0.005 + Valoare * 90 * 0.007 + (Date - Data_Scadenta - 180) * Valoare * 0.01

End If

End If

End If

End If

End If

End Function

2. Revenirea în foaia de calcul din Excel, selectarea celulei O5 şi scrierea următoarei formule: =PENALITATI(H5;I5;J5;K5)

7. Să se afişeze lista clienţilor care au majorări de întârziere cuprinse între 20% şi 80% din valoare (câmpul de rezultate va cuprinde rubricile: Cod Client, Nume Client, Valoare, Majorări)

Răspuns: Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter…. Fereastra de dialog ce apare impune completarea acesteia cu următoarele informaţii: Filter the list, in-place - opţiune ce trebuie bifată dacă se doreşte ca rezultatul interogării să fie plasat peste tabelul supus filtrării (opţiune nerecomandată deorece datele din tabelul de bază se vor pierde definitiv - se foloseşte numai dacă dorim în mod expres acest lucru); Copy to another location - opţiune ce trebuie bifată dacă se doreşte ca rezultatul interogării să fie plasat într-o altă zonă şi nu peste tabelul de bază (opţiune frecvent folosită) - bifarea acestei opţiuni activează zona Copy To (citiţi la ce este folosită Copy To); List Range - plaja de celule pe care se află tabelul supus interogării; Criteria Range - plaja de celule pe care am stabilit criteriile conform enunţului; Copy To - locul în care se doreşte plasarea rezultatului acestei interogări.

Altfel spus avem nevoie de:

1. tabelul supus filtrării - a cărui plajă de celule trebuie se specifică în zona List Range

2. o plajă de celule pe care să se stabilească criteriile din enunţ - plajă ce trebuie specificată în zona Criteria Range

3. precizarea locului din foaia de calcul Excel în care se doreşte plasarea rezultatului filtrării - plajă de celule ce trebuie specificată în zona Copy To

1. Tabelul supus filtrării sau interogării este $A$4:$N$24.

2. Crearea zonei de criterii: se alege o plajă de celule în care vom specifica criteriile impuse de enunţ.

CRITERII "Pentru a preciza o condiţie logică ""ŞI"" criteriile se precizează pe aceeaşi linie . Altfel spus se doreşte aflarea clienţilor care au majorări mai mari de 20% din valoarea facturii (criteriu plasat pe celula C121) şi mai mici de 80% din valoarea facturii (criteriu plasat pe celula D121).

Pentru a preciza o condiţie logică ""SAU"" criteriile se precizează pe linii diferite (de exemplu pe celula C121 prima condiţie, iar pe celula C122 cealaltă condiţie)"

TRUE FALSE

Aşadar, zona de citerii este $C$120:$D$121 - se precizează şi celula vidă de deasupra formulelor din criterii (face parte din logica realizări condiţiilor din criterii).

3. Pentru că se doreşte afişarea în tabelul rezultat numai a coloanelor "Cod Client", "Nume Client", "Valoare Factură" şi "Majorări" se impune copierea acestor denumiri de coloane din tabelul de bază într-o altă plajă de celule pe care se va afişa tabelul rezultat. S-a ales plaja de celule $R$127:$U$127.

Cod Client Nume Client Valoare Factură Majorări Orice modificare efectuată în tabelul de bază (A4:N24) nu se va reflecta şi în tabelul rezultat în urma filtrării (B127:E132 în exemplul nostru).

105 Client5 14.500.050 4.132.514

105 Client5 22.840.000 9.866.880 Verificaţi rezultatul dvs afişat pe plaja de celule R127:U127 cu rezultatul corect situat pe plaja de celule B127:E132.