Formuły Excel – 8 sposobów na oszczędzanie czasu

Formuły Excel są pewnie Twoim chlebem powszednim. Niezależnie od tego czy wykorzystujesz proste, czy złożone struktury – ich tworzenie, potrafi zajmować sporo czasu. Dlatego podzielę się z Tobą 8 sposobami na efektywną pracę z formułami, które oszczędzą Twój czas.

1# Użyj klawisza TAB, aby automatycznie uzupełnić formułę

Kiedy rozpoczynasz pisanie formuły od znaku „=”, a następnie zaczynasz wprowadzać nazwę funkcji Excel podpowiada Ci listę potencjalnych „kandydatów” pasujących do kolejno wpisywanych liter. Wystarczy, że przesuniesz się na wybraną funkcję strzałką w dół, a następnie klikniesz w klawisz TAB, aby ją wybrać. Excel automatycznie uzupełni pozostałą nazwę funkcji i otworzy nawias, abyś mógł wprowadzić argumenty.

2# Wykorzystuj tablice, aby uzupełniać formuły automatycznie

Tabela, tabeli nie równa. Nie chodzi o dowolną tabelkę jakich tworzysz dziesiątki, a „oficjalną” wersję tabeli dostępną na wstążce WSTAWIANIE > TABELA (lub skrót CTRL + T) Taka postać tabeli ma szereg zalet, a jedną z nich jest ta, że wprowadzając formułę w jednej komórce, reszta formuł w danej kolumnie uzupełni się automatycznie. Koniec zmartwień z przeciąganiem formuł i możliwością popełnienie błędu, jeżeli jakąś komórkę przez przypadek pominiesz.

3# Nie zamykaj funkcji nawiasem

Każda funkcja i formuła ma określoną strukturę. Najpierw mamy znak „=”, potem nazwa funkcji, otwieramy nawias itd. Każda formuła i funkcja musi także zostać zakończona nawiasem zamkniętym „)” Ale to nie oznacza, że musisz go wprowadzać. Gdy skończysz pisać formułę, po prostu kliknij ENTER, a Excel sam zamknie wszystkie niezbędne nawiasy.

4# Użyj F4 do zmiany formy adresowania

Tworząc formuły bardzo często musisz być świadomy jakiego rodzaju adresowania w nich użyjesz. Jeżeli kopiujesz czy przenosisz formułę pomiędzy różnymi adresami, często trzeba użyć adresu bezwzględnego, czy mieszanego. Aby, szybko przełączać się pomiędzy różnymi typami adresów użyj klawisza F4 zaraz potem, gdy skończysz wprowadzać adres jakieś komórki czy zakresu.

5# Przenoś formuły bez zmiany adresów odniesienia

Napisałeś formułę z wykorzystaniem adresu względnego ale chcesz przenieść wynik tej formuły w inne miejsce? Gdy, użyjesz metody kopiuj-wklej, Excel zmieni odwołania komórek źródłowych i formuła da inny wynik. Możesz po prostu „złapać” za obramowanie komórki, w której pierwotnie umieściłeś formułę i przeciągnąć ją w nowe, wybrane miejsce.

6# Kopiuj formuły bez zmiany adresów odniesienia

Możesz skopiować pojedynczą formułę z paska formuł, a później wkleić ją do nowej komórki (w trybie edycji komórki)

Nieco bardziej skomplikowane jest skopiowanie wielu formuł naraz. Aby tego dokonać postępuj wg tych kroków:

1. Zaznacz komórki z formułami, które chcesz skopiować.
2. Użyj skrótu CTRL + H, czyli znajdź i zamień.
3. W polu ZNAJDŹ wprowadź znak równości „=”, a w polu ZAMIEŃ dowolny znak lub znaki np. „#”
4. Teraz skopiuj komórki z formułami w wybrane przez Ciebie miejsce.
5. Ponownie użyj opcji ZNAJDŹ i ZAMIEŃ ale tym razem zamieniając „#” na „=”

7# Zamieniaj formuły na wartości

Dość często musimy wprowadzić formuły w dodatkowych, pomocniczych kolumnach. Wyniki obliczeń z tych formuł będą zmieniały się tak długo, jak długo w komórkach będzie umieszczona formuła.

Czasami jednak jesteś zmuszony usunąć dane, do których te formuły się odwołują. Jednocześnie nie chcesz tracić wyników obliczeń. W takim przypadku wystarczy zamienić komórki z formułami na wartości. Skopiuj komórki z formułami i użyj skrótu CTRL + ALT + V, a następnie wybierz opcję WARTOŚCI.

8# Użyj F9, aby zobaczyć wynik

Pisząc mniej i bardziej skomplikowane formuły, pewnie chciałbyś zobaczyć jaki wynik daje określony jej fragment. Ustaw się w wybranej części formuły, w trybie edycji na pasku formuł i naciśnij F9. Dzięki temu, możesz na bieżąco kontrolować wyniki swoich poczynań.

Chcesz lepiej poznać Excela? Zapisz się na Excelness News

Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera. 

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Zapisz mnie!

Loading…

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉

Podsumowanie

Każdy z tricków, może nie jest spektakularny ale jeżeli nauczysz się ich wszystkich używać, to jestem przekonany że Twoja praca z formułami Excel ulegnie znacznemu przyspieszeniu.

Znasz inne sposoby, których możemy użyć pracując na formułach? Podziel się Twoimi sposobami w komentarzu.

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.

Funkcja X.Wyszukaj, czyli nowa królowa wszystkich funkcji?

Był taki czas, kiedy pisałem że WYSZUKAJ.PIONOWO to królowa wszystkich funkcji. W tym artykule opiszę nową funkcję, która pojawiła się niedawno w Excelu 365. Funkcja X.WYSZUKAJ [ang. XLOOKUP] – bo o niej mowa – jest wg mnie jednym z przełomowych rozwiązań wprowadzonych ostatnio przez Microsoft.

Czy na tronie mamy nową królową? Czytaj dalej, a poznasz moje zdanie, a przede wszystkim możliwości jakie daje nowa funkcja.

Co nam daje funkcja X.WYSZUKAJ?

Funkcja ma bardzo wiele nowych możliwości i jest bardziej elastyczna niż wspomniana wcześniej WYSZUKAJ.PIONOWO czy WYSZUKAJ.POZIOMO.

Pewnie nieraz zdarzyło ci się korzystać z WYSZUKAJ.PIONOWO i napotkać na jej ograniczenia. Na przykład takie, że przeszukiwana kolumna w tabeli musi być zawsze z lewej strony. Było to jednym z powodów, dla których niektórzy nie radzili sobie z użyciem tej funkcji.

Część ograniczeń dawało się omijać, np. stosując połączone funkcje INDEKS i PODAJ.POZYCJĘ. Od „dziś” jednak możesz zapomnieć o ograniczeniach 🙂 Funkcja X.WYSZUKAJ eliminuje wszystkie najważniejsze, a dodatkowo zyskała zupełnie nowe funkcjonalności, np: możliwość jednoczesnego zwracania więcej niż jednej kolumny (jak w formułach tablicowych)

Składnia, czyli jak używać X.WYSZUKAJ?

Funkcja X.WYSZUKAJ ma sporo argumentów ale nie musisz używać wszystkich. W tym zakresie to również bardzo elastyczne rozwiązanie. Składnia wygląda następująco:

=X.WYSZUKAJ (szukana_wartość;szukana_tablica;zwracana_tablica;jeżeli_nie_znaleziono;tryb_dopasowania;tryb_wyszukiwania)

Co oznaczają poszczególne argumenty?

• szukana_wartość – podobnie jak w WYSZUKAJ.PIONOWO tu musi znaleźć się wartość, która występuje w obu tabelach – źródłowej i docelowej – tzw. klucz wg którego łączymy dane.
• szukana_tablica – tu zaznaczasz zakres tabeli (kolumny), w której funkcja „ma szansę” znaleźć szukaną wartość.
• zwracana_tablica – wskazujesz kolumnę, bądź kolumny(!) danych, które mają być zwracane jako wynik wyszukiwania.
• jeżeli_nie_znaleziono – podajesz tekst lub parametr, który ma się pojawić w momencie nie znalezienia danej szukanej wartości (odpowiednik użycia funkcji JEŻELI.BŁĄD).
• tryb_dopasowania – czy przeszukiwanie ma być dokładne (to jest domyślna opcja), czy przybliżone.
• tryb_wyszukiwania – w jaki sposób ma przebiegać kolejność przeszukiwania (od góry do dołu, od dołu do góry, z sortowaniem); domyślną opcją jest tu przeszukiwanie od pierwszego elementu od góry.

Przykład użycia

Przejdźmy do przykładu praktycznego. Powiedzmy, że chcesz wyszukiwać dane z dwóch oddzielnych zakresów danych / tabel. Przy czym kolumna przeszukiwana znajduje się na prawo od danych źródłowych (skąd będą pobierane informacje)

Zobacz, pierwsza tabela to same kontynenty. Natomiast druga tabela to państwa i odpowiednie cechy danego państwa. Załóżmy, że chcesz aby dla każdego państwa, zostały zwrócone informacje o: ludności i jednostce monetarnej.

Ustaw się w komórce C5 i wpisz:

=X.WYSZUKAJ(A5;$H$6:$H$34;$L$6:$M$34)

W pierwszym parametrze wskazałeś komórkę z państwem, które będzie wyszukiwane. W drugim argumencie wskazałeś kolumnę, gdzie funkcja będzie wyszukiwała nasze państwo. Ponieważ chcemy później skopiować formułę w dół – blokujemy wiersze z użyciem $. Kolejnym parametrem jest zwracana tablica, czyli zakres danych jaki chcesz zwrócić w wyniku wyszukiwania. W wyniku otrzymasz:

Funkcja X.WYSZUKAJ rządzi!

W artykule i na filmie pokazujemy Ci tylko niewielką część możliwości jakie daje nam funkcja X.WYSZUKAJ. Już tylko to wystarczy, aby stwierdzić że mamy nową królową 🙂

Nowa funkcja wyeliminowała najważniejsze z dotychczasowych bolączek i dała garść zupełnie nowych możliwości. Jeżeli chciałbyś wiedzieć więcej – jakie to możliwości – daj znam znać w komentarzu.

Na zakończenie, tradycyjnie mam do Ciebie prośbę. Podziel się tym artykułem ze znajomymi. Jestem pewny, że nie wszyscy znają nowe możliwości i możesz być jedną z pierwszych osób, które będą niosły „kaganek oświaty” 😉 Dziękuję!

Masz problemy z Excelem? Uzyskaj pomoc na grupie Keep Calm And Use Excel.

Regresja liniowa w Excelu, czyli prognozuj przyszłość

Regresja liniowa w Excelu pozwala na prognozowanie przyszłych wyników. Brzmi fantastycznie? Oczywiście, że tak ale nie jest to takie proste. Jednak warto znać tę metodę i sposób jej wykorzystania w Excelu, aby poszerzyć zakres swoich analitycznych możliwości.

Artykuł i film nie wyjaśnia wszystkich statystycznych zależności metody. Jeśli chcesz wiedzieć więcej o regresji liniowej, od strony teoretycznej odsyłam do innych stron. Tutaj dowiesz się jak szybko i w praktyce zastosować tę metodę w Excelu, aby prognozować przyszłe wyniki.

Regresja liniowa: podstawy

Funkcja regresji liniowej odpowiada na pytanie: „Jaką wartość przyjmie dane zjawisko (zmienna objaśniana), w zależności od wartości innych zjawisk (zmienne objaśniające)” W nazwie mamy pojęcie „liniowa”, zatem metoda zakłada, że zależności pomiędzy zjawiskiem objaśnianym oraz zmiennymi objaśniającymi jest właśnie liniowa.

Tyle teorii 🙂 Przejdźmy do praktyki. Mamy przykład, w którym będziemy chcieli przewidzieć wielkość sprzedaży dla kolejnych okresów, w zależności od nakładów na marketing. Na marginesie zawsze podobał mi się pewien cytat, dotyczący marketingu:

„Połowa pieniędzy wydawanych na reklamę jest wyrzucana w błoto. Problem w tym, że nie wiem, która to połowa”

John Wanamaker

Ponieważ mamy zależność tylko od jednej zmiennej – nakładów na marketing – funkcja przyjmie postać:

y = m1*x1 + b

gdzie m1 to tzw. współczynnik kierunkowy, a b to wyraz wolny.

Regresja liniowa z jedną zmienną

Aby obliczyć teoretyczną wielkość sprzedaży w następnym okresie, ustawiamy się w komórce w której chcemy mieć tę wartość i wykorzystujemy funkcję REGLINX.LINIOWA. 

Funkcja ma 3 parametry:

1. x – w naszym przypadku to nakłady na marketing jakie chcemy ponieść w kolejnym okresie,
2. znane y – to znana przez nas, historyczna wielkość sprzedaży w poprzednich okresach
3. znane x – to znana przez nas, historyczna wielkość nakładów na marketing w poprzednich okresach

Oczywiście podając zakresy komórek, w której znajduje się historyczna sprzedaż oraz nakłady na marketing, użyj adresowania bezwzględnego, aby odwoływać się cały czas do tych samych danych. Funkcja powinna wyglądać następująco:

Jak interpretować wyniki? Przy założeniu, że w styczniu 2019 roku wydamy 5 352 zł na marketing, to nasza przewidywana wielkość sprzedaży wyniesie 4 409 072 zł. Oczywiście dane są całkowicie teoretyczne. W rzeczywistości raczej nie osiągamy takich zwrotów z inwestycji 🙂

Możesz teraz skopiować funkcję na kolejne okresy, aby znaleźć odpowiedź na pytanie jak będzie wyglądała sprzedaż w kolejnych okresach. Poniższy wykres pokazuje zależność pomiędzy nakładami na marketing, a wielkością sprzedaży – właśnie na podstawie powyższych obliczeń.

Na sprzedaż wpływa wiele parametrów

Życie nie jest tak proste, aby sprzedaż zależała tylko od nakładów na marketing. W większości przypadków na dane zjawisko wpływa wiele zmiennych. Dlatego teraz pokażę Ci, jak wykorzystać funkcję regresji liniowej z użyciem wielu zmiennych.

W naszym przykładzie, przyjmiemy że wielkość sprzedaży zależy od następujących zmiennych:

• ilość sklepów 
• ilość promocji w okresie 
• nakłady na marketing 
• ilość pracowników w sklepie 

Podobnie jak wcześniej, na podstawie historycznych danych będziemy chcieli wyliczyć wielkość sprzedaży w kolejnych okresach. Aby tego dokonać, wykorzystamy inną funkcję – REGLINP.

Funkcja przyjmuje postać:

y = m1*x1 + m2*x2 + … + mn*xn + b

Regresja liniowa z wieloma zmiennymi

Aby łatwiej korzystać z tej funkcji, przygotuj sobie tabelę pomocniczą. Wypisz w niej wszystkie zmienne ale w odwrotnej kolejności niż są umieszczone w tabeli z danymi. To znaczy, że jeżeli mamy zmienną: “ilość sklepów” na pierwszym miejscu w tabeli z danymi, to w tabeli pomocniczej jest ona na ostatnim miejscu. I tak dalej.

Teraz za pomocą funkcji REGLINP wyliczymy współczynniki kierunkowe dla poszczególnych zmiennych. Ponieważ funkcja ma zwrócić nam wektor ze współczynnikami kierunkowymi i wyrazem wolnym (b) – zaznacz cały obszar w tabeli pomocniczej i wpisz funkcję. W przypadku naszych danych przykładowych, przyjmie ona taką postać:

{=REGLINP(D4:D27;E4:H27;PRAWDA;FAŁSZ)}

Pierwsza zmienna to znane y, czyli wielkości sprzedaży w poprzednich okresach. Następnie znane x – tutaj możesz podać cały zakres danych, gdzie znajdują się dane o: ilości sklepów, promocji, nakładów na marketing i pracownikach. 

Trzeci argument funkcji to tzw. stała, gdzie mówimy w jaki sposób ma być obliczana funkcja. Wpisz PRAWDA (1), co oznacza normalne obliczanie funkcji (bez wchodzenia w szczegóły – na tym nam w tym przypadku zależy)

Ostatni parametr mówi nam czy funkcja ma zwracać nam dodatkowe statystyki. Nie skorzystamy z opcji dodatkowych statystyk, dlatego wybieramy FAŁSZ (0)

Całą formułę musimy zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER, ponieważ jest to formuła tablicowa. W ten sposób obliczyliśmy współczynniki kierunkowe naszej funkcji.

Teraz możemy je wykorzystać w kolejnym okresie, aby przewidzieć teoretyczną wartość sprzedaży. W tym celu podstawiamy dane do wzoru i tworzymy formułę:

=$I$36+E28*$H$36+F28*$G$36+G28*$F$36+H28*$E$36

Czyli mnożymy w niej poszczególne współczynniki kierunkowe, ze znanymi wartościami poszczególnych zmiennych, takich jak: ilość sklepów, pracowników itd.

Finalnie otrzymamy teoretyczną wielkość sprzedaży w kolejnych miesiącach uzależnioną od czterech zmiennych. Wpisana przez nas formuła jest oczywiście dynamiczna – czyli możesz zmieniać wartości poszczególnych zmiennych, co wpłynie na przewidywaną wartość sprzedaży.

Możesz teraz symulować jak zmieni się sprzedaż, w zależności od wielkości pewnych zjawisk w Twojej firmie.

Chcę otrzymywać newsletter z ćwiczeniami z Excela, bezpłatnymi szablonami, rabatami na kursy i szkolenia oraz powiadomieniami o nowościach związane z blogiem Excelness.com. Rozumiem i akceptuję regulamin zapisu do newslettera. 

Twoje dane osobowe będą przetwarzane w celu obsługi newslettera przez Łukasz Pietrzaka na zasadach opisanych w polityce prywatności. W każdej chwili możesz zrezygnować.

Zapisz mnie!

Loading…

Dziękuję!

Proszę, sprawdź swoją skrzynkę pocztową.

Dodaj nasz adres do swojej książki adresowej, aby nasze wiadomości nie wpadały do spamu 😉

Podsumowanie

Czy musisz znać skomplikowane teorie, aby w praktyczny sposób wykorzystywać różne metody statystyczne? Stawiam tezę, że nie. Na pewno powinieneś wiedzieć jak interpretować wyniki i jakie są ograniczenia danej metody.

W omawianym w artykule przykładzie pokazałem Ci, jak w praktyczny sposób zastosować regresję liniową w Excelu. Możesz szybko wykorzystać tę wiedzę w swojej pracy. Oczywiście o ile rozumiesz, że nie warto iść do Twojego szefa z tezą, że wzrost nakładów na marketing o x zł, spowoduje wprost wzrost sprzedaży do y zł 😉

Jeżeli uznasz wiedzę zawartą w tym artykule za przydatną – udostępnij proszę artykuł w swoich social mediach, używając przycisku poniżej.