bg‑azure

Wyobraźmy sobie placówkę akademicką złożoną z instytutów naukowych, którym organ nadrzędny przypisał określoną klasę charakteryzującą poziom badawczy: A, B, C. Klasa A to poziom najniższy, klasa C – najwyższy. Każdy instytut otrzymuje dotację ministerialną uzależnioną od liczby zespołów badawczych, które wchodzą w jego skład. Przy podwyższaniu dotacji na następny rok obowiązuje zasada - im wyższa klasa badawcza instytutu, tym wyższy procentowy wzrost dotacji w następnym roku budżetowym. Sprawdźmy ile wynosić będzie dotacja dla każdego instytutu oraz klasy badawczej. Zacznijmy zatem od najprostszego modelu, w którym klasa A to podwyżka 1% w stosunku do roku ubiegłego, klasa B – 2%, a klasa C – 3%.

R1B2gSv0LgZnf

Plik ZIP o rozmiarze 28.00 KB w języku polskim

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

R1049aFdtrQjZ1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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.

Linia 1. WYSZUKAJ kropka POZIOMO otwórz nawias okrągły szukana podkreślnik wartość średnik tabela podkreślnik tablica średnik nr podkreślnik indeksu podkreślnik wiersza średnik otwórz nawias kwadratowy przeszukiwany podkreślnik zakres zamknij nawias kwadratowy zamknij nawias okrągły.

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!

Linia 1. SUMA kropka JEŻELI otwórz nawias okrągły zakres średnik kryteria średnik otwórz nawias kwadratowy suma podkreślnik zakres zamknij nawias kwadratowy zamknij nawias okrągły.

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łę:

Linia 1. znak równości otwórz nawias okrągły C2 asterysk otwórz nawias okrągły 1 plus WYSZUKAJ kropka POZIOMO otwórz nawias okrągły B2 średnik $G$1 dwukropek $I$2 średnik 2 średnik 0 zamknij nawias okrągły zamknij nawias okrągły zamknij nawias okrągły.

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

Linia 1. znak równości SUMA kropka JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$12 średnik G1 średnik $D$2 dwukropek $D$12 zamknij nawias okrągły. Linia 2. znak równości SUMA kropka JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$12 średnik H1 średnik $D$2 dwukropek $D$12 zamknij nawias okrągły. Linia 3. znak równości SUMA kropka JEŻELI otwórz nawias okrągły $B$2 dwukropek $B$12 średnik I1 średnik $D$2 dwukropek $D$12 zamknij nawias okrągły.

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:

RekbuVBIvQwj11
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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

R1WYlmXlM6UfU1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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

RLmQZk8WDt2OF
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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

RxEYnZ6scTv2i1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1H2xxcfbF6Xc1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R14a2uUdspH3a1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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.

RFYum6lShAoFp1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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

RKmBRZbS6LEmt
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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:

RGA5WOXUjMguV1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1EYjZ6ndyRI4
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

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

RN5GHxUkSg1ys1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
RmBXWS9YMnsuM1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.
R1OAksZw3aojB1
Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Słownik

scenariusz
scenariusz

zestaw wartości, który zapisany w tabeli arkusza kalkulacyjnego może być dynamicznie podstawiany do wskazanych komórek w celu użycia tych wartości jako argumentów formuł liczących. Zastosowanie wielu scenariuszy daje możliwość dynamicznego porównania wyników dla poszczególnych zestawów danych bez konieczności wielokrotnego kopiowania formuł (lub skoroszytów)