EXCEL: KWERENDY I FORMULARZE

Krok 1 / 6

1. Struktura Danych

Oto wyjściowa baza danych zaimportowana z Twojego pliku (zawiera m.in. ID Filmu, Gatunek, Tytuł Filmu, Rok, Ocena, Reżyser).

  • Zauważ, że każdy rekord jest w osobnym wierszu, a kolumny zawierają jeden, konkretny typ danych.
  • W oparciu o te nagłówki zbudujemy profesjonalny system wyszukiwania.

Zanim zaczniemy zadawać pytania, musimy przygotować wolną przestrzeń na górze arkusza.

2. Zakres Kryteriów

Przygotowujemy nasz "Panel Detektywa" (Zakres Kryteriów), oddzielając go od głównej tabeli.

  • Wstawiamy 5 pustych wierszy na samej górze. Twoja tabela zaczyna się teraz od wiersza 6.
  • Kopiujemy dokładnie te same nagłówki z tabeli głównej i wklejamy je w pierwszym wierszu (A1:F1).

Pusty wiersz 2 posłuży nam do wpisywania kryteriów.

3. Wprowadzanie Warunków

Zadajmy Excelowi precyzyjne pytanie, łącząc dwa warunki w ramach logiki ORAZ (AND).

  • Pod nagłówkiem Gatunek wpisujemy słowo sci-fi.
  • Pod nagłówkiem Ocena wpisujemy warunek >8.

Skoro wpisaliśmy je w tym samym wierszu, Excel poszuka filmów, które są jednocześnie z gatunku sci-fi oraz mają ocenę na poziomie 9 lub 10 (np. The Matrix).

4. Filtr Zaawansowany

Czas wywołać kwerendę z menu Dane -> Zaawansowane.

  • Jako Zakres listy podajemy główną tabelę.
  • Jako Zakres kryteriów wskazujemy nasze nagłówki na górze wraz z wpisanymi warunkami.

Z bazy zniknęły m.in. Interstellar (bo ma ocenę 8, a my szukamy >8) oraz komedie i horrory. Na ekranie widnieją tylko hity sci-fi.

5. Automatyzacja: D-Funkcje

Zamiast włączać filtr, możemy dynamicznie przeliczać interesujące nas dane (np. średnią ocen dla wybranego gatunku).

=BD.ŚREDNIA(A6:H36; "Ocena"; A1:H2)
  • A6:H36 - Nasza docelowa baza danych.
  • "Ocena" - Kolumna do matematycznych obliczeń.
  • A1:H2 - Nasz Zakres Kryteriów.

Wystarczy zmienić warunek na "komedia", a formuła natychmiast pokaże inny wynik.

6. Klikalna Lista (Pole Kombi)

Zamiast wpisywać słowa z klawiatury, stwórzmy profesjonalną listę rozwijaną. Aby ukryć przed użytkownikiem jej "techniczne" działanie, użyjemy triku z ukrywaniem kolumny.

  1. Lista: Wstawiamy "Pole kombi" (z zakładki Deweloper) i podpinamy pod nie nasz słownik gatunków.
  2. Cyfra zamiast słowa: Wybór opcji na liście wysyła do arkusza cyfrę (np. 1). Wybierzmy na jej "lądowisko" komórkę tuż obok naszego panelu, np. J2. Następnie klikamy literę kolumny "J" u góry ekranu prawym przyciskiem myszy i wybieramy Ukryj. Cyfra nadal tam jest (więc Excel może liczyć), ale nikt jej nie widzi!
  3. Tłumacz: W naszym Zakresie Kryteriów wpisujemy funkcję =INDEKS(SŁOWNIK; J2). Funkcja potajemnie zagląda do ukrytej komórki J2, odczytuje stamtąd cyfrę "1" i wypisuje na ekranie gotowe słowo "sci-fi".

Gotowe! Zbudowałeś interfejs, który jest czysty, bezpieczny i w 100% zautomatyzowany.

ID Filmu Gatunek Tytuł Filmu Rok Ocena Reżyser S-F03 sci-fi The Matrix 1999 10 L.Wachowski K04 komedia The Hangover 2009 8 T.Phillips H01 horror The Shining 1980 9 S.Kubrick S-F01 sci-fi Interstellar 2014 8 C.Nolan 1 2 3 4 5
ID Filmu Gatunek Tytuł Filmu Rok Ocena Reżyser ... 3 puste wiersze ... ID Filmu Gatunek Tytuł Filmu Rok Ocena Reżyser S-F03 sci-fi The Matrix 1999 10 L.Wachowski 1 2 6 7
ID Filmu Gatunek Tytuł Filmu Rok Ocena Reżyser sci-fi >8 ... 3 puste wiersze ... ... Tabela Bazy Danych ...
Gatunek sci-fi Ocena >8 ID Filmu Gatunek Tytuł Filmu Rok Ocena Reżyser S-F02 sci-fi Inception 2010 9 C.Nolan S-F03 sci-fi The Matrix 1999 10 L.Wachowski 6 8 9 ... reszta wierszy ukryta (nie spełniają warunków >8) ...
Gatunek sci-fi Średnia ocena filmów sci-fi: 8.6 =BD.ŚREDNIA(A6:H36; "Ocena"; A1:H2) * Uśrednia oceny wszystkich filmów sci-fi w bazie, bez ukrywania wierszy.
Panel Użytkownika: sci-fi Wysyła cyfrę 1 Ukryta Kolumna (J) 1 Słownik na arkuszu 1. sci-fi 2. komedia 3. horror Zakres Kryteriów (Gatunek): sci-fi =INDEKS( SŁOWNIK ; J2 )