28 Funkcji EXCELA.docx

28 funkcji Excela które odmienią Twoje życie

Kilka dni temu Microsoft w dość efektowny sposób zaprezentował możliwości nowej wersji Microsoft Office, która dotychczas roboczo zwana była Office 15. Nowa wersja pakietu to przede wszystkim zwrot w kierunku Metro, dostosowanie interfejsu do nowych grup urządzeń, a także nacisk na społecznościowe interakcje.

Niektórzy zarzucają gigantowi z Redmond, że pójście w stronę interfejsu Metro to zły kierunek, inni z kolei mówią, że nowa wersja przynosi zbyt mało nowości funkcjonalnych. Pojawiają się również oczywiście głosy pochwalne, które jednak łatwo giną w tej fali ogólnej krytyki. Nie zważając na obecne głosy negatywne, można być raczej spokojnym o sprzedaż tego oprogramowania. Microsoft jest na tym poletku właściwie liderem od zawsze i to on wyznacza standardy, a pakiet biurowy Office, to oprócz Windowsa i Xboxa wciąż najmocniejszy punkt oferty. Nic nie zapowiada się, by ten stan rzeczy miał ulec zmianie w najbliższych latach, a już za kilka miesięcy Word, Power Point oraz Excel powrócą wraz z towarzyszami w kolejnej odświeżonej odsłonie.

W tym miejscu warto również zwrócić uwagę na pewne zjawisko. Większość użytkowników, wykorzystuje tak naprawdę kilka % możliwości całego pakietu. Pisząc dokumenty w Wordzie, łatwo zapominamy choćby o możliwości zastosowania stylów do tworzonych prac, trochę lepiej radzimy sobie z PowerPointem (choć z własnych obserwacji wynika mi, że dla wielu obecnych uczniów oraz studentów stworzenie prezentacji jest wciąż czymś abstrakcyjnym), by kompletnie polec na froncie Excela, który jak dla mnie ma chyba największe możliwości z tych trzech wymienionych aplikacji. Dlatego też dziś chciałbym Wam przedstawić zestaw użytecznych funkcji z Excela, która można zastosować nie tylko dla operacji na liczbach i które w pewnym sensie mogą odmienić Wasze życie... z Excelem:)

Stosowanie funkcji

Mam nadzieję, że podstawy znacie, ale gdyby nie to szybkie wprowadzenie. Excel to arkusz kalkulacyjny, w którym operacje wykonujemy na konkretnych komórkach. Każda komórka ma swój adres, który wyrażony jest literami oznaczającymi kolumny (A,B,C itd.) oraz liczbami wyrażającymi wiersze (1,2,3), czyli sumarycznie uzyskujemy kombinacje A1, A2,..., B1, B2,..., C1, C2... itd.

Do komórek można wpisywać liczby, tekst oraz wprowadzać funkcje wraz z parametrami. Funkcje z reguły operują na wartościach określonych komórek, bądź też na danych podanych bezpośrednio przez użytkownika, a spektrum ich działania zaczyna się od prostej sumy, poprzez skomplikowane wyrażenia warunkowe, aż do wywołań funkcji stworzonych przez użytkownika (to ostatnie zagadnienie to jednak temat na osobny post).

Funkcje możemy zasadniczo wywołać na kilka sposobów. Dla początkujących, najłatwiej udać się do wstążki Formuły i tam skorzystać z przycisku Wstaw funkcję, by następnie wybrać interesującą pozycję z listy.

Możemy też wywołać funkcję wpisując ją ręcznie bezpośrednio w komórce ( bądź też w pasku adresu widocznym nad obszarem roboczym arkusza). Gdy tylko rozpoczniemy pisanie nazwy funkcji, Excel rozpocznie wyświetlanie dopasowanych podpowiedzi. Podobnie zachowa się w sytuacji kiedy zaczniemy wprowadzać argumenty funkcji. Kolejne z nich rozdzielamy średnikiem.

Jako argumenty funkcji możemy podawać zarówno konkretne komórki (A1, A2, A3 itd.), jak również zakresy komórek (A1:A6 - zakres ten obejmie wszystkie komórki od A1 do A6, bądź też A1:B6 co obejmie wszystkie komórki od A1 do B6 - łącznie 12 komórek itd.). Pamiętajmy również o możliwości przeciągania formuł w Excelu, w tym przypadku automatycznie aktualizowane są adresy komórek.

Jeśli w danej operacji chcemy zablokować wybrany adres, to wykorzystujemy znak dolara:

·         $A$1 - formuła zawsze odnosić będzie się do komórki A1

·         $A1 - formuła zawsze odnosić się będzie do kolumny A, może zmienić się numer wiersza

·         A$1 - formuła zawsze odnosić się będzie do wiersza 1, może zmienić się kolumna

To na tyle z wprowadzenia do pracy z funkcjami, czas przejść do konkretów.

Proste funkcje liczbowe

1. Suma

Suma to jedna z prostszych funkcji Excela, a jej istotę myślę, że nie trzeba nikomu wyjaśniać. Jeśli chcemy obliczyć sumę wybranych wartości, powiedzmy komórek od A1 do A3, to możemy wykonać następujące działanie:

?

W ten sposób podaliśmy konkretny zakres komórek do funkcji i to jest najprostsze możliwe jej wykorzystanie. Możemy jednak podawać również konkretne komórki - w takim przypadku należy rozdzielać je średnikiem:

?

Excel cały czas nam podpowiada co mamy wpisać. Warto również zwrócić uwagę na kolorowanie kolejnych elementów formuły oraz odpowiadających im komórek. Naprawdę trudno w tym przypadku o pomyłkę!

2. Iloczyn

Na tych samych komórkach, możemy wykonać dowolne inne działania - choćby mnożenie. Również i w tym przypadku działa wywołanie z zakresem, bądź też opcja podania ręcznej tablicy konkretnych komórek wyrażonych jako kolejne argumenty funkcji:

?

3. Pierwiastek

Liczenie pierwiastków już nigdy nie będzie trudne po zastosowaniu kolejnej sprytnej funkcji z Excela:) Aby uzyskać pierwiastek kwadratowy z danej liczby (komórki) wystarczy wywołać funkcję PIERWIASTEK:

?

4. Potęga

Potęgowanie w Excelu to również prościzna, nawet jeśli potęga nie jest standardowa. Kluczem do sukcesu będzie zastosowanie funkcji POTĘGA w której najpierw przekazujemy potęgowaną liczbę, a następnie wartość potęgi.

?

Jeśli w przypadku jakiejkolwiek funkcji nie jesteśmy pewni podawanych argumentów, zawsze, w dowolnym momencie możemy wywołać okienko wprowadzania funkcji (patrz screen 4).

5. Zaokrąglanie

Excel oferuje całą rzeszę funkcji do zaokrąglania, wystarczy zacząć pisać =ZAOKR... by się o tym przekonać. Główna i standardowa funkcja przeznaczona do tego celu, przyjmuje dwa argumenty. Pierwszy z nich to zaokrąglana liczba, druga to liczba miejsc po przecinku (możliwe jest 0, które sprowadzi liczbę do postaci całkowitej).

?

Powyższe wywołanie zwróci 2,57.

6. Liczba PI

Funkcje Excela oprócz operacji na wybranych elementach, mogą również zwracać stałe wartości. Aby poznać liczbę PI zaokrągloną do 15 miejsc po przecinku wystarczy wywołać funkcję PI:

?

7. Silnia

Silna to jedno z tych działań, którego obliczanie na piechotę może być niezwykle mozolne. Tak też pomyśleli również programiści Excela, którzy uraczyli nas funkcją o niezwykle niespodziewanej nazwie... SILNIA:

?

Niestety, ale przesadnie wielka wartość argumentu tej funkcji, może spowodować błąd.

Operacje na szeregu liczb

Mając określony szereg liczb, możemy wyciągnąć z niego wiele użytecznych informacji np. wartość minimalną, maksymalną, czy środkową. Na nasze potrzeby zakładam że w komórkach A1:A10 znajduje się ciąg losowych liczb.

8. Wartość minimalna

Dla danego szeregu liczb, czasem chcemy poznać jego wartości brzegowe. Excel nam oczywiście na to pozwala. Aby poznać wartość minimalną, korzystamy z funkcji MIN.

?

9. Wartość maksymalna

Analogicznie odczytamy wartość maksymalną, tylko że tym razem zmienimy nazwę funkcji na MAX.

?

10. Sprawdzanie liczebności szeregu

Jeśli szereg jest duży, możemy skorzystać z funkcji, która powie nam ile ma elementów. Taką informację możemy wykorzystać do wielu różnych innych operacji, bądź też w celach statystycznych. Informacje uzyskamy za pomocą funkcji ILE.LICZB, której jako argument podajemy określone liczby, bądź też zakres komórek:

?

11. Średnia

Dla danego szeregu możemy również obliczyć jego średnią. Jeśli przewidywaliście, że funkcja będzie nazywać się ŚREDNIA, a jako argumenty podawać będziemy kolejne wartości (bądź też ich zakres) to się nie pomyliliście. Jak widać funkcje w Excelu mogą być naprawdę intuicyjne (są też niestety niechlubne wyjątki).

?

12. Mediana

Za pomocą Excela odczytamy także medianę zwaną również drugim kwartylem. Jest to nic innego jak wartość przeciętna - środkowa danego szeregu liczb:

?

13. Dominanta

Kilka akapitów wcześniej pisałem o logice bijącej z Excela w stosowaniu i nazewnictwie funkcji. Niestety od każdej reguły są wyjątki i tak jest w przypadku dominanty, która w tym programie funkcjonuje jako funkcja WYST.NAJCZĘŚCIEJ. Niby jest w tym logika, ale jednak jakoś dziwnie...

?

14. Odchylenie standardowe

Badając szeregi liczbowe, nie sposób nie obliczyć odchylenia standardowego. Ten jeden z podstawowych mierników statystycznych również doczekał się swojej funkcji w Excelu:

?

Czasem, tak jak w tym przypadku, nie rozumiem skrótów w nazewnictwie funkcji tworzonych przez Microsoft.

15. Wariancja

Jest odchylenie standardowe więc nie mogło również zabraknąć wariancji, którą przy pomocy funkcji POTĘGA (4) moglibyśmy policzyć sobie sami na podstawie obliczeń uzyskanych z funkcji ODCH.STANDARDOWE (14):

?

W tym przypadku założyliśmy, że w komórce A11 znajduje się obliczone odchylenie standardowe. Jeśli potrzebujemy samej wariancji, obliczymy ją po prostu za pomocą funkcji WARIANCJA:

?

Użytkowe

Excel posiada również ogromny zestaw funkcji, które pozwolą nam np. przekonwertować określone wartości, czy też odczytać pewne stałe. Przyjrzyjmy się bliżej kilku z nich.

16. Konwersja jednostek

Jedną z ciekawszych funkcji w dzisiejszym zestawieniu jest funkcja CONVERT. Umożliwia ona konwersję sporej grupy jednostek na inne. W tym przypadku nieodzowne może stać się skorzystanie z pomocy (przynajmniej przy pierwszych użyciach), którą można uzyskać na ekranie na którym wprowadzamy argumenty funkcji.

CONVERT przyjmuje trzy argumenty. Pierwszy z nich to oczywiście liczba (w tym przypadku konkretna liczba, bądź komórka - nie jest możliwe podawanie zakresu). Drugi definiuje jednostkę wejściową (odpowiednie oznaczenie polecam zaczerpnąć ze wspomnianej wyżej pomocy), a trzeci ostatni pozwala na definicję jednostki wyjściowej - oczekiwanej. W przykładzie konwersja 5000 metrów na mile:

?

17. Konwersja liczb dziesiętnych na binarne

Poniższe działanie będzie niewątpliwie szczególnie przydatne dla informatyków, a pozwala na konwersję liczb dziesiętnych na binarne. Pożądany efekt uzyskamy przy pomocy funkcji DEC2BIN, która przyjmuje argument będący liczbą dziesiętną. Opcjonalnie możemy przekazać również ilość wyświetlanych liczb. Nie powinna ona być jednak mniejsza niż spodziewana długość oczekiwanego rezultatu. W sytuacji gdy podamy wielkość większą niż spodziewany rezultat, to brakujące miejsca zostaną uzupełnione zerami wiodącymi:

?

Spowoduje zwrócenie wartości: 001010.

18. Konwersja liczb dziesiętnych na szesnastkowe

W podobny sposób jak w funkcji wyżej, możemy uzyskać wartości szesnastkowe (hexadecymalne). Zmienia się tylko funkcja, która tym razem nazywa się DEC2HEX. Kompletnie nie zmienia się jej działanie.

?

Powyższe wywołanie zwróci rezultat 00FE.

W Excelu znajduje się znacznie więcej tego typu funkcji i pozwalają na konwersje pomiędzy systemem binarnym (BIN), ósemkowym (OCT), dziesiętnym (DEC) oraz szesnastkowym (HEX). Kluczem w tym przypadku są trójznakowe określenia używane przy każdym z systemów. Wystarczy je złożyć w postaci ŹRÓDŁO2REZULTAT by odnaleźć pożądaną funkcję.

19. Dzisiejsza data

Częstym elementem z którego korzystamy w Excelu oprócz liczb są również daty. W programie możemy łatwo sprawdzić dowolną datę i przede wszystkim uzyskać tą dzisiejszą. Wystarczy tylko zastosować funkcję DZIŚ.

?

20. Dzień miesiąca

DZIEŃ to odrobinę dziwna funkcja, która po wprowadzeniu dowolnej wartości liczbowej wskazuje numer dnia konkretnego miesiąca. Co ciekawe funkcja działa nawet jeśli przekroczymy wartość 366 jako argument.

?

21. Dzień tygodnia

Jeśli chcemy uzyskać dzień tygodnia dla dowolnej daty, stosujemy funkcję =DZIEŃ.TYG. Funkcja przyjmuje jeden argument będący datą, a zwraca dzień w postaci liczbowej, licząc niedzielę jako pierwszy dzień tygodnia. Funkcję możemy w łatwy sposób połączyć z funkcją DZIŚ:

?

22. Kwota

Jeśli chcemy zapisać określoną liczbę w postaci kwoty pieniężnej, wystarczy że skorzystamy z funkcji KWOTA. Jako argumenty przekazujemy konkretną liczbę oraz opcjonalnie liczbę miejsc wyświetlanych po przecinku. Funkcja automatycznie zaokrągli daną liczbę (jeśli będzie taka potrzeba), bądź też doda zera w przypadku, gdy liczba będzie całkowita, a my zdefiniowaliśmy ilość miejsc po przecinku. Wyświetli również walutę zgodną z ustawieniami regionalnymi.

?

Powyższe wywołanie wyświetli 25,6 zł (wynik uzależniony od ustawień regionalnych).

Logiczne i tekstowe

Przechodzimy do ostatniej sekcji, ale zarazem najbardziej ciekawej, ponieważ opierającej się na działaniach logicznych oraz operacjach na tekście.

23. Wycinanie tekstu od lewej

Jeśli jesteś programistą, to funkcja LEWY z pewnością szybko skojarzy Ci się z metodą substring (polecam wpis o operacjach na tekście w JAVIE) obecną w wielu językach programowania. Funkcja Excela to faktyczny odpowiednik tej dobrze znanej wśród developerów metody i pozwala na wycinanie tekstu z zadanego elementu o zadanej (opcjonalnie) długości. Tekst może być podany zarówno bezpośrednio w wywołaniu funkcji jak i też jako adres zmiennej (niewątpliwie bardziej praktyczne rozwiązanie).

Jeśli założymy, że w komórce A1 figuruje tekst Alt Control Delete, to poniższe wywołanie funkcji zwróci trzy pierwsze znaki z tego łańcucha czyli Alt:

?

24. Wycinanie tekstu od prawej

Jeśli mamy LEWY, przydałoby by się mieć i PRAWY i tym tokiem rozumowania poszli niewątpliwie programiści Excela. Funkcja działa w podobny sposób jak jej przeciwległy bliźniak, z tym że pobieramy tekst od końca komórki. Zatem jeśli skorzystamy z tej samej komórki źródłowej, to po pobraniu trzech ostatnich liter uzyskamy frazę ete:

?

25. Wyszukiwanie pozycji tekstu

Kolejna funkcja - SZUKAJ.TEKST powinna się skojarzyć również programistom, tym razem z metodą indexof. SZUKAJ.TEKST umożliwia odnalezienie pozycji wybranego tekstu wewnątrz komórki, bądź też łańcucha przekazanego jako argument funkcji. Jeśli zatem w komórce A1 ponownie umieścimy frazę Alt ...