Wykorzystanie połączonych formuł: index oraz podaj pozycję!

Z lekcji dowiesz się:

• Jak stworzyć panel analityczny?

• Jak przygotować: zestawiania, wykresy i podsumowania z różnych baz?

• O korzyściach z łączenia dwóch formuł!

Z lekcji dowiesz się:

• Jak stworzyć panel analityczny?

• Jak przygotować: zestawiania, wykresy i podsumowania z różnych baz?

• O korzyściach z łączenia dwóch formuł!

W przygotowanym arkuszu kalkulacyjnym są się dwie zakładki: „Baza” – w której znajdują się dane i „Analiza” – w której te dane będą prezentowane.

Baza zawiera tabele przestawną, w której umieszczane są dane dotyczące liczby wyświetleń stron w podziale na id i firmę, która je emituje. Naszym zadaniem jest stworzenie panelu umożliwiającego wybieranie z listy wybranego id oraz nazwy firmy i w rezultacie - zwracanie odpowiedniej liczby wyświetleń strony.

Ważne przy tym jest, żeby użytkownik, nie mógł wybrać błędnego parametru, dlatego skorzystamy z opcji poprawność danych - dostępnej w arkuszu kalkulacyjnym.

Z zakładki dane wybieramy poprawność danych (rys. 1 i 2).

(Rys. 1)

(Rys. 2)

Następnie wybieramy Lista, a jako źródło wskazujemy komórki, które będą możliwe do wyboru przez użytkownika (rys. 3).

(Rys. 3)

INDEKS(tablica;nr_wiersza;nr_kolumny)

W funkcji indeks występują następujące argumenty:

tablica - zakres komórek, w którym zawierają się dane,

nr_wiersza – numer wiersza tablicy z której pobierany dane,

nr_kolumny - numer kolumny tablicy z której pobieramy dane.

Przykładowo - jeśli występuję tablica danych, która mieści się w zakresie komórek A1:D10 i jako numer wiersza wybieramy 2 a kolumnę 3, to funkcja zwróci wartość, która znajduje się w komórce C3. Działanie funkcji można więc trochę porównać do gry w statki.

PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;typ_porównania)

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

Szukana_wartość - należy wskazać wartość, którą chcemy znaleźć w zbiorze (może być wartością tekstową, liczbową lub bezpośrednio odwołaniem do komórki),

Przeszukiwana_tablica - zakres danych w którym szukamy wartości z argumentu 1,

Typ_porównania – zaleca się wybieranie parametru 0, czyli dokładne dopasowanie wartości do siebie.

Teraz należy złączyć obie formuły. Przeanalizujmy jej poszczególne składniki:

W pierwszym kroku wybieramy funkcje indeks i jako jej rozmiar tablicy zaznaczamy obszar danych, które chcemy uzyskać (rys. 4).

(Rys. 4)

W miejscu gdzie podajemy numer wiersza funkcji indeks, nie podajemy konkretnej wartości, a wybieramy funkcję podaj pozycję. Wskazujemy wartość po której chcemy wyszukiwać nasze id, czyli komórka C6, jako zakres kolumnę w bazie w której znajdują się wszystkie id i typ porównania, jako dokładne dopasowanie, czyli 0 (rys. 5).

(Rys. 5)

Analogicznie w miejscu nr kolumny funkcji indeks działamy w ten sam sposób tylko wybieramy wartość szukaną - w tym przypadku nazwę firmy (komórka D5) i zakres w którym mamy tą zmienną zidentyfikować, typ porównania ponownie 0 (rys. 6).

(Rys. 6)

W rezultacie otrzymujemy postać funkcji (rys. 7).

(Rys. 7)

Następnie w komórce D6 otrzymujemy liczbę wyświetleń w zależności od wybranego id i nazwy firmy. Wartości te można dowolnie zmieniać, a wynik będzie się dynamicznie zmieniał.

Dzięki temu łączeniu funkcji można tworzyć zmieniające się zestawiania danych, wykresy i podsumowania z kilku różnych baz danych. To świetny sposób do stworzenia funkcjonalnego panelu analitycznego, przydatnego do wizualizacji i analizy danych.

Polecamy także:

• Jakie możliwości oferuje Solver?

• Jak przy pomocy Excela obliczyć wartości: ROA i ROE dla firm?