EXCEL – Ćwiczenia 4
1
XXIV. Formuły Tablicowe
Tablice: są to zbiory komórek lub wartości na których moŜna wykonywać operacje grupowe. Tablica
moŜe być zapisana w komórkach lub moŜe przyjąć postać stałej zawierającej wiele elementów. Do
operowania na tablicach uŜywamy formuł tablicowych. Wprowadzanie formuły tablicowej zakończone
będzie zawsze naciśnięciem klawiszy Ctrl+Shift+Enter. Formułę tablicową odróŜnia się od
zwykłej po nawiasach klamrowych.
1. Skonstruujmy formułę tablicową mnoŜącą dwa 5-elementowe pionowe tablice i umieszczającą w
trzeciej 5-elementowej tablicy pionowej wynik składający się
z
iloczynów
poszczególnych
odpowiadających
sobie
elementów. Wprowadź dane jak na rysunku, zaznacz obszar
C1:C5 , wpisz formułę A1:A5*B1:B5, następnie naciśnij
Ctrl+Shift+Enter aby określić tablicowy charakter tej
formuły. Oczywiście w tym przypadku to samo mogłeś
otrzymać wpisując do C1 formułę =A1*B1 i kopiując ją
potem do komórek C2,C3,C4,C5.
2. Wstaw następujące wartości do komórek : A1- 4, A2– 9, A3– 16, A4- 25. Zaznacz obszar
B1:B4. Wpisz PIERWIASTEK(A1:A4), naciśnij Ctrl+Shift+Enter. W kolumnie B
otrzymasz wartości pierwiastków dla składowych z kolumny A. Teraz oblicz te pierwiastki nie
uŜywając formuł tablicowych.
3. Arkusz z lewej strony uŜywa
standardowych
metod
do
wyliczenia tego jak zmieniała
się średnia wyników dwóch
testów
przeprowadzonych
wśród studentów. W tym celu
korzysta
z
dodatkowej
kolumny Zmiana zawierającej
róŜnicę pomiędzy testem2 a
testem1. Arkusz z prawej
strony wylicza tę samą wartość
przy
pomocy
formuły
tablicowej. W tym przypadku umieszczona jest ona tylko w jednej komórce, poniewaŜ wynik jest
pojedynczą wartością.
4. Aby obliczyć macierz odwrotną, lub iloczyn macierzy
niezbędne jest stosowanie formuł tablicowych. Łatwiej
będzie jeśli najpierw obszarowi B2:D4 nadasz nazwę.
Zaznacz ten obszar i w polu nazwy wpisz A. Zaznacz teraz
obszar B7:D9 wybierz funkcję Macierz.Odwrotna jako
argument
wpisz
A
(tablica,
którą
wcześniej
zdefiniowaliśmy).
Nie
zapomnij
o
naciśnięciu
Ctrl+Shift+Enter, w ten sposób tworzymy formułę
tablicową, która wydaje wyniki dla całego obszaru.
5. Podobnie wykonaj pozostałe operacje na macierzach.
6. W tym przykładzie formuła tablicowa nie jest jedynym sposobem
na uzyskanie sumy iloczynów liczb, ale pozwala pominąć
wprowadzania dodatkowej kolumny z wynikami pośrednimi
(iloczynów odpowiednich komórek kolumny A i B) Wzór sumy
uzyskasz po wykonaniu Wstaw Obiekt Microsoft Equation.
7. Wprowadź do komórek A1-A10 dowolne liczy o róŜnych
znakach. Spróbujmy teraz wyliczyć średnia liczb dodatnich. Dla ułatwienia nadajmy nazwę Dane
grupie komórek A1:A10. Teraz korzystamy z formuły {=ŚREDNIA(JEśELI(Dane>0;Dane;” ”))}.
ZauwaŜ, Ŝe funkcja JEśELI sprawdza, czy kaŜda liczba naleŜąca do obszaru Dane jest>0. Jeśli tak
EXCEL – Ćwiczenia 4
2
funkcja zwraca sprawdzaną wartość, w przeciwnym przypadku funkcja zwraca pusty łańcuch.
Powstaje tablica w której miejsce liczb ujemnych zajmują puste komórki. Funkcja średnia wylicza
średnią powstałej w ten sposób tablicy i zwraca poprawny wynik. Spróbuj zrobić to samo (oczywiście
bez
uprzedniego
sortowania
danych)
nie
korzystając
z
formuł
tablicowych:=SUMA.JEśELI(Dane;”>0”;Dane)/LICZ.JEśELI(Dane;”>0”).
8. Aby sprawdzić czy dana pozycja znajduje się w danym obszarze, moŜesz uŜyć polecenia
Edycja→ Znajdź lub formuły tablicowej. Do kolumny A wprowadź dane jak na rysunku i następnie
nadaj komórkom A3:A8 nazwę meble. Formuła tablicowa wpisana do komórki D5 sprawdza nazwę
wpisaną do komórki D3 o nazwie szukany i jeŜeli taki mebel jest wśród naszych mebli wypisuje „ Tak
jest”, w przeciwnym razie „ Niestety nie ma” . Funkcja LUB zastosowana w formule tablicowej zwraca wynik dla całego obszaru
9. Teraz policzymy ilość znaków w jakimś obszarze. Do komórek A1:A12 wprowadź nazwy miesięcy
(zastosuj wypełnienie). Nadaj im następnie nazwę Rok. Do B1 wprowadź formułę
{=SUMA(DŁ(Rok))}
Formuła ta tworzy tablicę zawierającą długości wszystkich wyrazów z obszaru Rok, a następnie
funkcja SUMA liczy sumę wszystkich elementów stworzonej tablicy.
XXV-UŜywanie komendy Autofiltr
Filtrów uŜywa się w celu wyodrębnienia tylko określonych elementów z pewnego zbioru. Komenda
Excela Autofiltr pozwala na filtrowanie listy w taki sposób
abyś widział te informacje które w danej chwili cię
interesują.( Lista jest nazwaną serią wierszy zawierających
podobne informacje np. 100 wierszy zawierających
informacje o płacach pracowników.)
• Filtrowanie względem jednej kolumny
Wykorzystajmy komendę Autofiltr do wyświetlenia tylko
tych wierszy arkusza, które zawierają informacje dotyczące
pracowników określonego oddziału:
Otwórzmy teraz arkusz Filtr zawierający listę płac.
Zaznacz dowolną komórkę znajdującą się w obrębie listy. Kiedy uŜywasz komendy Autofiltr jest rzeczą
waŜną aby twoja lista zawierała etykiety kolumn; pamiętaj o tym gdy będziesz projektował swoje
arkusze.
Wybierz Dane, Filtr, Autofiltr, aby przełączyć arkusz w tryb Filtr . Obok kaŜdej etykiety kolumny
znajdują się teraz strzałki list rozwijalnych
Kliknij strzałkę listy rozwijalnej znajdującej się po prawej stronie kolumny ODDZIAŁ w celu
wyświetlenia listy wartości zawartych w tej kolumnie. MoŜesz wybrać jedną z tych wartości, aby
wyświetlić tylko wiersze zawierające tę wartość
Zaznacz Niemcy, aby wyświetlić tylko wiersze zawierające wartość Niemcy w kolumnie ODDZIAŁ.
Kiedy filtrujesz listę, wiersze nie spełniające warunku filtrowania zostają ukryte (u nas osoby pracujące w
oddziałach w krajach innych niŜ Niemcy). Filtrowany rząd ma kolor niebieski. MoŜesz równieŜ zobaczyć
na pasku statusu u dołu ekranu liczbę wierszy spełniających kryteria (u nas 27 spośród 94 zawiera
Niemcy w kolumnie ODDZIAŁ)
Wybierz Dane, Filtr, PokaŜ wszystko aby ponownie wyświetlić całą listę. Arkusz nadal znajduje się w
trybie Filtr
Wybierz Dane, Filtr, Autofiltr w celu wyłączenia trybu Filtr. Strzałki list rozwijalnych znajdujących się
obok etykiet znikają
• Filtrowanie względem dwóch kolumn w celu stworzenia warunku „i”.
Przefiltrujmy teraz naszą listę w taki sposób, aby wyświetlić tylko pracowników oddziałów w
Niemczech, zatrudnionych w departamencie Pokazy:
1. Zaznacz dowolną komórkę znajdującą się w obrębie listy. Wybierz Dane, Filtr, Autofiltr, aby
przełączyć arkusz w tryb Filtr .
2. Z listy rozwijalnej ODDZIAŁ wybierz Niemcy, aby wyświetlić tylko pracowników zatrudnionych w
oddziałach niemieckich. Strzałka listy rozwijalnej, znajdująca się obok etykiety kolumny, zmienia
kolor wskazując, iŜ filtr jest aktywny w tej kolumnie.
3
3. Z list rozwijalnej DEPART wybierz Pokazy. Teraz wszystkie widoczne wiersze danych zawierają
wartość Niemcy w kolumnie ODDZIAŁ i Pokazy w kolumnie DEPART.
• Usuwanie filtra kolumny
Polecenie Dane, Filtr, PokaŜ wszystko usuwa wszystkie filtry, które są aktualnie aktywne na twojej
liście. Usuńmy jeden z dwóch filtrów aktualnie działających na naszej liście:
1. Kliknij strzałkę listy rozwijalnej znajdującej się obok etykiety kolumny ODDZIAŁ. Lista rozwijalna
kaŜdej kolumny zawiera 4 opcje oprócz wartości znajdujących się w danej kolumnie: ( Wszystkie),
( Inne), ( Puste), ( Niepuste)
2. Wybierz (Wszystkie).Wszystkie wyświetlane wiersze spełniają teraz tylko warunek nakładany na nie
przez drugi filtr.
• Wyszukiwanie komórek pustych bądź pełnych
Kolumna KORZ naszego arkusza zawiera litery oznaczające jakie korzyści przysługują danemu
pracownikowi: (L-otrzymuje opiekę lekarską, S-otrzymuje opiekę stomatologiczną, E- uczestniczy w
programie emerytalnym). Dokonajmy teraz filtrowania w taki sposób aby znaleźć pracowników nie
otrzymujących Ŝadnych korzyści, a następnie tych otrzymujących korzyści.
1. Z listy rozwijalnej KORZ wybierz (Puste).
2. Z listy rozwijalnej KORZ wybierz (Niepuste).Uwaga: dla potrzeb filtrowania Excel przyjmuje
komórkę zawierającą tylko spację za niepustą.
3. Wybierz Dane, Filtr, PokaŜ wszystko aby usunąć aktywne filtry z naszej listy.
• UŜycie opcji 10 pierwszych
Tej opcji moŜna uŜywać do filtrowania danych numerycznych. UŜyjemy tego filtru aby znaleźć
pracowników dostających najwyŜsze pensje.
1. Przesuń podgląd kolumn na kolumnę K (Płaca brutto)
2. Z listy rozwijalnej PŁACA BRUTTO wybierz 10 pierwszych, aby otworzyć okno dialogowe Autofiltr
10 pierwszych
3. Sprawdź czy w ramce PokaŜ są ustawione następujące dane: Pierwsze, 19, pozycje.
4. Kliknij OK. ZauwaŜ, Ŝe dziesięć najwyŜszych wynagrodzeń mieści się w zakresie 860zł do 1 008 zł.
5. Wybierz Dane, Filtr, PokaŜ wszystko aby usunąć aktywne filtry z naszej listy
• UŜywanie własnych kryteriów w celu stworzenia warunku LUB
Kryteria własne pozwalają na filtrowanie kolumny na podstawie dwóch warunków lub na poszukiwanie
wartości, które nie odpowiadają dokładnie pewnej wartości. Tworzenie warunku LUB oznacza
znalezienie takich wierszy, które spełniają albo pierwszy warunek albo drugi. Wykorzystajmy własne kryteria w celu znalezienia pracowników pracujących w jednym z dwóch odziałów:
1. Przełącz swoją listę w tryb Filtr. Z listy rozwijalnej ODDZIAŁ wybierz Niemcy, a następnie Wlk.
Brytania . ZauwaŜ, Ŝe wybierając inną wartość z listy rozwijalnej danej kolumny, nowa wartość
zastępuje aktualny filtr kolumny.
2. Aby stworzyć warunek LUB musimy skorzystać z kryteriów własnych. Z listy rozwijalnej
ODDZIAŁ wybierz (Inne) w celu wyświetlenia okienka dialogowego Autofiltr uŜytkownika, które
pozwala na połączenie dwóch kryteriów dla jednej kolumny. Pierwsze dwa pola wyświetlają
wyświetlają aktualne kryterium, czyli ODDZIAŁ=Wlk Brytania. Kliknij opcję LUB
3. Z listy rozwijalnej drugiego operatora (w lewej dolnej części pola PokaŜ wiersze spełniające warunek)
wybierz =
4. Z listy rozwijalnej drugiej wartości operatora (w prawej dolnej części pola PokaŜ wiersze spełniające
warunek) wybierz Niemcy. Te kryteria pozwolą nam na wyświetlenie tylko tych pracowników, którzy
mają w kolumnie ODDZIAŁ wartość Wlk Brytania lub Niemcy. Kliknij OK. Ponownie wyświetl
wszystkie wiersze tej listy.
• Filtrowanie w celu znalezienia zakresu wartości
Kryteria własne moŜesz takŜe wykorzystać w celu znalezienia wartości, które są mniejsze lub większe od
określonej wartości.{porównywanie pól tekstowych odbywa się względem kolejności alfabetycznej}.
Dokonaj filtrowania listy w celu znalezienia pracowników pracujących co najmniej 35 godzin
tygodniowo a następnie tych którzy zarabiają co najmniej 12.50 ale nie więcej niŜ 19,50 za godzinę.
1. Z listy rozwijalnej GODZ wybierz (Inne), aby wyświetlić okno dialogowe Autofiltr uŜytkownka
4
2. W polu PokaŜ wiersze spełniające warunek wybierz >= jako operator a 35.5 jako wartość dla
pierwszego kryterium. Kliknij OK., aby przefiltrować listę
3. Wybierz Dane, Filtr, PokaŜ wszystko, aby ponownie wyświetlić całą listę.
4. Z listy rozwijalnej STAWKA GODZ wybierz (Inne)
5. Jako pierwsze kryterium w polu PokaŜ wiersze spełniające warunek wprowadź >=12.5 zł
6. Sprawdź, czy została wybrana opcja I, a następnie wprowadź <=19.50 zł jako drugie kryterium.
Kliknij OK
• UŜywanie znaków wieloznacznych „*” i „?”
Znaki wieloznaczne pozwalają na znalezienie informacji nawet wtedy, gdy tylko częściowo wiesz czego
szukasz. Symbol ? zastępuje dowolny znak, symbol * zstępuje dowolny ciąg znaków. Znajdź wszystkich,
których nazwiska zaczynają się na M ,a potem tych którzy uczestniczą w programie emerytalnym
1. Z listy rozwijalnej NAZWISKO wybierz (Inne)
2. Wybierz = jako operator a m* jako wartość dla pierwszego kryterium (Kryteria nie uwzględniają wielkości liter). Kliknij OK.
3. Wyświetl całą listę. Wyświetl okno Autofiltr UŜytkownika dla kolumny KORZ
4. Wprowadź =*e jako pierwsze kryterium.
5. Kliknij OK. Kryterium to odnalazło wszystkie wartości kolumny KORZ kończące się na e, nie
zostały wyświetlone wiersze tych osób, które mają za literą e jeszcze inne litery.
6. Ponownie wyświetl okno dialogowe Autofiltr uŜytkownika dla kolumny KORZ, a następnie
zmodyfikuj kryterium na =*e*. Kliknij OK.
7. Teraz wykorzystajmy połączone znaki wieloznaczne i warunek LUB w celu znalezienia czy
pracownicy z kodem A zostali przypisani do odpowiedniego departamentu (Rajdy dorosłych). Pole
KOD zawiera kody oznaczające oddział i department kaŜdego pracownika. Pierwsza litera kodo
odpowiada pierwszej literze oddziału pracownika (za wyjątkiem Wlk. Brytanii tu: 2 litery), a druga
litera jest kodem departamentu pracownika. Wyświetl okno dialogowe Autofiltr uŜytkownika dla
kolumny KOD.
8. Jako pierwsze kryterium wprowadź =?r* .Odnajdziemy kody pracowników z literą r jako drugą {kod
departamentu Rajdy dorosłych}. Będziemy musieli znaleźć jeszcze Brytyjczyków pracujących w tym
departamencie, poniewaŜ u nich ta litera kodowa r jest na 3 pozycji.
9. Kliknij LUB, wprowadź =jako drugi operator, ??r* jako drugie kryterium.
10. Kliknij OK., aby przefiltrować listę. Wszyscy wyświetleni pracownicy pracują w departamencie
Rajdy Dorosłych. Oczywiście mogliśmy ich wyszukać uŜywając prostego filtru z kolumny DEPART,
ale tak sprawdziliśmy dodatkowo czy kody w kolumnie KOD zostały właściwie przypisane.
• UŜywanie narzędzia Autosuma z przefiltrowaną listą
Komenda Autosuma będzie wykonywała operacje tylko na widocznych wierszach przefiltrowanej listy.
Dodatkowym udogodnieniem jest to, Ŝe gdy zostaną zmienione warunki filtrowania, komenda Autosuma
automatycznie i odpowiednio uaktualni wynik.
1. UŜyj F5 (klawisz skrótu polecenia Idź do) w celu znalezienia komórki K99. Teraz wykorzystamy
narzędzie Autosuma, aby obliczyć całkowite wypłaty.
2. Kliknij narzędzie Autosuma (Σ), a następnie naciśnij Enter aby wstawić funkcję
3. Zaznacz komórkę K99 i przyjrzyj się paskowi formuły. Excel wykorzystuje funkcje SUMY
POŚREDNIE do obliczenia sumy wartości filtrowanej listy.
4. Dokonaj filtrowania listy tak, aby zostali wyświetleni tylko pracownicy pracujący w departamencie
Pokazy
5. Zaznacz komórkę K99 i przyjrzyj się jej, Excel uaktualnił funkcje SUMY POŚREDNIE