Funzione AGGREGATE in Excel
AGGREGATE La funzione in Excel restituisce l'aggregato di una determinata tabella di dati o elenchi di dati, questa funzione ha anche il primo argomento come numero della funzione e ulteriori argomenti sono per un intervallo di set di dati, il numero della funzione deve essere ricordato per sapere quale funzione utilizzare .
Sintassi
Esistono due sintassi per la formula AGGREGATA:
- Sintassi di riferimento
= AGGREGATE (function_num, options, ref1, ref2, ref (3), …)
- Sintassi degli array
= AGGREGATE (function_num, options, array, (k))
Function_num è un numero che denota una funzione specifica che vogliamo utilizzare; è un numero compreso tra 1 e 19
Opzione: è anche un valore numerico compreso tra 0 e 7 e determina quali valori devono essere ignorati durante i calcoli
Ref1, ref2, ref (3): è l'argomento durante l'utilizzo della sintassi di riferimento; è un valore numerico o dei valori su cui si desidera eseguire il calcolo, sono richiesti almeno due argomenti. Gli argomenti di riposo sono facoltativi.
Array: è un array di valori su cui vogliamo operare; viene utilizzato nella sintassi degli array della funzione AGGREGATE in Excel.
K: è un argomento opzionale e un valore numerico; viene utilizzato quando viene utilizzata la funzione come LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC in Excel.
Esempi
Esempio 1
Supponiamo di avere un elenco di numeri e calcoleremo la media, il conteggio che è il numero di celle che contengono un valore, il conteggio delle celle che non sono vuote, il massimo, il minimo, il prodotto e la somma dei valori numerici forniti . I valori sono riportati di seguito nella tabella:
Calcoliamo prima la media nella riga 9, per tutti i valori dati. Per la media, la funzione_ num è 1
Nella colonna C, vengono forniti tutti i valori e non dovremo ignorare alcun valore in modo da selezionare l'opzione 4 (ignorare nulla)
E selezionando l'intervallo di valori C1: C8 come matrice di valori numerici
Poiché ' k' è un argomento opzionale e viene utilizzato quando viene utilizzata una funzione come LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC o QUARTILE.EXC, ma in questo caso stiamo calcolando la media per omettere il valore di k.
Quindi, il valore medio è
Allo stesso modo, per l'intervallo D1: D8, selezioneremo nuovamente l'opzione 4.
Per l'intervallo E1: E8, una cella E6 contiene un valore di errore. Se usiamo la stessa formula AGGREGATA, otterremo un errore. Tuttavia, quando viene utilizzata un'opzione appropriata, AGGREGATO in Excel fornisce la media dei valori rimanenti trascurando il valore di errore in E6.
Per ignorare i valori di errore, abbiamo l'opzione 6.
Allo stesso modo, per l'intervallo G1: G8, useremo l'opzione 6 (ignora i valori di errore)
Ora, per l'intervallo H3, se inseriamo un valore 64 e nascondiamo la terza riga e utilizziamo l'opzione 5, per ignorare la riga nascosta, AGGREGATE in Excel, forniremo il valore medio solo per i valori numerici visibili.
Stampa senza nascondere la riga 3
Uscita dopo aver nascosto la riga 3
Applicando la formula AGGREGATO per altre operazioni, abbiamo
Esempio: n. 2
Supponiamo di avere una tabella per le entrate generate in date diverse dai diversi canali come indicato di seguito
Ora, vogliamo controllare le entrate generate per i diversi canali. Quindi, quando applichiamo la funzione di somma, otteniamo il totale delle entrate generate, ma nel caso in cui vogliamo controllare le entrate generate per il canale organico o il canale diretto o qualsiasi altro, quando applichiamo filtri in Excel per lo stesso, la funzione di somma darà sempre la somma totale
Vogliamo che quando filtriamo il canale, otteniamo la somma dei valori visibili, quindi invece di usare la funzione SUM, useremo la funzione AGGREGATE per ottenere la somma dei valori che sono visibili quando viene applicato un filtro.
Quindi, sostituendo la formula SUM con una funzione AGGREGATE con il codice opzione 5 (ignorando le righe ei valori nascosti), abbiamo,
Ora, quando applichiamo il filtro per diversi canali, mostrerà le entrate per quel canale solo quando il resto delle righe viene nascosto.
Entrate totali generate per canale diretto:
Entrate totali generate per canale organico:
Entrate totali generate per canale a pagamento:
Possiamo vedere che la funzione AGGREGA calcola i diversi valori Somma per le entrate generate per diversi canali una volta filtrati. Pertanto, la funzione AGGREGA può essere utilizzata dinamicamente per sostituire funzioni diverse per condizioni diverse senza utilizzare la formula condizionale.
Supponiamo che per la stessa tabella, alcuni dei nostri valori di entrate contengano un errore, ora dobbiamo ignorare gli errori e allo stesso tempo, se vogliamo applicare un filtro, la funzione AGGREGATE dovrebbe ignorare anche i valori delle righe nascoste.
Quando utilizziamo l'opzione 5, otteniamo l'errore per la SOMMA delle entrate totali. Per ignorare gli errori, dobbiamo usare l'opzione 6
Usando l'opzione 6, otteniamo la somma ignorando i valori di errore. Tuttavia, quando applichiamo il filtro, ad esempio, filtro per valore del canale Diretto, otteniamo la stessa somma ignorando gli errori, ma allo stesso tempo dobbiamo ignorare anche i valori nascosti.
Quindi, in questo caso, useremo l'opzione 7 che ignora i valori di errore e, allo stesso tempo, le righe nascoste.
Cose da ricordare
- La funzione AGGREGATE non riconosce la funzione _ num valore maggiore di 19 o minore di 1. Allo stesso modo, per Opzione numero, non identifica i valori maggiori di 7 e minori di 1; se forniamo altri valori, dà un #VALORE! Errore
- Accetta sempre il valore numerico e restituisce sempre un valore numerico come output
- L'AGGREGATO in Excel ha una limitazione; ignora solo le righe nascoste ma non ignora le colonne nascoste.
