Параметрический анализ в excel как сделать
Обновлено: 07.07.2024
Анализ оптимального решения выполняется на основании тех положений симплекс - метода, которые были рассмотрены на лекции, и начинается после успешного завершения решения задачи, когда на экране появляется диалоговое окно Результат поиска решения. Решение найдено. С помощью этого диалогового окна можно вызвать отчеты трех типов по алгоритму 3.1:
¨ результаты;
¨ устойчивость;
¨ пределы
Алгоритм 3.1. Вызов отчетов анализа.
На экране: диалоговое окно Результат поиска решения. Решение найдено.
1. Курсор на тип вызываемого отчета. Начнем с Отчета по результатам.
2. ОК.
На экране: вызванный отчет на новом листе, на ярлычке которого указано название отчета.
3. Курсор на ярлычок с названием отчета.
4. М1.
На экране: вызванный отчет.
Отчет по результатам
Отчет по результатам состоит из трех таблиц:
¨ Таблица 1 приводит сведения о целевой функции.
В столбце Исходно приведены значения целевой функции до начала вычислений.
¨ Таблица 2 приводит значения искомых переменных, полученные в результата решения задачи.
¨ Таблица 3 показывает результаты оптимального решения для ограничений и граничных условий.
Для Ограничений в графе Формула приведены зависимости, которые были введены в диалоговое окно Поиск решения; в графе Значения приведены величины использованного ресурса; в графе Разница показано количество неиспользованного ресурса. Если ресурс используется полностью, то в графе Состояние указывается связанное; при неполном использовании ресурса в этой графе указывается не связан.
Для граничных условий приводятся аналогичные величины с той лишь разницей, что вместо величины неиспользованного ресурса показана разность между значением переменной в найденном оптимальном решении и заданным для нее граничным условием.
Отчет по устойчивости
Отчет по устойчивости состоит из двух таблиц.
В таблице 1 приводятся следующие значения для переменных:
¨ Результат решения задачи;
¨ нормир. стоимость(нормированая стоимость), т. е. дополнительные двойственные переменные Vj, которые показывают, насколько изменится целевая функция при принудительном включении единицы этой продукции в оптимальное решение;
¨ коэффициенты целевой функции;
¨ предельные значения приращения коэффициентов DCj целевой функции, при которых сохраняется набор переменных, входящих в оптимальное решение.
В таблице 2 приводятся аналогичные значения для ограничений:
¨ величина использованных ресурсов;
¨ теневая цена, т. е. двойственные оценки Zi, которые показывают, как изменится целевая функция при изменении запаса ресурса на единицу;
¨ значения приращения ресурса Dbi, при которых сохраняется оптимальный набор переменных ,входящих в оптимальное решение.
Отчет по пределам
В этом отчете показано, в каких пределах может изменяться выпуск продукции, вошедшей в оптимальное решение , при сохранении структуры оптимального решения:
¨ приводятся значения Xj в оптимальном решении;
¨ приводятся нижние пределы изменения значения Xj.
Кроме этого, в отчете указаны значения целевой функции при выпуске данного типа продукции на нижнем пределе. Так, при значении 730 видно, что
Далее приводятся верхние пределы изменения Xj и значения целевой функции при выпуске продукции, вошедшей в оптимальное решение на верхних пределах.
F = 60 * 10 + 120 * 6 = 1320
Вариантный анализ
Для задач линейного программирования наибольший интерес представляет решение двух задач вариантного анализа:
· параметрического анализа, в ходе которого решаются задачи при различных значениях одного из параметров;
· сравнительный анализ нескольких моделей одной и той же ситуации
Параметрический анализ
Под параметрическим анализом будем понимать решение задачи оптимизации при различных значениях того параметра, который ограничивает улучшение целевой функции. Параметрический анализ будем выполнять для задачи, которая разобрана в лабораторной работе №1 “Решение задач линейного программирования средствами ЭТ Excel” , решая ее при различных значениях имеющихся финансов.
Сравнительный вариантный анализ
Построение нескольких моделей одной и той же ситуации дает возможность провести сравнительный анализ полученных оптимальных решений и выбрать тот вариант, который в данных условиях дает максимальный эффект.
Возможные варианты постановки задачи:
1. Критерий эффективности – максимум прибыли, ограничения – по запасам ресурсов, граничные условия – неотрицательность управляемых переменных.
2. Критерий эффективности – минимум затрат, ограничения и граничные условия те же.
3. Критерий эффективности – максимум продукции в стоимостном выражении, ограничения и граничные условия те же.
4. Критерий эффективности – максимум продукции в натуральном выражении, ограничения и граничные условия те же.
5. Критерий эффективности – максимальное использование имеющихся ресурсов, ограничения и граничные условия те же.
2 пример выполнения Лабораторной работы
2.1 пример проведения параметрического анализа
Алгоритм 3.2. Выполнение параметрических расчетов.
1. Подготовительные работы:
1.1. Составить на бумаге таблицу вариантов
| Вариант | 1 | 2 | 3 | 4 | 5 |
| Финансы |
1.2. Вызвать на экран таблицу с результатом решения задачи
1.3. Удалите результат решения, находящийся в B3:E3.
2. Решение задачи для 1 - го варианта.
2.1. Ввести в ячейку H11 => 50
2.2. Сервис, Поиск решения.
2.3.Выполнить.
На экране: диалоговое окно Результаты поиска решения
2.4. Сохранить сценарий.
2.5. Ввести имя сценария финансы = 50 ( рисунок 3.1)
2.6.ОК
На экране: диалоговое окно Результаты поиска решения
2.7. ОК
На экране: результат решения для данного варианта ( рисунок 3. 2)
3. Решение задачи для последующих вариантов.
3.1. Ввести в H11 значение финансов для следующего варианта.
3.2. Выполнить п.2.2 - 2.7 , при этом в п.2.5 вводить имя сценария, соответствующее значению финансов.
4. Представление результатов решения.
4.1. Сервис, Сценарии.
На экране: диалоговое окно Диспетчер сценариев
4.2. Отчет.
На экране: диалоговое окно Отчет по сценарию
4.3. Структура.
4.4. ОК
На экране: отчет Структура сценария
Внимание .
Сравните данные из структуры сценария со значениями, приведенными на рисунке 3.3. Если числа не совпадают, проверьте какой тип разделителя между целой и дробной частью использует операционная система WINDOWS:
- Установить в окне Разделитель целой и дробной части : "."
Для удобства дальнейшей работы выполним редактирование Итогового сценария.
Алгоритм 3.3. Редактирование Итогового сценария
1. Для размещения на экране всего отчета Итоговый сценарий в окне масштаба назначить 75 %.
2. Скрыть столбцы B и D.
3. Удалить строки 5 и 10.
¨ Прод1 : Прод4 в ячейки С5:С8
¨ Виды ресурсов: трудовые, сырье, финансы в ячейки С10:С12
5. Увеличить ширину столбца С.
6. Для наглядного представления данных на диаграммах:
¨ В дробных значениях Прод1 : Прод4 назначить 2 знака после запятой.
¨ Дробные значения в строках Прибыль, трудовые, сырье и финансы округлить до целых чисел.
7. Убрать примечание.
На экране: Отредактированный итоговый сценарий ( рисунок 3. 3)
Для наглядного представления результатов параметрического анализа на основании отредактированной таблицы построим графики.
Алгоритм 3.4. Построение гистограммы для искомых переменных
1. Выделить С3:I8
2. Построить гистограмму
3. Отформатировать гистограмму ( рисунок 3.4)
Из диаграммы можно сделать выводы:
¨ При различном финансировании в план входит продукция различных видов, однако, ни в один вариант не входит выпуск Прод2. Это объясняется тем, что при высоком потреблении ресурсов прибыль от производства Прод2 ниже, чем от производства других видов продукции.
¨ Для значений финансов 50,150,200 величина выпускаемой продукции является дробной. Такое положение допустимо при планировании выпуска ткани, добычи нефти и т.д.
Алгоритм 3.5. Построение смешанной диаграммы для целевой функции и требуемого сырья
1. Выделить С3:I3, С9:I9, С11:I11.
2. Построить смешанную диаграмму
3. Выполнить форматирование диаграммы.
На экране: диаграмма (рисунок 3.5) , на основании которой можно сделать выводы:
¨ Увеличение финансирования дает увеличение прибыли.
¨ При увеличении финансирования, начиная со 150, происходит уменьшение потребляемого сырья, т.к. выпуск Прод3, Прод4, обеспечивающих увеличение прибыли, требует меньшего потребления сырья.
2.1 пример выполнения сравнительного
Выполним вариантный анализ распределительной задачи.
1 постановка задачи
Составить план выпуска продукции, что бы прибыль была максимальна.
x1 + x2 + x3 + x4 £ 16
6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110
6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100
1. Ввести условие задачи и целевую функцию в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1
2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2
3. Решить задачу по алгоритму 1.3
На экране: рисунок 3.6
4. Сохранить сценарий…
5. Ввести имя МАКС ПРИБ
2 постановка задачи
Критерий эффективности – максимум продукции в натуральном выражении
F = X1 + X2+ X3+ X4 -> max
x1 + x2 + x3 + x4 £ 16
6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110
6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100
1. Ввести условие задачи и целевую функцию в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1
2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2
3. Решить задачу по алгоритму 1.3
4. На экране: рисунок 3.7
5. Сохранить сценарий…
6. Ввести имя МАКС ПРОД
3 постановка задачи
Критерий эффективности – максимальное использование имеющихся ресурсов
F = x1 + x2 + x3 + x4 + 6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 + 6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 -> max
x1 + x2 + x3 + x4 £ 16
6 * x1 + 5 * x2 + 4 * x3 + 3 * x4 £ 110
6 * x1 + 6 * x2 + 10 * x3 + 13 * x4 £ 100
1. Ввести условие задачи в таблицу для ввода условий задачи, руководствуясь алгоритмом 1.1
Ввести зависимости для целевой функции:
2. Назначить целевую функцию, ввести ограничения и граничные условия по алгоритму 1.2
3. Решить задачу по алгоритму 1.3
4. На экране: рисунок 3.8
5. Сохранить сценарий…
6. Ввести имя МАКС РЕСУРС
7. Сервис, Сценарии…
8. Отчет…
Структура
10. ОК. На экране: Структура сценария(рисунок 3.9)
11. Выполните редактирование структуры сценария. На экране: Структура сценария после редактирования (рисунок 3.10).
Задание
1. Откройте файл, созданный при выполнении лабораторной работы №1 “Решение задач линейного программирования средствами ЭТ Excel”
2. Перейдите на лист с решением задачи 1.
Установите начальные значения в ячейки исходных данных.
3. Сервис, Поиск решения…,Выполнить.
4. Создайте отчет по результатам, отчет по устойчивости, отчет по пределам.
5. Дайте ответ на вопросы, пользуясь информацией из отчетов:
5.1. Какие ресурсы являются дефицитными, а какие не дефицитными ?
5.2. Каковы остатки ресурсов ( Yi ) ?
5.3. Определите, как изменится целевая функция при принудительном включении единицы каждого вида продукции в оптимальный план ?
Проверьте на практике правильность своих выводов.
5.4.В каких пределах можно изменять коэффициенты целевой функции, оставляя оптимальный план неизменным ?
Проверьте на практике правильность своих выводов.
5.5. Как изменится целевая функция при изменении запаса дефицитного ресурса на единицу ?
Проверьте на практике правильность своих выводов.
5.6. Определите, во сколько раз ресурс 1 дефицитнее ресурса 3?
5.7. Как перевести изделие из категории убыточных в категорию безразличных ?
5.8. Проверьте обоснованность хранения запасов ресурса, имеющего остатки ?
5.9. Определите значение целевой функция, при X1=0 ?
5.10. Определите значение целевой функция, при X3=0 ?
6. Выполните параметрический анализ по алгоритмам 3.2, 3.3, 3.4.
7. Выполните экономико-математический анализ решения оптимального задачи распределения ресурсов, составленной вами (анализ на чувствительность и вариантный анализ).
ТРЕБОВАНИЯ К ОТЧЕТУ ПО ЛАБОРАТОРНОЙ РАБОТЕ
Отчет должен содержать:
1. Выводы по результатам анализа на чувствительность задачи 1.
2. Выводы по параметрическому анализу задачи 1.
3. Выводы по результатам анализа на чувствительность задачи, составленной вами.
4. Выводы по параметрическому анализу задачи, составленной вами.
5. Выводы по сравнительному вариантному анализу задачи, составленной вами.
Лабораторная работа N4
Общие условия выбора системы дренажа: Система дренажа выбирается в зависимости от характера защищаемого.
Механическое удерживание земляных масс: Механическое удерживание земляных масс на склоне обеспечивают контрфорсными сооружениями различных конструкций.
п.12 . Решение прикладных задач средствами EXCEL .
Процедуры описательной статистики . Параметрический критерий Стьюдента
В Excel для построения выборочных функций распределения используются специальная функция ЧАСТОТА и процедура пакета анализа ГИСТОГРАММА.
1. Функция ЧАСТОТА вычисляет частоты появления случайной величины в интервалах значений и выводит их как массив цифр. Функция задается в качестве формулы массива.
2. Процедура ГИСТОГРАММА используется для вычисления выборочных и интегральных частот попадания данных в указанные интервалы значений. Процедура выводит результаты в виде таблицы и гистограммы.
В мастере функций Excel имеется также ряд специальных функций, предназначенных для вычисления выборочных характеристик. Прежде всего, это функции, характеризующие «центр» распределения.
•Функция СРЗНАЧ вычисляет среднее арифметическое из нескольких массивов (аргументов) чисел. Аргументы число 1, число 2,… — это от 1 до 30 массивов, для которых вычисляется среднее. Например, если ячейки А1-А7 содержат числа 10, 14, 5, 6, 10, 12 и 13, то средним арифметическим СРЗНАЧ ( А1-А7 ) является 10.
• Например, СРГАРМ(10;14;5;6;10;12;13) равняется 8,317.
•Функция МЕДИАНА позволяет получать медиану заданной выборки. Медиана — это элемент выборки, число элементов выборки со значениями больше которого и меньше которого равно. Например, МЕДИАНА (10;14;5;6;10;12;13) равняется 10.
•Функция МОДА вычисляет наиболее часто встречающееся значение в выборке. Например, МОДА (10;14;5;6;10;12,13) равняется 10.
К специальным функциям, вычисляющим выборочные характеристики, характеризующие рассеяние вариант, относятся ДИСП, СТАНДОТКЛОН, ПЕРСЕНТИЛЬ.
•Функция ДИСП позволяет оценить дисперсию по выборочным данным. Например, ДИСП(10;14;5;6;10;12;13) равняется 11,667.
•Функция СТАНДОТКЛОН вычисляет стандартное отклонение. Например, СТАНДОТКЛОН(10;14;5;6;10;12;13) равняется 3,416.
•Функция ПЕРСЕНТИЛЬ позволяет получить квантили заданной выборки. Например, если ячейки А1-А7 содержат числа 10, 14, 5, 6, 10, 12 и 13, то квантиль со значением 0,1 является ПЕРСЕНТИЛЬ (А 1-А7;0,1), равная 5,6.
Форму эмпирического распределения позволяют оценить специальные функции:
•Функция ЭКСЦЕСС вычисляет оценку эксцесса по выборочным данным. Например, ЭКСЦЕСС(10;14;5;6;10;12;13) равняется -1,169.
•Функция СКОС позволяет оценить асимметрию выборочного распределения. Например, СКОС(10;14;5;6;10;12;13) равняется -0,527.
В пакете Excel помимо мастера функций имеется набор более мощных инструментов для работы с несколькими выборками и углубленного анализа данных, называемый Пакет анализа, который может быть использован для решения задач статистической обработки выборочных данных.
Для установки раздела «Анализ данных» в пакете Excel сделайте следующее:
•в меню Сервис выберите команду Надстройки;
•в появившемся списке установите флажок Пакет анализа.
В MS Excel для оценки достоверности отличий по критерию Стьюдента используются специальная функция ТТЕСТ и процедуры пакета анализа.
Функция ТТЕСТ (коэффициент Стьюдента) использует следующие параметры: ТТЕСТ (массив 1; массив 2; хвосты; тип). Здесь:
•массив 1 — это первое множество данных;
•массив2 — это второе множество данных;
•хвосты — число хвостов распределения. Обычно число хвостов равно 2;
•тип — это вид используемого t -теста.
Рассмотрим на примере применение ф ункции ТТЕСТ для оценки статистической значимости различий по исследуемому признаку между двумя выборками.
Изучалось различие в показателях интеллекта студентов первого и пятого курсов технического вуза. Для этого случайным образом были отобраны 12 студентов первого курса и 13 студентов 5 курса, у которых интеллект определялся по одной и той же методике. Были получены следующие результаты:
1 группа - первый курс: 111, 104, 107, 90, 101, 107, 106, 107, 95, 106, 105, 115.
2 группа – пятый курс: 113, 107, 123, 122, 117, 112, 105, 108, 111, 114, 102, 104, 108.
Оценить с помощью критерия Стьюдента достоверность различий между группами.
H – различия между группами не достоверны.
H – различия между группами достоверны.
1. Введите данные: откройте новую рабочую таблицу. Введите в ячейку А1 слово 1 группа - первый курс, затем в ячейки А2-А13 введите показатели интеллекта у студентов первой группы. В ячейку В1 введите слово 2 группа- пятый курс, а в В2-В14 введите показатели интеллекта у студентов второй группы.
3. Поскольку величина вероятности случайного появления анализируемых выборок (0,018563) меньше уровня значимости (р = 0,05), то нулевая гипотеза отклоняется. Следовательно, различия между выборками неслучайные, и средние выборок считаются достоверно отличающимися друг от друга. Поэтому на основании применения критерия Стьюдента можно сделать вывод о том, что различия между группами статистически достоверны .
Проверить с помощью критерия Стьюдента гипотезу о том, что в результате тренинга самооценка конформизма участников возросла.
H – различия между показателями до и после тренинга не достоверны.
H – различия между показателями до и после тренинга достоверны.
1. Введите данные: откройте новую рабочую таблицу. Введите в ячейку С1 слово До тренинга, затем в ячейки С2-С9 — соответствующие значения. В ячейку Д1 введите слова После тренинга, а в Д2-Д9 — значения.
3. Поскольку величина вероятности случайного появления анализируемых выборок (0,047945) меньше пятипроцентного уровня значимости (р = 0,05), нулевая гипотеза отклоняется. Следовательно, различия между выборками неслучайные, и средние выборок считаются достоверно отличающимися друг от друга. Поэтому на основании применения критерия Стьюдента можно сделать вывод о том, что различия между показателями до и после тренинга достоверны (р 0,05).
Итак, при использовании t -критерия выделяют два основных случая. В первом случае его применяют для проверки гипотезы о равенстве средних двух независимых, несвязанных выборок (так называемый двухвыборочный t -критерий). При заполнении диалогового окна ТТЕСТ при этом указывается Тип 3. Во втором случае, когда одна и та же группа объектов порождает числовой материал для проверки гипотез о средних, используется так называемый парный t -критерий (при заполнении диалогового окна ТТЕСТ указывается Тип 1).
Проверялась гипотеза о том, что мужчины агрессивнее женщин. По тесту Басса-Дарки опросили 17 мужчин и 20 женщин. Индексы общей агрессивности для каждого из опрошенных приведены в таблице.
26 , 16 , 19 , 14, 24, 15, 25, 11, 22, 20, 17, 10, 5, 9, 5, 8, 6
20, 17, 13, 19, 22, 8, 10, 7, 10, 15, 10, 5, 8, 5, 6, 5, 5, 8, 10, 10
Члены команды спасателей участвовали в тренинге по повышению стрессоустойчивости. В таблице приведены результаты измерения стрессоустойчивости до тренинга (выборка А) и после тренинга (выборка В).
Можно ли утверждать, что после тренинга стрессоустойчивость испытуемых возросла?
Очень полезной функцией в программе Microsoft Excel является Подбор параметра. Но, далеко не каждый пользователь знает о возможностях данного инструмента. С его помощью, можно подобрать исходное значение, отталкиваясь от конечного результата, которого нужно достичь. Давайте выясним, как можно использовать функцию подбора параметра в Microsoft Excel.
Суть функции
Если упрощенно говорить о сути функции Подбор параметра, то она заключается в том, что пользователь, может вычислить необходимые исходные данные для достижения конкретного результата. Эта функция похожа на инструмент Поиск решения, но является более упрощенным вариантом. Её можно использовать только в одиночных формулах, то есть для вычисления в каждой отдельной ячейке нужно запускать всякий раз данный инструмент заново. Кроме того, функция подбора параметра может оперировать только одним вводным, и одним искомым значением, что говорит о ней, как об инструменте с ограниченным функционалом.
Применение функции на практике
Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.
Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».
После этого, открывается окно подбора параметра. В поле «Установить в ячейке» нужно указать ее адрес, содержащей известные нам конечные данные, под которые мы будем подгонять расчет. В данном случае, это ячейка, где установлена премия работника Николаева. Адрес можно указать вручную, вбив его координаты в соответствующее поле. Если вы затрудняетесь, это сделать, или считаете неудобным, то просто кликните по нужной ячейке, и адрес будет вписан в поле.
В поле «Значение» требуется указать конкретное значение премии. В нашем случае, это будет 6035,68. В поле «Изменяя значения ячейки» вписываем ее адрес, содержащей исходные данные, которые нам нужно рассчитать, то есть сумму зарплаты работника. Это можно сделать теми же способами, о которых мы говорили выше: вбить координаты вручную, или кликнуть по соответствующей ячейке.
Когда все данные окна параметров заполнены, жмем на кнопку «OK».
После этого, совершается расчет, и в ячейки вписываются подобранные значения, о чем сообщает специальное информационное окно.
Подобную операцию можно проделать и для других строк таблицы, если известна величина премии остальных сотрудников предприятия.
Решение уравнений
Кроме того, хотя это и не является профильной возможностью данной функции, её можно использовать для решения уравнений. Правда, инструмент подбора параметра можно с успехом использовать только относительно уравнений с одним неизвестным.
Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.
В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».
Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».
Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.
Изучив инструмент Подбор параметра, мы выяснили, что это довольно простая, но вместе с тем полезная и удобная функция для поиска неизвестного числа. Её можно использовать как для табличных вычислений, так и для решения уравнений с одним неизвестным. Вместе с тем, по функционалу она уступает более мощному инструменту Поиск решения.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
- Откройте файл с таблицей, данные которой надо проанализировать.
- Выделите диапазон данных для анализа.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
2. 3D-карты
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.
Как работать
- Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
- Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
- Выделите диапазон данных для анализа.
- На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
- Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
- Выделите два ряда данных.
- На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
- В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
- Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Подбор параметра
Каждый раз при использовании формулы или функции в Excel Вы собираете исходные значения вместе, чтобы получить результат. Подбор параметра работает наоборот. Он позволяет, опираясь на конечный результат, вычислить исходное значение, которое даст такой результат. Далее мы приведем несколько примеров, чтобы показать, как работает Подбор параметра.
Как использовать Подбор параметра (пример 1):
Представьте, что Вы поступаете в определенное учебное заведение. На данный момент Вами набрано 65 баллов, а необходимо минимум 70 баллов, чтобы пройти отбор. К счастью, есть последнее задание, которое способно повысить количество Ваших баллов. В данной ситуации можно воспользоваться Подбором параметра, чтобы выяснить, какой балл необходимо получить за последнее задание, чтобы поступить в учебное заведение.
На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6) в ячейку B7. После того как Вы примените Подбор параметра к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.
Как использовать Подбор параметра (пример 2):
Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).
Как видно из предыдущего примера, бывают ситуации, которые требуют целое число в качестве результата. Если Подбор параметра выдает десятичное значение, необходимо округлить его в большую или меньшую сторону в зависимости от ситуации.
- Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
- Таблицыданных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:
Читайте также: