Convalida successiva univoca

posted Mar 15, 2011, 8:17 AM by r   [ updated Mar 15, 2011, 4:16 PM ]
 
Ho ripreso un ottimo lavoro di Nur del novembre 2009 su problema posto da Tiziano Marmiroli.
E' un caso in qui la soluzione è tanto bella quanto il problema posto. Per questo motivo e per nostalgia dei due protagonisti assenti da molto dall'NG di Excel, mi sono deciso ad approfondirlo in questa pagina.

Io, mi sono limitato ad ampliare le casistiche e cercare soluzioni alternative.
Vengono sfruttate come base di partenza le formule per la restituzione di un elenco univoco ordinato.
 
 

Descrizione del problema

Dato un elenco iniziale (DATI) si vuole in un altro range peter scegliere, usando la convalida da elenco, le voci dell'elenco rese però univoche (se necessario) e ordinate.
Inoltre si chiede che dalla convalida vengano successivamente escluse le voci già selezionate.
In sostanza si potrà dato un elenco iniziale scegliere gli elementi in modo successivo senza avere la possibilità di scegliere elementi doppi.
La serie di immagini di seguito aiuterà a capire le intenzioni.
 
Inizialmente nell'elenco della convalida sono presenti tutte le voci di DATI ordinate e univoche
 
La voce appena selezionata non compare più nelle successive convalide
 
 
 
Si continuerà con la possibilità di selezionare da un elenco sempre più ristretto
 
 

Casi trattati e specifiche

Le casistiche dipendono dal tipo di elenco DATI iniziale che potrà essere:
  • con doppioni testo e numeri
  • univoco testo e numeri
  • univoco solo testo
  • con doppioni solo testo
  • univoco solo numeri
  • con doppioni solo numeri
Nel file (Excel 2007) che trovate in fondo a questa pagina, sono utilizzate delle tabelle per definire gli intervalli. Nelle formule i nomi possono essere sostituiti anche con intervalli.
Negli elenchi DATI (dati_n) non devono essere presenti celle vuote. Le formule non distinguono tra maiuscolo/minuscolo.
La convalida usata è una convalida a elenco con l'origine che punta a un nome definito (unico_n) nel seguente modo:
=SCARTO(appoggio_1;;;RIGHE(dati_1)-SOMMA(--VAL.ERRORE(appoggio_1)))
Dove appoggio_n è la tabella in cui sono presenti le formule.

Riporterò le formule (tutte matriciali da confermare con Ctrl+Maiusc+Invio) da usare nel range di appoggio. La convalida a elenco infatti necessita di un intervallo dati sul foglio. Il range dove sono presenti le celle con convalida è definito dalla tabella convalida_n.
Nell'immagine sotto vengono riportati i nomi delle tabelle:

DATI è un elenco univoco di testi e/o numeri

Soluzione 1 (da confermare con Ctrl+Maiusc+Invio)
=INDICE(dati_1;RESTO(PICCOLO(SE(CONTA.SE(convalida_1;dati_1);"";CONTA.SE(dati_1;"<"&dati_1)+10^4*VAL.TESTO(dati_1)+CONFRONTA(dati_1;dati_1;)/10^4);RIF.RIGA(A1))*10^4;10^4))

=INDEX(dati_1,MOD(SMALL(IF(COUNTIF(convalida_1,dati_1),"",COUNTIF(dati_1,"<"&dati_1)+10^4*ISTEXT(dati_1)+MATCH(dati_1,dati_1,)/10^4),ROW(A1))*10^4,10^4))


DATI è un elenco di testi e/o numeri che può contenere doppioni

Soluzione 2 di Nur (da confermare con Ctrl+Maiusc+Invio)
=INDICE(dati_0;CONFRONTA(PICCOLO(SE((CONFRONTA(dati_0;dati_0;)=RIF.RIGA(INDIRETTO("1:"&RIGHE(dati_0))))*(CONTA.SE(convalida_0;dati_0)=0);CONTA.SE(dati_0;"<"&dati_0)+RIGHE(dati_0)*VAL.TESTO(dati_0));RIF.RIGA(A1));CONTA.SE(dati_0;"<"&dati_0)+RIGHE(dati_0)*VAL.TESTO(dati_0);))

=INDEX(dati_0,MATCH(SMALL(IF((MATCH(dati_0,dati_0,)=ROW(INDIRECT("1:"&ROWS(dati_0))))*(COUNTIF(convalida_0,dati_0)=0),COUNTIF(dati_0,"<"&dati_0)+ROWS(dati_0)*ISTEXT(dati_0)),ROW(A1)),COUNTIF(dati_0,"<"&dati_0)+ROWS(dati_0)*ISTEXT(dati_0),))
 
Soluzione 3 (da confermare con Ctrl+Maiusc+Invio)
=INDICE(dati_2;RESTO(PICCOLO(SE(FREQUENZA(SE(CONTA.SE(convalida_2;dati_2);"";CONFRONTA(dati_2;dati_2;));CONFRONTA(dati_2;dati_2;));CONTA.SE(dati_2;"<"&dati_2)+10^4*VAL.TESTO(dati_2)+CONFRONTA(dati_2;dati_2;)/10^4);RIF.RIGA(A1))*10^4;10^4))

=INDEX(dati_2,MOD(SMALL(IF(FREQUENCY(IF(COUNTIF(convalida_2,dati_2),"",MATCH
(dati_2,dati_2,)),MATCH(dati_2,dati_2,)),COUNTIF(dati_2,"<"&dati_2)+10^4*ISTEXT(dati_2)+MATCH(dati_2,dati_2,)/10^4),ROW(A1))*10^4,10^4))

Una variante interessante, restituisce come elenco della convalida un elenco univoco ordinato ma in modo tale che l'utente possa scegliere gli elementi doppi tante volte quanti sono presenti nell'elenco DATI. Se ad esempio nell'elenco dati la voce "prova" è presente in 3 diverse celle, "prova" rimmarrà disponibile nell'elenco della convalida fino a che non viene selezionato per tre volte.

Soluzione 3 - Variante (da confermare con Ctrl+Maiusc+Invio)

=INDICE(dati_2;RESTO(PICCOLO(SE(FREQUENZA(SE(CONTA.SE(dati_2;dati_2)-CONTA.SE(convalida_2;dati_2);CONFRONTA(dati_2;dati_2;));CONFRONTA(dati_2;dati_2;));CONTA.SE(dati_2;"<"&dati_2)+10^4*VAL.TESTO(dati_2)+CONFRONTA(dati_2;dati_2;)/10^4);RIF.RIGA(A1))*10^4;10^4))

=INDEX(dati_2,MOD(SMALL(IF(FREQUENCY(IF(COUNTIF(dati_2,dati_2)-COUNTIF
(convalida_2,dati_2),MATCH(dati_2,dati_2,)),MATCH(dati_2,dati_2,)),COUNTIF(dati_2,"<"&dati_2)+10^4*ISTEXT(dati_2)+MATCH(dati_2,dati_2,)/10^4),ROW(A1))*10^4,10^4))


DATI è un elenco univoco di soli testi

Soluzione 4 (da confermare con Ctrl+Maiusc+Invio)
=INDICE(dati_3;RESTO(PICCOLO(SE(CONTA.SE(convalida_3;dati_3);"";CONTA.SE(dati_3;"<"&dati_3)+CONFRONTA(dati_3;dati_3;)/10^4);RIF.RIGA(A1))*10^4;10^4))

=INDEX(dati_3,MOD(SMALL(IF(COUNTIF(convalida_3,dati_3),"",COUNTIF(dati_3,"<"&dati_3)+MATCH(dati_3,dati_3,)/10^4),ROW(A1))*10^4,10^4))


DATI è un elenco di soli testi che può contenere doppioni

Soluzione 5 (da confermare con Ctrl+Maiusc+Invio)
=INDICE(dati_4;RESTO(PICCOLO(SE(FREQUENZA(SE(CONTA.SE(convalida_4;dati_4);"";CONFRONTA(dati_4;dati_4;));CONFRONTA(dati_4;dati_4;));CONTA.SE(dati_4;"<"&dati_4)+CONFRONTA(dati_4;dati_4;)/10^4);RIF.RIGA(A1))*10^4;10^4))

=INDEX(dati_4,MOD(SMALL(IF(FREQUENCY(IF(COUNTIF(convalida_4,dati_4),"",MATCH
(dati_4,dati_4,)),MATCH(dati_4,dati_4,)),COUNTIF(dati_4,"<"&dati_4)+MATCH(dati_4,dati_4,)/10^4),ROW(A1))*10^4,10^4))


DATI è un elenco univoco di soli numeri

Soluzione 6 (da confermare con Ctrl+Maiusc+Invio)
=PICCOLO(SE(CONTA.SE(convalida_5;dati_5);"";dati_5);RIF.RIGA(A1))

=SMALL(IF(COUNTIF(convalida_5,dati_5),"",dati_5),ROW(A1))


DATI è un elenco di soli numeri che può contenere doppioni

Soluzione 7 (da confermare con Ctrl+Maiusc+Invio)
=PICCOLO(SE(FREQUENZA(SE(CONTA.SE(convalida_6;dati_6);"";dati_6);dati_6);dati_6);RIF.RIGA(A1))

=SMALL(IF(FREQUENCY(IF(COUNTIF(convalida_6,dati_6),"",dati_6),dati_6),dati_6),ROW(A1))


Ĉ
r,
Mar 15, 2011, 4:18 PM
Ĉ
r,
Mar 15, 2011, 10:23 AM
Comments