Excel pentru luarea deciziilor

în plus față de formule, un alt mod de a efectua calcule matematice în Excel este prin funcții. Funcțiile statistice aplică un proces matematic unui grup de celule dintr-o foaie de lucru. De exemplu, funcția SUM este utilizată pentru a adăuga valorile conținute într-o gamă de celule. O listă a funcțiilor statistice utilizate în mod obișnuit este prezentată în tabelul 2.4. Funcțiile sunt mai eficiente decât formulele atunci când aplicați un proces matematic unui grup de celule. Dacă utilizați o formulă pentru a adăuga valorile într-un interval de celule, va trebui să adăugați fiecare locație de celule la formula una la un moment dat. Acest lucru poate necesita foarte mult timp dacă trebuie să adăugați valorile în câteva sute de locații de celule. Cu toate acestea, atunci când utilizați o funcție, puteți evidenția toate celulele care conțin valori pe care doriți să le însumați într-un singur pas. Această secțiune demonstrează o varietate de funcții statistice pe care le vom adăuga în registrul de lucru al bugetului Personal. Pe lângă demonstrarea funcțiilor, această secțiune analizează și procentul din calculele totale și utilizarea referințelor absolute.

Tabelul 2.4 Funcții statistice utilizate în mod obișnuit

funcția sumă este utilizată atunci când trebuie să calculați totalurile pentru o gamă de celule sau un grup de celule selectate dintr-o foaie de lucru. În ceea ce privește foaia de lucru detaliată a bugetului, vom folosi funcția SUM pentru a calcula totalurile din rândul 12. Este important să rețineți că există mai multe metode pentru adăugarea unei funcții la o foaie de lucru, care va fi demonstrată în restul acestui capitol. Următoarele ilustrează modul în care o funcție poate fi adăugată la o foaie de lucru tastând-o într-o locație de celulă:

1. Faceți clic pe fila foaie de lucru detaliu buget pentru a deschide foaia de lucru.
2. Faceți clic pe celula C12.
3. tastați un semn egal =.
4. tastați suma numelui funcției.
5. introduceți o paranteză deschisă (.
6. Faceți clic pe celula C3 și trageți în jos la celula C11. Aceasta plasează intervalul C3: C11 în funcție.
7. paranteză de închidere tip A ).
8. apăsați tasta ENTER. Funcția calculează totalul pentru coloana de cheltuieli lunare, care este de 1.496 USD.

figura 2.11 arată aspectul funcției sumă adăugată la foaia de lucru detaliată a bugetului înainte de a apăsa tasta ENTER.

așa cum se arată în figura 2.11, funcția SUM a fost adăugată la celula C12. Cu toate acestea, această funcție este necesară și pentru a calcula totalurile din coloanele cheltuieli anuale și cheltuieli LY. Funcția poate fi copiată și inserată în aceste locații de celule din cauza referențierii relative. Referențierea relativă servește aceluiași scop pentru funcții ca și pentru formule. Următoarele demonstrează modul în care rândul total este finalizat:

1. Faceți clic pe celula C12 din foaia de lucru cu detalii bugetare.
2. Faceți clic pe butonul Copiere din fila Pornire a panglicii.
3. evidențiați celulele D12 și E12.
4. Faceți clic pe butonul Lipire din fila Acasă a panglicii. Aceasta lipeste funcția SUM în celulele D12 și E12 și calculează totalurile pentru aceste coloane.
5. Faceți clic pe celula F11.
6. Faceți clic pe butonul Copiere din fila Pornire a panglicii.
7. Faceți clic pe celula F12, apoi faceți clic pe butonul Lipire din fila Acasă a panglicii. Deoarece acum avem totaluri în rândul 12, putem lipi formula de schimbare procentuală în acest rând.

figura 2.12 arată ieșirea funcției SUM care a fost adăugată celulelor C12, D12 și E12. În plus, formula de schimbare procentuală a fost copiată și lipită în celula F12. Observați că această versiune a bugetului planifică o scădere a cheltuielilor cu 1,7% față de anul trecut.

referințe Absolute (calcularea procent din totaluri)

fișier de date: continuați cu bugetul personal CH2.

deoarece totalurile au fost adăugate la rândul 12 din foaia de lucru detaliată a bugetului, un procent din calculul total poate fi adăugat la coloana B începând din celula B3. Procentul din calculul total arată procentul pentru fiecare valoare din coloana cheltuieli anuale în raport cu totalul din celula D12. Cu toate acestea, după crearea formulei, va fi necesar să dezactivați caracteristica de referință relativă Excel înainte de a copia și lipi formula în restul locațiilor celulelor din coloană. Dezactivarea funcției de referință relativă Excel se realizează printr-o referință absolută. Următorii pași explică modul în care se face acest lucru:

1. Faceți clic pe celula B3 din foaia de lucru cu detalii bugetare.
2. tastați un semn egal =.
3. Faceți clic pe celula D3.
4. tip o bară oblică înainte /.
5. Faceți clic pe celula D12.
6. apăsați tasta ENTER. Veți vedea că utilitățile casnice reprezintă 16,7% din bugetul anual de cheltuieli (vezi figura 2.14).

Figura 2.14 prezintă formula completată care calculează procentul pe care îl reprezintă cheltuielile anuale ale utilităților casnice la cheltuielile anuale totale pentru buget (a se vedea celula B3). În mod normal, am copia această formulă și am lipi-o în intervalul B4:B11. Cu toate acestea, din cauza referențierii relative, ambele referințe de celule vor crește cu un rând pe măsură ce formula este lipită în celulele de sub B3. Acest lucru este bine pentru prima referință de celulă din formula (D3), dar nu pentru a doua referință de celulă (D12). Figura 2.15 ilustrează ce se întâmplă dacă lipim formula în intervalul B4:B12 în starea sa actuală. Observați că Excel produce codul de eroare #DIV/0. Aceasta înseamnă că Excel încearcă să împartă un număr la zero, ceea ce este imposibil. Privind formula din celula B4, vedeți că prima referință a celulei a fost schimbată de la D3 la D4. Acest lucru este în regulă, deoarece acum dorim să împărțim cheltuielile anuale pentru asigurare la cheltuielile anuale totale din celula D12. Cu toate acestea, Excel a schimbat și referința celulei D12 la D13. Deoarece locația celulei D13 este goală, formula produce codul de eroare #DIV/0.

pentru a elimina eroarea divide-by-zero prezentată în figura 2.15, trebuie să adăugăm o referință absolută la celula D12 din formulă. O referință absolută împiedică referențierea relativă să schimbe o referință de celulă într-o formulă. Aceasta este denumită și blocarea unei celule. Următoarele explică modul în care se realizează acest lucru:

1. faceți dublu clic pe celula B3.
2. plasați indicatorul mouse-ului în fața D12 și faceți clic pe. Cursorul intermitent trebuie să fie în fața D în referința celulei D12.
3. apăsați tasta F4. Veți vedea un semn dolar ($) adăugat în fața literei coloanei D și a numărului rândului 12. De asemenea, puteți introduce semnele dolarului în fața literei coloanei și a numărului rândului.
4. apăsați tasta ENTER.
5. Faceți clic pe celula B3.
6. Faceți clic pe butonul Copiere din fila Pornire a panglicii.
7. evidențiați intervalul B4:B11.
8. Faceți clic pe butonul Lipire din fila Acasă a panglicii.

figura 2.16 arată procentul din formula totală cu o referință absolută adăugată la D12. Observați că în celula B4, referința celulei rămâne D12 în loc să se schimbe la D13 așa cum se arată în figura 2.15. De asemenea, veți vedea că procentele sunt calculate în restul celulelor din coloană, iar eroarea divide-by-zero este acum eliminată.

funcția de numărare

fișier de date: continuați cu bugetul personal CH2.

următoarea funcție pe care o vom adăuga la foaia de lucru detaliată a bugetului este funcția de numărare. Funcția COUNT este utilizată pentru a determina câte celule dintr-un interval conțin o intrare numerică. Funcția de numărare nu va funcționa pentru numărarea textului sau a altor intrări non-numerice. Pentru foaia de lucru detaliată a bugetului, vom folosi funcția de numărare pentru a număra numărul de elemente planificate în coloana cheltuieli anuale (coloana D). Următoarele explică modul în care funcția de numărare este adăugată la foaia de lucru utilizând lista de funcții:

1. Faceți clic pe celula D13 din foaia de lucru cu detalii bugetare.
2. tastați un semn egal =.
3. tastați litera C.
4. Faceți clic pe săgeata în jos din bara de defilare a listei de funcții (a se vedea figura 2.17) și găsiți numărul de cuvinte.
5. faceți dublu clic pe numărul de cuvinte din lista de funcții.
6. evidențiați intervalul D3:D11.
7. puteți introduce o paranteză de închidere ) și apoi apăsați tasta ENTER sau pur și simplu apăsați tasta ENTER și Excel va închide funcția pentru dvs. Funcția produce o ieșire de 9, deoarece există 9 elemente planificate pe foaia de lucru.

Figura 2.17 afișează caseta listă de funcții care apare după parcurgerea pașilor 2 și 3 pentru funcția de numărare. Lista de funcții oferă o metodă alternativă pentru adăugarea unei funcții la o foaie de lucru.

figura 2.18 arată ieșirea funcției de numărare după apăsarea tastei ENTER. Funcția numără numărul de celule din intervalul D3: D11 care conțin o valoare numerică. Rezultatul a 9 indică faptul că există 9 categorii planificate pentru acest buget.

funcția medie

următoarea funcție pe care o vom adăuga la foaia de lucru cu detalii bugetare este funcția medie. Această funcție este utilizată pentru a calcula media aritmetică pentru un grup de numere. Pentru foaia de lucru detaliată a bugetului, vom folosi funcția pentru a calcula media valorilor din coloana cheltuieli anuale. Vom adăuga acest lucru în foaia de lucru utilizând biblioteca de funcții. Următorii pași explică modul în care se realizează acest lucru:

1. Faceți clic pe celula D14 din foaia de lucru cu detalii bugetare.
2. Faceți clic pe fila Formule din panglică.
3. Faceți clic pe butonul Mai multe funcții din grupul de comenzi Biblioteca de funcții.
4. plasați indicatorul mouse-ului peste opțiunea Statistică din lista derulantă de opțiuni.
5. Faceți clic pe numele funcției medii din lista funcțiilor care apar în meniu (a se vedea figura 2.19). Aceasta deschide caseta de dialog Argumente funcționale.
6. Faceți clic pe butonul de dialog restrângere din caseta de dialog Argumente funcționale (a se vedea figura 2.20).
7. evidențiați intervalul D3:D11.
8. Faceți clic pe butonul extindeți dialogul din caseta de dialog Argumente funcționale (vezi figura 2.21). De asemenea, puteți apăsa tasta ENTER pentru a obține același rezultat.
9. Faceți clic pe butonul OK din caseta de dialog Argumente funcționale. Aceasta adaugă funcția medie la foaia de lucru.

figura 2.19 ilustrează modul în care o funcție este selectată din biblioteca de funcții din fila Formule a panglicii.

figura 2.20 afișează caseta de dialog Argumente funcționale. Aceasta apare după ce o funcție este selectată din biblioteca de funcții. Butonul de dialog colaps este utilizat pentru a ascunde caseta de dialog, astfel încât o gamă de celule pot fi evidențiate pe foaia de lucru și apoi adăugate la funcția.

figura 2.21 arată modul în care o gamă de celule poate fi selectată din caseta de dialog Argumente funcționale după ce a fost restrânsă.

Figura 2.22 afișează caseta de dialog Argumente funcționale după ce intervalul de celule este definit pentru funcția medie. Caseta de dialog arată rezultatul funcției înainte de a fi adăugată la locația celulei. Acest lucru vă permite să evaluați ieșirea funcției pentru a determina dacă are sens înainte de a o adăuga în foaia de lucru.

figura 2.23 arată funcția medie finalizată în foaia de lucru cu detalii bugetare. Rezultatul funcției arată că, în medie, ne așteptăm să cheltuim 1.994 USD pentru fiecare dintre categoriile enumerate în coloana a a bugetului. Acest calcul al cheltuielilor medii pe categorie poate fi utilizat ca indicator pentru a determina ce categorii costă mai mult sau mai puțin decât cheltuielile medii bugetate.

funcțiile MAX și MIN

fișier de date: continuați cu bugetul personal CH2.

ultimele două funcții statistice pe care le vom adăuga la foaia de lucru detaliată a bugetului sunt funcțiile MAX și MIN. Aceste funcții identifică cele mai mari și cele mai mici valori dintr-o gamă de celule. Următorii pași explică modul de adăugare a acestor funcții în foaia de lucru cu detalii bugetare:

1. Faceți clic pe celula D15 din foaia de lucru cu detalii bugetare.
2. tastați un semn egal =.
3. tastați cuvântul MIN.
4. introduceți o paranteză deschisă (.
5. evidențiați intervalul D3:D11.
6. tastați o paranteză de închidere ) și apăsați tasta ENTER sau pur și simplu apăsați tasta ENTER și Excel va închide funcția pentru dvs. Funcția MIN produce o producție de 1.200 USD, care este cea mai mică valoare din coloana cheltuielilor anuale (a se vedea figura 2.24).
7. Faceți clic pe celula D16.
8. tastați un semn egal =.
9. tastați cuvântul MAX.
10. introduceți o paranteză deschisă (.
11. evidențiați intervalul D3:D11.
12. tastați o paranteză de închidere ) și apăsați tasta ENTER sau pur și simplu apăsați tasta ENTER și Excel va închide funcția pentru dvs. Funcția MAX produce o ieșire de 3.500 USD. Aceasta este cea mai mare valoare din coloana cheltuieli anuale (a se vedea figura 2.25).

copiați și lipiți formule(lipire fără formate)

fișier de date: continuați cu bugetul personal CH2.

după cum se arată în figura 2.25, funcțiile COUNT, AVERAGE, MIN și MAX rezumă datele din coloana cheltuieli anuale. De asemenea, veți observa că există spațiu pentru a copia și lipi aceste funcții sub coloana Ly Spend. Acest lucru ne permite să comparăm ceea ce am cheltuit anul trecut și ceea ce intenționăm să cheltuim anul acesta. În mod normal, am copia și lipi pur și simplu aceste funcții în gama E13:E16. Cu toate acestea, este posibil să fi observat marginea stilului cu două linii care a fost utilizată în jurul perimetrului gamei B13:E16. Dacă am folosi comanda obișnuită de lipire, linia dublă din partea dreaptă a intervalului E13:E16 ar fi înlocuită cu o singură linie. Prin urmare, vom folosi una dintre Paste comenzi speciale pentru a lipi doar funcțiile fără niciunul dintre tratamentele de formatare. Acest lucru se realizează prin următorii pași:

1. evidențiați intervalul D13: D16 în foaia de lucru cu detalii bugetare.
2. Faceți clic pe butonul Copiere din fila Pornire a panglicii.
3. Faceți clic pe celula E13.
4. Faceți clic pe săgeata în jos de sub butonul Lipire din fila Acasă a panglicii.
5. Faceți clic pe opțiunea formule din lista derulantă de butoane (A se vedea figura 2.26).

figura 2.26 afișează lista de butoane care apar atunci când faceți clic pe săgeata în jos de sub butonul Lipire din fila Pornire a panglicii. Un lucru de reținut despre aceste opțiuni este că le puteți previzualiza înainte de a face o selecție trăgând indicatorul mouse-ului peste Opțiuni. După cum se arată în figură, când indicatorul mouse-ului este plasat peste butonul formule, puteți vedea cum vor apărea funcțiile înainte de a face o selecție. Observați că marginea cu două linii nu se modifică atunci când această opțiune este previzualizată. De aceea, această selecție se face în locul opțiunii de lipire obișnuită.