W tym poście zajmiemy się wyznaczeniem pierwszego i
ostatniego dnia miesiąca za np. trzy miesiące. Temat ten omówimy na
przykładowych danych z rysunku nr 1.
Jest to bardzo proste w Excelu, przynajmniej jeśli chodzi o
wyznaczenie ostatniego dnia miesiąca. Użyjemy tutaj funkcji NR.SER.OST.DN.MIES
(numer seryjny ostatniego dnia miesiąca). W funkcji tej musimy podać dwa
argumenty, pierwszy to data początkowa, a drugi to ilość miesięcy, o które
chcemy się przesunąć (wartość dodatnia dla przesunięcia do przodu, wartość
ujemna dla przesunięcia w tył). Dla naszego przykładu formuła będzie wyglądać
następująco = NR.SER.OST.DN.MIES(A2;3). Jeśli jako drugi argument wpiszemy
wartość 0, to otrzymamy ostatni dzień z tego miesiąca, z którego jest data
początkowa. Zatwierdzamy formułę i kopiujemy na wiersze poniżej. Otrzymamy dane
z rysunku nr 2.
Podsumowując, w funkcji NR.SER.OST.DN.MIES możemy wyznaczyć
datę ostatniego dnia miesiąca za określoną ilość miesięcy dla dodatniego
drugiego argumentu tej funkcji lub dla ujemnego — datę ostatniego dnia miesiąca kilka miesięcy
wstecz. Możemy jej również użyć dla wyznaczenia daty ostatniego dnia aktualnego
miesiąca, wystarczy że jako drugi argument funkcji wpiszemy wartość 0.
W celu uzyskania pierwszego dnia
miesiąca za określoną ilość czasu, musimy użyć tej samej funkcji, mianowicie
NR.SER.OST.DN.MIES. Załóżmy, że chcemy wyznaczyć datę pierwszego dnia miesiąca
od daty 2018-07-15 za trzy miesiące. W funkcji jako drugi argument wpiszemy
wartość 2 (wyznaczymy ostatni dzień miesiąca za 2 miesiące), a następnie dodamy
do niej wartość 1, aby otrzymać pierwszy dzień kolejnego miesiąca (pamiętamy że
daty w Excelu to liczby).
Zapis formuły będzie wyglądał
następująco = NR.SER.OST.DN.MIES(A2;2)+1, zatwierdzamy formułę i otrzymujemy
dane z rysunku nr 3.
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ę tematyką czasu, a dokładnie jak
wyznaczyć datę za dokładnie 3 miesiące. Podstawowy problem będzie polegał na
tym, że miesiące mają różną liczbę dni ( szczególnie luty, który ma różną
liczbę dni w zależności od roku). Kolejnym problemem jest wyznaczenie daty z
końca miesiąca, np. załóżmy że mamy datę 2018-08-31 a za 3 miesiące
otrzymalibyśmy datę 2018-11-31, ale listopad ma tylko 30 dni, dlatego pokarzemy
sposób jak zmienić tą datę, żeby otrzymać prawidłowy wynik Zagadnienie to
omówimy na przykładowych danych z rysunku nr 1.
Prawidłowego wyniku nie uzyskamy przez dodanie do daty
początkowej odpowiedniej ilości dni, ponieważ w jednym przypadku to zadziała, a
w innym nie. Do wyznaczenia daty za 3 miesiące użyjemy funkcji NR.SER.DATY,
czyli numer seryjny daty. W funkcji tej wystarczy podać dwa argumenty, pierwszy
to data początkowa, a drugi ilość miesięcy, o które chcemy się przesunąć, z
założenia w przód (rys. nr 2).
Zatwierdzamy formułę i kopiujemy na wiersze poniżej, w
efekcie otrzymujemy wyniki z rysunku nr 3.
Funkcja ta może zadziałać również w tył, wystarczy w ilości
miesięcy podać wartość ujemną. Dla naszego przykładu dla 3 miesięcy w tył byłby
to zapis =NR.SER.DATY(A2;-3). Podsumowując, jeśli wpiszemy jako drugi argument
funkcji wartość ujemną, to otrzymamy datę wstecz.
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 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.
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.
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).
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.
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)
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)
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.
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.
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ę 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.
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)
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).
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).
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.
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ę operacjami na funtach i uncjach.
Zagadnienie to omówimy na przykładowych danych z rysunku nr 1.
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.
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.
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.
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).
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).
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
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.
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.