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.


Epidemia w statystykach Excela

Epidemia koronawirusa sprawia, że codziennie sprawdzamy podawane przez MZ statystyki zakażeń, wykonywanych testów, zajętych łóżek w szpitalach czy dostępnych respiratorów. Takie zbiorcze dane z pewnością stanowią doskonały materiał do analizy i to pod różnymi względami. Skąd je wziąć? Cóż, wydawać by się mogło, że takie dane  bez problemu powinny być dostępne na stronach któregoś z licznych ministerstw, ale niestety, nigdzie ich nie widać. Są za to na stronie Michała Rogalskiego – 19-latka z Łodzi, który zbiera i udostępnia je hobbistycznie. Czy są wiarygodne? Cóż, ostatnio okazało się, że to właśnie jego analizy są podstawą działań rządu…

Dane dostępne są w Arkuszach Google pod linkiem:

Covid-19 w Polsce

Ciekawy materiał. Można się z nimi zapoznać, pobrać na dysk lub połączyć do własnego pliku. Warto zwrócić uwagę, że część zawartych tam analiz dotyczy okresu do lub od 9 października – ministerstwo zmieniło wówczas sposób raportowania.

Też postanowiłam zająć się analizą tych danych. Wprawdzie nie chcę powielać już zawartych w nich rozwiązań, ale spróbuję zaadoptować je do własnych potrzeb.
Chcąc mieć dane automatycznie aktualizowane – wykorzystałam możliwości Power Query.
Opis połączenia do Arkuszy Google jest tu:

Power Query do Arkuszy Google (ang.Google Sheets)

Link do wstawienia łącza do pliku Covid-19 wygląda tak:

https://docs.google.com/spreadsheets/d/1ierEhD6gcq51HAm433knjnVwey4ZE5DCnu1bW7PRG3E/export?format=xlsx

Po załadowaniu danych – musiałam tylko zmienić nazwy arkuszy i sformatować daty w niektórych kolumnach. Następnie dołożyłam nowy arkusz, do którego ściągnęłam dane związane z łóżkami i respiratorami mojego województwa pomorskiego. Wyszło mi coś takiego:

To na początek. W kolejnych notkach opiszę szczegóły – jak maksymalnie zautomatyzować prezentowane dane – zarówno w tabeli jak i na wykresie.


Kurs Excel dla sprzedawców

Funkcja Przesunięcie

Funkcja Przesunięcie jest funkcją z grupy funkcji Wyszukiwania i odwołania. W wyniku jej działania zwracany jest zakres komórek lub pojedyncza komórka.
Argumenty funkcji to:

    • odwołanie – komórka, w stosunku do której wyznaczamy odwołanie.
      Argument obowiązkowy.
    • wiersze – liczba wierszy, o które ma być przesunięty początek zwracanego zakresu (w stosunku do numeru wiersza odwołania). Dla wartości dodatnich – przesuniecie następuje poniżej komórki odwołania, dla ujemnych – powyżej. Wstawienie liczby ułamkowej spowoduje, że jako argument pobrana będzie część całkowita (nie zaokrąglenie) tej liczby.
      Wartością domyślną jest 0 – czyli bez przesunięcia.
    • kolumny – liczba kolumn, o które ma być przesunięty początek zwracanego zakresu (w stosunku do numeru kolumny odwołania). Dla wartości dodatnich – przesuniecie następuje w prawo od komórki odwołania, dla ujemnych – w lewo. Wstawienie liczby ułamkowej spowoduje, że jako argument pobrana będzie część całkowita (nie zaokrąglenie) tej liczby.
      Wartością domyślną jest 0 – czyli bez przesunięcia.
    • wysokość – liczba wierszy zwracanego wyniku.
      Wartością domyślną jest ilość wierszy odwołania
    • szerokość – liczba kolumn zwracanego wyniku.
      Wartością domyślną jest ilość kolumn odwołania

W zależności od poszczególnych wartości wprowadzonych jako argumenty funkcji – wynikiem działania może być albo pojedyncza wartość albo tablica wartości.
Odpowiednikiem funkcji w kodzie VBA jest funkcja Offset().

Przykładem praktycznego zastosowania jest np. zakres dynamiczny.


Kurs Excel - zaawansowane funkcje i formuły

Komórki scalone w VBA

Scalanie komórek kodem VBA jest możliwe przy użyciu metody Merge obiektu Range.

Np. dla komórek z zakresu A1:A10

Range(“A1:A10”).Merge

Zdjęcie scalenia to metoda UnMerge

Range(“A1:A10”).UnMerge

To, co istotne w metodach stosowanych w odniesieniu do komórek scalonych (m.in. ClearContents) nie można się odwołać do pojedynczej komórki scalonego zakresu  – trzeba jako Range wpisać cały zakres.

 


 

Kurs Excel - makra i VBA dla początkujących

 

Komórki scalone

Komórki scalone to zakres komórek  scalony do jednej komórki. Zakres ten musi mieć kształt prostokąta (kwadratu).
Narzędzie scalania komórek jest dostępne na karcie Narzędzia główne, grupa opcji Wyrównanie.

Po zaznaczeniu zakresu komórek i naciśnięciu przycisku Scal i wyśrodkuj następuje scalenie komórek do jednej. Jeżeli scalane komórki nie są puste, wyświetli się komunikat ostrzegawczy przed opróżnieniem ich zawartości. Nowa, scalona komórka będzie miała adres i zawartość pierwszej komórki u góry z  lewej strony scalanego zakresu.
Przycisk ten działa jak przełącznik – ustawiając się na którejś z komórek zakresu i naciskając go ponownie – nastąpi zdjęcie scalenia komórek.

Czy warto stosować komórki scalone? Z pewnością tak, choć trzeba pamiętać, że pełnią one rolę głównie ozdobną. Pozwalają na ładne przedstawienie danych w formie tabelki, ale mogą się pojawić problemy przy analizie danych czy odwołaniach funkcji. Jeżeli argumentem jakieś funkcji, w zupełnie innym miejscu arkusza czy skoroszytu jest wartość którejś z dalszych komórek zakresu, wstawiona będzie wartość pusta.
Na screenie widoczny jest przykład, gdy w kolumnie A wstawiona jest data, a w kolumnie B – formuła wyliczająca ilość dni do końca miesiąca w stosunku do daty z kolumny A w tym samym wierszu:

Wartości są prawidłowe tylko w wierszu 2 i 12 czyli tam, gdzie rozpoczyna się scalony zakres (A2 i A12). Wstawiając więc taką formułę czy też zmieniając zakres , trzeba uważać.

 

 


Kurs Excel - zaawansowane techniki

Makra – odblokowywanie i uruchamianie

Jedną z domyślnych właściwości Excela jest ochrona przed otwarciem skoroszytów z makrami. Oczywiście nie chodzi tu o automatyczną blokadę wszystkich plików Excela, a tylko tych nieznanego pochodzenia, mogących zawierać szkodliwe oprogramowanie. Problematyczne mogą być tu przede wszystkim pliki pobrane z internetu czy otrzymane mailem od nieznanej nam osoby, lepiej na nie uważać.

W Excelu jest kilka opcji postępowania z plikami zawierającymi makra. Można je ustawić w Centrum Zaufania.
Karta Plik –> Opcje

Kolejny krok do przejście do Centrum Zaufania i kliknięcie przycisku Ustawienia Centrum Zaufania…

W nowym formularzu wybieramy Ustawienia makr

Domyślnie jest tu zaznaczona opcja druga: wyłącz wszystkie makra i wyświetl powiadomienia. I jest to moim zdaniem opcja optymalna – te ustawienia dotyczą  wszystkich plików otwieranych w Excelu, więc lepiej się zabezpieczyć.

Od tej pory pliki z makra otwierać się będą z komunikatem:

Jeżeli jesteśmy pewni pliku, wystarczy wcisnąć przycisk Włącz zawartość i makra zostaną włączone. Natomiast te, których nie jesteśmy pewni i nie naciśniemy przyciski – będzie można obejrzeć, edytować, jednak makra nie będą działać. Jednak uwaga – w przypadku bardziej złożonych projektów z oprogramowanymi zdarzeniami arkusza czy skoroszytu – bez makr mogą pojawić się problemy i całość nie będzie działać tak, jak powinna. Dotyczy to także funkcji użytkownika czyli funkcji zdefiniowanych kodem VBA.

Ponieważ jednak takie każdorazowe uruchamiane pliku z tym przyciskiem może być męczące. W takim przypadku warto odbezpieczyć cała grupę plików Excela, uznanych jako bezpieczne.
Robimy to także w ustawieniach Centrum Zaufania, ale tym razem wybieramy po lewej stronie Zaufane lokalizacje, a następnie naciskamy przycisk Dodaj nową lokalizację….

W nowym formularzu wystarczy wybrać folder (i ewentualnie znajdujące się w nim podfoldery), w którym chcemy przechowywać pliki Excela z makrami. Po zatwierdzeniu – pliki z tego folderu/folderów będą otwierać się automatycznie z włączonymi makrami.

 


 

Kurs Excel - zaawansowane techniki

 

Połączenie z innym plikiem Excela

Jak połączyć się z plikiem Excela, gdy np.na komputerze firmowym są ograniczone możliwości zainstalowania np.Power Query i nie można skorzystać z tego rozwiązania? Można to zrobić przez łącza w komórkach.

Załóżmy, że mamy taki plik udostępniony np. na serwerze firmowym:

Plik jest udostępniony, różni użytkownicy dodają/edytują dane, nie można w nim wstawić np. tabeli przestawnej, nie wszystkie makra działają. Na naszym komputerze lokalnym tworzymy własny plik do analizy danych (jeśli chcemy zastosować makra – to z rozszerzeniem .xlsm). Najprościej skopiować nagłówek – tym bardziej, że do analizy pewnie warto będzie dołożyć dodatkowe kolumny z obliczeniami.

Następnie wchodzimy do komórki A2, wpisujemy znak równości

a następnie, po przejściu do komórki A2 w pliku źródłowym, naciskamy Enter.

W komórce A2 pliku lokalnego pojawia się formuła:
=TabelaKsiazki.xlsx!$A$2
Teraz trzeba ją przerobić zmieniając adres bezwzględny na względny czyli
=TabelaKsiazki.xlsx!A2
Dodatkowo warto pamiętać, że jeżeli dana  komórka w pliku źródłowym jest pusta – w komórce z nią połączonej pokaże się zero. A ponieważ zero jest też wartością – lepiej tak zmodyfikować formułę, aby pobierała tylko wartości z niepustych komórek.
=JEŻELI(TabelaKsiazki.xlsx!A2=””;””;TabelaKsiazki.xlsx!A2)

No i na końcu przeciągamy formułę w prawo

i cały wiersz – w dół

I już – łącza do komórek są wstawione. Od tej pory wszystkie zmiany wprowadzone w pliku źródłowym będą odzwierciedlone w pliku lokalnym. Plik źródłowy może pozostać zamknięty, istotne jest odświeżanie łączy – karta Dane grupa opcji Połączenia.

Warto też zauważyć,  że przy zamkniętym pliku źródłowym automatycznie zmieniają się formuły w komórkach – pojawia się pełna  nazwa pliku :

 

 

 

Power Query do pliku Excela

Poprzednia notka PQ do Arkuszy Google opisuje jak ściągnąć dane z pliku Excela w internecie. W codziennej pracy często mamy jednak do czynienia z plikami udostępnionymi gdzieś na serwerze firmowym. Dane tam zapisane są nam potrzebne do analizy i raportowania. Oczywiście w prosty sposób można to zrobić również za pomocą Power Query.

Na karcie Power Query przechodzimy do grupy Pobierz dane zewnętrzne, naciskamy przycisk Z pliku i wybieramy Z programu Excel.

Otworzy się okno dialogowe, w którym wskazujemy plik, z którego chcemy pobrać dane.
Po wybraniu pliku w formularzu Nawigatora zaznaczamy arkusz z danymi.

Następnie naciskamy załaduj i wszystkie dane z pliku źródłowego znajdą się w pliku docelowym. Możemy w dowolny sposób je filtrować, obliczać czy analizować w tabeli przestawnej, jednocześnie nie wpływając na pracę innych użytkowników źródłowego pliku udostępnionego. W dodatku wszystkie zmiany wprowadzone przez innych użytkowników – będą się aktualizować po każdym odświeżeniu połączenia.
Można to zrobić w dowolnym momencie po wejściu w tabelę i naciśnięciu przycisku Odśwież na karcie Dane lub naciśnięciu przycisków Alt+F5.

Warto też zajrzeć do Właściwości połączenia… – w tym miejscu można ustawić zasady automatycznej aktualizacji danych:

W identyczny sposób można też pobierać dane nie tylko z plików Excela, ale również plików tekstowych csv.