Kategoryzacja ciągu na podstawie niektórych słów była jednym z moich podstawowych zadań w analizie danych. Na przykład w ankiecie, jeśli zapytasz ludzi, co im się podoba w konkretnym smartfonie, te same odpowiedzi będą zawierały różne słowa. W przypadku aparatu mogą używać słów takich jak zdjęcia, filmy, selfie itp. Wszystkie oznaczają aparat. Dlatego bardzo ważne jest, aby wcześniej kategoryzować zdania, aby uzyskać sensowne informacje.
W tym artykule dowiemy się, jak kategoryzować w Excelu za pomocą słów kluczowych.
Weźmy przykład ankiety, o której mówiliśmy.
Przykład: kategoryzowanie danych zebranych z ankiety w programie Excel
Dlatego przeprowadziliśmy ankietę na temat naszego nowego smartfona xyz. Zapytaliśmy naszych klientów, co im się podoba w telefonie xyz, i przechwyciliśmy ich odpowiedź w programie Excel. Teraz musimy wiedzieć, komu podobał się nasz ekran LED, głośnik i aparat.
W tym celu przygotowaliśmy listę słów kluczowych, które mogą odnosić się do kategorii, jak widać na poniższym obrazku. Aby zrozumieć, zachowałem to małe.
Informacje zwrotne znajdują się w zakresie A2:A9, słowa kluczowe w E2:E10, a Kategoria w F2:F10.
Ogólna formuła tworzenia kategorii będzie wyglądać następująco:
{=INDEKS(Kategoria,POZYCJĘ(PRAWDA,CZY.LICZBA(SZUKAJ(słowa,zdanie))),0))}
Zwróć uwagę na nawiasy klamrowe, jest to formuła tablicowa. Należy wprowadzić za pomocą klawiszy CTRL+SHIFT+ENTER.
Kategoria: Jest to zakres zawierający listę kategorii. Zdania lub informacje zwrotne będą kategoryzowane według tych wartości. W naszym przypadku jest to F2:F10.
Słowa: jest to zakres zawierający listę słów kluczowych lub fraz. Będą one przeszukiwane w zdania. Oto E2:E10.
Zdanie: to zdanie zostanie skategoryzowane. Jest to pojedyncza komórka względna.
Skoro już znamy każdą zmienną i funkcję służącą do kategoryzacji w Excelu, zaimplementujmy to na naszym przykładzie.
W komórce B2 wpisz tę formułę i naciśnij CTRL+SHIFT+ENTER.
{=INDEKS($F$2:$F$10,MATCH(PRAWDA,IST.LICZBA(SZUKAJ($E$2:$E$10,A2)),0))}
Skopiuj formułę, aby zobaczyć kategorię każdej opinii.
Musimy ustalić listę słów i kategorii, nie powinny się one zmieniać, gdy kopiujemy formułę, dlatego podałem bezwzględne odniesienie słów kluczowych i kategorii. Chociaż chcemy, aby zdania zmieniały się podczas kopiowania formuły, dlatego użyłem względnego odniesienia jako A2. Możesz przeczytać o odwoływaniu się w programie Excel tutaj.
Teraz możesz przygotować raport ilu użytkowników polubiło ekran LED, głośnik i kamerę.
Jak to działa?
Rdzeniem formuły jest CZY.LICZBA(SZUKAJ($2$:$10$,A2)): Wyjaśniłem to szczegółowo tutaj. Funkcja SEARCH przeszukuje każdą wartość słów kluczowych ($2$:$10$) w zdaniu A2. Zwraca tablicę znalezionej lokalizacji słowa lub #WARTOŚĆ (dla słowa nie znaleziono). Na koniec będziemy mieli tablicę 9 elementów dla tego przykładu. {#WARTOŚĆ!;5;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!;#WARTOŚĆ!}. Następnie używamy funkcji ISNUMBER, aby przekonwertować tę tablicę na przydatne dane. Konwertuje go na tablicę TRUE i FALSE. {FAŁSZ;PRAWDA;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}.
Teraz wszystko jest prostym dopasowaniem indeksu. MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0): funkcja MATCH szuka PRAWDA w tablicy wynikowej i zwraca indeks pierwszej znalezionej PRAWDA. czyli 2 w tym przypadku.
INDEKS($ F $2: $ F $ 10,MATCH(TRUE,ISNUMBER(SEARCH($E$2:$E$10,A2)),0)): Następnie funkcja INDEX sprawdza drugą pozycję w kategorii ($ F$2:$ F$ 10) który jest ekranem LED. Wreszcie ta formuła klasyfikuje ten tekst lub informację zwrotną jako ekran LED.
Rozróżnianie wielkości liter:
Aby funkcja rozróżniała wielkość liter, użyj funkcji ZNAJDŹ zamiast funkcji SZUKAJ. Funkcja ZNAJDŹ domyślnie rozróżnia wielkość liter.
{=INDEKS(Kategoria, DOPASUJ(PRAWDA,CZY.LICZBA(ZNAJDŹ()słowa,zdanie)),0))}
Słabe punkty:
1.Jeżeli dwa słowa kluczowe zostaną znalezione w tym samym zdaniu, zdanie zostanie podzielone według pierwszego słowa kluczowego na liście.
Przechwytywanie tekstu w innym słowie. Załóżmy, że szukamy LAD w zakresie. Następnie zostaną policzone słowa zawierające LAD. Na przykład drabiny będą liczone jako LAD, ponieważ zawierają w sobie LAD. Więc bądź ostrożny. Najlepszą praktyką jest jak największa normalizacja danych.
Był to więc krótki samouczek o tym, jak kategoryzować dane w programie Excel. Próbowałem to wyjaśnić tak prosto, jak tylko potrafiłem. Daj mi znać, jeśli masz jakiekolwiek wątpliwości dotyczące tego artykułu lub artykułów związanych z programem Excel.
Pobieranie pliku:
Kategoryzuj ciąg na podstawie słów kluczowychJak sprawdzić, czy komórka zawiera określony tekst w programie Excel?
Jak sprawdzić listę tekstów w ciągu w programie Excel?
Uzyskaj zgodność dwóch kryteriów LICZNIKI w programie Excel
Uzyskaj LICZNIKI z LUB dla wielu kryteriów w programie Excel
Popularne artykuły :
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.