W tym poście poznamy dwa sposoby na zaznaczanie różnic w wierszach i kolumnach.
Temat ten omówimy na podstawie przykładowych danych z rys. nr 1.
Rys. nr 1 – przykładowe dane
Pierwszy sposób będzie wykorzystywał zaznaczanie specjalne, a drugi formatowanie warunkowe.
Będziemy chcieli sprawdzić, które komórki z pozostałych kolumn różnią się od pierwszej kolumny. Podsumowując w zaznaczaniu specjalnym zawsze patrzymy na pierwszą kolumnę. Zaznaczamy zakres A1:C12, rozwijamy polecenie Znajdź i zaznacz (punkt 2 na rys. nr 2) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Przejdź do – specjalnie… (punkt 3).
Rys. nr 3 – polecenie Przejdź do – specjalnie…
Otworzy nam się okno Przechodzenia do – specjalnie, w którym ponieważ interesują nas różnice między kolumnami, to musimy zaznaczyć opcję Różnice w wierszach. Według mnie jest to mało intuicyjne, ale trzeba to zapamiętać. Zatwierdzamy przyciskiem OK (rys. nr 3).
Rys. nr 3 – okno Przechodzenia do – specjalnie…
Otrzymamy dane, w których Excel zaznaczył komórki różniące się od pierwszej kolumny, przedstawione na rys. nr 4.
Rys. nr 4 – zaznaczone komórki różniące się od pierwszej kolumny
Analogicznie wykonamy zadanie, w którym będziemy chcieli znaleźć dane różniące się od pierwszego wiersza. Zaznaczamy zakres A16:C27 i wykorzystamy tutaj skrót klawiszowy Ctrl+G, który otworzy nam okno Przechodzenia do, w którym klikamy przycisk Specjalnie… (rys. nr 5).
Rys. nr 5 – okno Przechodzenia do
Otworzy nam się okno Przechodzenia do – specjalnie, w którym ponieważ interesują nas różnice między wierszami, to musimy zaznaczyć opcję Różnice w kolumnach. Zatwierdzamy przyciskiem OK (rys. nr 6).
Rys. nr 6 – okno Przechodzenia do – specjalnie…
Ponownie otrzymamy sprawdzone dane, w których na żółto zostały zaznaczone komórki różniące się od pierwszego wiersza.
Rys. nr 7 – dane z zaznaczonymi różnicami w wierszach
Analogicznie możemy użyć tej opcji Excela przy liczbach, np. w bazach danych. Tutaj dodatkowo musimy uwzględnić zaokrąglenie, ponieważ mimo, że wartości wyglądają na takie same, może to być mylące. Wystarczy, że np. zostanie nałożone formatowanie z liczbami całkowitymi. Zaznaczamy zakres G1:H21 i ponownie rozwijamy polecenie Znajdź i zaznacz (jak na rys. nr 2) z karty Narzędzia główne, następnie wybieramy polecenie Przejdź do – specjalnie…
Otworzy nam się okno Przechodzenia do – specjalnie, w którym ponieważ interesują nas różnice między kolumnami, to musimy zaznaczyć opcję Różnice w wierszach. Zatwierdzamy przyciskiem OK (jak na rys. nr 3).
Otrzymamy dane przedstawione na rys. nr 8.
Rys. nr 8 – pokolorowane dane różniące się od pierwszej kolumny
Jak widać w pierwszym wierszu, mimo iż na pierwszy rzut oka dane wyglądają identycznie, są one inne. Mają inne wartości po przecinku, co widać na rys. nr 9, po odpowiednim sformatowaniu danych.
Rys. nr 9 – różnice w cyfrach po przecinku
Drugi sposób polega na użyciu Formatowania warunkowego. Zaznaczamy dane A1:C12, rozwijamy polecenie Formatowanie warunkowe (punkt 2 na rys. nr 10) z karty Narzędzia główne (punkt 1), następnie wybieramy polecenie Nowa reguła (punkt 3).
Rys. nr 10 – polecenie Nowa reguła
Otworzy nam się okno Nowa reguła formatowania, gdzie w polu Wybierz typ reguły (punkt 1 na rys. nr 11) wybieramy Użyj formuły do określenia komórek, które należy sformatować (punkt 2). Następnie w polu Edytuj opis reguły (punkt 3) wpisujemy formułę =$A1<>A1 (porównujemy wartość z komórki A1 tak, żeby była różna od pozostałych wartości). Naszą wartość będziemy chcieli przeciągać w dół danych a nie w bok, ponieważ chcemy sprawdzić każdą kolumnę niejako "osobno". Dlatego musimy użyć odpowiedniego odwołania mieszanego, czyli znak $ powinien pojawić się tylko przed nazwą kolumny (przed literą A). Klikamy przycisk Formatuj (punkt 4), który spowoduje otwarcie okna Formatowania komórek.
Rys. nr 11 – okno Nowej reguły formatowania
W oknie Formatowania komórek w zakładce Czcionka (punkt 1 na rys. nr 12), w polu Kolor (punkt 2) wybieramy czerwony, a w polu Styl czcionki (punkt 3) zaznaczamy Pogrubiony. Powyższe ustawienia zatwierdzamy przyciskiem OK.
Rys. nr 12 – okno Formatowania komórek
Excel wróci do okna Nowej reguły formatowania, którą pozostaje nam zatwierdzić przyciskiem OK. Otrzymamy dane przedstawione na rys. nr 13.
Rys. nr 13 – dane różniące się od pierwszego wiersza zaznaczone czerwoną pogrubioną czcionką
Teraz analogiczna sytuacja na podstawie danych z zakresu A16:C27, dla których będziemy chcieli otrzymać zaznaczone dane różniące się od pierwszego wiersza. Zaznaczamy odpowiedni zakres, rozwijamy polecenie Formatowanie warunkowe (jak na rys. nr 10) z karty Narzędzia główne, następnie wybieramy polecenie Nowa reguła.
Otworzy nam się okno Nowa reguła formatowania, gdzie w polu Wybierz typ reguły (jak na rys. nr 11) wybieramy Użyj formuły do określenia komórek, które należy sformatować. Następnie w polu Edytuj opis reguły wpisujemy formułę =A$16<>A17 (porównujemy wartość z komórki A16 tak, żeby była różna od pozostałych wartości). Naszą wartość będziemy chcieli przeciągać w bok danych a nie w dół, ponieważ chcemy sprawdzić każdy wiersz niejako "osobno". Dlatego musimy użyć odpowiedniego odwołania mieszanego, czyli znak $ powinien pojawić się tylko przed numerem wiersza (przed liczbą 16). Klikamy przycisk Formatuj, który spowoduje otwarcie okna Formatowania komórek.
W oknie Formatowania komórek w zakładce Czcionka (jak na rys. nr 12), w polu Kolor wybieramy czerwony, a w polu Styl czcionki zaznaczamy Pogrubiony. Powyższe ustawienia zatwierdzamy przyciskiem OK.
Excel wróci do okna Nowej reguły formatowania, którą pozostaje nam zatwierdzić przyciskiem OK. Otrzymamy dane przedstawione na rys. nr 14.
Rys. nr 14 – dane po nałożeniu formatowania warunkowego
Poznaliśmy dwa sposoby jak można w Excelu zaznaczać różnice w wierszach i kolumnach danych.
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.
Rys. nr 1 – przykładowe dane
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.
Rys. nr 2 – wyniki funkcji CZY.PARZYSTE
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.
Rys. nr 3 – wyniki funkcji CZY.NIEPARZYSTE
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.
Rys. nr 4 – wyniki funkcji ZAOKR.DO.PARZ
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.
Rys. nr 5 – wyniki funkcji ZAOKR.DO.NPARZ
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.
Rys. nr 1 – przykładowe dane
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.
Rys. nr 2 – wyniki funkcji DATA
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.
Rys. nr 3 – wyniki funkcji MIESIĄC
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.
Rys. nr 4 – wyniki funkcji JEŻELI
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.
Rys. nr 1 – wyniki funkcji KOD
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.
Rys. nr 2 – wyniki funkcji ZNAK
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.
Rys. nr 3 – przykładowy wylosowany kod
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.
Rys. nr 4 – losowe kody z trzech znaków
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).
Rys. nr 5 – Opcje wklejania
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).
Rys. nr 6 – opcja Odkryj
Otrzymamy wyniki funkcji UNICODE i ZNAK.UNICODE przedstawione na rys. nr 7.
Rys. nr 7 – wyniki funkcji UNICODE I ZNAK.UNICODE
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.
Rys. nr 1 – przykładowe dane
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).
Rys. nr 2 – polecenie Utwórz z zaznaczenia
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).
Rys. nr 3 – okno Tworzenia nazw z zaznaczenia
Teraz z w polu Nazwy możemy zobaczyć dwa nazwane zakresy – Koszty i Sprzedaż (rys. nr 4).
Rys. nr 4 – dwa nazwane zakresy
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).
Rys. nr 5 – polecenie Zastosuj nazwy
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.
Rys. nr 6 – okno Stosowania nazw
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.
