Funzione Excel AGGREGATA

Funzione AGGREGATE in Excel

AGGREGATE La funzione in Excel restituisce l'aggregato di una determinata tabella di dati o di 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:

1. Sintassi di riferimento

= AGGREGATO (function_num, opzioni, ref1, ref2, ref [3], ...)

2. Sintassi degli array

= AGGREGATE (function_num, opzioni, 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 valori su cui si desidera eseguire il calcolo, sono richiesti almeno due argomenti gli argomenti rest sono facoltativi.

Array: è un array di valori su cui vogliamo eseguire l'operazione, viene utilizzato nella sintassi array della funzione AGGREGATE in excel

K: è un argomento facoltativo ed è 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

È possibile scaricare questo modello Excel della funzione AGGREGATO qui - Modello Excel della funzione AGGREGATO

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 dati. 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 è

Nella colonna C, vengono forniti tutti i valori e non dovremo ignorare alcun valore, quindi selezioneremo l'opzione 4 (ignorare nulla)

E selezionando l'intervallo di valori C1: C8 come matrice di valori numerici

Poiché ' k' è un argomento facoltativo 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, quindi ometterà 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 useremo la stessa formula AGGREGATO otterremo un errore, ma quando viene utilizzata un'opzione appropriata, AGGREGATO in Excel fornisce la media dei valori rimanenti trascurando l'errore valore 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 mettiamo un valore 64 e nascondiamo la terza riga e usiamo 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 diversi canali. Quindi, quando applichiamo la funzione di somma otteniamo le entrate totali generate ma nel caso in cui desideriamo 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 sarà sempre dare la somma totale

Vogliamo che quando filtriamo il canale, otteniamo la somma dei valori che sono visibili, quindi invece di usare la funzione SUM, useremo la funzione AGGREGATE per ottenere la somma dei valori che sono visibili quando un filtro è applicato.

Quindi, sostituendo la formula SUM con una funzione AGGREGATE con il codice opzione 5 (ignorando le righe ei valori nascosti) abbiamo,

Ora, quando applicheremo 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:

Quindi, possiamo vedere che la funzione AGGREGATE calcola i diversi valori Somma per le entrate generate per diversi canali una volta filtrati. Pertanto, la funzione AGGREGATO può essere utilizzata dinamicamente per la sostituzione di funzioni diverse per condizioni diverse senza utilizzare la formula condizionale.

Supponiamo che per lo stesso canale di tabella e entrate, 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 usiamo l'opzione 5, otteniamo l'errore per la SOMMA delle entrate totali, ora per ignorare gli errori dobbiamo usare l'opzione 6

Usando l'opzione 6 otteniamo la somma ignorando i valori di errore, ma quando applichiamo il filtro, ad esempio, filtra per valore di 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 e analogamente per Option number non riconosce i valori maggiori di 7 e minori di 1, se forniamo altri valori dà un #VALUE ! 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.