źródło: http://www.pszyperski.republika.pl/index.htm
1. Wstęp
Kurs podzielony jest na lekcje, dostęp do każdej z lekcji możliwy jest poprzez kliknięcie jej nazwy w Spisie Treści.
Materiał został omówiony tak prosto jak tylko było to możliwe, a wszystkie przeprowadzane operacje są pokazane na obrazkach.
Strony zostały stworzone tak by zawierały dużo treści bez zbędnej grafiki, tła czy elementów interaktywnych.
W większości przypadków lekcje opierają się na uproszczonych przykładach z doświadczeń zawodowych autora zdobytych w największych międzynarodowych korporacjach w Polsce i USA. Przykłady opisują problemy biznesowe, jakie powszechnie spotykane są w dużych firmach niezależnie od branży, w działach Marketingu, Sprzedaży czy Finansów.
Wszelkie uwagi proszę wpisywać do księgi gości lub przesyłać na adres email.
Wszystkie ćwiczenia opisywane w ramach kursu, są dostępne do samodzielnego przygotowania w arkuszu Excela, numer zakładki Excela odpowiada numerowi lekcji w spisie treści. Plik ten dostępny jest tutaj.
Nie wszystkie lekcje wymagają ćwiczeń (np. ten Wstęp), dlatego też nie wszystkie numery lekcji można znaleźć w pliku. Ćwiczenia do niektórych lekcji prezentowane są na kilku zakładkach, są one wtedy oznaczone dodatkowo literami (np. 24a, 24b).
Program Excel jest dostępny w wielu wersjach, jednak różnice pomiędzy nimi, jeśli chodzi o funkcjonalność nie są duże.
Jeżeli Excel, którego używasz, wygląda nieco inaczej a obrazki ikon przedstawiają to samo tylko inaczej narysowane nie należy się tym przejmować funkcjonalność nie zmienia się istotnie pomiędzy wersjami a wiedza przedstawiona w tym kursie pozostaje aktualna.
Nazwy
Po otwarciu programu Excel wyświetli się ekran podobny do poniższego. Proszę o zapoznanie się z poniższym nazewnictwem ponieważ będzie one używane w kolejnych lekcjach.
Excel automatycznie otworzy nowy plik i nada mu nazwę Zeszyt1.
Nowy plik składa się z 3 arkuszy, aktywny arkusz jest podświetlony na biało. Pomiędzy arkuszami przechodzimy klikając na ich nazwy.
Nazwy arkuszy możemy zmieniać dwukrotnie klikając na nazwie arkusza i wprowadzając wybraną przez nas nazwę.
Każdy arkusz ma 65 536 wierszy oznaczonych od 1 do 65 536 i 256 kolumn oznaczonych literami od A do IV.
Przemnożenie 2 powyższych wartości daje nam 16 777 216 komórek, w które możemy wprowadzać dane.
2. Wprowadzanie formuł
Wszystkie przykłady opisane w tej lekcji dostępne są w arkuszu Excela Cwiczenia.xls, zachęcam do ich samodzielnego wykonania.
Dane do Arkusza Excela wprowadzamy poprzez wybranie komórki i wpisanie danych bezpośrednio w tej komórce bądź w pasku formuły (zaznaczone strzałkami)
Oznaczenia aktywnego wierszu (5) i aktywnej kolumny (D) są na ciemniejszym tle. Aktywna komórka ma pogrubione obramowanie a jej adres wyświetlany jest w polu nazwy.
Jeżeli chcemy przejść do którejś z komórek możemy kliknąć ją lewym klawiszem myszy, przesunąć znacznik aktywnej komórki strzałkami bądź wpisać nazwę komórki w pole nazwy.
Proszę o wprowadzenie danych jak w powyższym przykładzie i ustawienie aktywnej komórki D8.
Wprowadzimy w tej komórce formułę sumy powyższych 3 produktów.
Można to zrobić na kilka sposobów, najprostsze to:
- Kliknąć ikonę
- bądź wpisując formułę ręcznie ‘=suma(D5:D7)
Jeśli kliknęliśmy ikonę Excel sam spróbuje się domyślić, jaki zakres chcemy zsumować, w przypadku prostych tabel czasami się to nawet udaje. Jeśli zakres zaproponowany przez Excel nam nie odpowiada, możemy go zmienić klikając na jeden z rogów zakresu (oznaczony strzałką) i przytrzymując lewy klawisz zwężyć bądź rozszerzyć zakres, możemy też klikając krawędź zakresu przeciągnąć go przytrzymując lewy klawisz myszy w inne miejsce.
Do komórek możemy wprowadzać formuły używając znaków matematycznych i wskazując komórki, które mają być użyte w tych obliczeniach.
Ważne jest, aby wprowadzanie formuły rozpocząć od znaku =
W poniższym przykładzie wprowadzono formułę obliczającą wzrost procentowy używając znaków dzielenia oraz odejmowania i wskazując dane, z których komórek mają być wykorzystane w obliczeniach.
W powyższym przykładzie, jeżeli dane sprzedaży zmienią się formuła przeliczy się automatycznie od nowa i będzie wskazywała nową wartość.
3. Kopiowanie
(Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Cwiczenia.xls, zachęcam do ich samodzielnego wykonania.)
W poniższym przykładzie mamy tabelę przedstawiającą dane sprzedaży dla 10 brandów, z rozbiciem na 6 miesięcy.
Aby uzupełnić nagłówki, nie wpisując ich ręcznie, ale kopiując, wybieramy pierwszy z nich ‘brand 1’ i stajemy kursorem myszki na małym czarnym kwadraciku znajdującym się w prawym dolnym rogu komórki (na poniższym rysunku zaznaczony czerwonym kółeczkiem).
Klikamy na tym kwadraciku lewym klawiszem i trzymając go wciśniętego przesuwamy kursor myszy do ostatniej z komórek, do których ma być przegrany nagłówek.
Jeżeli tekst, który kopiujemy kończy się liczbą (np. brand 1), jest nazwą miesiąca lub dnia tygodnia, Excel samoczynnie będzie go zmieniał, w przypadku tekstu z liczbą zwiększał ją o 1 w każdej kolejnej komórce, w przypadku dni tygodnia i miesięcy, wprowadzał kolejny miesiąc bądź dzień tygodnia.
W przypadku, gdy kopiujemy liczbę bądź tekst, dokładnie to samo zostanie wprowadzone do wszystkich komórek.
Na poniższym rysunku widać, że to, co będzie w ostatniej z komórek, do których kopiujemy pokazuje się w żółtym prostokącie widocznym przez cały czas, gdy przytrzymujemy lewy klawisz myszy podczas kopiowania.
Poniżej widać jak komórka zawierająca ‘styczeń’ jest kopiowana do 5 przyległych komórek, w czerwonym kółku pokazuje się prostokąt z tekstem ‘czerwiec’, który zostanie wprowadzony do ostatniej z komórek.
Nie ma możliwości skopiowania jednej komórki przez przeciąganie jednocześnie do wierszy i kolumn, należy przeprowadzić 2 oddzielne operacje kopiowania.
Więcej możliwości kopiowania z wykorzystaniem wcześniej ustalonych list opisuje lekcja: Listy Własne.
W poniższym przykładzie sumę wprowadzoną do komórki E17 kopiujemy do 5 komórek znajdujących się po jej prawej stronie. W przypadku kopiowania formuł, prostokąt z wartością dla ostatniej komórki nie jest pokazywany (czerwone kółko, w którym powinien on się znajdować jest puste.
Znacznie szybszym sposobem na kopiowanie jest podwójne kliknięcie na kwadracik znajdujący się w prawym dolnym rogu aktywnej komórki.
Exel sam skopiuje komórki w dół na takiej długości, w jakiej wypełniona jest kolumna znajdująca się z lewej strony od aktywnej komórki, a jeżeli nie jest ona wypełniona na takiej jak kolumna z prawej.
Sposób ten znacznie przyspiesza pracę w przypadku długich tabel.
Sposób ten nie może być używany do kopiowania w poziomie.
Polecenia Kopiuj, Wklej, Wytnij
Kopiować możemy też używając poleceń: Kopiuj Wklej
Aby skopiować aktualnie wybraną komórkę lub obszar należy kliknąć ikonę:
Aby wkleić skopiowane wcześniej dane należy kliknąć ikonę:
Można też wyciąć dane zamiast je kopiować, w takim przypadku dane znikają z oryginalnej komórki/obszaru po wklejeniu ich do nowej.
Jeżeli pomiędzy skopiowaniem/wycięciem a wklejeniem wykonamy jakąś inną operację w Excelu, program zapomni, co było kopiowane/wycinane.
Klawisze skrótów
Wygodnie jest używać klawiszy skrótów, które znacznie przyspieszają pracę:
Ctrl+C kopiowanie
Ctrl+V wklej
Ctrl+X wytnij
Należy zachować szczególną ostrożność przy kopiowaniu formuł, ponieważ adresy względne w formułach zmieniają się wraz z kopiowaniem. Więcej na ten temat można przeczytać w lekcji ‘Adresy względne i bezwzględne’.
Wklej specjalnie
Nie musimy kopiować ‘wszystkiego’ z danej komórki. Po wybraniu polecenia kopiuj i przejściu do komórki gdzie mają się znaleźć dane możemy kliknąć prawym klawiszem myszy i z menu, które się rozwinie wybrać opcje ‘Wklej specjalne...’
Wyświetli się poniższe okienko.
Najbardziej użyteczne opcje okna Wklej specjalne to:
- Formuły – wkleja tylko formułę nie zmienia formatowania komórki.
- Wartości – wkleja wartość jaka znajdowała się w kopiowanej komórce, jeżeli była tam formuła, wkleja wartość którą ona miała.
- Wklej łącze – wkleja link do komórki która była kopiowana, szczególnie użyteczne jeżeli potrzebujemy wielu linków pomiędzy plikami.
- Podziel/Przemnóż – użyteczne do zmiany formatu liczb. Mamy np., w dużej tabeli dane w zł a chcielibyśmy mieć dane w tyś zł. Wpisujemy w dowolnej innej komórce 1000, kopiujemy tą komórkę, zaznaczamy cały obszar gdzie dane mają zostać zmniejszone i wybieramy podziel, wszystkie dane zostaną podzielone przez 1000, co da nam dane w tyś zł.
- Formaty – formatuje komórkę zgodnie z formatem jaki miała kopiowana komórka.
Wklejać wartości możemy także klikając na ikonę: .
Kopiować format można także używając ikony tzw. malarza formatów. Wybieramy komórkę lub obszar którego format chcemy skopiować, klikamy ikonę a później wybieramy komórkę/komórki które mają mieć taki format.
Kopiowanie prawym klawiszem.
W poniższym przykładzie chcielibyśmy skopiować formułę z komórki I3 do poniższych komórek tak, aby nie popsuć formatowania poniższych komórek.
Wybranie kwadracika lewym klawiszem i przeciągnięcie go na dół popsuje nam formatowanie, ponieważ z komórki I3 skopiowana zostanie nie tylko formuła sumy, ale także formatowanie.
Jeżeli ‘mały czarny kwadracik’ klikniemy prawym klawiszem i przeciągniemy w dół, wyświetli się menu pozwalające wybrać opcję ‘Wypełnij bez formatowania, dzięki temu skopiowana zostanie tylko formuła.
Wybranie opcji ‘Wypełnij bez formatowania’ powinno dać efekt taki jak poniżej.
Ten sam efekt można by uzyskać kopiując komórkę a później z Menu Wklej specjalne wybierając opcję ‘Formuły’, ale użycie prawego klawisza jest znacznie szybsze.
4. Adresy względne i bezwzględne
Przykłady opisane w tej lekcji dostępne są w arkuszu Excela: Cwiczenia.xls, zachęcam do ich samodzielnego wykonania.
Kopiowanie formuł jest niezwykle użyteczne w Excelu. Formuła raz napisana może być skopiowane w ciągu kilku sekund do wielu komórek i właściwe wartości zostaną obliczone w każdej z nich.
W przypadku formuł należy jednak uważać przy ich kopiowaniu.
Przykład 1.
W poniższym przykładzie wprowadzono formułę wyliczającą średnią sprzedaż na klienta w zł dla Filii A.
Formuła ta została przegrana poniżej tak aby obliczała średnią sprzedaż na klienta w sztukach.
Ponieważ jednak przy wprowadzaniu formuły użyto odwołań bezwzględnych (adresy komórek nie zawierają znaków $) odwołania zmieniły się (wskazują komórki o 1 wiersz niżej niż oryginalnie) i formuła oblicza obecnie coś zupełnie innego.
Aby takie kopiowanie dało pożądane rezultaty należy wprowadzić adres bezwzględny dla komórki D3, w której znajduje się ilość klientów.
Adres ze względnego na bezwzględny najprościej zmienić klikając na nim w formule i wciskając klawisz F4 tyle razy aż adres przybierze pożądany format.
Można także zmienić adres ręcznie wprowadzając znak $ przed cyfrą 3 w formule.
W naszym przykładzie wybieramy adres bezwzględny dla komórki z ilością klientów, ponieważ nie chcemy, aby przy przegrywaniu adres ten przesunął się o 1 w dół. Nie dodajemy $ przed oznaczeniem kolumny (D), ponieważ będziemy kopiowali naszą formułę także w prawo i chcemy by adres zmienił się z kolumny D na kolumnę E (z kolumny z ilością klientów dla Filii A na kolumnę z ilością klientów dla Filii B).
Tak przygotowaną formułę można skopiować do komórek D7, E6 i E7.
Jak widać na poniższym obrazie formuła w komórce E7 ma właściwe odwołania.
Przykład 2.
W drugim przykładzie należy ze sprzedaży z VAT obliczyć sprzedaż bez VAT dla 2 filii i 2 okresów.
Wprowadzamy formułę odejmującą podatek VAT w pierwszej z komórek, adres z wielkością podatku VAT zamieniamy na bezwzględny, dodając symbol $ przed oznaczeniem kolumny i oznaczeniem wiersza. Nie chcemy, aby adres do wielkości VAT zmieniał się niezależnie czy będziemy przegrywać do innych wierszy, czy do innych kolumn.
Tak wprowadzoną formułę kopiujemy.
Jak widać poniżej przegrana formuła korzysta z właściwej wartości sprzedaży i tego samego adresu z podatkiem VAT.
Przykład 3.
W przykładzie 3 wykonamy kilka najczęściej używanych działań w Excelu.
W kolumnach C i D mamy dane sprzedaży dla 10 brandów w pewnej firmie. W kolumnie E policzymy jaki jest wzrost lub spadek w procentach w stosunku do poprzedniego roku dla każdego z tych brandów a także dla całej sprzedaży.
Przed policzeniem wzrostu sprzedaży zsumujemy jeszcze sprzedaż w 2006 i 2007 roku dla wszystkich brandów. Korzystając z funkcji SUMA należy uważać aby Excel nie dodał do wyników sprzedaży roku znajdującego się w nagłówku.
Aby policzyć procentowy wzrost/spadek należy ‘Nową Wartość’ podzielić przez ‘Starą Wartość’ i od wyniku dzielenia odjąć 1. Czyli:
= NowaWartość / Stara Wartość -1
W naszym przykładzie będzie to =D5/C5-1
Formuła ta nie potrzebuje znaków $ ponieważ oba adresy mają się zmieniać kiedy będziemy przegrywać je w dół.
Kopiujemy formułę do poniższych komórek.
Formuła na udział procentowy to Wartość Sprzedaży Brandu podzielona przez Sprzedaż Całkowitą. Czyli:
= SprzedażBrandu / SprzedażCałkowita
A w naszym przykładzie = C5/C$15
W adresie C$15 użyliśmy symbolu $ aby dla kolejnych brandów ich sprzedaż cały czas była dzielona przez sprzedaż całości.
Tak przygotowaną formułę można skopiować na wszystkie komórki w kolumnach „udział ‘06” i „udział ‘07”
Wzrost w pln to po prostu różnica pomiędzy sprzedażą w 2007 i 2006. Nie używamy tu znaku $ ponieważ dla każdego z brandów dane powinny być ciągnięte z innych komórek.
Formułę =D5-C5 możemy skopiować do poniższych komórek.
W ostatniej kolumnie tej tabeli musimy przeliczyć ‘wzrost w pln’ na ‘wzrost w EURO’, kurs podany jest w komórce H2 i ponieważ każda z komórek obliczających wzrost w EURO powinna odnosić się do H2 i adres ten nie może się zmienić podczas kopiowania w dół przed oznaczeniem wiersza ‘2’ wpisujemy $ i tak przygotowaną formułę kopiujemy w dół.
Wynikiem opisanych wyżej działań powinna być tabela z danymi taka jak pokazana poniżej.
Przykłady 4. i 5.
Przeprowadzenie kilku samodzielnych ćwiczeń z adresami względnymi i bezwzględnymi pozwoli Wam osiągnąć biegłość w posługiwaniu się nimi.
W przykładzie z Tabliczką Mnożenia formuła mnożenia jest już wprowadzona, należy tylko w odpowiednich miejscach adresów dodać symbole $ tak aby formułę tą można było skopiować do całej tablicy. Należy pamiętać, że wprowadzamy znaki $ tam w tych częściach adresu, które nie chcemy, aby się zmieniały podczas kopiowania, te, które mają się zmieniać pozostawiamy bez $.
Powodzenia!
W przykładzie z Szachownicą w pierwszym polu szachownicy została wpisana funkcja ZŁĄCZ.TEKSTY, dzięki której w polu tym pojawiła się informacja, jakie jest to pole. Funkcja ZŁĄCZ.TEKSTY łączy dane znajdujące się w komórkach będących argumentami funkcji.
Przed skopiowanie tej funkcji do pozostałych pól należy dodać symbole $ w odpowiednie miejsca adresów.
Aby nie popsuć formatowania szachownicy należy kopiować prawym klawiszem myszy, tak jak zostało to opisane w poprzedniej lekcji.
5. Formatowanie
Załóżmy, że zostaliśmy poproszeni o przygotowanie tabeli z danymi w układzie jak poniżej. Udało nam się zgromadzić odpowiednie dane i wkleić je do tabeli. Poniższa tabela ma jednak wiele wad.
Zmiana szerokości kolumn i wierszy
Zaczniemy od tego, aby wszystkie dane i opisy kategorii były widoczne. W tej chwili dane w komórkach ‘I20’ oraz ‘I29’ (zaznaczone zielonymi elipsami) nie mieszczą się w obecnej szerokości kolumn i zostały zamienione przez Excel na symbole ###. Podobnie opisy np. w komórkach B5, B6, B7, B29 i B29 także nie są pokazane w całości.
Aby rozszerzyć kolumny najwygodniej będzie dwukrotnie kliknąć w kreskę pomiędzy kolumnami (miejsca oznaczone czerwonymi elipsami).
...
zwykla-polka