Excel Jak zaznaczyć tylko pierwszy wiersz spełniający warunek porada 348

W tym poście nauczymy się jak napisać formatowanie warunkowe, które sprawi, że podświetli nam się tylko pierwszy wiersz spełniający nasz warunek. Temat ten omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Przyjmijmy sobie, że dane muszą spełnić warunek wysokości sprzedaży powyżej 6000 zł. Pierwszym krokiem jest napisanie formuły, która sprawdzi nam ten warunek. Będzie ona wyglądała następująco: =$C3>$F$1, co przedstawia rysunek nr 2. Odpowiednio przeciągamy ją na wiersze obok i poniżej.

rys. nr 2 — Porównanie komórek

Tym sposobem sprawdziliśmy, które dane spełniają nadany warunek, czyli Excel zwróci nam wszystkie te wyniki. Nam natomiast zależy tylko na pierwszym wyniku spełniającym nasz warunek. Najprostszym sposobem wyznaczenia tego jest funkcja LICZ.JEŻELI z rozrastającym się zakresem. Zapis formuły będzie wyglądał następująco:

 =LICZ.JEŻELI($C$3:$C3;">"&$F$1)

W funkcji tej musimy określić 2 argumenty. Pierwszy argument to zakres, na jakim ma działać nasza funkcja – początek zakresu blokujemy bezwzględnie, natomiast koniec blokujemy tylko po kolumnach, żeby kolumna nam się nie przesuwała, a wiersze rozrastały się w dół. Drugim argumentem jest kryterium, czyli warunek jaki mają spełniać nasze dane, czyli że wartość ta ma być większa od wartości przechowywanej w komórce F1 (blokujemy bezwzględnie komórkę, ponieważ każdy z wyników ma być porównany tylko do tej komórki). Zatwierdzamy funkcję i przeciągamy na wiersze poniżej. Funkcja ta zwróci nam wartości – ile razy ten warunek został spełniony (rys nr 3).

rys. nr 3 — Działanie funkcji LICZ.JEŻELI

Aby wyznaczyć pierwszy przypadek spełnienia warunku, musimy użyć funkcji ORAZ, czyli dołożyć jeszcze jeden warunek dla funkcji LICZ.JEŻELI. Pierwszym argumentem funkcji ORAZ jest nasza funkcja LICZ.JEŻELI z zapisu powyżej, która zwróci nam wynik prawdziwy dla pojawienia się wartości 1 (ale wynik ten pojawia się 2 razy). Drugi warunek będzie bardzo podobny do pierwszego, z tą różnicą, że przesuniemy zakres danych o jedną komórkę w górę, czyli do C2.

=ORAZ(LICZ.JEŻELI($C$3:$C3;">"&$F$1); LICZ.JEŻELI($C$2:$C2;">"&$F$1)=0)

W wyniku działania tej formuły Excel zwróci nam prawidłowy wynik, czyli pierwsze pojawienie się cyfry 1, jako PRAWDA. Mając gotową, prawidłowo działającą formułę, możemy sformatować nasze dane przy użyciu Formatowania warunkowego.

Kopiujemy formułę w trybie edycji komórki, zaznaczamy zakres do formatowania warunkowego, a następnie z karty Narzędzia główne wybrać Formatowanie warunkowe i potem Nowa reguła (rys. nr 4)

rys. nr 4 — Formatowanie warunkowe

W oknie Nowej reguły formatowania wybieramy typ reguły – Użyj formuły do określenia komórek, które należy sformatować. Następnie wklejamy skopiowaną formułę  i wybieramy przycisk Formatuj, aby wybrać kolor, następnie zatwierdzamy przyciskiem OK. (rys. nr 5)

rys. nr 5 — Okno Nowej reguły formatowania

Otrzymamy dane sformatowane warunkowo, czyli zaznaczone zostaną wyniki spełniające nasz warunek (pierwsze wystąpienie kwoty większej od 6000 zł) – rys. nr 6.

rys. nr 6 — Efekt działania Formatowania warunkowego

Podsumowując poznaliśmy formułę, która liczy ile razy został spełniony warunek od pierwszego wiersza danych i od nagłówka danych. Należy pamiętać, żeby formułę liczącą od nagłówka danych porównać do zera, aby zwróciła nam pierwsze wystąpienie wyniku spełniającego warunek. Powinniśmy również pamiętać o pomalowaniu tego wyniku w formatowaniu warunkowym.


Właśnie dodałem mój kurs o wizualizacji i Dashboardach w Excelu na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Excel Dashboards i wizualizacja danych to:
- Ponad 12 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki wizualizacji
w Excelu i tworzenia Dashboardów.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o Dashboardach:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę wizualizacji danych
i tworzenie Dashboardów w Excelu za pomocą odpowiednio stworzonych tabel przestawnych,
wykresów i innych obiektów Excela.

1. Prezentacja danych na wykresach
2. Wizualizacja danych za pomocą formatowania warunkowego i liczbowe
3. Formanty i VBA przy wizualizacji danych
4. Dashboard podsumowujący sprzedaż za pomocą Tabel Przestawnych
5. Dashboard mapa lotów
6. Budowanie i wykorzystanie dynamicznych zakresów

Wejdź na stronę kursu Excel Dashboards i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy (jest ich 18),
żeby przekonać się czy to kurs dla Ciebie.

Excel — Dlaczego identyczne czasy nie są sobie równe — porada 347

W tym poście zajmiemy się tematem czasu, a konkretnie jak to się dzieje, że dwa z pozoru identyczne czasy nie są sobie równe. Zagadnienie to omówimy na podstawie danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

W trzech pierwszych kolumnach zapisałem czas, ten sam w każdym wierszu. W kolumnie A wpisałem go ręcznie. Kolumna B została uzupełniona na zasadzie przeciągania. Dane w kolumnie C natomiast powstały przez dodawanie, czyli do godziny 10.00 dodałem 5 minut, a następnie tą formułę przeciągnąłem na wiersze poniżej. Następnie sprawdzimy czy poszczególne wiersze z tych kolumn są sobie równe. Teoretycznie Excel pokazuje nam jednakowe wartości czasu, ale zdarza się, że pojawia się błąd. Wynika to z tego, że w Excelu czas jest zapisywany jako liczba ułamkowa, więc przy różnych operacjach może on być inaczej zaokrąglany. Naszym celem jest uzyskanie takich samych wartości przy wpisywaniu ręcznym, jak przy przeciąganiu formuły.

Rozwiązanie tych rozbieżności omówimy sobie na podstawie danych z wiersza 6 (rys. nr 2)

rys. nr 2 — Rozwiązanie problemu na podstawie danych

Aby lepiej zrozumieć temat, musimy zmienić formatowanie na Ogólne w zakładce Narzędzia główne i zwiększyć liczbę miejsc po przecinku. Po takich działaniach widzimy z czego wynika błąd (rys. nr 3).

rys. nr 3 — Różnice w zaokrągleniu liczb

Naszym celem jest znalezienie sposobu, aby te wartości były sobie równe, czyli aby dane otrzymane na zasadzie przeciągania były identyczne z tymi wpisywanymi ręcznie. W tym celu użyjemy funkcji ZAOKR.DO.WIELOKR (zaokrąglanie do wielokrotności). Rozpatrzmy przypadek dla równania C=A, gdzie zaokrąglimy wynik z kolumny C uzyskany przez przeciąganie. Nasza formuła będzie wyglądać następująco:

=ZAOKR.DO.WIELOKR(C2;"0:05")=A2

Zatwierdzamy formułę i przeciągamy na wiersze poniżej. Otrzymamy wszędzie wynik PRAWDA (rys. nr 4).

rys. nr 4 — Funkcja ZAOKR.DO.WIELOKR

Podsumowując rozbieżności w wynikach wpisanych ręcznie a uzyskanych poprzez przeciąganie wynikają z innego zaokrąglenia wyników (precyzji zapisywania liczb zmiennoprzecinkowych/dziesiętnych na komputerze). Aby te wyniki były jednakowe wystarczy użyć funkcji ZAOKR.DO.WIELOKR.


Właśnie dodałem mój kurs o wizualizacji i Dashboardach w Excelu na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Excel Dashboards i wizualizacja danych to:
- Ponad 12 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki wizualizacji
w Excelu i tworzenia Dashboardów.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o Dashboardach:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę wizualizacji danych
i tworzenie Dashboardów w Excelu za pomocą odpowiednio stworzonych tabel przestawnych,
wykresów i innych obiektów Excela.

1. Prezentacja danych na wykresach
2. Wizualizacja danych za pomocą formatowania warunkowego i liczbowe
3. Formanty i VBA przy wizualizacji danych
4. Dashboard podsumowujący sprzedaż za pomocą Tabel Przestawnych
5. Dashboard mapa lotów
6. Budowanie i wykorzystanie dynamicznych zakresów

Wejdź na stronę kursu Excel Dashboards i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy (jest ich 18),
żeby przekonać się czy to kurs dla Ciebie.

Excel — Operacje na funtach i uncjach — porada 345

W tym poście zajmiemy się operacjami na funtach i uncjach. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Funty i uncje to jednostki wagi i czasem musimy wykonać na nich działania matematyczne, np. dodawanie czy odejmowanie. Problem w tych działaniach polega na ich przeliczeniu, mianowicie funt równa się 16 uncji. Rzadko się zdarza, że ktoś zapisze te wartości w postaci np. 26 funtów i 11 uncji (czyli 26 i 11/16 funta).

Pierwsze 2 kolumny z rysunku nr 1 przedstawiają sposób zapisu funtów i uncji proponowane przez Billa Szysza, który rozwiązał problem przeliczania funtów i uncji.

Gdybyśmy ustawili Formatowanie liczbowe ogólne, dane z pierwszych 2 kolumn wyglądały by jak na rysunku nr 2, czyli w komórce A3 mielibyśmy 25 funtów i 27 uncji.

rys. nr 2 — Zapis funtów i uncji w systemie dziesiętnym

Bill Szysz zaproponował rozwiązanie dla wartości, które nie są standardowym systemem dziesiętnym. Aby program matematyczny sobie poradził z obliczeniami, musimy funty i uncje zapisać w postaci ułamków zwykłych a nie dziesiętnych. Rozwiązanie to polega na użyciu funkcji CENA.DZIEŚ (cena dziesiętna). Ma ona niewiele wspólnego z ceną, ale jej działanie polega na zmianie ułamka. Aby dobrze zrozumieć temat musimy zmienić kolumnie A i B formatowanie. Możemy to zrobić przy użyciu skrótu klawiszowego Ctrl+1. Otworzy nam się okno Formatowanie komórek, w którym zmienimy Kategorie formatowania liczbowego na ułamkowe i Typ na Do trzech cyfr co przedstawia rysunek nr 3.

rys. nr 3 — Formatowanie komórek na ułamek szesnastkowy

W kolumnach A i B mamy postać dziesiętną, ale z zapisem jako ułamek dziesiętny, natomiast kolumny C i D przedstawiają ilość uncji w funcie. Do tego działania idealnie nadaje się funkcja CENA.DZIES, jej działanie polega na zmianie sytemu dziesiętnego na szesnastkowy (rys. nr 4). Funkcja ta dodatkowo wyciąga wartości całkowite, tzn. w naszym przypadku mieliśmy wartość 25 i 27/100 w momencie zmiany otrzymalibyśmy 25 i 27/16, więc funkcja automatycznie zabiera wartość 16/16 z ułamka i dodaje ją do wartości całkowitych.

rys. nr 4 — Funkcja CENA.DZIES

Mając dane w postaci ułamków każda formuła matematyczna zadziała, ale wynik również otrzymamy w takich samych ułamkach.

Kolejnym zagadnieniem jest zmiana z wartości zapisanych w postaci ułamków w systemie szesnastkowym na dziesiętne. Skorzystamy wtedy z funkcji CENA.UŁAM, która działa odwrotnie do funkcji CENA.DZIES (rys. nr 5).

rys, nr 5 — Funkcja CENA.UŁAM

Funkcja CENA.UŁAM przelicza dane na wartości dziesiętne. Kiedy przy użyciu skrótu klawiszowego Ctrl+1 zmienimy sobie formatowanie liczbowe na ułamkowe w systemie na 100 to Excel przekształci nam wyniki do postaci w systemie dziesiętnym. Problem z tym sposobem liczenia jest taki, że jakaś minimalna część nam znika. W kolumnach E i F powinniśmy otrzymać taki sam wynik, bo teoretycznie wykonaliśmy taką samą operację (rys. nr 6).

rys. nr 6 — Działania matematyczne na ułamkach

Przy wykonaniu obliczeń na cenach ułamkowych otrzymaliśmy wartość 24 funty, a przy działaniach z ułamkami dziesiętnymi otrzymaliśmy 23 funty i 16/100 uncji, więc teoretycznie Excel powinien nam przekształcić i z tych 16 uncji zrobić 1 funt. Aby Excel wykonał nam to przeliczenie musimy użyć funkcji ZAOKR.DO.WIELOKR (zaokrąglij do wielokrotności) — rys. nr 7

rys. nr 7 — Funkcja ZAOKR.DO.WIELOKR

Podsumowując mamy wygodny i łatwy sposób przekształcania wartości z systemu dziesiętnego na ułamkowy (np. szesnastkowy jak przy funtach i uncjach). Mamy wtedy pewność, że działania matematyczne na takich danych będą poprawne.

Pamiętajmy, aby zwrócić uwagę czy nasz ułamek jest jedno‐, dwu‐ czy trzy‐cyfrowy, bo mogą wychodzić inne zaokrąglenia. Funkcje te są przydatne w wielu przeliczeniach, np. jedna kopa to 6 tuzinów.


Właśnie dodałem mój kurs o wizualizacji i Dashboardach w Excelu na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Excel Dashboards i wizualizacja danych to:
- Ponad 12 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki wizualizacji
w Excelu i tworzenia Dashboardów.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o Dashboardach:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę wizualizacji danych
i tworzenie Dashboardów w Excelu za pomocą odpowiednio stworzonych tabel przestawnych,
wykresów i innych obiektów Excela.

1. Prezentacja danych na wykresach
2. Wizualizacja danych za pomocą formatowania warunkowego i liczbowe
3. Formanty i VBA przy wizualizacji danych
4. Dashboard podsumowujący sprzedaż za pomocą Tabel Przestawnych
5. Dashboard mapa lotów
6. Budowanie i wykorzystanie dynamicznych zakresów

Wejdź na stronę kursu Excel Dashboards i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy (jest ich 18),
żeby przekonać się czy to kurs dla Ciebie.

Excel — Konwertowanie kilometrów do mil i inne przypadki funkcja KONWERTUJ — porada 346

W tej poradzie omówimy konwertowanie różnych jednostek długości, wagi, mocy czy pojemności przy użyciu funkcji KONWERTUJ. Poruszymy temat przejścia na inne jednostki metryczne np. z kilometrów na mile. Działanie funkcji KONWERTUJ omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Pierwszy omówiony przypadek dotyczy zmiany kilometrów na mile. Do tego zadania użyjemy funkcji KONWERTUJ. Jako pierwszy argument funkcji podajemy liczbę, którą chcemy przekształcić. Drugi argument to jednostka, w jakiej są podane nasze dane wyjściowe – rozwinie nam się lista różnych jednostek (jest ich bardzo dużo), w której jeśli nie możemy znaleźć naszej jednostki, a znamy jej skrót możemy po prostu wpisać ręcznie np.: "km" (rys. nr 2).

rys. nr 2 — Funkcja KONWERTUJ

Trzeci argument to jednostka docelowa, czyli ta na którą chcemy przekształcić nasze dane – u nas będą to mile "mi". Kiedy podamy jednostkę wejściową, czyli dla naszego przypadku kilometry, to przy wyborze jednostki wyjściowej w rozwijanej liście pojawią się tylko jednostki długości, z których wybieramy milę lądową (rys. nr 3). Nasza formuła będzie wyglądać następująco:

=KONWERTUJ(A2; "km";"mi")  

Zatwierdzamy i otrzymujemy wynik.

rys. nr 3 — Funkcja KONWERTUJ dla jednostek długości

Drugim przypadkiem jest wykorzystanie funkcji KONWERTUJ do zmiany jednostek masy, czyli zamienimy funty na kilogramy. W formule wpisujemy odwołanie do komórki, która chcemy przeliczyć, następnie jednostka wejściowa (ta, w której podane są nasze dane wejściowe) to funty, czyli oznaczenie "lbm". Na koniec podajemy jednostkę wyjściową, mianowicie kilogramy. Ciekawostką jest, że w liście rozwijanej nie znajdziemy jednostki kg, będą tylko g, ponieważ kg to wielokrotność g. Podsumowując w Excelu literka k przed wartością masy wyrażonej w gramach oznacza, że mnożymy ją razy 1000 (M dla 1000000). Dlatego w naszej formule musimy ręcznie wpisać jednostkę "kg". Nasza formuła będzie wyglądać następująco:

=KONWERTUJ(A5; "lbm";"kg")

co widać na rysunku nr 4.  Zatwierdzamy formułę i otrzymujemy wynik

rys. nr 4 — Funkcja KONWERTUJ dla jednostek masy

Ciekawostka dla fanów Gwiezdnych wojen 🙂 Zamienimy 1 parsek na kilometry. Skorzystamy z funkcji KONWERTUJ. W formule odwołujemy się do danych, które chcemy przeliczyć , następnie podajemy jednostkę wejściową, czyli "pc" i jednostkę wyjściową (długości) – u nas to "km". Zatwierdzamy i otrzymujemy wynik. Nasza formuła będzie wyglądać tak:

=KONWERTUJ(A8; "pc";"km")

Możemy również użyć większych jednostek, czyli wystarczy przed metrem wpisać symbol jednostki: M dla milion, G dla giga czyli mnożymy metr razy miliard lub T dla terra, czyli mnożymy razy bilion).

Kolejnym przykładem będzie użycie funkcji KONWERTUJ dla jednostek pojemności dysku. Chcemy przeliczyć ile bajtów mieści się w gigabajcie. Jeśli zadziałamy analogicznie do przykładów powyżej i użyjemy przy bajcie literki G, to Excel zwróci nam nieprawidłową wartość. Literka G oznacza, że mnożymy razy miliard, a dla jednostek pojemności dysku nie jest to prawdą. Na stronie MS Office możemy znaleźć informację, że przy jednostkach pojemności dysku używa się innych przedrostków w przeliczeniach (rys. nr 5).

rys. nr 5 — Inne oznaczenia przedrostków dla jednostek objętości dysku

Zapis naszej funkcji będzie zatem wyglądał następująco:

=KONWERTUJ(A11; "Gibyte";"byte"),

zatwierdzamy polecenie i otrzymujemy prawidłowy wynik.

Podsumowując należy pamiętać, że w funkcji KONWERTUJ mamy jednostki podstawowe i jeśli chcemy większe jednostki, musimy użyć przedrostka. Ponadto dla jednostek pojemności dysku przedrostki te różnią się od używanych standardowo. Symbole przedrostków możemy sprawdzić na stronie https://support.office.com/pl-pl/article/KONWERTUJ-funkcja-d785bef1-808e-4aac-bdcd-666c810f9af2 dla funkcji KONWERTUJ.


Właśnie dodałem mój kurs o wizualizacji i Dashboardach w Excelu na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Excel Dashboards i wizualizacja danych to:
- Ponad 12 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki wizualizacji
w Excelu i tworzenia Dashboardów.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o Dashboardach:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę wizualizacji danych
i tworzenie Dashboardów w Excelu za pomocą odpowiednio stworzonych tabel przestawnych,
wykresów i innych obiektów Excela.

1. Prezentacja danych na wykresach
2. Wizualizacja danych za pomocą formatowania warunkowego i liczbowe
3. Formanty i VBA przy wizualizacji danych
4. Dashboard podsumowujący sprzedaż za pomocą Tabel Przestawnych
5. Dashboard mapa lotów
6. Budowanie i wykorzystanie dynamicznych zakresów

Wejdź na stronę kursu Excel Dashboards i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy (jest ich 18),
żeby przekonać się czy to kurs dla Ciebie.

Excel — Numer tygodnia w miesiącu różne systemy — porada 344

W tym poście omówimy różne sposoby wyznaczania numeru tygodnia w miesiącu. Jest to czwarty i zarazem ostatni wpis z cyklu dotyczącego numerowania tygodni. Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.

rys. nr 1 — Przykładowe dane

Formuły do tego wpisu zaczerpnięte zostały ze strony https://stackoverflow.com/questions/20620712/excel-formula-to-get-week-number-in-month-having-Monday w języku angielskim.

Omówimy sobie pierwszy sposób działania. Pamiętamy, że tydzień ma 7 dni. Jeśli nasz tydzień zaczyna się w poniedziałek w poprzednim miesiącu to będzie się ciągnął do niedzieli w bieżącym miesiącu. Natomiast dopiero w sytuacji, kiedy 1 dnia miesiąca wypada czwartek, to dopiero wtedy możemy mówić, że jest to pierwszy tydzień tego miesiąca. I dopiero od tego tygodnia jest kontynuowana numeracja dla tego miesiąca. W celu wyznaczenia numeru tygodnia miesiąca skorzystamy z kilku funkcji takich jak: ZAOKR.DO.CAŁK, DZIEŃ oraz DZIEŃ.TYG. Nasza formuła (w komórce D13) będzie wyglądać następująco:

= ZAOKR.DO.CAŁK((6+DZIEŃ($B13+D$4-DZIEŃ.TYG(&B13-1)))/7)

co przedstawia rysunek nr 2

rys. nr 2 — Obliczenie numeru tygodnia w miesiącu (pierwszy sposób)

Zacznijmy wyjaśnienia od funkcji DZIEŃ. Odwołuje się ona do naszej daty ($B13) i dodaje wartość korekty ze względu na to, jaki dzień przyjęliśmy sobie za początek tygodnia (D$4). Funkcja DZIEŃ z założenia zwraca numer dnia w miesiącu. Od naszej daty z uwzględnieniem korekty odejmujemy dzień ze standardowej numeracji dni tygodnia pomniejszony o wartość 1. Musimy pamiętać o dodaniu wartości 6. Po tych wszystkich korektach otrzymujemy wartość liczbową, która podzielona przez 7 (ilość dni w tygodniu) i zaokrąglona do wartości całkowitych zwróci nam prawidłowy numer tygodnia w miesiącu.

Spróbujmy oszacować formułę krok po kroku. Wybieramy z zakładki Formuły polecenie Szacuj formułę (rys. nr 3).

rys. nr 3 — polecenie Szacuj formułę

Otworzy nam się okno Szacowania formuły. Po każdorazowym kliknięciu przycisku Szacuj, Excel zwróci nam wartości poszczególnych części formuły (rys. nr 4). Przyjęliśmy system, gdzie poniedziałek ma wartość 1, a niedziela ma wartość 7. Z formuły DZIEŃ.TYG otrzymujemy wartość 4, czyli czwartek. Dzięki naszym działaniom w funkcji DZIEŃ cofamy się do poniedziałku z danego tygodnia – otrzymamy liczbę 29 (2015–12-29), do której musimy dodać 6. Wyszła nam ilość dni 35, które musimy podzielić na ilość dni w tygodniu. Ostatecznie otrzymujemy numer tygodnia w miesiącu o wartości 5.

rys. nr 4 — Okno Szacowania formuły

Funkcja ZAOKR.DO.CAŁK. nie zaokrągla typowo, ale ucina wartość po przecinku. Nie bierze jej w ogóle pod uwagę. Drugi system wyznaczenia numeru tygodnia w miesiącu omówimy na przykładowych danych z rysunku na 5. Nowy tydzień będzie rozpoczynany od dnia, który sobie przyjmiemy za początek tygodnia.

rys. nr 5 — Przykładowe dane

W 1 przypadku (kolumna Tydzień miesiąca 01) poniedziałek zaczyna tydzień, ale jest jeszcze w poprzednim miesiącu, więc kontynuujemy numerację z zeszłego miesiąca. Analogicznie dla przypadków 2 i 3. Kolejny przypadek zakłada początek tygodnia w czwartek i jest on jednocześnie 1 dniem danego miesiąca, wtedy rozpoczynam numerację nowego miesiąca (rys. nr 5).

Sposób działania jest bardzo podobny do pierwszego przypadku. Różnica polega na drugim argumencie funkcji DZIEŃ.TYG. Zapis naszej formuły (w komórce K13) będzie wyglądał następująco:

= ZAOKR.DO.CAŁK((6+DZIEŃ($B13-DZIEŃ.TYG(&B13-K$4;3)))/7)

W formule tej odwołujemy się do naszej daty $B13 i od niej odejmujemy tyle dni, ile wynika z naszej daty minus korekta. Wartość korekty zależy od tego, który dzień przyjmiemy sobie za początek tygodnia (u nas drugi argument funkcji DZIEŃ.TYG wynosi 3 czyli poniedziałek ma numer 0, a niedziela to 6). Do otrzymanej wartości dodajemy 6, a następnie dzielimy przez 7 (analogicznie jak wyżej) – rys. nr 6.

rys. nr 6 — Obliczenie numeru tygodnia w miesiącu (drugi sposób)

Pod filmem Leili Gharani, na który trafiłem w sieci, znalazłem komentarz, który pokazywał jak numerować tygodnie miesiąca przy założeniu, że 1 dzień miesiąca to zawsze początek pierwszego tygodnia tego miesiąca. W zależności czy nasz tydzień będzie się zaczynał od poniedziałku, wtorku czy środy to te pierwsze tygodnie mogą być krótsze lub dłuższe, co widać na rysunku nr 7. Przykładowo, jeśli 1 stycznia wypada w czwartek (kolumna Tydzień miesiąca 005) ,a my przyjmujemy piątek za początek nowego tygodnia to wtedy pierwszy tydzień miesiąca będzie trwał tylko 1 dzień.

rys. nr 6 — Przykładowe dane

Formuła do wyznaczenia numeru tygodnia w miesiącu w omówionym przypadku (dla komórki R13) będzie wyglądać następująco:

=NUM.TYG($B13;R$4)-NUM.TYG(NR.SER.OST.DN.MIES($B13;-1)+1;R$4)+1

W pierwszej funkcji NUM.TYG korektą jest drugi argument, czyli wartość w zależności od dnia przyjętego za początek tygodnia w pierwszym systemie (rys. nr 7)

rys. nr 7 — Obliczenie numeru tygodnia w miesiącu (trzeci sposób)

Następnie od numeru dnia tygodnia, który przypada na naszą datę musimy odjąć numer tygodnia, który wyznaczamy z numeru seryjnego ostatniego dnia miesiąca. W funkcji NR.SER.OST.DN.MIES drugi argument jest ujemny, oznacza to, że cofamy się o miesiąc , czyli otrzymujemy datę ostatniego dnia miesiąca poprzedzającego nasz miesiąc. Następnie dodajemy wartość 1, czyli przechodzimy na 1 dzień naszego miesiąca (przedstawia to zapis NR.SER.OST.DN.MIES($B13;-1)+1, z którego otrzymujemy pierwszy dzień danego miesiąca). Drugi argument to po raz kolejny numer korekty, czyli systemu w którym przyjmujemy sobie dzień początku tygodnia. Należy pamiętać, aby na koniec dodać do wyniku wartość 1.

Wynik poszczególnych części formuły możemy sobie podejrzeć używając skrótu klawiszowego F9.


Właśnie dodałem mój kurs o wizualizacji i Dashboardach w Excelu na Udemy.com.
W związku z tym, możesz dostać ten kurs w promocyjnej Cenie Na Start za zaledwie 34,99 PLN.
To najniższa cena jaką mogę ustawić na platformie edukacyjnej Udemy!

Kurs Excel Dashboards i wizualizacja danych to:
- Ponad 12 godziny nagrań wideo, które krok po kroku wprowadzają Cię w tajniki wizualizacji
w Excelu i tworzenia Dashboardów.
- Pliki do pracy razem z filmami.
- Dożywotni dostęp.
- Elektroniczny certyfikat ukończenia

Spis treści kursu o Dashboardach:

Kurs jest podzielony na 6 rozdziałów, które pozwolą Ci wejść w tematykę wizualizacji danych
i tworzenie Dashboardów w Excelu za pomocą odpowiednio stworzonych tabel przestawnych,
wykresów i innych obiektów Excela.

1. Prezentacja danych na wykresach
2. Wizualizacja danych za pomocą formatowania warunkowego i liczbowe
3. Formanty i VBA przy wizualizacji danych
4. Dashboard podsumowujący sprzedaż za pomocą Tabel Przestawnych
5. Dashboard mapa lotów
6. Budowanie i wykorzystanie dynamicznych zakresów

Wejdź na stronę kursu Excel Dashboards i zobacz szczegóły kursu
oraz udostępnione do podglądu filmy (jest ich 18),
żeby przekonać się czy to kurs dla Ciebie.