Uwaga: przeglądasz tę stronę na urządzeniu o niewielkim ekranie (szerokość < 640px). Niektóre zamieszczone w artykule ilustracje i animacje mogą stać się nieczytelne po dopasowaniu ich do rozdzielczości tego ekranu.

Yestok.pl

Jerzy Moruś

© Wszystkie prawa zastrzeżone. Wykorzystanie całości serwisu lub jego fragmentów bez pisemnej zgody autora zabronione.

Obliczenia warunkowe.



Opracowanie powstało w oparciu o wersję programu istniejącą w trakcie jego pisania, Libre Office: 6.4.7, Apache OpenOffice: 4.1.8.


Właściwie nie można spotkać arkusza kalkulacyjnego, w którym nie pojawiłaby się konieczność wykonania obliczeń uzależnionych od spełnienia jakiegoś warunku. Ta potrzeba może być dwojakiego rodzaju. Pierwsza to taka, kiedy ma zostać wykonany konkretny algorytm, ale na zestawie danych spełniających określone warunki. Druga, to taka, która wymaga podania odmiennych wyników uzależnionych od spełnienia bądź nie, określonych warunków.

Wykonanie obliczeń warunkowych umożliwia wiele funkcji i w tym opracowaniu postaram się je przedstawić. Funkcje przedstawię w podziale na dwie główne grupy, odpowiadające wspomnianym powyżej potrzebom.

Dla wszystkich omawianych funkcji przytaczam także (w nawiasach) ich angielskie nazwy.

Funkcje te, analogicznie do sytuacji, w których obliczenia warunkowe mają być wykonane, można także podzielić na dwie grupy. Pierwsza to funkcje, które wykonują obliczenia na podstawie warunków, jakie muszą spełnić dane pobierane do tych obliczeń. Druga to funkcje, które mogą zmienić algorytm obliczeń w zależności od spełnienia określonych warunków.

Grupa pierwsza.

Na potrzeby omówienia funkcji należących do tej grupy przygotowałem (całkowicie zmyślone) zestawienie sprzedanego obuwia. Przedstawia je ilustracja zamieszczona poniżej. Zestawienie zawiera nazwę producenta, kategorię obuwia, rodzaj, rozmiar, cenę, ilość sprzedanych par i i wartość sprzedaży.

Przykładowy zestaw danych
Rys. 1: Przykładowa tabela danych.

Funkcje opisane w tej grupie wyliczają, zadeklarowaną w definicji funkcji, konkretną wartość liczbową. Wynik zależy jednak od spełnienia przez analizowane dane określonych kryteriów. Od funkcji zależy tylko rodzaj wyniku. Funkcje obsługują argumenty w postaci wyrażenia regularnego. Jednak aby to było możliwe, należy zaznaczyć odpowiednia opcję: „Narzędzia → Opcje → (x)Office Calc → Oblicz → Włącz wyrażenia regularne w formułach”.

Wszystkie przykłady odnoszą się do zestawu danych przedstawionych na rys. 1.

Funkcje z jednym warunkiem.

LICZ.JEŻELI (COUNTIF)

LICZ.JEŻELI(zakres kryterium, kryterium)

Funkcja oblicza, ile jest takich komórek w analizowanym obszarze, które spełniają kryterium wyboru. Pierwszy argument określa zakres, w którym znajdują się wartości poddawane sprawdzeniu na spełnienie warunku kryterium. Drugim jest samo kryterium.

Zakres kryterium może być dowolnym prostokątnym obszarem wartości. Kryterium zapisane w postaci ciągu tekstowego określa relację, jaka musi być spełniona, aby wartość znajdująca się w zakresie została uwzględniona przez funkcję. Na wynik funkcji nie wpływa wielkości liter użytych w kryterium. Argument „kryterium” może być też adresem komórki, która takie kryterium ma wpisane albo wyrażenie tekstowe reprezentujące taką relację.

  • Przykłady:
  • Jeżeli kryterium określa relację „=”, to znak równości może zostać pominięty. Kryterium tekstowe musi być ujęte w cudzysłów. Kryterium numeryczne może zostać wpisane bezpośrednio jako liczba lub wyrażenie obliczające taką liczbę.

    SUMA.JEŻELI (SUMIF), ŚREDNIA.JEŻELI (AVERAGEIF).

    nazwa.JEŻELI(zakres kryteriów; kryteria; zakres obliczeniowy funkcji)

    Pierwsza z tych funkcji oblicza sumę a druga – średnią z wartości, które znajdują się w wierszach spełniających kryterium. Pierwsze dwa parametry mają to samo znaczenie, jak w funkcji LICZ.JEŻELI(). W odróżnieniu od niej obszar kryterium musi być obszarem jednokolumnowym albo jednowierszowym. Trzeci argument określa zakres komórek, których wartości będą brały udział w obliczeniach.

    Formalnie zakres kryteriów może znajdować się w innym miejscu arkusza niż zakres obliczeniowy funkcji.

    Przykłady:

    Funkcje z wieloma warunkami.

    Są to funkcje odpowiadające tym omówionym powyżej (w tym dwie nowe), lecz pozwalające określić więcej niż jedno kryterium wyboru. Wartości funkcji są obliczane dla tych pozycji, które spełniają wyrażenie logiczne I(kryterium1;kryterium2;...). Inaczej mówiąc wówczas gdy wszystkie podane kryteria mają wartość PRAWDA. Wszystkie te funkcje mogą mieć do 255 argumentów, co oznacza, że można określić do 127 kryteriów.

    Wszystkie definiowane w funkcjach zakresy muszą mieć taki sam wymiar, w przeciwnym razie funkcja zgłosi błąd „Błąd: 502” (nieprawidłowy argument). Same zakresy mogą znajdować się w różnych częściach skoroszytu.

    LICZ.WARUNKI (COUNTIFS).

    LICZ.WARUNKI(zakres1; kryterium1 [;zakres2; kryterium2...])

    Funkcja wymaga wpisywania par argumentów: „Zakres – Kryterium”. Gdy wpisana zostanie tylko jedna para argumentów, funkcja działa tak, jak LICZ.JEŻELI().

    Przykłady:

    SUMA.WARUNKÓW (SUMIFS), ŚREDNIA.WARUNKÓW (AVERAGEIFS), MAKS.WARUNKÓW (MAXIFS), MIN.WARUNKÓW (MINIFS).

    nazwa.WARUNKÓW(zakres obliczeń funkcji; zakres1; kryterium1 [;zakres2; kryterium2...])

    We wszystkich tych funkcjach zakres, z którego dane są brane do obliczeń, jest pierwszym argumentem tych funkcji. Następnie występują pary argumentów „Zakres – Kryterium”. Takich par może być co najwyżej 127. Obliczony wynik dotyczy danych, dla których wszystkie kryteria miały wynik PRAWDA.

    Pierwsza część nazwy funkcji mówi o tym, jakiego rodzaju obliczenie zostanie zrealizowane.

    Przykłady:

    Funkcje bazodanowe.

    Funkcji bazodanowych nie należy utożsamiać z aplikacją Base, będącą jednym z komponentów całego pakietu (x)Office. Są to funkcje obliczeniowe działające na zestawieniach tabelarycznych, w których każdy wiersz stanowi jeden rekord danych. W odróżnieniu od funkcji omówionych powyżej te funkcje wymagają wskazania jednego spójnego zakresu danych (jest to pierwszy argument tych funkcji), na podstawie których nastąpią obliczenia. Do wyboru wartości, które wezmą udział w obliczeniach, używany jest zadeklarowany obszar kryteriów i ten obszar jest wskazywany jako jeden z parametrów funkcji. Tak więc kryteria wyboru danych nie są wprost argumentami funkcji, ale są zapisane w oddzielnym miejscu. To z kolei powoduje, że łatwo jest je zmieniać bez ingerencji w treść zapisu funkcji.

    Funkcje baz danych zostały omówione w opracowaniu „Baza danych w programie Calc”. Można o nich przeczytać tutaj: https://yestok.pl/lbo/y59.php#5909.

    Grupa druga.

    Dla funkcji zakwalifikowanych do tej grupy nie ma jakiejś wspólnej cechy, odpowiadającej np. kryteriom występującym w funkcjach omówionych w pierwszej grupie. Każda funkcja ma swoją indywidualną definicję. To użytkownik, tworząc swój projekt, wie co i jak ma być w nim policzone, i to on musi podjąć decyzję z jakich funkcji skorzysta.

    Do tej grupy zaliczam funkcje, które na podstawie zbadanego warunku podejmują decyzję, jaki wynik ma być zwrócony. Przy czym przez wynik należy rozumieć także zastosowanie różnych algorytmów obliczeniowych.

    JEŻELI (IF).

    JEŻELI(warunek; wyrażenie1; wyrażenie2)

    JEŻELI() jest najczęściej używaną funkcją realizującą możliwość modyfikowania obliczeń uzależnionych od spełnienia, bądź nie, określonych warunków. W przypadku bardzo złożonego sposobu obliczeń, formuły z użyciem tej funkcji mogą być bardzo skomplikowane i trudne do przeanalizowania.

    Funkcja zwróci wynik wyrażenia1, wtedy gdy warunek będzie miał wartość PRAWDA a wynik wyrażenia2, gdy wartość warunku wynosi FAŁSZ.

    Funkcji tej i jej zastosowaniu poświęciłem także oddzielne opracowanie, można je przeczytać tutaj: https://yestok.pl/ooo/y57.php.

    JEŻELI.BŁĄD (IFERROR).

    JEŻELI,BŁĄD(wyrażenie1; wyrażenie2)

    To bardzo wygodna funkcja. Niestety jest dostępna tylko w Calc LibreOffice (stan na dzień, w którym ten artykuł powstaje).

    Wynikiem funkcji jest obliczone wyrażenie1 pod warunkiem, że nie generuje ono błędu. Gdy wyrażenie generuje błąd, obliczane jest wyrażenie2.

    Przykład:

    Funkcja ta znacznie upraszcza budowanie formuły z bezpiecznym wynikiem. W Calcu komórka, w której wynikiem jest wygenerowany błąd, transformuje ten błąd do wyników wszystkich formuł, w których bezpośrednio albo pośrednio została użyta. Dlatego, aby zabezpieczyć się przed tym efektem, budowano formułę o postaci:

    =JEŻELI(CZY.BŁĄD(wyrażenie1); wyrażenie2; wyrażenie1)

    co w przypadku długiej i złożonej struktury wyrażenia1 – komplikowało napisanie całej formuły.

    WYBIERZ (CHOOSE).

    WYBIERZ(indeks; wyrażenie1; wyrażenie2; ...; wyrażenie30)

    Funkcja zwróci wynik wyrażenia znajdującego się na pozycji wynikającej z wartości indeksu. Wartość indeksu może zawierać się w zakresie od 1 do 30 (tyle najwyżej wyrażeń można ulokować w funkcji). W konkretnej realizacji tej funkcji największa dopuszczalna wartość indeksu wynika z liczby wstawionych wyrażeń. Zastosowanie indeksu spoza zakresu wynikającego z liczby wyrażeń wygeneruje „Błąd: 502” (nieprawidłowy argument).

    W funkcji można nie wpisywać wyrażenia na wybranej pozycji, jednak każdy taki fakt musi zostać zasygnalizowany średnikiem. Wówczas dla indeksu przypadającego na to opuszczone wyrażenie zostanie zwrócona wartość 0.

    Przykłady:

    PRZEŁĄCZ (SWITCH).

    PRZEŁĄCZ(wyrażenie; wynik1; wyrażenie1 [;wynik2; wyrażenie2 [;... [; wynikn; wyrażenien ]]][; wyrażenie domyślne])

    Funkcja jest dostępna tylko w wersji Calc LibreOffice.

    Wartość pierwszego argumentu („wyrażenie”) jest porównywana kolejno, od lewej do prawej, z wartością każdego argumentu „wynik”. Pierwszy zgodny wynik wyznacza wyrażenie, jakie ma zostać obliczone i dalsze porównywania nie są wykonywane. Jeśli wartość pierwszego argumentu nie jest zgodna z wartością żadnego argumentu „wynik”, wykonane jest wyrażenie domyślne. Jeżeli takiego wyrażenia nie ma, generowany jest błąd: „#N/D”.

    Funkcja może mieć co najwyżej 255 argumentów, a to oznacza możliwość zadeklarowania do 127 wariantów obliczeń (126 par „wynik – wyrażenie" plus wyrażenie domyślne).

    W funkcji każdy wynik musi mieć przypisane wyrażenie, nie można go pominąć. Brak wyrażenia, w przypadku odwołania się do niego, spowoduje wyświetlenie błędu „Błąd: 518” (Wewnętrzny błąd składni).

    Przykład:

    WARUNKI (IFS).

    WARUNKI(wyrażenie logiczne1; wyrażenie1 [; wyrażenie logiczne2; wyrażenie2 [; …]])

    Funkcja jest dostępna tylko w wersji Calc LibreOffice.

    Funkcja, zaczynając od pierwszego wyrażenia logicznego, sprawdza kolejne wyrażenia logiczne czy ich wartość wynosi PRAWDA (TRUE). Pierwsze napotkane wyrażenie spełniające ten wymóg decyduje o wyrażeniu, jakie zostanie wykonane i stanowić będzie wynik funkcji. Dalsze wyrażenia logiczne nie będą sprawdzane. Jeśli żadne wyrażenie logiczne nie będzie miało wartości PRAWDA, to wynikiem funkcji będzie błąd: „#N/D”.

    Można określić do 255 argumentów funkcji, co oznacza wykorzystanie 127 par argumentów „wyrażenie logiczne – wyrażenie”. Podobnie jak w funkcji PRZEŁĄCZ, nie można pominąć wyrażenia związanego z wyrażeniem logicznym. Brak wyrażenia, w przypadku odwołania się do niego, spowoduje wyświetlenie błędu „Błąd: 518” (Wewnętrzny błąd składni).

    Jeśli użytkownik chciałby zapewnić uzyskanie wyniku, nawet wtedy, gdy żadne wyrażenie logiczne nie ma wartości PRAWDA, powinien jako ostatnią parę wprowadzić wyrażenie logiczne, które zawsze jest prawdziwe, np. 1=1 i przypisać mu wyrażenie zwracające wynik.

    Interesującą cechą tej funkcji jest to, że kolejne wyrażenia logiczne mogą dotyczyć zupełnie różnych komórek arkusza.

    Przykład:

    ADR.POŚR (INDIRECT)

    ADR.POŚR(adres;typ)

    Funkcja zwraca odwołanie do komórki lub obszaru, podanego w postaci ciągu tekstowego, w argumencie „adres”. Drugi argument – „typ” – informuje, w jaki sposób „adres” został podany.

    Argument „typ” równy 0 oznacza, że „adres” jest podany w konwencji „nr wiersza – nr kolumny” (ten sposób podawania adresów określany jest w polskim systemie pomocy jako W1K1, choć w rzeczywistym adresowaniu należy użyć liter „R” i „C”. Np. zapis adresu D7 w tym systemie adresowania wygląda następująco: R7C4). Każda inna wartość lub brak tego argumentu oznaczają, że stosowany jest podstawowy sposób adresowania, skrótowo określany jako A1.

    Konwencja W1K1 została przejęta z pierwszego programu arkusza kalkulacyjnego Microsoftu – Multiplanu i zachowana do stosowania w Excelu (obecnie raczej jako opcja). Znakiem rozdzielającym nazwę arkusza od adresu komórki jest w Excelu wykrzyknik. Dlatego przykładowy adres, zapisany w konwencji A1 jako: Arkusz3.B12, w konwencji W1K1 należy zapisać tak: Arkusz3!R12C2.

    Krótki rys historyczny dotyczący adresowania komórek.

    VisiCalc, pierwszy arkusz kalkulacyjny opracowany na komputery personalne, wprowadził konwencję odwoływania się do adresów komórek za pomocą określeń literowo liczbowych. Ten sposób adresowania, nazywany A1, przyjął się powszechnie i jest obecnie stosowany we wszystkich arkuszach. Zapis adresów to np. A1, BE1234 czy C3:F8

    Microsoft w 1982 roku przedstawił własny program arkusza kalkulacyjnego, Multiplan, w którym zastosował odmienny sposób adresowania, nazywany RC, w którym po literze R (row, czyli wiersz) wpisywany jest numer wiersza a po literze C (column, czyli kolumna) – numer kolumny. W polskich systemach pomocy ten sposób adresowania często nazywany jest: W1K1. Podane powyżej adresy w tym programie byłyby prezentowane tak: R1C1, R1234C57 i R3C3:R8C6. W tym zapisie są to jednocześnie adresy bezwzględne. Odwołania względne wymagają podania adresu w postaci R[przesunięcie]C[przesunięcie], gdzie „przesunięcie” jest całkowitą liczbą dodatnią lub ujemną. Przykładowo, adres komórki leżącej w tym samym wierszu, ale o dwie kolumny wcześniej byłby zapisany tak: RC[-2].

    Następca Multiplanu, czyli Excel, przyjął adresowanie typu A1, jednak w jego opcjach zachowano możliwość używania adresowania RC. Możliwość stosowania notacji Excelowej, zarówno typu W1K1, jak i A1 ma Calc Libreoffice, w którego opcjach można ustawić dwa dodatkowe, zgodne z Excelem, sposoby adresowania. Sposób zgodny z Excelem oznacza, że separatorem rozdzielającym nazwę arkusza i adres komórki lub obszaru jest wykrzyknik, a nie kropka.

    Bez względu na przyjęty sposób adresowania w funkcji ADR.POŚR() można zastosować dowolny zapis adresowania typu A1, natomiast argument typ o wartości 0 jest wymagany, gdy argument adres będzie podany w postaci W1K1.

    Pierwszym argumentem funkcji może być także nazwa zakresu lub komórki nadana poleceniem „Arkusz → Nazwane zakresy lub wyrażenia” albo „Dane → Określ zakres...”.

    Jeżeli pierwszy argument funkcji określa zakres, to wynikiem wyrażenia złożonego wyłącznie z tej funkcji, czyli zapisu w postaci =ADR.POŚR("nazwa_zakresu"), jest: w Calc LibreOffice – błąd #VALUE! a w Calc Apache OpenOffice – błąd #ARG!. Funkcja nie może bowiem w takiej sytuacji zwrócić jednego, konkretnego wyniku. Jednak potrafi udostępnić cały zakres wynikający z nazwanego obszaru, dlatego np. wyrażenie w postaci =INDEKS(ADR.POŚR("nazwa_zakresu");1;1) zwróci zawartość komórki w pierwszym wierszu i pierwszej kolumnie tego zakresu a np. formuła =LICZBA.WIERSZY(ADR.POŚR("nazwa_zakresu")) zwróci liczbę wierszy w obszarze "nazwa_zakresu".

    Istnieją pewne różnice w zachowaniu tej funkcji w wersji Apache OpenOffice i LibreOffice. W wersji Apache OpenOffice nie są akceptowane nazwy obszarów zdefiniowane poleceniem „Dane → Określ zakres...”. Gdy taka nazwa zostanie użyta w funkcji, pojawi się błąd: „Błąd: 502” (Błąd: Nieprawidłowy argument). Inaczej to wygląda w wersji LibreOffice. Nazwa tak udostępnionego zakresu odnosi się zawsze do zakresu danych. Gdy więc podczas definiowania zakresu zaznaczono opcje informujące, że zakres zawiera wiersz nagłówka i/lub wiersz podsumowania, to udostępniony obszar nie obejmuje tych „pomocniczych” wierszy. Wspomniana powyżej funkcja INDEKS(), uzna, że pierwszym wierszem w zakresie działania tej funkcji jest pierwszy wiersz z danymi a liczba wierszy zakresu wskazanego przez funkcję ADR.POŚR() nie obejmie ewentualnych wierszy nagłówka i/lub podsumowania.

    Przy użyciu w funkcji nazwy zakresu lub nazwy komórki, nie ma znaczenia drugi argument funkcji.

    Przykłady:

    W aspekcie formuł warunkowych funkcja nie jest może najlepszym rozwiązaniem do uzyskiwania wyników tego rodzaju, chociaż modyfikując tekstowy zapis adresu komórki, można różnicować wyniki.

    SPRAWDŹ.PRÓG (GESTEP).

    Uwaga! W Apache OpenOffice nazwa funkcji nie została przetłumaczona. W tej wersji oprogramowania należy używać angielskiej nazwy GESTEP.

    SPRAWDŹ.PRÓG(liczba; próg)

    Funkcja porównuje wartość „liczba” z wartością „próg”. Jeśli liczba jest większa lub równa wartości progowej zwracana jest wartość 1, w przeciwnym przypadku wynikiem funkcji jest 0.

    Jest to, jak widać, funkcja zero-jedynkowa. Jej właściwości można wykorzystać przy formułach wykorzystujących progi liczbowe.

    Przykład:



    Informacje pomocnicze dla użytkowników Apache OpenOffice i LibreOffice w systemie MacOs

    System operacyjny Windows/Linux System operacyjny MacOs
    Kliknięcie prawym przyciskiem myszki Control + kliknięcie
    Klawisz CTRL (w skrótach klawiaturowych) Klawisz ⌘
    F5 Nawigator Shift + ⌘ + F5
    F11 Style i formatowanie ⌘ + T