Redakcja

Redaktor Naczelny

Piotr Gromulski

Wydawca

Monika Kijok

Opracowanie graficzne okładki

Małgorzata Piaskowska

Opracowanie graficzne

Zbigniew Korzański

Koordynator produkcji

Mariusz Jezierski

Korekta

Zespół

ISBN 978–83–269–3008-9

Nakład: 1000 egz.

Wydawnictwo Wiedza i Praktyka sp. z .o.o.

03–918 Warszawa, ul. Łotewska 9a

Tel. 22 518 29 29, faks 22 617 60 10, e-mail: pgromulski@wip.pl

NIP: 526–19–92–256

Numer KRS: 0000098264 – Sąd Rejonowy dla m.st. Warszawy, Sąd Gospodarczy

XIII Wydział Gospodarczy Rejestrowy. Wysokość kapitału zakładowego: 200.000 zł

Skład i łamanie: Triograf Dariusz Kołacz

Miller Druk Sp. z o. o., 03–301 Warszawa, ul. Jagiellońska 82, tel.: 22 614 17 67

Copyright © by Wydawnictwo Wiedza i Praktyka sp. z o.o. Warszawa 2014

Poradnik „Excel w praktyce” wraz z przysługującym Czytelnikom innymi elementami dostępnymi w subskrypcji (e-letter, strona www i inne) chronione są prawem autorskim. Przedruk materiałów opublikowanych w poradniku „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji – bez zgody wydawcy – jest zabroniony. Zakaz nie dotyczy cytowania publikacji z powołaniem się na źródło.

Poradnik „Excel w praktyce” został przygotowany z zachowaniem najwyższej staranności i wykorzystaniem wysokich kwalifikacji, wiedzy i doświadczenia autorów oraz konsultantów. Zaproponowane w publikacji „Excel w praktyce” oraz w innych dostępnych elementach subskrypcji wskazówki, porady i interpretacje nie mają charakteru porady prawnej. Ich zastosowanie w konkretnym przypadku może wymagać dodatkowych, pogłębionych konsultacji. Publikowane rozwiązania nie mogą być traktowane jako oficjalne stanowisko organów i urzędów państwowych. W związku z powyższym redakcja nie może ponosić odpowiedzialności prawnej za zastosowanie zawartych w publikacji „Excel w praktyce” lub w innych dostępntych elementach subskrypcji wskazówek, przykładów, informacji itp. do konkretnych przykładów.

Wydawca nie odpowiada za treść zamieszczonej reklamy; ma prawo odmówić zamieszczenia reklamy, jeżeli jej treść lub forma są sprzeczne z linią programową bądź charakterem publikacji oraz interesem Wydawnictwa Wiedza i Praktyka.

Automatyzacja prac – korzystajmy z gotowych rozwiązań przy analizach i prezentacji danych

Nieraz zadajemy sobie pytanie, dlaczego naszemu współpracownikowi udaje się codziennie wychodzić z biura o godzinie 16, a my prawie zawsze musimy zostawać po godzinach. Pracujemy przecież w Excelu nad podobnymi raportami, ale jemu udaje się skończyć dużo wcześniej. Zastanówmy się, gdzie tkwi sekret naszego kolegi. Prawdopodobnie stosuje sztuczki i gotowe rozwiązania, które pozwalają pewne czynności w Excelu wykonać szybciej.

Maciej Krasik, specjalista ds. MS Office

CZĘŚĆ 1. Przydatne formuły wyszukujące

Wyszukiwanie danych jest podstawową operacją wykonywaną w arkuszu. W wielu analizach potrzebujemy bowiem wybrać jedynie określoną wartość z zestawienia i wykorzystać ją do dalszych obliczeń. Do prostego odnajdywania danych możemy korzystać z polecenia Znajdź. Niestety, to narzędzie nie poradzi sobie ze złożonym odszukiwaniem wartości według zadanych kryteriów. Do takich operacji powinniśmy zastosować formuły oparte na funkcjach wyszukiwania i adresu. W odróżnieniu do polecenia Znajdź zwracają wartość wynikową, która może być wykorzystana w dalszych obliczeniach lub po prostu wyświetlona w komórce.

Formuła 1

Wartość najbardziej zbliżona do poszukiwanej

Potrafimy zbudować formułę, za pomocą której odszukamy na określonej liście wartość najmniejszą lub największą. Żadnym kłopotem nie będzie dla nas również wyszukanie pozycji odpowiadającej dokładnie ustalonej wartości. Sprawdźmy jednak coś jeszcze innego: formułę, która odnajduje komórkę z wartością najbliższą poszukiwanej, niezależnie od tego, czy jest trochę mniejsza, czy trochę większa. Niewątpliwą zaletą opisanej dalej formuły jest to, że jej poprawne działanie nie jest uwarunkowane sortowaniem listy.

Na rysunku 1 przedstawiony jest wykaz dostępnych w danym terminie sal szkoleniowych, zawierający ich lokalizację (miasto) oraz szacowaną liczbę wolnych miejsc.

Rys. 1. Sale szkoleniowe o różnej pojemności

Aby odszukać odpowiednią wartość:

1. W komórce E3 wpisujemy liczbę uczestników organizowanego przez nas szkolenia, np. 22.

2. Do komórki E4 wprowadzamy następującą formułę:

=INDEKS(B4:B14;PODAJ.POZYCJĘ(MIN(MODUŁ.LICZBY(B4:B14-E3));

MODUŁ.LICZBY(B4:B14-E3);0))

3. Zatwierdzamy ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa. Poprawnie wprowadzona zostanie ujęta w nawiasy klamrowe widoczne na pasku formuły.

Formuła zwróci wynik 25. Jest to bowiem wartość najbliższa 22.

Rys. 2. Formuła odszukała wartość najbliższą szukanej

Wyjaśnienie działania formuły:

– Od każdej liczby w obszarze B4:B14 jest odejmowana wartość z komórki E3, tworząc w ten sposób tablicę wartości równych tej różnicy, rozmiarem odpowiadającą liczbie komórek w obszarze B4:B14.

– Funkcja MODUŁ.LICZBY zamienia wszystkie wartości występujące w tej tablicy na liczby bez znaku.

– Z tych wartości za pomocą funkcji MIN wyszukiwana jest wartość najmniejsza.

– Następnie funkcja PODAJ.POZYCJĘ zwraca położenie (pozycję) tej minimalnej wartości.

– Obliczona pozycja staje się argumentem zewnętrznej funkcji INDEKS, która zwraca zawartość komórki znajdującej się na tej pozycji w obszarze B4:B14.

Formuła 2

Najmniejszy zysk i najmniejsza strata

Często się zdarza, że w jednym zestawieniu znajdują się zarówno liczby dodatnie, jak i ujemne. My potrzebujemy wyznaczyć skrajne wartości, ale z określonymi warunkami. Chcemy wyszukać najmniejszą wartość oddzielnie z grupy liczb dodatnich i oddzielnie spośród ujemnych. Do takiej operacji nie wystarczy zastosować jedynie funkcje MIN lub MAX. Jeżeli liczby o różnych znakach są pomieszane, wynikiem pierwszej będzie liczba ujemna, a drugiej dodatnia. Rozwiązanie zaprezentujemy na przykładzie zestawienia zysków i strat z rysunku 3.

Rys. 3. Tabela wyników finansowych

Naszym pierwszym zadaniem jest wyszukanie najmniejszego zysku. Formuła powinna zatem uwzględnić jedynie grupę kwot dodatnich i wybrać z nich najmniejszą.

W tym celu:

1. Do komórki D2 wprowadzamy następującą formułę:

=MIN(JEŻELI(B2:B13<=0;””;B2:B13))

2. Zatwierdzamy ją kombinacją klawiszy Ctrl + Shift + Enter, ponieważ jest to formuła tablicowa.

Rys. 4. Najmniejsza wartość spośród dodatnich

Wyjaśnienie działania formuły:

– Funkcja JEŻELI sprawdza każdą komórkę z zakresu B2:B13 pod kątem występowania liczb mniejszych lub równych zero (<=0).

– Tworzona jest tablica wartości logicznych PRAWDA (dla liczb ujemnych) i FAŁSZ (dla liczb dodatnich i w tym przypadku zera).

– Wszystkie wartości FAŁSZ są zamieniane w tablicy na rzeczywiste liczby z komórek, a w miejsce wartości PRAWDA jest podstawiany pusty ciąg znaków.

– Na koniec funkcja MIN przeszukuje liczby znajdujące się w tablicy i zwraca najmniejszą.

Aby sprawdzić teraz, ile wynosi najmniejsza strata, wykonujemy następujące czynności:

1. W komórce E2 wprowadzamy formułę:

=MAX(JEŻELI(B2:B13>=0;””;B2:B13))

2. Zatwierdzamy ją wspomnianą kombinacją klawiszy, ponieważ to także jest formuła tablicowa.

Rys. 5. Najmniejsza wartość spośród ujemnych

Formuła 3

Wartość występująca najczęściej lub najrzadziej

Zwróćmy uwagę, że w arkuszu znajduje się rejestr składanych zamówień. Potrzebujemy sprawdzić, z którego miasta jest ich najwięcej, a z którego najmniej. Przy tego typu wyszukiwaniu posłużymy się kombinacją funkcji wyszukujących oraz zliczających. Działanie formuły sprawdzimy na przykładzie z rysunku 6.

Więcej znajdziesz w wersji pełnej publikacji

Tematy publikacji w pełnej wersji

Automatyzacja pracy – korzystajmy z gotowych rozwiązań przy analizach i prezentacji danych

CZĘŚĆ 1. Przydatne formuły wyszukujące

Wartość najbardziej zbliżona do poszukiwanej

Najmniejszy zysk i najmniejsza strata

Wartość występująca najczęściej lub najrzadziej

Sprawdzenie, czy dana wartość znajduje się w tabeli

Zawartość ostatniej komórki w danej kolumnie

Wartość spełniająca więcej niż jedno kryterium

Kolumna, w której znajduje się określony zestaw danych

Uwzględnienie wielkości liter przy stosowaniu funkcji WYSZUKAJ.PIONOWO

Przeszukiwanie arkuszy bez potrzeby ich otwierania

Pierwsze wystąpienie wartości minimalnej

Wartość leżąca na przecięciu kolumny i wiersza

Automatyczne uzupełnianie rejestrów

Wyszukanie rekordu w bazie danych

Jednoczesne przeszukiwanie wielu tabel

Przeszukiwanie danych wierszami i kolumnami

Przeszukiwanie ciągów tekstowych

X-najmniejszych lub największych wartości

Pozycja wpisu z uwzględnieniem 2 kolumn

Zliczanie błędów występujących w arkuszu

Lokalizowanie komórek z błędami

CZĘŚĆ 2. Dynamiczne wykresy

Wykres na podstawie wskazań filtra

Wykres z konspektem arkusza

Sprytna przeglądarka wykresów

Wykres z szybką selekcją danych w kolumnach

Zmienne linie odniesienia na wykresie

Wykres z możliwością przewijania danych

Automatycznie aktualizowany wykres

Wykres kwot z określonego tygodnia

Zyski lub straty ukrywane jednym kliknięciem

Wykres z automatycznym pobieraniem danych z Internetu

Wykres raportu przestawnego