http://www.riolab.org/index.php?option=com_content&view=article&id=203&Itemid=68
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