Calcolare medie ed estrarre valori con DAVERAGE e DGET

Questa lezione tratta le ultime due funzioni di database:

• DAVERAGE - la media di un campo numerico per i record corrispondenti.
• DGET - estrae un singolo valore dalla riga che corrisponde ai criteri.

Entrambe riutilizzano lo stesso schema (database, field, criteria) già noto, quindi si tratta soprattutto di imparare che cosa restituiscono, non una nuova sintassi.

DAVERAGE(database, field, criteria) calcola la media della colonna del campo nelle righe che corrispondono all'intervallo dei criteri.

• database - la tabella completa con le intestazioni.
• field - una colonna numerica di cui calcolare la media.
• criteria - il blocco contenente le regole di corrispondenza.

È simile a AVERAGEIFS, ma legge le condizioni dalle celle.

Con i dati sulle vendite nell'intervallo A1:C13, con le intestazioni Region, Rep, Amount, inserisca Region in E1 e East in E2.

La formula restituisce la media di Amount per le righe di East. Se gli ordini di East sono 1200, 800 e 1000, la media è 1000. Sostituendo E2 con West, la media si aggiorna automaticamente.

DAVERAGE accetta l'intero insieme di strumenti per i criteri. Per calcolare la media solo degli ordini di East di grandi dimensioni, crei un intervallo di criteri a due colonne: le intestazioni Region e Amount, quindi East e >1000 sotto di esse.

Le condizioni sulla stessa riga implicano AND, quindi la funzione calcola la media degli importi delle righe di East superiori a 1000. I campi vuoti vengono ignorati: nella media confluiscono solo i valori numerici.

A differenza di DSUM, che restituisce 0, DAVERAGE restituisce un errore #DIV/0! quando nessuna riga corrisponde, perché non è possibile calcolare la media di zero valori.

Per gestire il caso, verifichi prima il conteggio oppure racchiuda la funzione in IFERROR per mostrare un messaggio chiaro invece di un codice di errore.

DGET è diverso: restituisce un singolo valore del campo dell'unica riga che corrisponde ai criteri.

Lo si utilizza come una funzione di ricerca. Se si dispone di una chiave univoca, ad esempio un ID ordine, DGET recupera un campo da quel record preciso. La sua forza e il suo rischio derivano dal fatto che richiede esattamente una corrispondenza.

Supponga che la tabella contenga venditori univoci e che si desideri l'Amount del venditore «Sara». Inserisca Rep in E1 e Sara in E2.

DGET trova l'unica riga di Sara e restituisce il suo Amount. Poiché può cercare usando contemporaneamente più colonne di criteri, DGET gestisce ricerche con più chiavi che un semplice VLOOKUP non è in grado di eseguire.

DGET richiede esattamente una riga corrispondente:

• Se nessuna riga corrisponde, restituisce #VALUE!.
• Se corrispondono più righe, restituisce #NUM!.

Questi errori sono in realtà utili: segnalano che la chiave è assente o non univoca. Restringa i criteri finché non rimane esattamente un record idoneo.

Per garantire una sola corrispondenza, aggiunga altre condizioni. Per ottenere l'Amount di Sara nella regione East, utilizzi un intervallo di criteri a due colonne: le intestazioni Rep e Region, quindi Sara e East sotto di esse.

La logica AND restringe i risultati a una sola riga e DGET ne restituisce l'Amount. In questo modo DGET funziona come una funzione di ricerca pulita con più chiavi.

Poiché DGET genera un errore quando le corrispondenze sono zero o più di una, lo si può racchiudere in una formula per offrire un'esperienza più fluida. IFERROR trasforma entrambi gli errori in un messaggio leggibile.

Se si desidera segnalare un duplicato in modo diverso da una mancata corrispondenza, è possibile verificare prima il conteggio con DCOUNTA e scegliere quindi il messaggio appropriato.

Una guida rapida per scegliere lo strumento giusto:

• Serve il totale delle righe corrispondenti? Utilizzi DSUM.
• Serve il conteggio delle righe corrispondenti? Utilizzi DCOUNT o DCOUNTA.
• Serve la media delle righe corrispondenti? Utilizzi DAVERAGE.
• Serve un valore da una singola riga corrispondente? Utilizzi DGET.

Tutte e quattro condividono un unico intervallo di criteri, quindi è possibile creare un report che somma, conta, calcola medie ed esegue ricerche, il tutto guidato dallo stesso insieme di celle delle condizioni.

I criteri di DGET corrispondono a tre righe della tabella. Che cosa restituisce DGET?

La famiglia delle funzioni D è completa:

• DAVERAGE calcola la media di un campo numerico per le righe corrispondenti; se non ci sono corrispondenze, restituisce #DIV/0!.
• DGET restituisce un valore da un'unica riga corrispondente; zero corrispondenze producono #VALUE!, mentre più corrispondenze producono #NUM!.
• Entrambe condividono lo schema (database, field, criteria) e le regole AND/OR degli intervalli di criteri.
• Le si possono racchiudere in IFERROR per ottenere un risultato chiaro e professionale.

Insieme a DSUM e DCOUNT, ora è possibile eseguire report completi basati sui criteri direttamente da una tabella strutturata.