rozwiń filtry »

Wyszukiwanie publikacji

Porady

maj
28

Praca z Excelem - 100 porad i tricków (4/5)

28.05.2015 16:05, dodał: Krzysztof Cyło
Ocena:

Zachęcamy do przeczytania naszego czwartego artykułu z cyklu "Praca z Excelem - 100 tricków i porad". W czwartej odsłonie kontynuować będziemy temat formuł.

W czwartej odsłonie będziemy kontynuować rozpoczęty w poprzedniej odsłonie wątek. Będzie wiec dalej o formułach, ale tym razem z innych grup - przejdziemy do formuł statystycznych i finansowych. Nie ukrywam, że najbliższe moim zainteresowaniem i doświadczeniu zawodowemu, są formuły z tej ostatniej grupy - dlatego też to właśnie o nich będzie najwięcej. Mam nadzieję, że okażą się one dla Was przydatne, np. do uzupełniania o dodatkowe informacje budżetu domowego. Formuły statystyczne i matematyczne omówię w zakresie, który wykorzystuję do przetwarzania danych na potrzeby innych zastosowań. Jeżeli jednak liczycie na zaawansowany poradnik analizy statystycznej, to być może będziecie nieco zawiedzeni. Z przydatniejszych rzeczy pokażemy sobie też działanie jednego z częściej wykorzystywanych dodatków do Excela - Analysis Toolpak.

Zachęcam do zapoznania się z poprzednimi artykułami z cyklu:

Praca z Excelem - 100 porad i tricków (1/5)

Praca z Excelem - 100 porad i tricków (2/5)

Praca z Excelem - 100 porad i tricków (3/5)

#61 Pracujemy na szeregach liczb - wartości minimalne, maksymalne, średnie i inne podstawowe formuły

Zacznijmy od czegoś na rozgrzewkę. Excel pozwala na przetwarzanie pokaźnych ilości danych. Czasem przydatne może być jednak wyłuskanie jedynie pojedynczych kwot granicznych - maksymalnych lub minimalnych. Jeżeli nie chcemy w tym celu korzystać z szeregowania wartości, możemy posłużyć się prostymi formułami:

=MIN(zakres)

lub

=MAX(zakres)

Korzystanie z nich nie powinno przysporzyć żadnego problemu - nawet jeżeli nie mieliście zbyt wiele styczności z Excelem, to z pewnością po przeczytaniu ostatnich artykułów z naszego cyklu, potraficie się już poruszać w tym programie ;-)

Excel umożliwia też wyliczenia różnego rodzaju średnich - warunkowych (średnia z wartości spełniających dane kryteria), arytmetycznych, geometrycznych, harmonicznych.

Najbardziej typową średnią będzie arytmetyczna, dana formułą:

=ŚREDNIA(zakres)

Jest jednak pewna odmiana tej funkcji, określana jako:

=ŚREDNIA.A

Dopisek ".A" pozwala na wyciąganie wartości z danych tekstowych, dla których wartościom PRAWDA przypisywana jest jedynka, a FAŁSZ (i innym dopiskom tekstowym) - zero. Jest to przydatne, gdy, korzystając np. z funkcji JEŻELI, chcemy szybko zweryfikować, jaki procent pozycji spełnia danych warunek (posiada dopisek PRAWDA).

O średniej ŚREDNIA.JEŻELI pisaliśmy w poprzednim artykule - liczy ona średnią arytmetyczną, w oparciu o założony warunek. ŚREDNIA.WARUNKÓW może spełniać więcej niż jeden warunek.

Przejdźmy jeszcze pokrótce przez podstawowe formuły statystyczne Excela.

=MEDIANA - inaczej drugi kwartyl, inaczej - wartość środkowa, poniżej i powyżej której znajduje się tyle samo obserwacji (lub jest to średnia z dwóch środkowych obserwacji). Różnicę między medianą a średnia najlepiej można pokazać na przykładzie statystyki wynagrodzeń. Nasz rzetelny GUS publikuje jednak dane odnośnie mediany raz na dwa lata, ale z danych z roku 2012 wynika, że średnia płaca wynosiła wówczas 3895,72 zł, podczas gdy mediana 3115,11 zł.

=WYST.NAJCZĘŚCIEJ - jest to po prostu dominanta, czyli wartość najczęściej występująca w zbiorze. (W 2012 r. takie wynagrodzenie wynosiło 2189,11 zł.)

=ODCH.STANDARDOWE; =WARIANCJA - Wikipedia podpowiada, że przytoczone dwie formuły, należą w statystyce, po średniej arytmetycznej, do najbardziej podstawowych miar statystycznych. I rzeczywiście. Są to miary zmienności, które określają rozproszenie danych wokół średniej arytmetycznej. Im wartość jest większa, tym dane bardziej się różnią. Odchylenie standardowe uzyskuje się jako pierwiastek kwadratowy z wariancji, która z kolei jako miara statystyczna nie ma sensownej interpretacji, gdyż wynik stanowi wartość podniesioną do kwadratu (np. wariancji wynosi 120 zł^2).

#62 Jak pokazać wartości minimalne i maksymalne na wykresie?

Przy okazji tego krótkiego wstępu, przypomniało mi się jeszcze jedno zastosowanie funkcji MAX i MIN. Są one nad wyraz przydatne, gdy wartości minimalne i maksymalne chcielibyśmy pokazać na wykresie, tzn. wyróżnić je spośród pozostałych danych. Do efektu jak poniżej można dojść w kilku prostych krokach.

Przede wszystkim, żeby dojść do takiej postaci, musimy za pomocą funkcji MAX i MIN określić, które wartości z naszej tabeli są właśnie maksymami, a które minimami. Zrobić to za pomocą formuł z dwóch przyczyn - pierwsza to taka, że będzie zwyczajnie szybciej (w przykładzie mamy tylko kilka wierszy danych, w praktyce może być dużo więcej), a drugi, że w razie zaktualizowania danych, ulec zmianie może również wartość maksymalna lub minimalna.

Chcąc użyć tych informacji celem pokazania ich na wykresie, musimy sprawdzić każdą wartość, określając czy jest maksymalną lub minimalną z zakresu. W tym celu użyjemy funkcji JEŻELI. Przypomnę, że funkcja JEŻELI sprawdza test logiczny, u nas będzie nim:

Wartość województwa = wartość maksymalna z całego zakresu

co w przełożeniu na zapis formuły widnieje u nas dla pierwszej komórki jako:

B3=MAX($B$3:$B$8)

Odwołania bezwzględnego (symbol "$") używamy celem usztywnienia zakresu dla formuły MAX, jest on w końcu ten sam dla wszystkich wartości.

JEŻELI PRAWDA - wówczas chcemy, by w wynikiem była wartość przypisana danemu województwu;

JEŻELI FAŁSZ - u nas będzie to zero; nie chcemy bowiem wyróżniać tego województwa.

Przeciągamy formułę do dołu, to samo robimy dla kolumny MIN - tyle, że zamiast formuły MAX damy MIN. Ok, jestem kapitanem oczywistym.

Mając tak sporządzane dane, tworzymy wykres kolumnowy, wybierając jako zakres całą tabelę.

Na razie wykres nie wygląda zbyt sensownie - kolumny z wartościami przypisanymi województwom i z formułami wartości maksymalnych i minimalnych tworzą trzy serie danych, tj. teoretycznie występują one na wykresie tuż obok siebie. W praktyce tego nie widać, gdyż jednemu województwu przypisane są co najwyżej dwie wartości. Teraz wystarczy jednak tylko "nałożyć" na siebie wszystkie serie przypisane poszczególnym wykresom.

Prawym klawiszem myszki klikamy na dowolnej kolumnie, a następnie:

Formatuj serię danych -> Opcje serii -> Nakładanie serii

Wartość z 0% zmieniamy na 100%. Odtąd kolumny będą się nakładać, a widoczna będzie ta wysunięta najbardziej na prawo w tabeli.

#63 Najczęściej występująca wartość tekstowa

Porada 61 zawierała szereg stosunkowo podstawowych formuł. Za ich pomocą nie da się jednak wprost uzyskać wyniku na następujące pytanie - jaka jest najczęściej występująca wartość tekstowa? Dla nas przykładem będzie poniższy szereg danych z imionami studentów, jak i uzyskanymi przez nich ocenami. Chcemy sprawdzić, jakie jest najczęściej występujące imię. W tym celu użyjemy kombinacji kilku funkcji: PODAJ.POZYCJĘ, INDEKS, MAX i LICZ.JEŻELI. Będzie to porada nieco bardziej zaawansowana.

Stosunkowo łatwo i szybko można odpowiedzieć sobie na pytania: jaka ocena występuje najczęściej? (formuła WYST.NAJCZĘŚCIEJ) i ile razy ona wystąpiła? (formuła LICZ.JEŻELI). Nasze pytanie jest jednak nieco bardziej skomplikowane dla Excela. Standardowe formuły poskutkują błędem pracując na formacie tekstowym.

Zacznijmy od funkcji MAX, dla której zakresem danych będzie funkcja LICZ.JEŻELI. W rezultacie otrzymamy ilość powtarzającej się wartości maksymalnej. U nas będzie to liczba 2 - bo i w zestawieniu  imię "ZOSIA" występuje dwa razy.

Mając tę informację, chcemy przypisać najczęściej występującej pozycji jej tekstowy odpowiednik. Najpierw musimy jednak wskazać Excelowi, na której pozycji w tabeli znajduje się najczęściej występująca wartość. W tym celu posłużymy się funkcją PODAJ.POZYCJĘ.

Szukaną wartością będzie nasza dotychczasowa funkcja (bo jej wynik jest 2, a zatem wyszukuje odpowiednią ilość wartości maksymalnej). Przeszukiwaną pozycją będzie kolejna funkcja LICZ.JEŻELI z jej niezmienionym zakresem. W rezultacie otrzymamy numer pozycji w naszym zakresie, pod którym występuje imię ZOSIA. Funkcję zatwierdzamy korzystając ze skrótu CTRL + SHIFT + ENTER (gdyż jest to formuła tablicowa).

Widzimy już, że ZOSIA występuje na pozycji 1. Teraz wystarczy już tylko całość poprzedzić formułą INDEKS, która określi jaka wartość (tekstowa) występuje pod podaną pozycją. Jako tabelę zaznaczymy przeszukiwany obszar, numerem wiersza będzie dotychczasowa formuła, a numerem kolumny - 0. Pamiętajmy o skrócie CTRL + SHIFT + ENTER, którym zatwierdzamy formułę.

#64 Robimy histogram

Mamy wyniki egzaminu studentów - chcemy sprawdzić jak wygląda nie tylko zdawalność (mak. liczba punktów to 50, zdawalność od 30), ale i generalnie jak wygląda rozkład punktów w poszczególnych przedziałach. Patrząc na dane, ciężko jest cokolwiek wywnioskować. Bardzo dobrą formą wizualizacji tego typu zbiorów jest histogram.

Na zrzucie wdarł się mały błąd - założyliśmy, że egzamin traktujemy jako zdany od 30 pkt, do czego dostosowane zostały również przedziały punktowe w dalszej części

Histogram to swego rodzaju wykres, który pokazuje dane agregowanego w zbiorach. Dla nas takimi zbiorami będą poszczególne przedziały punktowe - np. do 10, do 20, do 30, do 40 i do 50.

Standardowo Excel nie posiada możliwości rysowania histogramów. Należy zaopatrzyć program w dodatek Analiza danych. Jeżeli do tej pory tego nie zrobiliście, to można to szybko zmienić. Wystarczy wejść w ustawienia Excela.

Plik -> Opcje -> Dodatki


Z listy należy wybrać Analysis Toolpak. Do możliwości tego dodatku dostaniemy się z formatki Dane.


W opcjach histogramu wybieramy kolejno:

- Zakres komórek - u nas to tabela z punktami studentów;

- Zakres zbioru - to nowa tabela, w której zawarliśmy przedziały, które interesują nas w analizie.

Zaznaczamy również opcję Wykres wyjściowy i Łączny udział procentowy. Po kliknięciu OK, wykres pojawi się w nowym arkuszu z naniesionymi parametrami:

Jest to moim zdaniem bardzo czytelna forma analizowania danych.

#65 Analiza danych - statystyka opisowa

Mamy dane odnośnie sprzedaży dwóch handlowców. Jedną z możliwości szybkiej analizy tych danych, pomijając niezastąpione "na oko", jest Statystyka opisowa, która skrywa się w opcjach dodatku Analiza danych. Wejdźmy zatem ponownie do tej zakładki, tym razem wybierając: Statystyka opisowa.


W nowym okienku należy wybrać zakres danych, jak i rodzaj przygotowanego raportu.Proponuję, na początku, wybrać opcję Statystyki podsumowujące. Pojawią się one w następnym arkuszu.

W nowym okienku mamy już wyłożone na tacy najbardziej podstawowe miary statystyczne, które już dają nieco lepszy pogląd na kształtowanie się osiągnięć handlowców. Widzimy chociażby, że w przypadku pierwszego nie tylko średnia i mediana są wyższe, ale i mniejsze jest odchylenie standardowe, stąd można oczekiwać, że jego sprzedaż z miesiąca na miesiąc nie będzie drastycznie się różnić. Mamy też podane minimum, maksimum sprzedaży, jak i wynikający z tych dwóch wartości zakres.

 

Strona:






Polecane artykuły

Testy

Tagi

 android   apple   ciekawostki   gaming   google   gry   internet   komputery   kosmos   microsoft   mobilne   nauka   polska   samsung   smartfon   smartfony   tablet   technologia   telefony   windows 

Korzystamy z plików cookies i umożliwiamy zamieszczanie ich osobom trzecim. Pliki cookie pozwalają na poznanie twoich preferencji na podstawie zachowań w serwisie.
Uznajemy, że jeżeli kontynuujesz korzystanie z serwisu, wyrażasz na to zgodę. Poznaj szczegóły i możliwości zmiany ustawień w Polityce Cookies.