Ćw.6 MS EXCEL 2003 (3)
Sortowanie i filtracja danych, funkcje logiczne
Arkusz jako baza danych
1. Otworzyć skoroszyt Dane adresowe (materiały na stronie dydaktycznej).
2. Wykorzystując menu Narzędzia opcja Sortuj wykonać sortowanie według nazwisk rosnąco. Wypróbować inne
sposoby sortowania  dodając np. nowy poziom w okienku dialogowym i ustalając dodatkową kolumnę dla
sortowania.
3. Wybrać z menu Dane opcję Filt/Autofiltr. Zaobserwować pojawienie się w nagłówkach kolumn bazy adresowej
strzałek dla rozwijania okienek wyboru algorytmu filtracji. Rozwinąć okienko nagłówka Miasto i wybrać Kra-
ków. Powrócić do pełnej tabeli wybierając w okienku Miasto pozycję  wszystkie .
4. Rozwinąć okienko nagłówka Pensja  dla opcji Filtr liczb wykonać filtrację tylko tych osób, których pensja jest
w przedziale 1000 do 1100 zł (wykorzystać filtr niestandardowy z odpowiednią opcją  alternatywa "LUB" czy
koniunkcja "I"?)
Narzędzie Szukaj wyniku
Narzędzie służy do wyszukiwania wartości zmiennej spełniającej docelowo określany wynik formuły.
W komórce B4 wpisujemy dowolną liczbę. Dla funkcji zdefiniowanej jako:
f(x) = x4  4x3 + 4x  5
w komórce B5 tworzymy formułę obliczającą wartość tej funkcji względem komórki B4.
Chcemy znalez
ć wartość x, która da wartość funkcji równą 0 (jeden z pierwiastków równania):
" uruchamiamy narzędzie Szukaj wyniku (menu Narzędzia)
" wypełniamy dane jak na rysunku..
W przypadku gdy nie znaleziono rozwiązania wstawić inną wstępną wartość x.
Zadanie
Zbadać narzędziem Szukaj wyniku czy istnieje wartość x, dla której wyrażenie:
ln 3 - 1
+ 2
będzie miało wartość 2,53.
Funkcje logiczne
Sumowanie w wybranym zakresie tylko liczb przekraczających wartość progową.
Wpisać w nowym arkuszu kilka liczb w kolumnie A w formacie walutowym. Następnie w innej komórce sporzą-
dzić formułę:
=SUMA . JEŻELI( zakres_badany; ">10")
Zakres to nazwa zakresu komórek lub dwa adresy komórek skrajnych zakresu oddzielone dwukropkiem.
Obliczenie ile kwot przekracza 10 zł. Dla tego samego zakresu kwot wpisać formułę:
=LICZ . JEŻELI( zakres_badany; ">10")
Badanie pojedynczej wartości
1. Nazwać komórkę A1 jako x (w polu nazwy nad kolumną A).
2. W kolejnej komórce napisać formułę:
=JEŻELI(x>0; "większe od zera"; "mniejsze lub równe zero")
3. Wstawiać do komórki A1 wartości ujemne i dodatnie kolejno je zatwierdzając, obserwując wynik formuły
JEŻELI. Zwrócić uwagę na zapis stałych tekstowych jako argumentów formuły (w cudzysłowie).
4. Zanalizować postać formuły:
=JEŻELI(x>0 ; "większe od zera" ; JEŻELI(x=0 ; "równe zero" ; "mniejsze od zera"))
Wypróbować formułę.
Sprawdzenie czy zadana kwota mieści się w żądanym przedziale.
1. W dwóch komórkach ustalamy granice przedziału: nazywamy komórki dolna i gorna.
2. Wpisujemy poniżej w kolumnie kilka kwot
3. Obok pierwszej kwoty Wpisujemy formułę:
=JEŻELI(ORAZ(kwota>dolna; kwota4. Kopiujemy formułę do komórek poniżej  dla pozostałych kwot.
Przykład zadania decyzyjnego ustrukturyzowanego
Naszym zadaniem jest wyznaczenie odsetek karnych dla kilku kontrahentów, kwot i dat transakcji przy założeniu:
" odsetki płacone są tylko wtedy, gdy od daty transakcji minęło do dziś więcej niż 2 tygodnie,
" wysokość procentowa odsetek karnych wynosi 20% w skali roku.
Wykonujemy następujące czynności:
" Umieszczamy w arkuszu datę dzisiejszą formułą: =DZIŚ( )  nazwać komórkę data_dz
" Obliczamy odsetki za 1 dzień: =20%/365 ; nazwać komórkę stopa_dz
" Dla kilku kontrahentów wpisujemy w arkuszu dowolne daty z niedalekiej przeszłości, obok dowolne
kwoty transakcji,
" Wykorzystujemy funkcję JEŻELI do warunkowego obliczenia odsetek w postaci:
=JEŻELI(data_dz-data <= 14; 0 ; ( data_dz-data-14)*stopa_dz*kwota)
UWAGA: jeżeli nie nadano nazw komórkom stopa_dz i data_dz należy użyć ich adresu bezwzględnego, np.
$G$12.
Zadanie
1. Wykorzystać funkcje MAX i MIN do znalezienia największej i najmniejszej liczby w wypełnionym licz-
bami zakresie komórek.
2. Umieścić w arkuszu klika formuł dających wynik logiczny, np.:
=5>4 =G5=8 =A3<>4 (różne od)
Następnie utworzyć formuły z użyciem funkcji NIE, LUB, ORAZ wykorzystujące jako argumenty wyniki
poprzednich formuł. Zanalizować wyniki.
3. Dla kilku osób o imionach podanych w kolumnie A wpisać ich wiek w latach w kolumnie B  dowolne
liczby całkowite w przedziale (0 ; 100).
" W kolumnie C wykonać odpowiednią klasyfikację osób do przedziału wiekowego, zakładając:
wiek<= 50  młody
wiek>50  stary.
" W kolumnie D wykonać klasyfikację osób do jednego z czterech przedziałów:
wiek<= 18  dziecko
18>wiek>30  młodzież
30>wiek>50  średni
wiek>50  stary.
" Obliczyć ile osób jest dzieckiem (wykorzystać funkcję LICZ.JEŻELI).
" Obliczyć średnią wieku dzieci (wykorzystać funkcję SUMA.JEŻELI oraz uprzednio obliczoną liczbę
dzieci).