Ćw.6 MS EXCEL 2007 (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 Dane opcja Sortuj wykonać sortowanie według nazwisk rosnąco. Wypróbować inne spo-

soby sortowania – dodając np. nowy poziom w okienku dialogowym i ustalając dodatkową kolumnę dla sor-

towania.

3.

Wybrać z menu Dane opcję Filtruj. Zaobserwować pojawienie się w nagłówkach kolumn bazy adresowej strza-

łek dla rozwijania okienek wyboru algorytmu filtracji. Rozwinąć okienko nagłówka Miasto i wybrać Krakó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) = x

4

– 4x

3

+ 4x – 5

w komórce B5 tworzymy formułę obliczającą wartość tej funkcji względem komórki B4.

Chcemy znaleźć wartość x, która da wartość funkcji równą 0 (jeden z pierwiastków równania):

•

uruchamiamy narzędzie Szukaj wyniku (menu Dane/Narzędzia danych/Analiza symulacji),

•

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

1.

ZADANIE:

Sprawdzić czy zadana kwota mieści się w żądanym przedziale.

a.

W dwóch komórkach ustalamy granice przedziału: nazywamy komórki dolna i gorna.

b.

Wpisujemy poniżej w kolumnie kilka kwot

c.

Obok pierwszej kwoty Wpisujemy formułę:

=

JEŻELI(ORAZ(kwota>dolna; kwota<gorna) ; "tak"; "nie")

d.

Kopiujemy formułę do komórek poniżej – dla pozostałych kwot

2.

ZADANIE:

Zsumować tylko kwoty zakresu przekraczające 10 zł.

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; ">10")

Zakres to nazwa zakresu komórek lub dwa adresy komórek skrajnych zakresu oddzielone dwukropkiem.

3.

ZADANIE

: Obliczyć ile kwot przekracza 10 zł. Dla tego samego zakresu kwot wpisać formułę:

=LICZ . JEŻELI( zakres; ">10")

4.

ZADANIE

: Wykorzystać funkcje MAX i MIN do znalezienia największej i najmniejszej liczby w wypełnionym

liczbami zakresie komórek.

5.

ZADANIE

: 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.

6.

ZADANIE:

Sporządzić formuły obliczeniowe, których zadaniem jest wyznaczenie odsetek karnych dla kilku

kontrahentów przy założeniu:

a.

odsetki płacone są tylko wtedy, gdy od daty transakcji minęło do dziś więcej niż 2 tygodnie,

b.

wysokość procentowa odsetek karnych wynosi 20% w skali roku.

c.

Wykonujemy następujące czynności:

Umieszczamy w arkuszu datę dzisiejszą: =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 do-

wolne 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

7.

ZADANIE: W kolejnej komórce napisać formułę:

=

JEŻELI(x>0; "większe od zera"; "mniejsze lub równe zero")

Wstawiać do komórki A1 (nazwanej uprzednio jako x) wartości ujemne i dodatnie kolejno je zatwierdzając, obserwu-

jąc wynik formuły JEŻELI. Zwrócić uwagę na zapis stałych tekstowych jako argumentów formuły (w cudzysłowie). Zana-

lizować postać formuły:

=JEŻELI(x>0 ; "większe od zera" ; JEŻELI(x=0 ; "równe zero" ; "mniejsze od zera"))

Wypróbować formułę.

Zadania

1.

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).

2.

W kolumnie C wykonać odpowiednią klasyfikację osób do przedziału wiekowego, zakładając:

wiek<= 50 – młody
wiek>50 – stary.

3.

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.

4.

Obliczyć ile osób jest dzieckiem (wykorzystać funkcję LICZ.JEŻELI).

5.

Obliczyć średnią wieku dzieci (wykorzystać funkcję SUMA.JEŻELI oraz uprzednio obliczoną liczbę

dzieci).