Somma dei valori massimi/minimi di sottoinsiemi PDF Stampa E-mail
Scritto da Roberto Mensa   
Sabato 04 Dicembre 2010 09:15
a cura di Roberto Mensa e Paolo Ardizzoni
Propongo questo articolo come esempio per utilizzare in modo *spinto* varie funzionalità di Excel:
- Formule matriciali (utilizzate per restituire i risultati attesi senza l'uso di colonne di appoggio)
- Formattazione condizionale (per restituire visivamente la suddivisione dei sottogruppi)
- Convalida (per evidenziare con un cercho i valori massimi dei sottogruppi).
 

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. 

 
N.B.: In funzione dei dati vengono utilizzate formule che restituiscono numeri casuali e che si aggiornano ad ogni ricalcolo.
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) .

 

Sommario

  1. Il quesito
  2. Calcolo con una colonna/riga di appoggio
  3. Calcolo senza appoggio
  4. Le Formule Matriciali
  5. Formattazione condizionale e Convalida

***********-***********

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

 

lo scopo è quello di sommare i massimi valori di ogni gruppo 3+40+9 ... il risultato atteso è quindi 52.

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

Riporto le formule utilizzate nella formattazione condizionale e nella convalida nel caso dei dati disposti in tabella.
La definizione degli intervalli e il calcolo in questo caso avviene su un foglio diverso in alternativa si sarebbe potuto incorrere in riferimenti circolari. Qui a uso di legenda è presente l'elenco degli indirizzi e visualizzata la forma dei sottoinsiemi.
 
Nel foglio coi dati della tabella i sottoinsiemi si auto dimensionano e la convalida permette d'evidenziare i valori massimi.

Prima di impostare la formattazione condizionale ho colorato lo sfondo delle celle di arancione.
Ecco le formule per la Formattazione Condizionale selezionando l'intervallo a partire da B2:
 


=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))
In entrambe le condizioni viene impostato lo sfondo azzurro alle celle.
 
La convalida invece usa questa formula:
 


=B2<>MAX(SCARTO(rng_r;INT(INT((RIF.RIGA(A1)-1)/y)*y);INT(INT((RIF.COLONNA(A1)-1)/x)*x);y;x))
 
Qui sotto la visualizzazione nel caso dei dati in colonna. In questo caso e nel caso dei dati su una riga le formule sono più semplici e potete vederle aprendo il file allegato al fondo di questa pagina.

 

Conclusioni

Analogamente al calcolo della somma dei massimi valori è facile calcolare la somma dei minimi.
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:
Dati disposti su 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+rw)/
0,1^((RIF.RIGA(INDIRETTO("1:"&INT((RIGHE(rng)-1)/rw+1)))-1)*LUNGHEZZA(INT(MAX(rng)))))
 
Dati disposti su 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+cl)/
0,1^((RIF.RIGA(INDIRETTO("1:"&INT((COLONNE(rng_o)-1)/cl+1)))-1)*LUNGHEZZA(INT(MAX(rng_o)))))
 
Dati disposti in una tabella
 


=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))
 
E se volessimo la somma dei 2 maggiori valori di ogni sottogruppo?
Le formule della colonna di appoggio andrebbero drasticamente modificate, mentre alle matriciali è sufficiente un
piccolo ritocco. Basta aggiungere una colonna alla matrice usata come secondo argomento di GRANDE. Vediamo il solo caso dei dati disposti su una riga (in quanto la modifica è identica per tutte e tre le formule):
 


=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)))))
... 1 è stato sostituito con {1;2} così da trasformare la vecchia {1\4\7} (guarda l'esempio) in {1;2\4;5\7;8}.
 
Un'ultima considerazione riguarda la scelta su quale formula usare ... Personalmente opterei per usare una colonna di appoggio. Le formule matriciali in questo, come in altri casi risultano più difficili da adattare perchè la logica con cui lavorano è  più complicata, inoltre spesso comportano un uso maggiore della memoria e delle risorse. Io le adoro come gioco e come sfida, ma difficilmente le uso nel lavoro quotidiano.

 

 

Nota: questo articolo è una copia autorizzata dall'autore Roberto Mensa. L'originale (eventualmente più aggiornato) si trova all'indirizzo: https://sites.google.com/site/e90e50/user/somma-max-sottogruppi

 

 

Sistema operativo: Windows
Applicazioni: Excel 97/2003, Excel 2007/2010
Allegati:
FileDescrizioneDimensione del File
Scarica questo file (Sum_max_offset_range_all.xls)Sum_max_offset_range_all.xls 64 Kb
Ultimo aggiornamento Lunedì 06 Dicembre 2010 16:30