Testu di ligame per cundizione

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'è:

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

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ù:

Testu di ligame per cundizione

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):

Testu di ligame per cundizione

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):

Testu di ligame per cundizione

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):

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

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:

Testu di ligame per cundizione

Nota chì tutte e funzioni M sò sensible à u casu (cuntrariu di Excel). Dopu à cliccà nantu OK avemu una nova colonna cù indirizzi incollati:

Testu di ligame per cundizione

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):

Testu di ligame per cundizione

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

Lascia un Audiolibro