Analiza warunkowa programu Excel oferuje tabelę danych. To jest złe imię. Powinien się nazywać Analiza wrażliwości. To jest super. Przeczytaj o tym tutaj.
Szukanie celu pozwala znaleźć zestaw danych wejściowych, które prowadzą do określonego wyniku. Czasami chcesz zobaczyć wiele różnych wyników z różnych kombinacji danych wejściowych. Pod warunkiem, że masz tylko dwie komórki wejściowe do zmiany, tabela danych zapewnia szybki sposób porównywania alternatyw.
Korzystając z przykładu spłaty pożyczki, załóżmy, że chcesz obliczyć cenę dla różnych sald kapitału i dla różnych warunków.
Upewnij się, że formuła, którą chcesz modelować, znajduje się w lewym górnym rogu zakresu. Umieść różne wartości dla jednej zmiennej w lewej kolumnie, a różne wartości dla innej zmiennej u góry.
Na karcie Dane wybierz opcję Analiza warunkowa, tabela danych.
Masz wartości w górnym wierszu tabeli wejściowej. Chcesz, aby program Excel podłączył te wartości do określonej komórki wejściowej. Określ tę komórkę wejściową jako komórkę wejściową wiersza.
Masz wartości w lewej kolumnie. Chcesz, żeby były podłączone do innej komórki wejściowej. Określ tę komórkę jako komórkę wejściową kolumny.
Po kliknięciu OK program Excel powtórzy formułę w lewej górnej kolumnie dla wszystkich kombinacji górnego wiersza i lewej kolumny. Na poniższym obrazku widać 60 różnych spłat pożyczek na podstawie różnych wyników.
Zwróć uwagę, że sformatowałem wyniki tabeli tak, aby nie zawierały miejsc dziesiętnych i użyłem Home, Formatowanie warunkowe, Skala kolorów, aby dodać cieniowanie czerwony / żółty / zielony.
Oto świetna część: ten stół jest „na żywo”. Jeśli zmienisz komórki wejściowe wzdłuż lewej kolumny lub górnego wiersza, wartości w tabeli zostaną ponownie obliczone. Poniżej wartości po lewej stronie dotyczą zakresu od 23 000 do 24 000 USD.
Podziękowania dla Owena W. Greena za zasugerowanie stolików.
Obejrzyj wideo
- Trzy narzędzia typu „co, jeśli” w programie Excel
- Wczoraj - szukanie goli
- Dzisiaj - tabela danych
- Doskonały do problemów z dwiema zmiennymi
- Ciekawostka: funkcja tablicy TABLE nie może być wprowadzona ręcznie - nie zadziała
- Użyj skali kolorów, aby pokolorować odpowiedzi
- Co jeśli masz 3 zmienne do zmiany? Scenariusze? Nie! Skopiuj arkusz
- Tabele są wolne w obliczeniach: tryb obliczania dla wszystkich oprócz tabel
- Podziękowania dla Owena W. Greena za zasugerowanie tej wskazówki
Transkrypcja wideo
Naucz się programu Excel z podcastu, odcinek 2034 - Co jeśli z tabelą danych!
Podcastuję całą książkę, kliknij „i” w prawym górnym rogu, aby przejść do listy odtwarzania!
Dzisiaj porozmawiamy o drugim narzędziu w ramach analizy co-jeśli, wczoraj rozmawialiśmy o szukaniu wyniku, dzisiaj zajmiemy się tabelą danych. Mamy tutaj ładny mały model, to jest mały model, 3 komórki wejściowe, jedna formuła. Ale ten model mógłby składać się z setek komórek wejściowych, tysięcy wierszy, o ile sprowadza się do jednej ostatecznej odpowiedzi, a my chcemy modelować tę odpowiedź dla kilku różnych wartości 2-3 (?) Komórek wejściowych. Na przykład, może jesteśmy zainteresowani spojrzeniem na różne samochody, więc gdziekolwiek od 20000 wzwyż, więc wstawię tam 20 i 21000, chwytam za uchwyt napełniania i przeciągam, zmniejszam do 28000. Na górze: patrząc na różne warunki, więc pożyczka na 36 miesięcy, pożyczka na 42 miesiące, pożyczka na 48 miesięcy, 54, 60, 66, a nawet 72.
W porządku, ten następny krok jest całkowicie opcjonalny, ale naprawdę pomaga mi to przemyśleć, zawsze zmieniam kolory wartości u góry i wartości po lewej stronie. I naprawdę ważną rzeczą jest to, że ta narożna komórka, ta niezwykle ważna narożna komórka, musi być odpowiedzią, którą próbujemy modelować, w porządku. Musisz więc zacząć wybierać od tej narożnej komórki z odpowiedzią, a następnie zaznaczyć wszystkie wiersze i wszystkie kolumny. Przechodzimy więc do danych, analizy co-jeśli i tabeli danych i prosi o dwie rzeczy, a oto jak o tym myślisz. Mówi się, że w górnym wierszu tabeli znajduje się cała masa różnych elementów, chcę wziąć te elementy pojedynczo i podłączyć je do modelu, gdzie powinniśmy wprowadzić? Więc te elementy, to są terminy, powinny trafić do komórki B2. I wtedy,jest cała masa pozycji wzdłuż lewej kolumny, chcemy je wziąć pojedynczo i podłączyć do B1, tak, w porządku i klikamy OK, BAM, uruchamia ten model w kółko .
Teraz tylko trochę porządkowania, zawsze wchodzę i robię Home i prawdopodobnie 0 miejsc po przecinku, w ten sposób. I może trochę formatowania warunkowego, skali kolorów i przejdźmy do czerwonych liczb dla dużych i zielonych dla małych, żeby dać mi, wiesz, sposób na wizualne śledzenie tego. Teraz wygląda na to, że jeśli kręcimy za 425 $, jesteśmy trochę, no wiesz, w tym miejscu lub w tym miejscu, albo wiesz, może tutaj, wszyscy zbliżymy nas do 425 $. Więc mogę zobaczyć, jakie są różne kursy, nasze różne kombinacje, aby doprowadzić nas do tych wartości.
Teraz kilka rzeczy, ta część tutaj, jest w rzeczywistości dużą formułą tablicową, więc = TABELA (B2, B1), wiersz i kolumna. To ciekawe, nie możesz tego wpisywać, możesz to utworzyć tylko za pomocą danych, analizy warunkowej, musisz użyć tego okna dialogowego. Jeśli spróbujesz wpisać tę formułę, naciśnij Ctrl + Shift + Enter, to nie zadziała, prawda? Jest to więc funkcja w Excelu, ale jeśli jesteś wystarczająco sprytny, aby ją wpisać, szkoda, nie zadziała, ale ciągle przelicza. Więc jeśli ustalimy, że patrzymy tylko na terminy z 48 i chcemy spojrzeć na grupy po 3 lub coś w tym rodzaju, więc kiedy zmieniam te liczby, wszystko to jest obliczaniem. W tym przypadku robi tylko jedną formułę dla każdego, ale wyobraź sobie, że gdybyśmy robili 100 formuł, to dramatycznie się spowolni. Więc tutaj, pod Formułami, tamw rzeczywistości jest to opcja Opcje obliczania, automatyczne lub ręczne, jest jeszcze trzecia, która mówi „Tak, przelicz wszystko z wyjątkiem tabel danych, nie przeliczaj ponownie tabeli danych”. Ponieważ może to znacznie wpłynąć na czas obliczeń.
W porządku, tabele danych są niesamowite, gdy masz dwie zmienne do zmiany, ale mamy trzy zmienne do zmiany. A jeśli byłyby różne stopy procentowe, czy polecam udać się do Menedżera scenariuszy? NIE, NIGDY nie polecam pójścia do Menedżera Scenariuszy! W tym przypadku mamy 9x7, czyli 63 różne scenariusze, które tutaj obliczyliśmy, stworzenie 63 różnych scenariuszy Menedżera scenariuszy zajęłoby 2 godziny, to okropne. Nie opisuję tego w książce „MrExcel XL”, ponieważ zawiera 40 najlepszych wskazówek. Prawdopodobnie jest to w mojej książce „Power Excel” z rozwiązanymi 567 zagadkami Excela, ale jestem pewien, że narzekałem na to, jak niefortunnie się go używa, nie zobaczysz mnie tutaj, jak robię Menedżera Scenariuszy. Gdybyśmy naprawdę musieli to zrobić dla kilku różnych stawek, najlepszą rzeczą do zrobienia jest po prostu Ctrl-przeciągnij, weź ten arkusz, Ctrl-przeciągnij, Ctrl-przeciągnij,Przeciągnij z wciśniętym klawiszem Ctrl, a następnie zmień stawki na każdym arkuszu. Więc gdybyśmy mogli uzyskać 5% lub 4,75% lub coś podobnego i tak dalej, prawda, nie ma łatwego sposobu na ustawienie tego dla 3 zmiennych w Menedżerze scenariuszy. W porządku, „40 najlepszych wskazówek dotyczących Excela wszechczasów”, wszystko w tej książce, możesz kupić książkę, kliknij „i” w prawym górnym rogu.
Podsumowanie odcinka z dnia dzisiejszego: w programie Excel są trzy narzędzia typu „co, jeśli”, wczoraj rozmawialiśmy o szukaniu wyniku, dziś jest to tabela danych. Jest to świetne rozwiązanie dla problemów z dwiema zmiennymi, jutro zobaczysz jeden z problemem z jedną zmienną. Funkcja tablicy tabeli nie może być wprowadzona ręcznie, nie będzie działać, musisz użyć danych, analizy warunkowej, tabeli danych. Użyłem skali kolorów, Strona główna, Formatowanie warunkowe, Skale kolorów, aby pokolorować odpowiedzi. Jeśli masz 3 zmienne do zmiany, robisz scenariusze? Nie, po prostu wykonaj kopie arkusza lub kopii tabeli, obliczenia są powolne, szczególnie w przypadku złożonego modelu. Istnieje tryb obliczania Automatyczny dla wszystkich oprócz tabel, a Owen W. Green zasugerował włączenie tej funkcji do książek.
Więc dzięki niemu i dzięki za zatrzymanie się, do zobaczenia następnym razem z kolejnym netcastem!
Pobieranie pliku
Pobierz przykładowy plik tutaj: Podcast2034.xlsx
