Лекция4. Технология работы в электронной таблице.

Обобщенная технология работы в электронной таблице показана на рисунке 16.

		Рисунок 16.

 

 

 

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

		Рисунок 17

 

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

При работе с электронными таблицами часто возникает необходимость их объединения. Инструментами для объединения электронных таблиц являются:

· организация межтабличных связей

· консолидация электронных таблиц и ее частей (консолидация по расположению, консолидация по категориям, консолидация внешних файлов, сводные таблицы)

· объединение файлов.

В Excel предусмотрена возможность работы с таблицей как с базой данных. Для этого имеется группа команд:

· данные

· фильтр

· сортировка

· промежуточные итоги

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

Рассмотрим варианты задач, решаемы с помощью моделирования.

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

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

Команда Подбор параметра – это средство анализа таблицы, при котором значения ячейки-параметра изменяются таким образом, чтобы число в целевой ячейке стало равно заданному.

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

Для этого необходимо построить таблицу, приведенную на рисунке 18.

Таблица показана в режиме просмотра формул.

 

 

Рисунок 18.

Выполним команду Подбор параметра.

 

Рисунок 19.

Получим решение в основном режиме, показанное на рисунке 20.

 

Рисунок 20.

Решение поставленной задачи находится в ячейке В3. Это означает, что Вы можете взять деньги в банке под 7,02%. В ячейках С3, D3 приведены другие варианты исходных данных.

 Рассмотрим функцию в ячейках В4, С4 и D4. Это – финансовая функция ПЛТ (платеж). Выражение В3/12 соответствует ставке за месяц, В2 указывает на количество периодов, а В1 равно первоначальной стоимости, то есть сумма займа.

Различные виды анализа данных, содержащихся в исходной таблице, можно проводить с использованием встроенных функций и процедур. Мы только что познакомились с строенной финансовой функцией ПЛТ. Использование других финансовых функций позволяет оценку эффективности капитальных вложений, рассчитать стоимость ценных бумаг, размер амортизационных отчислений и т. д.

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

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

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

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

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

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

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

· показывать уравнение на диаграмме

· показать отклонение R² на два шага и т.д.

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

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

В Excel имеется команда Поиск решения, в основе которой заложен метод математического линейного программирования.

Используя эту команду, можно решать:

· задачу определения оптимального плана выпуска продукции при ограниченных ресурсах

· задачу определения штатного расписания организации

· транспортную задачу и многие другие.