Таблица данных в Excel — это диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это анализ «Что, если», о котором мы говорили в одной из предыдущих статей (если вы его еще не читали, настоятельно рекомендую ознакомиться с этой ссылкой) в удобной форме. Вы можете создать таблицу параметров с одной или двумя переменными.
Допустим, у вас есть книжный магазин, и есть 100 книг на продажу. Вы можете продать определенный процент книг по высокой цене — 50 долларов и определенный процент книг по более низкой цене — 20 долларов. Если вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общий доход от форум 60 * 50 + 40 * 20 долларов = 3800 долларов.
Вы можете скачать рассмотренный пример по этой ссылке: Пример анализа «что, если» в Excel.
Таблица данных с одной переменной.
Чтобы создать таблицу параметров с переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите = D10 (ссылка на общий доход).
2. Введите другой процент в столбце A.
3. Выберите диапазон A12: B17.
Мы рассчитаем общий доход, если вы продадите 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке «Данные» щелкните «Анализ« Что, если »» и выберите в списке «Таблица данных.
5. Щелкните поле «Заменить значения строки в:» и выберите ячейку C4.
Мы выбрали ячейку C4, потому что проценты относятся к этой ячейке (% книг, проданных по самой высокой цене). Помимо формулы в ячейке B12, Excel теперь знает, что для расчета общего дохода необходимо заменить значение в ячейке C4 на 60%, 70% и т.д.
Примечание. Поскольку мы создаем таблицу параметров с одной переменной, мы оставляем вторую ячейку ввода пустой («Заменить значения столбцами в.
6. Щелкните ОК.
Результат.
Итог: если вы продадите 60% книг по высокой цене, вы получите общий доход в размере 3800 долларов, если вы продадите 70% книг по высокой цене, вы получите 4100 долларов и так далее.
Примечание. Строка формул указывает на то, что ячейки содержат формулу массива. Следовательно, удалить результат невозможно. Чтобы удалить результаты, выберите диапазон B13: B17 и нажмите «Удалить.
Таблица данных с двумя переменными.
Чтобы создать таблицу с двумя переменными, выполните следующие действия.
1. Выберите ячейку A12 и введите = D10 (ссылка на общий доход).
2. Введите различные варианты максимальной цены в строке 12.
3. Введите другой процент в столбце A.
4. Выберите диапазон A12: D17.
Мы рассчитаем выручку от продажи книг в различных комбинациях высокой цены и% от продаж книги по высокой цене.
5. На вкладке «Данные» щелкните «Анализ« что, если »» и выберите в списке «Таблица данных.
6. Щелкните поле «Заменить значения столбца в:» и выберите ячейку D7.
7. Щелкните поле «Заменить значения строки в:» и выберите ячейку C4.
Мы выбрали ячейку D7, потому что в этой ячейке указана самая высокая цена на книги. Мы выбрали ячейку C4, потому что в ней указан процент продаж с максимальной ценой. Наряду с формулой в ячейке A12 Excel теперь знает, что он должен заменить значение в ячейке D7, начиная с 50 долларов, и в ячейке C4, начиная с 60%, чтобы рассчитать общий доход, до 70 и 100 долларов соответственно.
8. Щелкните ОК.
Результат.
Итог: если вы продадите 60% книг по высокой цене в 50 долларов, вы получите общий доход в размере 3800 долларов, если вы продадите 80% книг по высокой цене в 60 долларов, вы получите 5200 долларов, и так далее.
Примечание. Строка формул указывает, что ячейки содержат формулу массива. Следовательно, невозможно удалить один результат. Чтобы удалить результаты, выберите диапазон B13: D17 и нажмите «Удалить.
Спасибо за внимание. Теперь вы можете более эффективно применять один из видов анализа «что если» — формирование таблиц данных с одной или двумя переменными.
