Надстройка «Анализ данных» в Экселе

Содержание

Мастер (интеллектуального анализа данных надстройки для Excel) кластера – SQL Server 2014 Analysis Services

Надстройка «Анализ данных» в Экселе

  • 12/29/2017
  • Время чтения: 2 мин

Мастер кластеризации помогает построить модель, определяющую строки со сходными характеристиками и группирующую их для максимизации расстояния между группами.

The Cluster wizard helps you build a model that detects rows that share similar characteristics and groups them to maximize the distance between groups. Этот мастер полезен для нахождения закономерностей во всех видах данных.

This wizard is useful for finding patterns in all kinds of data.

Мастер кластеризации применяет алгоритм кластеризации Майкрософт и может быть в значительной степени настроен.The Cluster wizard uses the Microsoft Clustering algorithm and can be extensively customized.

Он работает на существующих данных из таблицы Excel, из диапазона Excel или из запроса Службы Analysis ServicesAnalysis Services.It works on existing data from an Excel table, an Excel range, or an Службы Analysis ServicesAnalysis Services query.

Аналогичная функциональность обеспечивается путем Поиск категорий средства, предусмотренные в средства анализа таблиц для Excel.Similar functionality is provided by the Detect Categories tool, provided in the Table Analysis Tools for Excel.

Однако средство «Определение категории» нельзя настроить, и оно может использовать данные только из таблиц Excel.However, the Detect Categories tool cannot be customized and must use data in Excel tables.

Использование мастера кластеризацииUsing the Cluster Wizard

  1. На ленте «Интеллектуальный анализ данных» щелкните кластера, а затем нажмите кнопку Далее.In the Data Mining ribbon, click Cluster, and then click Next.

  2. В Выбор источника данных странице, выберите таблицу Excel или диапазона.In the Select Source Data page, select an Excel table or range. Вместо этого можно указать внешний источник данных.Or specify and external data source.

    Если используется внешний источник данных, можно создать пользовательские представления или вставить пользовательский текст запроса и сохранить набор данных как источник данных Службы Analysis ServicesAnalysis Services.If you use an external data source, you can create custom views or paste in custom query text, and save the data set as an Службы Analysis ServicesAnalysis Services data source.

  3. На кластеризации страницы, можно настроить способ построения модели.On the Clustering page, you can customize the way the model is built.

    • Для число сегментов, можно указать мастеру создать фиксированное число категорий или разрешить ему автоматически определять оптимальное количество группирований.For Number of segments, you can tell the wizard to create a fixed number of categories, or let it automatically detect the optimum number of groupings.
    • Просмотрите список столбцов в входные столбцы списка и отменить выбор любых столбцов, которые не используются при создании шаблонов.Review the list of columns in the Input columns list, and deselect any columns that are not useful in creating patterns. В столбцы, которые следует исключить, входят идентификационные номера, имена клиентов и так далее.Columns you should exclude include ID numbers, customer names, and so on.
  4. При необходимости щелкните параметры Чтобы изменить параметры алгоритма и настроить поведение модели кластеризации.Optionally, click Parameters to change the algorithm parameters and customize the behavior of the clustering model.

  5. В разбить данные на обучающий и проверочный наборы , определите, какие данные нужно оставить для тестирования.In the Split data into training and testing sets page, specify how much data to hold out for testing. Остаток всегда используется для обучения модели.The remainder is always used for training the model.

    Значение по умолчанию — 30 % для проверочных данных и 70 % для обучения.The default setting is 30% testing data and 70% training data.

  6. На Готово странице, введите описательное имя для набора данных и модели и задать следующие параметры, определяющие, как работают с готовой моделью:On the Finish page, provide a descriptive name for your data set and model, and set the following options that control how you work with the finished model:

    • Обзор модели.Browse model. При выборе этого параметра, как можно скорее мастер завершении обработки модели, открывается Обзор окно помогут вам изучить результаты.When this option is selected, as soon as the wizard finishes processing the model, it opens a Browse window to help you explore the results. Содержимое средства просмотра зависит от типа создаваемой модели.The contents of the viewer depend on the type of model you built. Дополнительные сведения см. в разделе просмотр модели кластеризации.For more information, see Browsing a Clustering Model.
    • Включение детализации.Enable drillthrough. Выберите этот параметр, чтобы просмотреть базовые данные из созданной модели.Select this option to view the underlying data from the finished model. Этот параметр доступен только для модели «дерево принятия решений».This option is only available if you build a Decision Tree model.
    • Использовать временную модель.Use temporary model. Если выбрать этот параметр, модель не будет сохранена на сервере.If you select this option, the model will not be saved to the server. Временные модели удаляются при закрытии Excel.Temporary models are deleted when you close Excel.

Дополнительные сведения о моделях кластеризацииMore about Clustering Models

Можно изменить алгоритм кластеризации, используемый этим мастером, нажав кнопку Дополнительно и с помощью параметры алгоритма диалоговое окно.You can change the clustering algorithm used by this wizard by clicking Advanced and using the Algorithm Parameters dialog box.

Алгоритм кластеризации Майкрософт предоставляет следующие методы кластеризации:The Microsoft Clustering algorithm provides these clustering methods:

  • К-средние, масштабируемые и не масштабируемые.K-means – scalable or non-scaling.
  • Максимизация ожиданий (EM), масштабируемая и не масштабируемая.Expectation Maximization (EM) – scalable or non-scaling.

Также можно использовать параметр CLUSTER_SEED для контроля начального значения и обеспечения того, что повторяющиеся модели, использующие один и тот же набор данных, будут давать одинаковые результаты.You can also use the CLUSTER_SEED parameter to control the starting value and ensure that repeated models using the same data set have the same results.

ТребованияRequirements

Чтобы использовать мастер кластеризации, необходимо установить соединение с базой данных служб Службы Analysis ServicesAnalysis Services.

To use the Cluster wizard, you must be connected to a Службы Analysis ServicesAnalysis Services database. Дополнительные сведения см. в разделе подключение к данным источника (клиент интеллектуального анализа данных для Excel).

For more information, see Connect to Source Data (Data Mining Client for Excel).

См. такжеSee Also

Создание модели интеллектуального анализа данных Creating a Data Mining Model
Поиск категорий (средства анализа таблиц для Excel)Detect Categories (Table Analysis Tools for Excel)

Источник: https://docs.microsoft.com/ru-ru/sql/analysis-services/cluster-wizard-data-mining-add-ins-for-excel?view=sql-server-2014

Условное форматирование в Excel. Пример анализа данных

Надстройка «Анализ данных» в Экселе

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

Используйте пошаговые руководства:

Как это сделать? Очень просто, в Excel имеется набор инструментов, который позволяет выполнить это быстро и просто – буквально за пару-тройку щелчков мыши. Первый и самый простой из таких наборов – это условное форматирование* (речь идет об Excel 2007 года и позже). Давайте разберем несколько ситуаций, когда нам пригодятся условные форматы.

Ячейки с определенными значениями

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

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

Для решения такой задачи Excel в Условных форматах есть замечательная опция – Правила выделения ячеек. На рисунке ниже показано, что Условное форматирование в Excel находится на вкладке .

Там же указаны основные разделы быстрого доступа к настройкам.

Итак, чтобы увидеть ячейки более 150 тысяч, достаточно их выделить, перейти: → Условное форматирование → Правила выделения ячеек → Больше

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

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

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

Быстрый анализ с помощью условного форматирования в Excel

Работая с большими таблицами, мы – финансисты – не всегда их можем «схватить налету». Что тогда говорить о руководителях – нефинансистах, которые не привыкли «сканировать» оборотки и числовые ряды?
Ниже пример простой таблички. Глядя на нее не очень-то понятно, когда было хорошо, когда плохо и по каким подразделениям.

Давайте попробуем разобраться с этой таблицей при помощи Условного форматирования в Excel: Выделим ячейки с цифрами без итогов (чтобы исходные данные на фоне итогов не смотрелись заниженными) → → Условное форматирование (далее УФ) → Цветовые шкалы → Самое первое правило: чем зеленей, тем больше, чем краснее – меньше.

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

Теперь, можно проанализировать структуру продаж по направлениям (данные заранее отсортированы от большего к меньшему). → УФ → Гистограммы → Выбираем любую понравившуюся. Я выбрал синюю.

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

Нужно будет зайти в управление правилами и минимум указать числом «ноль». С заливкой ничего не поделать. Теперь строка итогов. Ее мы разметим с помощью значков (их хорошо использовать для kpi-панелей).

Путь тот же: Выделяем итоги по месяцам (без полугодия) → УФ → Наборы значков → любой подходящий, я выбрал 5 цветных стрелок.

По умолчанию это правило действует так: Excel находит разницу между максимальным и минимальным значением, делит его на количество стрелок (в нашем случае на 5) и затем, значениям меньше 1/5 присваивает красную стрелку, направленную вниз, далее желтая стрелка по диагонали вниз и вправо и т.д.
Так, финансисты и экономисты за 3 – 5 кликов мышкой могут обрабатывать и анализировать большие объемы информации с помощью Excel. Делать свои отчеты интуитивно понятными и наглядными.

* – условное форматирование в  Excel – 2003 года и старше – это окрашивание ячеек в зависимости от значений или формул (условий). Начиная с Excel 2007 года этот инструмент существенно доработан, условное форматирование в Excel 2010 усовершенствовано еще больше.
 

Источник: https://www.fd.ru/articles/50213-excel-dlya-finansista-kak-provesti-bystryy-analiz-dannyh-v-yacheykah-s-pomoshchyu

Обзор встроенных средств Excel для статистического анализа данных

Надстройка «Анализ данных» в Экселе

Основными средствами анализа статистических данных в Excel являются статистические процедуры надстройки Пакет анализа (Analysis ToolРак) и статистические функции библиотеки встроенных функций.

Основные сведения обо всех этих средствах имеются в электронной справочной системе Excel.

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

Некоторые из этих описаний не очень понятны, в них имеются неточности, а подчас и просто ошибки (это относится как к англоязычному оригиналу, так и к русскому переводу). Эти недостатки с завидным постоянством повторяются и во многих пособиях по Excel.

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

Статистические процедуры Пакета анализа

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

В Пакет анализа входят следующие статистические процедуры:

  1. генерация случайных чисел (Random number generation);
  2. выборка (Sampling);
  3. гистограмма (Histogram);
  4. описательная статистика (Descriptive statistics);
  5. ранги персентиль (Rank and percentile);
  6. двухвыборочный z-тест для средних (z-Test: Two Sample for Means);
  7. двухвыборочный t-тест для средних с одинаковыми дисперсиями (t-Test: Two-Sample Assuming Equal Variances);
  8. двухвыборочный t-тест для средних с различными дисперсиями (t-Test: Two-Sample Assuming Unequal Variances);
  9. парный двухвыборочный t-тест для средних (t-Test: Paired Two Sample for Means);
  10. двухвыборочный F-тест да я дисперсий (F-Test: Two Sample for Variances);
  11. коварнация (Covariance);
  12. корреляция (Correlation);
  13. рецессия (Regression);
  14. однофакторный дисперсионный анализ (ANOVA: Single Factor);
  15. двухфакторный дисперсионный анализ без повторений (ANOVA: Two Factor Without Replication);
  16. двухфакторный дисперсионный анализ с повторениями (ANOVA: Two Factor With Replication);
  17. скользящее среднее (Moving Average);
  18. экспоненциальное сглаживание (Exponential Smoothing);
  19. анализ Фурье (Fourier Analysis).

Для доступа к процедурам Пакета анализа необходимо в меню Сервис (Tools) щелкнуть указателем мыши на строке Анализ данных (Data Analysis). Откроется диалоговое окно с соответствующим названием, в котором перечислены процедуры статистического анализа данных (рис. 1).

Рис.1. Диалоговое окно Анализ данных

Для того чтобы запустить в работу нужную статистическую процедуру, нужно выделить ее указателем мыши и щелкнуть на кнопке ОК. На экране появится диалоговое окно вызванной процедуры. На рис. 2 для примера показано диалоговое окно процедуры Описательная статистика (Descriptive statistics).

Рис.2. Диалоговое окно процедуры Описательная статистика

Диалоговое окно каждой процедуры содержит элементы управления: поля ввода, раскрывающиеся списки, переключатели, флажки и т. п. Эти элементы позволяют задать нужные параметры используемой процедуры.

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

Другие элементы управления присутствуют в диалоговых окнах почти всех статистических процедур.

К числу общих для большинства процедур элементов управления относятся:

  • поле ввода Входной интервал (Input Range). В это поле вводится ссылка на диапазон, содержащий статистические данные, подлежащие обработке. Входной диапазон может быть столбцом пли группой столбцов (строкой или группой строк);
  • переключатель Группирование (Grouped By). В том случае, когда входной диапазон представляет собой столбец или группу столбцов, переключатель устанавливается в положение по столбцам (Columns). Если же входной диапазон представляет собой строку или группу строк, то переключатель устанавливается в положение по строкам (Rows). Более точным названием этого переключателя было бы название Расположение;
  • флажок Метки (Labels in First Row). Флажок устанавливается в тех случаях, когда первая строка (первый столбец) входного диапазона содержит заголовки. Если такие заголовки отсутствуют, флажок Метки не устанавливают. При этом Excel автоматически создает и выводит на экран стандартные названия для данных выходного диапазона (Столбец1, Столбец2,… или Строка 1. Строка2,…);
  • переключатели Выходной интервал/Новый рабочий лист/Новая книга (Output Range/New Worksheet/New Workbook). Эти переключатели определяют место вывода таблицы, содержащей результаты реализации статистической процедуры. В группе может быть выбран только одни переключатель.

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

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

В ответ на это сообщение пользователь должен разрешить удаление старых данных и вывод на их место новых.

В положении Новый рабочий лист открывается новый лист рабочей книги. На этот лист, начиная с ячейки А1, и выводится таблица результатов решения.

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

На первый лист этой новой книги, начиная с ячейки А1, выводится таблица результатов решения.

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

Эффективным и очень удобным в использовании средством парного регрессионного анализа и анализа временных рядов является процедура Добавить линию тренда (Add Trendline), входящая в комплекс графических средств Excel.

Статистические функции библиотеки встроенных функций Excel

Табличный процессор Excel имеет библиотеку встроенных функции рабочего листа (Worksheet function). Одним из разделов этой библиотеки является раздел Статистические функции. В этот раздел входят 83 функции, предназначенные для решения некоторых наиболее востребованных задач теории вероятностей и математической статистики.

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

Когда в качестве какого-либо аргумента встроенной статистической функции введен текст, функция выдает сообщение об ошибке #ЗНАЧ! (#VALUE!).

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

Никакие сообщения об этом «несанкционированном округлении» на экран не выводятся.

Источник: http://Excel2010.ru/obzor-vstroennyx-sredstv-excel-dlya-statisticheskogo-analiza-dannyx.html

Как включить анализ данных в Excel 2010, 2007, 2013

Надстройка «Анализ данных» в Экселе

При выполнении сложных аналитических задач по статистике (к примеру, корреляционного и дисперсионног…

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

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

Выясним, как включить анализ данных в Excel 2007, 2010, 2013.

Для Excel 2010, 2013

Рассмотрим анализ данных в Excel: как включить и чем будет отличаться процедура активации для других версий. В большинстве вариантов программы процедура выполняется одинаково. Поэтому последовательность действий, изложенная в разделе, подходит для большинства версий, в том числе для выпусков 2013 и 2016 годов.

Включение блока инструментов

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

  1. зайдите во вкладку «Файл», расположенную в верхней части ленты интерфейса;
  2. с левой стороны открывающегося меню найдите раздел «Параметры Эксель» и кликните по нему;
  3. просмотрите левую часть окошка, откройте категорию надстроек (вторая снизу в списке), выберите соответствующий пункт;
  4. в выпавшем диалоговом меню найдите пункт «Управление», кликните по нему мышью;
  5. клик вызовет на экран диалоговое окно, выберите раздел надстроек, если выставлено значение, отличное от «Надстройки Excel», поменяйте его на обозначенное;
  6. нажмите на экранную кнопку «Перейти» в разделе надстроек. В правой части выпадет список надстроек, которые устанавливает программа.

Поиск пакета в надстройках Excel

Активация

Рассмотрим, как активировать аналитические функции, предоставляемые надстройкой пакета:

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

Выбор нужной надстройки

Запуск функций группы «Анализ данных»

Аналитический пакет оперирует большим набором инструментов, оптимизирующих решение статистических задач. Некоторые из числа:

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

Расположение функции «Анализ данных» на вкладке «Данные»

Чтобы применить ту или иную опцию, действуют по нижеприведенному алгоритму:

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

Как включить анализ данных в Excel 2010

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

Для Excel 2007

Алгоритм, как включить анализ данных в Excel 2007, отличается от остальных тем, что в самом начале (для выхода на параметры Excel) вместо кнопки «Файл» пользователь нажимает четырехцветный символ Microsoft Office. В остальном же последовательность операций идентична приведенной для других версий.

Анализ данных в Excel 2007: как включить?

Источник: https://FreeSoft.ru/blog/kak-vklyuchit-analiz-dannykh-v-excel-2010-2007-2013

Анализ данных в Excel с примерами отчетов скачать

Надстройка «Анализ данных» в Экселе

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

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

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

  1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
  2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
  3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
  4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

  • группировка данных;
  • консолидация данных (объединение нескольких наборов данных);
  • сортировка и фильтрация (изменение порядка строк по заданному параметру);
  • работа со сводными таблицами;
  • получение промежуточных итогов (часто требуется при работе со списками);
  • условное форматирование;
  • графиками и диаграммами.

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



Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.

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

  1. Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
  2. Откроется диалоговое окно «Создание таблицы».
  3. Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.

К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).

Составить отчет можно с помощью «Сводной таблицы».

  1. Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» – «Таблицы» – «Сводная таблица»).
  2. В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
  3. Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
  4. Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.

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

Анализ «Что-если» в Excel: «Таблица данных»

Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» – «Таблица данных».

Важные условия:

  • данные должны находиться в одном столбце или одной строке;
  • формула ссылается на одну входную ячейку.

Процедура создания «Таблицы данных»:

  1. Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
  2. Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
  3. В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.

Анализ предприятия в Excel: примеры

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

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

Источник: https://exceltable.com/vozmojnosti-excel/analiz-dannyh-v-excel

Анализ данных и их оптимизация в Excel

Надстройка «Анализ данных» в Экселе

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

Формулы и функции в Excel автоматически пересчитывают результат при изменении содержимого ячеек, на которые имеются ссылки в данной формуле или функции. Другими словами, можно отвечать на вопросы типа «что-если». Например, при анализе финансовой функции ПЛТ ответить на вопрос, что будет, если первый взнос при получении ипотечной ссуды будет составлять не 20% от цены, а 15%. 

Итак, проиллюстрируем проведение анализа данных «что-если» на примере работы функции ПЛТ, которая вычисляет величину выплаты по ссуде на основе постоянных выплат и постоянной процентной ставки. 

Вызов функции имеет вид: ПЛТ (ставка;кпер;пс;бс;тип) 

Ставка –  процентная ставка по ссуде. 

Кпер – общее число выплат по ссуде. 

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

Бс – значение будущей стоимости, т. е. желаемого остатка средств после последней выплаты. Если этот аргумент опущен, предполагается, что он равен 0 (например, значение «бс» для займа равно 0). 

Тип – число 0 (ноль) или 1, обозначающее, когда должна производиться выплата. 

Рассмотрим пример использования функции ПЛТ в Exceel.

Итак, требуется определить ежемесячные выплаты по займу в 20 000 руб., взятому на 16 месяцев под 11% годовых. 

Для решения задачи выделяем ячейку на рабочем листе Excel (в нашел случаи ячейка А1) и в строку формул вводим следующее выражение: =ПЛТ(11%/12; 16; 20000) (Рис.1.1)

Рис. 1.1 –  Ввод формулы Excel. 

Нажав на клавишу  Enter   , мы получаем величину ежемесячных выплат по ссуде, которая составит -1350 руб. Рис.1.2

Рис. 1.2 – Величина ежемесячной выплаты по ссуде.

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

Другой подход к вычислению функции ПЛТ методом “что если” в Excel проиллюстрирован на Рис. 1.3. Функция ПЛТ определена в ячейке D7, а значения аргументов записаны в ячейках D2, D3 и D4.

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

3 мы вводим не конкретное значение аргумента, а ссылку ни соответствующую ячейку.  

Рис. 1.3 – Пример расчета Excel, в котором исходные данные в отдельные ячейки 

При изменении любых значений на рис.3 результаты расчета автоматически обновляются в разделе Результат расчета.  

Вывод: Рассмотренный выше примеры показывают, что размещение исходных данных в отдельные ячейки упрощает анализ зависимости выходного результата от изменения исходных данных с использованием анализа данных “Что если” в Exceel. 

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

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

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

Из расчетной части рис.1.3  видно, что при заданных исходных данных требуется ежемесячно выплачивать по 1350 руб. для погашения займа. Предположим, что по каким-то причинам кредитор имеется возможность выплачивать не более 1200 руб. в месяц. Спрашивается, какую максимальную величину ссуды может он запросить, если все прочие условия сохраняются? 

Для решения этой задачи выберем команду Данные > Анализ «что если» > Подбор параметра (рис. 2.1). В верхнем поле этого окна указывается ссылка на ячейку D7, в которой устанавливается желаемый результат (в нашем случае – это -1200 руб). В нижнее поле диалогового окна вставляется ссылка на ячейку, в которой хранится значение искомого параметра, т.е. D4.

Рис. 2.1 – Диалоговое окно Подбор параметра в Excel

При нажатии клавиши ОК мы получим максимальную сумму займа, при условии выплаты ежемесячно 1 200 руб. Рис.2.2

Рис. 2.2 – Максимальная величина займа 17 783 руб. 

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

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

Таблицы подстановки для одной переменной.  

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

Рассмотрим способ построения так называемой таблицы подстановки для одной переменной, используя приведенный выше пример вычисления функции ПЛТ. 

Для построения таблицы подстановки необходимо подготовить исходные данные рис.3.1

Рис. 3.1 – Подготовка исходных данных для построения таблицы подстановки Excel

В ячейке G3 этой таблицы определена точно такая же формула, как и в ячейке D7. Первый столбец таблицы подстановки заполнен значениями аргумента функции ПЛТ, в зависимости от которого требуется проанализировать поведение финансовой функции (в нашем случае от 11 до 15%). 

Чтобы получить соответствующие значения функции во втором столбце, нужно выделить диапазон ячеек — F3:G7, и после этого выполнить команду меню Данные > Анализ «что если» > Таблица данных… . В результате появляется диалоговое окно этой команды (рис. 3.2). 

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

Рис. 3.2. – Диалоговое окно Таблица подстановки в Excel

После щелчка на кнопке  ОК   столбец результатов таблицы подстановки будет заполнен (рис. 3.3).

Рис.3.3. Таблица подстановки для одной переменной в Excel

Таблица подстановки для двух переменных в Excel.  

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

Поставим задачу проследить характер изменения функции ПЛТ в зависимости от изменения годовой процентной ставки и срока погашения ссуды.  

Для начала, подготовить исходные данные на рабочем листе, как это показано на рис. 3.4 

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

Рис. 3.4 –  Подготовка исходных данных для построения таблицы подстановки Excel

Чтобы получить значения функции в таблице, выделяем диапазон ячеек F2:J7, который содержит исходные значения процентных ставок, исходные значения срока погашения ссуды и расчетную функцию. После этого нужно выполнить команду меню Данные > Анализ «что если» > Таблица подстановки. В результате появится диалоговое окно (рис. 3.5).

Рис. 3.5 Диалоговое окно Excel Таблица подстановки 

Это окно служит для задания абсолютных адресов ячеек, на которые ссылается расчетная функция. После щелчка на кнопке  ОК  столбец результатов таблицы подстановки будет заполнен (рис.3.6).

Рис. 3.6 Расчетные значения таблицы подстановки Excelдля двух переменных 

Вывод: С помощью таблицы подстановки выявляются характерные тенденции поведения функции в зависимости от изменения определенных параметров или аргументов. 

Проведение графического анализа в Excel. 

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

На рис. 3.7 и 3.8 представлены диаграммы, построенные на базе таблиц подстановки для одной-двух переменных соответственно. Так, для построения диаграммы для двух переменных выделим диапазон ячеек F3:J7 и выберем тип диаграммы «точечная». Затем следует отредактировать полученную диаграмму.  

Ежемесячные выплаты по ссуде

Рис. 3.7 Диаграмма excel, построенная на основе диапазона ячеек F3:G7 таблицы подстановки для одной переменной (см. рис. 3.3)

Поиск решения в Exceel

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

Средство Поиск решения — это надстройка Excel. Для ее подключения следует выполнить команду меню Сервис > Надстройки. В появившемся диалоговом окне Надстройки нужно установить флажок опции Поиск решения. 

Характерные особенности задач, для решения которых предназначено данное средство, заключаются в следующем: 

имеется единственная цель, например максимизация прибыли, минимизация расходов и т.п.; 

имеются ограничения, выраженные в виде неравенств; 

имеются переменные, значения которых влияют на ограничения и оптимизируемую величину. 

Правильная формулировка ограничений — самая ответственная часть описания модели для поиска решения. Следует особенно внимательно следить за тем, чтобы задавать все объективно существующие ограничения. Неполнота описания ограничений приводит к неправильному решению. 

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

Чтобы исключить использование общих более медленных методов для решения линейных задач, следует установить параметр Линейная модель в окне Параметры поиска решения. 

Решение задачи оптимизации. 

 Для пояснения принципа работы средства Поиск решения рассмотрим пример, используя данные таблицы на рис. 4.1.

Рис. 4.1 –  Таблица Excel для определения количества товаров, приносящих максимальную прибыль

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

Ячейка (Е7), в которую помещается ответ, называется целевой. Целевая ячейка содержит формулу, результат которой зависит от значений, содержащихся в других ячейках, называемых изменяемыми.  

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

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

общее число производимых товаров за отчетный период должно составлять ровно 1000 шт.; 

товар С пользуется наименьшим спросом, поэтому, как показал опыт, удается реализовать товар этого вида не более 140 шт.; 

на товары вида A, B, D имеются заказы соответственно на 50, 100 и 200 шт., которые необходимо выполнить. 

Для реализации процедуры поиска решения необходимо выполнить следующие действия. 

Ввести исходные данные, как это показано на рис. 4.1. 

  • Выполнить команду меню Сервис > Поиск решения, чтобы вызвать диалоговое окно Поиск решения (рис. 4.2) 
  • Установить курсор в поле Установить целевую ячейку диалогового окна и щелкнуть мышкой на целевой ячейке Е7 (рис. 4.2). 
  • Установить курсор в поле Изменяя ячейки диалогового окна и выделить диапазон изменяемых ячеек С3:С6. 
  • Установить курсор в поле Ограничения и щелкнуть на кнопке  Добавить . В появившееся диалоговое окно, показанное на рис. 4.3, вводить поочередно все ограничения (рис. 4.4). 
  • Щелкнуть на кнопке Выполнить диалогового окна Поиск решения. 

Результат поиска решения представлен на рис. 4.5. 

Рис. 4.2  – Диалоговое окно Поиск решений в Excel 

Рис 4.3 – Диалоговое отношение Добавление ограничений Excel

Рис. 4.4. – Введение ограничения Excel

После того как найдем оптимальное решение, мы можем выбрать одну из следующих возможностей: 

1) сохранить найденное решение; 

2) восстановить исходные значения в изменяемых ячейках; 

3) создать отчеты о процедуре поиска решения; 

4) щелкнуть на кнопке  Сохранить сценарий. Сохраненный сценарий может быть использован в средстве Диспетчер сценариев. 

Большинство задач, решаемых с помощью электронной таблицы Excel, предполагают нахождение искомого результата по известным исходным данным. Но в Excel есть инструменты, позволяющие решить и обратную задачу, подобрать исходные данные для получения желаемого результата. Одним из таких инструментов является Поиск решения, который особенно удобен для решения так называемых “задач оптимизации”.

Источник: http://ya-znau.ru/znaniya/zn/217

Поделиться:
Нет комментариев

    Добавить комментарий

    Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.