Microsoft Excel 2007

Cykl szkoleniowy realizowany jest w ramach projektu pn. Wdroenie strategii szkoleniowej, Programu

Operacyjnego Kapita Ludzki wspfinansowanego ze rodkw Unii Europejskiej w ramach Europejskiego

Funduszu Spoecznego (Priorytet V, Dziaanie 5.1, Poddziaanie 5.1.1).

MICROSOFT EXCEL 2007

POZIOM REDNIOZAAWANSOWANY

2

SPIS TRECI

Przypomnienie podstawowych pojd i narzdzi, organizacji programu oraz dokumentu-skoroszytu .............. 4

Klawisze do przemieszczania si w arkuszach i skoroszytach ....................................................................... 4

Klawisze do zaznaczania ................................................................................................................................ 5

Interfejs uytkownika i jego opcje ................................................................................................................. 6

Blokowanie i odblokowywanie tytuw wierszy i kolumn ............................................................................. 8

Adresy bezwzgldne ...................................................................................................................................... 9

Formatowanie wartoci liczbowych ............................................................................................................ 10

Korzystanie z autoformatw ....................................................................................................................... 11

Formatowanie warunkowe ......................................................................................................................... 12

Sprawdzanie poprawnoci danych reguy poprawnoci danych, lista rozwijalna ........................................ 14

Typy danych, ktrych poprawnod mona sprawdzad ................................................................................ 14

Typy komunikatw, ktre mona wywietlad ............................................................................................. 15

Przykady sprawdzania poprawnoci danych .............................................................................................. 16

Wprowadzanie dodatnich liczb cakowitych. .......................................................................................... 16

Wprowadzanie okrelonej iloci znakw ................................................................................................. 18

Wpisywanie dat z okrelonego przedziau czasu ..................................................................................... 18

Lista wartoci do wyboru ......................................................................................................................... 19

Ochrona dokumentu ochrona arkusza, ochrona skoroszytu, szyfrowanie dokumentu; .............................. 20

Szyfrowanie skoroszytu i ustawianie hasa do jego otwierania................................................................... 20

Ustawianie hasa do modyfikowania skoroszytu ......................................................................................... 21

Ochrona elementw arkusza ....................................................................................................................... 22

Funkcje zastosowanie funkcji matematycznych i statystycznych ................................................................. 26

Funkcje matematyczne i trygonometryczne ........................................................................................... 26

LICZ.JEELI ................................................................................................................................................... 28

SUMA.JEELI ................................................................................................................................................ 28

REDNIA.JEELI ............................................................................................................................................ 29

3

Praca z listami danych ..................................................................................................................................... 30

Sortowanie .................................................................................................................................................. 30

Autofiltr ....................................................................................................................................................... 31

Kryteria wg Filtru niestandardowego ...................................................................................................... 32

Funkcja Sumy.czciowe ............................................................................................................................. 33

Polecenie Sumy czciowe .......................................................................................................................... 34

Filtr zaawansowany ..................................................................................................................................... 36

Wykresy w arkuszu .......................................................................................................................................... 37

Charakterystyka wykresw .......................................................................................................................... 37

Tworzenie okrelonego typu wykresu ......................................................................................................... 38

Linie trendu ................................................................................................................................................. 45

Nieliniowe zagadnienie ekonomiczne ......................................................................................................... 49

Funkcje Baz danych ......................................................................................................................................... 50

Notatki ............................................................................................................................................................. 52

4

PRZYPOMNIENIE PODSTAWOWYCH POJ I NARZDZI, ORGANIZACJ I PROGRAMU

ORAZ DOKUMENTU-SKOROSZYTU

Zanim rozpoczniemy prac z Excelem na wyszym poziomie wtajemniczenia, warto przypomnied

i usystematyzowad sobie podstawowe pojcia zwizane z tym rodowiskiem:

Dokument excela (arkusz kalkulacyjny) to siatka komrek skadajca si z ponumerowanych

wierszy i po literowanych kolumn.

Na przeciciu wiersza i kolumny znajduje si komrka.

Kada komrka w arkuszu ma cile okrelony adres, np.: A1, B21; AB247.

Skoroszyt to dokument Excela (rozszerzenie*.xls dla Exceal 2003 lub *.xlsx dla Excela 2007).

Skoroszyt skada si standardowo z trzech arkuszy.

Do komrek mona wpisywad tekst, liczby, adresy komrek, formuy i funkcje.

Formua to wzr wedug ktrego program bdzie dokonywa obliczeo. Formua moe zawierad

operatory arytmetyczne i logiczne oraz funkcje i adresy komrek.

Operatory arytmetyczne:

Suma +

Rnica -

Iloczyn *

Iloraz /

Funkcja to standardowy wzr formuy gotowy do zastosowania, np.: SUMA, REDNIA

Zakres komrek to blok danych wyznaczony adresami pierwszej i ostatniej komrki w bloku.

Pojedyncz komrk arkusza moemy wskazad (zaznaczyd) myszk na kilka sposobw:

najechad myszk na komrk i kliknd lewy przycisk myszy

z klawiatury wybierajc klawisze strzaek, dziki temu moemy przemieszczad selektor

komrki w okrelone miejsce

wpisujc dokadnie adres komrki w tzw. polu nazwy znajdujcym si z lewej strony zaraz

pod wstk.

Szybkie odnajdowanie miejsc edycji moemy realizowad za pomoc paskw przewijania pionowego

i poziomego lub za pomoc klawiszy PgDn i PgUp. Pozostae sposoby podane s poniej w postaci

kombinacji klawiszy specjalnych.

KLAWISZE DO PRZEMIESZCZANIA SI W ARKUSZACH I SKOROSZYTACH

HOME Przechodzi do pocztku wiersza

CTRL+HOME Przechodzi do pocztku arkusza

CTRL+strzaka w gr Przechodzi w gr biecego obszaru danych

CTRL+strzaka w d Przechodzi w d do krawdzi biecego obszaru danych

CTRL+strzaka w lewo, prawo Przechodzi w lewo lub w prawo do krawdzi biecego obszaru

danych

PAGE DOWN Przechodzi w d o jeden ekran

PAGE UP Przechodzi w gr o jeden ekran

ALT+PAGE DOWN Przechodzi w prawo o jeden ekran

ALT+PAGE UP Przechodzi w lewo o jeden ekran

CTRL+PAGE DOWN Przechodzi do nastpnego arkusza skoroszytu

5

CTRL+PAGE UP Przechodzi do poprzedniego arkusza skoroszytu

TAB Przemieszcza pomidzy odryglowanymi komrkami na zabezpieczonym

arkuszu

KLAWISZE DO ZAZNACZANIA

Aby zaznaczyd blok komrek do wykonania operacji na wielu komrkach jednoczenie naciskamy

nastpujce kombinacje klawiszy:

SHIFT+klawisz STRZAKI Rozszerza zaznaczony obszar o jedn komrk

SHIFT+HOME Rozszerza zaznaczony obszar do pocztku wiersza.

CTRL+SHIFT+STRZAKI Rozszerza zaznaczony obszar do krawdzi biecego obszaru danych

w kierunku strzaki

CTRL+SHIFT+HOME Rozszerza zaznaczony obszar do pocztku arkusza

CTRL+END Przechodzi do ostatniej komrki w arkuszu (dolny prawy rg)

CTRL+SHIFT+END Rozszerza zaznaczony obszar do ostatniej komrki w arkuszu (dolny

prawy rg)

CTRL+KLAWISZ SPACJI Zaznacza ca kolumn

SHIFT+KLAWISZ SPACJI Zaznacza cay wiersz

CTRL+A Zaznacza cay arkusz

SHIFT+PAGE DOWN Rozszerza zaznaczony obszar w d o jeden ekran

SHIFT+PAGE UP Rozszerza zaznaczony obszar w gr o jeden ekran

CTRL+SHIFT+* Zaznacza biecy obszar

CTRL+SHIFT+SPACJIA Przy zaznaczonym obiekcie, zaznacza wszystkie obiekty arkusza

SCROLL LOCK Wcza lub wycza scroll lock

END Wcza lub wycza tryb koniec

END, klawisz STRZAKI Przechodzi po jednym bloku danych w wierszu lub kolumnie

END, SHIFT+STRZAKI Rozszerza zaznaczony obszar do kooca bloku danych w kierunku strzaki

END, HOME Przechodzi do ostatniej komrki w arkuszu (prawy dolny rg)

END, SHIFT+HOME Rozszerza zaznaczony obszar do ostatniej komrki w arkuszu (prawy

dolny rg)

6

INTERFEJS UYTKOWNIKA I JEGO OPCJE

W programie Office Excel 2007 nowy interfejs uytkownika Office Fluent zastpuje wszystkie menu, paski

narzdzi i wikszod okienek zadao, ktre byy dostpne w poprzednich wersjach programu Excel,

za pomoc prostego i wykrywalnego mechanizmu. Nowy interfejs uytkownika Office Fluent zosta

zaprojektowany tak, aby uatwid podnoszenie produktywnoci i efektywnoci w programie Excel,

wyszukiwanie odpowiednich funkcji dla rnych zadao i odkrywanie nowych zastosowao.

Nowa lokalizacja znajomych poleceo. Aby odnaled lokalizacj poleceo menu i paskw narzdzi znanych

z wczeniejszych wersji w programie Excel 2007, mona posuyd si tabel doczon na koocu skryptu

Lokalizacja w programie Excel 2003

Lokalizacja w programie Excel 2007

Nowy > Przycisk pakietu Office | Nowy | Pusty arkusz

Otwrz... > Przycisk pakietu Office | Otwrz

Zapisz > Pasek narzdzi Szybki dostp | Zapisz

Zapisz > Przycisk pakietu Office | Zapisz

Uprawnienie > Przycisk pakietu Office | Przygotuj | Ogranicz uprawnienie | Ogranicz uprawnienie jako

Wiadomod e-mail > Przycisk pakietu Office | Wylij | Wiadomod e-mail

Drukuj > Przycisk pakietu Office | Drukuj | Szybkie drukowanie

Podgld wydruku > Przycisk pakietu Office | Drukuj | Podgld wydruku

Pisownia... > Recenzja | Sprawdzanie | Pisownia

Poszukaj > Recenzja | Sprawdzanie | Poszukaj

Wytnij > Narzdzia gwne | Schowek | Wytnij

Kopiuj > Narzdzia gwne | Schowek | Kopiuj

Wklej > Narzdzia gwne | Schowek | Wklej

Malarz formatw > Narzdzia gwne | Schowek | Malarz formatw

Cofnij > Pasek narzdzi Szybki dostp | Cofnij

Wykonaj ponownie > Pasek narzdzi Szybki dostp | Wykonaj ponownie

Adnotacje odrczne > Recenzja | Pismo odrczne | Rozpocznij pisanie odrczne

Hipercze > Wstawianie | cza | Hipercze

Autosumowanie > Narzdzia gwne | Edycja | Autosumowanie

Autosumowanie > Formuy | Biblioteka funkcji | Autosumowanie

Sortuj rosnco > Dane | Sortowanie i filtrowanie | Sortuj od A do Z

Sortuj malejco > Dane | Sortowanie i filtrowanie | Sortuj od Z do A

Kreator wykresw > Wstawianie | Wykresy

Rysowanie > Te polecenia s dostpne na kartach Narzdzia do rysowania po wstawieniu lub zaznaczeniu ksztatu.

Powikszenie > Przycisk pakietu Office | Opcje programu Excel | Dostosowywanie | Wszystkie polecenia | Powikszenie

Microsoft Excel Pomoc > Grna czd Wstki | Pomoc

Raport tabeli przestawnej i wykresu przestawnego

> Wstawianie | Tabele | Tabela przestawna | Tabela przestawna/wykres przestawny

Komentarz > Recenzja | Komentarze | Nowy komentarz

Autofiltr > Przycisk pakietu Office | Opcje programu Excel | Dostosowywanie | Wszystkie polecenia | Autofiltr

7










































8




















BLOKOWANIE I ODBLOKOWYWANIE TYTUW WIERSZY I KOLUMN

Przy bardzo duych listach danych warto skorzystad z tzw. Opcji Zablokuj okienka, ktra pozwala

zablokowad jeden lub kilka grnych wierszy lub jedn, kilka kolumn z lewej strony w arkuszu tak, by przy

przewijaniu wierszy lub kolumn te zablokowane byy cay czas na tej samej pozycji. Opcj t stosuje si

czsto np. przy zdefiniowanych przez uytkownika nagwkach wierszy w listach, czy bazach danych.

Aby np. zablokowad pierwszy wiersz i pierwsz kolumn zaznacz komrk B2 i wybierz ze wstki Widok

przycisk Zablokuj okienka i dalej ponownie Zablokuj okienka.

9

ADRESY BEZWZGLDNE

Adresowanie bezwzgldne umoliwia blokowanie adresw co powoduj, e nie zmieniaj si podczas

kopiowania i tworzenia serii.

1. Zaznacz komrk zawierajc formu.

2. Na pasku formuy (pasek formuy: Pasek u gry okna programu Excel, ktry suy do wprowadzania

i edytowania wartoci i formu w komrkach i na wykresach. Wywietlana jest w nim wartod staa

lub formua przechowywana w komrce aktywnej.) zaznacz odwoanie, ktre chcesz

zmienid.

3. Naciskaj klawisz F4, aby przeczad si midzy kombinacjami.

W poniszej tabeli opisany jest sposb aktualizowania typu odwoania, gdy formua zawierajca odwoanie

jest kopiowana o dwie komrki niej i o dwie komrki w prawo.

Odwoanie: Zmienia si na:

$A$1 (bezwzgldne (bezwzgldne odwoanie do komrki: W formule jest to

dokadny adres komrki, niezaleny od pooenia komrki, ktra zawiera

formu. Bezwzgldne odwoanie do komrki ma postad $A$1.) odwoanie do

kolumny i bezwzgldne do wiersza)

$A$1

A$1 (wzgldne (odwoanie wzgldne: W formule jest to adres komrki oparty

na wzgldnym pooeniu komrki, ktra zawiera formu, i komrki, do ktrej

nastpuje odwoanie. Jeli formua zostanie skopiowana, odwoanie jest

automatycznie dostosowywane. Odwoanie wzgldne ma postad A1.)

odwoanie do kolumny i bezwzgldne do wiersza)

C$1

$A1 (bezwzgldne odwoanie do kolumny i wzgldne do wiersza) $A3

A1 (wzgldne odwoanie do kolumny i wzgldne do wiersza) C3

Formuy mona kopiowad rwnie do ssiednich komrek, uywajc uchwytu wypeniania (niewielki, czarny

kwadracik plusik w prawym dolnym rogu zaznaczenia). Gdy uytkownik wskae uchwyt wypenienia,

wskanik przybiera postad czarnego krzyyka.) .

10

FORMATOWANIE WARTOC I LICZBOWYCH

Wszystkie komrki nowo utworzonego arkusza przybieraj oglny format liczbowy oraz standardowe

wyrwnanie i inne atrybuty. Wygld naszych komrek moemy zmienid uywajc opcji Formatuj komrki

z menu prawego przycisku myszy lub ze wstki Narzdzia gwne.

Jeli jest to moliwe, wpisanemu cigowi znakw automatycznie przypisywany jest poprawny format

liczbowy. Na przykad, w przypadku wpisu zawierajcego znak dolara przed liczb lub znak procenta

po liczbie, format liczby jest automatycznie zmieniany z formatu oglnego na format walutowy

lub procentowy - odpowiednio. Format liczby moe zostad zastpiony formatem wbudowanym lub wasnym

formatem liczbowym utworzonym przez uytkownika. Wzorce formatw liczbowych mog zawierad

do czterech sekcji rozdzielonych rednikami. Sekcje te definiuj format liczb dodatnich, ujemnych, wartoci

zerowych i tekstu. Formaty obejmujce kolor (czerwony) wywietlaj liczby ujemne w kolorze czerwonym

(dotyczy monitorw kolorowych).

11

KORZYSTANIE Z AUTOFORMATW

Program Microsoft Excel posiada wbudowane formaty zakresu, zwane autoformatami, ktre pozwalaj

na ich automatyczne zastosowanie do zaznaczonych zakresw danych. Formaty te s pomocne w nadaniu

arkuszowi bardziej profesjonalnego wygldu, uatwiaj prezentacj danych i zwikszaj ich czytelnod.

Wywoane mog byd za pomoc opcji Autoformat ze wstki Narzdzia gwne:

i

Autoformaty to poczenia formatw liczb, wyrwnao, czcionek, krawdzi, wzorkw, kolorw, szerokoci

kolumn i wysokoci wierszy. Po zaznaczeniu zakresu i zastosowaniu autoformatu, automatycznie okrelane

s poziomy uoglnienia i szczegowoci zaznaczonych zakresw i stosowane s odpowiednie format. Kady

autoformat moe obejmowad poczenie wszystkich lub wybranych formatw tak, by zachowad formaty

ju zastosowane do danego zakresu.

Jeli formaty s po prostu kopiowane z jednej komrki lub zakresu do innej komrki lub zakresu, mona

do tego uyd przycisku Malarz formatw.

Aby powtarzad wklejanie wystarczy dwukrotnie kliknd na ten klawisz. Klawsz ESC kooczy uycie narzdzia.

12

FORMATOWANIE WARUNKOWE

Formatowanie warunkowe daje moliwod ustawienia do 64 warunkw, co w porwnaniu z poprzednimi

edycjami Excela daje potne moliwoci atrakcyjnego przedstawiania danych liczbowych.

We wczeniejszych wersjach programu mona byo wykorzystad tylko trzy pierwsze warunki. Wszystkie

reguy formatowania warunkowego pozostan jednak dostpne w skoroszycie i zostan zastosowane

przy ponownym otwarciu skoroszytu w programie Excel 2007, chyba e reguy s edytowane

we wczeniejszej wersji programu Excel.

Jeli chcemy wyrnid komrki speniajce okrelone kryteria innym formatowaniem ni pozostae

to moemy posuyd si formatowaniem warunkowym.

Po zaznaczeniu odpowiedniego zakresu komrek odnajdujemy opcj: Formatowanie warunkowe

i tworzymy reguy, przypisujc formaty

Kolejne warunki tworzymy klikajc na odpowiednie ikony. Ustalamy warunek i format

Po zatwierdzeniu obserwujemy dynamicznie zmieniajce si formatowanie komrek. Jeli zawartod

komrek zmieni si to adekwatnie zmieni si ich sformatowanie. Dziki temu moemy kontrolowad wyniki

formu i w atwy sposb odszukiwad specyficzne wartoci.

Kolejne warunki moemy sformatowad niestandardowo wykorzystujc np. desenie.

13

Zaznaczanie tekstw zawierajcych podobny fragment np. zaznaczamy splki S.A.. Zaznaczamy

analizowany obszar i w opcji formatowania warunkowego wybieramy Tekst zawierajcy

Nastpnie wpisujemy szukany fragment

Gdy bdziemy chcieli sprawdzid, do jakich komrek zostao zastosowane tego typu formatowanie, moemy

w module sprawdzania zgodnoci kliknd przycisk Znajd, aby odnaled komrki, do ktrych zastosowano

formatowanie warunkowe z uyciem wicej ni trzech warunkw, a nastpnie wprowadzid odpowiednie

zmiany.

14

SPRAWDZANIE POPRAWNOCI DANYCH REGUY POPRAWNOCI DANYCH, LISTA

ROZWIJALNA

Sprawdzanie poprawnoci danych w programie Microsoft Excel umoliwia zdefiniowanie typu danych, ktre

maj byd wprowadzane w komrce. Na przykad mona zezwolid na wpisywanie liter jedynie z zakresu od A

do F. Sprawdzanie poprawnoci danych mona skonfigurowad w taki sposb, aby uniemoliwid

wprowadzanie przez uytkownikw niepoprawnych danych albo umoliwid wprowadzanie niepoprawnych

danych, ale sprawdzad ich poprawnod po zakooczeniu wprowadzania przez uytkownika. Mona rwnie

ustawid wywietlanie komunikatw okrelajcych, jakie dane wejciowe komrki s oczekiwane, a take

instrukcji uatwiajcych uytkownikom poprawianie ewentualnych bdw.

Sprawdzanie poprawnoci danych jest szczeglnie uyteczne w przypadku projektowania formularzy lub

arkuszy, z ktrych inne osoby bd korzystad w celu wprowadzania danych, takich jak formularze budetu

albo raporty wydatkw.

TYPY DANYCH, KTRYCH POPRAWNOD MONA SPRAWDZAD

Program Excel umoliwia ustawienie nastpujcych typw poprawnych danych dla komrki:

Liczby Okrel, czy wpis w komrce musi byd liczb cakowit, czy dziesitn. Mona ustawid wartod

minimaln lub maksymaln, wykluczyd pewn liczb lub zakres, a take uyd formuy obliczajcej, czy liczba

jest poprawna.

15

Daty i godziny Ustaw minimum lub maksimum, wyklucz niektre daty lub godziny albo uyj formuy

obliczajcej, czy data lub godzina s poprawne.

Dugod Ogranicz liczb znakw wpisywanych w komrce lub ustaw wymagan minimaln liczb znakw.

Lista wartoci Utwrz list wyborw komrki takich wartoci, jak may, redni, duy i zezwalaj tylko

na te wartoci w komrce. Mona wywietlad strzak rozwijania, gdy uytkownik kliknie komrk,

aby uatwid wybieranie wartoci z listy.

TYPY KOMUNIKATW, KTRE MONA WYWIETLAD

Dla kadej sprawdzanej komrki mona wywietlid dwa rne komunikaty: jeden wywietlany przed

wprowadzaniem danych przez uytkownika, a drugi wywietlany wwczas, gdy uytkownik prbuje

wprowadzid dane niespeniajce wymagao. Jeli uytkownicy wcz Asystenta pakietu Office, komunikaty

wywietli Asystent.

Komunikat wejciowy Komunikat tego typu jest wywietlany, gdy uytkownik kliknie sprawdzan

komrk. Mona go uyd w celu podawania instrukcji dotyczcych typu danych, ktre maj byd

wprowadzane w komrce.

Komunikat o bdzie Komunikat tego typu jest wywietlany tylko wtedy, gdy uytkownik wpisze

niepoprawne dane i nacinie klawisz ENTER. Do wyboru s trzy typy komunikatw o bdzie:

16

Komunikat informacyjny Ten komunikat nie zapobiega wprowadzeniu niepoprawnych danych. Oprcz

zdefiniowanego tekstu, komunikat zawiera ikon informacyjn, przycisk OK, ktrego nacinicie powoduje

wprowadzenie niepoprawnych danych w komrce, a take przycisk Anuluj, przywracajcy poprzedni

wartod komrki.

Komunikat ostrzegawczy Ten komunikat nie zapobiega wprowadzeniu niepoprawnych danych. Oprcz

zdefiniowanego tekstu, komunikat zawiera ikon ostrzeenia oraz trzy przyciski: Tak, wprowadzajcy

niepoprawne dane w komrce, Nie, powodujcy powrt do komrki w celu dalszej edycji, oraz Anuluj,

przywracajcy poprzedni wartod komrki.

Komunikat zatrzymania Ten komunikat uniemoliwia wprowadzenie niepoprawnych danych. Zawiera

ikon zatrzymania i dwa przyciski: Ponw, powodujcy powrt do komrki w celu dalszej edycji, oraz

Anuluj, przywracajcy poprzedni wartod komrki. Naley zauwayd, e ten komunikat nie stanowi metody

zabezpieczeo: mimo e uytkownicy nie mog wprowadzid niepoprawnych danych, wpisujc je i naciskajc

klawisz ENTER, to mog obejd sprawdzanie poprawnoci danych, kopiujc i wklejajc dane lub wypeniajc

nimi komrk.

Jeli nie zostan okrelone adne komunikaty, dane wprowadzane przez uytkownika program Excel

oznaczy flagami jako poprawne bd niepoprawne, dziki czemu mona bdzie sprawdzid ich poprawnod

pniej, ale nie powiadomi uytkownika o niepoprawnoci wpisu.

PRZYKADY SPRAWDZANIA POPRAWNOCI DANYCH

WPROWADZANIE DODATNICH LICZB CAKOWITYCH .

Zaznacz fragment arkusza, dla ktrego ustanawiasz kryteria i wybierz polecenie Sprawdzanie poprawnoci.

Na zakadce Ustawienia ustaw kryteria, tak poniej.

Na zakadce Komunikat wejciowy podaj tred komunikatu, jaki pojawi si po wyborze komrki w obszarze

kontrolowanym przez sprawdzanie poprawnoci.

17

Alert o bdzie to komunikat pojawiajcy si wtedy, gdy uytkownik wpisze liczb nie speniajc

narzuconych warunkw (np. ujemna, uamek).

Oprcz treci alertu o bdzie naley ustawid Styl, czyli sposb reakcji Excela na prb wpisania wartoci

niedozwolonej. Po zatwierdzeniu ustawieo pora na przetestowanie. Po wpisaniu liczby niezgodnej

z narzuconymi kryteriami pojawia si alert o bdzie. Narzucony styl zatrzymaj nie daje moliwoci wpisania

niepoprawnej wartoci.

18

WPROWADZANIE OKRELONEJ ILOCI ZNAKW

Takie rozwizanie przyda si na przykad podczas wpisywania numeru PESEL, czyli w takich przypadkach,

kiedy uytkownik ma wpisad sta liczb znakw. Excel zgosi bd, jeli dostanie wpis duszy lub krtszy.

WPISYWANIE DAT Z OKRELONEGO PRZEDZIAU CZASU

Data pocztkowa i Data koocowa musi byd podana w poprawnym zapisie Excela, tzn. rok-miesic-dzieo

(koniecznie z mylnikami!) lub w postaci formuy wykorzystujcej funkcj date(). Efekt poniszego

sprawdzania poprawnoci bdzie taki, e uytkownik bdzie mg wprowadzid dat z przedziau 3 przed i 3

dni po aktualnej dacie.

Uwaga! Jeli komrki zostan wypenione kolejnymi datami za pomoc jakiejkolwiek opcji Kopiuj-Wklej

reguy sprawdzania poprawnoci danych nie zadziaaj! Blokada dziaa tylko na dane wpisywane z

klawiatury!

19

LISTA WARTOCI DO WYBORU

Jeli wypeniajc arkusz danymi wpisujemy wielokrotnie informacje (np.: dni tygodnia, miesice) mona

zastosowad sprawdzanie poprawnoci przy pomocy opcji Lista. Najpierw naley przygotowad na boku

arkusza list wszystkich moliwych opcji. Poniej w komrkach A1:A10 s wprowadzone nazwy miesicy.

Nastpnie na zakadce Ustawienia naley wybrad opcj Lista i w polu rdo zaznaczyd fragment arkusza

A1:A12.

Dziki temu w komrkach, w ktrych dziaa sprawdzanie poprawnoci nazwy miesicy mona wybierad

z listy po uprzednim klikniciu w przycisk.

20

OCHRONA DOKUMENTU OCHRONA ARKUSZA, OCHRONA SKOROSZYTU,

SZYFROWANIE DOKUMENTU;

W pakiecie Microsoft Office 2007 mona za pomoc hase chronid dokumenty programu Microsoft Office

Word 2007, skoroszyty programu Microsoft Office Excel 2007 oraz prezentacje programu Microsoft Office

PowerPoint 2007 przed otwarciem lub zmodyfikowaniem przez inne osoby. Naley uywad silnych hase,

w ktrych wystpuj mae i wielkie litery, cyfry i symbole. W sabych hasach nie wystpuj poczenia tych

elementw. Silne haso: Y6dh!et5. Sabe haso: Dom27. Hasa powinny mied co najmniej 8 znakw. Lepsze

s jednak hasa liczce co najmniej 14 znakw.

SZYFROWANIE SKOROSZYTU I USTAWIANIE HASA DO JEGO OTWIERANIA

Aby zaszyfrowad plik i ustawid haso do otwierania go, wykonaj nastpujce czynnoci:

1. Kliknij przycisk Microsoft Office , wska polecenie Przygotuj, a nastpnie kliknij polecenie

Zaszyfruj dokument.

2. W polu Haso wpisz haso, a nastpnie kliknij przycisk OK.

3. Maksymalnie mona wpisad 255 znakw. Domylnie ta funkcja korzysta z zaawansowanego

szyfrowania 128-bitowego AES. Szyfrowanie jest standardow metod zabezpieczania plikw.

4. W polu Wpisz ponownie haso wpisz ponownie to samo haso, a nastpnie kliknij przycisk OK.

5. Aby zapisad haso, zapisz plik.

21

USTAWIANIE HASA DO MODYFIKOWANIA SKOROSZYTU

Aby zezwolid na modyfikowanie zawartoci tylko uprawnionym recenzentom, wykonaj nastpujce

czynnoci:

1. Kliknij przycisk Microsoft Office , a nastpnie kliknij polecenie Zapisz jako.

2. W menu Narzdzia kliknij polecenie Opcje oglne.

3. Wykonaj jedn lub obie z nastpujcych czynnoci:

Jeli chcesz, by recenzenci musieli wprowadzad haso, zanim bd mogli wywietlid skoroszyt,

wpisz haso w polu Haso ochrony przed otwarciem.

Jeli chcesz, by recenzenci musieli wprowadzad haso, zanim bd mogli zapisad zmiany

wprowadzone w skoroszycie, wpisz haso w polu Haso ochrony przed zmianami.

Mona oczywicie przypisad oba hasa jedno umoliwiajce dostp do pliku oraz jedno zezwalajce

okrelonym recenzentom na zmienianie jego zawartoci. Naley si upewnid, e hasa rni si od siebie.

4. Aby zapobiec przypadkowemu modyfikowaniu pliku przez recenzentw zawartoci, zaznacz pole wyboru Zalecane tylko do odczytu. Podczas otwierania takiego pliku recenzenci bd pytani, czy plik ma zostad otwarty w trybie tylko do odczytu.

5. Kliknij przycisk OK. 6. Po wywietleniu monitu wpisz ponownie hasa, aby je potwierdzid, a nastpnie kliknij przycisk OK. 7. W oknie dialogowym Zapisywanie jako kliknij przycisk Zapisz. 8. Jeli zostanie wywietlony monit, kliknij przycisk Tak, aby zamienid istniejcy skoroszyt.

22

OCHRONA ELEMENTW ARKUSZA

Aby zapobiegad przypadkowemu lub umylnemu zmodyfikowaniu, przeniesieniu bd usuniciu wanych

danych, mona chronid okrelone elementy arkusza z uyciem lub bez uycia hasa. Wane jednak, by nie

naley mylid ochrony elementw skoroszytu i arkusza z zabezpieczeniem za pomoc hasa na poziomie

skoroszytu. Ochrona elementw nie moe ochronid skoroszytu przed uytkownikami majcymi ze zamiary.

1. Wybierz arkusz, ktry chcesz chronid. 2. Aby odblokowad komrki lub zakresy, ktre bd mogy byd zmieniane przez innych uytkownikw,

wykonaj nastpujce czynnoci: 1. Zaznacz wszelkie komrki lub zakresy, ktre chcesz odblokowad. 2. Na karcie Narzdzia gwne w grupie Komrki kliknij przycisk Format, a nastpnie kliknij

polecenie Komrki. 3. Na karcie Ochrona wyczyd pole wyboru Zablokuj, a nastpnie kliknij przycisk OK.

4. Aby ukryd formuy, ktre maj byd niewidoczne, wykonaj nastpujce czynnoci: 1. W arkuszu zaznacz komrki zawierajce formuy, ktre chcesz ukryd.

2. Na karcie Narzdzia gwne w grupie Komrki kliknij przycisk Format, a nastpnie kliknij

polecenie Komrki.

3. Na karcie Ochrona zaznacz pole wyboru Ukryty, a nastpnie kliknij przycisk OK.

Istotnym jest fakt, e nie trzeba odblokowywad przyciskw ani formantw, aby uytkownicy mogli je klikad

i korzystad z nich. Mona odblokowad osadzone wykresy, pola tekstowe i inne obiekty utworzone

za pomoc narzdzi do rysowania, ktre uytkownicy maj mc modyfikowad.

5. Na karcie Recenzja w grupie Zmiany kliknij przycisk Chroo arkusz.

23

6. Na licie Pozwl wszystkim uytkownikom tego skoroszytu na zaznacz te elementy, ktre uytkownicy bd mogli zmieniad.

7. W polu Haso do usunicia ochrony arkusza wpisz haso dla arkusza, kliknij przycisk OK, a nastpnie ponownie wpisz haso, aby je potwierdzid. Haso jest opcjonalne, ale jeli si go nie poda, kady uytkownik bdzie mg usund ochron arkusza i zmienid chronione elementy. Naley zadbad o wybranie hasa, ktre mona zapamitad, poniewa w przypadku utraty hasa nie bdzie mona uzyskad dostpu do chronionych elementw arkusza.

24

Wyczyd to pole wyboru Aby uniemoliwid uytkownikom

Zaznaczanie zablokowanych komrek

Przenoszenie wskanika do komrek, dla ktrych zaznaczono pole wyboru Zablokowany na karcie Ochrona w oknie dialogowym Formatowanie komrek. Zaznaczanie zablokowanych komrek jest domylnie dozwolone.

Zaznaczanie odblokowanych komrek

Przenoszenie wskanika do komrek, dla ktrych wyczyszczono pole wyboru Zablokowany na karcie Ochrona w oknie dialogowym Formatowanie komrek. Zaznaczanie odblokowanych komrek jest domylnie dozwolone, a uytkownicy mog przechodzid midzy odblokowanymi komrkami chronionego arkusza, naciskajc klawisz TAB.

Formatowanie komrek

Zmienianie jakichkolwiek opcji w oknach dialogowych Formatowanie komrek i Formatowanie warunkowe. Jeli przed wczeniem ochrony arkusza zastosowano formatowanie warunkowe, formatowanie komrek nadal bdzie ulegad zmianie, gdy uytkownik wprowadzi wartod pasujc do innego zdefiniowanego warunku.

Formatowanie kolumn

Uywanie wszelkich poleceo formatowania kolumn, w tym zmienianie szerokoci kolumn lub ukrywanie kolumn (karta Narzdzia gwne, grupa Komrki, przycisk Formatuj).

Formatowanie wierszy Uywanie wszelkich poleceo formatowania wierszy, w tym zmienianie wysokoci wierszy lub ukrywanie wierszy (karta Narzdzia gwne, grupa Komrki, przycisk Formatuj).

Wstawianie kolumn Wstawianie kolumn. Wstawianie wierszy Wstawianie wierszy.

Wstawianie hiperczy Wstawianie nowych hiperczy, rwnie w niezablokowanych komrkach.

Usuwanie kolumn

Usuwanie kolumn. Jeli polecenie Usuwanie kolumn jest chronione, a polecenie Wstawianie kolumn nie jest chronione, nie mona usuwad wstawionych przez siebie kolumn.

Usuwanie wierszy

Usuwanie wierszy. Jeli polecenie Usuwanie wierszy jest chronione, a polecenie Wstawianie wierszy nie jest chronione, nie mona usuwad wstawionych przez siebie wierszy.

Sortowanie

Uywanie wszelkich poleceo do sortowania danych (karta Dane, grupa Sortowanie i filtrowanie). Bez wzgldu na ustawienie tej opcji uytkownicy nie mog sortowad zakresw zawierajcych zablokowane komrki w chronionym arkuszu.

Uywanie Autofiltru

Zmienianie filtru dla zakresw komrek, gdy s stosowane funkcje Autofiltru, za pomoc strzaek rozwijanych. Bez wzgldu na ustawienie tej opcji uytkownicy nie mog stosowad ani usuwad funkcji Autofiltru w chronionym arkuszu.

Uywanie raportw tabeli przestawnej

Formatowanie, zmienianie ukadu, odwieanie raportw tabeli przestawnej i wprowadzanie do nich jakichkolwiek innych zmian oraz tworzenie nowych raportw.

Edytowanie obiektw

Wykonywanie dowolnej z nastpujcych czynnoci: Wprowadzanie zmian w obiektach graficznych takich jak mapy, wykresy osadzone, ksztaty, pola tekstowe i formanty o ile nie zostay one odblokowane przed wczeniem ochrony arkusza. Jeli na przykad arkusz zawiera przycisk uruchamiajcy makro, przycisk mona kliknd, aby uruchomid makro, ale nie mona go usund.

25

Dokonywanie zmian, takich jak formatowanie, w wykresach osadzonych. Wykres bdzie nadal aktualizowany po zmianie danych rdowych. Dodawanie lub edytowanie komentarzy.

Edytowanie scenariuszy

Wywietlanie scenariuszy ukrytych przez uytkownika, wprowadzanie zmian w scenariuszach, ktrych modyfikowanie jest zabronione, oraz usuwanie takich scenariuszy. Uytkownicy mog edytowad wartoci w komrkach, ktre nie s chronione, oraz dodawad nowe scenariusze.

Zaznacz to pole wyboru Aby uniemoliwid uytkownikom

Spis treci Wprowadzanie zmian elementw wykresu, takich jak serie danych, osie i legendy. Na wykresach nadal s odzwierciedlane zmiany wprowadzane w danych rdowych.

Obiekty Wprowadzanie zmian w obiektach graficznych takich jak ksztaty, pola tekstowe i formanty o ile nie zostay one odblokowane przed wczeniem ochrony arkusza wykresu.

26

FUNKCJE ZASTOSOWANIE FUNKCJI MATEMATYCZNYCH I STATYSTYCZNYCH

Wrd wszystkich zdefiniowanych w rodowisku Excela funkcji kady moe wybrad co dla siebie. Tak

na powanie, moim skromnym zdaniem funkcje (formuy) stanowi fundament moliwoci Excela. Moemy

je podzielid na kilka kategorii:

Finansowe

Logiczne

Tekstowe

Data i godzina

Wyszukiwania i odwoania

Matematyczne i trygonometryczne

Statystyczne

Inynierskie

Moduowe

Informacyjne

Funkcje zgodnoci

Aby dodad co wicej, formuy moemy podzielid jeszcze w inny sposb na tzw. Funkcje bezargumentowe

oraz argumentowe:

1. Funkcje bezargumentowe - nie wymagaj podawania adnych dodatkowych informacji poza wskazaniem

samej jej nazwy.

=nazwa_funkcji(), np. =dzi(), =teraz()

2. Funkcje argumentowe te z kolei wymagaj dodatkowych argumentw, ktrymi mog byd liczby, tekst,

inne funkcje (wtedy mamy do czynienia z tzw. zagniedaniem funkcji w funkcji).

=nazwa_funkcji(argument1, argument2;; argument n), gdzie n=1,2,3,, np.

=jeeli(test;prawda;fasz), =licz.jeeli(zakres; kryteria)

=nazwa_funkcji(argument1:argument n), gdzie n=1,2,3,, np.

=suma(wartod_pierwsza_zakresu:wartod_ostatnia_zakresu)

FUNKCJE MATEMATYCZNE I TRYGONOMETRYCZNE

Poniej znajduje si lista wybranych funkcji matematycznych:

MODU.LICZBY Zwraca wartod bezwzgldn liczby

ACOS Zwraca arcus cosinus liczby

ACOSH Zwraca arcus cosinus hiperboliczny liczby

ASIN Zwraca arcus sinus liczby

ASINH Zwraca arcus sinus hiperboliczny liczby

ATAN Zwraca arcus tangens liczby

ATAN2 Zwraca arcus tangens liczby na podstawie wsprzdnych x i y

ATANH Zwraca arcus tangens hiperboliczny liczby

ZAOKR.W.GR Zaokrgla liczb do najbliszej liczby cakowitej lub do najbliszej wielokrotnoci

zadanej dokadnoci

KOMBINACJE Zwraca liczb kombinacji dla danej liczby obiektw

COS Zwraca cosinus liczby

27

COSH Zwraca wartod cosinusa hiperbolicznego liczby

STOPNIE Konwertuje radiany na stopnie

ZAOKR.DO.PARZ Zaokrgla liczb w gr do najbliszej liczby parzystej

EXP Zwraca wynik podniesienia liczby e do okrelonej potgi

SILNIA Zwraca silni liczby

FACTDOUBLE Zwraca dwukrotn wartod silni liczby

ZAOKR.W.D Zaokrgla liczb w d w kierunku zera

GCD Zwraca najwikszy wsplny dzielnik

ZAOKR.DO.CAK Zaokrgla liczb w d do najbliszej liczby cakowitej

LCM Zwraca najmniejsz wspln wielokrotnod

LN Zwraca logarytm naturalny podanej liczby

LOG Zwraca logarytm liczby o podanej podstawie

LOG10 Zwraca wartod logarytmu liczby przy podstawie 10

WYZNACZNIK.MACIERZY Zwraca wyznacznik macierzy reprezentowanej przez dan tablic

MACIERZ.ODW Zwraca macierz odwrotn macierzy reprezentowanej przez dan tablic

MACIERZ.ILOCZYN Zwraca iloczyn macierzowy dwch tablic

MOD Zwraca reszt z dzielenia

MROUND Zwraca liczb zaokrglon do podanej wielokrotnoci

MULTINOMIAL Zwraca wielomian dla zbioru liczb

ZAOKR.DO.NPARZ Zaokrgla liczb w gr do najbliszej liczby nieparzystej

PI Zwraca wartod liczby Pi

POTGA Zwraca wynik podniesienia liczby do podanej potgi

ILOCZYN Zwraca iloczyn argumentw

QUOTIENT Zwraca cakowit czd ilorazu

RADIANY Konwertuje stopnie na radiany

LOS Zwraca liczb pseudolosow z zakresu od 0 do 1

RANDBETWEEN Zwraca liczb pseudolosow z zakresu okrelonego przez podane argumenty

RZYMSKIE Konwertuje liczb arabsk na liczb rzymsk w postaci tekstowej

ZAOKR Zaokrgla liczb do liczby o okrelonej liczbie cyfr

ZAOKR.D Zaokrgla liczb w d w kierunku zera

ZAOKR.GRA Zaokrgla liczb w gr, w kierunku od zera

SERIESSUM Zwraca sum szeregu potgowego o podanym wzorze

ZNAK.LICZBY Zwraca znak liczby

SIN Zwraca sinus podanego kta

SINH Zwraca sinus hiperboliczny podanej liczby

SQRT Zwraca dodatni pierwiastek kwadratowy podanej liczby

SQRTPI Zwraca pierwiastek kwadratowy z liczby pomnoonej przez liczb Pi

SUMY.POREDNIE Zwraca sum czciow listy lub bazy danych

SUMA Zwraca sum argumentw

SUMA.JEELI Sumuje komrki speniajce podane kryteria

SUMA.ILOCZYNW Zwraca sum iloczynw odpowiednich elementw tablicy

SUMA.KWADRATW Zwraca sum kwadratw argumentw

SUMA.X2.M.Y2 Zwraca sum rnic kwadratw odpowiadajcych sobie wartoci w dwch

tablicach

SUMA.X2.P.Y2 Zwraca sum sum kwadratw odpowiadajcych sobie wartoci w dwch tablicach

28

SUMA.X.M.Y2 Zwraca sum kwadratw rnic odpowiadajcych sobie wartoci w dwch tablicach

TAN Zwraca tangens liczby

TANH Zwraca tangens hiperboliczny liczby

LICZBA.CAK Przycina liczb do wartoci cakowitej

Poniej omwimy par z nich. Jednake najistotniejszym faktem w dyskusji o funkcjach jest zwrcenie uwagi

na bardzo mocno rozbudowan pomoc ich dotyczc, co pokae trener.

LICZ.JEELI

Funkcja naleca do kategorii Statystycznych. Zlicza liczb komrek wewntrz zakresu, ktre speniaj

podane wymagania.

LICZ.JEELI(zakres ; kryteria )

Zakres - to zakres komrek, z ktrego maj byd zliczane komrki.

Kryteria - s to kryteria w postaci liczby, wyraenia lub tekstu okrelajce, ktre komrki bd

obliczane.

Przykad

=LICZ.JEELI(G7:G24;">40") zliczy osoby starsze od 40 lat

SUMA.JEELI

Funkcja naleca do kategorii Matematycznych. Sumuje komrki speniajce podane kryteria.

SUMA.JEELI(zakres ; kryteria ; suma_zakres )

Zakres - jest zakresem komrek, ktre naley przeliczyd.

Kryteria - s to kryteria w postaci liczby, wyraenia lub tekstu okrelajce, ktre komrki bd

dodane. Na przykad, kryteria mona wyrazid jako 32, "32", ">32", "jabka".

Sum_zakres - to komrki wyznaczone do zsumowania. Komrki w sum_zakres s sumowane tylko

wtedy, jeli odpowiadajce im komrki w zakresie speniaj kryterium. Jeli Sum_zakres zostaje

pominite, to sumowane s komrki w zakresie .

29

Przykad

=SUMA.JEELI(G7:G24;">40";F7:F24) zliczy pace osb starszych od 40 lat.

REDNIA.JEELI

Zwraca redni (redni arytmetyczn) wszystkich komrek z zakresu, ktre speniaj podane kryteria.

REDNIA.JEELI(zakres; kryteria; rednia_zakres)

Zakres to jedna lub wicej komrek, ktre maj zostad urednione, wczajc w to liczby lub

nazwy, a take tablice lub odwoania zawierajce liczby.

Kryteria to kryteria w postaci liczby, wyraenia, odwoania do komrki lub tekstu, okrelajce

komrki, dla ktrych zostanie obliczona rednia. Kryteria mona wyrazid na przykad jako 32, "32",

">32", "jabka" lub B4.

rednia_zakres to rzeczywisty zestaw komrek, dla ktrych zostanie obliczona rednia. W

przypadku pominicia tego argumentu zostanie uyty parametr zakres.

Przykad: urednianie zyskw z oddziaw regionalnych

A B

1 Region Zyski (w tysicach)

2 Wschd 45 678

3 Zachd 23 789

4 Pnoc -4 789

5 Poudnie (nowy oddzia) 0

6 rodkowy zachd 9 678

7 Formua Opis (wynik)

8 =SUMA.JEELI(A2:A6;"=*Zachd";B2:B6) rednia zyskw z regionw Zachd oraz rodkowy zachd (16 733,5).

9 =SUMA.JEELI(A2:A6;"*(nowy oddzia)";B2:B6)

rednia zyskw dla wszystkich regionw z wyjtkiem nowych oddziaw (18 589).

30

PRACA Z LISTAMI DANYCH

SORTOWANIE

Czasami przy duych bazach danych konieczne moe okazad si posortowanie danych, czyli uporzdkowanie

wierszy listy zgodnie z zawartoci zaznaczonych kolumn. Mona wtedy uyd polecenia Sortuj ze wstki

Narzdzi gwnych lub wstki Dane. Nastpnie naley zaznaczyd dane i wybrad porzdek sortowania lub

utworzyd i wykorzystad wasny porzdek sortowania.

W oknie sortowania naley zaznaczyd czy lista ma wiersz nagwka czy nie. W przeciwnym przypadku nazwy

pl mog zastad posortowane razem z danymi.

W prostszych przypadkach moemy posuyd si klawiszami szybkiego sortowania.

Wystarczy wwczas zaznaczyd komrk w kolumnie, wg ktrej chcemy sortowad i nacisnd klawisz

szybkiego sortowania A->Z lub Z->A.

31

AUTOFILTR

Filtrujc list moemy wywietlad tylko te wiersze, ktre speniaj okrelone kryteria. Na przykad, na licie

sprzedawcw i wielkoci sprzeday moemy wywietlid tylko tych sprzedawcw, ktrzy sprzedali za kwot

powyej 5 000 z. W programie Microsoft Excel dostpne s dwa sposoby filtrowania listy: przy uyciu

polecenia Autofiltr lub polecenia Zaawansowany filtr (obie opcje we wstce Dane).

Polecenie Autofiltr wywietla strzaki obok etykiet kolumn na licie (tzw. pola kombi),zatem moemy wybrad

element, ktry chcemy wywietlid. Polecenia Autofiltr uywamy, aby szybko przefiltrowad wiersze

posugujc si kryteriami z jednej kolumny.

Aby pokazad, jak dziaa Autofiltr, wykorzystamy przykadow list z danymi

Jeli lista zawiera nagwki czyli nazwy pl oraz posiada cig struktur to kolejne wiersze moemy

nazywad rekordami a list baz danych.

Aby ponisza procedura dziaaa, lista musi posiadad etykiety kolumn.

1. Zaznacz jedn komrk na licie, ktr chcesz przefiltrowad.

2. We wstce Dane wska polecenie Filtruj.

3. Kliknij strzak w kolumnie zawierajcej dane, ktre chcesz przefiltrowad.

4. Aby przefiltrowad list wybierz t wartod.

32

Aby przefiltrowad list wedug dwch lub wicej wartoci z kolumny lub zastosowad operatory porwnania

inne ni "I", kliknij pozycj "Filtr niestandardowy".

KRYTERIA WG FILTRU NIESTANDARDOWEGO

Stosujc si do poniszych instrukcji mona przefiltrowad list uywajc jednego lub dwch kryteriw

porwnania dla tej samej kolumny. Aby ta procedura dziaaa, lista musi posiadad etykiety kolumn.

Aby uwzgldnid jedno kryterium, kliknij strzak obok pierwszego pola operatora, a nastpnie wybierz dany operator porwnania.

Aby uwzgldnid dwa kryteria, kliknij przycisk opcji "I" albo "LUB". W drugim polu operatora i w drugim polu etykiety kolumny wybierz odpowiedni operator i dan wartod.

33

Operatory porwnywania. Znak stosowany w kryteriach porwnawczych w celu porwnania dwch

wartoci. Szed standardowych operatorw porwnania:

= Rwne

> Wiksze ni

< Mniejsze ni

>= Wiksze lub rwne

34

SUMY.CZCIOWE(funkcja_liczba; adres)

Funkcja_liczba jest liczb od 1 do 11 okrelajc, ktra funkcja ma byd wykorzystana przy obliczaniu sum

porednich wewntrz listy.

Funkcja_liczba Funkcja

1 REDNIA

2 ILE.LICZB

3 ILE.NIEPUSTYCH

4 MAX

5 MIN

6 ILOCZYN

7 ODCH.STANDARDOWE

8 ODCH.STANDARD.POPUL

9 SUMA

10 WARIANCJA

11 WARIANCJA.POPUL

Parametry mona zmieniad uywajc klawisza kreatora funkcji fx:

Adres jest zakresem lub adresem, dla ktrego ma byd obliczona suma

czciowa. Jeli wewntrz adresu znajduj si inne sumy czciowe (lub

zagniedone sumy porednie), to te zagniedone sumy

s ignorowane, aby uniknd podwjnego obliczania. SUMY.CZCIOWE

zignoruje wszystkie ukryte wiersze, ktre powstaj na filtrowanej licie.

Jest to wane, gdy chcemy zsumowad tylko widoczne dane, powstajce

na filtrowanej licie.

POLECENIE SUMY CZC IOWE

Tak naprawd, atwiej jest stworzyd list z sumami porednimi przy

wykorzystaniu polecenia Sumy czciowe we wstce Dane. Naley

35

zwrcid uwag, e Sumy czciowe mona znaled pod pojciem Sum porednich. Jest to dokadnie ta sama

opcja. Wbrew nazwie oprcz sum moemy uzyskad inne funkcje np. rednia, licznik, max, min, wariancja itp.

Funkcje te moemy zamieniad lub nawarstwiad w zalenoci od zaznaczonej lub nie opcji Zamieo biece

sumy czciowe.

Warunkiem koniecznym, ktry trzeba spenid przed wykorzystaniem polecenia Sumy czciowe jest

posortowanie bazy danych. Nastpnie klikamy na dowolnej komrce posortowanej listy i wybieramy

polecenie Sumy czciowe

Po ustaleniu dla zmian jakiego pola bdziemy wyliczad sumy porednie, wybieramy funkcje analityczn oraz

pole do ktrego ta funkcja bdzie dodana. Nastpnie ustalamy opcje w dolnej czci pola dialogowego i

klikamy OK.

Otrzymamy tabel z dodan struktur sum porednich pod kadym z blokw danych oraz z dodanymi

po lewej stronie arkusza plusikami/minusikami uatwiajcymi ukrywanie elementw porednich.

Jeli chcemy usund struktur sum porednich wybieramy przycisk Usuo wszystko z okna Sum czciowych,

co usuwa naoone funkcje pozostawiaj dane w pierwotnym ksztacie.

36

FILTR ZAAWANSOWANY

Filtrowanie listy przy uyciu polecenia Filtr zaawansowany polega na ustalaniu kryteriw wielokrotnych

w wyspecyfikowanym obszarze i umieszczaniu wycigu poza baz.

Aby ponisza procedura dziaaa, lista musi posiadad etykiety kolumn.

1. Wstaw kilka wierszy nad list. 2. Do pustego wiersza wpisz lub skopiuj etykiety kryteriw, ktrych chcesz uyd do filtrowania listy.

Etykiety te musz byd takie same, jak etykiety kolumn, ktre chcesz filtrowad. 3. W wierszach poniej etykiet kryteriw wpisz kryteria, ktre chcesz uwzgldnid. Zachowaj odstp

jednego wiersza lub jednej kolumny (w zalenoci od miejsca usytuowania zakresu kryteriw). 4. Kliknij komrk na licie. 5. We wstce Dane wska w ramce Sortowanie i filtrowanie polecenie Zaawansowane.

6. Aby przefiltrowad list poprzez ukrycie wierszy, ktre nie speniaj podanych kryteriw, kliknij

opcj "Filtruj list w miejscu".

7. Aby przefiltrowad list przez skopiowanie w inne miejsce arkusza wierszy, ktre speniaj podane

kryteria, kliknij opcj "Kopiuj w inne miejsce", kliknij pole "Kopiuj do", a nastpnie kliknij lewy

grny rg obszaru wklejania.

8. W polu Zakres kryteriw okrel zakres kryteriw uwzgldniajc rwnie etykiety kryteriw.

Aby otrzymad operator logiczny LUB podczas budowania kryteriw elementy wpisujemy w kolumnie a dla

uzyskania operatora I moemy powielad te same nazwy pl w celu uzyskania elementw w jednym wierszu.

37

WYKRESY W ARKUSZU

TEMATYKA: tworzenia i modyfikacja wykresw oraz ich elementw, wykres jako graficzna interpretacja

umoliwiajca efektywn analiz danych w arkuszu kalkulacyjnym;

CHARAKTERYSTYKA WYKRESW

Wykres stanowi ilustracj graficzn danych z arkusza. Wykresy mog uatrakcyjniad dane, uatwiad ich odczyt

i ocen, s rwnie pomocne w analizie i porwnywaniu danych.

W przypadku utworzenia wykresu w oparciu o zaznaczony zakres komrek arkusza, wartoci z arkusza

s pobierane i przedstawiane na wykresie w postaci punktw danych, reprezentowanych przez supki, linie,

kolumny, wycinki, kropki i inne figury. Figury te nazywane s znacznikami danych.

Grupy punktw danych lub znacznikw danych pochodzce z wierszy lub kolumn jednego arkusza

s zgrupowane w serie danych. Kada seria danych jest odrniona od pozostaych cechami takimi jak kolor,

wzorek czy ich poczenie.

Po utworzeniu wykresu mona go wzbogacid i podkrelid pewne informacje poprzez dodanie elementw

wykresu takich jak etykiety danych, legenda, tytuy, tekst, linie trendu, paski bdu i linie siatki. Wikszod

elementw wykresu daje si przenosid i skalowad. Elementy te mona rwnie formatowad przy uyciu

wzorkw, kolorw, rozmieszczenia, czcionek i innych cech formatuj

Poniej przygotujemy tabel, ktr przeanalizujemy graficznie za pomoc wykresw. Najpierw wykonujemy

sumy kwartalne i wyliczamy zyski

Po sformatowaniu arkusza zaznaczamy blokiem zakres danych przeznaczonych do wykrelania np. A3:D7. Zaznaczamy etykiety kolumn i wierszy aby pojawiy si na wykresie. Nastpnie uywamy kreatora wykresw

do wygenerowania grafiki.

38

TWORZENIE OKRELONEGO TYPU WYKRESU

1. Zaznacz dane arkusza przeznaczone do rysowania, wczajc komrki zawierajce wszystkie nazwy

kategorii i serii, ktre maj zostad wykorzystane na wykresie.

2. Wejd na zakadk Wstawianie i wybierz typ Wykresu lub kliknij przycisk lub strzak na d. W celu

przejcia do kolejnych typw wykresw klikaj i podwietlaj ikony z poszczeglnymi rodzajami.

3. Dokonaj wyboru typu klikajc na jego ikon

4. Jako pierwszy wybierz podtyp wykresu pierwszy z kolumnowych 2-W

.

39

Otrzymany wykres moemy przesuwad po arkuszu, zmieniad rozmiar za naroniki i boki. Formatowanie

wykonujemy klikajc np. na serii obrotw prawym klawisz myszy i wybierajc Formatuj seri danych.

Opcje serii su do regulacji Nakadania serii, Szerokoci serii, i Kreleniu osi pomocniczej.

Kolejny element pola dialogowego to Wypenienie:

40

Nastpnie zdefiniuj zakres wypenienie wsplnie z instruktorem dwiczymy wszystkie rodzaje.

W nastpnym kroku ustalamy Kolor krawdzi, oraz przechodzc do opcji Styl krawdzi i Cieo. Na kolejnych

opcji Format 3-W ustalmy trjwymiarowod szczegw wykresu.

Wybierz przycisk Zamknij po wybraniu opcji serii wykresu

Po zakooczeniu kreacji wykresu moemy modyfikowad jego postad za pomoc katy narzdziowej

Projektowanie. Pierwszym klawiszem Zmieo typ wykresu zmieniamy typ na drugi.

41

Jest to wykres nakadany dlatego najlepiej przeczyd serie

Warto pokazad rwnie wykres tzw. 100% skumulowany kolumnowy np. w wykonaniu 3-W

42

Wybierajc obiekt wykresu zmieniamy go prawym klawiszami. Modyfikacji moemy dokonywad te

za pomoc karty Projektowanie wybierajc odpowiedni Ukad lub Styl wykresu.

43

Wybierajc typ wykresu Kolumnowy 3-W otrzymujemy jeden z najbardziej uniwersalnych wykresw:

Tu z ciekawszych opcji formatowania naley wymienid zmian ksztatu supkw

44

oraz Obrot 3-W, gdzie moemy obracad wykresem i dodawad perspektyw

Bardzo popularnym sposobem wykrelania danych jest wykres koowy pokazujcy procentowe udziay

kadego elementu serii w sumie cakowitej.

45

LINIE TRENDU

Jeli chcemy dopasowad do danych na wykresie lini, ktra okrelaaby jak bd si zachowywad wyniki

w przyszoci i jakim trendom podlegaj teraz moemy zastosowad graficzn metod.

Przygotowujemy tabel Bilans

Po zaznaczeniu serii danych klikamy prawym klawiszem myszy i wybieramy Dodaj lini trendu.

Zakadamy, e trend jest typu liniowego

46

Opcje linii trendu

Za pomoc tych opcji mona zmieniad formatowanie linii trendu (linia trendu: Graficzna reprezentacja

trendu dla serii danych, na przykad linia wznoszca si, ktra reprezentuje wzrastajc wartod sprzeday

w okresie kilku miesicy. Linie trendu s uywane do prognozowania, zwanego rwnie analiz regresji.)

wywietlanej na wykresie.

Typ trendu/regresji

Wykadniczy Wybranie tej opcji powoduje dodanie linii krzywej wywietlajcej wartoci danych, ktre

wzrastaj lub opadaj w przyspieszonym tempie. W przypadku wykadniczej linii trendu dane nie powinny

zawierad wartoci zerowej ani wartoci ujemnych.

Dla tego typu linii trendu jest uywane nastpujce rwnanie suce do obliczania dopasowania punktw

metod najmniejszych kwadratw:

gdzie c i b s staymi, a e jest podstaw logarytmu naturalnego.

Liniowy Wybranie tej opcji powoduje dodanie najlepiej dopasowanej linii prostej wywietlajcej proste

liniowe zestawy danych zawierajce wartoci, ktre wzrastaj lub opadaj w staym tempie.

Dla tego typu linii trendu jest uywane nastpujce rwnanie liniowe suce do obliczania dopasowania

linii metod najmniejszych kwadratw:

gdzie m to nachylenie, a b to punkt przecicia.

Logarytmiczny Wybranie tej opcji powoduje dodanie najlepiej dopasowanej linii krzywej wywietlajcej

wartoci danych, ktre najpierw szybko wzrastaj lub opadaj, a nastpnie utrzymuj si na staym

poziomie. W przypadku logarytmicznej linii trendu dane mog zawierad wartoci dodatnie i ujemne.



gdzie c i b s staymi, a ln jest logarytmem naturalnym.

Wielomianowy Wybranie tej opcji powoduje dodanie linii krzywej wywietlajcej fluktuacje wartoci

danych. Naley kliknd t opcj, a nastpnie w polu Kolejnod wprowadzid liczb cakowit od 2 do 6, aby

okrelid, ile zgid (wzniesieo i zagbieo) ma si pojawid na krzywej. Jeli na przykad w polu Kolejnod jest

ustawiona wartod 2, na wykresie zostanie najczciej wywietlone jedno wzniesienie lub zagbienie, przy

wartoci 3 jedno lub dwa, a przy wartoci 4 do trzech wzniesieo lub zagbieo.



gdzie b i s staymi.

Potgowy Wybranie tej opcji powoduje dodanie linii krzywej wywietlajcej wartoci danych

porwnujcych pomiary rosnce z okrelon szybkoci. W przypadku potgowej linii trendu dane nie

powinny zawierad wartoci zerowej ani wartoci ujemnych.

47



gdzie c i b s staymi.

rednia ruchoma Wybranie tej opcji powoduje dodanie linii krzywej trendu wywietlajcej wartoci danych

z wygadzonymi fluktuacjami danych, aby wyraniej pokazad wzr lub trend. Naley kliknd t opcj,

a nastpnie w polu Okres wprowadzid liczb pomidzy liczb 2 a liczb punktw danych w serii

pomniejszon o 1, aby okrelid rednie punktw danych, ktre bd uywane jako punkty linii trendu. Jeli

na przykad wartod ustawiona w polu Okres wynosi 2, wartod rednia z dwch pierwszych punktw

danych jest uywana jako pierwszy punkt linii trendu redniej ruchomej, rednia z drugiego i trzeciego

punktu danych jako drugi punkt linii trendu itd.

Dla tego typu linii trendu jest uywane nastpujce rwnanie:

Nazwa linii trendu

Automatyczna Dla linii trendu jest uywana nazwa domylna dla wybranego typu linii trendu/regresji.

Niestandardowa Mona okrelid wasn nazw linii trendu dla wybranego typu linii trendu/regresji. Naley

kliknd t opcj, a nastpnie wpisad odpowiedni nazw w polu Niestandardowa.

Prognoza

Do przodu Aby prognozowad dalszy przebieg trendu, naley wpisad w polu Do przodu odpowiedni liczb

okresw w przyszoci.

Do tyu Aby doczyd okresy przebiegu trendu w przeszoci, naley wpisad w polu Do tyu odpowiedni

liczb okresw w przeszoci.

Ustaw przecicie To pole okrela, w jakim miejscu linia trendu przecina o pionow wartoci. Naley

zaznaczyd to pole wyboru, a nastpnie wpisad liczb, ktra ma zostad uyta jako punkt przecicia z osi

pionow.

Wywietl rwnanie na wykresie Zaznaczenie lub wyczyszczenie tego pola wyboru powoduje wywietlenie

lub ukrycie rwnao uytych do obliczenia linii trendu.

Wywietl wartoci R-kwadrat na wykresie Zaznaczenie lub wyczyszczenie tego pola wyboru powoduje

wywietlenie lub ukrycie wartoci R-kwadrat wyliczanej automatycznie w programie Microsoft Office Excel

w celu okrelenia niezawodnoci trendu i dokadnoci prognozy.

Pozostae opcje su do formatowania np. kolory linii stylu linii i cienia.

49

NIELINIOWE ZAGADNIENIE EKONOMICZNE

Zamy, e sklep podwysza cen towaru chcc zwikszyd obrt ale okazuje si e podwyszajc cen

towaru o 1 z tracimy 5 nabywcw. Zakadajc, e pocztkowo mielimy 100 klientw przy cenie 10 z.

Tym razem mamy do czynienia z wielomianem stopnia 2 czyli z zagadnieniem rwnania kwadratowego.

50

FUNKCJE BAZ DANYCH

zasady tworzenia list, sortowanie w okrelonym porzdku, proste filtrowanie.

W tym rozdziale opisane zostao 12 funkcji arkusza uywanych w przeliczeniach baz danych (lub list

elementw) programu Microsoft Excel. Kada z tych funkcji, oznaczona odpowiednio jako BDfunkcja,

wymaga podania trzech argumentw:

baza danych ,

pole

kryterium .

Argumenty te podaj adresy zakresw arkusza, wykorzystywanych przez funkcje bazy danych.

BDfunkcja(baza_danych ; pole ; kryteria )

Baza_danych - jest to zakres komrek tworzcych baz danych.

W przypadku wszystkich funkcji bazy danych, jeli adresem bazy danych jest komrka pooona wewntrz

tabeli przestawnej, to obliczenia bd wykonywane tylko na danych z tabeli przestawnej.

Baza danych w pakiecie Microsoft Excel jest cigym zakresem komrek, podzielonym na rekordy (wiersze)

i pola (kolumny). Adres bazy danych mona wprowadzid jako zakres komrek lub jako nazw przypisan

do zakresu.

Wskazwki

Jako argumentu bazy danych mona uywad dowolnego zakresu wcznie z argumentami w postaci

nagwka umieszczonego nad kadym wierszem danych. Jest to uyteczne, jeli chcemy prowadzid

obliczenia BDfunkcji w wicej ni jednym zakresie danych arkusza. Jeli uywamy nagwkw pl, musimy

stosowad podwjne cudzysowy.

Pole - wskazuje, ktre pole funkcja ma obejmowad swoim dziaaniem. Pola baz danych stanowi kolumny

danych z identyfikujcym polem nazwy w pierwszym wierszu. Argument pola mona podad jako tekst, jak

np. "Wiek" lub "Zysk" w przykadowej tablicy bazy danych przedstawionej poniej, lub jako numer pola: 1

dla pola pierwszego (Drzewo) w poniszym przykadzie, 2 dla drugiego (Wysokod) i tak dalej.

Kryteria - jest to zakres komrek zawierajcych kryteria bazy danych. Adres kryteriw mona wprowadzid

jako zakres komrek, jak np. A9:F10 w przykadowej bazie danych, lub jako nazw przypisan do zakresu.

Przykady funkcji

BD.SUMA(baza_danych; pole; kryteria) - Dodaje do siebie liczby w kolumnie pola rekordw bazy danych

speniajcych kryteria.

Baza_danych - jest to zakres komrek tworzcych baz danych.

Pole - wskazuje, ktrego pola naley uyd w funkcji.

Kryteria - jest to zakres komrek zawierajcych kryteria bazy danych.

51

BD.ILE.REKORDW(Baza_danych ;"Pole"; Kryteria) - Funkcja ta przeglda rekordy dotyczce i zlicza, ile pl

spenia kryteria

BD.ILE.REKORDW.A(Baza_danych ;"Pole"; Kryteria) - Funkcja ta przeglda rekordy i zlicza, ile pl nie jest

pustych.

BD.MAX (Baza_danych ;"Pole"; Kryteria) - podaje maksymaln wartod w kolumnie Pole.

52

NOTATKI

Przypomnienie podstawowych poj i narzdzi, organizacji programu oraz dokumentu-skoroszytuKlawisze do przemieszczania si w arkuszach i skoroszytachKlawisze do zaznaczaniaInterfejs uytkownika i jego opcjeBlokowanie i odblokowywanie tytuw wierszy i kolumnAdresy bezwzgldneFormatowanie wartoci liczbowychKorzystanie z autoformatwFormatowanie warunkowe

Sprawdzanie poprawnoci danych reguy poprawnoci danych, lista rozwijalnaTypy danych, ktrych poprawno mona sprawdzaTypy komunikatw, ktre mona wywietlaPrzykady sprawdzania poprawnoci danychWprowadzanie dodatnich liczb cakowitych.Wprowadzanie okrelonej iloci znakwWpisywanie dat z okrelonego przedziau czasuLista wartoci do wyboru

Ochrona dokumentu ochrona arkusza, ochrona skoroszytu, szyfrowanie dokumentu;Szyfrowanie skoroszytu i ustawianie hasa do jego otwieraniaUstawianie hasa do modyfikowania skoroszytuOchrona elementw arkusza

Funkcje zastosowanie funkcji matematycznych i statystycznychFunkcje matematyczne i trygonometryczneLICZ.JEELISUMA.JEELIREDNIA.JEELI

Praca z listami danychSortowanieAutofiltrKryteria wg Filtru niestandardowego

Funkcja Sumy.czciowePolecenie Sumy czcioweFiltr zaawansowany

Wykresy w arkuszuCharakterystyka wykreswTworzenie okrelonego typu wykresuLinie trenduNieliniowe zagadnienie ekonomiczne

Funkcje Baz danychNotatki

Microsoft Excel 2007

Documents

Transcript of Microsoft Excel 2007