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