Slow7 - Office

Excel w zadaniach. Funkcje tekstowe i wyszukujące.

2017-07-14T09:12:48+00:00

Pomimo, iż arkusz kalkulacyjny został stworzony aby uprościć operacje związane z analizą i przetwarzaniem danych liczbowych to zawiera on szereg funkcji związanych z tekstem oraz wyszukiwaniem. Funkcje te typowo nie są związane z operacjami matematycznymi lecz pośrednio je wspomagają. Poznanie ich przeznaczenia i sposobu działania wiele spraw ułatwia gdyż operując na liczbach trzeba je w jakiś sposób opisać.

Wpis rozpoczniemy od omówienia funkcji DŁ() (zaczynamy od tych najprostszych, by w miarę zgłębianie tematu przejść do tych bardziej zaawansowanych).

Funkcja zwraca liczbę znaków, z których składa się tekst wskazany w argumencie funkcji. Argumentem może być konkretny ciąg zbudowany z liczb bądź liter lub adres komórki w której dana fraza się znajduje. Do zliczanych znaków włączane są również znaki interpunkcyjne oraz odstępy.

Składnia funkcji jest bardzo prosta i ma postać: DŁ(tekst/adres_komórki)

Argument: tekst/adres_komórki określa tekst bądź adres komórki w której tekst został wpisany, którego długość ma zostać określona. Spacje są liczone jako znaki.

Przykład zastosowania funkcji: DŁ() zaprezentowano na rysunku poniżej. W lewej kolumnie znajdują się wpisane frazy zaś po prawej została wyliczona ilość znaków. Jak można zauważyć ilość znaków z których są zbudowane dane ciągi została wyliczona prawidłowo. Choć w przypadku ostatniego przykładu wydawać by się mogło, że jest błąd gdyż przecież wyraz „spacja” zbudowany jest z 6 liter a nie 7. Taki stan rzeczy spowodowany jest tym, że po wyrazie „spacja” został dodany jeden znak właśnie w formie spacji - 6 liter plus spacja = 7.

Funkcję DŁ() możemy wykorzystać np. w zbudowaniu funkcji, która będzie Nam sprawdzać czy wpisany numer, który mus się składać z określonej liczby znaków (PESEL, NIP, REGON) został wpisany prawidłowo. Poniżej przykład w którym za pomocą formuły: =JEŻELI(DŁ(adres_komórki)=11;"OK";"BŁĄD") sprawdzamy poprawność numeru PESEL. Numer ten składa się z 11 liczb, brak (lub też pojawienie się dodatkowej) liczby powoduje wypisanie komunikatu BŁĄD Jeśli numer PESEL jest prawidłowy pojawia się słowo OK.

Te samo zadanie można wykonać z wykorzystaniem reguł formatowania warunkowego. Poprawnie zbudowane numery PESEL są zaznaczane na żółto.

W sytuacjach w których z podanego ciągu tekstowego musimy wyodrębnić jakąś jego część możemy wykorzystać funkcję: FRAGMENT.TEKSTU() Ogólna składnia funkcji wygląda następująco: FRAGMENT.TEKSTU(tekst;liczba_początkowa;liczba_znaków)

Zadaniem funkcji jest zwrócenie znaku bądź ciągu znaków zawartych w dowolnym innym tekście, pobrany ciąg liter bądź cyfr może rozpoczynać się od określonej pozycji tekstu bazowego i składać się z określonej liczby znaków. Do znaków tych zaliczamy litery, cyfry oraz znaki interpunkcyjne.

Pierwszy argument definiuje tekst bądź adres komórki w której ten tekst się znajduje, z którego ma zostać wyodrębniony znak lub ciąg znaków. Drugi parametr (liczba_początkowa) określa numer pozycji liczonej od lewej strony od której ma rozpocząć się pobieranie znaków. Ostatni argument liczba_znaków określa liczbę wyodrębnianych znaków.

Poniżej przykład użycia omawianej funkcji - z lewej strony zostały zapisane nazwy miejscowość z prawej zaś wyodrębniony ciąg - użyta formuła: =FRAGMENT.TEKSTU(D4;4;8) nakazuje wypisać 8 znaków począwszy od 4 znaku. Jak można zaobserwować znak spacji wliczany jest do wyodrębnianych znaków.

Praktyczny przykład wykorzystujący funkcję FRAGMENT.TEKSTU() został przedstawiony poniżej. Przypuśćmy, że posiadamy bazę faktur, których zasada numerowania opiera się na wzorcu: <numer_kolejny>/<kontrahent>/<rok> przy czym każde z pól zawiera następującą liczbę znaków: 5/3/4 Potrzebujemy z tak przygotowanej bazy wyodrębnić nazwę kontrahentów. Aby zadanie to wykonać została użyta następująca formuła: =FRAGMENT.TEKSTU(J18;7;3) która nakazuje od 7 znaku włącznie pobrać 3.

W operacjach związanych z tekstem bardzo często wykorzystywana jest funkcja: LEWY() oraz funkcja bliźniak: PRAWY().

Funkcja: LEWY() zwraca określoną liczbę znaków liczoną od lewej strony ciągu liczbowego bądź tekstowego.

Zaś funkcja: PRAWY() jak łatwo się domyśleć zwraca określoną liczbę znaków ciągu lecz tym razem znaki są liczone od strony prawej.

Składnia funkcji wygląda następująco: LEWY(tekst/adres_komórki;liczba_znaków) oraz PRAWY(tekst/adres_komórki;liczba_znaków)

Zastosowanie obu funkcji zostało zaprezentowane na zrzucie poniżej. W prezentowanym przykładzie z puli nazwisk wyodrębniono trzy pierwsze litery (formuła: =LEWY(adres_komórki;3) oraz trzy ostatnie litery nazwiska (formuła: =PRAWY(adres_komórki;3).

Praktyczne zastosowanie funkcji możemy zobrazować na przykładzie w którym to potrzebujemy na bazie imienia i nazwiska stworzyć nazwę logowania opierającą się na schemacie: 3 litery imienia + 3 litery nazwiska.

Aby zadanie te zrealizować została użyta dwukrotnie funkcja: LEWY() w połączeniu z symbolem łączenia zawartości dwóch komórek (&) - z zawartości dwóch komórek (imię i nazwisko) zostały pobrane 3 pierwsze znaki, które w następnym kroku zostały ze sobą połączone. Formuła, która realizuje powyższe zadanie ma postać: =LEWY(adres_komórki;3)&LEWY(adres_komórki;3)

No to jeszcze jeden mały przykład. Posiadamy bazę imion i nazwisk niestety są one poprzedzone tytułami naukowymi a my potrzebuje tylko imię i nazwisko. Aby z tak zbudowanego ciągu wyodrębnić samo imię i nazwisko z pominięciem tytułu naukowego należy użyć takiej o to formuły: =JEŻELI(LUB(LEWY(V16;4)="inż.";LEWY(V16;3)="mgr";LEWY(V16;2)="dr");PRAWY(V16;DŁ(V16)-ZNAJDŹ(" ";V16));V16)

Formuła ta działa w ten sposób, że przy pomocy funkcji JEŻELI() w połączeniu z funkcją LUB() oraz LEWY() sprawdzane jest czy występują tytuły naukowe: inż., mgr oraz dr jeśli ten warunek jest spełniony wyliczana jest długość znaków całego ciągu pomniejszona o liczbę pozycji w której występuje znak spacji. Tak wyliczona długość ciągu jest wyodrębniona za pomocą funkcji PRAWY(). Jeśli tytuł naukowy nie występuje zawartość komórki zostaje powielona. W formule tej została użyta funkcja ZNAJDŹ(), której opis działania znajdziesz w dalszej części wpisu.

Opis funkcji JEŻELI() oraz LUB() znajdziesz w tych wpisach: Excel w zadaniach. Funkcja JEŻELI oraz Excel w zadaniach. Funkcja ORAZ i LUB

Trzy następne funkcje są bardzo proste a ich zadaniem jest zamiana liter małych na wielkie i odwrotnie.

Aby każde słowo w komórce zostało zamienione na wielkie litery należy użyć funkcji: LITERY.WIELKIE(adres_komórki) Poniżej przykład w którym imiona zapisane w komórkach od D5 do D10 zostały zamienione na wielkie litery (komórki od G5 do G10).

W sytuacji odwrotnej używamy funkcji: LITERY.MAŁE(adres_komórki) Analogiczny przykład z tym powyżej lecz tym razem imiona zostają zapisane za pomocą małych liter.

Trzecią z funkcji odpowiedzialną za zmianę wielkości liter jest funkcja: Z.WIELKIEJ.LITERY(adres_komórki) która to pierwszą literę każdego słowa zamienia na wielką literę, zaś wszystkie pozostałe litery tworzące dany ciąg są literami małymi.

O to przykład w którym przy pomocy funkcji Z.WIELKIEJ.LITERY() imiona i nazwiska zostały zmienione tak by rozpoczynały się od wielkiej litery (zgodnie z zasadami ortografii). Imiona i nazwiska zapisane mały literami zostały pobrane z kolumny D oraz E i połączone ze sobą przy pomocy symbolu & (pomiędzy imieniem i nazwiskiem dodatkowo został dodany znak spacji) - formuła realizująca to zadanie przyjmie postać: Z.WIELKIEJ.LITERY(D37)&" "&Z.WIELKIEJ.LITERY(E37)

Komórka D37 zawierała tekst jan użycie formuły spowodowało zwrócenie tekstu Jan. Użycie formuły na komórce w której zawarty byłby tekst pan jan spowoduje zwrócenie ciągu Pan Jan. Użyta spacja w ciągu tekstowym powoduje, że słowo zawarte po spacji również rozpoczynane jest od wielkiej litery. Taką samą sytuację mamy w przypadku użycia znaku apostrofu (słowo d'artagnan).

Znając już funkcję LEWY(), PRAWY(), LITERY.WIELKIE, LITERY.MAŁE oraz DŁ() zadanie zapisania wyrazu zaczynającego się od wielkiej litery z pominięcie funkcji Z.WIELKIEJ.LITERY() można by było zrealizować za pomocą formuły użytej na poniższym zrzucie: =LITERY.WIELKIE(LEWY(D28))&PRAWY(LITERY.MAŁE(D28);DŁ(D28)-1) Formuła pobiera pierwszy lewy znak z imienia i zamienia go na wielki (LITERY.WIELKIE(LEWY(D28))) a następnie obliczana jest długość całego ciągu tekstowego minus znak pierwszy (DŁ(D28)-1), która w kroku kolejnym zamienia pozostałe litery na małe (PRAWY(LITERY.MAŁE(D28)) Tak oba powstałe ciągi są ze sobą łączone (symbol &).

Tak wiem, że przykład przedstawiony powyżej jest pewnego rodzaju „przekombinowaniem” ale pokazuje fajnie jak poznane do tej pory funkcję można ze sobą łączyć. Czytelniku potraktuj przedstawiony przykład jak łamigłówkę i postaraj zrozumieć jak przytoczona formuła działa.

W pracy z tekstem bardzo często zdarzają się takie sytuacje, w których trzeba część jakiegoś większego tekstu zastąpić innym. Do sytuacji takiej dochodzi najczęściej w przypadku importu danych np. data z importowanego pliku ma inny format od tego oczekiwanego - np. importowana data ma postać 12/02/1980 a my potrzebujemy mieć ją zapisaną w postaci 12-02-1980. Do przekształcenia znaku / na - możemy użyć narzędzia Zamień (polecenie Narzędzia główne/Edycja/Znajdź i zaznacz/Zamień) bądź wykorzystać rozwiązanie oparte na formule.

Do zamiany jednego fragmentu tekstu w drugi możemy użyć następujących dwóch funkcji:

funkcja: PODSTAW(tekst;stary_tekst;nowy_tekst;[wystąpienie_liczba]) - tą funkcję należy wybrać wtedy gdy znany Nam jest znak (bądź ich ciąg) lecz niewiadomą jest jego pozycja.

argumenty funkcji:

tekst - tekst lub odwołanie do komórki zawierającej tekst, w którym zostaną zastąpione znaki,

stary_tekst - łańcuch, który zostanie zastąpiony,

nowy_tekst - nowy łańcuch, który pojawi się w miejscu ciągu zdefiniowanego przez argument stary_tekst,

wystąpienie_liczba - jest to argument opcjonalny. Definiuje, które wystąpienie argumentu stary_tekst zostanie zastąpione przez argument nowy_tekst.

Poniżej zostały przedstawione trzy przykłady użycia funkcji: PODSTAW()

Przykład 1: formuła: =PODSTAW(D5;"2015";"2016") - zostaje wykonana zamiana roku 2015 na 2016,

Przykład 2: =PODSTAW(D10;" ";"") - zostają usunięte znaki spacji,

Przykład 3: =PODSTAW(D15;"r";"R";2) - zostaje zamienione drugie wystąpienie małej litery r na duże R.

funkcja: ZASTĄP(stary_tekst;nr_poz_pocz;liczba_znaków;nowy_tekst) - zamienia część ciągu tekstowego na inny ciąg tekstowy z uwzględnieniem określonej liczby znaków.

Argumenty funkcji:

stary_tekst - tekst, który ma zostać zamieniony,

nr_poz_pocz - pozycja znaku w tekście argumentu stary_tekst, w której ma nastąpić zamiana na tekst argumentu nowy_tekst,

liczba_znaków - liczba znaków w tekście argumentu stary_tekst, które funkcja ZASTĄP() ma zamienić na tekst argumentu nowy_tekst,

nowy_tekst - tekst, na który zostaną zamienione znaki w tekście argumentu stary_tekst.

Poniżej przykład zastosowania opisywanej funkcji. W komórce F21 została wprowadzona formuła: =ZASTĄP(D21;6;1;""), której zadaniem jest zastąpienie 6 znaku w komórce D21. Na wskutek działania formuły znak ukośnika zostaje skasowany.

Oprócz wykonywania operacji zastąpienia jednego tekstu drugim w przypadku pracy z danymi tekstowymi bardzo często dochodzi do sytuacji w której musimy dokonać wyszukania określonego ciągu znaków. W realizacji tego zadania pomogą Nam funkcję: ZNAJDŹ() oraz SZUKAJ.TEKST()

Funkcje ZNAJDŹ() oraz SZUKAJ.TEKST() umożliwiają określenie początkowej pozycji szukanego ciągu znaków, będącego częścią innego łańcucha.

Funkcja ZNAJDŹ(), której ogólna składnia prezentuje się następująco: ZNAJDŹ(tekst_szukany;w_tekście;[nr_poz_pocz]) zwraca pozycję początkową jednego ciągu tekstowego w drugim ciągu tekstowym. Funkcja opcjonalnie umożliwia definicję znaku, od którego ma rozpocząć się wyszukiwanie. Funkcja ta rozróżnia wielkie i małe litery. Nie można za jej pomocą przeprowadzić wyszukiwania wieloznacznego.

Argumenty funkcji to:

tekst_szukany - tekst, który ma zostać odnaleziony,

w_tekście - tekst/adres komórki przeszukiwania,

nr_poz_pocz - parametr opcjonalny, definiujący znak, od którego ma się rozpocząć wyszukiwanie (domyślnie w przypadku braku definicji tego argumentu przyjmuje się wyszukiwanie od pierwszego znaku).

Dwa przykłady pokazujące działanie funkcji ZNAJDŹ() zostały zaprezentowane na zrzucie poniżej.

Przykład 1: formuła: =ZNAJDŹ("abc";C20) - zostaje przeprowadzone wyszukanie ciągu liter: abc Po wywołaniu formuły zwracana jest wartość jego pozycji. Jak można zauważyć wielkość liter ma znaczenie gdyż w sytuacji wystąpienia łańcucha liter ABC zwracany jest błąd #ARG! (zresztą tak jak i w innych przypadkach),

Przykład 2: =FRAGMENT.TEKSTU(C30;1;ZNAJDŹ("-";C30;1)-1) - w połączeniu z funkcją FRAGMENT.TEKSTU() zostaje wykonana operacja wyodrębnienia pierwszego członu numeru partii. Funkcja ZNAJDŹ() wyszukuje pozycję wystąpienia pierwszego znaku: - (w tym przypadku zwracana jest wartość 4) i od tej pozycji jest odejmowane 1 (czwarta pozycja znaku minus jeden = 3) w kolejnym kroku tak wyliczona wartość jest przekazywana do funkcji FRAGMENT.TEKSTU().

Funkcja SZUKAJ.TEKST() podobnie jak wyżej przedstawiona ZNAJDŹ() zwraca numer znaku, który rozpoczyna ciąg znaków znaleziony w innym łańcuchu znaków. W przypadku tej funkcji również można określić pozycję znaku, od którego ma być rozpoczęte wyszukiwanie, lecz funkcja ta nie rozróżnia wielkich i małych liter, ale rekompensatą jest możliwość przeprowadzenia wyszukania wieloznacznego.

Składnia funkcji jest następująca: SZUKAJ.TEKST(tekst_szukany;w_tekście;[nr_poz_pocz])

Parametry:

tekst_szukany - szukany ciąg znaków,

w_tekście - przeszukiwany tekst bądź adres komórki w której tekst się znajduje,

nr_poz_pocz - parametr opcjonalny, określający numer znaku od którego ma nastąpić przeszukiwanie.

Przykłady użycia funkcji SZUKAJ.TEKST() zostały pokazana na zrzucie poniżej.

Przykład 1: formuła: =SZUKAJ.TEKST("abc";I20) - zostaje przeprowadzone wyszukanie ciągu liter: abc Po wywołaniu formuły zwracana jest wartość jego pozycji. Jak można zauważyć wielkość liter nie ma znaczenie gdyż w każdej sytuacji niezależnie od użytej wielkości liter zwracana jest pozycja wystąpienia szukanego ciągu. W przypadku wystąpienia braku dopasowania zwracany jest błąd #ARG!

Przykład 2: =SZUKAJ.TEKST("?-?";I30;1) - formuła wyszukuje wystąpienie znaku myślnika (-) Do jej zbudowania został wykorzystany symbol wyszukiwania wieloznacznego: znak zapytania (?). Przy budowaniu formuły z użyciem funkcji SZUKAJ.TEKST() można użyć dwóch symboli umożliwiających przeprowadzenie wyszukania wieloznacznego:

znak zapytania (?) - zastępuje jeden dowolny znak,
gwiazdka (*) - zastępuje dowolną sekwencję znaków.

W sytuacji w której wyszukanym symbolem ma być znak zapytania bądź gwiazdki umieszczamy przed nim znak tyldy (~).

Kolejną funkcją wspomagającą Nas w wyszukiwaniu zawartości jest funkcja: PODAJ.POZYCJĘ() Funkcja ta bardzo często łączona jest z funkcją: INDEKS().

Rozpoczniemy od omówienia funkcji INDEKS() a następnie przejdziemy do funkcji PODAJ.POZYCJĘ() zaś w kroku kolejnym wykonamy zadanie w którym wykorzystamy obie te funkcje.

Składnia funkcji INDEKS() wygląda następująco: INDEKS(tablica;nr_wiersza;[nr_kolumny]) a jej zadaniem jest zwrócenie wartości komórki znajdującej się na przecięciu zdefiniowanego numeru wiersza i numeru kolumny.

Składnia funkcji INDEKS() obejmuje następujące argumenty:

tablica - zakres komórek,

nr_wiersza - wskazuje wiersz tablicy,

nr_kolumny - wskazuje kolumnę tablicy.

Jeśli argument tablica zawiera tylko jeden wiersz lub jedną kolumnę, to odpowiednio argument nr_wiersza lub nr_kolumny jest argumentem opcjonalnym.

Przyjrzyjmy się zatem przedstawionemu poniżej przykładowi. Do komórki V7 została wprowadzona formuła: =INDEKS(Q6:T9;2;3) efektem wprowadzonego polecenia jest uzyskanie wartości: b3 Stało się tak gdyż wartość: b3 znajduje się w komórce, która leży na przecięciu drugiego wiersza (w zdefiniowanej tabeli został pominięty nagłówek tabeli) i trzeciej kolumny w tablicy obejmującej komórki od Q6 do T9

Tak więc wiemy jak już działa funkcja INDEKS() wykorzystajmy ją w jakimś konkretnym przykładzie. Przypuśćmy, że mamy bazę części obejmującą ich sprzedaż w rozbiciu na lata i chcemy sprawdzić ile sztuk produktu udało się sprzedać. Przykład przeprowadzimy na produkcie: nakrętka m10 i roku 2013.

Poniżej tabeli zostały dodane trzy pozycje:

LP - numer kolejny produktu, który tożsamy jest z numerem wiersza

Rok - numer kolumny interesującego nas roku

Sztuki - wynik czyli ilość sprzedanych sztuk.

Chcąc poznać ilość sztuk produktu, która została sprzedana w pole LP została wpisana wartość 6 gdyż nakrętka m10 znajduje się w szóstym wierszu tabeli (nagłówek tabeli nie został wykorzystany) zaś w polu Rok pojawiła się wartość 5 gdyż rok 2013 to piąta kolumna tabeli.

Formuła wyszukująca ilość sztuk towaru przyjęła postać: =INDEKS(F6:L15;H20;H21)

Na tym etapie korzystanie z tak przygotowanego zestawienia jest to powiedzmy sobie szczerze - mało intuicyjne i mało wygodne gdyż by poznać wartość musimy ręcznie ustalić wartość wiersza i kolumny. Ale bez obaw do przykładu zaraz wrócimy.

Drugą wspomnianą przeze mnie funkcją jest funkcja: PODAJ.POZYCJĘ()

Jej składnia przedstawia się następująco: PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;[typ_porównania])

W składni funkcji PODAJ.POZYCJĘ występują następujące argumenty:

szukana_wartość - szukana wartość, której przeszukiwanie następuje w zdefiniowanym zakresie określonym przez parametr przeszukiwana_tablica. Parametr ten może być definiowany jako: liczba, tekst lub wartość logiczna a także stanowić odwołanie do innej komórki,

przeszukiwana_tablica - zakres komórek w których ma nastąpić przeszukanie,

typ_porównania - wartość opcjonalna, określająca sposób w jaki następuje porównanie. Dozwolone wartości to: 1; 0 oraz -1. Wartość domyślna tego argumentu to 1 co oznacza, że funkcja odszuka największą wartość, która jest mniejsza lub równa wartości szukana_wartość. Wartości argumentu przeszukiwana_tablica muszą być uporządkowane w kolejności rosnącej. Parametr: 0 oznacza wyszukiwanie do pierwszego dopasowania. W tym przypadku wartości w argumencie przeszukiwana_tablica mogą być umieszczone w kolejności dowolnej. Zaś parametr: -1 nakazuje odszukanie wartości najmniejszej, która jest większa lub równa wartości szukana_wartość. Wartości w argumencie przeszukiwana_tablica muszą być uporządkowane w kolejności malejącej.

W poniższym przykładzie w komórce V19 została wpisana formuła: =PODAJ.POZYCJĘ("b3";Q19:T19) Formuła szuka wartości: b3 w tablicy zdefiniowanej jako zakres komórek od Q19 do T19 (szary obszar). Wynikiem formuły jest wartość 3 gdyż szukana wartość znajduje się w trzeciej komórce.

W drugim przykładzie dzięki zastosowaniu formuły: =PODAJ.POZYCJĘ("b3";S18:S21) wynikiem jest liczba 2 gdyż w tablicy szukana wartość znajduje się w drugiej komórce.

OK to już wiemy jak działają funkcje INDEKS() oraz PODAJ.POZYCJĘ() pora więc wrócić do naszego przykładu z częściami i zdobytą wiedzę wykorzystać w działaniu - przyszła pora aby obie funkcje ze sobą połączyć.

W komórce H50 znajduje się formuła: =INDEKS(F34:L43;PODAJ.POZYCJĘ(H48;G34:G43;0);PODAJ.POZYCJĘ(H49;F33:L33;0)) Wygląda skomplikowanie ale rozbijemy ją na części pierwsze i wszystko stanie się jasne.

Na początek co tak naprawdę formuła wykonuje - zadaniem jej jest podanie (tak jak w przykładzie wyżej) odszukanie ilości sztuk sprzedanego produktu w danym roku (ponownie rok 2013 i nakrętka m10). Lecz tym razem nie wpisujemy numeru wiersza i kolumny tylko numer danej części (komórka H48) oraz rok (komórka H49).

Tak więc do komórki H48 została wpisana wartość: 3433 gdyż jest to numer przydzielony produktowi nakrętka m10 oraz do komórki H49 wartość: 2013

Aby odnaleźć sprzedaną liczbę sztuk detalu do funkcji INDEKS() musimy przekazać wartość parametru: nr_wiersza oraz nr_kolumny Oba argumenty są uzyskiwane dzięki formule w której została wykorzystana funkcja PODAJ.POZYCJĘ().

Pierwsza formuła: PODAJ.POZYCJĘ(H48;G34:G43;0) powoduje odszukanie pozycji wartości: 3433 w tablicy, której zakres został zdefiniowany od komórki G34 do G43. Typ dopasowania został ustalony na 0 (choć tak naprawdę można by było parametr ten pominąć i przyjąć wartość domyślną gdyż numery części są posortowane w kolejności rosnącej). Numer części jest wartością unikalną (zakładamy, że numer części jest zawsze przypisany tylko jednemu produktowi). Efektem działania tej formuł jest uzyskanie wartości: 6 (wartość w szóstym wierszu tabeli).

Druga część formuły: PODAJ.POZYCJĘ(H49;F33:L33;0) realizuje podobne zadanie lecz w kontekście ustalenia numeru kolumny w której występuje data roku czyli wartość: 2013 Formuła zwraca wartość: 5

Wykorzystane formuły przekazują wartości do funkcji INDEKS(). Tak więc naprawdę, uproszczając całą formułę jest realizowana funkcja: =INDEKS(F34:L43;6;5) Efektem jej jest uzyskanie wartości: 4351 leżącej w szóstym wierszu i piątej kolumnie w tablicy określonej poprzez zakres F34:L43.

Mam nadzieję, że teraz wszystko wydaje się prostsze.

Oprócz funkcji PODAJ.POZYCJĘ istnieje jeszcze funkcja POZYCJA() a zadaniem tej funkcji jest zwrócenie pozycji zdefiniowanej liczby w odniesieniu do listy wartości. Mówiąc bardziej obrazowo gdybyśmy wykonali operację sortowania listy, pozycja liczby oznaczałaby jej miejsce na liście po sortowaniu.

Ogólna składnia funkcji wygląda następująco: POZYCJA(liczba;lista;[lp])

W składni funkcji POZYCJA() występują następujące argumenty:

liczba - liczba (bądź adres komórki w której została ona wpisana), której pozycja ma zostać określona,

lista - tablica albo odwołanie do listy liczb (wartości nie liczbowe zostaną zignorowane).

lp - parametr opcjonalny - argument określający sposób określania pozycji liczby. Jeżeli argument lp jest równy 0 lub został pominięty, pozycja liczby jest ustalana w oparciu o listę sortowaną w kolejności malejącej. Jeżeli zaś parametr przyjmie wartość 1 stosowane jest sortowanie w kolejności rosnącej.

Poniżej trzy przykłady użycia funkcji POZYCJA()

Przykład 1: formuła: =POZYCJA(2143;E4:E9) - naszym zadaniem jest określenie pozycji wyniku Tadeusza Nowaka zakładając, że wygrywa osoba z większą ilością punktów. Wpisując formułę jej wynik zostaje ustalony na 3 - wartość 2143 przy sortowaniu malejącym w zdefiniowanej liście liczb (zakres komórek od E4 do E9) ma pozycję numer trzy.

Przykład 2: formuła: =POZYCJA(2143;E4:E9;1) - sytuacja podobna tylko z tą różnicą iż wygrywa osoba z mniejszą ilością punktów. Do formuły został dołączony parametr: lp nakazujący odnaleźć pozycję szukanej wartości ale z wykorzystaniem sortowania rosnącego. Wynikiem działania formuły jest wartość: 4.

Przykład 3: formuła: =POZYCJA(E15;E$15:E$20) - określenie zdobytego miejsca (wygrywa ten kto ma więcej punktów).

Ostatnimi funkcjami jakie chciałbym przedstawić są dwie funkcje WYSZUKAJ.PIONOWO() oraz WYSZUKAJ.POZIOMO()

Zadaniem funkcji WYSZUKAJ.PIONOWO() jest odnalezienie szukanej wartości w pierwszej kolumnie tabeli lecz zwrócenie odpowiadającej jej wartości z kolumny innej. Funkcja przeszukuje tabelę z góry na dół (stąd słowo PIONOWO w nazwie funkcji). Składnia funkcji WYSZUKAJ.PIONOWO() jest następująca: WYSZUKAJ.PIONOWO(szukana_wartość;tablica;nr_indeksu_kolumny;[przeszukiwany_zakres])

Kolejne argumenty funkcji WYSZUKAJ.PIONOWO() to:

szukana_wartość - odszukiwana wartość w pierwszej kolumnie tabeli. Szukana wartość powinna zawsze znajdować się w pierwszej kolumnie tablicy oznacza to, że gdy szukana wartość znajduje się w komórce B2, to tabela powinna rozpoczynać się od kolumny B,

tabela - zakres komórek określający tabelę,

nr_indeksu_kolumny - numer kolumny z której zostanie zwrócona wartość. Jeśli tabelą jest zakres C3: E20, kolumnę C liczymy jako pierwszą kolumnę, D jako drugą i tak dalej.

przeszukiwany_zakres - argument opcjonalny. Obejmuje ustawienie flagi PRAWDA - dopasowanie przybliżone (pierwsza wartość większa niż Szukana_wartość), lub flagi FAŁSZ - dopasowanie dokładne (brak dopasowania, wyświetli w wyniku błąd #N/D!). Jeśli parametr w definicji funkcji zostanie pominięty domyślnie przyjmowaną opcją jest PRAWDA, czyli dopasowanie przybliżone. Użycie flagi PRAWDA wymusza na Nas wykonanie sortowania w porządku rosnącym dla pierwszej kolumny przeszukiwanej tabeli.

To tradycyjnie już przykład (a nawet dwa).

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(K16;J3:N12;2) - użyta formuła zwraca wartość 39 - dzieje się tak ponieważ szukaną przez Nas wartością jest ta wpisana w komórkę K16 (czyli 5), tabela obejmuje zakres komórek od J3 do N12 (kolumna J jest liczona jako pierwsza) zaś parametr nr_indeksu_kolumny został określona jako 2. Mówiąc prościej realizowane jest zadanie zwrócenia wartości z drugiej kolumny w przypadku wystąpienia dopasowania. Ponieważ dla wartości szukanej w drugiej kolumnie znajduje się 39 toteż ta wartość jest wynikiem formuły.

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(K16;J3:N12;4) - przykład analogiczny z tym powyżej lecz tym razem zwracana jest wartość z kolumny 4 czyli 36

Kolejny przykład pokazuje nieprawidłowe działanie funkcji WYSZUKAJ.PIONOWO()

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(K37;J24:N33;3) - formuła ta nie działa tak jakbyśmy tego oczekiwali gdyż dla szukanej wartości (6) zwróconą wartością jest 53 a po analizie Naszej tablicy danych dochodzimy, że powinna to być wartość 61. Formuła ta nie działa ponieważ parametr nr_indeksu_kolumny nie został zdefiniowany czyli została przyjęta wartość domyślna (PRAWDA) a pierwsza kolumna naszej tablicy nie jest posortowana w kolejności rosnącej.

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(K37;J24:N33;3;FAŁSZ) - tym razem zwrócona wartość jest prawidłowa gdyż zastosowanie flagi FAŁSZ dla parametru nr_indeksu_kolumny włącza dopasowanie dokładne (pierwsza kolumna nie musi podlegać sortowaniu).

No to ostatni przykład.

Przykład 1: - formuła: =WYSZUKAJ.PIONOWO(C34;B25:D30;2;FAŁSZ) - dysponujemy listą pracowników i zadaniem jest odszukanie numeru ID pracownika na podstawie wpisanego nazwiska i imienia (komórka C34). Wynikiem działania formuły jest wyświetlenie ID pracownika. Została użyta flaga FAŁSZ gdyż dane w kolumnie pierwszej nie są posortowane.

Przykład 2: - formuła: =WYSZUKAJ.PIONOWO(C34;B25:D30;3;FAŁSZ) - przykład analogiczny do powyższego lecz tym razem szukaną informacją jest numer PESEL pracownika.

Poniżej powielenie obu przykładów i jak widać funkcja WYSZUKAJ.PIONOWO() nie rozróżnia wielkości liter.

Jeszcze na chwilę zatrzymajmy się przy tej funkcji gdyż osobnego omówienia wymaga sytuacja w której w przeszukiwanej tablicy pojawi się pusta komórka. Tak jak to ma miejsce w poniższym przykładzie. Jak można zauważyć przy pracowniku Gazda Beata brak jest numeru PESEL. Formuła wpisana w komórkę C58 (=WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ)) zwraca wartość 0.

Dzieje się tak ponieważ Excel traktuje puste komórki w obszarze wyników jako zera. Uniknięcia tego typu przypadku pozwoli nam formuła, która wygląda następująco: =JEŻELI(WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ)="";"BRAK NUMERU PESEL";(WYSZUKAJ.PIONOWO(C57;B48:D53;3;FAŁSZ))) Jak można zauważyć problem rozwiązuje funkcja JEŻELI() - jeżeli brak jest numeru PESEL (czyli komórka jest pusta) zostaje wypisany stosowny komunikat (sytuacja w przypadku pracownika Gazda Beata) jeśli numer PESEL został wpisany zostaje on wyświetlony (pracownik Nowak Tadeusz).

Choć w tym zadaniu można pokusić się o dodatkowe poprawienie formuły. Bo co w przypadku w którym numer PESEL jest wpisany tylko na wskutek pomyłki została pominięta np. jedna cyfra? Na szczęście błąd ten (jak to ma miejsce w przypadku pracownika Polak Michał - numer PESEL liczy 10 cyfr a nie 11) łatwo jest zweryfikować. Wystarczy, że dodamy jeszcze jedną funkcję JEŻELI(), której zadaniem będzie sprawdzenie poprawności numeru (a raczej ilości cyfr z których się on składa). Formuła, która zrealizuje zadanie po modyfikacji z tą przedstawioną poprzednio przyjmie postać: =JEŻELI(WYSZUKAJ.PIONOWO(C82;B67:D72;3;FAŁSZ)="";"BRAK NUMERU PESEL";JEŻELI(DŁ(D71)=11;WYSZUKAJ.PIONOWO(C82;B67:D72;3;FAŁSZ);"BŁĄD PESEL")) W przypadku wystąpienia błędu w numerze PESEL zostanie wyświetlony komunikat: BŁĄD PESEL

Bliźniaczą funkcją jest funkcja: WYSZUKAJ.POZIOMO() Działa ona na tych samych zasadach co WYSZUKAJ.PIONOWO() lecz przeszukiwanie tabeli nie odbywa się pionowo lecz poziomo - wyszukiwana wartość umiejscowiono jest w pierwszym wierszu tabeli a zwracana jest odpowiadająca jej wartość z ustalonego wiersza.

Składnia funkcji jest następująca: WYSZUKAJ.POZIOMO(odniesienie;tablica;nr_wiersza;[wiersz])

Kolejne argumenty funkcji WYSZUKAJ.POZIOMO() to:

odniesienie - odszukiwana wartość w pierwszej kolumnie tabeli. Szukana wartość powinna zawsze znajdować się w pierwszym wierszu tablicy - oznacza to, że gdy szukana wartość znajduje się w komórce B2, to tabela powinna rozpoczynać się od wiersza 2,

tablica - zakres komórek określający tabelę,

nr_wiersza - numer wiersza z którego zostanie zwrócona wartość. Jeśli tabelą jest zakres C3: E20, wiersz 3 liczymy jako wiersz pierwszy, 4 jako drugi i tak dalej.

wiersz - argument opcjonalny. Obejmuje ustawienie flagi PRAWDA - dopasowanie przybliżone (pierwsza wartość większa niż odniesienie), lub flagi FAŁSZ - dopasowanie dokładne (brak dopasowania, wyświetli w wyniku błąd #N/D!). Jeśli parametr w definicji funkcji zostanie pominięty domyślnie przyjmowaną opcją jest PRAWDA, czyli dopasowanie przybliżone. Użycie flagi PRAWDA wymusza na Nas wykonanie sortowania w porządku rosnącym dla pierwszego wiersza przeszukiwanej tabeli.

I podobnie jak w przypadku funkcji WYSZUKAJ.PIONOWO() teksty pisane dużymi i małymi literami są równoważne.

No to zilustrujmy działanie tej funkcji.

Przykład 1: - formuła: =WYSZUKAJ.POZIOMO(S16;R2:V12;6) - formuła dla szukanej wartości wpisanej w komórkę S16 (Dane 2) zwraca wynik 21 gdyż w przeszukiwanej tablicy liczba ta znajduje się w szóstym wierszu.

Przykład 2: - formuła: =WYSZUKAJ.POZIOMO(S16;Q2:V12;3) - podobnie jak w przykładzie powyżej lecz w tym przypadku zwracana jest liczba znajdująca się w wierszu 3.

To przeanalizujmy jeszcze jeden przykład dotyczący funkcji WYSZUKAJ.POZIOMO(). Mamy listę uczniów, którym należy wystawić ocenę w zależności od ilości zdobytych punktów. Kryterium przydzielania oceny wygląda następująco:

0-24 pkt - ocena: 1
25-44 pkt - ocena: 2
45-69 pkt - ocena: 3
70-89 pkt - ocena: 4
90-97 pkt - ocena: 5
98-100 pkt - ocena: 6

Formuła, która zrealizuje nasze zadanie przyjmie postać: =WYSZUKAJ.POZIOMO(T28;$S34:$Y35;2) (komórka T29) Wartość ilości zdobytych punktów przez danego ucznia jest porównywana z tabelą punktacji (zakres komórek od S34 do Y35). Po dokonaniu porównania do danej liczby punktów przypisywana jest ocena.

Te samą operację wykonamy wpisując tabelę w formułę (zamiast funkcji WYSZUKAJ.POZIOMO() stosujemy WYSZUKAJ.PIONOWO()): =WYSZUKAJ.PIONOWO(T28;{0\"1";25\"2";45\"3";70\"4";90\"5";98\"6"};2) - więcej o definicji tabel znajdziesz w wpisie: Excel w zadaniach. Funkcje tablicowe

Ewentualnie to samo zadanie możemy zrealizować z wykorzystaniem funkcji WYSZUKAJ() Formuła realizująca te zadanie przyjmie postać: =WYSZUKAJ(T40;{0;25;45;70;90;98};{1;2;3;4;5;6})

Funkcji WYSZUKAJ() jeszcze nie omówiłem więc wypadałoby kilka słów o niej powiedzieć.

Funkcja WYSZUKAJ() działa w ten sposób, że przeszukuje zakres w postaci jednego wiersza lub jednej kolumny wartości i zwraca wartość z tej samej pozycji w drugim obszarze.

Składnia funkcji WYSZUKAJ() jest następująca:

WYSZUKAJ(Szukana_wartość;Przeszukiwany_wektor;Wektor_wynikowy)

Argumenty funkcji:

szukana_wartość - wartość szukana, która ma zostać odnaleziona w zakresie określonym w argumencie przeszukiwany_wektor.

przeszukiwany_wektor - pojedynczy wiersz lub kolumna, w którym znajdują się wartości podlegające procesowi przeszukania. Wartości te należy ułożyć w porządku rosnącym.

wektor_wynikowy - pojedynczy wiersz lub kolumna zawierająca wartości, które zostaną zwrócone. Rozmiar zdefiniowanego obszaru musi być tożsamy z tym ustalonym przy definicji parametru przeszukiwany_wektor.

Poniżej przykład z wykorzystaniem funkcji WYSZUKAJ(). Założenia ćwiczenia są takie iż na podstawie wpisanej liczby nadgodzin należy przeszeregować odpowiednią wartość przyznanej premii. Liczby nadgodzin i odpowiadający im procent premii zostały zebrane w tabeli. Formuła realizująca te zadanie ma postać: =WYSZUKAJ(C2;E2:E6;G2:G6)

Formuł ta wyświetla prawidłowe wartości przyznanej premii gdyż funkcja WYSZUKA() działa w ten sposób, że dopasowuje najbliższą najmniejszą znalezioną wartość.

I na tym etapie już kolejny mój wpis na temat Excela chciałbym zakończyć. Mam nadzieję, że przedstawione przykłady użycia funkcji tekstowych i wyszukujących pozwolą Wam je efektywnie wykorzystać do budowy swoich własnych arkuszy.

Excel w zadaniach. Tworzenie i modyfikacja wykresów.

2017-03-20T14:53:49+00:00

Wykres jak podaje definicja jest graficzną prezentacją danych oraz jest nieodzownym narzędziem programu Excel. Umiejętność tworzenia wykresów jak i ich modyfikacja jest jednym z kluczowych elementów jakie musimy przyswoić by móc sprawnie i efektywnie z arkuszem pracować. Tak więc artykuł ten, za zadanie ma, tą niezbędną wiedzę Ci przekazać. Nie przedłużając zapraszam do lektury i do odkrycia świata wykresów w narzędziu MS Excel.

Zanim zaczniemy dobrze jest uzmysłowić sobie i odpowiedzieć na pytanie - Po co w ogóle wykresy stosować? Aby udzielić odpowiedzi na to pytanie przeprowadźmy prosty eksperyment.

Spójrz na dane przedstawione poniżej i odpowiedz na pytanie - W którym miesiącu temperatura była najwyższa?

A teraz zadanie podobne pytanie z wykorzystaniem tego samego zestawu danych - W którym miesiącu temperatura była najniższa?

Jeśli Czytelniku odpowiedziałeś na te dwa proste pytania, zastanów się - W którym przypadku odpowiedź padła szybciej? Nie będzie tajemnicą, że pewnie w pytaniu w którym został użyty wykres. Dzieje się tak ponieważ o wiele łatwiej jest Nam pracować na danych, które zostały zobrazowane. Patrząc na wykres automatycznie udzielamy odpowiedzi pomijając etap zapoznawania się z danymi i ich analizowania jak to miało miejsce w przypadku „surowych” danych liczbowych.

I fakt ten jest odpowiedzią na pytanie - Po co stosować wykresy? Aby Nam było wygodniej. Dodatkowo przedstawiając dane w postaci wykresu łatwiej dostrzeżemy zależności pomiędzy nimi a także prawidłowości, nimi rządzące.

Wszystkie wykresy jakie zostały zaprezentowane w artykule można pobrać: tutaj

Mając już świadomość celowości stosowania wykresów zanim przejdziemy do ich tworzenia omówmy elementy z których wykres jest zbudowany.

Poniższy wykres przedstawia dwie serie danych: sprzedaż oraz marża i jest wykresem złożonym ponieważ do wizualizacji danych na nim zaprezentowanych użyto dwóch typów wykresów: marża jest przedstawiona w postaci wykresu liniowego zaś sprzedaż to wykres kolumnowy. Wartość sprzedaży została przedstawiona w postaci kolumn zaś wartości marży to linia z dodatkowo zdefiniowanymi znacznikami w kształcie trójkątów, które reprezentują wartości marży w danym miesiącu.

Dane, które posłużyły do zbudowania wykresu znajdują się w komórkach od B1 do D14 (wartość TOTAL na wykresie została pominięta).

Dodatkowo poniżej wykresu została umieszczona tabela danych oraz dla wartości sprzedaży została wyrysowana linia trendu.

Każdy wykres (no może prawie każdy) posiada oś poziomą nazywaną osią kategorii oraz oś pionową tzw. oś wartości. W przypadku powyższego wykresu na osi kategorii zostały umieszczone nazwy miesięcy. A co w przypadku osi wartości? Przedstawiony wykres posiada dwie osie pionowe. Powodem takiego stanu rzeczy jest użycie dwóch serii danych o różnych jednostkach (sprzedaż jest reprezentowana przez ilość sztuk zaś jednostką marży jest procent). Oś po lewej stronie przypisana jest wykresowi kolumnowemu (sprzedaż), natomiast oś prawa to wykres liniowy (marża).

Każda z osi została podpisana, skalowanie osi prawej obejmuje wartości od 0 do 1400, zaś osi lewej od 0% do 50% Aby łatwiej było wykres odczytywać na wykresie umieszczono linie podziału (są to tzw. główne linie podziału, ale także wyrysowano linie podziału pomocnicze). Linie podziału zostały włączone zarówno dla osi pionowej jak i poziomej.

Obszar wykresu został wyróżniony kolorem szarym.

Na dole wykresu została umieszczona legenda.

Tworząc bądź modyfikując wykres bardzo często Czytelniku będziesz musiał opisane elementy wykresu zaznaczać więc warto wiedzieć jak tą operację wykonać.

Excel oferuje nam trzy sposoby zaznaczenia:

za pomocą myszy,
z wykorzystaniem klawiatury,
przy użyciu kontrolki Elementy wykresu.

Najprostszym sposobem zaznaczenia elementu wykresu jest użycie myszki gdyż aby zaznaczyć dany element należy go po prostu kliknąć (najechanie na dany element spowoduje wyświetlenie jego nazwy).

Drugi sposób polega na użyciu klawiatury. Rozpoczynamy od kliknięcie na dowolny element a zmiana elementu następuje poprzez użycie strzałek góra dół w połączeniu z klawiszem Ctrl (choć zależy to od wersji pakietu gdyż w niektórych odsłonach narzędzia wystarcza użycie samych strzałek).

W przypadku wybrania serii danych, etykiet bądź legendy można dodatkowo wykorzystać strzałkę prawo lub lewo tak aby wybrać pojedynczy element.

Ostatni sposób wykorzystuje tzw. kontrolkę elementów wykresu bądź będąc bardziej precyzyjnym listę wszystkich elementów z których dany wykres został zbudowany. Listę tą znajdziemy w grupie Narzędzia wykresów na zakładce Formatowanie w sekcji Bieżące zaznaczenie (oczywiście w pierwszej kolejności musimy zaznaczyć wykres). Za jej pomocą po uaktywnieniu wykresu możemy wybrać pożądany jego element.

Jak już wiemy z jakich elementów wykres się składa i jak te elementy zaznaczyć omówmy dostępne typy wykresów.

Wykresy kolumnowe

Najczęściej stosowanym typem wykresu jest wykres kolumnowy. Dane w tym wykresie reprezentowane są poprzez kolumny a wysokość danej kolumny zależna jest od wartości danych. Na wykresie tym może być umiejcowionych wiele kolumn, różniących się pomiędzy sobą np. kolorem. Kolor kolumn odzwierciedla użycie wielu serii danych.

Wykres tego typu stosuje się do ukazania różnic między wartościami w ramach jednej serii a także do porównywania różnych serii danych.

Excel pozwala nam na zdefiniowanie siedmiu podtypów wykresów kolumnowych przy czym trzy podtypy zostały zarezerwowane dla widoku 2-W a pozostałe cztery dla widoku 3-W.

Na rysunku poniżej zaprezentowano przykład wykresu kolumnowego. Wykres przedstawia miesięczną sprzedaż trzech produktów (komputery stacjonarne, laptopy, tablety) - każdy produkt to pojedyncza seria danych. Dodatkowo zostały włączone etykiety danych (wartości nad kolumnami).

Oczywiście do zagadnienia można podejść trochę z innej strony i sprzedaż poszczególnych produktów przedstawić w postaci trzech grup w ramach, których każda kolumna będzie reprezentować dany miesiąc, tak jak to zostało zobrazowane na wykresie poniżej.

Wykresy słupkowe

Ten typ wykresu jest bardzo podobny do wykresu kolumnowego gdyż pokazuje serie danych (serii może być oczywiście kilka), która zamiast być przedstawiona w postaci pionowych kolumn jest ukazana jako poziome słupki (to tak jakbyśmy wykres kolumnowy obrócili o 90 stopni w prawo).

Przykład takiego wykresu przedstawiono na rysunku poniżej. Wykres przedstawia powierzchnię kontynentów, dodatkowo dla obszaru wykresu zostało zdefiniowane wypełnienie gradientowe a dla każdego z słupków został ustalony odrębny kolor.

W Excelu dostępnych jest sześć podtypów wykresów słupkowych obejmujących widok 2-W, 3-W oraz dane skumulowane. Wykres tego typu najczęściej stosowany jest przy analizie ankiet do zobrazowania ilości udzielonych odpowiedzi na dane pytanie.

Wykresy liniowe

Wykresy tego typu używa się podczas prezentacji wartości, które mają charakter danych ciągłych np. ilość sprzedanego towaru czy uzyskanego przychodu.

W wykresie liniowym poszczególne serie danych różnią się od siebie kolorem użytych linii oraz typem zdefiniowanych znaczników zaś wykres ten może obejmować dowolną liczbę serii.

Wykres liniowy z dwiema seriami danych został zaprezentowany poniżej. Na wykresie zostały umieszczone dane przedstawiające dzienną liczbę odwiedzin fikcyjnej strony internetowej (okres odwiedzin obejmuje część roku 2015 oraz 2016). Zastosowane linie różnią się między sobą kolorem ale również typem użytych znaczników (kwadrat oraz romb).

Wykresy kołowe

Wykresy kołowe obejmują tylko jedną serię danych gdyż w głównej mierze są one wykorzystywane do tego by ukazać proporcje pomiędzy przedstawianymi danymi.

Na rysunku poniżej został zaprezentowany przykładowy wykres kołowy (a nawet dwa), ten po lewej w widoku dwuwymiarowym zaś po prawej w widoku trójwymiarowym. Wykres przedstawia ilości komputerów w zależności od zainstalowanego na nich systemu operacyjnego.

Wykres kołowy w połączeniu z wykresem słupkowym (tzw. wykres słupkowy kołowego) pozwala Nam na przedstawienie wybranego wycinka koła w postaci wykresu słupkowego - tak jak to zostało pokazane poniżej. Do danych z poprzedniego przykładu zostały dodane maszyny pracujące pod kontrolą systemu Linux w rozbiciu na poszczególne dystrybucje (ich liczba została wyświetlona na wykresie słupkowym). Dodatkowo obszar wykresu został wypełniony deseniem.

Pewną odmianą wykresu kołowego jest wykres pierścieniowy, który podobnie jak wykres kołowy pokazuje zachodzące relacje między elementami stanowiącymi całość, lecz w odróżnieniu od niego wykres pierścieniowy może zawierać więcej niż jedną serię danych. Na wykresie poniżej zostały ukazane dwie serie danych - pierwsza z nich ukazuje liczbę ludności na poszczególnych kontynentach (pierścień zewnętrzny) zaś seria druga to powierzchnia kontynentów (pierścień wewnętrzny).

Wykresy XY (punktowe)

Ten typ wykresu różni się od pozostałych tym, że brakuje w nim osi kategorii oznacza to, że obie osie reprezentują wartości danych. Dlatego też świetnie nadaje się on do reprezentacji danych pomiędzy, którymi zachodzi zależność np. wykresy funkcji. Tak jak to zostało ukazane na poniższym wykresie na którym to została wyrysowana funkcja: y=2sin(x)

Wykresy warstwowe

Wykres warstwowy można porównać do wykresu liniowego lecz w odróżnieniu od niego obszar, który znajduje się poniżej linii danych jest wypełniony kolorem.

Poniżej przykład wykresu warstwowego przedstawiający sprzedaż elektroniki w poszczególnych kwartałach.

W przypadku wykresów warstwowych do dyspozycji mamy wykresy dwu oraz trzy wymiarowe wraz z opcją skumulowania wartości. Przykład wykresu warstwowego trójwymiarowego (te same wartości co w wykresie powyżej) został ukazany niżej.

Wykresy radarowe

Wykres tego rodzaju charakteryzuje się tym, że dla każdej kategorii została zarezerwowana oddzielna oś. Osie te mają punkt wspólny w środku wykresu z którego rozchodzą się na zewnątrz.

Przykładem wykresu radarowego może być ten zaprezentowany poniżej - wykres przedstawia średnią miesięczną temperatur w okresie jednego roku.

Wykresy powierzchniowe

Do zbudowania tego typu wykresu należy posłużyć się co najmniej dwiema seriami. W wykresach opisanych powyżej użyte kolory służyły do odróżnienia poszczególnych serii danych zaś w tym wykresie użyty kolor reprezentują wartości. Liczba kolorów, która jest prezentowana na wykresie zależy od definicji jednostki osi wartości (użyty kolor przypisany jest do jednostki).

Wykresy bąbelkowe

Wykresy bąbelkowe są ciekawą odmianą wykresu gdyż za pomocą ich na pojedynczym wykresie można przedstawić dane, które obejmują trzy serie - dwie serie dotyczą usytuowania bąbelka względem osi XY zaś trzecia seria przypisana jest do wielkości bąbelka. W przypadku tego typu wykresu brak jest osi kategorii - osie XY są osiami wartości.

Poniżej na rysunku został pokazany przykładowy wykres bąbelkowy. Usytuowanie bąbla względem osi daje nam informację o powierzchni danego kraju (oś x) oraz zaludnieniu (oś y) zaś wielkość bąbla to gęstość zaludnienia.

Wykresy giełdowe

Wykresy tego typu najczęściej służą do zobrazowania danych dotyczących informacji giełdowych. Wykres giełdowy wymaga od 3 do 5 serii danych (liczba serii zależna od wybranego typu wykresu giełdowego).

Na rysunku poniżej zaprezentowano przykład takiego wykresu do ukazania danych nie związanych z giełdą ale do bardziej przyziemnej rzeczy jaką jest dzienna temperatura w miesiącu lipcu. Z wykresu możemy odczytać najniższą, najwyższą temperaturę zanotowaną w ciągu dnia oraz wyliczoną średnią.

W przypadku wykresów w programie MS Excel do czynienia będziemy mieli z wykresami osadzonymi oraz arkuszem wykresów. Podział ten uwzględnia lokalizację wykresu. Wykres osadzony znajduje się razem z danymi w arkuszu natomiast arkusz wykresu jest osobnym arkuszem w którym znajduje się tylko wykres.

Znamy już podstawowe typy wykresów czas zatem przejść do utworzenia pierwszego wykresu (w końcu).

Nie jest tajemnicą, że aby wykres utworzyć potrzebujemy danych liczbowych dlatego też skorzystamy z tych jakie posłużyły Nam do ukazania wykresu kolumnowego (dla przypomnienia dane te dotyczą sprzedaży sprzętu elektronicznego).

Dobrą praktyką podczas budowania wykresu jest wyrobienie w sobie nawyku opisywania danych. Czynność ta sprowadza się do umieszczenia stosownych informacji w wierszu bądź kolumnie nad (bądź obok) serią gromadzonych danych. Wykonanie tej prostej czynności spowoduje umieszczenie tych informacji na tworzonym wykresie. Aby to zobrazować prześledźmy dwie sytuacje. W pierwszej użytkownik zapomniał o opisaniu danych (rysunek po lewej) w drugiej zaś dane zostały opisane (rysunek po prawej).

Na podstawie tych informacji zostały utworzone dwa wykresy kolumnowe.

Jak widać powyżej w przypadku opisania danych informacje te są zawarte na wykresie. Oczywiście wykres po lewej można poprawić ale zmusza to Nas do ręcznego wprowadzenia tych informacji. Czynność ta jednak wymaga wykonania większej liczby operacji (czytaj: trzeba się więcej naklikać) niż opisanie danych.

Przechodzimy zatem do utworzenia wykresu. Dane do arkusza zostały wprowadzone, kolejną czynnością jest ich zaznaczenie (punkt 1). Po zaznaczeniu danych, które na wykresie mają być umieszczone należy na wstążce wybrać kartę Wstawianie (punkt 2). Na karcie tej odszukujemy sekcję Wykresy z której to wybieramy interesujący Nas typ wykresu (punkt 3). Wybór typu wykresu spowoduje osadzanie go w arkuszu (punkt 4).

Gratulacje!!! Właśnie wstawiłeś swój pierwszy wykres.

Jeśli Czytelniku masz problem z określeniem typu wykresu zainteresuj się ikoną: Polecane wykresy Wybór ikony spowoduje wyświetlenie nowego okna w którym to program przedstawi Ci kilka wariantów wizualizacji zaznaczonych danych. Te najpopularniejsze znajdziesz na zakładce Polecane wykresy, gdy te zaproponowane Cię nie zadowalają lub gdy zależy Ci na wykresie nietypowym wybierz zakładkę Wszystkie wykresy.

Wykres do arkusza został wstawiony. Efekt naszych dotychczasowych prac może wydać się Nam nie do końca satysfakcjonujący dlatego też by uzyskać wykres, który spełnia nasze oczekiwania możemy posłużyć się szeregiem opcji, których wybranie pozwoli Nam na dostosowanie wyglądu oraz układu wykresu.

Na szybką zmianę układu pozwoli nam opcja Szybki układ, która dostępna jest na karcie Projektowanie. Wybranie ikony spowoduje zaproponowanie przez program szeregu układów tworzonego wykresu. Zmianie podlegają takie opcje jak tytuły osi, legenda, etykieta danych czy tytuł wykresu.

Podobnie jak inne obiekty umiejscowione w arkuszu takie jak np. kształt czy zdjęcie tak i w przypadku wykresów możliwa jest zmiana jego położenia czy rozmiarów. Aby zmienić parametry wykresu należy go uaktywnić, po zaznaczeniu wykresu na wstążce pojawią się nowe karty na których zostały zgrupowane opcje dotyczące formatowania wykresów.

W przypadku pakietu MSOffice 20016 będą to dwie karty: Projektowanie i Formatowanie

zaś w przypadku MSOffice 2010 trzy: Projektowanie, Układ oraz Formatowanie

Powodem tej różnicy jest zmiana podejścia producenta oprogramowania w MSOffice 2016 do edycji elementów (obrazy, wykresy, kształty, SmartArt, WordArt itd.) w programach tworzących całych pakiet. W nowych odsłonach Office zostały dodane dodatkowe przyciski dostępne po uaktywnieniu danego obiektu. Ilość dostępnych opcji zależna jest od typu obiektu. Poniżej przedstawiono przykład edycji wykresu. Kliknięcie wykresu spowoduje wyświetlenie trzech dodatkowych ikon: znaczek plus odpowiedzialny za dodanie/usunięcie elementów wykresu (np. tytuły osi, legenda itd.); pędzel - definicja stylu wykresu oraz określenie kolorów; lejek - filtrowanie danych.

Dlatego też w Office 2016 brak jest karty Układ gdyż opcje definiujące wykres zmieniły swoją lokalizację - są dostępne po wybraniu ikony ze znakiem plus.

Aby zmienić rozmiar wykresu należy go uaktywnić i po wybraniu jednej z kontrolek dostępnych na rogach obiektu ale również na jego bokach ustalić pożądany rozmiar. Dokładne ustalenie rozmiaru dostępne jest również na karcie Formatowanie w sekcji Rozmiar

Aby przenieś wykres w obrębie arkusza należy kliknąć na niego w obszarze obszaru wykresu i przeciągnąć go w docelowe miejsce (chwytamy za ramkę). Można również użyć kombinacji klawiszy Ctrl+X (wytnij) a następnie Ctrl+V (wklej). Aby wykres osadzony przenieś do arkusza wykresów (ale również odwrotnie) należy dany wykres uaktywnić a następnie wybrać ikonę Przenieś wykres, która dostępna jest na karcie Projektowanie w sekcji Lokalizacja W nowo otwartym oknie określamy docelowe miejsce wykresu. Opcja ta jest dostępna również w menu kontekstowym wykresu.

Wybranie opcji Nowy arkusz spowoduje umieszczenie wykresu w nowym arkuszu natomiast opcja Obiekt w pozwala na ręczne zdefiniowania arkusza w którym przenoszony wykres ma być umiejscowiony.

Aby wykres skopiować możemy posłużyć się kombinacją klawiszy Ctrl+C (kopiuj) a następnie Ctrl+V (wklej). Opcjonalnie kopiowanie wykresu dokonamy poprzez zaznaczenie wykresu a następnie wybraniu i przytrzymaniu klawisza Ctrl i ponownym kliknięciu na ramkę wykresu (kursor zmieni swój kształt) i przeciągnięciu wykresu przy użyciu PPM. W otwartym menu kontekstowym możemy wybrać rodzaj przeprowadzanej operacji (kopiowanie bądź przenoszenie).

Aby przenieś bądź skopiować arkusz wykresu należy kliknąć na dany arkusz PPM i z menu kontekstowego wybrać opcję Przenieś lub kopiuj.

Aby usunąć wykres należy kliknąć na niego z przytrzymanym klawiszem Ctrl (użycie Ctrl spowoduje zaznaczenie całego wykresu niezależnie w którym miejscu wykresu klikniemy) i naciśnięciu klawisza Delete. Aby zaś usunąć arkusz wykresu z menu kontekstowego wybieramy opcję Usuń.

Jak już wiemy cały wykres składa się z wielu elementów takich jak np. etykiety, legenda czy opisy osi. Aby dany element przenieś bądź usunąć należy go zaznaczyć. Aby dokonać zmiany położenia klikamy na ramkę elementu i przesuwamy go w docelowe miejsce. Zaś by element usunąć korzystamy z klawisza Delete.

I tu należy się mała uwaga gdyż zdarza się że dany element składa się z wielu obiektów. Przykładem takiego elementu są np. etykiety. Jeśli zdecydujemy się na umieszczenie w tworzonym wykresie etykiet wartości danych to okaże się że do każdej reprezentowanej wartości zostanie przypisana jedna etykieta. Aby zamienić położenie pojedynczej etykiety (bądź ją usunąć) należy na nią kliknąć dwukrotnie - pojedyncze kliknięcie zaznacza cały element czyli wszystkie etykiety a ponowne użycie LPM uaktywnia tylko żądaną etykietę.

Jak już zaznaczyłem od wersji MSOffice 2013 zmieniło się podejście dotyczące formatowania obiektów w programach wchodzących w skład pakietu. Do tej pory (Office 2007 oraz Office 2010) wszystkie opcje były zgrupowane na wstążce programu. W nowszych odsłonach oprogramowania opcje te zostały przeniesione na dodatkowy boczny pasek, który wyświetla się po zaznaczeniu danego elementu obiektu. Poniżej na rysunku zostało pokazane okno formatowania odnoszące się do elementu: osi wykresu. Dostępne opcje w oknie zadań zależne są od formatowanego elementu. Położenie okna możemy ustalić sami.

W górnej części okna zostały umieszczone dwie zakładki: Opcje osi oraz Opcje tekstu. W tym przykładowym oknie zadań po wybraniu zakładki Opcje osi zostaną wyświetlone cztery ikony (w kolejności): Wypełnienie i linia, Efekty Rozmiar i właściwości oraz Opcje osi. Kliknięcie na daną ikonę da Nam dostęp do kolejnych ustawień.

Ustawień tych wszystkich nie sposób opisać gdyż jest ich naprawdę wiele (by nie powiedzieć, że ogrom) dlatego zachęcam cię Czytelniku abyś opcje te przetestował Sam. W większości przypadków zaproponowane ustawienia przez program są wystarczające jeśli zaś potrzebujemy coś więcej to do tych ustawień należy sięgnąć.

Najważniejszymi opcjami i tymi z których chyba najczęściej się korzysta są te związane z formatowaniem osi. Podczas tworzenia wykresu Excel tak dobiera skalę wykresu by wszystkie wartości na podstawie, których wykres jest budowany na nim się znalazły. Lecz często dochodzi do sytuacji w której musimy wprowadzić pewne poprawki. Przyjrzyjmy się wykresowi przedstawionemu na poniższym rysunku (wykres liniowy przedstawia sprzedaż komputerów stacjonarnych i laptopów).

Jak widać powyżej część obszaru wykresu jest zmarnowana gdyż wartości osi Y zostały ustalone na przedział od 0 do 900. Tak naprawdę oś ta by mogła zaczynać się od 500 ponieważ żadna zmienna nie przyjmuje wartości poniżej tej liczby. Spróbujmy zatem dokonać korekty.

W pierwszym kroku klikamy na oś Y wykresu (punkt 1) a następnie z menu Formatowania osi wybieramy ikonę Opcje osi (punkt 2) i z dostępnych gałęzi rozwijamy tę nazwaną jako Opcje osi (punkt 3). Rozwinięcie gałęzi spowoduje wyświetlenie opcji związanych z granicami osi.

Aby skorygować wykres w polu Minimum wpisujemy 500. Zmiana wartości spowodowała, że oś Y rozpoczyna się nie od 0 ale od 500

Niezbyt satysfakcjonujące może być dla Nas ustawienie związane z jednostkami. Teraz gdy nasz obszar wykresu uległ powiększeniu możemy pokusić się o zmianę dokładności wykresu. Oś Y skalowana jest co 50 aby uzyskać trochę większą dokładność zmieńmy tę wartość na 20. Zmiany dokonujemy poprzez wpisanie wartości w pole jednostki głównej.

Definicja jednostki pomocniczej oraz wyświetlenie linii osi pomocniczej dodatkowo zwiększy dokładność całego wykresu. Aby włączyć wyświetlanie linii pomocniczych należy kliknąć na wykres i po wyświetleniu ikon związanych z opcjami wykresu wybrać tę ze znaczkiem plus. Ikona ta odpowiedzialna jest za dodawanie/usuwanie elementów wykresu (w przypadku starszych odsłon pakietu Office stosowne opcje znajdziesz na wstążce - karta Układ). Po wyborze ikony z menu wybieramy Linie siatki a następnie Podstawowe pomocnicze poziome. Efekt finalny został przedstawiony poniżej (dodatkowo zostały włączone linie siatki: podstawowe główne pionowe a wartość jednostki pomocniczej dla osi Y została ustalona na 5).

Gdy w szczególności zależy Nam na przedstawieniu wartości prezentowanych danych oraz by odciążyć czytających wykres od samodzielnego ich określania możemy zdecydować się na włączenie etykiet danych. Etykiety te umieścimy na wykresie po wyborze ikony plus i wybraniu opcji Etykiety danych (opcjonalnie możemy określić ich umiejscowienie oraz format).

Inną opcją (którą ja najczęściej stosuję, zamiast etykiet danych) jest włączenie Tabeli danych (opcja dostępna również pod ikoną plus). Włączenie tabeli spowoduje umieszczenie stosownych informacji w dolnej części wykresu.

A co w przypadku gdy do tworzonego wykresu chcemy dodać nową serię danych? Wyjścia z tej sytuacji mamy dwa albo tworzymy nowy wykres lub do już istniejącego dodajemy nowe informacje.

Aby dodać serię danych należy w obszarze wykresu kliknąć PPM i z menu wybrać opcję Zaznacz dane (opcja również dostępna jest pod ikoną lejka tzw. filtry wykresu po jej wybraniu należy kliknąć na Wybierz dane).

Do naszego testowego wykresu dodamy serię danych obrazującą roczną sprzedaż tabletów. Aby tego dokonać w oknie Wybieranie źródła danych klikamy na przycisk Dodaj.

W nowo otwartym oknie Edytowanie serii określamy Nazwę serii - możemy wpisać ją sami bądź wybrać adres komórki w której to nazwa ta jest umieszczona (punkt 1) oraz w polu Wartości serii wprowadzamy wartości, które zostaną naniesione na wykres (wartości wybieramy poprzez wybranie stosownych komórek bądź określamy je ręcznie wpisując stosowne liczby, które od siebie oddzielamy średnikiem) - punkt 2. Całość dwukrotnie zatwierdzamy przyciskiem OK.

Innym sposobem dodania nowych danych do wykresu jest przeciągnięcie ramki zakresu. Metoda ta świetnie sprawdza się wtedy gdy dodawane dane przylegają do tych już znajdujących się na wykresie. Aby skorzystać z tej metody zaczynamy od kliknięcia na wykres do którego chcemy dodać nową serię danych. Po wybraniu wykresu, Excel zaznaczy wszystkie dane, które już na wykresie się znajdują, nasza praca sprowadza się do rozszerzenia ramki, którą te dane są otoczone. Ramkę rozciągamy tak aby swym zasięgiem objęła dane, które do wykresu mają być dołączone. Myk ten nie uda się w przypadku wykresów umieszczonych w osobnym arkuszu.

Dodanie nowej serii możemy również zrealizować poprzez wykorzystanie kopiowania i wklejania. Wystarczy tylko skopiować dane, które na wykresie mają być umieszczone a następnie wybrać wykres i je wkleić.

Podczas tworzenia wykresów możemy natknąć się na problem związany z zbyt długimi opisami serii. Excel będzie sam starał się ten problem rozwiązać poprzez odpowiedni dobór wielkości czcionki czy obrót tekstu ale może okazać się to niewystarczające (wszystko zależy od wielkości wykresu).

W naszym wykresie można by było pokusić się o skrócenie nazw miesięcy umieszczonych na osi X do np. trzech znaków. Aby takową zmianę dokonać ponownie wywołujemy okno Wybieranie źródła danych i w sekcji Etykiety osi poziomej (kategorii) klikamy na przycisk Edytuj (punkt 1). W nowo otwartym oknie Etykiety osi zaznaczamy komórki w której zawarte są etykiety (opcjonalnie możemy zdefiniować je ręcznie tak jak w przypadku dodawania nowej serii danych) - punkt 2 i całość zatwierdzamy dwukrotnie przyciskiem OK.

Nowe etykiety powinny zostać umieszczone na wykresie.

W niektórych sytuacjach dobrym wyjściem jest zaprezentowanie danych za pomocą odrębnych typów wykresów (tzw. wykres słożony). Oznacza to, że do danej serii danych możemy przypisać określony typ wykresu.

Zmodyfikujmy nasz wykres i dodaną przed chwilą serię danych dotyczącą sprzedaży tabletów przedstawmy w postaci kolumn. Aby zmienić typ wykresu dla określonej serii danych w pierwszym kroku należy wybrać ją i z menu kontekstowego bądź wstążki wskazać opcję Zmień typ wykresu (w poprzednich wersjach Excela postępujemy analogicznie z wyjątkiem wersji 2003 gdzie ten typ wykresu - kolumnowo-liniowy znajdziemy w typach wykresu; nie zaznaczenie serii spowoduje zmianę wyglądu dla wszystkich serii danych).

Po wybraniu opcji w oknie Zmienianie typu wykresu w sekcji Wybierz typ wykresu i oś dla serii danych przypisujemy typ wykresu do serii danych (w naszym przypadku do serii tablety został przypisany wykres kolumnowy).

Nasze serie danych opisuje ta sama jednostka dlatego też opcjonalna opcja Oś pomocnicza nie została uaktywniona. Całość oczywiście zatwierdzamy przyciskiem OK.

Gdy Czytelniku będziesz na wspólnym wykresie przedstawiał dane, które opisują odrębne jednostki wykorzystaj możliwość dodania dodatkowej osi wartości tak jak to zostało pokazane na rysunku poniżej.

Kolumny ukazują średnią miesięczną temperaturę (w stopniach Celsjusza) natomiast linia - średnią miesięczną opadów (w mm). Ponieważ obie serie danych są opisywane poprzez różne jednostki oś pomocnicza do wykresu została dodana - lewa zarezerwowana jest dla temperatury, prawa zaś dla opadów.

Bezpośrednie tworzenie tego typów wykresów z serii danych możliwe jest po wybraniu typu wykresu Kombi.

Niewiele osób wie, że wykres możemy również edytować bezpośrednio z paska formuły. Dzieje się tak ponieważ do każdej serii danych jest przypisywana funkcja: SERIE.

Ogólna składnia tej funkcji przedstawia się następująco:

=SERIE(nazwa_serii; etykiety_kategorii; wartości; kolejność; rozmiary)

Użyte argumenty oznaczają:

nazwa_serii - argument ten przechowuje informację o nazwie serii, nazwa ta znajduje się w legendzie wykresu. W przypadku wykresu zbudowanego tylko z jednej serii, nazwa ta jest jednocześnie tytułem wykresu. W przypadku braku jego zdefiniowania Excel zastosuje nazwę domyślną Seria 1 (w przypadku większej liczby serii - Seria 2, Seria 3 itd.).

etykiety_kategorii - zakres zawierający informację o etykietach (w przypadku wykresów typu XY argument ten definiuje miejsce przechowywania wartości X). Brak argumentu spowoduje przypisanie kolejnych liczb całkowitych, zaczynających się od 1 do każdej z wartości składających się na serię danych.

wartości - argument określający położenie wartości serii (w przypadku wykresów typu XY argument ten definiuje miejsce przechowywania wartości Y).

kolejność - kolejność wyświetlenia serii danych (zastosowanie tylko w przypadku wykresów zawierających więcej niż jedną serię danych).

rozmiary - argument definiujący wielkość bąbelków (zastosowanie tylko w przypadku wykresów bąbelkowych).

Użyte argumenty zawierają odwołanie do arkusza oraz zawsze są typu bezwzględnego (użycie znaku $).

Modyfikując te argumenty możemy wpływać na wygląd naszego wykresu.

Przedstawiając dane na wykresie czasem spotykamy się z sytuacją w której musimy zaznaczyć pewne odchylenie od wartości reprezentowanej na wykresie. Odchylenie te np. może wynikać z tzw. niepewności pomiarowej. Odczytując daną wielkość przy użyciu narzędzia pomiarowego nigdy odczytywana wartość nie jest w 100% wartością rzeczywistą. Niedokładność ta najczęściej związana jest z klasą dokładności przyrządów pomiarowych (lecz nie tylko bo np. sam sposób wykonywania pomiarów może już być obarczony błędem).

Przyjrzyj się wykresowi przedstawionemu poniżej na którym to zaprezentowano wyniki pomiarów temperatury. Wyniki te obrazują średnią miesięczną temperatury. W każdym miesiącu dokonano pomiarów lecz przy użyciu termometru o innej dokładności. Jeśli samo użyte narzędzie nie jest Nam w stanie zapewnić dokładności pomiaru tak więc my nie możemy być pewni, że wartość odczytana jest tą wartością faktyczną. Dlatego też by zaznaczyć wartość niepewności możemy w tym celu użyć słupków błędu.

Na wykresie słupki błędów dodajemy po wybraniu ikony Elementy wykresu (to ta z plusem) a następnie opcji Słupki błędów.

Excel zapewnia nam użycie następujących typów słupków błędu:

Stała wartość - użytkownik sam określa stałą wartość błędu,

Wartość procentowa - słupek błędów jest zdefiniowanym przez użytkownika procentem danej wartości,

Odchylenie standardowe - błąd jest wyliczoną przez program wartością odchylenia standardowego obejmującego całą serię danych,

Błąd standardowy - wartości słupków błędu są wyliczane na podstawie serii danych,

Niestandardowa - sami określamy wartości definiujące dolny i górny zakres błędu.

Słupki błędów dostępne są w wykresach typu: punktowy (XY), kolumnowy, słupkowy, liniowy oraz warstwowy. Przy czym w wykresach typu XY słupki błędów mogą zostać zdefiniowane zarówno dla wartości X jak i Y.

Do prognozowania przyszłych wartości może Nam posłużyć linia trendu, której zadaniem jest pokazanie jak kształtuje się kierunek zmian w ramach badanej serii.

Aby na wykresie umieścić linię trendu należy wybrać ikonę Elementy wykresu z dostępnych opcji zaznaczyć Linia trendu.

W ramach serii danych możemy umieścić wiele różnych linii trendu a wybierać możemy pomiędzy następującymi jej typami: wykładnicza, liniowa, logarytmiczna, wielomianowa, potęgowa oraz średnia ruchoma.

Na rysunku powyżej zaprezentowano wykres przedstawiający liczbę złożonych ofert od liczby przeprowadzonych sprzedaży jak widać ogólny trend wyznaczony za pomocą linii trendu wskazuje, że mamy do czynienia z tendencją wzrostową.

Jeśli często tworzymy dany typ wykresu stratą czasu jest każdorazowe formatowanie wykresu dlatego też wykres można zapisać jako szablon, który zostanie wykorzystany do utworzenia kolejnych wykresów.

Aby utworzyć szablon należy zaznaczyć wykres i po kliknięciu na niego PPM z dostępnych opcji wybrać Zapisz jako szablon. W oknie Zapisywanie szablonu wykresu określamy nazwę szablonu oraz jego lokalizację. Szablony są zapisywane do pliku z rozszerzeniem *.CRTX

Aby użyć zapisany w poprzednim kroku szablon należy zaznaczyć dane, które powinny znaleźć się na wykresie i z wstążki wybrać polecenie Polecane wykresy. Po otwarciu okna Wstawianie wykresu przechodzimy do zakładki Wszystkie wykresy i z dostępnych opcji wybieramy Szablony.

Zastosowanie szablonu jest jedną z metod zachowania formatowania wykresu ale nie jedyną. Do dyspozycji mamy jeszcze:

Skopiowanie wykresu źródłowego i zmiana jego danych źródłowych - metoda polega na skopiowaniu wykresu, którego formatowanie chcemy wykorzystać i dokonanie zmiany danych, które na wykresie zostały zamieszczone.

Skopiowanie formatowania - po kliknięciu na wykres, którego formatowanie chcemy skopiować wybieramy Kopiuj (bądź używamy skrótu Ctrl+C) wybieramy wykres, który ma odziedziczyć dany styl a następnie przechodzimy na kartę Narzędzia główne gdzie po kliknięciu na strzałkę pod ikoną Wklej (w wcześniejszych wersjach Schowek) wybieramy Wklej specjalnie. W nowo otwartym oknie Wklejanie specjalne wybieramy opcję Formanty.

Aby wydrukować wykres osadzony należy objąć go zaznaczeniem tak jak to robi się w przypadku danych. Przed wykonaniem operacji drukowania jeśli Czytelniku nie jesteś pewny efektów wykorzystaj Podgląd wydruku. W przypadku wykresów umieszczonych w osobnym arkuszu są one zawsze drukowane na osobnej stronie. Wykres osadzony również można wydrukować oddzielnie, wystarczy go uaktywnić a następnie wybrać polecenie Plik/Drukuj. Aby zaś wyłączyć drukowanie wykresu należy kliknąć na niego a następnie w oknie zadań Formatowanie obszaru wykresu wybrać ikonę Rozmiar i właściwości a następnie rozwinąć gałąź Właściwości i odhaczyć opcję Drukuj obiekt. Po wykonaniu tych operacji wykresu nie będzie można wydrukować.

Jak już wyżej wspominałem aby tworzone wykresy spełniały nasze oczekiwania trzeba usiąść i opcjami tymi się pobawić. Eksperymentując na zasadzie prób i błędów sami dojdziemy do czego dana opcjami służy i jak jej użycie wpływa na tworzony wykres. Mnogość opcji na początku pracy z wykresami może przytłoczyć ale nie ma co się zrażać gdyż jak mówi pewne przysłowie „Nie od razu Rzym zbudowano” Poświęcając chwilę czasu na zabawę (tzn. naukę) prędzej czy później osiągniemy mistrzostwo w tworzeniu wykresów. Oczywiście życzę aby to nastąpiło prędzej.

W tematyce wykresów nie wszystko zostało omówione gdyż istnieją jeszcze tzw. wykresy przebiegu w czasie oraz wykresy przestawne ale tymi zagadnieniami zajmiemy się w oddzielnych wpisach.

W ramach nowego działu Wujek dobra rada co jakiś czas na pewno pokażę jak krok po kroku wykonać nietypowy wykres.

Excel w zadaniach. Drukowanie

2016-10-17T11:21:41+00:00

W tym wpisie zajmiemy się drukowaniem a mówiąc trochę bardziej szczegółowo omówimy sobie dostępne opcje drukowania w programie Excel. Wpis ten powstał na prośbę jednego z czytelników portalu, który zaproponował aby tym tematem się zająć. A że akurat na ukończeniu miałem wpis dotyczący list kontroli ACL i temat ten wydał mi się na tyle interesujący by opisać go szerzej postanowiłem to zrobić. Jeśli macie propozycje wpisów bądź tematów, które warto by było rozwinąć to zapraszam do kontaktu, wszelkie sugestie bardzo mile widziane.

Zanim przejdziemy do omówienia opcji związanych z drukowaniem zatrzymajmy się chwilę by omówić dostępne widoki, arkusza kalkulacyjnego gdyż widoki te (ja się Czytelniku przekonasz) ściśle są powiązane z operacją wydruku.

Excel umożliwia nam standardowo wybranie spośród trzech rodzajów widoku:

widok Normalny – jest domyślny rodzaj widoku arkusza. To w tym widoku standardowo odbywa się nasza praca z programem. Widok ten oferuje włączenie znaczników podziału stron,

widok Układ strony – odpowiedzialny za ukazanie poszczególnych stron,

widok Podgląd podziału stron – umożliwia ręczne ustawienie podziału stron.

Przełączenie pomiędzy widokami możemy realizować w dwóch miejscach:

poprzez wybranie jednej z trzech ikon dostępnych w prawym dolnym rogu programu,
poprzez wstążkę – karta Widok

Widok normalny - Jak już wspomniałem w widoku tym zazwyczaj pracujemy ale oprócz standardowych formuł, obliczeń czy wykresów możemy wyświetlić miejsca podziału stron. Podział strony jest reprezentowany poprzez poziome i pionowe kropkowane linie.

Linie podziału stron ulegają korekcie podczas:

zmianie wysokości i szerokości kolumn oraz wierszy,
zmianie orientacji strony,
po dodaniu/usunięciu nowych kolumn bądź wierszy.

Linie podziału stron standardowo są ukryte ale ukarzą się nam po wydrukowaniu arkusza, wyświetleniu podglądu wydruku bądź po ręcznym zdefiniowaniu obszaru wydruku.

Wyłączenie linii podziału odbywa się poprzez wybranie polecenia Plik a następnie Opcje. W nowo otwartym oknie klikamy zakładkę Zaawansowane, odszukujemy sekcję Opcje wyświetlania tego arkusza (jeśli mamy otwartych więcej arkuszy wybieramy ten co do którego ma być zastosowana zmiana) a następnie odhaczamy opcję Pokaż podziały stron.

Widok Układ strony - zawiera najszersze i najpełniejsze informacje dotyczące drukowanych arkuszy. W widoku tym mamy bezpośredni dostęp do wszystkich poleceń Excela a dodatkowo podgląd jak arkusz będzie wyglądał po wydrukowaniu. Poniżej na zrzucie przedstawiono arkusz w widoku Układ strony.

W widoku tym (jeśli zdefiniowano) pokazana jest zawartość nagłówka oraz stopki.

Kliknięcie na pusty szary obszar pomiędzy stronami spowoduje ukrycie obszarów marginesu, dzięki temu zabiegowi uzyskamy większą przestrzeń roboczą poprzez ukrycie niewykorzystywanych obszarów.

Widok Podgląd podziału stron - w widoku tym również mamy możliwość dokonywania korekty wprowadzonych formuł oraz możliwość wprowadzania nowych a także i co najważniejsze widok ten zawiera linie podziału stron. Linie te można przeciągać wpływając tym samym na ustalenie obszaru wydruku. Widok ten nie obejmuje informacji na temat nagłówka oraz stopki arkusza.

Poniżej na rysunku ukazano arkusz w widoku podglądu podziału stron.

Tak naprawdę niektórzy wyróżniają jeszcze jeden widok, tzw. widok Backstage, który dostępny jest po wybraniu polecenia Plik a następnie Drukuj bądź po skorzystaniu z kombinacji klawiszy Ctrl+P. Widok ten wyświetla arkusz tak jak będzie wyglądał po wydrukowaniu.

W widoku tym niestety nie mamy możliwości dokonania jakiejkolwiek korekty w danych zawartych w arkuszu ale za to mamy wpływ na wiele opcji związanych z drukowanym arkusze (ale więcej o tych opcja za chwilę).

Opcje związane z drukowaniem możemy definiować w trzech miejscach:

Pierwszym z nich jest widok Ustawienia drukowania dostępny po wybraniu Drukuj z menu Plik (bądź Ctrl+P) - rysunek powyżej.

Drugim miejsce jest karta Układ strony dostępna na wstążce.

Trzecim zaś okno dialogowe Ustawienia strony, dostępne w widoku Drukuj.

Wybór dostępnych opcji jest szeroki i opcje drukowania dostępne w przedstawionych miejscach nie zawsze się ze sobą pokrywają. Dlatego też poniżej w tabeli zebrano te najczęściej używane wraz z zaznaczeniem ich lokalizacji.

Ustawienie	Ustawienia drukowania	Karta Układ strony	Okno dialogowe Ustawienia strony
Wybór drukarki	X
Uwzględnienie linii siatki		X	X
Zmiana orientacji	X	X	X
Definicja obszaru wydruku na arkuszu		X	X
Wybór metody drukowania: jednostronne lub dwustronne	X
Układanie stron	X
Dostosowanie marginesów	X	X	X
Ustalenie rozmiaru papieru	X	X	X
Skalowanie wydruku		X	X
Ręczne zdefiniowanie podziałów stron		X
Definicja nagłówków wierszy i kolumn		X	X
Ustalenie liczby wydruku kopii	X
Komentarze			X
Opcje związane ze zmianą trybu kolorów			X
Numer pierwszej strony			X
Wyśrodkowanie			X
Błędy w komórkach			X
Kolejność stron			X

Tak więc omówmy sobie opcje dostępne w widoku Ustawienia drukowania.

1. Przycisk Drukuj – wybranie go spowoduje wysłanie pliku do drukarki, drukowanie zostanie przeprowadzone zgodnie z opcjami ustawionymi poniżej.

2. Kopie – ustalenie liczby kopii wydruku, potrzebną ilość ustalamy sami poprzez wpisanie ich liczby bądź używamy do tego strzałek.

3. Drukarka – w systemie może być zainstalowanych wiele drukarek, drukarki mogą być do komputera podłączone lokalnie bądź mogą to być drukarki sieciowe (w systemie Windows drukarką może być również faks a także kreator plików PDF). Okno umożliwia wybranie urządzenia do którego ma zostać wysłane polecenie wydruku. Standardowo wybierana jest drukarka, która w systemie została ustawiona jako domyślna.

4. Właściwości drukarki - kliknięcie opcji spowoduje otwarcie okna dialogowego w którym będą zawarte opcje wybranej drukarki. Ustawienia oraz zakres dostępnych opcji zależą od drukarki. Drukarki o większych możliwościach będą posiadały bogatszy zestaw opcji wpływających na końcowy efekt wydruku zaś w tych mniej zaawansowanych urządzeniach ilość dostępnych opcji będzie ograniczona do tych niezbędnych.

5. Drukuj aktywna arkusze - domyślnie drukowane są aktywne arkusze (możliwe jest wydrukowanie kilku arkuszy, definicję arkuszy przeprowadzamy z wykorzystaniem klawisza Ctrl) lecz po rozwinięciu listy można zdecydować się np. na drukowanie całego skoroszytu (wydruk całego skoroszytu wraz z arkuszami wykresów) lub jedynie zaznaczonego zakresu. Zaznaczenie zakresu następuje przed przejściem do widoku opcji Ustawienia drukowania. Dostępne są jeszcze dwie dodatkowe opcje a mianowicie Drukuj zaznaczony wykres (opcja aktywna po zaznaczeniu wykresu) oraz Drukuj zaznaczoną tabelę (opcja aktywna po tym jak wskaźnik komórki zostanie umieszczony w obrębie tabeli).

6. Strony - określenie zakresu stron, które zostaną wydrukowane.

7. Posortowane - określenie sposobu sortowania wydruku kopii. Dostępne dwie opcje: 1,2,3; 1,2,3; 1,2,3 (strona 1, strona 2, strona 3 itd.) lub 1,1,1; 2,2,2; 3,3,3 (wszystkie strony 1, wszystkie strony 2, wszystkie strony 3 itd.) Domyślnie ustawioną opcją jest: 1,2,3; 1,2,3; 1,2,3 Jeśli drukujemy tylko jedną stronę Excel ignoruje ustawienia sortowania.

8. Orientacja pionowa/pozioma - wybór orientacji pionowej (domyślna) i poziomej.

9. Rozmiar - wybór rozmiaru papieru na którym będzie realizowany druk (domyślnie A4). Spośród dostępnych opcji możemy wybrać wiele różnych rozmiarów papieru ale należy mieć świadomość iż to czy da się zrealizować wydruk na wybranym formacie zależy również od samej drukarki (drukarka części formatów może nie obsługiwać).

10. Marginesy - wybór spośród trzech (a tak naprawdę czterech) rodzajów marginesów: niestandardowe (ustawienie domyślne), normalne, szerokie i wąskie. Marginesy niestandardowe są definiowane przez użytkownika ich rozmiar można ustalać poprzez skorzystanie z opcji dostępnych w prawym dolnym rogu (punkt 13). Zmiana rozmiarów marginesów dostępna jest również na karcie Układ strony (patrz wstążka) a także na karcie Ustawienia strony w zakładce Marginesy (punkt 12).

11. Rozmiar - wybór opcji skalowania. Dostępne opcje to: Rozmiar rzeczywisty (drukuj arkusze w rozmiarze rzeczywistym), Dopasuj arkusz do jednej strony (zmniejsz wydruk aby zmieścić go na jednej stronie), Dopasuj wszystkie kolumny do jednej strony (zmniejsz wydruk, aby miał szerokość jednej strony), Dopasuj wszystkie wiersze do jednej strony (zmniejsz wydruk, aby miał wysokość jednej strony) oraz Opcje skalowania niestandardowego (wybór opcji spowoduje otwarcie okna Ustawienia strony). Link do okna Ustawienia strony (punkt 12) dostępny jest również poniżej opcji wydruku.

Po wybraniu tej opcji będziemy mieli wpływ na:

1 - ustalenie orientacji wydruku,

2 - dostosowania skalowania - możemy określić wartość procentową bądź zdecydować się na wpasowanie opierające się na wysokości i szerokości arkusza. W pewnych sytuacjach (a szczególnie w tych w których pracujemy z rozbudowanymi arkuszami) dochodzi do sytuacji w której musimy na pojedynczej kartce papieru umieścić wszystkie dane, które są zawarte w arkuszu aby wykonać to zadanie musimy posłużyć się mechanizmem, który pozwoli nam na zmniejszenie bądź zwiększenie skali wydruku. Zmiana współczynnika skalowania może nastąpić przy użyciu skali procentowej (przedział od 10% do 400%) bądź skali opartej na szerokości lub wysokości strony.

3 - ustalenie rozmiaru papieru a także ustaleniu jakości wydruku,

4 - wybranie przycisku Opcje przeniesie nas do ustawień oferowanych przez drukarkę.

Opcje związane z ustawieniem strony zostały omówione przyjrzyjmy się zatem tym, które są oferowane na karcie Układ strony

Orientacja strony określa sposób usytuowania wydruku na kartce. Domyślnym ustawieniem strony jest orientacja pionowa.

Zmianę orientacji strony dokonamy po wybraniu ikony Orientacja dostępnej na karcie Układ strony w sekcji Ustawienia strony.

Orientacja pionowa spełnia swe zadanie przy wąskim zakresie komórek i dużej liczbie wierszy, natomiast orientacja pozioma w sytuacji odwrotnej czyli gdy mamy do czynienia np. z szeroką tabelą.

Po dokonanej zmianie układu strony linie podziału stron zostaną automatycznie dostosowane do nowego układu.

Opcja zmiany ustawień strony dostępna jest również w widoku Backstage po wybraniu Drukuj.

Polecenie Rozmiar umożliwia zdefiniowanie rozmiaru papieru. Opcja zmiany rozmiaru dostępna jest również w widoku Backstage po wybraniu Drukuj.

Podczas drukowania (w szczególności długich raportów, bądź tabel) nie raz spotkamy się z sytuacją w której zaproponowany przez Excela automatyczny sposób podziału stron nie do końca będzie spełniał nasze oczekiwania. Na szczęście są mechanizmy, które pozwolą nam na zastosowanie ręcznego podziału dokumentu na strony.

Jedną z takich opcji są Znaki podziału.

Przypuśćmy, że mamy zestawienie takie jak przedstawione na rysunku poniżej. W zestawieniu tym istnieje dwóch Konsultantów (Paweł oraz Teresa) a zadanie polega na takim wydrukowaniu danych aby informacje odnośnie konsultantów znalazły się na dwóch osobnych kartkach.

Aby wykonać to zadanie należy wymusić ręczny podział strony. Aby wstawić poziomą linię podziału stron, należy kliknąć na komórkę, która ma rozpoczynać nową stronę. Przy czym komórka ta musi znajdować się w kolumnie A (w przykładzie komórka A41). Kliknięcie na komórkę należącą do innej kolumny spowoduje również dodanie pionowej linii podziału.

Po zaznaczeniu komórki wybieramy ikonę Znaki podziału a następnie opcję Wstaw podział strony.

Po wykonaniu tej operacji dane zostaną od siebie oddzielone czego efektem będzie ich wydruk na dwóch osobnych stronach.

Aby zaś dokonać podziału przy użyciu pionowej linii podziału wybieramy komórkę, która rozpocznie nam nową stronę (komórka musi być umieszczona w wierszu o numerze 1) i tak jak poprzednio wybrać ikonę Znaki podziału a następnie opcję Wstaw podział strony.

Poniżej przykład podziału informacji o konsultantach (te same dane co w ćwiczeniu powyżej) lecz tym razem dane zostały zgrupowane w poziomie. Aby wydrukować informacje z podziałem na poszczególnych pracowników należy wybrać komórkę D1 i tak jak poprzednio wybieramy ikonę Znaki podziału a następnie opcję Wstaw podział strony

Po dokonaniu podziału dane te zostaną od siebie odseparowane a ich wydruk nastąpi na dwóch oddzielnych stronach.

Oczywiście można również zastosować poziomą oraz pionową linię podziału. Przyjmijmy, że mamy dane zebrane jak na poniższym zrzucie i tak jak w poprzednich dwóch ćwiczeniach chcemy zrealizować wydruk z zastrzeżeniem, że jeden konsultant - jedna kartka.

Potrzebujemy podzielić dane na cztery części. Zadanie te możemy zrealizować na dwa sposoby:

Sposób 1 - umieszczamy kursor w komórce A14 i wstawiamy znak podziału a następnie kursor ustawiamy na komórkę D1 i ponownie wstawiamy znak podziału.

Sposób 2 - zaznaczamy komórkę D14 i wstawiamy znak podziału strony. Linie podziału zostaną umieszczone w następujący sposób: pozioma powyżej komórki D14, pionowa z lewej strony komórki D14.

Po zastosowaniu dowolnego z opisanych sposobów dane zostaną podzielone na cztery części.

Aby usunąć linię podziału zaznaczamy komórkę za pomocą, której podział wstawiliśmy a następnie klikając na ikonę Znaki podziału wybieramy opcję Usuń podział strony. Wybranie opcji Resetuj wszystkie podziały stron usunie wszystkie zdefiniowane linie podziału.

A co w sytuacji w której dane, które chcemy wydrukować znajdują się w różnych obszarach arkusza? Aby poradzić sobie z tym zadaniem należy wykorzystać opcję: Obszar wydruku.

Pozostańmy przy przykładzie z konsultantami, lecz tym razem zadanie polega na takim wydrukowaniu danych aby informacje przypisane Andrzejowi i Teresie również znalazły się na osobnej kartce.

Realizację zadania rozpoczniemy od zaznaczenia wszystkich komórek w których znajdują się dane konsultanta Andrzeja a następnie na karcie Układ strony odszukujemy ikonę Obszar wydruku i z menu wybieramy opcję Ustaw obszar wydruku.

Po wykonaniu tej czynności oczywiście należy do już zdefiniowanego obszaru wydruku dodać kolejny zawierający informacje o konsultantce Teresie.

Tak jak poprzednio rozpoczynamy od zaznaczenia komórek w których przechowywane są dane lecz tym razem muszą być zaznaczone komórki przynależne Teresie. Po zaznaczeniu komórek na karcie Układ strony wybieramy ikonę Obszar wydruku i z menu wybieramy opcję Dodaj do obszaru wydruku.

Po tak zdefiniowanym zakresie nie pozostaje nic innego jak zrealizować wydruk. Dane konsultantów zostaną wydrukowane na dwóch osobnych stronach.

Oczywiście ten sam efekt możemy uzyskać dzięki:

zastosowaniu linii podziału,
ręcznym zaznaczeniu komórek konsultanta pierwszego i po wyświetleniu widoku drukowania wybraniu opcji Drukuj zaznaczenie, operację oczywiście powtarzamy dla konsultanta drugiego.

A co w przypadku w którym chcemy zrealizować wydruk z wielu obszarów arkusza tak by znalazły się one na jednej stronie? Tu już sprawa mocno się komplikuje gdyż jak na razie Excel opcji takiej nie przewiduje (tak naprawdę da się to wykonać tylko trzeba by było skorzystać z języka VBA). Ja proponuję Ci Czytelniku znacznie łatwiejszy sposób.

Przyjmijmy że mamy arkusz przedstawiony na zrzucie poniżej.

Zadanie polega na wydrukowaniu danych w ten sposób aby informacje konsultanta Paweł i Jan znalazły się na jednej stronie.

Aby zadanie wykonać skorzystamy z mechanizmu, który nazywa się Aparat fotograficzny (mało znana funkcja Excela ale w pewnych przypadkach niezastąpiona. Jak na przykład w tym). Funkcji tej standardowo nie odnajdziemy wśród opcji pogrupowanych na pasku zadań Excela ale nic nie stoi na przeszkodzie by ją dodać ręcznie.

Rozpoczynamy od kliknięcia strzałki (lewy górny róg okna programu) odpowiedzialnej za dostosowanie narzędzi dostępnych w pasku szybkiego uruchamiania. Po wybraniu strzałki z dostępnych opcji wybieramy Więcej poleceń.

W nowo otwartym oknie Opcje programu Excel z menu Wybierz polecenia z: uaktywniamy opcję Polecenia, których nie ma na Wstążce (punkt 1). W oknie poniżej zostaną wyświetlone wszystkie dostępne opcje programu Excel, które nie zostały domyślnie zamieszczone na wstążce programu. Wśród opcji odnajdujemy Aparat fotograficzny (punkt 2) i poprzez przycisk Dodaj (punkt 3), ikonę funkcji przenosimy do okna narzędzi paska Szybki dostęp. Po zatwierdzeniu zmian ikona aparatu fotograficznego pojawi się na pasku szybkiego dostępu.

Kolejną czynnością jest zaznaczenie komórek zawierających dane konsultanta Paweł a następnie wybraniu ikony aparatu fotograficznego. Po wykonaniu „zdjęcia” klikamy w dowolny obszar arkusza. Analogicznie postępujemy z danymi konsultanta Jan. Tak przygotowane „zdjęcia” umieszczamy obok siebie.

Ostatnią czynnością jest wykonanie wydruku. Zaznaczamy komórki tak by zaznaczenie obejmowało wykonane „zdjęcia”.

Po przejściu do widoku drukowania wybieramy opcję Drukuj zaznaczenie.

W ten oto ciekawy sposób poradziliśmy sobie z wydrukiem danych na jednej stronie, leżących w różnych obszarach arkusza.

Funkcja aparatu fotograficznego Czytelniku przyda Ci się również podczas pracy z skoroszytem, który zawiera wiele arkuszów. Przypuśćmy, że modyfikujesz formułę bądź dane w jednym z arkuszów lecz odzwierciedlenie zmian znajduje się w innym. Poprzez wstawienie „zdjęcia” będziesz miał bezpośredni podgląd jak dokonywana zmiana wpływa na dane zawarte w komórkach znajdujących się w odrębnym arkuszu. Dzieje się tak gdyż dokonywane zmiany są na bieżące uaktualniane. Wykonane „zdjęcie” nie jest statycznym obrazem.

Czasem podczas drukowania niektórych arkuszy chcemy aby widoczne były linie siatki. W domyślnych ustawieniach linie siatki podczas operacji wydruku są pomijane. Aby zmienić te ustawienie należy przejść na kartę Układ strony i w sekcji Opcje arkusza zaznaczyć opcję Drukuj linie siatki.

Wydruk linii siatki poszczególnych komórek uzyskamy również włączając obramowanie komórki.

W pierwszym wierszu bądź pierwszej kolumnie często umieszczamy informacje identyfikujące dany wiersz czy kolumnę. Podczas wykonania wydruku, dane zawarte w tych obszarach a opisujące zawartość zostaną wydrukowane tylko na pierwszej stronie. Przeglądając wielostronicowy wydruk brak opisu wierszy bądź kolumn jest dla nas utrudnieniem. Na szczęście możemy to w prosty sposób zmienić tak by uwzględnić wybrane wiersze lub kolumny na każdej stronie wydruku.

Sytuacje tą obrazuje poniższy zrzut na którym widać iż dane opisujące tabelę są wydrukowane tylko na pierwszej stronie.

Aby pierwszy wiersz tabeli znalazł się na każdej z drukowanych stron bądź kolumna (wydrukowana jako pierwsza po lewej stronie) należy wykonać następujące czynności:

wybieramy polecenie Tytuły wydruku dostępne na karcie Ustawienia strony,
po wywołaniu polecenia w nowo otwartym oknie Ustawienia strony (karta Arkusz) należy uaktywnić odpowiednie pole: U góry powtarzaj wiersze lub Z lewej powtarzaj kolumny w zależności od tego co chcemy by zostało wydrukowane na każdej z stron (oczywiście możemy zdecydować się zarówno na kolumnę jaki i wiersz). Definicję wierszy bądź kolumn dokonujemy ręcznie poprzez wpisanie zakresu komórek bądź za pomocą przycisku.

Po wykonaniu tych dwóch prostych czynności zaznaczony wiersz/kolumna będą uwzględnione podczas wydruku.

Wstawianie znaku wodnego (czyli obrazu, bądź tekstu, który pojawi się na każdej drukowanej stronie) w Excelu nie przebiega w tak prosty sposób jak to ma się w Wordzie gdzie mamy odpowiedni przycisk, który całą robotę wykona za nas. Ale i w Excelu można takową grafikę wstawić trzeba po prostu użyć pewnej sztuczki.

Aby umieścić znak wodny należy wykonać następujące kroki:

Krok 1. Po odnalezieniu grafiki, która będzie naszym znakiem wodnym przechodzimy na kartę Widok i w sekcji Widoki skoroszytu wybieramy przycisk Układ strony.

Krok 2. Po zmianie widoku arkusza danych wybieramy pole Dodaj nagłówek (pole to składa się z trzech sekcji, zazwyczaj wybieraną jest ta środkowa). Po wybraniu sekcji odszukujemy przycisk Obraz. Opcję tą znajdziemy po wybraniu polecenia Narzędzia nagłówków i stopek/Projektowanie w sekcji Elementy nagłówka i stopki Po wybraniu przycisku Obraz na ekranie w nowo otwartym oknie dialogowym będziemy mogli wskazać plik graficzny, który będzie stanowił nasz znak wodny.

Krok 3. Po wybraniu obrazu (obraz wybieramy spośród tych dostępnych na dysku, w usłudze OneDrive bądź korzystamy z wyszukiwarki Bing) klikamy w dowolne miejsce poza obszarem nagłówka. Obraz zostanie wstawiony.

Krok 4. Wracamy z powrotem do obszaru nagłówka (klikamy na sekcję w której został wstawiony obraz) tak by wyświetlić kod: &[Obraz] Przed kodem tym dodajemy znaki Enter tak by ustawić go na odpowiedniej wysokości.

Jeśli chcemy dokonać korekty wstawionego obrazu przechodzimy do karty Narzędzia nagłówków i stopek/Projektowanie i w sekcji Elementy nagłówka i stopki odszukujemy ikonę Formatuj obraz. Opcje na które będziemy mieli wpływ to rozmiary wstawionego obrazu, przycięcie oraz zmiana kolorystyki (jasność, kontrast czy rozmycie).

Podczas pracy z Excelem mamy możliwość ustawienia tła arkusza (polecenie Tło dostępne na karcie Ustawienia strony). Zadaniem tła jest wizualne uatrakcyjnienie arkusza i tak naprawdę przeznaczenie tej opcji na tym się kończy. Po wstawieniu tła jest ono uwzględniane tylko w arkuszu i niestety nie ma możliwości jego wydrukowania.

Na zrzucie poniżej pokazano zastosowanie opcji tła i jak można zauważyć obrazek ten w podglądzie wydruku jest pomijany.

Sama opcja wstawienia tła jest bardzo ograniczona w swym działaniu gdyż nie mamy żadnej możliwości przeskalowania go czy zmiany położenia tak by np. wstawiony obrazek był uzupełnieniem arkusza a nie zbędnym elementem przeszkadzającym w pracy z arkuszem.

Zamiast obrazu tła znacznie wygodniejszym rozwiązaniem (acz do ideału bardzo dużo mu brakuje) jest umieszczenie obrazka (a także kształtu bądź obiektu WordArt) i takim jego wyskalowaniu by stał się częścią arkusza. Wstawiony obraz nie może zachodzić na komórki wypełnione danymi gdyż je przysłoni a niestety nie ma możliwości takiej zmiany kolejności wyświetlanych elementów, by tekst wpisany do komórki był nad zdjęciem. W przypadku wstawienia kształtu sytuacja jest o tyle łatwiejsza gdyż mamy opcje pozwalające na sterowanie jego przezroczystością.

Zaletą wstawionych obiektów graficznych jest ich uwzględnianie podczas wydruku lecz by obiekt taki znalazł się na każdej drukowanej stronie należy go wielokrotnie skopiować do wszystkich obszarów arkusza.

Czasem zachodzi potrzeba wydrukowania nagłówka wiersza bądź kolumny (w tym kontekście nagłówek rozumiany jest jako litera dla kolumny i cyfra dla wiersza). Domyślnie elementy te są wyłączone, aby były one uwzględnione na wydruku należy na karcie Układ strony w sekcji Opcje arkusza/Nagłówki zaznaczyć opcję Drukuj.

Poniżej przykład (po lewej) zastosowania opcji domyślnej zaś po prawej uaktywniona opcja drukowania nagłówków.

Nagłówek to informacja pojawiająca się w górnej części każdej z drukowanych stron, zaś informacje zawarte w stopce są umieszczane na dole strony. Zasadność stosowania nagłówka i stopki sprowadza się do umieszczenia tych samych danych na każdej z drukowanych stron (po za numerem stron). Często podczas drukowania wielu stron dokumentu (np. celem jego identyfikacji) do wydruku dołączana jest informacja o jego wersji. Zastosowanie nagłówka zwalnia nas z tego obowiązku gdyż definicja informacji zawartych w nagłówku spowoduje powielenie jej na każdej ze stron tworzących dokument.

Domyślnie podczas realizowania wydruku informacje z nagłówka oraz stopki nie są uwzględniane. Spowodowane jest to faktem iż w tej części arkusza żadne dane nie są umieszczone.

Informacje, które mają być zamieszczone w nagłówku bądź stopce można zdefiniować w zakładce Nagłówek/stopka która dostępna jest po wybraniu opcji Ustawienia strony (widok Backstage, Drukuj).

W sekcjach Nagłówek: bądź Stopka: zostały umieszczone standardowe formy nagłówka bądź stopki. Określenie własnych informacji następuje poprzez wybranie przycisku Nagłówek niestandardowy bądź Stopka niestandardowa (oczywiście w zależności gdzie informacja ta ma zostać umieszczona).

Przedstawiona powyżej opcja określenia nagłówka bądź stopki nie jest oczywiście jedyną, znacznie wygodniejszym sposobem jest skorzystanie z opcji dostępnych po przełączeniu się na widok Układ strony, w którym to poprzez kliknięcie na odpowiedni obszar nagłówka/stopki i bezpośrednie wpisanie informacji można zdefiniować dane, które będą uwzględniane podczas wydruku. Dane umieszczone w nagłówku/stopce można w dowolny sposób formatować.

Zarówno nagłówek i stopka zostały podzielone na trzy odrębne części. W sekcjach tych można umieszczać niezbędne nam informacje jak np. tytuł dokumentu, autora czy numer strony.

Aby nagłówek i stopka były automatycznie uzupełniane np. logiem firmy można posłużyć się szablonem. Szablon taki stanowi punkt wyjścia dla nowo definiowanych arkuszy. Użycie szablonu sprowadza się do definicji danych, które będą dołączane do nowo tworzonych dokumentów Excela. Jak taki szablon utworzyć? – to opiszę w kolejnym wpisie.

Zamiast samemu definiować informacje umieszczane w nagłówku/stopce możemy zdecydować się na użycie jednej z propozycji, które dostępne są po zaznaczeniu dowolnej sekcji nagłówka/stopki i wybraniu karty Projektowanie. Użycie ikon Nagłówek bądź Stopka pozwoli nam na umieszczenie proponowanych danych.

Definiując nagłówek/stopkę możemy oczywiście użyć dowolnego tekstu ale dodatkowo możemy zastosować również zmienne. Zmienne dostępne są na karcie Projektowanie (Narzędzia nagłówków i stopek) pod ikonami umieszczonymi w sekcji Elementy nagłówka i stopki.

Podążając od lewej kolejne ikony pozwolą nam na umieszczenie następujących informacji:

Numer strony - kod: &[Strona] - zostaje umieszczona informacja o numerze bieżącej strony,
Liczba stron - kod: &[Strony] - ogólna liczba stron,
Bieżąca data - kod: &[Data] - bieżącą datę,
Bieżąca godzina - kod: &[Czas] - bieżąca godzina,
Ścieżka pliku - kod: &[Ścieżka]&[Plik] - pełna ścieżka pliku wraz z nazwą pliku,
Nazwa pliku - kod: &[Plik] - tylko nazwa pliku,
Nazwa arkusza - kod: &[Karta] - nazwa arkusza,
Obraz - umieszczenie w obrębie nagłówka/stopki pliku graficznego,
Formatowanie obrazu - zmiana ustawień obrazu.

Przy definicji nagłówka i w przypadku użycia znaku & może pojawić się pewien problem gdyż znak & zarezerwowany jest do użycia z nazwą zmiennej. Co zatem zrobić gdy w nagłówku niezbędne okaże się użycie tegoż znaku? Należy wpisać go dwukrotnie - &&

Na karcie Projektowanie dostępne są jeszcze następujące opcje:

Inne na pierwszej stronie - włączenie opcji spowoduje możliwość ustawienia osobnego nagłówka/stopki dla pierwszej drukowanej strony,
Inne na stronach parzystych i nieparzystych - określenie nagłówka dla stron parzystych i nieparzystych,
Skaluj do rozmiaru dokumentu - automatyczna zmiana rozmiaru czcionki nagłówka/stopki podczas wykonywania operacji skalowania arkusza (domyślnie włączona),
Wyrównaj do marginesów strony - włączenie opcji spowoduje wyrównanie tekstu wprowadzonego w lewej sekcji nagłówka/stopki do strony lewej oraz wyrównanie tekstu wprowadzonego w prawej sekcji nagłówka/stopki do strony prawej (domyślnie włączona).

Często pojawiającym się pytaniem na forach dotyczących Excela jest pytanie – Jak w nagłówku/stopce wydrukować tekst, który został umieszczony w danej komórce? Niestety nie można wykonać tego zadania poprzez wyklinanie trzeba posłużyć się makrem (kiedyś na temat poświęcę więcej czasu).

Trochę zboczymy z tematu, ale myślę, że warto.

W pierwszej kolejności uaktywniamy zakładkę Deweloper.

W przypadku wersji Offica 2016.

Kliknij kartę Plik.
Kliknij pozycję Opcje.
Kliknij pozycję Dostosowywanie Wstążki.
W obszarze Dostosowywanie Wstążki oraz Karty główne zaznacz pole wyboru Deweloper.

Zaś w przypadku wersji starszych Offica.

W menu Narzędzia kliknij polecenie Opcje.
Na karcie Inne kliknij przycisk Opcje zaawansowane, a następnie zaznacz pole wyboru Pokaż kartę Deweloper na Wstążce.

Po uaktywnieniu zakładki Deweloper

wybieramy ikonę Makro,
w nowo otwartym oknie określamy nazwę makra (w przykładzie nazwa makra: druk),
wybieramy przycisk Utwórz.

Po wybraniu przycisku Utwórz zostanie otwarte nowe okno edytora VBA . W oknie tym pomiędzy kodem: Sub <nazwa_makra> a End Sub wpisujemy polecenie w postaci: ActiveSheet.PageSetup.CenterHeader = Worksheets(numer_arkusza/nazwa_arkusza).Range("adres komórki")

Ponieważ mój arkusz, który będę drukował nosi nazwę Premia a tekst, który ma się pojawić w nagłówku znajduje się w komórce A1, polecenie przyjęło postać: ActiveSheet.PageSetup.CenterHeader = Worksheets("Premia").Range("A1")

Po dokonaniu zmian, makro oczywiście zapisujemy (konieczne będzie zapisanie arkusza w formacie pozwalającym na obsługę makr czyli .xlsm).

Nie pozostaje nam nic innego jak sprawdzenie makra w działaniu. Ponownie klikamy ikonę Makra i w nowo otwartym oknie uaktywniamy opcję Uruchom.

Jak widać poniżej do nagłówka została wprowadzona informacja zawarta w komórce A1.

Zawartość komórki A1 została umieszczona w centralnej części nagłówka jeśli chcemy aby zawartość komórki znalazła się w lewej sekcji nagłówka parametr: CenterHeader zamieniamy na: LeftHeader zaś by umieścić dane w sekcji prawej stosujemy: RightHeader

Aby za pomocą makra wstawić zawartość dowolnej komórki do stopki (sekcja środkowa) należy użyć parametru: CenterFooter (dla sekcji lewej: LeftFooter zaś dla prawej: RightFooter).

Ukrywanie niektórych komórek podczas drukowania

Podczas realizowania procesu wydruku na pewno spotkasz się z sytuacją w której pewne dane będziesz chciał ukryć gdyż np. drukowany zakres komórek zawiera informacje, które niekoniecznie muszą być umieszczone na wydruku (np. numer PESEL, numer telefonu czy adres). Aby ukryć te informacje można zastosować następujące metody:

Ukrycie wiersza bądź kolumny - aby ukryć wiersz czy kolumnę należy zaznaczyć interesujący nas obiekt i po wybraniu PPM wybrać opcję Ukryj. Ukryt-y/a wiersz/kolumna nie jest uwzględnian-y/a podczas wydruku.

Jak widać poniżej ukryta kolumna C na podglądzie wydruku nie jest uwzględniana.

Do ukrycia pojedynczej komórki bądź zakresu komórek – tak aby zawartość komórki (bądź grupy komórek) została ukryta podczas drukowania można wykorzystać opcje związane z formatowaniem niestandardowym. Użycie odpowiedniego formatowania spowoduje ukrycie zawartości komórek i tak:

kod: ;; - wykona ukrycie wartości lecz nie tekstu,
kod: ;;; - pozwoli na ukrycie każdej zawartości.

Zawartość komórki będzie widoczna w pasku formuły.

Aby ukryć zawartość komórek należy wybrać te, które mają być niewidoczne a następnie w sekcji Liczba (karta Narzędzia główne) kliknąć na strzałkę w prawym dolnym rogu. W nowo otwartym oknie Formatowanie komórek w polu Typ: wpisujemy jeden z podanych kodów.

Poniżej przykład ukrycia zawartości komórek.

Jak widać poniżej ukryte komórki nie są uwzględniane podczas drukowania.

Mało eleganckimi acz skutecznymi sposobami ukrycia zawartości komórki jest:

ustawienie takiego samego koloru tekstu jaki ma formatowana komórka,
zasłonięcie zawartości komórki przez inny obiekt np. rysunek czy kształt.

Oprócz ukrywania zawartości komórek mamy też możliwość blokowania wydruku obiektów zawartych w arkuszu (obrazy, kształty czy wykresów).

Aby zablokować element należy wykonać następujące kroki:

1. PPM klikamy na wybrany obiekt i z menu kontekstowego wybieramy opcję Formatuj … (nazwa polecenia zależna jest od formatowanego obiektu),

2. W oknie Formatowanie … wybieramy ikonę Rozmiar i właściwości.

3. Po rozwinięciu sekcji Właściwości odznaczamy checkbox Drukuj obiekt.

Tak jak wszystkie wyżej opisane opcje wydruku na pewno prędzej czy później Czytelniku okażą się przydatne tak i ta przedstawiona poniżej na pewno nie raz będzie przez Ciebie używana. Podczas realizacji wydruków bardzo często stajemy przed pytaniem - Jak odpowiednią ilość informacji zawartych w arkuszu dopasować do rozmiarów kartki, tak by się wszystko zmieściło? Odpowiedzią na pytanie jest użycie opcji związanych z skalowaniem. Skalowanie pozwala nam na ustalenie procentowego pomniejszenia bądź powiększenia drukowanych informacji. Opcje skalowania dostępne są na karcie Układ strony w sekcji Skalowanie do rozmiaru

W sekcji tej mamy dostępne następujące opcje:

Szerokość - zmniejszenie szerokości wydruku celem dopasowania do odpowiedniej ilości stron,
Wysokość - zmniejszenie wysokości wydruku celem dopasowania do odpowiedniej ilości stron
Skala - umożliwia powiększenie bądź zmniejszenie wydruku o wartość procentową jego rzeczywistego rozmiaru.

Poniżej przykład użycia opcji skalowania, dla skoroszytu w którym skalowanie zostało ustalone na 60 i 130 procent.

Podczas realizowania procesu wydruku dla danych zawartych w danym skoroszycie może dojść do sytuacji w której będziemy musieli ten proces wykonać z różnymi ustawieniami wydruku. Czasem na podstawie tego samego skoroszytu tworzy się kilka różnych raportów, a zmienianie ulegają ustawienia wydruku.

Aby za każdym razem nie wykonywać zmiany ustawień opcji druku można te ustawienia zapisać w formie widoku niestandardowego. Definicja widoku sprowadza się do wykonania ustawień wydruku a następnie utworzeniu widoku.

Widok niestandardowy może zawierać dane o:

zablokowane okna,
powiększenie,
widoki arkuszy,
opcje wydruku zawarte na karcie Układ strony (sekcje: Skalowanie do rozmiaru, Opcje arkusza oraz Ustawienia strony),
ukryte kolumny bądź wiersze,
zaznaczone komórki i zdefiniowane obszary.

Definicja widoku odbywa się poprzez przejście na kartę Widok a następnie wybraniu ikony Widoki niestandardowe. W nowo otwartym oknie klikamy opcję Dodaj po wybraniu, której następuje definicja nazwy widoku.

Aby wczytać widok ponownie wybieramy opcję Widoki niestandardowe i po wybraniu widoku klikamy na przycisk Pokaż.

I to by było na tyle, mam nadzieję, że po zapoznaniu się z tym wpisem wydruk w Excelu nie będzie dla Ciebie Czytelniku żadnym problemem.

Excel w zadaniach. Tabele przestawne.

2016-07-19T20:49:18+00:00

Mechanizm tabel przestawnych w Excelu jest funkcją, która pozwala nam na zestawienie danych zawartych w arkuszach bądź w plikach zewnętrznych np. bazodanowych. Jest to świetne narzędzie służące do prezentacji danych, które w danym momencie są nam potrzebne. To idealne narzędzie dla osoby, która operuje na dużej ilości informacji i informacje te musi analizować. A największą zaletą stosowania tabel przestawnych jest interaktywność całego mechanizmu. Jak się przekonasz Czytelniku iż wbrew powszechnie panującej opinii o niezwykłym skomplikowaniu tego narzędzia analiza rozbudowanych arkuszy przy wykorzystaniu tabel przestawnych jest niezwykle intuicyjna i prosta. Wszystko sprowadza się do poznania narzędzia tak by jego używanie stało się jasne i proste.

Tabele przestawne zostały wprowadzone do Excela z wersją Microsoft Office 97 i z każdym nowym wydaniem pakietu do mechanizmu tego dodawane są nowe funkcje i rozszerzenia. Aby móc korzystać z tabel przestawnych należy spełnić jeden warunek – należy odpowiednio przygotować dane. Oznacza to, że informacje przechowywane w arkuszu muszą być odpowiednio przygotowane.

Niech jako przykład posłuży przedstawiony poniżej fragment tabeli w której został zebrany księgozbiór, tabela ta została przygotowana w ten sposób by zebrane w niej dane mogły być wykorzystane przy budowie tabel przestawnych.

Zaprezentowane dane ułożone są w wierszach a zawarte w nich informacje są opisane poprzez nagłówki kolumn. Zaprezentowane dane tworzą jednolity blok informacji nie zawierający żadnych pustych wierszy oraz podsumowań. Najlepszą analogią niech będą dane, które są zbierane w bazie danych tj. jedno pole jedna informacja a wszystko zebrane w rekordy. Poniżej jako przykład przedstawiłem arkusz, który z tabelami przestawnymi nie będzie współpracował (a tak naprawdę uda się utworzyć tabelę przestawną lecz wyniki prezentowane przez nią będą nieprawidłowe – w myśl zasady gdy na wejściu „śmiecie” to na wyjściu również „śmiecie”). Poniższy arkusz jest błędny gdyż znajdują się w nim podsumowania oraz puste wiersze.

Tak więc Czytelniku gdy będziesz chciał wykorzystać do swojej pracy tabele przestawne (a jak się przekonasz już za chwilę, że naprawdę warto) zadbaj w pierwszej kolejności o odpowiednie przygotowanie danych.

Aby dłużej nie przedłużać przejdźmy do zadań przy rozwiązywaniu, których postaram się wytłumaczyć całe zagadnienie. Tradycyjnie już zachęcam do równoczesnego czytania i wykonywania opisanych zadań. Arkusz do ćwiczeń możesz pobrać tu: arkusz tabele przestawne

Wykonajmy więc pierwsze zadanie i spróbujmy odpowiedzieć na pytanie – Ile książek znajduje się w każdej z filii biblioteki?

Zadanie te w pierwszej kolejności wykonamy w sposób tradycyjny (na tą chwilę zakładamy, że nie mamy pojęcia o istnieniu mechanizmu tabel przestawnych). Tak więc by odpowiedzieć na postawione pytanie skorzystamy z filtrowania.

Po zaznaczeniu nagłówka tabeli (punkt 1) klikamy opcję Sortuj i filtruj a następnie z rozwijanego menu wybieramy Filtruj (punkt 2).

W kolejnym kroku klikamy na ikonę strzałki (efekt włączenia filtrowania) i z menu zaznaczamy opcję Filia 1. Uaktywnienie opcji sprawi pokazanie tylko tych rekordów tabeli w których w kolumnie Biblioteka występuje wartość Filia 1.

Ostatnią czynnością jaką musimy wykonać to zliczenie liczby wierszy. Liczba wierszy będzie odpowiedzią na pytanie o ilość książek znajdujących się w Filia 1. Liczbę książek poznamy po zaznaczeniu wszystkich wierszy. Jak widać poniżej liczba dostępnych książek w Filia 1 to 182.

Opisane czynności musimy oczywiście powtórzyć dla pozostałych pięciu filii.

Czytelnicy co bardziej obeznani z Excelem stwierdzą, że aby poznać liczbę książek w każdej z filii można skorzystać z innej funkcji a mianowicie Sumy częściowe. Tak więc spróbujmy wykonać postawione przed nami zadanie ponownie, lecz tym razem z wykorzystaniem sum częściowych.

Aby móc skorzystać z Sum częściowych należy w pierwszej kolejności przygotować tabele. Chcemy poznać liczbę książek w każdej z filii, tak więc wykonujemy sortowanie według kolumny Biblioteka.

Po wykonaniu sortowania cały księgozbiór mamy ułożony według umieszczenia książek w danej filii.

W kolejnym kroku przechodzimy na zakładkę Dane i wybieramy opcję Suma częściowa (punkt 1). W nowo otwartym oknie w sekcji Dla każdej zmiany w: wybieramy kolumnę Biblioteka (punkt 2) oraz w sekcji Dodaj sumę częściową do: również zaznaczmy kolumnę Biblioteka (punkt 3). Użyta funkcja to Licznik (chcemy policzyć ilość wystąpienia każdej zmiany). Klikamy OK

Efektem zastosowanych opcji będzie tabela w której zostanie wyliczona zmiana każdej wartości znajdującej się w kolumnie Biblioteka. Dla nas oznacza to wyliczenie wystąpienia słów Filia 1, Filia 2 itd.

Zadnie zostało wykonane, w przypadku zastosowania Sumy częściowej poznajemy liczbę książek we wszystkich sześciu filiach. Więcej na temat tej funkcji programu Excel znajdziesz w wpisie: Excel w zadaniach. Funkcja AGREGUJ oraz SUMY.CZĘŚCIOWE

Do tej pory zakładaliśmy iż tabel przestawnych nie znamy, pora to zmienić i sprawdźmy jak to samo zadanie można wykonać przy wykorzystaniu tego mechanizmu.

Aby wstawić tabelę przestawną należy kliknąć w obszarze tabeli w której znajdują się dane a następnie z karty Wstawianie wybrać opcję Tabela przestawna.

Po uaktywnieniu opcji w oknie Tworzenie tabeli przestawnej należy sprawdzić zakres wybranych danych (Excel sam ten zakres zdefiniuje na podstawie analizy danych zawartych w tabeli – punkt 1) oraz określić umiejscowienie tworzonej tabeli przestawnej (do wyboru opcja Nowy arkusz – tabela zostanie utworzona w nowym, odrębnym arkuszu bądź Istniejący arkusz – tabela przestawna zostanie utworzona w arkuszu wskazanym w polu Lokalizacja) – punkt 2. Odwołanie do danych znajdujących się w osobnym pliku np. pliku bazy danych programu Access możemy zrealizować po wybraniu opcji: Użyj zewnętrznego źródła danych. Po zdefiniowaniu wszystkich opcji wybieramy OK.

Tabela przestawna (a raczej jej zarys) został utworzony. Kolejnym krokiem jest zdefiniowanie danych jakie w nowo tworzonej tabeli mają zostać umieszczone. Definicję danych przeprowadzamy za pomocą czterech pól tabeli przestawnej:

pole Filtry – pole odpowiedzialne za filtrowanie danych ukazywanych w tabeli przestawnej,

pole Kolumny - wszystkie dane reprezentowane przez nagłówek tabeli zostaną umieszczone w kolejnych kolumnach tabeli przestawnej,

pole Wiersze – wszystkie dane reprezentowane przez nagłówek tabeli zostaną umieszczone w kolejnych wierszach tabeli przestawnej,

pole Wartości – komórki tabeli przestawnej, które zawierają dane liczbowe.

Układ pól możemy zmienić po kliknięciu na ikonę Narzędzia.

Powracamy do postawionego pytania o ilość książek z każdej filii. Aby uzyskać odpowiedź należy pola nagłówka tabeli przeciągnąć do odpowiednich pól tabeli przestawnej. W naszym przypadku nagłówek Biblioteka należy przeciągnąć do pola Wiersze (wszystkie wartości kolumny Biblioteka zostaną umieszczone w wierszach tabeli) oraz również nagłówek Biblioteka przenieść do pola Wartości (zliczenie wartości zmiennych zawartych w kolumnie Biblioteka).

Tabela przestawna została utworzona a dla nas oznacza to, że poznaliśmy odpowiedź na wcześniej postawione pytanie.

Forma reprezentacji danych prezentowanych przez tabelę zależy od Nas, gdyż oczywiście można utworzyć tabelę przestawną w której filie biblioteki zamiast znajdować się w wierszach będą umieszczone w kolumnach. W tym celu należy nagłówek Biblioteka umieścić w polu Kolumny.

Excel został wyposażony w jeszcze jeden mechanizm, który pozwala nam operować na danych zebranych w tabeli, efektem działania jest zbudowanie wykresu a funkcja ta nosi nazwę wykresów przestawnych. Działanie funkcji jest bardzo podobne do tworzenia tabel przestawnych.

Aby utworzyć wykres na karcie Wstawianie wybieramy Wykres przestawny. Do dyspozycji mamy ten sam zestaw opcji co w przypadku tworzenia tabeli przestawnej.

Po wybraniu OK, przeniesieniu nagłówka Tytuł do pola Wartości oraz nagłówka Biblioteka do pola Oś (Kategorie) uzyskamy wykres przedstawiający ilość tytułów znajdujących się w każdej z filii.

Aby jeszcze bardziej nam ułatwić operowanie na danych zawartych w tabeli producent od wersji MSOffice 2013 udostępnił dwie opcje: Polecane tabele przestawne oraz Polecane wykresy. Uaktywnienie opcji spowoduje po analizie danych zawartych w tabeli zaproponowanie nam możliwych do utworzenia tabel bądź wykresów. Tak więc po przejrzeniu propozycji może się okazać iż znajdziemy w nich to co akurat jest nam potrzebne.

Ilość możliwych do utworzenia wariantów tabel przestawnych na bazie danych na których aktualnie operujemy jest ogromna i nie sposób przedstawić ich wszystkich tak więc wybrałem kilka przykładów by dobitniej pokazać przewagę tego rozwiązania nad innymi opisanymi wyżej (filtrowanie, sortowanie oraz sumy częściowe).

Spróbujmy znaleźć odpowiedź na następujące pytania:

1 – Ilość książek w każdej z filii biblioteki z podziałem na rok wydania.

2 – Ilość książek w obrębie danej kategorii.

3 – Średnią wartość książek w oddziale.

4 – Procentową ilość posiadanych książek.

5 – Na podstawie wyników sprzedaży wyliczyć premię.

Zadanie 1

Należy umieścić nagłówek Biblioteka w polu Wiersze (wszystkie wartości kolumny Biblioteka zostaną umieszczone w wierszach tabeli) oraz nagłówek Rok wydania również w polu Wiersze (wszystkie wartości kolumny Rok wydania zostaną umieszczone w wierszach tabeli) oraz nagłówek Tytuł w polu Wartości (spowoduje zliczenie wszystkich wartości kolumny Tytuł ze względu na zmienne zawarte w polu Biblioteka oraz Rok wydania).

Mając tak przygotowane dane nic nie stoi na przeszkodzie by je zilustrować graficznie w postaci wykresu. Wystarczy tylko zaznaczyć dowolną komórkę w obrębie utworzonej tabeli przestawnej i z karty Wstawiane wybrać ikonę Wykres przestawny. Do utworzonej tabeli przestawnej zostanie dodany wykres.

Inny wariant tego samego zadania możemy uzyskać np. poprzez przeniesienie nagłówka tabeli Biblioteka do pola tabeli przestawnej Kolumny.

Zadanie 2

Do wykonania tego zadania została użyta opcja jednoczesnego wstawienia tabeli przestawnej i wykresu przestawnego (opcja dostępna na karcie Wstawianie).

Aby znaleźć odpowiedź o ilość książek w danej kategorii pole nagłówka Kategoria zostało przeniesione do pola tabeli Wiersze a nagłówek Tytuł do pola Wartości.

Dodatkowo nagłówek Biblioteka został umieszczony w polu Filtry. Umieszczenie tego nagłówka w polu Filtry da nam możliwość filtrowania ilości książek w obrębie danej filii. Poniżej użycie opcji filtra tak by ukazać liczbę książek należących do danej kategorii w obrębie oddziału Filia 1 (wykres aktualizuje się automatycznie).

Zadanie 3

Aby wyliczyć średnią wartość książek w każdym z oddziałów należy nagłówek Biblioteka przenieść do pola Wiersze a następnie nagłówek Wartość do pola Wartości. Po tak przeprowadzonej operacji uzyskamy informację o wartości wszystkich książek znajdujących się w danym oddziale. Dodatkowo kolumna tabeli przestawnej została sformatowana tak by wartości były pokazane w formie waluty.

Czy taką informację chcieliśmy uzyskać? Wyliczyliśmy wartość książek w oddziale a przypominam, że zadanie zakładało obliczenie średniej wartości książek. Domyślnie ustawienie powoduje, że pole Wartości sumuje bądź zlicza elementy umieszczonego w niej nagłówka aby zaś wyliczyć średnią musimy opcję tą ustawić ręcznie.

Aby zmienić domyślne działanie pola Wartości z sumowania na wyliczenie średniej należy kliknąć na strzałkę znajdującą się w polu nagłówka Suma z Wartość i z rozwijanego menu wybrać opcję Ustawienia pola wartości (punkt 1). W kolejnym kroku w nowo otwartym oknie w sekcji Podsumuj pole wartości według wybrać pozycję Średnia (punkt 2).

Wprowadzone zmiany zatwierdzamy klawiszem OK. Domyślna operacja sumowania zostaje zastąpiona wyliczeniem średniej.

Zadanie 4

W pierwszym kroku (punkt 1) umieszczamy dwa razy nagłówek Tytuł w polu Wartości a następnie nagłówek Biblioteka w polu Wiersze. Po wykonaniu czynności uzyskamy tabele w której zostanie obliczona ilość książek w każdym z oddziałów. Następnie zmieniamy sposób podsumowania tak by uzyskać wartość procentową posiadanych książek. Analogicznie jak w zadaniu 3 wybieramy opcję Ustawienia pola wartości lecz tym razem będziemy modyfikować opcje znajdujące się na karcie Pokazywanie wartości jako. Po uaktywnieniu karty w sekcji Pokaż wartości jako z rozwijanego menu wybieramy % sumy końcowej (punkt 2).

Po wprowadzeniu zmian uzyskamy wynik procentowy obrazujący ilość książek w oddziale. Dodatkowo wynik ten został sformatowany z wykorzystaniem Formatowania warunkowego z wykorzystaniem pasków danych.

Jedną z przydatnych opcji często wykorzystywaną w pracy z tabelami przestawnymi jest możliwość grupowania jej elementów. Grupowanie możemy realizować na dwa sposoby możemy wykonać je ręcznie oraz automatycznie. Grupowane mogą być elementy znajdujące się w polach tabeli przestawnej Wiersze oraz Kolumny.

Rozwińmy nasze zadanie i wykonajmy grupowanie, które będzie wykonane według założenia iż Filia 1 oraz Filia 3 biblioteki głównej leżą w jednej dzielnicy miasta zaś Filia 2, 4, 5 oraz 6 są umiejscowione w innej.

Aby utworzyć pierwszą grupę (nazwijmy ją Dzielnica 1) przy pomocy klawisza Ctrl zaznaczamy pozycję Filia 1 oraz Filia 3 i po kliknięciu PPM z menu kontekstowego wybieramy Grupuj.

Analogicznie postępujemy z resztą pozycji.

Dodatkowo domyślne nazwy grup zostały zmienione na Dzielnica 1 oraz Dzielnica 2.

Aby powrócić do ustawień początkowych należy kliknąć na nawę grupy i z menu wybrać opcję Rozgrupuj.

Wspomniałem również o grupowaniu automatycznym, grupowanie te dostępne jest tylko dla pól zawierających liczby, daty bądź godziny. Dlatego zasadę działania tego typu grupowania przedstawię na osobnym przykładzie. Załóżmy, że dysponujemy tabelą zawierająca daty wraz z wartością wpływu na konto przelewów w tym dniu (przykład znajdziesz po pobraniu pliku na arkuszu data) i chcemy uzyskać informację o miesięcznej i kwartalnej wysokości wpływów.

W pierwszym kroku tworzymy tabelę przestawną w której w polu Wiersze zostaje umiejscowiony nagłówek Data natomiast w polu Wartości nagłówek Wpływy. Aby wyliczyć żądane wartości wybieramy dowolną komórkę z kolumny zawierające daty i po kliknięciu PPM wybieramy Grupuj. W nowo otwartym oknie Grupowanie w sekcji Według określamy zakres grupowania (w naszym przypadku należy zaznaczyć Dni, Miesiące oraz Kwartały). Dodatkowo można zdefiniować okres grupowania, który określamy poprzez zdefiniowanie daty początkowej i końcowej.

Po wykonaniu wszystkich czynności daty zostaną zgrupowane według dni, te zaś zostaną połączone w miesiące a te w kwartały.

Aby ułatwić sobie zadanie przeglądania zgromadzonych danych możemy wykorzystać nową opcję filtrowania jaką jest oś czasu. Opcję tą znajdziemy na karcie Analiza. Po wybraniu ikony Wstaw oś czasu będziemy musieli określić do której kolumny tabeli przestawnej będzie stosowane filtrowanie. Dużego wyboru nie mamy gdyż opcja ta współpracuje jedynie z danymi typu czas a jedyną kolumną zawierającą tego typu dane jest kolumna Data. Tak więc w nowo otwartym oknie odhaczamy opcję Data.

Po zastosowaniu opcji ukarze się nam nowy obiekt – oś czasu za pomocą osi będziemy mogli dokonywać filtrowania danych zawartych w tabeli przestawnej. Poniżej przykład w którym zostały zaznaczone na osi dwa miesiące kwiecień i maj, jak można zauważyć tabela po prawej wyświetla tylko dane z tego okresu.

Zadanie 5

Podczas pracy z tabelami przestawnymi bardzo często dochodzi do sytuacji w której musimy dokonać pewnych obliczeń. Obliczenia te bazują na danych już umieszczony w tabeli przestawnej. Aby zaprezentować sposób wykonania takowych obliczeń musimy posłużyć się nowym arkuszem - dane potrzebne do tego ćwiczenia znajdziesz w arkuszu Premia. W arkuszu zostały zebrane miesięczne obroty czterech przedstawicieli handlowych. Umowa z pracownikami gwarantuje wypłacenie premii kwartalnej, która zależy od osiągniętej wartości obrotów uzyskanych przez każdego z przedstawicieli. Premię należy wyliczyć według schematu poniżej:

Kwartał 1 - 5% wartości sprzedaży osiągniętej w styczniu, lutym i marcu,

Kwartał 2 - 6% wartości sprzedaży osiągniętej w kwietniu, maju i czerwcu,

Kwartał 3 - 5% wartości sprzedaży osiągniętej w lipcu, sierpniu i wrześniu,

Kwartał 4 - 6% wartości sprzedaży osiągniętej w październiku, listopadzie i grudniu.

Oprócz premii kwartalnych obliczymy również całość premii: Suma – roczna wartość premii.

Podczas pracy z tabelami przestawnymi obowiązują pewne reguły i zasady, które uniemożliwiają wykonanie pewnych czynności jakie by były dozwolone gdybyśmy pracowali na standardowym arkuszu. Ograniczenia te sprowadzają się do niemożności wstawiania nowych kolumn, wierszy oraz do wykorzystania formuł, które będą wykonywać obliczenia na podstawie danych, które są zawarte w tabeli przestawnej. Lecz zdarzają się sytuacje w których jednak takowe obliczenia musimy przeprowadzić. W takich sytuacjach możemy skorzystać z pól obliczeniowych bądź elementów obliczeniowych. Tak więc element obliczeniowy jest nowym wierszem, który będzie odwoływał się do danych zawartych w tabeli przestawnej a także będzie wykonywał zdefiniowaną formułę.

Arkusz do tego zadania został przedstawiony poniżej a nasze obliczenia rozpoczynamy od kliknięcia wiersza tabeli i przejściu na zakładkę Analiza. W następnym kroku należy rozwinąć menu zawarte pod ikoną Pola, elementy i zestawy i wybrać opcję Element obliczeniowy.

W nowo otwartym oknie definiujemy nowo tworzony element obliczeniowy. W pierwszym kroku należy podać jego nazwę a następnie określić realizowaną formułę. Naszym zadaniem jest wyliczenie premii za pierwszy kwartał więc element został nazwany Kwartał 1. Formuły w przypadku tabel przestawnych są definiowane inaczej niż jak to ma się zazwyczaj i aby obliczyć premię przysługującą za pierwszy kwartał wpisujemy =5%*( a następnie w sekcji Pola odszukujemy pole Miesiąc po zaznaczeniu którego w sekcji Elementy pojawią się nazwy miesiący. Premia ma stanowić 5% wartości sprzedaży z trzech pierwszych miesięcy roku więc należy miesiące te zsumować. Klikamy kolejno nazwy miesięcy oddzielając je oczywiście znakiem +. Po wprowadzeniu formuły nie zapominamy o zamknięciu nawiasu. Cała formuła przyjmie więc postać: =5%*(styczeń+luty+marzec) Po zdefiniowaniu formuły aby ją zapisać klikamy przycisk Dodaj po czym możemy przejść do budowania kolejnego elementu obliczeniowego. Całość wprowadzonych ustawień zatwierdzamy klawiszem OK.

Pozostałe formuły budujemy analogicznie jak tą pierwszą zmieniając oczywiście wartość procentową premii i miesiące. Element Suma wyliczamy z wykorzystaniem wcześniej zdefiniowanych pól kwartalnych. Po wprowadzeniu wszystkich formuł powinniśmy uzyskać 5 dodatkowych wierszy tabeli przestawnej w których będą wyliczone premie pracowników.

Wspomniałem, że oprócz elementów obliczeniowych można również stosować pola obliczeniowe. Pole obliczeniowe jest sposobem na dodanie kolejnych informacji, które zostały wyliczone na podstawie danych zawartych w innych polach (podobnie jak to się ma z elementami tylko z tą różnicą, że w sekcji pól tabel przestawnych nowo zdefiniowane pole będzie miało swoje odzwierciedlenie, element obliczeniowy do tabeli dodaje tylko nowy wiersz).

Aby wstawić nowe pole obliczeniowe należy kliknąć na dowolna komórkę tabeli przestawnej i z zakładki Analiza wybrać opcję Pola, elementy i zestawy a następnie z menu element Pole obliczeniowe.

Spróbujmy więc do naszej tabeli przestawnej dodać nowe pole obliczeniowe. Zadaniem tego pola będzie przedstawienie wartości wyliczonej w złotówkach a umieszczonej w kolumnie Suma końcowa jako wartość Euro.

Po wybraniu opcji wstawienia nowego pola, podobnie jak to miało miejsce w przypadku elementu obliczeniowego należy zdefiniować nazwę pola oraz formułę. Nazwa pola przyjęła wartość Euro natomiast formuła została zdefiniowana jako: =Obroty/<kurs_waluty> (taka formuła gdyż wartość Sumy końcowej jest wyliczana na podstawie nagłówka Obroty).

Po zatwierdzeniu klawiszem OK nowe pola obliczeniowe zostaną umieszczone w tabeli.

Aby element obliczeniowy bądź pole obliczeniowe usunąć a także zmodyfikować wybieramy interesującą nas pozycję z menu Pola, elementy i zestawy a następnie po wybraniu nazwy elementu w zależności od zamiaru klikamy przycisk Modyfikuj bądź Usuń.

Dodatkowo aby ułatwić sobie reprezentację danych możemy zdecydować się na zastosowanie tzw. fragmentatora (ang. slicer). Fragmentator jest interaktywnym polem, które umożliwia zastosowanie filtrowania. Przypuśćmy, że musimy z zdefiniowanej tabeli przestawnej uzyskać informację o obrotach przedstawiciela Andrzej uzyskanych w miesiącu marzec. Oczywiście dane te możemy w prosty sposób odszukać w tabeli lecz zdarza się, że przy dużym skomplikowaniu samej tabeli i dużej ilości zebranych informacji możemy popełnić błąd i potrzebne nam wartości odczytamy z sąsiedniej komórki. Popełnienia tego błędu unikniemy przy wykorzystaniu fragmentatora.

Wstawienie fragmentatora sprowadza się do odszukania na karcie Analiza ikony Wstaw fragmentator i zdefiniowania pól co do których będzie przeprowadzane filtrowanie. W naszym przypadku fragmentator będzie miał zastosowanie do pól Konsultant oraz Miesiąc.

Po wybraniu OK do arkusza zostaną dodane dwa fragmentatory. Odszukanie interesującej nas wartości obrotów przedstawiciela Andrzej z miesiąca marca sprowadza się tylko do zaznaczenia odpowiednich pól.

Dane zebrane w tabeli przestawnej można użyć do obliczeń. Oznacza to, że możemy je wykorzystać by uzyskać potrzebne nam informacje. Pozostajemy jeszcze przez chwilę przy naszym arkuszu i spróbujmy wyliczyć wartość obrotu. Po dodaniu nowych wierszy w tabeli przestawnej (tych wyliczających wartość premii) końcowa suma nie odpowiada już wartości obrotu uzyskanego przez każdego z przedstawicieli. Aby uzyskać wartość obrotu od wartości Suma końcowa należy odjąć wartość pola Premia, tak jak to zostało przedstawione na zrzucie poniżej.

Z prawej strony została utworzona nowa tabela, która odzwierciedla wartość uzyskanego obrotu. Wartość ta poprzez zbudowanie formuły oznaczonej w ramce odzwierciedla osiągnięty obrót. Jak widać formuła ta bazuje na funkcji WEŹDANETABELI. Zastosowana funkcja nie należy do prostych lecz na nasze szczęście nie musimy jej wprowadzać ręcznie - wystarczy, że budując formułę skorzystamy z myszki by za jej pomocą zaznaczać interesujące nas komórki. Aby obliczyć obrót musimy wykonać operację odejmowania czyli całe obliczenie sprowadza się do wybrania znaku = i kliknięciu komórki B10, wprowadzeniu znaku minus i kliknięciu komórki B9. Cała formuła zostanie wprowadzona automatycznie.

Pewnie Czytelniku zadajesz sobie pytanie - Dlaczego by nie wprowadzić formuły: = B10-B9? Taką formułę można wprowadzić i obliczenie będzie prawidłowe ale po rozwinięciu zgrupowanych pól pojawią się nowe wiersze i zawarte w nich dane nie będą odpowiadać wartością prawidłowym a co za tym idzie wartość uzyskanego obrotu będzie nieprawidłowa. Wykorzystanie myszki i wskazania interesujących nas komórek zapewni nam poprawność wyniku nawet gdy nasza tabela przestawna będzie ulegać zmianie. Poniżej zaprezentowano rozwinięcie komórek Kwartał 1 oraz Kwartał 3, jak widać wynik uzyskanego obrotu nie uległ zmianie.

I w tym miejscu Chciałbym rozważania na temat tabel przestawnych zakończyć. Myślę, że zaprezentowane zadania wraz z omówieniem zachęcą Cię do korzystania z tego mechanizmu przy tworzeniu swoich własnych arkuszy.

Excel w zadaniach. Funkcje tablicowe

2016-03-04T09:00:58+00:00

Arkusz kalkulacyjny poza nazwijmy to „tradycyjnym” sposobem operowania na danych zawartych w arkuszu oferuje pewną funkcjonalność, która w sposób bardziej wydajny pozwala nam przeprowadzać wszelkiego rodzaju obliczenia. Sposób ten bazuje na wykorzystaniu formuł tablicowych zwanych też formułami CSE, gdyż celem zatwierdzenia formuły tego typu należy posłużyć się kombinacją klawiszy Ctrl+Shift+Enter. Mechanizm ten swoją wyższość pokazuje w arkuszach w których to musimy operować na dużej liczbie powtarzalnych formuł. Użycie tej funkcjonalności spowoduje skrócenie czasu przygotowania arkusza oraz zapewni spójność użytych formuł a już na pewno wyniesie tworzone przez nas arkusze na kolejny poziom wtajemniczenia.

Arkusz z przykładami opisanymi w artykule można pobrać - TUTAJ

MS Excel obsługuje dwa rodzaje formuł tablicowych:

jednokomórkowe formuły tablicowe – tworzone formuły operują na danych zapisanych w wielu komórkach (w formule definiowany jest zakres) jako wynik działania formuły zwracana jest wartość, która zapisana jest w jednej komórce.
wielokomórkowe formuły tablicowe - formuły te operują na danych zapisanych w wielu komórkach lecz zwracany wynik jest umieszczany w zakresie komórek.

Dodatkowo w definicji formuł tablicowych wielokomórkowych określa się takie pojęcia jak: tablica jednowymiarowa i tablica dwuwymiarowa. Pierwsze z pojęć określa tablicę, której wymiary (obszar komórek tworzących tablicę) sprowadzają się do jednego wiersza lub jednej kolumny. Tablica dwuwymiarowa swym zasięgiem obejmuje zarówno wiersz i kolumnę (prostokątny obszar komórek).

Tak by zobrazować rodzaje formuł omówmy przykłady. Zaczniemy od formuły tablicowej wielokomórkowej.

Na rysunku poniżej został przedstawiony arkusz, którego celem jest wyliczenie wypłaty w zależności od liczby przepracowanych godzin i ustalonej stawki.

Przy użyciu tradycyjnej formuły zamieszczonej w komórce N5: =L5*M5 można obliczyć wartość wypłaty. Aby wartość wypłaty mogła być obliczona dla pozostałych osób, wprowadzoną formułę należy skopiować w dół. Gdy całość zostanie wykonana w kolumnie N zostanie umieszczone 10 formuł.

Te same zadanie, można by było wykonać z użyciem jednej formuły pod warunkiem, że będzie to formuła tablicowa.

Aby wykonać wyliczenie wartości wypłat wykorzystując do tego funkcję tablicową należy skorzystać z poniższej procedury:

Punkt 1: Zaznaczyć obszar obejmujący zakres komórek od N5 do N14, ponieważ w tym zakresie mają pojawić się wyniki.

Punkt 2: Wpisać następującą formułę: =L5:L14*M5:M14 (po zaznaczeniu komórek można użyć klawisz F2 celem wprowadzenia formuły).

Punkt 3: Aby zatwierdzić formułę należy użyć kombinacji klawiszy: Ctrl+Shift+Enter (nie Enter). Po wykonaniu wszystkich czynności zaznaczone komórki powinny uzupełnić się wynikami.

W wpisie dotyczącym adresacji bezwzględnej opisałem jak przy wykorzystaniu „dolara” przygotować arkusz zawierający tabliczkę mnożenia - http://slow7.pl/item/25-excel-w-zadaniach-adresacja-bezwzgledna. To samo zadanie można wykonać przy wykorzystaniu funkcji tablicowych. Wykonanie zadania sprowadza się do zaznaczenia zakresu komórek od B3 do K12 a następnie wpisania formuły: =B2:K2*A3:A12 i zatwierdzenia jej klawiszami Ctrl+Shift+Enter

Wykonując opisane czynności zyskujemy pewność, że wszystkie formuły w tablicy są spójne - trudniej popełnić błąd.

Przy pracy z tablicami należy dodatkowo pamiętać, że niemożliwa jest zmiana zawartości pojedynczej formuły. Oznacza to, że chcąc zmodyfikować formułę np. w komórce K3 zadanie te zakończy się informacją o błędzie. Analogicznie błąd wystąpi podczas próby skasowania formuły.

Dodatkowo niemożliwe jest wykonanie operacji usuwania oraz wstawiania kolumny bądź wiersza gdyż wiązałoby się to ze zmianą rozmiarów zdefiniowanej tablicy.

Zmianę wprowadzonej formuły bądź jej skasowanie możemy dokonać po zaznaczeniu całej tablicy. Zaznaczenie tablicy dokonujemy:

należy kliknąć dowolną komórkę w zakresie formuły tablicowej i z klawiatury wybrać skrót: Ctrl+/,
należy kliknąć dowolną komórkę w zakresie formuły tablicowej (punkt 1), wybrać ikonę Znajdź i zaznacz i z rozwiniętego menu opcję Przejdź do - specjalnie (punkt 2). W nowo otwartym oknie wybieramy opcję Bieżąca tablica (punkt 3)

Sposób działania formuły tablicowej wielokomórkowej został przedstawiony, przejdźmy zatem do przykładów ilustrujących działanie formuły tablicowej jednokomórkowej.

Powróćmy do przykładu wyliczenia wypłat i spróbujmy wykonać operację, której efektem będzie poznanie wartości wszystkich wypłat. W tradycyjny sposób wykonalibyśmy krok pośredni polegający na wyliczeniu wypłat cząstkowych (każdego z pracowników) by w kroku kolejnym uzyskane wyniki wypłat do siebie dodać.

To samo zadanie można by było wykonać z pominięciem wyliczenia wypłat poszczególnych pracowników, warunkiem jest skorzystanie z funkcji tablicowej. Należy skorzystać z formuły: =SUMA(L5:L14*M5:M14) (pamiętamy o zatwierdzeniu formuły klawiszami: Ctrl+Shift+Enter)

Działanie funkcji tablicowej sprowadza się do wymnożenia przez siebie wartości z określonych zakresów (w pamięci komputera powstaje nowa tablica składająca się z wartości wykonania działania liczba godzin * stawka) a następnie ich dodania.

W tym przypadku alternatywą dla formuły tablicowej mogłaby być funkcja: SUMA.ILOCZYNÓW

Funkcję tablicową również możemy użyć do policzenia wystąpień danego zestawu danych w zależności od ustalonych kryteriów. Celem zilustrowania przykładu posłużmy się używanym do tej pory zestawem danych.

Spróbujmy ustalić liczbę osób, które pracowały ponad 30 godzin. Aby zrealizować zadanie należy posłużyć się formułą tablicową: =SUMA((G4:G13>30)*1)

Formuła ta działa ponieważ funkcja SUMA w tym przypadku nie sumuje wartości tylko je zlicza. Dzieje się tak ponieważ funkcja SUMA wykonuje dodawanie logicznego mnożenia. Spełnienie warunku powoduje przekazanie do funkcji SUMA działania: PRAWDA (wartość ta równa się 1) x 1 Wartość 1 (jeśli warunek jest spełniony) razy 1 (z definicji formuły) daje w wyniku 1. Suma wszystkich jedynek jest liczbą osób spełniających ustalony warunek.

W przypadku takim jak ten gdzie został ustalony tylko jeden warunek należy pamiętać, że warunek ten musi zostać wymnożony przez wartość 1. W przeciwnym wypadku funkcja nie zadziała.

Budowa formuły nie musi ograniczać się do jednego warunku. Spróbujmy zatem ustalić liczbę osób spełniających dwa warunki: liczba godzin poniżej 20 z stawką powyżej 20 zł.

Formuła, która wyliczy liczbę osób spełniające oba kryteria będzie miała postać: =SUMA((G4:G13<20)*(H4:H13>20))

Wynikiem działania formuły jest wartość 3. W przypadku tym nie musieliśmy definiować mnożenia przez 1 gdyż formuła ta została zbudowana poprzez zdefiniowanie dwóch warunków. Działanie funkcji opiera się na założeniu: PRAWDA (wartość 1) razy FAŁSZ (wartość 0) = 0 W przypadku uzyskania dwóch wartości PRAWDA (działanie 1 x 1 = 1) otrzymujemy wartość 1 w każdym innym przypadku wartość wyniesie 0 (mnożenie przez 0 zawsze da 0) Wartości uzyskane dzięki wykonaniu logicznego mnożenia zostają przekazane do funkcji SUMA i są do siebie dodawane. Wynik dodawania jest liczbą osób spełniających oba kryteria.

Liczba definiowanych warunków jest oczywiście zależna od naszych potrzeb.

Zanim przejdziemy dalej jeszcze jeden przykład, który pokaże jak zsumować wartości wypłat od ustalonego kryterium. Celem lepszego zobrazowania działania formuły do arkusza została dodana jeszcze jedna kolumna, przyporządkowująca pracownika do konkretnego działu firmy. Arkusz przedstawia się następująco.

Naszym celem jest zsumowanie wypłat działu kadry. Aby zsumować wypłaty należy posłużyć się formułą: =SUMA(JEŻELI(H4:H13=J16;K4:K13;0))

Zadaniem użytej funkcji JEŻELI jest sprawdzenie czy w zdefiniowanym zakresie od H4 do H13 wystąpi zmienna określona w komórce J16 (kadry) W przypadku spełnienia warunku do funkcji SUMA zostaje przekazana odpowiadająca wartość z zakresu od K4 do K13. Wynikiem działania formuły jest zsumowanie wszystkich wartości wypłat działu kadry.

W przedstawionych przykładach tablice były przechowywane w komórkach arkusza, lecz wcale tak być nie musi. Tablicę również możemy definiować sami a jej lokalizacją może być formuła. Tablica zdefiniowana w ten sposób będzie przechowywana w pamięci komputera. Tablica ta nosi nazwę stałej tablicowej.

Tworzenie stałej tablicowej sprowadza się do wypisania jej elementów, które są objęte nawiasami klamrowymi - {}. Przykładowa tablica może zostać zdefiniowana w ten sposób - {2;6;10;14}

Tak określoną tablicę możemy oczywiście łączyć z funkcjami - np. celem dodania elementów tablicy można połączyć ją z funkcją SUMA: =SUMA({2;6;10;14}) Wynikiem funkcji będzie liczba 32. Co należy zaznaczyć, to to, że w przypadku definiowania stałej tablicowej nawiasy klamrowe umieszczamy sami.

Definicja tworzonych formuł nie musi sprowadzać się do użycia tylko jednej stałej tablicowej lecz możemy użyć ich wiele - wszystko zależy od potrzeb i charakteru tworzonej formuły. Poniżej przykład z wykorzystaniem dwóch stałych tablicowych. W przykładzie została użyta formuła: =SUMA({2;6;10;14}-{1;2;3;4})

Funkcjonowanie formuły opiera się na wykonaniu określonych działań na zdefiniowanych tablicach (w tym przypadku realizowane jest odejmowanie oraz sumowanie). Wyliczenie sprowadza się do wykonania następującego działania: (2-1)+(6-2)+(10-3)+(14-4) - w pierwszej kolejności jest wykonywane odejmowanie tablic - co prowadzi do zapisania w pamięci komputera tablicy {1;4;7;10} a następnie zsumowania jej elementów. Wynikiem formuły jest wartość 22.

Stałe tablicowe można łączyć z tablicami przechowywanymi w komórkach arkusza. Poniżej ten sam przykład co powyżej z tą różnicą, że tablica {1;2;3;4} została umieszczona w komórkach od A1 do A4 (umiejscowienie tablicy w kolumnie a nie w wierszu ma swoje konsekwencję ale o tym za chwilę).

W definicji stałej tablicowej mogą być zawarte taki elementy jak: tekst (tekst musi być objęty cudzysłowem); liczby; wartości logiczne czy wartości błędów. W budowaniu stałych tablicowych należy unikać stosowania: innych tablic; funkcji. Definiowane wartości liczbowe muszą być pozbawione takich znaków jak: nawias, kropka, symbol waluty czy procent.

Na początku wpisu zasygnalizowałem, że tablica może być jednowymiarowa lub dwuwymiarowa. Tablica jednowymiarowa może obejmować swym zakresem wiersz bądź kolumnę, co oznacza, że tworzona tablica może zajmować wiersz (orientacja pozioma) bądź kolumnę (orientacja pionowa). Wybór orientacji wymusza na nas zastosowanie odpowiedniego zapisu. Poszczególne elementy tablicy o orientacji poziomej oddzielać od siebie będziemy lewymi ukośnikami zaś elementy tablicy pionowej oddzielamy średnikami. Tak więc przykładowa formuła: ={1\2\3\4\5\6\7} oznaczać będzie tablicę umieszczoną w wierszu zaś formuła: ={1;2;3;4;5;6;7} tablicę umieszczoną w kolumnie. Aby wprowadzić opisane formuły należy zaznaczyć siedem komórek arkusza (siedem gdyż tablica zawiera siedem elementów) i zatwierdzić wpis klawiszami: Ctrl+Shift+Enter

W przypadku tablicy dwuwymiarowej definicję tablicy rozpoczynamy od zdefiniowania zawartości pierwszego wiersza by za pomocą znaku ; przejść do określenia elementów wiersza kolejnego. Tablicę dwuwymiarową definiujemy wierszami. Definicja przykładowej tablicy o wymiarach 5x2 będzie wyglądać następująco: ={1\2\3\4\5;6\7\8\9\10}

Ktoś mógłby zapytać - Czy ten sam efekt uzyskamy tworząc tablicę nie poprzez określenie kolejnych wierszy lecz kolumn? Formuła budująca tabelę miałaby zatem postać: ={1;6\2;7\3;8\4;9\5;10} Niestety tak wprowadzona formuła zakończy się błędem.

Przy ręcznej definicji tablic dwuwymiarowych należy być ostrożnym gdyż tablica musi zawierać taką samą liczbę elementów - każdy wiersz tablicy dwuwymiarowej musi zawierać tą samą liczbę elementów. Poniższa definicja tabeli jest nieprawidłowa, ponieważ w trzecim wierszu zostały określone tylko 4 elementy, gdy zaś w pozostałych dwóch jest ich 5: ={1\2\3\4\5;6\7\8\9\10;11\12\13\14}

Dla ułatwienia budowy przyszłych odwołań definiowane tablice można nazywać. Poniżej przykład przypisania nazwy do tablicy: ={"Sty";"Lut";"Mar";"Kwi";"Maj";"Cze";"Lip";"Sie";"Wrz";"Paź";"Lis";"Gru"}

W oknie Formuły/Nazwy zdefiniowane/Definiuj nazwę można określić nazwę odwołania. Poniżej na przykładzie zdefiniowana została tablica o nazwie: miesiące

Od tego momentu do nazwy tej możemy odwoływać się w ten sam sposób jak to robimy w przypadku wywołania funkcji, zdefiniowana nazwa pojawi się na liście proponowanych.

Nazwę możemy zdefiniować również po zaznaczeniu komórek w których znajduje się tablica (w przykładzie poniżej komórki od B2 do B13) i określeniu nazwy w Polu nazwy.

Do tej pory pokazałem jak utworzyć tablicę tak więc parę przykładów jak przeprowadzić działania na tablicach.

Poniższa funkcja do każdego elementu tablicy dodaje 5: ={1\2\3\4\5;6\7\8\9\10}+5 Oczywiście wykonane działanie nie musi ograniczać się tylko do dodawania.

Działania mogą być przeprowadzane również z wykorzystaniem innych tablic. Na przykład poniższa formuła odejmuje od siebie poszczególne elementy tablic: ={10\20\30\40\50;60\70\80\90\100}-{1\2\3\4\5;6\7\8\9\10}

O ile jak to zostało zaznaczone wcześniej elementem tablicy nie może być funkcja tak nic nie stoi na przeszkodzie aby daną funkcję wykorzystać do wykonania działania na elementach tablicy. Poniżej przykłady połączenia tablicy z funkcją PIERWIASTEK oraz POTĘGA.

=PIERWIASTEK({10\20\30\40\50;60\70\80\90\100})

=POTĘGA({10\20\30\40\50;60\70\80\90\100};2)

Podczas pracy z tablicami bardzo często zachodzi potrzeba zmiany układu tablicy. Najprościej można to wytłumaczyć w ten sposób, że to co mamy umieszczone w wierszu należy mieć w kolumnie (oczywiście sytuacja odwrotna również jest dozwolona). Aby wykonać zadanie należy posłużyć się funkcją TRANSPONUJ.

Zaczniemy od przykładu z funkcją jednowymiarową i spróbujemy transponować tablicę pionową do tablicy poziomej. Poniżej przykład transponowania tablicy nazwanej: tydzień W komórkach od C2 do C8 została wprowadzona funkcja tablicowa: ={"Pon";"Wto";"Śro";"Czw";"Pią";"Sob";"Nie"} Aby zmienić układ tablicy posłużono się funkcją tablicową: =TRANSPONUJ(tydzień), która została umieszczona w zakresie od F2 do L2

Te same zadanie można by było wykonać stosując formuły: =TRANSPONUJ({"Pon";"Wto";"Śro";"Czw";"Pią";"Sob";"Nie"})

bądź: =TRANSPONUJ(C2:C8)

Ciekawe przykłady obrazujące wykorzystanie funkcji tablicowych to te w których coś musimy policzyć.

Poniżej przykład ćwiczenia w którym to powiedzmy uczniowie za poprawne zachowanie i aktywność zdobywali plusy. Na koniec miesiąca dla najaktywniejszych nauczyciel przygotował nagrodę a że konkurowały ze sobą również klasy to należy również policzyć sumę wszystkich zdobytych punktów.

Aby wyłonić zwycięzcę należy policzyć ilość zdobytych plusów. Aby zliczyć plusy zdobyte przez ucznia należy skorzystać z funkcji DŁ. Funkcja zwraca liczbę znaków ciągu tekstowego. Aby policzyć ilość plusów pierwszego ucznia wydajemy komendę: =DŁ(E4) Formułę oczywiście kopiujemy do pozostałych komórek.

Po wykonaniu zliczenia plusów każdego z uczniów by uzyskać ich ogólną ilość należy skorzystać z funkcji SUMA.

Do zliczenia ilości wszystkich plusów można by było użyć o to taką funkcję: =SUMA(DŁ(E4:E8)) Funkcja ta jest funkcją tablicową zliczającą ilość znaków w zakresie komórek od E4 do E8.

Jak widać powyżej uzyskany wynik jest tożsamy z tym jaki uzyskaliśmy w poprzednik kroku.

No to przejdźmy do kolejnego przykładu wykorzystującego funkcje tablicowe. Poniżej na zrzucie został zaprezentowany arkusz w którym zebrano wartości pomiarów dokonanych przez jakiś system pomiarowy (nie jest ważne jaki, lecz celem uproszczenia dalszego opisu przyjmijmy, że jest to temperatura). W kolejnym kroku należy z zebranych danych wyznaczyć średnią trzech najwyższych pomiarów. Aby wybrać trzy najwyższe wartości z zakresu danych należy posłużyć się funkcją: MAX.K (analogicznie działa funkcja MIN.K wybierając z zakresu danych najmniejszą wartość n). Składnia funkcji jest następująca: MAX.K(dane;n) W składni funkcji MAX.K należy określić:

dane - tablica lub zakres danych, z których będzie wyznaczana n-ta największą wartość,
n - pozycja danej (od największej) w tablicy lub zakresie komórek.

Tak więc by wybrać wartość najwyższą z pomiarów należy posłużyć się formułą: =MAX.K(D4:D18;1) zaś by drugą najwyższą: =MAX.K(D4:D18;2) Wartości kolejne wybieramy za pomocą modyfikacji parametru n.

Po ustaleniu trzech najwyższych wartości temperatury z wartości tych zostaje wyciągnięta średnia.

Aby pominąć etap wyznaczenia trzech najwyższych wartości pomiaru i uzyskać od razu średnią możemy posłużyć się taką formułą: =ŚREDNIA(MAX.K(D4:D18;1);MAX.K(D4:D18;2);MAX.K(D4:D18;3))

Zastosowanie formuły da nam wynik średniej trzech najwyższych pomiarów, w przypadku gdybyśmy chcieli wyliczyć wartość dla trzech najniższych pomiarów funkcję MAX.K należy zastąpić MIN.K

Przedstawiona powyżej formuła jest tożsama z formułą: =ŚREDNIA(MAX.K(D4:D18;{1\2\3})) w której do wyliczenia średniej użyto stałej tablicowej.

Podsumowując i kończąc ten wpis należy stwierdzić, że przykładów z użyciem tablic może być wiele a te przedstawione mają za cel pokazanie jak wykorzystać tą funkcjonalność Excela (taki zarys) a także wyższości rozwiązania opartego na tablicach nad tradycyjnym. Mam nadzieję, że po lekturze artykułu część z Was w swoich arkuszach ten mechanizm zacznie stosować a w przypadku zetknięcia się z arkuszami, które zostały zbudowane na bazie funkcji tablicowych ich ewentualna modyfikacja nie sprawi Wam problemów.

Excel w zadaniach. Data i czas.

2015-11-05T21:01:55+00:00

W codziennej pracy z arkuszem Excela często musimy operować datami bądź czasem. Dla przykładu tworzone są arkusze sprzedaży produktów, analizy wyników czy harmonogramy. Początkowa praca z datami może wydawać się trudna i skomplikowana, ale po poznaniu zasad rządzących tym typem danych okazuje się, że nie taki diabeł straszny. Tak więc by posiąść tą wiedzę zapraszam do lektury wpisu.

Zanim przejdziemy dalej kilka słów wstępu i uwag ogólnych. Ustalmy, że w przykładach będziemy operować na dacie w formacie: rok-miesiąc-dzień. Oznacza to, że przykładowa data 12 luty 1980r przyjmie postać: 1980-02-12 (jak zmienić format daty pokażę w dalszej części wpisu).

Aby móc operować na datach w pierwszej kolejności musimy zrozumieć jak Excel intrepretuje datę. Dla programu data jest po prostu liczbą. Liczba ta bądź tzw. numer seryjny odpowiada liczbie dni jaka upłynęła od daty początkowej, którą ustalono na dzień 0 stycznia 1900 roku. Niektórym po przeczytaniu ostatniego zdania od razu zapali się „czerwona lampka” - przecież taka data jak 0 stycznia 1900 rok nie istnieje. Oczywiście jest to prawda, ale trzeba było przy operowaniu datami (a raczej jak już wiesz kolejnymi numerami sekwencyjnymi) wykorzystać liczbę 0. Tak więc fikcyjna data 1900-01-00 odpowiada liczbie 0, a każda kolejna data jest związana ze zwiększaniem numeru sekwencyjnego o 1. Dla przykładu numer seryjny równy 1 to dzień 1 stycznia 1900 roku, numer seryjny równy 2 to dzień 2 stycznia 1900 roku itd.

Aby sprawdzić w działaniu efekt wykorzystania kolejnych numerów sekwencyjnych w dowolnej komórce arkusza wpisujemy liczbę i zmieniamy format wyświetlania. Proces ten możemy wykonać dwojako:

- - wybieramy format z rozwijanej listy w naszym przypadku Data krótka bądź Data długa,
  - po kliknięciu na komórkę PPM z menu kontekstowego wybieramy Formatuj komórki a następnie na karcie Liczby w sekcji Kategoria interesujący nas format daty.

Excel potrafi operować na datach z zakresu od 1 stycznia 1900 r. do 31 grudnia 9999 r. (numer seryjny 2 958 465). Każda inna data nie mieszcząca się w tym zakresie zostanie sformatowana jako tekst.

Poniżej przedstawiono kilka sposobów na wprowadzenie daty do arkusza.

80-2-12	12 luty 1980
1980-2-12	12 luty 1980
12 lut 1980	12 luty 1980
12-luty-1980	12 luty 1980
80/2/12	12 luty 1980
1980/2/12	12 luty 1980
80/2-12	12 luty 1980
1980/2/12	12 luty 1980
lut 12	12 lutego bieżącego roku
12 lut	12 lutego bieżącego roku
2/12	12 lutego bieżącego roku
2-12	12 lutego bieżącego roku

Możliwe jest zastosowanie skrótów miesiąca. Oznacza to, że gdy wpiszemy trzy pierwsze litery miesiąca zostanie to zinterpretowane przez program jako nazwa miesiąca. Zasada ta nie dotyczy miesiąca sierpnia - by skorzystać ze skrótu należy użyć: sier

Ale data to nie tylko dzień, miesiąc i rok, ale również godziny, minuty oraz sekundy. Excel umożliwia nam również pracę na danych reprezentujących czas. Czas w Excelu jest reprezentowany poprzez wartość dziesiętną, która jest dodawana do numeru seryjnego. Oznacza to, że w przypadku wpisania numeru seryjnego 42186, numer ten zostanie zinterpretowany (oczywiście po zmianie formatowania) jako data: 1 lipca 2015, godzina zostanie ustawiona na północ. Poszerzenie liczby o wartość dziesiętną spowoduje dodanie do daty informacji o czasie. Do tego samego numeru sekwencyjnego 42186 dodajemy połówkę - 42186,5 - Excel tak wyrażoną liczbę po sformatowaniu (format Data długa) będzie traktował jako datę: 1 lipca 2015, godzina 12.00

Poniżej przedstawiono przykładowe wartości dziesiętne określające czas (w pierwszej kolumnie wartość godziny):

3	0,125
6	0,25
9	0,375
12	0,5
15	0,625
18	0,75
21	0,875

Lecz co w przypadku gdy chcemy skorzystać z czasu w formie bardziej zaawansowanej tak by móc określić czas w obrębie pojedynczych minut? Oczywiście zadanie to sprowadza się do wpisania odpowiedniej wartości, która jest wyliczana przy wykorzystaniu formuły: =1/(24*60). Uzyskana wartość 0,00069444 odpowiada jednej minucie. Np. chcąc uzyskać datę: 1 lipca 2015 godzina 11:32 należy użyć wartości: 42186,48056. Obliczenie konkretnej wartości czasu może okazać się nieco problematyczne, gdyż należy uważać na zaokrąglenia wartości.

Dodatkowo, by w ten sposób zacząć wprowadzać czas, w którym będą uwzględnione sekundy, trzeba posłużyć się wartością: 0,00001157 (formuła: =1/(24*60*60) - 1/(godziny*minuty*sekundy), która odpowiada jednej sekundzie. Na przykład dacie: 2015-12-31 23:59:59 odpowiada wartość: 42369,9999884259.

Aby do arkusz wprowadzić informację o czasie tak, by Excel poprawnie zinterpretował te dane, można skorzystać z jednego przedstawionych poniżej formatów wprowadzania czasu:

10:30:00 am	10:30 AM
10:30:00 AM	10:30 AM
10:30 pm	10:30 PM
10:30	10:30
17:30	17:30

Podczas procesu wprowadzania dat do arkusza kalkulacyjnego należy być bardzo uważnym, gdyż format daty, do której najczęściej jesteśmy przyzwyczajeni: dzień:miesiąc:rok nie zostanie poprawnie zinterpretowany przez program jako data. Na szczęście możemy to zmienić. By dokonać tej zmiany musimy przejść do Panelu sterowania, a następnie do Region bądź Opcje regionalne i językowe (zależne od posiadanej wersji systemu Windows). W nowo otwartym oknie, na karcie Formaty, wybieramy Ustawienia dodatkowe. W kolejnym oknie Dostosowywanie formatu, na karcie Data, w sekcji Formaty daty określamy obowiązujący format (punkt 1). Format daty definiujemy za pomocą znaków: d - dzień; M - miesiąc; r - rok. Chcąc by obowiązującym formatem daty był zapis np. 23.04.1980 format musimy zdefiniować jako: dd.MM.rrrr

W przypadku definiowania daty dodatkową uwagę należy zwrócić na sytuację, w której rok podajemy jako zapis dwucyfrowy. Excel dwucyfrowe daty lat z zakresu od 00 do 29 traktuje jako lata XXI wieku, zaś daty lat z zakresu od 30 do 99 zostaną potraktowane jako lata wieku XX. By zmienić to ustawienie należy przejść do tego samego okna, w którym definiowaliśmy obowiązujący format daty i w sekcji Kalendarz zdefiniować podział wieku (punkt 2). By przypisać wszystkie dwucyfrowe daty lat do wieku XXI należy w polu do wpisać wartość 2100 (wartość 00 będzie traktowana jako rok 2100).

Po tym krótkim wstępie teoretycznym przejdziemy do zadań związanych z datami i czasem.

Pierwszą z funkcji, którą chciałbym zaprezentować jest funkcja: DZIŚ(). Zadaniem tej funkcji jest wyświetlenie bieżącej daty, przy czym wyświetlana data nie jest związana z czasem wstawienia funkcji do arkusza. Oznacza to, że funkcja ta będzie automatycznie korygować bieżącą datę. Gdy otworzymy arkusz 1 kwietnia 2015 roku zostanie wyświetlona ta data, zaś gdy ten sam arkusz otworzymy dzień później data zmieni się na 2 kwietnia 2015 roku. Funkcja ta nie posiada żadnych paramentów, tak więc by jej użyć stosujemy zapis: =DZIŚ()

Możliwe jest połączenie funkcji DZIŚ z tekstem. Aby np. wyświetlić zapis: Dzisiaj jest: 13-10-2015 (wtorek) należy użyć formuły: ="Dzisiaj jest: "&TEKST(DZIŚ(); "d-mm-rrrr (dddd)") Do wyświetlenia tej formułki została dodatkowo użyta funkcja TEKST, której działania wybacz Czytelniku w tym wpisie nie będę tłumaczył (funkcjami tekstowymi zajmiemy się wkrótce). Z funkcją DZIŚ możemy stosować dodatkowo operatory dodawania (+) oraz odejmowania (-), np. zapis: =DZIŚ()+3 spowoduje wyświetlenie daty o trzy dni do przodu.

Analogicznie, by do arkusza wprowadzić informację o czasie, należy użyć funkcji: TERAZ. Formuła: =TERAZ() spowoduje wyświetlenie bieżącej daty w połączeniu z aktualną godziną. Funkcja TERAZ również zaktualizuje czas po ponownym otwarciu arkusza (by ręcznie wymusić aktualizację czasu wciśnij klawisz F9). W tej funkcji również możliwe jest użycie operatorów dodawania (+) oraz odejmowania (-).

Aby wprowadzić datę i czas można posłużyć się dodatkowo skrótami:

- - aby wstawić bieżącą datę, naciśnij kombinację klawiszy CTRL+; (średnik).
  - aby wstawić bieżącą godzinę, naciśnij kombinację klawiszy CTRL+SHIFT+; (średnik).
  - aby wstawić bieżącą datę i godzinę, naciśnij kombinację klawiszy CTRL+; (średnik), naciśnij klawisz spacji, a następnie naciśnij kombinację klawiszy CTRL+SHIFT+; (średnik).

Dowolną datę możemy również wprowadzić do arkusza z wykorzystaniem funkcji: DATA. Przykładowy zapis formuły: =DATA(1980;4;21) wprowadzi do arkusza datę 21 kwietnia 1980 roku. Oczywiście zamiast definiowania konkretnej wartości daty w definicji formuły możemy użyć odwołania do komórek, tak jak na poniższym przykładzie.

Analogicznie działa funkcja: CZAS. Zapis formuły: =CZAS(14;34;15) spowoduje wprowadzenie do arkusza godziny 14 minut 34 i 15 sekund. I tu również zamiast definiować stałe wartości możemy je pobrać z określonych komórek.

Aby uzyskać wartość liczbową konkretnej daty możemy posłużyć się funkcją DATA.WARTOŚĆ Funkcja ta zamienia wprowadzony tekst spełniający wymogi formatu data na jej numer sekwencyjny. Poniżej na zrzucie przedstawiono zamianę daty 2000-01-01 na jej odpowiednik liczbowy.

Podczas pracy z Excelem często zachodzi potrzeba wypełnienia komórek sekwencją dat. Daty te z reguły różnią się od siebie jakimś stałym odstępem czasowym, np. dniami czy miesiącami. Aby wygenerować taką sekwencję dat możemy posłużyć się mechanizmem autowypełnienia. Generowanie dat sprowadza się do wpisania pierwszej daty, a następnie na kliknięciu PPM na uchwyt wypełnienia (dla przypomnienia kwadracik w dolnym lewym rogu komórki) i wybraniu interesującego nas interwału. Do dyspozycji mamy cztery opcje: wypełnij dniami; wypełnij dniami powszednimi; wypełnij miesiącami oraz wypełnij latami.

Niestety Excel nie oferuje nam wypełnienia serią dat, których interwał obejmowałby tydzień. Ale jest i na to rada gdyż by rozwiązać ten problem możemy posłużyć się odpowiednią formułą. Aby daty były odległe od siebie o 7 dni możemy użyć o to takiej formuły: =C1+7 Oczywiście w komórce C1 umieszczamy datę początkową.

Aby daty odległe były od siebie o jeden miesiąc użyj formuły: =DATA(ROK(C1);MIESIĄC(C1)+1;DZIEŃ(C1))

Zaś by otrzymać serię dat z interwałem wynoszącym rok: =DATA(ROK(C1)+1;MIESIĄC(C1);DZIEŃ(C1))

Serię dni tygodnia bez weekendów (sobót i niedziel) wygenerujesz za pomocą funkcji (początkowy dzień nie może być ani sobotą ani niedzielą): =JEŻELI(DZIEŃ.TYG(C1)=6;C1+3;C1+1)

Całkiem innym zagadnieniem jest gdy daty importujemy z np. pliku tekstowego, który jest wynikiem utworzenia np. z jakiegoś systemu pomiarowego. Aby zamienić przykładowy tekst reprezentujący datę na format daty akceptowalny przez Excela można wykorzystać taką oto formułę (data w formacie tekstowym to: 29082015): =DATA(PRAWY(C1;4);FRAGMENT.TEKSTU(C1;3;2);LEWY(C1;2))

Oczywiście różnych wariantów reprezentacji daty może być wiele ale wtedy wystarczy zmodyfikować powyższą formułę. Do zbudowania formuły zostały użyte funkcje tekstowe PRAWY, LEWY, FRAGMENT.TEKSTU Działanie tych funkcji wykracza poza temat wpisu więc by zrozumieć ich działanie odsyłam do pomocy.

Poniżej przedstawiam kilka modyfikacji przedstawionej formuły:

Dla daty 20150809 - formuła: =DATA(LEWY(C1;4);FRAGMENT.TEKSTU(C1;5;2);PRAWY(C1;2))

Dla daty 2015 08 09 - formuła: =DATA(LEWY(C1;4);FRAGMENT.TEKSTU(C1;6;2);PRAWY(C1;2))

Dla daty 29/08/2015 - formuła: =DATA(PRAWY(C1;4);FRAGMENT.TEKSTU(C1;4;2);LEWY(C1;2))

Częstym zadaniem, które wykonujemy jest obliczenie ilości dni pomiędzy datami i tak naprawdę by wykonać tę czynność jedyne co musimy wykonać jest to wykonanie operacji odejmowania. Już wiesz Czytelniku, że Excel datę uważa jako liczbę tak więc wynikiem działania będzie odjęcie od liczby reprezentującej datę początkową liczby przypisanej do daty końcowej. Tak więc obliczenie dni pomiędzy datami 2015-03-21 a 2015-02-19 da nam liczbę 30 dni.

W przypadku wykonania działania odwrotnego uzyskamy wartość: -30 ponieważ odejmowaliśmy datę wcześniejszą od daty późniejszej. Aby uzyskać wynik dodatni możemy użyć funkcji: MODUŁ.LICZBY w takiej oto przykładowej formule: =MODUŁ.LICZBY(D5-D3)

Tworząc wszelkiego typu raporty np. pracownicze w których prowadzimy ewidencję dni pracy pracowników często potrzebujemy wyliczyć liczbę dni lecz uzyskany wynik nie powinien obejmować weekendów, świąt oraz dni w których pracownik nie był obecny w pracy z jakichkolwiek innych przyczyn. W wyliczeniu dni w których pracownik wykonywał swoje obowiązki pomoże nam funkcja: DNI.ROBOCZE. W przykładzie poniżej w zakresie komórek E2:E4 umieszczono daty świąt czyli dni ustawowo wolnych oraz w zakresie E9:E10 zamieszczono daty nieobecności pracownika w pracy z jakichkolwiek innych przyczyn (np. L4, art. 188 czy urlop okolicznościowy) W następnym kroku został wyliczony okres dni obejmujący obecność pracownika. Ilość dni przepracowanych zostało wyliczone w dwóch przedziałach czasowych tj. od 2015-01-01 do 2015-01-07 oraz dla całego roku kalendarzowego. W wyliczeniu dni obecności zastosowano już wspomnianą funkcję DNI.ROBOCZE Ogólna składnia funkcji wygląda następująco =DNI.ROBOCZE(data_początkowa;data_końcowa;święta). W przykładzie poniżej dzień początkowy został wpisany do komórki F15 a dzień końcowy do komórki G15 natomiast w kolumnie E zostały wpisane święta oraz dni w których pracownika w pracy nie było. Po wywołaniu formuły: =DNI.ROBOCZE(F15;G15;E2:E11) liczba dni roboczych została ustalona na 3. Zdefiniowany parametr święta daje nam możliwość określenia dat dni dodatkowych, które powinny być w ustaleniu liczby dni roboczych pominięte, parametr święta jest opcjonalny. W całym roku kalendarzowym liczba dni roboczych została ustalona na 259.

Czasem w niektórych firmach dzień weekendu jest ustalony na inne dni niż standardowo i funkcja DNI.ROBOCZE tu nam nie pomoże ale na szczęście istnieje jeszcze jedna funkcja, którą możemy użyć do obliczenia dni roboczych pracownika. Mowa tu o funkcji: DNI.ROBOCZE.NIESTAND Funkcja ta pozwala nam na określenie, które dni tygodnia są dniami weekendowymi. Ogólna składnia funkcji to: =DNI.ROBOCZE.NIESTAND(data_początkowa;data_końcowa;weekend,święta). W składni funkcji został dodany jeden dodatkowy, opcjonalny argument weekend. Argument ten określa dni tygodnia będące dniami weekendowymi nieuwzględnianymi w liczbie pełnych dni roboczych między datą początkową i datą końcową. Argument ten określa się jako liczbę. Poszczególne liczby oznaczają:

- - 1 lub pominięty - Sobota, niedziela
  - 2 - Niedziela, poniedziałek
  - 3 - Poniedziałek, wtorek
  - 4 - Wtorek, środa
  - 5 - Środa, czwartek
  - 6 - Czwartek, piątek
  - 7 - Piątek, sobota
  - 11 - tylko niedziela
  - 12 - Tylko poniedziałek
  - 13 - tylko wtorek
  - 14 - tylko środa
  - 15 - tylko czwartek
  - 16 - tylko piątek
  - 17 - tylko sobota

Poniżej przykład w którym została użyta funkcja DNI.ROBOCZE.NIESTAND lecz weekend został ustalony na dni czwartek i piątek.

Aby określić większą liczbę dni weekendu i dni, które nie leżą bezpośrednio obok siebie można skorzystać z opcjonalnego sposobu zdefiniowania dni weekendowych. Dni te określa się za pomocą ciągu o długości siedmiu znaków, z których każdy reprezentuje jeden dzień tygodnia, zaczynając od poniedziałku. Cyfra 1 oznacza dzień wolny, natomiast cyfra 0 oznacza dzień roboczy. W tym ciągu dozwolone są tylko znaki 1 i 0. Użycie ciągu 1111111 zawsze spowoduje zwrócenie ciągu 0. Tak więc jeśli dniami weekendowymi byłby dni środa i niedziela ciąg przyjmie postać 0010001. W definicji funkcji ciąg ten należy umieścić w cudzysłowie. Przykładowa funkcja mogłaby by przyjąć postać: =DNI.ROBOCZE.NIESTAND(M15;N15;"0010001";L2:L11)

W pracy z funkcjami daty bardzo często przydaje się obliczenie wieku danej osoby aby wykonać tę czynność można skorzystać z funkcji: DATA.RÓŻNICA W przykładzie poniżej obliczono wiek osoby urodzonej w dniu 17.02.1980 (wiek osoby obliczono odnośnie daty 29.20.2015)

Funkcja DATA.RÓŻNICA jest dość zagadkową funkcją bo nic na jej temat nie znajdziemy w pomocy programu Excel, nie widnieje na liście funkcji ani nie pojawia się na liście podczas procesu wpisywania funkcji do komórki arkusza. By z niej skorzystać musimy ją w całości wprowadzić ręcznie. Ogólna składnia funkcji jest następująca: = DATA.RÓŻNICA(data_początkowa;data_końcowa;"jednostka") Funkcja DATA.RÓŻNICA oprócz obliczenia lat pomiędzy dwoma datami potrafi również obliczyć liczbę dni czy miesięcy. Wszystko zależy od tego jaką jednostkę daty wybierzemy. Jednostkę tę definiujemy za pomocą zmiennych:

- - y - liczba pełnych lat,
  - m - liczba pełnych miesięcy,
  - d - liczba dni,
  - md - różnica między wartościami dni - miesiące i lata nie są uwzględniane,
  - ym - różnica między miesiącami - lata i dni nie są uwzględniane,
  - yd - różnica między dniami - lata nie są uwzględniane.

Aby wyliczyć dokładny wiek osoby z dodatkowym uwzględnieniem ilości miesięcy i dni można posłużyć się funkcją: =DATA.RÓŻNICA(K4;K5;"y")&" lat "&DATA.RÓŻNICA(K4;K5;"YM")&" miesięcy "&DATA.RÓŻNICA(K4;K5;"MD")&" dni"

Jak widać powyżej wiek osoby został ustalony na 35 lat 8 miesięcy i 12 dni.

Kolejne przykłady funkcji pozwalają nam na wyliczenie dnia roku, ilości dni które do końca roku pozostały oraz do określenia daty podanego dnia roku. Dwa pierwsze przykłady zostały wykonane dla daty 2015-10-29 trzeci zaś dla 67 dnia roku 2015.

- - przykład pierwszy: podana data, którym jest dniem roku? - funkcja: =F5-DATA(ROK(F5);1;0) przy założeniu, że data znajduje się w komórce F5, dla daty dzisiejszej: =DZIŚ()-DATA(ROK(DZIŚ());1;0),
  - przykład drugi: ile pozostało dni do końca roku? - funkcja: =DATA(ROK(F5);12;31)-F5 przy założeniu, że data znajduje się w komórce F5, dla daty dzisiejszej: =DATA(ROK(DZIŚ());12;31)-DZIŚ()
  - przykład trzeci: chcemy znać datę określonego dnia roku: funkcja: =DATA(D9;1;F9) przy założeniu, że w komórce D9 znajduje się rok a w komórce F9 szukany dzień roku.

Aby wyznaczyć dzień tygodnia należy posłużyć się funkcją: DZIEŃ.TYG Po określeniu daty, funkcja zwraca nam jako wynik liczbę od 1 do 7. Numer ten oznacza kolejny dzień tygodnia. Domyślnie pierwszym dniem tygodnia jest niedziela. Poniżej dla daty 2015-10-29 (czwartek) została wywołana funkcja DZIEŃ.TYG w pierwszym przykładzie funkcja zwraca wartość 5 gdyż domyślnie czwartek jest piątym dniem tygodnia (funkcja: =DZIEŃ.TYG(R5)) natomiast w drugim przykładzie uzyskano wynik 4 ponieważ funkcja DZIEŃ.TYG pozwala nam na zmianę domyślnego numerowania początku tygodnia. W drugim przykładzie tydzień rozpoczyna się od poniedziałku (funkcja: =DZIEŃ.TYG(R10;2)) Definicja początku tygodnia odbywa się poprzez podanie odpowiedniej wartości parametru zwracany_typ.

Aby zwrócić dzień tygodnia jako tekst możemy posłużyć się następującą funkcją: =TEKST(M19;"dddd") Funkcja zadziała jeśli w komórce M19 będzie znajdować poprawnie wprowadzona data.

Rozwiązaniem, które też jest możliwe jest połączenie funkcji DZIEŃ.TYG oraz WYBIERZ Wypisanie dnia tygodnia zrealizuje oto taka funkcja: =WYBIERZ(DZIEŃ.TYG(M19;2);"Poniedziałek";"Wtorek";"Środa";"Czwartek";"Piątek";"Sobota";"Niedziela")

Aby wyznaczyć numer tygodnia w roku należy posłużyć się funkcją: NUM.TYG Dla daty 2015-10-29 uzyskamy wynik 44 Przykładowa funkcja może przyjąć postać: =NUM.TYG(K5) - przy założeniu, że w komórce K5 została wpisana data.

Aby wyznaczyć pierwszy dzień tygodnia po określonej dacie można posłużyć się formułą: =D17+F17-DZIEŃ.TYG(D17;2)+(F17<DZIEŃ.TYG(D17;2))*7

Formuła ta zadziała jeśli:

- - w komórce D17 umieścimy datę.
  - w komórce F17 określimy dzień tygodnia - kolejno dla poniedziałku 1 natomiast dla niedzieli 7

Dla daty 2015-10-29 data pierwszego poniedziałku to 2015-11-02.

W niektórych sytuacjach do określonej daty musimy dodać odpowiednią liczbę miesięcy (bądź od daty odjąć daną liczbę miesięcy). Sytuacja ta jest trochę problematyczna ponieważ miesiące mają różną liczbę dni. Ale na szczęście z pomocą przychodzi nam funkcja: NR.SER.DATY (funkcja ta została wprowadzona w Excel 2010 zastępując funkcję EDATE, funkcja EDATE dostępna jest po instalacji dodatku Analysis ToolPak). Wystarczy, że do komórki wpiszemy formułę: =NR.SER.DATY(M32;1) której zadaniem jest do daty umieszczonej w komórce M32 dodanie jednego miesiąca. Wynikiem dodania do dnia 2015-10-31 jednego miesiąca jest dzień 2015-11-30 ponieważ listopad 2015 roku liczy 30 dni.

Aby odjąć miesiące w formule należy zdefiniować ujemną liczbę reprezentującą ilość odejmowanych miesięcy np. =NR.SER.DATY(M32;-2)

Aby wyznaczyć ostatni dzień miesiąca należy skorzystać z funkcji NR.SER.OST.DN.MIES (funkcja ta została wprowadzona w Excel 2010 zastępując funkcję EOMONTH, funkcja ta dostępna jest po instalacji dodatku Analysis ToolPak). Zadaniem funkcji jest wyliczenie ostatniego dnia miesiąca, w którym wystąpi data podana jako pierwszy argument zwiększona o liczbę miesięcy podanych jako argument drugi.

Poniżej przykład użycia funkcji odnośnie daty 2015-10-29, zadaniem zastosowanej formuły jest wyświetlenie ostatniego dnia przyszłego miesiąca.

Wszystko fajnie ale co w przypadku w którym będziemy chcieli operować na datach przed rokiem 1900. Niestety program Excel w przypadku tego typu dat nie pozwoli nam na operacje, które przedstawiłem powyżej. Ale na szczęście można ze strony: http://j-walk.com/ss/excel/files/xdate.htm pobrać dodatek XDATE, który z datami sprzed 1900 rokiem sobie poradzi. Dodatek tak naprawdę jest zestawem makr, których zadaniem jest umożliwienie użytkownikowi na operowaniu na datach sprzed roku 1900. Autorem dodatku jest John Walkenbach.

Po pobraniu odpowiednich plików należy je zainstalować tak by Excel mógł obsłużyć funkcje oferowane przez ten dodatek. W tym celu przechodzimy do Opcji programu (Plik – Opcje) i w nowo otwartym oknie wybieramy Dodatki a następnie w dolnej części okna wybieramy Przejdź

W nowym oknie wybieramy Przeglądaj i lokalizujemy ściągnięty plik: xdate.xla (plik znajduje się w archiwum, należy go uprzednio rozpakować).

Po kliknięciu na OK w oknie Dodatki powinna pojawić się nowa pozycja Extended Date Functions Od tej pory możemy korzystać z funkcji zawartych w tym dodatku.

Poniżej został pokazany przykład arkusza w którym zostały użyte daty sprzed roku 1900 z użyciem funkcji zawartych w dodatku XDATE

Dodatek oferuje nam następujące funkcje:

- - XDATE(rok;miesiąc,dzień,format): funkcja zwraca datę z podanego roku, miesiąca i dnia. Opcją dodatkową jest określenie formatu przedstawianej daty.
  - XDATEADD(data;liczba_dni,format): dodanie do daty określoną liczbę dni. Opcją dodatkową jest określenie formatu przedstawianej daty.
  - XDATEDIF(data_1,data_2): różnica dni pomiędzy datami.
  - XDATEYEARDIF(data_1,data_2): różnica pełnych lat między datami.
  - XDATEYEAR(data): zwraca rok zawarty w dacie.
  - XDATEMONTH(data): zwraca miesiąc zawarty w dacie.
  - XDATEDAY(data): zwraca dzień zawarty w dacie.
  - XDATEDOW(data): dzień tygodnia, wynikiem wywołania funkcji jest liczba z przedziału od 1 do 7 – numeracja zaczyna się od niedzieli.

Funkcje związane z datą mamy omówione przejdźmy zatem do funkcji związanych z czasem.

Aby wstawić do arkusza bieżącą datę i czas należy skorzystać z funkcji: TERAZ() aby uzyskać sam czas stosujemy formułę: =TERAZ()-DZIŚ() (w przypadku uzyskania liczby komórkę formatujemy jako czas).

Czas do komórek wprowadzamy z użyciem dwukropka (:) np. 7:45 ale można również użyć funkcji: CZAS W przykładzie poniżej w komórce B4 została wpisana godzina w komórce C4 minuta natomiast w komórce D4 sekunda.

Aby połączyć czas z datą można użyć funkcji CZAS w połączeniu z funkcją DATA np. =DATA(2015;10;29)+CZAS(7;30;0) Formuła ta zwraca liczbę 42306,3125, która oznacza godzinę 7:30 dnia 29 października 2015.

Aby poznać numer seryjny, który odpowiada konkretnej godzinie możemy skorzystać z funkcji CZAS.WARTOŚĆ Funkcja ta zamienia tekst (tekst ten musi być zgodny z formatem czasu) na numer seryjny czasu. Np. formuła: =CZAS.WARTOŚĆ("7:45") zwraca liczbę 0,3229166667, numer ten odpowiada godzinie 7:45.

Czas jak już wiesz jest reprezentowany za pomocą kolejnych numerów seryjnych tak więc podobnie jak data można wykonać odejmowanie czasu wcześniejszego od późniejszego.

Powyżej w przykładzie za pomocą formuły: =B4-B5 wykonano odejmowanie od godziny 18:45 godzinę 12:30 uzyskany wynik to 6 godzin i 15 minut.

Wykonanie działania odwrotnego czyli: =B5-B4 da wynik nieprawidłowy i Excel w takiej sytuacji wyświetli serię znaków: ######

Sytuacja taka ma miejsce ponieważ uzyskany wynik byłby liczbą ujemną (czas jest podany bez daty dlatego przyjmowane jest 0). Jeżeli zależy nam na wykonaniu takiego działania można użyć funkcji: MODUŁ.LICZBY

Ale zastosowanie funkcji MODUŁ.LICZBY nie rozwiązuje wszystkich problemów. Prześledźmy o to taki przykład. Na zrzucie poniżej przedstawiono czas pracy pewnego pracownika jak widać czasy te są obliczone prawidłowo poza jednym przypadkiem obejmującym godziny od 23:00 do 6:00 W przypadku standardowego odjęcia otrzymujemy brak wyniku a zastosowanie funkcji MODUŁ.LICZBY daje wynik nieprawidłowy – zamiast 7 godzin otrzymujemy 17.

Ten problem wyliczenia czasu bardzo często pojawi się w sytuacji w której obliczany czas obejmuje północ. Aby poradzić sobie z tym problemem można zastosować oto taką funkcję: =JEŻELI(C7>D7;D7+1;D7)-C7 Cały trik z zastosowaną funkcją polega na dodaniu do godziny wartości 1 tak by po odjęciu wynik nie przyjmował wartości ujemnych. Dodanie jedynki następuje tylko w sytuacji w której czas początku pracy jest większy od czasu jej zakończenia.

Kontynuujmy nasz przykład i spróbujmy podliczyć czas spędzony w pracy przez pracownika i w tym celu wykonajmy operację sumowania.

Jak widać po powyższym rysunku uzyskany czas pracy jest nieprawidłowy. Problem tkwi w sposobie formatowania komórki innymi słowy wynik jest prawidłowy lecz formatowanie komórki ucina nam informację o dniach. Rozwiązaniem problemu jest zastosowanie formatowania niestandardowego, wystarczy, że jako typ formatowania wprowadzimy: [g]:mm:ss. Umieszczenie symbolu godziny w nawiasie kwadratowym informuje Excela by czas traktować jako ciągłość.

W niektórych sytuacjach zachodzi potrzeba zaokrąglenia czasu tak więc zaokrąglenie możemy wykonać w zależności do określonej jednostki.

- - zaokrąglenie do minuty: =ZAOKR(C4*1440;0)/1440
  - zaokrąglenie do kwadransa: =ZAOKR(C4*24/0,25;0)*(0,25/24)
  - zaokrąglenie do pół godziny: =ZAOKR(C4*24/0,5;0)*(0,5/24)
  - zaokrąglenie do godziny: =ZAOKR(C4*24;0)/24

I na koniec parę słów o formatowaniu daty i czasu. Prawidłowo wpisaną datę możemy sobie wyświetlić w wielu różnych formatach, to od nas zależy jak data czy czas mają być reprezentowane. Aby zdefiniować wyświetlany format daty bądź czasu z rozwijanego menu na zakładce Narzędzia główne wybieramy odpowiedni typ.

Bądź decydujemy się sami na zdefiniowanie sposobu wyświetlania. W tym celu z menu wybieramy z menu Więcej formatów liczb i w sekcji Typ definiujemy własny sposób wyświetlania daty bądź czasu.

W definiowaniu własnego formatowania pomocne nam będą następujące zmienne:

Data:

miesiąc jako 1-12	m
miesiąc jako 01-12	mm
miesiąc jako Sty-Gru	mmm
miesiąc jako Styczeń-Grudzień	mmmm
miesiąc jako pierwsza litera miesiąca	mmmmm
dzień jako 1-31	d
dzień jako 01-31	dd
dzień jako Nie-Sob	ddd
dni jako Niedziela-Sobota	dddd
lata jako 00-99	rr
lata jako 1900-9999	rrrr

Czas:

godzina jako 0-23	g
godzina jako 00-23	gg
minuta jako 0-59	m
minuta jako 00-59	mm
sekunda jako 0-59	s
sekunda jako 00-59	ss
godzina jako AM (duże)	g AM/PM
godzina jako pm (małe)	g:mm am/pm
czas jako 5:15:09 p	g:mm:ss a/p
kontynuacja czasu w godzinach	[g]:mm
kontynuacja czasu w minutach	[mm]:ss
kontynuacja czasu w sekundach	[ss]
ułamki sekund	g:mm:ss.00

Poniżej na zrzucie kilka przykładów różnych definicji formatu daty i czasu.

Ostatnia porada, którą chciałbym zaprezentować jest funkcja znaleziona na stronie wyżej już wspominanego Johna Walkenbacha. Funkcja ta wstawia nam do arkusza kalendarz. Myślę, że dla osób często wykonujących operację na datach taki kalendarz umiejscowiony gdzieś w arkuszu naprawdę ułatwi sprawę (źródło: http://spreadsheetpage.com/index.php/file/yearly_calendar_with_holidays/).

Nasz kalendarz zaczniemy od wpisania w komórce H3 roku oraz komórce J3 numeru miesiąca. W scalonej komórce D5 została wprowadzona funkcja: =DATA(H3;J3;1), której zadaniem jest pobranie z komórki H3 oraz komórki J3 informacji o roku i miesiącu. Komórka D5 została sformatowana jako: mmmm tak by została wyświetlona nazwa miesiąca. Poniżej w komórkach od D6 do J6 zostały wpisane nazwy dni tygodnia. Następnie po zaznaczeniu kolejnych 5 wierszy (od D7 do J11) została wprowadzona funkcja tablicowa: {=JEŻELI(MIESIĄC(DATA(ROK(D5);MIESIĄC(D5);1))<>MIESIĄC(DATA(ROK(D5);MIESIĄC(D5);1)-(DZIEŃ.TYG(DATA(ROK(D5);MIESIĄC(D5);1))-1)+{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1);"";DATA(ROK(D5);MIESIĄC(D5);1)-(DZIEŃ.TYG(DATA(ROK(D5);MIESIĄC(D5);1))-1)+{0;1;2;3;4;5}*7+{1\2\3\4\5\6\7}-1)} Funkcję tą zatwierdzamy klawiszami CTRL+SHIFT+ENTER Po zatwierdzeniu komórki powinny wypełnić się serią numerów. Numery te odpowiadają, kolejnym dniom miesiąca. Aby kalendarz był kompletny komórki te formatujemy jako: d Kalendarz jest gotowy.

I na tym chciałby zakończyć. Mam nadzieję, że po lekturze tego wpisu już nikt nie będzie miał problemów z prawidłową pracą z danymi jakimi są data i czas.

BIBLIOGRAFIA:

https://support.office.com/pl-pl/article/Wstawianie-bie%C5%BC%C4%85cej-daty-i-godziny-do-kom%C3%B3rki-b5663451-10b0-40ab-9e71-6b0ce5768138?ui=pl-PL&rs=pl-PL&ad=PL

http://excelowiec.blogspot.com/2012/11/nowe-nazwy-funkcji-w-excel-2010.html

http://j-walk.com/ss/excel/files/xdate.htm

Excel w zadaniach. Funkcja AGREGUJ oraz SUMY.CZĘŚCIOWE

2015-06-03T09:14:29+00:00

Jedną z nowszych funkcji, która została wprowadzona w Excel 2010 jest funkcja AGREGUJ. Standardowe funkcje takie jak SUMA, ŚREDNIA czym MAX przerywają swoje działanie gdy w zdefiniowanym zakresie wystąpi błąd. Do ominięcia tego ograniczenia możemy właśnie wykorzystać funkcję AGREGUJ. Jej uniwersalność wynika z faktu, że (opcjonalnie) może ona ignorować ukryte komórki i wartości błędów. Funkcja AGREGUJ jest w zasadzie multifunkcją czyli taką funkcją, która potrafi operować na kilkunastu innych, różnych funkcjach czy to liczących czy sumujących. W składni formuły za pomocą wartości numerycznej (każda z funkcji ma przypisany swój numer) określamy z jakiej funkcji będziemy korzystać a dodatkowo również za pomocą wartości numerycznej określamy sposób zachowania się funkcji np. w sytuacji błędu czy wystąpienia wierszy ukrytych.

Arkusz na którym będę przeprowadzał ćwiczenia można pobrać tu: arkusz

Ogólna składnia funkcji jest następująca: AGREGUJ(funkcja_nr;opcje;odwołanie1; odwołanie2;...)

gdzie:

funkcja_nr – jest liczbą od 1 do 19, określająca funkcję, która ma zostać użyta,

opcje – czyli wartość liczbowa określająca, sposób zachowania się funkcji,

odwołanie1 – pierwszy argument liczbowy dla funkcji przyjmujących kilka argumentów liczbowych,

odwołanie2 – od 2 do 253 opcjonalnych argumentów liczbowych, dla których ma być agregowana wartość.

Oznaczenie poszczególnych wartości parametru funkcja_nr przedstawiono poniżej:

1 - ŚREDNIA

2 - ILE.LICZB

3 - ILE.NIEPUSTYCH

4 - MAX

5 - MIN

6 - ILOCZYN

7 - ODCH.STANDARD.PRÓBKI

8 - ODCH.STAND.POPUL

9 - SUMA

10 - WARIANCJA.PRÓBKI

11 - WARIANCJA.POP

12 - MEDIANA

13 - WYST.NAJCZĘŚCIEJ.WART

14 - MAX.K

15 - MIN.K

16 - PERCENTYL.PRZEDZ.ZAMK

17 - KWARTYL.PRZEDZ.ZAMK

18 - PERCENTYL.PRZEDZ.OTW

19 - KWARTYL.PRZEDZ.OTW

Natomiast oznaczenie parametru opcje przedstawia się następująco:

0 lub pominięte - Ignoruj zagnieżdżone funkcje SUMY.CZĘŚCIOWE i AGREGUJ

1 - Ignoruj ukryte wiersze oraz zagnieżdżone funkcje SUMY.CZĘŚCIOWE i AGREGUJ

2 - Ignoruj wartości błędów oraz zagnieżdżone funkcje SUMY.CZĘŚCIOWE i AGREGUJ

3 - Ignoruj ukryte wiersze, wartości błędów oraz zagnieżdżone funkcje SUMY.CZĘŚCIOWE i AGREGUJ

4 - Nie ignoruj niczego

5 - Ignoruj ukryte wiersze

6 - Ignoruj wartości błędów

7 - Ignoruj ukryte wiersze i wartości błędów

Wszystkie wartości parametrów będą podawane jako podpowiedź podczas definiowania funkcji.

OK to na tyle wstępu teoretycznego czas przejść do zadania i pokazać funkcję w konkretnym zastosowaniu.

Załóżmy, że prowadzimy księgarnie i w arkuszu mamy prowadzoną ewidencję zamówień np. taką jak poniżej.

Wykonajmy obliczenia ilości wszystkich książek oraz ich wartości. Zadanie wykonamy dwojako: pierwszy sposób - użyjemy tradycyjnej funkcji SUMA oraz drugi sposób - użyjemy funkcji AGREGUJ

W przypadku pierwszego sposobu zostały użyte formuły:

ilość książek - =SUMA(D2:D22)

wartość - =SUMA(F2:F22)

Zaś w przypadku drugiego sposobu:

ilość książek - =AGREGUJ(9;7;D2:D22)

wartość - =AGREGUJ(9;7;F2:F22)

Funkcji SUMA nie trzeba tłumaczyć zatrzymajmy się chwilę przy funkcji AGREGUJ. W formule jako pierwszy parametr (funkcja_nr) została użyta wartość 9 ponieważ odpowiada ona działaniu funkcji SUMA, kolejny parametr 7 (opcje) nakazuje ignorować ukryte wiersze i wartości błędów, zaś zakres (odwołanie1) jest przekazywany do użytej funkcji.

Oba użyte sposoby reprezentują te same wyniki, rodzi się pytanie - Gdzie jest haczyk i w czym oba użyte rozwiązania się różnią? Nie od dziś wiadomo, że diabeł tkwi w szczegółach więc by pokazać różnicę wykonajmy filtrowanie tak by pokazać zamówienia tylko firmy AAA.

Filtrowanie w programie Excel umożliwia nam wyświetlenie wyników spełniających dane kryterium bądź kryteria. W naszym scenariuszu filtr powinien pokazywać tylko zamówienia należące do firmy AAA. Aby zastosować filtr w pierwszej kolejności zaznaczamy nagłówek tabeli i na karcie Narzędzia główne klikamy ikonę „lejka” a następnie Filtruj

Po wybraniu opcji w nagłówku tabeli pojawią się strzałki umożliwiające nam wybranie i zastosowanie danego filtru. Wartości filtru odpowiadają wartością zawartym w danej kolumnie. Po rozwinięciu filtru wybieramy pole odpowiadające firmie AAA

Innym alternatywnym sposobem włączenia filtrowania jest zaznaczenie komórki w której znajduje się informacja co do której będzie stosowane filtrowanie. W naszym przypadku w kolumnie Firma wybieramy tą w której znajduje się nazwa firmy AAA i po kliknięciu PPM z menu kontekstowego odszukujemy Filtruj i następnie Filtruj według wartości wybranej komórki.

Po zastosowaniu filtru pokazującego zamówienia firmy AAA widać już pierwszą różnicę - obliczane wartości nie są takie same. A więc rodzi się kolejne pytanie - Które wartości są prawidłowe? Szybki rzut na obliczenia i dochodzimy do wniosku, że te użyte z wykorzystanie funkcji AGREGUJ. Przykład ten pokazuje jeden z atutów stosowania funkcji AGREGUJ niż SUMA - w przypadku użycia filtrowania nie musimy się martwić o obliczenia gdyż funkcja ta zadba o to aby samemu wykonać odpowiednia przeliczenia w zależności od wyświetlanej zawartości arkusza (użyta opcja numer 7 kazała zignorować ukryte wiersze).

By pokazać kolejny atut stosowania funkcji AGREGUJ zmodyfikujmy lekko nasz przykład i w kolumnie Ilość wpiszmy wartości określające nam stan poszczególnych książek np. przy braku książki w magazynie - słowo: zamówić a przy zakończeniu nakładu - słowo: wyczerpano.

Zastosowanie takiego rozwiązania oczywiście w kolumnie Wartość całość spowoduje wygenerowanie błędu gdyż nie można pomnożyć wartości tekstowej poprzez liczbę – błąd: #ARG To z kolei przełoży się na błąd w sumowanej wartości książek (komórka F25). Ale jak można zauważyć nie dotyczy to funkcji AGREGUJ. Formuła przy której wykorzystano tą funkcję zwraca poprawną wartość sumy wartości wszystkich książek (użyta opcja numer 7 kazała zignorować błędy).

W przypadku obliczenia ilości książek tradycyjna funkcja SUMA sprawdza się gdyż ignoruje ona komórki zawierające tekst.

Myślę, że przedstawione atuty tej funkcji przemawiają same za siebie a użycie tej funkcji w budowaniu formuł może nam zaoszczędzić niepotrzebnych komplikacji jakie mogą wystąpić gdy korzystamy z tradycyjnych funkcji.

W programie Excel istnieje jeszcze jedna funkcja zbliżona działaniem do funkcji AGREGUJ a mianowicie SUMY.CZĘŚCIOWE. Funkcja ta również potrafi ignorować ukryte dane np. podczas włączenia automatycznego filtrowania ale w przypadku ukrycia ręcznego musimy oddzielnie uwzględnić ten przypadek.

Składnia funkcji SUMY.CZĘŚCIOWE jest bardzo podobna do funkcji AGREGUJ lecz brak jest w niej definicji sposobu zachowania się formuły w przypadku wystąpienia błędów a także ofert współpracujących funkcji jest znacznie mniejsza.

Składnia funkcji jest następująca: SUMY.CZĘŚCIOWE(funkcja_nr;odwołanie1; odwołanie2;...)

gdzie:

funkcja_nr – jest liczbą od 1 do 11 lub od 101 do 111, określająca funkcję, która ma zostać użyta z uwzględnieniem wartości ukrytych bądź ich ignorowaniem,

odwołanie1 – pierwszy argument liczbowy dla funkcji przyjmujących kilka argumentów liczbowych,

odwołanie2 – od 2 do 254 opcjonalnych argumentów liczbowych, dla których ma zostać obliczona suma częściowa.

Funkcja_nr (uwzględnianie wartości ukrytych)	Funkcja_nr (ignorowanie wartości ukrytych)	Funkcja
1	101	ŚREDNIA
2	102	ILE.LICZB
3	103	ILE.NIEPUSTYCH
4	104	MAX
5	105	MIN
6	106	ILOCZYN
7	107	ODCH.STANDARDOWE
8	108	ODCH.STANDARD.POPUL
9	109	SUMA
10	110	WARIANCJA
11	111	WARIANCJA.POPUL

Tak więc zastosujmy tą funkcję w naszym przykładzie i porównamy uzyskane wyniki z tradycyjną funkcją SUMA oraz funkcją AGREGUJ

Jak widać powyżej w przypadku zastosowania filtrowania automatycznego (zamówienia firmy AAA) wpływ zdefiniowanego ignorowania bądź uwzględnienia wartości ukrytych w funkcji SUMY.CZĘŚCIOWE jest bez znaczenia.

Sprawdźmy jak funkcję zachowają się w przypadku ukrycia ręcznego. Ukrycie wiersza dokonujemy poprzez kliknięcie na jego numer i wybraniu z menu kontekstowego opcji Ukryj.

Aby sprawdzić działanie funkcji ukryjmy wiersze 8 i 9.

Tu już mamy odmienną sytuację gdyż w przypadku zastosowania w formule opcji 9 funkcja SUMY.CZĘŚCIOWE działa tak jak tradycyjna funkcja SUMA - wartości ukryte są uwzględniane. Natomiast zastosowanie opcji 109 powoduje, ignorowanie ukrytych wartości, obliczona wartość jest tożsama z tą obliczoną przy pomocy funkcji AGREGUJ.

Funkcja SUMY.CZĘŚCIOWE tak samo jak funkcja SUMA nie radzi sobie w przypadku wystąpienia błędów.

Podsumowując część obliczeń uda ci się wykonać przy użyciu funkcji SUMY.CZĘŚCIOWE lecz funkcja AGREGUJ jest bardziej elastyczna.

Z funkcją SUMY.CZĘŚCIOWE jest związane jeszcze jedno przydatne narzędzia zawarte na karcie Dane a nazwane (to ci niespodzianka) Suma częściowa. Narzędzie świetnie sprawdza się do wykonania wszelkiego rodzaju podsumowań ale aby narzędzie zadziałało należy w pierwszym kroku wykonać sortowanie według kryterium, którego będziemy te podsumowanie prowadzić.

Przypuśćmy, że chcemy dowiedzieć się o wartości sprzedanych książek ale w rozbiciu o firmy. Tak więc w pierwszej kolejności sortujemy nasz arkusz według firm z którymi prowadzimy współpracę. Aby dokonać sortowanie zaznaczamy wszystkie dane (bardzo ważny krok) i klikamy ikonę „lejka” lecz tym razem z dostępnych opcji wybieramy Sortuj … Do wyboru mamy trzy opcje: myślę, że dwie pierwsze nie wymagają tłumaczenia (Sortuj od A do Z oraz Sortuj od Z do A) natomiast zatrzymajmy się na chwilę przy trzeciej opcji a mianowicie Sortowanie niestandardowe. Wybranie tej opcji pozwoli nam na wykonanie sortowania według kilku kryteriów. Przypuśćmy, że chcemy nasze dane ułożyć w kolejności firm i dodatkowo największej sprzedanej ilości pozycji. Po zaznaczeniu danych i wybraniu Sortowanie niestandardowe w nowo otwartym oknie definiujemy poziomy sortowania. Aby dodać nowy poziom należy wybrać: Dodaj poziom

Efekt wykonanej operacji sortowania przedstawia się następująco, firmy są ułożone w kolejności od A do Z natomiast w obrębie firmy dane dodatkowo są ułożone według malejącej ilości sprzedanych egzemplarzy.

Oczywiście proces ten możemy powtarzać według wielu kryteriów lecz co najważniejsze pamiętaj abyś zawsze zaznaczał wszystkie dane. Może zastanawiasz się - Czemu jest to tak ważne? Gdy nie zaznaczysz wszystkich danych i wykonasz sortowanie według jednej ustalonej kolumny to wiersze zawarte w tej kolumnie ulegną wymieszaniu tj. posortujesz dane zawarte tylko w tej jednej zaznaczonej kolumnie, inne dane umiejscowione w innych kolumnach pozostaną na swoim miejscu. Na szczęście przed wykonaniem tego błędu chroni nas dodatkowy komunikat, który pojawia się w przypadku wykrycia zaznaczenia, które może spowodować zaburzenie spójności naszych danych.

Myślę że najlepiej to przedstawi przykład. Posortujmy nasze zamówienia wg. numeru zamówienia lecz w pierwszym przypadku zaznaczymy sortowanie tylko pierwszej kolumny a w drugim sortowanie wszystkich danych.

Dane oryginalne:

Sortowanie tylko wg. numeru zamówienia

Sortowanie całości

W pierwszym przypadku sortowaniu uległa tylko pierwsza kolumna, reszta danych pozostała bez zmian, sortowanie to zostało przeprowadzone błędnie gdyż w ten sposób straciliśmy integralność wprowadzonych danych tj. numer zamówienia nie odpowiada rzeczywistym zamówieniom.

W drugim zaś przypadku sortowaniu uległy wszystkie dane tj. numer zamówienia zmienił położenie ale również reszta danych też. Dzięki temu integralność danych nie została zaburzona.

Wracamy do naszego przykładu z narzędziem Suma częściowa. Aby uzyskać informację o wartości książek sprzedanych każdej z firm wykonujemy sortowanie według firm a w następnym kroku przechodzimy na kartę Dane i wybieramy Suma częściowa (po uprzednim zaznaczeniu wszystkich danych wraz z nagłówkami). W nowo otwartym oknie w polu Dla każdej zmiany wybieramy Firma, w polu Użyj funkcji definiujemy funkcję jaka ma być użyta natomiast w sekcji Dodaj sumę częściową do zaznaczamy kolumnę do której wybrana funkcja będzie miała zastosowanie.

Poniżej mamy jeszcze do dyspozycji trzy opcje, które możemy dodatkowo zdefiniować:

Zamień bieżące sumy częściowe - zamienia istniejące sumy częściowe na nowo zdefiniowane,

Podział strony pomiędzy grupami - ustawia podział strony na grupy, przydatne gdy chcemy wydrukować utworzone zestawienie dla każdej sekcji danych z osobna. W naszym przypadku na każdym z wydruków znalazłby się dane dotyczące tylko jednej z firm.

Podsumowanie poniżej danych - włącza podsumowanie poniżej każdej z grup.

Po określeniu opcji narzędzia Suma częściowa klikamy na OK.

Jak można zauważyć powyżej dla każdej z firm została obliczona suma wartości wszystkich zamówień z wyjątkiem wartości zamówienia firmy CCC i całkowitego podsumowania wszystkich firm. Stało się tak ponieważ jak już wiesz czytelniku funkcja SUMY.CZĘŚCIOWE nie radzi sobie z błędami.

Nowo utworzone pole (z lewej strony z cyframi 1,2 oraz 3) służy do „zawinięcia” danych.

Spróbujmy błąd wyeliminować i zaobserwować zmiany.

Jak można zauważyć po zmianie w komórce D18 gdzie znikło słowo „zamówić” a pojawiła się wartość liczbowa obliczenia są wykonane poprawnie za to ciekawe spostrzeżenia możemy dokonać poniżej. Jak widać funkcja SUMA nie radzi sobie z utworzonymi sumami częściowymi ponieważ są one wliczone do ogólnej wartości zamówień co prowadzi do błędnych obliczeń - zamiast prawidłowego wyniku jakim jest wartość 73380 zł funkcja pokazuje nam wynik 11608zł (stało się tak ponieważ wartości obliczone poprzez narzędzie Suma częściowa zostały wstawione wewnątrz zakresu zdefiniowanego w funkcji SUMA).

Funkcja AGREGUJ również nie poradziła sobie z wyliczeniem poprawnej wartości ale jest to błąd spowodowany nieprawidłowym zdefiniowaniem opcji ignorowania - opcja 7 nakazuje zignorować ukryte wiersze i wartości błędów a nie zagnieżdżone funkcje SUMY.CZĘŚCIOWE Aby wynik był prawidłowy wystarczy opcję zmienić np. na 3 (ignorowane będzie wszystko)

Dwie pozostałe ręcznie zdefiniowane funkcje SUMY.CZĘŚCIOWE pokazują poprawne wyniki ponieważ funkcja ta domyślnie ignoruje inne funkcje SUMY.CZĘSCIOWE

Jeżeli w naszym arkuszu będziemy chcieli korzystać z komórek, które zawierają tekst będziemy musieli niestety utworzone definicje formuł zawierających funkcję SUMY.CZĘŚCIOWE ręcznie zmodyfikować.

Jakbyś Czytelniku znał jakiś inny sposób poza ręczną modyfikacją formuł zawartych w komórkach to Ja bardzo chętnie go poznam.

Na koniec chciałbym jeszcze przedstawić dwie funkcje, które na pewno wzbogacą arkusz w kontekście tworzenia zestawień. Dlatego też w naszym arkuszu wprowadzę jeszcze jedną kolumnę a mianowicie pole Data.

Naszym zadaniem będzie utworzenie takiej formuły, która wyliczy nam wartość zamówienia obejmująca dany okres. Przyjmijmy, że interesuje nas wartość zamówień za miesiąc luty 2015. Zadanie to zrealizujemy za pomocą podanie wiersza w której znajduje się data początkowa (w przykładzie wiersz 5 – data: 2015-02-04) interesującego nas okresu oraz podanie drugiego numeru wiersza tym razem z datą końcową (w przykładzie wiersz 11 – data: 2015-03-28)

Aby wykonać zadanie będziemy musieli skorzystać z dwóch nowych funkcji a mianowicie funkcji ADRES oraz funkcji ADR.POŚR

Zadaniem pierwszej funkcji ADRES jest utworzenie tekstu będącego adresem komórki o określonych przez nas współrzędnych. Składnia funkcji prezentuje się następująco: ADRES(nr_wiersza;nr_kolumny ;typ_adresu;A1;tekst_arkusz)

gdzie poszczególne parametry oznaczają:

nr_wiersza - wymagany, wartość liczbowa określająca numer wiersza, który ma zostać użyty w odwołaniu do komórki,

nr_kolumny - wymagany, wartość liczbowa określająca numer kolumny, który ma zostać użyty w odwołaniu do komórki,

typ_adresu – opcjonalny, wartość liczbowa określająca, jakiego typu odwołanie będzie zwracane przez funkcję. Zwracany typ odwołania

1 - lub pominięte Bezwzględne (bezwzględne odwołanie do komórki: W formule jest to dokładny adres komórki, niezależny od położenia komórki, która zawiera formułę. Bezwzględne odwołanie do komórki ma postać $A$1.)

2 Bezwzględne wiersza, względne kolumny np. A$1

3 Względne wiersza, bezwzględne kolumny np. $A1

4 Względne np. A1

A1 - opcjonalny, wartość logiczna określająca styl odwołań A1 lub W1K1.

tekst_arkusz – opcjonalny, wartość tekstowa określająca nazwę arkusza używanego jako odwołanie zewnętrzne. Na przykład formuła =ADRES(1;1;;;"Arkusz2") zwraca wartość Arkusz2!$A$1. Jeśli argument tekst_arkusz jest pominięty, nazwa arkusza nie jest używana, a adres zwracany przez funkcję odwołuje się do komórki w bieżącym arkuszu.

To przykład ilustrujący działanie funkcji ADRES

Druga z funkcji ADR.POŚR jest funkcją, która jest używana do zamiany argumentu tekstowego opisującego odwołanie do obszaru na normalne odwołanie do zakresu komórek.

Składnia funkcji ADR.POŚR jest następująca: ADR.POŚR(adres_tekst; A1)

gdzie parametry oznaczają:

adres_tekst - wymagany, odwołanie do komórki zawierającej odwołanie w trybie adresowania A1 lub W1K1 albo nazwę zdefiniowaną jako odwołanie bądź odwołanie do komórki jako ciągu tekstowego.

A1 – opcjonalny, wartość logiczna określająca, jaki typ odwołania znajduje się w komórce adres_tekst.

Tak więc mały przykład.

W komórkach od D4 do D11 mamy wpisane wartości, natomiast w komórce G4 oraz G5 będziemy wpisywali zakres komórek, który będzie sumowany. Wpisane adresy komórek są wpisanym tekstem. Za pomocą funkcji ADR.POŚR tekst ten zamienimy na normalne odwołanie i połączymy je z funkcją SUMA.

Formuła, która pozwoli nam na zsumowanie zdefiniowanego zakresu przyjmie postać: =SUMA(ADR.POŚR(G4):(ADR.POŚR(G5))) Działanie formuły sprowadza się do pobrania zawartości komórek G4 oraz G5 i przy pomocy funkcji ADR.POŚR zamienienie ich na odwołanie. Odwołanie przyjmuje postać zakresu od D5 do D9 Zakres ten jest argumentem funkcji SUMA czego efektem jest dodanie do siebie wszystkich wartości zawartych w komórkach od D5 do D9.

Uzbrojeni w wiedzę o sposobie działania funkcji ADRES oraz ADR.POŚR wracamy do naszego przykładu.

Formuła, która pozwoli nam na wyliczenie wartości zamówień w zależności od podania wiersza daty początkowej i wiersza daty końcowej przyjmie postać: =AGREGUJ(9;3;ADR.POŚR(ADRES(J4;7)):ADR.POŚR(ADRES(J5;7)))

funkcja ADRES pobiera nam z komórek J4 oraz J5 numery wierszy, natomiast numer kolumny jest zdefiniowany na 7 ponieważ potrzebujemy dodać do siebie wartości zamówień, pobrane adresy są tekstem,
funkcja ADR.POŚR zamienia adresy tekstowe na normalne odwołania,
utworzony zakres stanowi argument funkcji AGREGUJ, nakazujący zsumowanie wartości znajdujących się w zakresie (opcja 9) z ignorowaniem ukrytych wierszy, wartości błędów oraz zagnieżdżonych funkcji SUMY.CZĘŚCIOWE i AGREGUJ

Nasz cel został osiągnięty, można by zadać pytanie – Czy nie da się tego zrobić prościej? Ten sam efekt otrzymamy przy użyciu funkcji: =SUMA(ADR.POŚR("G"&J4&":G"&J5))

Jak można zauważyć w formule tej nie użyliśmy funkcji ADRES lecz by otrzymać zakres został użyty operator konkatenacji łączący adres kolumny z adresem wiersza wprowadzonym w komórkach J4 oraz J5.

I tu pozwól czytelniku, że przykład tej formuły będzie wstępem do kolejnego wpisu w którym omówię sposoby łączenia ze sobą danych z różnych komórek.

Bibliografia:

https://support.office.com/pl-pl/article/AGREGUJ-funkcja-c8caed56-07df-4aeb-9741-23693ffbe525

https://support.office.com/pl-pl/article/ADRES-funkcja-47657e92-81ae-47f8-87cd-62d4f30c774d

Excel w zadaniach. Funkcja ORAZ i LUB

2015-02-10T08:31:30+00:00

We wcześniejszym wpisie opisałem funkcję JEŻELI, jak mogłeś się przekonać czytelniku funkcja ta jest bardzo przydatna ale ma ona ograniczenia, które powodują, że czasami można pogubić się w drzewie zależności i warunków. Dlatego dodatkowo warto poznać dwie dodatkowe funkcje, które rozszerzą funkcjonalność zastosowania funkcji JEŻELI. Choć oczywiście funkcje o których będzie mowa w tym wpisie mogą być stosowane oddzielnie. Tak więc na „tapetę” bierzemy funkcje logiczne: LUB i ORAZ.

Wadą funkcji JEŻELI, jest to, że w swej najprostszej postaci może ona operować tylko na jednym warunku logicznym. Tak więc gdy chcemy wprowadzić więcej zmiennych należy funkcję JEŻELI zagnieździć. Rozwiązaniem problemu zagnieżdżania jest użycie funkcji ORAZ bądź LUB.

W przypadku funkcji ORAZ, aby funkcja ta zwracała w wyniku wartość PRAWDA, niezbędne jest, aby każdy z ustalonych przez nas warunków, został spełniony (wszystkie warunki muszą zwrócić wartość PRAWDA). W przypadku sytuacji w której chociaż jeden z warunków zwróci wartość FAŁSZ, cała funkcja zwraca w wyniku FAŁSZ. Tak więc nazywając sprawę po imieniu funkcja ORAZ jest iloczynem (koniunkcją) warunków. Funkcja ORAZ pozwala na zastosowanie maksymalnie 30 warunków.

W przypadku funkcji LUB sprawa ma się zgoła odmiennie. Aby funkcja LUB zwróciła wartość PRAWDA przynajmniej jeden z warunków musi być prawdziwy (jeden warunek musi zwrócić wartość PRAWDA). Wartość FAŁSZ zwracana jest gdy wszystkie zdefiniowane warunki zwracają wartość FAŁSZ. Funkcja LUB jest więc, sumą (alternatywą) warunków.

Na tyle by mówiła teoria. Osoby, które wcześniej nie miały do czynienia z opisywanymi funkcjami, tak sobie myślę, że nie za bardzo będą miały do końca pojęcie o co naprawdę chodzi. Tak więc, żeby rozwiać wszelkie wątpliwości przejdźmy to małego (leczy wymownego) przykładu.

Wszystkie przedstawione przykłady w tym wpisie można pobrać tu: zadania funkcja ORAZ i LUB

Na początek przykład zastosowania funkcji ORAZ.

Naszym zadaniem jest sprawdzenie czy w dwóch komórkach została wpisana wartość 1. Jeżeli któryś z warunków nie zostanie spełniony (w komórce będzie wpisana inna wartość niż 1) ma zostać zwrócona wartość FAŁSZ a w przypadku w którym w dwóch sprawdzanych komórkach pojawi się wartość 1 ma być zwrócona wartość PRAWDA. Jak widzimy poniżej mamy cztery możliwe sytuacje i rzeczywiście wartość PRAWDA pojawia się w jednym przypadku, natomiast w pozostałych wartość FAŁSZ.

Do rozwiązania zadania użyliśmy funkcji ORAZ a zbudowana formuła ma postać: =ORAZ(C3=1;D3=1). Formuła ta sprawdza czy w komórce C3 oraz komórce D3 jest wpisana wartość 1 (oczywiście w pozostałych rozwiązaniach zmieniają się odwołania do sprawdzanych komórek). Rozbijając uzyskane rozwiązania na czynniki pierwsze otrzymujemy:

Sytuacja pierwsza: rozwiązaniem jest wartość FAŁSZ ponieważ zarówno w komórce C3 jak i D3 nie ma wpisanej sprawdzanej wartości czyli 1, oba sprawdzane warunki zwracają wartość FAŁSZ tak więc cała funkcja ORAZ zwraca wartość FAŁSZ.

Sytuacja druga: rozwiązaniem jest wartość FAŁSZ ponieważ w komórce D4 brak jest wartości 1, tak więc warunek ten zwraca wartość FAŁSZ, kolejny warunek jest spełniony i zwraca wartość PRAWDA (w komórce C4 znajduje się jedynka), tak więc otrzymujemy FAŁSZ i PRAWDA co w konsekwencji daje nam wynik FAŁSZ. Łatwiej to sobie wyobrazić gdy do wartości FAŁSZ przypiszemy 0 natomiast do wartości PRAWDA przypiszemy 1. Z opisu powyżej wiesz już Czytelniku, że funkcja ORAZ jest iloczynem tak więc otrzymujemy działanie: PRAWDA (komórka C4) razy FAŁSZ (komórka D4) co po podstawieniu wartości daje nam: 1 razy 0 czyli w ogólnym rozrachunku 0 (FAŁSZ).

Sytuacja trzecia: rozwiązaniem jest wartość FAŁSZ ponieważ w komórce C5 brak jest wartości 1, tak więc warunek ten zwraca wartość FAŁSZ, kolejny warunek jest spełniony i zwraca wartość PRAWDA (w komórce D5 znajduje się jedynka), tak więc otrzymujemy FAŁSZ i PRAWDA co w konsekwencji daje nam wynik FAŁSZ. Zestawiając to z analogią opisaną powyżej: otrzymujemy działanie: FAŁSZ (komórka C5) razy PRAWDA (komórka D5) co po podstawieniu wartości daje nam: 0 razy 1 czyli w ogólnym rozrachunku 0 (FAŁSZ).

Sytuacja czwarta: rozwiązaniem jest wartość PRAWDA ponieważ zarówno w komórce C6 jak i D6 znajduje się sprawdzana wartość (wpisana jest wartość 1), oba sprawdzane warunki zwracają wartość PRAWDA tak więc cała funkcja ORAZ zwraca wartość PRAWDA. Po podstawieniu otrzymujemy działanie: 1 (PRAWDA) razy 1 (PRAWDA) czyli wynikiem jest 1 (PRAWDA).

Tak więc myślę, że przykład dobitnie ukazuje sposób działania funkcji ORAZ.

We wstępie zaznaczyłem, że funkcję ORAZ można połączyć z funkcją JEŻELI, więc zróbmy to i zmodyfikujmy nasz przykład. Zadanie polega na tym: by zamiast wartości PRAWDA i FAŁSZ pojawił się zdefiniowany przez nas komunikat: i tak dla wartości PRAWDA – komunikat: WARUNKI ZOSTAŁY SPEŁNIONE natomiast dla wartości FAŁSZ - komunikat: WARUNKI NIE ZOSTAŁY SPEŁNIONE Postawione przed nami zadanie rozwiążemy za pomocą formuły: =JEŻELI(ORAZ(C3=1;D3=1);"WARUNKI ZOSTAŁY SPEŁNIONE";"WARUNKI NIE ZOSTAŁY SPEŁNIONE")

Funkcja ta działa w ten sposób, że gdy warunek określony w funkcji JEŻELI a zdefiniowany za pomocą funkcji ORAZ jest spełniony (czyli PRAWDA) zostaje wykonana WARTOŚĆ_JEŻELI_PRAWDA (wypisanie komunikatu: WARUNKI ZOSTAŁY SPEŁNIONE), jeżeli zaś zwracaną wartością jest FAŁSZ funkcja JEŻELI przechodzi do wykonania WARTOŚĆ_JEŻELI_FAŁSZ (wypisanie komunikatu: WARUNKI NIE ZOSTAŁY SPEŁNIONE).

Pójdźmy dalej i nasze przykładowe zadanie jeszcze bardziej skomplikujmy i zbudujmy taką formułę, która oprócz wypisania czy warunek został spełniony czy nie, umieści jeszcze informację o tym w której komórce pojawiło się 0 (FAŁSZ).

Formuła, która postawione przed nami zadanie wykona, mogłaby mieć następującą składnię: JEŻELI(ORAZ(C3=1;D3=1);"WARUNKI ZOSTAŁY SPEŁNIONE";JEŻELI(ORAZ(C3=0;D3=0);"WARUNKI NIE ZOSTAŁY SPEŁNIONE W OBU KOMÓRKACH 0";JEŻELI(C3=0;"WARUNKI NIE ZOSTAŁY SPEŁNIONE PIERWSZA KOMÓRKA 0";"WARUNKI NIE ZOSTAŁY SPEŁNIONE DRUGA KOMÓRKA 0"))) – chociaż oczywiście zadanie te można by rozwiązać na kilka innych sposobów.

Efekt działania funkcji widzimy na rysunku poniżej.

Formuła ta jest już troszkę skomplikowana i początkującemu zrozumienie jej działania może (choć wcale nie musi) przysporzyć nieco problemu. Dlatego myślę, że lepszym rozwiązaniem jest przedstawienie tej formuły za pomocą schematu blokowego (teraz jak na dłoni widać jak formuła działa).

Pierwsza funkcja JEŻELI sprawdza warunek: ORAZ(C3=1;D3=1) jeśli warunek jest prawdziwy zostaje wypisany komunikat: WARUNKI ZOSTAŁY SPEŁNIONE. W przypadku zwrócenia przez funkcję ORAZ wartości FAŁSZ funkcja JEŻELI przechodzi do wykonania WARTOŚĆ_JEŻELI_FAŁSZ czyli w naszym przypadku zostaje wykonana kolejna funkcja JEŻELI (druga) w której to sprawdzany jest warunek: ORAZ(C3=0;D3=0) Gdy warunek jest prawdą zostaje wykonane wyświetlenie komunikatu: WARUNKI NIE ZOSTAŁY SPEŁNIONE W OBU KOMÓRKACH 0 w przeciwnym przypadku następuje przejście do następnego warunku (trzecia funkcja JEŻELI). W funkcji tej sprawdzanym warunkiem jest zapis: C3=0 Jeżeli komórka C3 równa się 0, funkcja JEŻELI wykonuje WARTOŚĆ_JEŻELI_PRAWDA czyli wypisany jest komunikat: WARUNKI NIE ZOSTAŁY SPEŁNIONE PIERWSZA KOMÓRKA 0 Ostatnią możliwością jest pojawienie się wartości 0 w drugiej komórce, tak więc nie pozostało nam nic innego jak ostatni komunikat: WARUNKI NIE ZOSTAŁY SPEŁNIONE DRUGA KOMÓRKA 0 umieścić w sekcji WARTOŚĆ_JEŻELI_FAŁSZ gdyż jest to ostatnia możliwość, wykonywana tylko w przypadku nie spełnienia wszystkich wcześniejszych warunków.

Funkcja ORAZ świetnie nadaje się gdy mamy do czynienia z przedziałami. Ponieważ znacznie ułatwia nam tworzenie formuł, tworzone formuły są znacznie czytelniejsze. No to mały przykład. Przypuśćmy, że jesteśmy menadżerem, którego zadaniem jest wyliczenie prowizji naszym sprzedawcą w zależności od wartości sprzedanego towaru. Prowizję będziemy wyliczali według poniższych zasad:

- - 0 – 1999 zł - 5,0%
  - 2000 zł – 4999 zł - 10,0%
  - 5000 zł – 9999 zł - 16,5%
  - powyżej 10 000 zł - 25,0%

Przykładowy arkusz natomiast został przedstawiony na rysunku poniżej.

Wyliczenie wartości prowizji zrealizujemy za pomocą formuły: =JEŻELI(ORAZ(D4>=0;D4<=1999,99);D4*0,05;JEŻELI(ORAZ(D4>=2000;D4<=4999,99);D4*0,1;JEŻELI(ORAZ(D4>=2000;D4<=9999,99);D4*0,165;JEŻELI(D4>=10000;D4*0,25;0))))

W formule powyżej każda z funkcji ORAZ została użyta do ustalenia przedziału, natomiast funkcja JEŻELI w zależności od zwróconej wartości PRAWDA bądź FAŁSZ wykonuje wyliczenie wartości prowizji bądź przejścia do sprawdzenia kolejnego warunku.

Tak więc myślę, że przedstawione zadania dobrze dokumentują sposób działania funkcji ORAZ i nie ma co dalej rozwodzić się nad nią (choć jeszcze do niej wrócimy) i czas by przyjrzeć się funkcji LUB. Zaczniemy od tego samego zadania od którego rozpoczęliśmy omawianie funkcji ORAZ lecz funkcję tą zastąpimy funkcją LUB.

Podobnie jak w zadaniu z funkcją ORAZ sprawdzamy czy w komórkach (przynajmniej jednej) pojawi się wartość 1. Jak widać na rysunku poniżej, wartość PRAWDA pojawiła się w wszystkich przypadkach w których to w sprawdzanych komórkach została umieszczona cyfra 1.

Przy opisywaniu funkcji LUB użyłem stwierdzenia, że funkcja ta jest sumą warunków. By zrozumieć czemu w wyniku działania funkcji zwracana jest wartość PRAWDA lub FAŁSZ można cząstkowym, sprawdzanym warunkom w zależności od zwróconego wyniku przypisać wartość 1 lub 0. Jeżeli suma cząstkowych warunków wyniesie 1 bądź więcej zostanie zwrócona wartość PRAWDA, natomiast gdy suma ta będzie równała się 0 wartością zwracaną będzie FAŁSZ.

Sytuacja pierwsza: sprawdzany jest warunek: C3=1, warunek ten jest FAŁSZEM czyli 0, kolejny sprawdzany warunek (D3=1) również zwraca FAŁSZ (ponieważ 0 nie równa się 1) tak więc znów mamy 0. Suma warunków wynosi: 0 + 0 (FAŁSZ + FAŁSZ) co w konsekwencji daje nam 0 czyli wynikiem działania funkcji jest FAŁSZ.

Sytuacja druga: pierwszy warunek jest PRAWDĄ (wpisana wartość 1) czyli mamy 1, natomiast drugi warunek jest FAŁSZEM (wpisana wartość 0) czyli mamy 0. Podsumowując suma składowych warunków wynosi: 1 (1+0) Gdy suma alternatyw wynosi 1 cała formuła zwraca wartość PRAWDA.

Sytuacja trzecia: przypadek ten jest odwróceniem sytuacji drugiej, suma częściowych warunków wynosi również 1, ponieważ pierwszy warunek zwraca FAŁSZ (0), natomiast drugi warunek zwraca PRAWDA (1), tak więc suma wynosi 1 (0+1).

Sytuacja czwarta: oba składowe warunki zwracają wartość PRAWDA tak więc wynikiem funkcji jest PRAWDA (1+1=2).

Gdybyśmy chcieli uzyskać komunikaty podobne do tych jak w przypadku funkcji ORAZ oczywiście należałoby użyć funkcji JEŻELI.

Przy używaniu funkcji ORAZ i LUB (i tak samo gdy wykorzystujemy funkcję JEŻELI) należy mieć na uwadze, że gdy sprawdzaną wartością jest słowo w warunku należy użyć cudzysłowów. No to przykład. Jesteśmy osobą, która prowadzi przesłuchania kandydatów/kandydatek na stanowisko np. kierownik sklepu. Wymagania są następujące: znajomość języka angielskiego, doświadczenie trzy lata bądź więcej, znajomość pakietu MS Office bądź LibreOffice, wykształcenie wyższe marketing i zarządzanie. Wymagania te są konieczne by kandydacie przeszli do kolejnego etapu. By ułatwić sobie zadanie został utworzony arkusz, który ma pomóc w doborze kandydatów.

Do rozwiązania zadania została użyta następująca formuła: =JEŻELI(ORAZ(D5="tak";E5>=3;LUB(F5="office";F5="libre");G5="tak");"dodatkowa rozmowa";"odpada")

Formuła ta przy użyciu funkcji ORAZ i LUB (użyte w warunku funkcji JEŻELI) sprawdza poszczególne warunki częściowe natomiast funkcja JEŻELI w zależności od zwróconej wartości wyświetla odpowiedni komunikat. Komunikat: dodatkowa rozmowa zostanie zwrócono gdy: w kolumnie j. angielski pojawi się słowo tak; w kolumnie doświadczenie wpisana wartość będzie wynosić 3 lub więcej; w kolumnie znajomość programów pojawi się słowo office bądź libre; w kolumnie wykształcenie słowo tak. W przeciwnym przypadku jest wyświetlany komunikat: odpada. Jak można zauważyć wszystkie sprawdzane ciągi tekstowe zostały umieszczone w cudzysłowie a także wielkość użytych liter nie ma znaczenia.

Funkcję LUB (wraz w połączeniu z innymi funkcjami) możemy również wykorzystać w takim oto zadaniu. Przypuśćmy, że mamy listę imion i nazwisk zaimportowaną z innego programu ale niestety przed każdym imieniem mamy dodany zwrot grzecznościowy Pan bądź Pani. Naszym celem jest usunięcie tego zwrotu tak aby pozostało nam samo imię i nazwisko.

Zadanie to możemy zrealizować za pomocą następującej formuły: =JEŻELI(LUB(LEWY(E7;3)="Pan";LEWY(E7;4)="Pani");PRAWY(E7;DŁ(E7)-ZNAJDŹ(" ";E7));E7)

Tak wiem, pewnie się zastanawiasz jak do diabła ta formuła działa. Więc nie pozostaje nam nic innego jak formułę tą rozbić na czynniki pierwsze.

W pierwszej kolejności została użyta funkcja JEŻELI, której zadaniem jest wykonanie warunku.

W warunku funkcji JEŻELI znajduje się wpis: LUB(LEWY(E7;3)="Pan";LEWY(E7;4)="Pani") zadaniem tej części kodu jest sprawdzenie czy w komórce E7 znajduje się wyrażenie Pan bądź Pani. Sprawdzenie następuje z wykorzystaniem funkcji LEWY. Funkcja ta zwraca skrajne lewe znaki z wartości tekstowej. Tak więc wyrażenie LEWY(E7;3)="Pan" powoduje pobranie trzech pierwszych znaków z lewej strony wyrażenia znajdującego się w komórce E7 i sprawdzenie czy pobrane znaki to słowo Pan. Analogicznie działa wyrażenie LEWY(E7;4)="Pani" lecz tu pobierane są cztery znaki i sprawdzanym słowem jest Pani. Gdy słowo Pan bądź Pani pojawi się w sprawdzanej komórce warunek jest spełniony.

W sekcji WARTOŚĆ_JEŻELI_PRAWDA znajduje się wpis: PRAWY(E7;DŁ(E7)-ZNAJDŹ(" ";E7)) Do zbudowania wyrażenia zostały użyte funkcje PRAWY, ZNAJDŹ oraz DŁ. Zadaniem pierwszej funkcji (PRAWY) jest pobranie znaków z komórki E7 przy czym ilość pobieranych znaków jest zależna od wykonania zapisu: DŁ(E7)-ZNAJDŹ(" ";E7) Funkcja DŁ zwraca nam długość ciągu znaków znajdujących się w komórce E7 (czyli 16 - spacje też są wliczane), natomiast funkcja ZNAJDŹ w danej komórce szuka wystąpienia znaku spacja (zapis " " - do pierwszego wystąpienia), po zastosowaniu formuły zwracana jest wartość 4 (ponieważ spacja jest czwartym znakiem) Wykonywane jest odejmowanie, czyli w naszym przypadku: 16-4=12 Wartość 12 jest przekazywana do funkcji PRAWY, tak więc funkcja ta od prawej strony komórki E7 pobiera 12 znaków, tym samym zwracając nam ciąg: Jan Kowalski.

Jeżeli warunek nie jest spełniony formuła zwraca zawartość komórki E7.

Omówione funkcje LEWY, PRAWY, ZNAJDŹ oraz DŁ można użyć w scenariuszu w którym to chcemy w osobnych komórkach mieć imiona i nazwiska.

Aby otrzymać imiona użyj formuły: =LEWY(D7;ZNAJDŹ(" ";D7)-1) natomiast by uzyskać nazwiska: =PRAWY(D15;DŁ(D15)-ZNAJDŹ(" ";D15))

Kolejnym ciekawym zadaniem z zastosowaniem funkcji LUB jest zadanie w którym to musimy wyznaczyć dni weekendowe. Zadanie polega na wskazaniu, która z podanych dat to sobota i niedziela.

Jak widać powyżej przy datach, które przypadają w sobotę bądź niedzielę pojawia się zapis PRAWDA. Zadanie to zostało zrealizowane za pomocą formuły: =LUB(DZIEŃ.TYG(G6)=7;DZIEŃ.TYG(G6)=1) Jak widać po zapisie formuły w rozwiązaniu problemu dodatkowo została użyta funkcja DZIEŃ.TYG Zadaniem tej funkcji jest zwrócenie wartości, która odpowiada numerowi dnia tygodnia. Funkcja zwraca wartości od 1 do 7 - wartość 1 to niedziela, wartość 2 poniedziałek, wartość 3 to wtorek itd. Tak więc nasze zadanie sprowadza się do wyszukania tych wartości, które odpowiadają sobocie (wartość 7) i niedzieli (wartość 1). Oczywiście w ten sam sposób można wyszukać inne dni tygodnia, poprzez modyfikację wartości w funkcji DZIEŃ.TYG Gdyby funkcja nie zadziałała sprawdź czy komórka zawierające datę została sformatowana jako DATA.

Aby zakończyć rozważania na temat funkcji logicznych to jeszcze jedno zadanie z wykorzystaniem funkcji LUB i ORAZ (takie podsumowanie). Celem zadania jest stworzenie formuły, która będzie przyznawała zniżkę na bilety miesięczne przy następujących założeniach:

Zniżka przysługuje osobom, które spełniają następujące warunki:

a) są na emeryturze lub są studentami (niezależnie od dochodu) - zniżka 7%

b) ich dochód miesięczny na osobę nie przekracza bądź równa się 600 zł - zniżka 12%

Jeżeli osoba spełnia zarówno kryteria z punktu a oraz b przyznaj większą zniżkę. W przypadku braku zniżki niech się pojawi stosowna informacja.

Zadanie to rozwiąże nam następująca formuła: =JEŻELI(G10<=600;12%;JEŻELI(G10>600;JEŻELI(LUB(E10="tak";F10="tak");7%;"brak");"brak"))

Zaczynamy od funkcji JEŻELI ponieważ zadanie wymaga od nas wypisanie odpowiedniego komunikatu w zależności od spełnienia danych warunków. Zaczynamy od warunku: G10<=600 ponieważ nijako jest to warunek nadrzędny, tylko po jego spełnieniu jest możliwość otrzymania 12% zniżki. Warunek ten dostarcza nam jedno z rozwiązań. Spełnienie go (kwota od 0 do 600zł) spowoduje przyznanie 12% zniżki. Zostały nam do rozważenia przypadki (3 rozwiązania) w których to: dochód jest większy od 600zł i osoba jest na emeryturze bądź studentem (zniżka 7%) i rozwiązanie w którym to dochód przekracza 600zł ale dana osoba nie należy do żadnej z zdefiniowanych grup (brak zniżki). Ponieważ tak naprawdę w przypadku osoby przekraczającej 600zł i będącej emerytem lub studentem otrzymujemy jedno rozwiązanie, to aż prosi się by warunek ten połączyć ze sobą. Łączenie osiągniemy wykorzystując funkcję LUB. Ponieważ występuje tu rozgraniczenie pomiędzy osobami, których dochód przekracza 600zł i przynależnością do danej grupy musimy ponownie użyć funkcji JEŻELI. Gdy warunek zdefiniowany za pomocą funkcji LUB okaże się PRAWDĄ zostanie przyznana 7% zniżka w przeciwnym wypadku osoba nie otrzymuje rabatu.

Gdyby ta użyta funkcja w dalszym ciągu była nie jasna, to wszelkie wątpliwości rozwieje schemat poniżej.

Wnikliwy czytelnik na pewno zauważy, że jeden z podanych argumentów nigdy nie zostanie spełniony. A mowa tu o zapisie brak zapisanym w WARTOŚĆ_JEŻELI_FAŁSZ w drugiej funkcji JEŻELI. Dzieje się tak ponieważ użyte przedziały: G10<=600 oraz G10>600 wyczerpują wszystkie możliwe rozwiązania, jeśli liczba nie będzie mniejsza bądź równa 600 to musi być większa od 600. Tak więc we wszystkich przypadkach będzie wykonana WARTOŚĆ_JEŻELI_PRAWDA (dalej mowa o drugiej funkcji JEŻELI).

Czy można by było ten sam rezultat osiągnąć za pomocą innej funkcji. Oczywiście, że tak. Formuła, która rozwiązuje postawione przed nami zadanie może wyglądać następująco: =JEŻELI(G10<=600;12%;JEŻELI(ORAZ(G10>600;LUB(E10="tak";F10="tak"));7%;"brak"))

W formule tej wykorzystano fakt, że gdy liczba nie jest mniejsza bądź równa 600 to musi być większa od 600. Dlatego w zapisie formuły znalazła się funkcja ORAZ, a fakt ten odzwierciedla zapis G10>600 stanowiący pierwszy argument funkcji ORAZ. Drugim argumentem jest zapis LUB(E10="tak";F10="tak"), który jest PRAWDĄ w przypadku przynależności danej osoby do jednej z dwóch uprzywilejowanych grup. Użyta funkcja ORAZ zwróci nam PRAWDĘ gdy nie zostanie spełniony pierwszy argument pierwszej funkcji JEŻELI (G10<=600) i równocześnie osoba będzie studentem bądź emerytem. Po spełnieniu tych warunków zostanie zwrócony nam komunikat o 7% zniżce gdyż cała funkcja ORAZ znajduje się w WARTOŚĆ_JEŻELI_PRAWDA drugiej funkcji JEŻELI. Gdy zaś osoba nie będzie znajdować się w danej grupie, funkcja ORAZ zwróci nam FAŁSZ i zostanie wykonane polecenie wyświetlenia zapisu brak.

I na koniec schemat blokowy, który podsumowuje użytą formułę.

I na tym chciałbym zakończyć, myślę że po samodzielnym wykonaniu zadań funkcja ORAZ i LUB przed Tobą czytelniku nie będzie miała żadnych tajemnic.

Excel w zadaniach. Funkcja JEŻELI

2014-10-17T06:35:10+00:00

Funkcja JEŻELI zwraca daną wartość, jeśli podany argument (parametr) zostanie oszacowany jako PRAWDA, albo inną wartość, jeśli argument (parametr) zostanie oszacowany jako FAŁSZ. Dodatkowo funkcję JEŻELI należy stosować do przeprowadzania testów logicznych na wartościach i formułach. Taką definicję funkcji JEŻELI odnajdziemy w pomocy pakietu MS Office. Tłumacząc to trochę prościej zadaniem funkcji JEŻELI jest wykonanie odpowiedniego działania w zależności od tego czy dany warunek zostanie spełniony czy też nie.

Arkusz na którym będziemy pracować można pobrać: tutaj

Ogólna składnia funkcji przedstawia się następująco:

czyli: =JEŻELI(A1=B1; "Prawda, wszystko jest ok"; "Fałsz, nie jest równe")

Wykonując nasz przykład w przypadku spełnienia warunku (komórki A1 i B1 są sobie równe a więc PRAWDA) otrzymamy:

natomiast w przypadku zaistnienia nierówności (różnica pomiędzy wartościami zawartymi w komórkach A1 i B1 - FAŁSZ), otrzymamy o to taki wynik:

Tak więc jak widać w zależności od uzyskanego wyniku (PRAWDA bądź FAŁSZ) sprawdzanego warunku (a raczej testu logicznego) jest podejmowane odpowiednie działanie.

Rozbijając funkcję JEŻELI na składowe, otrzymamy:

- - test_logiczny czyli dowolna wartość lub wyrażenie, którą da się oszacować jako PRAWDA lub jako FAŁSZ. W naszym przykładzie testem logicznym jest wyrażenie A1=B1, jeśli wartości umieszczone w obu komórkach są zgodne co do wartości cały test zostanie oszacowany jako PRAWDA. W przeciwnym przypadku wyrażenie zostanie oszacowane jako FAŁSZ.
  - wartość_jeżeli_prawda to wartość bądź działanie, które jest zwracane bądź wykonywane tylko wtedy, gdy argument test_logiczny przyjmuje wartość PRAWDA lub po prostu, gdy warunek jest spełniony wykonaj wartość_jeżeli_prawda W naszym przykładzie (jak widać powyżej) zostaje wyświetlony ciąg znaków: Prawda, wszystko jest ok gdy test logiczny zostaje oszacowany jako PRAWDA (warunek spełniony, gdyż w obu komórkach jest wpisana liczba 100 czyli zdefiniowany warunek A1=B1 jest PRAWDĄ).
  - wartość_jeżeli_fałsz to wartość bądź działanie, które jest zwracane bądź wykonywane tylko wtedy, gdy argument test_logiczny przyjmuje wartość FAŁSZ lub po prostu, gdy warunek jest niespełniony wykonaj wartość_jeżeli_fałsz. Wracając do naszego przykładu zostaje wyświetlony tekst: Fałsz, nie jest równe w przypadku w którym test logiczny zostaje oszacowany jako FAŁSZ (warunek niespełniony, gdyż wartości wpisane do komórek A1 i B1 nie są zgodne co do wartości).

Tyle co mówi nam teoria na temat funkcji JEŻELI, przejdźmy do przykładów tak aby zobaczyć funkcję w działaniu.

Wróćmy do naszego wcześniejszego zadania omawianego w wpisie: Excel w zadaniach. Sortowanie, losowanie, zliczanie oraz nazywanie a mianowicie arkusza ucznia. Przyjrzyjmy się kolumną w których jest wyliczana średnia. Można np. tak ułożyć formułę aby w kolumnie Średnia wszystkich ocen była wyświetlana wartość 0,00 a dopiero w momencie wpisania jakiejkolwiek oceny była wyliczana średnia. Zadanie to możemy zrealizować (jest mnóstwo innych sposobów) np. za pomocą formuły: =JEŻELI(SUMA(F3:R3)>0;ŚREDNIA(E3:S3);"0,00"), co tłumaczymy: gdy suma z zakresu ocen będzie większa niż 0 to wykonaj funkcję ŚREDNIA w przeciwnym przypadku wstaw wartość 0,00.

Chociaż mnie osobiście bardziej by przekonała o to taka formuła: =JEŻELI(CZY.LICZBA(E3)+CZY.LICZBA(S3)=2;ŚREDNIA(E3:S3);"0,00") Ten przykład zapewnia nam dodatkowo sprawdzenie czy wpisaliśmy ocenę pierwszą (religia) oraz ocenę ostatnią (j.niemiecki) i dopiero gdy te dwa warunki są spełnione następuje wyliczenie średniej. W warunku funkcji JEŻELI zostało zapisane: gdy wartość testu logicznego będzie równać się 2 oblicz średnią z komórek, jeżeli zaś warunek ten jest nie spełniony wypisz 0,00. Można by zapytać skąd u licha wzięło się 2? Wartość 2 została użyta ponieważ warunek CZY.LICZBA(E3)+CZY.LICZBA(S3) po spełnieniu zwraca wartość 2. Funkcja CZY.LICZBA(E3) sprawdza czy w komórce E3 znajduje się liczba, gdy fakt ten zachodzi zostaje zwrócona wartość PRAWDA. Analogicznie działa druga funkcja sprawdzająca wystąpienie liczby w komórce S3. Ponieważ pomiędzy tymi funkcjami został użyty znak + to ni jako otrzymujemy dwie PRAWDY. Poniżej widzimy efekt zastosowania formuły. Jak widać poniżej średnia jest wyliczana tylko w przypadku wpisania obu ocen tj. oceny z religii i j. niemieckiego.

Podobnie moglibyśmy wykonać sprawdzenie faktu wpisania ocen z wszystkich dostępnych przedmiotów, przyrównując wartość testu logicznego do 15 (gdyż tyle jest przedmiotów a każde poprawne wykonanie funkcji CZY.LICZBA daje 1):

=JEŻELI(CZY.LICZBA(E3)+CZY.LICZBA(F3)+CZY.LICZBA(G3)+CZY.LICZBA(H3)+CZY.LICZBA(I3)+

+CZY.LICZBA(J3)+CZY.LICZBA(K3)+CZY.LICZBA(L3)++CZY.LICZBA(M3)+CZY.LICZBA(N3)+

+CZY.LICZBA(O3)+CZY.LICZBA(P3)+CZY.LICZBA(Q3)+CZY.LICZBA(R3)+CZY.LICZBA(S3)=

=15;ŚREDNIA(E3:S3);"0,00")

Jak widać zapisanie funkcji będzie już dość skomplikowane a że działa to widzimy na rysunku poniżej.

Oczywiście, ktoś uważny mógłby zapytać - A co z wpisem nkl? (oznaczającym ucznia niesklasyfikowanego, uczeń 4, na rysunku powyżej). Czemu to średnia w przypadku wpisania nkl, nie miała by być liczona? Oczywiście działanie naszej powyższej formuły jest jak najbardziej prawidłowe, ponieważ weryfikujemy wystąpienie liczby a zapis nkl nie jest liczbą i dla tego test logiczny nie zostaje sprawdzony a tym samym w komórce zostaje wypisana wartość 0,00. Aby poprawić wpiszmy:

=JEŻELI(CZY.PUSTA(E6)+CZY.PUSTA(F6)+CZY.PUSTA(G6)+CZY.PUSTA(H6)+CZY.PUSTA(I6)+

+CZY.PUSTA(J6)+CZY.PUSTA(K6)+CZY.PUSTA(L6)+CZY.PUSTA(M6)+CZY.PUSTA(N6)+

+CZY.PUSTA(O6)+CZY.PUSTA(P6)+CZY.PUSTA(Q6)+CZY.PUSTA(R6)+CZY.PUSTA(S6)=

=0;ŚREDNIA(E3:S3);"0,00")

Wartość testu logicznego została przyrównana do 0 gdyż funkcja CZY.PUSTA sprawdza czy w komórce jest wpisana jakakolwiek wartość (liczba, tekst, data) gdy komórka jest uzupełniona zwracana jest wartość FAŁSZ (czyli 0) a w przypadku braku wpisu wartość PRAWDA (czyli 1). Nam zależy aby wszystkie komórki były uzupełnione a więc nie może się zdarzyć aby któraś z funkcji CZY.PUSTA przyjęła wartość PRAWDA tym samym dając 1 (brak wpisu oceny). Każda z funkcji CZY.PUSTA musi zwrócić wartość FAŁSZ, zwracając 0 a tym samym suma wszystkich wykonanych funkcji CZY.PUSTA musi dać 0 (tak się stanie gdy wpiszemy wszystkie oceny). Gdy warunek zostaje spełniony obliczana jest wartość średniej wprowadzonych ocen.

Jak widać powyżej zaprezentowane funkcje, co zostało już nadmienione wyglądają nader skomplikowanie i łatwo jest przy konstruowaniu ich popełnić błąd. Czy można je zapisać prościej? Oczywiście, że tak. Trzeba znać tylko pojęcie formuły tablicowej. Tak więc w obu przypadkach nasze funkcje mogłyby by przyjąć postać:

- dla funkcji CZY.LICZBA - {=JEŻELI(ORAZ(CZY.LICZBA(E3:S3));ŚREDNIA(E3:S3);"0,00")}

-dla funkcji CZY.PUSTA - {=JEŻELI(LUB(CZY.PUSTA(E3:S3));"0,00";ŚREDNIA(E3:S3))}

Wybacz czytelniku ale nie będę w tym wpisie rozwijał zagadnienia formuł tablicowych jak i co pewne zauważyłeś dwóch kolejnych funkcji logicznych ORAZ i LUB (użytych w powyższych formułach), gdyż tematy te będę chciał opisać w osobnych wpisach. Na tym etapie chciałem tylko pokazać, iż funkcję JEŻELI można łączyć z innymi funkcjami oraz używać jej właśnie w formułach tablicowych. Oczywiście zachęcam do wykonania opisanych przeze mnie przykładów i sprawdzenia, czy to wszystko działa. Przy formule tablicowej nadmienię tylko jedną rzecz, że aby ona zadziałał należy użyć klawiszy Ctrl+Shift+Enter tzn. nawiasów klamrowych {} nie wprowadzamy, wpisujemy formułę a po wciśnięciu nadmienionych klawiszy nawiasy te automatycznie zostaną dopisane. Nawiasy klamrowe przez Excel są używane do zaznaczenia, iż mamy do czynienia właśnie z formułą tablicową.

Funkcję JEŻELI bardzo często używa się do porównania tych samych danych lecz umieszczonych w różnych kolumnach. Za pomocą tej funkcji bardzo szybko jesteśmy w stanie zweryfikować czy dane są ze sobą zgodne.

Posiadamy arkusz uzupełniony przykładowymi danymi, należy porównać czy dane z kolumny B oraz kolumny C w tych samych wierszach są ze sobą zgodne. W przypadku gdy w komórkach występuje zgodność, powinien pojawić się komunikat „ok”, natomiast gdy komórki różnią się od siebie, powinien pojawić się komunikat „błąd”. Porównanie zrealizujemy za pomocą o to takiej prostej formuły: =JEŻELI(B6=C6;"ok";"błąd")

Jak widać poniżej przy tak skonstruowanej formule nie jest uwzględniana wielkość liter. Dla Excela komórka z wpisaną wartością AQZ będzie tożsama z Aqz, AQz, azq, aZq itd. Aby wprowadzić rozróżnienie wielkości liter należy skorzystać z dodatkowej funkcji PORÓWNAJ. Funkcja ta porównuje ze sobą dwa ciągi tekstu zwracając wartość PRAWDA w przypadku zgodności ciągów a wartość FAŁSZ w przypadku jej braku. W przypadku funkcji PORÓWNAJ rozróżniane są wielkie i małe litery, ale ignorowane są różnice w formatowaniu.

Aby porównać ze sobą trzy różne komórki można oto skorzystać z takiej formuły: =JEŻELI(PORÓWNAJ(B16;C16)+PORÓWNAJ(C16;D16)=2;"ok";"błąd") Formuła ta sprawdza czy dana komórka (w tym przypadku komórka C16) jest zgodna z dwiema pozostałymi (z komórką B16 oraz D16). W przypadku zgodności zwracana jest wartość PRAWDA czyli 1, zgodność z obiema komórkami zwraca wartość 2 (PRAWDA+PRAWDA).

Porównanie tego typu można również zrealizować za pomocą zagnieżdżenia funkcji JEŻELI ale jak to wykonać wytłumaczę za chwilę.

Kolejnym zadaniem w którym możemy wykorzystać funkcję JEŻELI jest wyszukiwanie wartości spełniających zadane kryterium.

Przypuśćmy, że pracujemy w sklepie AGD i mamy przygotowany arkusz zawierający listę modeli telewizorów dostępnych w sklepie (taki jak poniżej) i chcemy znać liczbę modeli telewizorów firmy Sony.

Jednym z sposobów poradzenia sobie z zadaniem (rozwiązanie wcale nie jedyne i trza przyznać, że niezbyt wyszukane) jest dołożenie sobie kolejnej kolumny w której pojawi się słowo tak jeżeli model telewizora będzie wyprodukowany właśnie przez Sony. W kolumnie tej trzeba oczywiście wpisać odpowiednią formułę, która przyjmie postać: =JEŻELI(B2="Sony";"tak";"nie").

Jak widać powyżej przy każdym modelu Sony pojawiło się słowo tak.

Skomplikujmy sobie troszkę zadanie i dodajmy kolejne kryterium. Powiedzmy, że teraz interesują nas telewizory marki Samsung lecz tylko te, których rozmiar przekracza 32 cale. Zadanie to można wykonać lecz trzeba użyć dwa razy funkcję JEŻELI, cały trik polega na umiejętnym zagnieżdżeniu funkcji JEŻELI. Tak więc dla lepszego zrozumienia działania formuły w której funkcja JEŻELI została zagnieżdżona przyjmijmy: w przypadku gdy telewizor nie został wyprodukowany przez firmę Samsung niech pojawi się komunikat – „inna firma”; w przypadku w którym telewizor jest mniejszy niż 32 cale - komunikat – „poniżej 32 cali” a gdy nasze kryterium zostanie spełnione (firma Samsung i powyżej 32 cali) – komunikat – „wszystko ok”

Czyli nasze zadanie moglibyśmy rozpisać w ten poniższy sposób (wykorzystamy schemat blokowy):

Z pomocą schematu dokładnie możemy określić składnię formuły (jak ona działa), wystarczy, że romby symbolizujące warunek zamienimy na funkcję JEŻELI, przypisując do pytań w rombach odpowiedni adres komórki, wartości na TAK użyjemy do przypisania parametru wartość jeżeli prawda a wartości na NIE do przypisania parametru wartość jeżeli fałsz. Tak więc nasza formuła przyjmie następującą postać: =JEŻELI(B2="Samsung";JEŻELI(D2>32;"wszystko ok";"poniżej 32 cale");"inna firma")

Jak widać poniżej po przekopiowaniu formuły do pozostałych komórek uzyskaliśmy zamierzony efekt.

Oczywiście funkcję JEŻELI możemy zagnieżdżać wiele razy (a tak naprawdę maksimum to 7) tym samym sprawdzając wiele warunków.

Takim przykładowym zadaniem może być obliczenie wskaźnika BMI. BMI to skrót z języka angielskiego „body mass index”, co tłumaczymy jako wskaźnik masy ciała. Wskaźnik BMI określa relację pomiędzy masą ciała a wzrostem. Współczynnik ten wyliczamy poprzez podzielenie masy ciała podanej w kilogramach przez kwadrat wysokości podanej w metrach. Obliczając BMI możemy określić ilość tkanki tłuszczowej w organizmie.

Nasz kalkulator w zależności od wartości wyliczonego indeksu wyświetli odpowiedni komunikat według następującego przelicznika:

- - do 18,5 - niedobór masy,
  - 18,6 - 24,9 - masa prawidłowa,
  - 25,0 - 29,9 - nadwaga,
  - 30 - 34,9 - 1. st. otyłości,
  - 35 - 39,9 - 2. st. otyłości.

Zadanie to jest trochę trudniejsze niż te z wyborem telewizora z kilku powodów. Po pierwsze rozwiązaniem zadania będzie formuła zawierająca cztery funkcje JEŻELI. Po drugie w zadaniu tym nie będziemy porównywać wartości z wzorcem lecz będziemy musieli zdefiniować przedziały. I w końcu po drugie i pół (gdyż kolejna trudność jest powiązana z powodem drugim) – utworzone zakresy (przedziały) trzeba ułożyć w odpowiedniej kolejności.

Tak więc przykładowa formuła rozwiązująca nasz problem mogłaby wyglądać następująco:

=JEŻELI(E18<=18,5;"niedobór masy";JEŻELI(E18<=24,9;"masa prawidłowa";JEŻELI(E18<=29,9;"nadwaga";JEŻELI(E18<=34,9;"1 st otyłości";"2 st otyłości"))))

Oczywiście by rozwiązać to zadanie można by również użyć takiej o to formuły:

=JEŻELI(E18>=35;"2 st. otyłości";JEŻELI(E18>=30;"1 st. otyłości";JEŻELI(E18>=25;"nadwaga";JEŻELI(E18>=18,6;"masa prawidłowa";"niedobór masy"))))

Wspomniałem, że należy zwrócić uwagę na odpowiednie położenie zakresów (właściwa kolejność). Spójrzmy co by się wydarzyło gdybyśmy naszą formułę zapisali następująco:

=JEŻELI(E18>=18,6;"masa prawidłowa";JEŻELI(E18>=25;"nadwaga";JEŻELI(E18>=30;"1 st. otyłości";JEŻELI(E18>=35;"2 st. otyłości";"niedobór masy"))))

Tak zapisana formuła po wpisaniu wartości, która przekroczy wartość 18,6 zawsze będzie wyświetlać komunikat: masa prawidłowa, stanie się tak, ponieważ funkcja JEŻELI po wykonaniu pierwszego testu uzna, że jest on prawdziwy (co zresztą będzie prawdą) a tym samym zostanie pominięte sprawdzenie kolejnych warunków.

Z funkcją JEŻELI będziemy mieli do czynienia bardzo często. Zaryzykuję stwierdzenie, że jest to najczęściej wykorzystywana funkcja Excela a połączenie funkcji JEŻELI z innymi dostępnymi funkcjami zwiększa jej standardowe możliwości. Tak więc czytelniku po omówieniu tematu oraz ukazaniu podstawowych możliwości jak na ten wpis sprawę uważam za zamkniętą, co nie zmienia faktu, że jeszcze nie jeden raz w innych artykułach do funkcji JEŻELI będę się odwoływał.

Excel w zadaniach. Sortowanie, losowanie, zliczanie oraz nazywanie.

2014-07-19T21:48:46+00:00

W tym wpisie zajmiemy się funkcją: LICZ.JEŻELI oraz omówimy takie zagadnienia jak sortowanie, nazwy komórek oraz obszarów. Dodatkowo poćwiczymy użycie adresacji względnej i bezwzględnej i przypomnimy znane nam już funkcje jak również poznamy nowe. Wszystkie omawiane kwestie te nowe i te o których była już mowa (taka forma powtórzenia) standardowo będą opisywane na przykładzie konkretnego zadania czy też problemu do rozwiązania.

Dziś naszym zadaniem będzie utworzenie arkusza z ocenami dla poszczególnych uczniów, który będzie nam wyliczał poszczególne informacje. Tak więc utworzymy arkusz taki jak ten przedstawiony poniżej:

Zadaniem utworzonego arkusza będzie:

- - wyliczenie ilości poszczególnych ocen,
  - obliczenie średnich ocen uczniów oraz z poszczególnych przedmiotów,
  - wyliczenie ilości ocen z zachowania.

OK więc zaczynamy. Standardowo arkusz z ćwiczeniem do ściągnięcia tu:arkusz ćwiczenie

Pominę opisywanie formatowania, ponieważ formatowanie zostało opisane w wcześniejszych wpisach. Tworzenie arkusza od zera możesz obejrzeć w załączonym filmiku na końcu artykułu.

Pierwszą czynnością jaką wykonamy będzie wpisanie nazwisk i imion uczniów. Specjalnie dane te nie są wpisane w kolejności alfabetycznej gdyż by je ułożyć skorzystamy z funkcji sortowania.

Po zaznaczeniu interesujących nas danych (warto zaznaczyć z polem Nazwisko oraz Imię gdyż pola te zostaną użyte jako nagłówki) aby ułożyć nazwiska wraz z imionami w kolejności alfabetycznej należy kliknąć na karcie Narzędzia główne narzędzie Sortuj i filtruj a następnie z rozwijanej listy wybrać Sortowanie niestandardowe.

Po wybraniu Sortowania niestandardowego ukarze nam się okno opcji sortowania w którym będziemy mogli ustalić zasady przeprowadzenia procesu.

Naszym celem jest ułożenie nazwisk w kolejności od A do Z a w przypadku wystąpienia dwóch takich samych nazwisk brane ma być pod uwagę imię (i taka sytuacja wystąpi przy nazwisku Nowak).

Zaznaczenie uwzględnia wspomniane nagłówki, stąd na liście wyboru pojawia się opcja Nazwisko oraz Imię. Jeżeli chcemy skorzystać z nazw kolumn odznacz opcję: Moje dane mają nagłówki.

By osiągnąć nasz założony cel ułożenia wyrazów należy dodać dwa poziomy sortowania (kolejność według, której będzie przeprowadzane sortowanie). Jako pierwsze wybieramy sortowanie według nazwisk zaś jako drugie sortowanie według imion. Oczywiście w polu Kolejność wybieramy Od A do Z.

Po ustaleniu wszystkich opcji klikamy na OK. Następuje ułożenie wyrazów. Jak widać poniżej ułożenie te jest zgodne z naszymi oczekiwaniami.

Przy sortowaniu należy zwrócić uwagę na jedną bardzo ważną rzecz a mianowicie by sortować wszystkie obszary w których znajdują się dane. Oznacza to, że nie zaznaczenie właściwego obszaru będzie skutkować pomieszaniem danych. W naszym przykładzie poniżej na skutek nie zaznaczenia całości obszaru zostały pozamieniane imiona z nazwiskami.

Na szczęście przed tego typu błędem chroni nas stosowne ostrzeżenie.

Kolejnym naszym krokiem będzie uzupełnienie zachowania, tu wpisujemy fikcyjne dane.

Przyszedł czas by uzupełnić oceny. Można by było wpisać je ręcznie ale my pójdziemy na skróty i skorzystamy z pewnego ułatwienia a mianowicie wykorzystamy funkcję: LOS , która to zwraca losową liczbę rzeczywistą, liczba ta jest większa niż lub równa 0 i mniejsza od 1. Przy funkcji tej należy dopowiedzieć dwie kwestie: pierwsza to że w składni funkcji LOS nie występują argumenty a druga: to że nowa losowa liczba rzeczywista jest zwracana po każdym obliczeniu arkusza, oznacza to że każde odwołanie się do komórki w której jest użyta funkcja LOS powoduje wygenerowanie nowej wartości. By ręcznie wygenerować nowe wartości użyj klawisza F9.

W naszym przykładzie musimy wygenerować liczby z zakresu od 1 do 6 (bo takie mamy stopnie w szkole), wywołanie funkcji LOS spowoduje wygenerowanie liczb od 0 do 1, więc nasuwa się pytanie jak zmienić zakres generowanych liczb? Wystarczy, że skorzystamy z następującego wzoru:LOS()*(b-a)+a (gdzie a jest liczbą od której ma nastąpić generowanie a b granicą, lub prościej jest to zakres od a do b). A więc spróbujmy, nasze a=1 natomiast b=6.

Jak widać powyżej udało nam się wygenerować liczby, których zakres mieści się pomiędzy 1 a 6 tylko że my potrzebujemy liczby całkowite. Aby uzyskać liczby całkowite należy do naszego wzoru dodać jeszcze jedną funkcję a mianowicie funkcję, która zaokrągli nam wygenerowane liczby rzeczywiste. Excel oferuje nam taką funkcję a nazywa się ona ZAOKR. Funkcja ZAOKR zaokrągla liczbę do określonej liczby cyfr. Składnia funkcji jest następująca: ZAOKR(liczba/komórka;liczba_cyfr)

gdzie:

- - liczba/komórka - wymagana liczba, którą należy zaokrąglić.
  - liczba_cyfr - wymagana liczba cyfr, do których liczba ma zostać zaokrąglona.

Tak więc nasz wcześniejszy wzór przyjmie następującą postać: ZAOKR(LOS()*(b-a)+a;0) i dopiero użycie tej formuły złożonej z dwóch funkcji pozwoli nam na wygenerowanie liczb całkowitych od 1 do 6.

Trochę skomplikowane ale przynajmniej poznaliśmy dwie nowe funkcje. Czy jest prostszy sposób? Oczywiście jest można wykorzystać funkcję: LOS.ZAKR, która jest tak naprawdę złożeniem opisanej formuły. Funkcja ta zwraca losową liczbę całkowitą z wybranego zakresu liczb. Składnia funkcji jest następująca: LOS.ZAKR(dolna; górna)

gdzie:

- - dolna - najmniejsza liczba całkowita, jaką może zwrócić funkcja,
  - górna - największa liczba całkowita, jaką może zwrócić funkcja.

Tak więc chcąc wygenerować liczby od 1 do 6 należy wydać polecenie: =LOS.ZAKR(1;6) Funkcja ta jest dosyć nowym pomysłem, istniała już wcześniej ale pod nazwą RANDBETWEEN i dodatkowo wymagała zainstalowania dodatku Analysis ToolPack (starsze wersje Excela). Tak więc jakby z jakiegoś powodu funkcja ta nie była dostępna sprawdź czy ów dodatek jest zainstalowany. W przypadku Excela 2003 - z menu Narzędzia wybieramy pozycję Dodatki, a w oknie dialogowym zaznaczamy opcję Analysis ToolPack i klikamy OK. Jeżeli zaś chodzi o nowsze wersje z karty Plik wybieramy Opcje następnie z opcji dostępnych po lewej stronie wybieramy Dodatki i u dołu ekranu w polu Zarządzaj wybieramy Dodatki programu Excel i klikamy Przejdź W nowo otwartym oknie wybieramy interesujące nas dodatki w tym przypadku Analysis ToolPack.

To jak już wiemy jak utworzyć oceny to wykonajmy to.

Mamy wypełnione oceny.

Aby nie następowało generowanie nowych wartości za każdym odwołaniem się do komórki, która zawiera ocenę należy wykonać jeszcze jeden myk. A mianowicie należy skopiować wygenerowane oceny i wkleić je lecz w opcjach wklejenia należy wybrać Wartości.

Wybranie Wartości jako opcji wklejania spowoduje, że zostanie wklejone tylko to co znajduje się w komórce, zostanie pominięta funkcja, która te dane wygenerowała.

Mamy oceny to przystępujemy do zliczenia ilości poszczególnych ocen. W pierwszej kolejności wyliczymy oceny poszczególnych uczniów. By wykonać to zadanie należy posłużyć się jeszcze nie znaną nam funkcją LICZ.JEŻELI. Funkcja ta zlicza komórki w zakresie, które spełniają określone pojedyncze kryterium. Przykładowo można policzyć wszystkie komórki o wartościach zaczynających się od tej samej litery lub zawierające liczbę większą albo mniejszą od podanej. W naszym przypadku musimy zliczyć ilość wystąpienia danej oceny.

Składnia funkcji jest następująca: LICZ.JEŻELI(zakres; kryteria)

- - zakres - określa jedną lub więcej komórek, które mają zostać zliczone,
  - kryteria - liczba, wyrażenie, odwołanie do komórki lub ciąg tekstowego określający, które komórki będą zliczane.

Poniżej kilka przykładów użycia funkcji LICZ.JEŻELI

=LICZ.JEŻELI(Zakres:1) - zwraca liczbę komórek zawierających wartość 1,

=LICZ.JEŻELI(Zakres:"<0") - zwraca liczbę komórek zawierających wartości ujemne,

=LICZ.JEŻELI(Zakres: "<>0") - zlicza liczbę komórek różnych od zera,

=LICZ.JEŻELI(Zakres:"*") -zlicza liczbę komórek zawierających tekst,

=LICZ.JEŻELI(Zakres:">"&C2) - zwraca liczbę komórek o wartości większej niż wartość komórki C2,

=LICZ.JEŻELI(Zakres:C2) - zwraca liczbę komórek o takiej wartości jaka znajduje się w komórce C2,

=LICZ. JEŻELI(Zakres; "test") - zwraca liczbę komórek zawierających pojedyncze słowo test, bez uwzględnia wielkości liter,

=LICZ .JEŻELI(Zakres: "*test*")- zwraca liczbę komórek zawierających słowo test, słowo może znajdować się gdziekolwiek,

=LICZ. JEŻELI(Zakres: "Z*") - zwraca liczbę komórek zaczynających się na literę Z (nie uwzględnia wielkości liter),

=LICZ .JEŻELI(Zakres: PRAWDA)- zwraca liczbę komórek zawierających wartość logiczną PRAWDA,

=LICZ.JEŻELI(Zakres:"????") - zwraca liczbę komórek zawierających dokładnie cztery litery,

=LICZ.JEŻELI(Zakres:"<"&ŚREDNIA (Zakres)) - zwraca liczbę komórek o wartości mniejsza niż średnia,

=LICZ.JEŻELI(Zakres:DZIŚ()) - zwraca liczbę komórek zawierających aktualną datę,

=LICZ.JEŻELI(Zakres:5)+LICZ.JEŻELI(Zakres:-5) - zwraca liczbę komórek zawierających liczby 5 i -5,

=LICZ.JEŻELI(Zakres: ">9") - zlicza liczbę komórek zawierających wartości większe niż 9.

Aby więc aby wyliczyć ilość wystąpienia oceny 6 u pierwszego ucznia należy zbudować formułę LICZ.JEŻELI(E3:S3;6)

Po zatwierdzeniu okaże się że uczeń ten ma wystawione dwie 6.

Aby wyliczyć ilość 6 dla pozostałych uczniów wystarczy formułę przekopiować (magiczny kwadracik) do pozostałych komórek.

Aby wyliczyć ilość 5 należy powyższą formułę zmodyfikować w polu kryterium tzn. zamiast 6 wpisać 5 - LICZ.JEŻELI(E3:S3;5)

Komórkę kopiujemy do pozostałych i dalej analogicznie przechodzimy do wyliczenia 4,3,2 i 1. Oceny mamy wyliczone.

Wyliczenie wszystkich ocen wymaga od nas wprowadzenia 6 różnych formuł (dla każdej z zliczanej oceny osobno),czy można te same zadanie wykonać za pomocą tylko jednej formuły? Można. Należy tylko zastosować adresację bezwzględną, wyliczenie wszystkich ocen załatwi nam taka o to formułka: LICZ.JEŻELI($E3:$S3;T$2)

Już tłumaczę zasadę jej działania. W formule tej zakres nam się nie zmienił, ciągle chcemy wyliczyć ilość poszczególnych ocen (oceny pierwszego ucznia) więc zakresem będzie zbiór tych ocen. Zakres ten musimy zablokować ponieważ formuła będzie kopiowana w prawo, nie zablokowanie zakresu spowoduje jego przesuwanie podczas operacji kopiowania. Blokujemy litery ponieważ będziemy kopiowanie wykonywali w prawą stronę. W kryterium wpisaliśmy adres komórki T2, czemu akurat ta komórka? Odpowiedź jest prosta, ponieważ w tej komórce wpisana jest wartość 6 a nam zależy na wyliczeniu ilości wystąpienia oceny 6.Dodatkowo w adresie komórki T2 została zablokowana 2. Dwójka ta musi być „zdolarowana” ponieważ kopiowanie również będzie realizowane w dół tak aby każdy uczeń miał wyliczoną ilość każdej oceny. Reasumując, kopiując w dół zakres podąża za nami natomiast wyliczana ocena pozostaje stała, natomiast kopiując w prawo mamy sytuacje odwrotną, zakres nie ulega zmianie a wartość liczonej oceny podąża za kopiowaniem.

Wyliczenie średniej nie powinno Ci czytelniku nastręczyć problemów, gdyż w pierwszym przypadku wyliczamy średnią z wszystkich ocen oprócz religii i języka niemieckiego. Przedmioty te są umieszczone na skraju więc utworzony zakres będzie zakresem ciągłym.

Po wyliczeniu tej średniej najprawdopodobniej pojawi ci się ostrzeżenie (trójkąt w lewym górnym rogu komórki), informujące o tym że Formuła pomija przylegające komórki. Jest to kolejne z zabezpieczeń Excela, które z założenia ma chronić nas przed popełnieniem błędu. My zaś wiemy, że błąd ten jest bezzasadny więc ostrzeżenie możemy zignorować.

W drugim zaś przypadku wyliczamy średnią z wszystkich ocen bez żadnych wykluczeń.

Mamy wykonaną jedną część arkusza a więc pora by wykonać resztę.

Kolejnym elementem, który wyliczymy będzie ilość poszczególnych ocen z zachowania. I tu również użyjemy funkcji: LICZ.JEŻELI Aby wyliczyć ilość ocen wzorowych możemy skorzystać z następującej formuły: LICZ.JEŻELI(D3:D11;"wzorowe")

Pierwsza zmiana, która rzuca się w oczy to użycie cudzysłowu. Cudzysłów ten jest niezbędny gdy zliczamy wartości, które nie są liczbami. By wyliczyć pozostałe oceny z zachowania możemy powyższą formułę powtórzyć dla każdej oceny z zachowania osobno. Możemy też użyć innej formuły (oczywiście by sobie ułatwić) a mianowicie: LICZ.JEŻELI(D$3:D$11;B12)

Użycie formuły spowoduje, że nie będziemy musieli jej korygować przy każdej z ocen z zachowania, wystarczy ją skopiować do pozostałych komórek. Mamy sytuację podobną do tej opisywanej przy wyliczeniu ocen poszczególnych uczniów. Wykorzystaliśmy komórki w których potrzebne nam wartości zostały już wpisane oraz adresację bezwzględną. Zablokowaliśmy zakres w którym znajdują się oceny z zachowania, zakres został zablokowany gdyż kopiowanie będzie realizowane w dół.

Przechodzimy do wyliczenia ilości poszczególnych ocen lecz tym razem w rozbiciu na przedmioty. I znów do tego zadania posłuży nam funkcja LICZ.JEŻELI. Zakresem w tym przypadku będą wszystkie oceny z danego przedmiotu zaś kryterium będzie stanowiła ocena. Do wyliczenia ilości ocen 6 możemy posłużyć się formułą: LICZ.JEŻELI(E3:E11;6) i następnie skopiować ją w prawo.

W tym przypadku formuły liczące pozostałe oceny musimy albo utworzyć każdą z osobna lub skopiować pierwszą (z uwzględnieniem adresacji bezwzględnej) i utworzone w ten sposób formuły zmodyfikować (zamienić 6 na inne oceny). By trochę sobie uprościć w formule powyższej zablokujemy cyfry w zakresie tak by nie ulegał on przesunięciu podczas kopiowania w dół. Formuła przybierze postać: LICZ.JEŻELI(E$3:E$11;6)

Teraz musimy zmodyfikować poszczególne formuły uwzględniając liczone oceny.

Czy można te wyliczenie ocen zrobić jeszcze prościej? Oczywiście można. Bardzo często przygotowując arkusz z np. jakimś opracowaniem statystycznym tworzę sobie szereg pomocniczych komórek, które pozwalają mi uprościć prowadzone obliczenia. I tak samo można zrobić w tym przypadku. Wystarczy, że wykorzystamy do tego celu jakieś nie używane komórki. W naszym zadaniu musimy utworzyć komórki w których znajdą się wartości wpisane od 6 do 1 (jeśli chcemy by wartości były niewidoczne możemy zmienić kolor tekstu na kolor biały w ten sposób zostanie „ukryta” zawartość komórek).

Teraz by wyliczyć całość ocen należy skorzystać z formuły: LICZ.JEŻELI(E$3:E$11;$AD8) (zakres blokowanie ze względu na kopiowanie w dół, kryterium blokowane ze względu na kopiowanie w prawo). Zrobione.

Średnia ocen zostanie obliczona z wykorzystaniem formuły: ŚREDNIA(E3:E11)

Ostatnią czynności, która pozostaje nam do wykonani jest zsumowanie ilości wyliczonych ocen. Do tego wykorzystamy funkcję: SUMA

Tak więc arkusz mamy wykonany.

Rozwijając i poszerzając temat o jeszcze jedno zagadnienie a mianowicie o nazywanie komórek lub obszarów komórek dojdziemy do wniosku, że arkusz ten można by było wykonać (wspomniałem kiedyś, że jeśli chodzi o Excela to jedno właściwe rozwiązanie nie istnieje – dlatego właśnie Excel jest tak uniwersalny) właśnie w oparciu o nazwy.

Nazwy wymyślono po to by użytkownikowi łatwiej było budować nowe formuły ale również by lepiej orientować się już w tych utworzonych. Łatwiej jest nam po prostu zorientować się co jest liczone i w jaki sposób. Poprzez zastosowanie nazw gdy formuła ma postać: =ŚREDNIA(oceny_Baszta) od razu wiemy co wyliczamy, niż gdyby było zapisane =ŚREDNIA(E3:S3).

Nazwy również mogą nam posłużyć do odwołań do innych komórek np. poprzez formułę: =Baszta Wos zbudujemy adres odwołujący się do oceny ucznia Baszty z WOS-u.

Zanim zaczniemy tworzyć nazwy parę słów o regułach jakie rządzą przy ich nadawaniu:

- Prawidłowe znaki - jako pierwszy znak nie może zostać użyta cyfra czyli pierwszy znak nazwy musi być literą, znakiem podkreślenia (_) lub ukośnikiem odwrotnym (\). Pozostałe znaki w nazwie mogą być literami, cyframi, kropkami lub znakami podkreślenia. Niedozwolone jest zdefiniowanie następujących nazw: „K”, „k”, „W” ani „w”, ponieważ znaki te używane są jako skróty.
- Odwołania do komórek — zabronione - nazwy nie mogą być pokrywać się z adresami komórek, na przykład A$10 lub A1B1.
- Spacje są niedozwolone – niedozwolone jest użycie znaku spacji w nazwach. Aby oddzielić od siebie wyrazy można użyć do tego celu znak podkreślenia (_) bądź kropkę (.), na przykład: wypłata_pracowników lub podatek.VAT.
- Długość nazwy – długość nazwy jest ograniczona do 255 znaków.
- Uwzględnianie wielkości liter -nazwy mogą zawierać wielkie i małe litery. Wielkość liter przez program Excel nie jest rozróżniana. Utworzona np. nazwa Kupno jest tożsama z np. nazwą KUPNO.

Tak więc przykład. W pierwszej kolejności utwórzmy nazwy, które będą odwoływać się do ocen z przedmiotów. W tym celu zaznaczamy wszystkie oceny wraz z nazwą przedmiotu. Utworzenie nazw wykonamy po przejściu na zakładkę Formuły i kliknięciu na Utwórz z zaznaczenia.

Po wybraniu opcji w nowo otwartym oknie możemy ustalić skąd mają zostać pobrane dane celem utworzenia nazw. W naszym przypadku wybieramy Górny wiersz

Po kliknięciu na OK zostanie utworzonych 15 nazw, które będą odwoływać się do ocen z danego przedmiotu.

Poprawność utworzenia nazw możemy sprawdzić poprzez kliknięcie na Menedżer nazw.

Po kliknięciu możemy stwierdzić: jakie nazwy zostały utworzone, wartość, odwołanie oraz zasięg stosowania nazw (ten arkusz czy np. cały skoroszyt).

Od tej pory wpisując nazwę w komórce możemy się do niej odwołać. Utworzone nazwy pojawią się wraz z propozycją funkcji.

Sprawdźmy czy odwołania działają i spróbujmy wyliczyć średnią z przedmiotu historia, następnie porównamy wartość wyliczonej średniej z wartością wyliczoną w sposób tradycyjny.

Jak widać na powyższym zrzucie wywołanie nazwy historia i połączeniu nazwy z funkcją ŚREDNIA powoduje wyliczenie średniej z przedmiotu historia. Nieważne jaki sposób wybierzemy wyliczona wartość będzie taka sama.

Odwoływać się do utworzonych nazw możemy tak samo jak do adresów komórek, tak więc formuła: ŚREDNIA(historia;WOS) wyliczy nam średnią z historii i WOS-u, natomiast formuła: ŚREDNIA(historia:matematyka) średnią z przedmiotów od historia do matematyka (tj. historia, WOS, j.angielski i matematyka).

Spróbujmy teraz zdefiniować nazwy, które będą odnosić się do ocen konkretnych uczniów (np. zdefiniowana nazwa od nazwiska ucznia Baszta będzie się odnosić to wszystkich ocen tego ucznia). Tu niestety już nie ma tak prosto jak to było w przypadku ocen z przedmiotów ponieważ zaznaczony obszar jest nieciągły i nie da się utworzyć nazw z zaznaczenia (nie wiem jak to jest w Office 2013 ale w 2010 nie ma takiej możliwości przy takim zaznaczeniu). Dlatego musimy te nazwy utworzyć dla każdego z ucznia osobno. Nazwę po zaznaczeniu ocen ucznia utworzymy poprzez kliknięcie na Definiuj nazwę.

Po zdefiniowaniu nazw, możemy je użyć w formułach.

Gdy chcemy odwołać się do wspomnianej wyżej oceny ucznia Baszty z przedmiotu WOS zamiast wpisywać niewiele nam mówiący adres: =H3 możemy użyć odwołania =Baszta WOS

Na koniec jeszcze należałoby by wspomnieć, że nazwy komórek możemy definiować w polu nazwy komórki, poprzez wpisanie jej nazwy.

I to by było na tyle. Arkusz mamy utworzony a użyte narzędzia i funkcje programu Excel omówione. W następnym wpisie zajmiemy się funkcją JEŻELI.

I na koniec filmik obrazujący wszystkie czynności.

{mp4}Excel/arkuszocen/arkuszocen{/mp4}