Załóżmy, że masz kalendarz i chcesz w nim zaznaczyć dni na które przypada urlop. Kolejne urlopy mamy zapisane jako daty od do.
Ż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
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)
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.
Po przeciągnięciu formuły w bok i dół uzyskamy 1 tam gdzie przypada urlop.
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.
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:
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.
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
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)
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.
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ść.
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
Mamy problem, gdyż nasze dane rozdzielone są na wiele kolumn przechowujących te same informacje.
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.
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
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.
Teraz możemy pobrać dane do zapytania PowerQuery. Korzystając z odpowiedniego polecenia z karty dodatku lub z karty Dane od Excela 2016.
Po wczytaniu danych do PowerQuery musimy wykonać następujące kroki:
1. Musimy przenieść nagłówki do pierwszego wiersza
2. Transponować dane
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.
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ą
5. Usuwamy środkową kolumnę, bo jej nie potrzebujemy
6. Dzielimy kolumnę po użytym przez nas ograniczniku
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).
8. Na koniec zmieniamy nazwy kolumn i wczytujemy dane do Excela.
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.
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.
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.
Teraz wystarczy ponownie załadować dane zapytania i w naszej tabeli wynikowej zapytania PowerQuery mamy wszystkie istotne dla nas dane.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
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.
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.
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.
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.
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
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).
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
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)
Pozdrawiam
Adam Kopeć
Miłośnik Excela
Microsoft MVP
