Nadanie komórce formatu wyświetlającego dwa miejsca po przecinku to nie to samo co zaokrąglenie liczby do dwu miejsc po przecinku. Nie zrozumienie tego rodzi jeden z częściej powtarzanych błędów i komentarze 'excel źle liczy!'.

Jeżeli komórkom został nadany format 'dwa miejsca po przecinku' może się wydawać, że dodajemy dwie liczby 1.44 i suma powinna wynosić 2.88. Tymczasem komórki B1 i B2 na rysunku obok zawierają wartości 1.444. Stąd ich suma to 2.888, a ponieważ format to dwa miejsca po przecinku suma wyświetlona jest jako 2.89 - tutaj niejako zaokrąglony jest tylko wyświetlany format.

Aby uniknąć tej niedprawidłowości musimy użyć funkcji

=ZAOKR(liczba;liczba_miejsc), gdzie liczba to zaokrąglana liczba, a liczba_miejsc to liczba miejsc po przecinku do jakiej chcemy zaokrąglić.

Tak więc =ZAOKR(1.444;2) da nam w rezultacie 1.44 i wtedy uzyskamy sumę 2.88.

Jeżeli teraz zmienimy format komórki na 'trzy miejsca po przecinku' to okaże się, że w komórce A1 mamy wartość 1.440.

Oczywiście oba argumenty funkcji =ZAOKR() to mogą być odwołania do innych komórek zawierających wartości liczbowe.

Funkcja =ZAOKR() zaokrągla liczby w sposób ogólnie przyjęty: w dół do 0.4 i w górę od 0.5.

Jeżeli chcemy zaokrąglać tylko w dół lub tylko w górę użyjemy funkcji, odpowiednio:

=ZAOKR.DÓŁ()

=ZAOKR.GÓRA()

Funkcje te mają takie same argumenty jak =ZAOKR().

Funkcja =WARTOŚĆ() pozwala na szybkie skonwertowanie liczb traktowanych przez excela jako tekst, w tym także liczb poprzedzonych apostrofem, na liczby:

 

Funkcja =SUMA.JEŻELI() umożliwia zsumowanie wartości spełniających zadane kryteria, tak jak na zdjęciu obok. Formuła w komórce B14 to: =SUMA.JEŻELI($A$2:$B$11;A14;$B$2:$B$11) skopiowana została do komórek B15 i B16. Składnia tej funkcji to: zakres - zakres zawierający kolumnę z wartościami spełniającymi kryteria kryteria - sumowane kryterium suma_zakres - sumowane wartości Jeżeli zakres pokrywa się z suma_zakres ostatnie kryterium możemy pominąć.

Funkcje =LEWY(), =PRAWY(), =FRAGMENT.TEKSTU() są funkcjami umożliwiającymi wycięcie z ciągu znaków określonej liczby znaków odpowiednio od lewej strony ciągu, prawej i środka.

 

 

Przykład 1:

Jeżeli chcemy wyciąć z ciągu znaków znajdującego się w komórce A1:

Adam Kowalski

imię, czyli ciąg znaków 'Adam', to funkcja będzie wyglądać następująco:

 

=LEWY(A1,5), gdzie '5' to liczba wycinanych znaków.

 

 

Przykład 2:

Z zestawienia zawierającego kod pocztowy i nazwę miasta chcemy wyciąć nazwy miast (odrzucić kody pocztowe):

 

komórka A1: 00-175 Warszawa

komórka A2: 21-200 Lublin.

itd.

 

Posłużymy się funkcją =PRAWY(), jednakże liczba znaków jaką należy wyciąć jest zmienna. Do określenia liczby wycinanych znaków użyjemy funkcji =DŁ() zwaracającej długość ciągu:

=DŁ(A1) da nam w wyniku 15, a więc musimy wyciąć z prawej strony 15 minus 7 znaków (7 to długość kodu pocztowego plus spacja).

W rezultacie nasz formuła zwracająca miasta to:

 

=PRAWY(A1;DŁ(A1)-7))

 

 

Przykład 3:

Otrzymaliśmy zestawienie, w którym w pojedynczych komórkach mamy imię, nazwisko i adres. Aby wyciąć nazwisko posłużymy się funkcją =FRAGENT.TEKSTU(), oraz =ZNAJDŹ().

Funkcja =ZNAJDŹ() umożliwi nam znalezienie pozycji na której znajduje się spacja oddzielająca imię od nazwiska, oraz przecinek znajdujący się po nazwisku:

 

komórka A1: Adam Kowalski, ul. Podleśna 3

 

Pozycja od której będziemy wycinać tekst:

=ZNAJDŹ(" ";A1;1)+1 jedynka (trzeci argument funkcji) określa znak, od którego przeszukujemy ciąg znaków, rezultat fomuły: 6.

 

Pozycja na której zakończymy wycinanie:

=ZNAJDŹ(",";A1;1) rezultat formuły: 14,

a zatem powinniśmy wyciąć 14 minus 6 znaków poczynając od znaku 6-go.

 

Czyli nasza formuła przyjmie postać:

=FRAGMENT.TEKSTU(A1;ZNAJDŹ(" ";A1;1)+1;ZNAJDŹ(",";A1;1)-ZNAJDŹ(" ";A1;1)-1).

Funkcja =ZŁĄCZ.TEKSTY() umożliwia połączenie wielu ciągów tekstowych w jeden. Tę samą operację można także wykonać za pomocą operatora '&'.

 

Dwie pokrewne funkcje =TERAZ() i =DZIŚ() w wyniku zwracają, odpowiednio, aktualną datę i czas oraz aktualną datę. Należy zwrócić uwagę, iż obie funkcje nie mają argumentów. Funkcje są uaktualniane w momencie przeliczenia arkusza, tzn. gdy zmieniana jest wartość jakiejś komórki, gdy naciskamy klawisz F9 oraz przy otwarciu zeszytu.

 

Użycie obu funkcji skutkujące otrzymaniem w wyniku daty i/lub czasu powoduje automatyczne sformatowanie komórki do formatu daty i/lub czasu.

 

Przykładowy wynik funkcji:

 

=TERAZ() - 2004/05/11 11:09

=DZIŚ() - 2004/05/11

 

Funkcja =DATA() umożliwia utworzenie daty z kopenentów reprezentujących rok, miesiąc i dzień. Rok, miesiąc i dzień to arguemnty funkcji:

=DATA(rok;miesiąc;dzień).

Najprostszym, choć mało, użytecznym przykładem jest:

=DATA(2004;5;18)  - w rezultacie otrzymujemy datę: 2004/05/18.

Uwaga, funkcja =DATA() ma tę miłą cechę, iż argument 'miesiąc' nie musi być liczbą z przedziału 1-12; a 'dzień' z przedziału 1-liczba_dni_w_miesiącu. Proszę sobie sprawdzić jaką datę otrzymamy w przypadku: =DATA(2003;14;1) lub =DATA(2004;3;-1)

Funkcja =DATA() staje się niezwyke przydatna w połączeniu z innymi funkcjami:

1, aby skonwertować ciąg tekstowy, np. typu: 20040518 na datę możemy wykorzystać funkcję =DATA() w połączeniu z funkcjami wycinającymi fragment tekstu (patrz 'fragment tekstu').

Jeżeli nasz ciąg znajduje się w komórce A1 formuła:

=DATA(LEWY(A1;4);FRAGMENT.TEKSTU(A1;5;2);PRAWY(A1;2))

skonwertuje ciąg 20040518 na datę 2004/05/18

2, zwrócenie na podstawie danej daty ostatniego dnia miesiąca: 'ostatni dzień miesiąca'.

3, pierwszy dzień tygodnia: 'pierwszy dzień tygodnia'.

Poniższa formuła zwraca ostatni dzień miesiąca (w komórce 'A1' wpisana jest data):

 

=DATA(ROK(A1);MIESIĄC(A1)+1;0)

Formuła zwracająca pierwszy dzień tygodnia danego miesiąca (chyba nie bardzo jest to zrozumiale, najlepiej na przykładzie, a wiec np.: "pierwszy poniedziałek miesiąca"):

=DATA(A2;A3;1)+WYBIERZ(DZIEŃ.TYG(DATA(A2;A3;1);2);0;6;5;4;3;2;1)

gdzie A2 to rok, a A3 to miesiąc.

DATA(A2;A3;1) zwraca pierwszy dzień miesiąca, DZIEŃ.TYG(DATA(A2;A3;1);2) zwraca numer dnia, zaś funkcja WYBIERZ na jego podstawie dodaje odpowiednią liczbę dni.

Bardziej uniwersalna jest następująca formuła:

=DATA(A2;A3;1)+WYSZUKAJ.PIONOWO(DZIEŃ.TYG(DATA(A2;A3;1);2);{1;0;1;2;3;4;5;6\2;6;0;1;2;3;4;5\3;5;6;0;1;2;3;4\4;4;5;6;0;1;2;3\5;3;4;5;6;0;1;2\6;2;3;4;5;6;0;1\7;1;2;3;4;5;6;0};A4+1)

zwraca ona date dowolnego pierwszego w miesiącu dnia tygodnia. W komórkę A4 powinniśmy wpisać numer poszukiwanego dnia tygodnia (1 - poniedziałek, 7 - niedziela).

{1;0;1;2;3;4;5;6\2;6;0;1;2;3;4;5\3;5;6;0;1;2;3;4\4;4;5;6;0;1;2;3\5;3;4;5;6;0;1;2\6;2;3;4;5;6;0;1\7;1;2;3;4;5;6;0} to tablica stałych o postaci:

1	0	1	2	3	4	5	6
2	6	0	1	2	3	4	5
3	5	6	0	1	2	3	4
4	4	5	6	0	1	2	3
5	3	4	5	6	0	1	2
6	2	3	4	5	6	0	1
7	1	2	3	4	5	6	0

Separatory ";" (pola tablicy) i "\" (wiersze tablicy) mogą byc różne na różnych konfiguracjach, np. odpowiednio "," i ";".

Najlepiej zbudować taką tablicę w arkuszu, wejść w kreatora funkcji, wybrać np. WYSZUKAJ.PIONOWO i dla parametru "zakres tablicy" zaznaczyć tę tablicę i podejrzeć jak ona wygląda w podglądzie wartości jakie przyjmuje parametr (w excelu 2002 z prawej strony okna kreatora po znaku "=").

Jedna z bardziej pożytecznych funkcji jest =WYSZUKAJ.PIONOWO().

Wyobraźmy sobie takie zadanie: mamy listź artykułów z danymi w kolejnych kolumnach: symbol artykułu, nazwa artykułu, cena artykułu. Drugie zestawienie to rejestr sprzedaży, w którym mamy zawarty symbol artykułu i ilość sprzedanych sztuk. Jak każdemu artykułowi z rejestru sprzedaży przypisać cenę z listy artykułów? Całą pracę wykona za nas właśnie funkcja =WYSZUKAJ.PIONOWO().

Składnia tej funkcji:

=WYSZUKAJ.PIONOWO(co;gdzie;w_której_kolumnie;prawda/fałsz),

ostatni argument:

fałsz - zostanie wyszukany w tabeli 'gdzie' dokładnie element 'co',

prawda lub pominięty argument - zostanie wyszukany, w przypadku braku dokładnie takiego samego elementu, największy element mniejszy od 'co' (wykorzystać to można do przypisania np. ocen: dla p-któw z przedziału x-y ocena z), tabela 'gdzie' musi być posortowana rosnąco według pierwszej jej kolumny.

Formuła w G2 to:

=WYSZUKAJ.PIONOWO(F2,$A$2:$C$5,3,FALSZ)

Problem: suma obrotów kont korespondencyjnych jest różna od zera. Jak znaleźć błędne księgowanie wśród czasem setek, albo i tysięcy zapisów?

Założenie: mamy dostęp excelem do zapisów na naszych kontach (zapisy są wyeksportowane do pliku tekstowego i zaimportowane do excela, albo zostały pobrany za pomocą ODBC bezpośrenio z bazy danych).

Dane wykorzystane w przykładzie wyglądają tak jak w tabeli poniżej:

	A	B	C	D	E
1	konto	data	nr ksiegowania	kwota	opis
2	1000	2003/04/02	001	100	aa
3	1000	2003/04/02	002	250	bb
4	1000	2003/04/01	003	55	cc
5	1000	2003/03/31	004	66	dd
6	1000	2003/03/30	005	45	ee
7	1000	2003/03/29	006	100	ff
8	1000	2003/03/28	007	47	gg
9	1000	2003/03/27	008	85	hh
10	1000	2003/03/26	009	999	ii
11	1000	2003/03/25	010	100	jj
12	1000	2003/03/24	011	10	kk
13	1000	2003/03/23	012	100	ll
14	1300	2003/04/02	001	-100	aa
15	1300	2003/04/02	002	-250	bb
16	1300	2003/04/01	003	-55	cc
17	1300	2003/03/31	004	-66	dd
18	1300	2003/03/30	005	-45	ee
19	1300	2003/03/29	006	-100	ff
20	1300	2003/03/28	007	-47	gg
21	1300	2003/03/27	008	-85	hh
22	1300	2003/03/26	009	-999	ii
23	1300	2003/03/25	010	-100	jj
24	1300	2003/03/24	011	-10	kk

Do znalezienia błędnego księgowania użyjemy tabeli przestawnej.

Zaznaczmy obszar naszych danych (w przykładzie A1:E24) i wybierzmy w menu: "dane/raport tabeli przestawnej i raportu przestawnego". Otwiera się okno kreatora tabeli przestawnej. W zasadzie dla naszego przykładu w każdym oknie wystarczy klikać na przycisk "dalej", ale pokrótce omówię poszczególne okna:

1-sze okno pozwala na określenie rodzaju źródła danych (w naszym przykładzie są to dane zawarte w bazie danych excela) oraz rodzaju raportu (tabela lub wykres przestawny)

2-gie okno pozwala zdefiniować zakres danych jakie mają być analizowane (jeżeli zaznaczyliśmy wcześniej obszar naszych danych, to kreator użyje domyślnie odwołania do zaznaczonego obszaru)

3-cie okno umożliwia określenie gdzie chcemy umieścić tabelę przestawną, pozostawmy zaznaczoną opcję "nowy arkusz".

Powyższe klikanie powinno dać efekt jak na zdjęciu poniżej:

Lewy obszar z niebieskimi obwódkami to obszar tabeli przestawnej, w oknie prawym mamy zaś nagłówki naszej bazy danych (proszę porównać z pierwszym zdjęciem).

Teraz całe zadanie polega na przeciągnięciu myszą z prawego okna nagłówka "nr księgowania" nad obszar, który na zdjęciu opisany jest szarym napisem jako "Drop Row Fields Here" ("Upuść pola wierszy tutaj"); a następnie nagłówka "kwota" nad obszar "Drop Data Items Here" ("Upuść elementy danych tutaj") (przeciągamy i puszczamy przycisk myszy nad odpowienim obszarem z szarym napisem).

Efekt powinien być następujący:

W naszym przykładzie tabela przestawna wykonała za nas sumowanie "kwot" wg kryterium "nr księgowania". Od razu widzimy, że księgowanie numer 012 nie zamknęło się na kontach korespondencyjnych. Jeżeli klikniemy dwukrotnie na komórce B16 (suma dla księgowania nr 012) to będziemy mogli w nowym arkuszu zobaczyć szczegóły składające się na tę sumę (w naszym przykładzie jest to tylko jedna pozycja):

Zidentyfikowaliśmy, więc winnego!

Dodam jeszcze, że tabela jest "powiązana" z danymi żródłowymi (pierwsze zdjęcie) jednak nie jest odświeżana automatycznie - jeżeli dokonamy zmian w danych, na podstawie których jest zbudowana tabela, to aby ją odświerzyć należy kliknąć w jej obszarze prawym klawiszem myszy i wybrać "odśwież dane".

Polecenie menu narzędzia/opcje pozwala na dostosowanie środowiska pracy. Zostaną tutaj omówione najczęściej wykorzystywane opcje (niektóre mogą nie występowac w wersjach wcześniejszych niż wersja 2002):

1. zakładka "widok"

"Okna na pasku zadań" włącza/wyłącza pokazywanie otwartych skoroszytów excela na pasku zadań. Jeżeli włączymy to na pasku zadań będziemy mieli każdy skoroszyt jako oddzielny przycisk, lub jako przyciski zgrupowane. Jeżeli opcja ta będzie wyłączona będziemy mieć tylko jeden przycisk do programu excela.

"Linie siatki" - pozwala włączać i wyłączać linie siatki w arkuszu.

2. zakładka "przeliczanie" - tutaj możemy wybrać sposób przeliczania arkusza, zdarza się, że arkusz "nie liczy" formuł - należy sprawdzić czy przypadkiem nie jest włączona opcja przeliczania ręcznego (klawisz F9 przelicza arkusz). Przeliczanie ręczne jest użyteczne w przypadku arkuszy o dużej liczbie formuł.

3. zakładka "ogólne"

kolejny często napotykany problem to "nagłówki kolumn były w postaci literowej, a teraz są jako liczby" - proszę sprawdzić czy nie został włączony "styl odwołania W1K1"

przydatną funkcją jest "domyślna lokalizacja plików" - określa ona w jakim folderze domyślnie będą otwierane okna takie jak "Zapisz jako", "Otwórz"

4. zakładka "listy niestandardowe" - przeczytaj tutaj

5. zakładka "międzynarodowe" - umożliwia zdefiniowanie separatorów (dziesiętnego i tysięcy) innych niż separatory z ustawień regionalnych systemu Windows.

---