cuntenutu
Aghju digià scrittu annantu à cumu pudete cola rapidamente testu da parechje cellule in una è, à u cuntrariu, analizà una longa stringa di testu in cumpunenti. Avà fighjemu un compitu strettu, ma un pocu più cumplessu - cumu incolla u testu da parechje cellule quandu una certa cundizione specificata hè cumpleta.
Diciamu chì avemu una basa di dati di i clienti, induve un nome di a cumpagnia pò currisponde à parechji emails diffirenti di i so impiegati. U nostru compitu hè di cullà tutti l'indirizzi per i nomi di l'imprese è di cuncatenà (separati da virgule o punti e virgule) per fà, per esempiu, una lista di mailing per i clienti, vale à dì ottene qualcosa cum'è:
In altri palori, avemu bisognu di un strumentu chì cola (ligame) u testu secondu a cundizione - un analogu di a funzione SUMMESLI (SUMIF), ma per u testu.
Metudu 0. Formula
Ùn hè micca assai eleganti, ma u modu più faciule. Pudete scrive una formula simplice chì verificarà se l'impresa in a fila dopu differisce da a precedente. S'ellu ùn hè micca sfarente, poi cola l'indirizzu prossimu separatu da una virgola. S'ellu hè diversu, allora "resettà" l'accumulatu, cuminciendu di novu:
I disadvantages di questu approcciu sò evidenti: da tutte e cellule di a colonna supplementaria ottenuta, avemu bisognu solu l'ultimi per ogni cumpagnia (giallu). Se a lista hè grande, allora per selezziunà rapidamente, avete da aghjunghje una altra colonna cù a funzione DLSTR (LEN), cuntrollanu a lunghezza di e corde accumulate:
Avà pudete filtrà quelli è copià l'indirizzu necessariu incollatu per u più usu.
Metudu 1. Macrofunzione di cola per una cundizione
Se a lista originale ùn hè micca ordinata per cumpagnia, allora a formula simplice di sopra ùn viaghja micca, ma pudete fà facilmente cun una piccula funzione persunalizata in VBA. Apertura l'Editor Visual Basic pressu una scurciatoia di tastiera Alt + F11 o usendu u buttone franese tecnicu Insignia documentaliste (Sviluppatore). In a finestra chì apre, inserisce un novu modulu viotu attraversu u menù Inserisce - Modulu è copià u testu di a nostra funzione quì:
Funzione MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " incollazioni ùn sò micca uguali à l'altri - esce cù un errore If SearchRange.Count <> TextRange.Count Allora MergeIf = CVErr(xlErrRef) Exit Function End Se 'passa per tutte e cellule, verificate a cundizione è recullate u testu in a variabile OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Allora OutText = OutText & TextRange.Cells (i) & Delimeter Next I 'mostra i risultati senza l'ultimu delimitatore MergeIf = Left (OutText, Len (OutText) - Len (Delimeter)) End funzione
Sè avà torna à Microsoft Excel, allora in a lista di funzioni (buttone fx in a barra di formula o tab Formule - Inserisci Funzione) serà pussibule di truvà a nostra funzione MergeIf in categuria Definitu da l'Usuariu (User Defined). L'argumenti di a funzione sò i seguenti:
Metudu 2. Concatenate testu da cundizione inexact
Se sustituemu u primu caratteru in a 13a linea di a nostra macro = à l'operatore apprussimativu di match cum'è, Tandu serà pussibule di realizà l'incollatura da una partita inesatta di e dati iniziali cù u criteriu di selezzione. Per esempiu, se u nome di a cumpagnia pò esse scrittu in diverse varianti, allora pudemu verificà è cullà tutti cù una funzione:
I caratteri jolly standard sò supportati:
- astérisque (*) - désigne un certain nombre de caractères (y compris leur absence)
- segnu d'interrogazione (?) - significa ogni caratteru unicu
- signu di libbra (#) - significa ogni cifru (0-9)
Per automaticamente, l'operatore Like hè sensible à u casu, vale à dì capisce, per esempiu, "Orion" è "orion" cum'è diverse cumpagnie. Per ignurà u casu, pudete aghjunghje a linea à u principiu di u modulu in l'editore Visual Basic Opzione Compara Testu, chì cambierà Like per esse insensibile à maiuscule.
In questu modu, pudete cumpone maschere assai cumplesse per verificà e cundizioni, per esempiu:
- ?1##??777RUS - selezzione di tutte e targhe di a regione 777, cuminciendu cù 1
- LLC * - tutte e cumpagnie chì u nome principia cù LLC
- ##7## - tutti i prudutti cù un codice digitale di cinque cifre, induve u terzu cifru hè 7
- ?????? - tutti i nomi di cinque lettere, etc.
Metudu 3. Funzione macro per incollare u testu in dui cundizioni
In u travagliu pò esse un prublema quandu avete bisognu di ligà u testu più di una cundizione. Per esempiu, imaginemu chì in a nostra tavula precedente, una colonna più cù a cità hè stata aghjunta, è l'incollamentu deve esse realizatu micca solu per una cumpagnia determinata, ma ancu per una cità determinata. In questu casu, a nostra funzione duverà esse ligeramente mudernizata aghjunghjendu un altru cuntrollu di gamma:
Funzione MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'caratteri delimitatori (pò esse rimpiazzati cù spaziu o ; etc.) e.) 'se i intervalli di validazione è di incollatura ùn sò micca uguali à l'altri, esce cù un errore If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Allora MergeIfs = CVErr(xlErrRef) Exit Function End If "Passà tutte e cellule, verificate tutte e cundizioni è recullate u testu in a variàbile OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells (i) = Condition1 And SearchRange2.Cells (i) = Condition2 Allora OutText = OutText & TextRange.Cells(i) & Delimeter End If Next I 'mostra i risultati senza l'ultimu delimitatore MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) Funzione End
Serà appiicatu esattamente in u listessu modu - solu l'argumenti avà deve esse specificatu più:
Metudu 4. Grouping and gluing in Power Query
Pudete risolve u prublema senza prugrammazione in VBA, se utilizate l'add-in gratuitu Power Query. Per Excel 2010-2013 pò esse scaricatu quì, è in Excel 2016 hè digià custruitu in modu predeterminatu. A sequenza di l'azzioni serà a siguenti:
Power Query ùn sapi micca cumu travaglià cù tavule regulare, cusì u primu passu hè di trasfurmà a nostra tavola in una "intelligente". Per fà questu, selezziunate è appughjà a cumminazzioni Ctrl+T o sceglite da a tabulazione Home - Formate cum'è una tavola (Casa - Formate cum'è Table). Nantu à a tabulazione chì apparisce dopu regala (Cuncepimentu) pudete stabilisce u nome di a tavola (aghju lasciatu u standard Table 1):
Avà carchemu a nostra tavola in l'add-in Power Query. Per fà questu, nantu à a tabulazione Dati (se avete Excel 2016) o in a tabulazione Power Query (se avete Excel 2010-2013) cliccate Da a tavula (Dati - Da Table):
In a finestra di l'editore di quistione chì si apre, selezziunate a colonna clicchendu nantu à l'intestazione Sicília è appughjà u buttone sopra Group (Gruppu per). Inserite u nome di a nova colonna è u tipu d'operazione in u gruppu - Tutte e linee (tutte i fila):
Cliccate OK è uttene una mini-tavula di valori raggruppati per ogni cumpagnia. U cuntenutu di i tavulini sò chjaramente visibili si fate cliccà nant'à u fondu biancu di e cellule (micca nantu à u testu!) In a colonna resultanti:
Avà aghjustemu una colonna più, induve, usendu a funzione, pegamu u cuntenutu di e culonni di Indirizzu in ognuna di e mini-tavule, siparati da virgule. Per fà questu, nantu à a tabulazione Aghjunghjite a colonna pressemu Colonna persunalizata (Aggiungi colonna - Colonna persunalizata) è in a finestra chì appare, inserite u nome di a nova colonna è a formula di accoppiamentu in a lingua M integrata in Power Query:
Nota chì tutte e funzioni M sò sensible à u casu (cuntrariu di Excel). Dopu à cliccà nantu OK avemu una nova colonna cù indirizzi incollati:
Resta per sguassà a colonna digià inutile TableAddresses (cliccate dirittu nantu à u titulu) Sguassà a colonna) è carica i risultati à u fogliu clicchendu nantu à a tabulazione Home - Chiudi è scaricate (Casa - Chiudi è carica):
Sfumatura impurtante: A cuntrariu di i metudi precedenti (funzioni), i tavule da Power Query ùn sò micca aghjurnati automaticamente. Se in u futuru ci saranu cambiamenti in i dati fonte, allora vi tuccherà à cliccà dritta in ogni locu in a tabella di risultati è selezziunate u cumandamentu. Aghjurnà & Salvà (rinfriscà).
- Cumu sparte una longa stringa di testu in parti
- Diversi modi per incolla u testu da diverse cellule in una sola
- Utilizà l'operatore Like per pruvà u testu contr'à una maschera