Spis treści
Pisałem już o tym, jak szybko skleić tekst z kilku komórek w jedną i odwrotnie, przetworzyć długi ciąg tekstowy na komponenty. Przyjrzyjmy się teraz bliższemu, ale nieco bardziej złożonemu zadaniu – jak skleić tekst z kilku komórek, gdy spełniony jest określony warunek.
Załóżmy, że mamy bazę klientów, w której jedna nazwa firmy może odpowiadać kilku różnym e-mailom jej pracowników. Naszym zadaniem jest zebranie wszystkich adresów po nazwach firm i sklejenie ich (oddzielone przecinkami lub średnikami) w celu stworzenia np. listy mailingowej dla klientów, czyli np.:
Innymi słowy, potrzebujemy narzędzia, które sklei (połączy) tekst zgodnie z warunkiem – analogia funkcji SUMMESLI (SUMA.JEŻELI), ale dla tekstu.
Metoda 0. Formuła
Niezbyt elegancki, ale najłatwiejszy sposób. Możesz napisać prostą formułę, która sprawdzi, czy firma w kolejnym wierszu różni się od poprzedniej. Jeśli się nie różni, sklej następny adres oddzielony przecinkiem. Jeśli jest inny, „resetujemy” zgromadzone, zaczynając od nowa:
Wady tego podejścia są oczywiste: ze wszystkich uzyskanych komórek dodatkowej kolumny potrzebujemy tylko ostatnich dla każdej firmy (żółtych). Jeśli lista jest duża, to aby szybko je wybrać, będziesz musiał dodać kolejną kolumnę za pomocą funkcji DLSTR (DŁ), sprawdzając długość zgromadzonych ciągów:
Teraz możesz je odfiltrować i skopiować niezbędne sklejenie adresów do dalszego użytku.
Metoda 1. Makrofunkcja klejenia według jednego warunku
Jeśli oryginalna lista nie jest posortowana według firmy, powyższa prosta formuła nie działa, ale możesz łatwo obejść się za pomocą małej niestandardowej funkcji w VBA. Otwórz Edytor Visual Basic, naciskając skrót klawiaturowy Alt + F11 lub za pomocą przycisku Visual Basic Zakładka wywoływacz (Deweloper). W oknie, które się otworzy, wstaw nowy pusty moduł przez menu Wstaw – moduł i skopiuj tam tekst naszej funkcji:
Funkcja MergeIf(TextRange As Range, SearchRange As Range, Condition As String) Dim Delimeter As String, i As Long Delimeter = ", " sklejenia nie są sobie równe - wychodzimy z błędem If SearchRange.Count <> TextRange.Count Następnie MergeIf = CVErr(xlErrRef) Zakończ funkcję End If 'przejrzyj wszystkie komórki, sprawdź warunek i zbierz tekst w zmiennej OutText For i = 1 To SearchRange. Cells.Count If SearchRange.Cells(i) Like Condition Then OutText = OutText & TextRange.Cells(i) & Delimeter Next i 'wyświetl wyniki bez ostatniego ogranicznika MergeIf = Left(OutText, Len(OutText) - Len(Delimeter)) End funkcjonować
Jeśli teraz wrócisz do Microsoft Excel, to na liście funkcji (przycisk fx na pasku formuły lub karcie Wzory – Wstaw funkcję) będzie można znaleźć naszą funkcję Połącz, jeśli w kategorii Określony przez użytkownika (Określony przez użytkownika). Argumenty funkcji są następujące:
Metoda 2. Połącz tekst według niedokładnego warunku
Jeśli zamienimy pierwszy znak w 13. linii naszego makra = do przybliżonego operatora meczu Jak, wtedy będzie można przeprowadzić klejenie przez niedokładne dopasowanie danych wyjściowych do kryterium wyboru. Na przykład, jeśli nazwę firmy można zapisać w różnych wariantach, to możemy je wszystkie sprawdzić i zebrać za pomocą jednej funkcji:
Obsługiwane są standardowe symbole wieloznaczne:
- gwiazdka (*) – oznacza dowolną liczbę dowolnych znaków (łącznie z ich brakiem)
- znak zapytania (?) – oznacza dowolny pojedynczy znak
- znak funta (#) – oznacza dowolną cyfrę (0-9)
Domyślnie operator Like rozróżnia wielkość liter, tzn. rozumie na przykład „Orion” i „orion” jako różne firmy. Aby zignorować wielkość liter, możesz dodać linię na samym początku modułu w edytorze Visual Basic Opcja Porównaj tekst, co spowoduje, że polubienie będzie niewrażliwe na wielkość liter.
W ten sposób można komponować bardzo złożone maski do sprawdzania warunków, na przykład:
- ?1##??777RUS – wybór wszystkich tablic rejestracyjnych regionu 777, zaczynając od 1
- LLC* – wszystkie firmy, których nazwa zaczyna się na LLC
- ##7## – wszystkie produkty z pięciocyfrowym kodem cyfrowym, gdzie trzecia cyfra to 7
- ????? – wszystkie nazwy pięcioliterowe itp.
Metoda 3. Funkcja makro do sklejania tekstu w dwóch warunkach
W pracy może wystąpić problem, gdy trzeba powiązać tekst z więcej niż jednym warunkiem. Na przykład wyobraźmy sobie, że w naszej poprzedniej tabeli została dodana jeszcze jedna kolumna z miastem, a klejenie należy wykonać nie tylko dla danej firmy, ale również dla danego miasta. W takim przypadku nasza funkcja będzie musiała zostać nieco unowocześniona poprzez dodanie do niej kolejnego sprawdzenia zakresu:
Funkcja MergeIfs(TextRange As Range, SearchRange1 As Range, Warunek1 As String, SearchRange2 As Range, Condition2 As String) Dim Delimeter As String, i As Long Delimeter = ", " 'znaki separatora (można zastąpić spacją lub ; itp.) e.) 'jeśli zakresy walidacji i sklejania nie są sobie równe, zakończ z błędem If SearchRange1.Count <> TextRange.Count Lub SearchRange2.Count <> TextRange.Count Then MergeIfs = CVErr(xlErrRef) Exit Function End If „przejrzyj wszystkie komórki, sprawdź wszystkie warunki i zbierz tekst do zmiennej 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 'wyświetl wyniki bez ostatniego separatora MergeIfs = Left(OutText, Len(OutText) - Len(Delimeter)) End Function
Zostanie on zastosowany dokładnie w ten sam sposób – teraz tylko argumenty muszą zostać określone więcej:
Metoda 4. Grupowanie i sklejanie w Power Query
Możesz rozwiązać problem bez programowania w VBA, jeśli korzystasz z bezpłatnego dodatku Power Query. W przypadku programu Excel 2010-2013 można go pobrać tutaj, a w programie Excel 2016 jest już domyślnie wbudowany. Kolejność działań będzie następująca:
Power Query nie wie, jak pracować ze zwykłymi tabelami, więc pierwszym krokiem jest przekształcenie naszej tabeli w „inteligentną”. Aby to zrobić, wybierz go i naciśnij kombinację Ctrl+T lub wybierz z zakładki Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę). Na karcie, która się wtedy pojawi Konstruktor (Projekt) możesz ustawić nazwę tabeli (zostawiłem standard Tabela 1):
Teraz załadujmy naszą tabelę do dodatku Power Query. W tym celu w zakładce Dane (jeśli masz Excel 2016) lub na karcie Power Query (jeśli masz Excel 2010-2013) kliknij Ze stołu (Dane — z tabeli):
W otwartym oknie edytora zapytań wybierz kolumnę, klikając nagłówek O nas i naciśnij przycisk powyżej Zarządzanie (Grupuj według). Wpisz nazwę nowej kolumny i rodzaj operacji w grupowaniu – Wszystkie linie (Wszystkie rzędy):
Klikamy OK i otrzymujemy minitabelkę pogrupowanych wartości dla każdej firmy. Zawartość tabel jest wyraźnie widoczna po kliknięciu lewym przyciskiem myszy na białe tło komórek (nie na tekst!) w wynikowej kolumnie:
Dodajmy teraz jeszcze jedną kolumnę, w której za pomocą funkcji sklejamy zawartość kolumn Adres w każdej z minitabel, oddzieloną przecinkami. W tym celu w zakładce Dodaj kolumnę naciskamy Kolumna niestandardowa (Dodaj kolumnę — Kolumna niestandardowa) i w wyświetlonym oknie wprowadź nazwę nowej kolumny i formułę sprzężenia w języku M wbudowanym w Power Query:
Zauważ, że wszystkie funkcje M rozróżniają wielkość liter (w przeciwieństwie do Excela). Po kliknięciu OK otrzymujemy nową kolumnę z doklejonymi adresami:
Pozostaje usunąć już niepotrzebną kolumnę Adresy tabeli (kliknij prawym przyciskiem na tytule) Usuń kolumnę) i prześlij wyniki do arkusza klikając na zakładkę Strona główna — Zamknij i pobierz (Strona główna — Zamknij i załaduj):
Ważny niuans: W przeciwieństwie do poprzednich metod (funkcji) tabele z dodatku Power Query nie są aktualizowane automatycznie. Jeśli w przyszłości nastąpią jakiekolwiek zmiany w danych źródłowych, będziesz musiał kliknąć prawym przyciskiem myszy w dowolnym miejscu tabeli wyników i wybrać polecenie Zaktualizuj i zapisz (Odświeżać).
- Jak podzielić długi ciąg tekstowy na części?
- Kilka sposobów na sklejenie tekstu z różnych komórek w jedną
- Używanie operatora Like do testowania tekstu z maską