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

Stato
Chiusa ad ulteriori risposte.

ges

Excel/VBA Expert
Amministratore
21 Giugno 2015
27.694
1.865
Como
2011MAC 365WIN
774
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:




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:



Nel mio caso poiché voglio ordinare i valori trovati uno di seguito all'atro scelgo "Ignora 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
 

Marius44

VBA Expert
Moderatore
9 Settembre 2015
6.850
115
76
Catania
Excel2010
274
Ciao Ges
sono finiti i like ma voglio esprimerti il mio plauso per l'ottima spiegazione.

Sei sempre il FirstChampion

Ciao,
Mario
 
  • Like
Reactions: Xander
Stato
Chiusa ad ulteriori risposte.

Sostieni ForumExcel

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