excel - filtrari avansate - baze de date.xls

DA NU

CENTRALIZATORUL FACTURILOR EMISE

Localitate Client Adresa Nr factură Data facturii Data scadenţei Plătit Data plăţii Majorări

101 Client1 Constanta Str. A 456853 Wednesday, 24 November 2004 20 zile Err:511 5,200,100 NU ### Err:511 995,300 Err:511 #VALUE! Err:511

101 Client1 Constanta Str. A 456854 Tuesday, 03 February 2004 - Tuesday, 03 February 2004 10,000,000 NU 692,800,000 1,900,000 704,700,000 #VALUE! 7,006 Zile

101 Client1 Constanta Str. A 456856 Wednesday, 24 November 2004 20 zile Wednesday, 22 December 2004 3,600,000 DA ### 0 691,246 4,291,246 #VALUE! -

101 Client1 Constanta Str. A 456858 Sunday, 16 May 2004 10 zile Friday, 28 May 2004 2,300,000 NU 156,699,000 437,000 159,436,000 #VALUE! 6,891 Zile

101 Client1 Constanta Str. A 456859 Wednesday, 23 March 2005 20 zile Wednesday, 20 April 2005 38,060,000 NU 2,468,571,600 7,231,400 2,513,863,000 #VALUE! 6,564 Zile

102 Client2 Bucuresti Str. B 456855 Monday, 21 February 2005 30 zile Monday, 04 April 2005 6,760,000 NU 439,535,200 1,284,400 447,579,600 #VALUE! 6,580 Zile

102 Client2 Bucuresti Str. B 456861 ### 15 zile Friday, 07 October 2005 23,000,000 NU 1,452,680,000 4,370,000 1,480,050,000 #VALUE! 6,394 Zile

103 Client3 Brasov Str. C 456852 Friday, 19 August 2005 15 zile Friday, 09 September 2005 14,000,000 DA ### 840,000 2,667,339 17,507,339 #VALUE! 20 Zile

103 Client3 Brasov Str. C 456857 Sunday, 15 May 2005 15 zile Friday, 03 June 2005 7,000,000 NU 450,940,000 1,330,000 459,270,000 #VALUE! 6,520 Zile

104 Client4 Cluj Str. D 456860 Tuesday, 24 May 2005 - Tuesday, 24 May 2005 9,500,000 DA ### 0 1,812,314 11,312,314 #VALUE! -

104 Client4 Cluj Str. D 456862 Friday, 21 October 2005 8 zile Wednesday, 02 November 2005 12,500,000 DA ### 0 2,382,345 14,882,345 #VALUE! -

104 Client4 Cluj Str. D 456864 Tuesday, 08 November 2005 3 zile Friday, 11 November 2005 8,650,000 DA ### 103,800 1,650,847 10,404,647 #VALUE! 4 Zile

105 Client5 Arad Str. E 456865 Friday, 09 December 2005 17 zile Wednesday, 04 January 2006 14,500,050 DA ### 4,132,514 2,762,380 21,394,944 #VALUE! 69 Zile

105 Client5 Arad Str. E 456866 Thursday, 05 January 2006 5 zile Thursday, 12 January 2006 22,840,000 DA ### 9,866,880 4,346,977 37,053,857 #VALUE! 96 Zile

105 Client5 Arad Str. E 456867 Thursday, 05 January 2006 7 zile Monday, 16 January 2006 3,600,000 NU 223,740,000 684,000 228,024,000 #VALUE! 6,293 Zile

106 Client6 Timisoara Str. F 456868 Friday, 24 February 2006 - Friday, 24 February 2006 87,325,000 NU 5,393,192,000 16,591,750 5,497,108,750 #VALUE! 6,254 Zile

106 Client6 Timisoara Str. F 456869 Saturday, 04 March 2006 9 zile Thursday, 16 March 2006 9,000,000 NU 554,040,000 1,710,000 564,750,000 #VALUE! 6,234 Zile

107 Client7 Buzau Str. G 456870 Monday, 01 May 2006 14 zile Monday, 22 May 2006 4,780,000 DA ### 0 915,572 5,695,572 #VALUE! -

107 Client7 Buzau Str. G 456871 Monday, 01 May 2006 6 zile Wednesday, 10 May 2006 1,000,000 NU 61,010,000 190,000 62,200,000 #VALUE! 6,179 Zile

107 Client7 Buzau Str. G 456863 Wednesday, 03 May 2006 30 zile Wednesday, 14 June 2006 21,000,000 NU 1,273,860,000 3,990,000 1,298,850,000 #VALUE! 6,144 Zile

SĂRBĂTORI LEGALE

Sunday, 01 January 2006

Monday, 02 January 2006

Sunday, 23 April 2006

Monday, 24 April 2006

Tuesday, 25 April 2006

Monday, 01 May 2006

Tuesday, 02 May 2006

Friday, 01 December 2006

Monday, 25 December 2006

Tuesday, 26 December 2006

Wednesday, 27 December 2006

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

Cod Client

Nume Client

Nr zile graţie

Valoare Factură

TVA (19%)

Sumadatorată

MAJORĂRIFuncţie proprie

NR. ZILE INTARZIERE

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.

Funcţia WORKDAY( ) este o funcţie care trebuie activată astfel: Tools - > Add Ins… - >Analysis ToolPakSelectaţi o celulă de pe plaja de celule H5:H24 şi vedeţi formula scrisă.

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)

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ţ.

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".

A B C D E F G H I J K L M N O P

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

Răspuns: selectaţi o celulă de pe plaja de celule L5:L24 şi vedeţi formula scrisă.A B C D E F G H I J K L M N O P

54

55

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

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:

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


56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

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)

CRITERII

Err:511 Err:511

Cod Client Nume Client Majorări

105 Client5 14,500,050 4,132,514

105 Client5 22,840,000 9,866,880

105 Client5 3,600,000 1,908,000

106 Client6 87,325,000 25,760,875

107 Client7 1,000,000 200,000

Tabelul supus filtrării (tabelul de bază)

Plaja de celule pe care se află stabilite criteriile

Plaja de celule pe care se va afişa rezultatul interogării

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ţ.

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)

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.

Valoare Factură

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).

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

Dispunem acum de toate elementele necesare completării ferestrei Advanced Filter. Aşadar, Data - > Filter - > Advanced Filter - > apare fereastra Advanced Filter. Completată această fereastră se prezintă astfel:

Din motive didactice s-a copiat tabelul rezultat de pe plaja de celule $R$127:$U$132 pe plaja de celule $B$127:$E$132, lăsând loc pentru a verifica şi dvs.ATENŢIE!!! Plasarea capului de tabel rezultat (în cazul nostru capul tabelului rezultat l-am fixat în $R$127:$U$127 - este plasat bine) într-o zonă în care mai jos există alte informaţii din foia de calcul ar putea duce, fie la ştergerea informaţiilor din foaia de calcul peste care se va suprapune tot tabelul rezultat, fie la apariţia unui mesaj de eroare care ne avertizează că nu poate şterge informaţiile din foaia de calcul Excel pentru că acestea se află pe o celulă concatenată ce depăşeste dimensiunea tuturor coloanelor tabelului rezultat (dacă celula concatenată nu este mai mare decât dimensiunea tuturor coloanelor tabelului rezultat, atunci se va face automat deconcatenare şi ştergerea conţinutului acesteia, plasând în această celulă informaţiile rezultate în urma interogării). La apariţia acestui mesaj (pentru că operaţia nu a fost executată) schimbaţi plaja de celule pe care doriţi să afişaţi tabelul rezultat în urma interogării şi reluaţi numai filtrarea. Acest mesaj se prezintă astfel:

Orice modificare adusă tabelului sursă nu se va reflecta şi în tabelul rezultat în urma interogării. Pentru a se vedea modificările făcute în tabelul de bază şi în tabelul rezultat trebuie să refaceţi filtrarea.


107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

8. Să se afişeze lista clienţilor (informaţii de identificare - Cod Client, Nume Client, Localitate Client, Adresa) cu care societatea a încheiat contracte în luna curentă. Un client va fi afişat o singură dată, indiferent de nr. facturilor încheiate cu acesta.

CRITERII

0 0

Cod Client Nume Client Adresa

107 Client7 Buzau Str. G

Tabelul supus filtrării (tabelul de bază)Plaja de celule pe care se află stabilite criteriilePlaja de celule pe care se va afişa rezultatul interogării

Răspuns: Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter.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 4. specificarea faptului că se doreşte obţinerea de informaţii unice - se bifează caseta Unique records onlyObservaţie: caseta Unique records only se poate bifa dacă nu se doreşte afişarea în tabelul rezultat a mai multor linii comune. Prin linie comună se înţelege aceleaşi valori pe mai multe linii din tabelul rezultat. ATENŢIE !!!! Se va lua în calcul toată linia tabelului rezultat. Exemplu: Din enunţ deducem că se doreşte ca tabelul rezultat să aibe numai coloanele: "Cod Client", "Nume Client", "Localitate Client" şi "Adresa", iar informaţiile cuprinse în tabelul rezultat să fie unice. Dacă în tabelul rezultat ar trebui să se afişeze de exemplu pe linia 1: la Cod Client: 101, la Nume Client: Client2, la Localitate Client: Bucureşti şi la Adresa: Str.B, iar pe linia 3: la Cod Client: 101, la Nume Client: Client2, la Localitate: Bucureşti şi la Adresa: Str.B, atunci bifarea casetei Unique Record Only va face ca în tabelul rezultat acestă linie să apară o singură dată. Dacă diferă măcar o informaţie pe cele două linii, cum ar fi de exemplu pe linia 3 să avem: la Cod Client: 101, la Nume Client: Client2, la Localitate: Bucureşti, la Adresa: Str.C, atunci cele două linii nu sunt considerate comune şi deci vor fi ambele afişate în tabelul rezultat.

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ţ.

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

3. Pentru că se doreşte afişarea în tabelul rezultat numai a coloanelor "Cod Client", "Nume Client", "Localitate Client" şi "Adresa" 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$168:$U$168.

Localitate Client


Din motive didactice s-a copiat tabelul rezultat de pe plaja de celule $R$168:$U$1688 pe plaja de celule $B$168:$E$169, lăsând loc pentru a verifica şi dvs.

ATENŢIE!!! Plasarea capului de tabel rezultat (în cazul nostru capul tabelului rezultat l-am fixat în $R$168:$U$168 - este plasat bine) într-o zonă în care mai jos există alte informaţii din foia de calcul ar putea duce, fie la ştergerea informaţiilor din foaia de calcul peste care se va suprapune tot tabelul rezultat, fie la apariţia unui mesaj de eroare care ne avertizează că nu poate şterge informaţiile din foaia de calcul Excel pentru că acestea se află pe o celulă concatenată ce depăşeste dimensiunea tuturor coloanelor tabelului rezultat (dacă celula concatenată nu este mai mare decât dimensiunea tuturor coloanelor tabelului rezultat, atunci se va face automat deconcatenare şi ştergerea conţinutului acesteia, plasând în această celulă informaţiile rezultate în urma interogării). La apariţia acestui mesaj (pentru că operaţia nu a fost executată) schimbaţi plaja de celule pe care doriţi să afişaţi tabelul rezultat în urma interogării şi reluaţi numai filtrarea. Acest mesaj se prezintă astfel:


147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

9. Să se afişeze lista facturilor emise în ultimele 6 luni, pentru care clientul a beneficiat de un termen de graţie.

CRITERII

Err:511 1

Localitate Client Adresa Nr factură Data facturii Data scadenţei Plătit Data plăţii Majorări

105 Client5 Arad Str. E 456865 Friday, 09 December 2005 17 zile Wednesday, 04 January 2006 14,500,050 DA ### 4,132,514 2,762,380 21,394,944

105 Client5 Arad Str. E 456866 Thursday, 05 January 2006 5 zile Thursday, 12 January 2006 22,840,000 DA ### 9,866,880 4,346,977 37,053,857

105 Client5 Arad Str. E 456867 Thursday, 05 January 2006 7 zile Monday, 16 January 2006 3,600,000 NU 2,386,800 684,000 6,670,800

106 Client6 Timisoara Str. F 456869 Saturday, 04 March 2006 9 zile Thursday, 16 March 2006 9,000,000 NU 2,610,000 1,710,000 13,320,000

107 Client7 Buzau Str. G 456870 Monday, 01 May 2006 14 zile Monday, 22 May 2006 4,780,000 DA ### 0 915,572 5,695,572

107 Client7 Buzau Str. G 456871 Monday, 01 May 2006 6 zile Wednesday, 10 May 2006 1,000,000 NU 45,000 190,000 1,235,000

107 Client7 Buzau Str. G 456863 Wednesday, 03 May 2006 30 zile Wednesday, 14 June 2006 21,000,000 NU 0 3,990,000 24,990,000

Tabelul supus filtrării (tabelul de bază)

Plaja de celule pe care se află stabilite criteriile

Plaja de celule pe care se va afişa rezultatul interogării

Răspuns: Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter.

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



Funcţia EDATE( ) este o funcţie care trebuie activată astfel: Tools - > Add Ins… - >Analysis ToolPak


3. Pentru că în enunţ nu se specifică exact care va fi capul de tabel al tabelului rezultat, vom preciza ca loc de unde să înceapă afişarea tabelului rezultat o celulă vidă, capul de tabel al tabelului rezultat find, de data aceasta, acelaşi cu cel al tabelului supus filtrării (se va completa automat), iar ca înregistrări vor apare numai cele care respectă condiţiile precizate în criterii. S-a ales celula $R$205.

Cod Client

Nume Client

Nr zile graţie

Valoare Factură

TVA (19%)

Sumadatorată


Din motive didactice s-a copiat tabelul rezultat de pe plaja de celule $R$205:$AE$205 pe plaja de celule $A$205:$N$214, lăsând loc pentru a verifica şi dvs.ATENŢIE!!! Plasarea începutului capului de tabel rezultat (în cazul nostru l-am fixat în $R$205 - este plasat bine) într-o zonă în care mai jos există alte informaţii din foia de calcul ar putea duce, fie la ştergerea informaţiilor din foaia de calcul peste care se va suprapune tot tabelul rezultat, fie la apariţia unui mesaj de eroare care ne avertizează că nu poate şterge informaţiile din foaia de calcul Excel pentru că acestea se află pe o celulă concatenată ce depăşeste dimensiunea tuturor coloanelor tabelului rezultat (dacă celula concatenată nu este mai mare decât dimensiunea tuturor coloanelor tabelului rezultat, atunci se va face automat deconcatenare şi ştergerea conţinutului acesteia, plasând în această celulă informaţiile rezultate în urma interogării). La apariţia acestui mesaj (pentru că operaţia nu a fost executată) schimbaţi plaja de celule pe care doriţi să afişaţi tabelul rezultat în urma interogării şi reluaţi numai filtrarea. Acest mesaj se prezintă astfel:


186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

CRITERII

1 Err:511

Nr factură Majorări

456854 74,500,000 823 Zile456858 14,490,000 708 Zile456859 115,321,800 381 Zile456855 21,564,400 397 Zile456861 30,590,000 211 Zile456857 18,130,000 337 Zile


11. Câte facturi s-au emis în zilele de week-end?

Aşadar:

CRITERII

0

Err:504

10. Să se afişeze lista facturilor neplătite la care nr. de zile de întârziere depăşeşte media acestora (câmpul de rezultate va fi: "Nr Factură", "Majorări" şi "Nr zile întârziere" - această coloana nu există în tabelul de bază).

Răspuns: Mai întâi trebuie să determinăm numărul de zile de întârziere. Pentru aceasta s-a ales plaja de celule P4:P24. Selectaţi o celulă de pe plaja de celule P5:P24 şi vedeţi formula scrisă pentru determinarea numărului de zile de întârziere.

Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter.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:$P$242. Crearea zonei de criterii: se alege o plajă de celule în care vom specifica criteriile impuse de enunţ.


3. Pentru că se doreşte afişarea în tabelul rezultat numai a coloanelor "Nr Factură", "Majorări" şi "Nr. zile de întârziere" 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$251:$T$251.

NR. ZILE INTARZIERE


Din motive didactice s-a copiat tabelul rezultat de pe plaja de celule $R$251:$T$251 pe plaja de celule $B$251:$D$257, lăsând loc pentru a verifica şi dvs.

ATENŢIE!!! Plasarea capului de tabel rezultat (în cazul nostru $R$251:$T$251 - este plasat bine) într-o zonă în care mai jos există alte informaţii din foia de calcul ar putea duce, fie la ştergerea informaţiilor din foaia de calcul peste care se va suprapune tot tabelul rezultat, fie la apariţia unui mesaj de eroare care ne avertizează că nu poate şterge informaţiile din foaia de calcul Excel pentru că acestea se află pe o celulă concatenată ce depăşeste dimensiunea tuturor coloanelor tabelului rezultat (dacă celula concatenată nu este mai mare decât dimensiunea tuturor coloanelor tabelului rezultat, atunci se va face automat deconcatenare şi ştergerea conţinutului acesteia, plasând în această celulă informaţiile rezultate în urma interogării). La apariţia acestui mesaj (pentru că operaţia nu a fost executată) schimbaţi plaja de celule pe care doriţi să afişaţi tabelul rezultat în urma interogării şi reluaţi numai filtrarea. Acest mesaj se prezintă astfel:

Răspuns: Pentru a determina câte facturi s-au emis în zilele de Week-end se foloseşte funcţia bază de date DCOUNT( ). Această funcţie bază de date are în sintaxa sa următorii parametrii: 1. plaja de celule pe care se află tabelul (baza de date) utilizat la determinarea nr. de facturi emise în Week -end 2. coloana din tabel pe care să realizeze numărarea 3. plaja de celule pe care se află criteriile specificate în enunţ

1. Plaja de celule pe care se află tabelul este: $A$4:$N$24

2. Coloana din tabel pe care se va face numărarea trebuie să fie de tip numeric. S-a ales coloana 1, dar putea fi şi 5 sau 7, etc.

3. Realizarea plajei de celule în care vom specifica criteriile din enunţ. S-a ales plaja de celule: $C$283:$C$284

Toţi parametrii funcţiei bază de date sunt acum cunoscuţi. Nu ne-a rămas decât să scriem funcţia baza de date într-o celulă. S-a ales celula B288.


231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

12. Care este factura la care s-au aplicat cele mai mari penalităţi datorită nerespectării condiţiilor contractuale?

Aşadar:

CRITERII

Err:511

Err:504

0 0 0 ###

Err:504

Răspuns: Pentru a determina care este factura cu cele mai multe penalităţi se foloseşte funcţia bază de date DGET( ). Această funcţie bază de date are în sintaxa sa următorii parametrii: 1. plaja de celule pe care se află tabelul (baza de date) utilizat la rezolvarea cerinţei din enunţ 2. coloana din tabel pe care trebuie să o returnăm 3. plaja de celule pe care se află criteriile specificate în enunţ

1. Plaja de celule pe care se află tabelul este: $A$4:$N$24

2. Coloana din tabel pe care trebuie să o returnăm este, conform enunţului, "Nr Factură", care se află pe coloana a 5 -a.

3. Realizarea plajei de celule în care vom specifica criteriile din enunţ. S-a ales plaja de celule: $C$300:$C$301

Toţi parametrii funcţiei bază de date sunt acum cunoscuţi. Nu ne-a rămas decât să scriem funcţia baza de date într-o celulă. S-a ales celula B305.

13. Care este anul în care s-au emis cele mai multe facturi, câte facturi s-au emis în acest cel mai frecvent an, care este valoarea totală a acestora şi câţi clienţi au plătit penalităţi pentru întârzieri la plata facturii? Aceste informaţii vor fi disponibile într-o celulă a foii de calcul, respectând următorul format de afişare: „În anul <X>, s-au încheiat <N> facturi, în valoare de <Y> lei, <Z> clienţi plătind penalităţi pentru nerespectarea obligaţiilor contractuale”.

Răspuns: Pentru a determina: - care este anul în care s-au emis cele mai multe facturi vom folosi funcţi MODE( ); - câte facturi s-au încheiat în anul cel mai frecvent vom folosi funcţia bază de date DCOUNT( ) - care este valoarea totală a facturilor emise în acest an vom folosi funcţia bază de date DSUM( ); - câţi clienţi au plătit penalităţi în anul cel mai frecvent folosim funcţia bază de date DCOUNT( );

CRITERII pentru funcţia bază de date Dcount( ) care determină câte facturi s-au încheiat în anul cel mai frecvent.

CRITERII pentru funcţia bază de date Dsum( ) care determină valoarea tuturor facturilor emise

în anul cel mai frecvent.

CRITERII pentru funcţia bază de date Dcount( ) care determină câţi clienţi au plătit penalităţi în

anul cel mai frecvent.

Toţi parametrii funcţiilor bază de date sunt acum cunoscuţi. Nu ne-a rămas decât să scriem funcţia baza de date într-o celulă. S-a ales celula B318


290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

CRITERII

0 0

Cod Client Nume Client Data facturii Adresa

107 Client7 01 May 2006 Str. G




Cele mai multe facturi s-au încheiat pe data de 01-05-2006.

14. Care este ziua în care s-au încheiat cele mai multe facturi în luna curentă? Formatul de afişare: "Cele mai multe facturi s-au încheiat pe data de <dd-mm-yyyy>."

Răspuns: Rezolvarea acestui enunţ impune 2 etape şi anume: A. realizarea unei interogări în care să ne fie afişate numai acele facturi emise în luna curentă. B. folosindu-ne de acest tabel rezultat în urma interogării vom determina care este ziua în care s-au încheiat cele mai multe facturi.

A. Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter.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 4. specificarea faptului că se doreşte obţinerea de informaţii unice - se bifează caseta Unique records only




3. În enunţ nu se precizează ca tabelul rezultat în urma interogării să aibă numai anumite coloane, dar pentru uşurinţă am ales coloanele "Cod Client", "Nume Client", ", "Data facturii" şi "Adresa" 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$342:$U$342.



B. Determinarea zilei în care s-au încheiat cele mai multe facturi se face cu ajutorul funcţiei MODE( ).

Ne vom folosi de tabelul rezultat în urma interogării. Alegem o celulă în care să scriem răspunsul la cerinţa aplicaţiei noastre: B365.


321

322

323

324

325

326

327

328

329

330

331

332

333

334

335

336

337

338

339

340

341

342

343

344

345

346

347

348

349

350

351

352

353

354

355

356

357

358

359

360

361

362

363

364

365

366

367

15. Să se afiseze lista facturilor neplătite pentru care s-a depăşit scadenţa cu mai mult de un an de zile (câmp de rezultate: "Nr factură", "Data scadenţei", "Plătit" şi "Majorări")

CRITERII

Plătit

NU Err:511

Nr factură Data scadenţei Plătit Majorări

456854 Tuesday, 03 February 2004 NU 74,500,000

456858 Friday, 28 May 2004 NU 14,490,000

456859 Wednesday, 20 April 2005 NU 115,321,800

456855 Monday, 04 April 2005 NU 21,564,400


Răspuns: Se va folosi filtrarea avansată: Data - > Filter - > Advanced Filter.

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ţ.

Aşadar, zona de citerii este $C$380:$D$381

3. Pentru că se doreşte afişarea în tabelul rezultat numai a coloanelor "Nr Factură", "Data Scadenţei", "Plătit" ş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$387:$U$387.




368

369

370

371

372

373

374

375

376

377

378

379

380

381

382

383

384

385

386

387

388

389

390

391

392

393

394

395

396

397

398

399

400

401

402

403

404

405

406

407

Funcţia EDATE( ) Funcţia EOMONTH( )

1/1/2006

0 Sunday ### Tuesday, January 31, 2006 Tuesday

1 Wednesday ### ### Tuesday

2 Wednesday ### Friday, March 31, 2006 Friday

3 Saturday Saturday, April 1, 2006 Sunday, April 30, 2006 Sunday

4 Monday Monday, May 1, 2006 Wednesday, May 31, 2006 Wednesday

5 Thursday ### Friday, June 30, 2006 Friday

6 Saturday Saturday, July 1, 2006 Monday, July 31, 2006 Monday

7 Tuesday ### Thursday, August 31, 2006 Thursday

8 Friday ### ### Saturday

9 Sunday ### Tuesday, October 31, 2006 Tuesday

10 Wednesday ### ### Thursday

11 Friday ### ### Sunday

Aceeaşi funcţie, dar formatări de celule diferite. Aceeaşi funcţie, dar formatări de celule diferite.

Prima zi a anului curent.

16. Să se genereze pentru anul curent, serii de date referitoare la zilele din săptămână corespunzătoare începutului, respectiv sfârşitului de lună. Altfel spus: în ce zi din săptămâna (Luni sau Marţi sau Miercuri, sau .....etc) cade fiecare zi de 1 a lunii (1 ianuarie 2006, 1 februarie 2006, .....etc) şi fiecare zi de sfârşit de lună (31 ianuarie, 28 sau 29 februarie, 31 martie, 30 aprilie, .....etc).

Răspuns: Ø într-o celulă se va scrie prima zi din anul curent (s-a ales celula $B$413). Ø pe o altă plajă de celule se vor scrie în cifre cele 12 luni ale anului începând cu cifra 0 (zero), deoarece se doreşte şi aflarea zilei din săptămână a datei de 1 ianuarie 2006 (EDATE( )), respectiv 31 ianuarie 2006 (EOMONTH( )). Valoarea 0 (zero) în cadrul funcţiei EDATE( ) va determina data calendaristică existentă peste 0 (zero) luni - ca rezultat va fi aceeaşi dată precizată în celula $B$413 - , iar în cadrul funcţiei EOMONTH( ) care este ultima zi a lunii ce rezultă sărind peste 0 (zero) luni - ca rezultat va fi 31 ianuarie 2006. S-a ales ca plaja de celule în care vom scrie, în cifre, cele 12 luni (începând cu cifra 0 (zero)) B414:B425.

Ø scrierea formulelor funcţiilor EDATE( ) şi EOMONTH( ). S-a ales celula C414 pentru EDATE( ) - şi F415 pentru EOMONTH( ). Pentru restul valorilor, formulele au fost copiate.

Ziua din săptămână

Verificarea datei calendaristice

Verificarea datei

calendaristice

Ziua din săptămână


408

409

410

411

412

413

414

415

416

417

418

419

420

421

422

423

424

425426427428

429

430

431

DA NU

CENTRALIZATORUL FACTURILOR EMISE

Adresa Nr factură Data facturii Data scadenţei Plătit Data plăţii Majorări

101 Client1 Constanta Str. A 456853 Wednesday, 24 November 2004 20 zile Wednesday, 22 December 2004 5,200,100 DA ### 0 995,300 6,195,400 #VALUE! -

101 Client1 Constanta Str. A 456854 Tuesday, 03 February 2004 - Tuesday, 03 February 2004 10,000,000 NU 692,800,000 1,900,000 704,700,000 #VALUE! 7,006 Zile

101 Client1 Constanta Str. A 456856 Monday, 26 April 2004 20 zile Monday, 24 May 2004 3,600,000 DA ### 64,800 691,246 4,356,046 #VALUE! 6 Zile

101 Client1 Constanta Str. A 456858 Sunday, 16 May 2004 10 zile Friday, 28 May 2004 2,300,000 NU 156,699,000 437,000 159,436,000 #VALUE! 6,891 Zile

101 Client1 Constanta Str. A 456859 Wednesday, 23 March 2005 20 zile Wednesday, 20 April 2005 38,060,000 NU 2,468,571,600 7,231,400 2,513,863,000 #VALUE! 6,564 Zile

102 Client2 Bucuresti Str. B 456855 Monday, 21 February 2005 30 zile Monday, 04 April 2005 6,760,000 NU 439,535,200 1,284,400 447,579,600 #VALUE! 6,580 Zile

102 Client2 Bucuresti Str. B 456861 ### 15 zile Friday, 07 October 2005 23,000,000 NU 1,452,680,000 4,370,000 1,480,050,000 #VALUE! 6,394 Zile

103 Client3 Brasov Str. C 456852 Friday, 19 August 2005 15 zile Friday, 09 September 2005 14,000,000 DA ### 840,000 2,667,339 17,507,339 #VALUE! 20 Zile

103 Client3 Brasov Str. C 456857 Sunday, 15 May 2005 15 zile Friday, 03 June 2005 7,000,000 NU 450,940,000 1,330,000 459,270,000 #VALUE! 6,520 Zile

104 Client4 Cluj Str. D 456860 Tuesday, 24 May 2005 - Tuesday, 24 May 2005 9,500,000 DA ### 0 1,812,314 11,312,314 #VALUE! -

104 Client4 Cluj Str. D 456862 Friday, 21 October 2005 8 zile Wednesday, 02 November 2005 12,500,000 DA ### 0 2,382,345 14,882,345 #VALUE! -

104 Client4 Cluj Str. D 456864 Tuesday, 08 November 2005 3 zile Friday, 11 November 2005 8,650,000 DA ### 103,800 1,650,847 10,404,647 #VALUE! 4 Zile

105 Client5 Arad Str. E 456865 Friday, 09 December 2005 17 zile Tuesday, 03 January 2006 14,500,050 DA ### 4,205,015 2,762,380 21,467,444 #VALUE! 70 Zile

105 Client5 Arad Str. E 456866 Thursday, 05 January 2006 5 zile Thursday, 12 January 2006 22,840,000 DA ### 9,866,880 4,346,977 37,053,857 #VALUE! 96 Zile

105 Client5 Arad Str. E 456867 Thursday, 05 January 2006 7 zile Monday, 16 January 2006 3,600,000 NU 223,740,000 684,000 228,024,000 #VALUE! 6,293 Zile

106 Client6 Timisoara Str. F 456868 Friday, 24 February 2006 - Friday, 24 February 2006 87,325,000 NU 5,393,192,000 16,591,750 5,497,108,750 #VALUE! 6,254 Zile

106 Client6 Timisoara Str. F 456869 Saturday, 04 March 2006 9 zile Thursday, 16 March 2006 9,000,000 NU 554,040,000 1,710,000 564,750,000 #VALUE! 6,234 Zile

107 Client7 Buzau Str. G 456870 Monday, 01 May 2006 14 zile Friday, 19 May 2006 4,780,000 DA ### 0 915,572 5,695,572 #VALUE! -

107 Client7 Buzau Str. G 456871 Monday, 01 May 2006 6 zile Tuesday, 09 May 2006 1,000,000 NU 61,020,000 190,000 62,210,000 #VALUE! 6,180 Zile

107 Client7 Buzau Str. G 456863 Wednesday, 03 May 2006 30 zile Wednesday, 14 June 2006 21,000,000 NU 1,273,860,000 3,990,000 1,298,850,000 #VALUE! 6,144 Zile

CERINŢE FORMULA CRITERIISuma valorică a facturilor încasate de la clienţii din Constanta. Err:504

1 1

Maximul valoric al facturilor neîncasate din Brasov. Err:504

0 0

Minimul valoric al facturilor emise de "Client1". Err:504

1

-

0

Err:504

0 0 00 0 0

Câte zile de graţie s-au acordat pentru factura numărul 456.855? Err:504

0

Err:504

1 0

Nu sunt date în tabel!!!

0 0 Err:511

Câte facturi devin scadente în trimestrul al doilea al anului curent? Err:504

0

Cod Client

Nume Client

Localitate Client

Nr zile graţie

Valoare Factură

TVA (19%)

Sumadatorată

MAJORĂRIFuncţie proprie

NR. ZILE INTARZIERE

Media facturilor emise şi neîncasate. În cazul în care nici o înregistrare nu îndeplineşte criteriile enunţate, mesajul de eroare #DIV/0! va fi înlocuit cu cratima.

Numărul facturilor emise şi neîncasate de la clienţii din Cluj şi Brasov în anul precedent.

Care este suma datorată aferentă facturilor încasate pe ultimele 4 luni?

Media valorică a facturilor neplătite de clientii din Bucuresti în ultima decadă a lunii precedente.

Observaţie: În foaia de calcul "INTEROGĂRI" se mai găsesc şi alte aplicaţii ce presupun utilizarea funcţiilor baze de date. În acest sens vezi cerinţele: 11, 12, 13.

A B C D E F G H I J K L M N O P1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

excel - filtrari avansate - baze de date.xls

Documents