Corso di Excel 2000/2003 – Creare Formule

  • Definizioni
  • Creazione di formule base
  • Le funzioni
  • Creazione di Formule utilizzando funzioni
  • Inserimento guidato di funzioni
  • Le condizioni
  • Formule in forme di matrice
  • Costanti di Matrice
  • Utilizzo delle formule per la soluzione di problemi reali

Definizioni

Formula. Una Formula è una equazione che analizza alcuni dati presenti nel nostro foglio di lavoro. Una formula può eseguire calcoli e/o combinare valori.

In Excel tutte le formule iniziano con il carattere = (uguale) e sono seguite da operandi ed operatori.

Operandi. Un operando è un valore che viene impiegato nella formula. L’operando può essere una valore costante, un riferimento, un’etichetta, un nome o una funzione.

Operatori. Un operatore è un elemento che relazione deve esistere tra gli operandi, gli operatori disponibili in Excel sono i seguenti:

+ – * / : sono i comuni operatori aritmetici

% : percentuale (es. 20%)

^ : elevamento a potenza (es. 3^2)

& : esegua la concatenazione di due stringhe di testo

= > < : operatori di confronto semplici

<> : diverso (risulta vero se i due operandi confrontati sono diversi)

<= e >= : indicano le operazioni di minore o uguale e maggiore o uguale rispettivamente

Ordine di Precedenza. Le formule sono valutate da sinistra a destra seguendo un ordine di precedenza predefinito, con questo termine ci si riferisce ad un ordinamento imposto sugli operatori che stabilisce quali debbano essere eseguiti per primi durante il calcolo di una formula.

L’ordine di precedenza può essere illustrato dalla seguente tabella :

Operatore Descrizione
: (due punti)
, (virgola)
(spazio singolo)
Operatori di riferimento
Operatore di negazione (solo quando viene utilizzato come operatore di negazione)
% Percentuale
^ Elevamento a potenza
* e / Moltiplicazione e Divisione
+ e Somma e sottrazione
& Concatenazione di due stringhe
= < > <= >= <> Operatori di confronto

Tabella 4 – Ordine di precedenza degli operatori

Gli operatori posti prima nella tabella hanno precedenza maggiore.

Per una descrizione dettaglia dell’utilizzo degli operatori è possibile consultare la guida in linea alla voce Creazione di formule e controllo di cartelle di lavoro, Immissione formule, Operatori di calcolo delle formule.

Quando due operatori hanno la stessa precedenza il calcolo della formula avviene da sinistra a destra.

Per fare un esempio se noi scriviamo 3+5*2, prima viene calcolato il valore della moltiplicazione (5*2=10) quindi si procede a calcolare la somma tra questo risultato ed il valore 3, il risultato è 13.

Se si vuole impostare un ordine di precedenza diverso da quello stabilito dal programma si possono  utilizzare le parentesi tonde. Nel caso dell’esempio precedente se noi volessimo calcolare prima la somma 3+5 e quindi moltiplicare per 2 dobbiamo scrivere (3+5)*2.

L’utilizzo delle parentesi ha anche il vantaggio di migliorare la leggibilità della formula.

Funzione.Una funzione è una formula predefinita che esegue dei calcoli usando valori forniti dall’utente (detti argomenti).
La funzione è costituita da:

  • Un nome il quale identifica in modo univoco la funzione
  • Un insieme di argomenti, posti dopo il nome e racchiusi tra parentesi tonde, i quali rappresentano i valori utilizzati dalla funzione per elaborare il suo risultato.
    Gli argomenti possono essere costanti, riferimenti, valore di errore, vettori, funzioni o formule.
    Gli argomenti sono separati l’uno dall’altro mediante l’utilizzo di virgole.

Add-In (Componeti Aggiuntivi). Excel consente di installare nuovi programmi o componenti sviluppati separatamente. Questi hanno solitamente lo scopo di fornire nuove funzioni e nuove caratteristiche al programma.
Perché questi siano disponibili è necessario prima installarli, quindi procedere al loro caricamento, inoltre possono anche installare nuovi pulsanti sulla barre dei pulsanti o nei menù. Visto che molto spesso questi componenti richiedono un quantità considerevole di memoria è consigliabile provvedere a scaricare (unload) quelli non necessari dalla memoria, al fine da evitare che le prestazioni del programma non degenerino.
N.B.: Scaricare un componente dalla memoria non equivale a rimuoverlo dal disco, questo e sempre disponibile per un caricamento immediato qualora se ne riscontrasse la necessità.

Creazione di una Formula

Il primo passo per la creazione di una formula è l’immissione del simbolo = (uguale) nella cella destinata a contenerla; in alternativa è sempre possibile selezionare la cella in questione e premere il pulsante uguale presente nella barra della formula (Figura 17).

Figura 8 - Barra della Formula

Figura 17 - Barra della Formula

In entrambi i casi, appena effettuata l’operazione, Excel provvede a visualizzare una nuova versione della barra della formula (Figura 18) la quale permette di immettere direttamente la formula o attivare la modalità di costruzione guidata.

Il primo caso è solitamente riservato agli utenti più esperti, i quali conosco perfettamente la sintassi della funzione, il secondo è  stato introdotto per facilitare la creazione ai principianti.

È importante notare che posta sotto la barra della formula (Figura 18) compare un’apposita sezione in cui viene visualizzato in anteprima il risultato della formula, questo consente di avere un riscontro immediato alla correttezza del nostro operato.

Figura 9 – Barra della Formula espansa

Figura 18 - Barra delle formula pronta per l'immissione

Per attivare la modalità di costruzione guidata della formula consente di inserire le funzioni che la compongono in modo semplice e guidato.

Per inserire una funzione nella formula è sufficiente selezionare la funzione desiderata dalla lista a discesa posta alla sinistra della barra della formula. Questa presenta una lista delle funzioni più utilizzate e come ultima voce altre funzioni che permette di visualizzare il Finestra di dialogo per incollare funzioni (Figura 19), uno strumento che presenta l’elenco di tutte le funzioni disponibili in Excel. L’utilizzo di questa è molto semplice:

  • Selezionare la categoria di funzioni desiderata nel riquadro di sinistra della finestra.
    Nel caso non si sia in grado di identificare la categoria della funzione desiderata selezionare la voce Tutte, la quale visualizza tutte le funzioni disponibili.
  • Ora selezionare la funzione desiderata dal riquadro di destra della finestra di dialogo.
    È importante notare che nella parte posta sotto i due riquadri di selezione è sempre presente una breve descrizione dell’operazione svolta dalla funzione selezionata; inoltre è possibile ottenere una descrizione dettagliata della funzione premendo il pulsante di aiuto .
  • Infine premere OK per confermare l’immissione della funzione, o Annulla per abbandonare la finestra di dialogo senza immettere alcuna funzione.
Figura 19 – Finestra di dialogo incolla funzioni

È possibile attivare direttamente dal foglio di lavoro la Finestra di dialogo per incollare funzioni (Figura 19), utilizzando i menù (Inserisci->Funzione), sia l’apposito pulsante  presente nella Barra Standard dei pulsanti (Figura 20).

Barra Standard

Figura 20 - Barra Standard

Una volta selezionata la funzione viene visualizza la finestra per l’inserimento degli argomenti della funzione (Figura 21).

Anche in questo caso è possibile inserire direttamente i valori relativi a tali argomenti, oppure utilizzare la composizione guidata.

La finestra di dialogo presenta una descrizione del significato della funzione e dei suoi argomenti; inoltre fornisce sempre un’anteprima relativi ai risultati della formula creata, del contenuto delle celle selezionate e di altri eventuali argomenti.

Figura 21 - Inserimento guidato degli argomenti di una funzione

La composizione guidata consente principalmente di definire i riferimenti alle celle mediante la selezione diretta delle stesse.

Per attivare la selezione diretta è sufficiente seguire i seguenti passi:

  • Premere il pulsante  posto alla destra del campo relativo all’argomento.
    Excel provvederà a sostituire la finestra di dialogo attuale (Figura 21) con la finestra di dialogo per la selezione delle celle (Figura 22).
  • Selezionare nel modo usuale le celle desiderate
    L’intervallo corrispondente verrà visualizzato nella finestra di dialogo attiva (Figura 22)
  • Premere il pulsante , posto alla destra di tale finestra, per confermare l’inserimento delle celle selezionate.
    Excel provvederà a chiudere la finestra di dialogo attuale (Figura 22) e tornare alla visualizzazione della finestra per l’inserimento degli argomenti della funzione (Figura 21).
    Nel caso si voglia annullare l’operazione di inserimento è sufficiente premere il tasto ESC, oppure cancellare il contenuto della finestra di dialogo e confermare l’inserimento.

Figura 22 - Barra per il selezionamento automatico delle celle

Il procedimento è identico per ogni argomento.

Una volta effettuato l’inserimento di tutti i parametri premere OK per confermare l’inserimento o Annulla per chiudere la finestra di dialogo senza apportare modifiche al contenuto della cella.

Per inserire direttamente una formula da tastiera è necessario ricordare le seguenti regole sulla composizione di una formula e di una funzione:

  • Tutte le formule iniziano con il simbolo uguale
  • Una funzione è composta da un nome che la identifica univocamente e da una lista di argomenti.
  • La lista di argomenti è racchiusa all’interno di una coppia di parentesi tonde ( ).
  • Gli argomenti sono separati tra loro dal carattere ; (punto e virgola)
  • Ogni argomento deve essere del tipo corretto.
    In pratica se la sintassi della funzione prevede l’inserimento di un valore numerico, non è consentito inserire un valore testuale e così via.
  • Il numero di argomenti deve corrispondere al numero di argomenti previsto dalla sintassi della funzione.

In sintesi una funzione è così strutturata :

            nomefunzione (argomento1; argomento2; argomento3; argomento4)

Formule in Forma di Matrice

Una formula in forma di matrice può eseguire più calcoli e restituire uno o più risultati. Le formule in forma di matrice agiscono su uno o più insiemi di valori denominati argomenti matrice. È necessario che ciascun argomento matrice sia costituito dallo stesso numero di righe e di colonne. Le formule in forma di matrice vengono create allo stesso modo delle altre formule, con la sola differenza che occorre premere CTRL+MAIUSC+INVIO per immetterle.

Per ottenere un unico risultato può essere necessario effettuare più elaborazioni sugli stessi dati, prendiamo per esempio il calcolo delle ore totali di straordinario svolte da un dipendente in un mese; per calcolare il loro effettivo valore dobbiamo prima calcolare la quantità di straordinario svolta ogni giorno, quindi eseguire la somma di questi tempi.

In forma matematica potremmo scrivere che Somma(ore lavorate – 8), dove 8 è il numero di ore lavorative giornaliere standard.

Se vogliamo trasferire in Excel tale formula doppiamo selezionare la cella in cui calcolare il valore risultante, quindi iniziare ad inserire la funzione somma, come argomento della funzione somma dobbiamo inserire la differenza tra il valore della cella in esame ed il numero di ore lavorative giornaliere, che abbiamo stabilito essere 8.
Per fare questo è sufficiente prima inserire l’intervallo desiderato nel modo comune, quindi aggiunge la notazione –8 al seguito, al fine di ottenere le ore o di straordinario.

A questo punto nella nostra barra della formula (Figura 17) verrà visualizzato =SOMMA(B2:F2-8), dove B2:F2 rappresenta l’intervallo contente le ore lavorative.

Ora se noi premiamo invio, la formula viene inserita nel foglio elettronico e come risultato otteniamo #VALORE!, che è un errore.
Per effettuare l’immissione di tale formula è necessario premere CTRL+MAIUSC+INVIO invece del semplice invio, questo per segnalare ad excel che si tratta di una formula in forma di matrice.

Utilizzando tale tecnica possiamo notare che la barra della formula (Figura 17) contiene la seguente notazione {=SOMMA(B2:F2-8)}, la presenza delle parentesi graffe indica appunto la presenza di una formula in forma di matrice.

È importante notare che se noi andiamo a posizionarci all’interno della barra della formula (Figura 17) per modificare la nostra formula le parentesi graffe scompaiono istantaneamente; queste vengono visualizzate solo dopo che noi abbiamo inserito la formula mediante la pressione di CTRL+MAIUSC+INVIO.

Per calcolare più risultati con una formula in forma di matrice, è necessario selezionare un numero di celle uguale a tra risultato e matrice, per chiarire meglio il concetto illustriamo graficamente il concetto


Se il mio risultato viene sviluppato su una colonna allora dovrò selezionare una colonna ed un numero di righe pari a quelle della matrice, in questo caso 2 (parte con righe diagonali).Se la mia matrice è di tipo 2×3 (parte in con righe verticale) ed il mio risultato viene sviluppato su una riga e più colonne allora per il risultato dovrò selezionare una riga ed un numero di colonne pari a quelle della mia matrice, in questo caso 3 (parte con grata).

Infine nel caso il mio risultato si sviluppi su un’intera matrice questa dovrà avere la stessa dimensione della matrice originale, in questo caso 2×3.

N.B.: Le formule in forma di matrice non sono valide in celle unite.

Inserimento di costanti

In una formula in forma di matrice è possibile inserire direttamente la matrice di valori contenuti nelle celle. La matrice di valori inserita viene denominata costante di matrice e viene in genere utilizzata se non si desidera immettere ciascun valore in una diversa cella del foglio di lavoro. Per creare una costante di matrice, è necessario effettuare le seguenti operazioni:

  • Immettere i valori direttamente nella formula, racchiusi tra parentesi graffe ( { } )
  • Separare i valori appartenenti a colonne differenti con dei punti e virgola (;)
  • Separare i valori appartenenti a righe differenti con delle barre rovesciate (\)

Per esempio data la matrice

4

9

6

56

34

27

VERO

VERO

FALSO

“oggi”

“domani”

“festa”

Questa può essere inserita come costante nel seguente modo :

{4;9;6 \ 56;34;27; \ VERO;VERO;FALSO \ “oggi”; “domani” ; “festa”}

Questa costante di matrice è denominata matrice 4×3 ed è equivalente a un riferimento a 4 righe x 3 colonne all’interno di un foglio elettronico (Figura 23).

Figura 23 - Rappresentazione di una matrice nel foglio di lavoro

I possibili elementi di una costante di matrice sono

  • Numeri, Testo, Valori logici, Valori di errore.
  • I numeri possono essere interi, decimali o in formato scientifico.
  • È possibile utilizzare diversi tipi di valori nella stessa costante di matrice, ad esempio {1;3;4\VERO;FALSO;VERO}.

Le costanti di matrice non possono contenere

  • Il carattere $ (segno di dollaro)
  • Le parentesi
  • Il carattere % (segno di percentuale).
  • Riferimenti di cella.
  • Colonne o righe di lunghezza diversa.
  • Formule o funzioni
La potenzialità dei fogli elettronici è legata alle funzioni fornite, dal mio punto di vista è importate separare nettamente lo studio delle funzioni da quello di Excel. La conoscenza della funzione è legata strettamente a domini scientifici diversi (per esempio il coseno è un funzione di trigonometrica per capirla bisogna studiare trigonometria) che nulla hanno a che vedere con Excel. Chi volesse approfondire il discorso legato alle funzioni può utilizzare l”aiuto di Microsoft che generalmente presenta una buona descrizione della funzione e del suo utilizzo, e ricercare in internet informazioni specifiche, sicuramente più utili che un tutorial su Excel.

Torna alla lezione precendente Le Celle o passa alla lezione successiva L’Algebra Booleana. Per informazioni sul corso leggi l’introduzione

Annunci

, , ,

  1. Lascia un commento

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

%d blogger hanno fatto clic su Mi Piace per questo: