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.

EXCEL – Ćwiczenia 4

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

EXCEL – Ćwiczenia 4

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