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