Методы кибернетики в химической технологии: реализация основных вычислительных методов в пакете MS Excel и средствами MS VBA

106 Pages·2.843 MB·Russian

by  Воробьев Е.С., Воробьева Ф.И.

Е. С. Воробьев, Ф. И. Воробьева
МЕТОДЫ КИБЕРНЕТИКИ
В ХИМИЧЕСКОЙ ТЕХНОЛОГИИ

реализация основных вычислительных методов
в пакете MS Excel и средствами MS VBA
2015
Министерство образования и науки России
Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования
«Казанский национальный исследовательский
технологический университет»

Е. С. Воробьев, Ф. И. Воробьева

МЕТОДЫ КИБЕРНЕТИКИ
В ХИМИЧЕСКОЙ ТЕХНОЛОГИИ

реализация основных вычислительных методов
в пакете MS Excel и средствами MS VBA

Учебное пособие
Казань
Издательство КНИТУ
2015
УДК 007:66.01(075)
ББК 32.81:35.11

Воробьев Е. С.
Методы кибернетики в химической технологии: реализация ос-
новных вычислительных методов в пакете MS Excel и средствами MS
VBA учебное пособие / Е. С. Воробьев, Ф. И. Воробьева; М-во образ.
и науки России, Казан. нац. исслед. технол. ун-т – Казань:
Изд-во КНИТУ, 2015. – 105 с.
ISBN 978-5-7882-1737-6

Пособие содержит основные теоретические выкладки по мате-
матическим методам, используемым при решении задач, связанных с
химической технологией, в рамках дисциплины «Методы кибернети-
ки в химической технологии». Показаны способы решения типовых
задач средствами MS Excel, а также даны примеры реализации этих
задач программными средствами MS VBA.
Подготовлено на кафедре общей химической технологии.

Печатается по решению редакционно-издательского совета Ка-
занского национального исследовательского технологического уни-
верситета.
Рецензенты: зав. каф. автоматизации технол.
процессов и производств КГЭУ,
проф, д-р техн. наук
К. Х. Гильфанов
проф. каф. радиоэлектронных и
телекоммуникационных систем
КНИТУ (КАИ),
проф, д-р техн. наук
О. Ш. Даутов

ISBN 978-5-7882-1737-6  Воробьев Е.С., Воробьева Ф.И., 2015
 Казанский национальный исследовательский
технологический университет, 2015
Введение
В рамках дисциплины «Методы кибернетики в химической тех-
нологии» предполагается освоение основных математических мето-
дов, которые мы используем при решении наших задач. Основной
упор будем делать на численные решения, которые легко реализуют-
ся на компьютерах и могут быть просто использованы при решении
задач во время выполнения лабораторных и практических занятий, а
так же при подготовке курсовых и дипломных проектов.
Многие наши задачи требуют нахождения корней уравнений
или систем, поиски экстремумов функций. Мы не можем обходиться
без умения находить интегралы и дифференциалы функций. При ре-
шении прямых и обратных задач химической кинетики, построенных
на основе дифференциальных уравнений, требуется восстановление
первообразных функций на основании их дифференциальных урав-
нений. В данном пособии будут рассмотрены методы нахождения:
 корней уравнений и систем уравнений;
 экстремумов различных функций;
 дифференциалов функций;
 интегралов функций;
 решения дифференциальных уравнений.
Построение данных решений с использованием программных
средств VBA связано с необходимостью решения более общих задач,
в которых эти методы могут использоваться как вспомогательные
функции, и Excel к ним будет обращаться автоматически по мере
необходимости. Такой подход существенно ускоряет сами решения и
позволяет решать задачи более высоких уровней сразу же, а не по-
этапно.
Для удобного восприятия текста в нем приняты следующие
форматы:
 текст, набранный курсовом – общепринятый термин, курсивом
оформлены все математические формулы, за исключением стан-
дартных функций в них;
 [Enter] – названия клавиш на клавиатуре компьютера;
 Книга1, А1, Лист1 – названия объектов MS Excel;
 «Главная» – «Заполнить ( )» – команды MS Excel, как они записаны
на ленте;
 текст в кавычках «Sin(» требует ввода с клавиатуры;
3
 Public Function My_fun() – код программы в VBA;
Прежде чем перейти к основным задачам, познакомимся с чис-
ленными методами. Все они основаны на итерационных процедурах,
которые повторяют простые вычисления с подбором какого-либо из
параметров, пока не будет достигнуто решение с заданной точно-
стью. Не надо считать, что эти решения приближенные, мы вправе
задать любую точность (даже очень высокую), но это потребует
большего числа итераций и, как следствие, больших затрат машинно-
го времени.
Что же такое точности решения? Обычно требования по точно-
сти наших результатов выбираются из ГОСТ в зависимости и типа
наших исследований (лабораторные исследования, макетные, полу-
промышленные и промышленные установки и т.п.) . Например, для
наших научных исследований это 95%, как и требуется по ГОСТ для
обычных научно-исследовательских работ. Однако при выполнении
расчетов мы накапливаем погрешности как за счет ошибок в исход-
ных данных, так и за счет их (ошибок) накопления во время расчетов
и округления результатов вычислений. Реально мы можем разделить
наши ошибки на несколько групп:
 Ошибки модели – математические модели обычно являются при-
ближенными описаниями реальных процессов, поэтому парамет-
ры, вычисленные в рамках принятой модели, могут отличаться от
истинных значений. Их погрешность зависит от степени адекват-
ности модели реальному процессу.
 Ошибки данных – исходные данные, в свою очередь, содержат по-
грешности, связанные с их измерениями или вспомогательными
вычислениями.
 Ошибки метода – применяемые для решения задач методы обычно
являются приближенными, так как найти решение практической
задачи в виде конечной формулы возможно крайне редко.
 Ошибки вычислений и округления – при вводе исходных данных в
ЭВМ, выполнении арифметических операций и выводе результа-
тов на печать производятся округления.
Поэтому полная погрешность при решении задачи на ЭВМ
складывается из трех составляющих:
 Неустранимая погрешность складывается из ошибок математиче-
ской модели, которую приняли, и ошибок при получении исход-
ных данных. Они не могут быть устранены на этом этапе. Един-
4
ственный способ уменьшения этой погрешности – переход к более
точным математическим моделям и методикам измерения.
 Погрешности методов позволяют осознанно выбирать наилучший
метод для решения поставленной задачи и разумно задать его точ-
ность. Необходимо, чтобы величина погрешности метода была в 2
– 10 раз меньше неустранимой погрешности. Большие значения
(менее 2) сильно снижают точность результата, а меньшие (более
10) увеличивают затраты машинного времени и практически не
влияют на значение полной погрешности.
 Вычислительная погрешность определяется характеристиками ис-
пользуемой ЭВМ, её разрядной сеткой действительных чисел. Же-
лательно всегда использовать переменные двойной точности.
Рассмотрим, как возникают погрешности в арифметических вы-
числениях. Примем для рассмотрения этого вопроса следующую
форму записи: X  X , где X – реальное значение, ̅X – записанное
значение, ε – ошибка. Тогда можно построить все погрешности для
основных арифметических операций:
 Сложение и вычитание: X  X X   X*   , в
 1 1 2 2  1 2
результате чего погрешность операции не превышает суммы по-
грешностей операндов. Однако при вычитании чисел одного знака
ошибки могут сильно возрастать. Если числа близки, то не исклю-
чена полная или почти полная потеря точности. Это называется
катастрофической потерей точности. При реализации числен-
ных методов решения задач следует избегать вычитания близких
чисел одного знака.
 Умножение: если построить показанное в предыдущем абзаце вы-
ражение для суммы, сменив знак на умножение, то можно вычис-
лить точность умножения в виде      .
 1 2 1 2
 Деление при подобных расчетах дает зависимость для точности в
 
виде:   1 2 .
 1
2
Как видим, любые вычисления приводят к потере точности, но
самые опасные возникают при вычитании.
Для оценки погрешностей в расчетах используются:
 Абсолютная погрешность приближенного значения определяется
разницей между реальным и замеренным значениями измеряемой
величины. Данное значение может быть как положительным, так и
отрицательным. Качество данной оценки существенно зависит от
5
принятых единиц измерения и масштабов величин, что создает
много проблем при её использовании. Поэтому целесообразно со-
отнести погрешность величины и ее фактическое значение, для че-
го вводится понятие относительной погрешности.
 Относительная погрешность приближенного значения определя-
ется отношением абсолютной величины разности между реальным
и замеренным значениями к значению самого измерения. Относи-
тельную погрешность часто выражают в процентах. Она не зависит
от масштабов величин и единиц измерения.
В расчетах удобнее использовать относительные погрешности.
6
1. Основные приемы работы в среде MS Excel и VBA
Вводная информация
Прежде чем начинать непосредственно занятия по методам ки-
бернетики, вспомним ряд основных приемов работы в электронной
таблице MS Excel.
MS Excel – это электронные таблицы на листах, которые собра-
ны в книги Excel. Книги могут содержать любое количество листов,
часть из которых является таблицами, другие – диаграммами. В даль-
нейших работах нам надо будет уметь строить таблицы и графики,
которые позволят убедиться в правильности полученных решений.
Поэтому сейчас мы познакомимся с этими операциями.
Работа с таблицей и диаграммами в Excel
Для начала попробуйте самостоятельно решить следующую зада-
чу: постройте таблицу с данными по функции вида
Y=15 * Sin(X + Cos(X)) + 2,5 * Log(15 * X)
при изменении параметра Х в интервале от 1 до 14
и на основании её результатов постройте график. В
результате должна получиться таблица, фрагмент
которой показан на рис. 1.1, и график (рис. 1.2). Кто
получил эти результаты, может переходить к ра-
боте в VBA (см. раздел на с. 15).
График пользовательской функции
30
25
20
15
я
и
ц
к10
н
у
Ф
5
0
-5
Y
-10

0 3 6 9 12 15
Рис.1.1. Фрагмент
Параметр
таблицы расчета
Рис.1.2. График функции по данным таблицы
функции
Для тех, кто не смог, кратко вспомним поря-
док выполнения операций. Открываем MS Excel, в нем сразу создает-
7
ся новая книга с именем Книга1. На первом листе с именем Лист1 со-
здаем таблицу. Вводим в первую и вторую ячейки первой строки (A1,
B1) имена столбцов («Х» и «Y»). Это будут заголовки готовой табли-
цы. Заполняем столбец параметра (Х) необходимыми данными, ис-
пользуя прогрессию. Для этого во вторую ячейку (A2) столбца Х вво-
дим 1. Потом отсчитываем не менее 20 строк вниз, например, при
выборе 20 строк, перемещаемся в
ячейку A21 и вводим конечное зна-
чение прогрессии 14. Выделяем
диапазон от 1 до 14 и вызываем ко-
команду «Главная» – «Заполнить
( )» – «Прогрессия» (рис.1.3). Имея
начальное и конечное значения
прогрессии, программа вычисляет
Рис.1.3 Окно команды «Прогрессия»
необходимый шаг. Завершаем ко-
манду кнопкой «Ok». Чем большего размера будет таблица, тем точнее
будет построен график.
В ячейку В2 столбца «Y» вводим формулу для вычисления функ-
ции. Ввод начинаем со знака равно ( ), потом вводим постоянную часть
=
формулы « ». Теперь надо ввести встроенную функцию Sin(). Это
15*
можно сделать обычным вводом с клавиатуры, набрав «Sin(», или вы-
звать «Мастер функций» с помощью стрелки  вызова последних функ-
ций, расположенной слева от поля ввода формулы
.
Если формула начинается со стандартной функции, то мастер функций
можно вызвать кнопкой fx , которая находится рядом со строкой ре-
дактирования. После ввода функции с помощью мышки указываем на
ячейку, откуда берётся аргумента (Х), в нашем случае это соседняя сле-
ва ячейка. В строке формулы появится адрес этой ячейки « ».
А2
Вводить адреса ячеек с клавиатуры нецелесообразно, так как мож-
но ошибиться как самим адресом, так и раскладкой клавиатуры
(ввести адрес в русской раскладке), что сделает поиск таких ошибок
очень сложным.
Если формула была введена с клавиатуры, то продолжаем ввод »
«+Cos(
и снова выбираем ячейку , закрываем скобку второй функции (Cos) и
А2
потом закрываем скобку первой функции (Sin). Так же реализуем ввод
функции Ln() либо с клавиатуры или через «Мастер функций» (рис. 1.4),
который упрощает их ввод. Находим нужную функцию (рис. 1.4 а). Для
более быстрого поиска сначала можно выбрать категорию, к которой
8
функция относится. После выбора функции (рис. 1.4 б) заполняем её
параметры. В нашем случае надо указать ссылку на ячейку аргумента
(Х), повторяем с помощью мышки показанную выше процедуру выбора

а б
Рис.1.4 Окна «Мастер функций»
а – выбор функции, б – ввод аргументов функции.
адреса ячейки. Завершаем ввод формулы кнопкой «Ok» и растягиваем
ячейку с формулой на весь диапазон таблицы.
Чтобы таблица выглядела, как на рис. 1.1, её надо отформатиро-
вать. Это обычно делается для более наглядного представления дан-
ных в отчетах. Например, расположим заголовки по центру ячеек и
обрамим таблицу рамкой. Сначала выделим две ячейки заголовка и
определим форматирование ячейки по центру «Главная» – «Выровнять
по центру ». Для построения границ таблицы выделим все ячейки с
данными, включая заголовок, и вызовем команду «Главная» – «Все гра-
ницы ». По умолчанию здесь стоит другая команда ( ), поэтому
надо раскрыть весь список вариантов оформления границ и выбрать
нужную команду.
Все операции форматиро-
вания ячеек можно выполнить
через одну команду «Главная» –
«Формат » – «Формат ячейки …»,
где на различных закладках
расположены все команды
форматирования ячейки и дан-
ных в ней. На рис. 1.5 показана
закладка «Граница», где можно
выбрать формат линий для об-
рамления и указать, какие гра-
Рис.1.5. Окно форматирования ячейки
9