18 march 2015, 16:31 Kaleidoscope 13217

9 trucuri puţin cunoscute pentru business în Excel

Excel-ul nu este software-ul cel mai prietenos din lume. Un utilizator obișnuit folosește doar 5% din posibilităţile programului şi îşi imaginează cu greu ce comori se ascund în adîncurile ei. Utilizînd sfaturile unui guru în Excel puteți învăța cum să comparaţi listele de prețuri, să ascundeţi informații secrete de ochi străini indiscreţi și să alcătuiţi rapoarte analitice doar cu cîteva clickuri.

1. Pagina ascunsă

Să presupunem că doriți să ascundeți o parte din paginile Excel de alți utilizatori care lucrează asupra unui fişier. Dacă o faceți în modul clasic – acţionaţi click dreapta al mouse-ului pe domeniul selectat, apoi Hide (imaginea 1), atunci denumirea foii ascunse oricum va fi vizibilă pentru o altă persoană. Pentru a o face complet invizibilă, este necesar să acționaţi după cum urmează:

—Tastaţi ALT+F11.

— În stînga Dvs. va apare o fereastră alungită.

— În partea de sus a ferestrei selectaţi numărul filei, pe care doriţi să o faceţi invizibilă.

În partea de jos la finele listei o să găsiţi opţiunea Visible şi o veţi face xlSheetVeryHidden. Acum, despre această pagină nu va şti nimeni în afară de Dvs.

2. Interdicţia retroactivă de introducere a schimbărilor

Avem în fața noastră un tabel cu cîmpuri îndeplinite "Data" și "Cantitatea". Managerul Vasea va indica astăzi ce cantitate de morcovi a vîndut într-o zi. Cum să vă asiguraţi ca pe viitor el să nu poată face modificări retrospectiv în tabel?

—Plasați cursorul pe celula cu data și selectați din meniu "Date".

—Faceți clic pe "Validarea datelor" şi va apare un tabel.

—În lista derulantă "Tip de date", selectați "Altele".

— În celula"Formula" scrieţi =А2=ASTĂZI ().

—Scoateţi bifa de pe "Ignorați celulele goale".

—Facem click pe butonul "OK". Acum, în cazul în care o persoană dorește să introducă o dată diferită, va apărea un mesaj de avertizare.

—Puteţi interzice, de asemenea, modificarea cifrelor din coloana "Cantitatea". Aduceţi cursorul pe celula cu cantitatea şi repetaţi algoritmul de acţiuni, utilizat mai sus.

3. Interzicerea introducerilor duble

Doriţi să introduceţi lista mărfurilor în lista de prețuri, astfel încît acestea să nu se repete puteți seta interdicţia pentru o astfel de repetare. În exemplul prezentat este indicată o formulă pentru o coloană din 10 celule, cantitatea acestora poate fi însă oricare.

—Selectaţi celulele A1: A10, asupra cărora va fi extinsă interdicția.

—Selectaţi din bara de instrumente a meniului "Date", facem click pe "Validarea datelor".

—Din subopţiunea "Parametri" a listei derulante selectaţi "Tip de date", alegînd varianta "Altele".

—În coloana „Formula" introducem =SUM dacă ($A$1:$A$10;A1)<=1.

—În aceeași fereastră, trecem la subopţiunea "Mesaj despre eroare" și acolo introducem textul care va apărea atunci cînd se va încerca introducerea unui duplicat.

— Tastăm «ОК».

4. Sumarea selectivă

Aveţi în faţă un tabel din care reiese clar că diferiţi comanditari au cumpărat de la Dvs. de mai multe ori produse diferite contra anumitor sume de bani. Vreţi să aflaţi care e suma totală pe care clientul numit ANTON a cheltuit-o cumpărînd de la Dvs. carne de crabi (Boston Crab carne).

— În celula G4 introduceți numele comanditarului Anton.

— În celula G5 — denumirea produsului Boston Crab Meat.

—Treceţi la celula G7, unde va fi calculată suma și scrieţi pentru ea formula{= SUM ((C3: C21 = G4) * (B3: B21 = G5) * D3: D21)}. La început aceasta sperie prin dimensiuni, dar dacă scrieţi treptat sensul acesteia devine clar.

— Introducem întîi{=SUMA şi deschidem parantezele, în care vor fi 3 termeni de multiplicare.

— Primul termen de înmulţire (С3:С21=G4) caută în lista specificată de clienţi numele ANTON.

— Cel de-al doilea termen de multiplicare (B3:B21=G5) face acelaşi lucru cu Boston Crab Meat.

— Cel de-al treilea termen de multiplicare D3:D21 răspunde de coloana unde se indică costul, după care închidem parantezele.

— În loc de Enter la scrierea formulelor în Excel trebuie să tastaţi Ctrl + Shift + Enter.

5. Tabel recapitulativ (generalizator)

Aveţi un tabel unde este indicat ce marfă cărui client şi contra cărei sume de bani a fost vîndută de către un manager concret. Cînd tabelul conţine foarte multă informaţie, este foarte dificil să selectați anumite date din el. Spre exemplu, doriți să înțelegeţi contra cărei sume de bani a fost vîndut morcovul sau care dintre manageri a îndeplinit cele mai multe comenzi, pentru a rezolva astfel de probleme, există tabelele recapitulative (generalizatoare) în Excel. Pentru a crea un astfel de tabel trebuie să:

—Tastaţi de pe bara de meniuri „Inserte" tasta „Pivot table"

—În fereastra apărută faceți click pe "OK".

—Va apărea o fereastră în care veţi puteți crea un nou tabel, folosind doar datele vare vă interesează.

6. Bon de casă

Pentru a calcula valoarea totală a comenzii, putem proceda ca de obicei: adăugăm o coloană în care trebuie să înmulțim prețul și cantitatea, iar apoi calculăm suma totală conform acestei coloane. Iar dacă lăsaţi deoparte frica de formule, o puteţi face cu mult mai graţios.

— Evidenţiaţi celula C7.

— Introduceţi =SUM(

— Evidenţiaţi diapazonul B2:B5.

— Accesaţi steluţa - * -care în Excel este semnul înmulţirii.

— Evidenţiaţi diapazonul C2:C5 şi închidem parantezele (imaginea 2).

—În loc de Enter la scrierea formulelor în Excel trebuie să tastaţi Ctrl + Shift + Enter.

7. Compararea listelor de prețuri

Acesta este un exemplu pentru utilizatorii avansați ai Excel-ului. Să presupunem că aveți două liste de preturi şi doriţi să comparaţi prețurile acestora. Pe 1 și a 2 imagine sînt listele de prețuri din 4 și 11 mai 2010. O parte din mărfurile din ele nu corespund şi cum aflaţi, bunăoară, care anume din aceste produse nu corespund.

— Creaţi în Registru (Book) încă o pagină și copiaţi în acesta listele cu marfă din prima şi din a doua listă de preţuri.

— Pentru a scăpa de dublarea mărfurilor, selectaţi întreaga listă de produse, inclusiv denumirea acesteia.

— Alegeţi din meniu „Date" — „Filtru" — „Filtrare şi sortare".

— În fereastra apărută bifaţi 3 opţiuni:

а) copiaţi rezultatul în altă parte;

б) plasaţi rezultatul în diapazonul respectiv – selectați locul, unde doriţi să salvați rezultatul, în exemplul nostru aceasta este celula D4;

в) bifați "doar înregistrările unice" (Unique records only)

—Faceţi click pe butonul "OK" și începînd de la celula D4, obţineţi lista fără dublări.

—Ștergeţi lista inițială de mărfuri.

—Adăugaţi coloane pentru a descărca informaţiile listelor de prețuri pentru 4 și 11 mai și coloana pentru comparaţie.

—Introduceţi în coloana de comparaţie formula = D5-C5, care va calcula diferența.

—Nu vă rămîne decît să fie încărcaţi automat în coloanele "4 mai" și "11 mai" conţinutul listelor de prețuri respective. Pentru a face acest lucru, folosiţi funcția: = VLOOKUP (căutare_valoare, matrice_tabel, num_index_col, [zonă_căutare]).

— „căutare_valoare" —acesta va fi rîndul, pe care-l vom căuta în tabelul listei de prețuri. Cel mai simplu mod de a căuta mărfurile e după denumirea lor.

— „matrice_tabel" — o serie de date, dintre care le veţi căuta pe cele relevante pentru noi. Acestea urmează să fie selectate din tabelul care conține lista de prețuri din data de 4.

— „num_index_col" —acesta este numărul de ordine al coloanei în diapazonul pe care l-am indicat pentru căutarea datelor. Pentru căutare am stabilit tabelul compus din două coloane. Preţul este indicat în cea de-a doua.

— „zonă_căutare". Dacă tabelul în care căutați valoarea respectivă este sortat în ordine crescătoare sau descrescătoare, este necesar să bifaţi noţiunea de ADEVĂRAT (TRUE) sau dacă sortarea lipseşte – indicaţi FALS.

— Trageţi formula în jos, fără să uitați să stabiliţi diapazoanele. Pentru aceasta înainte de litera coloanei și numărul rîndului puneţi în fața semnul dolarului (acest lucru poate fi realizat prin selectarea diapazonului dorit și apăsînd tasta F4).

— În coloana cu totalurile este prezentată diferența de prețuri a acelor poziţii de marfă, care figurează atît într-o listă de preţuri, cît şi în cealaltă. În cazul în care coloana cu totalurile apare rezultatul # N / A, acest lucru înseamnă că marfa în cauză este doar pe una dintre listele de prețuri, și, prin urmare, este imposibil să fie calculată diferența.

8. Evaluarea investițiilor

În Excel, puteți calcula valoarea netă a discount-ului (NVP), adică suma valorilor fluxurilor actualizate de numerar la zi. În exemplul nostru este calculată valoarea actualizată netă prezentă (Net Present Value), în baza unei perioade de investiții şi a patru perioade de obţinere a profiturilor (rîndul 3 "Fluxul de numerar").

— Formula din celula B6 calculează NPV cu ajutorul funcţiei financiare: =NPV($B$4;$C$3:$E$3)+B3.

—În al cincilea rînd calcularea fluxurilor de numerar actualizate în fiecare perioadă se află utilizîndu-se două formule diferite.

—În celula С5 rezultatul a fost obţinut graţie formulei =C3/((1+$B$4)^C2) (imaginea 2).

— În celula C6 acelaşi rezultat a fost obţinut cu ajutorul formulei {=SUM(B3:E3/((1+$B$4)^B2:E2))}.

9. Compararea propunerilor investiționale

În Excel putem compara care dintre cele două propuneri investiționale este mai profitabilă. Pentru aceasta trebuie să înscrieţi în două coloane volumul necesar de investiții și sumele care urmează a fi restituite pe etape, precum și să specificați separat rata de actualizare a investițiilor făcute. Cu ajutorul acestor date, putem calcula valoarea netă (NPV).

—În celula liberă trebuie să introduceţi formula =NVP(b3/12,A8:A12)+A7, unde b3 este rata de actualizare, 12 — numărul de luni în an, A8:A12 — coloana cu cifrele restituirii pe etape a investiţiilor, A7 — suma necesară de investiţii.

—Conform aceleaşi formule este calculată valoarea actualizată netă a altui proiect investiţional

—Acum, ele pot fi comparate: care dintre acestea are NPV mai mare, acel proiect este mai profitabil.