Innehåll
Jag skrev redan om hur du snabbt kan limma text från flera celler till en och omvänt analysera en lång textsträng till komponenter. Låt oss nu titta på en nära, men lite mer komplex uppgift - hur man limmar text från flera celler när ett visst specificerat villkor är uppfyllt.
Låt oss säga att vi har en databas med kunder, där ett företagsnamn kan motsvara flera olika mejl från dess anställda. Vår uppgift är att samla alla adresser efter företagsnamn och sammanfoga dem (avgränsade med kommatecken eller semikolon) för att till exempel göra en e-postlista för kunder, dvs få ut något i stil med:
Med andra ord behöver vi ett verktyg som limmar (länkar) texten enligt villkoret - en analog till funktionen SUMMESLI (SUMIF), men för text.
Metod 0. Formel
Inte särskilt elegant, men det enklaste sättet. Du kan skriva en enkel formel som kontrollerar om företaget i nästa rad skiljer sig från det föregående. Om det inte skiljer sig, limma sedan nästa adress avgränsad med kommatecken. Om det skiljer sig "återställer" vi det ackumulerade och börjar igen:
Nackdelarna med detta tillvägagångssätt är uppenbara: från alla celler i den extra kolumnen som erhålls behöver vi bara de sista för varje företag (gul). Om listan är stor måste du lägga till ytterligare en kolumn med funktionen för att snabbt kunna välja dem DLSTR (LEN), kontrollera längden på de ackumulerade strängarna:
Nu kan du filtrera bort de och kopiera den nödvändiga adresslimningen för vidare användning.
Metod 1. Makrofunktion av limning med ett villkor
Om originallistan inte är sorterad efter företag så fungerar inte ovanstående enkla formel, men du kan enkelt ta dig runt med en liten anpassad funktion i VBA. Öppna Visual Basic Editor genom att trycka på ett kortkommando Alt + F11 eller med knappen Visual Basic fliken utvecklare (Utvecklaren). I fönstret som öppnas, infoga en ny tom modul genom menyn Infoga – Modul och kopiera texten till vår funktion där:
Funktion MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " limningar är inte lika med varandra - vi avslutar med ett fel Om SearchRange.Count <> TextRange.Count Då MergeIf = CVErr(xlErrRef) Avsluta Funktion End Om 'gå igenom alla celler, kontrollera villkoret och samla in texten i variabeln OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Nästa i 'visa resultat utan sista avgränsare MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End fungera
Om du nu återgår till Microsoft Excel, sedan i listan över funktioner (knapp fx i formelfältet eller fliken Formler – Infoga funktion) kommer det att vara möjligt att hitta vår funktion MergeIf i kategori Användardefinierad (Användardefinierad). Argumenten till funktionen är följande:
Metod 2. Sammanfoga text efter inexakt villkor
Om vi byter ut det första tecknet i den 13:e raden i vårt makro = till den ungefärliga matchoperatören Tycka om, då kommer det att vara möjligt att utföra limning genom en inexakt matchning av initialdata med urvalskriteriet. Till exempel, om företagsnamnet kan skrivas i olika varianter, då kan vi kontrollera och samla in dem alla med en funktion:
Standard jokertecken stöds:
- asterisk (*) – anger valfritt antal tecken (inklusive deras frånvaro)
- frågetecken (?) – står för varje enskilt tecken
- pundtecken (#) – står för valfri siffra (0-9)
Som standard är Like-operatören skiftlägeskänslig, dvs förstår till exempel "Orion" och "orion" som olika företag. För att ignorera skiftläge kan du lägga till raden i början av modulen i Visual Basic-redigeraren Alternativ Jämför text, vilket kommer att ändra Like till att vara skiftlägesokänslig.
På detta sätt kan du komponera mycket komplexa masker för kontroll av förhållanden, till exempel:
- ?1##??777RUS – urval av alla registreringsskyltar i 777-regionen, börjar med 1
- LLC* – alla företag vars namn börjar med LLC
- ##7## – alla produkter med en femsiffrig digital kod, där den tredje siffran är 7
- ????? – alla namn på fem bokstäver osv.
Metod 3. Makrofunktion för limning av text under två förhållanden
I arbetet kan det uppstå ett problem när du behöver länka texten mer än ett villkor. Låt oss till exempel föreställa oss att i vår tidigare tabell lades en kolumn till med staden, och limning bör utföras inte bara för ett visst företag, utan också för en viss stad. I det här fallet måste vår funktion moderniseras något genom att lägga till en annan intervallkontroll till den:
Funktion MergeIfs(TextRange As Range, SearchRange1 As Range, Condition1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'avgränsartecken (kan ersättas med mellanslag eller ; etc.) e.) 'om validerings- och limningsintervallen inte är lika med varandra, avsluta med ett fel If SearchRange1.Count <> TextRange.Count Eller SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If 'gå igenom alla celler, kontrollera alla villkor och samla in texten i variabeln 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 'visa resultat utan sista avgränsare MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Det kommer att tillämpas på exakt samma sätt – bara argument behöver nu specificeras mer:
Metod 4. Gruppering och limning i Power Query
Du kan lösa problemet utan att programmera i VBA, om du använder det kostnadsfria Power Query-tillägget. För Excel 2010-2013 kan den laddas ner här, och i Excel 2016 är den redan inbyggd som standard. Sekvensen av åtgärder kommer att vara följande:
Power Query vet inte hur man arbetar med vanliga tabeller, så det första steget är att göra vår tabell till en "smart". För att göra detta, välj den och tryck på kombinationen ctrl+T eller välj från fliken Hem – Formatera som en tabell (Hem — Formatera som tabell). På fliken som då dyker upp Konstruktör (Design) du kan ställa in tabellnamnet (jag lämnade standarden Tabell 1):
Låt oss nu ladda vår tabell i Power Query-tillägget. För att göra detta, på fliken Data (om du har Excel 2016) eller på fliken Power Query (om du har Excel 2010-2013) klickar du på Från bordet (Data – från tabell):
I frågeredigeringsfönstret som öppnas väljer du kolumnen genom att klicka på rubriken Företag och tryck på knappen ovan Grupp (Grupp av). Ange namnet på den nya kolumnen och typen av operation i grupperingen – Alla rader (Alla rader):
Klicka på OK så får vi en minitabell med grupperade värden för varje företag. Innehållet i tabellerna är tydligt synligt om du vänsterklickar på den vita bakgrunden i cellerna (inte på texten!) i den resulterande kolumnen:
Låt oss nu lägga till en kolumn till, där vi, med hjälp av funktionen, limmar innehållet i adresskolumnerna i var och en av minitabellerna, separerade med kommatecken. För att göra detta, på fliken Lägg till kolumn vi trycker på Anpassad kolumn (Lägg till kolumn – Anpassad kolumn) och i fönstret som visas anger du namnet på den nya kolumnen och kopplingsformeln på M-språket som är inbyggt i Power Query:
Observera att alla M-funktioner är skiftlägeskänsliga (till skillnad från Excel). Efter att ha klickat på OK vi får en ny kolumn med limmade adresser:
Det återstår att ta bort den redan onödiga kolumnen Tabelladresser (högerklicka på rubriken) Ta bort kolumn) och ladda upp resultaten till arket genom att klicka på fliken Hem — Stäng och ladda ner (Hem — Stäng och ladda):
Viktig nyans: Till skillnad från de tidigare metoderna (funktionerna) uppdateras inte tabeller från Power Query automatiskt. Om det i framtiden kommer att ske några ändringar i källdata, måste du högerklicka var som helst i resultattabellen och välja kommandot Uppdatera & spara (Uppdatera).
- Hur man delar upp en lång textsträng i delar
- Flera sätt att limma text från olika celler till en
- Använda Like-operatorn för att testa text mot en mask