Baza danych (lista) w programie Excel jest to dowolna grupa danych umieszczonych w skoroszycie, której pierwszy wiersz zawiera nagłówki definiujące znaczenie poszczególnych kolumn danych. Nie ma potrzeby definiowania bazy danych w jakiś specjalny sposób — Excel potrafi automatycznie rozpoznać taki obszar danych.
Jeżeli wprowadzamy dane ułożone w wierszach (baza danych), może się to okazać łatwiejsze przy użyciu wbudowanych do Excela formularzy.
Zacznij od zdefiniowania nagłówków w pierwszym wierszu nagłówków kolumn. Zaznacz komórkę w wierszu nagłówków i wybierz polecenie Dane/ Formularz. Program zapyta czy ten wiersz ma być wierszem nagłówków, odpowiedz Tak. Pojawi się okno dialogowe. Do poruszania się po polach edycji można używać klawisza [Tab]. Kiedy zakończysz wpisywanie danych z jakiegoś wiersza, kliknij przycisk Nowy. Excel przepisze wtedy dane do arkusza i oczyści pola edycji. Możesz rozpocząć wpisywanie danych do nowego wiersza.
Utwórz następującą listę uczniów w twojej klasie do arkusza jak na rysunku korzystając z formularza wprowadzania danych.
W utworzonej liście znajdź:
üuczniów, których pierwszymi literami nazwiska są „Pi”;
üuczniów, mających więcej niż 20 lat;
üSwoje imię i nazwisko.
1. W oknie formularza naciśnij przycisk Kryteria — na ekranie pojawi się okno dialogowe kryteriów wyszukiwania.
2. W odpowiednich polach formularza wprowadź kryteria wyszukiwania.
3. Naciśnij przycisk Znajdź następny, aby odszukać kolejny rekord spełniający kryteria wyszukiwania.lubNaciśnij przycisk Znajdź poprzedni, aby odszukać kolejny rekord spełniający kryteria wyszukiwania.
§ Kryteria wyszukiwania możesz wprowadzać w dowolnej kombinacji pól. Jeżeli wprowadzisz więcej niż jeden warunek, to Excel będzie wyświetlał tylko rekordy spełniające wszystkie kryteria wyszukiwania.
§ Im więcej wprowadzisz kryteriów wyszukiwania, tym dokładniejsze będą jego rezultaty i tym mniejsza ilość otrzymanych w odpowiedzi rekordów.
§ Podając kryteria wyszukiwania możesz używać operatorów porównania oraz symboli wieloznacznych (tabele poniżej). Przykładowo, wprowadzenie warunku >10 spowoduje wyświetlenie tylko tych rekordów, które w określonym polu posiadają wartość większą niż 10.
Operatory porównania
Operator
Funkcja
=
Równy
<>
Nierówny
>
Większy niż
>=
Większy lub równy
<
Mniejszy niż
<=
Mniejszy lub równy
Symbole wieloznaczne
Symbol
?
Zastępuje dowolny pojedynczy znak
*
Zastępuje dowolny ciąg znaków
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 w danej chwili które cię interesuję.
Wykorzystajmy komendę Autofiltr do wyświetlenia tylko tych wierszy arkusza, które zawierają informacje dotyczące pracowników określonego oddziału:
üOtwórz teraz arkusz Filtrowanie.xls 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 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ść Niemcyw 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 stanu u dołu ekranu liczbę wierszy spełniających kryteria.
ü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ą.
Przefiltruj teraz naszą listę w taki sposób, aby wyświetlić tylko pracowników oddziałóww Niemczech, zatrudnionych w departamencie Pokazy:
üZaznacz dowolną komórkę znajdującą się w obrębie listy. Wybierz Dane/Filtr/Autofiltr, aby przełączyć arkusz w tryb Filtr.
üZ listy rozwijalnej ODDZIAŁ wybierz Niemcy, aby wyświetlić tylko pracowników znajdujących się w oddziałach niemieckich. Strzałka listy rozwijalnej, znajdującej się obok etykiety kolumny, zmienia kolor wskazując, iż filtr jest aktywny w tej kolumnie.
üZ listy rozwijalnej DEPART wybierz Pokazy. Teraz wszystkie widoczne wiersze danych zawierają wartość Niemcy w kolumnie ODDZIAŁ i Pokazy w kolumnie DEPART.
Polecenie Dane/Filtr/Pokaż wszystko usuwa wszystkie filtry, które są aktualnie aktywne na twojej liście:
ü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)
üWybierz (Wszystkie). Wszystkie wyświetlone wiersze spełniają teraz tylko warunek nakładany przez drugi filtr.
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.
üZ listy rozwijalnej KORZ wybierz (Puste)
üZ listy rozwijalnej KORZ wybierz (Niepuste). Uwaga: dla potrzeb filtrowania Excel przyjmuje komórkę zawierającą tylko spacje za niepustą.
üWybierz Dane/Filtr/Pokaż wszystko, aby usunąć aktywne filtry z naszej listy.
Tej opcji można używać do filtrowania danych numerycznych. Użyjemy tego filtru aby znaleźć pracowników dostających najwyższe pensje.
üZ listy rozwijalnej PŁACA BRUTTO wybierz 10 pierwszych, aby otworzyć okno dialogowe Autofiltr 10 pierwszych.
üSprawdź czy w ramce Pokaż są ustawione następujące dane: górne, 19, elementy.
üKliknij OK.
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łnią albo pierwszy warunek albo drugi. Wykorzystajmy własne kryteria w celu znalezienia pracowników pracującychw jednym z dwóch oddziałów:
ü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.
üAby stworzyć warunek LUB musimy stworzyć z kryteriów własnych. Z listy rozwijalnej ODDZIAŁ wybierz (Inne) w celu wyświetlenia okna dialogowego Autofiltr użytkownika, które pozwala na połączenie dwóch kryteriów dla jednej kolumny. Pierwsze dwa pola wyświetlają aktualne kryterium, czyli ODDZIAŁ= Wlk. Brytania. Kliknij opcję LUB
üZ listy rozwijalnej drugiego operatora wybierz „równa się”.
üZ listy rozwijalnej drugiej wartości operatora wybierz Niemcy. Te kryteria pozwalają nam na wyświetlenie tylko tych pracowników, którzy mają w kolumnie oddział wartość Wlk Brytania lub Niemcy. Kliknij Ok.
Kryteria własne można także wykorzystywać w celu znalezienia wartości, które są mniejsze lub większe od określonej wartości. Dokonaj filtrowania listy w celu znalezienia pracowników pracujących co najmniej 35 godzin tygodniowo a następnie tych, którzy zarabiają co najmniej 19,50 ale nie więcej niż 22,50 za godzinę.
üZ listy rozwijalnej GODZ wybierz (Inne), aby wyświetlić okno dialogowe Autofiltr użytkownika.
üW polu Pokaż wiersze spełniające warunek wybierz >= jako operator a 35,50 jako wartość dla pierwszego kryterium. Kliknij OK, aby przefiltrować listę.
üZ listy rozwijalnej STAWKA GODZ wybierz (Inne).
üJako pierwsze kryterium w polu Pokaż wiersze spełniające warunek wprowadź >=19,50 zł.
üSprawdź czy została wybrana opcja I, a następnie wprowadź <=22,50 zł jako drugie kryterium. Kliknij OK.
Znaki wieloznaczne pozwalają na znalezienie informacji nawet wtedy, gdy tylko częściowo wiesz czego szukasz. Symbol ? zastępuje dowolny znak, symbol * zastępuje dowolny ciąg znaków. Znajdź wszystkich, których nazwiska zaczynają się na literę M, a potem tych, którzy uczestniczą w programie emerytalnym.
üZ listy rozwijalnej NAZWISKO wybierz (Inne)
üWybierz = jako operator a m* jako wartość do pierwszego kryterium (Kryteria nie uwzględniają wielkości liter). Kliknij OK.
üPonownie wyświetl okno dialogowe Autofiltr użytkownika do kolumny KORZ
üWprowadź =*e jako pierwsze kryterium.
ü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.
üPonownie wyświetl okno dialogowe Autofiltr użytkownika dla kolumny KORZ,a następnie zmodyfikuj kryterium na =*e*. Kliknij OK.
Teraz wykorzystajmy połączone znaki wieloznaczne i warunek LUB w celu znalezienia czy pracownicy z kodem R zostali przypisani do odpowiedniego departamentu (Rajdy dorosłych). Pole KOD zawiera kody oznaczające oddział i departament każdego pracownika. Pierwsza litera kodu 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.
üJako pierwsze kryterium wprowadź =?r*. Odnajdziemy kody pracowników literą r jako drugą. Będziemy musieli znaleźć jeszcze Brytyjczyków pracujących w tym departamencie, ponieważ u nich ta litera kodowa jest na 3 pozycji.
üKliknij LUB, wprowadź =jako drugi operator, ??r* jako drugie kryterium.
ü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 kodyz kolumny KOD zostały właściwie przypisane.
Wyszukaj wszystkie osoby pracujące w departamencie Rajdy dorosłych pracujących więcej niż 35 godzin tygodniowo i mających stawkę za godziną większą niż 20,00 zł. Zastosuj filtr zaawansowany.
üW zakresie K1:M2 wpisz kryteria sortowania
üPrzejdź do dowolnej komórki bazy danych i wydaj polecenie Dane/Filtr/Filtr zaawansowany.
üW oknie dialogowym Filtr zaawansowany wybierz opcje i wypełnij pola zgodniez rysunkiem, po czym naciśnięciem OK. włącz sortowanie.
Otwórz plik . Sortowaniem nazywamy zamianę porządku listy, pozwala ono na wyświetlenie danych w odpowiedniej kolejności, spełniającej zadane kryteria. Posortujmy pracowników względem NAZWISKA. Po wybraniu Dane /Sortuj pojawia się okno dialogowe. Z listy rozwijalnej Sortuj według wybierz NAZWISKO (nie zaznaczaj kolumn). Aby szybko posortować listę przesuwamy wskaźnik komórki do kolumny względem której chcemy sortować i klikamy jeden z przycisków Sortuj rosnąco, Sortuj malejąco. Posortuj listę względem kolumny IMIĘ. Można tez sortować względem większej ilości kolumn. Sortowanie takie przydaje się, gdy pozycje sortowanego pola powtarzają się np. jest wielu pracowników o tym samych nazwiskach. Aby posortować listę według drugiej czy trzeciej kolumny w okienku dialogowym Sortuj uzupełniamy pola następnie według. Posortuj listę względem imienia i nazwiska. Aby przywrócić pierwotną kolejność listy sortujemy względem pola Lp. Teraz posortuj pracowników od najmniej zarabiających do najwięcej (przy tych samych kwotach uwzględnij kolejność nazwisk) .
Do użycia tej funkcji wymagana jest lista posortowana. Sumy pośrednie wstawiane są bowiem w tych miejscach w których zmienia się określona wartość.
Posortuj listę według pola ODDZIAŁ. Wybierz polecenie Dane /Sumy pośrednie. Zliczmy teraz pracowników poszczególnych oddziałów używając funkcji Licznik.
...
Piju