Spis treści
Wyszukiwanie słów kluczowych w tekście źródłowym to jedno z najczęstszych zadań podczas pracy z danymi. Spójrzmy na jego rozwiązanie na kilka sposobów, korzystając z następującego przykładu:
Załóżmy, że ty i ja mamy listę słów kluczowych – nazw marek samochodów – i dużą tabelę wszelkiego rodzaju części zamiennych, gdzie opisy mogą czasem zawierać jedną lub kilka takich marek na raz, jeśli część zamienna pasuje do więcej niż jednej marka samochodu. Naszym zadaniem jest znalezienie i wyświetlenie wszystkich wykrytych słów kluczowych w sąsiednich komórkach poprzez dany znak separatora (na przykład przecinek).
Metoda 1. Power Query
Oczywiście najpierw zamieniamy nasze tabele na dynamiczne („inteligentne”) za pomocą skrótu klawiaturowego Ctrl+T lub komendy Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę)nadaj im nazwy (np Znaczkiи Części zamienne) i ładuj jeden po drugim do edytora dodatku Power Query, wybierając na karcie Dane — z tabeli/zakresu (Dane — z tabeli/zakresu). Jeśli masz starsze wersje programu Excel 2010-2013, w których dodatek Power Query jest zainstalowany jako osobny dodatek, żądany przycisk będzie na karcie Zapytanie o moc. Jeśli masz zupełnie nową wersję programu Excel 365, to przycisk Z tabeli/zakresu dzwonił tam teraz Z liśćmi (z arkusza).
Po załadowaniu każdej tabeli w Power Query wracamy z powrotem do Excela za pomocą polecenia Strona główna — Zamknij i wczytaj — Zamknij i wczytaj do… — Tylko utwórz połączenie (Dom — Zamknij i załaduj — Zamknij i załaduj do… — Tylko utwórz połączenie).
Teraz utwórzmy zduplikowane żądanie Części zamienneklikając go prawym przyciskiem myszy i wybierając Zduplikowane żądanie (Zduplikowane zapytanie), a następnie zmień nazwę wynikowego żądania kopiowania na Wyniki i będziemy z nim dalej pracować.
Logika działań jest następująca:
- Na karcie Zaawansowane Dodawanie kolumny wybierz drużynę Kolumna niestandardowa (Dodaj kolumnę — Kolumna niestandardowa) i wprowadź formułę = Marki. Po kliknięciu OK otrzymamy nową kolumnę, gdzie w każdej komórce będzie zagnieżdżona tabela z listą naszych słów kluczowych – marek samochodów:
- Użyj przycisku z podwójnymi strzałkami w nagłówku dodanej kolumny, aby rozwinąć wszystkie zagnieżdżone tabele. W tym samym czasie wiersze z opisami części zamiennych pomnożą się przez wielokrotność liczby marek i otrzymamy wszystkie możliwe pary-kombinacje „część zamienna-marka”:
- Na karcie Zaawansowane Dodawanie kolumny wybierz drużynę Kolumna warunkowa (kolumna warunkowa) oraz ustawić warunek sprawdzania występowania słowa kluczowego (marka) w tekście źródłowym (opis części):
- Aby wyszukiwanie nie uwzględniało wielkości liter, ręcznie dodaj trzeci argument na pasku formuły Porównaj.OrdinalIgnoreCase do funkcji sprawdzania wystąpień Tekst.Zawiera (jeśli pasek formuły nie jest widoczny, można go włączyć na karcie Review):
- Filtrujemy wynikową tabelę, pozostawiając tylko te w ostatniej kolumnie, czyli pasujące i usuwamy zbędną kolumnę zdarzenia.
- Grupowanie identycznych opisów za pomocą polecenia Grupuj według Zakładka Transformacja (Przekształć — Grupuj według). Wybierz jako operację agregacji Wszystkie linie (Wszystkie rzędy). Na wyjściu otrzymujemy kolumnę z tabelami, która zawiera wszystkie szczegóły dotyczące każdej części zamiennej, w tym marki producentów samochodów, których potrzebujemy:
- Aby wyodrębnić oceny dla każdej części, dodaj kolejną kolumnę obliczeniową na karcie Dodawanie kolumny – niestandardowa kolumna (Dodaj kolumnę — Kolumna niestandardowa) i użyj formuły składającej się z tabeli (znajdują się one w naszej kolumnie Szczegóły) i nazwę wyodrębnionej kolumny:
- Klikamy przycisk z podwójnymi strzałkami w nagłówku wynikowej kolumny i wybieramy polecenie Wyodrębnij wartości (Wyodrębnij wartości)aby wydrukować stemple z dowolnym znakiem ogranicznika:
- Usuwanie niepotrzebnej kolumny Szczegóły.
- Aby dodać do wynikowej tabeli części, które z niej zniknęły, gdzie w opisach nie znaleziono marek, wykonujemy procedurę łączenia zapytania Wynik z pierwotną prośbą Części zamienne przycisk Połączyć Zakładka Strona główna (Strona główna — Scal zapytania). Rodzaj połączenia - Połączenie zewnętrzne w prawo (Prawe sprzężenie zewnętrzne):
- Pozostaje tylko usunąć dodatkowe kolumny i zmienić nazwę-przenieść pozostałe – i nasze zadanie jest rozwiązane:
Metoda 2. Formuły
Jeśli masz wersję Excel 2016 lub nowszą, to nasz problem można rozwiązać w bardzo zwarty i elegancki sposób za pomocą nowej funkcji POŁĄCZYĆ (TEKSTJOIN):
Logika tej formuły jest prosta:
- Funkcjonować SZUKAJ (ODNALEŹĆ) wyszukuje po kolei występowanie każdej marki w bieżącym opisie części i zwraca albo numer seryjny symbolu, od którego została znaleziona marka, albo błąd #ARG! jeśli marka nie jest w opisie.
- Następnie za pomocą funkcji IF (JEŚLI) и EOSZIBKA (BŁĄD) błędy zastępujemy pustym ciągiem tekstowym „”, a liczebniki porządkowe znaków samymi nazwami marek.
- Otrzymana tablica pustych komórek i znalezionych marek jest składana w pojedynczy ciąg przez dany znak separatora za pomocą funkcji POŁĄCZYĆ (TEKSTJOIN).
Porównanie wydajności i buforowanie zapytań Power Query w celu przyspieszenia
Do testowania wydajności weźmy jako dane początkowe tabelę zawierającą 100 opisów części zamiennych. Otrzymujemy na nim następujące wyniki:
- Czas przeliczenia według wzorów (Metoda 2) – 9 sek. przy pierwszym kopiowaniu formuły do całej kolumny i 2 sek. przy powtarzaniu (prawdopodobnie wpływa na buforowanie).
- Czas aktualizacji zapytania Power Query (Metoda 1) jest znacznie gorszy – 110 sekund.
Oczywiście wiele zależy od sprzętu konkretnego komputera, zainstalowanej wersji pakietu Office i aktualizacji, ale myślę, że ogólny obraz jest jasny.
Aby przyspieszyć zapytanie dodatku Power Query, buforujmy tabelę odnośników Znaczki, ponieważ nie zmienia się w trakcie wykonywania zapytania i nie jest konieczne jego ciągłe przeliczanie (co de facto robi Power Query). W tym celu używamy funkcji Tabela. Bufor z wbudowanego języka Power Query M.
Aby to zrobić, otwórz kwerendę Wyniki i na karcie Review naciśnij przycisk Zaawansowany edytor (Widok — Edytor zaawansowany). W oknie, które zostanie otwarte, dodaj linię z nową zmienną Marka 2, który będzie buforowaną wersją naszego katalogu automaker, i użyjemy tej nowej zmiennej później w następującym poleceniu zapytania:
Po takim dopracowaniu szybkość aktualizacji naszego żądania wzrasta prawie 7-krotnie – do 15 sekund. Zupełnie co innego 🙂
- Wyszukiwanie rozmyte w Power Query
- Masowe zastępowanie tekstu formułami
- Masowe zastępowanie tekstu w dodatku Power Query za pomocą funkcji List.Accumulate