23
Ćwiczenia z Excela
opracowanie: mgr inż. Wojciech Sokołowski
Temat: Przygotowanie arkusza do pracy
Pierwszą czynnością w realizacji naszego projektu będzie zaplanowanie układu skoroszytu: z ilu arkuszy ma się składać i jak każdy z nich ma się nazywać.
W naszym przypadku przygotujemy po jednym arkuszu dla każdego roku i nadamy każdemu z nich nazwę Rok rrrr (gdzie rrrr oznacza rok) oraz dodatkowy arkusz dla zestawienia zbiorczego o nazwie Lata 2000-2005. Łącznie potrzebujemy, więc 7 arkuszy z odpowiednio zdefiniowanymi nazwami.
Ilość arkuszy w skoroszycie rzadko zgadza się z naszymi potrzebami. Możemy ją zmienić poprzez:
· usunięcie zbędnych arkuszy;
· wstawienie brakujących arkuszy;
· zdefiniowanie domyślnej ilości arkuszy w skoroszycie i utworzenie nowego skoroszytu.
Jeżeli nasz skoroszyt ma więcej arkuszy niż potrzebujemy, to najprostszą metodą jest usunięcie zbędnych arkuszy. w tym celu musimy zaznaczyć arkusze przeznaczone do usunięcia klikając na myszką na zakładce z nazwą każdego arkusza, który chcemy usunąć trzymając przy tym wciśnięty klawisz Ctrl lub przejść do pierwszego usuwanego arkusza i następnie trzymając wciśnięty klawisz Shift kliknąć myszką na zakładce ostatniego przeznaczonego do usunięcia arkusza. W wyniku tej operacji powinny podświetlić się na biało wszystkie zakładki zaznaczonych arkuszy. Wygląda to przykładowo tak:
Po zaznaczeniu zbędnych arkuszy (jest przy tym obojętne czy będą to początkowe, środkowe, czy też końcowe arkusze) usuwamy je korzystając z opcji menu: Edycja | Usuń arkusz. W ten sposób otrzymujemy skoroszyt z dokładnie taką ilością arkuszy, jakiej potrzebujemy.
Jeżeli nasz skoroszyt ma mniej arkuszy niż potrzebujemy, to należy dołożyć brakujące arkusze korzystając z opcji menu: Wstaw | Arkusz. Operację tą powtarzamy tyle razy, aż ilość arkuszy w naszym skoroszycie wyniesie dokładnie siedem.
Otwieramy skoroszyt ze zdefiniowaną ilością arkuszy
Jeżeli nie chcemy dokładać ani usuwać ręcznie arkuszy z naszego skoroszytu, ale wolimy, aby nowy skoroszyt otworzył się od razu z żądną ilością arkuszy, to musimy zmienić domyślne opcje programu Excel dla nowych skoroszytów.
W tym celu musimy wejść w menu Narzędzia | Opcje... w otwartym oknie dialogowym wybrać zakładkę Ogólne i w polu Liczba arkuszy w nowym skoroszycie ustawić wartość 7 tak, jak to pokazuje poniższa ilustracja:
Teraz zostaje nam już tylko zatwierdzić zmienione ustawienie, a następnie otworzyć nowy skoroszyt. Będzie on miał dokładnie 7 arkuszy.
Kiedy mamy już przygotowany skoroszyt z odpowiednią ilością arkuszy, to musimy każdemu z nich nadać odpowiednią nazwę: ułatwia to poruszanie się między arkuszami i jest o wiele bardziej czytelne niż standardowe nazwy Arkusz1, Akusz2, itd.
Najprostszą metodą jest dwukrotne kliknięcie myszką na zakładce arkusza, któremu chcemy zmienić nazwę i wpisanie odpowiedniej nazwy z klawiatury. Nazwę zatwierdzamy klawiszem Enter lub kliknięciem myszki w dowolnym miejscu arkusza. Tą metodę przedstawia poniższa ilustracja:
Dla osób, które wolą korzystać z menu programu istnieje druga metoda: wybranie z menu opcji Format | Arkusz | Zmień nazwę a następnie postępujemy identycznie, jak w poprzednim przypadku.
Kiedy mamy już przygotowany skoroszyt należy zapisać go na dysku, aby nie utracić efektów własnej pracy. W kolejnych lekcjach będziemy wracali do tego skoroszytu. Musimy, więc zachować go w wybranym przez nas folderze pod zdefiniowaną przez nas nazwą. Zapisu skoroszytu na dysk możemy dokonać kilkoma sposobami:
· korzystając z klawiaturowego skrótu Ctrl+S
· korzystając z ikony zapisu skoroszytu
· wybierając z menu opcję Plik | Zapisz lub | Zapisz jako...
W oknie dialogowym zapisu pliku musimy określić folder, w którym plik ma być zapisany, podać nazwę pliku ze skoroszytem (rozszerzenia nie musimy wpisywać, gdyż Excel automatycznie nadaje plikom skoroszytów rozszerzenie XLS). Należy jednak zwrócić uwagę na typ zapisywanego pliku: Excel może zapisywać dane w różnych formatach, ale my musimy wybrać format Skoroszyt Microsoft Excel (*.xls).
Temat: Tworzenie tytułów i nagłówków. Formatowanie
Teraz przystępujemy do przygotowania nagłówków kolumn. W komórce B2 powinien znaleźć się napis Sklep nr 1, w C2 - Sklep nr 2 ... w F2 - Sklep nr 5. Moglibyśmy wprowadzać kolejno wszystkich 5 napisów. Skorzystamy jednak z właściwości Excela polegającej na tworzeniu numerowanych list podczas przeciągania pojedynczej komórki, której zawartość jest tekstem zaczynającym się lub kończącym numerem. Nasz napis wprowadzimy tylko do komórki B2, a następnie "przeciągniemy" ją do komórki F2. Wynik tej operacji pokazuje poniższa ilustracja.
Poza listami numerowanymi w Excel'u możemy korzystać również z list tworzonych z dowolnych tekstów. Predefiniowanymi listami są pełne i skrócone nazwy dnia tygodnia i miesięcy. Możemy tworzyć również dodatkowe, własne listy. Dla potrzeb naszego zadania stworzymy listę kwartałów, abyśmy nie musieli wpisywać ich wszystkich ręcznie. Listy te pamiętane są razem z Excel'em, a nie z arkuszem. Możemy więc korzystać z raz utworzonej listy w każdym nowym skoroszycie.
Aby utworzyć (ewentualnie zmodyfikować) listę musimy z menu Narzędzia | Opcje... wybrać zakładkę Listy. Jeżeli chcemy edytować istniejącą listę, to musimy ją zaznaczyć w okienku Listy niestandardowe i w okienku Lista wpisów zmodyfikować listę: dodać nową pozycję, usunąć istniejącą lub zmienić tekst wybranej pozycji.
Chcąc stworzyć nową listę w okienku Listy niestandardowe zaznaczamy opcję NOWA LISTA, a w okienku Lista wpisów wprowadzamy kolejne jej elementy. Po zakończeniu wprowadzania wszystkich elementów naszej listy dopisujemy ją do już istniejących poleceniem Dodaj. Od tej chwili z nowej listy możemy korzystać w bieżącym skoroszycie, jak też we wszystkich, które otworzymy lub utworzymy nowe.
Sposób stworzenia listy kwartałów pokazuje poniższa ilustracja:
Korzystanie z listy polega na wpisaniu jej dowolnego elementu (nie koniecznie pierwszego) do wybranej komórki arkusza, a następnie przeciągnięciu komórki na żądany obszar (tylko w tym samym wierszu lub tej samej kolumnie - nie można jednocześnie wypełniać wierszy i kolumn). Excel wypełni zaznaczony obszar kolejnymi elementami z listy. Nie jest przy tym istotne czy element wpiszemy taką samą wielkością liter jak w liście. Jeżeli tylko tekst zgadza się z którymkolwiek elementem listy, to Excel utworzy listę zachowując nasze zasady pisowni (wszystkie litery małe, duże lub 1-sza duża, a reszta małe) niezależnie od tego, jak rzeczywiście zapisane są elementy w liście. Jeżeli zaznaczymy obszar większy od ilości elementów na liście, to będą one powtarzane cyklicznie.
Znając już sposób korzystania z list oraz mając przygotowaną listę z kwartałami przystępujemy do opisu wierszy naszej tablicy. Zrealizujemy to wykonując kolejno następujące czynności:
do komórki A3 wpisujemy tekst styczeń
przeciągamy komórkę A3 na obszar A3:A5
do komórki A6 wpisujemy tekst I kwartał
zaznaczamy obszar A3:A6
rozciągamy go na obszar A3:A18
Sposób wykonania tego zadania pokazuje poniższa ilustracja:
ten sposób mamy już prawie kompletnie opisaną naszą tabelę. Brakuje jeszcze tylko tekstów opisujących kolumnę z sumami miesięcy i kwartałów oraz wiersza z podsumowaniem roku. Te elementy musimy już jednak wpisać ręcznie. Do komórki G2 wprowadzamy tekst Razem, a do komórki A19 - Rok.
Na tym kończymy opisywanie wierszy i kolumn tabeli tekstami.
Przed zakończeniem lekcji zachowujemy skoroszyt !!!
Temat: Tworzenie tytułów i nagłówków
Przed przygotowaniem formuł sumujących dane oraz sporządzeniem wykresów dobrze jest przygotować sobie dane, które już w momencie wpisywania formuł i tworzenia wykresów będą nam pokazywały efekt swojego działania. Można w ten sposób na bieżąco kontrolować poprawność stworzonych wzorów oraz oglądać próbne wyniki obliczeń, które - choć nie są rzeczywistymi danymi - znacznie ułatwiają choćby formatowanie wyświetlania liczb, ustawianie szerokości kolumn, wielkości czcionek i wiele innych wykonywanych czynności.
W naszym przypadku danymi próbnymi będą obroty każdego ze sklepów w kolejnych miesiącach roku. Można je mozolnie wpisywać komórka po komórce, ale jest to dość pracochłonne: 5 sklepów x 12 miesięcy daje 60 liczb do wpisania. Prostszą metodą będzie wprowadzenie losowej liczby do jednej z komórek, a następnie powielenie jej do wszystkich pozostałych.
Wprowadzanie danych próbnych rozpoczniemy od komórki B3: obroty 1-szego sklepu za styczeń 2000 roku.
Dane próbne - choć nie odpowiadają rzeczywistości - muszą spełniać pewne wymogi prawdopodobieństwa. W naszym przypadku zakładamy, że miesięczne obroty każdego ze sklepów są rzędu 30.000 do 50.000 zł. Dodatkowo przyjmujemy, że liczby powinny być wyświetlane bez miejsc po przecinku, gdyż przy tego rzędu obrotach grosze są wartością nieistotną: w zestawieniach i na wykresach wymagają dodatkowych 3 miejsc do wyświetlenia, nie mają natomiast istotnego wpływu na porównywane wartości.
Do wygenerowania wartości losowej użyjemy funkcji wbudowanej LOS(), która generuje liczby z zakresu od 0 do 1. Aby otrzymać liczbę z założonego przez nas przedziału 30.000 - 50.000 zł pomnożymy wylosowaną liczbę przez 20.000 (da nam to 20-tysięczny zakres losowanych liczb) i do otrzymanego wyniku dodamy 30.0000 (aby minimalna wylosowana wartość była równa co najmniej 30.000), a następnie wynik zaokrąglimy do części całkowitej korzystając z funkcji ZAOKR(liczba, liczba_cyfr).
Wzór formuły możemy wpisać do komórki B3 samodzielnie. Będzie on miał wtedy postać:
=ZAOKR(LOS()*20000+30000;0)
Podczas wpisywania formuł w arkuszu kalkulacyjnym należy przestrzegać kilku reguł, które uchronią nas przed spowodowaniem błędu i odrzuceniem formuły przez Excel'a lub jej zniekształceniem:
· wprowadzanie formuły zawsze rozpoczynamy znakiem równości (=)
· parametry funkcji podajemy w nawiasach okrągłych bezpośrednio za nazwą funkcji
· jeżeli funkcja nie ma parametrów (np. LOS()), to wstawiamy za jej nazwą parę pustych nawiasów.
· jeżeli funkcja posiada kilka parametrów, to kolejne oddzielamy średnikiem (;)
· argumentem funkcji może być liczba, napis, adres komórki (obszaru) zawierającego liczbę lub napis lub też inna funkcja; jedynym kryterium jest zgodność typu wpisywanego argumentu z typem wymaganym przez funkcję
· literały użyte jako argumenty należy zamykać znakami cudzysłowu (")
· wewnątrz formuły - poza literałami - nie wolno używać znaków odstępu; mimo iż często formuły są nieczytelne, to "uczytelnienie" ich przez wstawienie znaków spacji zawsze generuje błąd .
Możemy również skorzystać z kreatora funkcji wybierając z menu Wstaw | Funkcja... lub uruchomić kreator funkcji ikoną ze standardowego paska narzędzi. Kreator funkcji poprowadzi nas przez kolejne etapy budowy formuły objaśniając typ i znaczenie kolejnych parametrów oraz pokazując spodziewany wynik.
Po przygotowaniu pierwszej liczby możemy przystąpić do powielenia jej we wszystkich komórkach, w których powinny znaleźć się dane za I kwartał. Rozpoczynamy od skopiowania komórki B3. Możemy to zrobić używając skrótu klawiaturowego Ctrl+C, wybierając z menu Edycja | Kopiuj lub korzystając z ikony kopiowania
Można teraz wkleić skopiowaną formułę. W tym celu zaznaczamy obszar B3:F5 i wklejamy skopiowaną wcześniej formułę korzystając ze skrótu klawiaturowego Ctrl+V, wybierając z menu Edycja | Wklej lub korzystając z ikony wklejania
Możemy też użyć do powielenia wprowadzonej formuły metody przeciągania. Najpierw komórkę B3 przeciągamy do komórki F3, a następnie cały obszar B3:F3 rozciągamy na obszar B3:F5. Metodę tą ilustruje poniższy rysunek:
Kolejnym krokiem będzie powielenie danych I-szego kwartału na kolejne kwartały, ale z pominięciem wierszy przeznaczonych na podsumowania. W tym celu zaznaczamy wszystkie dane z I kwartału: obszar B3:F5, a następnie go kopiujemy. Teraz ustawiamy się w komórce B7 i wklejamy skopiowane komórki.
Wystarczy ustawić się w komórce B7 bez potrzeby zaznaczania całego obszaru B7:F9, gdyż Excel sam dobiera sobie potrzebną ilość komórek, aby wkleić zaznaczony obszar: rozciąga go od komórki bieżącej odpowiednio w dół i w prawo.
Czynność wklejania powtarzamy ustawiając się kolejno w komórkach: B11 i B15. W ten sposób mamy przygotowane dane dla wszystkich miesięcy roku.
Sposób wykonania tego zadania prezentuje poniższa ilustracja:
Zwróćmy uwagę na poprzednie przykład: przy każdym kolejnym przeciągnięciu zawartość wszystkich komórek ulega zmianie. Wynika to z faktu, że każda zmiana czegokolwiek w arkuszu powoduje automatyczne przeliczenie wszystkich jego komórek. Ponieważ wprowadziliśmy do komórek wartości losowe, więc po każdej zmianie wszystkie liczby losowane są ponownie. Powoduje to niemiłe wrażenie migotania ekranu.
Kolejnym naszym działaniem będzie zmiana wprowadzonych formuł na odpowiadające im liczby, które nie zmieniałyby się przy zmianie czegokolwiek w arkuszu. Pozwoli to nam na spokojne wykonywanie kolejnych czynności bez koncentrowanie się na migających zmianach ekranu.
Wykonamy to kopiując cały obszar zapełniony liczbami losowymi (B3:F17) sam na siebie, ale ni zwykłym kopiowaniem, ale wykorzystując funkcje menu Edycja | Wklej specjalnie....
Zaznaczamy obszar B3:F17, kopiujemy go, a następnie w oknie dialogowym otwartym po wybraniu z menu Edycja | Wklej specjalnie... zaznaczamy opcję Wklej - Wartości | Operacja - Brak.
W ten sposób z naszego arkusza znikną wszystkie formuły, a ich miejsce zajmą wklejone, widziane ostatnio liczby.
Po wykonaniu wszystkich opisanych w tej lekcji zadań mamy przygotowane dane do testowania wprowadzanych w kolejnych lekcjach formuł oraz formatowania liczb w tabeli i samej tabeli.
Temat: Obliczenia w arkuszu
Mamy już przygotowane dane próbne. Teraz przygotujemy wzory do obliczania sum w wierszach oraz w kolumnach dla każdego kwartału i całego roku. Każda z sum powinna być liczona tylko wtedy, gdy wszystkie dane do jej wyliczenia są zapisane w arkuszu. Dlatego użyjemy formuł warunkowych: wynik będzie wyświetlany tylko w przypadku kompletnych danych, a gdy zabraknie choć jednej liczby, to komórka z sumą niczego nie wyświetli.
Pierwszą formułę musimy zawsze wpisać albo ręcznie, albo korzystając z kreatora funkcji
Wzór formuły możemy wpisać do komórki G3 samodzielnie. Będzie on miał wtedy postać:
=JEŻELI(ILE.LICZB(B3:F3)=5;SUMA(B3:F3);"")
W ten sposób mamy wyliczone obroty sklepów w styczniu. Pojawią się one tylko wtedy, gdy będą wprowadzone dane dla wszystkich sklepów.
Suma obrotów dla pozostałych miesięcy i kwartałów
Sumy dla pozostałych miesięcy, dla kwartałów i dla roku obliczana jest według identycznego wzoru. Różnisię jedynie numerami wierszy, dla których liczymy sumę. Skorzystamy z możliwości automatycznego przeliczania adresów względnych podczas kopiowania formuł: zawartoć komórki G3 przeciągniemy do komórek G4:G19 uzyskując w ten sposób wszystkie potrzebne sumy. Metodę wykonania tego ilustruje poniższy rysunek:
Suma obrotów sklepów za I kwartał
Wzór formuły możemy wpisać do komórki B6 samodzielnie. Będzie on miał wtedy postać:
=JEŻELI(ILE.LICZB(B3:B5)=5;SUMA(B3:B5);"")
a następnie p...
andziulla