[Tutorial Formule] Formula INDICE .. AGGREGA che trova valori corrispondenti - come funziona

Stato
Chiusa ad ulteriori risposte.

ges

Excel/VBA Expert
Amministratore
Expert
21 Giugno 2015
18.208
113
Como
2011MAC 2016WIN
324
Ciao a tutti,
cerco di spiegare questa formula e per farlo mostro un esempio più semplice

Ho nell'intervallo A1:A6 delle lettere "a,b,c,a,b,c e nell'intervallo B1:B6 dei numeri 1,2,3,4,5,6

Voglio trovare i numeri della colonna B corrispondenti alla lettera "a" della colonna A e voglio che questi dati siano restituiti in ordine nella colonna C.

Per il mio scopo usa la formula:

=INDICE($B$1:$B$6;AGGREGA(15;6;(RIF.RIGA($A$1:$A$6))/($A$1:$A$6="a");RIGHE($1:1)))



Vediamo ora come funziona questa formula.

Preso a riferimento l'INDICE interessato B1:B6 uso la funzione AGGREGA che si compone di vari argomenti

Il primo argomento è il numero della funzione che riguarda le seguenti opzioni:
[TABLE=class:-banded-flipColors,-width:-1]
Numero funzione


Funzione


1


MEDIA


2


CONTA.NUMERI


3


CONTA.VALORI


4


MAX


5


MIN


6


PRODOTTO


7


DEV.ST.C


8


DEV.ST.P


9


SOMMA


10


VAR.C


11


VAR.P


12


MEDIANA


13


MODA.SNGL


14


GRANDE


15


PICCOLO


16


INC.PERCENTILE


17


INC.QUARTILE


18


ESC.PERCENTILE


19


ESC.QUARTILE
[/TABLE]
In questo caso ci interessano solo dei valori non dovendo fare alcuna operazione e quindi c'è da scegliere se si vogliono ordinare dal valore minore o maggiore, dunque le scelte sono tra PICCOLO se si parte dall'1 a salire o GRANDE viceversa.

Nel mio caso visto che voglio che i numeri scelti partono dal più basso al più alto ho scelto PICCOLO, quindi 15

Il secondo argomento da scegliere è il comportamento, tra i seguenti:
[TABLE=class:-banded-flipColors,-width:-1]
Opzione


Comportamento


0 o omesso


Ignora le funzioni annidate SUBTOTALE e AGGREGA


1


Ignora le righe nascoste e le funzioni annidate SUBTOTALE e AGGREGA


2


Ignora i valori di errore e le funzioni annidate SUBTOTALE e AGGREGA


3


Ignora le righe nascoste, i valori di errore e le funzioni annidate SUBTOTALE e AGGREGA


4


Non ignora alcun elemento


5


Ignora le righe nascoste


6


Ignora i valori di errore


7


Ignora le righe nascoste e i valori di errore

[/TABLE]
Nel mio caso poiché voglio ordinare i valori trovati uno di seguito all'atro scelgo di "Ignorare le righe di errore" quindi 6


Fino a qui la formula è

=INDICE($B$1:$B$6;AGGREGA(14;6;

A questo punto serve il riferimento numerico della matrice di riferimento, cioè le righe corrispondenti ai valori da cercare che sono A1:A6 e poiché voglio che trascinando la formula non si modificano li imposto con i valori assoluti $A$1:$A$6 e poiché mi servono i riferimenti per ciascuna riga, per scorrere le righe uso la funzione RIF.RIGA, dunque

=INDICE($B$1:$B$6;AGGREGA(14;6;RIF.RIGA($A$1:$A$6)

A questo punto divido il valore di ciascuna riga RIF.RIGA($A$1:$A$6) per il valore che voglio trovare nelle righe corrispondenti /$A$1:$A$6="a"

Siccome devo dividere tutta la funzione metto le parentesi (in rosso) alla funzione RIF.RIGA e all'altra che verifica la corrispondenza

(RIF.RIGA($A$1:$A$6))/($A$1:$A$6="a")

Questa è la parte essenziale di tutta la funzione

Se uso solo la funzione $A$1:$A$6="a" e la trascino in basso vedo che quando trova la "a" nella colonna A mi restituisce VERO altrimenti FALSO



Secondo l'espressione booleana VERO corrisponde a 1 e FALSO corrisponde a 0 lo possiamo vedere mettere due trattini davanti alla funzione (in termini appropriati il doppio trattino si chiama "operatore doppio unario" , così

=--($A$1:$A$6="a")

A questo punto vedi esattamente che quando trova il VERO è 1 e quando trova il FALSO 0



Pertanto quando trova 1 (VERO) restituisce il valore dell'INDICE mentre quando trova FALSO 0 la sua divisone da errore ....

e quando da errore poiché il comportamento scelto è stato "Ignora errore", 6

=INDICE($B$1:$B$6;AGGREGA(14;6;

allora ignorerà questo dato saltandolo e quindi mettendolo alla fine

L'ultima parte della formula (il k) serve a fare scorrere le righe 1,2,3,4,5,6 per poterle controllare si può usare

RIGHE($1:1)

che trascinandolo verso il basso da appunto 1,2,3,4,5,6

ed ecco che la formula è fatta

=INDICE($B$1:$B$6;AGGREGA(15;6;(RIF.RIGA($A$1:$A$6))/($A$1:$A$6="a");RIGHE($1:1)))

Nel caso voglio non visualizzare l'errore si può anteporre la funzione SE.ERRORE che nasconde l'errore

=SE.ERRORE(INDICE($B$1:$B$6;AGGREGA(15;6;(RIF.RIGA($A$1:$A$6))/($A$1:$A$6="a");RIGHE($1:1)));"")


Allego file da scaricare per provare--> FILE DA SCARICARE
 
  • Like
Reactions: Andrea Guerri

Marius44

VBA Expert
Moderatore
Expert
9 Settembre 2015
5.396
63
75
Catania
Excel2010
116
Ciao Ges
sono finiti i like ma voglio esprimerti il mio plauso per l'ottima spiegazione.

Sei sempre il FirstChampion

Ciao,
Mario
 
Stato
Chiusa ad ulteriori risposte.

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!