Аналіз "Що, якщо аналіз" пропонує таблицю даних. Це погана назва. Це слід називати аналізом чутливості. Це круто. Прочитайте про це тут.
Goal Seek дозволяє знайти набір вхідних даних, що призводять до певного результату. Іноді ви хочете бачити багато різних результатів з різних комбінацій входів. За умови, що у вас є лише дві вхідні комірки для зміни, таблиця даних забезпечує швидкий спосіб порівняння альтернатив.
На прикладі оплати позики скажіть, що ви хочете розрахувати ціну для різних залишків основного боргу та для різних умов.
Переконайтеся, що формула, яку ви хочете змоделювати, знаходиться у верхньому лівому куті діапазону. Помістіть різні значення для однієї змінної в лівий стовпець, а різні значення для іншої змінної - зверху.
На вкладці "Дані" виберіть "Що робити", таблиця даних.
У вас є значення вздовж верхнього рядка таблиці введення. Ви хочете, щоб Excel підключив ці значення до певної вхідної комірки. Вкажіть цю вхідну комірку як Вхідну комірку рядка.
У вас є значення вздовж лівого стовпця. Ви хочете, щоб вони були підключені до іншої вхідної комірки. Вкажіть цю комірку як Вхідну комірку стовпця.
Коли ви натискаєте OK, Excel повторить формулу у верхньому лівому стовпці для всіх комбінацій верхнього рядка та лівого стовпця. На зображенні нижче ви бачите 60 різних виплат за кредитом на основі різних результатів.
Зверніть увагу, що я відформатував результати таблиці без десяткових знаків, а для додавання червоного / жовтого / зеленого відтінку використовував головне, умовне форматування, кольорову шкалу.
Ось чудова частина: ця таблиця "в прямому ефірі". Якщо змінити вхідні комірки вздовж лівого стовпця або верхнього рядка, значення в таблиці будуть перераховані. Нижче значення зліва зосереджені на діапазоні від 23 000 до 24 000 доларів.
Дякую Оуену В. Гріну за пропозицію таблиць.
Переглянути відео
- Три інструменти "що-якщо" в Excel
- Вчора - досягнення мети
- Сьогодні - таблиця даних
- Чудово підходить для задач із двома змінними
- Дрібниці: функцію масиву TABLE неможливо ввести вручну - вона не буде працювати
- Використовуйте кольорову шкалу, щоб розфарбувати відповіді
- Що робити, якщо у вас є 3 змінні для зміни? Сценарії? Ні! Скопіюйте аркуш
- Таблиці обчислюються повільно: режим обчислення для всіх крім таблиць
- Дякую Оуену В. Гріну за пропозицію цієї поради
Стенограма відео
Дізнайтеся Excel з подкасту, серія 2034 - Що-якщо з таблицею даних!
Я подкастирую всю цю книгу, клацніть “i” у верхньому правому куті, щоб перейти до списку відтворення!
Сьогодні ми поговоримо про другий інструмент у розділі Що робити, якщо вчора ми говорили про Goal Seek, сьогодні ми охопимо таблицю даних. Отже, у нас тут є ця приємна маленька модель, це маленька модель, 3 вхідні комірки, одна формула. Але ця модель може складати сотні вхідних комірок, тисячі рядків, якщо вона зводиться до однієї остаточної відповіді, і ми хочемо змоделювати цю відповідь для кількох різних значень 2-3 (?) Вхідних комірок. Наприклад, можливо, ми зацікавлені в розгляді різних автомобілів, тож десь від 20000 і вище, тому я покладу туди 20 і 21000, захоплю ручку заповнення та перетягну, зніму це до 28000. Через верхню частину ми переглядаючи різні умови, отже, 36-місячна позика, 42-місячна позика, 48-місячна позика, 54, 60, 66 і навіть 72.
Гаразд, цей наступний крок є зовсім необов’язковим, але це дійсно допомагає мені подумати про це, я завжди змінюю кольори значень вгорі та значення зліва. І насправді важливим є те, що ця кутова комірка, ця найважливіша кутова комірка повинна бути відповіддю, яку ми намагаємося змоделювати, добре. Отже, ви повинні розпочати вибір із цієї кутової комірки з відповіддю, а потім виділити всі рядки та всі стовпці. Отже, ми переходимо до Даних, Що робити, якщо Аналіз та Таблиці даних, і тут просять дві речі, і ось як ви про це думаєте. Там сказано, що вздовж верхнього рядка таблиці є ціла купа різних елементів, я хочу взяти ці елементи по одному і підключити їх до моделі, куди ми повинні вводити? Отже, ці предмети, це терміни, вони повинні входити в клітинку В2. І потім,по лівій колонці є ціла купа елементів, ми хочемо взяти їх по одному і підключити їх до B1, ось так, добре, і ми натискаємо OK, BAM, вона запускає цю модель знову і знову і знову .
Зараз лише трохи прибирання тут, я завжди заходжу і роблю Домашній, і, мабуть, 0 десяткових знаків, як це. І, можливо, трохи умовного форматування, кольорові шкали, і давайте підемо з червоними цифрами для великих та зеленими цифрами для малих, просто для того, щоб дати мені, знаєте, спосіб візуально відстежити це. Зараз схоже, що якщо ми знімаємо за 425 доларів, ми начебто, знаєте, на цьому місці чи на цьому місці, або ви знаєте, можливо, тут, ми всі отримаємо близько 425 доларів. Тож я бачу, які різні шанси, різні наші комбінації, щоб дістати нас до цих значень.
Тепер кілька речей, ця частина всередині тут, насправді є великою формулою масиву, отже = TABLE (B2, B1), введення рядка та стовпця. Це цікаво, ви не можете вводити це, ви можете створити це лише за допомогою даних, Аналіз що-якщо, вам потрібно використовувати це діалогове вікно. Якщо ви спробуєте ввести цю формулу, натисніть Ctrl + Shift + Enter, це не спрацює, правда? Отже, це функція в Excel, але якщо ви досить розумні, щоб ввести її, дуже погано, вона не буде працювати, але вона постійно перераховує. Отже, якщо ми визначимо, що ми розглядаємо лише терміни з 48, і ми хочемо дивитися в групи по 3 або щось подібне, тож, коли я змінюю ці числа, все це обчислюється. У цьому випадку це лише одна формула для кожної, але уявіть, якби ми робили 100 формул, це різко сповільнюється. Отже, тут під Формулами, там 's насправді варіант Параметри обчислення, автоматичний або ручний, є третій, який говорить: "Так, перерахуйте все, крім таблиць даних, не продовжуйте перераховувати таблицю даних". Оскільки це може суттєво затримати час розрахунку.
Добре зараз, таблиці даних є чудовими, коли у вас є дві змінні для зміни, але у нас є три змінні для зміни. Що робити, якщо існували різні процентні ставки, я рекомендую звернутися до менеджера сценаріїв? НІ, Я НІКОЛИ не рекомендую звертатися до Менеджера сценаріїв! У цьому випадку ми маємо 9x7, це 63 різні сценарії, які ми розрахували тут, для створення 63 різних сценаріїв Scenario Manager потрібно 2 години, це жахливо. Я не висвітлюю це у книзі “MrExcel XL”, оскільки це 40 найкращих порад. Це, мабуть, є в моїй книзі “Power Excel”, у якій розгадано 567 таємниць Excel, але я впевнений, що я скаржився на те, наскільки жалюгідним є використання. Якщо нам дійсно довелося зробити це за декілька різних темпів, найкраще зробити просто Ctrl-drag, візьміть цей аркуш, Ctrl-drag, Ctrl-drag,Ctrl-перетягніть, а потім змініть норми на кожному аркуші. Отже, якщо ми можемо отримати 5% або 4,75% або щось подібне тощо, правильно, немає простого способу встановити це для 3 змінних у Менеджері сценаріїв. Гаразд, “40 найкращих підказок Excel усіх часів”, усі в цій книзі ви можете придбати книгу, клацніть на „i” у верхньому правому куті.
Підсумок епізоду з сьогоднішнього дня: У Excel є три інструменти "Що робити", вчора ми говорили про Goal Seek, сьогодні Таблиця даних. Це чудово для проблем із 2 змінними, завтра ви побачите проблему з проблемою з 1 змінною. Функцію табличного масиву неможливо ввести вручну, вона не буде працювати, вам доведеться використовувати дані, що-якщо аналіз, таблицю даних. Я використовував кольорову гаму, Головна, Умовне форматування, Кольорові шкали, щоб розфарбувати відповіді. Якщо у вас є 3 змінні для зміни, ви робите сценарії? Ні, просто зробіть копії робочого аркуша або копії таблиці, вони повільно обчислюються, особливо за складної моделі. Існує режим розрахунку для автоматичного для всіх, крім таблиць, і Оуен В. Грін запропонував включити цю функцію до книг.
Тож завдяки йому та спасибі тобі, що ти заїхав, ми побачимось наступного разу для чергової трансляції від!
Завантажити файл
Завантажте зразок файлу тут: Podcast2034.xlsx
