Elenchi: estrazione e compattazione - Prima parte PDF Stampa E-mail
Scritto da Paolo Ardizzoni   
Giovedì 30 Luglio 2009 08:39
a cura di:  Giovanna Arcadu e Paolo Ardizzoni
ha collaborato: Calogero Cosentino 
Estrazione e compattazione di dati da elenchi – prima parte

Problema: estrarre dati da elenchi compattandoli:
1. in un elenco univoco non ordinato;
2. in un elenco univoco ordinato;
3. in un elenco i soli dati ripetuti non ordinati;
4. in un elenco i soli dati ripetuti ordinati;
5. in un elenco univoco da un elenco con dati ripetuti e intervallati da zeri e/o celle vuote.

 

 

Vediamo caso per caso le soluzioni.

 

1. Estrarre e compattare in un elenco univoco non ordinato

Ipotizziamo un elenco di nomi alcuni dei quali ripetuti, un elenco contenente quindi delle stringhe di testo

in [B2] è immessa la seguente formula matriciale (da inserire con CTRL+MAIUSC+INVIO)
=INDICE($A$2:$A$10;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA($A$2:$A$10)-1;CONFRONTA($A$2:$A$10;$A$2:$A$10;0);0));CONFRONTA($A$2:$A$10;$A$2:$A$10;0);""); RIF.RIGA(A1)))
poi copiata lungo la colonna.

Se l'elenco è costituito da valori numerici:

in cella [B2] la stessa formula matriciale:

=INDICE($A$2:$A$10;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA($A$2:$A$10)-1;CONFRONTA($A$2:$A$10;$A$2:$A$10;0);0));CONFRONTA($A$2:$A$10;$A$2:$A$10;0);""); RIF.RIGA(A1)))

poi copiata lungo la colonna.

In entrambi i casi notiamo che è restituito il valore di errore #NUM! poiché la formula, nella lettura dell'elenco, non trova più corrispondenze.

In Excel 2007 l'errore potrebbe essere gestito agevolmente integrando la formula con la funzione: =SE(VAL.ERRORE(espressione; risultato_se_vero; espressione) dove espressione è l'intera formula riportata più sopra. Se espressione genera un errore di qualsiasi tipo, la formula restituisce risultato_se_vero, che potrebbe essere una stringa nulla oppure un messaggio testuale, altrimenti restituisce il risultato di espressione.

Per le precedenti versioni dell'applicativo, l'integrazione per gestire l'errore, nella forma =SE(VAL.ERRORE(espressione);"";espressione) appesantirebbe tuttavia la formula. Per nascondere l’evidenza dell’errore si suggerisce perciò l’utilizzo della Formattazione Condizionale scegliendo un formato carattere bianco.
Nell'esempio, selezionare l'intero intervallo dei risultati [B2:B10], quindi "Formattazione Condizionale -->nuova regola-->utilizza una formula-->inserisci la seguente formula =VAL.ERRORE(B2:B10) --> formato carattere colore bianco”.
 
2. Estrarre e compattare in un elenco univoco ordinato

In questi casi è consigliabile, al fine di rendere la formula più leggibile, definire un nome per l'elenco dati.
N.B. il nome deve essere attribuito all'intero intervallo contenente i dati, se si dovesse eccedere la formula restituirebbe un errore.

Nell'esempio dell'elenco contenente stringhe di testo:

Per l'intervallo [A2:A10] è stato definito il nome "Elenco",

nella colonna risultato, col [C], è stato selezionato un intervallo abbastanza ampio da contenere il risultato delle formula, quindi, nella barra delle formule, immessa la seguente formula matriciale:

=INDICE(Elenco;CONFRONTA(PICCOLO(SE(CONFRONTA(Elenco;Elenco;)=RIF.RIGA(Elenco)-1;1)*CONTA.SE(Elenco;"<="&Elenco);RIF.RIGA(Elenco)-1+RIGHE(Elenco)-SOMMA(1/CONTA.SE(Elenco;Elenco)));CONTA.SE(Elenco;"<="&Elenco);))

 Nel caso di valori numerici la formula potrebbe essere la medesima;

qui riportiamo un esempio in cui si utilizza una formula semplificata:

Per l'intervallo [A2:A10] è stato definito il nome "Elenco2",

nella colonna risultato, col [C], selezionato un intervallo abbastanza ampio da contenere il risultato delle formula, nella barra delle formule è stata immessa la seguente formula matriciale:
=PICCOLO(Elenco2;PICCOLO(SE(RIF.RIGA(Elenco2)+1-MIN(RIF.RIGA(Elenco2))=CONFRONTA (Elenco2;Elenco2;);RANGO(Elenco2;Elenco2;1));RIF.RIGA(Elenco2)+1-MIN(RIF.RIGA(Elenco2))))

Per quanto concerne il valore di errore #NUM! valgono le considerazioni suddette.


3. Estrarre e compattare in un elenco i soli dati ripetuti non ordinati
Anche in questo caso usiamo i nomi definiti per gli elenchi-dati.

Dati testo

La formula matriciale, immessa in cella [D2] e copiata in basso:
=INDICE(Elenco;PICCOLO(SE(FREQUENZA(CONFRONTA(Elenco;Elenco;0);CONFRONTA(Elenco;Elenco;0))>1;RIF.RIGA(Elenco)-1;"");RIF.RIGA(A1)))
Dati numerici:

La formula, immessa in [D2], è la stessa utilizzata nel caso dei dati-testo:

Naturalmente cambia il nome definito:

=INDICE(Elenco2;PICCOLO(SE(FREQUENZA(CONFRONTA(Elenco2;Elenco2;0);CONFRONTA(Elenco2;Elenco2;0))>1;RIF.RIGA(Elenco2)-1;"");RIF.RIGA(A1)))

Sempre come formula matriciale.

 

4. Estrarre e compattare in un elenco i soli dati ripetuti ordinati
Assegnare i nomi agli elenchi come negli esempi precedenti.

Dati testo


In questo caso occorre selezionare un intervallo adeguato nella colonna-risultato, come visto nell'esempio n°2, quindi nella barra della formula è stata immessa la formula matriciale:
=INDICE(Elenco;CONFRONTA(PICCOLO(SE(FREQUENZA(CONFRONTA(Elenco;Elenco;0);CONFRONTA(Elenco;Elenco;0))>1;CONTA.SE(Elenco;"<="&Elenco));RIF.RIGA(Elenco)-1); CONTA.SE(Elenco;"<="&Elenco);))

confermata con Ctrl+Maiusc+Invio.

Dati numerici

In [E2] la formula matriciale:
=PICCOLO(SE(FREQUENZA(Elenco2;Elenco2)>1;Elenco2;"");RIF.RIGA(A1))
copiata lungo la colonna.

5. Estrarre e compattare in un elenco univoco da un elenco con dati ripetuti e intervallati da zeri e/o celle vuote

Dati testo


Il nome definito è "Nomi" riferito all'intervallo [H2:H10].
In [I2] immessa la seguente formula matriciale:
=INDICE(Nomi;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA(Nomi)-1; SE(Nomi=0;"";CONFRONTA(Nomi;Nomi;0));0));CONFRONTA(Nomi;Nomi;0);"");RIF.RIGA(A1)))
copiata lungo la colonna.

Dati numerici

Il nome definito è "Dati", riferito all'intervallo [H2:H10]

 la formula è la medesima, immessa in [I2] e copiata lungo la colonna:
=INDICE(Dati;PICCOLO(SE(VAL.NUMERO(CONFRONTA(RIF.RIGA(Dati)-1;SE(Dati=0;"";CONFRONTA(Dati;Dati;0));0));CONFRONTA(Dati;Dati;0);"");RIF.RIGA(A1)))


Nota: Le soluzioni sono il frutto di una ricerca nello storico del NG di Excel, grazie dunque al contributo dei più esperti frequentatori.

Sistema operativo: win32

Applicazioni: tutte le versioni di Excel 

 

 

Allegati:
FileDescrizioneDimensione del File
Scarica questo file (Estrai e compatta_1def.xls)Estrai e compatta_1def.xls 35 Kb
Ultimo aggiornamento Lunedì 24 Agosto 2009 14:26