User‎ > ‎

Funzione CONCATENA

Si è discusso molto sulla funzione CONCATENA di Excel. Ad ogni nuova versione di Excel aspettiamo che questa funzione sia migliorata ... purtroppo la speranza è stata inattesa anche nelle versioni 2007 e 2010.
Il problema è l'impossibilità di usare la funzione su range multicelle o su matrici.
Il concatena avviene singolarmente tra celle o argomenti costanti. Qualora un argomento sia costituito da range multicelle o matrici anche se seguito da una conferma con Ctrl+Maiusc+Invio l'esito sarà identico. Nella cella  non potranno essere concatenati un numero di elementi maggiori a quelli degli argomenti passati.
 
Se dovessimo concatenare le celle di un range ad esempio A1:A5 ... l'unico modo è scrivere la formula con i 5 argomenti:
=CONCATENA(A1;A2;A3;A4;A5)
per rendere la selezione più rapida è possibile tenere premuto Ctrl e eseguire la selezione successiva delle celle (questo risparmierà di digitare il ; che delimita gli argomenti).

Per concatenare due valori o celle è possibile utilizzare anche la e commerciale &.
=A1&A2&A3&A4&A5 concatena le celle di qui sopra.

Un uso matriciale è stato discusso a proposito del calcolo combinatorio del caso di Classe 2.
 
Altra soluzione è quella di utilizzare una colonna di appoggio, tornando al range A1:A5 ad esempio è possibile scrivere in B1
=A1
in B2
=CONCATENA(B1;A2)
da trascinare fino a B5 dove si otterrà il concatenamento desiderato.

Spesso mi è capitato di tentare una soluzione senza colonna di appoggio. E' uno dei problemi irrisolti di Excel e per quanto ne so non esiste una soluzione *pulita*.

Più come curiosità che come soluzione comoda e utile propongo un escamotage che permette di ottenere il concatenamento di un range multicelle senza l'uso del VBA e utilizzando come appoggio un'unica cella.
Il trucco consiste nel consentire le iterazioni. Consentire le iterazioni serve quando si vuole risolvere un riferimento circolare. Ha l'inconveniente di rallentare il calcolo del foglio di lavoro.

Per consentire le iterazioni in Excel 2003:
  1. Scegliere Opzioni dal menu Strumenti, quindi scegliere la scheda Calcolo.
  2. Selezionare la casella di controllo Iterazioni.
  3. Per impostare il numero massimo di iterazioni consentite, digitarlo nella casella Numero massimo. Più alto è il numero di iterazioni, maggiore è il tempo necessario al calcolo del foglio di lavoro.
Nel caso di Excel 2007:
  1. Fare clic sul pulsante con il logo di Microsoft Office Icona del pulsante, quindi su Opzioni di Excel e infine sulla categoria Formule.
  2. Selezionare la casella di controllo Attiva calcolo iterativo nella sezione Opzioni di calcolo.
  3. Per impostare il numero massimo di ricalcoli, digitare il numero di iterazioni nella casella Numero massimo
Immaginiamo ora di voler concatenare le celle A1:A50, dovremo impostare come numero massimo di iterazioni un valore maggiore di 50.
In B2:
=B2+1

In B1 scrivete:

=SE(RESTO(B2;100)=1;"";CONCATENA(B1;SCARTO(A1;RESTO(B2;100)-2;)))

Oppure nella più breve:
=SE(RESTO(B2;100)=1;"";B1&SCARTO(A1;RESTO(B2;100)-2;))

versione inglese
=IF(MOD(B2,100)=1,"",CONCATENATE(B1,OFFSET(A1,MOD(B2,100)-2,)))
La formula si aggiorna ad ogni variazione nel range A1:A50 e ottiene un concatena dei valori. Volendo è possibile giocare e ottenere soluzioni diverse, ad esempio limitare il range ad un intervallo o concatenare le celle usando uno spazio come delimitatore come in questo caso:
In B4 come prima:
=B4+1

In B3 scrivete:
=ANNULLA.SPAZI(SE(RESTO(B4;100)=1;"";CONCATENA(B3;" ";SCARTO(A1;RESTO(B4;100)-2;))))

oppure nella più breve:
=ANNULLA.SPAZI(SE(RESTO(B4;100)=1;"";B3&" "&SCARTO(A1;RESTO(B4;100)-2;)))

versione inglese
=TRIM(IF(MOD(B4,100)=1,"",CONCATENATE(B3," ",OFFSET(A1,MOD(B4,100)-2,))))

N.B.
è importante la posizione relativa della cella di appoggio rispetto a quella che conterrà la formula, infatti deve essere o alla sua destra sulla stessa riga oppure su una riga più bassa. Il motivo è legato all'ordine in cui le celle vengono ricalcolate è necessario infatti che venga calcolata prima la cella con la formula del concatena e poi quella con il contatore.

Altre soluzioni si potrebbero studiare (se ne valesse veramente la pena) ma come detto prima non è certo una soluzione comoda e che consiglierei. Una ottima UDF è stata già discussa in questo sito a proposito della funzione JoinUDF ... è quella che utilizzo io all'occorrenza, aspettando la prossima versione di Excel.

 
Ĉ
roberto mensa,
Feb 19, 2011, 7:21 AM
Comments