Анализ что если таблица данных в excel

Анализ данных в Excel

Чтобы лучше представить, как функция «Идеи» упрощает, быстрее и интуитивнее анализ данных, функция была переименована в Анализ данных. Возможности и функциональные возможности одинаковы и по-прежнему соответствуют тем же нормативным актам о конфиденциальности и лицензировании. Если вы работаете в Полугодовом канале (корпоративный), вы можете по-прежнему видеть «Идеи», пока Excel не будет обновлен.

Анализ данных в Excel помогает вам изучить ваши данные с помощью запросов на естественном языке, которые позволяют задавать вопросы о данных без написания сложных формул. Кроме того, Анализ данных создает наглядные визуальные представления сводных данных, тенденций и закономерностей.

Есть вопрос? Мы ответим!

Просто выделите ячейку в диапазоне данных и нажмите кнопку Анализ данных на вкладке Главная. Анализ данных в Excel проанализирует данные и вернет интересные визуальные элементы в области задач.

Если вы хотите получить более конкретные сведения, введите свой вопрос в поле запроса в верхней части панели и нажмите ВВОД. Анализ данных выдаст ответы с использованием графических элементов, например таблиц, диаграмм и сводных таблиц, которые можно будет вставить в книгу.

Если вы хотите проанализировать свои данные или просто хотите узнать об имеющихся возможностях, по щелчку в поле запроса функция Анализ данных предложит персонализированные вопросы.

Попробуйте воспользоваться предлагаемыми вопросами

Просто задайте вопрос

Выберите текстовое поле в верхней части панели «Анализ данных», и вы увидите список предложений, составленный на основе ваших данных.

Кроме того, вы можете ввести конкретный вопрос о своих данных.

• Подписчики Microsoft 365 могут пользоваться функцией на английском, французском, испанском, немецком, китайском (упрощенное письмо) и японском языках. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office. Подробнее о разных каналах обновления Office см. в статье Обзор каналов обновления приложений Microsoft 365.
• Функция запросов на естественном языке в компоненте Анализ данных предоставляется клиентам поэтапно. В данный момент она может быть доступна не во всех странах или регионах.

Получение конкретных сведений с помощью Анализ данных

Если у вас нет конкретного вопроса, Анализ данных не только отвечает на вопросы на естественном языке, но и анализирует данные, а также создает наглядные визуальные представления сводных данных, тенденций и закономерностей.

Вы можете сэкономить время и получить более конкретный анализ, выбрав только нужные вам поля. Когда вы выбираете поля и способ их обобщения, Анализ данных исключает другие доступные данные, что ускоряет процесс и обеспечивает предоставление предложений, меньших по количеству, но точнее сформулированных. Например, вам может потребоваться только общая сумма продаж за год. Или же можно попросить Анализ данных показать средний уровень продаж по годам.

Выберите Какие поля интересуют вас больше всего?

Выберите поля и способ обобщения содержащихся в них данных.

Анализ данных предоставляет меньшие по количеству, но точнее сформулированные предложения.

Примечание: Параметр Не является значением в списке полей относится к полям, для которых обычно не выполняется суммирование или вычисление средних значений. Например, вы не можете вычислить сумму отображаемых лет, но вы можете вычислить сумму значений отображаемых лет. Параметр Не является значением, используемый с другим полем, в котором производится суммирование или вычисление среднего значения, работает как метка строки, однако при самостоятельном использовании Не является значением подсчитывает уникальные значения выбранного поля.

Анализ данных лучше всего работает с очищенными табличными данными.

Вот некоторые советы по эффективному использованию функции Анализ данных.

1. Анализ данных лучше всего работает с данными, отформатированными в виде таблицы Excel. Чтобы создать таблицу Excel, щелкните в любом месте данных и нажмите клавиши CTRL+T.
2. Убедитесь, что у вас правильно отформатированы заголовки столбцов. Заголовки должны быть представлены в виде одной строки уникальных непустых имен столбцов. Не используйте двойные строки заголовков, объединенные ячейки и т. д.
3. При наличии сложных или вложенных данных для преобразования перекрестных таблиц или таблиц с несколькими строками заголовков можно использовать надстройку Power Query.

Анализ данных не работает? Скорее всего, проблема у нас, а не у вас.

Вот некоторые причины, по которым Анализ данных может не работать с вашими данными:

• Анализ данных в настоящее время не поддерживает анализ наборов данных размером более 1,5 миллионов ячеек. Временного решения этой проблемы пока нет. Вы можете отфильтровать данные, скопировать в другое место и обработать с помощью функции Анализ данных.
• Строковое даты, такие как «01-01-2017», анализируются как текстовые строки. В качестве временного решения можно создать для них новый столбец и отформатировать как даты с помощью функции ДАТА или ДАТАЗНАЧ.
• Анализ данных не будет работать, когда Excel находится в режиме совместимости (т. е. если файл находится в .xls формате). Сохраните файл в формате XLSX, XLSM или XLSB.
• Объединенные ячейки также могут представлять сложность для анализа. Если вы хотите выровнять данные по центру, например в заголовке отчета, то в качестве временного решения удалите все объединенные ячейки, а затем выровняйте ячейки по центру выделения. Нажмите клавиши CTRL+1 и перейдите на Выравнивание >По горизонтали >По центру выделения.

Анализ данных лучше всего работает с очищенными табличными данными.

Вот некоторые советы по эффективному использованию функции Анализ данных.

Анализ данных лучше всего работает с данными, отформатированными в виде таблицы Excel. Чтобы создать таблицу Excel, щелкните в любом месте диапазона данных и нажмите клавиши

Анализ данных не работает? Скорее всего, проблема у нас, а не у вас.

Вот некоторые причины, по которым Анализ данных может не работать с вашими данными:

• Анализ данных в настоящее время не поддерживает анализ наборов данных размером более 1,5 миллионов ячеек. Временного решения этой проблемы пока нет. Вы можете отфильтровать данные, скопировать в другое место и обработать с помощью функции Анализ данных.
• Строковое даты, такие как «01-01-2017», анализируются как текстовые строки. В качестве временного решения можно создать для них новый столбец и отформатировать как даты с помощью функции ДАТА или ДАТАЗНАЧ.
• Анализ данных не удается проанализировать данные, если Excel находится в режиме совместимости (т. е. если файл находится в .xls формате). Сохраните файл в формате XLSX, XLSM или XLSB.
• Объединенные ячейки также могут представлять сложность для анализа. Если вы хотите выровнять данные по центру, например в заголовке отчета, то в качестве временного решения удалите все объединенные ячейки, а затем выровняйте ячейки по центру выделения. Нажмите клавиши CTRL+1 и перейдите на Выравнивание >По горизонтали >По центру выделения.

Анализ данных лучше всего работает с очищенными табличными данными.

Вот некоторые советы по эффективному использованию функции Анализ данных.

1. Анализ данных лучше всего работает с данными, отформатированными в виде таблицы Excel. Чтобы создать таблицу Excel, щелкните в любом месте данных и выберите Пункт Главная > Таблицы > Форматировать как таблицу.
2. Убедитесь, что у вас правильно отформатированы заголовки столбцов. Заголовки должны быть представлены в виде одной строки уникальных непустых имен столбцов. Не используйте двойные строки заголовков, объединенные ячейки и т. д.

Анализ данных не работает? Скорее всего, проблема у нас, а не у вас.

Вот некоторые причины, по которым Анализ данных может не работать с вашими данными:

• Анализ данных в настоящее время не поддерживает анализ наборов данных размером более 1,5 миллионов ячеек. Временного решения этой проблемы пока нет. Вы можете отфильтровать данные, скопировать в другое место и обработать с помощью функции Анализ данных.
• Строковое даты, такие как «01-01-2017», анализируются как текстовые строки. В качестве временного решения можно создать для них новый столбец и отформатировать как даты с помощью функции ДАТА или ДАТАЗНАЧ.

Анализ данных постоянно совершенствуется

Даже если ни одно из указанных выше условий не выполняется, поиск рекомендаций может оказаться безрезультатным. Это объясняется тем, что служба пытается найти определенный набор классов аналитических сведений, и ей не всегда это удается. Мы постоянно работаем над расширением типов анализа, поддерживаемых службой.

Вот текущий список доступных типов анализа:

• Ранг. Ранжирует элементы и выделяет тот, который существенно больше остальных.

• Тренд. Выделяет тенденцию, если она прослеживается на протяжении всего временного ряда данных.

• Выброс. Выделяет выбросы во временном ряду.

• Большинство. Находит случаи, когда большую часть итогового значения можно связать с одним фактором.

Если вы не получили результатов, отправьте нам отзыв, выбрав на вкладке Файл пункт Отзывы и предложения.

Microsoft Privacy Policy

Так как Анализ данных выполняет анализ с помощью служб искусственного интеллекта, возможно, вас беспокоит безопасность данных. Вы можете ознакомиться с дополнительными сведениями в заявлении о конфиденциальности корпорации Майкрософт.

Сведения о лицензировании для функции Анализ данных

Анализ данных использует материалы сторонних производителей. Подробности см. в разделе Сведения о лицензировании для функции «Анализ данных».

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Вычисление множественных результатов с помощью таблицы данных

Таблица данных — это диапазон ячеек, в котором можно изменить значения в некоторых ячейках и придумать различные ответы на проблему. Хороший пример таблицы данных использует функцию PMT с разными суммами кредитов и процентными ставками для расчета доступной суммы по ипотечному кредиту дома. Экспериментирование с разными значениями для наблюдения за соответствующими различиями в результатах является распространенной задачей при анализе данных.

В Microsoft Excel таблицы данных являются частью набора команд, известных как средства анализа What-If. При создании и анализе таблиц данных выполняется анализ «что если».

Анализ «что если» — это процесс изменения значений в ячейках, который позволяет увидеть, как эти изменения влияют на результаты формул на листе. Например, таблицу данных можно использовать для изменения процентной ставки и срока кредита, чтобы оценить потенциальные суммы ежемесячных платежей.

Примечание: Вы можете выполнять более быстрые вычисления с помощью таблиц данных и Visual Basic для приложений (VBA). Дополнительные сведения см. в статье Таблицы данных Excel What-If: ускорение вычислений с помощью VBA.

Типы анализа «что если»

Существует три типа средств анализа «что если» в Excel: сценарии, таблицы данных и поиск целей. Сценарии и таблицы данных используют наборы входных значений для вычисления возможных результатов. Поиск целей явно отличается, он использует один результат и вычисляет возможные входные значения, которые бы привели к такому результату.

Как и сценарии, таблицы данных позволяют изучить набор возможных результатов. В отличие от сценариев, таблицы данных показывают все результаты в одной таблице на одном листе. С помощью таблиц данных можно легко и быстро проверить диапазон возможностей. Поскольку при этом используются всего одна или две переменные, вы можете без труда прочитать результат и поделиться им в табличной форме.

В таблице данных может быть не больше двух переменных. Для анализа большего количества переменных необходимо использовать сценарии. Хотя она ограничена только одной или двумя переменными (одна для входной ячейки строки и одна для ячейки ввода столбца), таблица данных может содержать любое количество различных значений переменных. В сценарии можно использовать не более 32 разных значений, но вы можете создавать сколько угодно сценариев.

Дополнительные сведения см. в статье Введение в анализ What-If.

Общие сведения о таблицах данных

Создайте таблицы данных с одной или двумя переменными в зависимости от количества переменных и формул, которые необходимо протестировать.

Таблицы данных с одной переменной

Таблицы данных с одной переменной используются в том случае, если требуется проследить, как изменение значения одной переменной в одной или нескольких формулах повлияет на результаты этих формул. Например, можно использовать таблицу данных с одной переменной, чтобы увидеть, как различные процентные ставки влияют на ежемесячный платеж по ипотеке с помощью функции PMT. Значения переменных вводятся в один столбец или строку, а результаты отображаются в смежном столбце или строке.

На следующем рисунке ячейка D2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая ссылается на входную ячейку B3.

Таблицы данных с двумя переменными

Таблицы данных с двумя переменными используются в том случае, если требуется проследить, как изменение значений двух переменных в одной формуле повлияет на результаты этой формулы. Например, таблицу данных с двумя переменными можно использовать, чтобы узнать, как разные комбинации процентных ставок и сроков ссуды повлияют на размер ежемесячного платежа.

На следующем рисунке ячейка C2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая использует две входные ячейки: B3 и B4.

Вычисления таблицы данных

При пересчете листа все таблицы данных также будут пересчитываться, даже если данные не были изменены. Чтобы ускорить вычисление листа, содержащего таблицу данных, можно изменить параметры вычисления , чтобы автоматически пересчитывать лист, но не таблицы данных. Дополнительные сведения см. в разделе Ускорение вычислений на листе с таблицами данных.

Создание таблицы данных с одной переменной

Таблица данных с одной переменной содержит входные значения либо в одном столбце (ориентированный на столбец), либо в строке (в виде строк). Любая формула в таблице данных с одной переменной должна ссылаться только на одну ячейка ввода.

1. Введите список значений, которые нужно заменить во входной ячейке: вниз по одному столбцу или через одну строку. Оставьте несколько пустых строк и столбцов по обе стороны от значений.
2. Выполните одно из указанных ниже действий.
3. Если таблица данных ориентирована на столбец (значения переменных находятся в столбце), введите формулу в ячейке на одну строку выше и одну ячейку справа от столбца значений. Эта таблица данных с одной переменной ориентирована на столбцы, а формула содержится в ячейке D2.

Примечание: После создания таблицы данных может потребоваться изменить формат ячеек результатов. На рисунке ячейки результатов отформатированы в виде валюты.

Добавление формулы в таблицу данных с одной переменной

Формулы, которые используются в таблице данных с одной переменной, должны ссылаться только на одну ячейку ввода.

Выполните эти действия

1. Выполните одно из следующих действий:
   • Если таблица данных ориентирована на столбцы, введите новую формулу в пустую ячейку справа от существующей формулы в верхней строке таблицы данных.
   • Если таблица данных ориентирована на строки, введите новую формулу в пустую ячейку под существующей формулой в первом столбце таблицы данных.
2. Выделите диапазон ячеек, которые содержат таблицу данных и новую формулу.
3. На вкладке Данные щелкните Анализ что если >Таблица данных (в группе Инструменты данных или ПрогнозExcel 2016 ).
4. Выполните одно из следующих действий.
   • Если таблица данных ориентирована на столбцы, введите ссылку на ячейку для входной ячейки в поле Ячейка ввода столбца .
   • Если таблица данных ориентирована на строки, введите ссылку на ячейку входной ячейки в поле Ячейка ввода строки .

Создание таблицы данных с двумя переменными

В таблице данных с двумя переменными используется формула, содержащая два списка входных значений. Формула должна ссылаться на две разные ячейки ввода.

1. В ячейке на листе введите формулу, которая ссылается на две входные ячейки. В следующем примере, в котором начальные значения формулы вводятся в ячейки B3, B4 и B5, введите формулу =PMT(B3/12;B4;-B5) в ячейку C2.
2. Введите один список входных значений в том же столбце под формулой. В данном примере нужно ввести разные процентные ставки в ячейки C3, C4 и C5.
3. Введите второй список в той же строке, что и формула — справа от нее. Введите срок погашения ссуды (в месяцах) в ячейки D2 и E2.
4. Выделите диапазон ячеек, содержащий формулу (C2), строку и столбец значений (C3:C5 и D2:E2), а также ячейки, в которых должны находиться вычисленные значения (D3:E5). В данном примере выделяется диапазон C2:E5.
5. На вкладке Данные в группе Средства обработки данных или Прогноз (в Excel 2016 ) щелкните Что если анализ >таблица данных (в группе Инструменты данных или Прогноз Excel 2016 ).
6. В поле Ячейка ввода строки введите ссылку на ячейку ввода для входных значений в строке.
   Введите ячейку B4 в поле Ячейка ввода строки .
7. В поле Входная ячейка столбца введите ссылку на входную ячейку для входных значений в столбце.
   Введите B3 в поле Входная ячейка Столбца .
8. Нажмите кнопку ОК.

Пример таблицы данных с двумя переменными

Таблица данных с двумя переменными может показать, как разные процентные ставки и сроки погашения ссуды влияют на размер ежемесячного платежа. На рисунке ячейка C2 содержит формулу оплаты =PMT(B3/12;B4;-B5)), которая использует две входные ячейки: B3 и B4.

Ускорение вычислений на листе с таблицами данных

Если этот параметр вычисления задан, вычисления таблицы данных не выполняются при пересчете для всей книги. Чтобы выполнить пересчет таблицы данных вручную, выделите содержащиеся в ней формулы и нажмите клавишу F9.

Чтобы повысить производительность вычислений, выполните следующие действия.

1. Выберите Пункт Параметры > файла >формулы.
2. В разделе Параметры вычисления в разделе Вычислить щелкните Автоматически, кроме таблиц данных.

Совет: При необходимости на вкладке Формулы щелкните стрелку на вкладке Параметры вычисления, а затем выберите пункт Автоматически, кроме таблиц данных (в группе Вычисление ).

Дальнейшие действия

Вы можете использовать несколько других средств Excel для анализа «что если», если у вас есть определенные цели или большие наборы переменных данных.

Подбор параметров

Если вы знаете результат, который следует ожидать от формулы, но не знаете точно, какое входное значение необходимо формуле, чтобы получить этот результат, используйте функцию Goal-Seek. См. статью Использование поиска цели, чтобы найти нужный результат, изменив входное значение.

Решатель Excel

Вы можете использовать надстройку «Решение Excel», чтобы найти оптимальное значение для набора входных переменных. Решатель работает с группой ячеек (называемых переменными решения или просто переменными ячейками), которые используются при вычислении формул в целевых и ограничивающих ячейках. Надстройка «Поиск решения» изменяет значения в ячейках переменных решения согласно пределам ячеек ограничения и выводит нужный результат в целевой ячейке. Дополнительные сведения см. в этой статье : Определение и решение проблемы с помощью средства решения.

Подключив различные числа к ячейке, вы можете быстро придумать различные ответы на проблему. Отличным примером является использование функции ПМТ с разными процентными ставками и периодами кредита (в месяцах), чтобы выяснить, сколько кредита вы можете позволить себе для дома или автомобиля. Числа введите в диапазон ячеек, называемый таблицей данных.

Здесь таблица данных — это диапазон ячеек B2:D8. Вы можете изменить значение в B4, сумму кредита и ежемесячные платежи в столбце D автоматически обновляться. Используя процентную ставку 3,75 %, D2 возвращает ежемесячный платеж в размере 1042,01 долл. США по следующей формуле: =PMT(C2/12,$B$3,$B$4).

Можно использовать одну или две переменные в зависимости от количества переменных и формул, которые требуется протестировать.

Используйте тест с одной переменной, чтобы увидеть, как различные значения одной переменной в формуле изменят результаты. Например, можно изменить процентную ставку для ежемесячного платежа по ипотеке с помощью функции PMT. Переменные значения (процентные ставки) вводятся в один столбец или строку, а результаты отображаются в соседнем столбце или строке.

В этой динамической книге ячейка D2 содержит формулу оплаты =PMT(C2/12,$B$3,$B$4). Ячейка B3 — это переменная ячейка, в которой можно подключить другую длину срока (количество ежемесячных периодов оплаты). В ячейке D2 функция PMT включает процентную ставку 3,75%/12, 360 месяцев и кредит на сумму 225 000 долл. США и вычисляет ежемесячный платеж в размере 1042,01 долл. США.

Используйте тест с двумя переменными, чтобы увидеть, как различные значения двух переменных в формуле изменят результаты. Например, можно протестировать различные сочетания процентных ставок и количества периодов ежемесячных платежей для расчета платежа по ипотеке.

В этой динамической книге ячейка C3 содержит формулу оплаты =PMT($B$3/12,$B$2,B4), в которой используются две ячейки переменных: B2 и B3. В ячейке C2 функция PMT включает процентную ставку 3,875%/12, 360 месяцев и кредит на сумму 225 000 долл. США и вычисляет ежемесячный платеж в размере 1058,03 долл. США.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Введение в анализ «что если»

С помощью средств анализа «что если» в 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 для автоматической генерации будущих значений на базе существующих данных или для автоматического вычисления экстраполированных значений на основе арифметической или геометрической прогрессии.

Вы можете заполнить ряд значений, которые соответствуют простому линейному тренду или тенденции экспоненциального роста, с помощью дескриптора заполнения или команды Series . Чтобы расширить сложные и нелинейные данные, можно использовать функции листа или средство анализа регрессии в надстройке Analysis ToolPak.

В средстве подбора параметров можно использовать только одну переменную, а с помощью надстройки Поиск решения вы можете создать обратную проекцию для большего количества переменных. Надстройка «Поиск решения» помогает найти оптимальное значение для формулы в одной ячейке листа, которая называется целевой.

Решатель работает с группой ячеек, связанных с формулой в целевой ячейке. Решатель настраивает значения в указанных изменяющихся ячейках, которые называются настраиваемыми ячейками, чтобы получить результат, указанный из формулы целевой ячейки. Ограничения можно применять для ограничения значений, которые Решатель может использовать в модели, а ограничения могут ссылаться на другие ячейки, влияющие на формулу целевой ячейки.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Элементы тригонометрии и Офисные информационные технологии

Microsoft Excel может создать структуру для данных, что позволяет скрыть и отобразить уровни детализации простым нажатием кнопки мыши. Щелкая символы структуры , и , можно быстро отобразить только строки или столбцы с итоговыми значениями или заголовками разделов листа либо использовать эти символы для просмотра сведений отдельного значения или заголовка.

Подготовка данных к структурированию

Структурируемые данные должны быть представлены в виде диапазона, в котором первая строка каждого столбца содержит подпись, остальные строки — однотипные данные; пустые строки или столбцы в диапазоне отсутствуют.

Перед началом создания структуры может потребоваться отсортировать данные, чтобы сгруппировать строки вместе. В показанном на рисунке примере диапазон был отсортирован по регионам, а затем по месяцам, после чего произошло объединение строк «Март» и «Апрель» восточного региона и строк каждого месяца западного региона.

Располагайте итоговые строки выше или ниже каждой группы детальных строк. Для получения наилучших результатов итоговые строки должны содержать формулы, ссылающиеся на ячейки во всех строках. В показанном на рисунке примере имеется строка под сведениями каждого месяца и региона. Например, данные по продажам в строках с 11 по 13 суммируются в строке 14. Также можно структурировать данные, в которых итоговые строки содержат описательный текст и иные данные.

Если структурируются столбцы, а не строки, убедитесь, что диапазон имеет заголовки в первом столбце, и что имеются итоговые столбцы слева или справа от столбцов сведений.

Отображение и скрытие структурированных данных

Структура может иметь до 8 уровней детализации, в которых каждый уровень обеспечивает подробную информацию для предыдущего уровня. В показанном на рисунке примере строка «Итого», содержащая итог для всех строк, имеет уровень 1. Строки, содержащие итоги для каждого месяца, имеют уровень 2, а конкретные данные по уровням продаж имеют уровень 3. Для отображения только строк на определенном уровне достаточно щелкнуть номер уровня, который нужно просмотреть. В показанном на рисунке примере строки с подробностями для восточного региона и для «Апрель» западного региона скрыты, но можно щелкнуть символ для их отображения.

Создание структуры автоматически и вручную

Автоматическое создание структуры. Если данные на листе обобщены формулами, которые используют функции , например СУММА, Microsoft Excel позволяет автоматически структурировать данные, как показано на рисунке. Итоговые данные должны располагаться рядом с подробными данными.

При вставке автоматических промежуточных итогов в диапазон, представленный в виде строк, Microsoft Excel автоматически структурирует лист, давая возможность отобразить ровно столько подробной информации, сколько необходимо.

Создание структуры вручную. Если данные не организованы так, что Microsoft Excel может создать структуру автоматически, структура может быть создана вручную. Например, требуется вручную создать структуры данных, если строки или столбцы с итоговыми данными содержат не формулы, а значения или описательный текст.

Настройка структурирования со стилями

Автоматические стили можно применить непосредственно при структурировании или после него. Для структурированных строк Microsoft Excel использует такие стили, как «Уровеньстрока_1» и «Уровеньстрока_2». Для структурированных столбцов Microsoft Excel использует такие стили как «Уровеньстолб_1» и «Уровеньстолб_2». Стили используют полужирное, курсивное и другое форматирование текста, чтобы облегчить различение итоговых строк в данных. Можно изменять способы форматирования, определенные каждому из стилей, что позволяет применять различные стили оформления текста и ячейки для настройки внешнего вида структуры. Также можно использовать автоформата для форматирования структурированных данных.

Создание структуры

Данная структура позволяет показывать и скрывать строки дополнительных сведений о месячном объеме продаж.

1. Если итоговые строки расположены над строками данных или итоговые столбцы расположены слева от столбцов данных, измените параметры расположения.

Инструкции

1. В меню Данные выберите команду Группа и структура , а затем — Настройки.

2. Снимите флажок в строках под детальными, флажок в столбцах справа от детальных или оба эти флажка.

2. Если требуется задать стили структуры для автоматического применения к новой структуре, установите флажок Автоматические стили.

Инструкции

1. В меню Данные выберите команду Группа и структура , а затем — Настройки.

2. Установите флажок Автоматические стили.

Примечание. Если при создании структуры не требуется применять автоматические стили, их можно применить после создания структуры.

3. Решите, как требуется структурировать лист — автоматически или вручную.

o Любой лист можно структурировать вручную.

o Если на листе есть итоговые формулы, ссылающиеся на ячейки в области сведений , его можно структурировать автоматически. Все столбцы, содержащие итоговые формулы, должны располагаться слева или справа от области сведений, а все строки, содержащие итоговые формулы — под или над областью сведений.

o Если вы не знаете, какие на данном листе есть формулы или как они расположены, попробуйте структурировать лист автоматически, а если результат не будет соответствовать желаемому, отмените это действие и выполните структурирование вручную.

Упражнение

Выполните одно из следующих действий.

1. Выделите диапазон ячеек, для которых необходимо создать структуру.

Для структурирования листа целиком укажите любую ячейку.

2. Выберите команду Группа и структура в меню Данные, а затем — Создание структуры.

1. Выделите строки или столбцы, содержащие сведения.

Строки или столбцы сведений обычно прилегают к строке или столбцу, содержащему итоговые формулы или заголовки. Например, если строка 6 содержит итоговые данные для строк с 3 по 5, выделите строки 3—5. Если строка 8 содержит заголовок, описывающий строки с 9 по 12, выделите строки 9—12.

2. В меню Данные укажите на пункт Группа и структура , а затем выберите команду Группировать.

Рядом с группой на экране появятся знаки структуры .

3. Продолжайте выделение и группировку строк или столбцов сведений и выполнение команды «Группировать» до тех пор, пока не будут созданы все необходимые уровни структуры.

4. Если ранее не были применены автоматически, их можно применить в любой момент.

Инструкции

1. Выделите ячейки, к которым необходимо применить стили.

2. В меню Данные укажите на пункт Группа и структура , а затем выберите команду Настройка .

3. Установите флажок Автоматические стили.

4. Нажмите кнопку Применить стили.

Скрытие и отображение структурированных данных

Упражнение

1. Если на листе отсутствуют символы структуры , и , выберите в меню Сервис команду Параметры, откройте вкладку Вид и установите флажок символы структуры.

2. Выполните одно или несколько следующих действий.

• Покажите или скройте подробные данные группы

1. Чтобы показать подробные данные группы, нажмите соответствующую этой группе кнопку .

2. Чтобы скрыть подробные данные группы, нажмите соответствующую этой группе кнопку .

• Разверните или сверните структуру до определенного уровня

Среди символов структуры нажмите кнопку с цифрой требуемого уровня. Подробные данные более низкого уровня будут скрыты.

Например , если в структуре четыре уровня, скрыть четвертый уровень (оставив все остальные развернутыми) можно, нажав кнопку .

• Покажите или скройте все структурированные данные

1. Чтобы показать все подробные данные, нажмите среди символов структуры кнопку самого низкого уровня. Например, если существует три уровня, нажмите кнопку .

2. Чтобы скрыть все подробные данные, нажмите кнопку .

Удаление структуры

При удалении структуры никакие данные не удаляются.

1. Выберите лист.

2. Выберите команду Группа и структура в меню Данные, а затем — Удалить структуру.

3. Если строки или столбцы все равно останутся скрытыми, перетащите указатель через скрытые заголовки строк или столбцов с одного видимого заголовка на другой, выберите в меню Формат команду Строка или Столбец, а затем выберите команду Отобразить.

Примечания

· Выделенные элементы структуры также можно разгруппировать без удаления всей структуры. Удерживайте нажатой SHIFT, щелкая кнопку или группы, затем в меню Данные укажите на пункт Группа и структура и выберите команду Разгруппировать.

· Чтобы скрыть структуру, не удаляя ее, покажите все данные, нажав кнопку с наибольшим числом в области структуры , выберите в меню Сервис команду Параметры, откройте вкладку Вид и снимите флажок символы структуры.

Выполнение анализов «что — если» для данных на листах

Подбор параметра

Подбор параметра является частью блока задач, который иногда называют инструментами анализа «что-если» . Когда желаемый результат одиночной формулы известен, но неизвестны значения, которые требуется ввести для получения этого результата, можно воспользоваться средством «Подбор параметра» выбрав команду Подбор параметра в меню Сервис. При подборе параметра Microsoft Excel изменяет значение в одной конкретной ячейке до тех пор, пока формула, зависимая от этой ячейки, не возвращает нужный результат.

Например , средство «Подбор параметра» используется для изменения процентной ставки в ячейке B3 в сторону увеличения до тех пор, пока размер платежа в ячейке B4 не станет равен 900,00р.

Изменение значения ячейки для получения определенного значения в другой ячейке

1. Выберите команду Подбор параметра в меню Сервис.

2. В поле Установить в ячейке введите ссылку на ячейку, содержащую необходимую формулу .

3. Введите искомый результат в поле Значение.

4. В поле Изменяя значение ячейки введите ссылку на ячейку, значение которой нужно подобрать. Формула в ячейке, указанной в поле Установить в ячейке должна ссылаться на эту ячейку.

5. Нажмите кнопку OK.

Анализ таблиц данных

Таблицы данных

Таблицы данных являются частью блока задач, который иногда называют инструментами анализа «что-если» . Таблица данных представляет собой диапазон ячеек, показывающий, как изменение определенных значений в формулах влияет на результаты этих формул. Таблицы предоставляют способ быстрого вычисления нескольких версий в рамках одной операции, а также способ просмотра и сравнения результатов всех различных вариантов на одном листе.

Таблицы данных с одной переменной . Таблица данных с одной переменной используется, например, при необходимости просмотреть, как различные процентные ставки влияют на размер ежемесячных выплат по закладной. В следующем примере ячейка D2 содержит формулу вычисления платежа, =ППЛАТ(B3/12;B4-B5), которая ссылается на ячейку ввода B3.

Таблицы данных с двумя переменными. Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по закладной различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.

Подсчет таблицы данных. Таблицы подстановки данных пересчитываются всякий раз при пересчете листа, даже если в них не были внесены изменения. Для ускорения процесса пересчета листа, содержащего таблицу подстановки данных, следует так изменить параметр Вычисления, чтобы автоматически пересчитывался лист, но не таблицы.

Добавление формулы в таблицу подстановки с одной переменной

Упражнение

Формулы , используемые в таблице подстановки с одной переменной, должны ссылаться на одну и ту же ячейку ввода .

1. Выполните одно из следующих действий:

o если значения в таблице расположены по столбцам, введите новую формулу в пустую ячейку, расположенную в верхней строке справа от имеющейся формулы;

o если значения в таблице расположены по строкам, введите новую формулу в пустую ячейку, расположенную в первом столбце снизу от имеющейся формулы.

2. Выделите таблицу подстановки, включая столбец или строку, где содержится новая формула.

3. В меню Данные выберите команду Таблица .

4. Выполните одно из следующих действий:

o если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

o если значения в таблице расположены по строкам, введите ссылку на ячейку ввода в поле Подставлять значения по столбцам в.

Удаление таблицы подстановки данных

Удаление всей таблицы

1. Выделите всю таблицу данных , включая все формулы , значения подстановки, рассчитанные значения, форматы и примечания.

2. Выберите пункт Очистить в меню Правка , а затем – команду Все.

Удаление рассчитанных значений из таблицы подстановки данных

Примечание. Поскольку рассчитанные значения находятся в массиве , необходимо очистить все значения. Если не требуется удалять всю таблицу данных , убедитесь, что формулы и значения подстановки не выделены.

1. Выделите в таблице данных все рассчитанные значения.

2. Выберите пункт Очистить в меню Правка , а затем – команду Содержимое.

Преобразование рассчитанных значений таблицы подстановки данных в константы

1. Выделите в таблице данных все рассчитанные значения.

2. Нажмите на стандартной панели инструментов кнопку Копировать, а затем выделите левую верхнюю ячейку области вставки .

3. Нажмите стрелку рядом с кнопкой Вставить и выберите вариант Значения.

Примечание. Так как рассчитанные значения расположены в массиве , все их необходимо преобразовать в статические значения.

Создание таблицы подстановки с одной переменной

Упражнение

Следует сформировать таблицу подстановки с одной переменной, чтобы введенные значения были расположены либо в столбце (ориентированные по столбцу), либо в строке (ориентированные по строке). Формулы , используемые в таблицах подстановки с одной переменной, должны ссылаться на ячейку ввода .

1. Либо в отдельный столбец, либо в отдельную строку введите список значений, которые следует подставлять в ячейку ввода.

2. Выполните одно из следующих действий.

o Если значения в таблице подстановки ориентированы по столбцу, введите формулу в ячейку, расположенную на одну строку выше и на одну ячейку правее первого значения. Правее первой формулы введите любые другие формулы.

o Если значения в таблице подстановки ориентированы по строке, введите формулу в ячейку, расположенную на один столбец левее и на одну строку ниже первого значения. В том же столбце, но ниже введите любые другие формулы.

3. Выделите диапазон ячеек, содержащий формулы и значения подстановки.

4. В меню Данные выберите команду Таблица .

5. Выполните одно из следующих действий:

o если значения в таблице расположены по столбцам, введите ссылку на ячейку ввода в поле Подставлять значения по строкам в;

o если значения в таблице расположены по строкам, введите ссылку на ячейку ввода в поле Подставлять значения по столбцам в.

Имеется возможность добавления дополнительных формул в таблицу подстановки с одной переменной.

Создание таблицы подстановки с двумя переменными

Таблицы подстановки с двумя переменными используют одну формулу с двумя наборами значений. Формула должна ссылаться на две различные ячейки ввода .

1. В ячейку листа введите формулу, которая ссылается на две ячейки ввода.

В приведенном ниже примере, где исходные значения формулы введены в ячейки B3, B4 и B5, формулу =ПЛТ(B3/12;B4;-B5) следует ввести в ячейку C2.

1. В том же столбце ниже формулы введите значения подстановки для первой переменной.

В приведенном ниже примере различные процентные ставки следует ввести в ячейки C3, C4 и C5.

3. Введите значения подстановки для второй переменной правее формулы в той же строке.

В приведенном ниже примере срок погашения ссуды (в месяцах) следует ввести в ячейки D2 и E2.

1. Выделите диапазон ячеек, содержащий формулу и оба набора данных подстановки.

В приведенном ниже примере следует выделить диапазон C2:E5.

5. В меню Данные выберите команду Таблица подстановки.

6. В поле Подставлять значения по столбцам в введите ссылку на ячейку ввода для значений подстановки в строке.

В приведенном ниже примере в поле Подставлять значения по столбцам в следует ввести B4.

1. В поле Подставлять значения по строкам в введите ссылку на ячейку ввода для значений подстановки в столбце.

В приведенном ниже примере в поле Подставлять значения по строкам в следует ввести B3.

Пример

Таблица данных с двумя переменными может показать влияние на размер ежемесячных выплат по ссуде различных процентных ставок и сроков займа. В следующем примере ячейка C2 содержит формулу вычисления платежа, =ПЛТ(B3/12;B4;-B5), которая ссылается на ячейки ввода B3 и B4.

Ускорение пересчета листа, содержащего таблицу подстановки данных

1. В меню Сервис выберите команду Параметры, а затем — вкладку Вычисления.

2. Выберите вариант Автоматически кроме таблиц.

Примечание. При выборе этого варианта вычисления при пересчете книги таблицы не пересчитываются. Чтобы выполнить пересчет таблиц вручную, выделите формулу и нажмите клавишу F9.