Metody obiektu Range

Najczęściej stosowane metody obiektu Range (czyli komórki lub zakresu komórek) to:

    • czyszczenie zakresu
      • Clear
        całkowite usunięcie treści, formatowania, obramowania wyrównania i komentarzy
      • ClearComments
        usuwanie komentarzy
      • ClearContents
        usuwanie treści i formuł
      • ClearFormats
        czyszczenie formatowania
    • kopiowanie, wycinanie, usuwanie
      • Copy
        cały zakres zostaje skopiowany do schowka
      • Cut
        komórki zakresu zostają wycięte i przekopiowanie do schowka
      • Delete
        usuwanie zakresu komórek
    • zaznaczanie
        • Select
          zaznaczanie zakresu
    • scalanie, rozcalanie
        • Merge
          scalenie komórek w zakresie
        • UnMerge
          rozcalenie komórek w zakresie

Jak to wygląda w praktyce? Najprościej daną metodę wybrać z rozwijalnej listy właściwości i metod uaktywnianej po wstawieniu kropki za oznaczeniem zakresu:

Oznaczenia metod to:

 

Oznaczenia właściwości to:

 

Przykładowy fragment kodu zaznaczającego zakres wygląda tak:

Public Sub CzyscZakres()
Dim Zakres As Range
SET ZakresA=Range(“A1:A10”)
Zakres.Clear
End Sub

lub np.tak:

Public Sub ZaznaczZakres()
Range(“A1:A10”).Select
End Sub

 

Excelowy webinar

Kilka dni temu na stronie Excel dla każdego na FB można było uczestniczyć w ciekawym webinarze. Można go podejrzeć tu:

101 trików w Excelu – zapis webinaru

Tylko uwaga – prawidłowy zapis jest od 44 minuty, początek niestety został poważnie zakłócony przez działania botów.

 

 

Tabela w Excelu

Tabela w Excelu to nie tylko ładnie opisane, sformatowane i obramowane komórki – takie coś nazywam zawsze tylko tabelką. Prawdziwa tabela to obiekt, mający swoje właściwości  i metody.

Załóżmy, że mamy taką tabelkę:

Chcąc przekształcić te komórki w prawdziwą tabelę, zaznaczamy je, a następnie na karcie Wstawianie wciskamy przycisk Tabela. Można też skorzystać ze skrótu klawiaturowego  Ctrl+T.

Pojawi się formularz, w którym można zmienić/zatwierdzić zakres danych jakie mają być tabeli.

Po wstawieniu tabeli do arkusza, aktywacja dowolnej jej komórki powoduje wyświetlenie nowej karty Projektowanie.

kliknij, aby powiększyć

Domyślnie nadawana nazwa dla obiektu tabeli to Tabela1 (i dalej Tabela2, Tabela3…). Dostępne są też wzory formatowania, możliwość wstawienia wiersza podsumowania i.in.

Mają c już tabelę – na co warto zwrócić uwagę?  Jest kilka istotnych właściwości:

nazwy strukturalne

    • każda tabela musi mieć nagłówek. Jeżeli przy tworzeniu tabeli nie ma wiersza spełniającego takich kryteriów, zostanie automatycznie nadany z nazwami odnoszącymi się do numerów kolumn (Kolumna1, Kolumna2… )
    • nagłówki tabelki są nazwami strukturalnymi dla wartości w poszczególnych kolumnach. Widać to chociażby  w formułach, w których argumentami są wartości tabeli – nie ma tu odniesień to adresów komórek tylko właśnie do nazw strukturalnych
      właściwości ogólne
    • w wierszu nagłówka automatycznie wbudowane są znaczki autofiltra pozwalające na filtrowanie rekordów tabeli,
    • wstawienie formuły w pierwszym wierszu tabeli powoduje, że cała kolumna automatycznie zostaje wypełniona tą formułą, nie trzeba nic przeciągać czy kopiować,
    • chcąc dodać kolejny wiersz tabeli -ustawiamy się w ostatniej kolumnie ostatniego wiersza i naciskamy  Tab. Nowy wiersz zostanie automatycznie dodany na końcu tabeli, jeszcze przed wierszem podsumowania. W kolumnach z formułami, pojawią się one także w tym nowym wierszu.
    • w wierszu podsumowania można modyfikować funkcje agregacji. Nie musi to być suma, może być średnia, liczba rekordów itp.
    • po zastosowaniu filtra tabeli – automatycznie zmieniają się wartości wiersza podsumowania, uwzględniając tylko widoczne wartości.

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