Jak wyodrębnić inicjały cz.2

Jak wyodrębnić inicjały cz.2 – film instruktażowy

Praca z przykładowym plikiem

Plik do przykładu można pobrać poniżej.

UWAGA! Plik wyświetla błędy, jeśli wpisana funkcja w komórkę jest inna niż zaplanowałem dla danego przykładu. Co nie oznacza, że koniecznie jest to błędem, ponieważ często pewne rzeczy w Excelu można zrobić na kilka / kilkanaście sposobów.

Funkcje, które 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:

1. 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.

2. Drugi sposób to najechanie kursorem na pole kolumny E, kursor zamieni się w czarna 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

1. 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ę.

2. 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))&”.”

3. 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 ponizszego 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))&”.”&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))&”.”;””)

1. Na zakończenie pierwszego etapu, możemy zmniejszyć szerokośc kolumny E do np. 13
2. następnie skopiujmy formułę z komórki E20 do E21.
3. 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, jednak czasami to nie wystarcza. Nie wystarczy na pewno teraz, kiedy będziemy jeszcze bardziej rozudowywać obecną formułę.

Jeśli klikniemy na komórce E24 i nciśniemy klawisz F2 naszym oczom ukaże się jak wygląda teraz nasza formuła.

Jak widać jest długa i nie jest ona zbyt czytelna, a chcemy na niej nadal pracować. Jak więc zabierzemy się za jej edytowanie, by było to bardziej czytelne? Jesli 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 cos podobnego.

Rozwinięcie paska formuł

1. W górnej częsci okna mamy pasek formuł, gdzie możemy je podejrzeć, chyba, że wykraczaja poza jedną linijkę,
2. na prawym końcu paska formuł znajduje się strzałka w dół, którą należy nacisnąć lewym przyciskiem myszy,
3. jeśli wcześniej nie rozwijalismy paska formuł to najprawdopodobniej zobaczycie tylko jedną dodatkową linijkę, ja na obrazku pkt. 3,
4. 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ół,
5. 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 nacisnię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ęsci formuły do nowej linii, a wykonamy to poprzez naciśnięcie jednocześnie lewego Alt oraz Enter czyli Alt+Enter.

1. 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,
2. ustawiamy kursor za nawiasem otwierającym pierwszą funkcje JEŻELI i naciskamy Alt+Enter,
3. następnie na końcu pierwszego argumentu ustawiamy kurosr za cudzysłowem LEWY(C24;2)=”ch„ i znów naciskamy Alt+Enter,
4. 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 raz przenosząc resztę funkcji, naciskając przed średnikiem drugiego argumentu Alt+Enter,
5. przechodzimy do trzeciej funkcji JEŻELI i ją przenosimy do nowej linii,
6. 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,
7. 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 sie na interesujące nas dwie pierwsze litery.

Dodajemy funkcję LUB, by odczytać inicjały imienia

1. Dodajemy więc w pierwszej funckji JEŻELI funkcje 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,

2. teraz zaznaczamy pierwszy argument, czyli funkcję LEWY i ją kopiujemy Ctrl+C,
3. następnie dodajemy na końcu funkcji LEWY średnik,
4. wklejamy skopiowaną wcześniej funkcję LEWY Ctrl+V, powtarzamy nastepnie kroki 3 i 4 jeszcze dwa razy, gdyz potrzebujemy czterech takich funkcji,
5. zmieniamy w funkcjach LEWY znaki ch na sz, rz, cz,
6. 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))&”.”;””)

7. 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 dówch poniższych. W efekcie nasze zadanie zostanie zakończone.