Transcript MS EXCEL cd MS Access

MS EXCEL cd.
MS Access
Wykład 5
Funkcje czasu
= DZIŚ()
= TERAZ()
= CZAS (gg;mm;ss)
= DATA(rr; mm ;dd)
Przykład:
= DZIŚ() - DATA(1981;12;13)
Funkcje statystyczne
= SUMA(zakres)
= ŚREDNIA(zakres)
= ODCH.STANDARDOWE(zakres)
= MIN(zakres1;zakres2)
= MAX (zakres)
Inne grupy funkcji
•
•
•
•
•
finansowe
logiczne
tekstowe
informacyjne
inne
•
•
•
bezpośrednie wpisywanie do formuł (wyrażeń)
kreator (lub menu fX)
funkcje
zagnieżdżane
(pole
nazwy
przekształcone w rozwijane okno dostępu do
ostatnio używanych funkcji oraz kreatora –
Więcej funkcji)
KOPIOWANIE I PRZENOSZENIE DANYCH I FORMUŁ
Jeśli przenosimy komórki z danymi do których odnoszą się formuły
w innych komórkach to zmiana adresu jest w formułach
automatycznie śledzona.
Jeśli przenosimy komórkę z formułą to odwołania do adresów
innych komórek nie zmieniają się.
Przy kopiowaniu komórki z formułą:
•jeżeli adresy odwołań są względne to następuje ich zmiana w
proporcji do drogi przemieszczenia kopii względem oryginału,
•jeżeli adresy odwołań są bezwzględne ich zmiana nie następuje
•jeżeli adresy odwołań są mieszane to następuje ich zmiana w
proporcji do drogi przemieszczenia kopii względem oryginału ale
tylko względem współrzędnej względnej (tylko wiersza albo tylko
kolumny)
Odwołanie do komórki (zakresu komórek)
innego arkusza:
=’Arkusz’!adres
Dokładność obliczeń i wizualizacja miejsc
dziesiętnych
dokładność obliczeń 1E-15
czyli 10-15
BŁĘDY W ARKUSZU
składniowe (niepoprawne wyrażenie, brak nawiasów, zły
zapis liczby, nieznana nazwa #NAZWA), zgłasza się
„Pomoc”.
#ADR!
matematyczne (np. dzielenie przez zero, argument poza
obszarem określoności funkcji), #LICZBA, #DZIEL/0
inne – np. za wąska kolumna ############
odwołanie cykliczne, formuła odnosi się do adresu samej
siebie.
Wykresy - kreator
Typy
•kolumnowe
•liniowe
•słupkowe
•powierzchniowe (3-wymiarowe)
•inne
Wykresy funkcji 1 zmiennej
x
Tworzenie wykresów
•utworzenie tabeli danych
•zakres i krok dla zmiennej niezależnej (seria)
•zapis serii dla zmiennej niezależnej,
•funkcja dla pierwszej wartości zmiennej y
•kopiowanie funkcji do pozostałych komórek
•wybór typu wykresu
•dane do wykresu
y
0
0,1
0,2
0,3
0,4
0,5
0,6
0,7
0,8
0,9
1
1,1
1,2
1,3
0
0,09531
0,182322
0,262364
0,336472
0,405465
0,470004
0,530628
0,587787
0,641854
0,693147
0,741937
0,788457
0,832909
Pozostałe opcje wykresu (tytuły, osie, siatka, legenda)
Korekta wykresu:
• wielkość – zaczepy rozmiaru
• parametry – z menu kontekstowego elementów wykresu
(tło, obszar danych, wykres, osie itp.)
Wykresy 2 zmiennych:
x
y
y
1
2
3
4
x 1
1,2322
3,445
5,667
7,544
2
...
...
....
...
3
4
f(x,y)
Przed wywołaniem kreatora zaznaczamy cały obszar danych
Sortowanie i filtrowanie (na wstędze Dane)
całość
część
kolum n
część w ierszy
fragm ent
EXCEL - WSPOMAGANIE DECYZJI
funkcje i narzędzia - użyteczne są:
• relacje oraz funkcje logiczne - do wspomagania
decyzji strukturyzowanych, szczególnie
przydatne w sytuacjach, gdy trzeba sprawdzać,
czy zawartości danych komórek arkusza
spełniają określony warunek logiczny.
• funkcje informacyjne, do testowania typu
zawartości komórek,
• narzędzia: Szukaj wyniku i Solver - do
wspomagania decyzji strukturyzowanych oraz
semi-strukturyzowanych
Relacja jest połączeniem dwóch wyrażeń (numerycznych lub
tekstowych) jednym z operatorów relacji (porównań):
=
równy
>
większy niż
<
mniejszy niż
>=
większy niż lub równy
<=
mniejszy niż lub równy
<>
różny (nierówny)
Wyrażenie logiczne przyjmuje wartość logiczną
PRAWDA lub FAŁSZ, zależną od tego, czy jest
spełniona czy nie.
Przykłady wyrażeń logicznych:
=A1>0
=B5>=2*C8
=SUMA(A1:A5)<1E-8
=C8<>-5
Funkcje logiczne
FAŁSZ
- oznacza wartość logiczną fałszu (0 logiczne),
PRAWDA - oznacza wartość logiczną prawdy (1 logiczna),
NIE
- odwraca wartość logiczną argumentu (negacja),
LUB
- suma logiczna (alternatywa) argumentów,
ORAZ
- iloczyn logiczny (koniunkcja) argumentów,
JEŻELI - określa wybór, na podstawie testu logicznego,
jednej z dwóch wartości.
Funkcje FAŁSZ( ) oraz PRAWDA( ) są funkcjami
bezargumentowymi, które oznaczają stałe logiczne, stosowane
w wyrażeniach logicznych lub jako wartości wpisywane do
komórek.
Funkcja negacji NIE(wyr_logiczne) jest funkcją
jednoargumentową, która neguje (odwraca) wartość swojego
argumentu. Należy stosować funkcję NIE wtedy, gdy trzeba
być pewnym, że dana wartość nie jest równa jakiejś
szczególnej wartości.
Przykład:
NIE(FAŁSZ) jest równe PRAWDA,
NIE(2+2=4) jest równe FAŁSZ
Funkcja sumy logicznej LUB o postaci:
LUB(wyrażenie_logiczne1; wyrażenie_logiczne2; ...)
przyjmuje wartość logiczną PRAWDA, jeśli choć jeden
argument ma wartość logiczną PRAWDA. Jeśli wszystkie
argumenty mają wartość logiczną FAŁSZ, funkcja przyjmuje
wartość logiczną FAŁSZ.
Przykłady:
LUB(1+1=2; 2+2=5) jest równe PRAWDA,
LUB(A1:A3) jest równe PRAWDA, jeśli zakres A1:A3 zawiera
wartości logiczne i przynajmniej jedna wartość wynosi PRAWDA.
Funkcja iloczynu logicznego ORAZ o postaci:
ORAZ(wyrażenie_logiczne1; wyrażenie_logiczne2; ...)
przyjmuje wartość PRAWDA, jeśli wszystkie jej
argumenty mają wartość PRAWDA. Wartością funkcji
jest FAŁSZ, jeśli co najmniej jeden z argumentów ma
wartość FAŁSZ.
Przykłady:
ORAZ(PRAWDA; FAŁSZ) jest równe FAŁSZ,
ORAZ(2+2=4; 2+3=5) jest równe PRAWDA.
Uwagi dotyczące funkcji LUB i ORAZ:
• argumenty powinny być wyrażeniami logicznymi
bądź adresami komórek, zawierających wyrażenia
lub wartości logiczne.
• jeśli którakolwiek z wartości składowych tabel lub
komórek zawiera tekst, liczby lub jest pusta,
wartości te są pomijane.
• jeśli określony argument nie jest wartością
logiczną, funkcja LUB przyjmuje wartość
komunikatu o błędzie "#ARG!".
Funkcja wyboru
JEŻELI, o postaci:
JEŻELI(wyrażenie_logiczne; wyrażenie_1; wyrażenie_2)
pozwala na podjęcie decyzji wyboru jednej z dwóch
alternatywnych wartości wyrażeń, na podstawie testu wartości
wyrażenia logicznego.
Argument wyrażenie_1 oznacza wartość jaką przyjmuje funkcja
dla przypadku, gdy wartość wyrażenia logicznego jest równa
PRAWDA.
W przypadku, gdy wyrażenie logiczne przyjmuje wartość FAŁSZ
wartością funkcji staje się wartość wyrażenie_2.
=JEŻELI(A1>0;"dodatnia"; "ujemna lub zero")
=JEŻELI(A1>0;A6; A7)
teksty w cudzysłowie
Funkcja JEŻELI jest szczególnie przydatna w zapisywaniu
określonych reguł decyzyjnych.
Można zagnieździć do siedmiu funkcji JEŻELI stosując je
jako argumenty wyrażeń wyrażenie_1 i wyrazenie_2, w
celu zapisania bardziej złożonych warunków.
=jeżeli (A1<20;"młody";jeżeli(A1>70;"stary";"średni"))
B
A
1
2
3
4
5
23
12
3
49
89
średni
młody
młody
średni
stary
Funkcje informacyjne
są przeznaczone do ustalania typu danych przechowywanych w komórce
Grupa funkcji CZY - funkcje te przyjmują wartość PRAWDA, jeśli
komórka-argument spełnia warunek.
LICZ.PUSTE(zakres)
CZY.PUSTA(adres)
CZY.BŁĄD(adres)
CZY.LICZBA(adres)
CZY.LOGICZNA(adres)
ile w zakresie komórek jest komórek pustych
czy pusta komórka
czy argument przyjmuje wartość błędu
czy argument jest liczbą
czy argument przyjmuje wartość logiczną
SUMA.JEŻELI(zakres;kryterium)
sumowanie komórek spełniających warunek:
np.:
=suma.jeżeli (A1:A10;”>5”)
LICZ.JEŻELI(zakres;kryterium)
ile komórek spełnia warunek: np.:
=licz.jeżeli (A1:A10;”>5”)
Wspomaganie decyzji ustrukturyzowanych
Przykład:
Sytuacja decyzyjna:
Przekroczenie – gdy 14 dni od daty sprzedaży.
=JEŻELI(data_b – data_sp <= 14; 0 ; (data_b – data_sp - 14) * stopa_dz*Wartość)
to są nazwy innych komórek
Narzędzie Szukaj wyniku
Wyrażenie, które zawiera zmienną - adres lub nazwę innej komórki - traktowaną
jako zmienną decyzyjną.
Celem jest znalezienie wartości zmiennej decyzyjnej aby uzyskać wartość
docelową wyrażenia (czyli rozwiązanie równania z jedną niewiadomą)
Czynności:
• identyfikacja problemu,
• określenie zależności pomiędzy zmiennymi występującymi w
problemie,
• wprowadzenie do arkusza zależności w postaci wyrażeń (wzór może
również zawierać funkcje standardowe),
• wskazanie komórki, zawierającej wzór określający poszukiwana
wartość oraz komórki, która stanowiącej zmienna decyzyjną
problemu.
• interpretacja wyniku.
Przykład
Komórka, której wartości
poszukujemy
Komórka, która powinna przyjąć
wartość 400 000 zł
Wielkość sprzedaży wpisujemy 1 oraz sporządzamy formułę przychodów
=B1 *B2
Uruchamiamy narzędzie Szukaj wyniku- na karcie Dane w grupie
Narzędzia danych przycisk Analiza symulacji/Szukaj wyniku.
WPROWADZENIE DO OBSŁUGI BAZ
DANYCH
MS ACCESS
Pliki z rozszerzeniem *.mdb *accdb
Możliwości:
• tworzenie tabel,
• projektowanie kwerend,
• projektowanie formularzy,
• projektowanie raportów itd.
Tabele bazy danych
Zbiór atrybutów, które są odpowiednikiem szczególnych,
elementarnych cech opisywanego obiektu i które zwane
są nazwami pól tabeli.
Każdy atrybut (pole tabeli) ma określone właściwości,
przede wszystkim typ danych. MS Access umożliwia
ustalenie następujących typów:
Typ
tekst
Przeznaczenie
Uwagi
dla pól zawierających ciągi standardowa długość 50 znaków,
znaków alfanumerycznych maksymalnie 255 znaków
(personalia, nazwy, adresy,
kody itp.)
liczba
dla danych numerycznych
data/godzina
dla daty i czasu
walutowy
autonumerowanie
kwoty
licznik – liczby całkowite,
zwykle wykorzystywany
jako klucz tabeli
TAK/NIE
dla pól typu logicznego:
np. zapłacił/nie zapłacił
Prawda/Fałsz, Wł./Wył.
do przechowywania plików:
dokumentów, obrazów,
dźwięków
odnośnik do innego pliku,
adresu strony www albo
adresu e-mail
dłuższe ciągi znaków, np.
długość pola do ok. 65000 znaków
życiorys
obiekt OLE
hiperłącze
nota (memo)
liczba całkowita,
liczba całkowita długa,
liczby dziesiętne
możliwy wybór formatu wyświetlania
standardowo w złotych
dla kolejnych rekordów automatycznie
powiększany o 1
Jeśli jest to pole tekstowe określamy długość (maksymalną
liczbę znaków)
Dla pola liczbowego ustalamy typ liczby (liczba całkowita,
liczba całkowita długa, dziesiętna itp.).
Odpowiedni typ danych wymusza:
- kontrolę poprawności wpisywanych danych,
- pozwala wykorzystywać mechanizmy sortowania, filtracji i wyszukiwania
danych.
Po zaprojektowaniu tabeli wprowadzamy dane do tabeli.
dane - rekord to pojedynczy wiersz danych w tabeli
Przykład
Podstawowe operacje na tabelach
Klucz podstawowy i obcy - związki między
tabelami
Jeden z atrybutów, najczęściej sztucznie wprowadzony identyfikator (z reguły
o typie danych Autonumerowanie), jest tzw. kluczem głównym
(podstawowym), jednoznacznie identyfikującym rekord
(pojedynczy
wiersz danych) w tabeli. Kluczem podstawowym może być też pole
informacyjne tabeli (np. numer telefonu w tabeli Książka telefoniczna).
Wartości w polu klucza nie mogą się powtarzać, muszą być
unikalne dla każdego rekordu danych.
Dostawcy
Towary
Klucz podstawowy
Klucz podstawowy
nazwa
nazwa
adres
cena
telefon
ilość
NIP
ID dostawcy – klucz obcy
Tworzymy drugą tabelę Towary, której atrybutami są
następujące dane:
Nazwa Pola
ID Towaru
Nazwa Towaru
Ilość
Data zakupu
Cena zakupu
IDDostawcy
Typ
Rozmiar
pola
Autonumerowanie
Text
20
Liczba
Liczba
całkowita
Data/Godzina
Walutowy
Liczba
Liczba
klucz obcy
całkowita długa
Tabele Dostawcy i Towary połączone są związkiem
jeden-do-wielu.
Związek jeden-do-wielu oznacza, że:
każdy towar ma jednego i tylko jednego dostawcę.
każdy dostawca może dostarczać wiele towarów.
Wymaga to umieszczenia w tabeli Towary pola IDdostawcy (klucza
obcego), zawierającego wartości z pola klucza głównego tabeli
Dostawcy.
Jeśli klucz główny tabeli Dostawcy był typu Autonumerowanie, to klucz
obcy powinien posiadać typ liczbowy o typie liczby całkowitej długiej.
IDdostawcy jest kluczem głównym w tabeli Dostawcy i kluczem obcym w
tabeli Towary.
Mogą występować też związki jeden-do-jeden (1:1) i wiele-do-wielu (N:M)
Jakie to związki?
WYKŁADOWCY – PRZEDMIOTY
WYKŁADY – SALE
OSOBY – TELEFONY
PRACOWNICY – STANOWISKA
KIEROWNICY – BRYGADY
PRACOWNICY – BIURKA
TOWARY – KATEGORIE
KSIĄŻKI - CZYTELNICY
Jeśli związki są 1:1 to wszystkie dane w jednej
tabeli
Jeśli związek wiele do wielu to tworzymy nową
tabelę:
Książki – wypożyczenie – Czytelnicy
Wówczas są dwa związki jeden do wielu –
jakie?
Książki
Wypożyczenia
?
Czytelnicy
?
Relacje – związki
między tabelami
Relacje i więzy integralności pilnują poprawności – np. aby
nie wolno było dla towaru wpisać numeru dostawcy, który
nie istnieje, lub usunąć dostawcy, którego towary istnieją w
tabeli towarów.
Kwerendy – QBE – Query by Example
Chcąc utworzyć kwerendę - zapytanie do
bazy – należy wybrać obiekt Kwerendy i
utworzyć nową kwerendę w widoku projektu.
kryteria
Chcąc wyświetlić dane z dwóch tabel należy dodać do
kwerendy obie tabele – Dostawcy i Towary.
Jeśli tabele zawierają identyczne nazwy pól – tu służą
one do związania obu tabel – klucz główny i klucz obcy –
to zostanie automatycznie wykonane sprzężenie.
Następnie przeciągamy z tabel do kwerendy te pola,
które nas interesują – tutaj Nazwa_Towaru i Cena z tabeli
Towary i NazwaFirmy z tabeli Dostawcy. Teraz należy
uruchomić kwerendę
Tworzenie kryterium:
like [Podaj literę:] &*
Precyzyjniej kwerendy buduje się przy pomocy SQL –
specjalny język zapytań – ale to już bardziej
zaawansowany problem.
Koniec