Uniwersytet Łódzki
Wydział Matematyki i Informatyki
Środowisko pracy informatyka
Praca z oprogramowaniem biurowym oraz jego automatyzacja za pomocą MS Visual
Basic for Application (VBA).
Ćwiczenie 1. (MS WORD – tabele, wykresy)
Na podstawie tabeli wykonać wykres reprezentujący notowania spółki KGHM, a następnie wstawić ten wykres do MS Word. Wykres wykonać poprzez wstawienie obiektu „Wykres Microsoft Graph” do dokumentu MS Word. Dodatkowo obramować wykres grubą krawędzią.
Wykres 1
Poniżej przedstawiono arkusz danych w „Microsoft Graph”.
Ćwiczenie 2. (MS WORD – tabele, wykresy cd.)
Wykorzystując tabelę utworzoną w MS Word wykonać w MS Excel następujący wykres (Wykres 2).
Wykres 2
Ćwiczenie 3. (MS EXCEL – Tabele)
W katalogu imię_nazwisko_grupa utworzyć dokument o nazwie frank.xls. Zmienić nazwę Arkusz1 na: kurs_franka. W MS Excel przygotować tabelę zgodnie z poniższym rysunkiem (Rys.1.).
Rys.1.
Ćwiczenie 4. (MS EXCEL – Sortowanie)
Otworzyć plik z ćwiczenia 3. Za pomocą menu sortować wartość według ceny rosnąco.
Zapisać plik pod nazwą frank_sortowanie.xls.
Ćwiczenie 5. (MS EXCEL – Wykresy)
15/Sep/11 13/Sep/11
09/Sep/11 07/Sep/11
05/Sep/11 01/Sep/11
30/Aug/11 26/Aug/11
24/Aug/11 22/Aug/11
18/Aug/11 16/Aug/11
11/Aug/11 09/Aug/11
05/Aug/11 0
20 40 60 80 100 120 140 160 180 200
Otwarcie Zamknięcie Maksimum Minimum
W dokumencie frank.xls utworzyć nowy arkusz o nazwie wykres_franka przedstawiający zmianę kursu franka. Wykres przygotować zgodnie z rysunkiem 2 (na podstawie arkusza z ćwiczenia 3).
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 0
1 2 3 4 5 6 7 8
Cena minimalna Cena maksymalna
Rys. 2.
Ćwiczenie 6. (MS EXCEL – Nagłówek, stopka)
Wstaw nagłówek (dla arkusza kurs_franka) oraz stopkę. Nagłówek zawiera treści: „Raport z dnia 11.10.2011”. Stopka zawiera treść: „Przygotowany przez imię nazwisko studenta”.
Ćwiczenie 7. (MS EXCEL – Filtrowanie)
Pobrać ze strony Narodowego Banku Polskiego plik Excel zawierające dane: średnich kursów walut obcych w złotych w 2012 roku. Na podstawie powyższego pliku wykonaj filtr danych, tak aby wyświetlić tylko kursy dolara, franka oraz euro.
Ćwiczenie 8. (MS EXCEL – Sortowanie)
Na podstawie pliku z ćwiczenia 7 posortuj dane rosnąco i określ która waluta była najtańsza w styczniu 2012 r. Podobnie wykonaj sortowanie dla innych miesięcy 2012 roku.
Ćwiczenie 9. (MS EXCEL – Formuły, Funkcje)
Na dysku C w katalogu imię_nazwisko_grupa utwórz plik o nazwie funkcje.xls. Zmienić nazwę Arkusz1 na Płace. Formatować arkusz w sposób przedstawiony na rysunku 3.
Wartości razem, średniej płacy i nagrody wylicz stosując odpowiednie funkcje suma(),
średnia() itd.
Rys. 3.
Ćwiczenie 10. (MS EXCEL - Funkcje podstawowe)
Sklep komputerowy osiąga następujące przychody i koszty: sklep pierwszy 21480 zł, sklep drugi 7867 zł, sklep trzeci 6543 zł oraz sklep czwarty 11234 zł. Koszty dla poszczególnych sklepów to odpowiednio: 6543 zł, 3200 zł, 2435 zł., 5321 zł. Pracownik działu księgowego przygotowuje na koniec miesiąca zestawienie kosztów z dochodami porównują koszty z przychodami. Uwzględnia, że podatek wynosi 23 %. Należy przygotować arkusz kalkulacyjny, który uwzględni łączne zyski, przychody i koszty. Arkusz powinien zawierać również średnie koszty, najmniejszy podatek i największe zyski.
Ćwiczenie 11. (MS EXCEL - Funkcje finansowe)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie funkcja_FV.
Zapoznaj się do czego służy funkcja FV (pomoc MS Office). Używając funkcji FV oblicz jaką kwotę można otrzymać deponując w banku kwotę 25000 zł. Zakładamy, że czas przez który utrzymywane są środki finansowe na lokacie to trzy lata, kapitalizacja jest miesięczna, a oprocentowanie wynosi 4,2 % rocznie.
Ćwiczenie 12. (MS EXCEL - Funkcje finansowe)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie funkcja_RATE. Zapoznać się z funkcją RATE (pomoc Microsoft Office). Wykorzystując funkcję RATE obliczyć jakie powinno być oprocentowanie roczne w banku. Zakładamy, że wpłacamy kwotę 7000 zł oraz 500 zł w każdym okresie przez 2 lata. Dodatkowo zakładamy, że chcemy otrzymać kwotę 10000 zł.
Ćwiczenie 13. (MS EXCEL - Funkcje finansowe)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie funkcja_NPER. Zapoznać się z funkcją NPER (pomoc Microsoft Office). Wykorzystując funkcję NPER oblicz przez jaką liczbę miesięcy należy wpłacać do banku po 500 zł, aby oszczędzić 25000 zł? Zakładamy, że ulokowano w banku kwotę 15000 zł, które są oprocentowane 3,4% w skali roku.
Ćwiczenie 14. (MS EXCEL - Funkcje matematyczne)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie funkcja_matematyczne. Zapoznaj się z następującymi funkcjami matematycznymi:
PIERWIASTEK(), POTĘGA(), SILNIA(), LOG(), RZYMSKIE(), ZAOK(), ILOCZYN(), SIN(), ZAOK() (pomoc Microsoft Office). W arkuszu funkcje_matematyczne użyj powyższe funkcje matematyczne dla odpowiednich różnych wartości.
Ćwiczenie 15. (MS EXCEL - Funkcje statystyczne)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie funkcja_statystyczne. W kolumnie C oraz D w wierszach od 1 do 20 wstaw 20 różnych wartości liczbowych. Na wprowadzonych danych zastosuj funkcję MIN(), MAX), ILE.LICZB(), MEDIANA(), NACHYLENIE(), PEARSON(), WYST.NAJCZĘŚCIEJ() Ćwiczenie 16. (MS EXCEL - Argumenty funkcji)
W pliku funkcje.xls utworzonym w ćwiczeniu 9 wstaw nowy arkusz o nazwie argumenty_funkcji. Użyj wszystkich wymienionych typów argumentów: liczb, adresów, zakresów, tekstu, funkcji, formuły dla dowolnych funkcji. (Zobacz DODATEK A)
Ćwiczenie 17 (MS EXCEL - Rodzaje adresowania)
Utwórz nowy plik o nazwie adresowanie, a następnie nowy arkusz o nazwie kopiowanie_formuł. Utwórz formułę wykorzystującą dodawanie, potęgowanie, odejmowanie i dzielenie. Formuła powinna wykorzystać adresowanie bezwzględne.
Następnie skopiuj formułę w inne miejsce arkusza tak aby adresowanie nie uległo zmianie.
Ćwiczenie 18 (MS EXCEL - Rodzaje adresowania)
Utworzyć nowy skoroszyt o nazwie: ceny. Nazwać trzy kolejne kolumny odpowiedni:
NAZWA TOWARU, CENA W ZŁOTOWKACH, CENA W EURO. Wprowadzić siedem dowolnych nazw towarów i przypisać im ceny. W pierwszej komórce kolumny CENA W EURO umieścić formułę przeliczającą ceny w złotówkach na ceny w euro (przyjąć aktualny kurs euro NBP). Skopiować przez przeciągnięcie wzór z pierwszej komórki kolumny CENA W EURO do pozostałych komórek, tak aby wyliczyć ceny w euro dla wszystkich towarów.
Ćwiczenie 19 (MS EXCEL – Formuły tablicowe)
Na dysku C w katalogu imię_nazwisko_grupa utwórz plik o nazwie funkcje_excel.xls.
Zmienić nazwę Arkusz1 na Macierze. Wykorzystując formuły tablicowe i używając funkcji matematycznych MS Excel oblicz iloczyn macierzy. W tym celu utwórz w MS Excel następującą macierz: Macierz X oraz Macierz Y (Rys. 4).
Rys. 4.
Należy nazwać zakres F2:L8 jako X oraz zakres O2:U8 jako Y. Zaznacz zakres X2:AD8
wstaw funkcję matematyczną MACIERZ.ILOCZYN(). Określ jako tablicę 1 macierz X oraz
jako tablicę 2 macierz Y. Pamiętaj aby użyć formuły tablicowej w tym celu użyj SHIFT+CTL+ENTER. Nawias klamrowy odróżnia formuły tablicowe od pozostałych.
Ćwiczenie 20 (MS Excel – Funkcje tekstowe)
Otworzyć plik z ćwiczenia 19 (funkcje_excel.xls) i utworzyć nowy arkusz o nazwie Tekstowe. W dowolnej komórce arkusza wpisz następujące zdanie:
„Nie od dzisiaj wiadomo, że możliwości wykorzystania krzemu do budowy bardziej wydajnych komputerów wkrótce wyczerpią się.”
Wykorzystując funkcję DŁ(tekst) obliczyć długość zdania. Dla powyższego tekstu użyj również inne funkcje tekstowe: LITERY.WIELKIE(), LITERY.MAŁE(), Z.WIELKIEJ.LITERY().
Ćwiczenie 21 (MS Excel – Funkcje logiczne)
Otworzyć plik z ćwiczenia 1 (funkcje_excel.xls) i utworzyć nowy arkusz o nazwie Logiczne.
Zapoznaj się do czego służą funkcje logiczne JEŻELI(), ORAZ(), LUB(). Wykonaj proste testy logiczne dla przykładowych danych.
Ćwiczenie 22 (MS Excel – Funkcje logiczne)
Sklep spożywczy posiada towary w dwóch walutach: złotówki i euro. Należy opracować arkusz kalkulacyjny, który zawierał będzie 5 kolumn nazwanych odpowiednio: „Lp.”,
„Towar”, „Kwota”, „Waluta”, „Cena w złotych”. Zastosować funkcję logiczną JEŻELI(), w celu obliczenia wartości w kolumnie „Cena w złotych”. Test logiczny powinien sprawdzać czy w kolumnie „Waluta” znajduje się tekst „eur”, jeżeli tak to powinna zostać wyliczona kwota w złotówkach w kolumnie „Cena w złotówkach”. Wprowadzić 5 towarów i odpowiadające im ceny, aby sprawdzić działanie arkusza. W kolumnie „Waluta” należy wprowadzić tekst „zł” lub „eur”, odpowiednio w jakiej walucie jest towar.
Ćwiczenie 23 (MS Excel – Funkcje daty i czasu)
Wykorzystując funkcję DATA() oraz prostą formułę odejmowania oblicz liczbę dni między poszczególnymi datami. Podobnie stosując funkcję CZAS() oblicz różnicę czasu w poszczególnych okresach (Rys. 5.).
Rys. 5.
Ćwiczenie 24 (MS Excel – Funkcje logiczne i modułu)
Wykorzystując dane statystyczne dotyczące bezrobocia w poszczególnych województwach wykonaj następującą tabelę oraz wykres. Wartości w kolumnach o nazwie „Przewaga” należy obliczyć stosując funkcję logiczną JEŻELI(), a także funkcję MODUŁ.LICZBY(). W przypadku przewagi pracujących formuła powinna podawać wartość liczbową będącą różnicą wartości liczby bezwzględnej pracujących i liczby bezrobotnych. Natomiast w przypadku przewagi bezrobotnych formuła powinna podawać wartość różnicy liczby bezrobotnych i wartości bezwzględnej liczby pracujących. Należy dokładnie odzwierciedlić kolorystykę i formatowanie tekstu zgodnie z poniższym rysunkiem.
Dodatkowo wykonać wykres reprezentujące powyższe dane w postaci histogramu. Wykres
powinien obrazować liczbę bezrobotnych, liczbę pracujących oraz uwzględniać przewagę
pracujących lub bezrobotnych. W tym celu należy wstawić wykres słupkowy skumulowany
zbliżony do poniższego wykresu:
Ćwiczenie 25
(MS Excel – Baza danych)
W katalogu imię_nazwisko_grupa utworzyć dokument o nazwie bazadanych.xls. Zmienić nazwę Arkusz1 na: Dane. Wprowadzić następujące etykiety pola: Imię, Nazwisko, Miejscowość, Województwo, Adres, Kraj, Uczelnia, Wydział.
Wprowadzić do bazy pięć przykładowych rekordów (bezpośrednie wpisanie rekordów do tabeli bazy danych). W celu przemieszczania się między polami użyć klawisza TAB. Po osiągnięciu ostatniego pola użyć przycisku ENTER.
Ćwiczenie 26 (MS Excel – Baza danych)
Wykorzystując arkusz utworzony w poprzednim ćwiczeniu wykorzystaj formularz baz danych w celu wprowadzenia kolejnych pięciu rekordów do bazy danych. W tym celu otwórz menu Dane oraz wybierz polecenie Formularz. Użyj klawisza TAB oraz ENTER w celu przemieszczania się między polami. Posortuj dane według etykiety pola: Miasto, a następnie przefiltruj dane według danych znajdujących się w polu (wyświetl 10 pierwszych elementów oraz przefiltruj dane według wybranego województwa). Zabezpiecz skoroszyt przed otwarciem za pomocą hasła.
Ćwiczenie 27 (MS Excel – Baza danych)
Przygotuj bazę danych w MS Excel, która następnie zostanie użyta do analizy danych tabelami przestawnymi. Baza ta powinna zostać wykonana podobnie jak w ćwiczeniu 2. Baza powinna zawierać artykuły sklepu komputerowego. Zakładamy, że baza posiada następujące etykiety pola: Numer, Nabywca, Sprzedawca, Cena, Ilość, Kwota, Opis produktu, Data.
Używając polecenia Formularz wprowadzamy przykładowe 5 rekordów do bazy.
Wykorzystując narzędzie tabela przestawna odpowiadamy na poniższe pytania:
Który sprzedawca sprzedaje najwięcej produktów?
Jakie produkty sprzedają się najlepiej?
Kto danego dnia sprzedał największą liczbę produktów?
Ćwiczenie 28 (MS Excel – Baza danych)
Zapoznaj się z funkcjami bazy danych: BD.SUMA(), BD.ILE.REKORDÓW(), BD.MAX(), BD.MIN(), BD.POLE(). Zastosuj powyższe funkcje na danych z ćwiczenia 27.
Ćwiczenie 29 (MS Excel – Baza danych)
Przygotuj arkusz składający się z dwóch kolumn pierwsza kolumna posiada etykietę pola o nazwie LICZBA SZTUK, a druga CENA JEDNOSTKOWA. Wprowadź przykładowe dane, a następnie wykorzystując funkcje SUMA.ILOCZYNÓW() oblicz łączną kwotę zakupów.
Ćwiczenie 30 (MS Visual Basic for Application)
Zarejestruj nowe makro pod nazwą „Warunkowa”. Uruchom edytor MS Visual Basic for Application, a następnie utwórz nowe makro, które będzie stosowało instrukcję warunkową, a następnie wyliczy automatycznie w arkuszu MS Excel dzielenie dwóch liczb. Poniżej znajduje się kod programu:
Sub Warunkowa() If [G5] <> 0 Then [G7] = [G5] / [G4]
Else
MsgBox "Nie można dzielić przez zero"
End If End Sub
Ćwiczenie 31 (MS Visual Basic for Application)
Utwórz arkusz z zawierający w komórkach F1:F20 różne nieuporządkowane liczy. Przygotuj makro, które posortuje wprowadzone uprzednio liczy.
Sub sortowanie()
Range("A1").Sort Key1:=Range("A1"), Order1:= _ xlAscending, Header:=xlGuess, OrderCustom:=6, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub
Ćwiczenie 32 (MS Visual Basic for Application)
Przygotuj makro, które stosowało będzie pętlę typu Do While, poniżej przedstawiono ideowo zasadę działania pętli:
Do While określamy warunek Podajemy instrukcję
Loop
Przykładowo pętlę Do While zastosować możemy do makra, w którym liczby z kolumny E mnożone są przez 5 do momentu napotkania komórki pustej
Sub mnoz() i = 1
Do While Range("A1").Cells(i, 1) <> ""
Range("A1").Cells(i, 1) = 5 * Cells(i, 1) i = i + 1
Loop End Sub
Ćwiczenie 33 (MS Visual Basic for Application)
Przygotuj makro, które zmieni w określonym zakresie komórek arkusza czcionkę na Arial rozmiar 17 pogrubiona. Wprowadź przykładowe dane i sprawdź działanie makra.
Sub zmiana()
Range("D1:G20").Select
Selection.Font.Name = "Arial"
Selection.Font.Size = 17 Selection.Font.Bold = True End Sub
Ćwiczenie 34 (MS Visual Basic for Application)
Uruchom edytor Visual Basic, następnie utwórz nowy UserForm. Dodaj przycisk (CommoButton) poprzez narysowanie go na dowolnym miejscu UserForm. Kliknij dwukrotnie na przycisk i wpisz następujące polecenie:
Private Sub Command1_Click()
a = Msgbox("Przycisnąłeś swój przycisk",,"Info") End Sub
Wybierz właściwości przycisku : Caption – Mój przycisk
Picture – Wskaż dowolny plik graficzny
ControlTipText – Ustaw teskt: Jestem na przycisku
Spróbuj ustawić inne właściwości obiektu przycisk.
Ćwiczenie 35 (MS Visual Basic for Application)
Celem ćwiczenia jest dodanie nowego przycisku menu, który uruchomi odkreślone makro. W tym celu należy zarejestrować nowe makro o nazwie komunikat, a następnie utworzyć nowy o następującym kodzie:
Private Sub Command1_Click()
a = Msgbox("To jest komunikat przycisku menu",,"Info") End Sub
Następnie należy kliknąć w menu Widok Paski narzędzi Dostosuj następnie wybrać kartę Polecenia i wybrać Makro. Należy przeciągnąć przycisk niestandrdowy do dowolnego menu.
Otworzyć menu w którym umieściliśmy przycisk makra, a nastepnie przypisać makro komunikat do przycisku z menu.
Ćwiczenie 36 (MS Visual Basic for Application)
Uruchom edytor Visual Basic, następnie utwórz nowy UserForm. Dodaj przycisk (CommoButton) poprzez narysowanie go na dowolnym miejscu UserForm. Kliknij dwukrotnie na przycisk i wpisz następujące polecenie:
Private Sub CommandButton1_Click()
S = InputBox("Musisz określić swoją prowizję") MsgBox ("Prowizja") & (35 * S)
End Sub
Uruchom makro nastpnie wpisz wartośc prowizji i zobacz efekt działania makra.
Ćwiczenie 37 (MS Visual Basic for Application)
W nowo utworzonym arkuszu kalkulacyjnym wstaw przycisk uruchamiający makro wyświetlające komunikat. W tym celu użyj ikonę przycisk z paska narzędzi formularze.
Następnie przypisz temu przyciskowi makro o następującej treści:
Private Sub Command1_Click()
a = Msgbox("To jest przycisk wewnątrz arkusza",,"Info") End Sub
Ćwiczenie 38 (MS Visual Basic for Application)
Utwórz nowy arkusz kalkulacyjny, a następnie wprowadź kilka przykładowych formuł np.
=SUMA(A1:A4). Wstaw przycisk uruchamiający makro, a następnie przypisz kod wyświetlający formuły w arkuszu kalkulacyjnym. Kod programu jest następujący:
Sub Wyswietlaj()
ActiveWindow.DisplayFormulas = True End Sub
Zmień wartość True na False i ponownie sprawdź efekt działania przycisku.
Wstaw nowy przycisk który będzie wyświetlał lub ukrywał formuły. W tym celu dodaj nowy przycisk o nazwie widok, któremu przypisz następujący kod:
Sub Wyswietlaj()
Widok = ActiveWindow.DisplayFormulas ActiveWindow.DisplayFormulas = Not Widok End Sub
