Assegnare un Nome ad una Matrice Dinamica PDF Stampa E-mail
Scritto da Paolo Ardizzoni   
Domenica 06 Gennaio 2013 10:53
a cura di Paolo Ardizzoni

Assegnare un Nome ad una Matrice Dinamica

In passato abbiamo già affrontato l'argomento degli intervalli dinamici, però riferiti ad un solo vettore (un elenco di nomi o valori in colonna), nell'articolo: come Assegnare un Nome ad un intervallo (vettore) dinamico.

Ora vediamo come Assegnare un Nome ad una Matrice Dinamica, vale a dire ad una matrice che può essere aggiornata con l'aggiunta di nuovi record e/o nuovi campi senza necessità di modificare gli estremi degli intervalli contenuti nelle formule, nonché come utilizzare il Nome all'interno di formule.

Partiamo con un esempio una matrice di questo tipo:

Vogliamo ottenere la Somma di tutti i record che hanno come Tipo: Entrata e come Nome: Pippo.

Per fare questo conviene prima parametrizzare la ricerca, ovvero memorizzare in celle gli elementi oggetto della ricerca e richiamare le coordinate di queste all'interno della formula, così digitiamo il tipo "Entrata" in D2 e il nome "Pippo" in E2:

Senza utilizzare i "Nomi" la formula da applicare potrebbe essere questa:

=SOMMA.PIÙ.SE(C2:C11;A2:A11;D2;B2:B11;E2) funzione introdotta dalla versione 2007 di Excel;

oppure questa:

=SOMMA((A2:A11=D2)*(B2:B11=E2)*C2:C11) matriciale (da confermare con Ctrl+Maiusc+Invio) valida anche per versioni precedenti la 2007;

infine, per non escludere la possibilità di aggiungere nuove righe, questa:

=SOMMA(SE.ERRORE((A:A=$D$2)*(B:B=$E$2)*$C:$C;0)) matriciale. Nella quale gli intervalli vengono generalmente impostati per tutta la colonna di riferimento. Ciò però comporta che la prima riga dei titoli, che viene compresa nel calcolo, restituisce un errore che deve essere gestito con SE.ERRORE(), anche questa funzione introdotta dalla versione 2007.

Per consentire l'aggiunta di nuove righe senza necessità di modificare gli estremi degli intervalli nelle formule, assegniamo un nome alla matrice in modo da renderla dinamica. Così assegniamo il nome rng riferito a:

=SCARTO(Foglio1!$A$1;1;;CONTA.SE(Foglio1!$A:$A;"<>")-1;3)

come da figura:

 La funzione SCARTO(), così come esplicitata, crea una matrice a partire dalla cella A1 che ha per altezza il numero di righe della tabella valorizzata con i dati, infatti conta con CONTA.SE() il numero di elementi in colonna con esclusione del titolo, e per larghezza 3 colonne, quante sono le colonne della tabella. Come segue

{"Entrata"\"Pippo"\12.
"Uscita"\"Pluto"\34.
................
................
"Uscita"\"Pippo"\78.
"Uscita"\"Minni"\90}

Nel momento in cui aggiungiamo nuove righe/record, per esempio le due righe 12 e 13:

la matrice con il nome rng viene aggiornata automaticamente come segue:

{"Entrata"\"Pippo"\12.
"Uscita"\"Pluto"\34.
....................
....................
"Uscita"\"Pippo"\78.
"Uscita"\"Minni"\90.
"Entrata"\"Gastone"\10.
"Entrata"\"Gastone"\20}

L'utilizzo del Nome rng all'interno della funzione SOMMA.PIÙ.SE() deve avvenire all'interno della funzione INDICE() al fine di definire la colonna di riferimento dell'argomento. Quindi l'originaria formula diventa:

=SOMMA.PIÙ.SE(INDICE(rng;;3);INDICE(rng;;1);D2;INDICE(rng;;2);$E$2)

Come si può notare, all'interno della funzione INDICE(), come primo argomento viene richiamato il Nome rng della matrice e come terzo argomento il numero di colonna della stessa.

Più in generale, in presenza di una matrice la cui prima riga è costituita dai titoli di colonna, il riferimento alla funzione SCARTO() del nome rng va definito con una funzione del tipo:

=SCARTO(Foglio1!$A$1;1;;CONTA.SE(Foglio1!$A:$A;"<>")-1;CONTA.SE(Foglio1!$1:$1;"<>"))

Ovviamente, se nella prima o seconda riga della matrice vi fossero altri valori oltre ai valori della matrice, nell'esempio di partenza per es. i dati da parametrizzare, per definire la larghezza della matrice dinamica occorrerebbe fare riferimento ad una riga che contenesse i soli valori della matrice, per esempio la terza riga:

=SCARTO(Foglio1!$A$1;1;;CONTA.SE(Foglio1!$A:$A;"<>")-1;CONTA.SE(Foglio1!$3:$3;"<>"))

Infine, in presenza di una versione datata di Excel che non dovesse supportare l'indicazione di intervalli non delimitati, la formula potrebbe essere così modificata:

=SCARTO(Foglio1!$A$1;1;;CONTA.SE(Foglio1!$A$1:$A$10000;"<>")-1;CONTA.SE(Foglio1!$A$3:$BZ$3;"<>"))

nella quale ultima gli intervalli sarebbero delimitati ma con margine ampio per contenere abbondantemente i dati della matrice/tabella. In presenza di un numero di righe maggiore sarebbe sufficiente aumentare il valore estremo dell'intervallo.


PS: L'articolo è stato realizzato anche con la collaborazione dell'amico Roberto Mensa.

Allegati:
FileDescrizioneDimensione del File
Scarica questo file (Matrice dinamica.xlsx)Matrice dinamica.xlsx 10 Kb
Ultimo aggiornamento Martedì 30 Dicembre 2014 09:23