a cura di Roberto Mensa e Paolo Ardizzoni
- Formule matriciali (utilizzate per restituire i risultati attesi senza l'uso di colonne di appoggio)
Formattazione e convalida sono utilizzate per restituire un risultato anche visivamente articolato ma sempre aggiornato in modo automatico al variare dei parametri che definiscono le dimensioni degli insiemi.
La convalida con cerchi rossi viene restituita sui dati presenti al momento della visualizzazione dei cerchi ma non si aggiorna a seguito di un eventuale ricalcolo (per cerchiare i valori massimi dei sottogruppi ...
versione 97/2003: menu Strumenti>Verifica formule>Mostra barra degli strumenti Verifica formule ... Cerchia dati non validi;
versione 2007/2010: barra Dati>Strumenti Dati>Convalida Dati..... Cerchia dati non validi) .
Il quesito
Dato un intervallo di celle contenenti valori numerici, si vuole ottenere la somma dei massimi valori di tutti i sottoinsiemi in esso contenuti.
La definizione dei sottoinsiemi verrà stabilita dal numero di celle che li costituiscono nel caso l'intervallo sia composto da una sola riga o colonna e dalla loro altezza e larghezza nel caso di un intervallo di partenza rettangolare(tabella). In ogni caso saranno valori variabili. Il punto di partenza per la suddivisione degli intervalli è la prima cella in alto e a sinistra.
Un esempio chiarirà e servirà per spiegare le logiche che vengono usate dalle formule.
Consideriamo i dati nellintervallo A1:A9 e definiamo la dimensione del sottoinsieme uguale a 3.
I sottoinsiemi corrispondono agli intervalli:
A1:A3
A4:A6
A7:A9
Si vedrà poi il caso in cui i dati sono disposti su una singola riga ad esempio nell'intervallo A2:I2 e analogamente con dimensione del sottoinsieme uguale a 3 la somma dei massimi valori avverrà sui gruppi:
A2:C2
D2:F2
G2:I2
In ultimo il caso dei dati disposti in una tabella ad esempio B2:G7 in cui i gruppi sono stabiliti definendo la loro altezza e larghezza ... ad esempio altezza 3 larghezza 3 ottenendo i quattro gruppi:
B2:D4
E2:G4
B5:D7
E5:G7
Calcolo con una colonna/riga di appoggio
Analizziamo il primo caso, quello coi dati su una colonna. Usufruendo di una colonna di appoggio è sufficiente valutare il massimo valore di un intervallo dimensionato correttamente e di volta in volta spostato. Le formule trascinate su tre righe potrebbero essere:
=MAX(SCARTO(rng;INT((RIF.RIGA(A1)-1)*rw);;rw))
=MAX(SCARTO(rng;INT((RIF.RIGA(A2)-1)*rw);;rw))
=MAX(SCARTO(rng;INT((RIF.RIGA(A3)-1)*rw);;rw))
rng è un nome definito che restituisce l'intervallo dati (A1:A9)
rw è il numero di celle di ogni sottogruppo (3 celle nel nostro esempio)
questa la parte più significativa della formula:
INT((RIF.RIGA(A1)-1)*rw)
RIF.RIGA(A1) restituisce il numero di riga (1) della cella A1 (e 2,3 per A2, A3 nelle celle dove è trascinata)
si sottrae 1 e si moltiplica per 3 tenendo poi solo la parte intera del risultato tutto questo per ottenere 0,3 e 6 che saranno gli scostamenti usati per spostare il range in basso.
Tutto il resto viene fatto dalla funzione SCARTO. Il range A1:A9 viene spostato in basso di 0 (A1:A9), 3 (A4:A12), 6 (A7:A15) per essere ridimensionato ad una altezza di 3 (A1:A3 A4:A6 A7:A9). Su tali range vengono calcolati i massimi valori.
Le celle della colonna di appoggio verranno sommate per ottenere il risultato atteso.
Calcolo senza appoggio
La faccenda si complica se vogliamo evitare l'uso della colonna di appoggio.
SCARTO, pur consentendo (con particolari accorgimenti) una matrice come argomento riga o colonna, non permette il ridimensionamento dell'intervallo.
Occorre quindi cambiare completamente strategia.
Usando ancora i dati dell'esempio il procedimento che verrà replicato dalla formula è questo:
- si divide ogni valore dell'intervallo per un numero progressivamente più grande ma uguale per ogni scaglione
- si recuperano i valori più grandi della matrice così ottenuta saltando quelli che non interessano
- si riconvertono i valori risultato dividendoli per un numero progressivamente più piccolo che rispecchia l'ordine di grandezza dell'iniziale moltiplicatore.
vediamo il procedimento sui dati dell'esempio ...
primo passaggio:
1/10^0 =1
2/10^0 =2
3/10^0 =3
40/10^2 =0,04
5/10^2 =0,005
6/10^2 =0,006
7/10^4 =0.000007
8/10^4 =0,000008
9/10^4 =0,000009
ottenendo la matrice {1\2\3\0,04\0,005\0,006\0,000007\0,000008\0,000009}
10^0, 10^2, 10^4 utilizza un moltiplicatore calcolato come lunghezza massima del valore intero più grande dell'intero intervallo. Questo è l'ordine di grandezza necessario a che tutte le celle di ogni scaglione risultino dopo il calcolo di una grandezza relativa ponderatamente distinta.
secondo passaggio:
trovare i valori più grandi saltando quelli che non interessano (ovvero il secondo e il terzo per il primo sottogruppo, il quinto e il sesto per il secondo, l'ottavo e il nono per l'ultimo sottogruppo), quindi:
GRANDE(matrice;{1\4\7})
ottenendo la matrice:
{3\0,4\0,0009}
i 3 valori sono i valori massimi da riconvertire:
3/0,1^0 =3
0,04/0,1^2 =40
0,000009/0,1^4 =9
si esegue in ultimo la somma di {3\40\9}.
Questa logica viene usata anche nel caso dei dati su una riga e su quelli in una tabella (seppur in quest'ultimo caso siano resi più complessi soprattutto nell'identificazione degli scaglioni).
Le Formule Matriciali
Di seguito riporto le formule matriciali utilizzate nei tre casi:
Dati in una colonna
=SOMMA(GRANDE(rng/10^(INT((RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)))-1)/rw)*LUNGHEZZA(INT(MAX(rng))));
(RIF.RIGA(INDIRETTO("1:"&INT((RIGHE(rng)-1)/rw)+1))-1)*rw+1)/0,1^((RIF.RIGA(INDIRETTO("1:"&INT((
RIGHE(rng)-1)/rw+1)))-1)*LUNGHEZZA(INT(MAX(rng)))))
=SUM(LARGE(rng/10^(INT((ROW(INDIRECT("1:"&ROWS(rng)))-1)/rw)*LEN(INT(MAX(rng)))),(ROW(INDIRECT( "1:"&INT((ROWS(rng)-1)/rw)+1))-1)*rw+1)/0.1^((ROW(INDIRECT("1:"&INT((ROWS(rng)-1)/rw+1)))-1)* LEN(INT(MAX(rng)))))
Dati in una riga
=SOMMA(GRANDE(MATR.TRASPOSTA(rng_o)/10^(INT((RIF.RIGA(INDIRETTO("1:"&COLONNE(rng_o)))-1)/cl)* LUNGHEZZA(INT(MAX(rng_o))));(RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl)+1))-1)*cl+1)/ 0,1^((RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl+1)))-1)*LUNGHEZZA(INT(MAX(rng_o)))))
=SUM(LARGE(TRANSPOSE(rng_o)/10^(INT((ROW(INDIRECT("1:"&COLUMNS(rng_o)))-1)/cl)*LEN(INT(MAX(rng_o)))), (ROW(INDIRECT("1:"&INT((COLUMNS(rng_o)-1)/cl)+1))-1)*cl+1)/0.1^((ROW(INDIRECT("1:"&INT( (COLUMNS(rng_o)-1)/cl+1)))-1)*LEN(INT(MAX(rng_o)))))
Dati in una tabella
=SOMMA(GRANDE(rng_r/10^((INT((RIF.RIGA(INDIRETTO("1:"&RIGHE(rng_r)))-1)/y)*LUNGHEZZA(INT( MAX(rng_r)))*COLONNE(rng_r)/x)+(INT((RIF.COLONNA(INDIRETTO("R1C1:R1C"&COLONNE(rng_r);0))-1)/x)* LUNGHEZZA(INT(MAX(rng_r)))));1+(RIF.RIGA(INDIRETTO("1:"&CONTA.VALORI(rng_r)/x/y))-1)*x*y)/ 0,1^((RIF.RIGA(INDIRETTO("1:"&CONTA.VALORI(rng_r)/x/y))-1)*LUNGHEZZA(INT(MAX(rng_r)))))
=SUM(LARGE(rng_r/10^((INT((ROW(INDIRECT("1:"&ROWS(rng_r)))-1)/y)*LEN(INT(MAX(rng_r)))* COLUMNS(rng_r)/x)+(INT((COLUMN(INDIRECT("R1C1:R1C"&COLUMNS(rng_r),0))-1)/x)*LEN(INT( MAX(rng_r))))),1+(ROW(INDIRECT("1:"&COUNTA(rng_r)/x/y))-1)*x*y)/0.1^((ROW(INDIRECT("1:"& COUNTA(rng_r)/x/y))-1)*LEN(INT(MAX(rng_r)))))
Formattazione condizionale e Convalida
=VAL.NUMERO(B2)*RESTO(INT((RIF.RIGA(B1)-1)/y);2)*(RESTO(INT((RIF.COLONNA(A2)-1)/x);2)-1)
=VAL.NUMERO(B2)*(RESTO(INT((RIF.RIGA(B1)-1)/y);2)-1)*(RESTO(INT((RIF.COLONNA(A2)-1)/x);2))
=B2<>MAX(SCARTO(rng_r;INT(INT((RIF.RIGA(A1)-1)/y)*y);INT(INT((RIF.COLONNA(A1)-1)/x)*x);y;x))
Conclusioni
Per le formule usate nella colonna di appoggio è sufficiente sostituire MAX con MIN. In quelle autonome è necessario modificare la matrice k usata come secondo argomento della funzione GRANDE. Anche se meno immediata la modifica da apportare è altrettanto semplice.
Riporto di seguito la formula che sarebbe necessario utilizzare per i tre casi trattati:
=SOMMA(GRANDE(rng/10^(INT((RIF.RIGA(INDIRETTO("1:"&RIGHE(rng)))-1)/rw)* LUNGHEZZA(INT(MAX(rng))));(RIF.RIGA(INDIRETTO("1:"&
IN
T
((RIGHE(rng)-1)/rw)+1))-1)*rw+rw)/ 0,1^((RIF.RIGA(INDIRETTO("1:"&INT((RIGHE(rng)-1)/rw+1)))-1)*LUNGHEZZA(INT(MAX(rng)))))
=SOMMA(GRANDE(MATR.TRASPOSTA(rng_o)/10^(INT((RIF.RIGA(INDIRETTO("1:"&COLONNE(rng_o)))-1)/cl)* LUNGHEZZA(INT(MAX(rng_o))));(
RI
F.RIGA
(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl)+1))-1)*cl+cl)/ 0,1^((RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl+1)))-
1)*LUNGHEZZA(INT(MAX(rng_o)))))
=SOMMA(GRANDE(rng_r/10^((INT((RIF.RIGA(INDIRETTO("1:"&RIGHE(rng_r)))-1)/y)*lMax* COLONNE(rng_r)/x)+(INT((RIF.COLONNA(INDIRETT
O("R1C1:R1C"&COLONNE(rng_r);0))-1)/x)*lMax)); x*y+(RIF.RIGA(INDIRETTO("1:"&nAree))-1)*x*y)/0,1^((RIF.RIGA(INDIRETTO("1:"&
nAre
e
))-1)*lMax))
Le formule della colonna di appoggio andrebbero drasticamente modificate, mentre alle matriciali è sufficiente un
=SOMMA(GRANDE(MATR.TRASPOSTA(rng_o)/10^(INT((RIF.RIGA(INDIRETTO("1:"&COLONNE(rng_o)))-1)/cl)* LUNGHEZZA(INT(MAX(rng_o))));(
RI
F.RIGA
(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl)+1))-1)* cl+{1;2})/0,1^((RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl+1)
))-1)* LUNGHEZZA(INT(MAX(rng_o)))))
Sistema operativo: Windows
Applicazioni: Excel 97/2003, Excel 2007/2010