Excel и Mathcad: пути интеграции

Excel и Mathcad: пути интеграции

Валерий Очков

В статье дано сравнительное описание самых популярных в настоящее время средств ведения на компьютере рутинных расчетов — электронных таблиц Excel (разработчик — Microsoft, Inc.) и математической программы Mathcad (MathSoft, Inc.). Показано, как с помощью программы MathConnex, входящей в состав Mathcad 7 Pro, можно интегрировать Excel и Mathcad, обеспечивая обмен данными между ними.

В настоящее время для рутинных расчетов на компьютере все чаще и чаще используются не традиционные языки программирования (BASIC, Pascal, C, fortran), а электронные таблицы и специальные математические программы. Сложилось так, что, говоря об электронных таблицах, мы имеем в виду Excel. Математическая же программа у нас ассоциируется в основном с Mathcad. Оба эти пакета задумывались как средства работы на компьютере пользователей, не желавших или не умевших “возиться” с языками программирования при решении финансовых, научно-технических и прочих прикладных задач (программирование без программирования).

Технология работы в средах Excel и Mathcad имеет много общего. И там и там на экране дисплея перед глазами пользователя некий волшебный лист бумаги, на котором с помощью клавиатуры и мыши пишутся математические выражения и комментарии к ним. И там и там символ “=” выводит на дисплей результат вычислений. Только в среде Excel “равно” пишется до соответствующего математического выражения, а в среде Mathcad - после:

=67 * (56 + SIN(2)) (Excel)

67 Ч (56 + sin(2))= (Mathcad)

Если с введенного (отредактированного) выражения увести курсор, то сразу будет получен результат вычислений. В среде Excel он появится вместо математического выражения в той же ячейке, где была введена соответствующая формула, в среде Mathcad — справа от знака “равно”. В этом главное преимущество Excel и Mathcad по сравнению с традиционными языками программирования, где сама программа (математические формулы) и протокол ее работы (результаты вычислений), как правило, разделены во времени и в пространстве.

В средах Excel и Mathcad процесс создания “программы” идет параллельно с ее отладкой и оптимизацией. Отладочные фрагменты (не только числа, но и графики, а также анимационные клипы) можно оставить в готовой таблице или в Mathcad-документе для того, чтобы, например, еще раз убедить себя, а также реального или воображаемого оппонента в правильности хода решения задачи. Такая открытость алгоритма (совмещение на одном листе и формул, и результатов) особенно полезна в учебном процессе. Не случайно пакет Mathcad в настоящее время стал неким стандартом в сфере образования, включая и дистанционное.

В Excel и в Mathcad встроено большое число математических операторов и функций, знание которых пользователем во многом определяет успех в решении задач. Со встроенными функциями помогает работать Мастер функций (рис.1 — Excel и рис.2 — Mathcad).

Рис.1. Мастер функций Excel

Рис.2. Мастер функций Mathcad

Команду Mathcad Вставить функцию... (Insert Function...) Мастером функций назвать можно лишь условно. В соответствующем диалоговом окне (рис.2) нет деления функций по категориям (как в среде Excel — см. рис.1), нет списка пользовательских функций и, главное, нет автоматической вставки аргументов. Хотя автоматическая вставка новой функции в качестве аргумента другой функции (механизм вложения функций) в среде Mathcad также возможен.

В среде Mathcad кроме встроенных функций есть и встроенные операторы. Чтобы ввести их в Mathcad-документ пользователь должен нажать соответствующую кнопку панелей (палитр) математических операторов (рис.3).

Рис.3. Панели математических операторов Mathcad

И в среде Excel, и в среде Mathcad математический аппарат можно расширять, создавая пользовательские операторы, функции и процедуры. Для этого Excel и Mathcad дооборудованы встроенными языками программирования. Вот как выглядит в средах Excel и Mathcad (билингва — см. рис.4 и 5) пользовательская функция с именем T, созданная средствами программирования.

Function T(M, S)

If S = "холост" Then

Select Case M

Case Is < 21: T = 0: Exit Function

Case Is < 378: T = 0.15 * (M - 21): Exit Function

Case Is < 885: T = 53.55 + 0.28 * (M - 378): Exit Function

Case Is < 2028: T = 195.51 + 0.33 * (M - 885): Exit Function

Case Else: T = 572.7 + 0.28 * (M - 2028)

End Select

End If

If S = "женат" Then

Select Case M

Case Is < 62: T = 0: Exit Function

Case Is < 657: T = 0.15 * (M - 62): Exit Function

Case Is < 1501: T = 89.25 + 0.28 * (M - 657): Exit Function

Case Is < 3695: T = 325.57 + 0.33 * (M - 1501): Exit Function

Case Else: T = 1049.59 + 0.28 * (M - 3695)

End Select

End If

End Function

Рис.4. Функция расчета налогов на языке VBA

Рис.5. Функция расчета налогов на языке Mathcad

Функция T возвращает федеральный налог США (Tax) с холостых и женатых по прогрессивной шкале налогообложения. Функции T и ее аргументы (M — недельный “грязный” заработок и S — семейное положение налогоплательщика) и в среде VBA (рис.4), и в среде Mathcad (рис.5) имеют по умолчанию тип Variant (один из стандартных типов ActiveX Automation).

В программах на рис.4 и 5 можно заменить константы и получить функцию для расчета российских налогов. Сравнивая нашу шкалу налогов с американской, следует отметить, что в США при больших заработках ставка налога падает с 33 до 28%. Дальновидная политика! В стабильном обществе богатые люди свои доходы не прячут и не проедают, а пускают в дело — расширяют производство, покупают акции и т.д. Кроме того, американская налоговая система нацелена на укрепление семьи.

Язык программирования Mathcad по своей идеологии очень похож на язык FRED интегрированного пакета Framework. Говорят, что один из “погорельцев” фирмы Ashton-Tate (разработчик Framework) перешел в фирму MathSoft и приложил руку к созданию языка программирования Mathcad. Внешне же своими вертикальными линиями, фиксирующими вложения конструкций программы и операторные блоки, пакет Mathcad напоминает алгоритмические конструкции книги А.П.Брудно “Программирование в содержательных обозначениях” (М.: Наука, 1968). Автор данной статьи в свое время очень увлекался подобными линиями, втискивая программы в рамки структурных диаграмм (см. книгу “128 советов начинающему программисту”. — М.: Энергоатомиздат, 1991). Вертикальные линии программ Mathcad более наглядны (особенно для обучения структурному программированию), чем просто операторные скобки (begin-end на языке Pascal, фигурные скобки языка С, оператор list() языка FRED, конец строки BASIC-программы, круглые скобки математических выражений и т.д.). Кроме того, вертикальные линии позволяют отказаться от ключевых слов-паразитов (Then, Select, End и т.д.), засоряющих программу фоновой информацией. Говоря о структурном программировании, следует отметить тот факт, что разработчики языка Mathcad раз и навсегда отказались от метки и операторов условного и безусловного перехода к метке как инструмента реализации разветвленных алгоритмов. Для некоторого смягчения этой категоричной позиции были введены операторы return (см. рис.5), break (передача управления в конец цикла) и continue (передача управления в начало цикла).

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

Алгоритмические конструкции в среде Mathcad вводятся не традиционным набором через клавиатуру ключевых слов If, Then, Else, While и т.д., а нажатием одной из кнопок панели программирования (рис.6), создающих соответствующие заготовки операторов: создание программного блока (Add Line), присвоение значения локальной переменной (¬ ), обработка ошибок (on error), изменение естественного хода выполнения операторов (while, if, otherwise, for, return, break и continue).

Рис.6. Панель программирования Mathcad 7 Pro

Последняя реализация Mathcad (седьмая версия) близка к электронным таблицам не только по духу, но и по интерфейсу. В меню Excel и в меню Mathcad есть одноименные заголовки (Insert — Вставить, Format - Формат: см. рис.6 и рис.12), собравшие команды создания и форматирования объектов Excel и Mathcad: чисел, текстов, формул, двух- и трехмерных графиков и т.д. Если на дисплее какого-то нужного объекта нет, то пользователь отдает команду из меню Insert (Вставить). Если объект есть, но он пользователя по каким-либо параметрам не устраивает, то отдаются команды из меню Format (Формат).

Теперь об отличиях Excel и Mathcad. Тут можно говорить о преимуществах того или иного пакета, но...

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

Во-вторых, Mathcad создавался на основе электронных таблиц (см. сноску 9). Естественно, что разработчики Mathcad постарались избежать некоторых недостатков традиционных электронных таблиц (VisiCalc, SuperCalc, Quattro Pro, Excel и др.).

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

Тем не менее...

1. Математические выражения в среде Mathcad записываются в их общепринятой нотации, к какой человек привык задолго до того, как он стал пользователем ЭВМ (и в историческом, и в биографическом планах). Числитель находится вверху, а знаменатель внизу, корень накрывает своей “крышей” число и т.д.:

B = 20 / SIN(A) / SQR(3) (BASIC и VBA)

= 20 / SIN(А1) / КОРЕНЬ(3) (Excel вне VBA)

(Mathcad)

Mathcad максимально учитывает тот факт, что пользователь, изучая математику в школе и в вузе, усвоил, что часть математики “живет” в виде функции (синус, например, - см. выше), а часть - в виде операторов: корень, факториал, дифференциал, интеграл, сумма, произведение и многое другое (см. рис.3), что и в среде языков программирования, и в среде Excel вопреки привычке (вторая натура пользователя) реализовано сугубо в виде функций. Многие функции Excel при переводе пакета на русский язык получили новые названия. Из-за этого возникают разного рода казусы. Так функция вычисления квадратного корня имеет два имени — SQR и КОРЕНЬ (см. выше). Символы операторов живут вне языкового Вавилона и понятны всякому мало-мальски образованному человеку — англичанину, китайцу, арабу, русскому: интеграл он и в Африке интеграл.

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

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

2. “Волшебный лист” Excel (Spreadsheet) разлинован на столбцы и строки, пересечение которых (ячейка) - это то место, куда пользователь должен заносить текст, число, формулу. Но таблица (или реляционная база данных) — это хоть и широко распространенная, но далеко не единственная форма записи информации. “Волшебный лист” Mathcad (Document, Worksheet) более приближен к обычному листу бумаги, на котором человек волен писать что угодно и где угодно, включая, естественно и таблицы (матрицы).

3. Решение любой задачи в любой программной среде, как правило, начинается с ввода исходных данных. Но при работе с Mathcad присутствует особенность, позволяющая называть этот пакет не просто математическим, а физико-математическим пакетом. Решая физическую задачу (школьную задачу по физике, например), мы часто путаемся не в формулах (все гениальные формулы, затрагивающие основы мироздания, просты — F = a m, E = m c² и т.д.) и не в счете (под рукой персональный компьютер или, на худой конец, калькулятор), а в размерностях. Мало того, что есть различные системы измерений (СИ, британская и др.), в рамках одной системы нет единообразия (метры-милиметры, футы-дюймы).

Работая с языком VBA (или с каким-то другим), вводя переменные и задавая им определенный тип, программист заботится не о сути решаемой задачи, а о... памяти машины. Тип числовой переменной с точки зрения программиста-прикладника — это атавизм тех времен, когда память машины была одним из лимитирующих факторов при решении задачи. Пакет Mathcad в этом отношении расточителен — он присваивает всем числовым переменным двойную точность с 15 знаками в мантиссе. Эти переменные предстают перед глазами пользователя либо в целочисленном (17, например), либо в вещественном (3.14, 5.5Ч 10^-7, а не 5.5E-07, как в среде Excel), либо в комплексном (1.84 + 2.2 i) виде. Кроме того, переменная может хранить скаляр, вектор или матрицу. Через знак “:=” в среде Mathcad можно присвоить переменной не только конкретную величину (20, 1, 2, 30 — математика задачи), но и размерность (Ньютон, метр, угловой градус — физика задачи). Что-то подобное есть и в языке VBA, когда числовая переменная “заглатывает” и свое значение, и свой тип: A = 20%, B = 1&, C = 2.2!, D = 3.3# и Е = 4.4@, опираясь на суффиксы % (простая целочисленная), & (длинная целочисленная), ! (простая вещественная; этот символ обычно опускают), # (двойная вещественная) и @ (сверхдлинная целочисленная). В среде Mathcad для присваивания размерности величине за ней ставится знак умножения и вводится название соответствующей размерности (m — метры, Newton — Ньютоны и т.д.). А можно поступить по-другому — нажать на панели инструментов кнопку с изображением мерной кружки. После этого на дисплее появится окно со списками физических величин (рис.7 — энергия, сила, индуктивность, освещенность, длина...) с соответствующими им размерностями (для силы это дина, килограмм силы, Ньютон, фунт силы), одну из которых можно вставить в Mathcad-документ.

Рис.7. Мастер ввода размерностей в среде Mathcad

Касаясь переменной, приходится говорить о ее типе. Ячейка электронной таблицы имеет определенный формат хранимой информации и может быть отформатирована для хранения числового и денежного значения, текста, календарной даты, времени и всего другого (“офисные” единицы измерения — см. рис.8).

Рис.8. Окно форматирования ячейки электронной таблицы Excel

В среде Excel не ведется контроль размерностей: можно безо всяких последствий (без “ругани” компьютера) сложить почтовый индекс, например, с номером телефона. Среда Mathcad такого “безобразия” не допустит (рис.9):

Рис.9. Контроль размерностей в среде Mathcad

В Mathcad-документе на рис.9 рассчитывается средняя скорость, к примеру, самолета, пролетевшего два расстояния (125 миль и 215 километров) за 25 минут. Найденная скорость выдается в затребованных пользователем размерностях: километры в час (kph) и мили в час (mph). Но если пользователю вздумается измерять одно из расстояний в килограммах, то переменные от стыда (за пользователя) покраснеют и появится соответствующее сообщение об ошибке.

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

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

Гибридность решения задачи (сочетание численных и аналитических методов) в среде Mathcad особо проявляется при включенном режиме Optimize (Оптимизировать). В этом режиме программа, столкнувшись с численной задачей (вычисление определенного интеграла, например), не будет вызывать соответствующую процедуру, реализующую численный метод (метод прямоугольников или трапеций), а постарается найти первообразную подынтегрального выражения и работать уже с ней (рис.10):

Вычисление тройного интеграла численным методом

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

Формула, упростившая расчет тройного интеграла

Рис.10. Пример оптимизации расчетов в среде Mathcad

Первое вычисление значения переменной S длится порядка пяти секунд (Pentium 166) из-за того, что Mathcad по умолчанию для вычисления тройного интеграла использует численный метод (замена криволинейной трапеции на набор множества прямоугольников с последующим суммированием их площадей). Второе же вычисление значения переменной S длится доли секунды, т.к. пользователем дана директива на оптимизацию расчетов. После этого система Mathcad ищет первообразную подынтегрального выражения (успешное завершение поиска отмечается красной звездочкой, появляющейся правее выражения) и работает уже с ней. Оптимизация через аналитические преобразования не только ускоряет расчеты, но делает их более точными — сравните ответы на рис.10.

Можно продолжить список преимуществ, пардон, особенностей Mathcad по сравнению с Excel, но лучше эти пакеты не противопоставлять, а заставить их трудится совместно. Эта идея реализована в среде программы MathConnex, входящей в состав Mathcad 7 Pro.

MathConnex (в бета-версии эта программа называлась Mathcad Explorer — Mathcad-проводник) позволяет интегрировать различные приложения Windows (Excel, Mathcad, MatLab, Axum) и организовывать передачу данных между ними.

На рис.11 представлено решение в среде MathConnex задачи из рассказа А.П.Чехова “Репетитор”: Купец купил 138 аршин черного и синего сукна за 540 рублей. Спрашивается, сколько аршин купил он того и другого, если синее стоило 5 рублей за аршин, а черное 3?

Рис.11. Решение задачи о купце и сукне в среде программы MathConnex

На рабочем столе MathConnex (рис.11) находятся три компоненты (Components): электронная таблица Excel (вверху) с одним выходом, Mathcad-документ (в середине; там комментарии прописаны синим шрифтом, а рабочие формулы — черным) с одним входом и одним выходом и еще одна электронная таблица Excel (внизу) с одним входом. Входы и выходы компонент пользователь соединяет линиями с помощью протяжки мышью. При создании рабочего листа MathConnex (а это делается довольно просто: нажимается кнопка с пиктограммой соответствующей компоненты (см. ниже), и “перетаскивается” на рабочий лист — обычная технология визуального программирования) появляются диалоговые окна для задания параметров компонент. На рис.12 показано одно из таких окон, работая с которым пользователь задает число входов (Inputs) и выходов (Outputs) у электронной таблицы, а также связывает с ними области ячеек.

Рис.12. Окно задания областей входных и выходных параметров при вставке электронной таблицы в среду MathConnex

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

Из верхней таблицы на рис.11 значения, хранящиеся в ячейках C2:C5, перекачиваются в Mathcad-документ, где их “подхватывает” системная переменная in0 (нулевой вход). Элементы вектора in0 (in0₀, in0₁,in0₂и in0₃) формируют вектор B и матрицу A, которые входят в систему линейных алгебраических уравнений A Ч X = B. По-другому ее можно записать так:

1 Ч X₀ + 1 Ч X₁ = Всего сукна

Стоимость синего сукна Ч X₀ + Стоимость черного сукна Ч X₁ = Всего денег

Пакет Mathcad оборудован инструментами (функции и операторы) решения задач линейной алгебры, что позволяет справиться с нашей задачей одним оператором X = A^-1 Ч B, которого в среде Excel нет. Ответ присваивается системной переменной out0 и пересылается в нижнюю таблицу Excel — в область C3:C4.

Если курсором мыши два раза щелкнуть по верхней таблице, то она окажется в своем привычном окружении Excel (рис.13):

Рис.13. Электронная таблица Excel и среде программы MathConnex

После этого в таблицу можно ввести новые исходные данные (область ячеек C2:C5) и повторить расчет.

Если курсором мыши два раза щелкнуть по Mathcad-компоненте, то он попадет в соответствующую Mathcad-среду. Щелканье по компонентам правой кнопкой мыши вызывает инструменты форматирования — обычная технология Windows 95.

В среде MathConnex имеется 16 компонент (см. левую панель на рис.11), разбитых на четыре группы:

Ввод и вывод данных:

— ввод локальных констант; на рис.11 можно обойтись без верхней Excel-таблицы, вводя исходные данные (константы 138, 540, 5 и 3) прямо в Mathcad-компоненту;

— ввод глобальных констант — констант, доступных во всех компонентах;

— чтение (запись) данных из файлов на дисках;

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

Просмотр результата:

— “инспектор”; эту компоненту обычно вставляют на линиях для контроля потоков данных;

— график MathConnex (не просто график, а скорее осциллограф, позволяющий визуализировать динамику изменения неких величин);

— график Axum (графики можно построить и в Excel- и Mathcad-компонентах).

Вычисление:

— Mathcad;

— Excel;

— MatLab;

— калькулятор MathConnex; его используют для небольших вычислений, когда не имеет смысла задействовать один из вышеперечисленных “монстров” — Excel, Mathcad или MatLab.

Управление потоками данных:

— выключатель;

— слияние потоков;

— разветвлитель по условию;

— стоп/пауза.

Кнопка позволят вводить метки (комментарии).

MathConnex — это одно из средств визуализации OLE2-технологии. Раньше такие программно-аппаратные средства решения задач назывались системными интеграторами. Они предназначены, в том числе, и для ситуационного моделирования.

Глядя на рис.11, автор вспоминает студенческие годы и МН-7 — легендарную аналоговую вычислительную машину (ABM). Это был железный сундук, под верхней крышкой которого размещалась панель с гнездами. У МН-7 тоже был набор “компонентов” (реальных, а не виртуальных — емкости, катушки, амперметры для выставления констант...) и двумерный график — осциллограф. Установив эти “компоненты” в машину и, по примеру телефонных барышень, соединив их проводками, можно было моделировать тот же полет искусственного спутника или работу отдельного регулятора в системе автоматического управления.

Получается так, что от чего ушли, к тому и пришли, правда, уже на качественно ином уровне: MathConnex — это виртуальная ABM (точнее, гибридная ЭВМ), на которой можно без особых трудов проводить прекрасные лабораторные работы по математике, физике, химии, теории автоматического регулирования и т.д. При этом пригодятся наработки, созданные и в среде Mathcad, и в среде Excel.

Литература:

Очков В.Ф. “Решатель Excel 5.0: Взгляд шутника, дериватора и эстета”. КомпьютерПресс, 3’1995.

Очков В.Ф. “Mathcad PLUS 6.0 для студентов и инженеров”. КомпьютерПресс, 1996.

Очков В.Ф. “Mathcad 7 Pro для студентов и инженеров”. КомпьютерПресс, 1998.

Контакт с автором ochkov@twt.mpei.ac.ru