Jak zaznaczyć urlop w kalendarzu — widzowie #116

Załóżmy, że masz kalendarz i chcesz w nim zaznaczyć dni na które przypada urlop. Kolejne urlopy mamy zapisane jako daty od do.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 01

Żeby to zrobić musimy najpierw napisać formułę, która będzie sprawdzać, czy dana data jest jednocześnie większa bądź równa dacie od i mniejsza bądź równa dacie do. Tylko nie będziemy tego robić na pojedynczych datach tylko od razu na kolumnie dat od i kolumnie dat do. Standardowo przy formatowaniu warunkowym najpierw piszemy formułę w komórkach obok, a jak ją przetestujemy to przeklejamy do formatowania warunkowego.

Czyli zaczynamy od porównania pojedynczej daty (komórka C3) z kolumną dat od:

=C3>=$AT$2:$AT$14

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 02

Powyższa operacja porównania zwróci nam tablicę wartości PRAWDA i FAŁSZ. Tą operację porównania musimy połączyć jeszcze ze sprawdzeniem czy pojedyncza data (C3) jest mniejsza bądź równa od dat do (końca urlopu). Najszybciej połączymy te operacje porównania za pomocą mnożenia. Musimy tylko pamiętać o kolejności operacji dlatego operacje porównania musimy wstawić w nawiasach.

=(C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14)

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 03

Tym razem gdybyśmy podejrzeli wynik naszej formuły to otrzymalibyśmy tablicę 0 i 1, najczęściej tylko zer. Jedynka pojawiłaby się tylko wtedy, kiedy przypadałby urlop na podstawie konkretnej pary od do.

={0;0;0;0;0;0;0;0;0;0;0;0;0}

Teraz potrzebujemy zsumować wynikową tablicę. Najlepiej to zrobić funkcją SUMA.ILOCZYNÓW – dzięki temu nie będziemy się musieli martwić, że pracujemy z formułami tablicowymi.

=SUMA.ILOCZYNÓW((C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14))

Po przeciągnięciu formuły w bok i dół uzyskamy 1 tam gdzie przypada urlop.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 04

Teraz wystarczy skopiować formułę do formatowania warunkowego po zakresie dat naszego kalendarza i już będę one podświetlały się ponieważ formatowanie warunkowe 0 potraktują jako wartości FAŁSZ, a 1 jako wartości PRAWDA.

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 05

P.S. Jeśli nie chcesz, żeby w tym mini kalendarzu urlopy zaznaczały się podwójnie, czyli na datach, które nie przypadają na konkretny miesiąc w wierszu musisz dopisać jeszcze jeden warunek do formuły:

=SUMA.ILOCZYNÓW((C3>=$AT$2:$AT$14)*(C3=$AU$2:$AU$14)*(MIESIĄC($B3)=MIESIĄC(C3)))

widzowie 116 - Jak zaznaczyć urlop w kalendarzu 06

Mini kalendarz w arkuszu — porada #167
https://www.youtube.com/watch?v=xfsTU4IVP_k

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Jak znaleźć przedostatni wiersz po warunku (formuła tablicowa) — widzowie #115

Czasami potrzebujemy znaleźć przedostatnią wartość na liście, a nawet przedostatnią wartość pod warunkiem. Rozpatrzmy to na prostym zestawie biegaczy i ich czasów, tylko nie chodzi nam o ich przedostatni czas w kontekście wartości, ale miejsca na naszej liście.

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 01

W pierwszej kolejności będziemy musieli sprawdzać warunek – wykonać test logiczny, a jak wykonujemy test logiczny, to niemal nieodzowna jest funkcja JEŻELI. Właśnie w niej napiszemy nasz prosty warunek, czy pozycja na liście jest równa wybranemu przez nas biegaczowi.

=JEŻELI(A2:A21=E1

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 02

Jeśli warunek jest spełniony to chcemy uzyskać numer wiersza danych (funkcja WIERSZ), gdzie ten warunek został spełniony. W przeciwnej sytuacji chcemy mieć zwracaną wartość FAŁSZ – wystarczy, że nie wypełnimy trzeciego argumentu funkcji JEŻELI:

=JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 03

Jeśli podejrzymy wynik formuły (klawiszem F9) to zobaczymy tablicę wartości FAŁSZ i numerów wiersza danych tam, gdzie warunek został spełniony.

{FAŁSZ;FAŁSZ;FAŁSZ;4;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ;FAŁSZ}

Nas interesuje druga maksymalna wartość, czyli potrzebujemy skorzystać z funkcji MAX.K, gdzie jej drugim argumentem będzie wartość 2.

=MAX.K(JEŻELI(A2:A21=E1;WIERSZ(A2:A21)-WIERSZ(A2)+1);2)

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 04

Musimy tylko pamiętać zatwierdzać formułę kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Uzyskujemy wtedy numer wiersza, gdzie jest interesująca nas wartość, a my potrzebujemy samej wartości. Wystarczy, że skorzystamy z funkcji INDEKS, której podamy kolumnę, z której chcemy poznać wartość.

=INDEKS($B$2:$B$21;MAX.K(JEŻELI($A$2:$A$21=E1;WIERSZ($A$2:$A$21)-1);2))

Widzowie 115 - Jak znaleźć przedostatni wiersz po warunku 05

Ciągle musimy pamiętać zatwierdzać formułę Ctrl + Shift + Enter. Analogicznie możesz budować formuły pozwalające Ci odszukać kolejne wystąpienia warunku zmieniając funkcję MAX.K na MIN.K

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Łączenie kolumn z danymi — Excel PowerQuery #2

Mamy problem, gdyż nasze dane rozdzielone są na wiele kolumn przechowujących te same informacje.

PowerQuery 2 - Łączenie kolumn z danymi 01

Takie dane bardzo trudno analizować, więc potrzebujemy je złączyć w pojedyncze kolumn. Wykorzystamy rozwiązanie Oza du Soleil, które opiera się o użycie dodatku do PowerQuery do Excela.

https://www.youtube.com/watch?v=hW9eVTbRbgI

Najpierw będziemy musieli zrobić pewną operację na danych – będziemy musieli połączyć informacje z dwóch kolumn do jednej. Tylko potrzebujemy dodać pomiędzy nimi ogranicznik, po którym później będziemy mogli podzielić je ponownie. Ważne, żebyśmy byli pewni, że ten ogranicznik nie występuje w naszych danych. Mogą to być np.: 2 symbole ^^

=A3&”^^”&B3

PowerQuery 2 - Łączenie kolumn z danymi 02

Następnie oprócz komórki z formułą zaznaczamy pustą komórkę po prawej stronie i przeciągamy je do końca danych. Następnie tak stworzony wiersz kopiujemy w dół do końca wszystkich danych. Musimy jeszcze dodać (skopiować) do naszych danych nagłówek, ponieważ dane do PowerQuery w Excelu należy pobierać z tabeli Excela.

PowerQuery 2 - Łączenie kolumn z danymi 03

Teraz możemy pobrać dane do zapytania PowerQuery. Korzystając z odpowiedniego polecenia z karty dodatku lub z karty Dane od Excela 2016.

PowerQuery 2 - Łączenie kolumn z danymi 04

Po wczytaniu danych do PowerQuery musimy wykonać następujące kroki:
1. Musimy przenieść nagłówki do pierwszego wiersza

PowerQuery 2 - Łączenie kolumn z danymi 05

2. Transponować dane

PowerQuery 2 - Łączenie kolumn z danymi 06

3. Odfiltrować wszystkie wiersze, które zawierają słowo Ilość (w naszym przykładzie), czyli te wiersze, które dla których kolumny były puste. Ważne, że nie możemy po prostu odznaczyć ich w filtrze, tylko musimy skorzystać z filtrów tekstowych i wybrać kryterium nie zawiera.

PowerQuery 2 - Łączenie kolumn z danymi 07

Ważne, że PowerQuery zwraca uwagę na wielkość liter, więc tutaj musimy odpowiednio wypełnić pole zgodnie z zapisanym przez nas tekstem.

4. Anulujemy przestawienie wszystkich kolumn poza pierwszą

PowerQuery 2 - Łączenie kolumn z danymi 08

5. Usuwamy środkową kolumnę, bo jej nie potrzebujemy

PowerQuery 2 - Łączenie kolumn z danymi 09

6. Dzielimy kolumnę po użytym przez nas ograniczniku

PowerQuery 2 - Łączenie kolumn z danymi 10

7. Odfiltrowujemy po jednej z kolumn wynikowych wiersze puste lub z wartością null. Możemy tak teraz postąpić, gdyż PowerQuery zapisze to jako filtr wartości różne od pustych (null).

PowerQuery 2 - Łączenie kolumn z danymi 11

8. Na koniec zmieniamy nazwy kolumn i wczytujemy dane do Excela.

PowerQuery 2 - Łączenie kolumn z danymi 12

Trzeba zwrócić uwagę, że wartości w pierwszej kolumnie nie są takie jakbyśmy chcieli ponieważ w pierwszym kroku skopiowaliśmy nagłówki bezpośrednio skopiowane z danych, które przekształcamy, a one nie przechowują dodatkowej informacji o produktach. W tym przykładzie powinniśmy zamiast nich wstawić jako nagłówki dane naszych sprzedawców, a następnie odświeżyć tabelę wynikową zapytania PowerQuery.

PowerQuery 2 - Łączenie kolumn z danymi 13

Niestety spowoduje to błąd :(, ale klikając dwukrotnie w oknie zapytań wrócimy do edycji zapytania i będziemy mogli je poprawić. Przy okazji PowerQuery podpowiada nam, gdzie wystąpił błąd.

PowerQuery 2 - Łączenie kolumn z danymi 14

Błędny okazuje się drugi krok ponieważ w pobieranych danych nie istnieje już kolumna Produkt itp. Najprostszym rozwiązaniem jest tutaj usunięcie tego kroku ponieważ mamy proste dane i jego usunięcie nie wpłynie negatywnie na wynik końcowy.

PowerQuery 2 - Łączenie kolumn z danymi 15

Teraz wystarczy ponownie załadować dane zapytania i w naszej tabeli wynikowej zapytania PowerQuery mamy wszystkie istotne dla nas dane.

PowerQuery 2 - Łączenie kolumn z danymi 16

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Excel — Zamiana “angielskich” liczb na “polskie” za pomocą Tekst jako kolumny — Porada #292

W tym tygodniu na szkoleniu, które prowadziłem, został poruszony również wątek zamiany angielskiego zapisu liczb na polski za pomocą polecenia Tekst jako kolumny.
Zacznijmy od tego, ze mamy liczby, gdzie separatorem tysięcy jest przecinek, a część całkowitą od ułamkowej liczby oddziela kropka czasem też się trawi minus na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 01

Liczby te możemy łatwo zamienić na polskie (czy też takiej jakie wynikają z Twoich ustawień regionalnych) za pomocą polecenia Tekst jako kolumny, które znajduje się na karcie Dane. Musimy tylko zaznaczyć kolumnę z liczbami, które chcemy zamienić.
Przez pierwsze dwa kroki przechodzimy szybko upewniając się tylko, że nie jest zaznaczony żaden ogranicznik, który spowodowałby podział liczby na osobne kolumny. Musimy się na chwilę zatrzymać w kroku 3 i kliknąć przycisk Zaawansowane.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 02

W oknie, które się otworzy musimy wybrać takie separatory jakie są w liczbach, które chcemy zmienić, a Excel zamieni je na takie, które wynikają z ustawień regionalnych. Możemy też zaznaczyć checkbox, że znak minus znajduje się na końcu liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 03

Po tym wystarczy zatwierdzić opcje i wkleić liczby tam, gdzie chcesz np.: w kolumnę obok, żeby było widać wcześniej „angielski” zapis i aktualny „polski” zapis liczby.

Porada 292 - Zamiana angielskich liczb na polskie za pomocą Tekst jako kolumny 04

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP

Sumowanie całego wiersza na podstawie pojedynczego kryterium — Porada #291

Dzisiaj chcemy zsumować wartości z całego wiersza na podstawie pojedynczego kryterium. Czyli przykładowo jeśli wybierzemy Tadeusza to chcemy zsumować 3 wartości (cały wiersz).

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 01

Problem jest taki, że SUMA.JEŻELI, z której najczęściej korzystamy, gdy chcemy zsumować wartości po warunku nie zadziała tu poprawnie:

=SUMA.JEŻELI(A3:A9;G1;B3:D9)

Wynika to ze sposobu działania funkcji SUMA.JEŻELI – na 1 komórkę, która spełnia warunek funkcja SUMA.JEŻELI zsumuje 1 komórkę z zakresu podanego do sumowania. Dlatego tutaj musimy skorzystać z innej formuły.
Odpowiednią możliwość daje nam SUMA.ILOCZYNÓW z odpowiednimi obliczeniami w argumencie.

Zaczniemy od tego, że potrzebujemy sprawdzić warunki, czyli prosta operacja porównania A3:A9=G1. Następnie tą wartość musimy (wciąż wewnątrz funkcji) przemnożyć przez cały zakres danych do zsumowania (A3:A9=G1)*B3:D9

Wtedy dla każdej wartości PRAWDA z operacji porównania zostanie przemnożony cały wiersz z drugiego zakresu – po ewaluacji (klawisz F9), będzie to taki wynik w argumencie funkcji

SUMA.ILOCZYNÓW: {0\0\0;193\148\276;0\0\0;0\0\0;0\0\0;0\0\0;0\0\0}

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 03

Funkcja SUMA.ILOCZYNÓW bez problemu sumuje wszystkie wartości z takiej tablicy i uzyskujemy poprawny wynik, czyli sumę wartości z każdego wiersza, dla którego zostanie spełnione kryterium w pierwszej kolumnie.

=SUMA.ILOCZYNÓW((A3:A9=G1)*B3:D9)

Porada 291 - Sumowanie całego wiersza na podstawie pojedynczego kryterium 04

Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP