Jak wyodrębnić inicjały cz.2
Jeśli posiadamy imię i nazwisko w osobnych komórkach, to możemy to zrobić za pomocą funkcji LEWY() oraz dodając do wyniku kropki.
Można do tego celu użyć funkcji FRAGMENT.TEKSTU oraz SZUKAJ.TEKST.
Można użyć w tym celu kombinacji funkcji LEWY, FRAGMENT.TEKSTU oraz SZUKAJ.TEKST.
Trzeba użyć funkcji logicznej, która to sprawdzi czy na początku tekstu pierwsze dwie lietry to Ch i na tej podstawie wyswietlić jeden lub dwa znaki.
Całość jest dostępna w obecnym artykule oraz filmie
Żeby sprawdzić czy nazwisko zaczyna się na dwie w/w litery należy sprawdzić np . funkcją JEŻELI i LUB czy to prawda. Jeśli tak, to wyświetlić odpowiednią ilość znaków. Należy przy tym pamiętać, że wypisywanie inicjałów na w/w dwie pierwsze litery nie jest zgodne z zasadami ortografii języka polskiego.
Jak wyodrębnić inicjały cz.2 – film instruktażowy
Praca z przykładowym plikiem
UWAGA! Niektóre pliki mogą wyświetlać błędy, jeśli wpisana formuła w komórkę jest inna niż zaplanowałem dla danego przykładu. Co nie oznacza, że koniecznie jest to błędem, gdyż często pewne rzeczy w Excelu można zrobić na kilka / kilkanaście sposobów.
Link do strony z plikiem do przykładu znajduje się poniżej. Żeby pobrać plik należy po kliknięciu w link założyć konto w serwisie, a jeśli już masz konto to się zalogować.
#25 plik do: Jak wyodrębnić inicjały część 2 ?
Funkcje, których użyjemy do wyodrębnienia incjałów zaczynających się na Ch, Sz, Rz, Cz
Żeby wyodrębnić inicjały z dwoma pierwszymi literami zaczynającymi się na Ch, Sz, Rz i Cz użyjemy poniższych funkcji Excela:
- LEWY do pobrania znaków z lewej strony ciągu znaków, w tym przypadku do wyświetlenia pierwszej litery imienia lub nazwiska,
- FRAGMENT.TEKSTU do wypisania fragmentu z ciągu znaków, w tym przypadku do wyświetlenia pierwszej litery z drugiego nazwiska dwuczłonowego,
- SZUKAJ.TEKST – do znalezienia miejsca, od którego znajduje się drugie nazwisko, konkretnie chodzi o myślnik,
- JEŻELI.BŁĄD – żeby w momencie nie znalezienia myślnika (czyli nazwisko nie bądzie dwuczłonowe) nie wyświetlać błędu i poprawnie wyświetlać nazwisko jednoczłonowe
- JEŻELI – funkcja logiczna, która pozwoli na sprawdzenie, czy pierwsze dwa litery nazwiska zaczynają się na Ch, Sz, Rz, Cz
- LUB – funkcja logiczna, która pozwoli sprawdzić kilka, w naszym przykładzie czterech przypadków w jednej funkcji JEŻELI
Pobieramy incijały nazwiska i sprawdzamy, czy rozpoczynają się Ch
Na początku zwiększymy szerowkość kolumny E, gdyż formułę którą za chwilę zaczniemy pisać będzie długa i nie zrobienie tego spowoduje, że będzie nieczytelna. W późniejszym czasie zaprezentuję jak sobie poradzić z tym problemem w inny sposób.
Zwiększenie szerokości kolumny
Zwiększyć szerokość kolumny możemy na dwa sposoby:
- Przesuń kursor pomiędzy kolumny E i F i kiedy kursor zmieni kształt w czarny krzyżyk to niciśnij lewy przycik myszy i przesuń w prawo. W momencie naciśnięcia przycisku pojawi się dymek z informacją o szerokości, a w momencie przesuwania wartość będzie się zmieniać. Ustaw szerkość na np. 106.
- Drugi sposób to najechanie kursorem na pole kolumny E, kursor zamieni się w czarną strzałkę skierowaną w dół, następnie naciśnij prawy przycisk myszy i wybierz z menu kontekstowego polecenie Szerokość kolumny… wpisz wartość, np. 106 i naciśnij przycisk Ok.
Pobieranie inicjałów z imienia i sprawdzenie, czy pierwsze dwie litery to Ch
- Klikamy na komórkę E20 i wpisujemy następująca formułę:
=LEWY(C20;JEŻELI(LEWY(C20;2)=”ch”;2;1))&”.”
Formuła sprawdza, czy pierwsze dwie litery to ch, jeśli tak to wyświetla 2 znaki, jeśli nie to jeden oraz dodaje na koniec kropkę.
- Nastepnie dodajemy jeszcze jeden znak &, który łączy ciągi znaków w formułach i możemy skopiować pierwszą część formuły, zmieniając tylko adres komórki C20 na D20. Całość formuły powinna wyglądać tak:
=LEWY(C20;JEŻELI(LEWY(C20;2)=”ch”;2;1))&”.”
&LEWY(D20;JEŻELI(LEWY(D20;2)=”ch”;2;1))&”.”
- Pozostaje nam sprawdzic drugie nazwisko. W tym celu użyjemy funkcji FRAGMENT.TEKSTU i SZUKAJ.TEKST do sprawdzenia, czy nazwisko jest dwuczłonowe. Jeśli masz problem ze zrozumieniem poniższego to zapraszam do pierwszej części Jak wyodrębnić inicjały, gdzie dokładniej tłumaczę jak to działa.
=LEWY(C20;JEŻELI(LEWY(C20;2)=”ch”;2;1))&”.”&LEWY(D20;JEŻELI(LEWY(D20;2)=”ch”;2;1))&”.”
<br>&FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1
Oczywiście tutaj sprawdzam za pomocą funkcji JEŻELI, czy tekst drugiego nazwiska zaczyna się na ch:=LEWY(C20;JEŻELI(LEWY(C20;2)=”ch”;2;1))&”.”&LEWY(D20;JEŻELI(LEWY(D20;2)=”ch”;2;1))&”.”
&FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;
JEŻELI(FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;2)=”ch”;2;1))&”.”
I tak jak w pierwszej części na koniec obsługujemy błąd, który pojawi się kiedy nazwisko jest jednoczłonowe. Opakowujemy więc funkcję FRAGMENT.TEKSTU w JEŻELI.BŁĄD
=JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;
JEŻELI(FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;2)=”ch”;2;1))&”.”;””)
a całość powinna wygladać tak:
=LEWY(C20;JEŻELI(LEWY(C20;2)=”ch”;2;1))&”.”&LEWY(D20;
JEŻELI(LEWY(D20;2)=”ch”;2;1))&”.”&
JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;
JEŻELI(FRAGMENT.TEKSTU(D20;SZUKAJ.TEKST(“-“;D20)+1;2)=”ch”;2;1))&”.”;””)
- Na zakończenie pierwszego etapu, możemy zmniejszyć szerokośc kolumny E do np. 13
- następnie skopiujmy formułę z komórki E20 do E21.
- W efekcie pierwszą część zadania mamy wykonaną.
Pobieramy incijały imienia i nazwiska oraz sprawdzamy, czy rozpoczynają się na Ch, Sz, Rz, Cz
Teraz rozpatrzymy przypadek z pisownią inicjałów imion i nazwisk, które zaczynają sie na Ch, Sz, Rz i Cz. Jest to co prawda niezgodne z zasadami ortograficznymi, ale na podstawie tego przykładu można rozwiązać inne zadanie, w którym chcemy wyodrębnić dwie pierwsze litery.
Na początku należy skopiować wcześniejszą formułę z komórki E21 do E24.
Jak poradzić sobie z edycją długiej formuły w komórce Excela?
Wcześniej pokazywałem jak sobie poradzić z formułą, która jest długa. Czasem jednak to nie wystarcza i na pewno nie wystarczy teraz, kiedy będziemy jeszcze bardziej rozbudowywać obecną formułę.
Jeśli klikniemy na komórce E24 i nciśniemy klawisz F2 naszym oczom ukaże się nasza obecna formuła w całej okazałości. A to nie koniec.
Jak widać formuła jest długa i nie jest ona zbyt czytelna, a my chcemy na niej nadal pracować. Jak więc zabierzemy się za jej edytowanie, by było to bardziej czytelne? Jeśli ktoś widział kod źródłowy dowolnego programu, to możliwe, że zwrócił uwagę, że ten kod jest uporządkowany, są tam różne wcięcia, dzięki którym łatwiej się w nim odnaleźć. My zrobimy coś podobnego.
Rozwinięcie paska formuł
- W górnej częsci okna mamy pasek formuł, gdzie możemy zobaczyć naszą formułę, chyba że ta wykraczaja poza jedną linijkę,
- na prawym końcu paska formuł znajduje się strzałka w dół, którą należy nacisnąć lewym przyciskiem myszy,
- jeśli wcześniej nie rozwijaliśmy paska formuł to najprawdopodobniej zobaczycie tylko jedną dodatkową linijkę, ja na obrazku pkt. 3,
- my jednak musimy bardziej rozwinąć pasek formuł, w tym celu przesuwamy kursor nad dolną krawędź paska formuł i w momencie zmiany kształu w strzałkę góra-dół naciskamy lewy przycisk myszy, następnie przesuwamy myszką w dół,
- po puszczeniu lewego przycisku myszy będziemy mieli kilka dodatkowych linii w pasku formuł, który po zamknięciu, czyli nacisnięciu strzałki z pkt. 2 będzie jedną linią, a po kolejnym naciśnięciu ukaże się tak duzy jak go ostatnio ustawiliśmy.
Zwiększenie czytelności formuły w pasku formuł
Czytelność formuły w pasku formuł uzyskamy przenosząc poszczególne części formuły do nowej linii, a wykonamy to poprzez naciśnięcie jednocześnie lewego Alt oraz Enter czyli Alt+Enter.
- Nasza obecna formuła posiada trzy funkcje JEŻELI, więc na początek przeniesiemy do nowej linii funkcje JEŻELI oraz ich pierwsze argumenty. W tym celu ustawiamy kursor przed pierwszą funkcją JEŻELI, a nastepnie naciskamy Alt+Enter,
- ustawiamy kursor za nawiasem otwierającym pierwszą funkcje JEŻELI i naciskamy Alt+Enter,
- następnie na końcu pierwszego argumentu ustawiamy kurosr za cudzysłowem LEWY(C24;2)=”ch“ i znów naciskamy Alt+Enter,
- druga funkcja JEŻELI jest identyczna, więc postępujemy tak samo, przenosząc do nowej linii samą funkcję, następnie pierwszy argument i kolejny oraz przenosząc resztę funkcji, naciskając przed średnikiem drugiego argumentu Alt+Enter,
- przechodzimy do trzeciej funkcji JEŻELI i ją przenosimy do nowej linii,
- następnie pierwszy argument, czyli całą funkcję FRAGMENT.TEKSTU, odnajdujemy zakończenie pierwszego argumentu funkcji JEŻELI, czyli ciąg “ch”, po którym resztę przenosimy do nowej linii – FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=“ch” po cudzysłowie naciskamy Alt+Enter,
- ostatecznie po sformatowaniu formuła powinna mieć postać:
=LEWY(C24;
JEŻELI(
LEWY(C24;2)=”ch”
;2;1))&”.”&LEWY(D24;
JEŻELI(
LEWY(D24;2)=”ch”
;2;1))&”.”&JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;
JEŻELI(
FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”ch”
;2;1))&”.”;””)
Dodatkowe warunki, czyli dodanie funkcji LUB
Teraz użyjemy funkcji LUB, by można było wyświetlić incjał z dwoma literami, jeśli zaczyna się na interesujące nas dwie pierwsze litery.
Dodajemy funkcję LUB, by odczytać inicjały imienia
- Dodajemy więc w pierwszej funckji JEŻELI funkcję LUB
=LEWY(C24;
JEŻELI(LUB(
LEWY(C24;2)=”ch”
);2;1
wewnątrz funkcji LUB musimy zawrzeć całą funkcje LEWY, pamiętamy o nawiasie z funkcji LUB, otwierający za słowem LUB i zamykającym za argumentem, czyli po cudzysłowie,
- teraz zaznaczamy pierwszy argument, czyli funkcję LEWY i ją kopiujemy Ctrl+C,
- następnie dodajemy na końcu funkcji LEWY średnik,
- wklejamy skopiowaną wcześniej funkcję LEWY Ctrl+V, powtarzamy nastepnie kroki 3 i 4 jeszcze dwa razy, gdyż potrzebujemy czterech takich funkcji,
- zmieniamy w funkcjach LEWY znaki ch na sz, rz, cz,
- w efekcie cała funkcja powinna wyglądać tak
=LEWY(C24;
JEŻELI(LUB(
LEWY(C24;2)=”ch”;LEWY(C24;2)=”sz”;LEWY(C24;2)=”rz”;LEWY(C24;2)=”cz”
);2;1))&”.”&LEWY(D24;JEŻELI(LEWY(D24;2)=”ch”;2;1))&”.”&JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;
JEŻELI(
FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”ch”;2;1))&”.”;””)
- po naciśnięciu przycisku Enter, otrzymamy inicjały z nowymi literami imienia i nazwiska.
Dodajemy funkcję LUB, by odczytać inicjały pierwszego nazwiska
Odczytanie inicjałów pierwszego nazwiska wykonjemy w taki sam sposób jak wcześniej imienia. Musimy dodać funkcję LUB, która musi mieć w środku jako cztery argumenty funkcję LEWY (pierwszy argument funkcji JEŻELI). Wykonujemy więc to identycznie jak wcześniej lub kopiujemy całą funkcję LUB z imienia i wklejamy ją w miejsce obecnej funkcji LEWY, następnie zmieniamy czterwy wskazania na komórkę C24 na D24.Nie bedę więc tego rozpisywać, wystarczy zerknąc wyżej. Poniżej cała funkcja.
=LEWY(C24;
JEŻELI(LUB(
LEWY(C24;2)=”ch”;LEWY(C24;2)=”sz”;LEWY(C24;2)=”rz”;LEWY(C24;2)=”cz”
);2;1))&”.”&LEWY(D24;
JEŻELI(LUB(
LEWY(D24;2)=”ch”;LEWY(D24;2)=”sz”;LEWY(D24;2)=”rz”;LEWY(D24;2)=”cz”
);2;1))&”.”&JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;
JEŻELI(
FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”ch”;2;1))&”.”;””)
Dodajemy funkcję LUB, by odczytać inicjały drugiego nazwiska
Ostatni element jest tak naprawdę także identyczny, jeśli mowa o sposobie działania, tutaj jednak opakowujemy w funkcję LUB funkcję FRAGMENT.TEKSTU, więc wystarczy wykonać wszystko jak w przykładzie perwszym z imieniem, z tą różnicą, że kompiujemy funkcję FRAGMENT.TEKSTU. Dla zmniejszenia pierwszej linii możemy np. po drugiej funkcji FRAGMENT.TEKSTU przenieść się do kolejnej linii. Poniżej cała formuła.
=LEWY(C24;
JEŻELI(LUB(
LEWY(C24;2)=”ch”;LEWY(C24;2)=”sz”;LEWY(C24;2)=”rz”;LEWY(C24;2)=”cz”
);2;1))&”.”&LEWY(D24;
JEŻELI(LUB(
LEWY(D24;2)=”ch”;LEWY(D24;2)=”sz”;LEWY(D24;2)=”rz”;LEWY(D24;2)=”cz”
);2;1))&”.”&JEŻELI.BŁĄD(FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;
JEŻELI(LUB(
FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”ch”;FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”sz”;
FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”rz”;FRAGMENT.TEKSTU(D24;SZUKAJ.TEKST(“-“;D24)+1;2)=”cz”
);2;1))&”.”;””)
Na zakończenie możemy zwinąć pasek formuł podobnie jak go rozwinęliśmy wcześniej lub użyć w tym celu skrótu klawiaturowego Ctrl+Shift+U, a nastepnie skopiować formułę z komórki E24 do dwóch poniższych. W efekcie nasze zadanie zostanie zakończone.
Tag:FRAGMENT.TEKSTU, JEŻELI, JEŻELI.BŁĄD, LEWY, LUB, SZUKAJ.TEKST
Może Ci się spodobać
Jak wyodrębnić inicjały
Jak znaleźć 3 największe wartości
