Praca z Excelem – 100 porad i tricków (3/5)
To już trzecia odsłona naszego cyklu. Do tej pory – streszczając – mówiliśmy już sobie o poruszaniu się w arkuszu, formatowaniu tekstu, pracy na datach, tabelach i wykresach. Porady dotyczące tych zagadnień, jak i wielu innych, znajdziecie pod niżej wymienionymi linkami. Dzisiaj przejdziemy już jednak do kolejnych tematów.
Praca z Excelem – 100 porad i tricków (1/5)
Praca z Excelem – 100 porad i tricków (2/5)
Jak na razie starałem się unikać w naszym poradniku wprowadzania zbyt wielu formuł – chciałem zachować pewien ciąg opisywanych zagadnień. Nie zawsze było to możliwe, stąd też o kilku mieliście okazję poczytać w pierwszej lub drugiej części cyklu. Dzisiejsza odsłona – i kolejne – poświęcona już będzie jednak temu zasadniczemu obszarowi pracy z Excelem. Moim założeniem było, wprowadzić Was w mechanizmy rządzące arkusze kalkulacyjnym, nauczyć jak się w nim poruszać, by dopiero z czasem przejść do formuł. W tym artykule zaczniemy od tych najbardziej przydatnych – oczywiście wedle mojego widzimisię 😉 Będzie trochę o funkcjach logicznych, służących wyszukiwaniu danych i niestandardowemu sumowaniu. O funkcjach statystycznych i finansowych powiemy sobie w kolejnej odsłonie. Ten artykuł zaczniemy jednak od kilku – może oczywistych – wskazówek odnośnie pracy na formułach w ogóle. Zanim przejdziemy do samych porad, chciałbym jednak poczynić jeszcze pewne zastrzeżenia:
1. Jak zapewne zauważyliście, pracuję na wersji Office’a 2010 (a w tym artykule również miejscami na wersji 2007). Z tego względu, że w wersji 2013 wprowadzono pewne zmiany, zwłaszcza mam tu na myśli rozlokowanie pewnych funkcji w interfejsie, a zatem i zmianę ich skrótów klawiszowych, postanowiłem zrezygnować z przedstawiania tych ostatnich – na pewno jednak dowiecie się, jak daną funkcję zlokalizować! 🙂
2. Przydatność lwiej części porad może nie być do końca widoczna na przytaczanych przykładach. Wynika to z tego, że Excel stworzony jest do pracy na dużych bazach. Poszczególne funkcje sprawdzają się właśnie wtedy, gdy mogą zautomatyzować pracę, która „manualnie” zajęłaby połowę życia. Przydatność wzrasta również wraz z nabyciem biegłości w łączeniu formuł i funkcji. Kilka ciekawych połączeń postarałem się Wam zaprezentować.
3. W tej odsłonie dołożyłem starań, by porady miały bardziej zróżnicowany poziom trudności. Dajcie proszę znać w komentarzach, czy taka forma Wam odpowiada.
A teraz przejdźmy już do samych porad. Zacznijmy od czegoś na rozgrzewkę.
Praca z formułami
#41 Inspekcja formuł
Stopień zaawansowania waszego arkusza zależy w dużej mierze od Waszej znajomości różnych formuł. W Excelu naprawdę niewiele jest rzeczy, które trzeba robić mechanicznie, manualnie – prawie na wszystko jest jakiś sposób. Dobra ich znajomość nie tylko przyspieszy waszą pracę, ale i sprawi, że arkusz będzie czytelniejszy i wymagał mniejszego nakładu pracy.
Celem korzystania z formuł niekoniecznie jest wykorzystywanie formatki Formuły, z górnego paska. Ich załączenia odbywa się po poprzedzeniu tekstu znakiem „=”. Z tego względu nie wszyscy mogą być świadomi, jakie dodatkowe funkcje znajdują się w tej formatce.
Szczególnie przydatne są przyciski Pokaż formuły i Śledź zależności. Pierwszy rozwinie każdą komórkę, w której znajduje się jakaś formułą, pokazując jej treść. Druga natomiast wskaże strzałkami komórki, w których znajdują się formuły wykorzystujące wskazaną komórkę. W tej sekcji znajdziecie też kilka innych, przydatnych funkcji. Wasz szef z pewnością z nich korzysta sprawdzając waszą pracę 😉
#42 Co zrobić, gdy rozwinięcie formuły zakrywa przydatne komórki?
Jest to porada odpowiadająca na „problem pierwszego świata”, czyli taki, który nie jest zbyt poważny, ale jednocześnie potrafi rozdrażnić. Poniżej widzicie przykład – pracuję właśnie nad formułą, która zakrywa mi dwie komórki po lewej stronie (B7 i C7).
Chcę natomiast ich użyć w obliczeniach. Mogę oczywiście wpisać zakres ręcznie, lub zaznaczyć komórkę przylegającą i nakierować strzałką na właściwą. Być może szybsze okażę się jednak przeniesienie rozwinięcia na drugą stronę – zwyczajnie wystarczy wyrównać zawartość komórki z formułą do przeciwnej krawędzi (w moim przypadku do lewej).
#43 Stałe odwołanie do jednej komórki – $
Również ta porada będzie miała stosunkowo elementarny wydźwięk, ale z mojego doświadczenia wynika, że wciąż wiele osób nie zna zastosowania znaku „$” w formułach. Załóżmy, że mamy taki przykład – poniżej wypisałem kilka produktów, którym w kolejnej komórce chce przypisać obliczoną kwotę VAT-u. Ten przykład jest o tyle niepraktyczny, że wystarczyłoby przemnożyć pierwszą wartość przez wpisaną na twardo wartość 0,23 i po prostu przeciągnąć tak skonstruowaną formułę do kolejnych komórek. Dla celów dydaktycznych załóżmy, że stawkę VAT-u chcemy jednak pobrać z tabeli obok.
W tym celu zaczynamy pisać naszą funkcję – kwotę netto z komórki C, przemnażamy przez VAT z komórki H5. Gdybyśmy teraz przeciągnęli taką formułę, również w dół przesuwałaby się komórkę mnożenia z VAT-em (do H6, H7, itd.). Nam zależy, by niezależnie od ilości wierszy, zawsze wartość netto z komórki C była mnożona przez komórkę H5. W tym celu, wystarczy wpisać znak „$” przed literą kolumny i numerem wiersza. W ten sposób: $H$5. Szybszym sposobem jest, mając w formule kursor na komórce, którą chcemy zablokować, wcisnąć klawisz F4.
#44 Formuła dla całego wiersza / kolumny / tabeli – jest na to skrót
Wszystko w Excelu da się robić szybciej. Załóżmy teraz, że chcemy zsumować kwotę netto i VAT-u, uzyskując w ten sposób kwotę brutto. Jednym z najczęściej używanych przeze mnie skrótów jest ALT + +, który do wskazanej komórki wstawia formułę sumy. Jeżeli skrót ten wciśniemy w kolumnie E, wówczas program automatycznie zlokalizuje wartości, które w tym miejscu powinien dodać. Podobnie chcemy sobie zsumować kwoty netto i VAT-u.
Chcemy teraz formułę sumującą kwotę netto i VAT przenieść także do pozostałych wierszy. Możemy użyć do tego myszki i przeciągnąć ją wzdłuż zakresu (przeciągając, gdy kursor zamieni kolor na czarny) lub też – co zalecam – użyć skrótu klawiszowego.
Zaznaczamy (SHIFT + strzałki) zakres komórek, w których chcemy wprowadzić sumy. Następnie wystarczy wybrać skrót CTRL + D (down), by funkcja ta została przeniesiona. W przypadku wierszy, zaznaczamy obszar w prawo, a następnie wciskamy CTRL + R (right).
W naszym przykładzie posługujemy się wyłącznie sumą, stąd jest jeszcze szybszy sposób na zsumowanie za jednym zamachem wierszy i kolumn. Wystarczy zaznaczyć cały zakres danych (kwoty netto i VAT), wraz z pustą kolumną po prawej (miejsce kwoty brutto) i pustym wierszem poniżej, a następnie wcisnąć ALT ++. Puste komórki wypełnią się sumą zaznaczonego obszaru – wiersza lub kolumny.
#45 Wypełnianie luk
Mamy arkusz jak poniżej. W jednej kolumnie (ale tylko przy pierwszy wierszu) widnieje nazwa firmy, w drugiej kwartał, w trzeciej wielkość sprzedaży. Często w zbliżony sposób wyglądają pliki wyeksportowane z zewnętrznych źródeł. Istnieją jednak pewne bariery w pracy na tak skonstruowanych danych. Dobrze by było, gdyby do każdego wiersza była przypisana również nazwa spółki – to ułatwi dopasowywanie danych z danego kwartału, danego przychodu, do odpowiadającej mu spółki. Wystarczy zaznaczyć w kolumnie A zakres czterech komórek, a następnie wcisnąć znany Wam już skrót – CTRL + D. Wiersze poniżej automatycznie uzupełnią się danymi. To samo należy zrobić z pozostałymi. Jest to o wiele szybszy sposób, niż kopiowanie i wklejanie.
Na tego typu problemy jest jednak jeszcze szybsze rozwiązanie. Należy zaznaczyć obszar z pustymi miejscami, które mają zostać uzupełnione (A4:A15), a następnie wcisnąć klawisz F5. Stamtąd ścieżka kliknięć jest następująca:
Specjalnie -> Wybierz – puste.
Mając zaznaczone wszystkie puste pola, wystarczy teraz tylko w pierwszej pustej komórce od góry wpisać znak „=”, ze wskazaniem komórki powyżej, czyli tą, której treść ma zostać do niej wklejona. Następnie skrótem CTRL + ENTER wprowadzimy tę komendę do wszystkich pustych komórek, które będą pobierały zawartość bezpośrednio z komórki nad nimi.
#46 TAB – wybieraj funkcje bez użycia myszki
Wpisując w dowolnej komórce znak „=”, a następnie pierwsze litery pożądanej formuły, Excel zacznie rozwijać listę wszystkich formuł rozpoczynających się od wybranej litery. Do tej pory zapewne spora część z Was formuły wybierała dwukrotnym kliknięciem myszki, jednocześnie frustrując się, że wciśnięcie klawisza „enter” nie wprowadziło jej do arkusza. Klawiszem, który wprowadzi formułę do komórki jest w Excelu TAB.
#47 Tabela danych – tabliczka mnożenia
Ta i kolejna porada dotyczyć będzie tworzenia tablicy uwzględniającej wyniki formuły w różnych kombinacjach zmiennych. Zacznijmy od prostszej wersji – tabeli danych, która mnożyć będzie wartości przypisane kolumnom i wierszom, w sposób, by każda wartość z wiersza była przemnożona przez każdą wartość z kolumny, tworząc tabelę z kombinacją wyników.
Zacznijmy od puste jeszcze tabeli. W kolumnie B podana jest wielkość sprzedaży, a w wierszu ósmym możliwe ceny. Chcemy poznać wielkość przychodu w przypadku kombinacji poszczególnych pozycji z kolumny B do cen z wartości 8.
W tym celu zaznaczamy obszar na szaro – w nim dokonywane będą obliczenia. Następnie zaznaczamy pierwszy obszar, który chcemy mnożyć, tj. ceny. Po tym wstawiamy znak mnożenia i zaznaczamy drugi zakres – ilości.
Mając taką funkcję i zaznaczony obszar, w którym chcemy, by ona się znalazła, należy uważać, by odruchowo nie wcisnąć ENTER, gdyż to spowoduje wpisanie formuły jedynie do zaznaczonej komórki. Skrót, który wypełni całą tabelę, to CTRL + SHIFT + ENTER. Jest on również niezwykle użyteczny w pracy na macierzach.
W ten sposób uzyskaliśmy prostą tabliczkę mnożenia. Co jednak, gdy w tabeli chcemy zawrzeć wyniki bardziej skomplikowanej formuły, z różnymi kombinacjami zmiennych?
#48 Analiza symulacji
Zakładamy firmę. Celem sprawdzenia, na jakim poziomie sprzedaży (i przy jakiej cenie) wyjdziemy na zero, a przy jakiej kombinacji tych zmiennych zaczniemy zarabiać – przy określonym poziomie kosztów stałych – możemy wyprowadzić prosty wzór funkcji zysku. W postaci formuły widzicie tę funkcję poniżej, w tym miejscu szybko rozpiszę, na czym polegać będzie przykład.
Wychodzimy od przychodu (ILOŚĆ x CENA), od tego odejmujemy koszty zmienne przypadające na każdą jednostkę sprzedaży (ILOŚĆ x KOSZTY ZMIENNE) i od tego odejmujemy jeszcze koszty stałe.
Zmieniając w arkuszu (komórki C6 i C7) parametry ILOŚĆ i CENA dojdziemy do różnych kombinacji wyniku (zysku lub straty). Naszym zadaniem jest jednak zaprezentowanie wszystkich możliwych kombinacji w postaci tabeli. W tym celu przepiszemy sobie naszą funkcję w miejsce komórki B11 z opisem wierszy i kolumn. Wykorzystamy na razie dokładnie ten sam zakres danych, pomijając tabelę.
Zaznaczmy teraz całą tabelę (tak, by w rogu zaznaczenia była nasza formuła). Następnie przejdziemy do zakładki Dane -> Analiza symulacji (Office 2007)/ Analiza warunkowa (Office 2010) -> Tabela danych. W nowym oknie dialogowym należy zdecydować, co jest obszarem wyjściowym dla wierszy – u nas jest to CENA (komórka C7), a co dla kolumn – u nas ILOŚĆ (C6). Po wybraniu tych opcji wciskamy ENTER.
Tabela sama się uzupełni. Można z niej wyciągnąć kilka wniosków – przy cenie 2, będziemy musieli sprzedać najmniej 80 sztuk, by przy określonym poziomie kosztów stałych, wyjść na zero. Z kolei przy cenie 3,50 wystarczy już tylko 40 sztuk.
#break#
PRZYDATNE FORMUŁY
#49 Funkcja JEŻELI
Funkcja JEŻELI jest stosunkowo prosta w użyciu, jednakże jej zastosowania nie mają właściwie końca. Przedstawię Wam tylko przykładowe jej wykorzystanie, mając przy tym nadzieję, że wpadniecie na to, jak użyć jej na własne potrzeby, by móc zwiększyć wartość waszego arkusza, a zatem pracy.
Zacznijmy od tego, że jest to tzw. funkcja warunkowa, z grupy formuł logicznych. Jej schematyczne rozwinięcie wygląda następująco:
=JEŻELI(test_logiczny; wartość_jeżeli_prawda; wartość_jeżeli_fałsz)
Kluczowy jest tu pierwszy element, czyli test logiczny. W teście logicznym możemy wykorzystywać następujące operatory: =, >, <, >=, <=. Pozwala ona więc nam stwierdzić, czy dana komórka spełnia lub nie, warunki naszego testu logicznego i - w stosownej sytuacji - zwróci wartość odpowiadającą spełnieniu tego warunku lub wartość odpowiadającą braku spełniania warunku.
Ok, może brzmi to trochę zagmatwanie, dlatego przejdźmy do krótkiego przykładu.
Mamy tabelę z wielkościami sprzedaży, osiągniętymi przez sprzedawców. Polityka firmy zakłada, że sprzedawcy osiągający obrót powyżej 100, otrzymają od zakładu premię w wysokości 15% wygenerowanych przychodów. W innym przypadku otrzymują oni jedynie ciepłe słowa motywacyjne.
Funkcją JEŻELI sprawdzimy zatem prosty warunek logiczny: czy sprzedaż jest większa niż 100?, co używając odpowiednich oznaczeń wyglądać będzie następująco: SPRZEDAŻ > 100. W języku logiki zakładamy więc warunek – sprzedaż jest większa od 100. Teraz musimy odpowiedzieć na pytania: co jeżeli jest większa, i co jeżeli jest mniejsza. Oddzielamy zatem średnikiem jeden element formuły, a następny zostaje wytłuszczony.
Jeżeli sprzedaż jest większa, wówczas będziemy chcieli naliczyć sprzedawcy premię w wysokości 15% obrotu. Dlatego warunkiem potwierdzenia będzie:
SPRZEDAŻ*0,15
Jeżeli sprzedaż będzie poniżej 100 lub równa 100, wówczas warunek nie jest spełniony i należy wpisać odpowiadający temu komunikat. Najlepiej byłoby wpisać wtedy „0”, co ułatwi pracę na tym pliku. Jeżeli jednak ma to być tekst, to należy pamiętać o wpisaniu go w cudzysłowie – inaczej formuła będzie błędna.
#50 Formatowanie warunkowe
O tym tricku powinienem był napisać pewnie w którymś z poprzednich artykułów, ale często używam go w połączeniu właśnie z funkcją JEŻELI. Chodzi o formatowanie warunkowe. W skrócie oznacza to, że w zależności od osiąganych wartości, określone komórki będą przybierały różne formatowanie, tj. np. tło, wielkość, czy kolor liter. Przydatne, prawda?
Weźmy sobie inny przykład. Mamy tabelę z zainwestowanym kapitałem i wyceną tych inwestycji. Chcemy osiągnąć następujące cele:
1. By komórka w kolumnie C określała słownie, czy osiągnięty jest zysk czy strata.
2. By komórka ta, w zależności od treści, przybierała różne kolory – zielony w przypadku słowa „ZYSK”, czerwony, gdy jest „STRATA”.
Zaczynamy od pierwszego etapu, czyli prostej funkcji JEŻELI, tak jak poniżej:
Teraz przejdźmy do zakładki Narzędzia główne -> Formatowanie warunkowe
Być może wcześniej nie korzystaliście z tego zakładki, ale teraz widzicie, że zawiera ona naprawdę sporo ciekawych opcji. Moim założeniem jest, by wyróżnić kolorem komórki zawierające dane sformułowanie, dlatego dalsza ścieżka kliknięć to: Reguły wyróżniania komórek -> Tekst zawierający…
Mając zaznaczony obszar z wykorzystaną funkcją JEŻELI, wybieram powyższą opcję. Chcę, by komórki w tym obszarze ze słowem „ZYSK” miały kolor zielony, a ze słowem „STRATA” czerwony (poprzez ustawienia można nadać właściwie dowolne formatowanie – ja dla przykładu wybrałem gotowe). Aby jednak dodać drugie formatowanie (ze stratą) trzeba jeszcze raz przeklikać się przez te ustawienia (nie da się ustawić formatowania dla dwóch wyrazów jednocześnie). Efekt jest jak poniżej.
Formatowanie pozostanie już na stałe – jeżeli zmieni się wynik wyceny, a z zysku zrobi się strata, formatowanie, wraz z funkcją JEŻELI, zaktualizują wartość tej komórki zgodnie z założeniami.
#51 Łączenie funkcji JEŻELI
Funkcja JEŻELI dobrze pracuje w stadzie. Mam tu na myśli, że pojedyncza funkcja pozwoli na zwrócenie jedynie wyniku dla spełnionego warunki lub niespełnionego. Co jednak, gdy warunków jest więcej? Weźmy za przykład system przyznawania ocen studentom. Możliwości jest wiele, od 2.0 do 5.0 (pomińmy z końcówkami 0,5).
Ocena uzależniona jest od ilości uzyskanych punktów. Jeżeli jest ona niższa od 50, wówczas student dostaje 2.0, jeżeli jest równa bądź mniejsza od 60 (50-60> dostaje 3.0, itd.
Naszą funkcja zaczyna się więc następująco:
=JEŻELI(UZYSKANE PUNKTY<50;2,0;JEŻELI...
Zapis ten pokazuje zasadnicze podejście do problemu: testowi logicznemu poddajemy uzyskane punkty. Jeżeli jest spełniony warunek, wówczas student otrzymuje 2.0, jeżeli nie, to wprowadzamy kolejną funkcję logiczną z testem warunku: jeżeli ilość punktów studenta jest równa bądź mniejsza od 60, wówczas dostaje 3.0. Jeżeli jest to nieprawda, wówczas wprowadzamy następny warunek, itd. Ostatecznie wygląda to następująco:
#52 Sumowanie warunkowe
Excel oferuje kilka innych, niezwykle przydatnych funkcji warunkowych. Formuła JEŻELI jest oczywiście niezwykle przydatna, ale może nawet bardziej przyda Wam się rozwiązać taki problem – należy zsumować jedynie wartości spełniające dane kryterium.
Weźmy sobie taki przykład – ze strony GUS-u pobrałem dane odnośnie dochodów osiąganych przez gminy. Każdej gminie (kolumna E) jest przypisane województwo (B). Wykorzystując funkcję SUMA.JEŻELI możemy w łatwy sposób zsumować dochodu wybranego województwa ze wszystkich gmin.
Formuła ma następującą postać:
=SUMA.JEŻELI(zakres;kryteria;[suma_zakres])
Naszym zakresem, czyli obszarem, w którym występuje nazwa województwa, jest kolumna A, kryterium to z kolei rzeczona nazwa, wpisana w cudzysłowie „WOJ. WIELKOPOLSKIE”. Suma zakresu to zaś obszar, który Excel ma zsumować – czyli wielkość osiąganego dochodu.
Dodam jedynie, że na podobnej zasadzie działa funkcja wyciągająca średnią z pozycji spełniających dane kryterium:
=ŚREDNIA.JEŻELI
#53 Suma, gdy warunków jest dwa lub więcej
Czasem nie wystarczy jeden warunek, by z tabeli wyciągnąć sumę pożądanych wartości.
Mamy tabelę, w której widoczna jest sprzedaż dwóch spółek na koniec poszczególnych kwartałów. Nas interesuje suma sprzedaży spółki BETA, jednakże po dacie 30 czerwca, czyli za ostatnie dwa kwartały. Jak to zrobić?
Na screenie wdarł się mały błąd – w przykładzie liczymy sprzedaż dla spółki Beta, nie Alfa 🙂
Użyjemy w tym celu formuły SUMA.WARUNKÓW, która zliczy nam sumę pozycji spełniających określone warunki. Oto komponenty formuły:
=SUMA.WARUNKÓW(suma_zakres; kryteria_zakres1; kryteria1,[kryteria_zakres2; kryteria2…]…)
Suma_zakres to zakres tej kolumny, z której wartości będziemy sumować – u nas jest to kwota obrotu. Ten element występuje tylko raz w formule.
Kryteria_zakres1 – teraz ustalamy obszar, który będzie podlegał pierwszemu warunkowi. U nas jest to data, bo zależy nam na kwocie obrotu za dwa ostatnie kwartały.
Kryteria1 – teraz zaznaczamy, że data ma być nie mniejsza niż 2014-06-30, bo to w naszym przykładzie będzie wystarczające, by ze zbioru danych wykluczyć ostatni dzień czerwca, a następny to już zakończenie Q3.
Zakresem drugiego kryterium jest kolumna z nazwami spółek, a warunkiem jest to, by nazwa brane pod uwagę były jedynie nazwy „BETA”.
Tak wygląda funkcja i wynik:
#54 Sumowanie iloczynów
Mam przed Wami jeszcze jedną sumę, która może się przydać przy pracy na większych danych. Suma iloczynów nie jest może zbytnio skomplikowana, ale gdy zaaplikuje się ją sprytnie do arkusza, może zaoszczędzić sporo czasu.
Mamy u samej góry arkusza kursy walut, które są wykorzystywane w całym pliku. Niżej zaś posługujemy się, zachowując ten sam układ w wierszu, poszczególnymi walutami, które ostatecznie chcemy wycenić w PLN jako jedną kwotę. M.in. takim zastosowaniom służy SUMA.ILOCZYNÓW.
Funkcja jest dość prosta, wystarczy w komórce z – u nas – wyceną, zaznaczyć pierwszą tabelę (kursy walut), którą – jeżeli chcemy zachować dla dalszych wierszy wypadałoby zablokować przy użyciu znaku $. Druga tabela to nasze waluty. ENTER. I gotowe.
#55 Suma komórek z wybranych arkuszy
W każdej zakładce arkusza wprowadziłem rejestr wydatków za poszczególne dni tygodnia. Chcę, by w ostatniej zakładce (RAZEM) zsumowane zostały kwoty odpowiadające wszystkim kategoriom z całego tygodnia. Oczywiście, przy możliwie najmniejszym nakładzie pracy.
Mógłbym wpisać znak „=”, po czym z każdej zakładki wybrać pożądaną komórkę. Jest to możliwe przy siedmiu zakładkach i kilku kategoriach, ale i tak nie ma sensu. Ważne jest jednak, aby w tej metodzie zachować jednolity układ poszczególnych zakładek, tak, by w danej komórce znajdowało się faktycznie wszędzie to, co chcemy. Po tym wystarczy już tylko użyć funkcji SUMA, wybierając arkusze, z których chcemy pobrać dane. Można to zrobić wpisując:
=SUMA(Poniedziałek:Niedziela!NUMER KOMÓRKI)
#56 Wyszukuj pionowo / poziomo – prawdziwe niezbędniki!
Przejdźmy do niezwykle użytecznej funkcji, bez której wykonywanie wielu czynności pracy biurowej jest albo niemożliwe, albo – w najlepszym przypadku – żmudne. WYSZUKAJ.PIONOWO to funkcja pozwalająca na przypisywanie wyszukiwanej frazie, wartości z przeszukiwanej tabeli. Pokażmy to sobie na przykładzie.
Mamy naszą tabelę z dochodami gmin. Tabela ma ponad 2400 wierszy, stąd stanowi jedynie solidne źródło danych, ale z pewnością nie będzie to plik, na którym da się wykonywać inne czynności. My natomiast potrzebujemy wyszukać z tej tabeli dochody odpowiadające gminom wypisanym w czerwonej ramce. Dla celów artykułu wybrałem ich tylko kilka – można by tę czynność przeprowadzić manualnie, wyszukując odpowiednie dane skrótem CTRL + F, ale przecież nie o to chodzi. Posłużymy się funkcją WYSZUKAJ.PIONOWO. Gdy zaczniecie wpisywać jej początek, Excel podpowie Wam również o istnieniu funkcji WYSZUKAJ.POZIOMO. Zasadniczo działają one na bardzo podobnej zasadzie – trzeba jedynie rozróżnić w jakim układzie, pionowym czy poziomym, są nasze dane. U nas tabela ma klasyczny wygląd – dane ułożone są w pionie.
Funkcja składa się z następujących elementów:
WYSZUKAJ.PIONOWO(szukana_wartość; tabela_tablica; nr_kolumny; [przeszukiwany_zakres])
W naszym przykładzie szukaną wartością będzie po prostu nazwa gminy – to jej chcemy przypisać wartość dochodu.
Tabela_tablica to zakres, w którym – przewidywalnie – znajduje się wyszukiwana przez nas argument, jak i przypadająca jej wartość, czyli obejmująca nazwę gminy i jej dochód. U nas taką tabelę tworzą kolumny E i F. Możemy je zaznaczyć całe. Jeżeli natomiast zaznaczycie dokładny zakres tabeli, wówczas należy użyć odwołania bezwzględnego (znaku „$”), bo w innym przypadku zakres będzie się przesuwał wraz z przenoszeniem formuły do niższych wierszy.
Nr_kolumny – podajemy tu numer kolumny z zaznaczonego zakresu, w której znajduje się wartość dochodu gminy. My zaznaczyliśmy tabelę obejmującą dwie kolumny – E i F – stąd numer kolumny wynosi 2, bo w drugiej kolumnie tego zakresu znajduje się pożądana wartość.
[przeszukiwany zakres] – ten parametr dotyczy oczekiwanego błędu (tj. czy chcemy poznać dokładny wynik, czy też dopuszczalny jest błąd). My liczymy na dokładne dane, więc w tym miejscu wpisujemy po prostu „0”.
Funkcja wygląda w następujący sposób:
Przeciągamy ją teraz do pozostałych komórek w pionie.
Okazuje się jednak, że jednej z gmin nie przypisano wartości – Krainie Czarów. Błąd pojawia się wtedy, gdy wyszukiwana przez nas wartość nie występuje w przeszukiwanym zakresie. Przysparza to problemów, gdy mamy wiele danych, a przez takie błędy nie możemy dalej pracować na stworzonym zestawieniu, gdyż nawet funkcja sumy nie będzie działać prawidłowo; wyrzucać będzie ten sam błąd braku danych.
#57 JEŻELI.BŁĄD
Nie chcemy, by w naszym zestawieniu pokazywane były tego typu błędy. W celu się ich pozbycia, należy użyć dodatkowej funkcji – =JEŻELI.BŁĄD.
Ta formuła będzie niejako nadrzędna wobec WYSZUKAJ.PIONOWO. Niezależnie bowiem od operacji, jaką wykonywać będziemy na danych, jeżeli wystąpi błąd jak powyżej, ma on zostać zastąpiony – dla przykładu – zerem.
Naszą wyjściową formułę trzeba więc wzbogacić o dodatkowy warunek. Całość poprzedzamy formułą =JEŻELI.BŁĄD, której elementami są: wartość i wartość jeśli błąd. Naszą wartością będzie rozpisana wcześniej formuła WYSZUKAJ.PIONOWO, niczego w niej nie zmieniamy. Po tej formule wpisujemy średnik, przechodząc do argumentu jeśli błąd, w miejsce którego wpisujemy 0. Jeżeli zatem wystąpi jakikolwiek błąd, bo dane nie będą dostępne, wówczas w ich miejsce wpisane zostanie 0.
#58 INDEKS i PODAJ.POZYCJĘ, gdy wyszukiwanie się nie sprawdza
Funkcje WYSZUKAJ.PIONOWO/POZIOMO są po prostu świetne i ich znajomość jest obowiązkowa. Niemniej mają one pewne ograniczenia. Funkcja wyszukiwania pionowego nie działa w lewo. Mam na myśli, że wartość, którą chcemy przypisać zmiennej, musi być zawsze po prawej (nie możemy wpisać, że znajduje się ona w kolumnie np. -3). W sytuacji, gdy chcemy jednak uzyskać taką informację, musimy skorzystać z kombinacji innych funkcji. Nieco bardziej rozbudowanych i mniej intuicyjnych, ale równie przydatnych.
Mamy teraz inną sytuację. Nazwie gminy, chcemy przypisać województwo. Formułą WYSZUKAJ.PIONOWO moglibyśmy zrobić jedynie coś odwrotnego. Wprowadźmy sobie zatem dwie funkcje, które razem poradzą sobie z tą trudnością.
Zaczynamy od funkcji =INDEKS, która jak widzicie, ma dwie postaci (tablicową i odwołaniową) – my skorzystamy z tej pierwszej.
=INDEKS(tablica; nr_wiersza; [nr_kolumny])
Zaczynamy od wybrania tablicy. W naszym przypadku tablicą będzie kolumna z województwami, bo te wartości chcemy pobierać i przypisywać gminom. Następnie przechodzimy do nr_wiersza, która prosi, byśmy podali z którego wiersza ma zostać podana nazwa województwa. I już tutaj wprowadzimy sobie funkcję PODAJ.POZYCJĘ.
(…)PODAJ.POZYCJĘ(szukana_wartość;przeszukiwana_tablica;[typ_porównania])
Szukana_wartość to po prostu nazwa gminy (zaznaczamy daną komórkę w tabeli, którą chcemy uzupełnić).
Przeszukiwana_tabela to z kolei zakres z nazwami gmin. Tak, by dana nazwa gminy mogła być odnaleziona w zakresie.
Typ porównania – również dokładny, a zatem wpisujemy 0.
Ostatecznie kombinacja formuł wygląda jak poniżej:
#59 WYSZUKAJ.PIONOWO nie widzi nazw, które występują w tabeli
Może się zdarzyć, że wyszukiwany przez Was element widnieje w tabeli, ale funkcja WYSZUKAJ.PIONOWO nie rozpoznaje go, komunikując jedynie błąd danych. Częstą przyczyną takiego stanu rzeczy jest dodatkowa spacja, która zapodziała się w nazwie elementu, który wyszukujemy. Może się to zdarzyć z naszej winy lub przy kopiowaniu nazw z innych źródeł. U nas takim przykładem jest Lumia 640, której funkcja nie rozpoznaje.
Prostym zabiegiem, o który powinniśmy w takiej sytuacji wzbogacić naszą formułę, jest formuła USUŃ.ZBĘDNE.ODSTĘPY. Wstawimy ją w miejsce argumentu szukana_wartość formuły WYSZUKAJ.PIONOWO, zaznaczając w dalszym ciągu tę samą komórkę, jednakże uwzględniającą zastrzeżenie o usuwaniu zbędnych spacji.
Zamiast „z palca” wpisywać hasło Lumia 640, mogłem w tym miejscu posłużyć się listą rozwijaną (o której pisałem w pierwszej części poradnika). Formuła WYSZUKAJ.PIONOWO dynamicznie dostosowuje się do danych w komórkach, nie trzeba jej odświeżać jak np, pivota.
#60 WYSZUKAJ.PIONOWO podaje pierwszą odnalezioną wartość wyszukiwanego elementu
Na koniec pozostawiłem jeszcze jedno bardzo ważne ograniczenie funkcji WYSZUKAJ.PIONOWO, o którym jednak nie każdy zdaje się pamiętać. Jeżeli przeszukamy tabelę, funkcja wykryje pożądany przez nas element, to należy pamiętać, że wartość zwrotna dotyczyć będzie pierwszego odnalezionego elementu spełniającego dane kryterium. Zobaczcie – w tabeli mamy teraz dwie Lumie 640, z różnymi cenami.
Formuła WYSZUKAJ.PIONOWO podała nam wynik (wyszukiwaną cenę) pierwszej odnalezionej Lumii.
Formuła LICZ.JEŻELI zlicza ile razy w zakresie występuje wyszukiwana wartość – widzimy, że dwa razy. Być może warto zatem dodać czasem dodatkową kolumnę do obliczeń, uwzględniającą tego typu parametr.
Dodatkowo możemy zrobić dwie następuję rzeczy:
Formuła SUMA.JEŻELI zlicza sumę kwot wszystkich powtarzających się elementów, czyli u nas dwóch pozycji.
Formuła ŚREDNIA.JEŻELI wyciąga natomiast średnią arytmetyczną z powtarzających się, wyszukiwanych wartości.
Na dziś to tyle. W tej części omówiliśmy sobie kilka podstawowych mechanizmów rządzących formułami, jak i szereg – wydaje mi się – bardzo przydatnych przykładów. W większości były to formuły logiczne i służące wyszukiwaniu treści, zgodnie z określonym kryterium. Uznałem, że to właśnie one chodzą najczęściej w parze z pozostałymi rodzajami formuł, warto jest je mieć „w małym paluszku”.
Za tydzień powrócimy z cyklem. Wtedy przejdziemy już do formuł statystycznych i finansowych. Być może uda mi się zademonstrować jeszcze działanie dodatków do Excela.
Dajcie znać, czy taki plan Wam odpowiada, ewentualnie zasugerujecie kierunek kolejnych artykułów. Planujemy w dalszej kolejności ruszyć z cyklem mini-poradników poświęconych już konkretnym i nieco bardziej rozbudowanym problemom. Tutaj przedstawiamy jedynie krótsze porady i tricki.
