| | Post: 1 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
  05/12/2017 09:17 | |
Buongiorno a tutti,
sono nuovo di questo forum spero di apportare il mio contributo anche se piccolo, ora ho bisogno di un vostro aiuto in quanto non riesco a gestire un concatena con più criteri come ho riportato nel file excel in allegato.
Cosa vorrei fare? Concatenare descrizioni uniche (riga CATEGORIA PROVE) che appartengono a valori univoci (riga CONCATENA) con il risultato che viene riportato nella riga RISULTATO CHE VORREI OTTENERE
non so se sono stato chiaro
grazie in anticipo |
|
| | Post: 2.282 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 09:39 | |
Ciao
la tua versione di Excel riconosce la funzione TESTO.UNISCI?
Faccio un esempio
=testo.unisci("-";1;A1:A10)
restituisce un risultato? Non mi interessa che sia corretto.
Se la formula non restituisce errore, provo ad aggiustarla al tuo caso.
Saluti [Modificato da (Canapone) 05/12/2017 09:52]
|
| | Post: 1 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 10:54 | |
no la funzione testo.unisci non va mi restituisce #NOME? |
| | Post: 2.288 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 11:04 | |
Ciao
uso Excel 2010 per PC: nel file allegato trovi la UDF "provaunione", che dovrebbe simulare TESTO.UNISCI (credo disponibile su Excel 365).
Ho trovato la formula udf in rete: l'ho copiata in un modulo.
Uso questa formula per concatenare sino a 50 stringhe:
=provaunione("-";1;INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);INDICE(RIF.RIGA($1:$50);)))) Ho lasciato la formula attiva solo nella cella D2.
Sotto ho fatto un copia incolla valori
Se servisse: spero di aver capito.
[Modificato da (Canapone) 05/12/2017 11:10]
|
| | Post: 2 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 11:21 | |
la formula funziona bene grazie mille....ora devo adattarla al file originario, vedo che hai usato VB, quindi capisco che senza non è possibile ottenere lo stesso risultato.
grazie ancora |
| | Post: 2.290 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 11:26 | |
Ciao
senza la simulazione di TESTO.UNISCI dovrei scrivere una formula che concatena un numero indefinito di correlazioni senza ripetizione
Si può fare, ma non saprei se ne vale la pena: è la stessa formula
esempio per le prime 7 corrispondenze
=SE.ERRORE(INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);1));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);2));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);3));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);4));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);5));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);6));"")&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);7));"")[Modificato da (Canapone) 05/12/2017 11:30]
|
| | Post: 3 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 11:29 | |
no no ma va bene quella che hai postato prima nel file era solo per capire
Ti ringrazio nuovamente |
| | Post: 2.291 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 11:34 | |
Giro un esempio senza UDF: se hai bisogno di un'ottava stringa (vedi l'8 infondo alla stringa) integri la formula con
&SE.ERRORE("-"&INDICE(A$1:A$154;AGGREGA(15;6;RIF.RIGA($1:$154)/(FREQUENZA(CONFRONTA(A$1:A$154;A$1:A$154;0);CONFRONTA(A$1:A$154;A$1:A$154;0)*($B$1:$B$154=B2))>0);8));"") Saluti
[Modificato da (Canapone) 05/12/2017 11:36]
|
| | Post: 4 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 11:49 | |
credo che prenderò in considerazione quella con UDF perchè il numero di stringhe è molto variabile e potrebbe capitare di averne maggiori di 8 (poi la formula diventerebbe troppo lunga)
|
| | Post: 2.292 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 12:01 | |
Ciao
nella coda della formula se devi gestire più di 50 stringhe diverse modifica solo il 50 (portalo a 100 per esempio, ma non esagerare) in
....INDICE(RIF.RIGA($1:$50);....
Saluti
|
| | Post: 5 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 12:18 | |
credo che 50 sia sufficientemente un numero congruo ma tengo a mente i tuoi preziosissimi consigli e dritte
ti ringrazio tantissimo [Modificato da geflor 05/12/2017 12:18] |
| | Post: 6 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 05/12/2017 15:11 | |
ho scritto in excel nel mio file originario, che contiene in questo momento colonne da A a BG e 16433 righe, la seguente formula:
=provaunione("-";1;INDICE(BG$1:BG$20000;AGGREGA(15;6;RIF.RIGA($1:$20000)/(FREQUENZA(CONFRONTA(BG$1:BG$20000;BG$1:BG$20000;0);CONFRONTA(BG$1:BG$20000;BG$1:BG$20000;0)*($AS$1:$AS$20000=AS2))>0);INDICE(RIF.RIGA($1:$50);)))) credo che il numero 20000 si troppo grande perchè excel non risponde più hai comandi.
come posso risolvere |
| | Post: 2.293 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 16:34 | |
Ciao
credo ci voglio del codice VBA che gestisca totalmente il concatenamento: in questo caso non sono d'aiuto.
Se mi viene in mente qualcosa di "formulato" la condivido.
Spero qualche amico del Forum arrivi in aiuto. [Modificato da (Canapone) 05/12/2017 16:35]
|
| | Post: 2.294 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 05/12/2017 19:23 | |
Ciao
faccio un altro tentativo.
Se copi le due colonne che devono gestire il concatenamento in un secondo foglio (una accanto all'altra; foglio "tabella") e rimuovi i duplicati, potesti usare nella terza colonna
=provaunione("-";1;INDICE(A$1:A$20000;AGGREGA(15;6;RIF.RIGA($1:$20000)/($B$1:$B$20000=B2);INDICE(RIF.RIGA($1:$50);)))) La formula è molto meno impegnativa per i processori: ho levato l'estrazione degli univoci sotto condizione.
Una volta ottenute le stringhe concatenate nel foglio "tabella", puoi intercettarle nel foglio principale con un INDICE/CONFRONTA
=INDICE(tabella!$C$2:$C$20000;CONFRONTA(A2&B2;INDICE(tabella!$A$2:$A$20000&tabella!$B$2:$B$20000;);0)) Spero si capisca dove sto cercando di andare a parare...
Allego esempio
[Modificato da (Canapone) 05/12/2017 19:26]
|
| | Post: 7 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 06/12/2017 16:44 | |
ho provato con la tabella di appoggio, come da tuo gentile ulteriore tentativo, ma nulla di fatto si blocca tutto, e che intorno alla riga 500 si impalla e non restituisce più nulla |
| | Post: 2.295 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 06/12/2017 17:45 | |
Ciao
grazie del riscontro: mi spiace.
Prima di condividere la nuova impostazione ho simulato il funzionamento delle formule su un database di qualche migliaio di righe (codici simulati) su un pc con processore I3.
Non ho avuto problemi, ma evidentemente non sono riuscito a ricreare la complessità del file con il quale stai lavorando.
In passato ho avuto problemi di ricalcolo a causa delle compresenza nel foglio di funzioni volatili (INDIRETTO() o OGGI() solo per fare un paio di esempi) che -letteralmente -mi appestavano il file.
Ho fatto attenzione a non proporle.
Spero qualche amico del Forum intervenga con il VBA ( sono a zero)
Buon lavoro
[Modificato da (Canapone) 06/12/2017 17:46]
|
| | Post: 2.296 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 07/12/2017 13:30 | |
Ciao,
trovata altra UDF qui:
https://www.mrexcel.com/forum/excel-questions/977125-using-concatenateif-function-trying-remove-duplicates.html
Ringrazio Mike Erickson.
Fatto prova su 20.000 record: bloccato il computer ( solito pc con processore I3 in ricalcolo per oltre 40 minuti)
Ho ridotto il range di lettura della formula.
Nelle prime 100 righe, formule verdi (vedi allegato)
=CONCATIF(B$2:B$300;B2;A$2:A$300;" - ";VERO) da riga 101 - sino a riga 19900: queste formule elaborano solo 200 righe (100 sopra la riga della formula e 100 sotto).
Formule arancioni
=CONCATIF(INDICE(B$2:B$20000;RIF.RIGA()-100):INDICE(B$2:B$20000;RIF.RIGA()+100);B101;INDICE(A$2:A$20000;RIF.RIGA()-100):INDICE(A$2:A$20000;RIF.RIGA()+100);" - ";VERO) i +/-100 creano i range d'elaborazione della formula
19000 in meno di due minuti
Ultime 100 righe formule celesti
=CONCATIF(B$19800:B$20000;B19900;A$19800:A$20000;" - ";VERO) Allego esempio
Saluti
[Modificato da (Canapone) 07/12/2017 13:41]
|
| | Post: 8 | Registrato il: 05/12/2017
| Città: AVELLINO | Età: 45 | Utente Junior | 2016 | | OFFLINE | |
|
| 14/12/2017 11:19 | |
scusami se rispondo solo adesso ma sono stato fuori
ho provato anche con quest'ultima ma nulla di fatto al dire il vero quando scrivo la tua funzione per le righe centrali non funziona proprio
siccome =CONCATIF(B$2:B$300;B2;A$2:A$300;" - ";VERO) se viene usata per tante righe impalla il pc non è possibile scrivere qualcosa che prende 30 righe sopra e 30 sotto un po come è fatta quelle per le righe arancioni che pero non funziona o almeno io non riesco a farla funzionare
|
| | Post: 2.299 | Registrato il: 27/09/2010
| Città: FIRENZE | Età: 61 | Utente Veteran | Excel 2010 | | OFFLINE | |
|
| 14/12/2017 13:23 | |
Ciao
anch'io avrei adattato la formula arancione dalla riga 31 in giù:
=CONCATIF(INDICE(B$2:B$20000;RIF.RIGA()-30):INDICE(B$2:B$20000;RIF.RIGA()+30);B31;INDICE(A$2:A$20000;RIF.RIGA()-30):INDICE(A$2:A$20000;RIF.RIGA()+30);" - ";VERO) Per ridurre il lavoro, non mi viene in mente altro.
Ho fatto una prova servendomi di INDIRETTO, ma i tempi di elaborazione si sono parecchio allungati
=CONCATIF(INDIRETTO("B"&RIF.RIGA()-30&":"&"B"&RIF.RIGA()+30);B31;INDIRETTO("A"&RIF.RIGA()-30&":"&"A"&RIF.RIGA()+30);" - ";VERO) Sempre da riga 31.
Saluti [Modificato da (Canapone) 14/12/2017 13:23]
|
|
|
|
|