W tym poście poznamy funkcje Excela związane z liczbami parzystymi i nieparzystymi.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
W pierwszej kolejności będziemy chcieli sprawdzić, czy podana liczna jest parzysta lub nieparzysta. Wykorzystamy tutaj funkcję CZY.PARZYSTE. Argumentem funkcji jest liczba, czyli wartość jaką chcemy sprawdzić. Zapis formuły powinien wyglądać następująco:
=CZY.PARZYSTE(A2)
Po zatwierdzeniu formuły i skopiowaniu na komórki poniżej otrzymamy dane przedstawione na rys. nr 2. Istotne jest, że funkcje CZY.PARZYSTE i CZY.NIEPARZYSTE nie zwracają uwagi na końcówkę liczby, czyli wartość po przecinku.
Analogicznie, kiedy użyjemy funkcji CZY.NIEPARZYSTE, zapis formuły powinien wyglądać następująco:
=CZY.NIEPARZYSTE(A2)
Funkcja ta działa tak jak poprzednia, czyli ucina część dziesiętną po przecinku. Co najważniejsze nie zaokrągla prawidłowo liczby. Po zatwierdzeniu formuły i skopiowaniu jej na komórki w dół otrzymamy dane przedstawione na rys. nr 3.
Za pomocą powyższych funkcji możemy sprawdzać, czy podane liczby są parzyste czy nieparzyste. Możemy też podane liczby zaokrąglać do liczb parzystych lub nieparzystych. Do parzystych liczb zaokrąglimy dane za pomocą funkcji ZAOKR.DO.PARZ. Zapis formuły powinien wyglądać następująco:
=ZAOKR.DO.PARZ(A2)
Ważną informacją jest tutaj, że Excela za pomocą funkcji ZAOKR.DO.PARZ zaokrągla zawsze w górę. Jeśli mamy 44,64 zł to funkcja ta zaokrągli nam ją do najbliższej większej liczby parzystej, czyli 46,00 zł. Analogicznie sytuacja wygląda z liczbami ujemnymi, zaokrąglenie następuje od zera w górę. Po zatwierdzeniu powyższej formuły i skopiowaniu jej na komórki poniżej otrzymamy dane przedstawione na rys. nr 4.
Analogicznie w Excelu mamy funkcję ZAOKR.DO.NPARZ, która zaokrągla podane liczby do wartości nieparzystych. Zapis formuły powinien wyglądać następująco:
=ZAOKR.DO.NPARZ(A2)
Po zatwierdzeniu formuły i skopiowaniu jej na komórki w dół otrzymamy dane przedstawione na rys. nr 5.
Podobnie jak poprzednia omawiana funkcja zaokrąglenie następuje od zera w górę. Czyli liczba 44,01 zł zostanie zaokrąglona do wartości 45,00 zł.
Omówiliśmy cztery najważniejsze funkcje związane z liczbami parzystymi i nieparzystymi.
W tym poście pokażemy, jak sprawdzić, czy podana data jest z roku przestępnego.
Zadanie to wykonamy na podstawie przykładowych danych z rys. nr 1.
Wykorzystamy tutaj właściwość funkcji DATA. Kiedy w tej funkcji "przekręcimy licznik", czyli podamy więcej dni niż jest w danym miesiącu, funkcja ta przejdzie na kolejny miesiąc. Pierwszym argumentem funkcji DATA jest rok, drugim – miesiąc, a trzecim – dzień. W argumencie rok musimy użyć funkcji ROK, żeby z podanej daty wyciągnąć rok. W drugim argumencie musimy podać numer miesiąca, który świadczy o roku przestępnym, czyli luty (2). Następnie w trzecim argumencie musimy podać wartość 29, ponieważ w roku przestępnym lury ma 29 dni. Zapis całej formuły powinien wyglądać następująco:
=DATA(ROK(A2);2;29)
Jeśli istnieje taka data w danym roku to Excel ją wyświetli. Jeśli jednak nie istnieje, to Excel przeskoczy na pierwszy dzień kolejnego miesiąca, czyli otrzymamy datę 1 marca danego roku. Po skopiowaniu formuły na komórki poniżej otrzymamy dane przedstawione na rys. nr 2.
Jeśli w wynikach funkcji mamy datę z dniem 29 lutego, oznacza to, że ten rok jest przestępny.
Teraz musimy sprawdzić, czy wynikiem jest data z lutego czy z marca. Użyjemy do tego funkcji MIESIĄC. Argumentem tej funkcji jest kolejna_liczba, który przyrównujemy do wartości 2. Zapis formuły powinien wyglądać następująco:
=MIESIĄC(DATA(ROK(A2);2;29))
Powyższą formułę zatwierdzamy i kopiujemy na komórki w dół. Otrzymamy wyniki przedstawione na rys. nr 3.
Wartość logiczną FAŁSZ otrzymamy, jeśli podany rok nie jest przestępny, w przeciwnym razie otrzymamy wartość logiczną PRAWDA. Powyższą formułę musimy jeszcze zapakować w funkcję JEŻELI, żeby otrzymać ładne teksty a nie wartości logiczne. Jeżeli warunek będzie spełniony, chcemy otrzymać tekst "Rok przestępny", w przeciwnej sytuacji "Nie rok przestępny". Zapis formuły powinien wyglądać następująco:
=JEŻELI(MIESIĄC(DATA(ROK(A2);2;29));"Rok przestępny";"Nie rok przestępny")
Powyższą formułę zatwierdzamy i kopiujemy w dół. Otrzymamy wyniki przedstawione na rys. nr 4.
Otrzymaliśmy informację, czy dany rok jest przestępny, czy też nie.
Niestety w omawianej sytuacji mamy jeden drobny "haczyk". W danych mamy datę 1900-03-29, który przez Excela został uznany za rok przestępny. Jest to niezgodne z prawdą. Dzieje się tak, ponieważ w Excelu istnieje data 1900-02-29, która jest jego błędem.
Podsumowując, jeżeli sprawdzasz daty poza rokiem 1900, to otrzymane wartości na pewno będą prawidłowe.
W tym poście dowiemy się, jak wygenerować prosty losowy kod.
Do tego celu będziemy używać dwóch funkcji KOD i ZNAK. Argumentem funkcjo KOD jest tekst. Funkcja ta podstawia wartość numeryczną danego znaku. Zapis formuły powinien wyglądać następująco:
=KOD(C2)
Literce A przypisana jest wartość liczbowa 65, natomiast literce Z – 90. Wyniki funkcji zostały przedstawione na rys. nr 1.
Na podstawie tych numerków (wartości liczbowych przypisanych literom) za pomocą funkcji ZNAK, możemy znowu uzyskać literę. Czyli z liczby funkcja zwraca nam literę. Argumentem funkcji ZNAK jest liczba. Zapis formuły powinien wyglądać następująco:
=ZNAK(D2)
Otrzymamy wyniki przedstawione na rys. nr 2.
Teraz, kiedy poznaliśmy działanie funkcji KOD i ZNAK możemy zająć się wygenerowaniem losowego kodu. W Excelu mamy funkcje LOS.ZAKR, której pierwszym argumentem jest dół, czyli u nas wartość 65 dla literki A. Drugi argument funkcji to góra, czyli wartość 90 dla litery Z. Zapis funkcji powinien wyglądać następująco:
=LOS.ZAKR(65;90)
Funkcja ta będzie nam losowała dowolne liczby całkowite z podanego zakresu, czyli między liczbą 65 a 90. Klawiszem F9 możemy odświeżać kod, żeby pojawiały się nowe wyniki. Jeśli dodatkowo powyższą funkcję otoczymy funkcją ZNAK, to wylosowana liczba zostanie zamieniona na literę. Otrzymamy zapis formuły przedstawiony poniżej:
=ZNAK(LOS.ZAKR(65;90))
Przykładowy wylosowany kod został przedstawiony na rys. nr 3.
Chcielibyśmy wygenerować tych znaków więcej, aby nasz kod składał się z kilku liter. Nie możemy tutaj użyć funkcji POWTÓRZ, ponieważ ona powtórzyła by ten sam znak. Nam zależy, żeby to były różne znaki. W tym celu musimy skopiować zapis funkcji i skleić ze sobą za pomocą znaków &. Zapis formuły dla trzech losowych znaków powinien wyglądać następująco:
Po zatwierdzeniu powyższej formuły i przeciągnięciu jej na komórki poniżej otrzymamy losowe kody przedstawione na rys. nr 4.
Po każdorazowym naciśnięciu klawisza F9 kody wygenerują się ponownie.
Jeśli zależy nam, żeby te kody zostały na stałe to zaznaczamy odpowiedni zakres i klikamy prawym przyciskiem myszy. Następnie z podręcznego menu wybieramy ikonkę Wartość z Opcji wklejania (rys. nr 5).
Po takiej czynności wygenerowane kody nie mają formuły, zostały wklejone same wyniki, których nie możemy odświeżać za pomocą klawisza F9.
Ponadto od Excela 2013 są nowe funkcje o nazwach UNICODE i ZNAK.UNICODE. Możemy je podejrzeć, jeśli zaznaczymy kolumnę Znak, klikniemy na nią prawym przyciskiem myszy, a następnie wybierzemy z podręcznego menu opcję Odkryj (rys. nr 6).
Otrzymamy wyniki funkcji UNICODE i ZNAK.UNICODE przedstawione na rys. nr 7.
Dzięki tym nowym funkcjom mamy dużo więcej znaków do wyboru. Jednak do tworzenia prostych losowych kodów w zupełności wystarczą nam funkcje KOD i ZNAK.
W tym poście omówimy zamianę zakresów na ich nazwę.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Chodzi o to, że zazwyczaj w formułach mamy odwołania do zakresów. Takie zakresy możemy nazwać, że podglądając formułę od razu wiedzieć z jakich zakresów korzysta dana formuła, np. zakres C2:C197 możemy nazwać Sprzedaż.
Zaznaczmy kolumny Sprzedaż i Koszty, następnie żeby przejść na koniec tabeli musimy użyć skrótu klawiszowego Ctrl+Shift+Strzałka w dół. Po zaznaczeniu danych wybieramy polecenie Utwórz z zaznaczenia z karty Formuły (rys. nr 2).
Otworzy nam się okienko Tworzenia nazw z zaznaczenia, gdzie zaznaczamy checkbox przy opcji Górny wiersz (nazwy zakresów zostaną utworzone z nazwy w górnym wierszu, czyli nagłówka kolumn). Zatwierdzamy przyciskiem OK (rys. nr 3).
Teraz z w polu Nazwy możemy zobaczyć dwa nazwane zakresy – Koszty i Sprzedaż (rys. nr 4).
W tabeli z Sumą i Średnią sprzedaży i kosztów mamy formuły z zakresami:
=SUMA($C$2:$C$197)
=SUMA($D$2:$D$197)
W tej sytuacji pracujemy z na tym samym arkuszu, w którym znajdują się zakresy z formuł. Dlatego sprawa jest oczywista, do czego się one odnoszą. Może być jednak sytuacja, że formuły będą w innym arkuszu niż zakresy, do których się odwołują. W takiej sytuacji lepiej wykorzystać nazwy zakresów. Wtedy łatwiej się domyślić, czego dana formuła dotyczy.
Nasze zadanie będzie polegać na zastąpieniu w formułach zakresach odpowiednimi nazwami. Excel daje nam taką możliwość. Wystarczy zaznaczyć zakres z formułami, w których chcemy podmienić zakresy na nazwy, następnie rozwinąć polecenie Definiuj nazwy (punkt 2 na rys. nr 5) z karty Formuły (punkt 1) i wybrać polecenie Zastosuj nazwy (punkt 3).
Otworzy nam się okienko Stosowania nazw, gdzie w polu Zastosuj nazwy (punkt 1 na rys. nr 6) muszą być zaznaczone obie nazwy (chcemy podmienić dwa zakresy jednocześnie). Musimy pamiętać, aby był zaznaczony checkbox przy Ignoruj względne/bezwzględne (punkt 2), chodzi tutaj o znaki $ przy zakresach. Następnie odznaczamy checkbox przy Używaj nazw wierszy i kolumn (punkt 3). Tak ustawione parametry zatwierdzamy przyciskiem OK.
Otrzymamy formuły z podmienionymi zakresami na nazwy. Przykładowe formuły zostały przedstawione poniżej
=SUMA(Sprzedaż)
=SUMA(Koszty)
Analogicznie zmieniły się formuły dla wartości Średniej z kosztów i sprzedaży, czy zakresy odpowiednio $C$2:$C$197 i $D$2:$D$197 zostały zamienione na Sprzedaż i Koszty.
Podsumowując, dla nas najważniejsze jest podstawienie nazw pod zakresy. Dzięki temu możemy łatwiej domyślić się, czego dotyczy dana formuła, szczególnie w sytuacji, kiedy formuła i dane, do których się odwołuje są w innych arkuszach Excela.
W tym poście nauczymy się, jak możemy dodać wiersz sumy nad tabelą.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Czasem potrzebujemy podsumować dane z tabeli Excela. Istotne dla nas jest, że chcemy takie podsumowanie mieć nad tabelą, aby po otwarciu pliku było widoczne na pierwszym planie (od razu). Gdybyśmy chcieli otrzymać standardowe podsumowanie pod tabelą, możemy użyć funkcjonalności Excela Wiersz sumy na karcie Projekt tabeli (rys. nr 2).
Po zaznaczeniu checkboxa przy funkcjonalności Wiersz sumy dopisze nam się dodatkowy wiersz z podsumowaniem pod tabelą Excela (rys. nr 3).
W tym wierszu dodało nam się podstawowe podsumowanie kosztów, ale łatwo możemy dodać podsumowanie po innych kolumnach. Wystarczy, że klikniemy na ikonkę trójkącika w prawym rogu odpowiedniej komórki. Rozwinie nam się podręczne menu z dostępnymi opcjami podsumowania widoczne na rys. nr 4.
Jak dla mnie mylące są tam nazwy Zliczanie i Liczba. Po sprawdzeniu tych funkcjonalności dowiemy się, że Zliczanie liczy liczby, a Liczba liczy tekst, czyli tak naprawdę ilość uzupełnionych komórek w danej kolumnie.Dopiero gdybyśmy się przyjrzeli, co ukrywa się pod tą funkcjonalnością, to możemy po podejrzeniu formuły za pomocą klawisza F2 zobaczyć funkcję SUMY.CZĘŚCIOWE (rys. nr 5).
W powyższej formule w argumencie o nazwie funkcja_nr został użyty kod 103, który odpowiada funkcji ILE.NIEPUSTYCH. Możemy podejrzeć możliwe funkcje do wyboru, co widać na rys. nr 6.
Dla nas istotne jest, że taki wiersz sumy chcemy mieć nad tabelą, a nie jak to się dzieje domyślnie – na końcu. Najprostszym sposobem na uzyskanie takiego wiersza, to wypełnienie wiersza sumy końcowej, następnie zaznaczenie całego wiersza z tym podsumowaniem i skopiowanie go za pomocą skrótu klawiszowego Ctrl+C. Wystarczy teraz przejść na górę arkusza, gdzie mamy przygotowaną małą tabelę z podsumowaniem i wkleić wcześniej skopiowany wiersz za pomocą skrótu klawiszowego Ctrl+V.
Ponieważ skopiowaliśmy wiersz z sumami częściowymi, te komórki będą miały takie same formuły, będą odwoływać się do odpowiednich komórek z kolumny. Podsumowując mamy takie same formuły, które odnoszą się do danych z tabeli z tą różnicą, że mieszczą się poza nią.
Ponadto ten wiersz jest dynamiczny, czyli jeśli zmienimy dane w tabeli, do której się odnoszą zmienią się nasze wyniki. Np. załóżmy, że chcemy zrobić podsumowanie sprzedaży konkretnego sprzedawcy – Andrzeja. Po przefiltrowaniu kolumny Link referencyjny, otrzymamy podsumowanie dla wybranego sprzedawcy, co widać na rys. nr 7.
Podsumowując, możemy mieć takie samo podsumowanie na dole tabeli jak i nad tabelą, wykorzystując proste sztuczki Excela.
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 nauczysz się jak łatwo wklejać nazwy obszarów w formułach.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Na przykład nazwiemy naszą kolumnę C jako "Sprzedaż", w tym celu zaznaczamy kolumnę i klikamy w pole Nazwy, następnie wpisujemy wybraną nazwę i zatwierdzamy Enterem (rys. nr 2).
Otrzymaliśmy nazwany zakres, którego teraz użyjemy do napisania formuły. Załóżmy, że chcemy obliczyć średnia wartość sprzedaży. Użyjemy do tego funkcji ŚREDNIA, której argumentami są liczba1, liczba2 itd. Jako argumentu funkcji będziemy chcieli użyć nazwanego zakresu, więc wystarczy, że zaczniemy wpisywać w formule pierwsze litery naszego zakresu, a Excel podpowie nam pasujące elementy (rys. nr 3).
Wystarczy, że klikniemy na odpowiednią nazwę zakresu. Zapis formuły będzie wyglądał następująco:
=ŚREDNIA(Sprzedaż)
Przy okazji pokażemy inny sposób nazywania zakresów danych. Zaznaczamy zakres z nagłówkiem, jaki chcemy nazwać, następnie wybieramy polecenie Utwórz z zaznaczenia (punkt 1 na rys. nr 4) z karty Formuły (punkt 2).
Otworzy nam się okienko Tworzenia nazwy z zaznaczenia, gdzie zaznaczamy na podstawie czego ma zostać nadana nazwa naszemu zakresowi. Zaznaczamy checkbox przy pozycji Górny wiersz, ponieważ ten wiersz zawiera nagłówek. Czynność zatwierdzamy przyciskiem OK (rys. nr 5).
Teraz w polu Nazwy możemy zobaczyć na liście zakres, który przed chwilą nazwaliśmy za pomocą polecenia Utwórz z zaznaczenia (rys. nr 6).
Możemy również wykorzystać tak stworzoną nazwę zakresu w formule. Wpisujemy nazwę funkcji, a następnie jako argument funkcji musimy podać nazwę zakresu. Załóżmy, że zapomnieliśmy, jaką nazwę nadaliśmy. Wtedy wciskamy klawisz F3, Excel otworzy nam okienko Wklejanie nazwy z listą wszystkich nazwanych w danym arkuszu zakresów danych. Wybieramy zakres, którego chcemy użyć i klikamy przycisk OK (rys. nr 7).
Zapis formuły powinien wyglądać następująco:
=ŚREDNIA(Koszty)
Niestety są sytuacje, w których nie można uzyskać podpowiedzi Excela dotyczącej nazwy zakresu. Mamy nazwany zakres Sprzedawcy z kolumny H. W kolumnie B będziemy chcieli zrobić sobie listę rozwijaną. Zaznaczamy zakres B2:B12 (kolumna Sprzedawcy bez nagłówka), następnie wybieramy polecenie Sprawdzanie poprawności danych (punkt 2 na rys. nr 8) z karty Dane (punkt 1).
Otworzy nam się okno Sprawdzania poprawności danych, gdzie w zakładce Ustawienia (punkt 1 na rys. nr 9) w Kryteriach poprawności jako Dozwolone (punkt 2) wybieramy Lista. W polu Źródło (punkt 3) musimy podać odpowiedni zakres danych. Właśnie tutaj Excel nie podpowiada nazwy zakresu po wpisaniu pierwszych jej liter. Ale zadziała tutaj klawisz F3, otworzy nam się okienko Wklejanie nazwy, z którego w polu Wklej nazwę wybieramy nasz zakres Sprzedawcy (analogicznie jak na rys. nr 7). W polu Źródło zostanie wstawiona nazwa Sprzedawcy poprzedzona znakiem "=". Zatwierdzamy powyższe ustawienia przyciskiem OK (punkt 4).
Otrzymamy listę rozwijaną przedstawioną na rys. nr 10.
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.