SOISK - SYSTEMY OPERACYJNE I SIECI KOMPUTEROWE
Tomasz Puchała

EXCEL PORADY

Porada Excel  - praktycznie!   Zespół Szkół Zawodowych im. St. Staszica Pińczów
ADRESOWANIE KOMÓREK
    Adresowanie komórek wygląda w sposób następujący:
np.  AB4500  najpierw podajemy numer kolumny, potem numer wiersza komórki, wpisujemy oba elementy razem

Piszemy np. tak  Arkusz4.Z9000  (w MsExcel  Arkusz4!Z9000 ), gdy pytamy się o komórkę spoza aktualnego skoroszytu, w tym przypadku o komórkę Z900 ze skoroszytu o nazwie Arkusz4.

Adres bezwzględny i względny

Gdy chcemy zabezpieczyć wzór przed zmianami przy kopiowaniu to przed tym elementem nie zmieniającym się wstawiamy znak  $ . W przykładzie powyżej stawka odsetek jest umieszczona w komórce D3. Jeżeli we wzorze wyliczającym odsetki nie zabezpieczymy wiersza nr 3, to już w następnym wierszu (po przekopiowaniu) wzór będzie wskazywał komórkę D4 czyli pustą! Możemy napisać również $D$3, ale nie jest to potrzebnie, bo wzór kopiujemy tylko w dół, więc stojąca na początku litera D i tak się nie zmieni.
Zmieniając wzór w komórce C6 możemy dodać jeszcze jeden znak dolara przed literą B w ten sposób: =$B6*D$3, ale jak wyżej i tutaj nie jest to konieczne.

Tabliczka mnożenia jednym wzorem!

Pierwszy wiersz liczb 1,2,3... i pierwszą kolumnę wpisujemy bez wyliczania. Trzeba teraz wpisać taki wzór, żeby po przekopiowaniu wypełnił całą tabelkę liczbami, jak w tabliczce mnożenia. We wzorze =$B4*C$3 wstawione są dwa znaki $, zadanie pierwszego to "nie zmieniaj kolumny B", a drugiego "nie zmieniaj wiersza 3" podczas kopiowania wzoru.

 
FORMUŁY W ARKUSZU
tworzenie formuł   Formuły, czyli wzory, rozpoczynamy zawsze od znaku równości.

Przykład 1.
 =2*(3-5) 

 =pierwiastek(A234)  - funkcja

 =SUMA(D4:D56;F3:F9)  - funkcja

Nazwy funkcji piszemy wielką lub małą literą, wszystko jedno. Arkusz zamieni wszystkie małe litery w nazwie funkcji na wielkie.
 
operatory   Operatory arytmetyczne (znaki działań):

 *  znak mnożenia  /  znak dzielenia,

 +  znak dodawania,  -  znak odejmowania,

 ^  znak potęgowania i  ()  nawiasy (tylko okrągłe!)

np. wzór w zapisie matematycznym 3(42-7) wygląda w arkuszu następująco:  =3*(4^2-7) 
PRZYKŁADY FORMUŁ
 
ZAOKR    =ZAOKR(liczba;liczbacyfr)  Zaokrągla liczbę do określonej liczby cyfr.

 liczba  jest liczbą albo wyrażeniem do zaokrąglenia.
 liczba_cyfr  określa liczbę cyfr, do których ma być zaokrąglona liczba.

Jeżeli  liczba_cyfr  jest większa od zera, to wyliczona wartość wyrażenia jest zaokrąglona do wybranej liczby cyfr po przecinku.
Jeżeli  liczba_cyfr  jest mniejsza od zera, to wyliczona wartość wyrażenia jest zaokrąglona do wybranej liczby cyfr przed przecinkiem.

Przykład 2.
 =ZAOKR(3,141592;3)  jest równe 3,142

 =ZAOKR(3,141592;2)  jest równe 3,14

 =ZAOKR(3,141592;0)  jest równe 3

 =ZAOKR(3,141592;-1)  jest równe 0


 
ILE.LICZB    =ILE.LICZB(obszar)  Sprawdza w ilu komórkach z obszaru są wpisane liczby i oddaje nam liczbę tych komórek.

Przykład 3.
 =ILE.LICZB(D5:G300)  sprawdza w ilu komórkach z bloku D5:G300 są wpisane liczby i oddaje nam liczbę tych komórek.
SUMA    =SUMA(obszar)  oblicza sumę wartości komórek z obszaru.

Przykład 4.
 =SUMA(A5:A10)  oblicza sumę wartości komórek A5 do A10.
 
ŚREDNIA    =ŚREDNIA(obszar)  oblicza średnią wartości komórek z obszaru.

Przykład 5.
 =ŚREDNIA(C5:K10)  wylicza średnią z komórek od C5 do K10.
 
ILOCZYN    =ILOCZYN(obszar)  oblicza iloczyn wartości komórek z obszaru. Inaczej mówiąc, mnoży wszystkie komórki z podanego obszaru.

Przykład 5.
 =ILOCZYN(B12:D30)  wylicza iloczyn(wynik mnożenia) z komórek od B12 do D30.
 
LICZ.JEŻELI    =LICZ.JEŻELI(obszar;warunek)  zlicza komórki z obszaru spełniające warunek.

Przykład 6.
 =LICZ.JEŻELI(A5:A10;">=0")  podaje liczbę komórek z bloku A5:A10 o wartościach dodatnich lub równych zero.
 
SUMA.JEŻELI    =SUMA.JEŻELI(obszar1;warunek;obszar2)  sprawdza komórki z obszaru1 spełniające warunek i w tym samym wierszu (kolumnie) sumuje komórkę z obszaru2. Obszar1 i obszar2 powinny wskazywać wiersze (albo kolumny) o tej samej liczbie komórek.

Przykład 7.
 =SUMA.JEŻELI(B6:B8;"Kowalski";C6:C8)  sprawdza w kolumnie nazwisk, wyszukuje nazwisko Kowalski i sumuje zaliczki wzięte przez pana Kowalskiego.



 =SUMA.JEŻELI(obszar1;warunek)  sprawdza komórki z obszaru1 czy spełniają warunek i sumuje wartości komórek spełniających warunek.

Przykład 8.
 =SUMA.JEŻELI(B6:B8;">230")  oblicza sumę zaliczek z komórek B6:B8 większych od 230.

 
WYSZUKAJ.POZIOMO    =WYSZUKAJ.POZIOMO(kryterium;macierz;indeks;posortowane) 

wyszukuje w  macierzy  w wierszu  indeks  elementu  kryterium  i zwraca znaleziony element. Argument  posortowane  określa czy macierz zawiera elementy posortowane (przyjmuje wartość 0-nie i 1-tak).

Przykład 9.
Chcemy wpisywać cenę towaru w walucie obcej, następnie nazwę waluty, a arkusz powinien przeliczać cenę na złote według tabeli powyżej.

W komórce D8 znajduje się formuła  =WYSZUKAJ.POZIOMO(C8;B4:D5;2;0)*B8  Wzór znajdzie w tabeli B4:D5 w drugim wierszu wartość waluty DM (marki niemieckiej) i wymnoży tę wartość przez cenę w markach, obliczając w ten sposób wartość towaru w złotych.



Po zabezpieczeniu wzoru i zaokrągleniu otrzymamy formułę:

 =ZAOKR(WYSZUKAJ.POZIOMO(C8;$B$4:$D$5;2;0)*B8;2) ,

która reaguje na zmianę symbolu waluty i daje się poprawnie kopiować.

 
WYSZUKAJ.PIONOWO    =WYSZUKAJ.PIONOWO(kryterium;macierz;indeks;posortowane) 

wyszukuje w  macierzy  w kolumnie  indeks  elementu  kryterium  i zwraca znaleziony element. Argument  posortowane  określa czy macierz zawiera elementy posortowane (przyjmuje wartość 0-nie i 1-tak).

Przykład 10.
Uczniowie mają do zrobienia trzy zadania. Obliczane są maksymalna i minimalna liczby punktów dla każdej osoby z każdego zadania oraz suma punktów za wszystkie zadania. Następnie arkusz na podstawie kryteriów wystawia ocenę.

W komórce G7 znajduje się formuła  =WYSZUKAJ.PIONOWO(F7;D$16:E$19;2;1) 
Arkusz bierze z komórki F7 sumę punktów, następnie znajduje liczbę równą albo mniejszą w pierwszej kolumnie tabeli kryteriów D16:E18 i z drugiej kolumny (z tego samego wiersza) wyświetla odpowiednią ocenę.




Parametry funkcji oznaczają:
  • F7 - stąd arkusz bierze sumę punktów do sprawdzenia
  • D$16:E$19 - tabela z kryteriami
  • 2 - z tej kolumny bierze napis
  • 1 - włączone przybliżone szukanie

    Dla porządku podam pozostałe wzory:
    C12: =MIN(C7:C10)
    C13: =MAX(C7:C10)
    C14: =ŚREDNIA(C7:C10)
    C7: =SUMA(C7:E7)



Szybkie zaznaczanie obszernych zakresów komórek

Sposób 1.
1. Wciśnij klawisz funkcyjny F5. Pojawi się okno dialogowe Przechodzenie do.
2. W polu Odwołanie wpisz zakres, który ma zostać podświetlony i zatwierdź, klikając przycisk OK.

Sposób 2.
1. W Polu nazwy wpisz adres obszaru i wciśnij klawisz Enter. Zwróć uwagę, aby wpisać adresy komórek rozdzielone dwukropkiem. Jeśli go przeoczysz, Excel zdefiniuje nazwę dla komórki, która aktualnie był zaznaczona.