Zastosowanie funkcji BPR w Microsoft Excel

W niektórych przypadkach użytkownik musi wykonać zadanie zwrócenia określonej liczby znaków do komórki docelowej z innej komórki, zaczynając od znaku wskazanego na koncie po lewej stronie. Ta funkcja jest doskonale obsługiwana przez funkcję PCR . Jeszcze więcej zwiększa jego funkcjonalność, jeśli w połączeniu z nią korzystają inni operatorzy, na przykład SZUKAJ lub ZNAJDŹ . Przyjrzyjmy się bliżej możliwościom funkcji CCT i zobaczmy, jak działa na konkretnych przykładach.

Korzystanie z CCRT

Głównym zadaniem operatora jest wyodrębnienie z określonego elementu arkusza pewnej liczby drukowanych znaków, w tym spacji, począwszy od znaku wskazanego na koncie po lewej stronie. Ta funkcja należy do kategorii operatorów tekstowych. Jego składnia jest następująca:

=ПСТР(текст;начальная_позиция;количество_знаков)

Jak widać, ta formuła składa się z trzech argumentów. Wszystkie są obowiązkowe.

Argument "Tekst" zawiera adres tego elementu arkusza, który zawiera wyrażenie tekstowe z wyodrębnionymi znakami.

Argument "Pozycja początkowa" jest reprezentowany jako liczba wskazująca, który znak z konta, począwszy od lewej strony, należy wyodrębnić. Pierwszy znak jest uważany za "1" , drugi za "2" itd. W obliczeniach uwzględniane są nawet spacje.

Argument "Liczba znaków" zawiera numeryczny indeks liczby znaków, zaczynając od pozycji początkowej, którą należy wyodrębnić do komórki docelowej. Podczas liczenia, podobnie jak poprzedni argument, spacje są brane pod uwagę.

Przykład 1: pojedyncza ekstrakcja

Aby opisać przykłady zastosowania funkcji DCS , rozpoczynamy od najprostszego przypadku, gdy musimy wyodrębnić pojedyncze wyrażenie. Oczywiście takie opcje są rzadko stosowane w praktyce, dlatego podajemy ten przykład jedynie jako wprowadzenie do zasad pracy operatora.

Mamy więc tabelę pracowników firmy. Pierwsza kolumna zawiera imiona, nazwiska i patronima pracowników. Musimy użyć operatora CCTS, aby wyodrębnić nazwisko tylko pierwszej osoby z listy Petra Iwanowicza Nikołajewa we wskazanej komórce.

1. Wybierz element arkusza, który chcesz wyodrębnić. Klikamy przycisk "Wstaw funkcję" , który znajduje się w pobliżu linii formuły.
2. Otworzy się okno Kreatora opcji. Przejdź do kategorii "Tekst" . Wybieramy tam nazwę "POS" i klikamy przycisk "OK" .
3. Wyświetlane jest okno z argumentami operatora "PTPR" . Jak widać, w tym oknie liczba pól odpowiada liczbie argumentów tej funkcji.

   W polu "Tekst" wprowadzamy współrzędne komórki zawierającej nazwiska pracowników. Aby nie prowadzić adresu ręcznie, wystarczy umieścić kursor w polu i kliknąć lewym przyciskiem myszy na elemencie na arkuszu, który zawiera potrzebne dane.

   W polu "Pozycja początkowa" należy podać numer symbolu, licząc po lewej stronie, od którego rozpoczyna się nazwisko pracownika. Podczas liczenia uwzględniamy również luki. Litera "N" , od której zaczyna się imię pracownika Nikolaev, jest piętnastym symbolem. Dlatego w polu wpisujemy numer "15" .

   W polu "Liczba znaków" podaj liczbę znaków składających się na nazwisko. Składa się z ośmiu znaków. Ale biorąc pod uwagę, że po nazwie w komórce nie ma więcej znaków, możemy podać więcej znaków. Oznacza to, że w naszym przypadku możesz umieścić dowolną liczbę równą lub większą niż osiem. Podajemy na przykład liczbę "10" . Ale jeśli po nazwisku w celi były jeszcze słowa, cyfry lub inne symbole, wówczas musielibyśmy ustalić tylko dokładną liczbę znaków ( "8" ).

   Po wprowadzeniu wszystkich danych kliknij przycisk "OK" .

4. Jak widać, po tej czynności nazwisko pracownika zostało wywnioskowane w komórce wskazanej w pierwszym kroku przykładu 1 .

Lekcja: Kreator funkcji w programie Excel

Przykład 2: ekstrakcja grupowa

Ale, oczywiście, ze względów praktycznych łatwiej jest ręcznie zarządzać jednym nazwiskiem niż stosować do tego formułę. Ale w przypadku transferu grupy danych, zastosowanie tej funkcji będzie całkiem odpowiednie.

Mamy listę smartfonów. Przed nazwą każdego modelu jest słowo "Smartphone" . Musimy umieścić w osobnej kolumnie tylko nazwy modeli bez tego słowa.

1. Wybierz pierwszy pusty element kolumny, do którego wynik zostanie wyprowadzony, i wywołaj okno argumentu operatora DTR w taki sam sposób, jak w poprzednim przykładzie.

   W polu "Tekst" podajemy adres pierwszego elementu kolumny z oryginalnymi danymi.

   W polu "Pozycja początkowa" musimy podać numer symbolu, z którego dane zostaną wyodrębnione. W naszym przypadku w każdej komórce przed nazwą modelu znajduje się słowo "Smartphone" i spacja. Tak więc fraza, która musi być wyprowadzona w oddzielnej komórce zaczyna się wszędzie od dziesiątego symbolu. Ustaw liczbę "10" w tym polu.

   W polu "Liczba znaków" musisz ustawić liczbę znaków, które zawierają wyświetlaną frazę. Jak widać, nazwa każdego modelu ma inną liczbę znaków. Ale fakt, że po nazwie modelu tekst w komórkach kończy się, ratuje sytuację. Dlatego możemy ustawić w tym polu dowolną liczbę równą lub większą niż liczba znaków w najdłuższej nazwie na tej liście. Ustawiamy dowolną liczbę znaków "50" . Nazwa dowolnego z wymienionych smartfonów nie przekracza 50 znaków, więc ta opcja nam odpowiada.

   Po wprowadzeniu danych kliknij przycisk "OK" .

2. Następnie nazwa pierwszego modelu smartfona jest wyświetlana we wcześniej określonej komórce tabeli.
3. Aby nie wprowadzać osobno formuły w każdej komórce kolumny, kopiujemy ją przy użyciu znacznika wypełnienia. Aby to zrobić, umieść kursor w prawym dolnym rogu komórki za pomocą formuły. Kursor jest konwertowany na uchwyt wypełnienia w postaci małego krzyża. Zacisnąć lewy przycisk myszy i przeciągnąć go na sam koniec kolumny.
4. Jak widać, cała kolumna zostanie wypełniona danymi, których potrzebujemy. Sekretem jest to, że argument "Tekst" jest odniesieniem względnym, a także zmienia się wraz ze zmianą pozycji komórek docelowych.
5. Problem polega jednak na tym, że jeśli zdecydujemy się nagle zmienić lub usunąć kolumnę z oryginalnymi danymi, dane w kolumnie docelowej będą wyświetlane niepoprawnie, ponieważ są powiązane ze sobą za pomocą formuły.

   Aby "rozwiązać" wynik z oryginalnej kolumny, wykonujemy następujące manipulacje. Wybierz kolumnę zawierającą formułę. Następnie przejdź do zakładki "Strona główna" i kliknij ikonę "Kopiuj" znajdującą się w bloku "Schowek" na wstążce.

   Jako alternatywną akcję możesz nacisnąć Ctrl + C po wyborze.

6. Następnie, bez usuwania zaznaczenia, kliknij kolumnę prawym przyciskiem myszy. Otwiera menu kontekstowe. W bloku "Parametry wstawiania" kliknij ikonę "Wartości" .
7. Następnie, zamiast formuł, wartości zostaną wstawione do wybranej kolumny. Teraz możesz bezpiecznie edytować lub usunąć oryginalną kolumnę. Wynik nie ma żadnego wpływu.

Przykład 3: Korzystanie z kombinacji operatorów

Jednak powyższy przykład ogranicza się do faktu, że pierwsze słowo we wszystkich komórkach źródłowych musi mieć taką samą liczbę znaków. Zastosowanie funkcji SEARCH lub SEARCH wraz z funkcją CCT znacznie rozszerzy możliwości wykorzystania formuły.

Operatory tekstowe SEARCH and FIND zwracają pozycję określonego znaku w wyświetlanym tekście.

Składnia funkcji SEARCH wygląda następująco:

=ПОИСК(искомый_текст;текст_для_поиска;начальная_позиция)

Składnia operatora FIND wygląda następująco:

=НАЙТИ(искомый_текст;просматриваемый_текст;нач_позиция)

Ogólnie rzecz biorąc, argumenty tych dwóch funkcji są identyczne. Główna różnica polega na tym, że operator SEARCH nie bierze pod uwagę litery liter podczas przetwarzania danych, a FIND - bierze pod uwagę.

Zobaczmy, jak używać operatora SEARCH w połączeniu z funkcją CCR . Mamy tabelę, w której wpisywane są nazwy różnych modeli technologii komputerowej o uogólnionej nazwie. Podobnie jak w ostatnim czasie, musimy wyodrębnić nazwy modeli bez uogólnionej nazwy. Trudność polega na tym, że jeśli w poprzednim przykładzie ogólna nazwa dla wszystkich przedmiotów była taka sama ("smartfon"), to na tej liście jest inna ("komputer", "monitor", "kolumny" itp.) z inną liczbą znaków. Aby rozwiązać ten problem, potrzebujemy również operatora SEARCH , który zainwestujemy w funkcję CCT.

1. Wybieramy pierwszą komórkę kolumny, w której dane będą wyprowadzane, iw zwykły sposób nazywamy okno argumentu funkcji CCT .

   W polu "Tekst" , jak zwykle, określamy pierwszą komórkę kolumny z oryginalnymi danymi. Tutaj wszystko pozostaje niezmienione.

2. Ale wartość pola "Pozycja początkowa" określa argument, który generuje funkcja SZUKAJ . Jak widać, wszystkie dane na liście są zjednoczone faktem, że istnieje luka przed nazwą modelu. Dlatego operator SEARCH wyszuka pierwszą przerwę w komórce oryginalnego zakresu i zgłosi numer tego symbolu do funkcji CCT .

   Aby otworzyć okno argumentów operatora SEARCH , ustaw kursor w polu "Pozycja początkowa" . Następnie kliknij ikonę w kształcie trójkąta, skierowaną w dół. Ta ikona znajduje się na tym samym poziomym poziomie okna, w którym znajduje się przycisk "Wstaw funkcję" i pasek formuły, ale po lewej stronie. Wyświetlana jest lista ostatnio stosowanych operatorów. Ponieważ wśród nich nie ma nazwy "SZUKAJ" , a następnie kliknij pozycję "Inne funkcje ..." .

3. Otworzy się okno Wizards . W kategorii "Tekst" wybierz nazwę "SZUKAJ" i kliknij przycisk "OK" .
4. Otwarte jest okno SEARCH operatora. Ponieważ szukamy spacji, umieszczamy spację w polu "Wyszukaj tekst", ustawiając tam kursor i naciskając odpowiedni klawisz na klawiaturze.

   W polu "Tekst do wyszukania" określamy odwołanie do pierwszej komórki kolumny z oryginalnymi danymi. To odniesienie będzie takie samo, jak to, które poprzednio wskazaliśmy w polu "Tekst" w oknie argumentów operatora BTS .

   Argument pola "Pozycja początkowa" jest opcjonalny. W naszym przypadku nie trzeba go wypełniać, lub można ustawić numer "1" . W przypadku każdej z tych opcji wyszukiwanie rozpocznie się od początku tekstu.

   Po wprowadzeniu danych nie spiesz się, aby nacisnąć przycisk "OK" , ponieważ funkcja SZUKAJ jest zagnieżdżona. Wystarczy kliknąć nazwę PCR na pasku formuły.

5. Po wykonaniu ostatniej określonej czynności automatycznie powracamy do okna argumentów operatora BTS . Jak widać, pole "Pozycja początkowa" jest już wypełnione formułą WYSZUKIWANIE . Ale ta formuła wskazuje na spację i potrzebujemy następnego znaku po spacji, od której zaczyna się nazwa modelu. Dlatego do istniejących danych w polu "Pozycja początkowa" dodajemy wyrażenie "+1" bez cudzysłowów.

   W polu "Liczba znaków" , tak jak w poprzednim przykładzie, zapisujemy dowolną liczbę, która jest większa lub równa liczbie znaków w najdłuższym wyrażeniu oryginalnej kolumny. Na przykład wpisz numer "50" . W naszym przypadku to wystarczy.

   Po wykonaniu wszystkich określonych czynności, kliknij przycisk "OK" w dolnej części okna.

6. Jak widać, nazwa modelu urządzenia została wyświetlona w oddzielnej komórce.
7. Teraz za pomocą Kreatora wypełniania, tak jak w poprzedniej metodzie, skopiuj formułę do komórek znajdujących się niżej w tej kolumnie.
8. Nazwy wszystkich modeli urządzeń są wyświetlane w komórkach docelowych. Teraz, jeśli to konieczne, możliwe jest zerwanie łącza w tych elementach z kolumną oryginalnych danych, tak jak w poprzednim czasie, poprzez sekwencyjne stosowanie kopiowania i wklejania wartości. Jednak działanie to nie zawsze jest obowiązkowe.

Funkcja FIND jest używana w połączeniu z formułą EPR na tej samej zasadzie, co operator SEARCH .

Jak widać, funkcja PCR jest bardzo wygodnym narzędziem do wyświetlania niezbędnych danych we wcześniej określonej komórce. Fakt, że nie jest tak popularny wśród użytkowników, tłumaczy się tym, że wielu użytkowników, korzystając z Excela, poświęca więcej uwagi funkcjom matematycznym niż tekstowi. Zastosowanie tej formuły w połączeniu z innymi operatorami zwiększa jej funkcjonalność.

• ← Obliczanie płatności dożywotniej w programie Microsoft Excel
• Drukowanie dokumentu w programie Microsoft Excel →