Инвестиционные решения с помощью Microsoft Excel. 
MathCAD: работа с векторами и матрицами

Министерство образования Республики Беларусь Учреждение образования БЕЛАРУСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ ИНФОРМАТИКИ И РАДИОЭЛЕКТРОНИКИ

Контрольная работа по курсу «Прикладные системы обработки данных»

Минск 2008

1. Инвестиционные решения с помощью Microsoft Excel: исследование критериев принятия решений для бизнес-анализа

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

Сортировка списков.

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

Фильтрация списков.

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

Выборка данных средствами автофильтра.

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

По команде Данные -> Фильтр -> Автофильтр в строке заголовков таблицы появляются кнопки с раскрывающимися списками значений. Автофильтр предполагает использование критериев поиска типа «сравнение». Существует два типа сравнений: по точному или шаблонному значению, по условию отбора.

Для задания сложного условия для значения элементов одного из столбцов в команде Автофильтр выбирается строка (Условие…), по которой открывается диалоговое окно Пользовательский автофильтр. В нём можно ввести два условия для одного столбца и объединить их логическими функциями и/или.

Другой возможностью задания сложного условия в команде Автофильтр является выбор строки (Первые 10…), вызывающей окно Наложение условия по списку. В котором задаётся вывод определённого числа строк, содержащих максимальные (минимальные) значения данного столбца или заданный % строк.

Технология использования расширенного фильтра Excel.

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

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

Значения условий фильтрации, размещённые в одной строке «диапазона», или «области», «критериев», объединяются логической функцией И, а значения условий, заданные в разных строках области критериев, связываются функцией ИЛИ. «Диапазон условий» должен отделяться от исходного списка по крайней мере одной пустой строкой.

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

Структурирование и группировка данных для формирования итогов.

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

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

Команда Итоги производит автоматический расчёт итогов по всем строкам списка для одинаковых значений поля группировки. Чтобы получить итоги только для тех строк или ячеек таблицы, которые удовлетворяют определённым условиям, следует использовать специальные функции Excel (=СУММЕСЛИ (), =СЧЕТЕСЛИ ()), а также функции баз данных (БДСУММ (), ДМАКС (), ДСРЗНАЧ () и др.). Первую группу функций применяют при формировании итогов по одному критерию, заданному в виде константы, а вторую — при расчёте итогов по комплексному критерию фильтрации строк списка.

Решение задач бизнес-анализа средствами аппарата сводных таблиц.

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

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

Для построения сводной таблицы необходимо разместить все исходные данные на одном рабочем листе Excel и применить команду Данные -> Сводная таблица и следовать предложенным инструкциям Мастера сводных таблиц.

Чтобы изменить итоговую функцию, которую Excel использует для вычисления поля данных (для числовых данных по умолчанию СУММ), надо выбрать любой элемент в этом поле и нажать кнопку Поле сводной таблицы на панели инструментов Сводная таблица. Можно использовать следующие функции: СУММ (), СЧЕТ (), СРЗНАЧ (), МАКС (), МИН (), ПРОИЗВЕД (), СТАНДОТКЛОЩ (), СТАНДОТКЛОНЩ (), ДИСП (), ДИСПР ().

2. MathCAD: работа с векторами и матрицами

Массив — имеющая уникальное имя совокупность конечного числа числовых или символьных элементов, упорядоченных некоторым образом и имеющих определенные адреса. В пакете MathCAD используются массивы двух наиболее распространенных типов: одномерные (векторы), двумерные (матрицы).

Все встроенные средства пакета MathCAD, предназначенные для работы с матрицами собраны на Панели векторов и матриц (vector and matrix toolbar). Задавать матрицу можно либо вставив с панели матриц шаблон нужного размера и заполнив его числами, либо присваивая каждому элементу матрицы его значение (обычно это производится в цикле). Если элемент массива (матрицы) имеет несколько индексов (порядковый номер элемента), то они указываются через запятую. Для элементов матрицы первым идёт номер строки, вторым — номер столбца. Индексация массивов по умолчанию начинается с нуля, однако есть возможность управлять этим процессом. Номер первого элемента хранится в предопределённой переменной ORIGIN, значение которой можно менять. Встретив присвоение ORIGIN:=k, MathCAD будет все встречающиеся ниже массивы нумеровать, начиная с номера k. Любое обращение к элементу с меньшим номером будет вызывать сообщение об ошибке.

Для ввода матрицы (или вектора) требуется проделать следующую последовательность операций:

— задаём имя матрицы и вводим знак присваивания. Например, для задания матрицы, А пишем А:. Получаем А:=;

— с помощью команды Insert -> Matrix…, или комбинации клавиш Ctrl + M, или щелчком на кнопке панели Матрица, заполнив массив пустых полей для не слишком больших массивов.

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

Формирование вектора осуществляется аналогично.

Следует отметить второй вариант формирования матриц и векторов без обращения к окну работы с матрицами, а через переменные с индексами, например, Ai, j, Bi. Индекс к имени переменной припечатывается нажатием либо на кнопку Xn на панели математических инструментов, либо на клавишу [(открывающаяся квадратная скобка).

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

Пример.

ORIGIN:=1 определяем номер первого элемента

формируем матрицу А

формируем матрицу В

решаем матричное уравнение АХ=В

вывод решения

проверка

|A|=-2 находим определитель

обратная матрица Пример.

ORIGIN:=0 определяем номер первого элемента А0,0:=1 А0,1=1 формируем матрицу, А А0,0:=5 А0,1=3

В0:=138 В1=540 формируем матрицу В

X:=lsole (A, B) решаем матричное уравнение АХ=В Х0=63 Х1=75 вывод решения А0,0Х0+А0,1Х1-В0=0 проверка А1,0Х0+А1,1Х1-В1=0.

Вычисления с векторами и матрицами.

Векторные матричные операторы.

Для работы с векторами и матрицами система MathCAD содержит ряд операторов и функций. Введём следующие обозначения: для векторов — V, для матриц — M, и для скалярных величин — Z.

фильтр excel строка матрица Под понятием «векторизация» подразумевается одновременное проведение математических операций в их скалярном значении над всеми элементами вектора или матрицы, помеченными векторизации. Это можно понимать и как возможность параллельных вычислений.

Если, А и В — векторы, то А*В даёт скалярное произведение этих векторов. Но то же произведение под знаком векторизации создает новый вектор, каждый j-й элемент которого есть произведение jх элементов векторов, А и В. Векторизация позволяет использовать скалярные операторы и функции с массивами.

Векторные и матричные функции.

Существует также ряд встроенных векторных и матричных функций:

Для работы с матрицами также существует ряд встроенных функций. Они перечислены ниже:

Функции, возвращающие специальные характеристики матриц.

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

Дополнительные матричные функции.

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

Функции сортировки для векторов и матриц.

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

3. Контур оперативного управления (контур логистики) комплекса «Галактика»

Контур Управления логистикой системы Галактика ERP помогает интегрировать в единую систему основные функции логистики: управление заказами и закупками, снабжением и сбытом, транспортировку, управление запасами, складирование, взаимодействие с поставщиками и получателями продукции и услуг, контроль взаиморасчётов. Перечисленные выше задачи решают модули системы Галактика ERP — Управление договорами, Управление снабжением, Управление сбытом, Складской учёт, Целевой учёт запасов, Поставщики, получатели, Управление качеством продукции.

Реализованные в системе функции дают возможность:

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

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

— управлять запасами и складскими операциями;

— контролировать взаимоотношения с поставщиками и получателями (контроль отгрузок, поставок, оплаты, штрафные санкции, взаимозачёты, уступка долга и т. д.);

— формировать прайс-листы;

— вести учёт продукции по серийным номерам;

— контролировать качество сырья и готовой продукции;

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

Контур Управления логистикой системы Галактика ERP позволяет значительно сократить товарные запасы, ускорить оборачиваемость активов, снизить себестоимость продукции и общие логистические издержки.

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

Управление снабжением Функции по отслеживанию предложений поставщиков, планированию закупок, выбору поставщика выполняются в модуле МТО. В модуле Управление снабжением сосредоточены операции по работе с конкретными документами на приобретение: документами-основаниями, накладными, доверенностями.

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

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

Целевой учёт запасов Позволяет вести автоматизированный учёт материально-технических ресурсов (МТР) и услуг по объектам целевого назначения.

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

Управление качеством продукции Позволяет автоматизировать деятельность служб контроля качества продукции, таких как ОТК, Лаборатории, а также служб, связанных с деятельностью по анализу и управлению качеством продукции при поставках, на складах, в производстве и при отпуске клиентам, таких как МТО, Складское хозяйство, Управление производством, Сбыт.

Преимущества использования контура логистики системы Галактика ERP:

Комплексное решение задач логистики.

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

При этом система Галактика ERP обеспечивает:

— однократный ввод информации;

— автоматическое формирование бухгалтерских проводок и финансовых операций;

— учёт материальных ценностей в нескольких валютах, формирование документов в любой из зарегистрированных в системе валют с расчётом соответственно рублёвого или валютного эквивалента;

— сокращение количества ошибок, а следовательно, экономию времени и средств.

Повышение эффективности управления логистикой:

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

— сокращение затрат на снабжение;

— снижение уровня неликвидных запасов;

— ускорение оборачиваемости активов;

— уменьшение дебиторской задолженности.

Повышение качества работы логистических служб:

— получение оперативной информации о состоянии запасов, своевременная отчётность в требуемой форме;

— регламентация бизнес-процессов;

— повышение оперативности и точности выполнения операций.

4. Задача

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

Решение:

Построение EXCEL:

Для построения графика функции необходимо сперва построить таблицу ее значений при различных значениях аргумента. Аргумент у нас изменяется в диапазоне от -1,7 до 1,9 (по условию задачи) с шагом 0,1.

Создадим в Excel таблицу следующего вида (рис. 1):

Рис. 1

Зафиксируем курсор на ячейке А17 и выберем команду Правка -> Заполнить -> Прогрессия. Далее появится диалоговое окно Прогрессия, которое заполним следующим образом (рис. 2):

Рис. 2

Нажимаем ОК и заполняются ячейки. Затем введем в ячейку В17 формулу =(1+A17)/(1+КОРЕНЬ (2+A17+A17*A17)), в результате вычисления получим -0,25 125 113. Заполним весь столбец значений Y (рис. 3).

Рис. 3

Для построения графика функции выделим диапазон ячеек А17: В53. Вызовем мастера диаграмм Вставка => Диаграмма (рис. 4, 5, 6,7)

Рис. 4 Рис. 5

Рис. 6 Рис. 7

Результат построения графика (рис. 8):

Рис. 8

Построение EXCEL:

График этой функции строится аналогично предыдущей задаче. Для вычисления значений функции мы будем использовать логическую функцию ЕСЛИ.

Введем в ячейку В65 следующую формулу:

=ЕСЛИ (A65<=0; КОРЕНЬ (1+A65*A65); (1+A65)/(1+СТЕПЕНЬ (1+СТЕПЕНЬ (EXP (1);-0,2*A65);1/3)))

Построим график, действуя по той же схеме как и в предыдущем примере.

Результат построения графика (рис. 9):

Рис. 9

Построение EXCEL:

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

ЕСЛИ.

Введем в ячейку В14 следующую формулу:

=ЕСЛИ (A14<0; (1+A14+A14*A14)/(1+A14*A14); ЕСЛИ (A14>=1; 2*ABS (0,5+SIN (A14)); КОРЕНЬ (1+2*A14/(1+A14*A14))))

Построим график, действуя по той же схеме, как и в предыдущем примере.

Результат построения графика (рис. 10):

Рис. 10

Построение MathCAD:

С клавиатуры вводим функцию, нажимаем кнопку Graph Toolbar и строится график (рис. 11, 12).

Рис. 11

Построение MathCAD:

Рис. 12

Построение MathCAD (рис. 13):

Рис. 13

5. Задача

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

Решение:

млн.

;

млн.

Ответ: 15.524 млн руб., 48.225 млн руб.

6. Задача

Транспортная задача. Имеются n пунктов производства и т пунктов распределения продукции. Стоимость перевозки единицы продукции с і-го пункта производства в j-й центр распределения cij приведена в таблице, где под строкой понимается пункт производства, а под столбцом — пункт распределения. Кроме того, в этой таблице в i-й строке указан объем производства в і-м пункте производства, а в j-м столбце указан спрос в j-м центре распределения. Необходимо составить план перевозок по доставке требуемой продукции в пункты распределения, минимизирующий суммарные транспортные расходы.

Решение:

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

Затем заполняем формулами ячейки:

В I25 пишем =СУММ (D25:G25) и растягиваем эту формулу до I29.

В D31 пишем =СУММ (D25:D29) и растягиваем до G31.

В D34 пишем =СУММ (D14:D18;D25:D29) и растягиваем до G34.

В J34 пишем =СУММ (D34:G34).

В I37 пишем =СУММПРОИЗВ (D25:G29;D14:G18).

Выбираем пункт меню Сервис -> Поиск решения…

Рис. 14

Рис. 15

После нажатия Выполнить Excel находит оптимальное решение задачи.

Рис. 16

7. Задача

Рассчитайте текущую стоимость вклада, который через три года составит 15 000 тыс. руб. при ставке процента 20% годовых.

Решение:

Для расчёта используем формулу pv=fv / (1+r)n или соответствующую ей запись функции ПЗ (норма, кпер, бс). При этом норма=20%, кпер=3, бс=15 000. Тогда, используя диалоговое окно Мастер функций, получим:

Рис. 17

ПЗ (20%, 3,, 15 000)=-31 597,22222 тыс. руб.

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