Excel per il processo decisionale

Oltre alle formule, un altro modo per condurre calcoli matematici in Excel è attraverso le funzioni. Le funzioni statistiche applicano un processo matematico a un gruppo di celle in un foglio di lavoro. Ad esempio, la funzione SUM viene utilizzata per aggiungere i valori contenuti in un intervallo di celle. Un elenco delle funzioni statistiche di uso comune è riportato nella Tabella 2.4. Le funzioni sono più efficienti delle formule quando si applica un processo matematico a un gruppo di celle. Se si utilizza una formula per aggiungere i valori in un intervallo di celle, è necessario aggiungere ogni posizione della cella alla formula uno alla volta. Questo può richiedere molto tempo se è necessario aggiungere i valori in poche centinaia di posizioni di cella. Tuttavia, quando si utilizza una funzione, è possibile evidenziare tutte le celle che contengono valori che si desidera sommare in un solo passaggio. Questa sezione mostra una varietà di funzioni statistiche che aggiungeremo alla cartella di lavoro del budget personale. Oltre a dimostrare le funzioni, questa sezione esamina anche la percentuale dei calcoli totali e l’uso di riferimenti assoluti.

Tabella 2.4 Comunemente Usati Funzioni Statistiche

La funzione SOMMA è utilizzata quando è necessario per calcolare i totali per un intervallo di celle o di un gruppo selezionato di celle in un foglio di lavoro. Per quanto riguarda il foglio di lavoro di dettaglio del budget, useremo la funzione SUM per calcolare i totali nella riga 12. È importante notare che esistono diversi metodi per aggiungere una funzione a un foglio di lavoro, che verranno dimostrati per tutto il resto di questo capitolo. Quanto segue illustra come una funzione può essere aggiunta a un foglio di lavoro digitandolo in una posizione di cella:

1. Fare clic sulla scheda Foglio di lavoro Dettaglio budget per aprire il foglio di lavoro.
2. Fare clic sulla cella C12.
3. Digitare un segno di uguale =.
4. Digitare il nome della funzione SUM.
5. Digitare una parentesi aperta (.
6. Fare clic sulla cella C3 e trascinare verso il basso nella cella C11. Questo pone l’intervallo C3: C11 nella funzione.
7. Digitare una parentesi di chiusura).
8. Premere il tasto INVIO. La funzione calcola il totale per la colonna Spesa mensile, che è $1.496.

La figura 2.11 mostra l’aspetto della funzione SUM aggiunta al foglio di lavoro dei dettagli del budget prima di premere il tasto INVIO.

Come mostrato nella Figura 2.11, la funzione SUM è stata aggiunta alla cella C12. Tuttavia, questa funzione è necessaria anche per calcolare i totali nelle colonne Spesa annuale e Spesa LY. La funzione può essere copiata e incollata in queste posizioni delle celle a causa del riferimento relativo. Il riferimento relativo ha lo stesso scopo per le funzioni come per le formule. Di seguito viene illustrato come viene completata la riga totale:

1. Fare clic sulla cella C12 nel foglio di lavoro Dettagli budget.
2. Fare clic sul pulsante Copia nella scheda Home della barra multifunzione.
3. Evidenziare le celle D12 ed E12.
4. Fare clic sul pulsante Incolla nella scheda Home della barra multifunzione. Questo incolla la funzione SUM nelle celle D12 ed E12 e calcola i totali per queste colonne.
5. Fare clic sulla cella F11.
6. Fare clic sul pulsante Copia nella scheda Home della barra multifunzione.
7. Fare clic sulla cella F12, quindi fare clic sul pulsante Incolla nella scheda Home della barra multifunzione. Poiché ora abbiamo totali nella riga 12, possiamo incollare la formula del cambiamento percentuale in questa riga.

La figura 2.12 mostra l’output della funzione SOMMA aggiunta alle celle C12, D12 ed E12. Inoltre, la formula di variazione percentuale è stata copiata e incollata nella cella F12. Si noti che questa versione del bilancio prevede una diminuzione dell ‘ 1,7% della spesa rispetto allo scorso anno.

Riferimenti assoluti (calcolo percentuale dei totali)

File di dati: continuare con CH2 Personal Budget.

Poiché i totali sono stati aggiunti alla riga 12 del foglio di lavoro dei dettagli del budget, è possibile aggiungere una percentuale del calcolo totale alla colonna B che inizia nella cella B3. La percentuale del calcolo totale mostra la percentuale per ogni valore nella colonna Spesa annuale rispetto al totale nella cella D12. Tuttavia, dopo la creazione della formula, sarà necessario disattivare la funzione di riferimento relativo di Excel prima di copiare e incollare la formula al resto delle posizioni delle celle nella colonna. La disattivazione della funzione di riferimento relativo di Excel viene eseguita tramite un riferimento assoluto. I seguenti passaggi spiegano come questo è fatto:

1. Fare clic sulla cella B3 nel foglio di lavoro Dettagli budget.
2. Digitare un segno di uguale =.
3. Fare clic sulla cella D3.
4. Digitare una barra in avanti/.
5. Fare clic sulla cella D12.
6. Premere il tasto INVIO. Vedrai che le utenze domestiche rappresentano il 16,7% del budget di spesa annuale (vedi Figura 2.14).

Figura 2.14 mostra la formula completata che calcola la percentuale che la spesa annuale delle utenze domestiche rappresenta rispetto alla spesa annuale totale per il budget (vedere cella B3). Normalmente, copieremmo questa formula e la incolleremmo nell’intervallo B4:B11. Tuttavia, a causa del riferimento relativo, entrambi i riferimenti di cella aumenteranno di una riga man mano che la formula viene incollata nelle celle sotto B3. Questo va bene per il primo riferimento di cella nella formula (D3) ma non per il secondo riferimento di cella (D12). La figura 2.15 illustra cosa succede se incolliamo la formula nell’intervallo B4: B12 nel suo stato corrente. Si noti che Excel produce il codice di errore # DIV/0. Ciò significa che Excel sta cercando di dividere un numero per zero, il che è impossibile. Guardando la formula nella cella B4, vedi che il primo riferimento alla cella è stato cambiato da D3 a D4. Questo va bene perché ora vogliamo dividere la spesa annuale per l’assicurazione per la spesa annuale totale nella cella D12. Tuttavia, Excel ha anche cambiato il riferimento della cella D12 a D13. Poiché la posizione della cella D13 è vuota, la formula produce il codice di errore # DIV/0.

Per eliminare l’errore DIV / 0 mostrato in Figura 2.15 dobbiamo aggiungere un riferimento assoluto alla cella D12 nella formula. Un riferimento assoluto impedisce al riferimento relativo di modificare un riferimento di cella in una formula. Questo è anche indicato come blocco di una cella. Quanto segue spiega come questo è realizzato:

1. Fare doppio clic sulla cella B3.
2. Posizionare il puntatore del mouse davanti a D12 e fare clic su. Il cursore lampeggiante dovrebbe essere davanti alla D nella cella di riferimento D12.
3. Premere il tasto F4. Vedrai un simbolo del dollaro ($) aggiunto davanti alla lettera D della colonna e al numero di riga 12. È inoltre possibile digitare i segni del dollaro di fronte alla lettera della colonna e il numero di riga.
4. Premere il tasto INVIO.
5. Fare clic sulla cella B3.
6. Fare clic sul pulsante Copia nella scheda Home della barra multifunzione.
7. Evidenziare l’intervallo B4: B11.
8. Fare clic sul pulsante Incolla nella scheda Home della barra multifunzione.

La figura 2.16 mostra la percentuale della formula totale con un riferimento assoluto aggiunto a D12. Si noti che nella cella B4, il riferimento della cella rimane D12 invece di passare a D13 come mostrato nella Figura 2.15. Inoltre, vedrai che le percentuali vengono calcolate nel resto delle celle della colonna e l’errore divide per zero viene ora eliminato.

La funzione di conteggio

File di dati: continuare con CH2 Personal Budget.

La funzione successiva che aggiungeremo al foglio di lavoro dei dettagli del budget è la funzione COUNT. La funzione COUNT viene utilizzata per determinare quante celle in un intervallo contengono una voce numerica. La funzione COUNT non funziona per il conteggio del testo o di altre voci non numeriche. Per il foglio di lavoro di dettaglio del budget, utilizzeremo la funzione COUNT per contare il numero di elementi pianificati nella colonna di spesa annuale (Colonna D). Quanto segue spiega come la funzione COUNT viene aggiunta al foglio di lavoro utilizzando l’elenco delle funzioni:

1. Fare clic sulla cella D13 nel foglio di lavoro Dettagli budget.
2. Digitare un segno di uguale =.
3. Digitare la lettera C.
4. Fare clic sulla freccia verso il basso sulla barra di scorrimento dell’elenco funzioni (vedere Figura 2.17) e trovare il CONTEGGIO delle parole.
5. Fare doppio clic sul CONTEGGIO delle parole dall’elenco delle funzioni.
6. Evidenziare l’intervallo D3: D11.
7. È possibile digitare una parentesi di chiusura) e quindi premere il tasto INVIO, o semplicemente premere il tasto INVIO e Excel chiuderà la funzione per voi. La funzione produce un output di 9 poiché ci sono 9 elementi pianificati nel foglio di lavoro.

Figura 2.17 mostra la casella di riepilogo funzione visualizzata dopo aver completato i passaggi 2 e 3 per la funzione COUNT. L’elenco delle funzioni fornisce un metodo alternativo per aggiungere una funzione a un foglio di lavoro.

La figura 2.18 mostra l’output della funzione di CONTEGGIO dopo aver premuto il tasto INVIO. La funzione conta il numero di celle nell’intervallo D3: D11 che contengono un valore numerico. Il risultato di 9 indica che ci sono 9 categorie pianificate per questo budget.

La funzione MEDIA

La funzione successiva che aggiungeremo al foglio di lavoro di dettaglio del budget è la funzione MEDIA. Questa funzione viene utilizzata per calcolare la media aritmetica per un gruppo di numeri. Per il foglio di lavoro di dettaglio del budget, useremo la funzione per calcolare la media dei valori nella colonna Spesa annuale. Aggiungeremo questo al foglio di lavoro utilizzando la libreria di funzioni. I seguenti passaggi spiegano come questo è realizzato:

1. Fare clic sulla cella D14 nel foglio di lavoro Dettagli budget.
2. Fare clic sulla scheda Formule sulla barra multifunzione.
3. Fare clic sul pulsante Altre funzioni nel gruppo di comandi Libreria funzioni.
4. Posiziona il puntatore del mouse sull’opzione Statistica dall’elenco a discesa delle opzioni.
5. Fare clic sul nome della funzione MEDIA dall’elenco delle funzioni visualizzate nel menu (vedere Figura 2.19). Si apre la finestra di dialogo Argomenti funzione.
6. Fare clic sul pulsante di dialogo Comprimi nella finestra di dialogo Argomenti funzione (vedere Figura 2.20).
7. Evidenziare l’intervallo D3: D11.
8. Fare clic sul pulsante Espandi finestra di dialogo nella finestra di dialogo Argomenti funzione (vedere Figura 2.21). È inoltre possibile premere il tasto INVIO per ottenere lo stesso risultato.
9. Fare clic sul pulsante OK nella finestra di dialogo Argomenti funzione. Questo aggiunge la funzione MEDIA al foglio di lavoro.

La figura 2.19 illustra come una funzione viene selezionata dalla Libreria di funzioni nella scheda Formule della barra multifunzione.

Figura 2.20 mostra la finestra di dialogo Argomenti funzione. Questo viene visualizzato dopo che una funzione è stata selezionata dalla libreria di funzioni. Il pulsante di dialogo Comprimi viene utilizzato per nascondere la finestra di dialogo in modo che un intervallo di celle possa essere evidenziato sul foglio di lavoro e quindi aggiunto alla funzione.

Figura 2.21 mostra come un intervallo di celle può essere selezionato dalla finestra di dialogo Argomenti funzione una volta che è stato compresso.

Figura 2.22 mostra la finestra di dialogo Argomenti funzione dopo la definizione dell’intervallo di celle per la funzione MEDIA. La finestra di dialogo mostra il risultato della funzione prima che venga aggiunta alla posizione della cella. Ciò consente di valutare l’output della funzione per determinare se ha senso prima di aggiungerlo al foglio di lavoro.

La figura 2.23 mostra la funzione MEDIA completata nel foglio di lavoro Dettaglio budget. L’output della funzione mostra che in media ci aspettiamo di spendere $1.994 per ciascuna delle categorie elencate nella colonna A del budget. Questo calcolo della spesa media per categoria può essere utilizzato come indicatore per determinare quali categorie costano più o meno dei dollari di spesa preventivati medi.

Le funzioni MAX e MIN

File di dati: continuare con CH2 Personal Budget.

Le ultime due funzioni statistiche che aggiungeremo al foglio di lavoro di dettaglio del budget sono le funzioni MAX e MIN. Queste funzioni identificano i valori più alti e più bassi in un intervallo di celle. I passaggi seguenti spiegano come aggiungere queste funzioni al foglio di lavoro dei dettagli del budget:

1. Fare clic sulla cella D15 nel foglio di lavoro Dettagli budget.
2. Digitare un segno di uguale =.
3. Digitare la parola MIN.
4. Digitare una parentesi aperta (.
5. Evidenziare l’intervallo D3: D11.
6. Digitare una parentesi di chiusura) e premere il tasto INVIO, o semplicemente premere il tasto INVIO e Excel chiuderà la funzione per voi. La funzione MIN produce un output di $1.200, che è il valore più basso nella colonna Spesa annuale (vedere Figura 2.24).
7. Fare clic sulla cella D16.
8. Digitare un segno di uguale =.
9. Digitare la parola MAX.
10. Digitare una parentesi aperta (.
11. Evidenziare l’intervallo D3: D11.
12. Digitare una parentesi di chiusura) e premere il tasto INVIO, o semplicemente premere il tasto INVIO e Excel chiuderà la funzione per voi. La funzione MAX produce un output di $3.500. Questo è il valore più alto nella colonna Spesa annuale (vedi Figura 2.25).

Copia e incolla le formule (incollando senza formati)

File di dati: continuare con CH2 Personal Budget.

Come mostrato nella Figura 2.25, le funzioni COUNT, AVERAGE, MIN e MAX riassumono i dati nella colonna Spesa annuale. Noterai anche che c’è spazio per copiare e incollare queste funzioni sotto la colonna LY Spend. Questo ci permette di confrontare ciò che abbiamo speso l’anno scorso e ciò che stiamo progettando di spendere quest’anno. Normalmente, semplicemente copiamo e incolliamo queste funzioni nell’intervallo E13:E16. Tuttavia, potresti aver notato il bordo di stile a doppia linea che è stato utilizzato attorno al perimetro dell’intervallo B13:E16. Se usassimo il comando Incolla regolare, la doppia riga sul lato destro dell’intervallo E13:E16 verrebbe sostituita con una singola riga. Pertanto, useremo uno dei comandi speciali Incolla per incollare solo le funzioni senza nessuno dei trattamenti di formattazione. Questo si ottiene attraverso i seguenti passaggi:

1. Evidenziare l’intervallo D13: D16 nel foglio di lavoro dei dettagli del budget.
2. Fare clic sul pulsante Copia nella scheda Home della barra multifunzione.
3. Fare clic sulla cella E13.
4. Fare clic sulla freccia giù sotto il pulsante Incolla nella scheda Home della barra multifunzione.
5. Fare clic sull’opzione Formule dall’elenco a discesa dei pulsanti (vedere Figura 2.26).

La figura 2.26 mostra l’elenco dei pulsanti visualizzati quando si fa clic sulla freccia giù sotto il pulsante Incolla nella scheda Home della barra multifunzione. Una cosa da notare su queste opzioni è che è possibile visualizzare in anteprima prima di effettuare una selezione trascinando il puntatore del mouse sopra le opzioni. Come mostrato in figura, quando il puntatore del mouse viene posizionato sopra il pulsante Formule, è possibile vedere come appariranno le funzioni prima di effettuare una selezione. Si noti che il bordo a doppia linea non cambia quando questa opzione viene visualizzata in anteprima. Questo è il motivo per cui questa selezione viene effettuata al posto della normale opzione Incolla.