Somma dei valori massimi/minimi di sottoinsiemi

a cura di Roberto Mensa e Paolo Ardizzoni

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. 

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

=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+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))));(RIF.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(INDIRETTO("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:"&nAree))-1)*lMax))

=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;2})/0,1^((RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl+1)))-1)*
LUNGHEZZA(INT(MAX(rng_o)))))
 

Allegati:

File	Descrizione	Dimensione del File
Sum_max_offset_range_all.xls		64 Kb