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