Niedawno zwrócił się do mnie znajomy z prośbą o pomoc w wygenerowaniu wszystkich możliwych fraz składających się z zestawu podanych słów. Tego rodzaju problemy mogą pojawić się podczas tworzenia list słów kluczowych i fraz dla reklamy online i promocji SEO, gdy trzeba przejrzeć wszystkie możliwe kombinacje słów w zapytaniu:
W matematyce ta operacja nazywa się Produkt kartezjański. Oficjalna definicja jest następująca: iloczyn kartezjański zbiorów A i B jest zbiorem wszystkich par, których pierwszy składnik należy do zbioru A, a drugi składnik należy do zbioru B. Ponadto elementy zbiorów mogą być zarówno numery i tekst.
W tłumaczeniu na język ludzki oznacza to, że jeśli w zbiorze A mamy np. słowa „biały” i „czerwony”, a w zbiorze B „BMW” i „Mercedes”, to po iloczynie kartezjańskim tych dwóch zbiorów get on output to zbiór wszystkich możliwych wariantów fraz, złożony ze słów obu list:
- białe bmw
- czerwone bmw
- biały Mercedes
- czerwony mercedes
… czyli dokładnie to, czego potrzebujemy. Przyjrzyjmy się kilku sposobom rozwiązania tego zadania w programie Excel.
Metoda 1. Formuły
Zacznijmy od formuł. Załóżmy, że jako dane początkowe mamy trzy listy oryginalnych słów odpowiednio w kolumnach A, B i C, a liczba elementów w każdej liście może być różna:
Najpierw utwórzmy trzy kolumny z indeksami, czyli liczbami porządkowymi słów z każdej listy we wszystkich możliwych kombinacjach. Pierwszy rząd jednostek (E2:G2) zostanie wprowadzony ręcznie, a dla reszty użyjemy następującego wzoru:
Logika tutaj jest prosta: jeśli indeks w poprzedniej komórce nadrzędnej osiągnął już koniec listy, czyli jest równy liczbie elementów na liście obliczonej przez funkcję COUNT (ILE.LICZB), następnie wznawiamy numerację. W przeciwnym razie zwiększamy indeks o 1. Zwróć szczególną uwagę na sprytne ustalanie zakresów za pomocą znaków dolara ($), tak aby można było skopiować formułę w dół iw prawo.
Teraz, gdy mamy liczby porządkowe słów, których potrzebujemy z każdej listy, możemy wyodrębnić same słowa za pomocą funkcji INDEKS (INDEKS) na trzy oddzielne kolumny:
Jeśli wcześniej nie spotkałeś się z tą funkcją w swojej pracy, to zdecydowanie radzę przestudiować ją przynajmniej po przekątnej – pomaga w wielu sytuacjach i jest nie mniej (a nawet bardziej!) przydatna! VPR (WYSZUKAJ.PIONOWO).
Cóż, po tym pozostaje tylko skleić powstałe fragmenty linia po linii za pomocą symbolu konkatenacji (&):
… lub (jeśli masz najnowszą wersję Excela) z przydatną funkcją POŁĄCZYĆ (TEKSTJOIN), który może skleić całą zawartość określonych komórek poprzez podany znak separatora (spację):
Metoda 2. Poprzez Power Query
Power Query to zaawansowany dodatek do programu Microsoft Excel, który wykonuje dwa główne zadania: 1. ładowanie danych do programu Excel z niemal dowolnego źródła zewnętrznego oraz 2. wszelkiego rodzaju przekształcenia załadowanych tabel. Dodatek Power Query jest już wbudowany w program Excel 2016–2019, a w przypadku programu Excel 2010–2013 jest instalowany jako osobny dodatek (można go bezpłatnie pobrać z oficjalnej witryny firmy Microsoft). Jeśli nie zacząłeś jeszcze korzystać z dodatku Power Query w swojej pracy, czas się nad tym zastanowić, ponieważ przekształcenia, takie jak opisane powyżej, wykonuje się tam łatwo i naturalnie, wykonując zaledwie kilka ruchów.
Najpierw załadujmy listy źródłowe jako osobne zapytania w dodatku Power Query. Aby to zrobić, dla każdej tabeli wykonaj następujące czynności:
- Zmieńmy stoliki w „inteligentne” za pomocą przycisku Formatuj jako tabelę Zakładka Strona główna (Strona główna — Formatuj jako tabelę) lub skrót klawiaturowy Ctrl+T. Każda tabela automatycznie otrzyma nazwę Tabela1,2,3…, który jednak można zmienić w razie potrzeby na karcie Konstruktor (Projekt).
- Po ustawieniu aktywnej komórki w tabeli naciśnij przycisk Ze stołu (Z tabeli) Zakładka Dane (Data) lub na karcie Zapytanie o moc (jeśli masz go zainstalowany jako osobny dodatek do programu Excel 2010-2013).
- W otwartym oknie edytora zapytań wybierz polecenie Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do...) a potem opcja Po prostu stwórz połączenie (Utwórz tylko połączenie). Spowoduje to pozostawienie załadowanej tabeli w pamięci i umożliwi dostęp do niej w przyszłości.
Jeśli zrobisz wszystko poprawnie, to wyjściem w prawym panelu powinny być trzy żądania w trybie Tylko połączenie z naszymi nazwami stołów:
Teraz kliknij prawym przyciskiem myszy pierwsze zapytanie i wybierz polecenie Połączyć (Odniesienie)aby utworzyć jego aktualizowalną kopię, a następnie dodać dodatkową kolumnę do danych za pomocą polecenia Dodawanie kolumny ž – Kolumna niestandardowa (Dodaj kolumnę -ž niestandardową kolumnę). W oknie wprowadzania formuły wprowadź nazwę nowej kolumny (na przykład Fragment2) i niezwykle proste wyrażenie jako formułę:
=Tabela2
… czyli innymi słowy nazwa drugiego zapytania:
Po kliknięciu OK zobaczymy nową kolumnę, w każdej komórce będzie zagnieżdżona tabela z frazami z drugiej tabeli (możesz zobaczyć zawartość tych tabel klikając w tło komórki obok słowa Stół):
Pozostaje rozwinąć całą zawartość tych zagnieżdżonych tabel za pomocą przycisku z podwójnymi strzałkami w nagłówku wynikowej kolumny i odznaczenie Użyj oryginalnej nazwy kolumny jako przedrostka (Użyj oryginalnej nazwy kolumny jako prefiksu):
… i otrzymujemy wszystkie możliwe kombinacje elementów z dwóch pierwszych zestawów:
Co więcej, wszystko jest podobne. Dodaj kolejną kolumnę obliczeniową ze wzorem:
=Tabela3
…, a następnie ponownie rozwiń zagnieżdżone tabele – i teraz mamy już wszystkie możliwe opcje permutacji słów z trzech zestawów, odpowiednio:
Pozostaje wybrać wszystkie trzy kolumny od lewej do prawej, przytrzymując Ctrl, i połącz ich zawartość oddzieloną spacjami za pomocą polecenia Scal kolumny (Połącz kolumny) z zakładki Transformacja (Przekształcać):
Otrzymane wyniki można przenieść z powrotem na arkusz za pomocą znanego już polecenia Strona główna — Zamknij i załaduj — Zamknij i załaduj w… (Strona główna — Zamknij i załaduj — Zamknij i załaduj do...):
Jeśli w przyszłości coś zmieni się w naszych tabelach źródłowych z fragmentami, wystarczy zaktualizować wygenerowane zapytanie, klikając prawym przyciskiem myszy na wynikową tabelę i wybierając polecenie Zaktualizuj i zapisz (Odświeżać) lub naciskając skrót klawiaturowy Ctrl+inny+F5.
- Co to są Power Query, Power Pivot, Power Map i Power BI i dlaczego potrzebują użytkownika programu Excel
- Tworzenie wykresu Gantta w dodatku Power Query
- 5 sposobów korzystania z funkcji INDEX