12 Trików Excela, dzięki którym zostaniesz Bohaterem w Twoim biurze

Jest taki program, z którego codziennie korzystają miliony, jeśli nie miliardy ludzi. Nie będzie wielkim zaskoczeniem jeśli powiem, że jest to flagowy program pakietu Office, czyli Microsoft Excel. W tym artykule pokażę kilkanaście jego funkcjonalności, które pozwolą Ci na większą automatyzację swojej pracy i przyjemniejszą pracę z Excelem. Jednak jeśli przećwiczysz je samodzielnie i wykorzystasz w swojej pracy, to zyskasz szacunek współpracowników i miano „eksperta od Excela”. Czytaj i działaj.

Każdy z nas miał kiedyś lub ma stale do czynienia z Excelem. Dla jednych jest to praca na skomplikowanych funkcjach, tabelach przestawnych, raportach i programowanie w VBA, inni zaś korzystanie z arkusza kalkulacyjnego ograniczają do ręcznego sumowania komórek. Dzisiejszy wpis jest kierowany do osób, które dobrze opanowały fundamenty i podstawy i teraz czują …niedosyt. Chciałyby więcej. Chciałyby automatyzować swoją pracę i jak najmniej roboty wykonywać po swojej stronie. W moim odczuciu te 12 trików pozwolą Ci na zrobienie dużego kroku do bycia ekspertem w zakresie Excela.

Artykuł jest dość długi, ale… jeśli miałbym wskazać tylko jeden trik z poniższej listy, który warto zapamiętać to bez wątpienia będzie to Trik nr 9 – złożenie PODAJ.POZYCJĘ i INDEKS. W tym przypadku naprawdę warto odpalić w tle Excela i samodzielnie wypróbować prawdziwą moc tego triku.

Żeby lepiej utrwalić wiedzę, przygotowałem dla Ciebie arkusze Excel, w których zobaczysz przykłady, które będę omawiać. Najlepiej jest przechodzić przez artykuł ćwicząc od razu na “żywym organizmie”.

ARKUSZE DO POBRANIA

A teraz zrób sobie jakąś dużą kawę lub herbatę i zapraszam na artykuł.

TRIK 1: Inspekcja formuł

Nie wiem jak to wygląda u Ciebie, ale podejrzewam, że masz tak samo jak ja. Powiedzmy, że pracujesz nad rozbudowanym plikiem Excela. Ma on kilka lub kilkanaście arkuszy, każdy z nich ma masę danych. Co gorsza – robisz w nim jakieś poprawki i chcesz nieco pozmieniać źródła danych do niektórych formuł. Trafiasz w którymś momencie na komórki wypełnione wartościami, które (jak Ci się wydaje) nie są potrzebne do dalszych przeliczeń. Czy aby na pewno?

 W tym momencie przyda się sekcja o nazwie Inspekcja Formuł. Znajdziesz ją standardowo rozwijając na górnym pasku przycisk Formuły:

Wybierz dowolną niepustą komórkę i kliknij na Śledź poprzedniki. Zobaczysz wtedy strzałki pokazujące, które komórki są wykorzystywane do obliczenia wartości wyjściowej komórki. Odwrotnie działa funkcja Śledź zależności. Tym razem Excel wskazuje wszystkie komórki, których wartości są obliczane przy użyciu danych z wybranej komórki. Excel potrafi śledzić także miejsca poza dokumentem w przypadku, gdy źródłem danych jest także plik zewnętrzny.

Jeśli po przeprowadzeniu Inspekcji formuł stwierdzimy, że żadna komórka nie jest przeliczana na podstawie tej, którą wybraliśmy, to spokojnie możemy ją usunąć z arkusza. Dodatkowo usuwamy denerwujące strzałki – możesz to zrobić klikając na Usuń Strzałki. Mniej wypełnionych komórek to szybsze działanie Excela i lepsza optymalizacja pliku. A dodatkowo pozbywamy się komórek mogących wywołać zamieszanie. Proste prawda?

TRIK 2: Operacje na wielu arkuszach

Pracujesz na plikach Excela składających się z wielu arkuszy o takiej samej strukturze? W takim razie możliwość wykonywania operacji na kilku arkuszach jednocześnie będzie dla Ciebie mega przydatna. Wyobrażam to sobie w ten sposób. W jednym pliku masz kilkanaście arkuszy. Każdy z nich wygląda praktycznie tak samo. Jedyną różnicą jest występująca w każdym arkuszu inna nazwa miasta / klienta / sklepu – you name it. Chcesz szybko dodać nowy wskaźnik do wszystkich arkuszy. Pomysłem dla tych, którzy mają zbyt wiele wolnego czasu będzie dokonanie zmiany ręcznie w każdym z kilkunastu arkuszy. Trwa to dłuższą lub nawet bardzo długą chwilę w zależności od charakteru zmian.

Ale nie martwcie się – jest też inna, sprytniejsza droga. Już piszę co trzeba zrobić. W pierwszej kolejności kliknij na pierwszy z arkuszy, który chcesz zaznaczyć. Następnie wciśnij klawisz CTRL i klikaj na wszystkie nazwy arkuszy, które chcesz zaznaczyć. Zobaczysz mniej więcej taki obrazek:

Teraz wystarczy puścić klawisz CTRL i wprowadzać zmiany w arkuszu. Każda zmiana, którą wprowadzimy zostanie uwzględniona we wszystkich zaznaczonych arkuszach. Dlatego ważne jest aby ich struktura była identyczna.

Po wprowadzenia zmian klikamy pasku arkuszy na dowolny z zaznaczonych wcześniej arkuszy. Masowa aktualizacja gotowa – przechodzimy dalej.

TRIK 3: Poprawność danych

Ręczne wprowadzanie danych do arkusza zawsze wiąże się z ryzykiem popełnienia literówki lub wpisania błędnej liczby. Aby zmniejszyć szansę na umieszczenie w komórkach błędnych wartości warto zastosować narzędzie poprawności danych. Szczególnie przydatna jest możliwość tworzenia listy wartości, które może przyjmować dana komórka. Przykład.

Ocena Traffic Light (z ang. Traffic Light Rating System) to łatwy do interpretacji, wykorzystujący podstawowe barwy sposób prezentacji danych wynikowych. Każdy wynik może otrzymać jedną z trzech ocen – świateł: Zielone, Żółte lub Czerwone.

Powiedzmy, że jeden z naszych sklepów osiągnął sprzedaż na poziomie 125. Musimy porównać się z ambitnym celem, który został ustawiony na poziomie 150, przy czym realne oczekiwania kończą się na liczbie 120. Oceniając wynik posługujemy się systemem Traffic Light:

Światło Wynik
ZIELONE 150 i powyżej
ŻÓŁTE 120 do 150
CZERWONE Mniej niż 120

W tym wypadku przypiszemy sklepowi światło ŻÓŁTE, ponieważ jego sprzedaż na poziomie 125 zawiera się w zakresie 120 do 150.

Mając do oceny wiele sklepów musimy ręcznie wpisywać ocenę. Aby uniknąć literówek takich jak „ZIELANE”, „ŻUŁTE” czy „CZYRWONE” warto zadbać o poprawność danych w polu oceny w następujący sposób.

W pierwszej kolejności – najlepiej w osobnym arkuszu – tworzymy słownik zawierający dostępne wartości pola ocena.

Następnie wracamy do pola oceny i z karty Dane i Sekcji Narzędzia Danych wybieramy polecenie Poprawność Danych:

W nowym okienku, które się pojawi w sekcji Kryteria poprawności z listy rozwijanej wybieramy opcję Lista:

Następnie wybieramy źródło danych do listy – nasz utworzony chwilę wcześniej słownik (ZIELONE – ŻÓŁTE – CZERWONE). I gotowe.

Dodatkowy trik polega na tym, aby zaznaczając zakres słownika przeciągnąć go o jedną komórkę w dół – na pustą komórkę. Pozwoli to na wybranie z listy wartości pustej, a nie jedynie słownikowej.

W przypadku, gdy będziemy chcieli wpisać w tak zabezpieczone pole wartość inną niż ta z listy, Excel wyświetli komunikat o niepoprawnej wartości. A teraz spróbuj samodzielnie dla swoich danych!

TRIK 4: Łączenie ciągu znaków

Bardzo przydatny i szybki trik. Szczególnie użyteczny gdy chcemy szybko połączyć ze sobą dwa lub trzy napisy znajdujące się w różnych komórkach. Składnia jest mega prosta wystarczy wskazać komórkę napisać znak „&” i wskazać drugą komórkę. Tak.. to już… Takie łączenie nie jest może idealne, bo między tekstami nie będziemy mieli przerwy, ale przecież zawsze możemy zostawić miejsce na przerwę w ten sposób:

=A1 & “ ” & A2

Do łączenia większej liczby tekstów możemy w Excelu użyć dedykowanej funkcji ZŁĄCZ.TEKSTY. Jej argumentami są kolejne teksty lub przerwy między nimi. Stąd wcześniejsze łączenie możemy zapisać również jako:

=ZŁĄCZ.TEKSTY(A1;“ ”; A2)

Tylko pisania więcej… 🙂

A teraz wracamy do naszych kolejnych trików:

TRIK 5: Łączenie dwóch wykresów

Jeśli kiedykolwiek w arkuszu Excel mieliście do wykonania kilka wykresów, to pewnie jednym z zadań było porównanie dwóch z nich. Powiedzmy, że mamy do porównania zachowanie cen akcji MBANK i PKO BP w pierwszym kwartale 2020 roku.

Pobieramy niezbędne dane – na przykład z serwisu Stooq. Następnie rysujemy dwa wykresy – mogą być to wykresy punktowe (oś X – daty, oś Y – cena zamknięcia z danego dnia).

Najlepiej porównywać wykresy przez nałożenie jednego na drugi. Excel umożliwia zrobienie czegoś takiego w bardzo prosty sposób. Wystarczy kliknąć na jeden z wykresów, skopiować go CTRL + C i wkleić za pomocą CTRL + V na drugi wykres. I voila 🙂

Jeśli chcesz wiedzieć w jaki sposób pobierać i obrabiać dane giełdowe z serwisu Stooq, to niedługo zaplanowałem wpis na ten temat.

TRIK 6: Wysyłanie mailem arkusza Excel nad którym pracujemy

W codziennej pracy często zdarza się, że przygotowujemy dane, zestawienia, analizy w Excelu a następnie rozsyłamy je do zainteresowanych osób. Jeszcze częściej chcemy wysłać coś „na szybko” pocztą Outlook.

Można oczywiście plik Excel edytować, zapisać i załączyć arkusz z dysku, ale jest też inna, szybsza droga. Microsoft pomyślał o tych, którym się śpieszy i postanowił skrócić drogę od edycji arkusza do wysyłki pocztą elektroniczną.

Jak to zrobić? Już piszę. Gdy skończymy edycję naszego pliku wchodzimy w Plik. Następnie wybieramy opcję Udostępnij i Wyślij wiadomość e-mail.

Po kliknięciu mamy gotowy szablon nowej wiadomości z załączonym dokumentem. Prościej chyba być nie może 🙂

Dotarliśmy do półmetka naszych zmagań. W międzyczasie zapraszam Cię do zapisania się na moją listę mailową. Dzięki temu będziesz na bieżąco z materiałami z bloga i co tydzień dostaniesz newsletter z ciekawymi materiałami. Zapisać się możesz w poniższym okienku:

Dołącz do Newslettera
Potwierdzam zapis na listę mailową!!!
Dołącz do listy mailowej ZobaczDane.pl. Dzięki temu od razu dostaniesz informację, gdy na blogu pojawi się nowy artykuł. Dodatkowo co tydzień wyślę Ci zbiór kilku ciekawych linków dotyczących zagadnień ze świata danych.
Nie będę wysyłał Ci żadnego spamu. U mnie masz same konkrety.

TRIK 7: Funkcja DNI_ROBOCZE

Zostało Ci zlecone zarządzanie projektem trwającym kilka miesięcy. Oczywiście musisz podzielić obowiązki pomiędzy uczestników, przypisać zadania, ustalić harmonogram. No właśnie – harmonogram. A ile właściwie dni roboczych mamy zaplanować?

Jeśli chcesz policzyć liczbę dni pomiędzy dwoma datami to sprawa jest prosta do zrobienia – szczególnie w Excelu. Wystarczy odjąć obie daty od siebie (zwykłym znakiem „ – ”) i dodać 1 – w przeciwnym razie nie policzymy dnia dzisiejszego. Wiadomo jednak, że wiele osób nie pracuje w sobotę i niedzielę, co automatycznie skraca tydzień i zaburza obliczenia. Co gorsza od czasu do czasu w kalendarzu wypada jakieś święto, którego pojawienie się oznacza kolejny odjęty dzień z planu.

Excel to jednak „sprytna bestia” i potrafi obsługiwać nawet takie zagadnienia. Posiada specjalną funkcję dedykowaną do takich obliczeń.

Funkcja DNI_ROBOCZE ma trzy argumenty na wejściu:

Data_pocz i data_końc – czyli data początku i końca projektu oraz opcjonalny argument święta. Ma on wielką moc. Jeżeli gdzieś z boku arkusza lub nawet w osobnym arkuszu wypiszemy wszystkie święta wypadające w danym roku, to możemy  tej listy skorzystać przy okazji funkcji DNI_ROBOCZE. Po prostu po podaniu początku i końca zakresu zaznaczamy listę dni świątecznych. Efektem będzie zliczenie dni pomiędzy dwoma krańcowymi datami. Plus jeżeli pomiędzy nimi wypada święto automatycznie ten dzień nie będzie brany do przeliczeń.

Czuję, że ta funkcja może pozwolić na właściwe rozplanowanie niejednego projektu!

TRIK 8: Od razu wartość zamiast formuły

W codziennej pracy z Excelem ważne jest posiadanie wiedzy na temat formuł i sposobów ich wykorzystania. Z drugiej strony nadrzędnym celem korzystania z Excela jest możliwość skorzystania z wyników generowanych przez te formuły. Jeśli wynik jest akceptowalny to w finalnym rozrachunku często formuły nie są aż tak istotne. Z drugiej strony czasem może nam nawet zależeć na tym, aby formuły stojące za wynikami nie były widoczne – żeby użytkownicy widzieli jedynie liczby, a nie wiedzieli jak do nich dotarliśmy.

W opisanych sytuacjach idealnie sprawdzi się kolejny trik. Pozwala on zaraz po napisaniu formuły przekonwertować komórki na wartości. Jak to zrobić? Sprawa jest bardzo prosta.

Za każdym razem, gdy kończysz pisanie formuły naciskasz Enter lub strzałkę. Aby od razu uzyskać wartości po prostu po napisaniu formuły naciśnij klawisz F9. I w sumie to tyle… Formuły ukryte, świat uratowany 🙂

TRIK 9: PODAJ POZYCJE/INDEKS

W jeden z firm, w której pracowałem kiedyś usłyszałem, że dwie funkcje Excela zrobiły tam rewolucję na miarę wynalezienia koła. Pierwsza z nich to PODAJ.POZYCJĘ a druga to INDEKS. Na pierwszy rzut oka ciężko znaleźć zastosowanie do każdej z tych funkcji z osobna. Kolejne mało użyteczne funkcje. Zgoda. Prawdziwa moc jest jednak w złożeniu obu funkcji razem. W tym przypadku 1+1 = 5 🙂 Potęga dwóch wspomnianych funkcji zastosowanych razem jest dużo większa niż mogłoby się wydawać.

Przejdźmy jednak do rzeczy. Po kolei omówimy obie funkcje:

Funkcje PODAJ.POZYCJĘ posiada trzy argumenty. Przede wszystkim należy podać jej wartość, której będzie szukać („szukana_wartość”). Gdy mamy to, wypadałoby pokazać funkcji gdzie szukać wartości („przeszukiwana_tab”). Ostatni argument to opcjonalny „typ_porównania”, który proponuję zawsze ustawiać na 0 (dokładne dopasowanie). Interpretacja tej funkcji jest następująca – powiedz mi, jakiej wartości szukasz a ja podam Ci jej pozycję w tabeli, którą wskażesz. Nic ciekawego. Chociaż… Nawet w takiej postaci funkcja fajnie sprawdza się do poszukiwania braków w dwóch porównywanych zbiorach. Sprawdźmy to.

Mamy dwa zbiory nazw zwierząt. W pierwszym z nich znajduje się zwierzę, które nie występuje w drugim. Jak wskazać to, które nie ma swojej pary?

Stosujemy w kolumnie C funkcję PODAJ.POZYCJĘ. Szukamy kolejnych nazw zwierząt z kolumny B w tablicy znajdującej się w kolumnie D. Na przykład dla Żyrafy (B2) w komórce C2 napiszemy:

=PODAJ.POZYCJĘ (B2; D:D; 0)

Drugim argumentem może być cała kolumna – powyższy zapis: „D:D”.

Stosując formułę do kolejnych zwierząt otrzymujemy takie rezultaty:

Prawie wszystkie zwierzątka mają swoje numery, oprócz Jednorożca, przy którym występuje wrogo brzmiący symbol „#N/D!”. Funkcja nie znalazła Jednorożca – nie ma go w Zoo we Wrocławiu. Dobre narzędzie do szukania braków danych!

Drugi z bohaterów tego akapitu to INDEKS. Składnia tej funkcji również zawiera trzy argumenty w tym jeden opcjonalny:

Pierwszy z argumentów określa zbiór, z którego będziemy pobierać wartości („tablica”), drugi jest numerem komórki w wybranym przez nas zbiorze („nr_wiersza”). „Nr_kolumny” jest argumentem opcjonalnym – dla naszych zastosowań nie będziemy go wykorzystywać.

Funkcja INDEKS działa w ten sposób, że zwraca wartość znajdującą się w komórce wskazanej przez „nr_wiersza” w ramach zakresu „tablica”. W naszym przykładzie ze zwierzątkami w ZOO, jeśli napiszemy:

=INDEKS(B:B, 3)

to w wyniku otrzymamy napisz „Słoń” (znajduje się w trzecim wierszu w kolumnie B).

Tak jak pisałem wcześniej prawdziwa moc jest jednak ukryta w złożeniu obydwu funkcji, które pokazałem. Wróćmy do naszych sympatycznych zwierzaków. W drugim arkuszu mamy tabelę opisującą kilka szczegółów dotyczących każdego z nich.

Chcemy szybko i najważniejsze – skutecznie przypisać do każdego ze zwierzaków imię i miasto pochodzenia z tabeli ze szczegółami. Jak to zrobić? Użyję złączenia funkcji INDEKS i PODAJ_POZYCJĘ w ten sposób, że argument funkcji INDEKS opisujący nr_wiersza będzie wynikiem działania funkcji PODAJ.POZYCJĘ:

Schematycznie możemy zapisać formułę jako:

=INDEKS(cała kolumna C ; PODAJ.PO ZYCJĘ ( Żyrafa; cała kolumna B; 0))

Co zrobi Excel? W pierwszej kolejności wykona PODAJ.POZYCJĘ – czyli wskaże, na którym wierszu kolumny B znajduje się napis „Żyrafa” (pozycja nr 2 – liczymy też nagłówek). Następnie wyszuka w zakresie kolumny B napisu znajdującego się na pozycji nr 2 (właśnie tej co „Żyrafa”) – wynikiem będzie „Jerzy”. Podobnie zrobimy dla wszystkich innych zwierzaków. W praktyce złożenie PODAJ.POZYCJĘ i INDEKS jest tym samym co LEFT JOIN w SQL – pozwala dołączać do wyjściowego zakresu danych informacje z innych tabel, arkuszy poprzez powiązanie pewnym kluczem.

TRIK 10: SUMA.WARUNKOW

Bardzo przydatną funkcjonalnością udostępnianą w ramach narzędzia Microsoft Excel są tabele przestawne. Na ich temat powstała nieskończona liczba wpisów, szkoleń, kursów i filmów. Nie zawsze jednak mamy czas na tworzenie i dopracowywanie pól tabeli przestawnej, ale szybko potrzebujemy zagregować pewne dane. Co wtedy zrobić? Z pomocą przychodzi bardzo prosta, ale również super użyteczna funkcji SUMA.WARUNKOW. Jak działa?

Poniższa tabelka prezentuje zarobki rolników i nie-rolników (opisuje to kolumna Czy_rolnik). Drugą klasyfikacją jest miejsce prowadzenia działalności, a w szczególności czy znajduje się w Województwie Mazowieckim. W ostatniej kolumnie mamy liczbową wartość zarobku w ostatnim miesiącu.

Nasze zadanie to podanie ile zarobili rolnicy z Województwa Mazowieckiego. Możemy oczywiście ręcznie dodawać poszczególne komórki, ale chodzi nam o rozwiązanie jak najbardziej automatyczne. Idealnie sprawdza się w tym wypadku tabela przestawna lub funkcja SUMA.WARUNKOW.

Powyższa funkcja posiada zestaw argumentów. Po pierwsze należy jej podać zakres skąd będzie brała dane do posumowania. Po drugie trzeba podawać zakresy danych do sprawdzania warunków, po trzecie podać jakie warunki nas interesują. Kolejne warunki i zakresy podajemy schematem:

=SUMA.WARUNKOW( zakres do sumowania; zakres do warunku #1; warunek #1; zakres do warunku #2; warunek #2; …. )

W naszym przypadku będzie to wyglądać następująco:

Mam nadzieję, że po tym przykładzie wszystko się wyjaśniło i że znajdziesz zastosowanie dla SUMY.WARUNKÓW w swoich projektach!

TRIK 11: Scenariusze

Jak każde większe oprogramowanie, również pakiet Office ma mnóstwo ciekawych, ale mniej znanych funkcjonalności. Jedna z nich wydaje się szczególnie przydatna, gdy w swojej pracy musisz podejmować decyzje na podstawie różnych wariantów tego samego zadania. Sekcja Analiza warunkowa oferuje narzędzie nazywane Menedżer Scenariuszy. Aby pokazać jego działanie zacznijmy od przykładu. Firma, dla której raportujesz inwestuje w złoto i miedź. Poniżej wpisujemy najważniejsze czynniki, które pozwolą na przeliczenie zysku firmy dla kilku scenariuszy.

Jak widać wysokość zysku zależeć będzie od cen surowców oraz kursów walutowych. Ich zmiany będą powodować spadek lub wzrost zysku lub straty. Uruchamiamy polecenie Dane -> Analiza warunkowa -> Menedżer Scenariuszy, aby w kolejnym kroku nacisnąć guzik Dodaj. W wyświetlonym okienku wpisujemy nazwę scenariusza – u mnie „Scenariusz bazowy”, a także podajemy zakres komórek, które będą ulegać zmianie – u mnie to wszystkie wartości kursów walut i cen surowców. Po zatwierdzeniu pokazuje się kolejne okienko, gdzie automatycznie podstawiają się obecne wartości wpisane do Excela. Zatwierdzamy i pierwszy scenariusz mamy gotowy. Na potrzeby naszego zadania przygotowałem jeszcze kilka scenariuszy (wzrost/spadek kursów i wzrost/spadek cen surowców). Spójrzcie jak to wygląda w Menedżerze Scenariuszy:

Najciekawsze jednak dopiero przed nami. Oczywiście każdy ze scenariuszy można edytować – zarówno w zakresie nazw, jak i wartości używanych do wyznaczana naszego ostatecznego zysku. Najważniejsze z naszego punktu widzenia będzie rzecz jasna porównanie wyników we wszystkich scenariuszach. Wówczas wiemy jak zachowają się nasze przychody w różnych wariantach. Na tej podstawie poprzez oszacowanie prawdopodobieństw poszczególnych scenariuszy możemy szacować oczekiwany zysk. Excel umożliwia szybkie przygotowanie podsumowania danych z kolejnych wariantów. Wystarczy, że w Menedżerze scenariuszy wybierzesz opcję Podsumowanie i przez Twoimi oczami pojawi się…

Fajne, gdy musisz szybko przeliczyć możliwe opcje – a przykład korzystając z Menedżera Scenariuszy nie pomylisz się zmieniając tylko niektóre z komórek zamiast wszystkich, a poza tym łatwo możesz zarządzać kolejnymi wersjami prognoz. Zdecydowanie do wykorzystana w działach finansowych wielu firm!

TRIK 12: Szukaj wyniku

Excel został stworzony, aby ułatwiać życie ludziom pracujących na liczbach. Po pierwsze przeciągając formuły bardzo szybko wykonujesz to samo działanie dla rożnych warunków początkowych zdejmując z siebie ciężar obliczeń. Po drugie Excel umożliwia automatyzację prostych czynności w stylu wstaw tę liczbę tutaj, policz coś, dodaj nowy wiersz i od nowa. Do tego służy wbudowany w Excel język makr VBA. Po trzecie Excel pomaga rozwiązywać problemy, w których musielibyśmy wykonać zadanie niekiedy dziesiątki, albo i setki razy, aż uzyskalibyśmy pożądany wynik. Właśnie po to powstało narzędzie Szukaj wyniku. Jego działanie zobrazuję na przykładzie poprzedniego zadania – z kursami i surowcami.

Jako właściciel firmy zastanawiałbym się jakie maksymalne zmiany mogą się zmaterializować, abym w dalszym ciągu był przynajmniej „na zero”. Patrząc na zadanie – o ile procent mogą spać kursy walut i ceny surowców, aby zysk był równy 0.

Przygotowuję arkusz dodając kolumnę z nagłówkiem „Po zmianie” – przeliczam w niej nowe wartości po wzroście/spadku cen i kursów o 10% (ta liczba w komórce C13 = 10% jest wstawiona roboczo).

Mając tak przygotowany arkusz (oczywiście załączam go na końcu tego artykułu) możemy zaprząc do pracy narzędzie Szukaj wyniku. Znajdziesz je w zakładce Dane -> Analiza warunkowa -> Szukaj wyniku. Po kliknięciu w pojawiającym się okienku musimy uzupełnić trzy pola:

  • Ustaw komórkę – komórka, dla której chcemy uzyskać docelową wartość – w naszym przypadku komórka w kolumnie „Po zmianie”, w której liczymy Zysk firmy
  • Wartość – docelowa wartość komórki wskazanej w polu Ustaw komórkę – u nas 0, bo chcemy sprawdzić jakie warunki powodują, że nie mamy już zysku
  • Zmieniając komórkę – która komórka powinna zmieniać swoją wartość – tą komórką Excel będzie żonglował, aby znaleźć docelową wartość.

Po naciśnięciu OK, Excel wykonuje wiele podstawień wartości w komórce wskazanej jako Zmieniając komórkę. Robi to tak długo, dopóki nie otrzyma wartości wskazanej w polu Wartość. Czasami takie podstawianie liczb jest skazane na porażkę (na przykład gdy szukamy wartości ponad maksymalną dostępną) – wtedy Excel poda wynik końcowy swoich starań z informacją, że nie udało mu się znaleźć rozwiązania. W naszym przypadku jednak wynik został znaleziony:

Jak dowiedzieliśmy się od Excela – wystarczy spadek kursów i cen surowców o nieco ponad 2%, aby nasza firma przestała zarabiać. Hmmm warto by było zastanowić się może nad dywersyfikacją portfela…

To już wszystko co przygotowałem dla Ciebie na dzisiaj.

Jeśli artykuł był pomocny to bardzo proszę:

  1. udostępnij ten artykuł w mediach społecznościowych
  2. powiedz o nim jednej osobie, której może się przydać
  3. zapisz się na listę mailową, aby być na bieżąco z artykułami na blogu i … dodatkowo przesyłam newsletter z ciekawymi materiałami wybranymi specjalnie dla Ciebie 🙂

Na newsletter możesz zapisać się w poniższym formularzu.

Dołącz do Newslettera
Potwierdzam zapis na listę mailową!!!
Dołącz do listy mailowej ZobaczDane.pl. Dzięki temu od razu dostaniesz informację, gdy na blogu pojawi się nowy artykuł. Dodatkowo co tydzień wyślę Ci zbiór kilku ciekawych linków dotyczących zagadnień ze świata danych.
Nie będę wysyłał Ci żadnego spamu. U mnie masz same konkrety.

Znajdziesz mnie również w social media.

Może Ci się spodobać...

17 komentarzy

  1. Hej ✋,

    Fajny wpis. Niektóre triki były dość zaawansowane.

    Moją radą było by, byś dał Krzyśku arkusze do ćwiczenia na sam początek tego artykułu. Ja przeczytałem go pobierznie myśląc że tych arkuszy nie ma, a jak byłyby na początku to bym je ściągnął i czytając bym sobie w tym samym czasie sprawdzał to o czym pieszesz na tych arkuszach.

    Napisz jakiś artykuł o języku Python to z chęcią przeczytam 🙂.

    Pozdrawiam 👋.

    1. Hej Mateusz,
      Dzięki za komentarz – zgodnie z Twoją sugestią przesunąłem arkusze na sam początek posta!
      Co do Pythona to wszystko w swoim czasie. Zaczynamy powolutku, ale już kolejny wpis powinien delikatnie dotknąć Pythona.
      Pozdrawiam

  2. Hej

    Ciekawy artykuł – dzięki.
    Pytanie o trik 9 – nie jestem mastah 😉 w excelu ale do tego triku podobnie (o ile nie tak samo) działa funkcja VLOOKUP ?
    A może się mylę i jest jakaś znacząca różnica między nimi ?

    Pozdrawiam

    1. Cześć,
      Tak zgadza się podobnie działają funkcje WYSZUKAJ.POZIOMO/PIONOWO. Ja jednak jako pierwszą poznałem kombinację INDEX i MATCH, więc przyzwyczaiłem się do niej i jest dla mnie bardziej intuicyjna. Poza tym w VLOOKUP trzeba liczyć kolumny co bywa uciążliwe i może generować błędy zwłaszcza przy dużych zbiorach.

  3. Odnośnie powyższego, to dla mnie VLOOKUP jest niezastąpione. Dużo prostsze i u mnie nie ma problemów nawet przy sporych zbiorach.

  4. Hello
    Przy okazji triku 10 SUMA.WARUNKÓW i innych podobnych warto dodać, że można budować bardziej rozbudowane warunki.
    “=A1+X3/2”
    “>8+A1”

    Jeżeli chodzi o trik 9 INDEX, to również mam odczucie, że wyszukaj.pionowo jest lepsze w większości przypadków. Jest bardziej zwięzłe, używa tylko połowy funkcji i połowy nawiasów, w porównaniu do index i podaj.pozycję. Wyszukaj.pionowo ma bardziej logiczną i płynną leksykalnie składnię: Wyszukaj pionowo, “co”, “gdzie” i “pobierz dane z”. Tymczasem index: “pobierz dane z”, “podaj pozycję”, “czego”, “gdzie”. Warto więc wspomnieć o obu funkcjach, aby czytelnik miał wybór. INDEX przydaje się, kiedy wyszukujemy dane z tabeli a nie wektora danych.

    1. Dzięki za Twój komentarz! Oczywiście istnieje wiele wiele innych trików które warto by było pokazać. Plus oczywiście dodatkowe warianty tych o których piszę w artykule.
      A jaki jest Twój ulubiony trik którego jeszcze nie było na liście? 🙂

      1. Niestety nie mam wielu asów w rękawie, bo ograniczałem się do podstawowych zastosowań. Warto znać tabele przestawne, bo łatwo zastępują bardzo skomplikowane funkcje. Warto wspomnieć, że są funkcje podobne do suma.warunków, jak licz.warunki. Z podstawowych rzeczy, jeżeli filtrujemy i usuwamy niektóre wiersze danych, to przy dużej liczbie danych, szybciej jest skopiować i wkleić to co potrzebne, niż usuwać resztę, nawet jeżeli więcej kopiujemy niż usuwamy. Nawet kilkaset razy szybciej. Trik 4 jest świetny, często używałem łączonych komórek jako klucza w funkcji wyszukaj.pionowo.

        1. Twoje przykłady też są bardzo fajne – śmiało mogłyby znaleźć się w zestawieniu. Jeśli chodzi o tabele przestawne to rzeczywiście – używam i szanuję 🙂 są idealne do raportowania z większy zbiorów danych w Excelu.
          Tak naprawdę to każda osoba używająca Excela do pracy wcześniej czy później poznaje fajne triki, które ułatwiają jej wykonywanie zadań lub przyspieszają je.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *