W dzisiejszym poście zajmiemy się tematem scalania tego
samego zapytania w Power Query. Zagadnienie to omówimy na podstawie danych z
poprzedniego postu przedstawionych na rysunku nr 1.
rys. nr 1 — Przykładowe dane
Poprzedni post dotyczył wyciągania pierwszych i ostatnich
wierszy po unikatowych wartościach z kolumn Data i Firma. Stworzyliśmy dwa
zapytania i scaliliśmy je ze sobą. Tym razem chcemy mieć jedno zapytanie.
Zagadnienie to jest wstępem do języka zapytań M, czyli kulis zapytań Power
Query. Pozwoli nam to w miarę naszego rozwoju nie korzystać tylko z gotowych
rozwiązań tego dodatku do Excela.
Pierwszym krokiem jest wczytanie naszych danych z tabeli do
Power Query. Musimy w tym celu użyć polecenia Z tabeli z karty Dane (rys. nr 2).
rys. nr 2 — Pobieranie danych zewnętrznych
Tak jak w poprzednim poście dane zostały wczytane w innym
formacie więc musimy dla kolumny Data i Czas zmienić typ kolumny na bieżącą –
klikamy prawym przyciskiem myszy na tytuł kolumn Data i analogicznie Czas,
następnie wybieramy polecenie Data lub dla drugiej kolumny Godzina i na
komunikacie, który się wyświetli klikamy przycisk Zamień bieżącą (zaznaczono
zieloną strzałką na rysunku nr 3). Celem tych działań jest to, żeby Power Query
odpowiednio interpretował te dane.
rys. nr 3 — Zmiana typu kolumny
Na karcie Widok musimy mieć włączony Pasek formuły (rys. nr
4). Będziemy robić na nim drobne zmiany w kodzie.
rys. nr 4 — Pasek formuły
Wczytane dane przedstawione zostały na rysunku nr 5.
rys. nr 5 — Wczytane dane
Kolejnym krokiem jest posortowanie danych. Klikamy prawym
przyciskiem myszki na trójkącik przy tytule kolumny Data i wybieramy polecenie
Sortuj rosnąco (rys. nr 6).
rys. nr 6 — Sortuj rosnąco
Te same działania musimy powtórzyć dla kolumny Czas. Klikamy
prawym przyciskiem myszki na trójkącik przy tytule kolumny Czas i wybieramy
polecenie Sortuj rosnąco. Z boku ekranu w Ustawieniach zapytania mamy wpisane
kroki, które wykonaliśmy na naszych danych (rys. nr 7).
rys. nr 7 — Ustawienia zapytania
Możemy zmienić nazwę tego kroku, aby w każdym momencie
działań widzieć co konkretnie zrobiliśmy. Naciskamy klawisz F2 i zmieniamy
nazwę na Posortowano Asc (nie napisałem rosnąco, bo ze względu na język
programowania staram się nie używać polskich znaków) – rys. nr 8.
rys. nr 8 — Zmiana nazwy kroku
Teraz tak jak w poprzednim poście musimy usunąć duplikaty po
kolumnach Data i Firma. Zaznaczamy kolumny Data i Firma, a następnie wybieramy
z karty Narzędzia główne polecenie Usuń wiersze (punkt nr 2 na rysunku nr 9), a
potem Usuń duplikaty (punkt nr 3 na rysunku nr 9).
rys. nr 8 — Usuń duplikaty
Otrzymamy wyciągnięte pierwsze wiersze po unikatowych
wartościach. Zostały one przedstawione na rysunku nr 10.
rys. nr 10 — Pierwsze wiersze po unikatowych wartościach
Analogicznie jak dla poprzedniego etapu zmieniamy nazwę
zastosowanego kroku w Ustawieniach zapytania za pomocą klawisza F2 z Usunięto
duplikaty na Pierwsze.
Chcemy aby teraz kolejny krok jaki wykonamy nie był zależny
od kroku Pierwsze tylko od wcześniejszego czyli Posortowano Asc. Klikamy na
krok Posortowano Asc i kopiujemy formułę z paska formuły za pomocą skrótu
klawiszowego Ctrl+C. Następnie przechodzimy do ostatniego naszego kroku, czyli
Pierwsze i na pasku formuły kliknąć przycisk funkcyjny fx – dodać krok
niestandardowy i wkleić (Ctrl+V lub kliknąć prawym przyciskiem myszy i z
podręcznego menu wybrać polecenie Wklej) w pasek formuły skopiowaną wcześniej
formułę z kroku Posortowano Asc.
Musimy zmienić część wklejonej formuły, bo chcemy aby kolumna
Czas była posortowana malejąco. Najprościej wystarczyło by zmienić rodzaj
sortowania klikając na znaczek sortowania przy nazwie kolumny Czas (rys. nr 12).
rys. nr 12 — Sortuj malejąco po kolumnie Czas
Dla bardziej zaawansowanych użytkowników jest inny sposób.
Wystarczy zmienić w formule dla kolumny czas sortowanie z Ascending na Descending,
co zostało pokazane na rysunku nr 13.
rys. nr 13 — Zmiana na pasku formuły
Oba sposoby zadziałają w ten sam sposób. Zmieniamy nazwę
kroku Niestandardowe 1 za pomocą klawisza F2 na Posortowano Desc. Musimy
pamiętać, że Power Query ma problem z tym, że dane nie zostały wczytane od
nowa, pamięta wcześniejszy bufor więc jak w poprzednim poście musimy dodać
kolumnę indeksu. Z karty Dodaj kolumnę wybieramy polecenie Kolumna indeksu
(punkt nr 2 na rysunku nr 14) a następnie Od 0 (punkt nr 3 na rysunku nr 14).
Nie ma znaczenia jaki typ kolumny indeksu wybierzemy bo jest to kolumna
pomocnicza i w późniejszym etapie ją usuniemy.
rys. nr 14 — Kolumna indeksu
Power Query dzięki dodaniu dodatkowej kolumny od nowa zaczyta
dane i zapamięta kolejność z odwrotnym sortowaniem. Zaznaczamy kolumny Data i
Firma i z karty Narzędzia główne wybieramy polecenie Usuń wiersze a następnie
Usuń duplikaty (analogicznie jak na rysunku nr 9 dla pierwszych wierszy).
Otrzymamy ostatnie wiersze dla unikatowych wartości przedstawione na rysunku nr 15.
rys. nr 15 — Ostatnie wiersze dla unikatowych wartości
Zaznaczamy kolumnę Czas i Indeks i usuwamy za pomocą polecenia Usuń kolumny z podręcznego menu (rys. nr 16).
rys. nr 16 — Usuń kolumny
W
poprzednim poście te same wyniki otrzymaliśmy w dwóch osobnych zapytaniach. W
tym poście otrzymaliśmy takie same wyniki w jednym zapytaniu tylko w innych
krokach. Pierwsze wiersze są w kroku Pierwsze, a ostatnie wiersze są w kroku
Usunięto kolumny. Zmienimy jeszcze nazwę ostatniego kroku za pomocą klawisza F2
na Ostatnie (rys. nr 17). Teraz wiemy, które kroki chcemy połączyć.
rys. nr 17 — Kroki które trzeba połączyć
Z karty Narzędzia główne wybieramy polecenie Połącz (punkt nr
2 na rysunku nr 18), a następnie Scal zapytania (punkt nr 3 na rysunku nr 18).
rys. nr 18 — Scal zapytania
Otworzy nam się okno Scalanie. Scalać będziemy to samo
zapytanie, czyli wybierzemy tNotowania i w drugim okienku tNotowania (bieżący).
Zaznaczamy w obu zapytaniach kolumny Data i Firma. One są na razie identyczne.
W kolejnym kroku zrobimy pewną zmianę. Zatwierdzamy nasz wybór przyciskiem OK
(rys. nr 19).
rys. nr 19 — Okno scalanie
W tym kroku bardzo ważne jest, że widzimy pasek formuły, bo
mamy tam podane, na którym kroku wykonujemy dane zadanie (rys. nr 20).
rys. nr 20 — Pasek formuły
W naszej formule powinniśmy zmienić pierwsze wystąpienie
słowa Ostanie na słowo Pierwsze (nazwy kroków zapytania). Aby otrzymać
prawidłowe dane formuła na pasku formuły powinna wyglądać jak na rysunku nr 21.
rys. nr 21 — Zmiana nazwy kroku w formule
W formule powyżej mamy informację, że chcemy połączyć dane z
dwóch kroków w tym samym zapytaniu – krok Pierwsze i krok Ostatnie.
Z naszej tabeli musimy się pozbyć kolumny Czas za pomocą
polecenie Usuń kolumnę z podręcznego menu. Otrzymamy dane przedstawione na
rysunku nr 22.
rys. nr 22 — Usuń kolumnę
Następnie rozwijamy kolumnę Ostatnie (strzałki w prawym roku
nazwy kolumny), odznaczamy Datę i Firmę, a zostawiamy tylko Cenę. Nie chcemy
używać oryginalnej nazwy kolumny jako prefiksu i na koniec zatwierdzamy nasze
ustawienia przyciskiem OK (rys. nr 23).
rys. nr 23
W otrzymanych danych zmienimy nazwę dwóch ostatnich kolumn z
Cenami na Pierwsze i Ostatnie. Otrzymamy końcowe dane dla pierwszych i
ostatnich wierszy unikatowych wartości przedstawione na rysunku nr 24.
rys. nr 24 — Pierwsze i ostatnie wiersze dla unikatowych wartości
Są to dane uzyskane w jednym zapytaniu, włącznie ze
scalaniem. Był to wstęp do zagłębiania się w język M, aby poznać lepiej formuły
i możliwości jakie nam dają. Ostatnim krokiem jest załadowanie danych do Excela
za pomocą polecenia Zamknij i załaduj z karty Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu otworzy nam się okno Ładowanie do, gdzie wybieramy
sposób wyświetlania danych jako Tabela i miejsce załadowania danych jako
Istniejący arkusz (konkretną komórkę). Nasz wybór zatwierdzamy przyciskiem
Załaduj (rys. nr 26).
rys. nr 26 — Okno Ładowanie do
Dane wklejone do Excela prezentują się jak na rysunku nr 27.
Dane wklejone do Excela
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście nauczymy się jak wyciągnąć wartości z
ostatniego i pierwszego wiersza po unikatowych wierszach (wartościach) w Power
Query. Jest to kontynuacja tematów z porad o numerach 307 i 308. Zagadnienie to
omówimy na podstawie przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Wykorzystamy tutaj metodę, której
użyliśmy w poradzie 307 w Excelu, ale tym razem w Power Query. Pierwszym
krokiem jest wczytanie danych z Excela. Wybieramy polecenie Z tabeli (punkt nr
2 na rysunku nr 2) na karcie Dane (punkt 1).
rys. nr 2 — Pobieranie danych zewnętrznych z tabeli
Otworzy nam się Okno – Edytor
zapytań, a w nim dane z tabeli tNotowania (rysunek nr 3).
rys. nr 3 — Edytor zapytań
Power Query niepotrzebnie do daty dodaje czas, więc klikamy na ikonkę
kolumny Data, a następnie wybieramy polecenie Data (tylko data) z podręcznego
menu (punkt nr 2 na rysunku nr 4).
rys. nr 4
Pojawi nam się komunikat dotyczący zmiany typu kolumny.
Klikamy przycisk Zamień bieżącą (rys. nr 5).
rys. nr 5 — Komunikat o zmianie typu kolumny
Power Query nie rozpoznał poprawnie czasu, więc analogicznie
klikamy na ikonkę na kolumnie Czas i wybieramy polecenie Godzina z podręcznego
menu (rys. nr 6).
rys. nr 6
Pojawi się taki sam komunikat jak dla Czasu. Klikamy przycisk
Zamień bieżącą. Otrzymamy wtedy odpowiednio przedstawione dane z rysunku nr 7.
rys. nr 7
Mając tak posortowane dane , chcemy teraz wyciągnąć pierwsze
i ostatnie wiersze. Klikamy znak < zaznaczony na rysunku nr 7 zieloną
strzałką. Nasze zapytanie nazywa się tNotowania. Potrzebujemy 2 razy tego
zapytania, ponieważ raz chcemy wyciągnąć pierwsze wiersze, a za drugim razem
ostatnie wiersze. Klikamy prawym przyciskiem myszy na nazwę zapytania
tNotowania i z podręcznego menu wybieramy polecenie Duplikuj (rys. nr 8).
rys. nr 8 — Polecenie Duplikuj
Klikamy na tNotowania i zaznaczamy sobie kolumny dla których
chcemy wyciągnąć unikatowe wartości, czyli kolumny Data i Firma (rys. nr 9).
rys. nr 9 — Zaznaczone kolumny Data i Firma
W pierwszym kroku tak jak w poradzie 307 musimy usunąć
duplikujące się wartości. Z karty Narzędzia główne wybieramy polecenie Usuń
wiersze (punkt nr 2 na rysunku nr 10), a następnie polecenie Usuń duplikaty
(punkt nr 3).
rys. nr 10 — Usuń duplikaty
W wyniku usunięcia duplikatów otrzymamy pierwsze wiersze
przedstawione na rysunku nr 11.
rys. nr 11 — Pierwsze wiersze
Analogicznie zaczniemy działać dla zapytania tNotowania2. W
pierwszej kolejności powinniśmy posortować dane w kolumnie Data. Użyjemy do
tego polecenia Sortuj rosnąco pokazanego na rysunku nr 12.
rys. nr 12 — Sortuj rosnąco
A następnie musimy posortować kolumnę Czas w odwrotnej
kolejności, czyli polecenie Sortuj malejąco. Posortowanie w ten sposób danych
sprawiło, że na początku danych mamy ostatnie wiersze. Teraz wystarczy usunąć
duplikaty. Zaznaczamy kolumny Data i Firma i z karty Narzędzia główne wybieramy
polecenie Usuń wiersze, a następnie polecenie Usuń duplikaty (identycznie jak
na rysunku nr 10). Otrzymamy ostatnie wiersze przedstawione na rysunku nr 13.
s. nr 13 — Błędne ostatnie wiersze
Od razu widać że są to takie same wartości jak dla pierwszych
wierszy w zapytaniu tNotowania. Z Power Query jest taki problem, że nie odświeża
sobie bufora danych i działa na pierwszym zaczytaniu. Nie uwzględnia sortowania. Możemy obejść ten
błąd. Przed usunięciem duplikatów musimy wykonać operację która każe Powe Query
ponownie zaczytać dane, stworzyć ich nowy układ. Rozwiązanie to podpowiedział
mi Bill Szysz. Najprostszą operacją jest dodawanie kolumny indeksu. Z karty
Dodaj kolumnę, wybieramy polecenie Kolumna indeksu (punkt 2 na rysunku nr 14),
a następnie Od 1 (punkt 3).
rys. nr 14 — Kolumna indeksu
Nie ma znaczenia czy dodamy Kolumnę indeksu od 0 czy od 1 bo
chodzi tylko o ponowne wczytanie danych przez program Power Query. Zaznaczamy
kolumny Data i Firma a następnie wybieramy polecenie Usuń duplikaty z karty
Narzędzia główne (rys. nr 10 wyżej). Teraz otrzymamy dane przedstawione na
rysunku nr 15.
rys. nr 15 — Ostatnie wiersze
Tym razem dane są poprawne, możemy to zauważyć między innymi
po numerze kolumny indeksu (numery są nie po kolei). Kolumna Indeks i Czas są
nam niepotrzebne. Zaznaczamy je, klikamy prawym przyciskiem myszki i z
podręcznego menu wybieramy polecenie Usuń kolumny (rys. nr 16).
rys. nr 16 — Usuń kolumny
Otrzymamy dane dla zapytania tNotowania2 przedstawione na
rysunku nr 17.
rys. nr 17 — Dane dla zapytania tNotowania2
Analogicznie dla zapytania tNotowania usuwamy kolumnę Czas i
otrzymujemy wyniki przedstawione na rysunku nr 18.
rys. nr 18 — Dane po usunięciu kolumny Czas
W kolejnym kroku musimy scalić te zapytania. Zaznaczamy zapytanie tNotowania i z karty Narzędzia główne wybieramy polecenie Połącz, potem Scal zapytania (polecenia oznaczone kolejno na rysunku nr 19).
rys. nr 19 — Scal zapytania
Otworzy nam się okno Scalanie. Musimy pamiętać, aby dla
zapytania tNotowania (punkt nr 1 na rysunku nr 20) przy użyciu przycisku Ctrl
zaznaczyć kolumny Data i Firma, bo po tych kolumnach mamy wyznaczone niepowtarzalność
wiersza. Analogicznie dla zapytania tNotowania2 (punkt nr 2 na rysunku nr 20)
zaznaczamy kolumny Data i Firma. Następnie w punkcie oznaczony numerem 3 na rysunku
poniżej wybrać Rodzaj sprzężenia, czyli Lewe zewnętrzne.
rys. nr 20 — Scalanie
Zatwierdzamy opcje scalania przyciskiem OK i mamy dołożone do
naszego zapytania tNotowania wyniki zapytania tNotowania2 (rys. nr 21).
rys. nr 21 — Scalone dane dla dwóch zapytań
Na razie wyniki drugiego zapytania są w formie tabeli. A
wyniki poszczególnych wierszy przedstawione są poniżej (miejsce zaznaczone
zieloną strzałką na rysunku nr 22)
rys. nr 22
Z tych wartości potrzebujemy wyciągnąć tylko cenę bo inne
wartości już mamy. Klikamy ikonkę dwóch strzałek przy nazwie tNotowania2 i
zaznaczamy opcję Rozwiń, następnie zaznaczamy tylko kolumnę Cena i odznaczamy
checkbox przy poleceniu Użyj oryginalnej
nazwy kolumny jako prefiksu (polecenia kolejno oznaczone numerami od do 3 na
rysunku nr 23).
rys. nr 23
Zatwierdzamy nasz wybór przyciskiem OK i otrzymujemy dane
przedstawione na rysunku nr 24, zawierające ceny z obu zapytań. Możemy
pozmieniać nazwy kolumn na Pierwsza cena i Ostatnia cena.
rys. nr 24 — Dane z obu zapytań
Tak przygotowane dane możemy zamknąć i załadować do Excela.
Polecenie Zamknij i załaduj do na karcie Narzędzia główne (rys. nr 25).
rys. nr 25 — Zamknij i załaduj
W Excelu wyświetli nam się okno Ładowanie do. Wybieramy
sposób wyświetlania danych jako Utwórz tylko połączenie, a następnie
lokalizację wstawienia danych jako Nowy arkusz i Zatwierdzamy nasz wybór
klikając przycisk OK (rys. nr 26).
rys. nr 26 — Ładowanie do
Rozwijamy po prawej stronie okienko Zapytania do. Klikamy
prawym przyciskiem myszy na tNotowania i z podręcznego menu wybieramy polecenie
Załaduj do (rys. nr 27).
rys. nr 27 — Załaduj do
Pokaże się nam okno ładowanie do, w którym wybieramy sposób
wyświetlania danych jako Tabela i lokalizację ich wyświetlenia w Istniejącym
arkuszu, następnie klikamy przycisk Załaduj (rys. nr 28).
rys. nr 28 — przycisk Załaduj
Otrzymamy dane przedstawione na rysunku nr 29.
rys. nr 29 — Końcowe dane
Power Query sprawdza się przy wyznaczaniu wartości z
pierwszego i ostatniego wiersza, ale zdarzają się zgrzyty przy usuwaniu
duplikatów. Wynika to z tego, że Power Query potrzebuje przeczyszczenia buforu
danych, ponownego załadowania danych.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście dowiemy się jak wyznaczyć miejsce na
podium (zdobyte miejsce) w sytuacji kiedy w wynikach występują remisy (np. taki
sam czas). Temat ten omówimy na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Jest to temat z pytania mojego widza. W pytaniu tym chodziło
o to, że nie chcemy mieć standardowego wyniku, jaki zwraca nam np. funkcja
POZYCJA lub POZYCJA.NAJW. Funkcja POZYCJA.NAJW w wyniku daje nam takie samo
miejsce dla takiego samego wyniku, a następne miejsce jest z odstępem o 2
pozycje (rys. nr 2).
rys. nr 2 — Wyniki funkcji POZYCJA.NAJW
Podobnie zadziała funkcja POZYCJA.ŚR (średnia), zwróci nam
średnia z dwóch miejsc dla takiego samego wyniku, czyli dla tego samego czasu
da nam dwa takie same miejsca – 8,5 w naszym przykładzie, a kolejnym miejscem
będzie 10 (sytuacja oznaczona niebieskimi strzałkami na rysunku nr 3).
rys. nr 3 — Wyniki funkcji POZYCJA.ŚR
W pytaniu widza chodziło o uzyskanie takiego samego miejsca
przy remisie, a jednocześnie żeby następna pozycja w rankingu była większa
tylko o 1 (sytuacja zaznaczona na rysunku nr 4 zielonymi strzałkami).
rys. nr 4 — Wyniki jakie chcemy uzyskać
Chcemy mieć kontynuacje przyznawanych miejsc, bez przeskoków
w sytuacjach remisowych. Wpadłem na pomysł, aby wykorzystać standardową
formułę, która wyznaczy nam ilość unikatowych wartości. Rdzeniem tej formuły
jest funkcja LICZ.JEŻELI
Liczymy po zakresie z kolumny Czas (zakres blokujemy
bezwzględnie klawiszem F9), ile razy wystąpiły te same liczby, czyli nasze
kryterium to ten sam zakres.
=LICZ.JEŻELI($B$2:$B$16;$B$2:$B$16)
Mamy tu do czynienia z formułą tablicową. Kiedy podświetlimy
sobie wyniki w trybie edycji komórki klawiszem F9 otrzymamy wyniki
przedstawione na rysunku nr 5.
rys. nr 5 — Podgląd funkcji LICZ.JEŻELI
Jedynki w wyniku oznaczają, że ten czas się nie powtarza, a
dwójki otrzymujemy dla tych samych czasów. Możemy tu zastosować pewną sztuczkę,
mianowicie te wyniki wykorzystamy jako dzielnik. Podzielimy 1 przez wartości
otrzymane z funkcji LICZ.JEŻELI. Formuła będzie wyglądać następująco:
=1/LICZ.JEŻELI($B$2:$B$16;$B$2:$B$16)
Jak podejrzymy wyniki formuły w trybie edycji komórki otrzymamy
wartości przedstawione na rysunku nr 6.
rys. nr 6 — Wyniki formuły 1/LICZ.JEŻELI
W miejsce wartości 2 podstawiły się wyniki 0,5. Jeśli teraz
zsumujemy te wartości otrzymamy unikalną liczbę miejsc. Użyjemy tutaj funkcji
SUMA.ILOCZYNÓW. Zapis formuły będzie wyglądał następująco:
Po zatwierdzeniu formuły otrzymamy 14 unikalnych wartości bo
był jeden raz remis (mamy 15 zawodników, jeden remis, czyli daje nam to 14
miejsc).
Nie chcemy mieć ilości tych unikalnych wartości tylko chcemy
mieć konkretne miejsce przypisane do każdego czasu. Wystarczy zrobić prosty
test logiczny, w którym sprawdzimy czy wartości z kolumny Czas są mniejsze lub
równe aktualnie sprawdzanej wartości. Zapis formuły będzie wyglądał
następująco:
Musimy pamiętać o zapisaniu takich wartości w nawiasach,
ponieważ operacje porównania w Excelu wykonujemy jako ostatnie. Kiedy
podejrzymy sobie wyniki w trybie edycji komórki części z testem logicznym
otrzymamy tablice wartości logicznych PRAWDA i FAŁSZ (rys. nr 7).
rys. nr 7 — Wyniki — wartość logiczne
Kiedy na wartościach logicznych wykonujemy działania
matematyczne, zamieniają się na wartości 1 i 0, odpowiednio PRAWDA i FAŁSZ.
Kiedy podejrzymy cała formułę w trybie edycji komórki otrzymamy wyniki z
rysunku nr 8.
rys. nr 8 — Wyniki formuły
Z tych wyników możemy wywnioskować, że 0 oznaczają mniejsze
czasy od aktualnie sprawdzanego, więc po zsumowaniu wartości 1 otrzymujemy
numer miejsca w rankingu. Zatwierdzamy formułę Ctrl+Enter i kopiujemy ją na
wiersze poniżej. Otrzymujemy wyniki z rysunku nr 9.
rys. nr 9 Wynik formuły zwracającej nu,ery w rankingu bez luk (ze względu na takie same wyniki)
Podsumowując z powyższych obliczeń uzyskaliśmy to samo
miejsce w rankingu dla takich samych czasów a kolejne miejsce jest o 1 większe
(sytuacja oznaczona na rysunku powyżej kolorem czerwonym i niebieskim w
kolumnie E — Kontynuacja).
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście przedstawimy odpowiedź na jedno z pytań
moich widzów, czyli ile jest dni pomiędzy dwoma konkretnymi datami. W
przykładowych danych na rysunku nr 1 mamy podane daty przyjazdu i wyjazdu.
Naszym zadaniem jest policzyć ile dni z danego przedziału przypadało na
konkretne miesiące.
rys. nr 1 — Przykładowe dane
Chcemy zsumować ilość dni pomiędzy dwoma datami. Zaczynamy od
rozpisania miesięcy według ilości dni w każdym z nich (rys. nr 2)
rys. nr 2 — Rozpisanie miesięcy według ilości dni
Tak naprawdę to nie są nazwy miesięcy, tylko odpowiednio
sformatowane daty. Naciskamy skrót klawiszowy Ctrl+1, aby zobaczyć jakie
formatowanie zostało tu użyte. Mamy tu użyte formatowanie niestandardowe typu
mmmm, które sprawia że pokazuje się tylko nazwa miesiąca. Pod każdą nazwą
miesiąca kryje się data ostatniego dnia tego miesiąca (rys. nr 3).
rys. nr 3 — Formatowanie komórek
Aby obliczyć ilość dni w miesiącu użyjemy funkcji JEŻELI.
Będziemy sprawdzać czy daty z kolumny Data wyjazdu ($B$2:$B$10) są mniejsze od
ostatniego dnia danego miesiąca. Jeśli są mniejsze to chcemy otrzymać te daty z
kolumny Data wyjazdu. W przeciwnej sytuacji chcemy aby funkcji zwróciła nam
datę ostatniego dnia tego miesiąca. W pierwszym wierszu obliczeń formuła będzie
wyglądać następująco:
=JEŻELI($B$2:$B$10<E2;$B$2:$B$10;E2)
Kiedy podejrzymy wyniki tej operacji w trybie edycji komórki
za pomocą klawisza F9 otrzymamy wyniki z rysunku nr 4 (dni tego miesiąca, ale w
postaci liczbowej).
rys. nr 4 — Podgląd komórki w trybie edycji
Od wartości uzyskanych z formuły zapisanej powyżej musimy
odjąć daty przyjazdu wyznaczone w analogiczny sposób, czy za pomocą funkcji
JEŻELI. Sprawdzamy, czy daty z kolumny Data przyjazdu są mniejsze od daty
ostatniego dnia miesiąca. Jeśli są mniejsze to chcemy otrzymać te daty, jeśli
nie spełniają warunku chcemy by funkcji zwróciła nam datę ostatniego dnia
miesiąca. Zapis całej formuły będzie wyglądał następująco:
Zatwierdzamy tą formułę tablicową
Ctrl+Shift+Enter. Otrzymujemy wynik i przeciągamy go sobie na wiersze poniżej
(rys. nr 5).
rys. nr 5 — Błędny wynik formuły
Poniższe wyniki już nie są prawidłowe, bo w lutym powinno być
jedynie 10 dni. Uprościłem formułę i patrzę tylko na ostatni dzień miesiąca, przez
to biorę pod uwagę wszystkie dni przed końcem tego miesiąca. Czyli w lutym
otrzymaliśmy 31 dni ze stycznia i 10 dni z lutego. Łatwo to naprawić wystarczy
odjąć sumę dni z miesiąca poprzedzającego obliczenia. Zapis formuły będzie
wyglądał następująco:
Zaczynamy obliczenia od komórki z
nagłówkiem, żeby formuła zadziałała też dla stycznia. Należy jednak pamiętać że
funkcja SUMA ignoruje tekst, więc w styczniu nic nie odejmiemy. Zatwierdzamy
formułę Ctrl+Shift+Enter i kopiujemy ją na wiersze poniżej. Otrzymamy wyniki
przedstawione na rysunku nr 6.
rys. nr 6 — Wyniki funkcji SUMA
Podsumowując należy bezwzględnie pamiętać o zatwierdzaniu
formuł tablicowym skrótem klawiszowym Ctrl+Shift+Enter. Jeśli nie użyjemy tego
skrótu otrzymamy błędne wyniki.
W kolumnie G nazwanej Nie tablicowo przygotowałem dużo
bardziej skomplikowaną formułę, przy której nie musimy pamiętać o odpowiednim
zatwierdzeniu formuły (kombinacją klawiszy Ctrl+Shift+Enter). Zapis tej formuły
wyglądałby następująco:
Tutaj mieliśmy przykład dla miesięcy, ale
możemy policzyć ten przedział pomiędzy dowolnymi datami. Należy najpierw
obliczyć wszystkie dni przed konkretną datą, a później za pomocą odejmowania
uzyskać interesujący nas wynik.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
W dzisiejszym poście zajmiemy się tematem duplikatów przy
numerach kont bankowych, a konkretnie Formatowaniem warunkowym. Dostałem
zapytanie od jednego z widzów, co się stanie, jeśli na numery kont nałożymy
formatowanie warunkowe zaznaczające duplikaty. Zagadnienie to omówimy na
podstawie przykładowych numerów kont (wymyślonych) z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Zaznaczamy numery kont, następnie z karty Narzędzia główne
wybieramy polecenie Formatowanie warunkowe (punkt nr 2 na rysunku nr 2), potem
Reguły wyróżniania komórek i Duplikujące się wartości (punkt nr 4).
rys. nr 2 Reguły wyróżniania komórek
Pojawi nam się okno Zduplikowane wartości, w którym mamy
wybrane Zduplikowane wartości i w polu obok mamy podany rodzaj zaznaczenia tych
duplikatów – jasnoczerwone wypełnienie z ciemnoczerwonym tekstem (rys. nr 3).
Zatwierdzamy parametry formatowania warunkowego klikając przycisk OK.
rys. nr 3 — Sposób zaznaczenia zduplikowanych wartości
Otrzymamy wyniki – podświetlone całe numery kont bankowych.
Mimo że różnią się od siebie ostatnimi cyframi to Excel rozpoznał je jako powtarzające
się (rys. nr 4).
rys. nr 4 — Błędne dane
Formatowanie warunkowe nie zadziała prawidłowo. Wiąże się to
z tym, że jeśli wpiszemy w komórkę taką długą liczbę (dla Excel numer konta w
tym przykładzie to właśnie liczba), Excel zamieni ją na format naukowy (rys. nr 5).
rys. nr 5 — Sposób rozumienia długich liczb w Excelu
Możemy sprawdzić czy takie numery/teksty są duplikatami przy
użyciu funkcji SUMA.ILOCZYNÓW, która będzie sprawdzała test logiczny. Zapis
formuły będzie wyglądał następująco:
=SUMA.ILOCZYNÓW($A$2:$A$10=A2)
Funkcja ma za zadanie sprawdzić, czy wartości w naszym
zaznaczonym zakresie ($A$2:$A$10 blokujemy bezwzględnie klawiszem F4), są równe
temu konkretnemu numerowi z aktywnej komórki. W wyniku tej funkcji otrzymamy
tablicę wartości logicznych PRAWDA i FAŁSZ (rys. nr 6)
rys. nr 2 — Podgląd wyników funkcji SUMA.ILOCZYNÓW
Funkcja SUMA.ILOCZYNÓW i podobne funkcje SUMA ignorują
wartości logiczne PRAWDA i FAŁSZ. Dlatego te wartości logiczne musimy zamienić
na 0 i 1. Najłatwiej i najszybciej można to zrobić za pomocą podwójnego
przeczenia, czyli wstawiamy dwa minusy przed operacją porównania. Zapis formuły
będzie wyglądał następująco:
=SUMA.ILOCZYNÓW(–($A$2:$A$10=A2))
Teraz kiedy podejrzymy wynik za pomocą klawisza F9 w trybie
edycji komórki otrzymamy zera i jedynki (rys. nr 7).
rys. nr 7 — Użycie podwójnego minusa w formule
Zatwierdzamy formułę i kopiujemy na wiersze poniżej.
Otrzymamy wyniki pokazane na rysunku nr 8.
rys. nr 8 — Wartości powtarzające się
W wierszach w których jest wartość 1, mamy informację że ta
wartość jest jedyna — unikatowa. My
chcemy teraz zaznaczyć wartości zduplikowane, czyli z liczbą 2 lub 3, bo one
się powtarzają.
Wystarczyłby prosty tekst logiczny, ale ja lubię przy takich
operacjach po prostu odjąć wartość 1, bo wtedy mam w wynikach zera i jakieś
wartości liczbowe (rys. nr 9). Zapis formuły będzie wyglądać następująco:
=SUMA.ILOCZYNÓW(–($A$2:$A$10=A2))-1
rys. nr 9 — Wynik odjęcie od poprzedniej formuły wartości 1
Tam, gdzie mamy w wyniku 0, to tak jakbyśmy mieli FAŁSZ (nie
zostanie nałożone formatowanie warunkowe), a gdzie są wartości liczbowe powyżej
zera – PRAWDA (tutaj Excel nałoży formatowanie).
Teraz możemy skopiować naszą formułę w trybie edycji komórki
(Ctrl+V), zaznaczyć zakres z numerami kont i wybrać polecenie Formatowanie
warunkowe (punkt nr 2 na rysunku nr 10) z karty Narzędzia główne, a następnie
Nowa Reguła (punkt 3 rys. nr 10).
rys. nr 10 — Nowa reguła
Otworzy nam się okno Nowa reguła formatowania. Wybieramy Typ
reguły (punkt 1 na rysunku nr 11), czyli Użyj formuły do określenia komórek,
które należy sformatować. Następnie w pole oznaczone na rysunku punktem nr 2
wklejamy skopiowaną wcześniej formułę. Na koniec musimy ustawić Formatowanie –
polecenie Formatuj (punkt nr 3), gdzie w oknie Formatowanie komórek wybieramy
kolor wypełnienia. Zatwierdzamy przyciskiem OK.
rys. nr 11 — Tworzenie nowej reguły formatowania
Otrzymujemy odpowiednio sformatowane dane przedstawione na
rysunku nr 12.
rys. nr 12 — Sformatowane dane z zaznaczonymi duplikatami
Jest to formatowanie warunkowe, więc jeśli zmienimy dane
(numer konta), wyniki formatowania też ulegną zmianie – formatowanie warunkowe
jest dynamiczne.
Książka Mistrz Excela + promo na 35 urodziny
Chcę Cię poinformować, że w końcu udało mi zebrać środki i dopiąć wszystkich formalności, żeby powstało II wydanie mojej książki Mistrz Excela (zostałem wydawcą) II wydanie jest wzbogacone o rozdział (nr 22) wprowadzający w genialny dodatek (Power Query) do Excela służący do pobierania, łączenia i wstępnej obróbki danych z wielu źródeł.
Książka Mistrz Excela to historia Roberta, który musi poznać dobrze Excela na potrzeby nowej pracy. Książka jest napisana w formie rozmów Roberta z trenerem, dzięki temu jest przystępniejsza w odbiorze niż standardowe książki techniczne pisane językiem "wykładowym".
Rozmowy zostały podzielone na 22 tematyczne rozdziały, które krok po kroku wprowadzają Cię w tajniki Excela. Robert zaczyna naukę od poznania ciekawych aspektów sortowania i filtrowania danych w Excelu, przechodzi przez formatowanie warunkowe, tabele przestawne, funkcje wyszukujące i wiele innych tematów, by na koniec poznać wstępne informacje o VBA i Power Query. A wszystko to na praktycznych przykładach i z dużą ilością zdjęć.
Żebyś mógł śledzić postępy Roberta, do książki dołączone są pliki Excela, na których pracuje Robert.
Na powyższej stronie znajdziesz dokładniejszy opis książki, opinie osób, które kupiły I wydanie oraz podgląd pierwszego rozdziału książki, żeby upewnić się, czy forma rozmów przy nauce Excela jest dla Ciebie. Jeśli książka Ci się spodoba poinformuj o niej swoich znajomych.
W ramach promocji na moje 35 urodziny możesz też mieć każdy z moich kursów wideo na Udemy za zaledwie 35 zł. Linki do kursów zamieszczam poniżej. W każdym kursie są udostępnione filmy do podglądu, byś mógł się przekonać czy dany kurs jest dla Ciebie.
