Excel 2021 i Microsoft 365. Analiza i modelowanie danych biznesowych - Wayne Winston

Excel 2021i Microsoft 365Analiza i modelowanie danych biznesowych

wydanie 7

Wayne L. Winston

Przekład:Janusz MachowskiLeszek BiolikMarek Włodarz

APN PromiseWarszawa 2022

Excel 2021 i Microsoft 365 Analiza i modelowanie danych biznesowych

Authorized Polish translation of the English language edition entitled Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition by Wayne L. Wilson, ISBN: 978-0-13-761366-3

Copyright ? 2022 by Pearson Education, Inc.

All rights reserved. No part of this book may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording or by any information storage retrieval system, without permission from Pearson Education, Inc.

Polish language edition published by APN PROMISE SA Copyright ? 2022

Autoryzowany przekład z wydania w języku angielskim, zatytułowanego:Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition by Wayne L. Wilson, ISBN: 978-0-13-761366-3

Wszystkie prawa zastrzeżone. Żadna część niniejszej książki nie może być powielana ani rozpowszechniana w jakiejkolwiek formie i w jakikolwiek sposób (elektroniczny, mechaniczny), włącznie z fotokopiowaniem, nagrywaniem na taśmy lub przy użyciu innych systemów bez pisemnej zgody wydawcy.

APN PROMISE SA, ul. Domaniewska 44a, 02-672 Warszawatel. +48 22 35 51 600,e-mail: [email protected]

Książka ta przedstawia poglądy i opinie autora. Przykłady firm, produktów, osób i wydarzeń opisane w niniejszej książce są fikcyjne i nie odnoszą się do żadnych konkretnych firm, produktów, osób i wydarzeń, chyba że zostanie jednoznacznie stwierdzone, że jest inaczej. Ewentualne podobieństwo do jakiejkolwiek rzeczywistej firmy, organizacji, produktu, nazwy domeny, adresu poczty elektronicznej, logo, osoby, miejsca lub zdarzenia jest przypadkowe i niezamierzone.

Microsoft oraz znaki towarowe wymienione na stronie http://www.microsoft.com/about/legal/en/us/IntellectualProperty/Trademarks/EN-US.aspx są zastrzeżonymi znakami towarowymi grupy Microsoft. Wszystkie inne znaki towarowe są własnością ich odnośnych właścicieli.

APN PROMISE SA dołożyła wszelkich starań, aby zapewnić najwyższą jakość tej publikacji. Jednakże nikomu nie udziela się rękojmi ani gwarancji. APN PROMISE SA nie jest w żadnym wypadku odpowiedzialna za jakiekolwiek szkody będące następstwem korzystania z informacji zawartych w niniejszej publikacji, nawet jeśli APN PROMISE została powiadomiona o możliwości wystąpienia szkód.

ISBN: 978-83-7541-488-2 (druk), 978-83-7541-489-9 (ebook)

Projekt graficzny okładki: Twist Creative, Seattle

Przekład: Janusz Machowski, Leszek Biolik, Marek WłodarzKorekta: Ewa SwędrowskaSkład i łamanie: MAWart Marek Włodarz

Dla Vivian, Jen i Grega.Jesteście wspaniali i tak bardzo was kocham!

Spis treści

Wstęp

O autorze

Podstawowe modelowanie arkusza Nazwy zakresów

Jak tworzyć nazwy zakresów?

Tworzenie nazwy zakresu przy użyciu Pola nazwy

Tworzenie nazw zakresów przy użyciu polecenia Utwórz z zaznaczenia

Tworzenie nazw zakresów przy użyciu opcji Definiuj nazwę

Menedżer nazw

Odpowiedzi na pytania z początku rozdziału

Uwagi

Problemy

Funkcje wyszukujące

Składnia funkcji WYSZUKAJ

Składnia WYSZUKAJ.PIONOWO (VLOOKUP)

Składnia WYSZUKAJ.POZIOMO (HLOOKUP)

Składnia X.WYSZUKAJ (XLOOKUP)

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcja INDEKS

Składnia funkcji INDEKS (INDEX)

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcja PODAJ.POZYCJĘ

Składnia funkcji PODAJ.POZYCJĘ

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcje tekstowe i wypełnianie błyskawiczne

Składnia funkcji tekstowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Data i funkcje daty

Odpowiedzi na pytania z początku rozdziału

Problemy

Ocena inwestycji za pomocą kryteriów zdyskontowanej wartości netto

Odpowiedzi na pytania z początku rozdziału

Problemy

Wewnętrzna stopa zwrotu

Odpowiedzi na pytania z początku rozdziału

Problemy

Jeszcze więcej funkcji finansowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Odwołania cykliczne

Odpowiedzi na pytania z początku rozdziału

Problemy

Instrukcje warunkowe

Odpowiedzi na pytania z początku rozdziału

Problemy

Czas i funkcje czasu

Odpowiedzi na pytania z początku rozdziału

Problemy

Polecenie Wklej specjalnie

Odpowiedzi na pytania z początku rozdziału

Problemy

Formuły trójwymiarowe

Odpowiedzi na pytania z początku rozdziału

Problemy

Narzędzia inspekcji i dodatek Inquire

Opcje inspekcji programu Excel

Pokaż formuły

Sprawdzanie błędów

Okno czujki

Śledź poprzedniki, Śledź zależności i Usuń strzałki

Odpowiedzi na pytania z początku rozdziału

Problemy

Analiza wrażliwości przy użyciu tabel danych

Odpowiedzi na pytania z początku rozdziału

Problemy

Polecenie Szukaj wyniku

Odpowiedzi na pytania z początku rozdziału

Problemy

Używanie Menedżera scenariuszy do analizy wrażliwości

Odpowiedź na pytanie z początku rozdziału

Uwagi

Problemy

Funkcje LICZ.JEŻELI,LICZ.WARUNKI, ILE.LICZB,ILE.NIEPUSTYCH i LICZ.PUSTE

Odpowiedzi na pytania z początku rozdziału

Uwagi

Problemy

Warunkowe funkcje agregujące

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcja PRZESUNIĘCIE

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcja ADR.POŚR

Odpowiedzi na pytania z początku rozdziału

Problemy

Formatowanie warunkowe

Odpowiedzi na pytania z początku rozdziału

Problemy

Tabele i fragmentatory tabel

Odpowiedzi na pytania z początku rozdziału

Problemy

Pokrętła, paski przewijania, przyciski opcji, pola wyboru, kombi, grup i list

Odpowiedzi na pytania z początku rozdziału

Problemy

Importowanie danych z pliku tekstowego lub dokumentu

Odpowiedź na pytanie z początku rozdziału

Problemy

Edytor Power Query

Odpowiedzi na pytania z tego rozdziału

Problemy

Nowe typy danych programu Excel

Odpowiedzi na pytania z tego rozdziału

Problemy

Podsumowywanie danych za pomocą histogramów i wykresów Pareto

Odpowiedzi na pytania z początku rozdziału

Problemy

Podsumowywanie danych przy użyciu statystyki opisowej

Odpowiedzi na pytania z początku rozdziału

Problemy

Sortowanie w programie Excel

Odpowiedzi na pytania z początku rozdziału

Problemy

Filtrowanie danych i usuwanie powtórzeń

Odpowiedzi na pytania z początku rozdziału

Problemy

Podsumowywanie danych przy użyciu bazodanowych funkcji statystycznych

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcje i formuły tablicowe

Odpowiedzi na pytania z początku rozdziału

Problemy

Nowe dynamiczne funkcje tablicowe

Odpowiedzi na pytania z tego rozdziału

Problemy

Kontrola poprawności danych

Odpowiedzi na pytania z początku rozdziału

Uwagi

Problemy

Opisywanie danych przy użyciu tabel przestawnych i fragmentatorów

Odpowiedzi do pytań z początku rozdziału

Problemy

Model danych

Odpowiedzi na pytania z początku rozdziału

Problemy

Power Pivot

Odpowiedzi na pytania z początku rozdziału

Problemy

Konsolidowanie danych

Odpowiedź na pytanie z początku rozdziału

Problemy

Tworzenie sum częściowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Podstawy wykresów

Odpowiedzi na pytania z tego rozdziału

Problemy

Wykresy zaawansowane

Odpowiedzi na pytania z początku rozdziału

Problemy

Kartogramy i Mapy 3D

Odpowiedzi na pytania z początku rozdziału

Problemy

Wykresy przebiegu w czasie

Odpowiedzi na pytania z początku rozdziału

Problemy

Szacowanie zależności liniowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Modelowanie wzrostu wykładniczego

Odpowiedź na pytania z początku rozdziału

Problemy

Krzywa potęgowa

Odpowiedź na pytania z początku rozdziału

Problemy

Wykorzystanie korelacji do podsumowywania zależności

Odpowiedź na pytania z początku rozdziału

Problemy

Wprowadzenie do regresji wielokrotnej

Odpowiedzi na pytania z początku rozdziału

Regresja wielokrotna z uwzględnieniem czynników jakościowych

Odpowiedzi na pytania z początku rozdziału

Modelowanie nieliniowości i interakcji

Odpowiedzi na pytania z początku rozdziału

Problemy do rozdziałów 51 - 53

Jednoczynnikowa analiza wariancji

Odpowiedzi na pytania z początku rozdziału

Problemy

Bloki losowe oraz dwuczynnikowa analiza wariancji

Odpowiedzi na pytania z początku rozdziału

Problemy

Interpretacja szeregów czasowych przy użyciu średnich ruchomych

Odpowiedź na pytanie z początku rozdziału

Problem

Metoda prognozowania "stosunek do średniej ruchomej"

Odpowiedzi na pytania z początku rozdziału

Problem

Wprowadzenie do rachunku prawdopodobieństwa

Odpowiedzi na pytania z początku rozdziału

Problemy

Wprowadzenie do zmiennych losowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Zmienne losowe o rozkładzie dwumianowym, hipergeometrycznym i ujemnym dwumianowym

Odpowiedzi na pytania z początku rozdziału

Problemy

Zmienne losowe o rozkładzie Poissona i wykładniczym

Odpowiedzi na pytania z początku rozdziału

Problemy

Zmienna losowa o rozkładzie normalnym i wskaźniki Z

Odpowiedzi na pytania z początku rozdziału

Problemy

Ocenianie prawdopodobieństwa prognozy

Odpowiedzi na pytania z początku rozdziału

Problemy

Modelowanie cen akcji za pomocą zmiennej losowej o rozkładzie logarytmiczno-normalnym

Odpowiedzi na pytania z początku rozdziału

Uwagi

Problemy

Importowanie wcześniejszych cen akcji, kursów wymiany walut i cen kryptowalut

Odpowiedzi na pytania z tego rozdziału

Problemy

Wprowadzenie do optymalizacji przy użyciu dodatku Solver

Odpowiedzi na pytania z tego rozdziału

Problemy

Użycie dodatku Solver do ustalenia optymalnego asortymentu produkcji

Odpowiedzi na pytania z początku rozdziału

Problemy

Wykorzystywanie dodatku Solver do planowania zatrudnienia

Odpowiedź na pytanie z początku rozdziału

Problemy

Wykorzystywanie dodatku Solver do rozwiązywania problemów transportu i dystrybucji

Odpowiedź na pytanie z początku rozdziału

Problemy

Wykorzystywanie dodatku Solver do planowania nakładów inwestycyjnych

Odpowiedź na pytania z początku rozdziału

Problemy

Wykorzystywanie dodatku Solver do planowania finansowego

Odpowiedzi na pytania z początku rozdziału

Problemy

Wykorzystywanie dodatku Solver do oceny drużyn sportowych

Odpowiedź na pytania z początku rozdziału

Problemy

Metoda Wintersa i arkusze prognoz

Odpowiedź na pytania z początku rozdziału

Uwagi

Problemy

Prognozowanie z uwzględnieniem zdarzeń specjalnych

Odpowiedzi na pytania z początku rozdziału

Problemy

Lokalizacja magazynów za pomocą metody Multistart GRG i metody ewolucyjnej dodatku Solver

Odpowiedzi na pytania z początku rozdziału

Problemy

Kary i metoda ewolucyjna dodatku Solver

Odpowiedzi na pytania z początku rozdziału

Problemy

Problem komiwojażera

Odpowiedzi na pytania z początku rozdziału

Problemy

Rozkłady Weibulla i beta: modelowanie żywotności urządzeń i czasu trwania projektów

Odpowiedzi na pytania z początku rozdziału

Problemy

Wprowadzenie do metody symulacji Monte Carlo

Odpowiedzi na pytania z początku rozdziału

Problemy

Wyliczanie optymalnej oferty

Odpowiedzi na pytania z początku rozdziału

Problemy

Symulowanie cen akcji i modelowanie alokacji aktywów

Odpowiedzi na pytania z początku rozdziału

Problemy

Gry i zabawy: symulowanie prawdopodobieństw wyników gier hazardowych i wydarzeń sportowych

Odpowiedzi na pytania z początku rozdziału

Problemy

Analiza danych przy użyciu metody resamplingu

Odpowiedź na pytanie z początku rozdziału

Problemy

Ustalanie cen opcji kupna i sprzedaży akcji

Odpowiedzi na pytania z początku rozdziału

Problemy

Ustalanie wartości klienta

Odpowiedzi na pytania z początku rozdziału

Problemy

Model ekonomicznej wielkości zamówienia

Odpowiedzi na pytania z początku rozdziału

Problemy

Modelowanie poziomu zapasów przy niepewnym popycie

Odpowiedzi na pytania z początku rozdziału

Problemy

Teoria kolejek: analiza oczekiwania w kolejce

Odpowiedzi na pytania z początku rozdziału

Problemy

Szacowanie krzywej popytu

Odpowiedzi na pytania z początku rozdziału

Problemy

Wycenianie produktów powiązanych

Odpowiedź na pytanie z początku rozdziału

Problemy

Wycenianie produktów na podstawie subiektywnie ustalonego popytu

Odpowiedzi na pytania z początku rozdziału

Problemy

Wycena nieliniowa

Odpowiedzi na pytania z początku rozdziału

Problemy

Używanie funkcjonalności Analiza danych do wyszukiwania wzorców w danych

Odpowiedzi na pytania z tego rozdziału

Problemy

Rejestrowanie makr

Odpowiedzi na pytania z początku rozdziału

Problemy

Funkcje LET i LAMBDA oraz funkcje pomocnicze LAMBDA

Odpowiedzi na pytania z tego rozdziału

Problemy

Zaawansowana analiza wrażliwości

Odpowiedzi na pytanie z początku rozdziału

Problemy

Nazwy funkcji

Polecamy także

Wstęp

Bez względu na to, czy jesteś pracownikiem dużej korporacji z listy Fortune 500, małej firmy, instytucji rządowej albo organizacji charytatywnej, skoro czytasz ten wstęp, to prawdopodobnie korzystasz w swojej pracy z programu Microsoft Excel. Do twoich zadań należy zapewne opracowywanie zestawień, raportów oraz analizowanie danych, a być może także tworzenie modeli służących zwiększeniu zysków, redukcji kosztów lub po prostu wydajniejszemu zarządzaniu działalnością.

Od 1999 roku uczyłem tysiące analityków z takich organizacji, jak 3M, Booz Allen Hamilton, Bristol-Myers Squibb, Cisco Systems, Drugstore.com, Ebay, Eli Lilly, Ford, General Electric, General Motors, Intel, Microsoft, NCR, Owens Corning, Pfizer, ­Proctor & Gamble, armia USA, Departament Obrony USA i Verizon, jak używać programu Excel efektywniej i wydajniej. Uczestnicy mówili mi często, że demonstrowane przeze mnie narzędzia i metody pozwoliły im zaoszczędzić wiele godzin pracy tygodniowo oraz umożliwiły stosowanie nowych i lepszych metod analizowania ważnych problemów biznesowych.

Sam wykorzystuję techniki opisane w tej książce do rozwiązywania problemów napotykanych w mojej pracy konsultanta. Na przykład używając programu Excel pomogłem drużynie koszykarskiej Dallas Mavericks i jej właścicielowi Markowi Cubanowi w ocenie sędziów ligi NBA, a także zawodników i sposobu ich rozstawienia. Przez wiele lat prowadziłem także kursy poświęcone modelowaniu i analizie danych biznesowych za pomocą programu Excel dla studentów MBA w Kelley School of Business na Uniwersytecie stanu Indiana. (Na dowód moich umiejętności dydaktycznych otrzymałem 45 nagród nauczycielskich, a sześć razy zdobyłem nagrodę MBA dla całej szkoły). Chciałbym także zauważyć, że 95 procent studentów MBA na Uniwersytecie Indiana uczęszcza na moje zajęcia z modelowania, mimo że są fakultatywne.

Książka ta jest próbą udostępnienia moich udanych kursów wszystkim zainteresowanym. Oto dlaczego uważam, że może ona pomóc w lepszym wykorzystaniu programu Excel:

Jej materiał został sprawdzony podczas nauczania tysięcy analityków pracujących dla korporacji z listy Fortune 500 i agencji rządowych, łącznie z armią USA. Książka jest wykorzystywana przez tysiące studentów MBA na Uniwersytecie Indiana w Wake Forest oraz na Uniwersytecie Houston. Materiały z książki są używane również w szkoleniach firmy Becker, z których korzystają tysiąc księgowych i analityków. Napisałem tę książkę tak, jakbym mówił do Czytelnika. Takie podejście pozwala zachować w tekście atmosferę panującą na sali ćwiczeń. Uczę na przykładach, które ułatwiają zrozumienie przedstawianych pojęć. Przykłady są "wzięte z życia". Wiele z nich ma swoje źródło w pytaniach nadesłanych do mnie przez pracowników korporacji z listy Fortune 500.

Przez większą część książki pokazuję, jak w programie Excel zadawać najróżnorodniejsze pytania dotyczące analizy danych i biznesu i jak znajdować na nie odpowiedzi. Można śledzić mój tok myślenia korzystając z przedstawień arkuszy towarzyszących każdemu przykładowi. Oprócz tego umieściłem pliki odpowiadające poszczególnym przykładom na specjalnej stronie sieci Web (MicrosoftPressStore.com/Excel365data7e/downloads). Pliki te umożliwiają samodzielne wykonanie każdego przykładu bezpośrednio w programie Excel.

W większości przypadków rozdziały są krótkie i dotyczą tylko jednego zagadnienia. Zazwyczaj do opanowania materiału jednego rozdziału potrzebne są najwyżej dwie godziny nauki. Czytając pytania postawione na początku każdego rozdziału można zorientować się, jakiego typu problemy będzie można rozwiązywać po jego przestudiowaniu.

Oprócz samych formuł Excel przedstawione są także, w dość przystępnej formie, niektóre ważne zagadnienia matematyczne. Na przykład omawiane są elementy statystyki, prognozowania, optymalizacji, metoda symulacji Monte Carlo, koszty magazynowania czy teoria kolejek. Można się również dowiedzieć o niektórych najnowszych rozwiązaniach w świecie biznesu - opcjach rzeczywistych, wartości klienta, czy matematycznych metodach wyceny.

Na końcu każdego rozdziału znajduje się lista zadań (łącznie ponad 900), które można przerobić samodzielnie. Wiele z tych zadań jest opartych na rzeczywistych problemach rozwiązywanych przez analityków pracujących dla firm z listy Fortune 500. Zadania te pomagają opanować materiał zawarty w danym rozdziale. Rozwiązania wszystkich zadań można znaleźć w plikach znajdujących się na stronie sieci Web, stanowiących załącznik do książki.

Nauka powinna być przede wszystkim rozrywką. Po przeczytaniu książki Czytelnik będzie wiedział, jak przewidzieć wyniki wyborów prezydenckich, jak utworzyć arkusz wyników rozgrywek piłkarskich, jak ustalić prawdopodobieństwo wygranej w kości i jak obliczyć prawdopodobieństwo wygrania rozgrywek NCAA przez dany zespół. Przykłady te są interesujące i zabawne, a ponadto uczą rozwiązywania wielu problemów biznesowych za pomocą programu Excel.

Uwaga Do realizacji wszystkich przykładów zawartych w tej książce potrzebny jest program Excel z pakietu Microsoft 365. W dużej części tej książki powinna wystarczyć wersja Excel 2021 lub Excel 2019. Zadania z poprzednich wydań książki można realizować odpowiednio w wersji programu Excel 2003, 2007, 2010 i 2013.

Co nowego w tym wydaniu

To wydanie książki zawiera następujace zmiany i nowe elementy:

Rozdział 3 zawiera omówienie nowej funkcji X.WYSZUKAJ (XLOOKUP) oraz wyszukiwania przybliżonego i problemów pojawiających się w sytuacji, gdy formuły nie mogą rozstrzygnąć, czy zawartość komórki jest tekstem, czy liczbą. Rozdział 6 zawiera omówienie, jak Excel radzi sobie ze szczególnymi formatami danych, takimi jak numery telefoniczne lub dane z wiodącymi (początkowymi) zerami. Rozdział 7 zawiera omówienie nowych funkcji NR.SER.OST.DN.MIES (EOMONTH) oraz NR.SER.DATY (EDATE). Rozdział 25 zawiera omówienie problemów pojawiających się przy kopiowaniu formuł wykorzystujących odwołania do tabel. Nowy rozdział 28 zawiera wyczerpujące omówienie Power Query. Nowy rozdział 29 udostępnia pełne omówienie nowych typów danych występujących w Office 365. Rozdział 32 zawiera omówienie sortowania pomiędzy kolumnami oraz porządku bazującego na wielkości pierwszej litery (mała lub wielka) w komórce. Rozdział 33 zawiera omówienie sortowania na podstawie zaznaczonej komórki. Rozdział 35 poświęcony tablicom został uaktualniony, aby zwrócić uwagę na wycofywanie powodującej obawy kombinacji klawiszy Ctrl+Shift+Enter, wcześniej wymaganej do wprowadzania funkcji i formuł tablicowych. Zupełnie nowy rozdział 36 poświęcony jest nowym funkcjom tablic dynamicznych w Office 365: UNIKATOWE (UNIQUE), SORTUJ (SORT), SORTUJ.WG (SORTBY), FILTRUJ (FILTER) oraz SEKWENCJA (SEQUENCE). Rozdział 37 używa formuł tablic dynamicznych do tworzenia zagnieżdżonych pól rozwijanych. Rozdział 38 używa tablic dynamicznych do emulowania mechanizmu tabel przestawnych (PivotTable). Rozdział 42 zawiera omówienie posługiwania się funkcjami SUMY.CZĘŚCIOWE (SUBTOTAL). Nowy rozdział 43 zawiera wyczerpujące wprowadzenia do najczęściej używanych rodzajów wykresów dostępnych w programie Excel. Rozdział 44 przedstawia bardziej zaawansowane techniki budowania wykresów. Nowy rozdział 65 zawiera omówienie zadziwiającej funkcji HISTORIA.AKCJI (STOCKHISTORY) dostępnej w Office 365. Rozdział 79 omawia nową funkcję TABELA.LOSOWO (RANDARRAY) w Office 365 oraz pokazuje, jak można jej używać do generowania losowej próbki z podanego zestawu danych. Zupełnie nowy rozdział 93 zawiera omówienie funkcjonalności Analyze Data, która umożliwia uzyskanie wglądu w dane poprzez pojedyncze kliknięcie*. Znacząco zmieniony rozdział 94 zawiera wiele nowych przykładów nagrywania makr. Nowy rozdział 95 zawiera skrótowe omówienie funkcji niestandardowych (użytkownika) oraz przedstawienie funkcji programistycznych dostępnych w Office 365: LET, LAMBDA oraz pomocniczych funkcji LAMBDA.

Co należy wiedzieć przystępując do lektury tej książki

Aby zrozumieć przykłady zamieszczone w tej książce, nie trzeba być guru programu Excel. Zasadniczo wystarczy znać dwie podstawowe rzeczy:

Jak wprowadzać formuły Trzeba wiedzieć, że formuły muszą zaczynać się od znaku równości (=). Trzeba również znać podstawowe operatory matematyczne. Na przykład trzeba wiedzieć, że gwiazdka (*) oznacza mnożenie, ukośnik (/) oznacza dzielenie, a znak karetki (^) jest symbolem podnoszenia do potęgi. Jak stosować odwołania do komórek Należy pamiętać, że w przypadku kopiowania formuły zawierającej odwołanie do komórki, takie jak $A$4 (bezwzględne odwołanie do komórki, tworzone przez dodanie znaków dolara), formuła w komórce docelowej nadal odwołuje się do komórki A4. W przypadku kopiowania formuły zawierającej odwołanie do komórki typu $A4 (mieszany adres komórki), kolumna (A) pozostaje nadal ta sama, natomiast numer wiersza (4) ulega zmianie. I wreszcie w przypadku kopiowania formuły zawierającej odwołanie do komórki typu A4 (względne odwołanie do komórki), zmianie ulega zarówno wiersz, jak i kolumna komórki, do której odwołuje się formuła.

Koncepcje te są szczegółowo omówione w rozdziale 1.

Jak korzystać z tej książki

Czytając przykłady opisane w tej książce można stosować dwa podejścia:

Można otworzyć plik szablonu odpowiadający analizowanemu przykładowi i wykonywać na bieżąco każdy jego punkt opisany w książce. Taki sposób okazuje się zadziwiająco prosty, a do tego pozwala bardzo wiele nauczyć się i zapamiętać. Jest to metoda stosowana przeze mnie podczas kursów dla pracowników korporacji. Zamiast korzystać z plików wzorcowych, można śledzić moje objaśnienia patrząc na końcową wersję każdego pliku przykładowego.

Pobieranie materiałów dołączonych do książki

Książce tej towarzyszy witryna sieci Web, która udostępnia wszystkie pliki wykorzystywane w omawianych przykładach (zarówno ostateczne wersje skoroszytów programu Excel, jak i szablony umożliwiające samodzielną pracę). Skoroszyty i szablony są umieszczone w folderach o nazwach odpowiadających poszczególnym rozdziałom. W podfolderach Practice Files znajdziemy pliki przykładów roboczych, a w podfolderach Templates pliki szablonów. Dołączone zostały również rozwiązania wszystkich zadań występujących na końcu poszczególnych rozdziałów, w podfolderach Solutions. Każdy plik z odpowiedziami ma nazwę umożliwiającą jego łatwą identyfikację. Na przykład plik zawierający rozwiązanie zadania 2 z rozdziału 10 ma nazwę S10_2.xlsx.

Aby wykonywać samemu przykłady opisane w tej książce, należy skopiować wszystkie pliki z witryny do swojego komputera. Pliki te, jak i pozostałe informacje, można pobrać ze strony o adresie:

MicrosoftPressStore.com/Excel365data7e/downloads

Po wyświetleniu tej strony w oknie przeglądarki sieci Web pojawi się instrukcja pobierania plików.

Podziękowania

Jestem niezmiernie wdzięczny Jennifer Skoog i Normanowi Tonina, którzy uwierzyli we mnie i wynajęli po raz pierwszy do poprowadzenia kursu programu Excel dla działu finansowego firmy Microsoft. W szczególności dotyczy to Jennifer, która pomogła mi zaprojektować treść i styl tych kursów, na których to została oparta niniejsza książka. Również Keith Lange z Eli Lilly, Pat Keating i Doug Hoppe z Cisco Systems oraz Dennis Fuller z armii USA pomogli mi dopracować pomysły dotyczące nauki analizy i modelowania danych przy użyciu programu Excel.

Jestem wdzięczny wielu moim słuchaczom z różnych organizacji oraz z Kelley School of Business i Uniwersytetu Houston, dla których prowadziłem wykłady. Wielu z nich nauczyło mnie całkiem nieznanych możliwości programu Excel.

Alex Blanton, pracujący wcześniej dla Microsoft Press, okazał się na początku wielkim orędownikiem tego projektu i podzielał moją wizję napisania przystępnej książki, przeznaczonej dla analityków biznesowych.

Na koniec dziękuję mojej ukochanej i utalentowanej żonie Vivian oraz moim cudownym dzieciom Jennifer i Gregory'emu, że znosili w weekendy długie godziny mojego siedzenia nad klawiaturą.

Errata do książki i pomoc

Wydawnictwo dołożyło wszelkich starań, aby zapewnić najwyższą jakość tej książce i towarzyszącym jej materiałom. Listę już odkrytych błędów wraz z poprawkami można znaleźć na stronie:

MicrosoftPressStore.com/Excel365data7e/errata

W razie odkrycia nowego błędu można go zgłosić na tej samej stronie.

O autorze

Wayne Winston jest emerytowanym profesorem nauk decyzyjnych i operacyjnych w ­Kelley School of Business na Uniwersytecie Indiana, a obecnie wykładowcą w ­Bauer ­College of Business na Uniwersytecie Houston. Laureat ponad 45 nagród, nauczał technik modelowania z wykorzystaniem programu Excel tysiące analityków z takich organizacji, jak Microsoft, Eli Lilly, Cisco, eBay, armia USA, General Motors, Ford, Pfizer i Verizon. Jest również konsultantem drużyn ligi NBA Dallas Mavericks i New York Knicks. Jest autorem kilkunastu książek na temat Excela, nauk zarządzania i badań operacyjnych, w tym książki o związkach matematyki i sportu, zatytułowaną Mathletics. Jest też dwukrotnym zwycięzcą konkursu Jeopardy!

Rozdział 1

Podstawowe modelowanie arkusza

Rozdział zawiera odpowiedzi na następujące pytania:

Jak mogę szybko określić tygodniowe zarobki każdego z moich pracowników? Jak mogę sprawnie określić, jaką kwotę winna jest piekarnia każdemu dostawcy? Jak mogę przewidzieć liczbę klientów w nowym klubie fitness w ciągu 10 lat? Jak działają reguły określające kolejność operacji w formułach programu Excel? W jaki sposób mogę określić w mojej kawiarni, jak zmiany cen kosztów jednostkowych wpływają na zysk?

Mój przyjaciel, Dennis Fuller, powiedział kiedyś: "dla analityka biznesowego kanwą jest arkusz kalkulacyjny". Nikt z nas nie potrafi namalować arcydzieła, takiego jak Gwiaździsta noc van Gogha. Jeśli jednak opanujemy Excel, będziemy mogli od pustego arkusza rozpocząć tworzenie własnego arcydzieła, które pozwoli nam modelować wirtualnie każdą sytuację.

Dla wielu osób bariera, która nie pozwala sprawnie korzystać z programu Excel, to zrozumienie sposobu działania formuł w programie Excel. W tym rozdziale przeanalizujemy kilka prostych modeli arkusza, które powinny przygotować nas do opanowania bardziej złożonych zagadnień w programie Excel.

Odpowiedzi na pytania z początku rozdziału

Jak mogę szybko określić tygodniowe zarobki każdego z moich pracowników?

W pliku Wagestemp.xlsx (w folderze Templates), pokazanym na rysunku 1-1, dla kilku pracowników mamy podane godziny przepracowane w tym tygodniu oraz stawkę godzinową. Chcemy określić kwotę wypłaty dla każdego pracownika, a także łączną liczbę przepracowanych godzin i średnią wypłatę.

Rysunek 1-1 Obliczanie wypłat tygodniowych

W celu obliczenia tygodniówki pracownika Luka trzeba pomnożyć wartość w komórce C4 przez wartość w komórce D4. Tak więc, w komórce E4 wpisujemy formułę =C4*D4.

Moglibyśmy teraz przejść do komórki E5 i wprowadzić formułę =C5*D5, która obliczy płacę dla pracownika Terry, jednak polecenie kopiowania w programie Excel pozwala nam szybko obliczyć tygodniówki dla wszystkich pracowników, nawet jeśli jest ich milion (w programie Excel 2007 i nowszych możemy mieć 1 048 576 wierszy!). W tym celu, po prostu przechodzimy do komórki E4 i za pomocą skrótu klawiszowego Ctrl+C kopiujemy formułę. Następnie zaznaczamy zakres E5:E11 i za pomocą połączenia klawiszy Ctrl+V lub klawisza Enter wprowadzamy formułę w zakresie E5:E11.

Uwaga Warto pamiętać, że będziemy często używać do pokazywania formuł w arkuszach funkcji FORMUŁA.TEKST (funkcja ta pojawiła się w programie Excel 2013)*. Na przykład, wprowadzenie w komórce F4 formuły =FORMUŁA.TEKST(E4) powoduje wyświetlenie formuły zapisanej w komórce E4.

Możemy również skopiować formułę z komórki E4 do zakresu E5:E11 poprzez wskazanie kursorem małego kwadratu w prawym dolnym narożniku komórki E4 i po zmianie wyglądu kursora do postaci cienkiego krzyżyka naciskamy lewy przycisk myszy i przeciągamy formułę do zakresu E5:E11. W każdej komórce, do której została skopiowana formuła, Excel pomnoży w kolumnie E dwie wartości z lewej strony danej komórki.

W komórce C12 możemy obliczyć (jak to robiłem w pliku Wagesfinal.xlsx) łączną liczbę godzin przepracowanych w tym tygodniu za pomocą formuły =SUMA(C4:C11), natomiast w komórce E14 możemy obliczyć średnią wypłatę dla pracowników za pomocą formuły =ŚREDNIA(E4:E11).

Jak mogę sprawnie określić, jaką kwotę winna jest piekarnia każdemu dostawcy?

W pliku Bakery1temp.xlsx mamy podaną cenę za funt, jaką piekarnia płaci każdemu z sześciu dostawców za cukier, masło i mąkę. Określimy kwotę płaconą każdemu dostawcy za cukier, mąkę i masło. Ponadto określimy łączną kwotę płatności.

Zgodnie z rysunkiem 1-2, w komórce E23 obliczamy płatność dla dostawcy Supplier 1 za cukier, mnożąc cenę za funt cukru przez ilość cukru zakupionego od Supplier 1). W tym celu posługujemy się formułą =E5*E14.

Aby obliczyć płatność dla każdego dostawcy dla każdego produktu, można posłużyć się jedną z poniższych metod:

Zaznacz komórkę E23, a po naciśnięciu klawiszy Ctrl+C zaznacz zakres E23:G28 i naciśnij klawisze Ctrl+V. Zaznacz komórkę E23, a po naciśnięciu klawiszy Ctrl+C zaznacze zakres E23:G28 i naciśnij klawisz Enter. Zaznacz komórkę E23 i po zmianie wyglądu kursora do postaci krzyżyka przeciągnij formułę do zakresu F23:G23. Następnie przeciągnij zakres E23:G23 do E28:G28.

Rysunek 1-2 Obliczanie kosztów piekarni: różne ceny dla każdego dostawcy

W celu określenia kwoty łącznej płatności dla każdego dostawcy w komórce H23 wprowadzamy formułę =SUMA(E23:G23) i kopiujemy tę formułę do zakresu H24:H28. Wprowadzając formułę =SUMA(E23:E28) w komórce E29 i kopiując tę formułę do zakresu F29:H29, obliczamy łączny koszt dla każdego produktu.

Szybszy sposób tworzenia naszych podsumowań polega na zaznaczeniu zakresu H23:H28 i (po naciśnięciu i przytrzymaniu klawisza Ctrl) zaznaczeniu zakresu E30:G30. Następnie klikamy po prostu przycisk Autosumowanie (AutoSum), który znajduje się na Wstążce w grupie Edytowanie na karcie Narzędzia główne (Home), co ilustruje rysunek 1-3.

Rysunek 1-3 Użycie przycisku Autosumowanie do obliczenia sumy zestawu liczb

Polecenie Autosumowanie domyśla się (nie zawsze poprawnie, trzeba więc być uważnym!), jaki zakres komórek chcemy posumować, by wypełnić zaznaczone komórki. W tym przypadku funkcja Autosumowanie może nam zaoszczędzić 5 sekund!

W pliku Bakery2temp.xlsx (rysunek 1-4), założymy teraz, że każdy dostawca stosuje tę samą cenę dla poszczególnych produktów. Obliczymy ponownie łączną płatność dla każdego dostawcy za każdy produkt oraz łączną kwotę, jaką piekarnia płaci każdemu dostawcy.

Rysunek 1-4 Obliczanie kosztów piekarni: wszyscy dostawcy mają te same ceny

Postępując tak, jak poprzednio, nieuważny czytelnik może w komórce E23 wprowadzić formułę E12*E14 i skopiować tę formułę do zakresu E23:G28. Niestety w wierszach 24 do 28 zmienione będzie odwołanie do wiersza 12 i 14. Podczas kopiowania formuły chcemy, by zmieniało się odwołanie do wiersza 14, ale nie chcemy by zmieniało się odwołanie do wiersza 12, ponieważ w każdej sytuacji pobieramy cenę jednostkową produktu z wiersza 12. Aby zrealizować to zadanie, umieszczamy znak $ przed 12. Metoda ta nazywana jest adresowaniem bezwzględnym lub blokowaniem wiersza. Kiedy w formule numer wiersza poprzedzony jest znakiem dolara, a formuła jest kopiowana, numer wiersza pozostaje niezmienny. I dlatego w komórce E23 wprowadzimy formułę =E$12*E14.

Prosta metoda wstawiania znaku dolara w formule polega na użyciu klawisza F4. Jeśli zaznaczymy część formuły i będziemy powtarzać naciskanie klawisza F4, program Excel będzie cyklicznie proponował różne sposoby wstawienia znaku dolara (dodanie znaku dolara do wiersza i kolumny, tylko do wiersza, tylko do kolumny lub znak dolara nie będzie dodawany).

Jak mogę przewidzieć liczbę klientów w nowym klubie fitness w ciągu 10 lat?

Nasza odpowiedź znajduje się w pliku Chapter1customer.xlsx (rysunek 1-5). Trzeba tutaj od początku zbudować model. Modele arkuszy mają dane wejściowe lub założenia używane do uzyskania potrzebnych obliczeń.

Rysunek 1-5 Przewidywanie liczby klientów poprzez definiowanie modelu z danymi wejściowymi i wyjściowymi

W przypadku podstawowego modelu szacowania liczby klientów potrzebne będą trzy dane wejściowe:

Liczba klientów na początku pierwszego roku (Year 1). Wskaźnik odpływu klientów (churn rate): część początkowej liczby klientów (bez nowych klientów), którzy odchodzą każdego roku. Liczba nowych klientów pozyskanych każdego roku.

Te wartości wejściowe wprowadzimy w komórkach C2:C4. Warto stosować się do zasady, która mówi, że dane wejściowe arkusza powinny być oddzielane od danych wyjściowych arkusza i nigdy nie były wpisywane na stałe w formułach programu Excel. Oddzielenie danych wejściowych od wyjściowych arkusza ułatwia określenie, jak zmiana danych wejściowych wpływa na dane wyjściowe arkusza.

W wierszach 8-17 będziemy obliczać liczbę klientów pozostających na koniec każdego roku, dodając nowych klientów do początkowych i odejmując tych, którzy odeszli. Jak wspomniałem, komórki C2:C4 zawierają dane wejściowe arkusza.

W naszym modelu szacowania liczby klientów kluczowe są następujące zależności:

(końcowa liczba klientów dla roku t) = (początkowa liczba klientów dla roku t) + (nowi klienci dla roku t) - (liczba klientów, którzy odchodzą w ciągu roku). (początkowa liczba klientów dla roku t+1) = (końcowa liczba klientów dla roku t) początkowa liczba klientów dla roku 1 = wartość w komórce C2.

Inną kluczową sprawą do rozwiązania tego problemu jest wiedza o tym, jakie informacje trzeba śledzić w trakcie każdego roku:

początkowa liczba klientów liczba nowych klientów liczba klientów, którzy odchodzą końcowa liczba klientów

W komórce C8, obliczamy początkową liczbę klientów dla roku 1 za pomocą formuły =C2. Następnie, w kolumnie D, ponownie wprowadzamy liczbę nowych klientów dla każdego roku, kopiując z D8 do D9:D17 formułę =$C$3 lub =C$3.

Trzeba pamiętać, aby liczbę 3 poprzedzić znakiem dolara; w przeciwnym razie, podczas kopiowania formuły w D8, liczba 3 zostanie zmieniona, dając nieprawidłowy wynik. Poprzedzanie litery C znakiem dolara jest opcjonalne, ponieważ nie kopiujemy formuły pomiędzy kolumnami.

Liczba klientów odchodzących każdego roku to liczba początkowych klientów pomnożona przez współczynnik odpływu. Dlatego w kolumnie E obliczamy liczbę klientów, którzy odeszli każdego roku poprzez skopiowanie z komórki E8 do E9:E18 formuły =$C$4*C8 lub =C$4*C8. Zwróćmy uwagę, że dla liczby 8 nie jest używany znak dolara, ponieważ chcemy, by podczas kopiowania 8 zmieniło się na 9, 10 itd.

Końcowa wartość liczby klientów dla każdego roku jest uzyskiwana poprzez dodanie początkowej liczby klientów do liczby nowych klientów, a następnie odjęcie od tej wartości liczby odchodzących klientów. Skopiowanie z komórki F8 do F9:F18 formuły =C8+D8-F8 powoduje obliczenie końcowej liczby klientów dla każdego roku.

Dla lat 2-10 początkowa liczba klientów będzie równa końcowej liczbie klientów poprzedniego roku, tak więc z komórki C9 do C10:C17 skopiujemy formułę =F8. Widzimy, że (nie należy przejmować się wartością ułamkową) po 10 latach nasz klub fitness będzie miał około 127 klientów.

Wnikliwy czytelnik może uważać, że w rzeczywistości nie znamy współczynnika odpływu i liczby nowych klientów dla każdego roku. I to jest prawda. Powinniśmy wykonać dokładne analizy w celu określenia, jak zmiany liczby nowych klientów każdego roku i roczny współczynnik odpływu zmieniają końcową liczbę klientów dla roku 10-tego. Z rozdziału 17 dowiemy się, jak wykorzystywać tabele danych do przeprowadzania takich precyzyjnych analiz.

Jak działają reguły określające kolejność operacji w formułach programu Excel, czyli reguły PEMDAS (lub "Please excuse my dear Aunt Sally")?

Złożone formuły programu Excel często wiążą się z różnymi złożonymi operacjami matematycznymi, jak potęgowanie, mnożenie i dzielenie. Podczas oceniania formuł program Excel stosuje standardową arytmetyczną kolejność operacji NPMDDO (angielski akronim to PEMDAS, stąd nazwa pliku przykłowego):

Najpierw wykonywane są operacje wewnątrz Nawiasów (Parantheses). Następnie wykonywane są Potęgowania (Exponentiation), od lewej do prawej. Kolejne operacje to Mnożenia i Dzielenia, wykonywane od lewej do prawej (Multiplication i Division, odpowiednio). Na koniec wykonywane są operacje Dodawania (Addition) i Odejmowania (Substraction).

Dla przykładu, program Excel obliczy formułę =3+6*(5+4)/3-7 w następującej kolejności:

3 + 6*9/3 - 7 (usunięcie nawiasów) 3 + 54/3 - 7 (mnożenie) 3 + 18 - 7 (dzielenie) 21 - 7 (dodawanie) 14 (odejmowanie)

By przytoczyć inny przykład, założymy, że trzeba obliczyć pierwiastek kwadratowy rocznego wzrostu sprzedaży naszych produktów (patrz plik PEMDAStemp.xlsx i rysunek 1-6).

Rysunek 1-6 Przykład demonstrujący działanie reguł PEMDAS

W komórce F4 wprowadzona została prawidłowa formuła =((E4-D4)/D4)^0,5 i skopiowana do komórek F5:F6. Formuła ta powoduje obliczenie wartości procentowej (wyrażonej jako ułamek) wzrostu sprzedaży (0,5), a następnie obliczany jest pierwiastek kwadratowy. Końcowy wynik to 0,707 (pierwiastek kwadratowy z 0,5) jest prawidłowy. Znak ^ (na klawiaturze umieszczony nad cyfrą 6) jest używany jako oznaczenie działania podnoszenia liczb do potęgi.

W komórce G4 wprowadzona została nieprawidłowa formuła =(E4-D4)/D4^0,5. Formuła ta najpierw oblicza E4-D4 = 50, a następnie oblicza pierwiastek kwadratowy z D4 (10). Wynik końcowy (nieprawidłowy) to 50/10 = 5.

W jaki sposób mogę określić w mojej kawiarni, jak zmiany cen i kosztów jednostkowych wpływają na zysk?

Klucz do zrozumienia, jak zmiany cen wpływają na zysk, to prawidłowe oszacowanie krzywej popytu. Krzywa popytu pokazuje, jak zmiany ceny zmieniają popyt na produkt. Załóżmy, że dzienny popyt na filiżankę kawy przedstawia wyrażenie 100-15*Cena w dolarach (w rozdziałach 84 do 86 przedstawiono dokładniejsze omówienie szacowania krzywej popytu). W pliku Coffee.xlsx (rysunek 1-7) zobaczymy, jak dzienny zysk zmienia się w zależności od zmian kosztów jednostkowych i ceny filiżanki kawy.

Rysunek 1-7 Zależność popytu od ceny i kosztów jednostkowych

Zakładamy, że koszt jednostkowy filiżanki kawy zmienia się w zakresie od 0,50$ do 2,00$, a cena filiżanki kawy w zakresie od 2,00$ do 5,00$. Aby określić zysk dla każdej kombinacji pary cena/koszt jednostkowy, musimy w komórce F10 wprowadzić formułę =($F$5-$F$4*F$9)*(F$9-$E10) i skopiować ją z komórki F10 do F10:L13. Odwołania do komórek F5 i F4 są bezwzględne, ponieważ nie chcemy, by podczas kopiowania formuł zmieniały się wiersze lub kolumny. Odwołanie do ceny (komórka F9) wymaga użycia znaku dolara (blokowanie wiersza), ponieważ cenę zawsze będziemy pobierać z wiersza 9. Odwołanie do kosztu jednostkowego (E10) wymaga stosowanie w kolumnie znaku dolara, ponieważ wartość ta zawsze będzie pobierana z kolumny E.

Przykładowo, okazuje się, że jeśli koszt jednostkowy wynosi 1,50$, a cena 4,00$, to nasz zysk wynosi 100$:

(100-4*15)*(4-1.5) = 100

Dla każdego kosztu jednostkowego wyróżniona została cena, która maksymalizuje zysk. W rozdziale 24 omówiono formatowanie warunkowe do różnego rodzaju sposobów wyróżniania.

Problemy

Mojej klasie przydzieliłem pięć prac domowych, każda warta 25 punktów i trzy egzaminy, każdy wart 100 punktów. Aktualnie obliczam końcową ocenę uczniów poprzez przypisanie wagi 75 procent do egzaminu i wagi 25 procent do pracy domowej. Skonstruuj arkusz, który oblicza ocenę końcową ucznia i który pozwala zmieniać wagę przypisaną do egzaminów. Wskaźnik masy ciała (BMI, body mass index) jest obliczany w następujący sposób: BMI=703*Ciężar/Wzrost2. Zbuduj arkusz do obliczania wskaźnika BMI. Ciąg Fibonacciego jest definiowany przez wyrażenie: F0 = 0, F1 = 1, a dla następnie FN+1 = FN + FN-1 dla N większych niż 1. Utwórz arkusz do obliczania ciągu Fibonacciego i pokaż, że dla dużych N iloraz sąsiadujących liczb ciągu Fibonacciego zbliża się do odwrotności złotego podziału (1,61). Zgodnie ze znanym efektem motyla wiemy, że trzepot skrzydeł motyla na ­Tahiti może po pewnym czasie spowodować huragan w Teksasie. Załóżmy, że pogoda w czasie t zawsze znajduje się w przedziale pomiędzy 0 a 1 i że opisuje je wyrażenie xt+1 = 4 * xt * (1 - xt). Wyznacz x1, x2, ... x50 dla xt = 0,3 i xt=0,3000001. Jak obliczenia te mogą ilustrować efekt motyla? W jeziorze jest 12 230 ryb. Każdego roku, w odniesieniu do jednej ryby, współczynnik narodzin wynosi 1,2, a współczynnik śmierci 0,7. Wykaż, że jeśli rocznie odławianych jest 6115 ryb, liczba ryb w jeziorze będzie stała. Współczynnik Giniego jest najczęściej używany jako wskaźnik nierówności ekonomicznej. Jeśli dochód n osób zostanie wymieniony w porządku rosnącym (x1= najmniejszy dochód, xn = największy dochód), to wskaźnik Giniego jest zdefiniowany przez poniższe wyrażenie:

Utwórz arkusz, który określa wskaźnik Giniego dla grupy pięciu osób.