1

1

Obliczenia inżynierskiew arkuszach kalkulacyjnych

Zbigniew Rudnicki

WIMiR AGH Kraków

2

Tematyka:

z Modele matematyczne, zmienne, komórki arkuszy

z Typy zmiennych a typy zawartości komórek

z Tradycyjne użytkowanie arkusza

z Wymagania dla programów obliczeniowych

z Wprowadzanie zmiennych do arkusza

z Elementy dialogowe w arkuszu i formularze

z Zabezpieczanie arkuszy

z Makra i programowanie w języku Visual Basic for Applications

2

3

Modelowanie matematyczne

z Modelowanie matematyczne polega na opisywaniufunkcjonowania układów (mechanicznych, elektrycznych iin.) przy użyciu równań oraz nierówności zawierającychstałe, zmienne, operatory działań oraz funkcje.

z Model matematyczny może mieć postać ALGORYTMUopisującego dane czyli zmienne wejściowe i kolejne kroki(operacje) prowadzące do wyznaczenia wartości zmiennychwyjściowych

4

Komputerowe narzędzia obliczeniowe

z Arkusze kalkulacyjne - Excel, Calc i inne

z Uniwersalne programy matematyczne- Mathcad, Mathematica, Statistica, Maple, ..

z Języki programowania- do samodzielnego tworzenia programównp.: Basic, Fortran, Pascal, C, C++, i in.

3

5

Elementy narzędzi obliczeniowych

W każdym z narzędzi obliczeniowych występują:z Stałe (literały) oraz zmienne różnych typów:

np.:liczby i zmienne liczboweteksty i zmienne tekstowe,daty i zmienne typu dataciągi liczb i zmienne wektorowe,tablice liczb i zmienne tablicowe

z Operatory działań arytmetycznych: +, -, *, /, ...relacji: >, < , ...i operacji logicznych: NIE, I, LUB,

z Nawiasy

z Funkcje

z Procedury obliczeniowe

6

Zmienna to:

symboliczna reprezentacjacechy badanego obiektu lub procesualbookreślonej własności samego modelu.

Każda zmienna ma ściśle określonysens i rolę w modelu matematycznym

4

7

DEFINICJA ZMIENNEJ(deterministycznej)

Zmienna to symboliczna reprezentacjaokreślonej cechy,Każda zmienna posiada:

� NAZWĘ (lub inny identyfikator np.: adres)

� TYP wartości jakie może przyjmować(oraz zakres i strukturę - jeśli jest to zmienna złożona)

� WARTOŚĆ - określoną w każdym momencie(a jeśli jest to zmienna złożona to zbiór wartości)

� ale przede wszystkim pełni OKREŚLONĄ ROLĘ

8

Zmienne w komputerze

z Każdej zmiennej przypisany jest określony obszar wpamięci operacyjnej komputera.

z Obszar ten przechowuje wartość zmiennej aodwołujemy się do niego przez jej nazwę.

z Nowo nadana wartość zmiennej zastępuje wartośćdotychczasową.

z Jeśli zmienna musi przechowywać wiele wartości tomusi być zmienną złożonąnp. wektorem, tablicą, rekordem, listą.

5

9

Zmienne proste i złożoneczyli STRUKTURY DANYCH

Oprócz zmiennych prostych czyli skalarnychmogą występować zmienne złożone czyli strukturydanych takie jak:y wektoryy macierze dwu (lub więcej) wymiarowe

y rekordyy listy (zmienna długość)

y . . . . . i inne

10

Arkusz kalkulacyjny

Arkusz kalkulacyjny to wielka tabela złożona z komórek

ustawionych w kolumnach i wierszach.

Kolumny mają oznaczenia literowe a wiersze są

ponumerowane.

Jeden arkusz w Excelu ma 256 kolumn (A, B, ... Z, AA,

AB, ... IV) oraz tysiące wierszy - np.: w Excel'u 97 jest to

65536 wierszy.

Każda komórka ma adres wynikający z oznaczenia

kolumny i wiersza na których przecięciu się znajduje.

Na przykład komórka D68 jest w kolumnie D i wierszu nr 68.

Adres aktywnej komórki (zaznaczonej grubszą obwiednią)

ukazuje się w t.zw. polu adresu (polu nazwy)

- z lewej nad arkuszem.

6

11

Ms ExcelDokument Excel'a jest nazywany zeszytem (a czasem skoroszytem

lub książką) dlatego, że składa się z wielu arkuszykalkulacyjnych (ich początkowa liczba zależy od ustawienia wNarzędzia->Opcje->Ogólne). Można mieć otwartych wiele zeszytówa w każdym wiele arkuszy. Początkującym wystarcza jedyniefragment jednego arkusza. Arkusz na którym operujemy nazywa sięaktywnym arkuszem, Inne arkusze można wybierać (uaktywniać)przy pomocy widocznych o dołu zakładek.

12

Kursory w Excel’u

7

13

Komórka jako zmienna

Komórka pełni rolę zmiennej identyfikowanejadresem.

Może przechowywać wartość stałą typu:

y teksty lub liczbay lub data

albo

y wzór pozwalający wyznaczyć wartość

14

Rozpoznawanie typuzawartości komórki

Arkusz musi prawidłowo rozpoznać typ zawartościkomórki na podstawie składni tej zawartości

a więc:

z teksty muszą rozpoczynać sięod litery lub apostrofu [‘],

z daty i liczby powinny mieć określoną postać,z wzory powinny rozpoczynać się od znaku =

8

15

Adres zakresu komórek

Można adresować prostokątne obszaryarkusza czyli zakresy komórek -podając adresy dwu granicznych komórek,przedzielone dukropkiem np.:

C6:E12

16

Dokument = zbiór arkuszy.Odwołania do innego arkusza

Dokument może składać się z wielu arkuszy.

Jeśli odwołujemy się do zakresu komórek na innymarkuszu to adres tego zakresu poprzedzamynazwą arkusza oraz znakiem:

z wykrzyknika w Excel’u, np.: Arkusz2!A5:B8z kropki w Calc’u np.: Arkusz2.A5:B8

Nazwy arkuszy mozna zmieniać

9

17

Ekran Excel’a

18

Komórki i adresy. Typy adresów

Komórka - pełni rolę zmiennejZamiast nazw zmiennych - są ADRESY KOMÓREKW formułach matematycznych stosowane są:

z adresy względne np.: C6- które zmieniają się wraz ze zmianą położenia wzoru,

czyli określane są „względem położenia wzoru”

z adresy bezwzględne np.: $C$6- które NIE zmieniają się wraz ze zmianą położenia wzoru,znak $ należy czytać jako „NIE ZMIENIAJ”

z adresy mieszane np.: $C6 lub C$6

10

19

Adresy względne - przykład

Wzory poprawne (czarne) i błędne (czerwone).

Konieczne użycie adresu bezwzględnego $C$1

20

Potrzeba adresów mieszanych - przykład:

11

21

Zmiennew arkuszach kalkulacyjnych

Komórka - pełni rolę zmiennejZamiast nazw zmiennych - są ADRESY KOMÓREK

Jednak można w arkuszach tworzyć zmienne reprezentujące komórkilub zakresy komórek: Wstaw - Nazwy - Utwórz lub Wstaw - Nazwy - Definiuj

22

Obliczenia w arkuszuz użyciem nazw zmiennych

Formuły matematyczne wykorzystującezdefiniowane nazwy zmiennych są znacznieczytelniejsze np.:

moment = sila * ramiezamiast:

moment = C4 * C5

12

23

Nazwy wstawiane z wykazu

Zamiast wpisywać - można do wzoru wstawiać nazwy

z wykazu:

24

Obliczenia z użyciem funkcji

13

25

Wybór typu wykresu funkcji jednej zmiennej

Należy wybierać typ „Wykres XY” a nie „Wykres liniowy” bo możnaotrzymać błędny rezultat:

26

14

27

28

Optymalizacja - SOLVER

Solver automatycznie znajduje takie wartości zadanychzmiennych, które spełniają podane warunki - na przykładspełniają określone równanie lub występuje dla nichekstremum (min / max) podanego wyrażenia

15

29

Wymagane oraz pożądane cechyaplikacji obliczeniowych

1) Ułatwienia we wprowadzaniu danych (np. wybór z listy)

2) Kontrola poprawności danych (np.: dopuszczalne zakresy wartości)

3) Zabezpieczenie przed przypadkową lub celową modyfikacją wzorów

4) Różne sposoby prezentowania wyników (tabele, wykresy, ...)

5) Wykorzystanie pamięci masowych - dla danych i wyników

6) Stosowanie, kontrolowanie oraz konwertowanie jednostek miar7) Jawna postać wzorów matematycznych i ich kolejność

8) Dokumentacja i objaśnienia dotyczące:y tematyki i zakresu obliczeń

y znaczenia poszczególnych zmiennych

y dopuszczalnych zakresów zmiennych

y metod lub wzorów obliczeniowych

y dokładności obliczeń

y wersji, daty opracowania i autorów (instytucja, nazwiska)

30

1) Ułatwienia we wprowadzaniu danych

z Wybór z listy automatycznie tworzonej przy wielokrotnymwprowadzaniu tych samych słów np.: „krzesło” przy spisieinwentarza

z Możliwość wstawienia listy rozwijalnejzwanej „Pole kombi”:

z Zadawanie wartości liczbowychprzy pomocy „pokrętła”lub „paska przewijania:

16

31

2) Kontrola poprawności danych (np.: przezpodanie dopuszczalnych zakresów wartości)

z Możliwa przy wykorzystaniu językaVisual Basic for Applications

32

3) Zabezpieczenie przed przypadkowąlub celową modyfikacją wzorów

Arkusz można zabezpieczyć przed zmianami

ale wcześniej trzeba określić komórki danych- nie przeznaczone do zabezpieczenia:

zaznaczamy i w oknie „Formatuj - Ochrona” usuwamy zaznaczenieopcji „zablokuj”

17

33

4) Różne sposoby prezentowaniawyników

34

5) Wykorzystanie pamięci masowych -dla danych i wyników

z Dane i wyniki można przechowywać wwielu arkuszach

18

35

6) Stosowanie, kontrolowanie orazkonwertowanie jednostek miar

z Ogólnie Arkusze nie mają takich możliwości

z Wyjątkiem są daty i czas

7) Jawna postać wzorów matematycznychi ich kolejność

- w ograniczonym zakresie i nie bezpośrednio.Wzory z adresami są niezbyt czytelne.

Można je zobaczyć po ustawieniu odpowiedniej opcji widoku

Czytelniejsze są wzory z zastosowaniem nazw zmiennych

36

8) Dokumentacja i objaśnienia

Możliwe wpisywanie objaśnień

lub umieszczanie obszerniejszych opisów

na osobnych arkuszach.

19

37

Dokumentowanie obliczeń c.d.:

„KOMENTARZE” = Dodatkowe objaśnienia

Dla poszczególnych komórek można definiować„komentarze”, które będą ukazywać się w postaci„dymków” po wskazaniu myszką danej komórki

38

Makrai

programowaniew językuVBA

Visual Basic for Applications

20

39

Tworzenie funkcji w Visual Basic

z Użytkownik arkusza może tworzyć w języku Visual BASICi wykorzystywać w arkuszach własne funkcje

z Należy z menu głównego wybrać:� w Calc'u: Narzędzia - Makra - Zarządzaj makrami -

OpenOffice BASIC,

� w Excel'u: Narzędzia - Makro - Edytor Visual Basic.

z Pojawią się okna środowiska języka Visual Basica wśród nich okno edytora do pisania programów.

z Wcześnie waro zapoznać się z makrami

40

Rejestrowanie Makr

MAKRO - to zarejestrowany ciąg poleceń, który możnaużyć jako pojedynczego polecenia.

Makra są przeznaczone do automatyzacji złożonych, częstopowtarzających się zadań.

Makra są rejestrowane w języku programowania VisualBasic for Applications, po uruchomieniu rejestracjiz (menu: Narzędzia - Makro)

Makra można także pisać bezpośrednio korzystającz edytora Visual Basic

21

41

VBA - przypisanie najprostszej procedury

do przycisku

z Z menu „Widok - Paski narzędzi” włącz „Przybornikformantów”.

z Kliknij na ikonę „Przycisk polecenia” i wstaw go do arkusza.Ikona Tryb projektowania w przyborniku powinna się uaktywnić.

z Kliknij podwójnie wstawiony przycisk - uruchomi się Edytor VisualBasic z szkieletem procedury Click naszego Przycisku (to coniebieskie):

y Private Sub CommandButton1_Click()

y Range("A1").Value = "WITAJ ŚWIECIE !"y End Sub

z Uzupełniamy - wpisując tekst zaznaczony na czerwono

42

VBA - przypisanie wartości komórce

z Wstawienie wartości 124 do komórki o konkretnym

adresie np. B5:

z [B5] = 124

z albo: Range("B5") = 124

z albo: Range("B5").Value = 124

z albo: Cells(5,2) = 124

z albo: Cells(5,2).Value = 124

22

43

VBA - wybór komórki

z Przesunięcie kursora (czyli wyboru komórki) o 3 wiersze w

dół i 2 kolumny w prawo:

z Selection.Offset(3, 2).Select

z Przesunięcie kursora o dW wierszy i dK kolumn:

z Selection.Offset(dW, dK).Select

z Selection - to wybrana komórka arkusza,

z Offset - określa położenie innej komórki względem aktywnej

z Select - to polecenie uaktywnienia (ustawienia wyboru)

44

VBA - Operowanie na zakresach komórek

z Uaktywnienie (ustawienie podświetlenia) w komórce D2:

z [D2].Select

z Podświetlenie (zamalowanie) obszaru komórek A1:C3

z Range("A1:C3").Select

z Poszerzenie obszaru podświetlonego o 2 wiersze i 4 kolumny

z Selection.Resize(2, 4).Select

z Wstawienie wartości 9 do wszystkich komórek podświetlonego obszaru:

z Selection.Value = 9

z Wstawienie 9 do komórek przesuniętych o 3 w dół i 1 w prawo od

pocz.podświetlonego obszaru:

z Selection.Offset(3, 1).Value = 9

23

45

VBA - Odwoływanie się do komórek innegoarkusza

z Aby zapisane powyżej odwołania do komórek i zakresów dotyczyły

nie arkusza aktualnego (aktywnego) lecz innego np. o nazwie

"Arkusz5", należy je poprzedzać zapisem (z kropką na końcu):

z Worksheets("Arkusz5").

z na przykład:

z Worksheets("Arkusz5").Cells(5,2).Value = 124

46

VBA - PĘTLA FOR oraz INSTRUKCJEPODSTAWIANIA

z Podstawianie wartości od 0, 5, 10, ..., 50 do komórekB7, B8, B9, ...:B17

z (bez zmiany pozycji aktywnej komórki)

z kolumna=2

z wiersz=7

z For i=0 to 50 step 5

z Cells(wiersz, kolumna).Value = i

z wiersz = wiersz +1

z Next i

24

47

VBA - Funkcja PODATEK