POWRÓT

SEMESTR VI

Program Excel a Visual Basic for Applications (VBA)


Spis treści rozdziału - tutaj kliknij

Składniki Excela
Dane w Excelu
Zasady stosowania formuł
Formuły tablicowe

Źródła - Excel 2013 Pl, Programowanie VBA - John Walkenbach, VBA dla Excela 2010 Pl - Witold Wrotek, Excel 2013 Pl, programowanie w VBA dla Bystrzaków.


Pliki do testów


 

Składniki Excela

   

    Excel to najpopularniejszy i najczęściej używany arkusz kalkulacyjny na świecie. Jest częścią pakietu Microsoft Office, który umożliwia nam wpisywać liczby, teksty oraz wykonywać obliczenia według rozmaitych wzorów. Oprócz spełniania podstawowych funkcji, czyli umożliwienia wprowadzania danych i wykonywania prostych działań arytmetycznych, excel wyposażony jest w narzędzia graficzne, funkcje matematyczne, statystyczne, finansowe i inne dodatki pozwalające na tworzenie wykresów, złożonych analiz i zestawień.
Pliki programu Microsoft Excel, noszą nazwę skoroszytów. Skoroszyt może składać się z jednego lub kilku arkuszy roboczych, ich liczbę może ustalać użytkownik. Arkusz jest podzielony na kolumny i wiersze, które są odpowiednio nazwane: kolumny kolejnymi literami alfabetu (A, B, C, ..., Z, AA, AB, AC, ..., IV), wiersze kolejnymi liczbami (1, 2, 3, ..., 65536). Na ekranie monitora widoczna jest zazwyczaj tylko niewielka część arkusza.
Podstawowym elementem arkusza jest komórka, której niepowtarzalny adres powstaje z połączenia nazwy kolumny i wiersza, na przecięciu których się ona znajduje.
Podczas tworzenia aplikacji w Excelu pomaga myślenie o Excelu w kategoriach obiektów, czyli składników arkusza kalkulacyjnego, którymi można manipulować ręcznie bądź za pośrednictwem makar. Przykładami obiektów są;

  • sam program Excel,
  • skoroszyt programu Excel,
  • arkusz zawarty w skoroszycie,
  • zakres komórek zdefiniowany w arkuszu,
  • wykres osadzony na arkuszu danych,
  • seria danych prezentowana na wykresie,
  • wybrany punkt danych serii prezentowanej na wykresie.

Okno arkusza kalkulacyjnego Excel 2007

Rys. 1 - elementy okna Excel 2007

  • Wstążka - wyświetla karty i związane z kartą grupy przycisków,
  • Pasek tytułu - zawiera nazwę aplikacji (Microsoft Excel 2007) oraz standardowa nazwę skoroszytu Zeszyt1,
  • Przyciski sterujące oknem aplikacji to: Minimalizuj, Przywróć, Zamknij. Wybór przycisku Zamknij zakończy prace z programem Excel,
  • Przyciski sterujące oknem skoroszytu - dotyczą tylko okna aktywnego skoroszytu. Prace w nowym skoroszycie można rozpocząć wybierając ze Standardowego paska narzędzi przycisk Nowy,
  • Pasek narzędzi - ułatwia wywoływanie najczęściej wykonywanych funkcji. Obok wskazanego przycisku pojawia sie opis jego działania,
  • Pasek menu - zawiera tytuły poszczególnych list, z których każda mieści określony zestaw powiązanych tematycznie poleceń,
  • Pole nazwy - wskazuje adres aktywnej (zaznaczonej) komórki, wielkość lub nazwę zaznaczonego zakresu, czyli grupy komórek,
  • Pasek formuły - umożliwia wprowadzanie i odczytywanie zawartości poszczególnych komórek arkusza,
  • Zakładka bieżącego arkusza oraz zakładki pozostałych arkuszy - standardowo Excel przydziela każdemu nowotworzonemu skoroszytowi trzy arkusze o nazwach, rozpoczynających sie od słowa Arkusz, ale zarówno ich ilość jak i nazwy można dostosować do własnych potrzeb. Aby zmienić nazwę arkusza wystarczy kliknąć dwukrotnie jego zakładkę, wpisać własna nazwę i nacisnąć Enter lub kliknąć dowolna komórkę,
  • Przyciski przewijania arkusz - korzystamy z nich wtedy, gdy w skoroszycie jest taka liczba arkuszy, że ich nazwy nie mieszczą się jednocześnie na monitorze,
  • Pasek stanu - pokazuje informacje o stanie arkusza: Gotowy, Wprowadź dane, Edycja, jak również wyświetla wyniki prostych obliczeń na danych z zaznaczonych komórek arkusza.

Nowsze wersje Excela

    Nowsze wersje to; Excel 2010 i Excel 2013. Każda z nowych wersji rózni się od opisanej wyżej wersji Excel 2007. Różnice są następujące. Wersja Excel 2010 ma wprowadzone nowe funkcje i mechanizmy (takie jak na przykład wykresy przebiegu w czasie) i ulepszoną ogólną wydajność Excela.
    Wersja Excel 2013, jest dostępna również w wersji sieciowej (za pośrednictwem przeglądarki internetowej) i może być używany na urządzeniach mobilnych, działających pod kontrolą systemu Windows RT. Wersja ta nadal wykorzystuje Wstążkę, ale jej wygląd został znacząco zmieniony.
    W 2015 r. koncern Microsoft ma w planie wydać nowy udoskonalowny pakiet biurowy - Word, Excel i PowerPoint - przeznaczony do systemu Windows 10. W nowym pakiecie Excel dla Windows 10 został zoptymalizowany pod kątem technologii dotykowej, a to oznacza, że użytkownik za pomocą odpowiednich gestów na ekranie może między innymi zmieniać rozmiar poszczególnych komórek czy format arkuszy.

Do góry


 

Dane w Excelu

   

    Program Excel rozróżnia trzy typy danych:

  • Etykiety - tak określa sie wpisywany tekst: tytuł tabeli, nagłówki kolumn i wierszy. Jeżeli wpisywana etykieta jest dłuższa niż szerokość komórki, to jej treść zostaje przedłużona na następna komórkę sprawiając wrażenie, że jest ona zajęta. Jeżeli komórka obok nie jest wolna, to treść wpisywanej etykiety wyda sie pozornie skrócona, w ten sposób, że znaki nie mieszczące się w obrębie bieżącej komórki nie zostaną wyświetlone. Etykiety tekstowe są standardowo wyrównywane do lewej krawędzi komórki.
  • Liczby - są to wartości liczbowe wprowadzone do komórki. Cześć całkowita liczby od dziesiętnej należy oddzielać przecinkiem lub kropką z klawiatury numerycznej. Jeśli zostanie wstawiona kropka z klawiatury podstawowej, to wartość będzie potraktowana, jako tekst. Najprostszym sposobem rozpoznania, czy dane są wprowadzone prawidłowo, jest kontrolowanie sposobu wyrównywania. Liczby standardowo są wyrównywane do prawej krawędzi komórki.
  • Wzory (formuły) - są to wszelkie zapisy złożone z liczb, adresów komórek, operatorów arytmetycznych i specjalnych wbudowanych funkcji.

Wprowadzanie danych

    Wprowadzanie danych w Excelu jest całkiem proste. Program interpretuje zawartość każdej komórki jako jeden z następujących danych;

  • wartość liczbowa (w tym data i czas),
  • tekst,
  • wartość logiczna (prawda lub Fałsz),
  • Formuła.

  Dane wprowadza się bezpośrednio w arkuszu lub powyżej obszaru roboczego w wierszu wprowadzania na pasku formuły - rys. 2 - żółte zaznaczenie. Aby zatwierdzić wprowadzone dane, należy nacisnąć klawisz Enter. Aby wycofać się z wprowadzania zmian, można nacisnąć klawisz Escape. W celu zatwierdzania lub anulowania wpisu można posłużyć się także przyciskami Anuluj i Akceptuj (Wpis), które w momencie wprowadzania pojawiły się na pasku formuły.

Rys. 2 - arkusz z wprowadzonym tekstem, liczbami i formułami

Klawisze przydatne przy wprowadzaniu danych

  • Esc - wycofanie się z wpisywania lub modyfikacji danej komórki,
  • Delete - usuwanie zawartości całej komórki (gdy jest aktywna) i całego zaznaczonego zakresu,
  • F2 - rozpoczęcie edycji zawartości w komórce (poszczególnych znaków),
  • Ctrl + górny apostrof - przepisanie zawartości z komórki powyżej,
  • Ctrl + średnik - wprowadzenie aktualnej daty.

Do góry


 

Zasady stosowania formuł

   

    Prawie każda użyteczna aplikacja oparta na arkuszu kalkulacyjnym korzysta z formuł. Formuła to wzór, według którego następuje obliczenie wartości. Formuła wprowadzona do komórki może składać się z następujących elementów.

  • operatorów działań takich jak znak + (dodawanie) czy * (mnożenie),
  • odwołań do komórek (w tym komórek i zakresów posiadających nazwy),
  • liczb lub łańcuchów tekstowych,
  • funkcji arkusza (takich jak na przykład SUMA czy ŚREDNIA).

Przykłady działań na operatorach
+ dodawanie np. =A1+C1,
- odejmowanie np.=B1-180,
* (gwiazdka) mnożenie np. =A1*B8,
/ dzielenie np. =B5/D12,
^ (daszek) potęgowanie np. =C5^3,
& (ampersand ) łączenie (konkatenacja) tekstów.

Przykłady stosowania formuł - formuła może zawierać

  • adresy np. =E10 $E$8 (adres może mieć różną postać)
  • liczby np. =E20/180,
  • teksty np. = "Pani/Pan "&B5& " " &C5 (np. gdy B5 i C5 jest wpisane imię i nazwisko),
  • funkcje np. =SUMA(B12:B20).

    Formuła może składać się maksymalnie z 8192 znaków. Po zakończeniu wprowadzania formuły, w komórce pojawia się wynik jej działania, a sama formuła pojawia się na pasku formuły po uaktywnieniu komórki. Działania w formule wykonywane są zgodnie z matematyczną kolejnością (np. najpierw mnożenie, potem dodawanie). Przy działaniach o tym samym priorytecie (np. mnożenie i dzielenie) działania wykonywane są od lewej do prawej strony formuły.

Odwołania do komórki lub zakresu

    Większość formuł odwołuje się do jednej lub kilku komórek. Odwołanie takie może być wykonane przy użyciu adresu lub nazwy komórki bądź zakresu. Zasadniczą częścią formuł są adresy komórek, w których pobierane są dane do przeliczania. Adresy te mogą (ale nie muszą) się zmieniać, gdy zawartość komórki jest kopiowana do innych komórek.
Możemy wyróżnić cztery typy odwołań do komórek;

  • Względne - tego typu odwołanie jest całkowicie względne. Po skopiowaniu formuły odwołanie do komórki zostanie dopasowane do jej nowej lokalizacji. Przykład odwołania A1,
  • Bezwzględne - tego typu odwołanie jest całkowicie bezwzględne. po skopiowaniu formuły odwołanie do komórki nie ulegnie zmianie. Przykład - $E$8.
  • Bezwzględne do wiersza - tego typu odwołanie jest częściowo bezwzgledne. Po skopiowaniu formuły część kolumnowa zostanie dopasowana, natomiast część wierszowa nie ulegnie zmianie. Przykład - E$8.
  • Bezwzględne do kolumny - tego typu odwołanie jest częściowo bezwględne. Po skopiowaniu formuły część wierszowa zostanie dopasowana, natomiast część kolumnowa nie ulegnie zmianie. Przykład odwołania - $E1.

    Domyślnie wszystkie odwołania do komórek i zakresów są względne. Aby zmienić typ odwołania, konieczne jest ręczne dodanie symbolu dolara ($). Innym sposobem zmiany rodzaju odwołania jest umieszczenie w trakcie edytowania formuły kursora przy adresie komórki i kolejne naciskanie klawisza F4 do momentu wybrania żądanego rodzaju odwołania.

Odwołania do innych arkuszy i skoroszytów

    Aby odwołać się do komórki znajdującej się w innym arkuszu, należy poprzedzić odwołanie nazwą arkusza i znakiem wykrzyknika, na przykład.

=Arkusz2!A1+1

Można również tworzyć formuły odwołujące się do komórek znajdujących się w innym skoroszycie. Aby to zrobić, należyodwołanie poprzedzić nazwą skoroszytu (podaną w nawiasach kwadratowych), nazwą arkusza i znakiem wykrzyknika, na przykład.

='[Budżet.xls]Arkusz1!A1'

Jeżeli skoroszyt jest zamknięty, nalezy w odwołaniu wpisać jego pełną ścieżkę, na przykład.

='C:\Budżet\Dokumenty Excel\[Budżet.xls]Arkusz1!A1'

Do góry


 

Formuły tablicowe

   

    Tablica jest zbiorem komórek lub wartości przetwarzających grupowo. W programie Excel te elementy mogą znajdować się w jednym wierszu (nazywanym poziomą tablicą jednowymiarową), w kolumnie (pionowej tablicy jednowymiarowej) lub w wielu wierszach i kolumnach (tablica dwuwymiarowa). Za pomocą formuł tablicowych można wykonywać złożone zadania, takie jak:

  • zliczanie znaków znajdujących się w zakresie komórek,
  • sumowanie tylko liczb spełniających określone kryteria, takich jak najmniejsze wartości w zakresach lub liczby należące do przedziału określonego przez górną i dolną granicę,
  • umowanie co n-tej wartości w zakresie wartości.

UWAGA - formuły tablicowe są czasami nazywane "formułami CSE", ponieważ aby wprowadzić je do skoroszytu, należy nacisnąć klawisze CTRL+SHIFT+ENTER.

    Formuła tablicowa to formuła, która może wykonywać wiele obliczeń na jednym lub większej liczbie elementów w tablicy. Formuły tablicowe mogą zwracać wiele wyników lub jeden wynik. Można na przykład umieścić formułę tablicową w zakresie komórek i używać jej do obliczania sum częściowych kolumn lub wierszy. Formułę tablicową można także umieścić w pojedynczej komórce, a następnie obliczać pojedynczą wartość. Formuła tablicowa znajdująca się w wielu komórkach nosi nazwę formuły wielokomórkowej, a formuła tablicowa znajdująca się w jednej komórce - formuły jednokomórkowej.

Przykład dla formuły wielokomórkowej

    Aby dodać wartości w tablicy (zakres od C3 do D12), zaznacz komórki od E3 do E12, a następnie na pasku formuły wprowadź następującą formułę; =C3:C12+D3:D12 i naciśnij klawisze CTRL+SHIFT+ENTER. Program Excel otoczy formułę nawiasami ({ }) i umieści wystąpienie formuły w każdej komórce wybranego zakresu. Stanie się to bardzo szybko, a w kolumnie E będą widoczne łączne sumy kolumny C i D (rys. 3).

Rys. 3 Formuła wielokomórkowa

Przykład dla formuły jednokomórkowej

    W komórce skoroszytu C13 wpisujemy wyraz Razem. W komórce E13 wpisujemy formułę =suma(C3:C12+D3:D12), a następnie naciśnij klawisze CTRL+SHIFT+ENTER. W tym przypadku program Excel dodaje wartości w tablicy (zakres komórek od C3 do D12), a następnie używa funkcji SUMA, aby dodać sumy do siebie. Wynikiem jest suma końcowa o wartości 54978,17 zł.

Rys. 4 Formuła jednokomórkowa

Używanie formuł tablicowych zamiast formuł jednokomórkowych ma nastepujące zalety;

  • formuły tablicowe z reguły zajmują mniej pamięci,
  • znacznie poprawiają wydajność obliczen,
  • pozwalają wyeliminować formuły pośrednie,
  • umożliwiają wykonywanie operacji, które w przeciwnym razie byłyby trudne do zrealizowania lub wręcz niemożliwe.

Do góry


 

 (C) 2011 - 2018 Wydział Przyrodniczo - Techniczny KPSW. All Rights Reserved