В этой главе рассматривается таблица данных, которая создает массив, как результат работы функции Таблица (эту функцию нельзя ввести в ячейки вручную). Таблица данных – это быстрый и простой способ выполнить анализ «что если» для сложных взаимосвязанных вычислений на основе формул. Эта функция позволяет изменять одну или две формулы входов и отображение того, что результаты. В главах , и уже были приведены примеры таблиц данных, которые использовались в сочетании с функциями базы данных.
Но дизайн не ограничивается только входными данными. Это также зависит от того, что вы хотите увидеть, созданного вашей моделью. Например, если фокус модели состоит в том, чтобы определить количество транзакций в день, чтобы безуспешно, модель должна спроецировать это.
Как это изменится, если средний размер билета повысится на 20%, а валовая прибыль увеличится еще на 5%? Между двумя концами у вас получился дизайн. Теперь предположим на секунду, что у вас есть базовая финансовая модель, которая использует вышеуказанные параметры дизайна и дает финансовые результаты для магазина розничного магазина в торговом центре на Ближнем Востоке. Просто простая модель, которая разрабатывает финансовую отчетность в течение следующих 3 лет.
Скачать заметку в формате или , примеры в формате
Анализ «что если» на основе Таблицы с одной переменной
На рис. 21.1 в ячейки В6 используется функция ПЛТ, косвенно зависящая от значения ячейки В2. Если вы измените годовую ставку ставка, функция ПЛТ обновит значение в ячейке В6. Цель состоит в том, чтобы одновременно увидеть, как месячный платеж будет меняться при пяти различных годовых ставках. Хотя это можно сделать путем написания формулы, функция Таблица может быть полезна по двум причинам:
Анализ данных и таблицы данных
Мы начнем с одного измерения и одной измерительной метрики. Мы будем следить за этим двумя измерениями и одной измерительной метрикой. И мы закончим с одним измерением и несколькими метриками измерения. Для удобства мы будем называть эти два члена переменной и метрикой.
Для нашей первой задачи мы хотим рассказать о влиянии размеров билетов на количество транзакций, которые нам нужны в год, до безубыточности. Мы начинаем с создания простой сетки различной величины размера билета, которую мы хотим проверить, и результата транзакции безубыточности.
- Таблицы данных работают быстрее, чем решения на основе иных формул.
- Таблицы данных проще в использовании, и время создания формулы сокращается.
Чтобы создать таблицу данных:
- Создайте заголовки А9:В9. В ячейке В10 введите формулу =В6. В ячейки А11:А15 введите значения годовой ставки для анализа. Выделите диапазон А10:В15.
- Пройдите по меню ДАННЫЕ –> Анализ «что если» –> Таблица данных , чтобы открыть диалоговое окно Таблица данных , или нажав и удерживая клавишу Alt, последовательно нажмите Ы, Ё, Т (после нажатия Alt в меню будут появляться подсказки).
- Поскольку вы анализируете влияние годовой ставки, укажите ссылку на нее в поле Подставлять значение по строкам в (рис. 21.2). Вы говорите Таблице данных , заменить значение из ячейки В2 в процессе расчета ПЛТ и вместо него подставить в формулу значения из диапазона А11:А15.
- Нажмите ОК.
Следующий шаг - выбрать область, отмеченную границей. В раскрывающемся меню выберите «Таблицы данных». Вы увидите небольшое всплывающее окно с возможностью размещения в ячейке ввода строки и ячейке ввода столбца. В этом случае, поскольку мы выложили размеры билетов в колонке, мы выберем ячейку ввода столбца.
Перенос на двумерный анализ
Теперь, когда мы успешно рассмотрели одномерную таблицу, давайте рассмотрим более сложный сценарий. Мы хотим изучить влияние размеров билетов и коэффициентов конверсии продаж на операционный доход нашего розничного магазина. Для этого мы начинаем с построения сетки. Внутри сетки мы добавляем размер билета в качестве нашей главной колонки и коэффициентов конверсии в качестве нашей основной строки.
Рис. 21.2. Диалоговое окно Таблица данных
Если вы выделите диапазон В11:В15 и взглянете на строку формул, то увидите формулу массива Таблица со ссылкой на ячейку В2. Функцию Таблица нельзя ввести с клавиатуры; она автоматически создается при использовании диалогового окна Таблица данных .
Переменная, которую мы хотим отслеживать, является вычисленным значением. Мы помещаем ссылку на ячейку в подчеркнутом месте в точку в нашей электронной таблице, где рассчитывается операционный доход. Два входа - это два местоположения в наших электронных таблицах, где эти драйверы определены. Первое - это местоположение, в котором коэффициент преобразования принимается за вход; второй - это место, где размер билета принимается как вход.
В то время как размер билета входит в качестве входа в ячейку ввода столбца. Вот три исходные таблицы данных из нашего предыдущего сообщения, которые привели к этой дискуссии. Идите вперед и посмотрите, сможете ли вы построить их самостоятельно и самостоятельно.
Рис. 21.3. Функцию Таблица можно ввести только с помощью диалогового окна Таблица данных
На рис. 21.4 ячейки в диапазон E3:I3 содержат различные формулы, которые прямо или косвенно ссылаются на число проданных штук (в ячейке В3). Используя Таблицу данные можно выполнить анализ «что если» для пяти формул. Причем все они основываются на одной и той же переменной, расположенной в диапазоне D4:D12.
Для типичной финансовой модели запуска основные драйверы, которые необходимо смоделировать. Метрики, которые необходимо отслеживать, следующие. Доходы Операционная прибыль Доход Прибыль Валидация Разрушение Группы продаж Количество клиентов Фиксированные затраты. Вы можете смешивать и сопоставлять их для создания собственного анализа, а также для определения областей фокусировки. Приводы и показатели, которые могут означать разницу между успехом и неудачей.
В таблице данных суммируется влияние одной переменной на формулу. Давайте рассмотрим простой расчет ипотеки. Возможно, вам захочется выяснить, какие разные ежемесячные выплаты будут выплачиваться по разным процентным ставкам. Этот тип проблемы можно решить, используя одну таблицу данных переменных.
Рис. 21.4. Одна переменная Таблицы данных может работать на нескольких формулах; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке
Вы должны создать таблицу, содержащую не менее двух столбцов и двух строк. Первый столбец должен содержать значения для одного из аргументов, которые вы хотите заменить в формуле. Второй столбец должен содержать формулу в первой ячейке. Создайте таблицу с двумя столбцами, содержащую различные процентные ставки в одном столбце и ежемесячную выплату в другой.
Выберите, чтобы отобразить диалоговое окно «Таблица». Введите первую ячейку из столбца, который вы хотите подставить в ячейку ввода столбца. Если вы используете строки вместо столбцов, то значения подстановки должны быть в первой строке. Когда вы создаете формулу, аргумент, который вы хотите заменить, должен находиться в первом столбце таблицы.
Две переменные в Таблице данных
В следующем примере задача – найти минимальное значение по двум критериям. Для этого используется Таблица данных на основе двух переменных (рис. 21.5). Поместите формулу в левом верхнем углу Таблицы данных (в нашем примере – в ячейке F8), и введите настройте в диалоговом окне Таблица данных , как показано на рис. 21.5.
Создание таблицы и ссылки для результата
Например, мы знаем, как изменится внутренняя ценность нашего анализа, если мы установим стоимость капитала и темп роста на 1% выше или ниже. На первом этапе мы сначала должны создать таблицу, которая позже будет включать анализ чувствительности. Поэтому мы должны думать, для каких двух переменных мы хотим определить зависимость от внутренней стоимости. Кроме того, нам нужно указать, какие изменения в переменной мы хотим посмотреть.
Не менее интересна тема «таблица данных». Таблица данных - это диапазон ячеек, который указывает, как изменение определенных значений в формулах влияет на результат формул. Таблицы данных обеспечивают быстрый способ вычисления нескольких версий за одну операцию и обеспечивают возможность просмотра и сравнения всех результатов разных версий вместе на листе.
Рис. 21.5. Таблица данных с двумя переменными
Второй пример (рис. 21.6) вы уже видели в . Там использовалась формула массива. Например, в ячейке F9: =ИНДЕКС($C$2:$C$15;ПОИСКПОЗ($E9&F$8;$A$2:$A$15&$B$2:$B$15;0)). Решение на основе Таблицы данных проще, и работает быстрее.
Это звучит сложно, но это не так. При вложении в машины или оборудование они активируются по завершении, т.е. принятый в пользование. За период использования, срок полезного использования, эти активы амортизируются. Стоимость актива должна составлять € 000. По истечении срока полезной службы остаточная стоимость в размере 000 евро должна быть реализована путем продажи. Срок полезной службы должен составлять 12 лет.
Для этого подходит таблица данных. Появится небольшое меню. Теперь вы можете увидеть все возможные варианты в таблице данных и сравнить их друг с другом. Результат первоначального выбора также можно найти в таблице и отмечен желтым цветом. Этот пример посвящен вычислению времени, необходимого для завершения пути с определенной скоростью.
Рис. 21.6. Использование Таблицы данных , как альтернатива ВПР по двум параметрам
Если есть 60, вы вычисляете минуты. Со структурой таблицы данных вы выполните действия, описанные в примере 1. Анализ чувствительности исследует, насколько чувствительно ситуация реагирует на изменения факторов, которые влияют на эту ситуацию. Они различаются по существу количеством допустимых переменных.
В первой версии только одно значение может быть изменено во вторых двух. Однако различное количество переменных также имеет последствия для компоновки данных, которые включены в расчет. Это разные версии для двух версий. Кроме того, количество формул, которые могут быть оценены, зависит от количества переменных. Только версия с одной переменной может одновременно оценивать несколько формул.
Одно заключительное замечание по поводу Таблицы данных : существует параметр, который позволяет отключить автоматическое обновление Таблиц данных , при этом другие формулы будут пересчитываться автоматически. Если ваш файл «тормозит», пройдите по меню ФАЙЛ –> Параметры , перейдите на вкладку Формулы , и выберите опцию автоматически, кроме таблиц данных (рис. 21.7). Когда вы всё же захотите обновить вычисления в Таблице данных , нажмите F9.
Сначала простой пример. На рисунке показано небольшое приложение с текущей функцией значений. Первоначально зарегистрировано 6%. В этом примере это текущая функция значения. Эта формула должна быть введена в строке непосредственно над первой строкой, которая содержит альтернативные значения.
Однако он должен быть смещен по крайней мере на один столбец справа, потому что программа будет размещать вновь вычисленные значения непосредственно по этой формуле. На рисунке эта область выделена серым рисунком для ясности. Диапазон определяется столбцом альтернативного значения и строкой, содержащей формулу или формулы, которые команда должна умножать.
Рис. 21.7. Отключение автоматического вычисления Таблиц данных
Excel содержит множество мощных инструментов для выполнения сложных математических вычислений, например, Анализ "что если" . Этот инструмент способен экспериментальным путем найти решение по Вашим исходным данным, даже если данные являются неполными. В этом уроке Вы узнаете, как использовать один из инструментов анализа "что если" под названием Подбор параметра .
В отличие от нормальной формулы, результат этой операции не отображается в ячейке, содержащей формулы, а в непрерывной серии ячеек. Это входная ячейка, в которую различные процентные ставки «толкаются» друг за другом, чтобы пересчитывать формулу настоящего значения каждый раз. Ячейки, отображающие результаты многократной операции, образуют матрицу результатов. Так же применяются особенности, описанные в разделе 5. Поэтому отдельные ячейки в этой области не могут быть изменены или удалены.
С другой стороны, другие процентные ставки могут быть изменены по желанию. Если вы также установили другие аргументы текущей функции значения, используя ссылки на ячейки, вы также можете попробовать другие значения для количества периодов или регулярных платежей. Вся таблица многократной операции пересчитывается каждый раз.
Подбор параметра
Каждый раз при использовании формулы или функции в Excel Вы собираете исходные значения вместе, чтобы получить результат. Подбор параметра работает наоборот. Он позволяет, опираясь на конечный результат, вычислить исходное значение, которое даст такой результат. Далее мы приведем несколько примеров, чтобы показать, как работает Подбор параметра .
В последнем примере воспроизводится только одна формула с альтернативными значениями. Пока вы работаете с одной переменной, вы также можете наблюдать поведение нескольких формул одновременно, как уже упоминалось. Например, вы могли бы также рассчитывать текущую стоимость в случае, если депозиты должны быть в начале периода. Формула должна быть расширена только аргументом для полноты.
Предварительный и последующий расчет стоимости. Вторая версия множественной операции позволяет использовать две переменные, две входные ячейки, но может оценивать только одну формулу за раз. Структура табличного пространства немного отличается от первой версии. Формула вводится в ранее пустой, верхнюю левую угловую ячейку области таблицы, выбранной для многократной операции.
Как использовать Подбор параметра (пример 1):
Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра , чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.
Альтернативные значения для первой переменной сохраняются в левом столбце области таблицы, как обычно. С другой стороны, значения для второй переменной вводятся в первой строке, начиная с ячейки справа от ячейки формулы. Если формулы были в первой версии, значения для второй переменной должны быть в этой версии.
Взгляд на рисунок показывает структуру этой специальной таблицы. В качестве примера еще раз выполните финансовую функцию. Расчет скорости работает с тремя аргументами, суммой капитала, например. Кредит, процентная ставка и количество периодов. Кредит и процентная ставка должны быть разнообразными.
На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.
На этот раз оба поля ввода должны быть заполнены. Эта ячейка содержит соответствующую скорость. Расчет скорости с двумя переменными. Способ работы со списком и таблицей очень, очень отличается, с рядом преимуществ в пользу работы с таблицами. Кроме того, этот список «изолирован» от любых других данных, то есть имеет по крайней мере одну пустую строку сверху и снизу и пустой столбец слева и справа, который отделяет его от любых других данных который может находиться на листе; Это удобно, так оно и есть.
Последний момент, чтобы учесть, что список правильно структурирован: считается, что если в столбце есть формула, та же самая формула должна быть во всем столбце, в одном столбце никогда не должно быть разных формул; ни в том же столбце не должны быть ячейки с данными и ячейками с формулами. Список, с некоторыми данными некоторых людей.
Как использовать Подбор параметра (пример 2):
Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра , чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3 , которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).
Как видно из предыдущего примера, бывают ситуации, которые требуют целое число в качестве результата. Если Подбор параметра выдает десятичное значение, необходимо округлить его в большую или меньшую сторону в зависимости от ситуации.
Другие типы анализа "что если"
Для решения более сложных задач можно применить другие типы анализа "что если" - сценарии или таблицы данных. В отличие от Подбора параметра , который опирается на требуемый результат и работает в обратном направлении, эти инструменты позволяют анализировать множество значений и наблюдать, каким образом изменяется результат.
