Serwis Edukacyjny
w I-LO w Tarnowie
obrazek

Materiały dla uczniów liceum

  Wyjście       Spis treści       Wstecz       Dalej  

obrazek

Autor artykułu: mgr Jerzy Wałaszek

©2020 mgr Jerzy Wałaszek
I LO w Tarnowie

obrazek

Materiały dla klasy I

Zaawansowane funkcje w arkuszu

SPIS TREŚCI

Funkcje zakresów

Przez zakres w arkuszu rozumiemy prostokąt obejmujący grupę komórek.

Ten zakres obejmuje komórki leżące w kolumnach B, C, D oraz wierszach 2 ... 7. W Excelu obszar taki zaznaczamy podając adresy dwóch narożnych komórek: lewej górnej (B2) oraz prawej dolnej (D7). Adresy te rozdzielamy dwukropkiem – B2:D7.

Zakres może objąć tylko komórki leżące w jednej komórce:

Tutaj obowiązuje identyczna reguła: zakres definiujemy podając adresy górnej i dolnej komórki, rozdzielone dwukropkiem – C3:C8.

Zakres może obejmować tylko komórki jednego wiersza:

Oznaczamy go wtedy przez podanie lewej i prawej komórki rozdzielonych dwukropkiem – B3:G8.

W arkuszu istnieją funkcje, które operują na zakresach. Przedstawimy kilka z nich w dalszej części lekcji.

Na początek:  podrozdziału   strony 

Ćwiczenie nr 1

Wykonaj samodzielnie poniższe ćwiczenie wg opisu.

Wypełnij komórki zakresu B1:D6 dowolnymi liczbami:

Zaznacz myszką obszar z liczbami i w sekcji ustaw 2 cyfry po przecinku:

W komórkach kolumny A umieść napisy i dosuń je do prawego marginesu:

Kliknij lewym przyciskiem myszki w komórkę B8 (obok napisu suma =) i wpisz początek formuły:

Teraz przejedź kursorem myszki z wciśniętym lewym przyciskiem po komórkach od B1 do B6:

 Zaznaczasz w ten sposób zakres komórek. Zwróć uwagę, że Excel wpisał ten zakres do argumentu funkcji w formule:

=suma(B1:B6

Zamknij nawias w formule i naciśnij klawisz Enter. Excel policzy sumę zawartości komórek objętych zakresem i wyświetli wynik:

Teraz skopiujemy formułę w komórce B8 na komórki C8 i D8. W tym celu kliknij myszką w komórkę B8:

Chwyć lewym przyciskiem myszki uchwyt kopiowania (mały kwadracik w prawym dolnym rogu kursora) i przeciągnij go na komórki C8 i D8. Zwolnij lewy przycisk myszki. Formuła z komórki B8 zostanie skopiowana do komórek C8 i D8:

Otrzymaliśmy sumy liczb w kolumnach C i D. Dlaczego? Kliknij lewym przyciskiem myszki w komórkę C8, aby ustawić na niej kursor. Teraz popatrz nad arkusz. Znajduje się tam długi pasek, w którym Excel pokazuje zawartość komórki nie jako liczbę, lecz jako formułę:

Zwróć uwagę, iż zakres zmienił się z B1:B6 na C1:C6 - czyli został przesunięty w prawo o jedną kolumnę. Zakresy, podobnie jak komórki mogą być względne lub bezwzględne. Jeśli zakres jest względny, to podąża zawsze za kopiowaną formułą - skopiowaliśmy formułę z komórki B8 do C6, zatem formuła przemieściła się o jedną kolumnę w prawo. Tak samo przemieścił się zakres zawarty w formule:

B8 → C8
=SUMA(B1:B6) → =SUMA(C1:C6)

Dlatego w komórce C8 otrzymaliśmy sumę komórek z kolumny C. To samo z formułą w komórce D8. Formuła zmieniła się na:

=SUMA(D1:D6)

Postaraj się to zrozumieć.

Teraz policzymy średnie arytmetyczne. Średnia arytmetyczna n liczb jest ich sumą podzieloną przez n. Kliknij w komórkę B9 i rozpocznij formułę:

Teraz podobnie jak poprzednio przeciągnij lewym przyciskiem myszki po komórkach od B1 do B6. W formule pojawi się zakres:

Zamknij nawias i naciśnij klawisz Enter. Arkusz policzy średnią arytmetyczną komórek od B1 do B6 i wyświetli wynik w komórce B9:

Skopiuj komórkę B9 na komórki C9 i D9. Otrzymasz średnie arytmetyczne liczb w kolumnach C i D:

Teraz policzymy wartość maksymalną w zakresie B1:B6. W komórce B10 rozpocznij formułę: = max( i zaznacz zakres B1:B6 lewym przyciskiem myszki:

Zamknij formułę nawiasem ) i naciśnij klawisz Enter. Excel wyświetli największą liczbę w zakresie B1:B6:

Skopiuj formułę z komórki B10 na komórki C10 i D10:

Teraz policzymy wartość minimalną w zakresie B1:B6. W komórce B11 rozpocznij formułę: = min( i zaznacz lewym przyciskiem myszki komórki B1 ... B6:

Zakończ formułę nawiasem ), naciśnij klawisz Enter, po czym skopiuj komórkę B11 na komórki C11 i D11:

Ćwiczenie nr 1 jest zakończone. Podsumujmy poznane funkcje (zapisz poniższe w zeszycie. Temat lekcji "Zaawansowane funkcje w arkuszu"):

SUMA(zakres)

Oblicza sumę liczb w zakresie. Zakres można wpisywać ręcznie lub zaznaczać myszką bezpośrednio na arkuszu.

ŚREDNIA(zakres)

Oblicza średnią arytmetyczną liczb w zakresie.

MAX(zakres)

Wyszukuje największą wartość liczb w zakresie.

Uwaga: W LibreOffice.Calc funkcja ta ma nazwę MAKS(zakres)

MIN(zakres)

Wyszukuje najmniejszą wartość liczb w zakresie.

Na początek:  podrozdziału   strony 

Ćwiczenie nr 2

W tym ćwiczeniu zapoznamy się z nowymi funkcjami arkusza. Przygotuj nowy arkusz.

Zaznacz lewym przyciskiem myszki poniższą tabelkę i naciśnij klawisze Ctrl+C, aby skopiować zaznaczenie do schowka:

Lp. Uczeń j.pol. j.ang. chem. biol. fiz. geo. hist. inf. mat. w-f Średnia  6   1  Prymus Leser
1 Bochenek Julia 4 5 6 4 4 4 5 3 4 5          
2 Cicha Wiktoria 5 5 5 4 4 4 5 3 4 5          
3 Leń Jacek 2 1 2 2 1 2 2 1 2 3          
4 Leser Agata 1 1 2 1 1 2 2 1 1 3          
5 Pracuś Wiktor 6 6 6 5 5 5 6 5 5 6          
6 Spóźnialski Karol 3 2 3 3 2 3 4 4 3 4          
7 Śnięty Andrzej 3 2 2 2 2 3 3 4 3 3          
8 Śpioch Dorota 3 2 2 2 1 3 3 4 3 3          
9 Zygułą Beata 4 5 4 4 3 4 4 5 4 5          
10 Żądło Patryk 5 5 5 4 4 6 4 5 4 5          
  KLASA                              

Przejdź do arkusza, kliknij w komórkę A1, aby ją wybrać i wklej zawartość schowka naciskając klawisze Ctrl+V. Dopasuj szerokości kolumn i wysokości wierszy do zawartego w nich tekstu (klikamy dwukrotnie lewym przyciskiem myszki pomiędzy oznaczeniami kolumn/wierszy):

Skopiowane dane to oceny pewnej grupy uczniów. Naszym zadaniem będzie przetworzenie tych danych za pomocą funkcji arkusza. Wykonaj uważnie ćwiczenie, ponieważ otrzymasz podobne zadanie na ocenę.

Zaczniemy od wyliczenia średnich ocen uczniów z poszczególnych przedmiotów. Znamy już odpowiednią funkcję z ćwiczenia nr 1. Umieść kursor w komórce M2 i wpisz formułę (zakres ocen ucznia dla średniej zaznacz myszką):

Następnie skopiuj zawartość komórki M2 na komórki M3 ... M11:

Teraz policzymy średnie ocen z poszczególnych przedmiotów dla całej klasy. Zmień napis KLASA w komórce B12 na Średnie klasy i dosuń go do prawej krawędzi komórki:

Wybierz komórkę C12 i wpisz do niej formułę:

=ŚREDNIA(C2:C11)

Zakres komórek C2:C11 zaznacz myszką.

Następnie skopiuj zawartość komórki C12 w prawo na komórki D12 ... M12:

Otrzymasz średnie ocen z poszczególnych przedmiotów, a w komórce M12 jest średnia średnich ocen uczniów, czyli średnia klasy z wszystkich ocen.

Teraz zajmiemy się liczeniem ocen 6 i 1. Do tego celu wykorzystamy funkcję:

LICZ.JEŻELI(zakres,kryteria)

Funkcja posiada dwa argumenty. Pierwszy określa zakres komórek, które będą przeglądane przez funkcję. Drugi argument określa kryterium zawartości komórki. Jeśli w podanym zakresie funkcja znajdzie komórkę spełniającą kryterium, to funkcja zliczy tę komórkę. Gdy funkcja przeglądnie wszystkie komórki w zakresie, to wynikiem będzie liczba komórek spełniająca kryterium. My chcemy policzyć, ile ocen 6 ma dany uczeń. W komórce N2 wpisz formułę:

=LICZ.JEŻELI(C2:L2;6)

Zakres C2:L2 zaznacz na arkuszu myszką. Po zaznaczeniu zakresu wpisz średnik ; i 6. Zamknij funkcję nawiasem i wciśnij Enter. Arkusz policzy ilość ocen 6, które ma uczennica Bochenek Julia. Skopiuj komórkę N2 w dół na komórki N3 ... N11:

W podobny sposób policzymy ilość ocen niedostatecznych. W komórce O2 wpisz formułę:

=LICZ,JEŻELI(C2:L2;1)

Zakres zaznacz myszką na arkuszu. Formuła policzy ilość komórek w zakresie, które zawierają liczbę 1. Skopiuj zawartość komórki O2 na komórki O3 ... O11.

Jedynki są istotnymi ocenami, dlatego wyróżnimy je w ocenach uczniów. Zaznacz myszką obszar ocen uczniowskich:

W sekcji style kliknij ikonę formatowanie warunkowe i z menu wybierz opcje:

Pojawi się okno dialogowe z dwoma polami:

W pierwszym polu wpisz 1, drugie pole pozwala wybrać sposób wyróżniania komórek zawierających wartość z pola pierwszego, czyli 1. Zatwierdź okienko przyciskiem OK. W zaznaczonym obszarze komórki z oceną 1 zostaną wyróżnione kolorem czerwonym:

W podobny sposób możesz wyróżniać inne oceny, lecz zmień kolory, aby dało się je łatwo odróżnić.  Pokoloruj oceny 6 na zielono (wybierz sposób wyróżnienia w drugim polu okienka dialogowego):

Pozostało nam wskazanie prymusa i lesera. Prymus jest uczniem, który ma najwyższą średnią ocen, a leser ma najniższą średnią. Zastosujemy tutaj specjalną funkcję:

JEŻELI(test;wartość dla prawdy;wartość dla fałszu)

Funkcja działa następująco. Najpierw funkcja wykonuje test. Jeśli test jest prawdziwy, to zwraca wartość dla prawdy. Jeśli test jest fałszywy, to zwraca wartość dla fałszu.

Wpisz w komórce A15 liczbę 5. W komórce B15 wpisz 5. W komórce C15 wpisz formułę:

=JEŻELI(A15=B15;"równe";"różne")

Wciśnij Enter. W komórce C15 pojawi się napis równe, ponieważ test dał wynik prawda, 5 jest równe 5:

Zmień zawartość komórki A15 lub B15, tak, aby te dwie liczby były różne. Teraz pojawi się napis Różne, ponieważ test dał wynik fałszywy:

Wykorzystamy teraz tę funkcję do wyszukania prymusa wśród naszych uczniów. Średnia ocen prymusa jest najwyższa, Do komórki P2 wpisz:

=JEŻELI(M2=MAX(

Zaznacz myszką komórki od M2 do M11, a następnie naciśnij klawisz F4. Powstanie w ten sposób zakres bezwzględny:

=JEŻELI(M2=MAX($M$2:$M$11

Zakres bezwzględny nie będzie podążał za formułą przy kopiowaniu - przecież nie chcemy tej formuły wpisywać ręcznie dla każdego ucznia! Zamknij nawias funkcji MAX() i dopisz:

=JEŻELI(M2=MAX($M$2:$M$11);"P";"")

Pusty tekst to dwa cudzysłowy. Formuła działa w ten sposób, iż jeśli średnia ucznia jest równa średniej maksymalnej, to w komórce z tą formułą pojawi się literka P (prymus), a jeśli nie, to komórka będzie pusta. Wciśnij Enter i skopiuj komórkę na komórki P3 ... P11. Arkusz wyszuka nam prymusa:

W podobny sposób znajdziemy lesera. Leser ma najniższą średnią ocen w klasie. Do komórki Q2 wpisz formułę:

=JEŻELI(M2=MIN($M$2:$M$11);"L";"")

Pamiętaj, po zaznaczeniu zakresu należy nacisnąć klawisz F4, aby zakres był adresowany bezwzględnie, inaczej będzie się on przemieszczał przy kopiowaniu formuły.

Skopiuj komórkę Q2 na komórki Q3 ... Q11. Arkusz wyszuka lesera:

Kolumny P i Q wyśrodkuj.

Podsumujmy funkcje:

LICZ.JEŻELI(zakres;kryterium)

W podanym zakresie zlicza komórki spełniające kryterium i podaje ich liczbę.

JEŻELI(test;wartość dla prawdy;wartość dla fałszu)

Wykonuje test. Jeśli test jest prawdziwy, to zwraca wartość dla prawdy. Jeśli test jest fałszywy, zwraca wartość dla fałszu

 

Na początek:  podrozdziału   strony 

Zadanie

To już ostatnie zadanie z arkusza Excel. Oto instrukcja wykonania:
  1. Utwórz arkusz wg ćwiczenia nr 2.
  2. Arkusz prześlij w załączniku listu na adres:

    i-lo@eduinf.waw.pl
     
  3. W temacie listu wpisz swoje imię, nazwisko, klasę i słowo OCENY (konieczne, bez tego listów nie przyjmuję, gdyż utrudnia to ich sortowanie i robi się bałagan).
  4. Czas do 17.04.2020 (piątek).

Zadania będą pojawiały się systematycznie w serwisie do końca kwarantanny, proszę tutaj zaglądać.

W odpowiedzi na pytania uczniów informuję, że dwa pierwsze ćwiczenia miały na celu sprawdzenie waszych umiejętności w sytuacji kryzysowej, jaką jest kwarantanna - zadania polegały na wysłaniu prostych wiadomości e-mail z odpowiednią treścią zgodnie z instrukcją i w zadanym terminie. Wysyłanie poczty e-mail jest podstawową umiejętnością i każdy z was MUSI TO UMIEĆ ZROBIĆ.

Błędy popełnione w arkuszu z płacami wynikały głównie z tego, iż nie przeczytaliście tekstu lekcji i nie wykonaliście opisanych tam ćwiczeń lub wykonaliście je bezmyślnie. Stawka VAT (lub PIT) jest daną i jako taka musi być umieszczona w osobnej komórce. Formuły liczące podatek powinny się odwoływać do tej komórki za pomocą adresowania bezwzględnego - część z was bezmyślnie i ręcznie poprawiała adresy, a w lekcji było przecież ćwiczenie z adresowaniem bezwzględnym - czy zastanowiliście się po co? Arkusze takie były oceniane na dst i db.

W drugim ćwiczeniu z wykresem wielu z was nie zrozumiało punktu 6. Pisze tam, że należy podać wartości x-sów dla miejsca zerowego, max i min funkcji w podanych przedziałach argumentów. Zadanie polegało na wskazaniu myszką odpowiedniego punktu na linii wykresu i odczytanie z okienka współrzędnej x - bardzo skomplikowane, jak widać.

Proszę:

  1. Czytać materiały podawane w tekście lekcji.
  2. Wykonywać opisane w tekście lekcji ćwiczenia - dokładnie to samo robilibyśmy w pracowni.
  3. Zadania wykonywać zgodnie z instrukcją.

Jeśli ktoś ma braki ze szkoły podstawowej lub gimnazjum, to musi je nadrobić - skargi na nauczyciela w niczym wam nie pomogą, macie to umieć i koniec dyskusji. W sieci jest mnóstwo poradników odnośnie wysyłania listów e-mail z załącznikami. Niektórym proponuję również dowiedzieć się, czym jest plik, katalog, gdzie są zapisywane pliki pobierane z sieci, jak sprawdzić, czy list został wysłany, itp.

Kłopoty w dostępie do sprzętu i do Internetu proszę zgłaszać wychowawcy. Ja nie blokuję wam żadnych kont pocztowych - nie wiem, kto z was na to wpadł - gdybym miał taką moc, nie pracowałbym w szkole. Administrator serwera poczty może wyłączać przyjmowanie listów w przypadku przeciążenia sieci. Ja nie jestem administratorem serwera, tylko administratorem serwisu - wykupuję usługę w firmie ogicom.pl, ale nie zarządzam ich komputerami. Proszę zadawać pytania na adres (w temacie listu imię, nazwisko, klasa i słowo PYTANIE):

i-lo@eduinf.waw.pl

E-dziennik jest często przeciążony i nie można z nim normalnie pracować.

Na początek:  podrozdziału   strony 

Zespół Przedmiotowy
Chemii-Fizyki-Informatyki

w I Liceum Ogólnokształcącym
im. Kazimierza Brodzińskiego
w Tarnowie
ul. Piłsudskiego 4
©2020 mgr Jerzy Wałaszek

Materiały tylko do użytku dydaktycznego. Ich kopiowanie i powielanie jest dozwolone
pod warunkiem podania źródła oraz niepobierania za to pieniędzy.

Pytania proszę przesyłać na adres email: i-lo@eduinf.waw.pl

Serwis wykorzystuje pliki cookies. Jeśli nie chcesz ich otrzymywać, zablokuj je w swojej przeglądarce.
Informacje dodatkowe.