Funkcja TEKST, czyli jak wyświetlić liczby w innych formatach

Funkcja TEKST pozwoli Ci wyświetlić liczby w innym, wybranym przez Ciebie formacie. Co szczególnie interesujące i przydatne – możesz połączyć tekst z wartością walutową lub datą w innej komórce i wyświetlić obie wartości we właściwych formatach.

Co daje nam funkcja TEKST?

Najkrócej mówiąc, funkcja zamienia wartości liczbowe na tekst lub inny format (np. datę) Ta właściwość ma swoje wady i zalety.

Wada oczywiście polega na tym, że jeśli zamienisz liczbę na tekst – to nie będziesz mógł już na niej dokonywać obliczeń. Dlatego z reguły, funkcję wprowadzamy w osobnej komórce i odwołujemy się do wartości liczbowej podając ją jako argument.

Z drugiej strony, możesz potrzebować powyższej właściwości przy funkcjach wyszukujących. Przykładowo dość popularnym błędem przy stosowaniu funkcji WYSZUKAJ.PIONOWO jest niezgodność formatów w danych wyszukiwanych i będących szukanymi. Mówiąc prościej – na przykład szukasz id produktu, który jest liczbą w danych gdzie występuje jako tekst. Zobacz jak to wygląda poniżej. Łatwo o pomyłkę – prawda?

I tu z pomocą przychodzi funkcja TEKST, dzięki której zamienisz liczby na tekst i wyeliminujesz błąd wyszukiwania.

Składnia, czyli jak używać TEKST?

Funkcja TEKST ma tylko dwa argumenty. Składnia wygląda następująco:

=TEKST(wartość;format_tekst)

Co one oznaczają?

• wartość – to po prostu liczba wskazana z komórki lub wpisana ręcznie (pamiętaj, że daty to tak naprawdę liczby w Excelu)
• format_tekst – to format w jakim chcesz wyświetlić daną liczbę

Teraz pewnie zapytasz mnie albo sam siebie: „no dobra…ale skąd mam wiedzieć jaki to ma być format i co mam wpisać?”. Na szczęście to nie jest skomplikowane.

Wystarczy, że wywołasz menu formatowania komórek (skrót CTRL + 1), a zyskasz dostęp do dowolnego formatu jakiego zechcesz użyć. Przykłady znajdziesz poniżej.

Przykłady użycia

Pokaże Ci dwa dość popularne przykłady użycia funkcji TEKST.

Przykład 1: zmiana sposobu wyświetlania daty

Powiedzmy, że w komórkach masz daty zapisane standardowo, w formacie: RRRR-MM-DD (przykładowo w komórce A2 masz: 2021-03-01) Chciałbyś, odwołując się do wybranych komórek wyświetlić tę datę jako: „1 marzec 2021”. Excel jeszcze nie odmienia przez przypadki 😉 Do dzieła!

=TEKST(A2;"d mmmm rrrr")

Jak widzisz argumenty związane z formatem tekstu zapisujemy w cudzysłowie. Literka „d” to oczywiście dzień, „mmmm” – to pełna nazwa miesiąca, a „rrrr” roku. Pokombinuj z mniejszą i większą ilością liter dla uzyskania innych efektów.

Przykład 2: łączenie tekstu z wartością w walucie

Nie wiem jak u Ciebie w pracy, ale mnie zdarzało się analizować wykonanie budżetów. Oczywiście jednym z podstawowych zadań jest porównywanie wykonania z planem. Powiedzmy, że masz taką formułę w komórce C2, która sprawdza procentowe wykonanie budżetu. Chciałbyś, aby w Twoim raporcie oprócz wartości % pokazywał się w jednej komórce tekst typu: „Budżet został zrealizowany w … %”

Ustaw się w wybranej komórce i wpisz:

="Budżet został zrealizowany w "&TEKST(C2;"0,0%")

Zaraz po znaku „=” mamy oczywiście tekst. Potem znak łączenia „&”, który zastępuje funkcję ZŁĄCZ.TEKSTY. Na końcu łączymy to z funkcją TEKST, gdzie jako format wpisałem „0,0%”.

Skąd wiedziałem, że właśnie tak należy to zapisać? No właśnie dzięki menu formatowania komórek. Najpierw formatuję liczbę w wybrany przez siebie sposób, a następnie odczytuję jej format z kategorii NIESTANDARDOWE.

---

Czy chcesz bezpłatny test z Excela?

---

---

---

Podsumowanie

Jak widzisz funkcja TEKST jest bardzo przydatna i powinna znaleźć się w Twoim „arsenale”. Nie tylko poradzisz sobie dzięki niej z problemem wyszukiwania, ale stworzysz odpowiednie komunikaty we właściwych formatach. Ta funkcjonalność jest także bardzo przydatna, gdy tworzysz dashboardy.

Jeżeli znasz inne, ciekaw sposoby wykorzystania tej funkcji – podziel się z nami w komentarzu.

---

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

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

---

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.

---

---

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.

Odchylenie standardowe w Excelu

Odchylenie standardowe to jedna z podstawowych miar statystycznych, która pomoże Ci lepiej zrozumieć analizowane przez Ciebie dane. Na pewno często liczysz średnią arytmetyczną. Interpretacja wyników z samej średniej może być (i często jest) niewystarczająca. Odchylenie standardowe „poinformuje” Cię jak zmieniają się wyniki, wokół tej średniej. A to może mieć kluczowe znaczenie dla dalszych obliczeń i wniosków.

Średnia, średniej nierówna

Mamy taką sytuację. Prowadzimy miesięczny rejestr wydatków na hobby dla kilku osób. W kolejnych kolumnach umieścimy miesiące, w których odnotujemy wydatki. Na końcu możemy oczywiście podsumować wydatki roczne.

Jednym z najprostszych sposobów na sumowanie jest zaznaczenie danych oraz pustej komórki po prawej stronie, gdzie mamy umieścić wynik. Teraz użyj skrótu klawiszowego ALT + =.

Po podsumowaniu zobaczysz, że każda z osób wydała 4 200 zł. Jednak w tabeli widzimy, że każda osoba inaczej wydawała te pieniądze w poszczególnych miesiącach. Suma nie jest zatem wystarczającą miarą, aby opisać nasze dane.

Pierwszą podstawową miarą jakiej możemy użyć jest średnia arytmetyczna. Jej użycie pozwoli nam sprawdzić jak średnio, w każdym miesiącu wyglądały wydatki każdej osoby. 

W wyniku widzimy, że każda osoba wydawała średnio w miesiącu 350 zł na swoje hobby. Jeśli przyjrzysz się danym, to znowu okazuje się, że ta miara nie jest wystarczająca, aby opisać różnorodność wydatków w poszczególnych miesiącach. Za chwilę okaże się że jedna średnia „nie równa” innej średniej.

Odchylenie standardowe

Zobacz, że Ania w każdym miesiącu wydawała bardzo podobnie. Kasia przez pierwsze pół roku nie wydała nawet złotówki, natomiast później wydawała bardzo dużo. Z kolei u Tomka bywało różnie. Czasami nie wydał nic, czasami 700 zł, a czasami jego wydatki były bliskiej średniej. 

Aby opisać tego typu rozbieżności możesz użyć odchylenia standardowego. Ta miara pokaże jak wydatki w poszczególnych miesiącach, przeciętnie różniły się od średniej arytmetycznej.

Excel oczywiście ma wbudowanych wiele funkcji statystycznych, w tym funkcję odchylenia standardowego:

=ODCH.STAND.POPUL(zakres komórek)

W przypadku naszych danych, otrzymamy następujące wyniki:

• Ania 50 zł
• Kasia 350 zł
• Tomek 189,30 zł

Jak je interpretować?

Możemy powiedzieć, że średnie wydatki na hobby Ani w poszczególnych miesiącach różniły się od średniej arytmetycznej przeciętnie o 50 zł. Za to w przypadku Kasi, było to aż 350 zł.

Zatem, im mniejsza wartość odchylenia standardowego tym wydatki w poszczególnych miesiącach są bardziej zbliżone do średniej. Większe odchylenie standardowe, oznacza że wydatki w poszczególnych miesiącach bardziej odbiegają od średniej arytmetycznej. 

---

---

Podsumowanie

Dzięki zastosowaniu odchylenia standardowego możesz w łatwy sposób określić czy Twoje dane są stabilne czy zmienne. To może mieć duże znaczenie dla interpretacji wyników.

Przykładowo, jeżeli masz dwie grupy pracowników i w każdej z grup średnia ocen wynosi 3, to czy tylko na tej podstawie możesz powiedzieć, że obie grupy są do siebie podobne? Zastosowanie odchylenia, powie Ci znacznie więcej.

Jeśli wiedza zawarta w tym artykule i filmie okazała się dla Ciebie przydatna – udostępnij go proszę w swoich mediach społecznościowych. Kliknij na ikonę, poniżej.

Formuły tablicowe – gdy standardowa funkcja nie wystarczy

Formuły tablicowe wykorzystasz do rzeczy pozornie niemożliwych. Spróbuj za pomocą standardowej funkcji policzyć medianę wynagrodzeń dla handlowców, pracujących w regionie północ, od co najmniej 3 lat. Nie pomoże nawet tabela przestawna. A to tylko prosty przykład.

Formuły tablicowe pomogą Ci w tym i wielu innych złożonych obliczeniach. Co ciekawe, możesz zaangażować do pracy w ten sposób (tablicowy) wiele „standardowych” funkcji, np. ŚREDNIĄ, MEDIANĘ, JEŻELI etc.

Formuła tablicowa, czasami nazywana też CSE od CTRL + SHIFT + ENTER, które kończy jej wprowadzanie to sposób działania, a nie jakaś pojedyncza, tajemnicza funkcja. W artykule wyjaśnię Ci praktyczne działanie na przykładach, ale wykorzystanie formuł tablicowych to prawdziwy „temat rzeka”.

Formuła tablicowa: podstawowe reguły

Używając na przykład funkcji JEŻELI, wprowadzasz ją do pojedynczej komórki, a sama funkcja pobiera argumenty również z pojedynczych komórek.

Formuła tablicowa tym różni się od standardowej funkcji, że może:

• dokonywać „w tle” wielu obliczeń, zwracając wynik do pojedynczej komórki,
• zwracać wynik swoich obliczeń do więcej niż jednej komórki jednocześnie.

W artykule skupimy się na tym pierwszym przypadku. Zanim przejdziemy do konkretnego przykładu, musisz pamiętać o kluczowej zasadzie – każdą formułę tablicową zatwierdzasz kombinacją klawiszy CTRL + SHIFT + ENTER. Bez tego formuła nie zadziała. W wyniku tego, na końcu i na początku formuły pojawiają się nawiasy klamrowe { }, na przykład:

{=SUMA(JEŻELI(A:A="Północ";JEŻELI(B:B="Klient X";C:C)))}

Formuły tablicowe: przykład z użyciem ŚREDNIEJ i JEŻELI

Aby zrozumieć działanie formuł tablicowych posłużmy się prostym przykładem. Będzie łatwiej, jeżeli przed dalszą lekturą pobierzesz ten plik. Masz 10 przypadków sprzedaży dla różnych handlowców, w różnych miastach. Twoim zadaniem jest policzyć średnią sprzedaż dla Marcina z Łodzi. Oczywiście możesz to samo zrobić np. w tabeli przestawnej, ale wykorzystamy ten uproszczony przykład, żeby zobrazować działanie formuł tablicowych.

Gdy wprowadzisz całą formułę do pojedynczej komórki i zatwierdzisz klawiszami CSE, „w tle” będą się działy pewne operacje 🙂 Przede wszystkim, formuła korzystając z właściwości funkcji JEŻELI, sprawdzi:

• wszystkie komórki w kolumnie Miasto i jeżeli napotka tam Łódź – wirtualnie zaznaczy tam 1 (czyli prawda),
• wszystkie komórki w kolumnie Handlowiec i napotykając Marcina – podobnie postawi 1,
• sprzedaż pozostaje bez zmian, bo nie mamy tam żadnego warunku.

Jak widzisz w tabelce powyżej, są 3 wiersze w których warunek z miastem i handlowcem jest spełniony jednocześnie. Podczas moich szkoleń tłumaczę to jako mnożenie przez siebie każdej komórki w danym wierszu. I tak na przykład: w wierszu 1, mamy 0 x 1 x 26900. Wynik, rzecz jasna to 0, więc formuła nie weźmie tej wartości sprzedaży w końcowym wyniku. Z kolei, w wierszu 5, mamy 1 x 1 x 50680 – co daje wynik 50680. I oczywiście ta wartość sprzedaży zostanie uwzględniona przy wyliczaniu średniej.

No właśnie…, a gdzie tu średnia zapytasz? Bo do tej pory to tylko sprawdzamy warunki i mówimy PRAWDA lub FAŁSZ – jak w przypadku funkcji JEŻELI. Aby policzyć średnią, na wybranych w powyższy sposób wartościach trzeba użyć (niespodzianka!) funkcji ŚREDNIA 🙂

To co musisz zrozumieć, to fakt że ŚREDNIA zadziała na końcu (po wybraniu odpowiednich wartości spełniających warunki) ale w zapisie formuły umieścimy ją na początku. Generalnie w formułach Excela, funkcje umieszczone najbardziej wewnątrz działają jako pierwsze, a te na zewnątrz (od lewej) jako ostatnie. Jeżeli wiesz dlaczego tak się dzieje, podpowiedz innym w komentarzu.

{=ŚREDNIA(JEŻELI(Q15:Q24=Q10;JEŻELI(R15:R24=R10;S15:S24)))}

Opisane powyżej kroki dzieją się w ułamku sekundy. Chyba, że danych jest bardzo dużo i używasz wielu formuł tablicowych. W takich przypadkach odczujesz spowolnienie, w działaniu arkusza.

Formuły tablicowe: przykłady z użyciem MEDIANY i KWARTYLI

Powyższy przykład to tylko wierzchołek góry lodowej. Formuły tablicowe to naprawdę potężne narzędzie i potrafią znacznie więcej. Pokażę Ci jeszcze dwa przykłady, które mam nadzieję będą dla Ciebie użyteczne.

Liczenie średnich z wynagrodzeń jest kiepskim pomysłem. Ja częściej używam MEDIANY, KWARTYLI, DECYLI i DOMINANTY (w Excelu odpowiednia funkcja to WYST.NAJCZĘŚCIEJ.WART)

Trudność pojawia się, gdy trzeba policzyć medianę dla kilku różnych typów stanowisk, lokalizacji, czy kategorii zaszeregowania. Na poniższym przykładzie zobaczysz jak można sobie z tym poradzić, dzięki formułom tablicowym.

---

---

Podsumowanie

Formuły tablicowe to „level master” jeżeli, chodzi o umiejętność posługiwania się funkcjami Excela. Jednak zrozumienie podstawowej koncepcji działania nie powinno sprawić dużych kłopotów. Mam nadzieję, że tym artykułem pomogłem Ci osiągnąć właśnie ten cel.

Życzę Ci owocnego wykorzystania „tablicówek” do wszelkich obliczeń, gdzie zawiedzie standardowa funkcja czy tabela przestawna.

---

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

Funkcja WYSZUKAJ PIONOWO: 5 sposobów użycia, o których pewnie nie wiesz

Funkcja WYSZUKAJ PIONOWO chyba powoli odchodzi do lamusa… W każdym razie, jeśli jesteś analitykiem, tudzież potrzebujesz bardziej elastycznych narzędzi – pewnie używasz relacji w Power Pivot. A jeżeli dysponujesz nową wersją Excela – funkcji X.WYSZUKAJ.

Dla całej reszty z nas (większości?) funkcja WYSZUKAJ PIONOWO, nadal może być i będzie użytecznym narzędziem. Jeśli używasz jej na co dzień, to ten artykuł pomoże Ci poszerzyć zakres możliwości. A jeśli masz z nią jakieś trudności – polecam najpierw lekturę innych moich artykułów.

Przeczytaj również:

• WYSZUKAJ PIONOWO – krok po kroku
• WYSZUKAJ PIONOWO nie działa?

Ściągnij plik początkowy, jeżeli chcesz przećwiczyć rozwiązania opisane w artykule.

Policz prowizję, dzięki najlepszemu dopasowaniu

Musisz policzyć odpowiednią wartość prowizji, na podstawie wartości sprzedaży. Na przykład sprzedawca, który zrealizuje sprzedaż do 10 000 zł – nie otrzyma prowizji. Za to ten, który zrealizuje sprzedaż od 10 000 zł włącznie do 20 000 zł – otrzyma prowizję w wysokości 1% sprzedaży.

Zazwyczaj takie zadanie realizujemy budując zagnieżdżoną formułę z funkcją JEŻELI. I to jest ok, dopóki masz mało warunków. Im więcej warunków, tym bardziej złożona staje się formuła. A dla higieny naszych arkuszy (a raczej ograniczaniu ryzyka popełnienia błędów) powinniśmy upraszczać formuły.

Niewiele osób wie, że w takich przypadkach można wykorzystać WYSZUKAJ PIONOWO, używając w czwartym argumencie: 1 (PRAWDA) – najlepsze dopasowanie, zamiast 0 (FAŁSZ) – dokładne dopasowanie.

W powyższym przykładzie, dla wartości >= 10 000 zł i mniejszej niż 20 000 zł, funkcja zwróci prowizję 1%. Z kolej dla sprzedaży >=20 000 zł i mniejszej niż 30 000 zł, prowizja wyniesie 2%. I tak dalej. Tak właśnie działa WYSZUKAJ PIONOWO z najlepszym dopasowaniem.

Jak pewnie zauważyłeś wartości wyszukiwane (pierwsza lewa kolumna w tabeli źródłowej) są posortowane rosnąco. To nie przypadek, tylko warunek konieczny, aby funkcja zwracała prawidłowe wartości.

Wyszukaj niekompletną wartość

Zdarza Ci się, że dane źródłowe w których wyszukujesz mają nieco inną postać niż byś sobie życzył? Mnie tak 🙂 Jest sposób i na to.

W przykładzie poniżej, chcemy znaleźć ceny dla naszych produktów: krzesła, stołu, ławki i kanapy. Ale dane źródłowe z cenami, ściągnięte z systemu, zawierają jeszcze dodatkowe oznaczenia. Mamy zatem nazwę naszego produktu – Krzesło – wpisaną jako fragment większego ciągu znaków 1_Krzesło_WXX_2020.

Mógłbyś podzielić dane źródłowe, za pomocą narzędzia TEKST JAKO KOLUMNY, ze wstążki DANE ale jak zwykle – jest lepszy sposób. W Excelu (i nie tylko) – „*” (gwiazdka) – zastępuje dowolny ciąg znaków. Okazuje się, że jeżeli użyjesz jej w WYSZUKAJ PIONOWO to spełni dokładnie taką rolę. Wpisując:

"*"&"Krzesło"&"*"

dajesz znać, że szukasz dowolnego ciągu znaków, który w środku zawiera słowo „krzesło”. Zobacz jak to zrobić na poniższym przykładzie.

Wyszukiwanie częściowe z zamianą tekstu na liczbę

W poprzednim akapicie pisałem, o problemie jaki mamy w użyciem WYSZUKAJ PIONOWO, gdy poszukiwane przez nas wartości mają zmienioną formę. Tak jest w sytuacji, gdy szukasz tylko fragmentu jakiegoś tekstu – powiedzmy identyfikatora produktu, a w danych źródłowych ten identyfikator jest „zaszyty” w identyfikatorze transakcji.

I ponownie, dzięki połączeniu WYSZUKAJ PIONOWO z inną funkcją jesteś w stanie sobie doskonale poradzić. W naszym przykładzie id produktu występuje jako pierwsze cztery znaki w id transakcji. Możemy zatem użyć funkcji LEWY, aby wyciągnąć te pierwsze 4 znaki i użyć ich jako argumentu w WYSZUKAJ PIONOWO.

Gdyby okazało się, że poszukiwany przez Ciebie fragment jest w innym miejscu (na przykład w środku) możesz analogicznie użyć funkcji FRAGMENT TEKSTU.

Oglądając przykład, zauważysz, że po fragmencie z funkcją LEWY dodaje zero. Dlaczego? Ponieważ wyciągnąłbym id produktu jako tekst, a w danych źródłowych ten sam identyfikator występuje jako liczba. Jak wiesz dla WYSZUKAJ PIONOWO to znacząca różnica. Użycie operacji matematycznej, w tym przypadku dodawania zamienia tekst na liczby, a żeby nie zmieniać wartości id – po prostu dodaję zero. Dla wnikliwych – odejmowanie zadziała podobnie, ale mnożenie już nie. Jeżeli wiesz dlaczego nie – pochwal się w komentarzu 😉

Uniknij zera w wyniku wyszukiwania

Jeżeli w danych źródłowych nie będzie poszukiwanej przez WYSZUKAJ PIONOWO wartości, to funkcja zwróci błąd: N/D! – błąd wartości niedostępnej. Jeśli natomiast poszukiwana wartość będzie, ale nie będzie do niej przypisanej „korespondującej” wartości, której poszukujemy (pusta komórka) – to w wyniku dostaniemy 0 (zero).

Aby tego uniknąć (zera), do funkcji WYSZUKAJ PIONOWO wystarczy „dokleić” tzw. pusty znak, czyli dwa cudzysłowy „”. Będzie to wyglądało następująco:

=WYSZUKAJ.PIONOWO(E11;$B$11:$C$18;2;0)&""

WYSZUKAJ PIONOWO z dwoma warunkami wyszukiwania

Standardowo, funkcja WYSZUKAJ PIONOWO działa z jednym warunkiem wyszukiwania. Co jeśli masz dwa warunki? Przykładowo, masz zamiar zrobić podwyżki pracownikom na podstawie oceny ich wynagrodzenia (A – oznacza wynagrodzenie poniżej benchmarku, B – jesteśmy w benchmarku, a C – powyżej benchmarku) Jeśli mielibyśmy na tym poprzestać, to standardowe podejście nam wystarczy.

Jednak, gdy chcielibyśmy różnicować podwyżki także w zależności od działu w jakim ktoś pracuje, to już musimy pokombinować. Jedną z metod jest wykorzystanie: nazw oraz funkcji ADR.POŚR.

la każdego działu definiujemy inną tabelę podwyżkową, nazywamy jej zakres tak jak nazwane są nasze działy: Sprzedaż, Finanse, Logistyka. Za pomocą funkcji ADR.POŚR przekształcisz tekst „Sprzedaż” na zdefiniowaną wcześniej nazwę. Żeby lepiej zrozumieć działanie całego mechanizmu, pobierz plik w którym znajdziesz końcowe rozwiązania i więcej szczegółów dotyczących funkcji ADR.POŚR.

Całość wygląda tak:

{=WYSZUKAJ.PIONOWO(D11;ADR.POŚR(C11);2;0)}

Nawiasy klamrowe oznaczają, że jest to formuła tablicowa (musisz ja zatwierdzić kombinacją klawiszy CTRL + SHIFT + ENTER) Jest to konieczne, ponieważ funkcja ADR.POŚR zwraca zakres komórek, a nie pojedynczą komórkę.

Podsumowanie

Jestem ciekawy, czy znałeś takie zastosowania funkcji WYSZUKAJ PIONOWO. Nie zawsze są one popularne i powszechne, a jak widzisz ułatwiają życie.

A może znasz jeszcze inne? Podziel się z nami w komentarzu.

---

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

Funkcja PMT, czyli jak policzyć ratę kredytu

Masz kredyt? A może zastanawiasz się nad pożyczką? Byłoby dobrze, gdybyś potrafił sam policzyć jaka będzie rata do spłaty. Podstawą takich obliczeń jest funkcja PMT.

Funkcja PMT: jak działa

Funkcja wyliczy ratę kredytu lub pożyczki, czyli kapitał plus odsetki w danym okresie. Założenie jest takie, że zarówno raty, jak i stopa procentowa jest stała w całym okresie kredytowania. Na marginesie, szkoda że w Polsce nie ma kredytów hipotecznych ze stałą stopą oprocentowania…

Funkcja PMT: składnia i argumenty

Składnia funkcji PMT wygląda tak:

PMT(Stopa;Liczba_rat;Wa;Wp;Typ)

Poniżej znajdziesz wyjaśnienie każdego argumentu:

• Stopa – oprocentowanie, czyli stopa procentowa w ujęciu rocznym. Jeśli okresy są na przykład miesięczne, to stopę procentową musisz podzielić przez liczbę okresów (jeżeli wydaje Ci się to skomplikowane – spokojnie, poniżej znajdziesz przykłady)
• Liczba_rat – liczba wszystkich okresów, przez które spłacasz kredyt
• Wa – wartość aktualna [ang. PV – Present Value] kredytu / pożyczki, czyli kwota, którą pożyczasz
• Wp – wartość przyszła [ang. FV – Future Value] kredytu / pożyczki. Jeżeli pominiesz ten argument, to funkcja przyjmie 0. W końcu każdy kredyt trzeba spłacić do zera 🙂
• Typ – argument, który mówi o tym czy raty są płacone: na końcu każdego okresu – wtedy 0; czy na początku – wtedy 1. Jeżeli pominiesz funkcja przyjmie z automatu 0.

---

---

Przykład użycia

Przechodząc do przykładu, weźmy sytuację w której zastanawiasz się nad kredytem z banku na 5 000 zł. Kredyt chcesz wziąć na 4 lata, rata będzie spłacana co miesiąc na koniec okresu, a oprocentowanie wyniesie 18%.

Formuła w komórce D9, wygląda następująco:

=PMT(D7/12;D6;-D5;0;0)

Kilka słów wyjaśnienia. Oprocentowanie z komórki D7, jest dzielone na 12, ponieważ mamy miesięczne okresy spłaty. Z kolei, kwota kredytu z komórki D5 poprzedza znak minus. To dlatego, że biorąc kredyt zaciągasz zobowiązanie, czyli jesteś coś winny.

Wynik tej formuły daje ratę na poziomie 146,87 zł. Jeżeli przemnożysz tę ratę przez ilość miesięcy, przez jaką będziesz spłacał kredyt powinieneś otrzymać kwotę 7050 zł. A zatem finalnie oddasz do banku o 2050 zł więcej niż pożyczyłeś.

W pliku znajdziesz opisany powyżej przykład oraz jeszcze jeden, z rocznymi okresami spłaty. Zachęcam Cię do samodzielnych ćwiczeń. Jeżeli nie będziesz pewny czy masz dobry wynik, możesz skorzystać z zewnętrznego kalkulatora w celu weryfikacji (tylko dla miesięcznych okresów spłaty). A w razie pytań korzystaj z możliwości komentowania lub grupy na Facebooku.

Na koniec pamiętaj, aby zadłużać się z głową i z wykorzystaniem Excela! 🙂

---

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

---

Funkcja LUB

Funkcja LUB [Ang: OR] sprawdza podane przez Ciebie warunki i w rezultacie daje wartość PRAWDA albo FAŁSZ.

Jak działa?

Każdy ze sprawdzanych warunków także daje wartość PRAWDA albo FAŁSZ. Powiedzmy, że warunek – sprawdzamy płeć osoby – wygląda tak:

=A2="K"

czyli, sprawdzamy czy w komórce A2, znajduje się litera K, która to reprezentuje kobietę. I tak:

• Jeżeli w komórce A2 będzie litera K, w wyniku otrzymasz wartość PRAWDA.
• Jeżeli będzie wartość inna niż K, w wyniku otrzymasz FAŁSZ.

Z kolei w kolejnej komórce sprawdzamy wiek:

=B2>30

co oznacza, sprawdzenie czy dana osoba ma więcej niż 30 lat.

I podobnie:

• Jeżeli w komórce B2 będzie wartość większa niż 30, w wyniku otrzymasz wartość PRAWDA.
• Jeżeli będzie wartość mniejsza lub równa, w wyniku otrzymasz FAŁSZ.

Funkcję LUB wykorzystujesz właśnie w sytuacji, w której masz do sprawdzenia wiele warunków. Powiedzmy, że chcesz sprawdzić (dość abstrakcyjny) zestaw warunków:

Szukasz przypadków, kiedy dana osoba jest kobietą lub ma więcej niż 30 lat. Zapiszesz to tak:

=LUB(A2="K";B2>30)

Funkcja LUB działa w ten sposób, że jeżeli przynajmniej jeden z warunków jest prawdziwy, to całe wyrażenie jest prawdziwe. A zatem, aby funkcja LUB dała w wyniku FAŁSZ – wszystkie warunki muszą być fałszywe.

Składnia i argumenty

Składnia jest bardzo prosta. To nazwa funkcji i kolejne warunki. Warunków może być maksymalnie…255.

=LUB(logiczna1;logiczna2;...;logicznaN)

=LUB(pierwsza wartość logiczna, którą 
sprawdzasz; druga wartość logiczna;...;
i kolejne)

Przykład użycia z użyciem funkcji JEŻELI

Funkcje logiczne, takie jak funkcja LUB bardzo często występują w połączeniu z funkcją JEŻELI. Dlatego, że po sprawdzeniu określonych warunków najczęściej chciałbyś, aby w wyniku tej kontroli coś się wydarzyło – czytaj pojawiło się jakieś inne działanie albo wynik.

Powiedzmy, że liczysz premie dla handlowców. Warunki premiowe są następujące: każdy dostaje 500 zł premii, jeżeli: sprzedaż (wartość sprzedaży jest w komórce A1) przekroczy 10 000 zł, lub handlowiec pozyska 10 nowych klientów (ilość nowych klientów w komórce A2).

Wiesz już, jak sprawdzić warunki dzięki funkcji LUB:

=LUB(A1>10000;A2>10)

Jednak, aby „wypłacić premię” po spełnieniu minimum jednego z warunków, musisz dodać funkcję JEŻELI i całość będzie wyglądała tak:

=JEŻELI(LUB(A1>10000;A2>10);500;0)

Powyższa formuła sprawdza warunek (sprzedaż lub liczba nowych klientów) i jeżeli jest spełniony – daje w wyniku 500, a jeżeli nie to oczywiście 0.

Czy chcesz bezpłatny test z Excela?

---

Podsumowanie

Funkcje logiczne, takie jak LUB czy ORAZ to „must have” w arsenale każdego adepta Excela. W połączeniu z funkcją JEŻELI pozwalają na „sterowanie” działaniami w zależności od spełnienia określonych warunków.

---

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

Działania matematyczne w Excelu, czyli 8 podstawowych funkcji, które musisz znać.

Podstawowe funkcje matematyczne….w zasadzie nie ma innej możliwości – korzystając z Excela musisz ich używać. Skoro bowiem, matematyka jest królową nauk – to musisz wiedzieć jak obliczyć iloczyn, procent, średnią czy pierwiastek w Excelu. O tym jak to robić, dowiesz się z tego artykułu.

Do dziś z uśmiechem wspominam pewną panią, która na moim szkoleniu z podstaw Excela przyznała:

„Czasami sprawdzam na kalkulatorze wynik, jaki wychodzi mi w Excelu.”

Gwarantuję… nie musisz tego robić 😉 Excel to taki większy i lepszy kalkulator. Musisz tylko wiedzieć co robisz.

Podstawowe funkcje matematyczne mają swoje wymagania.

Podstawowe funkcje matematyczne w Excelu są w większości bardzo proste. Na pewno jednak musisz znać pewne podstawy. Do nich zaliczam umiejętność adresowania komórek czy zakresów. Musisz także wiedzieć, że w Excelu do obliczeń służą funkcje. Większość funkcji ma argumenty. A każda funkcja zaczyna się od znaku równości =.

Pewnym wyjątkiem są zupełnie podstawowe działania matematyczne, takie jak:

• dodawanie +
• odejmowanie –
• mnożenie *
• dzielenie /
• potęgowanie ^

W tych przypadkach, nie musisz używać nazwy funkcji (np. SUMA) Możesz po prostu użyć znaków: dodawania (+), odejmowania (-), mnożenia (*), dzielenia (/) i potęgowania (^).

Zacznijmy właśnie od takich podstaw.

Dodawanie w Excelu – suma

Aby dodać do siebie dwie lub więcej wartości (składników), w dowolną komórkę wpisz:

=10+10

Zatwierdź wciskając ENTER na klawiaturze.

Excel tym się różni od kalkulatora (nie sądziłem, że kiedyś napiszę takie zdanie ;-)), że nie musisz używać „sztywnych” wartości. Możesz odwołać się do komórek arkusza. Dzięki temu zmieniając liczby w tych komórkach, będziesz na bieżąco otrzymywał wyniki sumowania (czy innych działań lub funkcji)

Na przykład możesz chcieć zsumować takie 4 komórki:

=A1+A2+B1+B2

Przy większym zakresie danych, z pewnością będziesz chciał użyć funkcji SUMA. Dzięki temu, nie musisz wpisywać adresu każdej pojedynczej komórki.

=SUMA(A1:E10)

Odejmowanie w Excelu – różnica

Odejmowanie działa bardzo podobnie do sumowania. Oczywiście użyjesz innego znaku. Na przykład:

=A1-A2

A co jeśli będziesz chciał odjąć od siebie liczby z większego zakresu? Możesz użyć funkcji SUMA. Tak, nie pomyliłem się. Powiedzmy, że chcesz odjąć wartości z zakresu B1:B10 od tych z A1:A10. Zrobisz tak:

=SUMA(A1:A10)-SUMA(B1:B10)

Mnożenie w Excelu – iloczyn

Analogicznie do poprzednich działań, w przypadku mnożenia możesz użyć znaku „*” (gwiazdka):

=A1*A2*A3*A4

W powyższym przykładzie przemnożysz liczby, które znajdują się w komórkach A1, A2, A3, A4. Zamiast za każdym razem używać „*”, możesz przy większej ilości liczb / komórek do przemnożenia użyć funkcji ILOCZYN. W argumentach tej funkcji podajesz zakres liczb jakie mają ulec przemnożeniu:

=ILOCZYN(A1:A4)

Dzielenie w Excelu – iloraz

„Pamiętaj cholero, nie dziel przez zero” – mawiała moja nauczycielka od matematyki w podstawówce. W odróżnieniu do mnożenia, dzielenie w Excelu nie ma swojego odpowiednika w funkcji. Aby podzielić dwie liczby, użyjesz znaku „/”, np. tak:

=100/50

Tak jak poprzednio, możesz używać odwołań do komórek zamiast konkretnych liczb.

=A1/A2

Potęgowanie w Excelu

Podnieść do potęgi, możesz korzystając z funkcji POTĘGA. Funkcja ma dwa argumenty: liczba, która ma być potęgowana oraz wartość potęgi.

=POTĘGA(10;2)

Taki sam efekt osiągniesz, korzystając ze znaku „^”, który reprezentuje to działanie:

=10^2

Oczywiście i tutaj możesz użyć odwołania do komórek.

Pierwiastek w Excelu

Właściwie to – pierwiastek kwadratowy. Aby wyciągnąć pierwiastek użyj funkcji…o zaskoczenie…PIERWIASTEK 🙂

=PIERWIASTEK(100)

Jeżeli argumentem będzie liczba ujemna, w wyniku otrzymasz błąd #LICZBA!.

Reszta z dzielenia

Możesz się zastanawiać kiedy będziesz miał potrzebę, aby w Excelu liczyć resztę z dzielenia. Spieszę odpowiedzieć, że nie tak często jak liczyć sumę, nie mniej jednak i ta funkcja ma swoje zastosowanie.

Powiedzmy, że chcesz sprawdzić na dużym zakresie danych, które liczby są parzyste. Jak to zrobić? No właśnie sprawdzając resztę z dzielenia, np. przez dwa 😉

Funkcją, która liczy resztę z dzielenia jest MOD. Ma dwa argumenty. Pierwszy z nich to liczba, którą dzielisz (w matematyce – to dzielna). Drugi to liczba, przez którą dzielisz (dzielnik)

=MOD(100;3)

Z przykładu powyżej otrzymamy wynik równy 1. Dlaczego? Ponieważ 3 razy 33 daje 99… czyli brakuje 1 do 100. Albo inaczej 100 / 3 = 33 ale zostaje reszta…1 😉

Procenty w Excelu

Ach…obliczanie procentów. Zaskakująco wiele razy przekonałem się, że wielu z Was ma problemy z procentami. Ups. To źle zabrzmiało 🙂 Ale zupełnie serio, napiszę kiedyś osobny artykuł na ten temat, bo jest tam kilka kwestii, które warto wyjaśnić. Teraz tylko podstawy oraz zagadka. Daj znać w komentarzu jaka jest Twoja odpowiedź.

Jeżeli cena mieszkania najpierw wzrosła o 10%, a następnie spadła o 10% to aktualna cena mieszkania jest: taka sama, większa czy mniejsza niż przed zmianami?

Wracając do procentowania, to w Excelu po prostu mnożysz jakąś wartość przez procent, np:

=A1*10%

Czyli „wyciągam” 10% z liczby w komórce A1.

Alternatywnie, jeżeli chcesz sprawdzić jaki procent jednej liczby stanowi inna liczba – wykonujesz dzielenie:

=10/100

a następnie możesz zmienić formatowanie komórki z wynikiem na procentowe. „Fabrycznie” bowiem, wynik dostaniesz w liczbach dziesiętnych.

Czy chcesz bezpłatny test z Excela?

---

Podsumowanie

Podstawowe funkcje, czy też działania matematyczne mają swoje odpowiedniki w Excelu. Jak widzisz nie ma w ich przypadku, niczego bardziej skomplikowanego, co nie wynikałoby z natury samej…matematyki 🙂

Na pewno musisz pamiętać o kilku podstawach, takich jak: używanie znaku równości przed nazwą funkcji, czy działaniami; rozdzielaniu argumentów funkcji średnikami (w polskiej wersji); umiejętność poprawnego adresowania.

Jeżeli masz problem z inną funkcją – daj mi znać w komentarzu. Spróbuję pomóc.

---

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