Анализ что если в excel
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше
С помощью средств анализа "что если" в Excel вы можете экспериментировать с различными наборами значений в одной или нескольких формулах, чтобы изучить все возможные результаты.
Например, можно выполнить анализ "что если" для формирования двух бюджетов с разными предполагаемыми уровнями дохода. Или можно указать нужный результат формулы, а затем определить, какие наборы значений позволят его получить. В Excel предлагается несколько средств для выполнения разных типов анализа.
Обратите внимание на то, что в этой статье приведен только обзор инструментов. Подробные сведения о каждом из них можно найти по ссылкам ниже.
Анализ "что если" — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе.
В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Таблицы данных работают только с одной или двумя переменными, но могут принимать множество различных значений для них. Сценарий может содержать несколько переменных, но допускает не более 32 значений. Подбор параметров отличается от сценариев и таблиц данных: при его использовании берется результат и определяются возможные входные значения для его получения.
Помимо этих трех средств можно установить надстройки для выполнения анализа "что если", например надстройку Поиск решения. Эта надстройка похожа на подбор параметров, но позволяет использовать больше переменных. Вы также можете создавать прогнозы, используя маркер заполнения и различные команды, встроенные в Excel.
Для более сложных моделей можно использовать надстройку Пакет анализа.
Сценарий — это набор значений, которые сохраняются в Excel и могут автоматически подставляться в ячейки на листе. Вы можете создавать и сохранять различные группы значений на листе, а затем переключиться на любой из этих новых сценариев, чтобы просмотреть другие результаты.
Предположим, у вас есть два сценария бюджета: для худшего и лучшего случаев. Вы можете с помощью диспетчера сценариев создать оба сценария на одном листе, а затем переключаться между ними. Для каждого сценария вы указываете изменяемые ячейки и значения, которые нужно использовать. При переключении между сценариями результат в ячейках изменяется, отражая различные значения изменяемых ячеек.
1. Изменяемые ячейки
2. Ячейка результата
1. Изменяемые ячейки
2. Ячейка результата
Если у нескольких человек есть конкретные данные в отдельных книгах, которые вы хотите использовать в сценариях, вы можете собрать эти книги и объединить их сценарии.
После создания или сбора всех нужных сценариев вы можете создать сводный отчет по сценариям, в который включаются данные из этих сценариев. В отчете по сценариям все данные отображаются в одной таблице на новом листе.
Примечание: В отчетах по сценариям автоматический пересчет не выполняется. Изменения значений в сценарии не будут отражается в уже существующем сводном отчете. Вам потребуется создать новый сводный отчет.
Если вы знаете нужный результат формулы, но не знаете, какое входные значения требуется для получения этого результата, используйте функцию "Поиск окна". Предположим, что вам нужно занять денег. Вы знаете, сколько вам нужно, на какой срок и сколько вы сможете выплачивать каждый месяц. С помощью средства подбора параметров вы можете определить, какая процентная ставка вам подойдет.
Ячейки B1, B2 и B3 — это значения для суммы займа, длины срока и процентной ставки.
Ячейка B4 отображает результат формулы =PMT(B3/12;B2;B1).
Примечание: В средстве поиска окна можно ввести только одно значение переменной. Если вы хотите определить несколько входных значений, например сумму займа и сумму ежемесячного платежа по кредиту, используйте надстройка "Надстройка "Найти решение". Дополнительные сведения о надстройки "Решение" см. в разделе Подготовка прогнозов и расширенных бизнес-моделей ипо ссылкам в разделе См. также.
Если у вас есть формула с одной или двумя переменными либо несколько формул, в которых используется одна общая переменная, вы можете просмотреть все результаты в одной таблице данных. С помощью таблиц данных можно легко и быстро проверить несколько возможностей. Поскольку используются всего одна или две переменные, результат можно без труда прочитать или опубликовать в табличной форме. Если для книги включен автоматический пересчет, данные в таблицах данных сразу же пересчитываются, и вы всегда видите свежие данные.
Ячейка B3 содержит входные значения.
Ячейки C3, C4 и C5 являются значениями, Excel заменяются на основе значения, введенного в ячейку B3.
В таблицу данных нельзя помещать больше двух переменных. Для анализа большего количества переменных используйте сценарии. Несмотря на то что переменных не может быть больше двух, можно использовать сколько угодно различных значений переменных. В сценарии можно использовать не более 32 различных значений, зато вы можете создать сколько угодно сценариев.
При подготовке прогнозов вы можете использовать Excel для автоматической генерации будущих значений на базе существующих данных или для автоматического вычисления экстраполированных значений на основе арифметической или геометрической прогрессии.
Вы можете заполнить ряд значений, которые соответствуют простому линейному или экспоненциальному тренду роста, с помощью ручки заполнения или команды Ряд. Для расширения сложных и нелинейных данных можно использовать функции или средство регрессионного анализа надстройки "Надстройка анализа".
В средстве подбора параметров можно использовать только одну переменную, а с помощью надстройки Поиск решения вы можете создать обратную проекцию для большего количества переменных. Надстройка "Поиск решения" помогает найти оптимальное значение для формулы в одной ячейке листа, которая называется целевой.
Над решением работает группа ячеек, связанных с формулой в целевой ячейке. "Решение" изменяет значения изменяемых ячеек, которые вы указываете (регулируемые ячейки), чтобы получить результат, который вы указываете из формулы целевой ячейки. Ограничения можно применять для ограничения значений, которые можно использовать в модели, а ограничения могут ссылаться на другие ячейки, влияющие на формулу целевой ячейки.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция ЕСЛИ — одна из самых популярных функций в Excel. Она позволяет выполнять логические сравнения значений и ожидаемых результатов.
Поэтому у функции ЕСЛИ возможны два результата. Первый результат возвращается в случае, если сравнение истинно, второй — если сравнение ложно.
Например, функция =ЕСЛИ(C2="Да";1;2) означает следующее: ЕСЛИ(С2="Да", то вернуть 1, в противном случае вернуть 2).
Функция ЕСЛИ, одна из логических функций, служит для возвращения разных значений в зависимости от того, соблюдается ли условие.
ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь])
Имя аргумента
лог_выражение (обязательно)
Условие, которое нужно проверить.
значение_если_истина (обязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ИСТИНА.
значение_если_ложь (необязательно)
Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.
Простые примеры функции ЕСЛИ
В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)
В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст "Да", в противном случае вернуть текст "Нет"). Как видите, функцию ЕСЛИ можно использовать для сравнения и текста, и значений. А еще с ее помощью можно оценивать ошибки. Вы можете не только проверять, равно ли одно значение другому, возвращая один результат, но и использовать математические операторы и выполнять дополнительные вычисления в зависимости от условий. Для выполнения нескольких сравнений можно использовать несколько вложенных функций ЕСЛИ.
=ЕСЛИ(C2>B2;"Превышение бюджета";"В пределах бюджета")
В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст "Превышение бюджета", в противном случае вернуть текст "В пределах бюджета")
На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение "Фактические" больше значения "Плановые", то вычесть сумму "Плановые" из суммы "Фактические", в противном случае ничего не возвращать).
В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = "Да", то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)
Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: "Текст"). Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.
Распространенные неполадки
0 (ноль) в ячейке
Не указан аргумент значение_если_истина или значение_если_ложь. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.
Как правило, это указывает на ошибку в формуле.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Таблица данных – это диапазон ячеек, по которому видно, как изменение одной или двух переменных в формулах повлияет на результаты вычисления этих формул. Таблицы данных позволяют быстро вычислять несколько результатов за одну операцию, а также просматривать и сравнивать результаты всех вариантов формулы на одном листе.
Таблицы данных входят в набор команд, которые называются средствами анализа "что если". При использовании таблиц данных вы выполняете анализ "что если". Средства анализа "что если" изменяют значения в ячейках и показывают, как эти изменения повлияют на результаты формул на листе. Например, вы можете использовать таблицу данных для изменения процентной ставки и срока ссуды с целью определения возможных сумм ежемесячных платежей.
В Excel предлагаются средства анализа "что если" трех типов: сценарии, таблицы данных и подбор параметров. В сценариях и таблицах данных берутся наборы входных значений и определяются возможные результаты. Подбор параметров отличается от сценариев и таблиц данных тем, что при его использовании берется результат и определяются возможные входные значения для его получения. Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, в таблицах данных все результаты представлены в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.
В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя таблица данных ограничена только одной или двумя переменными (одна для подстановки значений по столбцам, вторая — по строкам), она позволяет использовать множество разных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.
Общие сведения о таблицах данных
Вы можете создавать таблицы данных с одной или двумя переменными в зависимости от числа переменных и формул, которые необходимо проверить. Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, таблицу данных с одной переменной можно использовать, чтобы узнать, как разные процентные ставки повлияют на размер ежемесячного платежа, вычисляемый с использованием функции ПЛТ. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.
Дополнительные сведения см. в статье Функция ПЛТ.
Ячейка D2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейку ввода B3.
Таблица данных с одной переменной
список значений, которые Excel подставить в ячейку ввода B3.
Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.
Ячейка C2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.
Таблица данных с двумя переменными
список значений, которые Excel подставить в ячейку ввода строки (B4).
список значений, которые Excel подставить в ячейку ввода столбца B3.
Таблицы данных пересчитываются всякий раз при пересчете листа, даже если в них не были внесены изменения. Для ускорения пересчета листа, содержащего таблицу данных, можно изменить параметры вычислений так, чтобы автоматически пересчитывался лист, но не таблицы.
Введите в отдельном столбце или в отдельной строке список значений, которые нужно подставлять в ячейку ввода. Оставьте по обе стороны от значений несколько пустых строк и столбцов.
Выполните одно из указанных ниже действий.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее столбца значений.
На рисунке в разделе "Обзор" показана ориентированная по столбцу таблица данных с одной переменной, формула находится в ячейке D2.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки справа от первой формулы.
По строке (значения переменной находятся в строке)
Введите формулу в ячейку, расположенную на один столбец левее первого значения и на одну ячейку ниже строки значений.
Примечание: Если требуется исследовать влияние различных значений на другие формулы, введите дополнительные формулы в ячейки под первой формулой.
Выделите диапазон ячеек с формулами и значениями, которые нужно заменить. На первом рисунке в разделе "Обзор" это диапазон C2:D5.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам. На первом рисунке ячейка ввода — это B3.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов имеют денежный формат.
Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
По столбцу (значения переменной находятся в столбце)
Введите новую формулу в пустую ячейку, расположенную в верхней строке таблицы справа от имеющейся формулы.
По строке (значения переменной находятся в строке)
Введите новую формулу в пустую ячейку, расположенную в первом столбце таблицы под имеющейся формулой.
Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
Выполните одно из действий, указанных ниже.
Ориентация таблицы данных
Необходимые действия
Введите ссылку на ячейку ввода в поле Подставлять значения по строкам.
Введите ссылку на ячейку ввода в поле Подставлять значения по столбцам.
В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.
В ячейку на листе введите формулу, которая ссылается на две ячейки ввода. В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, введите формулу =ПЛТ(B3/12;B4;-B5) в ячейку C2.
Введите один список входных значений в том же столбце под формулой. В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
Введите второй список справа от формулы в той же строке. Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5). В данном примере выделяется диапазон C2:E5.
В Excel 2016 для Mac: выберите пункты Данные > Анализ "что если" > Таблица данных.
В Excel 2011 для Mac: на вкладке Данные в группе Анализ выберите пункты Что если > Таблица данных.
В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке. Введите B4 в поле Ячейка ввода строки.
В поле Ячейка ввода столбца введите ссылку на ячейку ввода для входных значений в столбце. Введите B3 в поле Ячейка ввода столбца.
Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ниже ячейка C2 содержит формулу для расчета платежа =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.
список значений, которые Excel подставить в ячейку ввода строки (B4).
список значений, которые Excel подставить в ячейку ввода столбца B3.
Важно: Если выбран этот вариант вычисления, при пересчете книги таблицы данных не пересчитываются. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9. Чтобы использовать эту клавишу в Mac OS X версии 10.3 или более поздней, сначала необходимо отключить ее назначение в Exposé. Дополнительные сведения см. в Excel сочетания клавиш Windows.
В меню Excel выберите пункт Параметры.
В разделе Формулы и списки выберите пункт Вычисление, а затем — параметр Автоматически, кроме таблиц данных.
Анализ "Что Если" в Excel позволяет попробовать различные значения (сценарии) для формул.
Следующий пример поможет Вам освоить Анализ "что если" быстро и легко.
Предположим, у вас есть книжный магазин и есть 100 книг на продажу. Вы продаете определенный % книг по самой высокой цене в $ 50 и определенный % книг по более низкой цене $ 20.
Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.
Создание различных сценариев
Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг - это различный сценарий.
Вы можете использовать "Диспетчер сценариев" для создания этих сценариев.
Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ "что если" позволит Вам сравнить результаты различных сценариев.
1. На вкладке Данные выберите Анализ "что если" и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.
2. Добавьте сценарий, нажав на кнопку Добавить.
3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.
4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.
5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).
И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:
Примечание: чтобы увидеть результат сценария, выберите сценарий и нажмите на кнопку Вывести. Excel изменит значение ячейки C4 в соответствии со сценарием, что бы Вы смогли увидеть результат на листе.
Отчет по сценариям
Для того, чтобы легко сравнить результаты этих сценариев, выполните следующие действия:
1. Кликните по кнопке "Отчет" в Диспетчере сценариев.
2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.
Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ "что если" в Excel.
Подписывайтесь на нас в социальных сетях, оставляйте комментарии к статье. Надеюсь пример использования анализа "что если" в Excel Вам понравился.
Таблица данных в Excel представляет собой диапазон, который оценивает изменение одной или двух переменных в формуле. Другими словами, это Анализ "что если", о котором мы говорили в одной из прошлых статей (если Вы ее не читали - очень рекомендую ознакомиться по этой ссылке), в удобном виде. Вы можете создать таблицу данных с одной или двумя переменными.
Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене - $50 и определенный % книг по более низкой цене - $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.
Таблица данных с одной переменной.
Что бы создать таблицу данных с одной переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).
2. Введите различные проценты в столбце А.
3. Выберите диапазон A12:B17.
Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4.
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.
Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода ("Подставлять значения по столбцам в: ") мы оставляем пустой.
Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.
Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.
Таблица данных с двумя переменными.
Что бы создать таблицу с двумя переменными, выполните следующие шаги.
1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).
2. Внесите различные варианты высокой цены в строку 12.
3. Введите различные проценты в столбце А.
4. Выберите диапазон A12:D17.
Мы будем рассчитывать выручку от реализации книг в различных комбинациях высокой цены и % продаж книг по высокой цене.
5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.
6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7.
7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4.
Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.
Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.
Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.
Спасибо за внимание. Теперь Вы сможете более эффективно применять один из видов анализа "что если" , а именно формирование таблиц данных с одной или двумя переменными.
Остались вопросы - задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.
Читайте также: