Covidowe statystyki – ciąg dalszy

Kontynuując wątek z notki:
Epidemia w statystykach Excela
– jak zrobić taką tabelę, w której widoczne będą dane tylko z jednego województwa (w moim przypadku – pomorskiego) i to na bieżąco aktualizowane? Oczywiście można pobawić się ze strukturą zapytania Power Query, ale to temat na zupełnie inny cykl notek. W tym przypadku pokażę, jak to zrobić łączami.

W tabelach źródłowych na Google Sheets codziennie dodawane są nowe rekordy. Odświeżanie PQ powoduje, że w poszczególnych tabelach połączonego skoroszytu automatycznie pojawiają się nowe pozycje. Ponieważ dla wybranego przeze mnie województwa pomorskiego założyłam nowy arkusz – muszą się one też tam pojawić.
Przede wszystkim więc, żeby wiedzieć ile jest rekordów w tabeli, zdefiniowałam własną funkcję:
IleDatSE=ILE.NIEPUSTYCH(Sytuacja_epidemiologiczna_w_woj!$A$5:$A$1000)
Następnie w pierwszej kolumnie tabeli lokalnej wstawiłam formułę:
=JEŻELI(WIERSZ()>IleDatSE+1;””;Sytuacja_epidemiologiczna_w_woj!A5)
Interesujące mnie województwo pomorskie zaczyna się od kolumny CD tabeli źródłowej, więc w kolumnie B wstawiłam formułę
=JEŻELI(WIERSZ()>IleDatSE+1;””;Sytuacja_epidemiologiczna_w_woj!CD5)
i przeciągnęłam ją w prawo, a następnie cały wiersz w dół. W efekcie – w tabeli pojawiają się tylko te wiersze, które nie są puste w tabeli źródłowej. A dlaczego w ogóle formuła, a nie zwykłe bezpośrednie odwołanie do komórki np. w stylu
=Sytuacja_epidemiologiczna_w_woj!CD5
– takie odwołanie do komórki pustej zwróciłoby zero, co mogłoby zniekształcić wyniki końcowe.  A  w ten sposób – widoczne są tylko prawdziwe wyniki.

A skoro mamy dane w tabeli – dobrze byłoby je jeszcze zilustrować wykresem. Oczywiście też automatyczne, choć tu problem jest bardziej skomplikowany -temat na kolejną notkę.


 

Kurs Power Pivot - analiza i modelowanie danych w Excelu
 

 

Definiowanie własnych funkcji

W Menedżerze nazw Excela w prosty sposób można przypisać własną nazwę nie tylko zakresom komórek, ale również formułom. Warto to stosować w sytuacji, gdy w większej ilości komórek wykorzystywana jest bardziej złożona formuła i opłacalne jest skrócenie takiej formuły.

Chcąc na przykład wykorzystać w jakiejś tabeli do dalszych obliczeń ilość dni do końca miesiąca, można do menedżera zadań dodać definicję takiej funkcji:

Pod nazwą DKM zapisana jest funkcja:
=NR.SER.OST.DN.MIES(DZIŚ();0)-DZIŚ()
Można jej używać jak każdej normalnej funkcji wbudowanej Excela, wpisując do komórki:
=DKM

Może też oczywiście wykorzystana jako argument w innych formułach.


Nazwa zakresu

Gdy często odwołujemy się do jakiegoś określonego zakresu komórek, warto jest zdefiniować i zapisać pod jakąś nazwą. Służy do tego grupa opcji Nazwy zdefiniowane na karcie Formuły.

Możemy zrobić to na kilka sposobów:

  • zaznaczamy zakres komórek, któremu chcemy przypisać nazwę, klikamy na przycisk Definiuj nazwę i wypełniamy formularz:
    Nadajemy unikalną nazwę, ewentualnie wpisujemy komentarz. Pamiętajmy o ustawieniu prawidłowego zakresu – dana nazwa może być przypisana do całego skoroszytu lub do konkretnego arkusza. Jeżeli ustawimy Zakres na konkretny arkusz – w innych arkuszach ta nazwa nie będzie widziana i nie będzie można się do niej odwoływać. Domyślną opcją jest Skoroszyt.
  • klikamy w przycisk Menedżer nazw, a następnie Nowy…
    Dalsze kroki – jak w poprzednim przykładzie
  • zaznaczamy zakres komórek, w którym znajdują się także planowane nazwy zakresów i naciskamy przycisk Utwórz z zaznaczenia:
    W ten sposób zostały utworzone 2 zakresy o nazwie Dziewczynki i Chłopcy.

 

Przegląd i zakres poszczególnych nazw można sprawdzić w Menedżerze nazw.
Można też je usuwać czy edytować.
A sam Menedżer nazw służy nie tylko do definiowania zakresów, ale także np. do tworzenia własnych formuł – ale to już temat na oddzielną notkę.


 

 

Kurs Excel dla sprzedawców