Analiza danych przy pomocy funkcji Najważniejsze narzędzia analityczne 25 XI 2013

1. Analiza danych przy pomocy funkcji Najważniejsze narzędzia analityczne 25 XI 2013

2. Analiza danych przy pomocy funkcji• przegląd wybranych funkcji Excela (m.in. wyszukaj.pionowo, jeżeli, suma.jeżeli)• przypisywanie nazw zakresom komórek• zagnieżdżanie funkcji• formuły tablicowe Najważniejsze narzędzia analityczne (cz. I)• sprawdzanie poprawności danych• zaawansowane sortowanie• sumy częściowe• filtr zaawansowany• narzędzie „usuń duplikaty”

3. Funkcja WYSZUKAJ.PIONOWO • WYSZUKAJ.PIONOWO(szukana_wartość; tablica; nr_kolumny; dopasowanie_przybliżone) • Jeśli tabela jest nieposortowana rosnąco, to będzie problem z wyszukiwaniem przybliżonych wartości • Uwaga na odniesienie bezwzględne do tabeli • Podobna funkcja: WYSZUKAJ.POZIOMO

4. Funkcje: WYSZUKAJ, JEŻELI • WYSZUKAJ(szukana_wartość; wektor_przeszukiwany; wektor_wynikowy) • JEŻELI(test_logiczny; wartość_jeżeli_prawda; wartość_jeżeli_fałsz) • Uwaga na posortowanie

5. Funkcje: PODAJ.POZYCJĘ, INDEKS • PODAJ.POZYCJĘ(szukana_wartość; przeszukiwana_tablica; typ_porównania) • INDEKS(tablica; nr_wiersza; nr_kolumny)

6. SUMA.JEŻELI • SUMA.JEŻELI(zakres_kryterium, kryterium, zakres_suma)

7. Przypisywanie nazw zakresom komórek • ctrl+F3

8. Przypisywanie nazw zakresom komórek – zalety: • Łatwiejsze formuły i makra • Szybkie przemieszczanie dzięki polu nazwy • Łatwiejsze makra • Możliwość nadawania nazwom zakresu lokalnego lub globalnego • Możliwość automatycznego nadawania nazw • Możliwość nazywania kolumn i wierszy • Możliwość tworzenia listy nazw • Autouzupełnianie w formułach • Przeglądanie po zmniejszeniu arkusza

9. Przypisywanie nazw zakresom komórek • Uwaga na wstawianie wierszy lub kolumn: w obrębie i poza nazwanym zakresem • Uwaga na usuwanie wierszy lub kolumn • Uwaga na nazwy globalne podczas kopiowania arkuszy => zostaną przekonwertowane na nazwy lokalne • Uwaga na nazwy obecne mimo usunięcia arkusza => będą miały błędne odwołania • Jak utworzyć formułę (stałą) bez odwołań do komórek:

10. Zagnieżdżanie funkcji Przykłady: • =ROZKŁAD.NORMALNY.ODW(LOS();0;1) • =JEŻELI(CZY.BŁĄD(B7);"";B7) • =LICZBA.CAŁK(FRAGMENT.TEKSTU($J$20;G24;1)) • =10-(MOD(SUMA.ILOCZYNÓW(G26:P26;G25:P25);10)) • Zalety: Excel szybciej działa, pliki mniej ważą • Wada: trudność w zrozumieniu formuły • Na przykład: o co chodzi w poniższej formule? =MACIERZ.ILOCZYN(MACIERZ.ODW(MACIERZ.ILOCZYN(TRANSPONUJ(M2:N7);M2:N7));MACIERZ.ILOCZYN(TRANSPONUJ(M2:N7);L2:L7))

11. Zagnieżdżanie funkcji - ćwiczenie • Ostatnia cyfra numeru PESEL ma charakter kontrolny. Każdej pozycji numeru PESEL nadany został odpowiedni współczynnik - waga pozycji. Algorytm kontrolny numeru PESEL jest następujący: każdą cyfrę numeru mnoży się przez jego wagę i sumuje wyniki mnożenia. Otrzymany wynik dzielimy modulo 10 i odejmujemy od 10 (dla wyniku 10 cyfra kontrolna przyjmuje wartość 0). • Wagi: 1 3 7 9 1 3 7 9 1 3

12. Formuły tablicowe • Wielokomórkowa formuła tablicowa • Jednokomórkowa formuła tablicowa, np. suma ilorazów • Inny przykład: SUMA(MAX.K(G24:P26;{1;2;3})) • ŚREDNIA(A1:A5-B1:B5) • Zaznaczamy zakres komórek lub jedną komórkę, wpisujemy formułę, wciskamy : ctrl+shift+enter • Nawiasy klamrowe wstawi za nas Excel • Stała tablicowa – przechowywana w pamięci 10-(MOD(SUMA(pesel*wagi);10)) 10-(MOD(SUMA(pesel*{1;3;7;9;1;3;7;9;1;3});10))

13. Formuły tablicowe - przykłady • Poprawna postać stałej tablicowej: {1; 2; prawda; fałsz; „MF”} • Niepoprawna postać: {SUMA(1;2); 2%} • Jednowymiarowe tablice poziome: {„Styczeń”; „Luty”; „Marzec”; „Kwiecień”} • Jednowymiarowe tablice pionowe: {1\2\3\4\5\6} • Dwuwymiarowe tablice: {1;2;3\4;5;6\7;8;9} • Przydatne funkcje: TRANSPONUJ, INDEKS, WIERSZ, CZĘSTOŚĆ • Przykładowo: INDEKS({1;2;3\4;5;6\7;8;9};2;2)= 5 • WIERSZ(ADR.POŚR("1:16"))

14. Formuły tablicowe – warunki logiczne • Jeśli chcemy skorzystać z wartości logicznych w formułach tablicowych, np. zsumować liczbę „prawd”, trzeba je przekonwertować na wartości liczbowe poprzez przekształcenie tożsamościowe, np. *1 lub +0 • Uwaga! Funkcje ORAZ i LUB działają źle w formułach tablicowych • Przykład: jak sprawdzić, czy dany wyraz znajduje się na liście?

15. Usuń duplikaty • Musimy zdecydować, które rekordy są według nas zduplikowane – czy muszą mieć wszystkie te same pola czy tylko część z nich?

16. Zaawansowane sortowanie • Dynamiczne sortowanie wartości • problem z imionami, gdy występują identyczne wartości • Okno dialogowe „sortowanie”

17. Sumy częściowe • Wiersz sumy w tabeli wykorzystuje funkcję SUMY.CZĘŚCIOWE

18. Co warto wiedzieć o sumach częściowych? • Nie musi być sumą, może być średnią, max, wariancją itd. Należy wybrać odpowiednie działanie jako pierwszy argument. Excel podpowie jak to zrobić • Funkcja nie bierze pod uwagę wierszy odfiltrowanych • Sprawa z wierszami ukrytymi ręcznie jest bardziej skomplikowana

19. Jak wstawić sumę częściową? • Jeśli to potrzebne, konwertujemy tabelę na zakres • Sortujemy zakres • Dane/Konspekt/Suma częściowa

20. Filtr zaawansowany Określenie kryterium: =, >, >=, <, <=, <> * - dowolna liczba znaków, ? – jeden dowolny znak

21. Określenie kryterium w filtrze zaawansowanym Kryteria zawsze muszą zwracać PRAWDA lub FAŁSZ W porównaniach liczb: =, >, >=, <, <=, <> W porównaniach tekstów: * - dowolna liczba znaków, ? - jeden dowolny znak Przykłady: =„=AB1” – rekordy jednoznacznie zawierające tekst „AB1” AB – rekordy rozpoczynające się od „AB” <>A* - rekordy niezaczynające się na literę „A” *AB* - rekordy zawierające w sobie słowo „AB” =??? – rekordy zawierające dokładnie trzy litery = - puste rekordy <> - rekordy, które zawierają jakikolwiek niepusty wpis

22. Sprawdzanie poprawności danych • Zaznaczamy komórkę lub zakres. • Wybieramy Dane/Narzędzia danych/Poprawność danych/Ustawienia • Wybieramy opcję z listy „Dozwolone” • Określamy warunki (w zależności od uprzednio wybranej opcji) • (Opcjonalnie) Wpisujemy komunikat wejściowy • (Opcjonalnie) Wpisujemy komunikat w razie wystąpienia błędu Ćwiczenie: Budżet departamentu AB. Nie może zostać przekroczony!!!

23. Tworzenie list rozwijanych • Tworzymy pozycje listy (patrz powyżej kolumna G) (Alternatywnie można ją wpisać wprost w komórce „źródło”) • Zaznaczamy komórkę lub zakres. • Wybieramy Dane/Narzędzia danych/Poprawność danych/Ustawienia • Wybieramy opcję „Lista” z listy „Dozwolone” • Źródło: zakres listy z kolumny G • Sprawdź, czy w danej komórce można wybierać pozycje z listy