W tym poście omówimy działanie funkcji ZASTĄP, polegającej na zmienianiu/zastępowaniu lub usuwaniu fragmentów tekstu z komórek. Działanie tej funkcji omówimy na przykładowych danych z rysunku nr 1.
W poradzie 339 omawialiśmy funkcję PODSTAW. Funkcja ZASTĄP ma podobne działanie, ale działa na innej zasadzie. Funkcja PODSTAW wyszukuje konkretny fragment tekstu, natomiast funkcja ZASTĄP zmienia ciąg znaków na podstawie podanej liczby początkowej, czyli miejsca od którego chcemy dokonać zmian oraz liczby znaków które mają zostać zmienione. Aby dobrze zrozumieć temat, przyjmijmy, że chcemy zmienić tekst z komórki A3, konkretnie znaki ASD na zapis ‑MS- (rys. nr 2 pokazuje analogiczną zamianę w komórce A2)
Czyli jako pierwszy argument w formule podajemy tekst w którym chcemy dokonać zmian. Drugi argument to numer znaku od którego ma się zaczynać zmiana, a trzeci to ilość znaków, które chcemy zmienić, na koniec (czwarty argument) wpisujemy nowy tekst, który ma być wstawiony na miejsce usuniętego. Zapis funkcji będzie wyglądał następująco
=ZASTĄP(A3;4;3;"-MS-")
Zatwierdzamy formułę,a następnie kopiujemy na wiersze poniżej. W efekcie otrzymujemy dane z rysunku nr 3.
Przy użyciu funkcji ZASTĄP, możemy usuwać fragment tekstu. Z naszych przykładowych danych z rysunku nr 4 będziemy chcieli usunąć ciąg różnych znaków, które w każdym wierszu pojawiają się po różnej ilości znaków, ale mają dokładnie taką samą długość i zaczynają się od myślnika.
Użyjemy funkcji ZASTĄP, dla zakresu A12, ale liczbę
początkową, czyli miejsce od którego chcemy usunąć tekst, musimy znaleźć za
pomocą innej funkcji Excela – za pomocą funkcji ZNAJDŹ, która nasz szukany test
(myślnik) odnajdzie w zaznaczonym tekście. Chcemy podmienić 4 znaki, a w ich miejsce nic nie wstawiać. Formuła będzie wyglądać
następująco:
=ZASTĄP(A12;ZNAJDŹ("-";A12)4;"")
zatwierdzamy formułę i przeciągamy na wiersze poniżej. (rys. nr 5)
Funkcja ZASTĄP umożliwia nam również podstawianie tekstu w miejsce różnych znaków, szukając dynamicznie, czyli znajdując miejsce za pomocą funkcji ZNAJDŹ. W naszym przykładzie chcemy podmienić 4 znaki poprzedzone myślnikiem na inny tekst. Zapis funkcji będzie wyglądał następująco
= ZASTĄP(A21;ZNAJDŹ("-";A21)4; "-MS")
Istotne jest, żeby dopisać myślnik do tekstu, który chcemy wstawić, ponieważ w funkcji ZNAJDŹ szukany myślnik stanie się pierwszym usuniętym znakiem. Jeśli nie chcemy dopisywać myślnika w nowym tekście, wystarczy że do liczby znaków w formule dodamy 1, (wtedy podmianę zaczniemy od znaku po myślniku) i zmienimy ilość znaków do wymiany na 3. Formuła po tych przekształceniach będzie wyglądać następująco:
= ZASTĄP(A21;ZNAJDŹ("-";A21+1)3; "MS")
Obie formuły zadziałają jednakowo. Analogicznie pamiętajmy o zatwierdzeniu formuły i przeciągnięciu na wiersze poniżej (rys. nr 6)
Funkcja ZASTĄP ma jeszcze taką możliwość, że za jej pomocą możemy wstawić dodatkowy tekst w konkretne miejsce. W naszym przykładzie chcemy wstawić tekst po pierwszym myślniku. Znowu w takim przypadku musimy za pomocą funkcji ZNAJDŹ znaleźć gdzie ten myślnik się znajduje w tekście. Nie chcemy usuwać żadnych znaków(zastępować ich) tylko wstawić nowe więc w trzeci argument funkcji ZASTĄP wpisujemy zero, potem podajemy nowy tekst, który chcemy wstawić. Zapis formuły będzie wyglądał następująco:
=ZASTĄP(A30;ZNAJDŹ("-";A30)0; "-MS")
Zatwierdzamy przyciskiem Enter i kopiujemy na pozostałe wiersze (rys. nr 7).
Podsumowując funkcja ZASTĄP i PODSTAW przydają się do zmiany tekstu w zależności jaki tekst zmieniamy, czy ten tekst jest różnej długości, czy są to te sami znaki i czy jest w różnych miejscach usytuowany
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą)
II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query.
A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Aktualnie w promocji urodzinowej możesz mieć Mistrza Excela w obniżonej cenie, jeśli tylko wpiszesz kod 35URODZINY
https://exceliadam.pl/produkt/ksiazka-mistrz-excela
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie.
Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
Power Query
https://www.udemy.com/course/mistrz-power-query/?couponCode=35URODZINY
Mistrz Excela
https://www.udemy.com/mistrz-excela/?couponCode=35URODZINY
Dashboardy
https://www.udemy.com/course/excel-dashboardy/?couponCode=35URODZINY
Mistrz Formuł
https://www.udemy.com/course/excel-mistrz-formul/?couponCode=35URODZINY
VBA
https://www.udemy.com/course/excel-vba-makra/?couponCode=35URODZINY
Microsoft Power BI
https://www.udemy.com/course/power-bi-microsoft/?couponCode=35URODZINY
