Załącznik z przykładami dla tego rozdziału znajduje się w tym miejscu.

WYSZUKAJ.PIONOWO/WYSZUKAJ.POZIOMO

Dwie bardzo podobne funkcje, które różnią się przede wszystkim tym, że jedna wyszukuje w kolumnach, druga we wierszach. Ponadto funkcja WYSZUKAJ.PIONOWO działa też dla tabel importowanych z nazwanymi nagłówkami kolumn (może używać ich jako argumentów) - jednak my skupimy się na ich podstawowej funkcjonalności, która powoduje że jedyną różnicą w działaniu i zastosowaniu jest kierunek wyszukiwania. Funkcje te służą znajdowaniu określonych wartości w tabelach na podstawie zadanych kryteriów: w pierwszej kolumnie/wierszu tabeli szukamy określonej wartości lub łańcucha znaków, a następnie funkcja zwraca wartość komórki położonej w tym samym wierszu/kolumnie oddalonej o określoną liczbę kolumn/wierszy.
Obie funkcje posiadają 4 argumenty (przy czym 3 spośród nich są wymagane, ostatni jest opcjonalny) - poniżej krótkie omówienie argumentów, z rozróżnieniem nazw (pierwsza wersja dotyczy WYSZUKAJ.PIONOWO, druga WYSZUKAJ.POZIOMO):

• szukana_wartość/odniesienie - oznacza kryterium wg którego będzie prowadzone wyszukiwanie - dopuszczalne jest odwołanie do pojedynczej komórki (pomijamy importowane tabele i zakresy nazwane) lub podane określonego argumentu wprost.
• tabela_tablica/tablica - zakres który jest tabelą. Najważniejsze jest to, żeby pamiętać, iż zakres musi się zaczynać od kolumny/wiersza w którym będziemy szukać podanego argumentem powyżej kryterium.
• nr_indeksu_kolumny/nr_wiersza - w tym miejscu podajemy za pomocą odwołania lub wprost numer wiersza lub kolumny, z której ma być zwrócona wartość. Numer jest liczony od kolumny lub wiersza, który jest początkiem zakresu podanego w argumencie powyżej.
• przeszukiwany zakres/wiersz - opcjonalny argument, określający czy dopasowanie szukania ma być dokładne, czy też nie.

Przykłady użycia obu funkcji w różnych kombinacjach prezentuje poniższy przykład:

Zaprezentowany przykład jest też zawarty w załączniku. Użycie funkcji WYSZUKAJ.PIONOWO w komórkach C15 i C16 daje prawidłowe wyniki znajdując liczby z 3-ciej kolumny zaznaczonego na żółto zakresu położonych w tych samych wierszach, co wyrażenia "Warunek1" i "Warunek5" w pierwszej kolumnie tego samego zakresu.
Bardzo podobnie sytuacja wygląda przy użyciu WYSZUKAJ.POZIOMO w komórkach J15 i J16: odnajdując wartości z 3-go wiersza położonych w tych samych kolumnach co kryteria "Dane 1" i "Dane 4".

Funkcje zakładają, że dane w pierwszej kolumnie/wierszu będą posortowane narastająco (z góry na dół/z lewej w prawą stronę). W sytuacji, kiedy tak właśnie jest wszystko działa poprawnie (nawet, jeśli pominiemy ostatni argument).
Jednak w przypadku gdy nie mamy danych posortowanych (bo np. z jakiegoś powodu takich mieć nie możemy), trzeba koniecznie zdefiniować ostatni warunek jako FAŁSZ (lub wpisać "0" - jak w przykładzie). Wówczas spowodujemy, że obie funkcje będą działać poprawnie.
Skutki pominięcia ostatniego argumentu przedstawione są w komórkach C20 i C21 (WYSZUKAJ.PIONOWO) oraz J20 i J21 (WYSZUKAJ.POZIOMO). Mimo, że takie wartości "Warunek11" i "Dane 11" nie występują w tabelach, funkcje potraktowały "Warunek1" i "Dane 1" jako najbardziej podobne i zwróciły wynik, który w zdecydowanej większości sytuacji nie jest dla nas satysfakcjonujący. Osobiście, oczekuję zawsze od tych funkcji dokładnego dopasowania - dlatego zawsze używam zera jako ostatni argument.
Ostatnią, pewnie dość oczywistą informacją jest ta, iż funkcje zwracają zawsze pierwszą (od góry/od lewej) napotkaną wartość - gdyby kryterium występowało większą liczbę razy, inne niż pierwsze wystąpienie nie będzie wzięte pod uwagę.

INDEKS

Bardzo szybko działająca funkcja, która zwraca zawartość komórki z podanego zakresu/zakresów (który/które są tablicami danych) w podanym wierszu i kolumnie. Funkcja posiada dwie postacie:

Postać tablicowa

W tym przypadku funkcja wymaga trzech argumentów:

• tablica - zakres, który jest brany pod uwagę przy przeszukiwaniu.
• nr_wiersz - numer wiersza zakresu liczony od jego początku.
• nr_kolumny - numer kolumny zakresu liczony od jego początku.

Wynikiem użycia funkcji otrzymamy wartość komórki położonej na przecięciu nr wiersza i kolumny w zakresie podanym jako argument funkcji. Prezentuje to poniższy obraz:

Ten sam, powyższy przykład zawiera też drugą postać funkcji.:

Postać odwołaniowa

W tym przypadku mamy dwie różnice względem poprzedniej postaci:

• pierwszy argument może składać się z więcej niż jednego zakresu danych, ale muszą one być oddzielone średnikami i w nawiasie.
• dodatkowo funkcja wymaga czwartego argumentu, wskazującego, z którego z podanych zakresów danych ma zwrócić wartość komórki.

POZYCJA/PODAJ.POZYCJĘ

Dwie funkcje szukające określonego kryterium w pojedynczej kolumnie/wierszu.

Pierwsza z nich (POZYCJA) pozwala na znalezienie miejsca podanego kryterium (którym może być np. liczba, lub odwołanie do komórki z liczbą) na liście, która nie musi być posortowana. Posiada 3 argumenty:

• liczba - która jest szukana - może być podana wprost, lub jako odwołanie do komórki zawierającej liczbę.
• lista - zakres, który określa listę liczb do przeszukania.
• lp - opcjonalny określa, czy ma podać pozycję danej liczby tak, jakby wszystkie wśród których szuka, były posortowane malejąco (przyjmując wartość 0) lub rosnąco (1).

Innymi słowy jeśli mamy np. wiersz z liczbami od 2 do 20 (co 2), to liczba 12 jest 6-tą z kolei i taki wynik zwróci funkcja POZYCJA. Zostało to pokazane też w przykładzie, z którego obraz widoczny jest poniżej:

Funkcja PODAJ.POZYCJĘ (przykład również powyżej) działa dla liczb, ale i także dla łańcuchów znaków podając względną pozycję określonego kryterium na liście. Zawiera 3 argumenty, przy czym ostatni jest opcjonalny:

• szukana_wartość - kryterium, które będzie szukane w podanym zakresie.
• przeszukiwana_tab - zakres komórek, który będzie przeszukiwany.
• typ_porównania - argument opcjonalny - z podobnymi konsekwencjami użycia jak w przypadku funkcji WYSZUKAJ.PIONOWO: dla równego 0, będzie dokładne dopasowanie, dla -1 najmniejsza wartość, która jest większa od szukanej, jeśli 1 to będzie to pozycja największej wartości mniejszej od podanego kryterium lub samo kryterium (ale pewność będzie tylko w sytuacji, kiedy lista jest posortowana - stąd podobieństwo do funkcji wyszukujących).

PRZESUNIĘCIE

Bardzo użyteczna funkcja, która powoduje, że odwołanie do zawartości komórki może być łatwo modyfikowane, bez zmiany jego samego. Funkcja posiada, aż 5 argumentów - jednak ostatnie dwa: wysokość i szerokość są opcjonalne (ich podane może być przydatne podczas użycia funkcji PRZESUNIĘCIE dla określenia argumentu np. funkcji SUMA) i nie będziemy się nimi bliżej zajmować:

• odwołanie - odwołanie jako adres komórki lub zakres komórek (ale zakres tylko w przypadku kiedy PRZESUNIĘCIE jest argumentem funkcji agregującej).
• wiersze - liczba wierszy, o którą podane powyżej odwołanie ma być przesunięte.
• kolumny - liczba kolumn, o którą podane powyżej odwołanie ma być przesunięte.

W załączonym przykładzie pokazano wykorzystanie tej funkcji:

Komórka B18 zawiera zwykłe odwołanie do komórki B2, wyświetlając jej zawartość (wynoszącą 1 - zaznaczone na żółto). W komórce B20 użyto funkcji PRZESUNIĘCIE dla odwołania do komórki B2, ale z argumentami podanymi w komórkach G13 i G14. Przesunięcie odwołania do komórki B2 o 6 wierszy i 7 kolumn powoduje wyświetlenie zawartości komórki I8. Proponuję czytelnikowi poeksperymentować z plikiem i zmieniając parametry wiersze i kolumny (wspomniane G13 i G14), obserwować jak zmienia się wynik w G20.

ADRES/ADR.POŚR

Dosyć rzadko używane w praktyce funkcje. Ich zadaniem jest także "manipulowanie" odwołaniami, ale bardziej od strony adresu (jak sama nazwa wskazuje).

Funkcja ADRES zwraca adres określonej komórki w żądanym formacie. Poza podaniem argumentów nr_wiersza i nr_kolumny, czyli np. jeśli podamy 3 i 5 (jak w przykładzie) to funkcja zwróci domyślnie wartość $E$5 (użycie w E5 załącznika). Pozostałe parametry:

typ adresu - określenie od 1 do 4 rodzaju adresowania (względne, mieszane, bezwzględne).

a1 - określenie czy adres ma być zwrócony w formacie A1 (domyślny), czy też np. W1K1 (rzadko używany).

tekst_arkusz - podany tekst, który spowoduje, że do zwracanego łańcucha określającego adres dodany zostanie człon określający arkusz (taki jak argument).

Funkcja ADR.POŚR jest niejako uzupełnieniem poprzedniej. Zawiera tylko dwa argumenty (adres_tekst, który może być też odwołaniem do komórki zawierającej adres w odpowiednim formacie i jego typ: a1, czyli format adresu podanego w poprzednim argumencie). Użycie tej funkcji pozwoli na uzyskanie zawartości komórki z adresu podanego w pierwszym argumencie. Przykład podany został w załączniku: użycie jako pierwszego argumentu odwołania do komórki E5 spowodowało zwrócenie zawartości komórki E3, ze względu na fakt, iż komórka E5 zawiera adres właśnie od E3.

Transformacja danych

Poza powyższymi, oczywistymi przykładami użycia funkcji adresu, warto chwilę poświęcić praktycznemu przykładowi ich zastosowania. W załączniku zaprezentowano wykorzystanie obu funkcji (i dodatkowo funkcji PODAJ.POZYCJĘ i WIERSZ - funkcji, która zwraca nr wiersza argumentu, którym jest odwołanie) do przekształcenia nieregularnie ułożonych danych w formę bazodanową.
Załączony przykład jako dane wyjściowe ma tabelę z 6 kolumnami (pomijam nagłówki wierszy), w których w danym wierszu zawsze występuje najpierw tekst, a potem wartość, jednak teksty są w różnych kolumnach (np. XXX w pierwszym wierszu jest w kolumnie 1, w drugim w kolumnie 5, w trzecim ponownie w kolumnie 1), co uniemożliwia wykonanie jakiejkolwiek sensownej agregacji. Sprowadzenie takiej tabeli do normalnego wyglądu jest przedstawione poniżej, a załącznik zawiera schemat wykorzystania formuł w takim przypadku.
Cała rzecz sprowadza się do znalezienia nr kolumny, w której znajduje się żądany tekst i odtworzeniu danych "wiersz w wiersz" budując dzięki tej informacji adresy i odwołania z użyciem powyższych funkcji.

SZUKANIE W TABELI

Ostatni epizod tego rozdziału poświęcony będzie prostemu przykładowi wyszukiwania zarówno PIONOWO jak i POZIOMO. Załącznik zawiera dwa przykłady łączące funkcjonalność funkcji INDEKS lub WYSZKUAJ.PIONOWO z funkcją PODAJ.POZYCJĘ.

Jeśli użyjemy funkcji PODAJ.POZYCJĘ do określenia numeru wiersza lub kolumny - których to następnie użyjemy jako argumenty w funkcjach WYSZUKAJ.PIONOWO, bądź INDEKS - będziemy w stanie szukać po argumentach w obu wymiarach tabeli. Jedynym wymogiem jest to, aby tabela miała nagłówki wierszy i kolumn wg których będziemy szukać. Szczegóły w załączniku.