Jestem Excel, znamy się?

Wstęp do cyklu artykułów o nieznanym obliczu Excela

Zbliża się okres wakacyjny. Najczęściej jest to czas relaksu i oderwania się od spraw zawodowych, ale niektórzy zabiegani czytelnicy być może będą chcieli go poświęcić na podniesienie swoich kwalifikacji.

Excel popularny

Excel jest bezprzecznie jednym z najpopularniejszych programów używanych w zarządach firm i instytucji. Jeszcze niedawno badania wykazywały, że np. 75% controllerów korzysta z niego, jako głównego programu wspomagającego pracę. Dzisiaj trudno jest spotkać pracownika już nie tylko controllingu ale któregokolwiek z działów zarządu i księgowości, który by nie używał na co dzień Excela.

Jak każdy popularny program i on ma tę zaletę, że można zacząć go używać po kilku minutach od momentu uruchomienia. Wystarczy znajomość podobnych aplikacji windowsowych, wciśnięcie klawisza F1 (HELP – Pomocy!), spytanie się bardziej doświadczonej koleżanki „o co tu chodzi”, lub przeczytanie kilku stron z pierwszego lepszego podręcznika.

I prawdopodobnie na takiej zasadzie używa go przeważająca większość użytkowników.

Wystarczy, że wiedzą, jak policzyć sumę kolumny, Vat od wartości netto, jak pogrubić podsumowanie, jak stworzyć tabelkę z obramowaniami i wreszcie, jak całość wydrukować.

Korzyści z przyspieszenia pracy, zwolnienie ze żmudnych czynności jest tak oczywiste, że te kilka umiejętności wystarcza na długo i jest racją używania programu.

Rys. 1 To każdy potrafi:

(Tabela liczb, policzony podatek Vat, podsumowane kolumny i sformatowana przy pomocy autoformatu.)

Na tym rysunku jest już jednak pierwszy problem: sumy się nie zgadzają! Dlaczego? Dowiecie się Państwo w następnych odcinkach!

Na początek krótkie przypomnienie, co widzimy na ekranie: Kolumna B zawiera w wierszach od 6-tego do 9 liczby. Czytelnicy początkujący, ale ambitni – proszeni są o wykonanie tego prostego ćwiczenia.

Rys 2: Zawartość arkusza z rys. 1.

B C D

5		22%
........	.........
8 wiersz	250	55	305
9 wiersz	200	=B9*C5	=suma(B9:C9)
10 wiersz	=suma(B6:B9)	=Suma(C6:C9)	=suma(B10:C10)

Ekran Excela wygląda, jak pokratkowany arkusz, przypomina formularze do wyliczeń tabelarycznych (dzisiaj zupełnie zapomniane). Każde pole (komórka) ma swój adres składający się z literowego oznaczenia kolumny i nr wiersza (np. A1, B8 itp).

Ten pokratkowany arkusz jest ogromny: ma 256 kolumn i 65536 wierszy, widzimy więc na raz tylko mały wycinek, ale mamy duży zapas, który czasem, przy analizie dużej ilości danych, przydaje się. Ale o tym będzie w drugiej części cyklu

Do każdej komórki arkusza możemy wpisywać dane w dwóch postaciach: wprost, jako liczby, daty, teksty (tak, jak w edytorze tekstów) i pośrednio, podając regułę, według której Excel ma obliczyć a następnie wyświetlić liczbę, datę, tekst.

Przeanalizujmy teraz, co kryje się za postacią przedstawioną na pierwszym rysunku:

W wierszu 9-tym pokazano, jak Excel ma zdefiniowaną zawartość poszczególnych pól:

Jest liczba 200 (pole B9) wpisana wprost oraz formuły (pola C9 i D9): (obliczany podatek Vat (pole B9 razy pole C5 oraz funkcja Suma). Formuły wpisuje się, zaczynając od znaku równości: =B9*C5

W polu B10 wpisana jest funkcja =SUMA(B6:B9).

B6:B9 oznacza obszar (ciągły) zawarty miedzy komórkami o tych adresach. Funkcja Suma z parametrem obszaru B6:B9 oznacza: posumuj liczby znajdujące się w obszarze.

W kolumnie D znajdują się formuły sumy wartości netto z kolumny B i podatku z kolumny C: =B6+C6

W wierszu 10-tym znajdują się odpowiednie sumy wartości netto, podatku i wartości brutto.

Ładniejszy wygląd tabelki można uzyskać zaznaczając cały obszar i wybierając Menu FORMAT – AUTOFORMATOWANIE i następnie wybierając któryś z szablonów formatowania tabelki.

Następnie z pozycji Menu PLIK – wybrać Zapisz. Radzimy zapamiętać miejsce, gdzie zapisujemy swój plik, inaczej trzeba będzie go później szukać wyszukiwarką (pod warunkiem, że zapamiętamy nazwę pliku lub cokolwiek z jego istotnych cech, które mogą posłużyć jako parametry wyszukiwania). Prawda, że to każdy potrafi?

Excel nieznany

Ponieważ każdy potrafi to, o czym była wyżej mowa, nasz cykl jest przeznaczony dla każdego. Zakładamy, że nikomu nie wystarcza taka umiejętność.

Czy chcielibyście więc Państwo potrafić sporządzić następujący arkusz? (Rys.2)

Proszę zwrócić uwagę na formułę, która jest wpisana do komórki B3. (Widać ją nad literami oznaczającymi kolumny arkusza A, B, C, D). W całej tabeli są identyczne formuły a zostały wprowadzone przez kilka uderzeń klawiszy!

Wszystkie dane o wykonaniu budżetu (księgowania) znajdują się w oddzielnych arkuszach danych – (widać nieotwarte zakładki styczeń, luty, marzec, kwiecień z danymi księgowymi).

Każda informacja o pozycji budżetu jest obliczana kombinacją funkcji arkuszowych. Nie ma tu żadnych liczb wprowadzonych ręcznie.

Rys. 2 Arkusz wykonania budżetu

Proszę się nie zniechęcać skomplikowaniem tej formuły. Zresztą, żeby przygotować ten arkusz w takiej postaci, trzeba dane o wykonaniu skądś wziąć. Umiejętność pozyskiwania danych do arkusza jest równie ważna, jak umiejętność analizy tych danych przy pomocy odpowiednio zdefiniowanych formuł.

Wysiłek się opłaci, a jest, jak już wspominaliśmy, i dobra wiadomość: istnieje możliwość, żeby taką funkcję napisać tylko raz. Będzie obowiązywać dla całej tablicy: dla wszystkich miesięcy i dla wszystkich pozycji budżetu.

Raz opracowany wzór arkusza z formułami analizy i procedurami pozyskiwania danych będzie odtąd służył nam przez cały rok i nie będzie wymagał wielu dodatkowych czynności. Dopiero wówczas naprawdę odczujemy korzyści ze stosowania arkusza i wejdziemy do ekskluzywnego grona twórców aplikacji komputerowych. Zapraszamy do towarzystwa.

Nasze doświadczenia z Excelem są związane z zastosowaniami w księgowości, w działach analiz ekonomicznych, controllingu itp.

Stykając się na co dzień z „przeciętnymi” użytkownikami i ich problemami wyrobiliśmy sobie pogląd na to, jak Excel jest znany i używany a jak, naszym zdaniem, powinien być. Wyrazem tego poglądu jest właśnie niniejszy cykl.

Założenia o czytelniku

Przyjmujemy, że czytelnik często (kilka razy w tygodniu) używa Excela, zna podstawowe jego funkcje, ale uważa, że wszystko, co ponadto, jest dla niego za trudne lub zbyt pracochłonne.

Dlatego musi długo, z wytężoną uwagą i czasem ponawiając nieudane próby, (widzieliśmy na własne oczy) operować myszką, żeby zaznaczyć obszar do sformatowania, następnie pięć razy kliknąć, żeby ustawić format liczby z dokładnością do groszy i z podzieleniem cyfr na grupy po 3, zamiast użyć dwóch skrótów klawiszowych i zrobić to samo w ciągu sekundy.

Musi mozolnie przeklejać dane z arkusza dostarczonego z systemu f-k do sprawozdania, zamiast użyć funkcji SUMA.JEŻELI(), która będzie wyliczać właściwy wynik dla każdej nowej zawartości arkusza danych, pobieranej w ciągu kilku sekund przy pomocy prostych czynności lub wręcz automatycznie dzięki nagranym makrom.

Musi polegać na kompetencji i chęci (lub zderzać się z niekompetencją i/lub niechęcią i/lub brakiem czasu) swoich zakładowych informatyków, aby uzyskać dane do analiz i nie ma ani argumentów ani wiedzy, żeby zareagować na odzywkę „to się nie da”.

Wreszcie jest skazany na funkcje Excela takie, jak zostały opracowane w Redmond. (Wprawdzie Excel ma bardzo bogatą funkcjonalność, ale wszystkiego tam o naszych potrzebach nie wiedzą, warto więc mieć świadomość, że Excel można rozbudowywać o nowe funkcje.) Nie wszyscy muszą umieć programować, ale dobrze jest przynajmniej wiedzieć, jak sformułować problem dla programisty i kiedy warto to zrobić, zamiast męczyć się ze standardowymi funkcjami.

Jeśli zaś specjalnie dla nas lub dla naszego profilu zawodowego opracowano dodatek przydałoby się wiedzieć, jak z niego skorzystać i jak go zainstalować do swojego Excela.

Jeśli większość z opisanych sytuacji rozpoznajesz jako sytuacje będącą Twoim, czytelniku lub czytelniczko, udziałem, ten cykl jest dla Ciebie.

Cztery nieznane oblicza Excela

Nasze rozxważania będą kolejno się koncentrować na czterech mało znanych a często prawie zupełnie nieznanych obliczach Excela:

Excel dla zaawansowanych, czyli wiedza tym, jak zorganizować sobie lepiej środowisko pracy, umiejętność użycia skrótów klawiszowych, przyspieszająca pracę na etapie wprowadzania danych, formuł i formatowania, podstawowa wiedza o przydatnych funkcjach Excela i sposobach ich parametryzacji, czyli wszystko to, co bedzie potrzebne do tworzenia takich arkuszy, jak ten z rys. 2-giego.

Poznamy pojecie funkcji i nauczymy się tak wpisywać formuły z ich wykorzystaniem, by raz wpisana formuła została obliczona prawidłowo wszędzie, gdziekolwiek zostanie skopiowana.

Rys. 3

Excel dla kompetentnych: Raz wpisana formuła obowiązuje w całej tabeli. Jak to osiągnąć?

	A	B	C
1	Netto	22%	Brutto
2	100	=B1*A2	=suma(A2:B2)

Najbardziej wydajnymi i najmniej znanymi funkcjami Excela są funkcje analizy danych. Nauczymy się je wykorzystywać i odpowiednio organizować skoroszyty Excela, aby nasze analizy były uniwersalne i nie wymagały ciągłych niepotrzebnych przeróbek i przeklejania wartości. Ilustruje to rys. 3

Rys. 4

Funkcje analizy danych i właściwa organizacja skoroszytu: Arkusz danych i arkusze analiz tych danych (raporty)

Źle:

Arkusz A (danych)

	A	B	C
1	Region	F-ra	Kwota
2	Warszawa	1	120
3	Warszawa	1	1

...................... Suma(W-wa)

100	Szczecin	15	=suma(A2:B2)
Razem		=Suma(Szczecin)	=suma(C2:C100)

Lepiej:

Arkusz B (korzystający z arkusza A)

1	Warszawa	=Suma.Jeżeli(zakres;A1;Kwota)
2	Szczecin	=Suma.Jeżeli(zakres;A2;Kwota)

Excel jako centrum analizy danych

Tabela przestawna to bardzo zaawansowane narzędzie do wieloaspektowej analizy danych. Umożliwia przekształcenie zwykłej tabeli danych ułożonych tak, jak zostały wprowadzone do systemu transakcyjnego (f-k, magazyn, fakturowanie, płace) w tabelę pokazującą związki między różnymi cechami opisanymi w tabeli: np. jeśli w tabeli w różnych polach znajdują się informacje o regionie sprzedaży i o asortymencie sprzedaży, kilka ruchów myszką pozwoli pokazać które asortymenty sprzedają się najlepiej w których regionach.

Rys. 5

Tabela przestawna nałożona na tabelę danych jest kopalnią informacji biznesowych

.

Kiedy ujrzymy to nowe oblicze Excela, jako narzędzie do analizy danych, jeszcze bardziej wzrośnie nasze zapotrzebowanie na dane w Excelu. Dajcie mi dane a ja je przeanalizuję! chciałoby się krzyknąć. Przecież w komputerach każdej firmy istnieją gigabajty danych o wielkiej różnorodności i bogactwie. Każdy system transakcyjny zawiera np. tysiące faktur własnych i obcych. Wystarczy więc trochę wiedzy i umiejętności a analiza rentowności kierunków sprzedaży, lub rentowność klienta nie będzie stanowiła problemu.

Jednak, żeby zacząć taką analizę, trzeba najpierw te dane mieć w arkuszu w formie przydatnej do analizy. W naszym cyklu zaprezentujemy wydajne sposobu dostępu do nich wprost z arkusza elektronicznego! A jest to wiedza słabo znana nawet wśród zakładowych informatyków.

My zaś chcemy dostarczyć tych informacji zwykłym użytkownikom i nieco odczarować ten tajemniczy obszar dostępu do firmowych danych.

Rys.5

Funkcja dostępu do danych zawartych w systemie transakcyjnym z bazą danych SQL

Jak pobrać takie dane bezpośrednio z systemu SQL (Oracle, SAP Business One itp)

Excel jako platforma budowy wieloskoroszytowych modeli

Excel umożliwia budowę wieloskoroszytowych modeli, powiązanych ze soba hiperłączami lub powiązaniami międzyarkuszowymi (miedzyskoroszytowymi).

Możliwość odwołań międzyskoroszytowych otworzyła drogę do skomplikowanych wieloplikowych modeli i.....problemów z ich niezawodnością i przejrzystością.

Wśród excelistów znane są kłopoty z psuciem się tych łączy, niekontrolowanym rozrostem modeli i powstawaniem systemu skoroszytów„spaghetti”, nad którym nikt już nie panuje.

Pokażemy, jakich zasad przestrzegać przy budowie wieloskoroszytowych modeli i jak unikać najgorszych kłopotów stosując mało popularne metody dostępu do arkuszy nawet bez konieczności ich otwierania.

Excel jako samodoskonaląca się platforma

Exel pozwala tworzyć nowe funkcje, procedury a nawet zaawansowane aplikacje. Dla zwykłego użytkownika oznacza to przede wszystkim możliwość rozwijania zastosowań arkusza, gromadzenia doświadczeń, korzystania z dorobku innych, tworzenia profesjonalnej i specjalizowanej platformy analiz.

Chcemy w tej części zaprezentować Excela jako integralny składnik zaawansowanego środowiska informatycznego, ale przeznaczonego dla „zwykłych” użytkowników, twórczych, kompetentnych, ale niekoniecznie specjalistów informatyki. Chcemy spowodować, by użytkownicy Ci nie tylko sami sobie lepiej radzili we współczesnym środowisku sieciowym, ale i umieli stawiać wymagania informatykom. Na ile się nam to uda, ocenicie Państwo sami.

Zapraszamy!

Test wstępny:

Pytanie: Jakie są sposoby wpisywanie danych do Excela i czym się różni Excel od edytora tekstów?

Zakładamy, że potrafisz: spróbuj:

a. Uruchomić swojego Excela,

b. Sporządzić tabelkę zawierającą: Kolumnę liczb, kolumnę liczb zwiększoną o 7%.,

c. Podsumować obydwie kolumny liczb w kierunku poziomym i pionowym. Użyj do tego celu jednej funkcji – Sumy na zakresie ciągłym komórek – usytuowanych w jednym wierszu lub kolumnie, podaj argument tej funkcji w postaci zakresu adresów względnych (czyli takich, jakie się pojawią, kiedy będziesz wpisywał adres przez wskazanie komórki myszką),

d. Sformatować tę tabelkę: pogrubić lub powiększyć czcionkę, ustawić tekst na środku komórki arkusza, dodać kolor tła, lub kolor czcionki, zmienić krój czcionki.

e. Zapisać plik na nośniku (dysku komputera, dyskietce),

f. Otworzyć ponownie zapisany plik (odtworzyć swoją pracę po jakimś czasie),