• Home
  • Server 2003/2008
  • Windows 7
  • Office
  • Linux
  • Sieci komputerowe
  • Wujek dobra rada
  • Mapa strony
  • Napisz
  • czcionka Zmniejsz czcionkę Zmniejsz czcionkę Powiększ czcionkę Powiększ czcionkę
  • Wydrukuj
  • Email
  • Dodaj komentarz
Excel w zadaniach. Funkcje tekstowe i wyszukujące.
pikolo pikolo

Excel w zadaniach. Funkcje tekstowe i wyszukujące.

14 lipiec 2017
Dział: Office
Czytany 39600 razy
Oceń ten artykuł
  • 1
  • 2
  • 3
  • 4
  • 5
(11 głosów)

Pomimo, iż arkusz kalkulacyjny został stworzony aby uprościć operacje związane z analizą i przetwarzaniem danych liczbowych to zawiera on szereg funkcji związanych z tekstem oraz wyszukiwaniem. Funkcje te typowo nie są związane z operacjami matematycznymi lecz pośrednio je wspomagają. Poznanie ich przeznaczenia i sposobu działania wiele spraw ułatwia gdyż operując na liczbach trzeba je w jakiś sposób opisać.

 

Wpis rozpoczniemy od omówienia funkcji DŁ() (zaczynamy od tych najprostszych, by w miarę zgłębianie tematu przejść do tych bardziej zaawansowanych).

 

Funkcja zwraca liczbę znaków, z których składa się tekst wskazany w argumencie funkcji. Argumentem może być konkretny ciąg zbudowany z liczb bądź liter lub adres komórki w której dana fraza się znajduje. Do zliczanych znaków włączane są również znaki interpunkcyjne oraz odstępy.

 

Składnia funkcji jest bardzo prosta i ma postać: DŁ(tekst/adres_komórki)

 

Argument: tekst/adres_komórki określa tekst bądź adres komórki w której tekst został wpisany, którego długość ma zostać określona. Spacje są liczone jako znaki.

 

Przykład zastosowania funkcji: DŁ() zaprezentowano na rysunku poniżej. W lewej kolumnie znajdują się wpisane frazy zaś po prawej została wyliczona ilość znaków. Jak można zauważyć ilość znaków z których są zbudowane dane ciągi została wyliczona prawidłowo. Choć w przypadku ostatniego przykładu wydawać by się mogło, że jest błąd gdyż przecież wyraz „spacja” zbudowany jest z 6 liter a nie 7. Taki stan rzeczy spowodowany jest tym, że po wyrazie „spacja” został dodany jeden znak właśnie w formie spacji - 6 liter plus spacja = 7.

 

image1

 

Funkcję DŁ() możemy wykorzystać np. w zbudowaniu funkcji, która będzie Nam sprawdzać czy wpisany numer, który mus się składać z określonej liczby znaków (PESEL, NIP, REGON) został wpisany prawidłowo. Poniżej przykład w którym za pomocą formuły: =JEŻELI(DŁ(adres_komórki)=11;"OK";"BŁĄD") sprawdzamy poprawność numeru PESEL. Numer ten składa się z 11 liczb, brak (lub też pojawienie się dodatkowej) liczby powoduje wypisanie komunikatu BŁĄD Jeśli numer PESEL jest prawidłowy pojawia się słowo OK.

 

image2

 

Te samo zadanie można wykonać z wykorzystaniem reguł formatowania warunkowego. Poprawnie zbudowane numery PESEL są zaznaczane na żółto.

 

image3

 

W sytuacjach w których z podanego ciągu tekstowego musimy wyodrębnić jakąś jego część możemy wykorzystać funkcję: FRAGMENT.TEKSTU() Ogólna składnia funkcji wygląda następująco: FRAGMENT.TEKSTU(tekst;liczba_początkowa;liczba_znaków)

 

Zadaniem funkcji jest zwrócenie znaku bądź ciągu znaków zawartych w dowolnym innym tekście, pobrany ciąg liter bądź cyfr może rozpoczynać się od określonej pozycji tekstu bazowego i składać się z określonej liczby znaków. Do znaków tych zaliczamy litery, cyfry oraz znaki interpunkcyjne.

 

Pierwszy argument definiuje tekst bądź adres komórki w której ten tekst się znajduje, z którego ma zostać wyodrębniony znak lub ciąg znaków. Drugi parametr (liczba_początkowa) określa numer pozycji liczonej od lewej strony od której ma rozpocząć się pobieranie znaków. Ostatni argument liczba_znaków określa liczbę wyodrębnianych znaków.

 

Poniżej przykład użycia omawianej funkcji - z lewej strony zostały zapisane nazwy miejscowość z prawej zaś wyodrębniony ciąg - użyta formuła: =FRAGMENT.TEKSTU(D4;4;8) nakazuje wypisać 8 znaków począwszy od 4 znaku. Jak można zaobserwować znak spacji wliczany jest do wyodrębnianych znaków.

 

image4

 

Praktyczny przykład wykorzystujący funkcję FRAGMENT.TEKSTU() został przedstawiony poniżej. Przypuśćmy, że posiadamy bazę faktur, których zasada numerowania opiera się na wzorcu: <numer_kolejny>/<kontrahent>/<rok> przy czym każde z pól zawiera następującą liczbę znaków: 5/3/4 Potrzebujemy z tak przygotowanej bazy wyodrębnić nazwę kontrahentów. Aby zadanie to wykonać została użyta następująca formuła: =FRAGMENT.TEKSTU(J18;7;3) która nakazuje od 7 znaku włącznie pobrać 3.

 

image5

 

W operacjach związanych z tekstem bardzo często wykorzystywana jest funkcja: LEWY() oraz funkcja bliźniak: PRAWY().

 

Funkcja: LEWY() zwraca określoną liczbę znaków liczoną od lewej strony ciągu liczbowego bądź tekstowego.

 

Zaś funkcja: PRAWY() jak łatwo się domyśleć zwraca określoną liczbę znaków ciągu lecz tym razem znaki są liczone od strony prawej.

 

Składnia funkcji wygląda następująco: LEWY(tekst/adres_komórki;liczba_znaków) oraz PRAWY(tekst/adres_komórki;liczba_znaków)

 

Zastosowanie obu funkcji zostało zaprezentowane na zrzucie poniżej. W prezentowanym przykładzie z puli nazwisk wyodrębniono trzy pierwsze litery (formuła: =LEWY(adres_komórki;3) oraz trzy ostatnie litery nazwiska (formuła: =PRAWY(adres_komórki;3).

 

image6

 

Praktyczne zastosowanie funkcji możemy zobrazować na przykładzie w którym to potrzebujemy na bazie imienia i nazwiska stworzyć nazwę logowania opierającą się na schemacie: 3 litery imienia + 3 litery nazwiska.

 

Aby zadanie te zrealizować została użyta dwukrotnie funkcja: LEWY() w połączeniu z symbolem łączenia zawartości dwóch komórek (&) - z zawartości dwóch komórek (imię i nazwisko) zostały pobrane 3 pierwsze znaki, które w następnym kroku zostały ze sobą połączone. Formuła, która realizuje powyższe zadanie ma postać: =LEWY(adres_komórki;3)&LEWY(adres_komórki;3)

 

image7

 

No to jeszcze jeden mały przykład. Posiadamy bazę imion i nazwisk niestety są one poprzedzone tytułami naukowymi a my potrzebuje tylko imię i nazwisko. Aby z tak zbudowanego ciągu wyodrębnić samo imię i nazwisko z pominięciem tytułu naukowego należy użyć takiej o to formuły: =JEŻELI(LUB(LEWY(V16;4)="inż.";LEWY(V16;3)="mgr";LEWY(V16;2)="dr");PRAWY(V16;DŁ(V16)-ZNAJDŹ(" ";V16));V16)

 

Formuła ta działa w ten sposób, że przy pomocy funkcji JEŻELI() w połączeniu z funkcją LUB() oraz LEWY() sprawdzane jest czy występują tytuły naukowe: inż., mgr oraz dr jeśli ten warunek jest spełniony wyliczana jest długość znaków całego ciągu pomniejszona o liczbę pozycji w której występuje znak spacji. Tak wyliczona długość ciągu jest wyodrębniona za pomocą funkcji PRAWY(). Jeśli tytuł naukowy nie występuje zawartość komórki zostaje powielona. W formule tej została użyta funkcja ZNAJDŹ(), której opis działania znajdziesz w dalszej części wpisu.

 

Opis funkcji JEŻELI() oraz LUB() znajdziesz w tych wpisach: Excel w zadaniach. Funkcja JEŻELI oraz Excel w zadaniach. Funkcja ORAZ i LUB

 

image8

 

Trzy następne funkcje są bardzo proste a ich zadaniem jest zamiana liter małych na wielkie i odwrotnie.

 

Aby każde słowo w komórce zostało zamienione na wielkie litery należy użyć funkcji: LITERY.WIELKIE(adres_komórki) Poniżej przykład w którym imiona zapisane w komórkach od D5 do D10 zostały zamienione na wielkie litery (komórki od G5 do G10).

 

image9

 

W sytuacji odwrotnej używamy funkcji: LITERY.MAŁE(adres_komórki) Analogiczny przykład z tym powyżej lecz tym razem imiona zostają zapisane za pomocą małych liter.

 

image10

 

Trzecią z funkcji odpowiedzialną za zmianę wielkości liter jest funkcja: Z.WIELKIEJ.LITERY(adres_komórki) która to pierwszą literę każdego słowa zamienia na wielką literę, zaś wszystkie pozostałe litery tworzące dany ciąg są literami małymi.

 

O to przykład w którym przy pomocy funkcji Z.WIELKIEJ.LITERY() imiona i nazwiska zostały zmienione tak by rozpoczynały się od wielkiej litery (zgodnie z zasadami ortografii). Imiona i nazwiska zapisane mały literami zostały pobrane z kolumny D oraz E i połączone ze sobą przy pomocy symbolu & (pomiędzy imieniem i nazwiskiem dodatkowo został dodany znak spacji) - formuła realizująca to zadanie przyjmie postać: Z.WIELKIEJ.LITERY(D37)&" "&Z.WIELKIEJ.LITERY(E37)

 

image11

 

Komórka D37 zawierała tekst jan użycie formuły spowodowało zwrócenie tekstu Jan. Użycie formuły na komórce w której zawarty byłby tekst pan jan spowoduje zwrócenie ciągu Pan Jan. Użyta spacja w ciągu tekstowym powoduje, że słowo zawarte po spacji również rozpoczynane jest od wielkiej litery. Taką samą sytuację mamy w przypadku użycia znaku apostrofu (słowo d'artagnan).

 

image12

 

Znając już funkcję LEWY(), PRAWY(), LITERY.WIELKIE, LITERY.MAŁE oraz DŁ() zadanie zapisania wyrazu zaczynającego się od wielkiej litery z pominięcie funkcji Z.WIELKIEJ.LITERY() można by było zrealizować za pomocą formuły użytej na poniższym zrzucie: =LITERY.WIELKIE(LEWY(D28))&PRAWY(LITERY.MAŁE(D28);DŁ(D28)-1) Formuła pobiera pierwszy lewy znak z imienia i zamienia go na wielki (LITERY.WIELKIE(LEWY(D28))) a następnie obliczana jest długość całego ciągu tekstowego minus znak pierwszy (DŁ(D28)-1), która w kroku kolejnym zamienia pozostałe litery na małe (PRAWY(LITERY.MAŁE(D28)) Tak oba powstałe ciągi są ze sobą łączone (symbol &).

 

image13

 

Tak wiem, że przykład przedstawiony powyżej jest pewnego rodzaju „przekombinowaniem” ale pokazuje fajnie jak poznane do tej pory funkcję można ze sobą łączyć. Czytelniku potraktuj przedstawiony przykład jak łamigłówkę i postaraj zrozumieć jak przytoczona formuła działa.

 

W pracy z tekstem bardzo często zdarzają się takie sytuacje, w których trzeba część jakiegoś większego tekstu zastąpić innym. Do sytuacji takiej dochodzi najczęściej w przypadku importu danych np. data z importowanego pliku ma inny format od tego oczekiwanego - np. importowana data ma postać 12/02/1980 a my potrzebujemy mieć ją zapisaną w postaci 12-02-1980. Do przekształcenia znaku / na - możemy użyć narzędzia Zamień (polecenie Narzędzia główne/Edycja/Znajdź i zaznacz/Zamień) bądź wykorzystać rozwiązanie oparte na formule.

 

Do zamiany jednego fragmentu tekstu w drugi możemy użyć następujących dwóch funkcji:

 

funkcja: PODSTAW(tekst;stary_tekst;nowy_tekst;[wystąpienie_liczba]) - tą funkcję należy wybrać wtedy gdy znany Nam jest znak (bądź ich ciąg) lecz niewiadomą jest jego pozycja.

 

argumenty funkcji:

tekst - tekst lub odwołanie do komórki zawierającej tekst, w którym zostaną zastąpione znaki,

stary_tekst - łańcuch, który zostanie zastąpiony,

nowy_tekst - nowy łańcuch, który pojawi się w miejscu ciągu zdefiniowanego przez argument stary_tekst,

wystąpienie_liczba - jest to argument opcjonalny. Definiuje, które wystąpienie argumentu stary_tekst zostanie zastąpione przez argument nowy_tekst.

 

Poniżej zostały przedstawione trzy przykłady użycia funkcji: PODSTAW()

 

image14

 

Przykład 1: formuła: =PODSTAW(D5;"2015";"2016") - zostaje wykonana zamiana roku 2015 na 2016,

 

Przykład 2: =PODSTAW(D10;" ";"") - zostają usunięte znaki spacji,

 

Przykład 3: =PODSTAW(D15;"r";"R";2) - zostaje zamienione drugie wystąpienie małej litery r na duże R.

 

funkcja: ZASTĄP(stary_tekst;nr_poz_pocz;liczba_znaków;nowy_tekst) - zamienia część ciągu tekstowego na inny ciąg tekstowy z uwzględnieniem określonej liczby znaków.

 

Argumenty funkcji:

stary_tekst - tekst, który ma zostać zamieniony,

nr_poz_pocz - pozycja znaku w tekście argumentu stary_tekst, w której ma nastąpić zamiana na tekst argumentu nowy_tekst,

liczba_znaków - liczba znaków w tekście argumentu stary_tekst, które funkcja ZASTĄP() ma zamienić na tekst argumentu nowy_tekst,

nowy_tekst - tekst, na który zostaną zamienione znaki w tekście argumentu stary_tekst.

 

Poniżej przykład zastosowania opisywanej funkcji. W komórce F21 została wprowadzona formuła: =ZASTĄP(D21;6;1;""), której zadaniem jest zastąpienie 6 znaku w komórce D21. Na wskutek działania formuły znak ukośnika zostaje skasowany.

 

image15

 

Oprócz wykonywania operacji zastąpienia jednego tekstu drugim w przypadku pracy z danymi tekstowymi bardzo często dochodzi do sytuacji w której musimy dokonać wyszukania określonego ciągu znaków. W realizacji tego zadania pomogą Nam funkcję: ZNAJDŹ() oraz SZUKAJ.TEKST()

 

Funkcje ZNAJDŹ() oraz SZUKAJ.TEKST() umożliwiają określenie początkowej pozycji szukanego ciągu znaków, będącego częścią innego łańcucha.

 

Funkcja ZNAJDŹ(), której ogólna składnia prezentuje się następująco: ZNAJDŹ(tekst_szukany;w_tekście;[nr_poz_pocz]) zwraca pozycję początkową jednego ciągu tekstowego w drugim ciągu tekstowym. Funkcja opcjonalnie umożliwia definicję znaku, od którego ma rozpocząć się wyszukiwanie. Funkcja ta rozróżnia wielkie i małe litery. Nie można za jej pomocą przeprowadzić wyszukiwania wieloznacznego.

 

Argumenty funkcji to:

tekst_szukany - tekst, który ma zostać odnaleziony,

w_tekście - tekst/adres komórki przeszukiwania,

nr_poz_pocz - parametr opcjonalny, definiujący znak, od którego ma się rozpocząć wyszukiwanie (domyślnie w przypadku braku definicji tego argumentu przyjmuje się wyszukiwanie od pierwszego znaku).

 

Dwa przykłady pokazujące działanie funkcji ZNAJDŹ() zostały zaprezentowane na zrzucie poniżej.

 

image16

 

Przykład 1: formuła: =ZNAJDŹ("abc";C20) - zostaje przeprowadzone wyszukanie ciągu liter: abc Po wywołaniu formuły zwracana jest wartość jego pozycji. Jak można zauważyć wielkość liter ma znaczenie gdyż w sytuacji wystąpienia łańcucha liter ABC zwracany jest błąd #ARG! (zresztą tak jak i w innych przypadkach),

 

Przykład 2: =FRAGMENT.TEKSTU(C30;1;ZNAJDŹ("-";C30;1)-1) - w połączeniu z funkcją FRAGMENT.TEKSTU() zostaje wykonana operacja wyodrębnienia pierwszego członu numeru partii. Funkcja ZNAJDŹ() wyszukuje pozycję wystąpienia pierwszego znaku: - (w tym przypadku zwracana jest wartość 4) i od tej pozycji jest odejmowane 1 (czwarta pozycja znaku minus jeden = 3) w kolejnym kroku tak wyliczona wartość jest przekazywana do funkcji FRAGMENT.TEKSTU().

 

Funkcja SZUKAJ.TEKST() podobnie jak wyżej przedstawiona ZNAJDŹ() zwraca numer znaku, który rozpoczyna ciąg znaków znaleziony w innym łańcuchu znaków. W przypadku tej funkcji również można określić pozycję znaku, od którego ma być rozpoczęte wyszukiwanie, lecz funkcja ta nie rozróżnia wielkich i małych liter, ale rekompensatą jest możliwość przeprowadzenia wyszukania wieloznacznego.

 

Składnia funkcji jest następująca: SZUKAJ.TEKST(tekst_szukany;w_tekście;[nr_poz_pocz])

 

Parametry:

tekst_szukany - szukany ciąg znaków,

w_tekście - przeszukiwany tekst bądź adres komórki w której tekst się znajduje,

nr_poz_pocz - parametr opcjonalny, określający numer znaku od którego ma nastąpić przeszukiwanie.

 

Przykłady użycia funkcji SZUKAJ.TEKST() zostały pokazana na zrzucie poniżej.

 

image17

 

Przykład 1: formuła: =SZUKAJ.TEKST("abc";I20) - zostaje przeprowadzone wyszukanie ciągu liter: abc Po wywołaniu formuły zwracana jest wartość jego pozycji. Jak można zauważyć wielkość liter nie ma znaczenie gdyż w każdej sytuacji niezależnie od użytej wielkości liter zwracana jest pozycja wystąpienia szukanego ciągu. W przypadku wystąpienia braku dopasowania zwracany jest błąd #ARG!

 

Przykład 2: =SZUKAJ.TEKST("?-?";I30;1) - formuła wyszukuje wystąpienie znaku myślnika (-) Do jej zbudowania został wykorzystany symbol wyszukiwania wieloznacznego: znak zapytania (?). Przy budowaniu formuły z użyciem funkcji SZUKAJ.TEKST() można użyć dwóch symboli umożliwiających przeprowadzenie wyszukania wieloznacznego:

  • znak zapytania (?) - zastępuje jeden dowolny znak,
  • gwiazdka (*) - zastępuje dowolną sekwencję znaków.

 

W sytuacji w której wyszukanym symbolem ma być znak zapytania bądź gwiazdki umieszczamy przed nim znak tyldy (~).

 

Kolejną funkcją wspomagającą Nas w wyszukiwaniu zawartości jest funkcja: PODAJ.POZYCJĘ() Funkcja ta bardzo często łączona jest z funkcją: INDEKS().

 

Rozpoczniemy od omówienia funkcji INDEKS() a następnie przejdziemy do funkcji PODAJ.POZYCJĘ() zaś w kroku kolejnym wykonamy zadanie w którym wykorzystamy obie te funkcje.

 

Składnia funkcji INDEKS() wygląda następująco: INDEKS(tablica;nr_wiersza;[nr_kolumny]) a jej zadaniem jest zwrócenie wartości komórki znajdującej się na przecięciu zdefiniowanego numeru wiersza i numeru kolumny.

 

Składnia funkcji INDEKS() obejmuje następujące argumenty:

tablica - zakres komórek,

nr_wiersza - wskazuje wiersz tablicy,

nr_kolumny - wskazuje kolumnę tablicy.

 

Jeśli argument tablica zawiera tylko jeden wiersz lub jedną kolumnę, to odpowiednio argument nr_wiersza lub nr_kolumny jest argumentem opcjonalnym.

 

Przyjrzyjmy się zatem przedstawionemu poniżej przykładowi. Do komórki V7 została wprowadzona formuła: =INDEKS(Q6:T9;2;3) efektem wprowadzonego polecenia jest uzyskanie wartości: b3 Stało się tak gdyż wartość: b3 znajduje się w komórce, która leży na przecięciu drugiego wiersza (w zdefiniowanej tabeli został pominięty nagłówek tabeli) i trzeciej kolumny w tablicy obejmującej komórki od Q6 do T9

 

image18

 

Tak więc wiemy jak już działa funkcja INDEKS() wykorzystajmy ją w jakimś konkretnym przykładzie. Przypuśćmy, że mamy bazę części obejmującą ich sprzedaż w rozbiciu na lata i chcemy sprawdzić ile sztuk produktu udało się sprzedać. Przykład przeprowadzimy na produkcie: nakrętka m10 i roku 2013.

 

Poniżej tabeli zostały dodane trzy pozycje:

LP - numer kolejny produktu, który tożsamy jest z numerem wiersza

Rok - numer kolumny interesującego nas roku

Sztuki - wynik czyli ilość sprzedanych sztuk.

 

Chcąc poznać ilość sztuk produktu, która została sprzedana w pole LP została wpisana wartość 6 gdyż nakrętka m10 znajduje się w szóstym wierszu tabeli (nagłówek tabeli nie został wykorzystany) zaś w polu Rok pojawiła się wartość 5 gdyż rok 2013 to piąta kolumna tabeli.

 

Formuła wyszukująca ilość sztuk towaru przyjęła postać: =INDEKS(F6:L15;H20;H21)

 

image19

 

 

Na tym etapie korzystanie z tak przygotowanego zestawienia jest to powiedzmy sobie szczerze - mało intuicyjne i mało wygodne gdyż by poznać wartość musimy ręcznie ustalić wartość wiersza i kolumny. Ale bez obaw do przykładu zaraz wrócimy.

 

Drugą wspomnianą przeze mnie funkcją jest funkcja: PODAJ.POZYCJĘ()

 

Jej składnia przedstawia się następująco: PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;[typ_porównania])

 

W składni funkcji PODAJ.POZYCJĘ występują następujące argumenty:

szukana_wartość - szukana wartość, której przeszukiwanie następuje w zdefiniowanym zakresie określonym przez parametr przeszukiwana_tablica. Parametr ten może być definiowany jako: liczba, tekst lub wartość logiczna a także stanowić odwołanie do innej komórki,

przeszukiwana_tablica - zakres komórek w których ma nastąpić przeszukanie,

typ_porównania - wartość opcjonalna, określająca sposób w jaki następuje porównanie. Dozwolone wartości to: 1; 0 oraz -1. Wartość domyślna tego argumentu to 1 co oznacza, że funkcja odszuka największą wartość, która jest mniejsza lub równa wartości szukana_wartość. Wartości argumentu przeszukiwana_tablica muszą być uporządkowane w kolejności rosnącej. Parametr: 0 oznacza wyszukiwanie do pierwszego dopasowania. W tym przypadku wartości w argumencie przeszukiwana_tablica mogą być umieszczone w kolejności dowolnej. Zaś parametr: -1 nakazuje odszukanie wartości najmniejszej, która jest większa lub równa wartości szukana_wartość. Wartości w argumencie przeszukiwana_tablica muszą być uporządkowane w kolejności malejącej.

 

W poniższym przykładzie w komórce V19 została wpisana formuła: =PODAJ.POZYCJĘ("b3";Q19:T19) Formuła szuka wartości: b3 w tablicy zdefiniowanej jako zakres komórek od Q19 do T19 (szary obszar). Wynikiem formuły jest wartość 3 gdyż szukana wartość znajduje się w trzeciej komórce.

 

image20

 

W drugim przykładzie dzięki zastosowaniu formuły: =PODAJ.POZYCJĘ("b3";S18:S21) wynikiem jest liczba 2 gdyż w tablicy szukana wartość znajduje się w drugiej komórce.

 

image21

 

OK to już wiemy jak działają funkcje INDEKS() oraz PODAJ.POZYCJĘ() pora więc wrócić do naszego przykładu z częściami i zdobytą wiedzę wykorzystać w działaniu - przyszła pora aby obie funkcje ze sobą połączyć.

 

W komórce H50 znajduje się formuła: =INDEKS(F34:L43;PODAJ.POZYCJĘ(H48;G34:G43;0);PODAJ.POZYCJĘ(H49;F33:L33;0)) Wygląda skomplikowanie ale rozbijemy ją na części pierwsze i wszystko stanie się jasne.

 

Na początek co tak naprawdę formuła wykonuje - zadaniem jej jest podanie (tak jak w przykładzie wyżej) odszukanie ilości sztuk sprzedanego produktu w danym roku (ponownie rok 2013 i nakrętka m10). Lecz tym razem nie wpisujemy numeru wiersza i kolumny tylko numer danej części (komórka H48) oraz rok (komórka H49).

 

Tak więc do komórki H48 została wpisana wartość: 3433 gdyż jest to numer przydzielony produktowi nakrętka m10 oraz do komórki H49 wartość: 2013

 

Aby odnaleźć sprzedaną liczbę sztuk detalu do funkcji INDEKS() musimy przekazać wartość parametru: nr_wiersza oraz nr_kolumny Oba argumenty są uzyskiwane dzięki formule w której została wykorzystana funkcja PODAJ.POZYCJĘ().

 

Pierwsza formuła: PODAJ.POZYCJĘ(H48;G34:G43;0) powoduje odszukanie pozycji wartości: 3433 w tablicy, której zakres został zdefiniowany od komórki G34 do G43. Typ dopasowania został ustalony na 0 (choć tak naprawdę można by było parametr ten pominąć i przyjąć wartość domyślną gdyż numery części są posortowane w kolejności rosnącej). Numer części jest wartością unikalną (zakładamy, że numer części jest zawsze przypisany tylko jednemu produktowi). Efektem działania tej formuł jest uzyskanie wartości: 6 (wartość w szóstym wierszu tabeli).

 

Druga część formuły: PODAJ.POZYCJĘ(H49;F33:L33;0) realizuje podobne zadanie lecz w kontekście ustalenia numeru kolumny w której występuje data roku czyli wartość: 2013 Formuła zwraca wartość: 5

 

Wykorzystane formuły przekazują wartości do funkcji INDEKS(). Tak więc naprawdę, uproszczając całą formułę jest realizowana funkcja: =INDEKS(F34:L43;6;5) Efektem jej jest uzyskanie wartości: 4351 leżącej w szóstym wierszu i piątej kolumnie w tablicy określonej poprzez zakres F34:L43.

 

Mam nadzieję, że teraz wszystko wydaje się prostsze.

 

image22

 

Oprócz funkcji PODAJ.POZYCJĘ istnieje jeszcze funkcja POZYCJA() a zadaniem tej funkcji jest zwrócenie pozycji zdefiniowanej liczby w odniesieniu do listy wartości. Mówiąc bardziej obrazowo gdybyśmy wykonali operację sortowania listy, pozycja liczby oznaczałaby jej miejsce na liście po sortowaniu.

 

Ogólna składnia funkcji wygląda następująco: POZYCJA(liczba;lista;[lp])

 

W składni funkcji POZYCJA() występują następujące argumenty:

liczba - liczba (bądź adres komórki w której została ona wpisana), której pozycja ma zostać określona,

lista - tablica albo odwołanie do listy liczb (wartości nie liczbowe zostaną zignorowane).

lp - parametr opcjonalny - argument określający sposób określania pozycji liczby. Jeżeli argument lp jest równy 0 lub został pominięty, pozycja liczby jest ustalana w oparciu o listę sortowaną w kolejności malejącej. Jeżeli zaś parametr przyjmie wartość 1 stosowane jest sortowanie w kolejności rosnącej.

 

Poniżej trzy przykłady użycia funkcji POZYCJA()

 

image23

 

Przykład 1: formuła: =POZYCJA(2143;E4:E9) - naszym zadaniem jest określenie pozycji wyniku Tadeusza Nowaka zakładając, że wygrywa osoba z większą ilością punktów. Wpisując formułę jej wynik zostaje ustalony na 3 - wartość 2143 przy sortowaniu malejącym w zdefiniowanej liście liczb (zakres komórek od E4 do E9) ma pozycję numer trzy.

 

Przykład 2: formuła: =POZYCJA(2143;E4:E9;1) - sytuacja podobna tylko z tą różnicą iż wygrywa osoba z mniejszą ilością punktów. Do formuły został dołączony parametr: lp nakazujący odnaleźć pozycję szukanej wartości ale z wykorzystaniem sortowania rosnącego. Wynikiem działania formuły jest wartość: 4.

 

Przykład 3: formuła: =POZYCJA(E15;E$15:E$20) - określenie zdobytego miejsca (wygrywa ten kto ma więcej punktów).

 

Ostatnimi funkcjami jakie chciałbym przedstawić są dwie funkcje WYSZUKAJ.PIONOWO() oraz WYSZUKAJ.POZIOMO()

 

Zadaniem funkcji WYSZUKAJ.PIONOWO() jest odnalezienie szukanej wartości w pierwszej kolumnie tabeli lecz zwrócenie odpowiadającej jej wartości z kolumny innej. Funkcja przeszukuje tabelę z góry na dół (stąd słowo PIONOWO w nazwie funkcji). Składnia funkcji WYSZUKAJ.PIONOWO() jest następująca: WYSZUKAJ.PIONOWO(szukana_wartość;tablica;nr_indeksu_kolumny;[przeszukiwany_zakres])

 

Kolejne argumenty funkcji WYSZUKAJ.PIONOWO() to:

szukana_wartość - odszukiwana wartość w pierwszej kolumnie tabeli. Szukana wartość powinna zawsze znajdować się w pierwszej kolumnie tablicy oznacza to, że gdy szukana wartość znajduje się w komórce B2, to tabela powinna rozpoczynać się od kolumny B,

tabela - zakres komórek określający tabelę,

nr_indeksu_kolumny - numer kolumny z której zostanie zwrócona wartość. Jeśli tabelą jest zakres C3: E20, kolumnę C liczymy jako pierwszą kolumnę, D jako drugą i tak dalej.

przeszukiwany_zakres - argument opcjonalny. Obejmuje ustawienie flagi PRAWDA - dopasowanie przybliżone (pierwsza wartość większa niż Szukana_wartość), lub flagi FAŁSZ - dopasowanie dokładne (brak dopasowania, wyświetli w wyniku błąd #N/D!). Jeśli parametr w definicji funkcji zostanie pominięty domyślnie przyjmowaną opcją jest PRAWDA, czyli dopasowanie przybliżone. Użycie flagi PRAWDA wymusza na Nas wykonanie sortowania w porządku rosnącym dla pierwszej kolumny przeszukiwanej tabeli.

 

To tradycyjnie już przykład (a nawet dwa).

 

image24

 

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(K16;J3:N12;2) - użyta formuła zwraca wartość 39 - dzieje się tak ponieważ szukaną przez Nas wartością jest ta wpisana w komórkę K16 (czyli 5), tabela obejmuje zakres komórek od J3 do N12 (kolumna J jest liczona jako pierwsza) zaś parametr nr_indeksu_kolumny został określona jako 2. Mówiąc prościej realizowane jest zadanie zwrócenia wartości z drugiej kolumny w przypadku wystąpienia dopasowania. Ponieważ dla wartości szukanej w drugiej kolumnie znajduje się 39 toteż ta wartość jest wynikiem formuły.

 

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(K16;J3:N12;4) - przykład analogiczny z tym powyżej lecz tym razem zwracana jest wartość z kolumny 4 czyli 36

 

Kolejny przykład pokazuje nieprawidłowe działanie funkcji WYSZUKAJ.PIONOWO()

 

image25

 

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(K37;J24:N33;3) - formuła ta nie działa tak jakbyśmy tego oczekiwali gdyż dla szukanej wartości (6) zwróconą wartością jest 53 a po analizie Naszej tablicy danych dochodzimy, że powinna to być wartość 61. Formuła ta nie działa ponieważ parametr nr_indeksu_kolumny nie został zdefiniowany czyli została przyjęta wartość domyślna (PRAWDA) a pierwsza kolumna naszej tablicy nie jest posortowana w kolejności rosnącej.

 

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(K37;J24:N33;3;FAŁSZ) - tym razem zwrócona wartość jest prawidłowa gdyż zastosowanie flagi FAŁSZ dla parametru nr_indeksu_kolumny włącza dopasowanie dokładne (pierwsza kolumna nie musi podlegać sortowaniu).

 

No to ostatni przykład.

image26

 

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(C34;B25:D30;2;FAŁSZ) - dysponujemy listą pracowników i zadaniem jest odszukanie numeru ID pracownika na podstawie wpisanego nazwiska i imienia (komórka C34). Wynikiem działania formuły jest wyświetlenie ID pracownika. Została użyta flaga FAŁSZ gdyż dane w kolumnie pierwszej nie są posortowane.

 

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(C34;B25:D30;3;FAŁSZ) - przykład analogiczny do powyższego lecz tym razem szukaną informacją jest numer PESEL pracownika.

 

Poniżej powielenie obu przykładów i jak widać funkcja WYSZUKAJ.PIONOWO() nie rozróżnia wielkości liter.

 

Jeszcze na chwilę zatrzymajmy się przy tej funkcji gdyż osobnego omówienia wymaga sytuacja w której w przeszukiwanej tablicy pojawi się pusta komórka. Tak jak to ma miejsce w poniższym przykładzie. Jak można zauważyć przy pracowniku Gazda Beata brak jest numeru PESEL. Formuła wpisana w komórkę C58 (=WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ)) zwraca wartość 0.

 

image27

 

Dzieje się tak ponieważ Excel traktuje puste komórki w obszarze wyników jako zera. Uniknięcia tego typu przypadku pozwoli nam formuła, która wygląda następująco: =JEŻELI(WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ)="";"BRAK NUMERU PESEL";(WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ))) Jak można zauważyć problem rozwiązuje funkcja JEŻELI() - jeżeli brak jest numeru PESEL (czyli komórka jest pusta) zostaje wypisany stosowny komunikat (sytuacja w przypadku pracownika Gazda Beata) jeśli numer PESEL został wpisany zostaje on wyświetlony (pracownik Nowak Tadeusz).

 

image28

 

Choć w tym zadaniu można pokusić się o dodatkowe poprawienie formuły. Bo co w przypadku w którym numer PESEL jest wpisany tylko na wskutek pomyłki została pominięta np. jedna cyfra? Na szczęście błąd ten (jak to ma miejsce w przypadku pracownika Polak Michał - numer PESEL liczy 10 cyfr a nie 11) łatwo jest zweryfikować. Wystarczy, że dodamy jeszcze jedną funkcję JEŻELI(), której zadaniem będzie sprawdzenie poprawności numeru (a raczej ilości cyfr z których się on składa). Formuła, która zrealizuje zadanie po modyfikacji z tą przedstawioną poprzednio przyjmie postać: =JEŻELI(WYSZUKAJ.PIONOWO(C82;B67:D72;3;FAŁSZ)="";"BRAK NUMERU PESEL";JEŻELI(DŁ(D71)=11;WYSZUKAJ.PIONOWO(C82;B67:D72;3;FAŁSZ);"BŁĄD PESEL")) W przypadku wystąpienia błędu w numerze PESEL zostanie wyświetlony komunikat: BŁĄD PESEL

 

image29

 

Bliźniaczą funkcją jest funkcja: WYSZUKAJ.POZIOMO() Działa ona na tych samych zasadach co WYSZUKAJ.PIONOWO() lecz przeszukiwanie tabeli nie odbywa się pionowo lecz poziomo - wyszukiwana wartość umiejscowiono jest w pierwszym wierszu tabeli a zwracana jest odpowiadająca jej wartość z ustalonego wiersza.

 

Składnia funkcji jest następująca: WYSZUKAJ.POZIOMO(odniesienie;tablica;nr_wiersza;[wiersz])

 

Kolejne argumenty funkcji WYSZUKAJ.POZIOMO() to:

odniesienie - odszukiwana wartość w pierwszej kolumnie tabeli. Szukana wartość powinna zawsze znajdować się w pierwszym wierszu tablicy - oznacza to, że gdy szukana wartość znajduje się w komórce B2, to tabela powinna rozpoczynać się od wiersza 2,

tablica - zakres komórek określający tabelę,

nr_wiersza - numer wiersza z którego zostanie zwrócona wartość. Jeśli tabelą jest zakres C3: E20, wiersz 3 liczymy jako wiersz pierwszy, 4 jako drugi i tak dalej.

wiersz - argument opcjonalny. Obejmuje ustawienie flagi PRAWDA - dopasowanie przybliżone (pierwsza wartość większa niż odniesienie), lub flagi FAŁSZ - dopasowanie dokładne (brak dopasowania, wyświetli w wyniku błąd #N/D!). Jeśli parametr w definicji funkcji zostanie pominięty domyślnie przyjmowaną opcją jest PRAWDA, czyli dopasowanie przybliżone. Użycie flagi PRAWDA wymusza na Nas wykonanie sortowania w porządku rosnącym dla pierwszego wiersza przeszukiwanej tabeli.

 

I podobnie jak w przypadku funkcji WYSZUKAJ.PIONOWO() teksty pisane dużymi i małymi literami są równoważne.

 

No to zilustrujmy działanie tej funkcji.

 

image30

 

Przykład 1: - formuła: =WYSZUKAJ.POZIOMO(S16;R2:V12;6) - formuła dla szukanej wartości wpisanej w komórkę S16 (Dane 2) zwraca wynik 21 gdyż w przeszukiwanej tablicy liczba ta znajduje się w szóstym wierszu.

 

Przykład 2: - formuła: =WYSZUKAJ.POZIOMO(S16;Q2:V12;3) - podobnie jak w przykładzie powyżej lecz w tym przypadku zwracana jest liczba znajdująca się w wierszu 3.

 

To przeanalizujmy jeszcze jeden przykład dotyczący funkcji WYSZUKAJ.POZIOMO(). Mamy listę uczniów, którym należy wystawić ocenę w zależności od ilości zdobytych punktów. Kryterium przydzielania oceny wygląda następująco:

  • 0-24 pkt - ocena: 1
  • 25-44 pkt - ocena: 2
  • 45-69 pkt - ocena: 3
  • 70-89 pkt - ocena: 4
  • 90-97 pkt - ocena: 5
  • 98-100 pkt - ocena: 6

 

Formuła, która zrealizuje nasze zadanie przyjmie postać: =WYSZUKAJ.POZIOMO(T28;$S34:$Y35;2) (komórka T29) Wartość ilości zdobytych punktów przez danego ucznia jest porównywana z tabelą punktacji (zakres komórek od S34 do Y35). Po dokonaniu porównania do danej liczby punktów przypisywana jest ocena.

 

image31

 

Te samą operację wykonamy wpisując tabelę w formułę (zamiast funkcji WYSZUKAJ.POZIOMO() stosujemy WYSZUKAJ.PIONOWO()): =WYSZUKAJ.PIONOWO(T28;{0\"1";25\"2";45\"3";70\"4";90\"5";98\"6"};2) - więcej o definicji tabel znajdziesz w wpisie: Excel w zadaniach. Funkcje tablicowe

 

image32

 

Ewentualnie to samo zadanie możemy zrealizować z wykorzystaniem funkcji WYSZUKAJ() Formuła realizująca te zadanie przyjmie postać: =WYSZUKAJ(T40;{0;25;45;70;90;98};{1;2;3;4;5;6})

 

image33

 

Funkcji WYSZUKAJ() jeszcze nie omówiłem więc wypadałoby kilka słów o niej powiedzieć.

 

Funkcja WYSZUKAJ() działa w ten sposób, że przeszukuje zakres w postaci jednego wiersza lub jednej kolumny wartości i zwraca wartość z tej samej pozycji w drugim obszarze.

 

Składnia funkcji WYSZUKAJ() jest następująca:

WYSZUKAJ(Szukana_wartość;Przeszukiwany_wektor;Wektor_wynikowy)

 

Argumenty funkcji:

szukana_wartość - wartość szukana, która ma zostać odnaleziona w zakresie określonym w argumencie przeszukiwany_wektor.

przeszukiwany_wektor - pojedynczy wiersz lub kolumna, w którym znajdują się wartości podlegające procesowi przeszukania. Wartości te należy ułożyć w porządku rosnącym.

wektor_wynikowy - pojedynczy wiersz lub kolumna zawierająca wartości, które zostaną zwrócone. Rozmiar zdefiniowanego obszaru musi być tożsamy z tym ustalonym przy definicji parametru przeszukiwany_wektor.

 

Poniżej przykład z wykorzystaniem funkcji WYSZUKAJ(). Założenia ćwiczenia są takie iż na podstawie wpisanej liczby nadgodzin należy przeszeregować odpowiednią wartość przyznanej premii. Liczby nadgodzin i odpowiadający im procent premii zostały zebrane w tabeli. Formuła realizująca te zadanie ma postać: =WYSZUKAJ(C2;E2:E6;G2:G6)

 

image34

 

Formuł ta wyświetla prawidłowe wartości przyznanej premii gdyż funkcja WYSZUKA() działa w ten sposób, że dopasowuje najbliższą najmniejszą znalezioną wartość.

 

I na tym etapie już kolejny mój wpis na temat Excela chciałbym zakończyć. Mam nadzieję, że przedstawione przykłady użycia funkcji tekstowych i wyszukujących pozwolą Wam je efektywnie wykorzystać do budowy swoich własnych arkuszy.

Ostatnio zmieniany środa, 19 lipiec 2017 11:48
Etykiety
  • arkusz kalkulacyjny
  • EXCEL
  • MSOffice
  • JEŻELI
  • LUB
  • DŁ
  • FRAGMENTTEKSTU
  • LEWY
  • PRAWY
  • LITERYWIELKIE
  • LITERYMAŁE
  • ZWIELKIEJLITERY
  • PODSTAW
  • ZASTĄP
  • ZNAJDŹ
  • SZUKAJTEKST
  • funkcja
  • formuła
  • INDEKS
  • PODAJPOZYCJĘ
  • POZYCJA
  • WYSZUKAJPIONOWO
  • WYSZUKAJPOZIOMO
  • WYSZUKAJ

Artykuły powiązane

  • Łączenie liczb i tekstu.
  • Jak usunąć puste komórki?
  • Excel w zadaniach. Tworzenie i modyfikacja wykresów.
  • Bezstresowa prezentacja.
  • Eksport plików obrazów z dokumentów pakietu MSOffice oraz LibreOffice.
Więcej w tej kategorii: « Excel w zadaniach. Tworzenie i modyfikacja wykresów.

Dodaj komentarz



Odśwież

Wyślij
Skasuj
JComments
Powrót na górę

Wujek dobra rada

Szybkie pytania i szybkie odpowiedzi czyli garść porad do wykorzystania w codziennej pracy z komputerem.

  • Jak utworzyć RAMdysk w systemie Windows? Jak utworzyć RAMdysk w systemie Windows?

    RAMdysk jest wydzieloną częścią pamięci, która w systemie operacyjnym jest widziana jak kolejny dysk/partycja. Praca z tak wydzielona przestrzenią pamięci odbywa się jak z normalnym dyskiem. Dostępne są wszystkie operacje związane z plikami.  

    Napisano poniedziałek, 04 grudzień 2017 21:44
  • Bezpieczny pendrive Bezpieczny pendrive

    Jak zabezpieczyć nasze dane w sytuacji utraty pendiva/karty pamięci.

    Napisano czwartek, 29 czerwiec 2017 12:00
  • Wyszukiwanie plików w systemie Windows Wyszukiwanie plików w systemie Windows

    Krótki opis jak wyszukać pliki przy wykorzystaniu Eksploratora plików.

    Napisano sobota, 17 czerwiec 2017 20:31
  • Diagnostyka pamięci RAM Diagnostyka pamięci RAM

    Jak zdiagnozować uszkodzenie modułu pamięci RAM

    Napisano wtorek, 16 maj 2017 12:39
  • Konwersja maszyny fizycznej na wirtualną (odsłona druga). Konwersja maszyny fizycznej na wirtualną (odsłona druga).

    W poprzednim wpisie (Konwersja maszyny fizycznej na wirtualną) opisałem konwersję maszyny fizycznej do wirtualnej, efektem Naszych działań było przeniesienie systemu działającego na fizycznym hoście do środowiska opartego o oprogramowanie Vmware. Zaś w tym wpisie wykonamy podobne działanie lecz efektem będzie uzyskanie maszyny działającej w VirtualBox.

    Napisano czwartek, 04 maj 2017 11:53
Czytaj więcej...

Najczęściej komentowane

  • Jak wyznaczyć broadcast, adres sieci i liczbę hostów? (+19)
  • Instalacja Windows XP/Vista/7 z pendriv'a. (+12)
  • Dostęp zdalny oraz prawa użytkownika w urządzeniach CISCO (+12)
  • Co w sieci siedzi. Protokół DNS. (+10)
  • Windows i Linux w jednej stali sieci. (+8)

Najnowsze komentarze

  • Dzak 07.09.2020 17:32
    Witam. Nie rozumiem dlaczego zamiast podziału na podsieci nie możemy po prostu ustanowić 7 lokalnych ...
     
  • fgm 03.09.2020 06:47
    jak nie pamietam daty rozszezenia i dokladnej nazwy tylko podobna to jak wyszukac taki plik lub wiele ...
     
  • Andrzej 13.08.2020 07:26
    Usunięcie x z /etc/passwd uważam za niebezpieczne rozwiązanie. Ponieważ po takiej operacji i ustawieniu ...
     
  • Andrzej 13.08.2020 07:15
    To zdanie Utworzenie użytkownika w ten sposób powoduje wyłączenie konta poprzez wstawienie znaku x w ...
     
  • goodbye world 01.07.2020 10:20
    Będą jakieś nowe wpisy?

Ostatnio komentowane

  • Słów kilka o adresacji sieci. (3)
  • Wyszukiwanie plików w systemie Windows (1)
  • Dogadać się z Linuksem. Zarządzanie kontem użytkownika. (3)
  • Yubico czyli jak chronić dostęp do naszych kont (6)
  • Atak na warstwę 2 modelu ISO/OSI - preludium (4)

Popularne tagi

80211 Active Directory arkusz kalkulacyjny CISCO cmd DHCP domena EXCEL filtrowanie formuła FTP funkcja GPO grupy jednostka organizacyjna JEŻELI kontroler LibreOffice Linux MSOffice panel sterowania PowerShell przełącznik rejestr Router Serwer SUMA switch TCP trunk Ubuntu UDP usługi VirtualBox VLAN warstwa 2 warstwa 3 warstwa sieciowa warstwa łącza danych wifi Windows wirtualizacja WORD zakres ŚREDNIA

UWAGA! Ten serwis używa cookies

Brak zmiany ustawienia przeglądarki oznacza zgodę na to.

Zrozumiałem

Created by: clivio.pl

Copyright © Created by: 2022 All rights reserved. Custom Design by Youjoomla.com
Home