Arkusz, który wykorzystamy do obliczeń wygląda w sposób następujący:

Aby rozwiązać problem, należy powiązać ze sobą wartości procentowe podwyżek znajdujących się w obszarze komórek od G2 do I2 z planowanymi dotacjami, które obliczymy w kolumnie D przy uwzględnieniu klasy badawczej (kolumna B). Ponadto należy obliczyć sumę łącznej dotacji po podwyżce ogólnie i w rozbiciu na poszczególne klasy badawcze.

Formuły w obliczeniach z wieloma argumentami

Wykorzystamy dwie, poniżej omówione formuły: WYSZUKAJ.POZIOMO oraz SUMA.JEŻELI.

WYSZUKAJ.POZIOMO(szukana_wartość;tabela_tablica;nr_indeksu_wiersza;[przeszukiwany_zakres])

Formuła ta wyszukuje wartość w górnym wierszu tabeli danych (argument tabela_tablica), a następnie zwraca wartość w tej samej kolumnie z wiersza określonego argumentem nr_indeksu_wiersza. Poszczególne argumenty tej funkcji oznaczają:

• szukana_wartość – wartość, którą należy znaleźć w pierwszym wierszu tabeli tabela_tablica,

• tabela_tablica – zakres zawierający tabelę z przeszukiwanymi danymi,

• nr_indeksu_wiersza – numer wiersza w tabela_tablica, z którego zostanie zwrócona pasującą wartość,

• przeszukiwany_zakres – jest argumentem opcjonalnym. Oznacza wartość logiczną określającą, czy funkcja WYSZUKAJ.POZIOMO ma znaleźć dokładne czy przybliżone dopasowanie. Jeśli tą wartością jest PRAWDA bądź argument został pominięty, zwracane jest przybliżone dopasowanie. Jeśli nie zostanie znalezione dokładne dopasowanie, zwracana jest następna największa wartość, która jest mniejsza niż argument szukana_wartość. Jeśli tą wartością jest FAŁSZ, funkcja WYSZUKAJ.POZIOMO wyszuka dokładne dopasowanie. W omawianym przypadku wybierzemy tę wartość. Jeśli nie zostanie znalezione, zwracana jest wartość błędu #N/D!

SUMA.JEŻELI(zakres;kryteria;[suma_zakres])

Formuła pozwala zsumować wartości z zakresu komórek spełniających określone kryteria. Poszczególne argumenty tej funkcji oznaczają:

• zakres – zakres komórek, do których zostaną zastosowane kryteria,

• kryteria – kryteria w postaci liczby, wyrażenia, odwołania do komórki, tekstu lub funkcji określającej, które komórki będą dodawane,

• suma_zakres – jest argumentem opcjonalnym. Są to rzeczywiste komórki, które podlegają sumowaniu w przypadku, gdy należy zsumować komórki inne niż określone w argumencie zakres. UWAGA - suma_zakres powinny mieć taki sam rozmiar jak zakres.

W naszym przykładzie wpiszemy zatem w komórce D2 następującą formułę:

=(C2*(1+WYSZUKAJ.POZIOMO(B2;$G$1:$I$2;2;0)))

W komórkach G5:G7 natomiast poniższe formuły

=SUMA.JEŻELI($B$2:$B$12;G1;$D$2:$D$12)
=SUMA.JEŻELI($B$2:$B$12;H1;$D$2:$D$12)
=SUMA.JEŻELI($B$2:$B$12;I1;$D$2:$D$12)

Po przekopiowaniu formuły z komórki D2 do całego zakresu D3:D12 i dodaniu wartości dotacji dla poszczególnych klas, arkusz wynikowy będzie wyglądał następująco:

Porównanie dotacji z dwóch lat pokazuje, że w roku 2022 musi ona wzrosnąć w stosunku do roku 2021 o 323209 zł.

Budowa scenariuszy

Odwołania zastosowane w formule WYSZUKAJ.POZIOMO pozwoliły uwzględnić w obliczeniach kilka różnych argumentów. Aby nie trzeba było kopiować arkusza tyle razy, ile wariantów chcemy przetestować, arkusz kalkulacyjny oferuje narzędzie do zarządzania takimi zestawami danych (scenariuszamiscenariuszscenariuszami). Scenariusz dla naszego przykładu wyglądałyby tak, jak na ilustracji poniżej.

RP6P7Tfw2GYs2

Ilustracja

Microsoft Excel

Aby jednak wartości były przeliczane przez program dynamicznie, dając nam wgląd w wyniki obliczeń dla wszystkich scenariuszy jednocześnie, musimy poszczególne wartości podwyżek dla wszystkich klas połączyć w jeden zestaw. Dostęp do tej funkcjonalności uzyskujemy wybierając zakładkę Dane ze wstążki, następnie z pola Prognoza wybieramy Analiza warunkowa, a z rozwiniętego menu wybieramy opcję Menedżer scenariuszy...

Zostanie wyświetlone okno Menedżer scenariuszy, w którym wyświetlą się wszystkie stworzone przez nas scenariusze. Jak widzimy na ten moment nie mamy żadnego scenariusza, aby stworzyć nowy scenariusz wybieramy przycisk Dodaj...

RpExmRavcjSJ0

Ilustracja

Po wybraniu Menedżera scenariuszy i kliknięciu przycisku Dodaj... możemy wprowadzić podstawowe dane w okno dialogowe zilustrowane poniżej:

RLmQZk8WDt2OF

Ilustracja

Do wyświetlonego okna Edytowanie scenariusza wpisujemy nazwę nowego scenariusza, komórki, w których zmieniać się będą wartości, a także komentarz. Tutaj musimy pamiętać, aby dobierać nazwy scenariusza tak, aby łatwo można było stwierdzić na czym on polega. Pola komentarz nie musimy uzupełniać, jednak dobrą praktyką jest dokładniejsze opisanie scenariusza.

Po zatwierdzeniu powyższych zmian przyciskiem OK możemy wprowadzić wartości procentowe podwyżek dla pierwszego z trzech scenariuszy.

R189ldN92INKV

Ilustracja

Podobnie postąpimy w budowania dwóch następnych scenariuszy. Za każdym razem wartości procentowe podwyżek przewidziane w danym scenariuszu będziemy przypisywali do tych samych komórek, które w poprzednim oknie dialogowym wskazaliśmy jako „Komórki zmieniane” (co ilustruje bezwzględne ich adresowanie widoczne na ilustracji powyżej).

Po wprowadzeniu danych dla wszystkich scenariuszy będziemy mogli dynamicznie przeliczać wartości podwyżek. W tym celu wystarczy kliknąć przycisk Pokaż dla każdego scenariusza osobno (pokazują to trzy poniższe ilustracje).

LibreOffice Calc

Aby jednak wartości były przeliczane przez program dynamicznie, dając nam wgląd w wyniki obliczeń dla wszystkich scenariuszy jednocześnie, musimy poszczególne wartości podwyżek dla wszystkich klas połączyć w jeden zestaw. Dostęp do tej funkcjonalności uzyskujemy, wybierając Narzędzia z menu głównego, a następnie Scenariusz...

Najpierw jednak musimy zaznaczyć komórki, dla których chcemy utworzyć scenariusz. W naszym przypadku będą to komórki z zakresu F1:I2. Przejdźmy do kreatora scenariusza, tak jak zostało to opisane wyżej.

Zostanie wyświetlone okno Utwórz scenariusz, w którym możemy wprowadzić podstawowe dane w okno dialogowe zilustrowane poniżej:

RKmBRZbS6LEmt

Ilustracja

Wpisujemy nazwę nowego scenariusza, komentarz, a także wybieramy kolor krawędzi scenariusza. Tutaj musimy pamiętać, aby dobierać nazwy scenariusza tak, by łatwo można było stwierdzić, na czym on polega. Pola komentarz nie musimy uzupełniać, jednak dobrą praktyką jest dokładniejsze opisanie scenariusza. Ustawienie koloru również pomoże w szybszym zrozumieniu, który ze scenariuszy jest aktualnie wyświetlany.

Po zatwierdzeniu powyższych zmian przyciskiem OK możemy wprowadzić wartości procentowe podwyżek dla pierwszego z trzech scenariuszy bezpośrednio w komórkach G2, H2 oraz I2.

RGSGvm0GfnTeB

Ilustracja

Podobnie postąpimy w budowania dwóch następnych scenariuszy. Za każdym razem wartości procentowe podwyżek przewidziane w danym scenariuszu będziemy przypisywali do tych samych komórek.

Po wprowadzeniu danych dla wszystkich scenariuszy będziemy mogli dynamicznie przeliczać wartości podwyżek. W arkuszu mamy dwie możliwości przełączania się między scenariuszami. Pierwszą z nich jest naciśnięcie strzałki w dół bezpośrednio przy nazwie scenariusza i wybranie interesującego nas scenariusza:

Drugą możliwością jest rozwinięcie menu Nawigator na panelu bocznym, a następnie otworzenie listy scenariuszy poprzez wybór ikony książki.

RBS1Rfrwq1IpE

Ilustracja

R1EYjZ6ndyRI4

Ilustracja

Klikając podwójnie lewym przyciskiem myszy interesujący nas scenariusz na liście, zostanie on wyświetlony w arkuszu (pokazują to trzy poniższe ilustracje).

Słownik