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.
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 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.
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:
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:
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:
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.
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 tym
poście nauczymy się jak na podstawie roku i numeru tygodnia w roku, wyznaczyć
początek i koniec tygodnia. Zagadnienie to omówimy na przykładowych danych z
rysunku nr 1.
Są różne sposoby wyznaczania numeru tygodnia w roku, dlatego
w zależności jaki system liczenia przyjmiemy, potrzebujemy do obliczeń różnych
formuł. Chcemy wyznaczyć początek tygodnia w systemie drugim, w tym celu
użyjemy funkcji DATA i DZIEŃ.TYG. Nasza formuła będzie wyglądać następująco:
=DATA(A2;1;-2)-DZIEŃ.TYG(DATA(A2;1;3))
W funkcji DATA musimy określić sobie 3 argumenty: Rok czyli u
nas będzie to odwołanie do komórki A2, następnie Miesiąc to wartość 1 dla
stycznia oraz Dzień, czyli wartość minus dwa. Wartość ujemna oznacza, że od
pierwszego dnia w roku (2015–01-01) cofamy się o 2 dni (tak naprawdę cofamy się
o 3 dni, ale pamiętajmy, że przyjęliśmy system drugi obliczeń, czyli dla
poniedziałku mamy korektę o wartości 0). Od tego odejmujemy datę 2015-01-03 w
następujący sposób: użyjemy funkcji DZIEŃ.TYG dla roku 2015, miesiąca stycznia
oraz dnia 3. Formuła ta zapewni nam cofnięcie do początku tygodnia, ale
tygodnia jeszcze wcześniej (do pełnego tygodnia, czyli do 2014-12-22) tak więc do naszej formuły musimy
dodać 7 dni (u nas B2*7). Formuła wtedy będzie wyglądać następująco:
=DATA(A2;1;-2)-DZIEŃ.TYG(DATA(A2;1;3))+B2*7
Otrzymaliśmy w ten sposób datę początku tygodnia dla systemu drugiego, kiedy czwartek wypada 2015-01-01 (czyli pierwszy tydzień roku zawierający pierwszy czwartek stycznia) rys. nr 2
rys. nr 2 — Obliczenie początku tygodnia w systemie 2
Aby wyznaczyć koniec tygodnia wystarczy do formuły omówionej
powyżej dodać 6 dni, czyli zapis formuły będzie wyglądać następująco =E2+6
Omówmy sobie teraz przypadek, kiedy tydzień zawierający 1
stycznia jest pierwszym tygodniem roku oraz zakładamy, że tydzień rozpoczyna
się w poniedziałek. Musimy w tej sytuacji użyć inne formuły (rys. nr 3)
rys. nr 3 — Obliczenie początku tygodnia dla założenia, że tydzień zawierający 1 stycznia jest 1‑szym tygodniem roku
Aby zrozumieć formułę powyżej, musimy wiedzieć, że chcemy się
cofnąć do wcześniejszego poniedziałku, czyli od 1 stycznia danego roku
(DATA($A$2;1;1)) cofamy się o ilość dni, jaka nam wypada z daty (DZIEŃ.TYG(DATA($A$2;1;1);11).
Liczba 11 (ostatni argument funkcji DZIEŃ.TYG) w formule oznacza przyjęty
system, który zakłada że pierwszy dzień tygodnia to poniedziałek (poniedziałek
oznaczony jest cyfrą 1, a niedziela cyfrą 7). Z tej części formuły otrzymujemy
niedzielę 2014-12-28, a więc żeby otrzymać poniedziałek musimy dodać 1 oraz
dodać ilość dni tygodnia odpowiednio przemnożone. Analogicznie, aby z początku
tygodnia wyznaczyć koniec tygodnia wystarczy do naszego wyniku (formuły z rys.
3) dodać 6 dni.
Podobnie rozwiążemy przypadek, kiedy nasz tydzień będzie się
rozpoczynał w czwartek, wystarczy zmienić w formule DZIEŃ.TYG drugi argument na
system, w którym to czwartek jest traktowany jako pierwszy dzień tygodnia
(czwartek oznaczony cyfrą 1, a środa cyfrą 7 – zamieniamy wartość 11 na 14).
Wtedy nasza formuła będzie wyglądać następująco:
Ostatni dzień tygodnia wyznaczamy jak wyżej, czyli do wyniku z powyższej formuły dodajemy 6 dni (rys. nr 4).
rys. nr 4 — Obliczenie początku tygodnia dla założenia, że tydzień rozpoczyna się w czwartek
Ostatni omówiony sposób dotyczy sytuacji, kiedy 1 stycznia
zawsze rozpoczyna pierwszy tydzień roku (tak jak w systemie pierwszym funkcji
NUM.TYG), a my zakładamy początek tygodnia w poniedziałek. Formuła będzie
wyglądać następująco:
Obliczamy to za pomocą funkcji MAX dla różnych argumentów. Drugim argumentem jest nic innego jak funkcja, której użyliśmy do obliczenia początku tygodnia w jednym z przykładów powyżej. Mamy ustalone zapisem MAX(DATA($A$2;1;1), że 1 stycznia to jest początek roku, że nie może to być data wcześniej (rys. nr 5)
rys. nr 5 — Obliczenie początku tygodnia za pomocą funkcji MAX
Koniec tygodnia obliczymy wtedy korzystając z funkcji MIN.
Musimy sobie założyć minimalną datę, możemy to zrobić dodając do roku 1,
otrzymamy wtedy rok 2016, natomiast kiedy jako trzeci argument podamy 0, cofniemy
się o jeden dzień do tyłu do dnia 2015-12-31. Łatwiejszym sposobem byłoby
podanie po prostu w formule miesiąca 12 i dnia 31 – zapis by wyglądał tak: DATA($A$2;12;31)
Zapis funkcji będzie wyglądał następująco (rys. nr 6)
rys. nr 6 — Obliczenie początku tygodnia za pomocą funkcji MIN
Jeśli chcielibyśmy aby początek tygodnia był w inny dzień to
w podanych formułach wystarczy zmienić drugi argument w funkcji DZIEŃ.TYG. Na
przykład dla początku w środę byłaby to wartość 13 (rys. nr 7)
rys. nr 7 — Systemy przyjmowania początku i końca tygodnia
Podsumowując jest to bardzo trudne zagadnienie i ma wiele
wariantów rozwiązania 😛
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 tym poście zajmiemy się zagadnieniem wyznaczania początku i
końca tygodnia (pierwszego i ostatniego dnia) na podstawie daty. W Polsce
ogólnie przyjęte jest, że tydzień zaczyna się od poniedziałku, natomiast w
innym krajach może się zaczynać od pozostałych dni tygodnia. Temat ten omówimy
na przykładowych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Omówimy
działanie funkcji DZIEŃ.TYG, w której jako pierwszy argument podajemy datę, a
drugi to zwracany typ, czyli sposób w jaki funkcja będzie numerować
poszczególne dni tygodnia. Innymi słowy jaki dzień przyjmiemy za początek, a
jaki za koniec tygodnia (rys. nr 2)
rys. nr 2 — Drugi argument funkcji DZIEŃ.TYG
W systemie pierwszym przyjmujemy, że pierwszym dniem tygodnia
jest niedziela (numer 1), a ostatnim sobota (numer 7). Drugi system (stosowany
w Polsce) zakłada, że pierwszy dzień tygodnia to poniedziałek (numer 1), a
ostatni to niedziela (numer 7). Trzeci system jest praktycznie taki sam jak
drugi z tą różnicą, że poniedziałek oznaczony jest cyfrą 0, a niedziela cyfrą
6. Systemy od 11 do 17 działają analogicznie, różnią się tylko tym, który dzień
jest pierwszy (oznaczony cyfrą 1), a który ostatni (oznaczony cyfrą 7). Systemy
te pokazane zostały na rysunku nr 3.
rys. nr 3 — Systemy funkcji (jaki dzień tygodnia przyjmiemy za początek a jaki za koniec tygodnia)
W naszym przykładzie wybierzemy system nr 3, czyli tydzień
liczony od poniedziałku (cyfra 0) do niedzieli (cyfra 6). Zapis naszej formuły
będzie wyglądał następująco:
=DZIEŃ.TYG([@Data];3)
Otrzymamy wyniki zamieszczone na rysunku nr 4 w kolumnie Koniec tygodnia.7. Musimy pamiętać o zmianie formatowania na ogólne formatowania liczbowe.
rys. nr 4 — Wynik działania funkcji DZIEŃ.TYG
Teraz zajmiemy się sposobem wyznaczenia początku i końca
tygodnia na podstawie aktualnej daty. Zacznijmy od przypadku, że chcemy, aby
początek tygodnia był w poniedziałek. Aby to osiągnąć, musimy od aktualnej daty
odjąć numer dnia tygodnia, który nam zwróci funkcja DZIEŃ.TYG, gdy w jej drugi
argument wpiszemy wartość 3. Wtedy poniedziałek ma numer 0, a niedziela numer 6.
Nasza formuła będzie wyglądać następująco:
=$A3-DZIEŃ.TYG($A3;3)
Problem pojawia się, kiedy chcemy, żeby początek tygodnia
przypadał w inny dzień niż poniedziałek. Trzeba wtedy przeprowadzić korektę,
mianowicie w formule funkcji DZIEŃ.TYG odjąć od aktualnej daty wartość tej
korekty. W pierwszym wierszu wpisaliśmy wartość korekty, w zależności od tego,
który dzień przyjmiemy jako początek tygodnia, np. dla poniedziałku korekta
wynosi 0, dla wtorku – 1, dla środy – 2, itd. W takim przypadku formuła będzie
wyglądać następująco:
=$A3-DZIEŃ.TYG($A3‑Q$1;3)
(rys. nr 5)
rys. nr 5 — Funkcja DZIEŃ.TYG dla poniedziałku jako pierwszy dzień tygodnia
Aby uzyskać ostatni dzień tygodnia (koniec tygodnia), wystarczy do początku tygodnia dodać 6 dni, czyli nasza formuła powinna wyglądać tak:
=$A3-DZIEŃ.TYG($A3‑Q$1;3)+6
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 tym poście omówimy działanie funkcji NUM.TYG, czyli sposobach
jak na podstawie numeru tygodnia w roku znaleźć datę pierwszego i ostatniego
dnia tego tygodnia. Pierwszym etapem będzie omówienie jak wyznaczyć numer
tygodnia w roku na podstawie daty. W kolejnych postach będziemy omawiać jak
wyznaczamy początek i koniec tygodnia, konkretną datę z tygodnia oraz będziemy
mówić o tygodniach w miesiącu. Od porady nr 341 do porady 344 będziemy się
zajmować tematami powiązanymi ze sobą. Natomiast na koniec powiemy sobie jak
podobne funkcjonalności działają w Power Query.
Działanie
funkcji NUM.TYG omówimy na podstawie przygotowanych danych z rysunku nr 1.
rys. nr 1 — Przykładowe dane
Załóżmy, że mamy podaną datę i na jej podstawie chcemy
znaleźć numer tygodnia w roku. Istnieją 2 systemy. Pierwszy — łatwiejszy
zakłada, że pierwszy stycznia to zawsze pierwszy tydzień roku. Następnie
przyjmujemy sobie jakiś dzień, który ma być początkiem nowego tygodnia.
Jeśli przyjmiemy sobie za początek tygodnia piątek, to 2015-01-02
będzie już traktowane jako drugi tydzień roku (rys. nr 2). Zatem w tym systemie
pierwszy tydzień stycznia będzie miał tylko jeden dzień.
rys. nr 2 — Pierwszy system wyznaczania numeru tygodnia w roku
Podsumowując w tym systemie pierwszy dzień stycznia to
jednocześnie pierwszy tydzień roku, a kolejne tygodnie ustalają się w
zależności od tego, jaki dzień przyjmiemy sobie za początek tygodnia.
Drugi
system, jest nieco trudniejszy. Zakłada, że pierwszy tydzień roku to cały
tydzień (od poniedziałku do niedzieli), w którym wystąpił pierwszy czwartek stycznia.
W naszym przykładzie 2015-01-01 to czwartek, więc pierwszy tydzień roku
obejmuje daty między 2014-12-29 Pn. a 2015-01-04 Nd. Podsumowując pierwszy
tydzień roku 2015 zaczyna się 2014-12-29 (rys. nr 3)
rys. nr 3 — Drugi system wyznaczania numeru tygodnia w roku (pierwszy czwartek 2015-01-01)
Może wystąpić również sytuacja odwrotna, czyli że czwartek
przypada na 2015-12-31, więc ten tydzień nie może być pierwszy tygodniem roku
2016. Pierwszy tydzień będzie wtedy zaczynał się od poniedziałku 2016-01-04,
natomiast 53 tydzień poprzedniego roku będzie trwał aż do 2016-01-03 (rys. nr 4)
rys. nr 4 — Drugi system wyznaczania numeru tygodnia w roku (pierwszy czwartek 2016-01-07)
Kiedy wiemy już, jak działają te systemy, to wykorzystanie
funkcji NUM.TYG (numer tygodnia) jest bardzo proste. W formule jako pierwszy
argument podajemy datę, a jako drugi kod, który dokładnie określa z jakiego
systemu korzystamy – rys. nr 5
rys. nr 5 Funkcja NUM.TYG
Cyfry 1 i 2 to podstawowe wersje systemów omówionych powyżej,
a pozostałe liczby to wariacje pierwszego systemu.
My przyjmiemy sobie, że chcemy wyznaczyć tydzień roku dla
daty 25.12.2014 oraz przyjmijmy sobie, że początkiem nowego tygodnia ma być
wtorek czyli liczba 12. Formuła funkcji będzie wyglądać następująco
=NUM.TYG(Tabela1[@Data];12)
Otrzymamy wyniki przedstawione na rysunku nr 6 – kolumna Tydzień roku.008.
rys. nr 6 — Działanie funkcji NUM.TYG w pierwszym systemie
W przypadku, gdybyśmy chcieli skorzystać z drugiego systemu w formułę jako pierwszy argument wstawiamy datę 25.12.2014, następnie jako drugi argument wybieramy 21 – odpowiada systemowi drugiemu, czyli zaczynamy tydzień od poniedziałku. Formuła będzie wyglądać następująco =NUM.TYG(Tabela1[@Data];21)
Otrzymamy wtedy wyniki zamieszczone na rysunku nr 8 – kolumna Tydzień roku.009
rys. nr 7 — Działanie fukcji NUM.TYG w drugim systemie
Podsumowując w pierwszym systemie mamy często ucięte tygodnie na przełomie roku (53-ci tydzień ma tylko 2 dni, a 1‑szy tydzień ma 5 dni – rys. nr 6, kolumna Tydzień roku.008), natomiast w drugim systemie otrzymujemy pełne tygodnie.
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.
