Excel Profesional

E CEL

_profesional Nr. 1/noiembrie 2006

Unica revist` cu sfaturi practice pentru speciali[tii \n calcul tabelar

SUMAR

Trucuri [i sfaturi

Excel [i managementul eficient al proiectelor . . . . . . . . . . . . . . . 1 Problema . . . . . . . . . . . . . . . . . . . 1 Definirea foilor de calcul cu date de intrare . . . . . . . . . . . . 2 Restric]ionarea introducerii de date la valorile dintr-o list` . . . . . . . . . . . . . . . . . . . 5 Introducerea datelor \n Excel \ntr-o structur` asem`n`toare unei baze de date . . . . . . . . . . . . . . . . . . . . . 8 Crearea din punct de vedere vizual a unui Grafic Gantt . . . . . . . . . . . 9 Folosirea formulei matrice SUM(IF) . . . . . . . . . . . . . . . . . . . . 10 Formatarea condi]ional` . . . . . . 12 Ascunderea valorilor de zero inutile . . . . . . . . . . . . . . . . . 13 Ascunderea valorilor din celulele colorate . . . . . . . . . . . . . 14 Crearea unei foi de calcul care s` arate alocarea resurselor umane pe activitate . . . . . . . . . . . . . . . . . . . 14 Crearea unei foi de calcul care s` arate alocarea resurselor umane pe lun` . . . . . 15 Avantajele [i dezavantajele aplica]iei . . . . . . . . . . . . . . . . . . . 16

Probleme [i solu]ii

Formulare

|ntreb`ri [i r`spunsuri

Aplica]ii

Scurt`turi de taste

Formule utile

Erori [i explica]ii

R&S

RENTROP & STRATON

www.rs.ro

EDITORIAL

Pentru a EXCELa

Florian Filat redactor-[ef

Excel este unul dintre programele cel mai des folosite \n \ntreaga lume. |n orice firm` a]i merge, g`si]i fi[iere Excel, \ncercånd s` r`spund` nevoilor particulare ale celor mai diverse departamente, de la resurse umane la contabilitate, de la vånz`ri la logistic`, de la achizi]ii la financiar. |n orice loc, o „situa]ie“ realizat` \n Excel vine s` completeze ceea ce sistemele informatice integrate din firm` pot oferi. |n firmele din Romånia, cei mai mul]i utilizatori folosesc programul Excel la un nivel mediu. Dup` \nv`]area conceptelor [i tehnicilor de baz`, se construiesc diferite fi[iere \n Excel pentru a eficientiza munca [i a ob]ine diverse rapoarte, esen]iale pentru activitatea angaja]ilor [i a companiilor. La un moment dat \ns`, fi[ierele create ajung s` fie insuficiente pentru nevoile existente. Uneori se ive[te un raport urgent, ave]i toate datele, dar nu [ti]i ce formule s` folosi]i. Alteori constata]i c` opera]iunile pe care le efectua]i sunt repetitive [i v` gåndi]i c` ar trebui s` existe o modalitate de a le automatiza, dar nu [ti]i de unde s` \ncepe]i. {i, desigur, exist` momente \n care „sim]i]i“ c` dureaz` prea mult timp crearea unei „situa]ii“ [i nici nu sunte]i sigur dac` nu cumva a]i gre[it pe undeva... Intuitiv, realiza]i c` ar trebui s` existe pe undeva o metod` mai simpl`, dar... |n toate aceste situa]ii [i altele similare, cuno[tin]ele medii de Excel nu v` sunt suficiente. Ave]i nevoie de ceva mai mult. Materialele existente pe pia]` nu ajut` prea mult \n aceast` privin]`. Cele mai multe se adreseaz` unui grup-]int` foarte eterogen. |n acest sens, firmele au de suferit. Datorit` specificului firmei, apar particularit`]i care ]in de natura datelor de afaceri, neabordate \ntr-o lucrare obi[nuit`. Pe de alt` parte, pe pia]` g`si]i lucr`ri pentru nivelul \ncep`tor/mediu [i aproape deloc pentru mediu/avansat. Dar lipsa cea mai mare este de fapt \n alt` parte. Lucr`rile obi[nuite prezint` \n special tehnici disparate care rezolv` probleme \n mare

parte simple. |ntr-o firm`, problemele sunt \ns` complexe [i utilizatorul trebuie s` „combine“ diversele tehnici Excel. Nu este \ntotdeauna u[or s` alege]i din multitudinea de facilit`]i Excel. Cele alese trebuie s` „lucreze \mpreun`“. Ineficien]a celor mai multe foi de calcul de aici provine. Foile folosesc tehnici bune luate fiecare \n parte, dar ineficiente luate \mpreun`. „Excel_profesional“ \[i propune s` rezolve aceste dificult`]i. „Excel_profesional“ este mai mult decåt o simpl` revist`. Este un proiect complex care are ca scop sprijinirea utilizatorilor Excel \n dobåndirea unor tehnici avansate de folosire a programului Excel pentru rezolvarea problemelor concrete cu care se confrunt`. Revista nu se adreseaz` mae[trilor \n Excel. Nici celor multumi]i cu nivelul pe care \l au. Este destinat` celor care caut` permanent solu]ii de \mbun`t`]ire, care [tiu c` pentru problemele concrete cu care se confrunt` exist` cu siguran]` rezolv`ri ingenioase. Celor care nu spun „a[a ceva nu se poate“, ci „\nc` nu [tiu cum se face, dar voi afla“. Exemplele care vor fi prezentate \n revist` se bazeaz` toate pe realitatea unor firme din Romånia. {i vor acoperi pe cåt posibil probleme diverse cu care se confrunt` toate departamentele. Prezen]a \n lucrare a unui caz ce aparent nu are leg`tur` cu activitatea dvs. nu este o pierdere, ci, din contr`, un cå[tig. Tehnicile prezentate (chiar dac` nu se refer` concret la situa]ia dvs.) pot fi u[or adaptate altor contexte. Pentru c` aceast` revist` reprezint` undi]a ce v` sluje[te oricånd la pescuit, nu por]ia de pe[te pentru o singur` mas`. {ti]i c` [ansele de cre[tere a procentului de 10% de exploatare a creierului sunt aproape nule? M`car programul Excel s`-l folosim \n propor]ie mai mare!

Problem` & Solu]ie

Excel [i managementul eficient al proiectelor

Problem`:

r`im \n prezent \ntr-o societate a proiectelor. Aproape c` nu mai putem desf`[ura nici o activitate dac` nu este inclus` \ntr-un proiect. TPlanificarea unui proiect este o sarcin` foarte laborioas` de care depinde, de

Solu]ie:

cele mai multe ori, succesul sau insuccesul unui proiect. {i dup` ce a durat cåteva zile s` corela]i cåt de cåt datele, se \ntåmpl` s` vin` [eful [i s` solicite cåteva „mici“ modific`ri. Este inutil s` \i spune]i c` toate schimb`rile, aparent mici [i inofensive, pot \nseamna alte ore de calcule [i, \n plus, nu pute]i fi 100% sigur c` opera]i modific`rile peste tot. Cu ajutorul programului Excel pute]i crea \ns` o mini-aplica]ie extrem de puternic` care face aproape automat toat` planificarea de care ave]i nevoie pentru un proiect. Nu este complicat`, con]ine doar cåteva foi de calcul, formule [i format`ri condi]ionale. V` va lua circa dou` ore ca s` crea]i aplica]ia [i apoi circa treizeci de minute ca s` o adapta]i pentru oricare alt proiect. Eficien]`, nu?

Problema T.R. lucreaz` \ntr-o agen]ie de publicitate [i este responsabil de implementarea de campanii de con[tientizare pentru diferi]i clien]i. Are permanent mai multe campanii \n derulare, aflate \n diferite etape ale implement`rii. Echipa pe care o are la dispozi]ie este implicat` aproape \n fiecare proiect, \ntr-o m`sur` mai mare sau mai mic`. F`r` o planificare foarte atent`, proiectele lui T.R. sunt extrem de dificil de implementat. Problema este c` planificarea unui proiect este foarte laborioas`, iar T.R. uneori face [i erori mici, dar care afecteaz` apoi \ntregul proiect. Pe de alt` parte, \i ia prea mult timp, \n loc s` se concentreze pe lucrurile cu adev`rat importante... |n prezent, T.R. dore[te s` implementeze o campanie na]ional` de con[tientizare a publicului privind pericolele consumului de droguri. Proiectul este complex, are mai multe componente [i activit`]i, se deruleaz` pe 11 luni [i sunt implicate circa 20 de persoane. Un poten]ial co[mar... Dar T.R. s-a decis s`-[i creeze un instrument \n Excel care s`-l ajute acum [i pe viitor \n planificarea proiectelor. T.R.: „Mi se pare atåt de complex \ncåt nici nu [tiu de unde s` \ncep.“ La \nceput trebuie s` stabili]i foarte clar care sunt datele de intrare [i care sunt datele de ie[ire. Cu alte cuvinte, ce date trebuie s` ave]i disponibile, pe care s` le lua]i \n considerare [i care sunt datele pe care dori]i s` le ob]ine]i.

R&S

RENTROP & STRATON

Datele de ie[ire |n fiecare proiect, exist` practic minimum 3 tipuri de rapoarte pe care dori]i s` le ave]i: 1 – Grafic Gantt (care arat` \ntinderea \n timp a fiec`rei activit`]i); 2 – Alocarea resurselor umane pe activit`]i (\n ce activit`]i se implic` fiecare persoan` alocat` pe proiect [i care este contribu]ia respectivei persoane la fiecare activitate, \n zile om de munc`); 3 – Alocarea resurselor umane \n timp (cåt lucreaz` fiecare persoan` alocat` pe proiect \n fiecare lun` de implementare a proiectului). Ar mai putea exista [i alte rapoarte, dar pentru simplificarea exemplului ne vom limita la cele de mai sus.

EXCEL_profesional

|n fi[ierul ata[at (PONTAJ.xls) a[ dori s` calculez automat num`rul de ore de concediu medical (BO) sau concediu de odihn` (CO)

|n domeniul AL10:AM34 se introduc formulele care calculeaz` totalul de ore pentru CO (concediu de odihn`) [i BO (concediu medical). Pornim de la prezum]ia c` ziua de BO/CO are 8 ore, similar cu zilele care sunt pontate. Ce face formula folosit`: 1. num`r` de cåte ori apare BO sau CO \n linia aferent` unui angajat – func]ia COUNTIF; 2. rezultatul \l \nmul]e[te cu 8 (num`rul de ore pentru BO sau CO). COUNTIF are urm`toarea sintax` foarte simpl`: COUNTIF(domeniu_celule; criteriu) domeniu_celule = domeniul de celule \n care se va face „num`r`toarea“ criteriu = ce criteriu dori]i s` aplica]i pentru „num`r`toare“ |n cazul nostru, domeniul de celule este C10:AH10 pentru un angajat, iar criteriul „BO“ (se pun ghilimelele pentru c` se caut` o valoare text), adic` num`r` de cåte ori apare BO \n domeniul de celule respectiv. Formula care se \nscrie \n celula AM10

noiembrie 2006 ● pag. 1

Trucuri & Sfaturi

Excel [i managementul eficient al proiectelor Datele de intrare

Transformarea rezultatelor formulelor \n valori Dac` \n foile de calcul pe care le construi]i folosi]i foarte multe formule [i dori]i s` transforma]i rezultatele formulelor \n valori, atunci cånd nu mai ave]i nevoie de actualizarea leg`turilor create... iat` cum proceda]i: 1. selecta]i formulele fie manual, fie cu CTRL + G, Special, Formulas;

Analizånd datele de ie[ire, putem vedea c` acestea reprezint` combina]ii \ntre: – activit`]i \n proiect – resurse alocate (exper]i implica]i \n proiect) – lunile de desf`[urare a activit`]ilor – num`rul de zile om pe care o persoan` de pe proiect trebuie s` le lucreze pentru a implementa o activitate. Activit`]ile-exper]ii-lunile-num`rul de zile om reprezint` practic datelecheie pe care trebuie s` le ave]i \n vedere pentru a face o planificare corect` [i cuprinz`toare. Acum toate aceste informa]ii trebuie transformate \n tabel Excel. Este recomandat ca fiecare tip/grup de informa]ie s` fie stocat \ntr-un tabel/foaie de calcul separat`.

2. copia]i formulele cu CRTL + C sau ap`sånd Copy;

Definirea foilor de calcul cu date de intrare

3. alege]i Edit, Paste Special (Lipire special`);

Foaia de calcul Componente Pune]i drept cap de tabel Componenta, Activitate [i Numar activitate [i apoi introduce]i datele aferente. Foaia de calcul va ar`ta ca mai jos:

4. \n caseta de dialog care apare bifa]i op]iunea Values (Valori); 5. ap`sa]i OK [i formulele sunt \nlocuite cu rezultatele lor.

Formular

Rambursare \mprumut

Inten]iona]i s` lua]i un \mprumut [i dori]i s` stabili]i suma maxim` pe care o pute]i primi [i durata de timp pe care se poate \ntinde \mprumutul, \n func]ie de posibilit`]ile de rambursare a sumei pe care le ave]i. V` oferim un instrument care v` ajut` s` v` face]i o impresie \nainte de a merge la banc` pentru a cere detalii. Formularul v` ofer` posibilitatea de a identifica rapid sumele anuale ce trebuie rambursate \n urma efectu`rii unui \mprumut. Tot ce ave]i de f`cut este s` introduce]i \n formular suma \mprumutat` (C2), dobånda anual` (C3) [i durata \mprumutului \n ani (C4). pag. 2 ● noiembrie 2006

EXCEL_profesional

Excel [i managementul eficient al proiectelor |n exemplul nostru, proiectul are 4 componente, fiecare component` avånd mai multe activit`]i. Fiecare activitate a fost numerotat` de la 1 la 4 ca 1.1, 1.2, 2.1, 2.2 [.a. Toate numerele activit`]ilor (coloana C) au fost formatate ca text.

Trucuri & Sfaturi Calcule matematice f`r` formule \n Excel

R.T.: „De ce trebuie numerele formatate ca text?“ Este \ntotdeauna recomandat ca atunci cånd numerele nu func]ioneaz` ca numere (adic` nu sunt folosite \n opera]ii matematice: adun`ri, sc`deri, \nmul]iri etc.) s` fie formatate ca texte. |n cazul nostru, 1.1, 1.2 nu sunt practic ni[te numere, ci mai degrab` ni[te etichete ale activit`]ilor. Puteam foarte bine s` le denumim a1, a2, b1, b2 [.a. [i atunci era clar c` sunt texte. A[a, formatarea coloanei C ca text evit` orice ambiguitate cu privire la tipul de date care sunt introduse. Dac` datele ar fi stocate ca num`r (a[a cum le consider` \n mod predefinit Excel), atunci folosirea lor \n anumite formule ar da erori.

Foaia de calcul Experti Avånd un cap de tabel simplu: Expert [i Pozitie, introduce]i datele respective. Foaia de calcul va ar`ta ca mai jos:

Introduce]i \n sec]iunea „Caracteristicile \mprumutului“: suma \mprumutat`, dobånda anual` [i durata \mprumutului \n ani.

Func]ia economico-financiar` PMT calculeaz` valoarea ratelor bazat` pe pl`]i ale acestora la intervale egale de timp [i la o rat` a dobånzii constant`.

Poate p`rea surprinz`tor, dar nu \ntotdeauna utilizarea unor formule este cel mai eficient mod de a manipula datele \n Excel. Dac` ave]i o coloan` cu pre]urile produselor, cre[terea lor cu 5% poate c` nu vi se pare o sarcin` chiar imposibil de realizat. Metoda clasic` este urm`toarea: ● g`si]i sau insera]i o coloan` goal` lång` coloana de pre]uri; ● introduce]i o formul` care s` \nmul]easc` pre]ul ini]ial cu 1.05; ● selecta]i coloana ob]inut`, Ctrl + C, apoi Paste Special ➯ Values pe coloana ini]ial`. A]i ob]inut ceea ce dorea]i, dar a]i f`cut destul de multe opera]iuni. Exist` o metod` mai simpl` pentru a face acela[i lucru: 1. introduce]i \ntr-o celul` oarecare valoarea 1.05 (cu care ar trebui s` se \nmul]easc` pre]urile ini]iale); 2. selecta]i celula [i apoi Ctrl + C sau Edit ➯ Copy; 3. selecta]i domeniul de celule cu pre]urile ini]iale. Alege]i Edit ➯ Paste Special ➯ Mutiply [i ap`sa]i OK. A]i ob]inut acela[i rezultat, dar mult mai repede.

Sintaxa func]iei este: =PMT(dobanda_anuala,dura ta_de_imprumut,valoarea_ prezenta_a _imprumutului)

Instruc]iuni pentru modificarea duratei \mprumutului

Formularul este flexibil, existånd chiar [i posibilitatea de a modifica durata de rambursare a \mprumutului. Not`: desc`rca]i formularul de pe Internet (www.rs.ro)

EXCEL_profesional

noiembrie 2006 ● pag. 3

|ntrebare & R`spuns |ntrebare: Cum elimin dublurile de date dintr-o list`?

R`spuns: Pute]i rezolva asta \n cåteva secunde utilizånd comanda „Advanced Filter“ (Filtru avansat) din Microsoft Excel. S` presupunem c` ave]i urm`toarea list`:

Excel [i managementul eficient al proiectelor Observa]i c` acolo unde nu s-a cunoscut numele exper]ilor sau erau mai mul]i exper]i, pentru a fi relevant, \n coloana A s-a \nscris denumirea generic` a exper]ilor (de exemplu, intervievatori, operatori interviu, coordonatori control calitate etc.)!

Foaia de calcul Planificare Este poate cea mai important` foaie de calcul din instrumentul pe care dori]i s` \l crea]i. Capul de tabel este unul la fel de simplu ca [i \n cazul celorlalte: Activitate, Expert, Luna, Zile om input. |n coloana Activitate vom introduce num`rul activit`]ii (1, 1.1, 1.2 [.a.), \n coloana Expert vom introduce numele expertului, \n coloana Luna vom introduce luna \n care se desf`[oar` activitatea respectiv`, iar \n coloana Zile om input vom trece num`rul de zile om pe care ar trebui s`-l lucreze expertul respectiv \n luna respectiv` [i pentru activitatea respectiv`. Figura urm`toare prezint` cåteva date introduse deja:

●

selecta]i o celul` din list`, apoi, \n meniul Data, selecta]i Filter (Filtru) [i Advanced Filter (Filtru avansat); ●

\n fereastra care se deschide selecta]i Copy to another location (Copiaz` \ntr-o alt` loca]ie) [i \n caseta List range (Limitele listei) introduce]i: $A$1:$A$12. Bifa]i op]iunea Unique records only (Doar \nregistr`rile unice);

● alege]i loca]ia unde vor fi afi[ate datele dumneavoastr` unice. |n cazul nostru, alegem coloana B, cu \ncepere chiar din celula B1. Pentru a realiza acest lucru, introduce]i valoarea $B$1 \n caseta Copy to (Copiaz` la). Rezultatul va fi afi[at imediat. |n coloana B ave]i datele dumneavoastr`, f`r` dubluri.

Cum se „traduc“ aceste informa]ii?

Linia 2 – Diana Andone lucreaz` pentru activitatea 1 \n luna 1 un num`r de 8 zile om. Linia 5 – Tot pentru activitatea 1 [i tot \n luna 1 lucreaz` [i Irina Pomeanu, dar cu o contribu]ie de 10 zile om. Linia 6 – Pentru activitatea 1.1, Florin Pascu lucreaz` \n luna 2 a proiectului timp de 15 zile om. Dup` introducerea celorlalte date pentru celelalte activit`]i, toate resursele umane din proiect (exper]i) sunt alocate pe o activitate [i pe o anumit` lun` [i le-a fost estimat` o anumit` contribu]ie \n num`r zile om pe fiecare activitate.

R.T.: „Constat c` \mi ia destul de mult timp pentru introducerea tuturor datelor [i exist` [i riscul de a gre[i introducerea unui nume sau a unei activit`]i. B`nuiesc c` dup` aceea datele sunt folosite la diferite calcule [i \mi poate afecta toate rapoartele pe care doresc s` le ob]in.“ A[a este, calitatea datelor din acest tabel este esen]ial` [i riscul introducerii gre[it de date trebuie minimizat. Facilitatea Excel cea mai u[oar` de folosit pentru acest lucru este validarea datelor.

pag. 4 ● noiembrie 2006

EXCEL_profesional

Excel [i managementul eficient al proiectelor

Trucuri & Sfaturi

Restric]ionarea introducerii de date la valorile dintr-o list` Ar trebui s` restric]ion`m datele introduse pe coloana A la etichetele pe care le-am dat activit`]ilor \n foaia Componente [i de asemenea ar trebui s` restric]ion`m datele din coloana B la numele exper]ilor introdu[i \n foaia de calcul Experti. Trebuie parcur[i urm`torii pa[i: 1. Denumi]i listele cu date de etichete activit`]i [i cu nume de exper]i. Recomandat este ca listele s` fie denumite dinamic pentru a putea actualiza u[or lista \n cazul \n care se mai adaug` o activitate sau un expert sau pentru cazul \n care folosi]i acela[i instrument Excel pentru planificarea altui proiect. Selecta]i Insert ➯ Name ➯ Define Name [i se va deschide o caset` de dialog:

Formatarea subtotalurilor |n mod predefinit, Excel formateaz` subtotalurile cu bold. Dac` vre]i s` modifica]i formatarea subtotalurilor proceda]i \n felul urm`tor: 1. ascunde]i liniile care cuprind valorile ce au fost adunate pentru subtotal, ap`sånd butonul 2 situat sus, \n stånga coloanelor; Ap`sa]i butonul 2

|n Names in workbook introduce]i numele listei nr_list, iar \n caseta Refers to introduceti o formul` folosind func]ia OFFSET care permite denumirea dinamic` a domeniului de celule. Func]ia returneaz` un domeniu de celule. Sintaxa func]iei este: =OFFSET(referinta, randuri, coloane, inaltime, latime), unde: referinta = referin]a celulei care va fi baz` pentru func]ia OFFSET; randuri = num`rul de rånduri, \n sus sau \n jos, fa]` de referin]a la care dori]i s` se refere celula din stånga sus a domeniului returnat. Num`rul 5 la rånduri \nseamn` c` celula din stånga sus pe care o va returna func]ia OFFSET va fi cu 5 rånduri mai jos de celula fixat` ca referin]`; coloane = num`rul de coloane, la stånga sau dreapta, fa]` de referin]` la care dori]i s` se refere celula din stånga sus a domeniului returnat. Num`rul 2 la coloane \nseamn` c` celula din stånga sus pe care o va returna func]ia OFFSET va fi cu 2 coloane mai la dreapta de celula fixat` ca referin]`; inaltime – este \n`l]imea, \n num`r de celule, pe care dori]i s` o aib` domeniul de celule pe care \l va returna func]ia OFFSET; latime – este l`]imea, \n num`r de celule, pe care dori]i s` o aib` domeniul de celule pe care \l va returna func]ia OFFSET. |n cazul nostru, formula folosit` \nseamn`: =OFFSET(Componente!$C$2;0;0;COUNTA(Componente!$C:$C) -1;1), unde

3. ap`sa]i CTRL + G pentru a afi[a fereastra de dialog Go To [i alege]i Special; 4. \n fereastra nou` selecta]i Visible cells only (Doar celulele vizibile);

5. selecta]i doar subtotalurile [i aplica]i formatarea dorit`. Formatarea va fi valabil` doar pentru subtotaluri, nu [i pentru valorile care au stat la baza subtotaliz`rii.

R&S

RENTROP & STRATON

2. selecta]i domeniul de celule care con]ine subtotalurile [i pe care dori]i s`-l formata]i;

EXCEL_profesional

noiembrie 2006 ● pag. 5

Trucuri & Sfaturi Actualizarea automat` a formulei care \nsumeaz` Pentru a \nsuma, de exemplu, celulele A1:A4, folosi]i func]ia SUM(A1:A4). Problema este c`, dac` introduce]i date pe råndul 5, formula nu se actualizeaz` automat [i trebuie s` o edita]i din nou. Exist` o solu]ie ca actualizarea s` se fac` automat, indiferent cåte rånduri mai introduce]i \ntre råndul 4 [i råndul care con]ine formula! Se poate folosi o combina]ie \ntre func]ia SUM [i func]ia OFFSET. |n celula A5 introduce]i formula:

Excel [i managementul eficient al proiectelor Componente!$C$2 = celula de referin]`, prima celul` din domeniul de celule care cuprinde etichetele activit`]ilor; 0 = rånduri, adic` domeniul pe care \l dorim nu \ncepe mai jos sau mai sus fa]` de celula de referin]`; 0 = coloane, adic` domeniul pe care \l dorim nu \ncepe mai la dreapta sau mai la stånga fa]` de celula de referin]`; (pån` aici \nseamn` c` domeniul pe care \l dorim \ncepe exact de la celula de referin]`, adic` C2) COUNTA(Componente!$C:$C)-1 = \n`l]imea. Func]ia COUNTA num`r` cåte celule care nu sunt goale exist` \n coloana C. |ntrucåt lista noastr` \ncepe de la al doilea rånd, primul fiind ocupat cu capul de tabel, trebuie s` sc`dem 1, pentru a avea exact \n`l]imea listei. Func]ia COUNTA este cea care fixeaz` limita de jos a listei. Dac` mai ad`ug`m un element la list`, rezultatul func]iei COUNTA se va modifica [i implicit va modifica \n`l]imea listei din func]ia OFFSET; 1 = l`]imea, adic` domeniul de celule care con]ine lista are o l`]ime de 1 coloan`. |n felul acesta, func]ia OFFSET returneaz` un domeniu de celule \ncepånd de la celula C2 [i pån` la cap`tul listei (aflat cu COUNTA) [i avånd o l`]ime de 1 coloan`. Adic` exact domeniul de celule care con]ine lista noastr`. Este bine s` v` \nsu[i]i aceast` tehnic` pentru c` este esen]ial` \n momentul \n care lucra]i cu tabele/liste/baze de date a c`ror dimensiune se modific` de-a lungul perioadei \n care sunt introduse date.

=SUM(A1:OFFSET(A5,-1,0) [i rezultatul va \ngloba [i råndurile nou-introduse.

|n mod similar proceda]i [i pentru definirea listei cu numele exper]ilor, pe care denumi]i-o nume_list. 2. Selecta]i celula A2 din foaia de calcul Planificare [i alege]i Data ➯ ➯Validation.

Aplica]ie

Program de trimis e-mail-uri

A]i fost probabil deseori pus \n situa]ia de a trimite prin e-mail acela[i mesaj unei liste lungi de adrese. Cunoa[te]i, cu siguran]`, solu]iile clasice BCC (undisclosed receipents) sau lista lung` de adrese la To sau CC. Problema este c` nici una dintre aceste solu]ii nu este elegant`. Ideal este ca fiecare destinatar s` primeasc` un mesaj personalizat, adresat doar lui, [i nu un e-mail la care se vede din avion c` a mai fost trimis \nc` la 1.000 alte persoane. Este evident c` a sta [i a concepe cåte un e-mail pentru fiecare persoan` din list` nu este solu]ia optim`. Dar \n lips` de altceva, poate func]iona. Totu[i exist` [i ceva mai simplu, care doar printr-o ap`sare de buton rezolv` problema. Aplica]ia Excel creat` special pentru acest scop v` permite s` transmite]i prin e-mail un mesaj personalizat c`tre un num`r aproape nelimitat de adrese [i incluzånd numele destinatarului \n corpul mesajului. Economie imens` de timp! Acum pute]i s` trimite]i, de exemplu, un mesaj cu prilejul s`rb`torilor de iarn` c`tre to]i furnizorii dumneavoastr`, \n doar 3 minute!

pag. 6 ● noiembrie 2006

EXCEL_profesional

Excel [i managementul eficient al proiectelor

Trucuri & Sfaturi

Se va deschide o c`su]` de dialog ca mai jos:

Introducerea datelor \n foi de calcul multiple

– Din lista derulant` Allow (Permite) alege]i List. – |n c`su]a Source introduce]i =nr_list (adic` numele listei domeniului care con]ine etichetele activit`]ilor). Nu uita]i semnul = pentru c` altfel Excel nu va [ti la ce v` referi]i! – Selecta]i [i op]iunile Ignore blank [i In-cell dropdown dac` nu sunt \n mod predefinit selectate de c`tre Excel. – Ap`sa]i OK [i \n acest moment \n c`su]a A2 nu sunt permise decåt valori din lista de etichete ale activit`]ilor. Observa]i c` \n momentul \n care selecta]i celula A2 apare o mic` s`geat` la dreapta care ap`sat` afi[eaz` o list` cu toate etichetele de activit`]i din list`. Pute]i introduce o dat` fie manual, fie prin selectarea din lista derulant`. Dac` introduce]i o valoare gre[it`, Excel va afi[a un mesaj predefinit:

De multe ori apare situa]ia de a introduce acelea[i date \n foi de calcul diferite, de exemplu capuri de tabel. Excel are o op]iune care v` poate ajuta ca datele s` fie introduse automat \n toate foile de calcul \n care dori]i. Ap`sa]i tasta CTRL [i executa]i clic pe fiecare din foile de calcul care trebuie s` con]in` acelea[i date. Foile de calcul respective vor fi marcate ca \n figura urm`toare:

|n foaia Sheet1, \ncepe]i s` introduce]i datele. Datele vor fi introduse [i \n celelalte foi selectate (\n exemplul nostru Sheet2, Sheet3) exact \n aceea[i pozi]ie. Termina]i prin selectarea unei alte foi decåt cele din selectarea cu CTRL ini]ial` (\n cazul nostru Sheet4).

Instruc]iuni

R&S

Baza de date cu adresele de e-mail

EXCEL_profesional

noiembrie 2006 ● pag. 7

Trucuri & Sfaturi

Excel [i managementul eficient al proiectelor

Eliminarea spa]iilor \n plus dintre cuvinte

Mesajul \nseamn` c` valoarea introdus` \n celul` nu este valid` [i c` nu pute]i introduce orice valoare pentru c` exist` restric]ii. Dac` ap`sa]i pe Cancel, \n celula A2 va fi p`strat` valoarea anterioar`, dac` ap`sa]i pe Retry ave]i posibilitatea s` mai introduce]i \nc` o dat` o valoare \n celula A2.

Primi]i fi[iere Excel pe care trebuie s` le procesa]i. Constata]i uneori c` \n anumite celule informa]ia are \ntre cuvinte mai mult de un singur spa]iu. Cum pot fi eliminate spa]iile \n plus, f`r` s` fie nevoie s` parcurge]i fiecare celul` \n parte?

3. Copia]i celula A2 [i lipi]i-o cu Paste Special ➯ Validation pe celulele A3 pån` la A1000. 4. Repeta]i pa[ii 2 [i 3 pentru restric]ionarea datelor \n coloana B cu numele exper]ilor.

Se poate folosi func]ia TRIM care elimin` toate spa]iile dintrun text, cu excep]ia spa]iilor singulare \ntre cuvinte. Sintaxa este simpl`: TRIM(text). Proceda]i astfel: 1. crea]i o coloan` nou` lång` coloana cu texte cu mai multe spa]ii; 2. \n prima celul` din coloana nou`, introduce]i formula TRIM(text), unde text este celula din coloana cu texte cu mai multe spa]ii;

3. copia]i formula \n toat` coloana nou`. Ve]i ob]ine toate textele doar cu un singur spa]iu \ntre cuvinte (cele care au un singur spa]iu sunt l`sate a[a, pentru celelalte se elimin` spa]iile \n plus);

Introducerea datelor \n Excel \ntr-o structur` asem`n`toare unei baze de date O modalitate mai u[oar` de a introduce datele este de a \ncepe cu fiecare activitate. Se selecteaz` activitatea, se stabile[te \n ce luni se desf`[oar`, apoi se aleg exper]ii care lucreaz` \n fiecare lun` [i la final se pune num`rul de zile om pe fiecare expert [i lun`. Practicånd pu]in, ve]i reu[i s` månui]i Copy [i Paste foarte bine pentru a introduce rapid datele pentru o activitate. Apoi trece]i la activitatea urm`toare. |n cazul \n care dori]i s` face]i verific`ri, puteti s` filtra]i lista dup` diferite criterii. Pute]i observa foarte u[or dac` a]i uitat s` introduce]i ceva sau exist` dubl`ri. Cåteva filtr`ri \ncruci[ate sunt mai mult decåt recomandate pentru a fi sigur c` datele introduse \n tabel sunt corecte [i nu exist` erori. De exemplu, f`cånd o filtrare dup` expertul Florin Pascu (vezi figura), se poate observa foarte u[or c` pentru activitatea 3.1 \n luna 8 s-au f`cut dou` \nregistr`ri care probabil se refereau la acela[i lucru (liniile 45 [i 65), iar una dintre ele ar trebui [tears`.

4. copia]i coloana nou`; 5. selecta]i prima celul` din coloana veche; 6. selecta]i Paste Special, Values [i apoi OK. Textele corecte (cu un singur spa]iu) din coloana nou` au fost lipite peste coloana veche; 7. [terge]i coloana nou`. pag. 8 ● noiembrie 2006

EXCEL_profesional

Excel [i managementul eficient al proiectelor

|ntrebare & R`spuns

Crearea din punct de vedere vizual a unui Grafic Gantt Graficul Gantt este un grafic foarte simplu, care arat` \ntinderea unei activit`]i \n timp. Stabili]i, din punctul de vedere al layoutului, cum dori]i s` arate Graficul Gantt. O posibil` abordare este prezentat` \n figura urm`toare.

|ntrebare: Cum creez un buton pentru lansarea automat` a unui macro?

R`spuns: Pentru crearea unui buton \n vederea lans`rii automate a unui macro, parcurge]i urm`torii pa[i: selecta]i View ➯ Toolbars ➯ Forms; ●

●

face]i clic stânga pe icoana care arat` un buton în „miniatur`“;

Observa]i urm`toarele aspecte: – tabelul a fost formatat astfel \ncåt componentele [i activit`]ile aferente acestora s` fie cåt mai u[or de citit; – \n coloana A au fost trecute etichetele activit`]ilor separat de denumirile activit`]ilor care au fost introduse \n coloana B. Coloana A a fost formatat` ca text, exact cum au fost formatate etichetele activit`]ilor \n foile de calcul anterioare. Acest lucru este justificat de faptul c` dorim s` folosim etichetele activit`]ilor \n formule [i s` facem referire la etichetele situate \n alte foi de calcul; – pe råndul 2, de la C2 la M2 a fost trecut` „Luna“, iar separat, pe råndul 3, exact sub acestea, au fost trecute numerele lunilor, de la 1 la 11. Separarea pe dou` rånduri este justificat` din nou de dorin]a noastr` de a utiliza lunile \n formule; – formatarea a fost f`cut` \n a[a fel \ncåt separarea etichetelor de numele activit`]ilor [i a lunii de num`rul acesteia s` nu fie aproape deloc observat`. Este \ntotdeauna recomandat s` face]i aceast` separare, pentru c` nu [ti]i cånd ave]i nevoie s` le folosi]i \n formule. Dac` cele dou` ar fi \n aceea[i celul`, referirea la etichete sau num`rul lunii ar fi imposibil`. {i de multe ori acest lucru este foarte necesar.

●

deplasa]i cursorul mouse-ului pe suprafa]a foii de calcul. Cursorul se va modifica din s`geat` obi[nuit` într-o cruce sub]ire;

●

]inând mouse-ul ap`sat (clic stânga) desena]i un dreptunghi. Elibera]i [i un buton va fi creat; în mod automat apare o caset` de dialog numit` Assign Macro;

Dac` ar fi s` facem Graficul Gantt manual, atunci ar trebui s` color`m celulele din dreptul activit`]ilor [i al lunilor respective cu o culoare distinct` pentru a fi mai u[or de observat. |n exemplul nostru, luånd \n considerare lunile \n care am „spus“ c` se desf`[oar` activit`]ile (\n foaia de calcul Planificare), Graficul Gantt ar ar`ta ca mai jos: ●

selecta]i macro-ul [i ap`sa]i pe OK;

●

RENTROP & STRATON

dac` dori]i s` schimba]i denumirea butonului – în mod prestabilit aceasta este Button1 – face]i clic dreapta pe buton, selecta]i Edit Text [i apoi modifica]i textul care apare pe buton.

R&S

→→ continuare \n pag. 10 →→

EXCEL_profesional

noiembrie 2006 ● pag. 9

|ntrebare & R`spuns →→ continuare din pag. 9 →→ În plus, iat` care sunt [i la ce v` ajut` controalele disponibile în bara de instrumente Forms: Label (Etichet`) Introduce \n foaia de calcul o etichet` care va fi utilizat` ulterior la denumirea altor controale. Option Button (Buton op]iune) Permite alegerea unei op]iuni din mai multe disponibile. De regul` se folose[te \n combina]ie cu alte butoane de op]iune. Group Box (Caset` de grupare) Grupeaz` mai multe butoane de op]iune.

Excel [i managementul eficient al proiectelor R.T.: „|n cazul \n care colorez manual fiecare celul`, Graficul Gantt nu se mai actualizeaz` automat la diferite modific`ri. A[ vrea s` pot modifica \n foaia de calcul Planificare [i apoi, automat, \n foaia Gantt activitati-luna, Excel s` coloreze celulele care trebuie, \n func]ie de modific`ri. Cum trebuie procedat?“ Din capul locului trebuie spus c` nu este chiar simplu. Dac` am fi avut un alt design al foii de calcul ar fi fost aproape imposibil. A[a, vom folosi o combina]ie \ntre o formul` matrice [i formatarea condi]ional` [i problema este rezolvat`.

Folosirea formulei matrice SUM(IF) Ca o preg`tire pentru folosirea formulei vom numi mai \ntåi domeniile de celule care con]in etichetele activit`]ilor [i num`rul lunilor din foaia de calcul Planificare. Pentru a putea considera [i eventualele modific`ri \n foaia de calcul Planificare (fie [tergerea unor linii, fie ad`ugarea altora noi), denumirea se va face dinamic, dup` pa[ii ar`ta]i anterior. ■

Domeniul cu etichetele activit`]ilor va fi denumit plan_activitate:

■

Domeniul con]inånd numerele lunilor va fi denumit plan_luna:

■

Domeniul cu num`rul de zile va fi denumit plan_zile:

Button (Buton) Permite lansarea unui macro la clic pe buton. Check box (Caset` de selectare) Selectarea sau deselectarea acestuia permite func]ionarea diferit` a unor elemente din foaia de calcul. List Box (Caset` list`) Permite selectarea dintr-o list` a unui element. Combo Box (Caset` combo) Este similar` cu caseta list`, cu diferen]a c` lista este afi[at` doar la clic pe caseta combo. Scroll Bar (Bar` de defilare) Este o bar` care permite modific`ri incrementale, \ntre o valoare minim` [i una maxim`. Spinner (Buton de incrementare/ decrementare) Este similar cu bara de defilare, avånd \ns` un aspect diferit (ca un contor).

pag. 10 ● noiembrie 2006

Formula matrice SUM(IF) este mai pu]in cunoscut` [i folosit` de utilizatorii Excel. Are o sintax` mai pu]in obi[nuit`, dar merit` s` fie \n]eleas` [i aplicat`. Ea v` poate scoate din necaz atunci cånd sim]i]i c` func]ia

EXCEL_profesional

Excel [i managementul eficient al proiectelor SUMIF este limitat`, [i asta nu se \ntåmpl` chiar rar. De exemplu, atunci cånd ave]i mai mult de un criteriu dup` care trebuie calculat` o sum`. {ti]i c` func]ia SUMIF nu poate lua \n considerare decåt un singur criteriu. Dac` ave]i mai multe, folosi]i func]ia matrice SUM(IF)! Sintaxa este: =SUM(IF((domeniu1=condi]ie1)*(domeniu2=condi]ie2)*s.a.); domeniu_suma)), unde domeniu1, domeniu2, domeniuN sunt domeniile de celule unde verifica]i \ndeplinirea unor condi]ii; condi]ie1, condi]ie2, condi]ieN sunt condi]iile care trebuie \ndeplinite de c`tre domeniile de mai sus; domeniu_suma este domeniul de celule ale c`rui valori vor fi \nsumate dac` sunt \ndeplinite simultan cele dou` sau mai multe condi]ii. Pentru a nu p`rea prea teoretic, vom reveni la exemplul nostru pentru o mai u[oar` \n]elegere. Dorim s` calcul`m, \n luna 1 [i pentru activitatea 1 cåte zile om au planificate cumulat to]i exper]ii. O solu]ie u[oar` ar fi filtrarea pe luna 1 [i activitatea 1 \n foaia de calcul Planificare [i calcularea sumei aferente.

Eroare & Explica]ie Eroare de tip ##### Apare pentru celule care con]in numere sau format tip dat`, respectiv timp (or`), \n momentul \n care dimensiunea celulei nu este suficient de mare pentru a acoperi dimensiunea num`rului introdus \n celul` sau valoarea nu este \n concordan]` cu formatul pentru dat` [i timp. De exemplu, exist` situa]ia \n care introduce]i \ntr-o celul` cu format dat` sau timp o valoare negativ`. Pute]i rezolva \n mai multe moduri aceast` eroare: ● M`ri]i dimensiunea celulei. Tra-

ge]i cu mouse-ul c`tre dreapta de linia de delimitare dintre celule, atåt cåt este necesar pentru ca num`rul s` poat` fi afi[at. ●

Modifica]i formatul num`rului sau datei. Da]i clic dreapta pe celul` [i selecta]i din meniul derulant Format Cells. Selecta]i Number, Date sau Time. |n cazul unui num`r, pute]i mic[ora num`rul de zecimale care se afi[eaz` dup` virgul`. Eventual pute]i debifa [i op]iunea de utilizare a separatorului pentru ordin. Urm`ri]i modific`rile \n caseta Sample din aceea[i fereastr`.

●

Verifica]i cu aten]ie formula pentru cazul formulelor de tip dat` [i timp, \nainte de a introduce informa]ia. Dac` formatul datei este luna/ziua/anul [i inversa]i valorile, va fi afi[at` aceast` eroare.

Sum=43

Observa]i \n dreapta jos suma Sum=43. Acela[i lucru poate fi realizat [i cu func]ia SUM(IF), \n Graficul Gantt.

R&S

RENTROP & STRATON

Introduce]i \n celula C4 formula: =SUM(IF((plan_activitate=$A4)*(plan_luna=C$3);plan_zile)) [i ve]i ob]ine aceea[i valoare, 43.

EXCEL_profesional

noiembrie 2006 ● pag. 11

Formule utile G`sirea numelui folderului \n care se afl` un fi[ier Excel Dac` dori]i s` afla]i \n ce folder se afl` un anumit fi[ier, f`r` a apela la Visual Basic sau a verifica acest lucru din File, Properties, pute]i folosi urm`toarea formul`: =LEFT(CELL(’’Filename’’, $A$1),FIND(’’[’’,CELL(’’Filename’’,$A$1))-1) |n cazul \n care abia a]i creat un nou registru de lucru [i nu l-a]i salvat, formula va returna eroarea #VALUE!. Dup` salvarea fi[ierului, rezultatul formulei va ar`ta calea \n care se afl` fi[ierul.

Utilizarea operatorilor INDEX [i MATCH pentru a g`si o informa]ie

Excel [i managementul eficient al proiectelor Aten]ie! Fiind o formul` matrice, ea trebuie introdus` prin ap`sarea simultan` a tastelor CTRL + SHIFT + ENTER. O introducere corect` va fi eviden]iat` prin apari]ia unor acolade la \nceputul [i finalul func]iei. Ce face func]ia? Se uit` \n domeniul de celule plan_activitate (cu etichetele activit`]ilor), se uit` \n domeniul de celule plan_luna (cu numerele lunilor) [i de fiecare dat` cånd celula din plan_activitate este egal` cu A4 (adic` egal` cu 1) [i simultan celula din plan_luna este egal` cu C3 (adic` egal` cu 1) adun` valoarea din celula din domeniul plan_zile.

R.T.: „De ce \n celul` apare $A4 [i C$3, [i nu, simplu, A4 [i C3?“ Inten]ia este ca formula s` fie copiat` prin Copy [i Paste \n tot Graficul Gantt. Introducerea $ la referin]a celulei „\nghea]`“ fie linia, fie coloana, \n func]ie de locul unde se plaseaz` semnul $. $A4 absolutizeaz` coloana unde se afl` A4, adic` coloana A. Dac` copiem formula \n celula C7, referin]a va fi $A7; dac` o copiem \n celula H7, referin]a va fi $A7. Adic` r`måne tot pe coloana A, unde avem etichetele activit`]ilor. |n mod similar, C$3 absolutizeaz` råndul 3, acolo unde avem numerele lunilor. Astfel, copierea formulei va face corect referin]a fie la coloana cu etichetele activit`]ilor, fie la råndul cu numerele lunilor. Copia]i celula C4 [i lipi]i-o \n domeniul de celule C4:M4, C7:M11, C14:M17, C20:M22, C25:M25. Ve]i ob]ine rezultatele din figur`:

S` presupunem c` ave]i o list` foarte lung` de nume c`rora le sunt asociate vårsta [i departamentul \n care persoana respectiv` lucreaz`. Pe coloana A este numele, pe B departamentul [i pe C vårsta. Pute]i afla foarte repede vårsta unei anumite persoane f`r` a c`uta printre date sau a apela la filtre complexe. Tot ceea ce trebuie s` face]i este s` introduce]i \ntr-o celul` urm`toarea formul` (pentru a afla vårsta angajatului Valentin, de exemplu): =INDEX($A$1:$C$5, MATCH(„Valentin“,$A$1: $A$5,),3) Rezultatul va ap`rea imediat \n celula \n care a]i introdus formula. Not`: 5 din $A$5 este ultimul rånd al listei \n care sunt numele, 3 din func]ia INDEX este a treia coloan` din domeniul A1:C5 \n care este \nregistrat` vårsta.

pag. 12 ● noiembrie 2006

Observa]i c`: – \n lunile \n care a fost planificat` o activitate, rezultatul este mai mare decåt 0; – \n lunile \n care nu a fost planificat` nici o activitate, rezultatul este 0.

Formatarea condi]ional` Avånd \n minte aceste 2 criterii, putem crea o formatare condi]ional` \n care dac` \n celul` este o valoare mai mare decåt 0, celula este colorat` \ntr-o anumit` culoare. Se vor parcurge urm`torii pa[i:

EXCEL_profesional

Excel [i managementul eficient al proiectelor

Trucuri & Sfaturi

1. selecta]i domeniul de celule C4:M25; 2. alege]i Format ➯ Conditional Formatting pentru a afi[a urm`toarea caset` de dialog:

3. alege]i condi]ia „is greater than“ din lista derulant` [i introduce]i 0 \n c`su]a din dreapta (cu alte cuvinte, dac` valoarea din celul` este mai mare decåt 0);

Conversia \n num`r a unui [ir de text care reprezint` un num`r Cånd copia]i date din alte programe \n Excel, constata]i uneori c` valorile numerice sunt copiate ca text, de[i ele arat` ca numere. Pentru a putea lucra cu ele, trebuie s` le transforma]i din texte \n numere. Cum se procedeaz`? Se poate folosi func]ia Value care face conversia \n num`r a unui [ir de text ce reprezint` un num`r. Sintaxa este simpl`: Value(text).

4. ap`sa]i pe Format, iar \n tabul Patterns alege]i culoarea \n care dori]i s` se coloreze celula; 5. ap`sa]i OK pentru a \nchide prima c`su]` [i apoi din nou OK pentru a o \nchide pe cea de-a doua. Foaia de calcul va ar`ta ca mai jos:

Proceda]i astfel: 1. crea]i o coloan` nou` lång` coloana cu numere texte; 2. \n prima celul` din coloana nou`, introduce]i formula Value(text), unde text este celula din coloana cu numere texte; 3. copia]i formula \n toat` coloana nou`. Ve]i ob]ine toate valorile convertite \n numere; 4. copia]i coloana nou`; 5. selecta]i prima celul` din coloana veche;

R&S

RENTROP & STRATON

Cu siguran]` ve]i spune c` foaia de calcul nu arat` exact a[a cum a]i dorit, dar trebuie s` recunoa[te]i c` suntem totu[i aproape.

6. selecta]i Paste Special, Values [i apoi OK. Valorile numerice din coloana nou` vor fi lipite peste coloana veche;

Mai trebuie f`cute 2 lucruri: – eliminate zerourile, pentru c` \ncarc` Graficul Gantt; – ascunse valorile din celulele colorate (\n lunile \n care sunt planificate activit`]i), de[i s-ar putea ca \n anumite situa]ii aceste valori s` fie utile chiar pe grafic.

Ascunderea valorilor de zero inutile Exist` 2 posibilit`]i:

EXCEL_profesional

7. [terge]i coloana nou`.

noiembrie 2006 ● pag. 13

Trucuri & Sfaturi

Excel [i managementul eficient al proiectelor 1. selecta]i Tools ➯ Options ➯ View [i debifa]i op]iunea Zero values;

Ascunderea con]inutului unor celule la imprimare Este posibil s` dori]i s` tip`ri]i o foaie de calcul f`r` con]inutul unor celule (nu doar rånduri sau coloane – acestea pot fi ascunse \n \ntregime). Dac` lucra]i cu date care pentru anumite persoane sunt confiden]iale, pute]i printa foaia de calcul f`r` aceste date. Iat` un truc extrem de simplu, dar deosebit de util: 1. selecta]i celula (celulele); 2. merge]i \n meniul Format, Cells (Formatare, Celule); 3. \n fereastra Format Cells merge]i \n registrul Font; 4. din lista de culori selecta]i culoarea „Alb“;

Debifa]i op]iunea Zero values

Astfel Excel nu va mai afi[a \n registrul de calcul nici o valoare de zero; 2. selecta]i domeniul C4:M25 [i seta]i culoarea textului alb. |n felul acesta, valorile zero vor exista, dar fiind albe vor fi „invizibile“.

Ascunderea valorilor din celulele colorate Cea mai simpl` metod` este de a colora fontul cu aceea[i culoare ca [i celula \n care este valoarea. |n Conditional Formatting definit anterior (dup` selectarea C4:M25, Format ➯ Conditional Formatting), ap`sa]i Format [i alege]i din tabul Font aceea[i culoare ca [i cea folosit` \n tabul Patterns. Ap`sa]i OK de dou` ori, iar rezultatul va fi cel dorit. Graficul Gantt va ar`ta foarte simplu [i curat, de[i este plin de formule [i format`ri. Marele avantaj este c` celulele se vor colora diferit dac` schimba]i planificarea activit`]ilor \n foaia Planificare. |n plus, aceste opera]iuni nu trebuie s` le face]i decåt o singur` dat`, pentru c` formulele sunt suficient de flexibile pentru a putea fi u[or copiate pentru un alt proiect. Pur [i simplu trebuie s` p`stra]i acela[i format al Graficului Gantt [i s` ad`uga]i alte activit`]i.

Crearea unei foi de calcul care s` arate alocarea resurselor umane pe activitate Aceast` foaie de calcul va urm`ri alocarea resurselor umane (exper]ii) pe fiecare dintre activit`]ile proiectului. Formatul ar putea ar`ta ca \n figura urm`toare: 5. clic pe OK; 6. printa]i foaia de calcul. Textul scris cu alb nu se va vedea; 7. reveni]i la culoarea ini]ial`.

Speciali[tii no[tri v` r`spund la \ntreb`rile dvs. legate de utilizarea programului Excel. Adresa de coresponden]`: [email protected] Conceptul foii de calcul a fost p`strat acela[i ca la Graficul Gantt, avånd pe coloana A etichetele activit`]ilor [i pe o singur` linie numele exper]ilor. pag. 14 ● noiembrie 2006

EXCEL_profesional

Excel [i managementul eficient al proiectelor Aten]ie! Numele exper]ilor din aceast` foaie trebuie s` fie identic cu numele exper]ilor din foaia de calcul Experti. Pentru a fi siguri c` nu se strecoar` gre[eli, copia]i numele exper]ilor din foaia Experti [i lipi]i-o cu Paste Special ➯ Transpose \n foaia de calcul Alocare resurse-activ. De aici \nainte se procedeaz` similar cu foaia de calcul Grafic Gantt: 1. se introduce formula matrice SUM(IF): =SUM(IF((plan_activitate=$A3)*(plan_expert=C$2);plan_zile)); 2. se copiaz` \n tot domeniul aferent; 3. se ascund valorile zero; 4. se adaug` formulele de totaluri pentru componente, total proiect [i total activit`]i. Rezultatul este cel de mai jos:

Scurt`turi de taste Func]iile tastelor F

F1

Afi[eaz` fereastra de ajutor sau Asistentul Office

F2

Permite editarea celulei selectate (similar cu BACKSPACE)

F3

Permite introducerea unui nume de domeniu \ntr-o formul`

F4

Repet` ultima ac]iune

F5

Deschide fereastra de salt la o anumit` celul` (GO TO)

F6

Trece de la un panou la altul \n sensul acelor de ceasornic (cånd fereastra este \mp`r]it` \n panouri, dar nu este \nghe]at`)

F7

Verific` ortografia

F8

Permite selectarea celulelor de jur \mprejur cu ajutorul tastelor s`geat`

F9

Calculeaz` toate formulele din toate foile de calcul ale tuturor fi[ierelor deschise

{i aceast` foaie de calcul va fi automat actualizat` \n momentul \n care modific`m ceva \n planificare.

Crearea unei foi de calcul care s` arate alocarea resurselor umane pe lun`

R&S

RENTROP & STRATON

A treia foaie de calcul urm`re[te alocarea resurselor pe fiecare lun` \n parte. Cu alte cuvinte, poate r`spunde la \ntrebarea referitor la cånd [i cåt lucreaz` expertul de-a lungul proiectului. Pa[ii care trebuie parcur[i sunt similari cu cei de la foile anterioare: 1. crea]i layoutul raportului; 2. introduce]i formula matrice SUM(IF) \n celula C5: =SUM(IF((plan_expert=$A5)*(plan_luna=C$3);plan_zile)); 3. copia]i formula \n tot domeniul C5:M24;

EXCEL_profesional

F10

Activeaz` bara de meniuri (similar cu tasta ALT sau /)

F11

Creeaz` un grafic

F12

Deschide fereastra Save As (similar cu ALT + F2) noiembrie 2006 ● pag. 15

Trucuri & Sfaturi Identificarea rapid` a tipului de format Trebuie s` procesa]i deseori cu Excel date introduse de al]i utilizatori [i ave]i nevoie s` [ti]i care date sunt texte [i care numere, pentru c` func]ionarea unor formule depinde de tipul valorii. De obicei lua]i fiecare celul` \n parte, selecta]i Format Cells [i v` uita]i la tipul de format. Dar acest lucru dureaz` foate mult. Exist` o modalitate mult mai simpl`!

Excel [i managementul eficient al proiectelor 4. formata]i condi]ional domeniul de celule C5:M24 pentru a colora celulele care au valori mai mari decåt zero; 5. ascunde]i valorile zero; 6. ad`uga]i un total pe fiecare expert. Ve]i ob]ine un tabel ca \n figura urm`toare:

Func]ia TYPE returneaz` o astfel de informa]ie. Sintaxa este simpl`: TYPE(value). Pentru a avea o imagine dintr-o privire, introduce]i formula pe o coloan` situat` lång` coloana care con]ine valorile pe care vre]i s` le verifica]i. Rezultatul formulei este interpretat astfel: ● dac` este 1, atunci valoarea este num`r; ● dac` este 2, atunci valoarea este text.

Aflarea unui r`spuns, identificarea unei solu]ii Pentru aceasta trebuie doar s` intra]i \n dialog cu autorii revistei. V` macin` o \ntrebare, ave]i o problem` \n lucrul cu programul Excel? Autorii revistei v` stau la dispozi]ie! Transmite]i prin e-mail, la adresa [email protected], o \ntrebare sau descrierea unei probleme cu care v` confrunta]i, eventual \mpreun` cu fi[ierul Excel ce are leg`tur` cu aceasta. Ve]i primi pe aceea[i cale, \ntr-un timp cåt mai scurt, r`spunsul/solu]ia speciali[tilor no[tri \n Excel. Nu ezita]i!

pag. 16 ● noiembrie 2006

Lunile \n care lucreaz` [i contribu]ia fiec`rui expert sunt foarte u[or de observat. Aici pute]i sa v` da]i seama imediat: – ce exper]i sunt sub-aloca]i; – ce exper]i sunt supra-aloca]i. {i v` permite s` face]i foarte u[or modific`ri \n planificare. Dac` ave]i foarte mul]i exper]i [i vre]i s` nu face]i aceste verific`ri „la ochi“ pute]i ad`uga o alt` condi]ie la formatarea condi]ional`, de exemplu s` colora]i cu ro[u toate celulele care sunt mai mari decåt 22 (adic` o lun` de munc`).

Avantajele [i dezavantajele aplica]iei Avantaje – este foarte flexibil`. Odat` creat`, poate fi replicat` pe orice alt proiect \n cåteva minute (schimba]i doar numele [i etichele activit`]ilor, numele exper]ilor [i planificarea [i mai opera]i cåteva modific`ri \n formatele celor 3 rapoarte, iar apoi copia]i formulele); – permite actualizarea automat` a celor 3 rapoarte, f`r` nici o alt` interven]ie din partea dvs. Dezavantaje – formulele matrice au o vitez` mai mic` [i pot \ngreuna foaia de calcul. Dac` nu ave]i un proiect foarte mare, atunci viteza mai mic` nu v` va deranja. Dac` totu[i exist` multe activit`]i [i actualizarea foii de calcul se face \n timp mai mare, blocåndu-v` lucrul dup` fiecare modificare pe care o opera]i, solu]ia este: Tools ➯ Options ➯ Calculation [i selecta]i Manual. Foaia de calcul va fi actualizat` doar cånd salva]i sau cånd ap`sa]i tasta F9 (calculeaz`).

EXCEL_profesional

Desc`rca]i de pe Internet (www.rs.ro)

fi[ierele, formularele, aplica]iile construite de autori pentru prezentarea informa]iilor

www.rs.ro ➯ Lucrari ➯ Newsletter tiparit ➯ Revista Excel_profesional

Clic aici

E CEL

sau

_profesional

Unica revist` cu sfaturi practice pentru speciali[tii \n calcul tabelar

Redactor-[ef: Florian Filat Colectiv de redac]ie: Monica Gearb`, Sanda Vl`descu Manager produs: Tudor Mihalache Manager Centru de Profit: Claudia Breban Art Director: Cristina Straton DTP: Mirela Vasilescu, Carmen Ilinca Corectur`: Elvira Panaitescu

Clic dreapta ➯ Save Target AS...

Publica]ie editat` de: RENTROP & STRATON Grup de Editur` [i Consultan]` \n Afaceri Membru fondator al B.R.A.T. Pre[edinte-Director General: George Straton Director Executiv: Cipriana T`nase Director Editorial: Florin Cåmpeanu Director Economic: Mariana Ne]oiu Director Comercial: Valentin T`nase Difuzare: Sofica Costea tel.: 021/205.57.47, 0744.647.602 ISSN: 1842-4252

Redac]ia: Bdul Na]iunile Unite nr. 4, etaj 1, sector 5, Bucure[ti; Telefon: 021/317.25.87 E-mail: [email protected]; Internet: www.rs.ro Coresponden]`: Ghi[eul extern 3 – O.P. 39, sector 3, Bucure[ti

© 2006 – RENTROP & STRATON Toate drepturile rezervate. Nici o parte din aceast` lucrare nu poate fi reprodus`, arhivat` sau transmis` sub nicio form` [i prin nici un fel de mijloace, mecanice sau electronice, fotocopiere, \nregistrare audio sau video, f`r` permisiunea \n scris din partea editorului. Autorii sau editorii nu sunt responsabili pentru nici o pierdere ocazionat` vreunei persoane fizice sau juridice care ac]ioneaz` sau se ab]ine de la ac]iuni ca urmare a citirii materialelor publicate \n aceast` lucrare.

Nu rata]i subiectele din numerele urm`toare: A

B

1

Subiect

v` \ndrum` spre

2

Analiza eficien]ei unei investi]ii (Excel)

– \n]elegerea conceptelor financiare privind eficien]a investi]iei [i transpunerea lor \n tabele Excel – calcularea ratei interne de rentabilitate [i a valorii actualizate nete pentru fundamentarea deciziei de investi]ie – legarea foilor de calcul \ntre ele pentru automatizarea calculelor

3

Colectarea [i procesarea datelor financiare (Excel + VBA)

– denumirea domeniilor de celule – restric]ionarea accesului la anumite date din foaia de calcul – ascunderea anumitor foi de calcul – protejarea anumitor celule din foaia de calcul

4

Realizarea unui chestionar \n Excel (Excel)

– realizarea unui layout eficient pentru chestionar – construirea unei minibaze de date pentru colectarea r`spunsurilor la \ntreb`ri – afi[area unui mesaj pentru repondentul care a completat chestionarul – crearea unei baze de date care s` cumuleze r`spunsurile la toate chestionarele – crearea unui raport \n func]ie de baza de date cumulativ`

5

Analiza situa]iilor financiare anuale (Excel)

– colectarea datelor din balan]a de verificare \n Excel – importul formatului de bilan] \n Excel – importul formatului de cont de profit [i pierdere \n Excel – crearea notelor explicative – analiza cheltuielilor – urm`rirea modific`rilor legate de situa]iile financiare

Not`: Lista subiectelor nu se opre[te aici. |n plus, chiar dumneavoastr` pute]i propune teme pe care dori]i s` le trat`m \n revist`.

= Formula ideal` pentru abona]i! intra]i \n dialog cu autorii revistei, prin e-mail, la adresa:

desc`rca]i de pe Internet ( www.rs.ro )

[email protected] ✔ solicita]i tratarea anumitor teme \n numerele viitoare ale revistei ✔ transmite]i o \ntrebare sau descrierea unei probleme care v` preocup`; primi]i r`spunsul/solu]ia de la autori pe aceea[i cale, \ntr-un timp cåt mai scurt

+

toate formularele, aplica]iile [i fi[ierele construite de autori \n vederea prezent`rii informa]iilor. Folosindu-le ve]i face o economie imens` de timp.

Contact: Telefon: (021) 209 45 45

Fax: (021) 205 57 30

E-mail: [email protected]