Lunghezza testo con copia testo nel foglio di destinazione

Lunghezza testo con copia testo nel foglio di destinazione. - dal 04/09/04 pagina vista: volte

Una recente richiesta è il motivo di questa esercitazione: vediamo la situazione:

Sono diverse le possibilità di soluzione per un problema del genere, ma in comune avremo per ogni soluzione adottata, la necessità di avere tanti fogli per quante lunghezze testo avremo, e su 20000 parole le combinazioni di lunghezze possibili sono elevate: potremo avere infatti celle che contengono addirittura frasi, ed il famoso limite di Windows che non accetta nomi lunghi al massimo 255 caratteri, in questo caso non ci aiuta a determinare il numero massimo di fogli (255 fogli perciò): infatti essendo testo ciò che andiamo a misurare, potremo avere interi periodi da dover misurare.

Altro aspetto da considerare è la "pesantezza" che avrà una cartella con elevato numero di fogli pieni di dati; non esiste infatti un limite al numero di fogli inseribili in una cartella se non quello dato dalle risorse di sistema: CPU lente e scarsa dotazione di RAM sono nemici di grandi numeri; inoltre le procedure per un'operazione del genere richiedono certamente cicli For Next che richiederanno tempi lunghi di esecuzione.

Insomma, il panorama non è dei più invitanti, ma non è impossibile da realizzare, specie se anzichè 20000 parole ne avremo molto meno. Vediamo quindi di parlarne partendo da soluzioni semplici.

Potremmo iniziare, snellendo alcune necessità da non affidare a istruzioni in codice: in una colonna a lato della colonna contenente i nomi, possiamo inserire la funzione =LUNGHEZZA(rif_cella) da trascinare per quante celle conterranno testo, e poi usare la funzione =MAX(rif_Colonna) per sapere quele sarà la parola con il maggior numero di caratteri presenti.

Questo valore ci dirà di quanti fogli avremo bisogno, ma ci consentirà anche di creare un ordinamento basato su due chiavi di ordinamento: sulle lunghezze il primo, e alfabetico il secondo; ordinamento da creare prima della routine che copierà parole di lunghezza uguale nello stesso foglio, e di trovarle già in ordine alfabetico. Dovremo tenere presente comunque che conoscere il numero massimo di fogli da predisporre per accogliere le parole, non ci dirà se esistono parole con di lunghezza sequenziale: non è detto infatti che se MAX ci restituirà 26 (quindi dovremo predisporre 26 fogli), esistano parole di 25 o 24 o 23 ecc. caratteri. Questo vuol dire che alcuni fogli potranno rimanere vuoti. Ma vediamo un esempio (limitato) di una possibile situazione

Nella colonna A un elenco di parole e nella colonna B la formula =SE(A1="";"";LUNGHEZZA(A1)) , cioè controlliamo se la cella a lato nella colonna A non contiene dati, si lascia la cella vuota, altrimenti ne prendiamo la lunghezza. La formula è stata "trascinata per quanto è lungo l'elenco.

In un'altra cella metteremo la formula =MAX(B:B) che ci restituirà 26 (il numero maggiore nella colonna B); questò sarà il numero di fogli di cui avremo bisogno.

Potevamo in una cella, inserire una formula matriciale per ottenere in un solo passaggio il numero maggiore di caratteri delle parole contenute in A, così:

{=MAX(LUNGHEZZA(A1:A50))}

avremmo ottenuto comunque 26, ma non disporremo poi di chiavi di ordinamento per lunghezza.

In pratica vorremo copiare tutte le parole di 2 caratteri nel Foglio2, quelle di 3 caratteri nel Foglio3, quelle di 4 caratteri nel Foglio4, e così via.

Stabilito il numero di fogli necessari possiamo decidere se aggiungere i fogli manualmente, o affidarci ad una routine che lo faccia per noi; in entrambi i casi dovremo trovare un sistema che ci consenta di "abbinare" il numero dei caratteri del testo al nome del Foglio, questo perchè una rouitne, leggendo una lunghezza possa idendificare il numero di foglio corrispondente dove copiare le parole con la stessa lunghezza.

Occorre ricordare che Excel "riconosce" i Fogli o dal "numero indice" del Foglio (valore numerico intero di tipo Integer), che si incrementa di una unità ad ogni nuova aggiunta di ogni foglio, (esempio Sheets(1), Sheets(5) (il primo foglio, il quinto foglio ecc.) oppure dal nome del Foglio (quello che leggiamo nella linguetta e possiamo modificare) che è un valore stringa e come tale va posto sempre tra doppi apici (es. Sheets("Foglio1"), Sheets("Foglio5"), ecc.). Suggerisco di dare un'occhiata all'articolo "Identificare i Fogli di lavoro" presente in questa sezione.

Le routine che presento, si articolano sull'identificazione del foglio destinazione attraverso il nome del foglio, e anche qui occorre fare una precisazione: come nome di un foglio possiamo scegliere se chiamarlo "FoglioX" dove X rappresenta un numero, esempio "Foglio4", o semplicemente "4". Entrambe le soluzioni si affidano ad un nome (quindi stringa, non valore numerico), ma la seconda soluzione (numero senza Foglio)presenta un vantaggio e sicuramente due inconvenienti:

Fatte queste premesse necessarie, e dando per scontato di avere posto una colonna a lato dell'elenco, con le formule Lunghezza, prima di passare ad esaminare le routine per la lettura della lunghezza dei caratteri e rispettiva copia , vediamo una semplice routine che ci servirà per aggiungere tanti fogli quanti ne avremo bisogno.

Partiamo da un foglio classico, di default munito dei tre fogli standard, quindi creeremo un ciclo For Next che partendo da 4, aggiunga i fogli desiderati. Va detto che in questo modo, aggiungendo i fogli in questo sistema, ci troveremo anche col numero indice del foglio che va di pari passo in progressione col nome del Foglio. Comunque questa una possibile routine:

In contatore N viene usato per formare il nome concatenando la parola "Foglio" al valore letto da N, quindi spostiamo (Move) il foglio appena creato alla fine dei fogli presenti contati con Sheets.Count. Ci troveremo con una situazione del genere (vediamo solo i primi fogli, dipende dalla risoluzione schermo usata, ma i fogli ci sono tutti):

Dopo aver aggiunto i fogli, dovremo impostare una routine che in base alla lunghezza, copi le parole nel rispettivo foglio. Una possibile soluzione potrebbe essere questa sotto, che presenta però l'inconveniente di dover predisporre tante condizioni da verificare per quanti saranno i fogli. La cito solo come esempio che potrà servire visto che useremo il Select Case. Per ogni Case previsto, usiamo un ciclo While..Wend che cercherà sul foglio destinazione, la prima riga libera nella colonna 1, dove incollare le parole trovate: iniziamo con un ciclo For Each..Next che scorrendo tutte le celle in un intervallo, legga tramite la funzione Len la lunghezza del testo contenuto nella cella stessa. Da notare che l'identificazione del foglio in cui copiare, viene fatta attraverso il numero indice del foglio e non il suo nome, ma avendo già provveduto alla creazione dei fogli con la routine sopra, siamo sicuri che a indice foglio corrisponde "nome foglio" uguale.

Questa routine diventa chilometrica e poco felice come scelta nei tempi di esecuzione (infatti pur avendo optato per il Select Case che risulta meno "faticoso" da leggersi da parte del codice rispetto agli If... Then, rappresenta comunque una grossa perdita di tempo visto che il codice dovrà comunque scegliere il Case giusto per ogni ciclo For).

Una procedura migliore può essere questa che vediamo sotto. In questa non aggiungiamo i fogli prima, ma useremo una macro che provvederà ad aggiungere un foglio solo se risulterà inesistente. Questa macro la "chiamiamo" dall'istruzione principale che leggendo la lunghezza della parola, cercherà se esiste il foglio con il nome "FoglioX" dove X sarà il numero corrispondente al valore della lunghezza, se non esiste l'aggiunge e ci copia le parole di pari lunghezza. Il foglio aggiunto lo faremo spostare alla fine dei fogli esistenti, e per questo diventa importante ordinare prima di tutto, per lunghezza parola, l'elenco da leggere sul foglio 1; è tramite questo ordinamento che ci assicuriamo una progressione anche nei fogli aggiunti.

Il tutto viene comunque svolto tramite il solito ciclo For Each..Next. Avremo bisogno di dichiarare come Public della variabile L (lunghezza) che sarà letta da entrambe le routine, e la posizioniamo in Generale - Dichiarazioni del Modulo. Vediamo i blocchi di istruzioni: in questa routine eseguo l'ordinamento basandolo solo sulla lunghezza, mentre eseguiamo l'ordinamento sulle parole sul foglio destinazione; è solo per mostrare come eseguire un ordinamento "a distanza" sul foglio destinazione, lanciando la macro dal foglio1. Nella pratica è preferibile, per rendere più veloce l'esecuzione dele istruzioni, eseguire subito l'ordinamento alfabetico sul foglio1 aggiungendo all'ordinamento sulla chiave "lunghezza" anche l'ordinamento sul testo con SortKey2:=Range("A1") ecc.

Sezione Generale - Dichiarazioni del Modulo:

Public L As Integer 'con L prenderemo la lunghezza del testo nella cella letta dal ciclo For
---------------------------------------------------------------------------------

Sub ContaCopia()
'sotto iniziamo l'ordinamento sulla chiave Lunghezza
Range("A1:B30").Sort Key1:=Range("B1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Dim CL As Object 'dichiariamo CL come Object (le celle lette nell'intervallo)
For Each CL In Range("A1:A30") 'iniziamo a scorrere le celle dell'intervallo (nell'es. solo fino alla 30)
If CL = "" Then GoTo 10 'se la cella è vuota passiamo alla successiva (andiamo a Next)
L = Len(CL) 'con la variabile L prendiamo la lungezza della cella ora letta
AggiungiFoglio 'chiamiamo la macro AggiungiFoglio che verifica ed ev. anngiunge il foglio

Dim iRow As Integer
iRow = 1 'impostiamo la riga di inizio ricerca cella libera sul foglio (destinazione)
'sotto, ora sul foglio di cui conosciamo il nome, cerchiamo la prima cella libera dove copiare
While Sheets("Foglio" & L).Cells(iRow, 1) <> ""
iRow = iRow + 1
Wend
CL.Copy Sheets("Foglio" & L).Cells(iRow, 1) 'copiamo il valore che è nella cella letta sul foglio1

'e ordiniamo le parole copiate sul foglio destinazione. se si fosse scelto il doppio ordinamento iniziale, 'queste istruzioni, da With a End With, andranno tolte.

With Sheets("Foglio" & L)
.Range("A:A").Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
End With
Sheets(1).Activate 'indi ritorniamo al Foglio1, visto che se viene aggiunto un nuvo foglio il focus si 'posiziona su questo
10:
Next

End Sub

Non sarebbe male inserire a inizio della routine l'istruzione Application.ScreenUpdating = False , per evitare di vedere i saltellamenti a schermo, ma potrete inserirla voi. Ora vediamo la routine AggiungiFoglio:

Sub AggiungiFoglio()
For Each ws In Worksheets 'inizia un ciclo che controlla tutti i fogli della cartella
y = ws.Name 'con "y" prendiamo il nome del foglio ora letto nel ciclo
h = "Foglio" & L 'con h prendiamo il nome del foglio formato dal concatenamento della parola "Foglio" 'più il valore restituito dalla variabile L letta con l'altra routine

If y = h Then Exit Sub 'se il nome del foglio y è uguale al nome ottenuto con h, quindi il foglio esiste, 'usciamo dalla routine, altrimenti, alla fine del ciclo, dopo averli letti tutti
Next
'aggiungiamo il nuovo foglio dandogli il nome
Sheets.Add.Name = "Foglio" & L
'e lo spostiamo alla fine dei fogli
Sheets("Foglio" & L).Move After:=Sheets(Sheets.Count)

End Sub

Io ho provato con un elenco di 500 parole, e su un pentium 4 2400, ho impiegato meno di tre secondi.