Excel 2010

Microsoft Office Excel 2010 Nivel Avansat

Cuprins Capitolul 1. Prezentarea programului .............................................................................................. 8 1.1 Interfaţa cu utilizatorul ................................................................................................................................. 8 1.2 Formate de fişiere în Excel 2010 ................................................................................................................ 14 1.3 Capabilitati in Excel 2010 .......................................................................................................................... 15 1.4 Redimensionarea barei de formule ............................................................................................................. 15 1.5 Folosirea sistemului de asistenţă Help ........................................................................................................ 15 Capitolul 2. Editarea şi formatarea datelor .................................................................................... 16 2.1 Introducerea numerelor, datelor, textului într-o celulă ............................................................................... 16 2.2 Formatarea datelor ...................................................................................................................................... 16 2.2.1 Formatarea datelor folosind eticheta Home ................................................................... 16 2.2.2 Formatarea rândurilor şi coloanelor ............................................................................... 17 2.2.3 Formatarea celulelor .................................................................................................... 17 2.2.4 Crearea formatelor numerice personalizate .................................................................... 21 2.3 Utilizarea funcţiilor ..................................................................................................................................... 21 2.3.1 Crearea unei formule utilizând Formula AutoComplete ................................................... 22 2.3.2 Afişarea formulelor în celule. ........................................................................................ 22 2.4 Denumirea celulelor şi domeniilor.............................................................................................................. 22 2.4.1 Crearea numelor folosind selecţii de date ...................................................................... 24 2.4.2 Vizualizarea numelor definite ........................................................................................ 24 2.4.3 Utilizarea numelor în formule ........................................................................................ 25 2.5 Referinţele celulelor.................................................................................................................................... 26 2.6 Crearea de legături ...................................................................................................................................... 27 2.6.1 Crearea unei legături între datele din aceeaşi foaie sau registru ...................................... 27 2.6.2 Consolidarea datelor din foi de calcul diferite cu ajutorul referinţelor 3D .......................... 28 2.6.3 Crearea unei legături între datele din registre diferite ..................................................... 30 2.6.4 Crearea unei legături între datele din Excel şi alt document Office................................... 31 Capitolul 3. Elemente deosebite de formatare ............................................................................... 32 3.1 Elemente de formatare ................................................................................................................................ 32 3.1.1 Imprimarea foilor de calcul .......................................................................................... 32 3.1.2 Folosirea instrumentului de „îngheţare” a rândurilor sau coloanelor ................................. 32 3.1.3 Utilizarea comentariilor ................................................................................................. 33 3.1.4 Ascunderea rândurilor şi coloanelor ............................................................................... 33 3.1.5 Ascunderea foilor de calcul ........................................................................................... 34 3.2 Opţiuni speciale de selectare (Go To)......................................................................................................... 34 3.2.1 Selectarea unor celule sau domenii denumite ................................................................ 34 3.2.2 Selectarea celulelor conţinând anumite date .................................................................. 35 3.3 Opţiuni speciale de lipire (Paste special) .................................................................................................... 37 3

3.4 Formatarea condiţionată ............................................................................................................................. 39 3.5 Noutăţi în lucrul cu tabele ........................................................................................................................... 44 3.5.1 Noul concept de tabel în Excel 2010 .............................................................................. 44 3.5.2 Formatarea unui tabel .................................................................................................. 44 3.5.3 Afişarea sau ascunderea elementelor de formatare a tabelului ........................................ 45 3.5.4 Eliminarea liniilor sau a coloanelor dintr-un tabel ........................................................... 45 3.5.5 Eliminarea liniilor duplicat dintr-un tabel ........................................................................ 46 3.5.6 Redimensionarea unui tabel ......................................................................................... 46 Capitolul 4. Grafice ...................................................................................................................... 48 4.1 Crearea şi modificarea graficelor ................................................................................................................ 48 4.1.1 Etapele creării unui grafic ............................................................................................. 48 4.1.2 Cele mai întâlnite tipuri de grafic .................................................................................. 50 4.1.2.1 Grafic sub formă de bară ........................................................................................... 51 4.1.2.2 Graficul de tip coloană ............................................................................................... 51 4.1.2.3 Graficul Pie ............................................................................................................... 52 4.1.2.4 Grafic de tip linie ....................................................................................................... 53 4.2 Modificarea graficelor ................................................................................................................................ 53 4.2.1 Adăugarea unor serii noi pe grafic ................................................................................ 53 4.2.2 Ştergerea unei serii din grafic ....................................................................................... 54 4.2.3 Comutarea între serii în grafic ....................................................................................... 55 4.2.4 Modificarea titlului, etichetei datelor, legendei si a axelor unui grafic ............................... 55 4.2.4 Adăugarea unui stil nou graficului ................................................................................. 56 4.2.5 Modificarea amplasării graficului ................................................................................... 56 4.2.6 Schimbarea culorii de umplere a unui grafic .................................................................. 56 4.2.7 Adăugarea opţiunilor bordurilor graficului: culoare, tipul liniei. ........................................ 57 4.2.8 Schimbarea culorilor graficului ...................................................................................... 57 4.2.9 Introducerea unei imagini într-un grafic 2D ................................................................... 57 4.2.10 Eliminarea fundalului zonei de date ............................................................................. 58 4.3 Copierea, mutarea graficului în acelaşi registru de calcul sau în registre de calcul diferite ...................... 58 4.4 Redimensionarea, ştergerea graficului ........................................................................................................ 58 4.5 Grafice de tip Sparklines ............................................................................................................................ 58 Capitolul 5. Funcţii ...................................................................................................................... 60 5.1 Funcţii de dată şi oră ................................................................................................................................... 60 5.1.1 Funcţia DATE ............................................................................................................... 60 5.1.2 Funcţia NOW ............................................................................................................... 60 5.1.3 Funcţia YEAR ............................................................................................................... 61 5.1.4 Funcţia MONTH ........................................................................................................... 61 5.1.5 Funcţia DAY ................................................................................................................ 61 5.1.6 Funcţia TODAY ............................................................................................................ 62 5.1.7 Functia TIME() ............................................................................................................ 62 5.1.8 Funcţia NETWORKDAYS ............................................................................................... 62 4

5.1.9 Funcţia EDATE ............................................................................................................. 64 5.1.10 Funcţia EOMONTH ..................................................................................................... 64 5.1.11 Funcţia WORKDAY ..................................................................................................... 65 5.1.12 Funcţia WEEKNUM ..................................................................................................... 66 5.2 Funcţii matematice si statistice ................................................................................................................... 67 5.2.1 Funcţia ROUND............................................................................................................ 67 5.2.2 Funcţia SUMPRODUCT ................................................................................................. 68 5.2.3 Funcţia PRODUCT ........................................................................................................ 68 5.2.4 Funcţia SUMIF ............................................................................................................. 69 5.2.5 Funcţia SUMIFS ........................................................................................................... 69 5.2.6 Funcţia AVERAGEIF ...................................................................................................... 70 5.2.7 Funcţia AVERAGEIFS .................................................................................................... 70 5.2.8 Funcţia COUNTIF ......................................................................................................... 71 5.2.9 Funţia COUNTIFS ......................................................................................................... 71 5.4 Funcţii de text ............................................................................................................................................. 71 5.4.1 Funcţia CONCATENATE ................................................................................................ 71 5.4.2 Funcţia LEFT ............................................................................................................... 72 5.4.3 Funcţia RIGHT ............................................................................................................. 72 5.4.4 Funcţia MID ................................................................................................................. 72 5.4.5 Funcţia UPPER ............................................................................................................. 73 5.4.6 Funcţia PROPER ........................................................................................................... 73 5.4.7 Funcţia LOWER ............................................................................................................ 73 5.5 Funcţii logice .............................................................................................................................................. 74 5.5.1 Funcţia IF .................................................................................................................... 74 5.5.2 Funcţia AND ................................................................................................................ 74 5.5.3 Funcţia OR .................................................................................................................. 75 5.6 Funcţii de căutare şi referinţă...................................................................................................................... 75 5.6.1 Funcţia VLOOKUP ........................................................................................................ 75 5.6.2 Funcţia HLOOKUP ........................................................................................................ 76 5.7 Funcţii de baze de date ............................................................................................................................... 77 5.7.1 Funcţia DCOUNT .......................................................................................................... 77 5.7.2 Funcţia DSUM .............................................................................................................. 77 5.7.3 Funcţia DCOUNTA ........................................................................................................ 78 5.7.4 Funcţia DAVERAGE ...................................................................................................... 78 5.7.5 Funcţia DMAX .............................................................................................................. 78 5.7.6 Funcţia DMIN .............................................................................................................. 78 5.7.7 Funcţia DGET .............................................................................................................. 78 5.8 Funcţii financiare ........................................................................................................................................ 78 5.8.1 Funcţia PMT ................................................................................................................ 78 5.8.2 Funcţia FV ................................................................................................................... 79 5.8.3

Funcţia PV ................................................................................................................ 79 5

5.8.4

Funcţia RATE ............................................................................................................ 80

5.8.5

Funcţia NPV ............................................................................................................. 80

Capitolul 6. Manipularea datelor ................................................................................................... 82 6.1 Sortarea datelor ........................................................................................................................................... 82 6.2 Filtrarea datelor........................................................................................................................................... 84 6.2.1

Filtre noi pe date calendaristice ................................................................................. 84

6.2.2

Filtre noi pe text ....................................................................................................... 85

6.2.3 Filtre noi pe numere ..................................................................................................... 86 6.3 Protejarea informaţiilor ............................................................................................................................... 86 6.3.1 Protecţia la nivel de fişier ............................................................................................. 86 6.3.2 Protecţia la nivel de foaie de calcul ............................................................................... 88 6.3.3 Protecţia la nivel de registru ......................................................................................... 89 6.4 Validarea şi auditul datelor ......................................................................................................................... 89 6.4.1 Validarea datelor ......................................................................................................... 90 6.4.2 Auditul unei foi de calcul .............................................................................................. 93 6.5 Scenarii ....................................................................................................................................................... 94 6.5.1 Crearea scenariilor ....................................................................................................... 94 6.5.2 Afişarea unui scenariu .................................................................................................. 96 6.5.3 Editarea unui scenariu .................................................................................................. 96 6.5.4 Rapoarte rezumative de scenariu .................................................................................. 96 6.5.5 Îmbinarea scenariilor din diferite foi de lucru ................................................................. 97 6.5.5 Ştergerea unui scenariu ............................................................................................... 98 6.6 Metode de prognoză a valorilor utilizând analiza de tip „what-if” ............................................................. 98 6.6.1 Soluţionarea problemelor cu o singură variabilă (GOAL SEEK) ......................................... 98 6.7 Partajarea fisierelor ..................................................................................................................................... 99 6.7.1 Partajarea in retea ....................................................................................................... 99 6.7.1.1 Urmarirea modificarilor ............................................................................................ 100 6.7.2 Partajarea pe Net ...................................................................................................... 101 6.7.3 Caracteristici care nu sunt acceptate într-un registru de lucru partajat .......................... 102 Capitolul 7. Tehnici de sinteză.................................................................................................... 105 7.1 Subtotalurile.............................................................................................................................................. 105 7.2 Consolidări................................................................................................................................................ 106 7.3 Analiza datelor folosind tabelele pivot ..................................................................................................... 107 7.3.1 Crearea tabelelor pivot ............................................................................................... 107 7.3.2 Formatarea tabelelor pivot ......................................................................................... 113 7.3.3 Filtrarea datelor din tabelele pivot ............................................................................... 114 7.3.4 Gruparea datelor în tabelele pivot ............................................................................... 114 7.3.5 Grafice obtinute din tabele pivot ................................................................................. 115 7.3.6 Slicere ....................................................................................................................... 115 Capitolul 8. Utilizarea şabloanelor............................................................................................... 118 Capitolul 9. Lucrul cu macrocomenzi .......................................................................................... 120 6

9.1 Generalităţi legate de macrocomenzi ........................................................................................................ 120 9.5 Înregistrarea unei macro-comenzi............................................................................................................. 121 9.7 Modificarea unei macrocomenzi .............................................................................................................. 123 9.8 Ştergerea unei macro-comenzi.................................................................................................................. 123 Anexa 1. Caracteristici care nu sunt acceptate într-un registru de lucru partajat ............................ 124 Anexa 2. Shortcut-uri in Excel 2010 ............................................................................................ 126

7

Capitolul 1. Prezentarea programului

Microsoft Office Excel asa cum il stim astazi este urmasul primului program de calcul tabel, elaborat in urma cu mai mult de 3 decenii, numit Microsoft Multiplan. De atunci a trecut prin multe updatari si imbunatatiri, lunga perioada de folosinta facandu-l cunoscut in intreaga lume. Datorita usurintei in folosire, a devenit un standard de facto al aplicatiilor de calcul tabelar, companiile folosind experienta acumulata pe parcursul timpului pentru mentinerea lucrarilor elaborate, la zi, odata cu noile facilitati puse la dispozitie de Microsoft. Astfel se explica marele succes din zilele noastre, in multe privinte nemaiputandu-ne gandi la calcule fara ajutorul Microsoft Office Excel.

1.1 Interfaţa cu utilizatorul Interfaţa programului Microsoft Office Excel 2010 se prezintă astfel:

În această fereastră avem: • Bara de titlu - afişează numele programului “Microsoft Excel”. Alături de numele programului poate fi afişat numele documentului activ (ex. Book1). • Banda sau panglica (Ribbon) - conţine numele etichetelor derulante, disponibile. Aceste file sunt grupate pe categorii, în funcţie de ceea ce se doreşte să se realizeze. Aceste categorii se numesc grupuri şi conţin o listă de comenzi. • Bara pentru formule - specifică programului Excel, plasată sub lista de etichete, din partea de sus a ferestrei Excel are un rol important în procesul de introducere, editare şi editare date

• • • • •

Butonul File (Back Stage ) – specific Microsoft 2010 conţine o listă de comenzi pentru salvare, tipărire, deschidere etc. Bara de acces rapid: conţine butoanele cel mai des utilizate și stabilite de utilizator Fereastra document - ocupă cea mai mare parte a ecranului. Este spaţiul în care se lucrează efectiv cu informaţiile specifice Excel (informaţii grupate într-o mapă Excel). Bara de stare – se află în partea de jos a ferestrei Excel şi conţine: în partea stângă texte explicative privitoare la comanda selectată sau acţiunea în curs (modul Ready, modul Enter şi modul Edit). Indicatorul de celulă activă 8

Butoanele de minimizare, maximizare, restaurare, închidere se află la fel ca și Excel 2003 în partea dreaptă sus. Butonul Microsoft Office Excel Help, se află lângă butoanele de minimizare, maximizare, restaurare, închidere. Etichetele de rânduri şi coloane Barele de derulare orizontală şi verticală Butoanele pentru modurile de vizualizare se află în partea dreaptă jos. Panoramarea (Zoom) se află lângă butoanele pentru modurile de vizualizare.

• • • • • •

Meniul File apare atunci când executăm clic pe butonul File, situat în colţul din stânga – sus al aplicaţiei. Aici vom găsi comenzile care înainte se aflau în meniul File şi anume: New, Open, Save, Save As..., Print, şi Close. Pe lângă aceste comenzi, în meniul File apar comenzi noi: Save & Send, Info, Recent, Help, Option, Exit. Acest tip de vizualizare se numeste BackStage, si este specifica pentru Office 2010. Comanda Info ne permite sa vedem informatii referitoare la fisier, proprietati, inspectarea documentului, etc. -

-

-

Protect Workbook o Mark as Final – fisierul se salveaza si nu mai poate fi editat. Va avea un indicator in bara de stare o Encript with Password – fisierul va fi inaccesibil in deschidere fara a cunoaste parola o Protect Current Sheet – se interzice accesul la celulele protejate cu parola, se pot seta diverse actiuni ce pot fi protejate o Protect Structure and Windows – se protejeaza modificarea structurii de foi si/sau a modului in care se deschide registrul o Restricted Permision by People – pemite restrictionarea accesului la fisier pe baza unei adrese de e-mail a celui care va avea permisiuni de editare o Add a Digital Signature –folosita la semnarea digitala a unui document excel, cu o semnatura deja existenta sau obtinuta de la partenerii Microsoft. Check for Issues o Inspect Document – permite vizualizarea informatiilor personale, a informatiilor ascunse sau invizibile; nu poate accesa informatii dintr-un fisier partajat o Check Accesibility – verifica daca exista informatii care nu pot fi citite de persoane cu dificultati de citire o Check Compatibility – verifica daca poate fi salvat in versiunile anterioare Versions o Prezinta o lista a versiunilor anterioare, salvate cu auto-save.

Comnada Recent ne faciliteaza accesul rapid la ultimele fisiere prelucrate cu Excel precum si la ultimele foldere prin care am trecut, Atat fisierele cat si folderele pot fi prinse in pioneze, astefel incat sa fie in varful listei si sa fie mai usor gasite si accesate. Lista se actualizeaza continuu si poate fi golita.

9

Comanda New ne permite sa cream un registru nou, fie gol fie dintr-un template, furnizat de Microsoft sau creat de utilizator.

Comanda Print reuneste mai multe facilitati printre care cea mai importanta este Preview, paginat. De aici se poate alege imprimanta si caracteristicile ei, avem acces la Page Setup, la preferinte de imprimare.

10

Comanda Save&Send permite salvarea sau trimiterea documentului intr-un cont de e-mail. Salvarea se face in formatul implicit sau in unul din formatele disponibile, inclusiv .pdf.

-

Trimiterea ca attachment la un e-mail se poate face in mai multe formate, inclusiv .pdf Salvarea pe Web se face pentru ca fisierul sa fie disponibil si altor persoane, intr-un cont de WindowsLive. 11

-

Salvarea intr-un folder special de tip SharePoint, unde poate fi ulterior accesat de mai multi utilizatori, pentru editarea simultana

-

Trimiterea ca atachment intr-un cont de Instant Messenger; trebuie sa avem deschis clientul de mesagerie implicit.

-

Salvarea cu un tip diferit (asemanator cu Save as...)

-

Crearea uni fisier de tip .pdf (se regaseste si in Save As...)

de

fisier

Comanda Help – ajutor pentru utilizatori in legatura cu diverse aspecte ale muncii cu Excel 2010, inclusiv ajutor online de la Microsoft si setari (similar cu File – Options), pentru incepatori sau pentru cei ce migreaza de la versiuni anterioare lui Office 2010 precum si actualizarea MS Office 2010.

12

Comanda Options seteaza diverse optiuni grupate pe mai multe categorii: - General - Formulas - Proofing - Save - Language - Advanced - Customize Ribbon - Quick Access Toolbar - Add-ins - Trust Center

Etichetele pun la dispoziţie instrumentele care ne sunt necesare în momentul respectiv. De exemplu, atunci când creăm o foaie de lucru Excel, apare eticheta Home din programul Excel. Eticheta Home permite efectuarea unor comenzi uzuale, cum ar fi alinierea, decuparea, copierea, lipirea, elemente de formatare a celulei, formatarea condiţionată, sortarea, găsirea, înlocuirea si multe alte comenzi obişnuite, folosite frecvent în cadrul unei foi de lucru. Gruparile de butoane din cadrul unei file (etichete) au un Dialog Box Launcher - săgeata din partea de jos a grupării de pe o filă, ce ne permite sa apelam ferestrele de comenzi din versiunea 2003.

Eticheta Insert ne permite crearea tabelelor pivot, inserarea pozelor, introducerea elementelor grafice din clip art şi smart art, preluarea imaginilor cu Screenshot, crearea diagramelor, introducerea hiperlink-urilor, scrierea în antet şi subsol, introducerea efectelor Word Art, inserarea simbolurilor şi a diferitelor obiecte.

Eticheta Page Layout permite adăugarea temelor, modificarea marginilor foii, orientarea foii, dimensiunea foii, opţiuni de printare.

Eticheta Formulas ne permite folosirea funcţiilor, structurate pe fiecare categorie, definirea numelor unui grup de celule sau tabel şi folosirea elementelor de audit a formulelor.

Eticheta Data. Cu ajutorul acesteia aducem în fişier date externe (texte, baze de date), putem face sortare, filtrare, transformarea textului în coloane, validarea datelor, consolidare, analiză de tip whatif, calcularea unui subtotal, grupare de date.

13

Eticheta Review permite corectarea gramaticală, adăugarea cuvintelor în dicţionar, folosirea comentariilor, protejarea foii, protejarea registrului, urmărirea modificărilor.

Eticheta View ne pune la dispoziţie modurile de vizualizare a foii, adăugarea sau ascunderea caroiajului, antetele de linii şi/sau coloane, a riglei, a barei de formule, modificarea dimensiunii de vizualizare (zoom), îngheţarea, macrouri.

Eticheta Developer este folosită pentru a automatiza activitatea folosind macrouri şi programe VBA.

În eticheta Add-Ins apar alte elemente instalate pe parcursul activităţii noastre. Tot aici avem posibilitatea sa adaugam functiuni si comenzi proprii.

1.2 Formate de fişiere în Excel 2010 În mod implicit, documentele, registrele de lucru şi prezentările pe care le creăm în Office 2010 sunt salvate în format XML cu noile extensii de nume de fişier care adaugă un „x” sau un „m” la extensiile cu care suntem deja familiarizaţi. Fişierele salvate utilizând sufixul implicit cu „x” (cum ar fi .docx, .xlsx sau .pptx) nu pot conţine macrocomenzi Visual Basic for Applications (VBA) sau controale ActiveX şi astfel nu implică riscurile de securitate asociate cu aceste tipuri de cod încorporat. Fişierele care au extensia numelui de fişier terminată în „m” (cum ar fi .docm și xlsm) pot conţine macrocomenzi VBA şi controale ActiveX, care sunt stocate într-o secţiune distinctă din fişier. Extensiile de nume de fişier distincte facilitează distingerea fişierelor care conţin macrocomenzi de cele care nu conţin, facilitând identificarea de către software-ul antivirus a fişierelor care conţin cod cu potenţial dăunător. Dacă vrem să salvăm un fişier ca şablon, observăm acelaşi tip de modificare. Extensia pentru şabloane are un „x” sau un „m” la final. Dacă fişierul conţine cod sau macrocomenzi, trebuie să-l salvăm utilizând noul format de fişier XML care permite macrocomenzi, format care adaugă un „m”, de la macrocomandă, la extensia numelui de fişier. În tabelul de mai jos avem extensiile pentru fiecare tip de fişier creat cu Excel 2010: Tip de fişier XML Registru de lucru Registru de lucru care permite macrocomenzi Șablon

Extensia .xlsx .xlsm .xltx 14

Șablon care permite macrocomenzi

.xltm

1.3 Capabilitati in Excel 2010 Diferenţe

Excel 2003

Excel 2007-2010

Numărul coloanelor într-o foaie de lucru

256

16.384

Numărul liniilor într-o foaie de lucru

65.536

1.048.576

Numărul diferenţelor de culori într-o foaie de lucru 56

4.3 bilioane

Câte formatări condiţionate se pot face?

3

Număr limitat de spaţiul alocat memoriei

Numărul sortărilor într-un tabel

3

64

Numărul itemilor dispuşi în lista de autoflitrare

1.024

32.768

Numărul total de caractere dispuse în celule şi care 1.024 pot fi printate

32.768

Numărul total al stilurilor de celule (unice) într-o foaie de lucru

4.000

65.536

Numărul de nivele folosind funcţiile imbricate

7

64

Numărul maxim al argumentelor în celule

30

255

Numărul total de caractere dispuse într-o celula

255

32.768

Numărul coloanelor într-un tabel pivot

255

16.384

Numărul de câmpuri afişat într-un tabel pivot

255

16.384

1.4 Redimensionarea barei de formule Bara de formule din Office Excel 2010 se poate extinde pentru formule la trei sau mai multe rânduri. Pentru aceasta putem executa clic pe butonul cu două săgeţi în jos din capătul barei de formule, prin tragere cu ajutorul mouse-ului sau prin combinaţia de taste Ctrl+Shift+U.

1.5 Folosirea sistemului de asistenţă Help În Office Excel 2010 de un real ajutor este şi funcţia Help la care putem apela prin apăsarea tastei funcţionale F1. Apare o fereastră (vezi imaginea din dreapta), în care sunt afişate principalele capitole legate de utilizarea programului Excel 2010. Dacă dorim să vizualizăm informaţii legate de un anumit capitol, îl selectăm din listă, iar dacă ne interesează o informaţie anume, o tastăm în căsuţa Search. Oricând putem trece la alt capitol prin clic pe pictograma Show table of Contents , ce ne afişează un cuprins în partea stângă cu tot ceea ce este tratat în funcţia Help.

15

Capitolul 2. Editarea ș i formatarea datelor

2.1 Introducerea numerelor, datelor, textului într-o celulă

Completarea foilor de calcul se face prin introducerea informaţiilor (datelor), în celulele foii. Informaţiile introduse pot fi:  valori (sunt date constante care nu se modifică decât prin intervenţia utilizatorului)  formule (care se recalculează automat în urma modificării datelor). Tipuri de date Valorile pe care le putem introduce în celule pot fi de 3 tipuri: texte, numere şi date calendaristice.  Textele - sunt şiruri de caractere formate din litere, cifre, semne speciale (ex. titluri de linii şi coloane, nume de persoane, adrese etc.) Textele sunt automat aliniate la stânga în celulă. În cazul textelor lungi, dacă celula din dreapta e liberă textul se revarsă peste aceasta iar dacă e ocupată va fi afişat trunchiat (dar, este memorat în întregime). Se vor putea redimensiona pentru ca informaţia să încapă.  Numerele - sunt combinaţii de cifre de la 0-9 şi caractere speciale. Ele sunt automat aliniate la dreapta în celulă. Dacă un număr nu poate fi afişat în spaţiul oferit de celulă apare ###... (deşi nr. este corect memorat, pt. a-l vizualiza este necesar să lăţim coloana). Virgula zecimală şi separatorul pentru mii se pot vizualiza/schimba din Control Panel – Regional Settings.  Datele calendaristice - exprimă date (ex. data angajării 01/15/97). Excel le tratează, de fapt, tot ca pe nişte numere. Sunt aliniate implicit la dreapta în celulă. Formate acceptate pentru introducerea datelor calendaristice (ll/zz/aa sau ll.zz.aa). Partea intreaga reprezinta data iar partea zecimala reprezinta timpul.  Recomandare: Nu este indicat să se lase coloane şi linii necompletate deoarece vor defini marginile (sfarsitul) tabelului.

2.2 Formatarea datelor 2.2.1 Formatarea datelor folosind eticheta Home Opţiunile principale de formatare a fontului se găsesc în eticheta Home, gruparea Font. Butoanele disponibile permit modificarea stilului fontului, dimensiunea, mărirea sau micşorarea fontului, îngroşarea, înclinarea, sublinierea, aplicarea bordurilor, a fundalului celulei sau a culorii textului. Din gruparea Alignment putem alege diferite tipuri de aliniere a textului, încadrarea textului într-o celulă şi îmbinarea celulelor. În gruparea Number avem elemente de formatare a unui număr. Multe dintre aceste formatări le găseam în Excel 2003 pe bara de instrumente Formatting. Exemplu de utilizare a formatării: Adăugarea bordurilor la celule: Pasul 1: Selectăm celula sau grupul de celule căruia vrem să îi adăugăm o bordură. Pasul 2: Executăm clic pe eticheta Home. Pasul 3: Executăm clic pe butonul cu săgeata Borders. Pasul 4: Selectăm o bordură din submeniu. Remarcă: 16

• • •

Pentru a aplica cea mai recent selectată bordură, executăm clic pe butonul Borders. Pentru a elimina bordurile unei celule executăm clic pe opţiunea No Border. Putem desena noi o bordură dacă selectăm una din opţiunile de desenare a bordurii.

2.2.2 Formatarea rândurilor şi coloanelor Din eticheta Home, gruparea Cells, alegem Format şi putem modifica înălţimea rândului, lăţimea coloanei, ascunderea unui rând, a unei coloane, redenumirea unei foi, ascunderea, mutarea sau copierea unei foi, etc.

2.2.3 Formatarea celulelor In eticheta Home există gruparea Number cu ajutorul căreia putem face unele formatări.

17

Cu un clic pe săgeata de la General (vezi imaginea de mai sus) ne apare lista din dreapta, cu ajutorul căreia putem formata celulele ca număr ( Numbers ), ca valoare monetară ( Currency ), ca dată scurtă ( Short Date ), ca dată lungă ( Long Date ), ca procent ( Percentage ), etc. Putem utiliza şi instrumentele de mai jos pentru a formata ca şi valoare monetară, ca procent sau pentru a pune zecimale sau a scade numarul zecimalelor.

Pentru a formata o celulă, după ce am selectat celula sau grupul de celule pe care dorim să le formatăm avem opţiunea de Format Cells. Fereastra Format Cells o putem accesa în mai multe moduri:

1.

Din eticheta Home, de pe grupările: Font, Alignment, Number folosind butoanele destinate. 2. Din eticheta Home, gruparea Cells alegem Format şi apoi Format Cells. 3. Clic dreapta pe selecţie, alegem opţiunea Format Cells.

Formatarea celulor ca dată Pasul 1: Selectăm informaţia de formatat Pasul 2: Clic pe eticheta Home, clic în dreapta grupării Numbers Pasul 3: Apare fereastra Format Cells

se alege numărul de zecimale Se alege virgula ca separator pentru mii se alege reprezentarea numerelor negative Pasul 4: Alegem Number, apoi Date, iar din Type, modul de reprezentare al datei. În Sample va apărea o mostră.

18

Formatarea celulelor pentru afişarea de simboluri monetare Pasul 1: Selectăm informaţia de formatat Pasul 2: Clic pe eticheta Home, clic în dreapta grupării Numbers Pasul 3: Apare fereastra Format Cells Pasul 4: Din Numbers alegem Currency

De aici alegem simbolul monetar

Sau după ce am selectat celulele facem clic pe butonul Currency Formatarea celulelor pentru afisarea procentelor: selectaţi celulele, facem clic pe butonul Percent Style Modificarea dimensiunii fontului, tipului fontului Pasul 1: Selectăm informaţia de formatat Pasul 2: Clic pe eticheta Home, clic în dreapta grupării Numbers Pasul 3: Apare fereastra Format Cells Pasul 4: Alegem Font

19

De aici modificăm dimensiunea fontului De aici modificăm fontul

De aici se aplică diferite tipuri de subliniere – cu linie dublă, cu linie punctată, etc

De aici se aplică stilul îngroşat, cursiv De aici se aplică diferite culori conţinutului celulelor

Aplicarea diferitelor culori conţinutului celulelor, fundalului celulelor - aplicarea unei culori fundalului celulei se realizează cu butonul Copierea formatului unei celule, grup de celule in alta celula sau grup de celule - se selectează celula sau grupul de celule al căror format dorim să-l copiem - se da clic sau dublu clic pe butonul Format Painter , după cum dorim să copiem formatul selectat o singura dsata sau de mai multe ori. Se renunta la Format Painter cu ESC. Încadrarea textului într-o celulă - funcţia Wrap text permite dimensionarea automată a celulei (pe verticala), astfel încật toate datele să fie cuprinse în celulă. Această funcţie se găseşte în meniul Alignment- Text Control din fereastra Format Cells. De asemenea se poate realiza cu manevra Alt+Enter. - functia Shrink to fit – realizeaza modificarea marimii fontului pentru ca textul sa incapa in celula - se da dublu click pe granita din dreapta a a coloanei care contine celula in care strebuie sa incapa textul – coloana se va redimensiona automat. Aliniere/Borduri - alinierea la centru, stậnga, dreapta se realizează cu butoanele respective de pe bara de formatare - alinierea sus, jos (pe verticală) se realizează apelậnd funcţia Cells a meniului Format, eticheta Alignment Centrarea unui titlu într-un grup de celule - selectăm celulele peste care se întinde titlul - clic pe butonul Merge and Center sau folosim opţiunea Merge cells din meniul AlignmentText control din fereastra Format Cells Modificarea orientării conţinutului unei celule - se selectează celula sau grupul de celule unde dorim să modificăm orientarea textului - clic pe meniul Format, opţiunea Cells, eticheta Alignment - din Orientation alegem noua orientare - clic pe OK. Textul va arăta:

20

Adăugarea bordurilor celulelor, unui grup de celule - se realizează cu ajutorul meniului Border din fereastra Format Cells

Linii despărţitoare în interiorul selecţiei

Se alege tipul liniei

Contur în jurul selecţiei

Se alege culoarea liniei

Sau o metodă mai rapidă este cu ajutorul butonului Border de pe bara de instrumente de formatare

2.2.4 Crearea formatelor numerice personalizate Textele, numerele, datele calendaristice pot fi formatate şi într-un mod particularizat. Pentru a realiza acest lucru alegem eticheta Home, clic pe colţul din dreapta al grupării Numbers. Apare caseta Format Cells, alegem Number (Număr) şi apoi categoria Custom. În această fereastră, în câmpul Type (Tip) introducem formatul dorit (de ex. data de 02.04.2009, dată calendaristică introdusă într-o celulă, scriem tipul: dddd,ddmmm-yyyy). Coduri de format: Date calendaristice: d – codul pentru ziua, m –codul pentru lună, y – codul pentru an

2.3 Utilizarea funcţiilor Funcţiile sunt formule predefinite care efectuează operaţiile cele mai frecvent folosite. Natura operaţiei efectuate depinde de numele funcţiei utilizate. Forma generală:

=Numefunctie(arg1,arg2,...,argn) atunci cand apare singura intr-o celula.

Intr-o celula cu functii si formule, caracterul

= apare o singura data, la inceput.

Aceste funcţii se introduc în alte formule prin numele lor (ex. SUM, MAX, etc.), dar fara caracterul =. De asemenea, fiecare funcţie are parametrii săi care trebuie specificaţi între paranteze. Un parametru (un argument) poate fi: o constantă, o adresă de celulă sau de domeniu, o altă funcţie. 21

SUM( )

adună valorile din celulele sau din domeniul specificat în interiorul parantezelor.

AVERAGE( )

calculează media aritmetică a valorilor din celulele sau domeniul specificat.

COUNT( )

calculează numărul valorilor.

MAX( )

determină maximul valorilor.

MIN( )

determină minimul valorilor

Pentru a construi corect funcţiile mai complicate este mai uşor să folosim Asistentul de Funcţii (Function Wizard).

2.3.1 Crearea unei formule utilizând Formula AutoComplete Pentru a reduce greşelile de scriere şi de sintaxă, putem crea şi edita formule folosind funcţia Formula AutoComplete. După ce introducem semnul egal „ =” şi începem să scriem o formulă, programul Excel afişează o listă derulantă dinamică cu funcţii valide, argumente, nume de tabele, elemente speciale (paranteze drepte, virgulă, două puncte). Pentru a insera în formulă elementul dorit apăsăm tasta Tab sau executăm dublu clic pe element.

Dacă această opţiune nu este activă, o putem activa prin următorii paşi: Pasul 1: Executăm clic pe butonul File. Pasul 2: Executăm clic pe Excel Options. Pasul 3: Alegem categoria Formulas (din panoul din stânga). Pasul 4: Selectăm caseta de validare a opţiunii Formula AutoComplete si apoi dăm clic pe Ok. Remarcă: În Excel 2010 putem imbrica funcţiile până la 64 de nivele.

2.3.2 Afişarea formulelor în celule. Pasul 1: Executăm clic pe eticheta Formulas. Pasul 2: Executăm clic pe butonul Show Formulas. Pentru a dezactiva afişarea formulelor, executăm din nou clic pe butonul Show Formulas. Mai mult: Putem tipări formulele. După ce le-am afişat cu Show Formulas urmăm paşii pentru printare.

2.4 Denumirea celulelor şi domeniilor 22

Varianta 1 – Denumirea celulelor folosind caseta Name Pentru a facilita lucrul cu domenii, programul Excel ne permite să le denumim. Putem defini un nume pe care putem să îl folosim într-o foaie de calcul sau într-un registru întreg cunoscut sub numele de domeniu. Remarca: Caseta Name din bara de formule îşi poate modifica lăţimea. Poziţionăm mouse-ul între cele două până când acesta se schimba într-o săgeată orizontală cu două capete şi apoi tragem cu mouse-ul spre stânga sau spre dreapta. Pentru denumirea unei celule sau grup de celule urmărim paşii: Pasul 1: Selectăm celula sau grupul de celule (nu neapărat una lângă alta) pe care dorim sa îl denumim. Pasul 2 : Clic în caseta Name, din bara de formule. Pasul 3: Tastăm numele dorit, care poată să conţină maxim 255 de caractere (litere, numere, semne de punctuaţie) dar nu poate să conţină spatii.

Pasul 4: Apăsăm tasta Enter. Remarca: Excel nu face diferenţa între literele mari şi literele mici. Numele utilizează referinţe absolute de celule. Exemplu: În tabelul de mai jos am denumit celula care conţinea valoarea unui euro cu numele de euro, pentru a o folosi în calcule:

Varianta 2 – Denumirea celulelor folosind opţiunea Define Name: Putem denumi o celulă sau un interval de celule apelând la eticheta Formulas, clic pe butonul

Define Name şi apare căsuţa de dialog New Name.

23

Introducem un nume de referinţă iar la Scope lăsăm workbook sau selectăm o foaie de calcul. Selecţia curentă apare în Refers to. Exemplu:

2.4.1 Crearea numelor folosind selecţii de date Putem lăsa programul Excel să denumească o celulă sau un interval. După ce am selectat celulele dăm clic pe eticheta Formulas şi apoi pe butonul Create from Selection. Selectăm caseta de validare pentru poziţia etichetelor relativ la celule. Excel încearcă automat să determine poziţia etichetelor, deci e posibil să nu fie nevoie să schimbăm opţiunile.

Exemplu. Valorile din trimestrul 1 le-am denumit cu numele care se află în coloana din stânga lor:

2.4.2 Vizualizarea numelor definite Name Manager facilitează lucrul cu toate numele definite şi numele de tabel dintr-un registru de lucru. Putem afişa valoarea şi referinţele unui nume, putem preciza domeniul unui nume la nivel de foaie de calcul sau registru de lucru, putem găsi numele cu erori şi putem vedea sau edita descrierile numelor. În plus, putem adăuga, modifica sau şterge nume, putem sorta şi filtra lista de nume. În formule, în loc de referinţă de celulă putem folosi numele de antet ale coloanelor unui tabel. În căsuţa de dialog Name Manager, putem adăuga un nume nou de la butonul New, putem schimba un nume de la butonul Edit sau putem şterge un nume cu un clic pe butonul Delete:

24

Remarca: Nu putem utiliza caseta de dialog Name Manager în timp ce edităm o celulă şi nu afişează numele definite în VBA sau numele care sunt ascunse. Pentru filtrare:

Dacă alegem Name Scoped to Worksheet obţinem ca rezultat numele locale dintr-o foaie de lucru, cu Name Scoped to Workbook numele dintr-un registru de lucru. Dacă alegem Name with Errors afişează numele cu valori care conţin erori (exemplu : #NAME, #VALUE) sau pentru a afişa numele fără erori selectăm Names without Errors. Pentru a afişa numele definite de noi sau de programul Excel selectăm Defined Names iar pentru a afişa numele de tabel selectăm Table Names.

2.4.3 Utilizarea numelor în formule În Excel 2010 putem de asemenea simplifica formulele utilizând intervale şi nume de interval. De exemplu, în tabelul următor pentru efectuarea calculului sumei, mediei, etc putem folosi denumirea de celule pentru tot trimestrul 1 sau trimestrul 2. Exemplu. Am denumit toată coloana trimestrului 1 cu Trim1 şi coloana cu datele trimestrului 2 cu Trim2. Am dat clic în celula în care vrem să efectuăm calculul sumei şi după ce am introdus formula putem scrie numele coloanei sau în cazul în care nu ştim numele coloanei, in gruparea Define Names a etichetei Formulas, avem butonul Use in Formula... alegem numele corespunzător:

de unde putem să

25

După ce am selectat Trim1, apăsăm tasta Enter şi calculul s-a efectuat.

2.5 Referinţele celulelor Adresele sau referinţele identifică celule sau grupuri de celule (domenii). Adresele spun programului Excel în care celule să caute pentru a găsi valori ce vor fi folosite în formule. Adresele sau referinţele de celule folosite în construirea formulelor sunt de mai multe tipuri: - relative ex. B5 - absolute ex. $B$5 - mixte ex. $B4 sau B$4 O adresare relativă - specifică programului Excel direcţia şi distanţa pentru a găsi locaţia. ex.: “mergi 2 celule în sus şi una la dreapta” Acest tip de referinţă spune cum să găseşti celula, pornind de la celula care conţine formula. O adresare absolută - este o adresă exactă a unei celule. Ex. $E$1 (relativ ar fi E1) − B2 => cu două celule spre stânga − $E$1=> celula de la intersecţia liniei 1 cu col. E Transformarea unei adrese relative în adresă absolută se face apăsând tasta funcţională F4. Exemplu. Diferenţa între o adresă relativă şi o adresă absolută:

26

Dacă vom copia formulele în linia următoare rezultatele vor fi diferite . Expresia = A2+A1 adună valorile de pe aceeaşi coloană din cele 2 celule de deasupra. Oriunde vom formula va face acelaşi lucru (va aduna valorile din 2 celule de deasupra de pe aceeaşi coloană). Expresia =$B$2+$B$1, în schimb, va aduna totdeauna conţinutul celulelor B2 şi B1. Concluzie: Microsoft Excel ajustează automat formulele care folosesc adrese relative, dacă le copiem în alt loc. Dacă vrem ca o adresă să nu fie modificată prin copierea formulei în altă locaţie, folosim adrese absolute. Exemple:

Folosirea referinţelor relative

2.6 Crearea de legături 2.6.1 Crearea unei legături între datele din aceeaşi foaie sau registru În cadrul unor formule avem deseori nevoie de date aflate pe aceeaşi foaie sau în acelaşi fişier. Pentru a folosi o celulă în cadrul unei formule de pe aceeaşi foaie sau fişier: Pasul 1: Pasul 2: Pasul 3: Pasul 4:

Clic pe celula destinaţie (unde dorim să avem legătura) Introducem = Clic pe celula sursă (a cărei conţinut ne interesează) Introducem operatorul dorit pentru a continua formula sau finalizăm cu Enter.

O altă variantă posibilă, în cazul în care dorim într-o celulă o legătură cu altă celulă, fără a o folosi într-o formulă, este secvenţa de comenzi Copy pe celula sursă şi în celula destinaţie alegem Paste Special, apoi clic pe butonul Paste Link. Această variantă conduce la crearea unei referinţe fixe către acea celulă. Ex: În foaia Cheltuieli avem cheltuielile pe departamente, iar în foaia Venituri – veniturile realizate de companie. În foaia Profit trebuie să calculăm profitul. Pentru a obţine veniturile folosim prima variantă, iar pentru a obţine cheltuielile pe cea de-a doua.

27

Foaia Cheltuieli

Foaia Venituri

Foaia P rofit

Foaia P rofit cu form ulele afişate

2.6.2 Consolidarea datelor din foi de calcul diferite cu ajutorul referinţelor 3D Referinţe tridimensionale (3D) se folosesc pentru accesarea datelor din alte foi de lucru ale aceluiaşi registru. Exemple. Sheet1!$A$2, Sheet1:Sheet5!A1:B10 (Nume_foaie! Referinţa) Exemplu. În registrul produse.x ls avem 3 foi de calcul I anuarie, Februarie şi Total . În foile I anuarie şi Februarie sunt datele privind valorile produselor în cele două luni. În foaia Total se adună valorile corespunzătoare din lunile I anuarie şi Februarie .

Foaia I anuarie:

28

Foaia Februarie:

Foaia Total:

Referinţe externe se folosesc pentru accesarea datelor dintr-un alt registru. ‘cale [nume_registru] nume_foaie’! referinţă unde:

cale - unitatea de disc şi calea pentru accesarea fişierului cu documentul sursă num e_registru - numele registrului Excel (fisierul cu extensia .xlsx) num e_foaie - numele documentului (foii de lucru) referinţa - numele celulei sau a domeniului de celule sursă. Exemplu: Avem 3 registre I an.x ls, Feb.x ls,Tot.x ls . În registrele I an.x ls şi Feb.x ls sunt datele privind valorile produselor în cele două luni. În registrul Tot.x ls se vor aduna valorile corespunzătoare din lunile Ianuarie şi Februarie.

Registrul I an.x ls, foaia I an

Registrul Feb.x ls, foaia Feb

29

Registrul Tot.x ls, foaia I an& Feb

Remarca: Dacă modificăm una din valorile registrelor Ian.xlsx sau Feb.xlsx, calculul totalului din foaia Tot.xlsx se modifică automat dacă registrele se află în aceeaşi locaţie.

2.6.3 Crearea unei legături între datele din registre diferite În cazul în care dorim o legătură cu o celulă din alt registru, deschidem ambele registre şi folosim una din variantele prezentate anterior. Rezultatul este o referinţă externă fixă. În cazul în care mutăm sau redenumim fişierul sursă şi alegem opţiunea de actualizare, programul Excel va afişa un mesaj de avertizare – Security Warning care ne spune că nu poate efectua actualizarea.

Dăm clic pe butonul Options şi apare caseta Microsoft Office Security Options:

Bifăm Enable this content şi apăsăm clic pe butonul Ok.

30

2.6.4 Crearea unei legături între datele din Excel şi alt document Office Varianta 1: În cazul în care dorim să avem într-un document Word, Excel sau PowerPoint o legătură către datele dintr-un fişier Excel, efectuaţi următoarele: Pasul 1: Din eticheta Insert clic pe Object Pasul 2: În fereastra Object alegem Create from File Pasul 3: Clic pe Browse şi selectăm fişierul Pasul 4: Bifăm opţiunea Link to file Pasul 5: Clic pe butonul Ok.

Veţi obţine în oricare din programele Office obiectul următor, ale cărui valori vor fi actualizate, în cazul modificărilor în fişierul Excel sursă. Actualizarea se efectuează la deschiderea fişierului sau prin clic dreapta pe obiect şi comanda Update Link. Varianta 2: În cazul în care dorim doar o valoare actualizat să o aveţi la dispoziţie în orice document Word, Excel sau Powerpoint, efectuaţi următoarele: Pasul 1: În documentul Excel daţi comanda Copy pe celula sau domeniul dorit Pasul 2: În documentul Office executăm clic dreapta şi alegem Paste special Pasul 3: Din fereastra Paste Special alegem Paste Link şi eventual Format HTML, dacă dorim ca textul să fie formatat similar cu formatarea din Excel, sau Text Unicode unformated, dacă dorim ca textul să nu fie formatat. Pasul 4: Clic pe OK. Actualizarea se efectuează la deschiderea fişierului sau prin clic dreapta pe obiect şi comanda Update Link.

31

Capitolul 3. Elemente deosebite de formatare

3.1 Elemente de formatare

3.1.1 Imprimarea foilor de calcul Toate comenzile folosite pentru imprimare sunt disponibile folosind butonul File, opţiunea Print, care include optiuni pentru alegerea imprimantei, a caracteristicilor de imprimare si ne prezinta si un Preview.

La accesarea optiunii Page Setup, apare fereastra de setare, asa cum o stiam din versiunea 2003. Aproape toate optiunile de setare sunt active, exceptand liniile si coloanele care sa se repte pe fiecare pagina, din tabul Sheet. Aceste doua optiuni sunt active doar in eticheta Page Layout – Print Titles.

3.1.2 Folosirea instrumentului de „îngheţare” a rândurilor sau coloanelor Pasul 1: Selectăm celula care se gaseste in acelasi timp in dreapta coloanelor pe care le dorim „inghetate” si sub randurile pe care dorim sa le „inghetam”, (in exemplu, F3). Optiunea este utila pentru derularea tabelelor cu multe coloane si/sau randuri.

32

Pasul 2: Executăm clic pe eticheta View. Pasul 3: Executăm clic pe butonul Freeze Panes și apoi clic pe una dintre opţiunile: Freeze Panes – îngheaţă liniile și coloanele în funcţie de selecţia curentă Freeze Top Row – îngheaţă primul rând Freeze First Column – îngheaţă prima coloană Mai mult: Pentru a anula îngheţarea, executăm clic pe butonul Freeze Panes și apoi clic pe opţiunea Unfreeze Panes

3.1.3 Utilizarea comentariilor Pentru adăugarea comentariilor, alegem din eticheta Review, gruparea Comments, New Comments. Pentru a afişa toate comentariile dăm un clic pe butonul Show All Comments, care se află tot în gruparea Comments. Pentru a şterge un comentariu este suficient un clic pe Delete, din gruparea Comments. Pentru a ne muta de la un comentariu la altul avem butoanele Preview şi Next.

3.1.4 Ascunderea rândurilor şi coloanelor Pentru a ascunde diverse informaţii, astfel încât ele să nu fie vizibile, nici tipărite se pot folosi două metode: - redimensionăm linia/coloana până când devine atât de subţire, încât practic dispare; - selectăm linia/coloana (liniile/coloanele) şi din eticheta Home, gruparea Cells executăm clic pe butonul Format şi alegem Hide Rows/Columns

33

Remarcă: Când ascundem o linie/coloană, apare o margine groasă între antetele liniilor/coloanelor, acolo unde ar trebui să apară litera sau numărul ascuns. Pentru a reafişa informaţiile ascunse avem două posibilități: 1. Selectam liniile/coloanele adiacente liniei ingrosate din antetul liniei/coloanei 2. Alegem din eticheta Home, gruparea Cells, clic pe butonul Format şi apoi alegem Unhide Rows/Columns

3.1.5 Ascunderea foilor de calcul Pentru a ascunde o foaie de calcul cu informaţii care nu dorim să fie vizibilă, alegem din eticheta Home, gruparea Cells, butonul Format şi alegem Hide Sheet. Pentru reafişarea foilor de calcul ascunse alegem din eticheta Home, gruparea Cells, butonul Format şi alegem Unhide. Va apărea o fereastră din care putem alege foaia pe care vrem să o reafişăm.

3.2 Opţiuni speciale de selectare (Go To) 3.2.1 Selectarea unor celule sau domenii denumite 34

Pasul 1: Clic pe oricare celulă din fişier şi din eticheta Home, gruparea Editing executăm clic pe săgeata de la Find&Select şi de aici alegem Go To

Pasul 2: Va apare fereastra Go To de unde selectăm numele celulei sau domeniului. Apoi executăm clic pe Ok.

3.2.2 Selectarea celulelor conţinând anumite date Pasul 1: Pentru a selecta toate celulele de acest tip din foaia de lucru activă, executăm clic într-o celulă oarecare sau selectăm zona care include tipul de celule pe care dorim să le selectăm. Pasul 2: Din eticheta Home, gruparea Editing executăm clic pe săgeata de la Find&Select şi de aici alegem Go To si apoi executăm clic pe Special sau direct Go To Special. Apare următoarea fereastră:

35

Fereastra Go To Special afişează următoarele variante:             



Pentru selectarea celulelor care conţin comentarii, alegem opţiunea Comments. Pentru selectarea celulelor care conţin constante, alegem opţiunea Constants. Pentru selectarea celulelor care conţin formule, alegem opţiunea Formulas. Pentru selectarea celulelor necompletate, alegem opţiunea Blanks. Pentru selectarea celulelor din zona curentă, alegem opţiunea Current region. Pentru selectarea celulelor din matricea curentă, alegem opţiunea Current array. Pentru selectarea obiectelor de pe foaia de lucru curentă, alegem opţiunea Objects. Pentru selectarea celulelor ce conţin valori diferite dintr-un rând sau coloană selectată, alegem opţiunea Row differences sau Column differences. Pentru selectarea celulelor precedente celulei active, alegem opţiunea Precedents. Pentru selectarea celulelor ce depind de celula activă, alegem opţiunea Dependents. Pentru a selecta ultima celulă a zonei, alegem opţiunea Last cell. Pentru a selecta numai celulele care sunt vizibile dintr-o zonă care intersectează rânduri sau coloane ascunse, alegem opţiunea Visible cells only Pentru a selecta celulele ce au formate condiţionate alegem opţiunea Conditional formats. Dacă dorim să selectaţi toate celulele cu formatare condiţionată, alegem Toate de sub Data validation, iar dacă dorim să selectaţi doar celulele cu aceeaşi formatare condiţionată cu celula/celulele selectate, atunci alegem opţiunea Same de sub Data validation. Pentru a selecta celulele ce au ataşate opţiuni de validare date, alegem opţiunea Data Validation. Dacă dorim să selectaţi toate celulele cu orice validare, alegem All, iar dacă dorim să selectaţi doar celulele cu aceeaşi validare cu celula/celulele selectate, atunci alegem opţiunea Same.

Remarcă: Unele opţiuni le avem direct din eticheta Home, gruparea Editing când executăm clic pe săgeata de la Find&Select putem alege direct selectarea formulelor (Formulas), comentariile (Comments), formatările condiţionate (Conditional Formatting), constante (Constants) sau celule ce au ataşate opţiuni de validare date cu Data Validation.

36

3.3 Opţiuni speciale de lipire (Paste special) Copierea celulelor se realizează cu comanda Copy. Lipirea cu tot cu atribute (formule, formatări sau comentarii) este realizată prin Paste. În cazul în care dorim o lipire fără formate, doar valori sau alt tip de lipire, alegem din eticheta Home, gruparea Clipboard, alegem direct Formulas (dacă vrem să copiem doar formulele), Paste Values (dacă vrem să copiem doar valorile, fără formule), Borders (dacă vrem să copiem un tabel fără bordure) sau Transpose (dacă datele copiate sunt pe rânduri/ coloane şi dorim să le lipiţi invers, pe coloane / rânduri), Paste link sau putem să alegem comanda Paste Special.

În cazul în care apelăm la Paste Special va apare următoarea fereastră:

Din fereastra Paste Special din care putem alege urmăroarele opţiuni: • Din zona Paste (Lipire) se indică ce anume se lipeşte:  All (Totală): conţinutul şi formatările celulelor. 37

          •

Formulas (Formule): doar formulele. Values (Valori): doar valurile afişate în celule. Formats (Formate): doar formatările. Comments (Comentarii): doar comentariile din celulele copiate. Validation (Validare): regulile de validare în zona selectată. All except borders (Tot exceptând borduri): totul, exceptând formatul de chenar. Column widths (Lăţimi coloane): coloana / coloanele selectate vor avea aceeaşi dimensiune cu coloanele copiate. Formulas and number formats (Formule şi formate de numere): doar formulele şi formatările celulelor copiate. Values and number formats (Valori şi formate de numere): doar valorile şi formatările celulelor copiate. All merging conditional formats: imbina formatarile conditionate din sursa cu orice formatari conditionate din destinatie.

Din zona Operation (Operaţie) se alege operaţia matematică ce se va efectua cu datele existente în zona de lipire:     

None (Nici una): Se lipeşte conţinutul, fără a efectua nici o operaţie matematică. Add (Adăugare): Se adună la celulele existente noile valori copiate. Substract (Scădere): Se scad din valorile existente noile valori copiate. Multiply (Înmulţire): Se înmulţesc valorile existente cu noile valori copiate. Divide (Împărţire): Se împart valorile existente la noile valori copiate.

Mai mult: 1) Operaţiile matematice pot fi aplicate doar dacă se lipesc valori. Aşadar, aceste operaţii pot fi efectuate doar cu una din opţiunile All, Values, All except border, or Values and number formats selectate din zona Paste. 2) Dacă dorim să evităm înlocuirea valorilor din celulele destinaţie cu celule goale copiate, alegem opţiuna Skip blanks (Ignorare celule libere). 3) Dacă datele copiate sunt pe rânduri / coloane şi dorim să le lipim invers, pe coloane / rânduri, alegem opţiunea Transpose (Transpus).

38

3.4 Formatarea condiţionată În unele cazuri se doreşte evidenţierea anumitor date ale unei foi de calcul care îndeplinesc anumite condiţii specificate. De exemplu, în următorul tabel salariile mai mici de 1500 să fie evidenţiate cu verde, cele între 1500 şi 2500 cu mov, iar cele peste 2500 să fie evidenţiate cu roşu. Nr. Crt.

Nume

Prenume

Data naşterii

Funcţia

1

Rosca

Simona

27734

2

Bugilan

Daniela

3

Sirbu

4

Salariul

Avans (40%)

Operator

1000

400

25614

Programator

2000

800

Iasmina

24949

Programator

2500

1000

Roman

Cristian

19496

Operator

1500

600

5

Chivu

Sorin

26984

Programator

2800

1120

6

Pop

Simina

30451

Secretara

1000

400

7

Popescu

Stela

28686

Director tehnic

3200

1280

8

Andreescu

Mariana

29456

Instructor

1100

440

9

Mihaescu

Ion

30550

Asistent programator

1100

440

10

Radu

Mihai

28034

Manager

2700

1080

11

Filimon

Mara

18556

Economist

1700

680

12

Grec

Ionut

29846

Contabil

1300

520

13

Miron

Victoria

27426

Operator

1300

520

14

Vitonescu

Ileana

27010

Instructor

1120

448

Pentru a realiza această formatare trebuie să urmăm paşii: Pasul 1: Se selectează datele Pasul 2: Din eticheta Home, gruparea Styles, alegem una dintre variantele de la Conditional Formatting:

Pasul 3: În fereastra apărută , în zona Format Cells that are … se completează valoarea cu care se compară, în zona With se alege stilul de formatare predefinită sau se particularizează formatarea cu opţiunile dorite accesând comanda Custom format. 39

Din această fereastră se pot stabili: tipul de data, stilul fontului utilizat, modul de subliniere, culoarea fontului, borduri pe celulă, culoarea de fundal a celulei. Pe fereastră este un buton de comandă Clear, prin care opţiunile selectate anterior pot fi şterse. Formatările selectate în aceste fişe vor fi aplicate de programul Excel numai atunci când valoarea din celula formatată îndeplineşte condiţia. Pentru îndepărtarea formatărilor condiţionate fie se selectează celulele asupra cărora se doreşte înlăturarea formatărilor fie nu se selectează nimic. Se accesează eticheta Home, gruparea Styles, Conditional Formatting/Clear Rules: Clear rules from selected cells Clear rules from entire sheet Remarca: Într-un tabel pot fi aplicate mai multe formatări condiţionate. Dacă se doreşte adăugarea condiţiilor suplimentare, atunci urmează aceeaşi paşi ca la prima condiţie. Pot fi specificate mai multe condiţii, nu doar trei, după cum se ştia din Excel 2003.

Ex em ple:

Comparăm salariul tarifar cu Highlight Cells și opțiunile lui. Opţiunea greater than. Salariilor mai mari decât 1500 să le fie aplicat un font roşu.

40

Opţiunea less than. Pentru salariile mai mici de 1200 se aplică formatarea Light Red with Dark Red Text.

Opţiunea duplicate/unique. Daca textul “Programator” apare de mai multe ori în tabel se va aplica formatarea Light Red with Dark Red Text.

Opţiunea text than contains. Textul care contine „ Prog” i se va aplica formatarea Light Red with Dark Red Text.

41

 -

Top/Buttom Rules. Aici avem următoarele opţiuni: Top 10 Items – primele 10 elemente cu cele mai mari valori Top 10% - primele 10% elemente cu cele mai mari valori Buttom 10 Items – ultimele 10 elemente cu cele mai mici valori Buttom 10% - ultimele 10% elemente cu cele mai mici valori Above Average – peste medie Below Average – sub medie

Exemplu: Opţiunea Top 10 Items.

 Data bars. Cu această opţiune putem aplica o formatare conditionată folosind bare de date, scări de culori și seturi de pictograme. Exemplu: Cu această bară de date colorată putem să comparăm valoarea celulelor. Lungimea barei reprezintă valoarea din celulă, mai mică sau mai mare.

Color scales. O scară de culori este un ghid vizual care ne ajută să înţelegem distribuţia și variaţia datelor utilizând un degrade cu două sau trei culori. Nuanţa culorii reprezintă valoarea celulei. O scară de două culori reprezintă cele mai mari si cele mai mici valori. O scară cu trei culori reprezintă cele mai mari, cele mai mici și valorile medii. Exemplu: O scară cu trei culori.

42



Icon sets. Cu această opţiune putem alege diferite iconiţe (bulinuțe colorate, săgeţi).

Exemplu:

Opţiunea New Rule se stabileşte tipul regulii se descrie tipul de formatare Opţiunea Manage Rules

introducerea unei noi reguli de formatare editarea unei reguli existente ştergerea unei reguli existente reordonarea regulilor; este importanta ordinea de evaluare, ea putand fi modificata cu ajutorul sagetilor sus/jos.

43

3.5 Noutăţi în lucrul cu tabele 3.5.1 Noul concept de tabel în Excel 2010 În versiunea 2010, când creăm un tabel (cunoscut în trecut ca listă) într-o foaie de lucru Microsoft Office Excel, avem posibilitatea de a gestiona şi de a analiza datele din acel tabel în mod independent de datele din afara tabelului. De exemplu, avem posibilitatea să filtrăm coloanele de tabel, să adăugăm un rând de totaluri, să aplicăm formatări de tabel şi să publicam un tabel pe un server pe care se execută Microsoft Windows SharePoint Services 3.0.

3.5.2 Formatarea unui tabel În Excel 2010 formatarea tabelelor poate fi realizată rapid şi foarte uşor cu ajutorul stilurilor rapide de tabel. Pentru a ne uşura şi mai mult munca, Excel 2010 are o galerie de stiluri de tabel bazate pe tema curentă. Un stil de tabel include combinaţii prestabilite de modele şi culori de umplere, font, culori de font, borduri care au fost realizate pentru a îmbunătăţi aspectul paginii. Aplicăm un stil unui tabel prin următorii paşi: Pasul 1: Selectăm o celulă sau grupul de celule asupra căruia vrem să îi aplicăm un stil Pasul 2: Executăm clic pe eticheta Home şi apoi pe butonul Format as Table şi selectăm unul din stiluri:

Remarca: După ce am aplicat un stil tabelului nostru apar automat elementele de filtrare care pot fi scoase cu ajutorul grupării Editting, alegând Sort&Filter, butonul Filter.

44

3.5.3 Afişarea sau ascunderea elementelor de formatare a tabelului După ce am selectat o celulă sau un interval din tabel executăm clic pe eticheta Design din bara Table Tools.

Aici selectăm sau eliminăm bifa din caseta elementului pe care îl vrem afișat sau ascuns:     

Header Row – pentru a formata o linie a tabelului ca linie specială Totals Row – pentru a formata ultima linie a tabelului cu totaluri pe coloane First Column – pentru a formata prima coloana a tabelului drept coloană specială Banded Column – pentru a formata coloanele pare diferit de cele impare Banded Rows – pentru a formata liniile pare diferit de cele impare

Mai mult: Putem însuma rapid datele într-un tabel utilizând opţiunea Total Row. Linia de totaluri apare ca ultima linie în tabel şi afişează cuvântul Total în celula din extrema stânga. Pentru a completa celulele celelalte, dăm clic în celulă şi ne apare automat o listă cu funcţiile uzuale. Alegem una din ele (în cazul nostru funcţia Sum) şi se face calculul automat.

3.5.4 Eliminarea liniilor sau a coloanelor dintr-un tabel Executăm clic într-o celulă din tabelul. Apoi clic pe eticheta Home şi pe butonul cu săgeata Delete şi apoi clic pe una dintre opţiunile Delete Table Rows sau Delete Table Columns.

45

3.5.5 Eliminarea liniilor duplicat dintr-un tabel Pasul 1: Executăm clic pe eticheta Design din bara Table Tools Pasul 2: Clic pe butonul Remove Duplicates. Selectăm coloanele cu duplicate, cele care vrem să le eliminăm Pasul 3: Clic pe OK.

3.5.6 Redimensionarea unui tabel Pasul 1: Executăm clic într-o celulă din tabel Pasul 2: Executăm clic pe eticheta Design din bara Table Tools. Pasul 3: Executăm clic pe Resize Table. Introducem intervalul pe care dorim să-l utilizăm pentru tabel.

Sau, pentru a redimensiona mai rapid tabelul, folosim mouse-ul și tragem de marcajul triunghiular de redimensionare din colţul dreapta al tabelului pentru a selecta intervalul dorit. După ce am creat un tabel, putem sorta, adăuga alte intrări şi afişa totaluri şi subtotaluri. Putem insera linii oriunde în tabel sau să le adăugăm la sfârşitul acestuia. Dacă dăm un clic dreapta în tabel ne apare următoarea căsuţă de dialog: 46

De aici, putem insera coloane şi/sau linii, putem şterge sau selecta, asemănător cu ceea ce puteam face în Word 2003, din meniul Table.

47

Capitolul 4. Grafice

4.1 Crearea şi modificarea graficelor

În Office Excel 2010, avem posibilitatea să utilizăm instrumente noi de diagrame pentru a crea cu uşurinţă diagrame cu aspect profesional, care comunică informaţiile eficient. Pe baza temei care este aplicată registrului de lucru, aspectul nou, actualizat al diagramelor include efecte speciale, cum a fi efectele 3-D, de transparenţă şi de umbre atenuate. Noua interfaţă de utilizator uşurează explorarea tipurilor disponibile de diagrame, astfel încât să creăm tipul de diagramă potrivit pentru datele dvs. Sunt furnizate numeroase stiluri şi aspecte de diagramă, astfel încât să aplicăm rapid un format care arată bine şi să aplicăm detaliile dorite în diagramă. Deoarece diagramele din Office Excel 2010 sunt desenate cu OfficeArt, diagramele şi elementele lor acceptă aproape orice operaţiune posibilă cu o formă OfficeArt. De exemplu, avem posibilitatea să adăugăm un efect de umbră atenuată sau de teşitură, pentru a evidenţia un element sau să utilizăm un efect de transparenţă pentru a face vizibile elementele acoperite din aspectul diagramei. De asemenea, se pot utiliza efecte 3-D realiste. Diagramele pot fi copiate şi lipite uşor între documentele sau dintr-un program în altul. Când copiaţi o diagramă din Excel în Word sau PowerPoint, ea se modifică automat pentru a se potrivi cu documentul Word sau prezentarea PowerPoint, dar se poate reţine şi formatul de diagramă Excel. Datele din foaia de lucru Excel pot fi încorporate într-un document Word sau într-o prezentare PowerPoint, dar poate fi lăsată şi în fişierul sursă Excel.

4.1.1 Etapele creării unui grafic Pasul 1: Se selectează datele, inclusiv antetele de coloane (B4:E10).

Pasul 2: Se alege eticheta Insert, gruparea Charts. Pasul 3: Se alege tipul şi subtipul de grafic, precum şi stilul subtipului din gruparea Chart Styles.

48

Pornind de la tabelul de mai sus, alegem un grafic de tip coloană şi obţinem: 90% 80% 70% 60% 50%

<30

40%

30-49

30%

>50

20% 10% 0% Ian

Feb

Mar

Apr

Mai

Iun

Pasul 4: După ce am realizat graficul de mai sus, automat ne apar instrumente de modificare a graficului realizat, in eticheta contextuala Design din cadrul Chart Tools.

Tot de aici, din gruparea Type putem modifica tipul graficului (columns, bars, pie, etc.) iar din gruparea Data putem schimba intre ele liniile cu coloanele. Din gruparea Chart Layout putem alege un aspect cu mai multe informatii:

49

Axis Title

Chart Title 90% 80% 70% 60% 50% 40% 30% 20% 10% 0%

<30 30-49 >50

Ian

Feb

Mar

Apr

Mai

Iun

Axis Title Alegem eticheta contextuala Layout si de acolo din gruparea Labels putem modifica aspectul si afisarea graficului. Din grupareaType

4.1.2 Cele mai întâlnite tipuri de grafic După crearea unui grafic avem posibilitatea de modificare a acestuia. Dacă vrem să modificăm tipul unui grafic alegem din eticheta Chart Tools/Design/gruparea Type, butonul Change Chart Type. Apare fereastra Change Chart Type şi se alege noul tip al graficului:

Exemple:

50

Pornind de la tabelul de mai sus putem construi următoarele tipuri de grafice:

4.1.2.1 Grafic sub formă de bară Acest tip de grafic este cel mai des folosite pentru a reprezenta sau compara date.

Spain

Ireland

Renault Peugeot Mercedes

England

BMW

France 0

50

100

150

200

250

4.1.2.2 Graficul de tip coloană În general, acest tip de grafic este folosit pentru a reprezenta sau compara date. Este asemănător cu graficul de tip bara dar barele sunt verticale.

51

250 200 BMW

150

Mercedes Peugeot

100

Renault 50 0 France

England

Ireland

Spain

4.1.2.3 Graficul Pie Graficul de tip Pie compară dimensiunile proporţionale ale articolelor ca parti din întreg.

BMW Spain 187 27%

Ireland 169 25%

France 124 18%

France England 210 30%

England Ireland Spain

52

4.1.2.4 Grafic de tip linie Se folosesc pentru a sublinia tendinţele care se manifestă în anumite perioade. Există două tipuri de grafice liniare şi anume: monoliniare şi multiliniare. 250 England 209 210

200 150 100

156 145 France 124 102

214 Ireland 169

132 121

136 123

198 Spain 187 146 125

BMW Mercedes Peugeot Renault

50 0 France

England

Ireland

Spain

4.2 Modificarea graficelor Dacă am creat un grafic şi ne dăm seama că trebuia construit altfel, trebuie să ştim că îl putem modifica oricând sau chiar îi putem crea un şablon. Ii putem schimba tipul graficului, putem comuta între serii, adăuga o nouă serie, putem modifica datele în tabel, putem adăuga, modifica sau şterge titlul, legenda, numele axelor, îi putem aplica diferite formatări, etc. Aproape orice parte a unui grafic Excel poate fi formatat. Această flexibilitate oferă un control total asupra aspectului graficului creat. Se pot modifica culorile, haşurile, fonturile, etc. Pentru a putea fi formatat, orice element din grafic trebuie selectat. Selectarea se face foarte uşor cu ajutorul mouseului aplicând un clic asupra elementului respectiv.

4.2.1 Adăugarea unor serii noi pe grafic După ce am creat graficul, dacă vrem să adăgăm o serie de date pe grafic, procedăm astfel: Pasul 1: Clic in graficul creat Pasul 2: Executăm clic pe Select Data, din gruparea Data. Îmi apare fereastra Select Data Source.

53

Pasul 3: Din fereastra Select Data Source, alegem Add. Apare fereastra Edit Series. În caseta Series Name, scriem numele seriei ce o adăugăm, iar în fereastra Series Values o să trecem valorile seriei respective pe care le selectăm din tabelul pe baza căruia am creat graficul.

4.2.2 Ştergerea unei serii din grafic După ce am creat graficul, dacă vrem să ştergem o serie de date din grafic, procedăm astfel: Pasul 1: Clic in graficul creat Pasul 2: Executăm clic pe Select Data, din gruparea Data. Îmi apare fereastra Select Data Source. Pasul 3: Executăm clic pe seria pe care vrem să o eliminăm şi alegem Remove.

54

4.2.3 Comutarea între serii în grafic Lângă Type avem gruparea Data. De aici putem adăuga serii noi în tabel şi/sau comuta între serii cu butonul:

Obţinem: 250

250

200

200

150

France

150

BMW

100

England

100

Mercedes

50

Ireland

50

Spain

0

Peugeot Renault

0

4.2.4 Modificarea titlului, etichetei datelor, legendei si a axelor unui grafic Pentru introducerea elementelor diagramei: titlu, legendă, denumirea axelor utilizează Chart Tools/Layout, gruparea Labels:

Pentru a adăuga titlul alegem Chart Title iar pentru valori alegem Data Labels (aşa cum apare mai sus) şi obţinem:

Chart Title 250 200

210

187 169

150 100

124

50

209

102

214

198

156 146 145 136 132 123 121 125

France England Ireland Spain

0 BMW

Mercedes

Peugeot

Renault

55

Pentru a modifica titlul dăm un clic pe Chart Title şi scriem noul titlu.

4.2.4 Adăugarea unui stil nou graficului Pentru a adăuga un nou stil graficului realizat, avem opţiunea Chart Style.

4.2.5 Modificarea amplasării graficului Putem alege locul în care va fi afişat graficul creat accesând butonul Move chart din Location: Se poate crea o foaie nouă care să conţină numai graficul. Graficul poate să rămână în aceeaşi foaie cu tabelul.

4.2.6 Schimbarea culorii de umplere a unui grafic Schimbarea culorii de umplere a unui grafic. Clic dreapta pe graficul căruia dorim să-i schimbăm culoarea. Se deschide fereastra Format Chart Area de unde alegem culoarea dorită. Întâi se selectează opţiunea Fill din partea partea stangă a ferestrei. In locul unei singure culori de fundal se poate folosi o imagine care sa ilustreze mai bine continutul graficului daca alegem Picture or texture fill

56

4.2.7 Adăugarea opţiunilor bordurilor graficului: culoare, tipul liniei. Adăugarea opţiunilor bordurilor graficului: culoare, tipul liniei. Clic dreapta pe graficul căruia dorim să-i schimbăm culoarea. Se deschide fereastra Format Chart Area de unde alegem Border Color sau Border Styles.

4.2.8 Schimbarea culorilor graficului Schimbarea culorilor graficului. Clic dreapta pe graficul deja definit. Se alege opţiunea Format Data Series şi apoi se selectează opţiunea Fill. Dar chiar si aceste elemente se pot particulariza prin imagini, daca alegem in loc de Solid fill, Picture or texture fill de unde alegem o imagine sugestiva pentru respectiva serie.

4.2.9 Introducerea unei imagini într-un grafic 2D Putem introduce o imagine ca fundalul unui grafic sau ca umplere a seriei de date. Dacă vrem să înlocuim culoarea într-un grafic, cu o imagine procedăm astfel: Pasul 1: Executăm clic în locul în care vrem să introducem imaginea Pasul 2: Din eticheta Format, gruparea Shapes Styles alegem Shape Fill. Pasul 3: Dacă vrem să alegem o imagine, executăm clic pe Picture. Cu Browse găsim calea imaginii respective. Mai mult: Putem alege ca şi culoare de umplere diferiţi gradienţi (clic pe Gradient) sau o textura (clic pe Texture).

57

4.2.10 Eliminarea fundalului zonei de date Daca am folosit ca fundal o imagine si nu o culoare, poate ar fi util ca zona de date sa nu acopere fundalul ci sa fie transparenta. Aceasta se poate realiza daca la Format Plot Area vom seta No fill. Aceasata va avea ca rezultat un grafic de acest fel: 250 210 209

200 150

156 145 124

100

214

198 187

169 132 121

146

136 123

125

BMW Mercedes Peugeot

102

Renault 50 0 France

England

Ireland

Spain

4.3 Copierea, mutarea graficului în acelaşi registru de calcul sau în registre de calcul diferite Copierea, mutarea graficului în acelaşi registru de calcul sau în registre de calcul diferite: se selectează graficul clic dreapta pe grafic, se alege opţiunea Copy sau combinaţia de taste Ctrl+ C, sau clic pe butonul Copy se alege destinaţia graficului clic dreapta pe grafic, se alege opţiunea Paste, combinaţia de taste Ctrl + V sau clic pe butonul Paste

4.4 Redimensionarea, ştergerea graficului -

se selectează graficul se vine cu mouse-ul pe unul din cele 8 butoane, pậnă apare o săgeată dublă. se trage în sensul dorit

4.5 Grafice de tip Sparklines Acest tip de grafice este folosit pentru o privire de ansamblu asupra unui set de valori, fara o prea mare importanta acordata cifrelor ci doar trendului seriei. Din eticheta Insert gruparea Sparklines, alegem Line si din fereastra alegem zona de celule pentru care dorim sa construim graficul si locul in care il vom plasa (o celula). Dupa obtinerea graficului, din etcheta contextuala Sparklines Tools putem sa modificam datele, tipul, stilul sau punctele ce vor aparea pe grafic. Celula in care se gaseste graficul poate fi copiata prin tragere astfel incat sa afiseze grafice si pentru alte zone de valori.

58

Pasul 1: Din eticheta Insert, gruparea Sparklines, alegem unul dintre cele trei tipuri de diagramă: Line, Column, Win/Loss:

Pasul 2: Selectăm domeniul C3:F3 (celulele care conțin vanzarile din Franta) la Data Range. În zona Location Range, selectăm celula unde vrem să fie afișat graficul. Pentru că celula activa era H3, nu mai este necesară selecția locației. Apoi tragem de coltul celului H3 in jos pentru a vedea graficele si pentru celelalte tari si Total.

Pasul 3: Executăm clic pe Ok. Rezultatul va fi următorul:

După ce am construit diagrama în celulă, va apare automat eticheta contextuală Sparklines Tools – Design:

Cu ajutorul acestei etichete putem modifica tipul graficului (din gruparea Type), afișarea punctului cel mai înalt, cel mai mic, punctele nagative, primul sau ultimul punct sau chiar toate (markers) – gruparea Show, stilul (Styles), culoare, etc.

59

Capitolul 5. Funcț ii

5.1 Funcţii de dată şi oră

Excel memorează datele calendaristice sub formă de numere seriale secvenţiale pentru a putea efectua calcule cu acestea. Excel memorează 1 ianuarie 1900 ca numărul serial 1, dacă registrul de lucru utilizează sistemul de dată calendaristică 1900. Dacă registrul de lucru utilizează sistemul de dată calendaristică 1904, Excel memorează 1 ianuarie 1904 ca numărul serial 0 (2 ianuarie 1904 este numărul serial 1). De exemplu, în sistemul de dată calendaristică 1900, Excel memorează 1 ianuarie 1998 ca numărul serial 35.796 deoarece sunt 35.795 de zile după 1 ianuarie 1900. In celula in care se memoreaza data calendaristica ca parte intreaga reprezentand numarul de zile trecute din 01.01.1900, partea zecimala reprezinta ora, minutul si secunda din ziua respectiva, adica se poate memora timpul. Timpul este memorat ca parti din zi, adica 35.796,50 este interpretat ca 1 ianuarie 1998 ora 12:00:00, 35.796,25 este interpretat ca 1 ianuarie 1998 ora 06:00:00, 35.796,75 este interpretat ca 1 ianuarie 1998 ora 18:00:00, 35.796,123456 este interpretat ca 1 ianuarie 1998 ora 02:57:47 Exemplu Utilizând sistemul de dată calendaristică 1900, funcţia DATE(1998;1;1) returnează 35.796, număr serial care corespunde datei de 1 ianuarie 1998.

5.1.1 Funcţia DATE Funcţia DATE returnează numărul serial ce reprezintă o anumită dată calendaristică. Sintaxă : DATE(an;lună;zi) Anul - poate fi format din una până la patru cifre. Excel interpretează argumentul an în concordanţă cu sistemul de dată calendaristică utilizat. Implicit, Excel pentru Windows utilizează sistemul de dată calendaristică 1900. Funcția DATE este cea mai utilă în situațiile în care anul, luna și ziua sunt furnizate de formule sau referințe de celule. De exemplu, este posibil să aveți o foaie de lucru care conține date într-un format pe care Excel nu îl recunoaște, cum ar fi AAAALLZZ. Aveți posibilitatea să utilizați funcția DATE împreună cu alte funcții, pentru a efectua conversia datelor în numere seriale pe care Excel le recunoaște.

5.1.2 Funcţia NOW Funcţia NOW întoarce numărul serial al datei şi orei curente. Sintaxă: NOW( ) Funcția NOW este utilă atunci când trebuie să afișați data și ora curente într-o foaie de lucru sau să calculați o valoare pe baza datei și orei curente și să fie actualizată acea valoare de fiecare dată când deschideți foaia de lucru. Valoarea funcției NU este actualizată în mod continuu. 60

Exemplu Dacă se utilizează sistemul de dată calendaristică 1900 şi ceasul computerului dvs. indică 12:30:00 P.M., 1-Ian-1987, atunci: NOW() egal 31778,52083 După zece minute: NOW() egal 31778,52778

5.1.3 Funcţia YEAR Funcţia YEAR returnează anul corespunzător unei date calendaristice. Anul este returnat ca un întreg cuprins în intervalul 1900-9999. Sintaxă: YEAR(număr_serial) Număr_serial este data calendaristică a anului căutat. Datele calendaristice pot fi introduse ca şiruri de text între ghilimele (de exemplu, „30/1/1998” sau „30/01/1998”), ca numere seriale (de exemplu, 35.825 care reprezintă 30 ianuarie 1998, dacă se utilizează sistemul de dată calendaristică 1900 ) sau ca rezultat al altor formule sau funcţii (de exemplu, DATEVALUE("30/1/1998”)). Exemplu: YEAR ( ”7/5/1998” ) ⇒ 1998 YEAR("11/05/03") ⇒ 2003

5.1.4 Funcţia MONTH Funcţia MONTH returnează luna unei date calendaristice reprezentate printr-un număr serial. Luna este dată ca un întreg, cuprins între 1 (ianuarie) şi 12 (decembrie). Sintaxă: MONTH(număr_serial) Număr_serial este data calendaristică a zilei căutate. Datele calendaristice pot fi introduse ca şiruri de text între ghilimele (de exemplu, „30/1/1998” sau „30/01/1998”) sau ca numere seriale (de exemplu, 35.825 care reprezintă 30 ianuarie 1998, dacă se utilizează sistemul de dată calendaristică 1900 ) sau ca rezultat al altor formule sau funcţii. Exemple MONTH("6-Mai") ⇒ 5 MONTH(35795) ⇒ 12 MONTH(35796) ⇒ 1 MONTH("01/04/2004") ⇒ 4

5.1.5 Funcţia DAY

- returnează ziua din data calendaristică, reprezentată ca un nr. serial. Ziua este un întreg cuprins între 1 şi 31. Sintaxă : DAY(număr_serial) Număr_serial este data calendaristică a zilei căutate. Exemple: DAY ( ”23/09/2002” ) ⇒ 23 DAY (“TODAY”) ⇒ 6 ( am fost in data de 6 Noiembrie 2003) DAY("4-Ian") ⇒ 4 DAY("15-Apr-1998") ⇒ 15 DAY("11/8/1998") ⇒ 11 DAY("10/10/2001") ⇒ 10

61

5.1.6 Funcţia TODAY

Sintaxă : TODAY() – este o functie fara parametri - întoarce numărul serial al datei curente preluata din data sitemului de calcul. Numărul serial este codul dată-oră utilizat de Microsoft Excel pentru calculele de date calendaristice şi ore. Funcția TODAY se poate utiliza numai pentru valorile implicite, nu se poate utiliza într-o coloană calculată.

5.1.7 Functia TIME()

Sintaxă : TIME(ora,minut,secunda) - Intorce numarul serial ce reprezinta ora sistemului. Numarul serial este o valoare cuprinsa intre 0 si 0,99999999 reprezentand respectiv ora 0:00:00 si ora 23:59:59 Ora, este un număr de la 0 (zero) la 32767 reprezentând ora. Orice valori mai mari de 23 vor fi împărţite la 24 şi restul va fi tratat ca valoarea de oră. De exemplu, =TIME(27,0,0) este egal cu =Time(3,0,0) este egal cu 0.125 sau 3:00 AM. Minut este un număr de la 0 (zero) la 32767 reprezentând minutul. Orice valoare mai mare de 59 va fi transformata în ore şi minute. De exemplu, =TIME (0,750,0) este egal cu =TIME(12,30,0) este egal cu 0.520833 sau 12 şi jumătate. Secunda este un număr de la 0 (zero) la 32767, reprezentând secundele. Orice valoare mai mare de 59 va fi transformata în ore, minute şi secunde. De exemplu, =TIME(0,0,2000) este egal cu =TIME(0,33,22) este egal cu 0.023148 sau 12:33:20 AM

5.1.8 Funcţia NETWORKDAYS Funcţia NETWORKDAYS returnează numărul zilelor lucrătoare cuprinse între start_date şi end_date . Zilele lucrătoare exclud weekend-urile şi toate datele identificate ca zile nelucrătoare. NETWORKDAYS se utilizează pentru a calcula câştigurile angajaţilor în funcţie de numărul zilelor lucrate într-un anumit interval de timp. Sintaxă: NETWORKDAYS(start_date;end_date;holidays) Remarcă: • Dacă această funcţie nu este disponibilă şi se returnează eroarea #NAME?, trebuie să instalăm şi să încărcăm programul de completare Analysis ToolPak (Pachet instrumente analiză): accesăm butonul Office, alegem Excel Options şi executăm clic pe Add-Ins (Programe de completare), selectăm Analysis ToolPak (Pachet instrumente analiză) şi clic pe OK. (Dacă este necesar, urmăm instrucţiunile din programul de instalare). •

Datele calendaristice ar trebui introduse utilizând funcţia DATE sau ca rezultate ale altor formule sau funcţii. De exemplu, se utilizează DATE(2008;5;23) pentru 23 mai 2008. Dacă datele calendaristice sunt introduse ca text pot apărea probleme. Dacă unul din argumente nu este o dată calendaristică validă, NETWORKDAYS returnează valoarea de eroare #VALUE!. 62

Start_date este data calendaristică de început. End_date este data de sfârşit. Holidays este un interval opţional cu una sau mai multe date care se exclud din zilele lucrătoare, cum ar fi sărbătorile naţionale şi religioase. Lista poate fi un domeniu de celule care conţine datele a numerelor seriale care reprezintă datele.

Exemplu:

Calculul diferenţei dintre două date calendaristice a) Calculul numărului de ore dintre două date calendaristice

b) Calculul numărului de zile dintre două date calendaristice

c) Calculul numărului de luni dintre două date calendaristice

63

d) Calculul numărului de ani dintre două date calendaristice

Transformarea unui text ce reprezinta minute si secunde in valorile corespunzatoare de minute si secunde

5.1.9 Funcţia EDATE Funcţia EDATE întoarce data ce reprezintă numărul indicat de luni, înainte sau după o dată specificată (start_date). Se utilizează EDATE pentru calculul datelor scadenţelor sau al datelor de scadenţă care cad în aceeaşi zi a lunii ca şi data emiterii. Dacă această funcţie nu este disponibilă şi se returnează eroarea #NAME?, instalăm şi încărcăm programul de completare Analysis ToolPak (vezi funcţia NETWORKDAYS) . Sintaxă: EDATE(start_date;months), unde: Start_date este o dată care reprezintă data de început. Months este numărul de luni dinainte sau după start_date. O valoare pozitivă pentru argumentul months dă o dată viitoare; o valoare negativă dă o dată trecută. Exemplu:

5.1.10 Funcţia EOMONTH

64

Funcţia EOMONTH - întoarce numărul serial pentru ultima zi din luna care este cu un număr indicat de luni înainte sau după data de început (start_date). Se utilizează EOMONTH pentru a calcula datele scadenţei sau datele efectuării plăţilor atunci când acestea cad în ultima zi a lunii. Dacă această funcţie nu este disponibilă şi se returnează eroarea #NAME?, instalăm şi încărcăm programul de completare Analysis ToolPak (vezi funcţia NETWORKDAYS) . Sintaxă: EOMONTH(start_date;months) Start_date este o dată care reprezintă data de început. Months este numărul de luni dinainte sau după start_date. O valoare pozitivă pentru argumentul months dă o dată viitoare; o valoare negativă dă o dată trecută. Dacă argumentul months nu este un întreg, el este trunchiat. Remarca: • Dacă argumentul start_date nu este o dată validă, EOMONTH întoarce valoarea de eroare #NUM!. • Dacă suma start_date plus months dă o dată invalidă, EOMONTH întoarce valoarea de eroare #NUM!. Exemplu:

5.1.11 Funcţia WORKDAY Funcţia WORKDAY - întoarce un număr care reprezintă o dată care este numărul indicat de zile de lucru înainte sau după o anumită dată (data de pornire). Zilele de lucru exclud sfârşiturile de săptămână şi orice date identificate drept sărbători. Utilizăm WORKDAY pentru a exclude sfârşiturile de săptămână sau sărbătorile când calculaţi datele scadenţei pentru facturi, termenele de livrare sau numărul de zile lucrate. Dacă această funcţie nu este disponibilă şi se returnează eroarea #NAME?, instalaţi şi încărcaţi programul de completare Analysis ToolPak (vezi funcţia NETWORKDAYS) . Sintaxă: WORKDAY(start_date;days;holidays), unde: Start_date este data calendaristică de început. Days reprezintă numărul de zile care nu sunt weekend-uri sau zile libere dinaintea sau după start_date. O valoare pozitivă înseamnă o dată viitoare; o valoare negativă înseamnă o dată trecută. Holidays este o listă opţională cu una sau mai multe date calendaristice care vor fi excluse din calendarul de lucru, cum ar fi sărbătorile. Lista poate fi o zonă de celule care conţine datele calendaristice a numerelor seriale care reprezintă datele calendaristice. Mai mult: • Dacă unul din argumente nu este o dată calendaristică validă, WORKDAY returnează valoarea de eroare #VALUE!. • Dacă suma argumentelor start_date şi days reprezintă o dată incorectă, WORKDAY returnează valoarea de eroare #NUM!. 65

•

Dacă days nu este un întreg, este trunchiat.

Exemplu:

5.1.12 Funcţia WEEKNUM Pentru a crea un calendar care să conţină şi numărul săptămânii, sau pentru a crea un planificator de proiecte sau lucrări sau in alte situaţii avem nevoie să cunoaştem a câta săptămână din an este o dată calendaristică. În Excel putem determina uşor numărul săptămânii cu ajutorul funcţiei WEEKNUM(). Sintaxă: WEEKNUM(serial_num; return_type), unde: Serial_num este o dată din săptămână. Return_type este un număr care indică în ce zi începe săptămâna. Punem 1 daca săptămâna începe duminica si 2 daca săptămâna începe luni. Remarca: Valoarea implicită este 1. Din păcate pentru noi, funcţia WEEKNUM() nu returnează numărul săptămânii conform sistemului european. Această funcţie consideră ca primă săptămână din an săptămâna care conţine ziua de 1 ianuarie, spre deosebire de sistemul european (de fapt Sistemul Internaţional ISO) care consideră ca prima săptămână a anului este săptămâna care are cel puţin patru zile. Exemplu: Dacă aplicăm doar funcţia =WEEKNUM(A2;2) pentru data calendaristică 10.03.2010 aflată în celula A2 rezultatul va fi 11. Dar calculând ne dăm seama că răspunsul corect conform ISO ar trebui să fie 10.

Atunci, utilizând şi alte funcţii construim următoarea formulă: =IF(WEEKDAY(A3;2)>4;WEEKNUM(A2;2)-1;WEEKNUM(A2;2)) In celula A3 am pus data de 1 ianuarie 2010 ( adică 1 ianuarie a anului respectiv). Puteam să construim şi astfel: =DATE(YEAR(A2);1;1). Funcţia WEEKDAY îmi returnează un număr intre 1 si 7 care reprezintă a câta zi din săptămâna este acea data. 66

Cu alte cuvinte, WEEKDAY(A3;2) determină în ce zi a săptămânii cade 1 ianuarie 2010. Ca să construiesc la modul general =WEEKDAY(DATE(YEAR(A2);1;1);2) determină în ce zi a săptămânii cade anul datei calendaristice din A2, ţinând cont că în Europa prima zi a săptămânii este

luni

Formula construită cu funcţia IF: =IF(WEEKDAY(A3;2)>4;WEEKNUM(A2;2)-1;WEEKNUM(A2;2)) returnează următoarele valori: 1. Dacă 1 ianuarie cade într-o vineri, sâmbătă sau duminică (adică “mai mare decât 4″) returnează numărul săptămânii minus 1; 2. Dacă 1 ianuarie cade într-o luni, marţi, miercuri sau joi (adică restul posibilităţilor) returnează numărul săptămânii pentru că, în acest caz, funcţia WEEKNUM() returnează o valoare corectă; Remarca: Al doilea parametru al funcţiilor WEEKDAY() şi WEEKNUM() (valoarea 2 în cazul nostru) le spune funcţiilor că săptămâna începe luni (sistem european). Revenind la exemplul de mai sus, pentru data de 10.03.2010, aflată în celula A2, rezultatul este 10.

5.2 Funcţii matematice si statistice 5.2.1 Funcţia ROUND Funcţia ROUND rotunjeşte un număr la un număr specificat de cifre, rezultatul fiind modificarea valorii numarului. Nu trebuie confundata cu formatarea unei celule, care NU modifica numarul ci doar il afiseaza cu mai putine zecimale. Numarul (sau expresia numerică) este orice expresie evaluată printr-un număr. Expresia poate fi orice combinație de variabile, constante, funcții și operatori. Se poate folosi la calcularea valorilor unei facturi, aplicandu-se o singura data asupra rezultatului final si nu de mai multe ori asupra rezultatelor partiale. Sintaxă : ROUND (număr , număr_poziţii) unde număr_poziţii: > 0 rotunjirea la dreapta poziţiilor zecimale. = 0 rotunjirea la întreg. < 0 rotunjire la partea întreagă. Exemple: ROUND(2,15; 1) egal 2,2 ROUND(2,149; 1) egal 2,1 ROUND(-1,475; 2) egal -1,48 ROUND(21,5; -1) egal 20

67

5.2.2 Funcţia SUMPRODUCT Funcţia SUMPRODUCT înmulţeşte componentele corespondente din matricele date şi întoarce suma acelor produse. Sintaxa: SUMPRODUCT(array1;array2;array3; ...) Array1, array2, array3, ... sunt de la 2 până la 30 de matrice ale căror componente putem să le înmulţim şi apoi să adunăm produsele. • Argumentele matrice trebuie să aibă aceleaşi dimensiuni. Dacă nu, SUMPRODUCT întoarce valoarea de eroare #VALUE!. • SUMPRODUCT tratează înregistrările din matrice care nu sunt numerice ca şi cum ar fi egale cu zero. Exemplu: Formula următoare înmulţeşte toate elementele celor două matrice şi apoi adună produsele — adică efectuează 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3. Pret cat1

Pret cat2

Cant cat1

Cant cat2

3

4

2

7

8

6

6

7

1

9

5

3

156

=SUMPRODUCT(H2:I4,K2:L4)

156

=SUMPRODUCT({3,4;8,6;1,9},{2,7;6,7;5,3})

156

=SUM({3,4;8,6;1,9}*{2,7;6,7;5,3})

5.2.3 Funcţia PRODUCT

- Înmulţeşte toate numerele date ca argumente şi întoarce produsul lor. Sintaxa: PRODUCT(number1;number2; ...) Number1, number2, ... sunt de la 1 până la 30 de numere care trebuie înmulţite. Observaţii • Argumentele care sunt numere, valori logice sau reprezentări text de numere sunt luate în calcul; argumentele care sunt valori de eroare sau texte ce nu pot fi interpretate ca numere cauzează erori. • Dacă un argument este o matrice sau o referinţă, numai numerele din matrice sau referinţă sunt luate în calcul. Celulele goale, valorile logice, textele sau valorile de eroare din matrice sau din referinţă sunt ignorate. Exemple Dacă celulele A2:C2 conţin 5, 15 şi 30: PRODUCT(A2:C2) egal 2.250 PRODUCT(A2:C2; 2) egal 4.500

68

Importante funcţii matematice şi statistice cum sunt funcţiile SUM, COUNT, AVERAGE prezentate anterior au si versiuni cu unul sau mai multi parametrii (IF/IFS). În continuare vor fi prezentate aceste funcţii utile folosindu-ne de urmatorul tabel pentru exemplificare.

Tabel Flori si Orase

5.2.4 Funcţia SUMIF Funcţia SUMIF adună celulele specificate printr-un criteriu dat. Sintaxă: SUMIF(range; criteria; sum_range), unde: - range - zona de celule pe care vreţi să o evaluaţi. - criteria - un criteriu de selecţie, sub forma unui număr, expresie sau text, adresa de celula, care defineşte care celule să fie adunate. De exemplu, criteriul poate fi exprimat ca 32, „32”, „>32”, „mere”. - sum_range - celulele actuale care trebuie însumate. Celulele din sum_range sunt însumate numai dacă celulele care le corespund în argumentul range îndeplinesc criteriul de selecţie. Dacă sum_range este omis, sunt însumate celulele specificate în argumentul range. Exemplu: Să se facă suma stocurilor pentru orasul Deva =SUMIF(B2:B18;”Deva”;C2:C18) Rezultat = 205

5.2.5 Funcţia SUMIFS Funcţia SumIFs este o extensie a lui SumIF şi permite totalizarea datelor din celule în funcţie de mai multe criterii. Sintaxa: SUMIFS (sum_range; criteria_range1; criteria1; [criteria_range2; criteria2]; [criteria_range3; criteria3]; ...), unde: 69

-

sum _range este domeniul pe care se face suma; criteria_range1 este domeniul pe care se aplică criteriul1 criteria1 reprezintă criteriul care trebuie aplicat domeniului anterior criteria_range2 reprezintă al doilea domeniu pe care se aplică criteriul2 criteria2 reprezintă criteriul care trebuie aplicat domeniului2 ...

Exemplu: Adunam stocurile pentru Camelia din Deva, practic facem intersectia intre planta selectata si orasul selectat, insumand stocurile. =SUMIFS(C2:C18;A2:A18;A11;B2:B18;B9) Rezultat = 23 Aveți posibilitatea să utilizați metacaractere în criterii, cum ar fi semnul de întrebare (?) și asteriscul (*). Un semn de întrebare se potrivește cu orice caracter unic, asteriscul se potrivește cu orice secvență de caractere. Dacă doriți să găsiți un semn de întrebare sau asterisc, tastați tilda (~) înainte de caracter. =SUMIFS(C2:C18;A2:A18;”Cam*”;B2:B18;”Dev?”)

5.2.6 Funcţia AVERAGEIF AverageIF – Această funcție returnează valoarea medie (media aritmetică) a tuturor celulelor dintr-o zonă care respectă anumite criterii. Sintaxa şi argumentele sunt similare funcţiei SumIf. Sintax a: AVERAGEI F (criteria_range; criteria; average_range) unde: - criteria_range este zona in care se cauta criteria - average_range este zona din care se fac calculele Sa se faca media stocurilor pentru orasul Resita. =AVERAGEIF(B2:B18;B15;C2:C18) Rezultat = 51,75

5.2.7 Funcţia AVERAGEIFS

AverageIFs – Această funcție returnează valoarea medie (media aritmetică) a tuturor celulelor dintr-o zonă care respectă anumite criterii. Sintaxa şi argumentele sunt similare funcţiei SumIfs.

Sintax a: AVERAGEI F (average_range; criteria_range1; criteria1; [criteria_range2; criteria2]; [criteria_range3; criteria3]; ...) Unde: - average_range este zona unde se fac calculele - criteria_range1 este zona in care se cautacriteria1 - criteria_range2 este zona in care se cautacriteria2 - ... Exemplu: Trebuie calculata media vanzarilor pentru Azalee din Deva =AVERAGEIFS(D2:D18;A2:A18;A2;B2:B18;B9) Rezultat = 2410 Trebuie calculata media stocurilor pentru Azalee din Deva =AVERAGEIFS(C2:C18;A2:A18;A2;B2:B18;B9) 70

Rezultat = 40

5.2.8 Funcţia COUNTIF Funcţia COUNTIF numără, într-o zonă de celule, celulele care îndeplinesc un criteriu dat. Sintaxa : COUNTIF(criteria_range;criteria), unde: - criteria_Range - este zona din care vor fi numărate celulele. - criteria - este criteriul de selectare, sub forma unui număr, adresa de celula, expresie sau text. De exemplu, criteria poate fi exprimat ca 32, "32", ">32", "mere". Exemplu: Sa contorizam de cate ori apare „Azalee” in coloana A =COUNTIF(A2:A18;A2) Rezultat = 3

5.2.9 Funţia COUNTIFS Funţia CountIFs permite numărarea datelor din celule funcţie de mai multe criterii.

Sintax a: COUNTI FS (criteria_range1; criteria1; [criteria_range2; [criteria_range3; criteria3]; ...) Unde: - criteria_range1 este zona in care se cauta criteria1; - criteria_range2 este zona in care se cauta criteria2 - ...

criteria2];

Exemplu: Sa contorizam de cate ori apare „Azalee” in „Deva” =COUNTIFS(A2:A18;A2;B2:B18;B9) Rezultat = 2

5.4 Funcţii de text 5.4.1 Funcţia CONCATENATE Funcţia CONCATENATE combină prin concatenare două sau mai multe valori de tip text într-o singură valoare de tip text. Operatorul „&” poate fi utilizat în loc de CONCATENATE pentru operația de concatenare a elementelor de text. Sintaxa : CONCATENATE(Text1, Text2, ...) Text1, Text2, ... – textele care vor fi concatenate Text1, Text2, pot fi de la 1 până la 30 elemente de text care sunt unite într-un singur element de text. Elementele de text pot fi șiruri de text, numere sau referințe la coloane singulare.

71

Exemplu:

5.4.2 Funcţia LEFT Funcţia LEFT returnează primele caractere dintr-un şir text. Sintaxă: LEFT(text;num_car) Text - este şirul de text ce conţine caracterele pe care le vom extrage. Num_car - indică numărul de caractere pe care le va extrage LEFT. Remarcă: • Num_car trebuie să fie mai mare sau egal cu zero. • Dacă num_car este mai mare decât lungimea textului, LEFT returnează tot textul. • Dacă num_car este omis, se consideră egal cu 1. Exemple LEFT("Preţ vânzare";4)  "Preţ" Dacă A1 conţine "C101", atunci: LEFT(A1)  "C"

5.4.3 Funcţia RIGHT Funcţia RIGHT returnează ultimele caractere dintr-un şir text. Sintaxă RIGHT(text;num_car) Text - este şirul de text ce conţine caracterele pe care le vom extrage. Num_car - indică numărul de caractere pe care le va extrage. Remarcă: • Num_car trebuie să fie mai mare sau egal cu zero. • Dacă num_car este mai mare decât lungimea textului, RIGHT returnează tot textul. • Dacă num_car este omis, se consideră egal cu 1. Exemple RIGHT("Preţ vânzare";7)  " vânzare" Dacă A1 conţine "C101", atunci: RIGHT(A1)  "1"

5.4.4 Funcţia MID Funcţia MID întoarce un anumit număr de caractere dintr-un şir de text, începând din poziţia specificată, pe baza numărului de caractere specificat. 72

Sintaxă: MID(text;start_num;num_chars) Text - este şirul de text care conţine caracterele pe care le veţi extrage. Start_num - este poziţia din text a primului caracter pe care vreţi să-l extrageţi. Pentru primul caracter din text, start_num este 1 şi aşa mai departe. Num_chars - specifică numărul de caractere ce trebuie extrase din text de funcţia MID. Remarcă: Dacă num_chars este negativ, MID întoarce valoarea de eroare #VALUE!. Exemple: MID("C101CS";2;3) egal „101” MID("Fluid Flow";7;20) egal „Flow” MID("1234";5;5) egal "" (text vid)

5.4.5 Funcţia UPPER Funcţia UPPER face conversia textului în majuscule. Sintaxă: UPPER(text) Text este textul căruia vreţi să-i faceţi conversia în majuscule. Text poate fi o referinţă sau un şir de text.

5.4.6 Funcţia PROPER Transformă în majusculă prima literă dintr-un şir de text şi orice altă literă din text care urmează după orice alt caracter în afară de o literă. Face conversia tuturor celorlalte litere la litere mici. Sintaxă: PROPER(text) Text este textul căruia vreţi să-i faceţi.

5.4.7 Funcţia LOWER Funcţia LOWER face conversia tuturor literelor mari dintr-un şir de text în litere mici. Sintaxă: LOWER(text) Text este textul căruia vreţi să-i faceţi conversia în litere mici.

73

Exemplu:

5.5 Funcţii logice 5.5.1 Funcţia IF Funcţia IF întoarce o valoare dacă condiţia specificată este evaluată la TRUE şi o altă valoare dacă este evaluată la FALSE. Utilizaţi IF pentru a supraveghea testele condiţionale pe valori şi formule. Sintaxă: IF(logical_test;value_if_true;value_if_false) Exemplu:

logical_test este o intrebare la care se poate raspunde doar cu DA sau NU, adica de tip logic. Argumentele value_if_true;value_if_false sunt optionale; daca lipseste value_if_true si rezultatul evaluarii este adevarat, functia IF intoarce 0. Daca lipseste value_if_false si rezultatul evaluarii este fals, functia IF intoarce FALSE. Daca valorile intoarse de functia IF sunt de tip text, trebuie incadrate intre ghilimele duble ”. Se pot imbrica pana la 64 de nivele. Exemplu: Stiind care sunt veniturile si cheltuielile, ne propunem sa afisam Profit / Pierdere / Suntem pe zero in functie de comparatia dintre venituri si cheltuieli.

5.5.2 Funcţia AND

Întoarce TRUE dacă toate argumentele sale sunt TRUE; întoarce FALSE dacă unul sau mai multe argumente sunt FALSE. Sintaxă: AND(logical1;logical2, ...) Logical1, logical2, ... sunt de la 1 până la 30 de condiţii pe care le testaţi şi care pot fi TRUE sau FALSE. • Argumentele trebuie să poată fi evaluate la valori logice cum ar fi TRUE sau FALSE sau argumentele trebuie referinţe care conţin valori logice. • Dacă un argument referinţă conţine text sau celule goale, acele valori sunt ignorate. • Dacă zona specificată nu conţine nici o valoare logică, AND întoarce valoarea de eroare #VALUE!. Exemple IF ( AND (B12 > 5; B12 < 20 ) ; “OK” ; “ Eroare!”)

74

5.5.3 Funcţia OR

Întoarce TRUE dacă cel puţin un argument este TRUE; întoarce FALSE dacă toate argumentele sunt FALSE. Sintaxă: OR(logical1;logical2;...) Logical1,logical2,... sunt de la 1 până la 30 condiţii pe care vreţi să le testaţi şi care pot fi TRUE sau FALSE. • Argumentele trebuie să poată fi evaluate la valorile logice TRUE sau FALSE sau să fie referinţe care conţin valori logice. • Dacă un argument referinţă conţine text sau celule goale, acele valori sunt ignorate. • Dacă zona specificată nu conţine nici o valoare logică, OR întoarce valoarea de eroare #VALUE!. Exemple: OR(TRUE) egal TRUE OR(1+1=1;2+2=5) egal FALSE Dacă zona A1:A3 conţine valorile TRUE, FALSE şi TRUE, atunci: OR(A1:A3) egal TRUE IF ( OR (B12 = ”Ion”; B12 =”Vasile” ) ; “OK” ; “ Eroare!”)

5.6 Funcţii de căutare şi referinţă 5.6.1 Funcţia VLOOKUP Sintaxa : VLOOKUP (valoare_căutată , matrice_căutare , nr_coloană_rezultat , tip_ căutare) Funcţia efectuează căutarea elementului valoare_căutată în coloana din stânga a matrice_căutare şi apoi în linia corespunzătoare acestuia până în coloana indicată de nr_coloana_rezultat. Tip_căutare e valoare logică TRUE (valori apropiate), FALSE (valoare exactă sau #N/A – dacă nu s-a găsit nici un element). Dacă Tip_căutare este TRUE sau este omis, valorile din prima coloană din matrice_cautare trebuie plasate în ordine ascendentă, altfel este posibil ca VLOOKUP să nu returneze valoarea corectă. Exemplu 1 – căutare exactă în tabel

Exemplu 2 – căutare la valoarea apropiată, mai mică decât valoarea căutată

75

5.6.2 Funcţia HLOOKUP Funcţia HLOOKUP efectuează căutarea valoare_căutată în linia superioară a domeniului definit de matrice_căutare, apoi se face o căutare în linia dată de nr_linie_rezultat Sintaxă : HLOOKUP (valoare_căutată , matrice_căutare ,nr_linie_rezultat, tip_căutare) Valoare_căutată şi valorile primei linii pot fi numere, texte, logice. Returnează valoarea cea mai mare care este mai mică sau egală cu valoarea_căutată, dacă aceasta nu e găsită. Pentru a găsi o valoare exactă se foloseşte argumentul tip_căutare. Nr_linie_rezultat - poate avea orice valoare începând cu 1. Pentru a se returna o valoare din prima linie se foloseşte nr. 1, pentru cea de-a doua linie se foloseşte nr. 2. Dacă linia rezultat < 1 ⇒ # VALUE, > nr. linii tabel ⇒ #REF Tip_căutare e valoare logică (TRUE (valori apropiate), FALSE (valoare exactă sau #N/A – dacă nu s-a găsit nici un element). Obs. Valorile din prima linie trebuie să fie ordonate crescător (alfabetic A - Z , numeric 0 - 9) Exemplu:

76

5.7 Funcţii de baze de date Programul EXCEL ne pune la dispoziţie functii predefinite pentru gestiunea bazelor de date. Acestea au următorul format general : Numefunctie (baza de date, câmp, criteriu) unde : baza de date – este un domeniu specificat prin referinţa la domeniu (ex. B7:I21) sau prin numele domeniului, definit cu Insert/Name/Define. câmp – specifică ce coloană va fi utilizată. Poate fi specificat in mai multe moduri : prin numele câmpului plasat între ghilimele (ex. “Funcţia”) sau prin numărul coloanei (ex. 2) sau prin adresa celulei care conţine numele câmpului. criteriu – este o referinţă la un domeniu (ex. A1:A2) sau numele domeniului (ex. criteriu) dacă acesta a fost anterior definit. Exemplu: Baza de date – A4:F10 – trebuie sa contina denumirile coloanelor Camp – C4 – Criteriu – A1:F2 - – trebuie sa contina denumirile coloanelor pentru a se putea face concordanta numelor

Se poate spune ca functiile de baze de date sunt o combinatie intr-un singur pas, mai rapida, a filtrarilor avansate si a functiei propriuzise.

5.7.1 Funcţia DCOUNT Sintaxa: DCOUNT (baza date, camp, criteriu) - Numără înregistrările numerice din câmpul bazei de date care respectă un criteriu. Caută înregistrările cu Meri de Inălțimi cuprinse între 10 și 16 și numără câte din câmpurile Vârstă din aceste înregistrări conțin numere. =DCOUNT(A4:E10;"Vârstă";A1:F2) Rezultat = 1

5.7.2 Funcţia DSUM

- Însumează toate numerele dintr-un câmp al b.d. pentru înregistrările care respectă un criteriu.

77

5.7.3 Funcţia DCOUNTA

- Numără celulele nevide din câmpul b.d. care respectă criteriul specificat.

5.7.4 Funcţia DAVERAGE

- Calculează media aritmetică a valorilor din câmpul b.d. care respectă criteriul specificat.

5.7.5 Funcţia DMAX

- Calculează maximul valorilor din câmpul b.d. care respectă criteriul specificat.

5.7.6 Funcţia DMIN

- Calculează minimul valorilor valorilor din campul b.d. care respecta criteriul specificat.

5.7.7 Funcţia DGET

- Extrage din baza de date o singura înregistrare care respectă un criteriu. Dacă nici o înregistrare nu satisface criteriul se va returna #VALUE!. In cazul în care există mai multe înregistrări care respectă criteriul dat, se va returna #NUM!. Exemplu:

5.8 Funcţii financiare 5.8.1 Funcţia PMT

Calculează şi retunează plata lunară sau anuală pentru un împrumut sau o investiţie, bazate pe o plată în rate şi pe un nivel fix al dobanzii. Sintaxa : PMT(rate, nper, pv [, fv,type]) Argumente: - rate – reprezintă dobânda corespunzătoare intervalului de timp analizat. - nper - numărul perioadelor (ex. lunile); Obs: dobânda anuală trebuie să se împartă la 12 dacă plăţile se efectuează lunar.

78

- pv - este valoarea constantă care se plăteşte / primeşte în fiecare perioadă a unei investii. In mod normal, plata conţine şi suma principală şi dobanda. Plăţi deja efectuate se vor introduce ca valori negative. - fv – valoarea viitoare a unei sume, la sfarşitul perioadei. - type : 0 – banii sunt plătiţi la sfarşitul perioadei. 1 – plata se face la începutul perioadei. Dacă tip şi pv nu se specifică se consideră nule. Exemplu Să presupunem că vrem să facem un împrumut la o bancă comercială pentru achiziţionarea unui autoturism în valoare de 10.000 €. Rata dobânzii pentru creditele de consum este de 29% pe an, iar durata înprumutului a fost stabilită la 8 ani.

5.8.2 Funcţia FV Calculează şi retunează valoarea viitoare a unei investiţii sau plasament în condiţii de plăţi şi rate ale dobânzii constante. Sintaxa: FV(rate, nper[, pmt,pv, type]) Exemplu: Dorim efectuarea unui plasament la o bancă comercială. Suma depusă iniţial este de 1.400 €. Dobânda anuală este de 12%. Alături de depozitul iniţial urmează să depunem lunar câte 100 € timp de 1 an. La finalul perioadei vom avea suma de 2.845,81 €.

5.8.3 Funcţia PV Întoarce valoarea actualizată a unei investiţii. Valoarea actualizată este suma totală pe care o reprezintă în prezent o serie de plăţi viitoare. De exemplu, când luaţi bani cu împrumut, suma împrumutată este valoarea actualizată pentru creditor. Sintaxă: PV(rate,nper,pmt,fv,type) Exemplu:

79

Rezultatul este negativ deoarece reprezintă banii pe care îi veţi plăti, un flux de numerar expediat. Dacă vi se cere să plătiţi (6.000) pentru anuitate, veţi hotărî că nu este o investiţie bună deoarece valoarea prezentă a anuităţii (5.977,71) este mai mică decât suma care vi se cere să o plătiţi.

5.8.4 Funcţia RATE Întoarce rata dobânzii periodice pentru o anuitate. RATE este calculată prin iteraţii şi poate avea zero sau mai multe soluţii. Dacă rezultatele succesive ale funcţiei RATE nu converg spre 0,0000001 după 20 de iteraţii, RATE întoarce valoarea de eroare #NUM!. Sintaxă: RATE(nper; pmt; pv [;fv; type; guess]) Nper - este numărul total de perioade de plată dintr-o anuitate. Pmt - este plata efectuată periodic; ea nu poate fi schimbată pe durata anuităţii. În mod tipic, argumentul pmt conţine principalul (valoarea iniţială) şi dobânda, dar nu şi alte taxe şi datorii. Dacă pmt este omis, trebuie inclus argumentul pv. Pv - este valoarea actualizată - suma totală pe care o reprezintă în prezent o serie de plăţi viitoare. Fv - este valoarea viitoare sau o balanţă în numerar pe care dorim să o obţineţi după efectuarea ultimei plăţi. Dacă fv este omisă, se presupune a fi egală cu 0 (valoarea viitoare a unui împrumut, de exemplu, este 0). Type - este numărul 0 sau 1 şi indică momentul când sunt datorate plăţile. Guess - este valoarea estimată pentru argumentul rată. • Dacă omiteţi argumentul guess, se consideră de 10 procente. • Dacă RATE nu converge, încercaţi şi alte valori pentru argumentul guess. RATE converge de obicei dacă guess este cuprins între 0 şi 1. Exemplu:

5.8.5 Funcţia NPV Calculează valoarea netă actualizată a unei investiţii prin utilizarea unei rate de actualizare (rata de scont) şi a unei serii de plăţi (valori negative) şi încasări (valori pozitive) viitoare. Sintaxă: NPV(rate;value1;value2; ...) Rate - este rata de actualizare pe parcursul unei perioade. Value1, value2, ... - sunt de la 1 până la 29 de argumente care reprezintă plăţi şi încasări. • Value1, value2, ... trebuie repartizate la aceleaşi intervale de timp şi trebuie efectuate la sfârşitul fiecărei perioade. • NPV foloseşte ordinea argumentelor value1, value2, ... pentru a interpreta ordinea fluxurilor de numerar. Asiguraţi-vă că valorile plăţilor şi încasărilor sunt introduse în succesiunea corectă. • Argumentele care sunt numere, celule goale, valori logice sau reprezentări text ale numerelor, sunt luate în calcul, iar argumentele care nu se pot converti în numere sunt ignorate. • Dacă un argument este o matrice sau o referinţă, sunt luate în calcul numai numerele din matrice sau din referinţă. Celulele goale, valorile logice, textele sau valorile de erori din matrice sau din referinţă sunt ignorate. Exemplu: 80

81

Capitolul 6. Manipularea datelor

6.1 Sortarea datelor

Sortarea se poate face până la 64 de nivele (în versiunea 2003 existau cel mult 3 nivele de sortare) şi în al doilea rând putem face sortări în funcţie de diferite atribute de formatare. Din eticheta Data, în gruparea Sort & Filter… avem butoanele clasice de sortare ascendentă şi descendentă şi butonul Sort. Pentru sortari multiple: Pasul 1: Executăm clic oriunde în interiorul tabelului Pasul 2: Executăm clic pe eticheta Data Pasul 3: In gruparea Sort & Filter… exceutam clic pe butonul Sort Pasul 4: Executăm clic pe butonul cu săgeata al câmpului Sort by si precizăm coloana de sortare Pasul 5 : Executăm clic pe butonul cu săgeata al câmpului Values si precizăm criteriul de sortare Pasul 6 : Executăm clic pe butonul cu săgeata al câmpului Order si precizăm sensul de sortare Pentru mai multe nivele, adaugam cate un nivel cu butonul Add Level si le putem ierarhiza cu ajutorul butoanelor de mutare sus / jos. Cu clic pe butonul Sort apare caseta de dialog:

Remarca: Caseta de dialog de mai sus poate fi activată și din eticheta Home, gruparea Editing alegând Sort & Filter.

82

Putem face o sortare, astfel încât aceşti studenţi să apară la începutul tabelului. Sortarea se face în funcţie de culoarea textului:

Ca ordine de apariţie am ales culoarea roşie, ceea ce înseamnă că la începutul tabelului afişează rândurile al căror font este roşu şi apoi celelalte. Rezultatul este următorul:

83

Sortarea se poate aplica si tabelelor ce trebuie ordonate pe orizontala, dupa valorile uneia sau mai multor linii, sortare utila de exemplu, in cazul folosirii functiei HLOOKUP. Pentru aceasta, va trebui ca in eticheta Data, gruparea Sort&Filter, butonul de Sort sa apelam Options... si de acolo sa alegem Sort left to right, sortarea functionand similar cu cea verticala dar aplicata pe orizontala.

6.2 Filtrarea datelor Faţă de versiunea 2003, opţiunile de filtrare aduc o serie de îmbunătăţiri noi, îmbunătăţiri care înainte puteau fi făcute doar cu ajutorul formulelor. Tot în versiunea 2010 apare şi butonul de Reapply (lângă butonul Filter), cu ajutorul căruia putem reaplica acelaşi filtru. Putem impune un filtru în funcţie de felul cum arată o celulă la un moment dat. De exemplu, toate celulele care sunt colorate într-un anumit fel să fie afişate la un moment dat. Pentru a executa operaţia de filtrare executăm paşii: Pasul 1: Executăm clic oriunde în interiorul tabelului Pasul 2: Executăm clic pe eticheta Data Pasul 3: Dacă este necesar, executăm clic pe butonul Filter Pasul 4: Executăm clic pe butonul cu săgeata al câmpului pentru care dorim să precizăm criteriul de căutare Pasul 5 : Pentru a utiliza filtrele prefabricate, plasăm indicatorul mouse-ului pe opţiunea Filters şi apoi selectam o opţiune de filtrare, cum ar fi Equals.

Remarca: • •

6.2.1

Putem adăuga mai multe filtre repetând paşii 4 si 5. Pentru a elimina un filtru, executăm clic pe săgeata câmpului si apoi clic pe opţiunea Clear Filter From .

Filtre noi pe date calendaristice

Pentru date calendaristice putem filtra tabelul în funcţie de oricare din componentele datei. În tabelul de mai jos putem filtra datele dintr-o anumită perioadă. Cu un clic pe Date Filters după ce am executat paşii de mai sus ne apare următoarea listă: 84

De aici putem să alegem tranzacţiile de astăzi, de săptămâna viitoare, din această lună, de luna viitoare, dintr-o anumită lună sau dintr-un anumit trimestru, etc.

6.2.2

Filtre noi pe text

Când aplicăm filtre în tabele Excel, rapoarte PivotTable și rapoarte PivotChart, avem posibilitatea de a pune filtre pe text: anumite texte care încep cu anumite litere, care se termină cu literele ... care conțin, care nu conțin, etc. Mai mult decât atât, putem profita de noua casetă de căutare Search, care ne va ajuta să găsim rapid ceea ce căutăm în foile mari de lucru. De exemplu, pentru a găsi un anumit produs într-un catalog care stochează câteva zeci de mii de elemente, începem să tastăm termenul de găsit și elementele vor apărea instantaneu în listă. Avem posibilitatea să restrângem rezultatele și mai mult, deselectand conținutul pe care nu dorim să-l vedem.

85

6.2.3 Filtre noi pe numere

Avem și pentru coloanele care conțin numere, elemente noi de filtrare: putem filtra elementele egale cu (equal to), diferite de, mai mici decât (less then), mai mici sau egale cu (less then or equal to), mai mari decât (grater then), mai mari sau egale cu (grater then or equal to), valorile care se află între (between), primele x elemente (top 10), etc Mai mult decât atât, avem și aici opțiunea de a căuta cu Search.

6.3 Protejarea informaţiilor Securitatea datelor dintr-un registru Microsoft Excel poate fi asigurată la mai multe nivele. Un prim nivel este acela de acces la fişier. Al doilea mare nivel este acela care priveşte protejarea informaţiilor după ce utilizatorul a deschis fişierul Excel.

6.3.1 Protecţia la nivel de fişier Asigurarea parolelor de acces la fişier este efectuată în momentul salvării fişierului. Se pot stabili parole pentru deschiderea fişierului, pentru modificarea şi salvarea fişierului, precum şi ataşarea unei recomandări de a deschide fişierul doar în citire (read-only). Un registru Excel protejat prin parolă pentru deschidere poate fi accesat doar de utilizatorii care cunosc parola. De preferat ar fi să utilizăm parole puternice, care combină litere mari și mici, numere și simboluri. Parolele slabe nu combină aceste elemente. Parolele trebuie să aibă o lungime de 8 sau mai multe caractere. Exemplu: Parolă puternică: he%llo”!21.

Parolă puternică: „Scrierea” pe tastatura a unei/unor litere: litera A mare de tipar se obtine prin tastarea succesiva a caracterelor zaq123edcasd, in timpul tastarii putand activa si tasta Shift. Sau inclinata la dreapta: zse345tfcsdf, etc. Parolă slabă: hello27. Remarca: Este esenţial să ne amintim parola. Dacă uităm ce parolă am pus, Microsoft nu poate să o regăsească. Criptarea fişierului şi setarea unei parole pentru a-l deschide Pasul 1: Clic pe butonul File , Info, apoi pe Protect Workbook.

86

Pasul 2: În caseta Encript with Password, tastăm o parolă şi apoi clic pe OK. Putem tasta până la 255 de caractere. Criptarea este metoda standard utilizată pentru a face fişierul mai sigur. Pasul 3: Apare caseta Confirm Password. Aici trebuie să reintroducem parola şi apoi clic pe OK.

Pasul 4: Pentru a salva parola, trebuie salvat fişierul. Setarea unei parole pentru a modifica un registru de lucru Pasul 1: Clic pe butonul File, apoi pe Save as. Pasul 2: Clic pe Tools, General Options.

Pasul 3: Putem alege una din variantele:

87

•

•

Dacă se vrea introducerea unei parole înainte de a vizualiza documentul, tastăm o parolă în caseta Password to open. În mod implicit, această caracteristică utilizează criptarea avansată, dar, spre deosebire de utilizarea comenzii Criptare document (Enycript Document) descrisă mai sus, aceasta nu ne permite să tastăm până la 255 de caractere, ci numai 15 caractere. Dacă se vrea introducerea unei parole înainte de a salva modificări la document, tastăm o parolă în caseta Password to modify. Această parolă nu permite metoda de criptare. Este proiectată pentru a permite colaborarea cu recenzenții de conținut în care avem încredere. Nu este proiectată să ne ajute să facem fișierul mai sigur.

Remarcă: Putem alege ambele variante dar trebuie să adăugăm parole diferite şi să avem grijă să nu le încurcăm (eventual prefixate cu „open” sau „modify”). Dacă nu vrem ca fişierul să fie modificat atunci bifăm caseta Read Only, doar pentru a fi citit. Când recenzenții deschid fișierul, aceștia sunt întrebați dacă doresc să deschidă fișierul doar în citire. Se execută clic pe OK. Dacă se solicită, trebuie introduse parolele pentru a le confirma, apoi clic pe OK. Pasul 4: În caseta de dialog Save as, facem clic pe Save. Pasul 5: Dacă se solicită, executăm clic pe Yes pentru a înlocui documentul existent.

6.3.2 Protecţia la nivel de foaie de calcul Prin protejarea informaţiilor înţelegem mecanismele de care dispune cel ce creează un registru Excel pentru limitarea accesului unui utilizator. Accesul poate fi limitat atât în ceea ce priveşte vizualizarea informaţiilor, cât şi în ceea ce priveşte posibilitatea de modificare a acestora. Stabilirea protecţiei la nivel de foaie de calcul are ca efect prevenirea acţiunilor de modificare a informaţiilor din foaia de calcul. Proiectantul foii poate interzice modificarea conţinutului unor celule (sau a tuturor celulelor), vizualizarea liniilor/coloanelor ascunse, vizualizarea formulelor utilizate în anumite celule. Protecţia registrului de lucru și a foii de lucru, o putem seta din eticheta Review, gruparea Changes.

Alegem Protect Sheet. Prin alegerea acestei opţiuni de protecţie, utilizatorul nu poate modifica conţinutul foii de calcul. Este afişată fereastra Protect Sheet în care se stabilesc elementele pentru care se asigură protecţia, prin bifarea opţiunilor respective. Protejarea efectivă a celulelor unei foi de calcul: eticheta Review, gruparea Changes, alegem Protect Sheet În zona Password to unprotect sheet se introduce parola. Pentru selectarea şi introducerea textului în celulele neprotejate se selectează opţiunea Select unlocked cells.

88

Remarca: Faptul că parola nu este obligatorie are utilitatea de a comuta mai simplu între stările de protejare în timpul proiectării foii de calcul (protejarea neparolată înlătură eventuale modificări accidentale ale informaţiei).

6.3.3 Protecţia la nivel de registru La acest nivel de protecţie se poate preveni modificarea numărului de foi ale registrului (adăugare sau eliminare) şi modificarea dimensiunilor şi poziţiilor ferestrelor în care se vede registrul. Există de asemenea posibilitatea de a însoţi protecţia de o parolă. Din eticheta Review, gruparea Changes, alegem Protect Workbook putem restricţiona sau nu accesul.

Clic pe Protect Structure and Windows. Aici avem două opţiuni:  Structure (protejarea structurii registrului) Selectând această opţiune de protecţie, utilizatorii au restricţie la: - vizualizarea foilor care erau ascunse în momentul protejării; - mutarea, eliminarea, ascunderea sau redenumirea foilor; - inserarea de noi foi (de calcul sau chart) - mutarea sau copierea foilor într-un alt registru;  Windows (protejarea ferestrelor registrului) Această opţiune nu permite utilizatorului - să modifice dimensiunea şi poziţia ferestrelor registrului; - să mute, să redimensioneze sau să închidă ferestrele; este însă posibilă ascunderea şi reafişarea ferestrelor.

6.4 Validarea şi auditul datelor Prin validarea datelor înţelegem procedurile activate astfel încât în anumite celule să nu se poată introduce decât date care îndeplinesc condiţii impuse. Procesul poate asigura atât afişarea unui mesaj informativ sau de avertizare la activarea celulei, cât şi afişarea unui mesaj de eroare dacă s-a completat o valoare neacceptabilă. Prin auditul foii de calcul înţelegem procedurile care permit vizualizarea dependenţelor în formule şi funcţii, localizarea valorilor de tip eroare etc. 89

6.4.1 Validarea datelor Restricţiile care pot fi impuse pentru conţinutul unei celule se referă la : – tipul datei (întreg, zecimal, text etc.); – limite între care pot fi valorile numerice sau de tip dată calendaristică; – lungimea unui text; După introducerea datelor şi efectuarea calculelor, se poate efectua auditul foii pentru a localiza şi corecta datele eronate. De exemplu, avem un tabel utilizat pentru pontaj cu următoarele informaţii :

În acest tabel ziua trebuie să fie între 02/12/2005 şi 06/12/2005, marca să conţină 4 caractere, orele lucrate să fie între 4 şi 8, orele suplimentare între 0 şi 2. Datele care nu corespund acestor criterii dorim să fie evidenţiate. Pentru a realiza acest obiectiv vom impune următoarele restricţii: Restricţii pentru datele numerice sau date calendaristice Acestea pot fi impuse pentru valori numerice sau de tip calendaristic. Pasul 1. Se selectează celule pentru care se definesc restricţiile. De ex. selectăm celulele cu datele calendaristice. Pasul 2. Din meniul Data, alegem Tools/Data Validation şi se alege eticheta Settings. Pasul 3. În lista Allow se selectează tipul de dată numerică impus: Whole number – număr întreg, Decimal – număr zecimal, Date – dată calendaristică, Time – oră. Pasul 4. În lista Data se alege operatorul utilizat la validare (numere între anumite limite, mai mici decât, mai mari decât etc.). În funcţie de operatorul selectat se completează zonele care urmează (în figură Start Date şi End Date deoarece s-a ales operatorul between). Aceste zone pot să conţină valori, referinţe de celule care conţin valorile, formule. Dacă se permite ca în celulele selectate să poată apărea blancuri, atunci se va bifa zona Ignore blank. Dacă se doreşte ca restricţiile impuse unei celule goale să o trateze ca şi cum ar conţine zero, atunci se debifează din Ignore blank. Pentru impunerea restricţiei pe dată alegem:

90

Pasul 5. Pentru afişarea unui mesaj de atenţionare la activarea unei celule pentru care se definesc restricţiile, din eticheta Input Message, se bifează opţiunea Show input message when cell is selected. Aceasta va determina afişarea mesajului scris în zona Input message. Acesta este afişat fie ca şi casetă de tip balon lângă Office Assistant, fie ca o casetă alăturată celulei, doar când aceasta este activã. Titlul dat în zona Title identifică mesajul şi este afişat o dată cu textul acestuia.

Pasul 6. Pentru afişarea unui mesaj de eroare, în cazul când în celulă s-a introdus o valoare care nu respectă restricţia impusă, din eticheta Error Alert se bifează Show error alert… . În lista Style se alege opţiunea dorită :  Stop - afişează un mesaj cu butoanele Retry, care permite întoarcerea la editarea celulei, şi Cancel.  Information - afişează un mesaj de informare, care are butoanele OK si Cancel.  Warning - afişează un mesaj de atenţionare cu textul "Continue?" urmat de butoanele Yes, No si Cancel. Butoanele OK şi Yes acceptă data introdusă, butonul No nu acceptă data şi lasă celula în starea de editare iarCancel reface starea precedentă a celulei. În zona Title se trece titlul mesajului de eroare, textul acestuia introducându-se în zona Error message. Pentru impunerea restricţiilor pe orele lucrate, se selectează datele, din Data/Data Tools/Data Validation/Settings se alege Whole number, between 4 şi 8 şi apoi se introduc mesajele de atenţionare şi eroare dorite utilizând etichetele Input Message şi Error Alert. Similar se procedează şi cu orele suplimentare. b) Limitarea numărului de caractere

91

Deşi este caracteristică datelor de tip text, limitarea numărului de caractere poate fi utilizată şi pentru alte tipuri de date. Pasul 1. Se selectează celule pentru care se definesc restricţiile. Pasul 2. Din meniul Data alegem Data Tools/Data Validation/Settings Pasul 3. În lista Allow se selectează Text Length. Pasul 4. În lista Data se alege operatorul dorit şi se completează apoi numărul minim de caractere, numărul maxim de caractere, potrivit operatorului selectat. Prin selectarea casetei de control Ignore blank nu se verifică restricţia pentru celulele goale sau dacă asemenea celule sunt referite în definirea limitelor. De exmplu, pentru a stabili restricţia pe marcă de 4 caractere se aleg următoarele opţiuni :

Pasul 5. Pentru mesajele de ajutor si de eroare se vor efectua etapele 5 si 6 expuse în secţiunea Restricţii privind datele numerice. Remarcă: După stabilirea restricţiilor şi a mesajelor pentru o celulă, acestea pot fi modificate prin parcurgerea etapelor următoare: Pasul 1. Se selectează celula pentru care se modifică validarea. Pasul 2. Se dă comanda Data Validation din eticheta Data, gruparea Data Tools. Pasul 3. Se modifică, pe toate etichetele, opţiunile dorite. Pasul 4. Dacă se doreşte ca aceeaşi modificare să fie efectuată pentru toate celulele care au aceleaşi restricţii şi mesaje ca şi celula curentă, se va marca zona de control Apply these changes to all other cells with the same settings de pe eticheta Settings. Restricţii impuse dintr-o lista Restrictiile impuse la culegerea datelor se pot defini si printr-o lista de valori dintre care se poate alege una. Pentru aceasta vom urma pasii: Pasul 1. Se selectează celula/celulele pentru care se aplica/modifică validarea. Pasul 2. Din eticheta Data, gruparea Data Tools se dă comanda Data Validation. Pasul 3. Din tabul Settings se alege optiunea List. Pasul 4. La Source se indica lista de valori din care se face alegerea Pasul 5. Din tabul Imput Message se poate alege un mesaj pentru operator Pasul 6. Din tabul Error Alert se face alegerea nivelului de restrictionare Pasul 4. Se finalizeaza cu Ok Pentru o mai buna protectie a listei, aceasta va putea fi plasata intr-o foaie separata, eventual ascunsa si protejata la modificare.

92

6.4.2 Auditul unei foi de calcul Prin auditul foii de calcul înţelegem operaţiunile efectuate pentru identificarea intrărilor incorecte şi evidenţierea dependenţelor (celule care depind de valorile altor celule sau celule care furnizeazã argumente pentru functii din alte celule). Se accesează eticheta Formula/gruparea Formula Auditing Instrumentele Auditing sunt prezentate în figura următoare.

Trace Precedents – Trasează săgeţi de la celulele care furnizează valori către formula din celula activă. Pentru vizualizarea tuturor nivelelor de precedenţă se acţionează butonul Trace Precedents de mai multe ori. Celula activă conţine formula care se examinează.

Remove Arrows – Elimină săgeţile create prin Trace Precedents şi Trace Dependents. Trace Dependents – Trasează săgeţi de la celula activă la celulele care conţin formule care depind de celula activă. Pentru trasarea tuturor nivelelor de dependenţă se acţionează Trace Dependents de mai multe ori.

93

Trace Error – În cazul când celula activă conţine o valoare de eroare #VALUE sau #DIV/0 trasează săgeţi la celula activă din celulele care cauzează eroarea.

Din eticheta Data/Data Tools/Data Validation:

Circle I nvalid Data – Identifică şi marchează prin încercuire celulele care conţin valori în afara limitelor stabilite la validarea datelor (comanda Validation din meniul Data).

Clear Validation Circles – Elimină marcajele de celule create prin Circle Invalid Data.

6.5 Scenarii Un scenariu este un set de valori memorat de Microsoft Excel, care poate fi introdus automat în foaia de lucru. Scenariile pot fi utilizate pentru previziuni aplicate la un anumit model dintr-o foaie de lucru. Utilizatorul poate crea şi salva diferite grupuri de valori într-o foaie de lucru şi apoi să comute la oricare din aceste scenarii pentru vedea diferite rezultate.

6.5.1 Crearea scenariilor De exemplu, dacă pentru stabilirea bugetului există incertitudini în privinţa valorilor încasărilor, se pot defini diferite valori ale încasărilor iar apoi se poate comuta între diferite scenarii pentru o analiză de tip „Ce se întâmplă dacă”.

94

În exemplul de mai sus, s-ar putea numi scenariul cazului cel mai defavorabil, completarea celulei B2 cu valoarea 50.000 LEI şi a celulei B3 cu valoarea 33.200 LEI. Scenariul cazului cel mai favorabil se obţine prin schimbarea valorii celulei B2 în 110.000 LEI şi a valorii celulei B3 în 20.000 LEI. Pentru a crea un scenariu parcurgeţi următorii paşi: 1. În eticheta Data, gruparea Data Tools, clic pe What-If Analysis

2.

Alegem Scenario Manager.

Pasul 3: Clic pe butonul Add. Apare fereastra Add Scenario în care completăm:

95

 În caseta Scenario name, tastăm un nume pentru scenariu (ex. Cazul defavorabil).  În caseta Changing cells, introducem referinţele pentru celulele pe care dorim să le modificăm (ex. B2:B3).  Faceţi clic pe OK. Apoi apare fereastra Scenario Manager, de unde putem adăuga un alt scenariu, putem sterge scenariile care nu ne covin, putem face modificări.

Remarca: Pentru a păstra valorile originale ale celulelor modificate, se crează un scenariu (numit eventual „Original”) care utilizează valorile originale ale celulelor înainte de a crea scenarii care modifică valorile.

6.5.2 Afişarea unui scenariu Când se afişează un scenariu, se vor modifica valorile celulelor salvate ca parte din acel scenariu. Pasul 1: În eticheta Data, gruparea Data Tools, clic pe What-If Analysis Pasul 2: Apare fereastra Scenario Manager, selectăm numele scenariului pe care dorim să îl afişăm Pasul 3: Clic pe Show.

6.5.3 Editarea unui scenariu Pasul 1: Pasul 2: Pasul 3: Pasul 4: Pasul 5:

În eticheta Data, gruparea Data Tools, clic pe What-If Analysis Selectaţi scenariul pe care doriţi să îl editaţi şi clic pe Edit. Faceţi modificările dorite. În fereastra Scenario Values, tastaţi noile valori. Clic pe OK.

6.5.4 Rapoarte rezumative de scenariu Pentru compararea diferitelor scenarii, se poate genera un raport rezumativ al acestora pe aceeaşi pagină. Raportul poate afişa scenariile unul lângă altul sau le poate rezuma într-un raport P ivotTable cu un clic pe Summary din fereastra Scenario Manager.

96

Pentru a afişa un raport rezumativ parcurgeţi următorii paşi: Pasul 1: În eticheta Data, gruparea Data Tools, clic pe What-If Analysis Pasul 2: Apare fereastra Scenario Manager, alegem opţiunea Summary. Pasul 3: Faceţi modificările dorite. Pasul 4: Faceţi clic pe Scenario Summary sau pe Scenario PivotTable.

Exemplu: Raport care afişează scenariile unul lângă altul:

6.5.5 Îmbinarea scenariilor din diferite foi de lucru Îmbinarea scenariilor este foarte utilă atunci când modelele de analiză „ce se întâmplă dacă” din foile de lucru, sunt identice. Pasul 1: Deschidem toate fişierele care conţin scenariile pe care dorim să le îmbinăm. Pasul 2: Revenim în foaia de lucru în care se doreşte să fie îmbinate scenariile. Pasul 3: În eticheta Data, gruparea Data Tools, executăm clic pe What-If Analysis Pasul 4: Apare fereastra Scenario Manager, alegem opţiunea Merge. Pasul 5: Apare fereastra Merge Scenarios în care selectăm fişierul ce conţine scenariile (Auto.xls), precum şi foaia de lucru (ex. modelul original). În partea de jos a ferestrei apare numărul de scenarii din foaia curentă. Pasul 6: Clic pe OK.

97

Remarca: Toate celulele modificabile din foaia de lucru activă trebuie să se fie la aceleaşi adrese ca şi cele din foaia de lucru sursă a scenariului. Programul Excel copiază toate scenariile din foaia sursă în foaia de lucru activă.

6.5.5 Ştergerea unui scenariu

Pasul 1: În eticheta Data, gruparea Data Tools, clic pe What-If Analysis Pasul 2: Selectaţi scenariul şi clic pe Delete.

6.6 Metode de prognoză a valorilor utilizând analiza de tip „what-if” 6.6.1 Soluţionarea problemelor cu o singură variabilă (GOAL SEEK) Dacă scriem o formulă ce utilizează mai multe valori, programul Excel poate căuta şi modifica o valoare astfel încât rezultatul sa devină cel aşteptat (ţinta). Exemplu: Suntem distribuitorii unui anumit produs şi până în prezent am vândut 2000 bucăţi la preţul de 3,46$. Din vinderea acestui produs am realizat încasări de 6.920$, iar ţinta vânzărilor este 100.000$.

Întrebarea noastră este: Câte produse trebuie să vând la preţul de 3,46$ pentru a atinge ţinta? Pentru aflarea răspunsului la această întrebare vom folosi intrumentul Goal Seek. Găsim un anumit rezultat pentru o celulă ajustând valoarea unei alte celule Pasul 1: În eticheta Data, gruparea Data Tools, clic pe What-If Analysis Pasul 2: Apare fereastra Goal Seek în care completaţi:

 În caseta Set cell, introduceţi referinţa către celula care conţine formula care trebuie să atingă ţinta (ex. D5).  În caseta To value, tastaţi rezultatul dorit.  În caseta By changing cell, introduceţi referinţa către celula care conţine valoarea de ajustat (ex. B5). Această celulă trebuie să fie folosită în formula din celula specificată în caseta Set cell.

98

Va apare fereastra Goal Seek Status în care programul Excel spune că a găsit (sau nu) o soluţie.

Programul Excel modifică valoarea dintr-o anumită celulă până când formula care depinde de conţinutul acelei celule returnează rezultatul asteptat.

6.7 Partajarea fisierelor Avem posibilitatea să creăm un registru de lucru partajat și să îl punem într-o locație de pe rețea, unde să poată fi editat simultan de mai multe persoane. De exemplu, dacă participanții la grupul dvs. de lucru gestionează fiecare mai multe proiecte și trebuie să se cunoască starea fiecărui proiect, grupul poate utiliza un registru de lucru partajat pentru a urmări starea proiectelor și a actualiza informațiile. Ca proprietar al registrului de lucru, putem să îl gestionăm controlând accesul la registrul de lucru și rezolvând modificările conflictuale. Când se încorporează toate modificările, se poate opri partajarea registrului de lucru. Remarcă: Nu toate caracteristicile sunt acceptate complet într-un registru de lucru partajat. Dacă dorim să adăugăm oricare dintre următoarele caracteristici, se recomandă să le adăugăm înainte să salvăm registrul de lucru ca partajat. Nu putem efectua modificările la aceste caracteristici după ce partajăm registrul de lucru.

6.7.1 Partajarea in retea Pașii care trebuie urmați pentru a partaja un fișier in retea: Pasul 1: Executăm clic pe Share Workbook din gruparea Changes, a etichetei Review: Pasul 2: În caseta de dialog Share Workbook, în fila Editing, bifăm caseta de selectare Allow changes by more than one user at the same time. This also allows workbook merging (Se acceptă modificări simultane făcute de mai mulți utilizatori). Se permite astfel și îmbinarea registrelor.

99

Pasul 3: În fila Advanced, selectăm opțiunile dorite pentru urmărirea și actualizarea modificărilor, apoi executăm clic pe OK.

Datorită instrumentului Excel Web App, mai mulți utilizatori pot deschide același fișier și pot lucra la el simultan. Dupa setarea optiunilor de partajare, trebuie sa alegem folderul din retea in care sa salvam registrul, cu Address Bar din bara de stare. Acesta trebuie sa fie un folder share, la care sa aiba acces ceilalti utilizatori ai registrului.

Daca exista link-uri in registru, vor trebui verificate si updatate. In File, alegem Save si salvam registrul in noul folder. Tot ceea ce se modifică în workbook apare în timpul setat la Automatically every în sesiunea deschisă de alt utilizator.

6.7.1.1 Urmarirea modificarilor Pasul 1: Executăm clic pe Protect and Share Workbook din gruparea Changes, a etichetei Review Pasul 2: În caseta de dialog Protect Share Workbook, bifăm caseta de selectare Shared with track changes. In acest moment, fisierul este shared si poate fi modificat (eventual cu parola setata anterior), butonul Protect Share Workbook devenind Unprotect Share Workbook. Pasul 3: Tot in acest moment, avem acces la butonul de Track changes. Va trebui sa setam in ce fel se va face memorarea schimbarilor, in fereastra Highlight Changes, respectiv cand/de cand sa se monitorizeze, pe cine sa monitorizam si unde in registru/foi sa se monitorizeze schimbarile.

100

Ulterior acestei actiuni, avem posibilitatea de a vedea si accepta sau rejecta modificarile facute de colaboratori. Pentru acesta, in eticheta Review, gruparea Changes, butonul Track changes si de acolo alegem Accept/Reject Changes Funcţia Track Changes trebuie activată înainte de a începe să ţineţi evidenţa modificărilor registrului. Pentru a activa această opţiune, în registrul dorit : Pasul 1: Din eticheta Review, gruparea Changes, alegem Track Changes / Highlight Changes. Pasul 2: Apare fereastra Highlight Changes şi bifăm Track changes while editing. Pasul 3: Selectăm modul în care vrem să ţinem evidenţa - When – pentru a determina punctul de plecare al evidenţei: Since I Last Saved (de la ultima salvare), All (toate), Not yet reviewed (modificările nerevizuite), Since Date (de la data). - Who – pentru a evidenţia modificările făcute de Everyone (toţi), Everyone but Me (toţi, în afara mea), User (numele unui utilizator). - Where – pentru a evindenţia modificările într-un anumit domeniu. Dacă nu se selectează nimic, se va urmări tot registrul. Pasul 4: Se bifează Highlight changes on screen pentru a evidenţia modificările. Pasul 5: Clic pe butonul OK. Remarcă: Detaliile privind modificarea unei celule este afişată într-o casetă de comentarii atunci când este selectată celula respectivă. În acea casetă sunt listate şi comentariile scrise de utilizatori. – Evidențierile apar şi la tipărirea foii de calcul care are activat procesul de vizualizare a modificărilor. – Sunt evidențiate prin comenzile menționate: - Schimbarea conţinutului celulelor - Liniile/coloanele inserate sau eliminate. Nu sunt evidenţiate modificările: - Formatarea - Ascunderea/afişarea liniilor/coloanelor - Denumiri ale foilor (informatie disponibilă în foaia History) - Inserarea/eliminarea foilor - Adăugarea/editarea comentariilor – acestea apar în forma curentă. - Valorilor celulelor care se modifică datorită dependenței de alte celule. Aceste modificări pot fi văzute utilizând eticheta Formula, gruparea Formula Auditing.

6.7.2 Partajarea pe Net Efectul salvarii pe WEB a fisierelor Cu optiunea de salvare pe WEB, avem posibilitatea sa partajam fisierul cu alte persoane care sa lucreze simultan in acelasi fisier cu noi. Pentru aceasta, toti cei implicati in actiune trebuie sa posede un cont de WindowsLive. Proprietarul fisierului il salveaza in File – Save&Send – Save to Web – Shared Folders (din contul lui de WindowsLive) apoi il partajeaza cu cei din echipa: alege optiunea 101

Shared File, introduce adresa / adresele @live.com si astfel ii invita (printr-un mail trimis automat) sa participe la editarea online a fisierului. Cei vizati raspund ofertei si pot edita fisierul in browser (daca nu are incompatibilitati cu browserul) sau il salveaza local si-l deschid in MS Office – Excel local, salvarea facandu-se tot pe serverele WindowsLive. La salvarea pe Web se solicita contul de @live.com al proprietarului. Dupa accesarea contului, fisierul poate fi salvat folderul Documents (pesonal) sau Public.

Dupa salvare, se acceseaza contul de @live.com si se face share pe fisierul salvat.

Corespondentul va primi un e-mail de instiintare, in contul lui de @live.com, din acel moment putand sa editeze fisierul, fie in browser, fie sa-l descarce si sa-l editeze local, salvarea facandu-se automat in locul de unde a fost descarcat (respectiv folderul proprietarului din contul de @live.com) Incetarea partajarii se face din contul propriu de @live.com prin accesarea butonului de stergere din dreptul contului corespondentului.

6.7.3 Caracteristici care nu sunt acceptate într-un registru de lucru partajat Într-un registru de lucru partajat, nu putem să:

Însă, această disponibilă:

funcționalitate

este

Creăm un tabel Excel Inserăm sau să ștergem blocuri de celule

Inserarea de rânduri și coloane întregi

Ștergem foi de lucru Îmbinăm celule sau să scindăm celule îmbinate Adăugăm sau condiționate

să

modificăm

formatări

Adăugăm sau să modificăm validări de date

Utilizarea formatării condiționate existente ca modificări a valorilor celulelor Utilizarea validarii datelor când tastăm valori noi

102

Creăm sau să modificăm grafice sau rapoarte PivotChart

Vizualizarea diagramelor și a rapoartelor existente

Inserăm sau să modificăm imagini ale altor obiecte

Vizualizarea imaginile și obiectelor existente

Inserăm sau modificăm hyperlinkuri

Utilizarea hyperlinkurile existente

Utilizăm instrumente de desen

Vizualizarea desenelor și elementelor grafice existente

Atribum, modificăm sau eliminăm parole

Utilizarea parolelor existente

Să protejăm sau să eliminăm protecția foilor sau registrelor de lucru

Utilizarea protecției existente

Creăm, modificăm sau vizualizăm scenarii Grupăm sau să schițăm date

Utilizarea schițelor existente

Inserăm subtotaluri automate

Vizualizarea subtotalurilor existente

Creăm tabele de date

Vizualizarea tabelelor de date existente

Creăm sau modificăm rapoarte PivotTable

Vizualizarea rapoartelor existente

Creăm sau să aplicăm slicere

Slicerele existente dintr-un registru de lucru sunt vizibile după ce este partajat registrul de lucru, dar ele nu se pot modifica în cazul slicerelor individuale și nu pot fi reaplicate pentru datele PivotTable sau pentru funcțiile Cube. Orice filtrare care s-a aplicat pentru slicer rămâne intactă, indiferent dacă slicerul este individual sau este utilizat de date PivotTable sau funcții Cube din registrul de lucru partajat.

Creăm sau modificăm diagrame sparkline

Diagramele sparkline existente într-un registru de lucru se afișează după partajarea registrului de lucru și se vor modifica pentru a reflecta datele actualizate. Însă, nu putem să creăm diagrame sparkline noi, să modificăm sursa lor de date sau să le modificăm proprietățile.

Scriem, inregistrăm, modificăm, vizualizăm sau atribuim macrocomenzi

Se pot executa macrocomenzile existente care nu accesează caracteristici indisponibile. Se pot înregistra operații de registru de lucru partajat într-o macrocomandă memorată în alt registru de lucru nepartajat.

Modificăm sau ștergem formule de matrice

Excel va calcula corect formulele de matrice existente

Utilizăm un formular de date pentru a adăuga date noi

Utilizăm un formular de date pentru a găsi o înregistrare

Lucrul cu date XML, inclusiv: • Importul, reîmprospătarea și exportul datelor XML 103

• • •

Adăugarea, redenumirea sau ștergerea de asocieri XML Asocierea de celule la elemente XML Utilizarea panoului de activități Sursă XML, a barei de instrumente XML sau a comenzilor XML în meniul Date

104

Capitolul 7. Tehnici de sinteză

7.1 Subtotalurile

O metodă rapidă de rezumare a datelor dintr-o listă este introducerea unor totaluri parţiale şi a unui total general pentru anumite câmpuri ale înregistrărilor. Această facilitate este utilă pentru realizarea operaţiilor de bilanţ. Etapele creării de subtotaluri a unei liste sunt: Pasul 1: Sortarea înregistrărilor în funcţie de criteriul corespunzător. Pasul 2: Clic pe eticheta Data, gruparea Outline și alegem Subtotal. Apare următoarea caseta de dialog:

 În lista At each change in se selectează numele câmpului pe care se bazează gruparea articolelor bazei de date.  Din lista Use function se selectează numele funcţiei care va fi utilizată pentru determinarea rezultatului itermediar al articolelor care aparţin aceluiaşi grup. Remarcă: Funcţia selectată implicit este Sum. Funcţiile care pot fi utilizate în calcularea rezultatelor intermediare sunt centralizate în Tabelul 1. Funcţii utilizate în subtotaluri: Sum - Suma numerelor care aparţin unui grup de date (calcularea subtotalurilor propriu-zise)  Count - Numărul celulelor nevide din interiorul unui grup de date  Average - Media aritmetică a numerelor care aparţin unui grup de date  Max - Maximul numerelor care aparţin unui grup de date  Min - Minimul numerelor care aparţin unui grup de date  Product - Produsul numerelor care aparţin unui grup de date  Count Nums - Numărul celulelor cu valori numerice în interiorul unui grup de date  StdDev - Deviaţia standard estimată a numerelor care aparţin unui grup de date (rădăcina pătrată a dispersiei estimate)  StdDevp - Deviaţia standard calculată a numerelor care aparţin unui grup de date (rădăcina pătrată a dispersiei calculate)  Var - Dispersia estimată a numerelor care aparţin unui grup de date  Varp - Dispersia calculată a numerelor care aparţin unui grup de date 

 Lista Add subtotal to conţine un şir de butoane de opţiune, care afişează numele câmpurilor din baza de date. Aceste butoane de opţiune permit selectarea acelor coloane pentru care vor fi calculate diferite rezultate intermediare. Remarcă: Este posibilă selectarea mai multor coloane.  Dacă este selectată opţiunea Replace current subtotals, atunci rezultatele intermediare calculate anterior vor fi înlocuite cu rezultate intermediare noi.

105

 Dacă este selectată opţiunea Page break between groups, atunci fiecare grup va fi depus pe o pagină nouă. Dacă este activată opţiunea Summary below data, atunci liniile corespunzătoare subtotalurilor şi totalului general vor fi inserate sub datele grupate. În cazul în care această opţiune nu este selectată liniile inserate vor apare deasupra datelor grupate. Remarcă: Se observă că la un moment dat nu se poate executa decât rezumarea pentru un singur criteriu. Dacă se doreşte rezumarea datelor pentru mai multe criterii se executa paşii de mai sus de un număr de ori egal cu numărul de criterii de rezumare, cu observaţia că celula de validare "Replace curent subtotals" trebuie să fie dezactivată.

7.2 Consolidări Prin “consolidare de date” se înţelege crearea unui raport, care combină datele care sunt memorate în diferite arii sursă. Ariile sursă şi raportul care se generează pot fi situate în aceeaşi foaie de calcul, în foi de calcul diferite din cadrul aceluiaşi registru, sau în registre diferite. În procesul de consolidare se va utiliza o funcţie (de exemplu Sum). Pentru consolidarea datelor pot fi utilizate patru metode: • Prin poziţie Datele din ariile sursă sunt ordonate identic şi sunt utilizate etichete identice. Metoda este utilizată pentru consolidarea datelor provenite dintr-o serie de foi de calcul, unde fiecare foaie de calcul a fost creată după aceeaşi structură. Această metodă poate fi folosită prin intermediul comenzii Consolidate din meniul Data. • Prin categorie Datele din ariile sursă nu sunt ordonate identic, dar sunt folosite etichete identice. Metoda este utilizată pentru consolidarea datelor provenite dintr-o serie de foi de calcul, a căror structură diferă, dar folosesc etichete identice de date. Această metodă poate fi folosită prin intermediul comenzii Consolidate din meniul Data. • Prin utilizarea referinţelor tridimensionale În cazul utilizării referinţelor tridimensionale nu există nici o restricţie referitoare la structura datelor din ariile sursă (deci nu trebuie să avem etichete identice sau o ordine prestabilită a câmpurilor). În cazul utilizării acestei metode, toate formulele necesare consolidării ariilor sursă trebuie introduse de utilizator. • Prin crearea unui tabel pivot Metoda este asemănătoare cu metoda de consolidare prin categorie, însă pentru reorganizarea categoriilor sunt puse la dispoziţia utilizatorului metode mai flexibile. Această metodă poate fi folosită prin intermediul comenzii Pivot Table Wizard din meniul Data. Pentru consolidarea datelor prin poziţie sau prin categorie: Pasul 1: Se selectează celula din colţul stânga sus al zonei de destinaţie (zona ce va conţine datele consolidate). Pasul 2: Se alege din eticheta Data, gruparea Data Tools se alege Consolidate, care va afişa o fereastră Consolidate: Pasul 3: Din lista Function se alege funcţia care va fi utilizată pentru consolidarea datelor. Funcţiile disponibile sunt: Sum, Count, Average, Max, Min, 106

Product, Count Nums, StdDev, StdDevp, Var, Varp. Pasul 4: În caseta Reference se definesc ariile sursă care urmează să fie consolidate. În cazul în care se foloseşte consolidarea prin categorie, referinţele la ariile sursă obligatoriu trebuie să conţină şi etichetele datelor. Pasul 5: Se execută comanda Add. Referinţa introdusă va fi depusă în lista All references, care conţine toate referinţele introduse anterior. Ştergerea unei referinţe poate fi realizată prin selectarea referinţei din listă şi apăsarea butonului Delete. Pasul 6: Se repetă paşii 4 şi 5 pentru fiecare din ariile sursă care urmează să fie consolidată. Pasul 7: Dacă se foloseşte consolidarea prin categorii, în zona Use labels in (utilizarea etichetelor situate în) se selectează butoanele de opţiune care indică locaţia etichetelor în ariile sursă:  Top row (etichetele sunt situate în linia superioară)  Left column (etichetele sunt situate în coloana din stânga) Există posibilitatea utilizării ambelor tipuri de etichete. Pasul 8: Dacă se doreşte ca programul Excel să actualizeze automat tabelul consolidat ori de câte ori se modifică datele sursă, atunci se va bifa butonul de opţiune Create links to source data (crearea înlănţuirilor cu datele sursă). Mai mult :  Dacă ariile sursă şi aria de destinaţie sunt situate pe aceeaşi foaie de calcul, atunci nu pot fi create înlănţuiri la datele sursă.  Dacă se utilizează consolidarea datelor prin poziţie, etichetele ariilor sursă nu sunt copiate în aria destinaţie. Dacă se doreşte ca în foaia destinaţie să apară şi etichetele de date, atunci ele trebuie copiate sau introduse manual.

7.3 Analiza datelor folosind tabelele pivot 7.3.1 Crearea tabelelor pivot Dintr-o bază de date (tabel) se poate genera un tabel pivot (denumit şi raport) prin care se pot sintetiza diferite informaţii. Comanda PivotTable Report din eticheta Data permite crearea unui raport complex în care datele sunt organizate conform criteriilor specificate de utilizator. Acest raport poate fi ulterior editat, iar datele pot fi analizate în diverse moduri noi. Înainte de a începe lucrul cu un raport PivotTable, trebuie consultată foaia de lucru Excel, pentru a vedea dacă este pregătită pentru raport. Fiecare coloană a datelor sursă devine un câmp care se poate utiliza în raport. Câmpurile rezumă mai multe rânduri de informaţii din datele sursă. Numele câmpurilor pentru raport derivă din titlurile coloanelor din datele sursă. Numele pentru fiecare coloană în primul rând al foii de lucru din datele sursă trebuie să existe. Rândurile care rămân sub titluri trebuie să conţină elemente similare în aceeaşi coloană. De exemplu, textul trebuie să fie într-o coloană, numerele în altă coloană, iar datele în altă coloană. Cu alte cuvinte, o coloană care conţine numere nu trebuie să conţină text. Nu trebuie să existe coloane necompletate în interiorul datelor care vor fi utilizate pentru raportul PivotTable. De asemenea, se recomandă să nu existe rânduri necompletate; de exemplu, rândurile necompletate care se utilizează pentru a separa un bloc de date de altul trebuie să fie eliminate.

107

Exemplu: O societate comercială vinde flori in diferite orase. În următoarea bază de date sunt memorate informaţiile referitoare la activitatea respectivă. Plante Azalee Camelia Cyclamen Ficus Iris Strelitia Trandafir Azalee Camelia Lalele Strelitia Iris Ficus Begonia Iris Strelitia Azalee Crin Camelia Ficus Iris Strelitia Trandafir

Localitate Arad Arad Arad Arad Arad Arad Arad Deva Deva Deva Deva Deva Resita Resita Resita Resita Timisoara Timisoara Timisoara Timisoara Timisoara Timisoara Timisoara

Stoc 50 30 28 52 34 65 23 45 23 12 67 23 56 75 23 53 40 23 51 25 61 32 41

Vânzări (lei) 1.200 lei 2.103 lei 1.267 lei 3.215 lei 1.258 lei 2.146 lei 1.023 lei 2.410 lei 910 lei 860 lei 3.127 lei 750 lei 2.589 lei 3.582 lei 1.094 lei 2.941 lei 3.065 lei 850 lei 3.951 lei 940 lei 2.359 lei 948 lei 1.850 lei

În cele ce urmează vom parcurge paşii prin care se poate crea un tabel pivot care conţine cantităţile totale ale produselor grupate pe ani. Pasul 1: Atunci când datele sunt pregătite, se plaseaza cursorul oriunde în tabel (date). Acest lucru va include în raport toate datele din tabel. Sau, se selectează doar datele care sunt dorite pentru raport. Apoi, pe eticheta Insert, în gruparea Tables, se accesează butonul PivotTable, apoi din nou PivotTable. Se deschide caseta de dialog Create PivotTable. Se va afişa fereastra următoare: În linia etichetei Table/Range apar automat toate datele tabelului sau se selectează regiunea de celule care conţin datele pe care vrem sa le introducem în tabelul pivot. Tot la acest pas se bifează locaţia unde se creează tabelul pivot: New Worksheet (într-o foaie nouă) sau Existing Worksheet (în foaia existentă). Pasul 2: La acest pas se defineşte structura raportului. La accesarea butonului Ok, din fereastra anterioară apare următoarea fereastra:

108

Pasul 3: În partea dreaptă apare lista câmpurilor din tabel. Aici bifăm câmpurile care vrem să apară în tabelul pivot. Se vor stabili câmpurile structurii care se plasează în: • Linia de antet a tabelului pivot (Row labels) • Coloana de antet a tabelului pivot (Column labels) • Zona de date a tabelului pivot (Values); Pentru aceste date vor fi aplicate diferite funcţii, implicit Sum sau Count • Filtrarea raportului după criteriul specificat • Pentru crearea raportului PivotTable, se bifează câmpurile dorite, programul Excel plasându-le în zonele implicite, acestea putând fi mutate prin tragere în zonele dreptunghiulare. Eliminarea acestor butoane se face prin tragere în afara ferestrei sau prin debifarea unui câmp. Exemplu: Pentru fiecare plantă, vrem să afişăm suma stocurilor şi suma vânzărilor. După ce am bifat câmpul Plante în PivotTable Field List ne apare automat câmpul în Row Labels. Punem bifa şi pe câmpurile Stoc și Vânzări iar rezultatul este următorul:

109

Zonele Row Labels şi Column Labels trebuie să conţină acele câmpuri care se plasează în linia de antet, respectiv coloana de antet. Zona Report Filter conţine câmpul sau câmpurile pentru care se va crea câte o listă ascunsă. În zona Values se introduc numele de câmpuri ale căror valori vor fi detaliate în tabelul pivot. În această zonă vor fi introduse câmpuri ce conţin date numerice sau câmpuri de date de tip text. În cazul datelor numerice se va calcula subtotalul şi totalul generat corespunzător valorilor câmpului specificat (funcţia implicită este Sum), iar în cazul datelor de tip text, funcţia care se utilizează implicit este Count. Remarcă: Dacă, în locul sumei vânzărilor vrem să calculăm media vânzărilor. În Values, executăm clic pe săgeata de la Sum of Vanzari , se afişează casuța de dialog de mai jos, apoi executăm clic pe Value Field Settings. Apare următoarea căsuţă de dialog din care putem să alegem funcţia dorită:

110

Aceleasi optiuni de la Value Field Settings le avem si in Options – Summarize Values By si Show Values By. Practic, din combinarea celor doua (ce calculam + cum afisam) putem avea orice tip de tabel pivot dorit.

In acest caz, valorile calculate (Sum of Stoc) sunt reprezentate ca procente din Grand Total. Daca dorim ca ca valorile de 0,00% sa nu mai fie afisate, formatam conditionat celule G3:K13, fontul sa fie de aceasi culoare cu fondul.

111

O dată cu trecerea mouse-ului peste unul din câmpuri în PivotTable Field List, apare o săgeată . Dacă executăm clic pe săgeata respectivă, apar elemente de sortare şi selecţia valorilor care se pot debifa pentru a nu fi afişate în tabel. Acelaşi lucru îl avem şi in capul de tabel al noului tabel construit, toate denumirile de campuri au .

Mai mult: • Dacă se dă clic în exteriorul zonei de aspect (a unui raport PivotTable). Lista de câmpuri PivotTable dispare. Pentru a o face din nou vizibilă, facem clic în interiorul zonei de aspect sau al raportului PivotTable. • Pentru modificarea numelui unui câmp din raport, se selectează câmpul respectiv şi, fie în bara de formule se tastează noul nume, fie se dă un clic în raport, eticheta Options gruparea Active fields se tastează numele. Remarcă: Dacă vrem să afişăm valorile pe rând, şi nu pe coloană tragem cu mouse-ul din Column Labels în Row Labels. Pentru exemplul de mai sus, rezultatul va fi următorul:

112

7.3.2 Formatarea tabelelor pivot

După ce am creat un tabel pivot, în momentul în care executăm un clic în interiorul tabelului pivot apar automat etichetele Options şi Design. În eticheta Design avem mai multe grupări Layout, Pivottable Style Options şi PivotTable Styles.

Din gruparea PivotTable Styles putem adăuga unul dintre stilurile alocate pentru tabelele pivot.

Putem crea chiar propriul nostru stil de tabel pivot, accesând New PivotTable Style. Din gruparea Layout putem adăuga un subtotal, un total pe rânduri şi coloane:

113

7.3.3 Filtrarea datelor din tabelele pivot La obtinerea unui tabel pivot, in capul de tabel, se observa aparitia unor săgeți de filtrare automată in dreapta numelui campului, folosite pentru a filtra si / sau sorta valorile din campul respectiv, similar cu cele din Filters. De un mare ajutor este campul Search, cu el putand selecta, pe masura ce tastam, valori dintr-o lista cu multe mii de linii.

7.3.4 Gruparea datelor în tabelele pivot În eticheta Options avem grupările: Pivot Table, Active Field, Group, Sort, Data, Actions, Tools, Show/Hide.

Pentru a grupa pe categorii elemente dintr-un tabel pivot: Pasul 1: Selectăm liniile pe care vrem să le adăugăm într-un grup (eventual cu CTRL). Pasul 2: Executăm clic pe Group Selection. Apare automat o altă coloană în care sunt afişate grupurile.

114

Pasul 3: Redenumim aceste grupe cu numele dorite de noi. Mai mult: • Dacă vrem să scăpăm de coloana din care am format grupurile o tragem efectiv în afara tabelului. • Dacă vrem să scăpăm de grupul nostru, executăm clic pe Ungroup din gruparea Group.

7.3.5 Grafice obtinute din tabele pivot Avand un tabel pivot construit deja, se poate realiza un grafic pentru vizualizarea dateleot din acest tabel. Pentru aceasta, cu celula activa in tabelul pivot, se acceseaza Options din PivotTabels Tools, gruparea Tools – Pivot Chart. De aici putem alege tipul de grafic dorit. Daca ulterior constatam ca un alt tip de grafic ar fi mai potrivit, putem comuta pe PivotChart Tools, eticheta Design, unde la gruparea Type, avem posibilitatea sa schimbam tipul cu Change Chart Type.

Graficul odata creat, va avea etichetele contextuale din PivotChart Tools – Design, Layout, Format, Analyze cu care va putea fi editat asemanator cu un grafic obisnuit.

7.3.6 Slicere

Filtrarea dupa valorile campului poate fi extinsa pentru mai multe campuri ale tabelului pivot construite pe aceleasi date initiale, cu ajutorul instrumentului Slicer. Acesta creaza o lista de campuri,

115

din fiecare camp putand filtra / sorta unele valori. Aceasta se realizeaza dand clic pe Insert Slicer, din gruparea Sort&Filter a etichetei contextuale Options din PivotTabel Tools.

Daca vom crea mai multe slicere pentru campuri diferite, selectarea unor date dintr-unul va avea ca efect afisarea campurilor corespunzatoare in celelalte. Exemplu: am bifat câmpurile Plante și Localitate

Dacă executăm clic pe una din plante (exemplu, Azalee), vom avea următorul rezultat (ne va afișa suma stoculu și suma vânzărilor pentru azalee și observăm din Slicer-ul Localitate, că azalee avem doar în Arad, Deva și Timișoara:

Mai mult decât atât, putem să personalizăm acest Slicer cu ajutorul elementelor din Slicer Tools:

116

Cu ajutorul Slicerelor putem conecta doua sau mai multe tabele pivot, in care datele afisate vor fi in concordanta cu selectarea facuta in slicer. Tabelele pivot ce se vor conecta trebuie sa fi fost obtinute din acelasi tabel initial Exemplu: Pornind de la aceleasi date initiale, am creat doua tabele pivot, in doua sheet-uri diferite, Sheet13 si Sheet15. In eticheta contextuala Option din Table Pivot Options, in gruparea Sort&Filter, alegem Insert Slicer. Aici alegem campurile pentru care dorim sa facem filtrari. Apoi, in acelasi Slicer, cu click dreapta, alegem PivotTable Connections... si selectam tabelele ce vor fi conectate.

117

Capitolul 8. Utilizarea ș abloanelor

Pentru a economisi timp, avem posibilitatea să salvăm un registru de lucru ca şablon şi să îl utilizăm la crearea altor registre de lucru. În Microsoft Office Excel 2010, un fişier şablon este salvat cu extensia (.xltx) şi poate include date şi formatări. Dacă şablonul conţine macrocomenzi atunci salvăm cu extensia (.xltm). Pentru a crea un registru de lucru nou, avem posibilitatea să utilizăm unul dintre şabloanele personale sau unul dintre şabloanele predefinite ce se pot descărca de la Microsoft Office Online. Pentru a crea un şablon urmăm paşii: Pasul 1: Deschidem registrul de lucru pe care dorim să îl utilizăm ca şablon. Pasul 2: Facem clic pe butonul File , apoi pe Save As. Pasul 3: În caseta File name, tastăm numele pe care dorim să îl utilizăm pentru şablon. Pasul 4: În caseta Save as type, facem clic pe Excel Template sau pe Excel macro - enable template dacă registrul de lucru conţine macrocomenzi pe care dorim să le facem disponibile în şablon. Pasul 5: Apoi executăm clic pe Save. Şablonul este amplasat automat în folderul Şabloane, pentru a asigura că va fi disponibil atunci când dorim să îl utilizăm pentru a crea un registru de lucru nou. Utilizarea unui șablon pentru a crea un registru de lucru nou: Pasul 1: Facem clic pe butonul File , apoi pe New. Pasul 2: In Șabloane, alegem una dintre următoarele variante: o o

o o

Pentru ca registrul de lucru să se bazeze pe un șablon utilizat recent, facem clic pe Recent templates, apoi, facem dublu clic pe şablonul pe care dorim să îl utilizăm Pentru ca registrul de lucru să se bazeze pe un șablon instalat, facem clic pe unul dintre sabloanele din zona Office.com Templates, apoi, dublu clic pe şablonul pe care dorim să îl utilizăm. Pentru ca registrul de lucru să se bazeze pe un șablon pe care l-am creat, facem clic pe My templates, apoi, facem dublu clic pe şablonul pe care dorim să îl utilizăm. Pentru ca registrul de lucru să se bazeze pe alt registru de lucru, facem clic pe New from existing, apoi facem dublu clic pe registrul de lucru pe care dorim să îl utilizăm.

118

În partea dreapta se află Search Office.com for template unde tastam un tip de sablon ce va fi cautat pe Office.com.

119

Capitolul 9. Lucrul cu macrocomenzi

9.1 Generalităţi legate de macrocomenzi

Un macro (sau o macro-comandă) reprezintă un grup de acţiuni sau operaţii. Acestea sunt memorate de Excel local (în fişierul în care lucrăm) sau global (în fişierul Personal Macro Workbook), pentru a fi utilizate şi în alte fişiere. Vom folosi macro-comenzi pentru acele secvenţe de acţiuni pe care le efectuăm în mod repetat. (de exemplu: formatarea unor celule, paginarea foilor, introducerea unor date, etc.). Macro-comenzile se execută automat când apăsăm combinaţia de taste asociată, rulăm macro-ul din lista de macro-comenzi sau apăsăm butonul asociat acestuia. Acţiunile pe care le conţine un macro sunt codificate într-un limbaj de programare Visual Basic for Application. Acest cod este creat în foaia pentru module sub forma unei subrutine ( SUB ) care efectuează acţiunile înregistrate. Pentru a crea un macro se folosesc 2 metode: Metoda1: Înregistrare Metoda2: Introducerea instrucţiunilor. Pentru a modifica o macrocomanda înregistrată sau pentru a scrie o macrocomandă proprie sunt necesare cunoştiinţe minimale de Visual Basic. Intrarea în mediul Visual Basic se poate face utilizând comanda Visual Basic aflată în eticheta Developer sau, mai rapid, folosind combinaţia de taste ALT+F11.

120

9.5 Înregistrarea unei macro-comenzi O macro-comandă lucrează ca un casetofon: l-am pornit şi înregistrează acţiunile pe care le “traduce” în limbaj VBA, pana la comanda de oprire a inregistrarii apoi „reda” (executa) comenzile memorate. Pentru a crea macrocomenzi parcurgeţi următorii paşi: Pasul 1: Alegem comanda Record Macro din eticheta View / Macro sau din eticheta Developer

sau de pe bara de stare

.

Pasul 2: Apare fereastra următoare unde completăm numele macro-comenzii (fără spaţii), alegem o combinaţie de taste (shortcut key) pentru execuţia rapidă a macro-ului, alegem locul de stocare a macro-comenzii (în fişierul curent – This Workbook, într-un fişier nou – New Workbook sau global, pentru accesul din orice fişier Excel - Personal Macro Workbook ). Opţional, putem scrie o scurtă descriere a acţiunii macro-comenzii.

121

Pasul 3: Dacă dorim ca înregistrarea să se efectueze folosind adrese relative alegem opţiunea Relative Reference (butonul sa fie portocaliu) din eticheta Developer sau View / Macros. Optiunea este utila atunci cand dorim ca actiunile care fac parte din macrocomanda sa porneasca din celula activa la un moment dat si nu din mereu acelasi loc.

Pasul 4: Se executa toate comenzile care trebuiesc înregistrate. Ex.: Page Layout / Page Setup / Margins : Top :2, Bottom :2, Left : 2, Right : 2 ; Page Layout / Page Setup / Header şi introduceţi numele utilizatorului şi data în stânga; Page Layout / Page Setup / Footer şi introduceţi numărul paginii curente în dreapta. Pasul 5: Se opreşte înregistrarea apăsând butonul Stop Recording de pe bara de stare, din eticheta Developer sau View. Exemplu: Crearea unei macrocomenzi cu numele Date_firmă.          

Nume: Date_firma Combinaţia de taste: Ctrl + y. Localizare: This Workbook Descriere: Introducere date firma Activam Relative Reference pentru ca datele să fie scrise începând cu celula activă Acţiuni: scriem datele firmei în celula curentă: Infotim ETA2U Training Center enter introducem adresa: Str. C. Brediceanu, nr. 8 Stop înregistrare

În urma înregistrării unei macro-comenzi, programul Excel generează o secvenţă de program VBA, de exemplu: Sub Date_firma() ' ' Date_firma Macro ' Introducere date firma ' ' Keyboard Shortcut: Ctrl+y ' ActiveCell.FormulaR1C1 = "Infotim ETA2U Training Center" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "Str. C. Brediceanu, nr. 8" ActiveCell.Offset(0, 1).Range("A1").Select End Sub

122

9.6 Execuţia unei macrocomenzi Un macro poate fi executat astfel: - din eticheta View / Macros / View Macro sau Developer / Macros ajungem în fereastra Macro - se selectează macro-ul dorit din lista Macro Name. - se apasă butonul Run Obs. Mai rapid se poate executa o macro-comandă cu combinaţia de taste asociate. O modalitate mai comoda de executie a unei macrocomenzi ar fi sa-i atribuim un buton pe ribbon. Aceasta se poate face astfel: Pasul 1: Cu clic dreapta pe ribbon alegem Customize the Ribbon... Pasul 2: In partea dreapta, facem clic pe butonul New Tab iar noul tab creat il redenumim (Macrocomenzi). Pasul 3: In cadrul tabului nou creat, modificam numele New Group... atibuindu-i o iconita Pasul 4: In partea stanga, din lista derulanta Chose command from: alegem Macros Pasul 5: Din lista de macrouri, selectam macroul care ne intereseaza si-l adaugam in noul grup tocmai redenumit cu butonul Add >> Pasul 5: Inchidem fereastra, confirmand cu Ok. Ulterior putem adauga macrocomenzi sub forma de butoane in acelasi grup, avandu-le astfel grupate si la indemana.

9.7 Modificarea unei macrocomenzi Modificarea unei macro-comenzi presupune intervenţia în succesiunea comenzilor înregistrare cu ajutorul editorului Visual Basic. Pasul 1: Din eticheta View, alegem Macros - View Macro sau Developer / Macros ajungem în fereastra Macro Pasul 2: Selectăm numele macrocomenzii. Pasul 3: Apăsăm butonul Edit şi va apărea fereastra de editare in VBA Pasul 4: Modificăm acţiunile înregistrate (cu ajutorul VBA). De exemplu, adăugăm la adresă localitatea Timişoara: Pasul 5: Închidem fereastra de editare.

9.8 Ştergerea unei macro-comenzi Pasul 1: Din eticheta View, alegem Macros şi View Macro sau Developer / Macros ajungem în fereastra Macro Pasul 2: Selectăm numele macrocomenzii. Pasul 3: Delete.

123

Anexa 1. Caracteristici care nu sunt acceptate ı̂ntr-un registru de lucru partajat

Într-un registru de lucru partajat, nu putem să:

Însă, această funcționalitate este disponibilă:

Creăm un tabel Excel Inserăm sau să ștergem blocuri de celule

Inserarea de rânduri și coloane întregi

Ștergem foi de lucru Îmbinăm celule sau să scindăm celule îmbinate Adăugăm sau să modificăm formatări condiționate

Utilizarea formatării condiționate existente ca modificări a valorilor celulelor

Adăugăm sau să modificăm validări de date

Utilizarea validarii datelor când tastăm valori noi

Creăm sau să modificăm grafice sau rapoarte PivotChart

Vizualizarea diagramelor și a rapoartelor existente

Inserăm sau să modificăm imagini ale altor obiecte

Vizualizarea imaginile și obiectelor existente

Inserăm sau modificăm hyperlinkuri

Utilizarea hyperlinkurile existente

Utilizăm instrumente de desen

Vizualizarea desenelor și elementelor grafice existente

Atribum, modificăm sau eliminăm parole

Utilizarea parolelor existente

Să protejăm sau să eliminăm protecția foilor sau registrelor de lucru

Utilizarea protecției existente

Creăm, modificăm sau vizualizăm scenarii Grupăm sau să schițăm date

Utilizarea schițelor existente

Inserăm subtotaluri automate

Vizualizarea subtotalurilor existente

Creăm tabele de date

Vizualizarea tabelelor de date existente

Creăm sau modificăm rapoarte PivotTable

Vizualizarea rapoartelor existente

Creăm sau să aplicăm slicere

Slicerele existente dintr-un registru de lucru sunt vizibile după ce este partajat registrul de lucru, dar ele nu se pot modifica în cazul slicerelor individuale și nu pot fi reaplicate pentru datele PivotTable sau pentru funcțiile Cube. Orice filtrare care s-a aplicat pentru slicer rămâne intactă, indiferent dacă slicerul este individual sau este utilizat de date PivotTable sau funcții Cube din registrul de lucru partajat.

Creăm sau modificăm diagrame sparkline

Diagramele sparkline existente într-un registru de lucru se afișează după partajarea registrului de lucru și se vor modifica pentru a reflecta datele actualizate. Însă, nu putem să creăm diagrame sparkline noi, să modificăm sursa lor de date sau să le modificăm proprietățile.

Scriem, inregistrăm, modificăm, vizualizăm sau atribuim macrocomenzi

Se pot executa macrocomenzile existente care nu accesează caracteristici indisponibile. Se pot înregistra operații de registru de lucru partajat într-o macrocomandă memorată în alt registru de lucru nepartajat.

124

Modificăm sau ștergem formule de matrice

Excel va calcula corect formulele de matrice existente

Utilizăm un formular de date pentru a adăuga date noi

Utilizăm un formular de date pentru a găsi o înregistrare

Lucrul cu date XML, inclusiv: • Importul, reîmprospătarea și exportul datelor XML • Adăugarea, redenumirea sau ștergerea de asocieri XML • Asocierea de celule la elemente XML • Utilizarea panoului de activități Sursă XML, a barei de instrumente XML sau a comenzilor XML în meniul Date

125

Category Auditing

Anexa 2. Shortcut-uri in Excel 2010 Command Name

Key Stroke

Display Cell Formulas (Toggle)

Ctrl `

Auditing

Edit Links

Alt ek

Auditing

Find

Ctrl f / Alt ef / Shift F5

Auditing

Highlight Direct Cell Dependents

Ctrl ]

Auditing

Highlight Direct Cell Precedents

Ctrl [

Auditing

Highlight Indirect Cell Dependents

Ctrl }

Auditing

Highlight Indirect Cell Precedents

Ctrl {

Auditing

Replace

Ctrl h / Alt ee

Auditing

Show Dependent Arrows

Alt tud

Auditing

Show Precedent Arrows

Alt tut

Auditing

Smart Dependents

Ctrl Shift }

Auditing

Smart Precedents

Ctrl Shift {

Auto Filling

Smart Fill Dates

Ctrl Alt d

Auto Filling

Smart Fill Numbers

Ctrl Alt f

Clear/Delete

Clear Everything Within Selection

Alt eaa

Clear/Delete

Delete Selection

Alt ed

Clear/Delete

Edit Delete Sheet

Alt el

Commands

Edit Redo (Reverse Undo)

Ctrl y / Alt er

Commands

Edit Undo

Ctrl z / Alt eu

Commands

Repeat Last Command

F4 / Ctrl y / Alt Enter

Copy/Paste

Copy Down (Excel)

Ctrl d

Copy/Paste

Copy Formula From Cell Above

Ctrl '

Copy/Paste

Copy Right (Excel)

Ctrl r

Copy/Paste

Copy Value From Cell Above

Ctrl Shift "

Copy/Paste

Paste Special - Formats

Alt est Enter

Copy/Paste

Paste Special - Formulas

Alt esf Enter

Copy/Paste

Paste Special - Values

Alt esv Enter

Copy/Paste

Smart Copy Down

Ctrl Shift D

Copy/Paste

Smart Copy Right

Ctrl Shift R

Copy/Paste

Smart Formula Column Spacing

Ctrl Alt l

Copy/Paste

Smart Formula Row Spacing

Ctrl Alt r

Copy/Paste

Smart Formula Transpose

Ctrl Alt t

Cut/Copy/Move/Paste/Insert

Copy

Ctrl c / Alt ec

Cut/Copy/Move/Paste/Insert

Copy/Move Sheet

Alt em

Cut/Copy/Move/Paste/Insert

Cut

Ctrl x / Alt et

Cut/Copy/Move/Paste/Insert

Insert (Copied) Cells

Alt ie

Cut/Copy/Move/Paste/Insert

Paste

Ctrl v / Alt ep

Files

File Close

Alt fc

Files

File New

Ctrl n / Alt fn

Files

File Open

Ctrl o / Alt fo / Ctrl F12

126

Files

File Save

Ctrl s / Alt fs

Files

File Save As

F12 / Alt fa

Formatting

Apply DM Paintbrush Style

Ctrl Alt v

Formatting

Apply Sheet Style

Ctrl Alt 2

Formatting

Capture DM Paintbrush Style

Ctrl Alt c

Formatting

Cell Color Toggle

Ctrl Shift K

Formatting

Cell Formatting Dialog Box

Ctrl 1 / Alt oe

Formatting

Edit Clear Formats

Alt eaf

Formatting - Alignment

Center Toggle

Ctrl Shift C

Formatting - Alignment

Horizontal Alignment Toggle

Ctrl Shift H

Formatting - Alignment

Indent Toggle

Ctrl Shift I

Formatting - Alignment

Vertical Alignment Toggle

Ctrl Shift V

Formatting - Borders

Border None

Ctrl Shift _

Formatting - Borders

Border Outline

Ctrl Shift &

Formatting - Borders

Border Outline

Ctrl Shift &

Formatting - Borders

Border Toggle

Ctrl Shift B

Formatting - Borders

Remove Borders

Ctrl Shift _

Formatting - Font

AutoColor Sheet

Ctrl Alt 1

Formatting - Font

Blue Black Color Toggle

Ctrl ;

Formatting - Font

Bold

Ctrl b

Formatting - Font

Decrease Font Size

Ctrl Shift G

Formatting - Font

Increase Font Size

Ctrl Shift F

Formatting - Font

Italics

Ctrl i

Formatting - Font

Purple Red Green Color Toggle

Ctrl '

Formatting - Font

Strikethrough

Ctrl 5

Formatting - Font

Underline

Ctrl u

Formatting - Font

Underline Toggle

Ctrl Shift U

Formatting - Number Format

Binary Number Toggle

Ctrl Shift Y

Formatting - Number Format

Currency Toggle

Ctrl Shift $

Formatting - Number Format

Date Toggle

Ctrl t

Formatting - Number Format

Decrease Decimal

Ctrl k

Formatting - Number Format

General Number Toggle

Ctrl Shift !

Formatting - Number Format

Increase Decimal

Ctrl j

Formatting - Number Format

Multiple Toggle

Ctrl Shift *

Formatting - Number Format

Percent Toggle

Ctrl Shift %

Formatting - Rows/Columns

Auto Column Width

Ctrl Alt 6

Formatting - Rows/Columns

Auto Row Height

Ctrl Alt 5

Formatting - Rows/Columns

Column Autofit

Alt oca

Formatting - Rows/Columns

Column Standard Width

Alt ocs

Formatting - Rows/Columns

Column Width

Alt ocw

Formatting - Rows/Columns

Column Width Toggle

Ctrl Alt 4

Formatting - Rows/Columns

Row Autofit

Alt ora

Formatting - Rows/Columns

Row Height

Alt ort

Formatting - Rows/Columns

Row Height Toggle

Ctrl Alt 3

Formulas

Absolute/Relative Reference

F4

Formulas

Calculate Formulas

F9

127

Formulas

Check for Errors

Ctrl e

Formulas

Delete Remainder of Entry

Ctrl Delete

Formulas

Enter/Exit Cell Formula Bar

F2

Formulas

Fill Selection With Entry

Ctrl Enter

Formulas

Finish Entry

Enter

Formulas

Finish Entry and Move Left

Shift Tab

Formulas

Finish Entry and Move Right

Tab

Formulas

Insert Defined Name in Formula

F3

Formulas

Insert Function (Function Wizard)

Shift F3

Formulas

Insert Function Arguments

Ctrl Shift A

Formulas

Leader Dots

Ctrl .

Formulas

New Line in Cell Entry

Alt Enter

Formulas

Sum Bar

Ctrl Shift S

Formulas

Sum Formula

Alt =

Hiding

Group Rows/Columns

Alt Shift Right Arrow

Hiding

Hide Columns

Ctrl 0

Hiding

Hide Rows

Ctrl 9

Hiding

Smart Hide

Ctrl Alt h

Hiding

Ungroup Rows/Columns

Alt Shift Left Arrow

Hiding

Unhide Columns

Ctrl Shift 0

Hiding

Unhide Rows

Ctrl Shift 9

Insert New

Insert Chart Sheet

F11

Insert New

Insert Column

Alt ic

Insert New

Insert Row

Alt ir

Insert New

Insert Worksheet

Alt iw / Shift F11

Insert/Delete

Delete Column

Ctrl Alt 0

Insert/Delete

Delete Row

Ctrl Alt 9

Insert/Delete

Insert Column

Ctrl Alt 8

Insert/Delete

Insert Row

Ctrl Alt 7

Navigation

Add Bookmark

Ctrl Alt b

Navigation

Clear Bookmarks

Ctrl Alt x

Navigation

Go To...

F5 / Ctrl g / Alt eg

Navigation

Move Clockwise Within Selection

Ctrl .

Navigation

Move Down

Down Arrow

Navigation

Move Down Within Selection

Enter

Navigation

Move Left

Left Arrow

Navigation

Move Left Within Selection

Tab

Navigation

Move Right

Right Arrow

Navigation

Move Right Within Selection

Shift Tab

Navigation

Move To Bottom Right Cell

Ctrl End

Navigation

Move To Cell A1

Ctrl Home

Navigation

Move To Column A

Home

Navigation

Move To Last Used Column

End, Enter

Navigation

Move Up

Up Arrow

Navigation

Move Up Within Selection

Shift Enter

Navigation

Next Bookmark

Ctrl Alt n

128

Navigation

Next Region Within Selection

Ctrl Alt Right Arrow

Navigation

Next Workbook

Ctrl Tab / Ctrl F6

Navigation

Next Worksheet

Ctrl Page Down

Navigation

Next Worksheet Loop

Ctrl Page Down

Navigation

Page Down

Page Down

Navigation

Page Left

Alt Page Up

Navigation

Page Right

Alt Page Down

Navigation

Page Up

Page Up

Navigation

Previous Bookmark

Ctrl Alt p

Navigation

Previous Region Within Selection

Ctrl Alt Left Arrow

Navigation

Previous Workbook

Ctrl Shift Tab / Ctrl Shift F6

Navigation

Previous Worksheet

Ctrl Page Up

Navigation

Previous Worksheet Loop

Ctrl Page Up

Navigation

Quick Move Down

Ctrl Down Arrow

Navigation

Quick Move Left

Ctrl Left Arrow

Navigation

Quick Move Right

Ctrl Right Arrow

Navigation

Quick Move Up

Ctrl Up Arrow

Other

Activate/Deactivate Menu Bar

F10 / Alt

Other

Edit Clear Comments

Alt eam

Other

Help

F1

Other

Help (Context Sensitive)

Shift F1

Other

Insert Comment

Alt im / Shift F2

Other

Insert Current Date

Ctrl ;

Other

Insert Current Time

Ctrl Shift :

Other

Insert Hyperlink

Ctrl k / Alt ii

Other

Insert Name (Define)

Alt ind / Ctrl F3

Other

Next Toolbar

Ctrl Tab

Other

Previous Toolbar

Shift F10

Other

Run Macro

Alt F8

Other

Show Right Click Menu

Shift F10

Other

Show/Hide Standard Toolbar

Ctrl 7

Other

Spelling Check

F7

Other

Tools Options

Alt to

Other

Visual Basic Editor (Toggle w/ Excel)

Alt F11

Printing

File Clear Print Area

Alt ftc

Printing

File Page Setup

Alt fu

Printing

File Print

Ctrl p / Alt fp

Printing

File Print Preview

Alt fv

Printing

File Set Print Area

Alt fts

Printing

Insert Page Break

Alt ib

Selecting

Extend Selection Page Down

Shift Page Down

Selecting

Extend Selection Page Left

Shift Alt Page Up

Selecting

Extend Selection Page Right

Shift Alt Page Down

Selecting

Extend Selection Page Up

Shift Page Up

Selecting

Highlight Down

Shift Down Arrow

Selecting

Highlight Left

Shift Left Arrow

129

Selecting

Highlight Right

Shift Right Arrow

Selecting

Highlight Up

Shift Up Arrow

Selecting

Only Select Active Cell

Shift Backspace

Selecting

Quick Highlight Down

Ctrl Shift Down Arrow

Selecting

Quick Highlight Left

Ctrl Shift Left Arrow

Selecting

Quick Highlight Right

Ctrl Shift Right Arrow

Selecting

Quick Highlight Up

Ctrl Shift Up Arrow

Selecting

Select All (Entire Worksheet)

Ctrl a / Ctrl Shift Spacebar

Selecting

Select Column

Ctrl Spacebar

Selecting

Select Region Around Active Cell

Ctrl Shift *

Selecting

Select Row

Shift Spacebar

Selecting

Select To Bottom Right Cell

Ctrl Shift End / End, Shift Home

Selecting

Select To Cell A1

Ctrl Shift Home

Selecting

Select To Column A

Shift Home

Selecting

Select To Last Used Column

End, Shift Enter

Selecting

Set/Clear Selection Anchor

F8

View

Decrease Zoom

Ctrl Alt -

View

Increase Zoom

Ctrl Alt =

View

Split Window Toggle

F6

View

View Normal

Alt vn

View

View Page Breaks

Alt vp

View

View Zoom

Alt vz

View

Window Freeze/Unfreeze

Alt wf

View

Window Split

Alt ws

Windows

Close Application

Alt F4

Windows

Close Window

Ctrl F4 / Ctrl w

Windows

Maximize Window

Ctrl F10

Windows

Maximize Window

Ctrl Shift O

Windows

Minimize Window

Ctrl F9

Windows

Next Application

Alt Tab

Windows

Previous Application

Alt Shift Tab

Windows

Restore Window Size

Ctrl F5

Windows

Training Window Size

Ctrl o

Windows

Windows Start Menu

Ctrl Esc

130