Jak korzystać z narzędzi analizy typu „co, jeśli” w programie Microsoft Excel

Podczas analizowania danych w programie Microsoft Excel możesz zechcieć wykonać kilka porównań, na przykład „A co, jeśli wybiorę opcję A zamiast opcji B?” Korzystając z wbudowanych narzędzi do analizy typu „co by było, gdyby” w programie Excel, można łatwiej porównywać liczby i kwoty — na przykład w celu oszacowania wynagrodzenia za pracę, opcji pożyczki lub scenariuszy przychodów i wydatków.

Narzędzia analizy typu „co, jeśli” w programie Excel obejmują Menedżera scenariuszy, Wyszukiwanie celu i Tabela danych. Aby najlepiej wyjaśnić przeznaczenie tych narzędzi, spójrzmy na przykłady każdego z nich.

Menedżer scenariuszy

Korzystając z Menedżera scenariuszy, wprowadź wartości, które możesz zmienić, aby zobaczyć różne wyniki. Jako bonus, utwórz Raport Podsumowania Scenariusza, aby porównać kwoty lub liczby obok siebie.

Załóżmy na przykład, że planujesz wydarzenie i wybierasz między kilkoma tematami, które mają różne koszty. Ustaw ceny każdego motywu, aby zobaczyć, ile będą kosztować, aby je porównać.

Twórz różne scenariusze dla różnych sytuacji, aby pomóc w podejmowaniu decyzji.

Jak korzystać z Menedżera scenariuszy

Jeśli chcesz porównać różne sytuacje, jak w powyższym przykładzie, wykonaj następujące kroki, aby użyć Menedżera scenariuszy w programie Excel.

• Wprowadź dane dla pierwszego scenariusza w swoim arkuszu. Korzystając z wcześniejszego przykładu, porównujemy koszty motywu naszego wydarzenia i wprowadzamy wydatki na motyw plażowy w komórkach od A2 do A6 oraz ich koszty w komórkach od B2 do B6. Dodajemy ceny razem w komórce B7, aby zobaczyć całkowity koszt.

• Aby dodać te szczegóły do ​​Menedżera scenariuszy, przejdź do zakładki „Dane” i sekcji „Prognoza” na wstążce. Otwórz menu rozwijane „Analiza co-jeśli” i wybierz „Menedżer scenariuszy”.

• Kliknij „Dodaj”.

• Nadaj swojemu scenariuszowi nazwę (my używamy motywu „Plaża”) i wprowadź komórki, które chcesz dostosować, w polu „Zmiana komórek”. Możesz też przeciągnąć kursor przez komórki w arkuszu, aby wypełnić to pole. Opcjonalnie wprowadź komentarz inny niż domyślny i kliknij „OK”.

• Wartości w polu „Zmienianie komórek” powinny odpowiadać wartościom w arkuszu, ale możesz je dostosować tutaj. Kliknij „OK”, aby kontynuować.

• Teraz, po dodaniu pierwszego scenariusza, zobaczysz go na liście w Menedżerze scenariuszy. Wybierz „Dodaj”, aby skonfigurować następny scenariusz.

• Wprowadź szczegóły drugiego scenariusza, tak jak w przypadku pierwszego. Dołącz nazwę, zmieniające się komórki i opcjonalny komentarz, a następnie kliknij „OK”. W naszym przykładzie wprowadzamy „Motyw Vegas” i ten sam zakres komórek, od B2 do B6, aby łatwo zobaczyć porównanie w miejscu.

• Wprowadź wartości dla drugiego scenariusza w oknie Wartości scenariusza. Jeśli używasz tych samych komórek co pierwsza, zobaczysz te wypełnione. Wpisz te, których chcesz użyć, i kliknij „OK”.

• Wybierz scenariusz, który chcesz obejrzeć z listy w oknie Menedżera scenariuszy i kliknij „Pokaż”.

• Wartości w arkuszu zostaną zaktualizowane, aby wyświetlić wybrany scenariusz.

• Kontynuuj dodawanie i wyświetlanie dodatkowych scenariuszy, aby wyświetlić zaktualizowane wartości w arkuszu. Po znalezieniu tego, który chcesz zachować w swoim arkuszu, wybierz „Zamknij”, aby wyjść z Menedżera scenariuszy.

Wyświetl podsumowanie scenariusza

Wyświetl Podsumowanie scenariuszy, aby zobaczyć wszystkie scenariusze jednocześnie i przeprowadzić bezpośrednie porównanie.

• Wróć do „Dane -> Analiza co-jeśli -> Menedżer scenariuszy”, a następnie kliknij „Podsumowanie”.

• Wybierz typ raportu, który chcesz wyświetlić: „Podsumowanie scenariusza” lub „ Raport w formie tabeli przestawnej scenariusza”. Opcjonalnie, jeśli chcesz wyświetlić swój wynik, wprowadź komórkę, która go zawiera, i kliknij „OK”.

W naszym przykładzie wybieramy „Podsumowanie scenariusza”, co powoduje umieszczenie raportu w nowej karcie arkusza. Zauważysz również, że raport może opcjonalnie zawierać grupowanie komórek w celu ukrycia niektórych części raportu.

Pamiętaj, że jeśli dostosujesz szczegóły w Menedżerze scenariuszy, raport nie zostanie zaktualizowany automatycznie, więc musisz wygenerować nowy raport.

Poszukiwanie celu

Narzędzie Goal Seek działa nieco inaczej niż Menedżer scenariuszy. Dzięki temu narzędziu masz znany wynik i wprowadzasz różne zmienne, aby zobaczyć, jak możesz dojść do tego wyniku.

Na przykład, być może sprzedajesz produkty i masz roczny cel zysku. Chcesz wiedzieć, ile jednostek musisz sprzedać lub po jakiej cenie, aby osiągnąć swój cel. Goal Seek to idealne narzędzie do znajdowania odpowiedzi.

W funkcji Goal Seek można użyć tylko jednej zmiennej lub wartości wejściowej. Użyj tego dla tych scenariuszy, w których masz pozostałe wartości z góry.

Jak korzystać z funkcji szukania celu

W przykładzie narzędzia Goal Seek mamy 1500 produktów do sprzedania i chcemy zarobić 52 000 USD. Używamy Goal Seek, aby określić, po jakiej cenie powinniśmy sprzedawać nasz produkt, aby osiągnąć ten cel.

• Zacznij od wprowadzenia wartości i formuł w arkuszu zgodnie ze swoim scenariuszem. Korzystając z naszego przykładu, w komórce B2 wpisujemy aktualną ilość, w komórce B3 szacowaną cenę, aw komórce B4 formułę zysku, czyli =B2*B3.

• Przejdź do zakładki „Dane”, otwórz menu rozwijane „Analiza co-jeśli” i wybierz „Wyszukiwanie celu”.

• Wprowadź następujące wartości, a następnie kliknij „OK”:
  • Ustaw komórkę : odwołanie do komórki (zawierające formułę) dla wartości, którą chcesz zmienić, aby osiągnąć pożądany wynik. W naszym przykładzie jest to komórka B4.
  • Do wartości : wartość pożądanego wyniku. Dla nas to 52000.
  • Zmieniając komórkę : odwołanie do komórki, które chcesz zmienić, aby osiągnąć wynik. Używamy komórki B3, ponieważ chcemy zmienić cenę.

• Kliknij „OK”, aby zobaczyć aktualizację pola „Stan wyszukiwania celu”, aby wyświetlić rozwiązanie, a arkusz zmieni się, aby zawierał dostosowane wartości. W naszym przykładzie musimy sprzedać nasz produkt za 35 USD, aby osiągnąć nasz cel 52 000 USD. Wybierz „OK”, aby zachować nowe wartości w arkuszu.

Czy wiesz , że w programie Microsoft Excel można robić wiele rzeczy, w tym wstawiać wykresy przebiegu w czasie i miniwykresy .

Tabela danych

Użyj tabeli danych w programie Excel, aby wyświetlić zakres możliwych sytuacji liczbowych.

Idealnym przykładem może być przeglądanie opcji pożyczek. Wprowadzając różne stopy procentowe, możesz zobaczyć, jaka byłaby Twoja miesięczna płatność przy każdej z nich. Pomaga to określić, jaką stawkę kupić lub omówić z pożyczkodawcą.

W tabeli danych możesz użyć maksymalnie dwóch zmiennych. Jeśli potrzebujesz więcej, skorzystaj z Menedżera scenariuszy.

Jak korzystać z tabeli danych

Wykonaj poniższe czynności, aby użyć tabeli danych, trzeciego narzędzia do analizy typu „co-jeśli”. Zanotuj konfigurację danych.

Na przykład używamy tabeli danych, aby zobaczyć, ile wynosiłyby nasze spłaty kredytu przy różnych stopach procentowych, korzystając z następujących danych:

• Stopa procentowa, liczba płatności i kwota pożyczki w komórkach od B3 do B5.
• Kolumna Stopa ze stopami procentowymi do zbadania w komórkach od C3 do C5.
• Kolumna Płatność z formułą bieżącej płatności w komórce D2.
• Komórki wynikowe pod formułą w kolumnie Płatność, wprowadzone automatycznie za pomocą narzędzia Tabela danych. To pokazuje nam kwoty płatności według stopy procentowej.

Podczas wprowadzania danych i formuły w arkuszu pamiętaj o następujących kwestiach:

• Użyj układu zorientowanego na wiersze lub kolumny. To określi położenie twojej formuły.
• Aby uzyskać układ zorientowany na wiersze, umieść formułę w komórce o jedną kolumnę na lewo od wartości początkowej i jedną komórkę poniżej wiersza zawierającego wartości.
• Aby uzyskać układ zorientowany na kolumny, umieść formułę w komórce jeden wiersz powyżej i jedną komórkę po prawej stronie kolumny zawierającej wartości.

W naszym przykładzie używamy jednej zmiennej (stopy procentowej) w układzie zorientowanym na kolumny. Zwróć uwagę na umieszczenie naszej formuły w komórce D2 (wiersz powyżej i jedna komórka na prawo od naszych wartości).

• Wprowadź własne dane i zaznacz komórki zawierające formułę, wartości i komórki wynikowe. W naszym przykładzie wybieramy komórki od C2 do D5.

• Przejdź do zakładki „Dane”, otwórz menu rozwijane „Analiza co-jeśli” i wybierz „Tabela danych”.

• Wprowadź komórkę zawierającą zmieniającą się zmienną dla twoich danych w polu Tabela danych. W przypadku układu zorientowanego na wiersze użyj „Wierszowej komórki wejściowej”, a w przypadku układu zorientowanego na kolumny użyj „Kolumnowej komórki wejściowej”. W naszym przykładzie używamy tego drugiego i wprowadzamy „B3”, czyli komórkę zawierającą stopę procentową.

• Po kliknięciu „OK” w polu Tabela danych powinieneś zobaczyć, jak komórki wyników wypełniają się oczekiwanymi danymi. Nasz przykład obejmuje kwotę naszej płatności dla każdej innej stopy procentowej.

Pamiętaj, że możesz użyć dwóch zmiennych w tabeli danych zamiast jednej, wypróbować układ zorientowany na wiersze lub wyświetlić dalsze szczegóły i ograniczenia tego narzędzia do analizy warunkowej na stronie pomocy technicznej firmy Microsoft dla tej funkcji .

Często Zadawane Pytania

Jak edytować istniejący scenariusz w programie Excel?

Możesz zmienić nazwę i wartości dla scenariusza za pomocą Menedżera scenariuszy. Otwórz narzędzie, wybierając „Dane -> Analiza co-jeśli -> Menedżer scenariuszy”. Wybierz scenariusz z listy i kliknij „Edytuj” po prawej stronie. Wprowadź zmiany i wybierz „OK”, aby je zapisać.

Jeśli początkowo utworzyłeś raport Podsumowanie scenariusza, musisz ponownie wygenerować raport, aby zobaczyć zaktualizowane szczegóły.

Czy mogę zatrzymać program Excel przed ponownym obliczeniem tabeli danych?

Jeśli skoroszyt zawiera tabelę danych, program Excel automatycznie przelicza tę tabelę danych, nawet jeśli nie ma żadnych zmian. Możesz jednak wyłączyć tę opcję, jeśli chcesz.

Przejdź do zakładki „Formuła”, otwórz menu rozwijane „Opcje obliczeń” w grupie Obliczenia i wybierz „Automatyczne z wyjątkiem tabel danych”.

Aby ręcznie przeliczyć Tabelę danych, wybierz formułę(y) i naciśnij F9.

Jakie inne narzędzia analityczne oferuje Excel?

Excel udostępnia wiele różnych typów narzędzi do analizy danych, w zależności od tego, czego potrzebujesz. Aby wymienić tylko kilka, możesz użyć formatowania warunkowego do wyróżnienia określonych danych, szybkiej analizy do formatowania, wykresów i tabel oraz dodatku Power Query do szczegółowej analizy danych.

Możesz także korzystać z podstawowych funkcji programu Excel, takich jak filtry do zawężania danych, fragmentatory do filtrowania tabel i wykresów oraz narzędzie Analizuj dane do uzyskiwania odpowiedzi na pytania dotyczące danych.

Aby uzyskać dodatkową pomoc dotyczącą tych i innych funkcji, przejdź do karty „Pomoc” w programie Excel w systemie Windows lub użyj opcji menu „Powiedz mi” w programie Excel na komputerze Mac.

Źródło obrazu: Pixabay . Wszystkie zrzuty ekranu autorstwa Sandy Writtenhouse.