Masowe zastępowanie tekstu formułami

Załóżmy, że masz listę, na której z różnym stopniem „prostoności” zapisane są dane początkowe – na przykład adresy lub nazwy firm:

Widać wyraźnie, że to samo miasto lub firma występuje tutaj w pstrokatych wariantach, co oczywiście w przyszłości przysporzy sporo problemów przy pracy z tymi stołami. A jeśli trochę się zastanowisz, możesz znaleźć wiele przykładów podobnych zadań z innych dziedzin.

Teraz wyobraź sobie, że takie krzywe dane docierają do Ciebie regularnie, tzn. nie jest to jednorazowa historia „napraw to ręcznie, zapomnij”, ale problem na bieżąco i w dużej liczbie komórek.

Co robić? Nie zastępuj ręcznie krzywego tekstu 100500 razy poprawnym tekstem za pomocą pola „Znajdź i zamień” lub klikając Ctrl+H?

Pierwszą rzeczą, jaka przychodzi do głowy w takiej sytuacji, jest dokonanie wymiany masy zgodnie z wcześniej skompilowaną książką odniesienia dopasowania błędnych i poprawnych opcji – tak:

Niestety, przy oczywistej przewadze takiego zadania, Microsoft Excel nie ma prostych wbudowanych metod jego rozwiązania. Na początek zastanówmy się, jak to zrobić za pomocą formuł bez angażowania „ciężkiej artylerii” w postaci makr w VBA lub Power Query.

Przypadek 1. Pełna wymiana luzem

Zacznijmy od stosunkowo prostego przypadku – sytuacji, w której trzeba wymienić stary krzywy tekst na nowy. w pełni.

Załóżmy, że mamy dwa stoły:

W pierwszym – oryginalne różnorodne nazwy firm. W drugim – księga podręczna korespondencji. Jeżeli w pierwszej tabeli znajdziemy w nazwie firmy dowolne słowo z kolumny Znaleźć, to trzeba całkowicie zastąpić tę krzywą nazwę poprawną – z kolumny namiastka druga tabela przeglądowa.

Dla wygody:

• Obie tabele są konwertowane na dynamiczne („inteligentne”) za pomocą skrótu klawiaturowego Ctrl+T lub zespół Wypełnij tabelę (Wypełnij tabelę).
  
• Na wyświetlonej karcie Konstruktor (Projekt) nazwa pierwszej tabeli Dane, a druga tabela referencyjna – Zastępstwa.

Aby wyjaśnić logikę formuły, odejdźmy trochę z daleka.

Biorąc za przykład pierwszą firmę z komórki A2 i chwilowo zapominając o pozostałych firmach spróbujmy ustalić, która opcja z kolumny Znaleźć spotyka się tam. Aby to zrobić, wybierz dowolną pustą komórkę w wolnej części arkusza i wprowadź tam funkcję ZNALEŹĆ (ODNALEŹĆ):

Funkcja ta określa, czy dany podciąg jest uwzględniony (pierwszy argument to wszystkie wartości z kolumny) Znaleźć) do tekstu źródłowego (pierwsza firma z tabeli danych) i powinna wyprowadzić albo numer porządkowy znaku, z którego znaleziono tekst, albo błąd, jeśli nie znaleziono podciągu.

Sztuczka polega na tym, że ponieważ jako pierwszy argument określiliśmy nie jedną, ale kilka wartości, funkcja ta zwróci w wyniku nie jedną wartość, ale tablicę 3 elementów. Jeśli nie masz najnowszej wersji Office 365 obsługującej tablice dynamiczne, to po wpisaniu tej formuły i kliknięciu Wchodzę zobaczysz tę tablicę bezpośrednio na arkuszu:

Jeśli masz poprzednie wersje programu Excel, to po kliknięciu Wchodzę zobaczymy tylko pierwszą wartość z tablicy wyników, czyli błąd #ARG! (#WARTOŚĆ!).

Nie powinieneś się bać 🙂 W rzeczywistości nasza formuła działa i nadal możesz zobaczyć całą tablicę wyników, jeśli wybierzesz wprowadzoną funkcję na pasku formuły i naciśniesz klawisz F9(tylko nie zapomnij nacisnąć Escaby wrócić do formuły):

Otrzymana tablica wyników oznacza, że ​​w oryginalnej krzywej nazwie firmy (GK Morozko OAO) wszystkich wartości w kolumnie Znaleźć znalazł tylko drugi (Morozko)i zaczynając od czwartego znaku z rzędu.

Dodajmy teraz funkcję do naszej formuły ZOBACZ(SPOJRZEĆ W GÓRĘ):

Ta funkcja ma trzy argumenty:

1. Pożądana wartość – możesz użyć dowolnej odpowiednio dużej liczby (najważniejsze, że przekracza długość dowolnego tekstu w danych źródłowych)
   
2. Wyświetlony_wektor – zakres lub tablica, w której szukamy pożądanej wartości. Oto wcześniej wprowadzona funkcja ZNALEŹĆ, który zwraca tablicę {#VALUE!:4:#VALUE!}
   
3. Wektor_wyniki – zakres, z którego chcemy zwrócić wartość, jeśli żądana wartość znajduje się w odpowiedniej komórce. Oto poprawne nazwy z kolumny namiastka nasza tabela referencyjna.
   

Główną i nieoczywistą cechą jest to, że funkcja ZOBACZ jeśli nie ma dokładnego dopasowania, zawsze szuka najbliższej najmniejszej (poprzedniej) wartości. Dlatego określając dowolną dużą liczbę (na przykład 9999) jako pożądaną wartość, wymusimy ZOBACZ znajdź komórkę z najbliższą najmniejszą liczbą (4) w tablicy {#WARTOŚĆ!:4:#WARTOŚĆ!} i zwróć odpowiednią wartość z wektora wynikowego, czyli poprawną nazwę firmy z kolumny namiastka.

Drugi niuans polega na tym, że technicznie nasza formuła jest formułą tablicową, ponieważ funkcja ZNALEŹĆ zwraca jako wynik nie jedną, ale tablicę trzech wartości. Ale ponieważ funkcja ZOBACZ obsługuje tablice po wyjęciu z pudełka, to nie musimy wprowadzać tej formuły jako klasycznej formuły tablicowej – za pomocą skrótu klawiaturowego Ctrl+Shift+Wchodzę. Wystarczy prosty Wchodzę.

To wszystko. Mam nadzieję, że rozumiesz logikę.

Pozostaje przenieść gotową formułę do pierwszej komórki B2 kolumny Stały – a nasze zadanie rozwiązane!

Oczywiście przy zwykłych (nie sprytnych) stołach ta formuła również sprawdza się świetnie (tylko nie zapomnij o kluczu F4 i ustalenie odpowiednich linków):

Przypadek 2. Częściowa wymiana luzem

Ta sprawa jest trochę trudniejsza. Znowu mamy dwie „inteligentne” stoły:

Pierwsza tabela z krzywo napisanymi adresami do poprawienia (ja to nazwałem Data2). Druga tabela to książka referencyjna, zgodnie z którą należy dokonać częściowej zamiany podciągu wewnątrz adresu (nazwałem tę tabelę Zastępstwa2).

Zasadnicza różnica polega na tym, że trzeba podmienić tylko fragment oryginalnych danych – np. pierwszy adres ma błędny „Św. Petersburg” po prawej „Św. Petersburg”, pozostawiając resztę adresu (kod pocztowy, ulica, dom) bez zmian.

Gotowa formuła będzie wyglądać tak (dla łatwiejszej percepcji podzieliłem ją na ile linii używam inny+Wchodzę):

Główna praca jest tutaj wykonywana przez standardową funkcję tekstową Excel ZASTĄPIĆ (ZASTĄPIĆ), który ma 3 argumenty:

1. Tekst źródłowy – pierwszy krzywy adres z kolumny Adres
2. Czego szukamy – tutaj używamy triku z funkcją ZOBACZ (SPOJRZEĆ W GÓRĘ)z poprzedniego sposobu na pobranie wartości z kolumny Znaleźć, który jest zawarty jako fragment w zakrzywionym adresie.
3. Czym zastąpić – w ten sam sposób znajdujemy poprawną odpowiadającą mu wartość z kolumny namiastka.

Wprowadź tę formułę za pomocą Ctrl+Shift+Wchodzę tutaj również nie jest potrzebna, chociaż w rzeczywistości jest to formuła tablicowa.

I wyraźnie widać (patrz błędy #N/A na poprzednim rysunku), że taka formuła, przy całej swojej elegancji, ma kilka wad:

• Funkcjonować SUBSTITUTE rozróżnia wielkość liter, więc „Spb” w przedostatnim wierszu nie został znaleziony w tabeli zamian. Aby rozwiązać ten problem, możesz użyć funkcji ZAMENIT (ZASTĄPIĆ)lub wstępnie przenieść obie tabele do tego samego rejestru.
• Jeśli tekst jest początkowo poprawny lub w nim? nie ma fragmentu do wymiany (ostatnia linia), wtedy nasza formuła zgłasza błąd. Ten moment można zneutralizować przechwytując i podmieniając błędy za pomocą funkcji JEŻELI BŁĄD (JEŻELI BŁĄD):

  

• Jeśli oryginalny tekst zawiera: kilka fragmentów z katalogu na raz, wtedy nasza formuła zastępuje tylko ostatnią (w ósmej linii, Ligovsky «Aleja« zmienić na „pr-t”, Ale „S-Pb” on „Św. Petersburg” już nie, bo „S-Pb” znajduje się wyżej w katalogu). Ten problem można rozwiązać, ponownie uruchamiając naszą własną formułę, ale już wzdłuż kolumny Stały:

  
  

Niezbyt idealna i nieporęczna miejscami, ale znacznie lepsza niż ta sama ręczna wymiana, prawda?

PS

W następnym artykule dowiemy się, jak zaimplementować takie zastępowanie zbiorcze za pomocą makr i dodatku Power Query.

• Jak działa funkcja SUBSTITUTE w celu zastąpienia tekstu
  
• Znajdowanie dokładnych odpowiedników tekstu za pomocą funkcji DOKŁADNE
• Wyszukiwanie i podstawianie z rozróżnianiem wielkości liter (uwzględniająca wielkość liter)