Анализ и оптимизация. Прогнозирование и регрессионный анализ

 

1. Изучить теоретический материал.
2. Произвести вычисления:

Microsoft Excel позволяет заполнить ячейки рядом значений, соответствующих простой линейной или экспоненциальной зависимости с помощью маркера автозаполнения

1. Прогнозирование с помощью функции Предсказания

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

Синтаксис

ПРЕДСКАЗ (x;известные_значения_y;известные_значения_x)

X - это точка данных, для которой предсказывается значение.

Известные_значения_y - это зависимый массив или интервал данных.

Известные_значения_x - это независимый массив или интервал данных.

Пример. Известны данные о продажах за полгода

Воспользуемся функцией Предсказание

Прогнозирование линейной зависимости с помощью функции ТЕНДЕНЦИЯ.

Возвращает значения в соответствии с линейным трендом. Аппроксимирует прямой линией (по методу наименьших квадратов) массивы известные_значения_y и известные_значения_x. Возвращает значения y, в соответствии с этой прямой для заданного массива новые_значения_x.

Синтаксис ТЕНДЕНЦИЯ (известные_значения_y;известные_значения_x;новые_значения_x;конст)

Следует ли ожидать роста продаж или последует спад и как скоро.

 

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

Перед вводом формулы Тенденция, выделите ячейки G4:L4
Введите ссылки как указано на рисунке, установите курсор в строку Константа
и нажмите <Ctrl+Shift+Enter> для ввода в массив

Лабораторная работа 7

Частотный анализ

1. Изучить теоретический материал.
2. Произвести вычисления:

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

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

Заполните данными рабочий лист электронной таблицы, как показано ниже.

Используя функцию ЧАСТОТА(данные; интервалы), где данные - это множество значений блока A3:D10, а интервалы - блока E3:E9, определим число людей в группах.

Поскольку этих групп на одну больше числа интервалов, то:

· выделите блок F3:F10;

· наберите формулу

=ЧАСТОТА(A3:D10;E3:E9);

· введите ее, нажав комбинацию клавиш Ctrl+Shift+Enter.

Результат анализа будет следующим:

Лабораторная работа 8

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

1.Изучить теоретический материал.

Произвести вычисления.

Построим модель решения этой задачи. За основу возьмем оклад уборщицы, а остальные оклады будем вычислять, исходя из него: во столько-то раз или на столько-то больше. Говоря математическим языком, каждый оклад является линейной функцией от оклада уборщицы: Ai*С+Вi, где С - оклад уборщицы; Аi и Вi – коэффициенты

Задание: Составить таблицу по образцу

Серым шрифтом закрашены вычисляемые ячейки

· Премия вычисляется –если коэффициент выше 1,5 прибыль за текущий месяц *5%

· Зарплата =мин з/пл*коэфф+ премия

· Суммарная зар/пл=кол.сотр*зарплату.

	Штатное расписание
№ п/п	Должность	Коэфф	Премия	Зарплата	Кол сотрудников	суммарная з/пл
	уборщица
	кассир	1,3
	продавец	1,6
	консультант	1,4
	зав. Отделом	1,7
	зав. Секцией
			Итого		Итого
Прибыль за текущий месяц
Минимальная оплата

 

Определите оклад уборщицы так, чтобы расчетный фонд по заплате был равен был равен заданному: (70 000 рублей)

o активизируйте команду Подбор параметра из меню Сервис;

o в поле "Установить в ячейке" появившегося окна введите ссылку на ячейку (71500)итогопо сумм.з/пл;

o в поле "Значение" наберите искомый результат 70000;

o в поле "изменяя значение ячейки" введите ссылку на изменяемую ячейку (минимальная оплата) и щелкните на кнопке ОК.

	Штатное расписание
№ п/п	Должность	Коэфф	Премия	Зарплата	Кол. сотрудн.	суммарная з/пл
	уборщица			4883,721		9767,442
	кассир	1,3		6348,837		12697,67
	продавец	1,6		9563,953		19127,91
	консультант	1,4		6837,209		6837,209
	зав. Отделом	1,7		10052,33		10052,33
	зав. Секцией			11517,44		11517,44
			Итого	49203,49	Итого
Прибыль за текущий месяц
Минимальная оплата	4883,721

Лабораторная работа 9

Условное форматирование

1.Изучить теоретический материал.

Произвести вычисления

Задание 1.Поставить курсор на любую заполненную ячейку.

1. Нажать клавиатурное сочетание Ctrl+A (выделить всё).
2. Присвоить выделенным ячейкам условное форматирование:
• Условие 1. Значения > 0: Цвет фона – желтый, цвет букв – тоже желтый.
• Условие 2. Значения = 0: Цвет фона – зеленый, цвет букв – тоже зеленый.
• Условие 3. Значения < 0: Цвет фона – черный, цвет букв – тоже черный.
3. Сохранить файл.

Меню Формат→Условное форматирование. (Fomat→Conditional formating...).

Данный термин (Conditional formating) был не совсем корректно переведен с английского языка. Было бы правильнее говорить о форматировании по условию.

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

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

Диалог выглядит как показано на рисунке.

Первый список позволяет указать, относительно чего будет формироваться условие: значений (Cell value is) или формул (Formula is).

В случае значений, нужно указать одно из условий сравнения

Для формулы –

 

Некоторые из них далее предполагают ввод двух (например, см. выше: между 5 и 10), а другие – одного значения (например, больше 5).

Кнопка справа от поля ввода () переводит программу в режим выбора координат ячеек при помощи мыши: Помеченные ячейки выделяются пунктиром, а в поле вводятся правильные координаты:

 

Разумеется, что координаты можно и набрать, но при этом возрастает вероятность ошибки.

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

Если условие представляет собой текст, то его можно ввести не задумываясь. Excel сам превратит его в нужный вид. Примером может служить наш электронный журнал, где ячейка с пропусками помечается светло-коричневым фоном. В поле значения условия записано ="н".

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

Кнопка А также >> (Add >>) позволит добавить дополнительные условия. К сожалению, их количество ограничено только тремя.

Ошибки в задании условий

1. Условие 1: от 10 до 1000.
Условие 2: равно 200. Оно не будет выполняться: уже сработало условие 1.
Условие 3: равно 500. Оно также не выполняется: уже сработало условие 1.
Выход: первое условие надо сделать третьим. Как? Удалить условие 1 и создать условие 3 с таким же содержанием. Вряд ли такая работа кому-либо понравится.
Вывод: прежде чем задавать условия, необходимо продумать, не перекроется ли последующее предыдущим.

2. Условие задается, а форматирование не указывается. Надо внимательно смотреть на диалог, оценивая будущий внешний вид ячеек в образце. Слова «Формат не задан» могут привлечь внимание)

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

4. Условия, разбивающие значения на диапазоны пересекаются или наоборот, оставляют «дыры».
Пример: надо выделить отрицательные и неотрицательные числа. Условия <0 и >0 оставляют ноль вне рассмотрения. Второе условие должно было быть «не меньше 0» (то есть ≥0).

Редактирование условий

Выделяем ячейки, в которых нужно изменить условие форматирования и меняем его. Казалось бы всё очень просто, НО!

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

Чтобы полностью удалить условное форматирование, можно (предварительно выделив нужные ячейки) зайти в основной диалог и нажать кнопку Удалить... (Delete...). Появится запрос:

После пометки необходимых условий и подтверждения в двух диалогах (кнопкой OK), условия будут удалены.

Контрольные вопросы

1. Как осуществляется запуск EXEL и выход из него (указать все известные способы)?

2. Как называется документ, создаваемый программой MS EXEL?

3. Сколько листов по умолчанию содержит созданная рабочая книга?

4. Каково максимально допустимое количество рабочих листов в рабочей книге?

5. Как по умолчанию располагаются в ячейке вводимые текстовые данные?

6. Что отобразится на экране при вводе слова «компьютеризация» в ячейку шириной 9 символов, при условии, что соседняя клетка справа занята?

7. Как по умолчанию располагаются в ячейке вводимые числовые данные,текстовые данные?

8. Какой символ является разделитель целой и дробной части в числе в Excel

9. В Excel иногда при вводе числа ячейка будет выглядеть так: #####. Как исправить положение?

10. Какие типы данных могут содержать электронные таблицы?

11. Какие данные называют зависимыми, а каких независимыми?

12. По какому признаку программа определяет, что введенные данные являются не значением, а формулой?

13. Что такое формула в электронной таблице и ее типы? Приведите примеры.

14. Что такое функция в электронной таблице и ее типы? Приведите примеры.

15. Как можно "размножить" содержимое ячеек?

16. 11.Как посмотреть и отредактировать формулу, содержащуюся в ячейке?

17. Что такое диапазон и как его выделить?

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

19. Какие способы объединения ячеек вы знаете в электронной таблице?

20. Что такое Мастер функции?

21. Что такое Мастер диаграмм?

 

http://yuschikev.narod.ru/Teoria/Excel2003/Excel5.html