Анализът на Excel какво може да предложи таблица с данни. Това е лошо име. Трябва да се нарече Анализ на чувствителността. Готино е. Прочетете за това тук.
Goal Seek ви позволява да намерите набора от входни данни, които водят до определен резултат. Понякога искате да видите много различни резултати от различни комбинации от входове. Ако имате само две входни клетки за промяна, таблицата с данни осигурява бърз начин за сравняване на алтернативи.
Използвайки примера за плащане на заем, кажете, че искате да изчислите цената за различни салда по главница и за различни условия.
Уверете се, че формулата, която искате да моделирате, е в горния ляв ъгъл на диапазон. Поставете различни стойности за една променлива надолу в лявата колона и различни стойности за друга променлива в горната част.
От раздела Данни изберете Анализ какво, ако, таблица с данни.
Имате стойности по горния ред на таблицата за въвеждане. Искате Excel да включи тези стойности в определена входна клетка. Посочете тази входна клетка като Входна клетка за ред.
Имате стойности по лявата колона. Искате тези, включени в друга входна клетка. Посочете тази клетка като клетка за въвеждане на колона.
Когато щракнете върху OK, Excel ще повтори формулата в горната лява колона за всички комбинации от горния ред и лявата колона. На изображението по-долу виждате 60 различни плащания по заеми въз основа на различни резултати.
Имайте предвид, че форматирах резултатите от таблицата, за да няма десетични знаци и използвах Начало, Условно форматиране, Цветова скала, за да добавя червеното / жълтото / зеленото засенчване.
Ето страхотната част: Тази таблица е „на живо“. Ако промените входните клетки по лявата колона или горния ред, стойностите в таблицата ще се преизчислят. По-долу стойностите вляво са фокусирани върху диапазона от $ 23K до $ 24K.
Благодаря на Оуен У. Грийн, че предложи таблици.
Гледам видео
- Три инструмента „какво да“ в Excel
- Вчера - търсене на цел
- Днес - таблица с данни
- Страхотно за проблеми с две променливи
- Любопитни факти: Функцията масив TABLE не може да се въведе ръчно - тя няма да работи
- Използвайте цветова скала за оцветяване на отговорите
- Ами ако имате 3 променливи за промяна? Сценарии? Не! Копирайте работния лист
- Таблиците се изчисляват бавно: режим на изчисление за всички с изключение на таблици
- Благодаря на Оуен У. Грийн, че предложи този съвет
Видео транскрипция
Научете Excel от подкаст, епизод 2034 - Какво-ако с таблица с данни!
Подкастирам цялата тази книга, щракнете върху „i“ в горния десен ъгъл, за да стигнете до плейлиста!
Днес ще говорим за втория инструмент под Анализ какво-ако, вчера говорихме за Goal Seek, днес ще покрием таблица с данни. Така че тук имаме този хубав малък модел, това е малък модел, 3 входни клетки, една формула. Но този модел може да бъде стотици входни клетки, хиляди редове, стига да се свежда до един окончателен отговор и ние искаме да моделираме този отговор за няколко различни стойности от 2-3 (?) Входни клетки. Например, може би се интересуваме от разглеждането на различни автомобили, така че навсякъде от 20000 нагоре, така че ще сложа 20 и 21000 там, ще хвана дръжката за пълнене и ще плъзна, ще сваля това до 28000. Отгоре ние разглеждайки различни условия, така че 36-месечен заем, 42-месечен заем, 48-месечен заем, 54, 60, 66 и дори 72.
Добре сега, тази следваща стъпка е напълно незадължителна, но наистина ми помага да мисля за това, винаги сменям цветовете на стойностите отгоре и стойностите вляво. И наистина важното тук е, че тази ъглова клетка, тази най-важна ъглова клетка трябва да бъде отговорът, който се опитваме да моделираме, добре. Така че трябва да започнете да избирате от тази ъглова клетка с отговора и след това да изберете всички редове и всички колони. Така че влизаме в Data, What-if Analysis и Data Table и тук се искат две неща и ето как бихте помислили за това. Той казва, че има цял куп различни елементи в горния ред в таблицата, искам да взема тези елементи, един по един, и да ги включа в модела, къде да въведем? Така че тези елементи, това са термини, те трябва да влязат в клетката B2. И тогава,има цял куп елементи по лявата колона, ние искаме да ги вземем, един по един, и да ги включим в B1, така, добре и щракваме OK, BAM, той пуска този модел отново и отново и отново .
Сега само малко почистване тук, аз винаги влизам и правя Home и вероятно 0 знака след десетичната запетая, така. И може би малко условно форматиране, цветни скали, и нека отидем с червени цифри за големи и зелени числа за малки, само за да ми дадете, знаете ли, начин да проследя това визуално. Сега изглежда, че ако снимаме за $ 425, ние сме нещо, знаете ли, на това място или на това място, или знаете ли, може би тук, всички ще ни доближим до $ 425. Така че мога да видя какви са различните шансове, нашите различни комбинации, за да стигнем до тези стойности.
Сега няколко неща, тази част от тук, всъщност е голяма формула на масив, така че = TABLE (B2, B1), входът за реда и колоната. Това е любопитно, нямате право да въвеждате това, можете да го създадете само с помощта на Data, What-If Analysis, трябва да използвате този диалогов прозорец. Ако опитате да въведете тази формула, натиснете Ctrl + Shift + Enter, тя няма да работи, нали? Така че, това е функция в Excel, но ако сте достатъчно умни, за да я въведете, много лошо, тя няма да работи, но постоянно преизчислява. Така че, ако определим, че разглеждаме само термини от 48 и искаме да гледаме на групи от по 3 или нещо подобно, така че докато променя тези числа, всичко това се изчислява. В този случай се прави само една формула за всяка, но представете си, че ако правехме 100 формули, това се забавя драстично. Така че тук, под Формули, там "всъщност е опция Опции за изчисляване, Автоматична или Ръчна, има и трета, която казва „Да, преизчислявайте всичко, с изключение на таблиците с данни, не продължавайте да преизчислявате таблицата с данни“. Тъй като това може да е огромно забавяне на времето за изчисление.
Добре сега, таблиците с данни са страхотни, когато имате две променливи, които трябва да промените, но ние имаме три променливи, които да променим. Какво ще стане, ако има различни лихвени проценти, препоръчвам ли ви да отидете в Scenario Manager? НЕ, НИКОГА не препоръчвам да отидете в Scenario Manager! В този случай имаме 9x7, това са 63 различни сценария, които сме изчислили тук, за да създадем 63 различни сценария на Scenario Manager ще отнеме 2 часа, това е ужасно. Не отразявам това в книгата „MrExcel XL“, защото това са 40-те най-добри съвета. Това вероятно е в моята книга „Power Excel“ с 567 загадки за Excel, но съм сигурен, че се оплаках колко мизерно е да се използва, няма да ме виждате да правя Scenario Manager тук. Ако наистина трябваше да направим това за няколко различни скорости, най-доброто нещо да направим е просто Ctrl-плъзгане, вземете този лист, Ctrl-плъзгане, Ctrl-плъзгане,Ctrl-плъзнете и след това променете тарифите на всеки лист. Така че, ако можем да получим 5% или 4,75% или нещо подобно и така нататък, нали, няма лесен начин да го настроим за 3 променливи в Scenario Manager. Добре, „40 най-добри съвета на Excel за всички времена“, всички в тази книга, можете да я купите, щракнете върху „i“ в горния десен ъгъл.
Обобщение на епизодите от днес: В Excel има три инструмента „Какво да стане“, вчера говорихме за Goal Seek, днес таблица с данни. Страхотно е за проблеми с 2 променливи, утре ще видите такъв с проблем с 1 променлива. Функцията за масив на таблица не може да бъде въведена ръчно, тя няма да работи, трябва да използвате Data, what-If Analysis, Data Table. Използвах цветна скала, Начало, Условно форматиране, Цветови скали, за да оцветя отговорите. Ако имате 3 променливи за промяна, правите сценарии? Не, просто направете копия на работния лист или копия на таблицата, те се изчисляват бавно, особено при сложен модел. Има режим на изчисление за Автоматично за всички с изключение на таблици и Оуен У. Грийн предложи да включите тази функция в книгите.
Така че благодарение на него и благодарение на вас, че се отбихте, ще се видим следващия път за поредното излъчване от!
Свали файл
Изтеглете примерния файл от тук: Podcast2034.xlsx
