Contenuti
Ho già scritto su come incollare rapidamente il testo da più celle in una e, al contrario, analizzare una lunga stringa di testo in componenti. Ora diamo un'occhiata a un'attività vicina, ma leggermente più complessa: come incollare il testo da più celle quando viene soddisfatta una determinata condizione specifica.
Diciamo che abbiamo un database di clienti, dove il nome di un'azienda può corrispondere a diverse email dei suoi dipendenti. Il nostro compito è raccogliere tutti gli indirizzi per nome di società e concatenarli (separati da virgole o punto e virgola) in modo da creare, ad esempio, una mailing list per i clienti, ovvero ottenere un output del tipo:
In altre parole, abbiamo bisogno di uno strumento che incollerà (collegherà) il testo in base alla condizione, un analogo della funzione SUMMESLI (SOMMA.SE), ma per il testo.
Metodo 0. Formula
Non molto elegante, ma il modo più semplice. Puoi scrivere una semplice formula che verificherà se l'azienda nella riga successiva è diversa dalla precedente. Se non differisce, incolla l'indirizzo successivo separato da una virgola. Se differisce, allora "resettiamo" l'accumulato, ricominciando:
Gli svantaggi di questo approccio sono evidenti: da tutte le celle della colonna aggiuntiva ottenuta, abbiamo bisogno solo delle ultime per ogni azienda (giallo). Se l'elenco è grande, per selezionarli rapidamente, dovrai aggiungere un'altra colonna usando la funzione DLSTR (LENTE), controllando la lunghezza delle stringhe accumulate:
Ora puoi filtrare quelli e copiare l'incollaggio dell'indirizzo necessario per un ulteriore utilizzo.
Metodo 1. Macrofunzione di incollaggio di una condizione
Se l'elenco originale non è ordinato per azienda, la semplice formula sopra non funziona, ma puoi facilmente spostarti con una piccola funzione personalizzata in VBA. Aprire l'editor di Visual Basic premendo una scorciatoia da tastiera Alt + F11 o utilizzando il pulsante Visual Basic linguetta sviluppatore (Sviluppatore). Nella finestra che si apre, inserisci un nuovo modulo vuoto tramite il menu Inserisci – Modulo e copia lì il testo della nostra funzione:
Function MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " gli incollaggi non sono uguali tra loro - usciamo con un errore If SearchRange.Count <> TextRange.Count Then MergeIf = CVErr(xlErrRef) Exit Function End If 'passa attraverso tutte le celle, controlla la condizione e raccogli il testo nella variabile OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'visualizza i risultati senza l'ultimo delimitatore MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funzione
Se ora torni a Microsoft Excel, nell'elenco delle funzioni (pulsante fx nella barra o nella scheda della formula Formule: funzione di inserimento) sarà possibile trovare la nostra funzione Unisci se nella categoria Definito dall'utente (Definito dall'utente). Gli argomenti della funzione sono i seguenti:
Metodo 2. Concatena il testo per condizione inesatta
Se sostituiamo il primo carattere nella 13a riga della nostra macro = all'operatore di corrispondenza approssimativo Come, sarà quindi possibile effettuare l'incollaggio per corrispondenza inesatta dei dati iniziali con il criterio di selezione. Ad esempio, se il nome dell'azienda può essere scritto in diverse varianti, allora possiamo verificarle e raccoglierle tutte con un'unica funzione:
I caratteri jolly standard sono supportati:
- asterisco (*) – indica qualsiasi numero di qualsiasi carattere (inclusa la loro assenza)
- punto interrogativo (?) – sta per ogni singolo carattere
- cancelletto (#) – sta per una cifra qualsiasi (0-9)
Per impostazione predefinita, l'operatore Like fa distinzione tra maiuscole e minuscole, ovvero comprende, ad esempio, "Orion" e "orion" come società diverse. Per ignorare il caso, puoi aggiungere la riga all'inizio del modulo nell'editor di Visual Basic Opzione Confronta testo, che cambierà Like in modo che non faccia distinzione tra maiuscole e minuscole.
In questo modo è possibile comporre maschere molto complesse per il controllo delle condizioni, ad esempio:
- ?1##??777RUS – selezione di tutte le targhe della regione 777, a partire da 1
- LLC* – tutte le società il cui nome inizia con LLC
- ##7## – tutti i prodotti con un codice digitale a cinque cifre, dove la terza cifra è 7
- ????? – tutti i nomi di cinque lettere, ecc.
Metodo 3. Funzione macro per incollare il testo in due condizioni
Nel lavoro potrebbe esserci un problema quando è necessario collegare il testo a più di una condizione. Ad esempio, immaginiamo che nella nostra tabella precedente sia stata aggiunta un'altra colonna con la città e che l'incollaggio debba essere eseguito non solo per una determinata azienda, ma anche per una determinata città. In questo caso, la nostra funzione dovrà essere leggermente modernizzata aggiungendo ad essa un altro controllo di intervallo:
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 (può essere sostituito con spazio o ; ecc.) e.) 'se gli intervalli di convalida e incollaggio non sono uguali tra loro, esci con un errore If SearchRange1.Count <> TextRange.Count Or SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'esamina tutte le celle, controlla tutte le condizioni e raccogli il testo nella variabile OutText For i = 1 To SearchRange1.Cells.Count If SearchRange1.Cells(i) = Condition1 And SearchRange2.Cells(i) = Condition2 Then OutText = OutText & TextRange.Cells(i) & Delimeter End If Next i 'visualizza i risultati senza l'ultimo delimitatore MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Verrà applicato esattamente allo stesso modo: ora è necessario specificare di più solo gli argomenti:
Metodo 4. Raggruppamento e incollaggio in Power Query
Puoi risolvere il problema senza programmare in VBA, se usi il componente aggiuntivo gratuito Power Query. Per Excel 2010-2013 può essere scaricato qui e in Excel 2016 è già integrato per impostazione predefinita. La sequenza delle azioni sarà la seguente:
Power Query non sa come lavorare con le tabelle normali, quindi il primo passaggio è trasformare la nostra tabella in una "intelligente". Per fare ciò, selezionalo e premi la combinazione Ctrl+T o seleziona dalla scheda Home – Formatta come una tabella (Home — Formatta come tabella). Nella scheda che poi appare Costruttore (Design) puoi impostare il nome della tabella (ho lasciato lo standard Tabella 1):
Ora carichiamo la nostra tabella nel componente aggiuntivo Power Query. Per fare ciò, nella scheda Dati (se hai Excel 2016) o nella scheda Power Query (se hai Excel 2010-2013) fai clic su Dal tavolo (Dati — Dalla tabella):
Nella finestra dell'editor di query che si apre, seleziona la colonna facendo clic sull'intestazione Azienda e premere il pulsante in alto Gruppo (Raggruppa per). Immettere il nome della nuova colonna e il tipo di operazione nel raggruppamento – Tutte le linee (Tutte le righe):
Fare clic su OK e otteniamo una mini-tabella di valori raggruppati per ciascuna azienda. Il contenuto delle tabelle è chiaramente visibile se si fa clic con il tasto sinistro del mouse sullo sfondo bianco delle celle (non sul testo!) nella colonna risultante:
Ora aggiungiamo un'altra colonna, dove, usando la funzione, incolliamo il contenuto delle colonne Indirizzo in ciascuna delle mini-tabelle, separati da virgole. Per fare ciò, nella scheda Aggiungi colonna premiamo Colonna personalizzata (Aggiungi colonna — Colonna personalizzata) e nella finestra che appare, inserisci il nome della nuova colonna e la formula di accoppiamento nel linguaggio M integrato in Power Query:
Nota che tutte le funzioni M fanno distinzione tra maiuscole e minuscole (a differenza di Excel). Dopo aver cliccato su OK otteniamo una nuova colonna con indirizzi incollati:
Resta da rimuovere la colonna già non necessaria TableAddress (clic destro sul titolo) Elimina colonna) e carica i risultati sul foglio cliccando sulla scheda Home — Chiudi e scarica (Home — Chiudi e carica):
Sfumatura importante: a differenza dei metodi (funzioni) precedenti, le tabelle di Power Query non vengono aggiornate automaticamente. Se in futuro ci saranno modifiche nei dati di origine, dovrai fare clic con il pulsante destro del mouse in un punto qualsiasi della tabella dei risultati e selezionare il comando Aggiorna e salva (Ricaricare).
- Come dividere una lunga stringa di testo in parti
- Diversi modi per incollare il testo di celle diverse in una
- Utilizzo dell'operatore Mi piace per testare il testo rispetto a una maschera