0
0 Produkty w koszyku

No products in the cart.

Jak pobrać dane przycisku, który uruchomił makro — odcinek #18

Dziś chcemy poznać kod, który umożliwi nam odczytanie danych przycisku, który uruchomił makro, dzięki czemu będziemy mogli odczytać informacje bezpośrednio np: z tekstu przechowywanym w przycisku, a nie zamieszczać tych informacji w kodzie VBA.
Dodatkowo będziemy mogli podpiąć to samo makro pod wiele przycisków/kształtów i w zależności od tego, który przycisk klikniesz wpiszą się inne informacje.

Zrobimy to na uproszczonym przykładzie akcji, jakie mogą wykonywać koszykarze z kosmicznego meczu. Mamy 3 przyciski akcji i 5 przycisków zawodników. Odpowiednio przyciski akcji są podpięte pod makro Akcja, a przyciski zawodników pod makro Zawodnik.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 01

Dwie najważniejsze dla nas linijki kodu z tych dwóch makr to:
Przycisk = Application.Caller
NazwaPrzycisku = ActiveSheet.Shapes(Przycisk).TextFrame.Characters.Text

Najpierw pobieramy "wewnętrzną" nazwę przycisku (obiektu) który uruchomił makro, a później wykorzystujemy tą nazwę, żeby wyciągnąć dokładną informację o tekście przechowywanym w kształcie/przycisku. Ot i cała filozofia. Reszta kodu służy temu, żeby informacje wstawiały się w odpowiednich miejscach.

Przy tym kodzie ważna jest jeszcze informacja, że warto, żeby tekst w kształtach nie był wpisywany ręcznie tylko pobierany z komórek Excela.

VBA 18 - Jak pobrać dane przycisku, który uruchomił makro 02

Dzięki temu szybko możemy zmienić np: drużynę i jej wszystkich zawodników po prostu przekopiowując w komórki powiązane zawodników z drugiej drużyny.

Sub Zawodnik()
Dim Przycisk As String, NazwaPrzycisku As String
'Pobiera "wewnętrzną" nazwę przycisku/kształtu
Przycisk = Application.Caller
'na podstawie tej nazwy wyciągamy tekst wpisany w tym kształcie
NazwaPrzycisku = ActiveSheet.Shapes(Przycisk).TextFrame.Characters.Text

If Range("C2").Value = "" Then
Range("C2").Value = NazwaPrzycisku
Else
Range("C1").End(xlDown).Offset(1, 0).Value = NazwaPrzycisku
End If
End Sub

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Wyciąganie drugiego imienia — Tekst jako kolumny i JEŻELI działaj jak umiesz — porada #282

Mówi się, że przeciętny użytkownik Excela zna około 2% jego możliwości. Dla nas nie jest istotne ile tych procent faktycznie jest, ale żeby nauczyć się korzystać z tego co umiesz, bo w Excelu, jak w życiu, jest wiele sposobów na rozwiązanie problemów, które stoją przed Tobą. Dlatego w tym wpisie chodzi przede wszystkim o to, żeby nakłonić Cię do myślenia, żebyś wykorzystał swoje szare komórki i umiejętności do rozwiązania problemu, a nie żebyś załamywał ręce.

Zrobimy to na przykładzie podziału danych osobowych na pierwsze imię, drugie imię i nazwisko. Robimy założenie, że znamy polecenie Tekst jako kolumny i JEŻELI.

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-01

W pierwszym kroku wystarczy, że dane osobowe podzielimy za pomocą polecenia Tekst jako kolumny z karty Dane 

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-02

według ogranicznika,

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-03

którym będzie spacja

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-04

na kolumny i wstawimy do komórki B2.

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-05

Nasz podział nie będzie idealny, bo czasami będziemy mieli 2, a czasami 3 kolumny, ale tutaj z pomocą przyjdzie nam jeszcze funkcja JEŻELI. Najpierw będziemy sprawdzać czy jest drugie imię – jeśli jest wypełniona 3 kolumna to znaczy, że jest drugie imię, co się przekłada na formułę:

=JEŻELI(D3="";"";C3)

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-06

W podobny sposób, jeśli 3 kolumna nie jest pusta oznacza to, że jest w niej nazwisko, a jeśli jest to znaczy, że nazwisko jest w 2 kolumnie, czyli możemy odpowiednio napisać taką formułę:

=JEŻELI(D2>"";D2;C2)

porada-282-wyciaganie-drugiego-imienia-tekst-jako-kolumny-i-jezeli-dzialaj-jak-umiesz-07

Może nie jest to najelegantszy sposób na rozwiązanie tego problemu, ale w większości sytuacji wystarczający i przede wszystkim opiera się, z założenia, o znane nam funkcjonalności Excela 😉

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Jak zaznaczyć 5 komórek w ciągu, które spełniają warunek — widzowie #108

Dostałem zapytania jak zaznaczyć ciąg 5 (lub więcej) komórek, które spełniają warunek (np: są puste). Jeśli możesz sobie pozwolić, że nad danymi, które sprawdzasz jest odpowiednia ilość wierszy, gdzie nie będzie spełniony sprawdzany warunek, to zadanie sprowadza się do liczenia najdłuższego ciągu w zakresie, który się przesuwa — porada 263:
https://www.youtube.com/watch?v=as9mztMnTsw

Końcowa formuła tablicowa (musisz zatwierdzić Ctrl + Shift + Enter):

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4

Jednak czasami zdarza się sytuacja, gdzie musisz martwić się o sufit, czyli górę arkusza, żeby Twój zakres nie wyszedł poza niego. W takiej sytuacji musisz najpierw zbudować zakres, który zatrzyma się na pierwszym wierszu arkusza i nie będzie szedł wyżej. Przyda się Ci do tego funkcja INDEKS.

Tablicą na którą będziemy patrzeć jest cała kolumna A (odwołanie A:A). Musimy tylko wybrać odpowiedni wiersz. W górę musimy przesunąć się 4 wiersze w górę (dla ciągu 5 komórek spełniających nasz warunek), ale nie dalej niż wiersz 1. Wystarczy, że do funkcji MAX wstawimy formułę WIERSZ()-4 oraz 1, funkcja WIERSZ bez podania argumentów zwraca wiersz komórki, w której znajduje się formuła. Czyli pierwsza komórka zakresu to:

=INDEKS(A:A;MAX(WIERSZ()-4;1))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-01

Tu trzeba wspomnieć, że funkcja INDEKS zwraca faktycznie odwołanie do komórki, a nie jej wartość, dzięki czemu jeśli dołożymy dwukropek możemy za pomocą funkcji INDEKS budować zakresy. Druga komórka jest łatwiejsza do zbudowania, bo to tylko WIERSZ()+4, czyli:

=INDEKS(A:A;WIERSZ()+4)

łącząc dwie powyższe formuły mamy zbudowany dynamiczny zakres:

=INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4)

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-02

Teraz musimy sprawdzić, czy komórki z zakresu spełniają warunek, tu jest on prosty — wystarczy wstawić naszą formułę do funkcji CZY.PUSTA. Ważniejszy jest kolejny krok, gdzie jeśli dana komórka spełnia nasz warunek, to chcemy, żeby został zwrócony numer wiersza tej komórki. Będziemy potrzebować funkcji JEŻELI i funkcji WIERSZ po naszym zbudowanym zakresie. Jeśli komórka nie spełnia warunku chcemy uzyskać pusty ciąg znaków (""), czyli całość sprowadza się do formuły:

=JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"")

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-03

Zbudowana przez nas formuła posłuży jako tablica danych do funkcji CZĘSTOŚĆ, natomiast jako tablicę przedziałów będziemy chcieli mieć odwrotność zbudowanej formuły, czyli pusty ciąg znaków ma być wtedy, gdy warunek jest spełniony, a numer wiersza wtedy, gdy warunek nie jest spełniony.

=CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"");JEŻELI(CZY.PUSTA(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4));"";WIERSZ(INDEKS(A:A;MAX(WIERSZ()-4;1)):INDEKS(A:A;WIERSZ()+4))))

widzowie-108-jak-zaznaczyc-5-komorek-w-ciagu-ktore-spelniaja-warunek-04

Pozostaje sprawdzić, jaki jest maksymalny ciąg (wstawić naszą formułę do funkcji MAX) i pamiętać żeby zatwierdzić kombinacją klawiszy Ctrl + Shift + Enter ponieważ jest to formuła tablicowa.

Niestety tej formuły nie przyjmie nam formatowanie warunkowe, bo dynamicznie budujemy zakres za pomocą funkcji INDEKS i musimy sobie zostawić formułę w kolumnie pomocniczej, by sprawdzać, czy dla danego wiersza maksymalny ciąg spełnia nasze kryteria. 

Ponieważ te komplikacje występują przypomnę jeszcze raz, że łatwiejszym rozwiązaniem może być dostawienie wierszy na początku arkusza, żebyśmy od pierwszej sprawdzanej komórki mogli zaznaczyć cały sprawdzany zakres, który będzie się przesuwał z formułą w dół, czyli przykładowa formuła:

=MAX(CZĘSTOŚĆ(JEŻELI(CZY.PUSTA(A1:A9);WIERSZ(A1:A9);"");JEŻELI(CZY.PUSTA(A1:A9);"";WIERSZ(A1:A9))))>4

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Zmiana ludzkiej tabelki na bardziej bazodanową — funkcje Excela — porada #281

Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-01

Na podstawie danych nie da się stworzyć Tabeli Przestawnej i innych analiz danych dostępnych w Excelu. Trzeba je najpierw przekształcić . W poradzie 280 opisałem jak to można zrobić za pomocą Power Query, ale ponieważ nie wszyscy mogą zainstalować ten dodatek poniżej znajdziesz opisany sposób jako możesz to zrobić za pomocą formuł w Excelu. Specjalnie dlatego nagrałem ten film na Excelu 2007, żeby pokazać, że jest to możliwe.

W pierwszej kolejności musimy usunąć scalanie nagłówków, żeby późniejsze formuły były prostsze.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-02

Później musimy zaznaczyć tylko puste komórki z nagłówków. Najprościej to zrobisz najpierw naciskając skrót klawiszowy Ctrl + G, a następnie z okna, które się pojawi wybierzesz przycisk Specjalnie.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-03

W kolejnym oknie, które się pojawi musisz wybrać polecenie Puste i zatwierdzić wybór przyciskiem OK.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-04

Teraz musisz wpisać znak = w komórkę i kliknąć na komórkę obok i zatwierdzić formułę skrótem Ctrl + Enter. Dzięki temu formuła odpowiednio przesunięta zostanie wstawiona we wszystkie puste komórki.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-05

Następnie potrzebujemy skopiować nagłówki i wkleić je jako wartości.

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-06

Teraz możemy się zająć pisaniem formuł do tabeli. Zaczniemy od numeru entomologa. Wiemy, że jest ich 20 i muszę się systematycznie powtarzać, żeby dla każdego entomologa (jego numeru) w wierszu znalazł się jeszcze miesiąc i typ owada. Ponieważ mamy 20 entomologów, 3 typu owadów i 12 miesięcy, to w sumie będziemy potrzebować 720 wierszy.

W takim razie jak zapewnimy sobie cykliczne powtarzanie numerów entomologów? W pierwszej kolejności musimy liczyć wiersze (ILE.WIERSZY($A$2:A2)), a następnie wyciągać z nich resztę z dzielenia przez 20 (funkcja MOD), bo tyle w przykładzie mamy numerów.

Musimy tylko dokonać drobnych korekt, żeby numery powtarzały się od 1 do 20.

=MOD(ILE.WIERSZY($A$2:A2)-1;$H$1)+1

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-07

Teraz możemy przejść do następnej kolumny, musimy dla każdej serii numerów wstawić miesiąc z kolejnej kolumny, czyli dla pierwszej serii numerów mamy wstawić miesiąc z pierwszej kolumny, dla następnej z drugiej itd.

Będzie nam do tego przydatna funkcja INDEKS, która będzie wskazywała na nagłówki z miesiącami. Będziemy wyciągać zawsze informację z pierwszego wiersza, więc jedynym problemem będzie wybranie odpowiedniej kolumny. 

Na szczęście funkcja INDEKS nie patrzy na część dziesiętną liczby, dlatego wystarczy, że odrobinę zmodyfikujemy formułę z numerów entomologów — zamiast wyciągać resztę z dzielenia, będziemy dzielić przez ilość entomologów.

=INDEKS(Dane!$B$3:$AK$3;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-08

W analogiczny sposób wybieramy typy owadów. Tylko patrzymy na wiersz niżej.

=INDEKS(Dane!$B$4:$AK$4;1;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-09

Uff. Została nam ostatnia formuła. Tym razem musimy pobrać dane z tabeli, czyli w naszym przykładzie zmyśloną ilość owadów, które złapał entomolog, w danym miesiącu, danego typu.

Znów pomoże nam funkcja INDEKS, ale będziemy musieli wybierać różne numery wiersza, ale to już mamy w pierwszej kolumnie (numery entomologów), a numer kolumny wybieramy tak jak wcześniej, czyli nasza formuła przybierze postać:

=INDEKS(Dane!$B$5:$AK$24;A2;(ILE.WIERSZY($C$1:C1)-1)/$H$1+1)

porada-281-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-funkcje-excela-10

Nareszcie konie pracy 😀 Uważam, że to jest skomplikowany proces i jeśli tylko możesz skorzystać z dodatku Power Query zobacz poradę 280 gdzie jest opisany proces konwersji danych za pomocą właśnie Power Query.

Pozdrawiam
Adam Kopeć
Miłośnik Excela

Zmiana ludzkiej tabelki na bardziej bazodanową — porada #280

Często dostaje dane, które są zapisany w wygodny dla człowieka sposób, ale bardzo niewygodny dla Excela.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-01

Na podstawie danych nie da się stworzyć Tabeli Przestawnej i innych analiz danych dostępnych w Excelu. Trzeba je najpierw przekształcić .

Niedawno tego samego dnia dwóch ekspertów od Excela zamieściło filmy, w których znalazło się również rozwiązanie mojego problemu za pomocą Power Query.

Oz du Solei https://www.youtube.com/watch?v=EM15idCJXXU
Mike Girvin https://www.youtube.com/watch?v=_csX8sCzJd0

Więc jeśli masz taki sam problem jak ja zobacz jak go możesz rozwiązać za pomocą PowerQuery (jeśli nie możesz zainstalować u siebie tego dodatku do Excela zobacz porada 281, gdzie opisuję, jak to robię za pomocą formuł.
Pierwszą rzeczą, którą musimy zrobić to zamienić nasz zakres danych na tabelę, ale odznaczamy, że nasza tabela ma nagłówki. Ułatwi nam to później operacje. 

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-02

Zwróć uwagę, że miesiące były wpisywane w scalonych komórkach, a teraz się rozdzieliły. W odpowiednim kroku szybko to naprawimy. Najpierw musimy wczytać naszą tabelę do Power Query. Ponieważ mam w końcu Excel 2016, to robię to z karty dane (wcześniej musiałem instalować dodatek i korzystać z karty Power Query). 

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-04

Naszym danym nie jest potrzebna zmiana rodzaju danych, więc możemy ten krok usunąć.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-03

Kolejnym krokiem będzie transponowanie danych.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-05

Następnie możemy wykorzystać pierwszy wiersz danych jako nagłówki.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-06

Kolejny krok to wypełnienie w dół kolumny miesiące, czyli wypełnianie pustych komórek wartościami, które znajdują się nad nimi (w pewnym momencie musi znaleźć się wypełniony wiersz ;))

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-08

Następnie musimy zaznaczyć 2 pierwsze kolumny i anulować przestawienie pozostałych kolumn.

porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-07

Pozostaje jeszcze zmiana nazw kolumn (wystarczy, że klikniesz w nią dwukrotnie), żeby bardziej odpowiadały danym i już możesz je załadować do nowego arkusza Excela.
porada-280-zamiana-ludzkiej-tabelki-na-bardziej-bazodanowa-power-query-09

Dla naszych przykładowych danych powstało 720 wierszy, na podstawie których możesz już bez problemu stworzyć Tabelę Przestawną lub inaczej je analizować.

Pozdrawiam
Adam Kopeć
Miłośnik Excela