UsedRange

UsedRange to jedna z właściwości arkusza. Zwraca obiekt Range obejmujący zakres używanych komórek. Zakres ten jest wyznaczany jako prostokąt, którego lewym górnym rogiem jest “niepusta” komórka. Istotne jest tu to, że komórką “niepustą” dla zakresu używanego może być komórka, w której nie ma żadnej wartości, ale jest np.sformatowana inaczej niż pozostałe komórki arkusza.
W kodzie VBA wywołanie UsedRange np. aktywnego arkusza wygląda tak:

Public Sub ZakresUzytkowy()
Dim ZakresA As Range
SET ZakresA=ActiveSheet.UsedRange
End Sub

Dla właściwości UsedRange można też wywołać adres:
UsedRange.Address

A gdzie jest właściwość UsedRange najczęściej używana? M.in. do sprawdzania i czyszczenia różnych śmieci, które czasem pojawiają się w arkuszu i niepotrzebnie obciążają pamięć i rozmiar pliku.


 

Kurs Programowanie VBA w Excelu dla zaawansowanych

 

Obiekt Range

Range to obiekt w kodzie VBA oznaczający komórkę lub zakres komórek arkusza.
Dla pojedynczej komórki zapis wygląda np. Range(“A1”)
Dla ciągłego zakresu komórek – np. Range(“A1:C10”) lub Range(“A1″,”C10”) – gdzie A1 to adres komórki w lewym górnym rogu, natomiast C1 to komórka wyznaczająca prawy dolny róg.

Można też przypisać całe kolumny lub wiersze, odwołując się do oznaczenia kolumny lub numeru wiersza.
Np. Range(“1:1”) – odpowiada zakresowi wiersza pierwszego
Range(“A:C”) – zakres kolumn A do C.
W kodzie można odwoływać się także do nazw zdefiniowanych w Menedżerze nazw. Jeżeli mamy taki zakres o nazwie np.MojeKomorki, to można się odwołać do niego poprzez Range(“MojeKomorki”).

Chcąc zadeklarować zakres jako zmienną w kodzie VBA korzystamy z:

Dim ZakresA As Range
Dim ZakresB As Range

Przypisanie wartości (czyli zakresu komórek) do zmiennej typu Range musi być, jak w przypadku wszystkich obiektów VBA, poprzedzone słowem kluczowym Set.

Set ZakresA=Range(“1:1”)
Set ZakresB = Range(“A:A”)

W odniesieniu zakresu Range często wykorzystuje się funkcje:

    • Intersect(Arg1 as Range, Arg1 as Range,… as Range
      funkcja zwracająca zakres będący częścią wspólną zakresów wprowadzonych jako argumenty
    • Union(Arg1 as Range, Arg1 as Range,… as Range
      funkcja zwracająca zakres będący sumą zakresów wprowadzonych jako argumenty

Obie funkcje przydają się szczególnie w kodzie związanym ze zdarzeniami arkusza.

 


 

 

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

 

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