prepod

Путь к Файлу: /институт / Информационно командная среда.doc

Ознакомиться или скачать весь учебный материал данного пользователя
Скачиваний:   0
Пользователь:   prepod
Добавлен:   30.12.2015
Размер:   1.1 МБ
СКАЧАТЬ

Информационно-командная среда

Раздел 1. Структура информационно-командной среды

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

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

Как видно из определения, информационно-командная среда состоит из следующих частей:

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

· механизм активизации вычислительного процесса, представляемого в виде установления ряда параметров, необходимых для вычисления, и кнопки запуск некоторого вычислительного процесса, представленного в виде небольшой процедуры (макроса), написанной на встроенном языке программирования;

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

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

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

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

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

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

Структурно информационно-командную среду можно представить следующим образом:

Информационно командная среда

Рисунок 210. Структура информационно-командной среды

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

Первичным элементом информационно-командной среды является информационная часть, которая содержит три ключевых составляющих:

· исходные данные, представленные статическими показателями, которые не изменяются, а также статистическими показателями, описывающие изменяемые с течением времени данные;

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

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

o вычисление по формулам на основе статистических и статических данных;

o вычисление с помощью системы статистического анализа;

o вычисление с помощью системы поиска оптимального решения;

o формирование с помощью алгоритмического метода, использующего технологию макросов.

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

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

· спрогнозировать развитие инфляции на предстоящий год с учетом изменения инфляции за предыдущие годовые периоды, определив зависимость инфляции от различных ее составляющих;

· определить будущую динамику прибыли компании от продажи и использования программного обеспечения;

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

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

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

· определить оптимальное значение экспорта и импорта, чтобы инфляция была минимальной;

· определить цены на продаваемые продукты и услуги, чтобы прибыль компании была максимальной;

· определить стоимость PR-акций, чтобы PR-компания была максимально успешной.

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

Раздел 2. Использование статистических вычислений

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

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

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

Чтобы сделать доступным систему «Пакет анализа…» необходимо с помощью настроек табличного процессора указать необходимость его использования:

Информационно командная среда Информационно командная среда Информационно командная среда

Рисунок 211. Настройка системы «Пакет анализа...»

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

Очень часто требуется заполнить комплекс ячеек некоторыми случайными данными, которые подчиняются определенному закону распределения. Для этого в системе «Пакет анализа…» имеется генератор случайных чисел, который сгенерирует некоторую последовательность данных.

Информационно командная среда

Рисунок 212. Диалоговое окно выбора функции Пакета анализа

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

Информационно командная среда

Рисунок 213. Диалоговое окно настроек генератора случайных чисел

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

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

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

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

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

В результате использования этого закона получается некоторая последовательность значений:

Информационно командная среда Информационно командная среда

Рисунок 214. Результат равномерного распределения

Если возникает необходимость получить набор случайных значений в диапазоне от -1 до 1, то возможно использование другого распределения – нормального. В этом случае необходимо указать дополнительные параметры:

· среднее – некоторое среднее значение, исходя из которого формируются случайные числа;

· стандартное отклонение – это значение, на величину которого случайные числа могут отличаться от среднего значений.

Естественно, что, поскольку случайные числа вычисляются по некоторой математической формуле, то реальные значения могут существенно отходить от максимального и минимального значения. Но количество таких отклонений от нормы настолько мало, что им можно пренебречь. Для получения чисел из диапазона от -1 до 1, необходимо в качестве параметров указать: среднее – 0, стандартное отклонение – 1.

Информационно командная среда Информационно командная среда

Рисунок 215. Результат случайных значений нормального распределения

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

Информационно командная среда

Рисунок 216. Использование функции случайного числа равномерного распределения

Для других вариантов распределений существуют собственные функции:

· нормальное распределение - НОРМРАСП;

· биномиальное распределение - БИНОМРАСП;

· распределение Пуассона - ПУАССОН.

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

Информационно командная среда Информационно командная среда

Рисунок 217. Настройка  базовых статистических показателей

Чтобы получить полный перечень статистических показателей, необходимо указать параметр «Итоговая статистика».

Информационно командная среда

Рисунок 218. Результат выполнения описательной статистики

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

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

· стандартное отклонение, которое показывает максимальное отклонение значений показателя от вычисленного среднего значения, описывая наибольший разброс значений;

· максимум и минимум значений;

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

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

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

Результата корреляционного анализа показывает три значения:

· прямая корреляция, обозначающая, что при росте одного показателя растет и другой показатель – значения корреляции больше 0;

· обратная корреляция, обозначающая, что при снижении одного показателя другой показатель растет – значения корреляции меньше 0;

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

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

· влияние цен составляющих потребительской корзины, на стоимость самой потребительской корзины;

· влияние объемов экспорта и импорта на стоимость потребительской корзины;

· влияние определенного вида продукции на прибыль компании;

· влияние различных PR-акций на всю PR-компанию.

Информационно командная среда Информационно командная среда

Рисунок 219. Использование корреляционного анализа

В результате решения поставленной задачи были сформированы исходные данные:

 

Информационно командная среда

 

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

Информационно командная среда

 

Анализ полученных результатов говорит о том, что стоимость потребительской корзины достаточно слабо зависит от экспорта и импорта государства, но, тем не менее, наблюдается обратная зависимость, которого говорит, что:

· при росте размеров экспорта значение стоимости потребительской корзины будет немного снижаться;

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

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

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

Информационно командная среда Информационно командная среда

Рисунок 220. Использование функции корреляции

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

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

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

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

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

Информационно командная среда Информационно командная среда Информационно командная среда

Рисунок 221. Настройка механизма «Поиск решений»

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

· базовые показатели, значения которых должны подбираться для поиска оптимального решения;

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

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

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

Возьмем в качестве примера следующую задачу:

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

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

По условию поставленной задачи известно:

· имеется два вида продукции;

· имеется три вида ресурсов;

· имеется формула вычисления необходимых объемов ресурсов для первого вида продукции - Информационно командная среда;

· имеется формула вычисления необходимых объемов ресурсов для второго вида продукции - Информационно командная среда;

· имеется условие ограничения на общие объемы первого ресурса - Информационно командная среда;

· имеется условие ограничения на общие объемы второго ресурса - Информационно командная среда;

· имеется условие ограничения на общие объемы третьего ресурса - Информационно командная среда;

· имеется функция выручки - Информационно командная среда;

· имеется целевая функция на выручку - Информационно командная среда.

Таким образом, получается математическая модель задачи:

Информационно командная среда

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

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

Информационно командная среда

Рисунок 222. Информационная модель задачи оптимизации

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

Информационно командная среда

В качестве подбираемого параметра были выбраны показатели объемов производства.

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

Информационно командная среда Информационно командная среда

Рисунок 223. Определение параметров поиска решений

В данном диалоговом окне описываются основные параметры поиска решений:

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

· оптимальное значение (равной) – этот показатель определяет критерий оптимизации: максимальное или минимальное значение, либо явное значение, которое должно получиться;

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

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

Дополнительно, к основным параметрам необходимо определить параметры методов оптимизации (кнопка «Параметры»):

Информационно командная среда

Рисунок 224. Параметры используемых методов в поиске решений

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

· временные параметры поиска решений («максимальное время» и «предельное число итераций») – эти параметры определяют время, в течение которого может быть найдено оптимальное решение. По истечении указанного времени или количества итераций будет выведено последнее получившееся решение;

· погрешность – этот показатель определяет точность получаемого результата;

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

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

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

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

После проведения поиска решений пользователь получит возможность определить свои дальнейшие действия:

Информационно командная среда

Рисунок 225. Окончание поиска решений

По окончании поиска, можно сформировать некоторые отчеты по оптимизации:

· результаты – это отчет по полученным результатам поиска, а именно по целевой функции и искомым показателям;

· устойчивость – это отчет по возможным изменениям базовых показателей, представленных константами в информационной среде;

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

В результате решения поставленной задачи, была получена информационная среда:

Информационно командная среда

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

После выполнения расчетом получены следующие отчеты:

Информационно командная среда

Рисунок 227. Отчет по результатам поиска

Информационно командная среда

Рисунок 228.Отчет по устойчивости

 

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

Раздел 4. Использование макросов

Подраздел 1. Основы программирования в VBA

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

Для реализации этих функций необходимо использование встроенного в табличный процессор языка программирования Visual Basic for Application (VBA). С помощью этого языка пользователь может запрограммировать любую обработку данных в виде процедуры и закрепить выполнение этих процедур за активными элементами информационной среды табличного процессора.

Чтобы реализовывать сложные механизмы обработки данных в табличном процессоре необходимо знать два важных элемента программирования:

· правила алгоритмизации процессов и команды, которые эти алгоритмы реализуют;

· правила обращения к ячейкам таблицы и обработки переменных программы.

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

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

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

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

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

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

Информационно командная среда

Рисунок 229. Пример линейного алгоритма

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

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

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

Информационно командная среда

Рисунок 230. Пример циклического алгоритма с известным количеством повторений

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

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

Информационно командная среда

Рисунок 231. Пример циклической обработки с предусловием

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

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

Информационно командная среда

Рисунок 232. Пример условного алгоритма

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

· линейный алгоритм:

1. Установить активной ячейку A2

2. Записать в ячейку формулу «=A1+1»

3. Установить активной ячейку B2

4. Записать в ячейку формулу «=A1/A2»

· циклический алгоритм с известным количеством повторений:

1. Цикл по переменной i от 1 ячейки до 10 ячейки

1.1. Установить активной ячейку «B»+i

1.2. Записать в ячейку формулу “=(«A»+i)*10”

2. Конец цикла

· циклический алгоритм с предусловием:

1. Установить начальное значение в переменную i=1

2. Цикл пока ячейка «A»+i не пустая

2.1. Установить активной ячейку «B»+i

2.2. Записать в ячейку формулу “=(«A»+i) *10”

3. Конец цикла

· циклический алгоритм с постусловием:

1. Установить начальное значение в переменную i=1

2. Цикл

2.1. Установить активной ячейку «B»+i

2.2. Записать в ячейку формулу “=(«A»+i) *10”

3. Конец цикла когда “(«A»+i)>10”

· условный алгоритм:

1. Установить активной ячейку A1

2. Если ячейка A1 не пустая

2.1. Установить активной ячейку A2

3. Иначе

3.1. Записать в ячейку число 1

4. Конец если

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

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

Информационно командная среда

Рисунок 233. Информационная среда примера

Чтобы решить эту задачу, необходимо описать алгоритм ее решения:

1. Установить для переменной i значение 2

2. Цикл пока ячейка «D»+i не пустая

2.1. Увеличить значение переменной i на 1

3. Конец цикла

4. Установить активной ячейку «D»+i

5. Записать в ячейку формулу “=(«B»+i)*( «C»+i)”

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

В языке VBA используется механизм объектно-ориентированного программирования. Этот механизм говорит о том, что каждый элемент в программе представляется некоторым объектом, обладающим двумя составляющими:

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

· методы, это процедуры и функции, которые выполняют некоторый алгоритм обработки данных.

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

Информационно командная среда

Рисунок 234. Пример свойств и методов объекта "Ячейка"

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

Для организации циклов и условий в языке VBA имеются соответствующие командные конструкции:

· Условная конструкция

– if <условие> then {операции} [else {операции}];

· Циклическая конструкция с известным количеством повторений

– for <переменная>=<начальное значение> to <конечное значение> [step <величина шага>] {операции} next <переменная>;

· Циклическая конструкция с предусловием

– while <условие выполнения> {операции} wend;

· Циклическая конструкция с постусловием

– do {операции} loop until <условие выхода>.

Используя команды языка программирования, код программы примера будет выглядеть следующим образом:

i = 2

While Not (Range("D" + Trim(Str(i))).Text = "")

    i = i + 1

Wend

Range("D" + Trim(Str(i))).Select

ActiveCell.Formula = "=B" + Trim(Str(i)) + "*C" + Trim(Str(i))

Таким образом, изучив основные команды языка программирования VBA, можно создать достаточно динамичную информационно-командную среду решения задачи.

Подраздел 2. Интерфейсная среда с помощью VBA

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

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

Например, необходимо для задачи поиска решений сделать два действия:

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

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

Для создания экранных форм используются элементы экранной формы, которые могут быть вызваны с помощью панели инструментов «Элементы управления»:

Информационно командная среда

Рисунок 235. Панель инструментов «Элементы управления»

В рассматриваемом примере создаем экранную форму на втором листе:

Информационно командная среда

Рисунок 236. Экранная форма ввода новых данных

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

Private Sub CommandButton1_Click()

    If (Not (TextBox1.Value = "")) And (Not (TextBox2.Value = "")) And (Not (TextBox3.Value = "")) And (Not (TextBox4.Value = "")) And (Not (TextBox5.Value = "")) Then

    i = 4

    While Not (Sheets("Лист1").Range("A" + Trim(Str(i))).Text = "")

        i = i + 1

    Wend

    Sheets("Лист1").Range("A" + Trim(Str(i))).Value = TextBox3.Value

    Sheets("Лист1").Range("B" + Trim(Str(i))).Value = TextBox4.Value

    Sheets("Лист1").Range("C" + Trim(Str(i))).Value = TextBox5.Value

    Sheets("Лист1").Range("D" + Trim(Str(i))).Value = TextBox1.Value

    Sheets("Лист1").Range("E" + Trim(Str(i))).Value = TextBox2.Value

    Sheets("Лист1").Range("H" + Trim(Str(i))).Formula = "=F" + Trim(Str(i)) + "+2*G" + Trim(Str(i))

    Sheets("Лист1").Range("I" + Trim(Str(i))).Formula = "=F" + Trim(Str(i)) + "+G" + Trim(Str(i))

    Sheets("Лист1").Range("J" + Trim(Str(i))).Formula = "=2*F" + Trim(Str(i))

    Sheets("Лист1").Range("K" + Trim(Str(i))).Formula = "=D" + Trim(Str(i)) + "*F" + Trim(Str(i)) + "+E" + Trim(Str(i)) + "*G" + Trim(Str(i))   

    End If

End Sub

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

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

Информационно командная среда

Рисунок 237. Окно свойств элементов экранной формы

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

Чтобы сгенерировать макрос необходимо вызвать панель инструментов «Visual Basic»:

Информационно командная среда

Рисунок 238. Панель инструментов «Visual Basic»

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

Информационно командная среда

Рисунок 239. Окно настройки макроса

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

В рассматриваемом примере выполняем следующие действия:

· Переходим на Лист 1, где расположены данные сценариев;

· Вызываем механизм поиска решений;

· Устанавливаем параметры, соответствующие строке с первым сценарием;

· Запускаем механизм на выполнение.

После выполнения необходимых действий, запись Маркоса необходимо остановить с помощью пиктограммы (Информационно командная среда) «Остановить запись». В результате будет сформирован код программы, с записанными в нее действиями пользователя.

В результате выполненных действий в редакторе Visual Basic будет записан код программы:

    Sheets("Лист1").Select

    SolverOk SetCell:="$K$4", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$4:$G$4"

    SolverAdd CellRef:="$H$4", Relation:=1, FormulaText:="$A$4"

    SolverAdd CellRef:="$I$4", Relation:=1, FormulaText:="$B$4"

    SolverAdd CellRef:="$J$4", Relation:=1, FormulaText:="$C$4"

    SolverOk SetCell:="$K$4", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$4:$G$4"

    SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _

        :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _

        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True

    SolverOk SetCell:="$K$4", MaxMinVal:=1, ValueOf:="0", ByChange:="$F$4:$G$4"

    SolverSolve

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

Private Sub CommandButton2_Click()

    If Not (TextBox6.Value = "") Then

        Sheets("Лист1").Select

        i = TextBox6.Value

        SolverOk SetCell:="$K$" + Trim(Str(i)), MaxMinVal:=1, ValueOf:="0", ByChange:="$F$" + Trim(Str(i)) + ":$G$" + Trim(Str(i))

        SolverAdd CellRef:="$H$" + Trim(Str(i)), Relation:=1, FormulaText:="$A$" + Trim(Str(i))

        SolverAdd CellRef:="$I$" + Trim(Str(i)), Relation:=1, FormulaText:="$B$" + Trim(Str(i))

        SolverAdd CellRef:="$J$" + Trim(Str(i)), Relation:=1, FormulaText:="$C$" + Trim(Str(i))

        SolverOk SetCell:="$K$" + Trim(Str(i)), MaxMinVal:=1, ValueOf:="0", ByChange:="$F$" + Trim(Str(i)) + ":$G$" + Trim(Str(i))

        SolverOptions MaxTime:=100, Iterations:=100, Precision:=0.000001, AssumeLinear _

        :=True, StepThru:=False, Estimates:=1, Derivatives:=1, SearchOption:=1, _

        IntTolerance:=5, Scaling:=False, Convergence:=0.0001, AssumeNonNeg:=True

        SolverOk SetCell:="$K$" + Trim(Str(i)), MaxMinVal:=1, ValueOf:="0", ByChange:="$F$" + Trim(Str(i)) + ":$G$" + Trim(Str(i))

        SolverSolve

    End If

End Sub

В результате, при нажатии на кнопку «Выполнить сценарий», будет запущена данная процедура.

Информационно командная среда

Рисунок 240. Окно ввода номера сценария

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

Информационно командная среда

Рисунок 241. Результат выполнения автоматизации

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

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

Раздел 5. Дополнительные возможности табличного процессора

Среди дополнительных возможностей табличного процессора особо выделяются три:

· сортировка данных;

· фильтрация данных;

· построение сводных таблиц.

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

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

Информационно командная среда Информационно командная среда

Рисунок 242. Диалоговое окно сортировки данных

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

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

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

Информационно командная среда

Рисунок 243. Вызов режима Автофильтр

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

Информационно командная среда

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

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

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

Информационно командная среда Информационно командная среда

Рисунок 245. Настройки фильтрации данных

Данный режим настройки предполагает установление ряда параметров:

· исходный диапазон – это множество ячеек, начинающихся с заголовков. Именно эти ячейки будут фильтроваться:

Информационно командная среда

Рисунок 246. Исходный диапазон для фильтрации

· диапазон условий – это множество ячеек с заголовками, в которых указаны условия, ограничивающие выборку:

Информационно командная среда

Рисунок 247. Условия для фильтрации

· диапазон результата – это область таблицы, в которую необходимо вывести результат фильтрации:

Информационно командная среда

Рисунок 248. Результат фильтрации

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

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

Информационно командная среда Информационно командная среда

Рисунок 249. Вызов сводной таблицы

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

В качестве исходных данных можно использовать таблицу или базу данных Microsoft Excel, размещенных на листах программы, либо из внешних источников, которыми могут являться базы данных из СУБД Microsoft Access.

Информационно командная среда

Рисунок 250. Определение области исходных данных

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

Информационно командная среда

Рисунок 251. Комплекс исходных данных

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

Информационно командная среда

Рисунок 252. Настройка сводной таблицы

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

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

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

· область строк – это область, аналогичная области столбцов, но данные будут рассматриваться в качестве отдельных строк;

· область данных – это область, в которой будут выводиться данные, сгруппированные по все описанным измерениям.

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

Информационно командная среда

Рисунок 253. Результат формирования сводной таблицы

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

 

Наверх страницы

Внимание! Не забудьте ознакомиться с остальными документами данного пользователя!

Соседние файлы в текущем каталоге:

На сайте уже 21970 файлов общим размером 9.9 ГБ.

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

Не нашли нужный документ? Воспользуйтесь поиском по содержимому всех файлов сайта:



Каждый день, проснувшись по утру, заходи на obmendoc.ru

Товарищ, не ленись - делись файлами и новому учись!

Яндекс.Метрика