Excel - Filtrari Avansate - Baze de Date

Curs
7/10 (6 voturi)
Domeniu: Birotică
Conține 1 fișier: xls
Pagini : 15 în total
Mărime: 90.47KB (arhivat)
Cost: Gratis
Profesor îndrumător / Prezentat Profesorului: Ionescu Bogdan, Glavan Nicolae
ASE BUCURESTI Probleme rezolvare: domenii: - FILTRARI AVANSATE - BAZE DE DATE

Extras din document

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.

Conținut arhivă zip

  • Excel - Filtrari Avansate - Baze de Date.xls

Alții au mai descărcat și

Publicitatea și Promovarea prin Internet

1.1 Reteaua Internet Internet-ul reprezinta mai mult decât o retea uriasa ce uneste milioane de calculatoare. El constituie o retea de retele si...

Gestiunea Activitatii la o Asciatie de Locatari Utilizand Excel 2000

CAPITOLUL 1. NOTIUNI INTRODUCTIVEUT 4CAPITOLUL 1. NOTIUNI INTRODUCTIVE In ultimele decenii, calculatoarele si-au exercitat pionieral in numeroase...

Utilizarea Aplicatiei Microsoft Office 2007 - SC Vel Pitar SA

1.Introducere Scopul proiectului Scopul acestui proiect este acela ca studenţii (indirect beneficiarii ) sa înveţe să elaboreze un proiect , sa...

Baza de Date in Excel pentru Evidenta Vanzarilor dintr-un Magazin

Se va intocmi in Excel o Baza de date pentru evidenta vanzarilor dintr-un magazin pe o perioada de 4 zile pe prima foaie de lucru ce va fi...

Proiect Birotica - TECHEM Eenergy Services SRL

I. Introducere Organizatia “Techem Energy Services” SRL face parte din Divizia Internationala a Corporatiei “Techem” Germania, care are o vechime...

Birotică Profesională

Introducere Întreprinderea de Avioane din Bacău a luat fiinţă în anul 1953 prin H.C.M. 1165/1953 sub denumirea de U.M. 03767,având drept scop...

Te-ar putea interesa și

Proiectare Baze de date în Access

Aplicatia I - BAZA DE DATE ÎN EXCEL I.1 CREAREA UNEI BAZE DE DATE Am creat 4 baze de date reprezentând situatia ?colarA a studentilor de la...

Ai nevoie de altceva?