Excel do podejmowania decyzji

oprócz formuł innym sposobem przeprowadzania obliczeń matematycznych w programie Excel są funkcje. Funkcje statystyczne stosują proces matematyczny do grupy komórek w arkuszu roboczym. Na przykład funkcja SUM służy do dodawania wartości zawartych w zakresie komórek. Wykaz powszechnie stosowanych funkcji statystycznych przedstawiono w tabeli 2.4. Funkcje są bardziej wydajne niż formuły, gdy stosujesz proces matematyczny do grupy komórek. Jeśli używasz formuły, aby dodać wartości w zakresie komórek, musisz dodać każdą lokalizację komórki do Formuły 1 naraz. Może to być bardzo czasochłonne, jeśli musisz dodać wartości w kilkuset lokalizacjach komórek. Jednak podczas korzystania z funkcji można wyróżnić wszystkie komórki zawierające wartości, które chcesz zsumować w jednym kroku. Ta sekcja pokazuje wiele funkcji statystycznych, które dodamy do skoroszytu Personal Budget. Oprócz demonstrowania funkcji, w tej sekcji przegląda się również procent całkowitych obliczeń i wykorzystanie bezwzględnych odniesień.

Tabela 2.4 powszechnie używane funkcje statystyczne

funkcja suma jest używana, gdy musisz obliczyć sumy dla zakresu komórek lub grupy wybranych komórek w arkuszu roboczym. W odniesieniu do arkusza szczegółów budżetu użyjemy funkcji sumy do obliczenia sumy w wierszu 12. Ważne jest, aby pamiętać, że istnieje kilka metod dodawania funkcji do arkusza roboczego, które zostaną zademonstrowane w dalszej części tego rozdziału. Poniżej przedstawiono, w jaki sposób funkcję można dodać do arkusza roboczego, wpisując ją w lokalizacji komórki:

1. kliknij kartę arkusz szczegółowy budżetu, aby otworzyć arkusz roboczy.
2. kliknij komórkę C12.
3. wpisz znak równości =.
4. wpisz nazwę funkcji SUMA.
5. wpisz otwarty nawias (.
6. kliknij komórkę C3 i przeciągnij w dół do komórki C11. To umieszcza zakres C3: C11 w funkcji.
7. wpisz nawias zamykający).
8. naciśnij klawisz ENTER. Funkcja oblicza sumę dla kolumny miesięcznych wydatków, która wynosi $1,496.

rysunek 2.11 pokazuje wygląd funkcji SUMA dodanej do arkusza szczegółów budżetu przed naciśnięciem klawisza ENTER.

jak pokazano na rysunku 2.11, funkcja sumy została dodana do komórki C12. Jednak funkcja ta jest również potrzebna do obliczenia sumy w kolumnach wydatków rocznych i wydatków LY. Funkcja może być kopiowana i wklejana do tych lokalizacji komórek ze względu na względne odniesienie. Względne odwoływanie służy temu samemu celowi dla funkcji, co dla formuł. Poniżej przedstawiono, w jaki sposób całkowity wiersz jest wypełniany:

1. kliknij komórka C12 w arkuszu szczegółów budżetu.
2. kliknij przycisk Kopiuj na karcie Strona główna wstążki.
3. zaznacz komórki D12 i E12.
4. kliknij przycisk Wklej na karcie Strona główna wstążki. To wkleja funkcję sumy do komórek D12 i E12 i oblicza sumy dla tych kolumn.
5. kliknij komórkę F11.
6. kliknij przycisk Kopiuj na karcie Strona główna wstążki.
7. kliknij cell F12, a następnie kliknij Wklej przycisk na karcie Strona główna wstążki. Ponieważ mamy teraz sumy w wierszu 12, możemy wkleić wzór zmiany procentowej do tego wiersza.

rysunek 2.12 pokazuje wyjście funkcji sumy, która została dodana do komórek C12, D12 i E12. Ponadto wzór zmiany procentowej został skopiowany i wklejony do komórki F12. Zauważ, że ta wersja budżetu planuje spadek wydatków o 1,7% w porównaniu do ubiegłego roku.

bezwzględne odniesienia (Obliczanie procent sumy)

plik danych: Kontynuuj z CH2 Personal Budget.

ponieważ sumy zostały dodane do wiersza 12 arkusza szczegółowego budżetu, procent całkowitego obliczenia można dodać do kolumny B rozpoczynającej się w komórce B3. Wartość procentowa całkowitego obliczenia pokazuje wartość procentową każdej wartości w kolumnie wydatków rocznych w odniesieniu do sumy w komórce D12. Jednak po utworzeniu formuły konieczne będzie wyłączenie funkcji względnego odwoływania programu Excel przed skopiowaniem i wklejeniem formuły do pozostałych lokalizacji komórek w kolumnie. Wyłączenie funkcji względnego odwoływania programu Excel odbywa się za pomocą bezwzględnego odniesienia. Poniższe kroki wyjaśniają, jak to się robi:

1. kliknij komórka B3 w arkuszu szczegółów budżetu.
2. wpisz znak równości =.
3. kliknij komórkę D3.
4. wpisz ukośnik do przodu /.
5. kliknij komórkę D12.
6. naciśnij klawisz ENTER. Widać, że gospodarstwa domowe stanowią 16,7% rocznego budżetu wydatków (patrz rysunek 2.14).

Rysunek 2.14 przedstawia wypełniony wzór, który oblicza procentowy udział rocznych wydatków gospodarstw domowych w całkowitych rocznych wydatkach budżetu (zob. komórka B3). Normalnie skopiowalibyśmy tę formułę i wkleilibyśmy ją do zakresu B4: B11. Jednak ze względu na względne odniesienie, oba odwołania do komórek zwiększy się o jeden wiersz, ponieważ wzór jest wklejany do komórek poniżej B3. Jest to w porządku dla pierwszej komórki odniesienia we wzorze (D3), ale nie dla drugiej komórki odniesienia (D12). Rysunek 2.15 ilustruje, co się stanie, jeśli wkleimy wzór do zakresu B4: B12 w obecnym stanie. Zauważ, że Excel generuje kod błędu # DIV / 0. Oznacza to, że Excel próbuje podzielić liczbę przez zero, co jest niemożliwe. Patrząc na wzór w komórce B4, widać, że pierwsza komórka odniesienia została zmieniona z D3 na D4. Jest to w porządku, ponieważ chcemy teraz podzielić roczne wydatki na ubezpieczenia przez całkowite roczne wydatki w komórce D12. Jednak Excel zmienił również odniesienie do komórki D12 na D13. Ponieważ lokalizacja komórki D13 jest pusta, formuła tworzy kod błędu # DIV/0.

aby wyeliminować błąd dzielenia przez zero pokazany na rysunku 2.15, musimy dodać bezwzględne odniesienie do komórki D12 we wzorze. Odniesienie bezwzględne zapobiega zmianie odniesienia względnego w komórce we wzorze. Jest to również określane jako blokowanie komórki. Poniżej wyjaśniamy, jak to się odbywa:

1. Kliknij dwukrotnie komórkę B3.
2. Umieść wskaźnik myszy przed D12 i kliknij. Migający kursor powinien znajdować się przed D w komórce odniesienia D12.
3. naciśnij klawisz F4. Zobaczysz znak dolara ( $ ) dodany przed literą kolumny D i numerem wiersza 12. Możesz także wpisać znaki dolara przed literą kolumny i numerem wiersza.
4. naciśnij klawisz ENTER.
5. kliknij komórkę B3.
6. kliknij przycisk Kopiuj na karcie Strona główna wstążki.
7. zaznacz zakres B4: B11.
8. kliknij przycisk Wklej na karcie Strona główna wstążki.

rysunek 2.16 pokazuje procent całkowitego wzoru z bezwzględnym odniesieniem dodanym do D12. Zauważ, że w komórce B4, odniesienie do komórki pozostaje D12 zamiast zmieniać się na D13, jak pokazano na fig. 2.15. Ponadto zobaczysz, że procenty są obliczane w pozostałych komórkach w kolumnie, A błąd dzielenia przez zero jest teraz eliminowany.

funkcja liczenia

plik danych: Kontynuuj z CH2 Personal Budget.

następną funkcją, którą dodamy do arkusza szczegółów budżetu, jest funkcja zliczania. Funkcja COUNT służy do określenia, ile komórek w zakresie zawiera wpis liczbowy. Funkcja COUNT nie będzie działać do zliczania tekstu lub innych nieliczbowych wpisów. W przypadku arkusza szczegółów budżetu użyjemy funkcji policz, aby policzyć liczbę pozycji zaplanowanych w kolumnie wydatków rocznych (Kolumna D). Poniżej wyjaśniono, w jaki sposób funkcja COUNT jest dodawana do arkusza roboczego za pomocą listy funkcji:

1. kliknij komórka D13 w arkuszu szczegółów budżetu.
2. wpisz znak równości =.
3. wpisz literę C.
4. kliknij strzałkę w dół na pasku przewijania listy funkcji (patrz rysunek 2.17) i znajdź liczbę słów.
5. kliknij dwukrotnie liczbę słów z listy funkcji.
6. zaznacz zakres D3: D11.
7. możesz wpisać nawias zamykający), a następnie naciśnij klawisz ENTER lub po prostu naciśnij klawisz ENTER, a program Excel zamknie funkcję za Ciebie. Funkcja daje wynik 9, ponieważ w arkuszu zaplanowano 9 elementów.

Rysunek 2.17 pokazuje listę funkcji, która pojawia się po wykonaniu kroków 2 i 3 dla funkcji COUNT. Lista funkcji zapewnia alternatywną metodę dodawania funkcji do arkusza roboczego.

rysunek 2.18 pokazuje wyjście funkcji COUNT po naciśnięciu klawisza ENTER. Funkcja zlicza liczbę komórek z zakresu D3: D11, które zawierają wartość liczbową. Wynik 9 wskazuje, że w tym budżecie zaplanowano 9 kategorii.

średnia funkcja

następną funkcją, którą dodamy do arkusza szczegółów budżetu, jest funkcja średnia. Funkcja ta służy do obliczania średniej arytmetycznej dla grupy liczb. W przypadku arkusza szczegółów budżetu użyjemy funkcji do obliczenia średniej wartości w kolumnie wydatków rocznych. Dodamy to do arkusza za pomocą biblioteki funkcji. Poniższe kroki wyjaśniają, w jaki sposób można to osiągnąć:

1. kliknij komórka D14 w arkuszu szczegółów budżetu.
2. kliknij kartę formuły na Wstążce.
3. kliknij przycisk Więcej funkcji w grupie poleceń Biblioteka funkcji.
4. Umieść wskaźnik myszy nad opcją statystyczną z rozwijanej listy opcji.
5. kliknij średnią nazwę funkcji z listy funkcji wyświetlanych w menu (patrz rysunek 2.19). Spowoduje to otwarcie okna dialogowego argumenty funkcji.
6. kliknij przycisk Zwiń okno dialogowe w oknie dialogowym argumenty funkcji (patrz rysunek 2.20).
7. zaznacz zakres D3: D11.
8. kliknij przycisk Rozwiń okno dialogowe w oknie dialogowym argumenty funkcji (patrz rysunek 2.21). Możesz również nacisnąć klawisz ENTER, aby uzyskać ten sam wynik.
9. kliknij przycisk OK w oknie dialogowym argumenty funkcji. Dodaje To średnią funkcję do arkusza roboczego.

rysunek 2.19 ilustruje sposób wybierania funkcji z biblioteki funkcji na karcie Formuły wstążki.

rysunek 2.20 pokazuje okno dialogowe argumenty funkcji. Pojawia się po wybraniu funkcji z biblioteki funkcji. Przycisk Zwiń okno dialogowe służy do ukrycia okna dialogowego, dzięki czemu zakres komórek może być podświetlony w arkuszu roboczym, a następnie dodany do funkcji.

rysunek 2.21 pokazuje, w jaki sposób można wybrać zakres komórek z okna dialogowego argumenty funkcji po jego zwinięciu.

Rysunek 2.22 pokazuje okno dialogowe argumenty funkcji po zdefiniowaniu zakresu komórek dla funkcji średniej. Okno dialogowe pokazuje wynik funkcji przed dodaniem jej do lokalizacji komórki. Pozwala to ocenić wyjście funkcji, aby określić, czy ma to sens przed dodaniem go do arkusza roboczego.

rysunek 2.23 pokazuje ukończoną średnią funkcję w arkuszu szczegółów budżetu. Wynik funkcji pokazuje, że średnio spodziewamy się wydać $1,994 na każdą z kategorii wymienionych w kolumnie a budżetu. Ta średnia kalkulacja wydatków na kategorię może być wykorzystana jako wskaźnik do określenia, które kategorie kosztują więcej lub mniej niż średnia budżetowana kwota wydatków w dolarach.

funkcje MAX I MIN

plik danych: Kontynuuj z CH2 Personal Budget.

ostatnie dwie funkcje statystyczne, które dodamy do arkusza szczegółów budżetu, to funkcje MAX I MIN. Funkcje te identyfikują najwyższe i najniższe wartości w zakresie komórek. Poniższe kroki wyjaśniają, jak dodać te funkcje do arkusza szczegółów budżetu:

1. kliknij komórka D15 w arkuszu szczegółów budżetu.
2. wpisz znak równości =.
3. Wpisz słowo MIN.
4. wpisz otwarty nawias (.
5. zaznacz zakres D3: D11.
6. wpisz nawias zamykający) i naciśnij klawisz ENTER lub po prostu naciśnij klawisz ENTER, a program Excel zamknie funkcję za Ciebie. Funkcja MIN daje wynik 1200 USD, co jest najniższą wartością w kolumnie wydatków rocznych (patrz rysunek 2.24).
7. kliknij komórkę D16.
8. wpisz znak równości =.
9. Wpisz słowo MAX.
10. wpisz otwarty nawias (.
11. zaznacz zakres D3: D11.
12. wpisz nawias zamykający) i naciśnij klawisz ENTER lub po prostu naciśnij klawisz ENTER, a program Excel zamknie funkcję za Ciebie. Funkcja MAX daje wynik $3,500. Jest to najwyższa wartość w kolumnie wydatków rocznych (zob. rysunek 2.25).

Kopiuj i wklej Formuły (wklejanie bez formatów)

plik danych: Kontynuuj z CH2 Personal Budget.

jak pokazano na rysunku 2.25, funkcje COUNT, AVERAGE, MIN i MAX podsumowują dane w kolumnie rocznych wydatków. Zauważysz również, że w kolumnie Ly Spend jest miejsce na skopiowanie i wklejenie tych funkcji. Dzięki temu możemy porównać to, co wydaliśmy w zeszłym roku i to, co planujemy wydać w tym roku. Zwykle po prostu kopiujemy i wklejamy te funkcje do zakresu E13: E16. Być może jednak zauważyłeś obramowanie stylu dwuliniowego, które było używane na obwodzie zakresu B13: E16. Jeśli użyjemy zwykłego polecenia Wklej, podwójna linia po prawej stronie zakresu E13: E16 zostanie zastąpiona pojedynczą linią. Dlatego użyjemy jednego ze specjalnych poleceń Wklej, aby wkleić tylko funkcje bez żadnych zabiegów formatowania. Osiąga się to poprzez następujące kroki:

1. zaznacz zakres D13: D16 w arkuszu szczegółów budżetu.
2. kliknij przycisk Kopiuj na karcie Strona główna wstążki.
3. kliknij komórkę E13.
4. kliknij strzałkę w dół poniżej przycisku Wklej na karcie Strona główna wstążki.
5. kliknij Formuły opcja z rozwijanej listy przycisków (patrz rysunek 2.26).

rysunek 2.26 pokazuje listę przycisków, które pojawiają się po kliknięciu strzałki w dół poniżej przycisku Wklej na karcie Strona główna wstążki. Należy zwrócić uwagę na to, że możesz wyświetlić podgląd tych opcji przed dokonaniem wyboru, przeciągając wskaźnik myszy nad opcjami. Jak pokazano na rysunku, po umieszczeniu wskaźnika myszy nad przyciskiem formuł można zobaczyć, jak funkcje będą się wyświetlać przed dokonaniem wyboru. Zauważ, że obramowanie dwuliniowe nie zmienia się, gdy ta opcja jest wyświetlana pod podglądem. Dlatego wybór ten jest dokonywany zamiast zwykłej opcji wklejania.