СОДЕРЖАНИЕ

 
 

ПРИЛОЖЕНИЕ 1. Распечатки рабочих листов табличного процессора Excel 

ПРИЛОЖЕНИЕ 2. Распечатки вкладок пользовательской формы 

ПРИЛОЖЕНИЕ 3. Распечатки VBA процедур  
 
 

Общие сведения о решении  задач на ЭВМ

 

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

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

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

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

1. Постановка задачи:

•   сбор информации о задаче;

•   формулировка условия задачи;

•   определение конечных целей решения  задачи;

•   определение формы выдачи результатов;

•   описание данных (их типов, диапазонов величин, структуры и т. п.).

2. Анализ и исследование  задачи, модели:

•   анализ существующих аналогов;

•   анализ технических и программных  средств;

•   разработка математической модели;

•   разработка структур данных.

3. Разработка алгоритма:

•   выбор метода проектирования алгоритма;

•  выбор формы записи алгоритма (блок-схемы, псевдокод и др.);

•    выбор тестов и метода тестирования;

•    проектирование алгоритма.

4. Программирование:

•   выбор языка программирования;

•   уточнение способов организации  данных;

•   запись алгоритма на выбранном языке

программирования.

5. Тестирование и  отладка:

•   синтаксическая отладка;

•   отладка семантики и логической структуры;

•    тестовые расчеты и анализ результатов  тестирования;

•   совершенствование программы.

  6. Анализ результатов решения задачи и уточнение в случае необходимости математической модели с повторным выполнением этапов 2-5.

7. Сопровождение программы:

•   доработка программы для решения  конкретных задач;

• составление  документации к решенной задаче, к  математической модели, к алгоритму, к программе, к набору тестов, к использованию.

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

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

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

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

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

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

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

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

      Иными словами, алгоритм — это точное предписание, ведущее от варьируемых исходных данных к искомому результату. 
 

      Основные  свойства алгоритма:

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

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

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

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

      Способы описания алгоритма

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

      Блок-схема - графический способ описания алгоритма. Обладает прекрасной наглядностью.

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

      Запись  алгоритма на одном из алгоритмических языков (программная реализация на одном из ЯП). 
 

1. ПОСТАНОВКА  ЗАДАЧИ

 

      В процессе выполнения курсовой работы необходимо определить реакции связей составной конструкции. Рассматривается плоская механическая конструкция, находящаяся в равновесии под действием заданных сил и наложенных связей (рис.1.1). Элементы конструкции считаются абсолютно жесткими. Стержни, изображенные сплошными линиями, невесомые. Трение в шарнирах, катках и точках контакта тел отсутствует.

      Рис.1.1.

      Числовые  значения величин задаются формулами (1-5):

    

      Требуется определить реакции шарнира О  и Катковой опоры С, усилия в невесомых стержнях, давление в точке D.

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

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

1. средствами Excel
2. средствами VBA

      Для проверки правильности решения задачи следует составить уравнение, не совпадающее ни с одним из уравнений. Подстановка решения системы в это уравнение должна обращать его в тождество. За счет погрешностей счета и погрешностей округления даже в правильно решенной задаче левая часть контрольного уравнения после подстановки не обращается в ноль и дает «невязку» Δ. Точность решения считается удовлетворительной, если относительная «невязка»:

    

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

      При выборе реакции связи Excel в области построения графика должен отобразить соответствующий график. Расчет дополняется условием: определить диапазон значений горизонтально направленной силы F, при которых односторонняя связь в точке D не нарушается, т.е. давление удовлетворяет условию N_D>0.

      Уравнения равновесия составляются по рассмотренной  методике и решаются для двух значений F: один раз – для заданного по условию расчета, другой раз – для F=0.

      По  полученным данным найти коэффициенты уравнения прямой: k и q (y=kx+q).

      Учитывая  линейный характер зависимости реакций  от F, строим по результатам счета график N_D(F).

2. ОПИСАНИЕ ИСПОЛЬЗУЕМЫХ МЕТОДОВ И ПРИЕМОВ

 

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

      При выполнении курсовой работы использовались следующие возможности табличного процессора MS Excel 2003. Для решения систем линейных уравнений средствами Excel была использована надстройка «Поиск решения». Кроме того, для представления графических результатов была использована возможность работы Excel с диаграммами. Также бы использован встроенный язык программирования Visual Basic for Applications.

1. Метод Гаусса

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

     Опишем алгоритм решения системы линейных уравнений с помощью метода Гаусса.

      Пусть имеется система N линейных уравнений, формула (2.1.1).

      a₁₁x₁ + a₁₂x₂ + a₁₃x₃ + ... a_1Nx_N = b₁

      a₂₁x₁ + a₂₂x₂ + a₂₃x₃ + ... a_2Nx_N = b₂

      a₃₁x₁ + a₃₂x₂ + a₃₃x₃ + ... a_3Nx_N = b₃            (2.1.1)

      ...

      a_N1x₁ + a_N2x₂ + a_N3x₃ + ... a_NNx_N = b_N

      где x_i - неизвестные, a_ij - коэффициенты при неизвестных, b_i - свободные члены в уравнениях, i,j принимают значения от 1 до N.

      Цель  задачи - зная a_ij и b_i найти x_i.

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

      a₁₁x₁ +  a₁₂x₂ +  a₁₃x₃ +  ...  a_1Nx_N = b₁

      a₂₂x₂ +  a₂₃x₃ +  ...  a_2Nx_N = b₂

                              a₃₃x₃ +  ...  a_3Nx_N = b₃            (2.1.2)

      ...

                                          ...  a_NNx_N = b_N

      Особенность этой системы - в строках с номером i все коэффициенты a_ij при j<i равны нулю.

      Если  мы смогли привести нашу систему уравнений  к такому треугольному виду, то решить уравнения уже просто. Из последнего уравнения находим x_N= b_N / a_NN. Дальше подставляем его в предпоследнее уравнение и находим из него x_N-1. Подставляем оба найденных решения в следующее с конца уравнение и находим x_N-2. И так далее, пока не найдем x₁, на чем решение заканчивается. Такая процедура называется обратной прогонкой.

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

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

      Нужно, чтобы во второй строке получилось уравнение, в которой отсутствует член при x₁. Прибавим к этой строке первую строку, умноженную на некоторое число M, формула (2.1.3).

      (a₁₁x₁ + a₁₂x₂ + a₁₃x₃ + ... a_1Nx_N = b₁)*M +           (2.1.3)

      a₂₁x₁ + a₂₂x₂ + a₂₃x₃ + ... a_2Nx_N = b₂

      Получим

      (a₁₁*М + a₂₁) x₁ + ... = b₁*M + b₂            (2.1.4)

      Для того чтобы член при x₁ равнялся нулю, нужно, чтобы M = - a₂₁ / a₁₁. Проделав эту операцию, получившееся уравнение запишем вместо второго и приступим к третьему уравнению. К нему мы прибавим первое уравнение, умноженное на M = - a₃₁ / a₁₁ и тоже получим ноль вместо члена при x₁. Такую операцию нужно проделать над всеми остальными уравнениями. В результате получим систему такого вида:

      a₁₁x₁ +  a₁₂x₂ +  a₁₃x₃ +  ...  a_1Nx_N = b₁

                  a₂₂x₂ +  a₂₃x₃ +  ...  a_2Nx_N = b₂

                  a₃₂x₂ +  a₃₃x₃ +  ...  a_3Nx_N = b₃        (2.1.5)

      ...

                  a_N2x₂ +  a_N3x₃ +  ...  a_NNx_N = b_N

      После этого будем избавляться от членов при x₂ в третьем, четвертом, N-ом уравнении. Для этого нужно к уравнению с j-м номером прибавить 2-ое уравнение, умноженное на M = - a_j2 / a₂₂. Проделав эту операцию над всеми остальными уравнениями, получим систему, где нет членов с x₂ в уравнениях с номером больше 2.

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

2.   Использование надстройки «Поиск решения»

 

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

      В данной курсовой работе надстройка Поиск решения применяется для решения линейных систем.

      Перед вызовом надстройки Поиск решения необходимо разместить в ячейках таблицы исходные данные и целевую функцию. В качестве исходных данных выступают коэффициенты X₀, Y₀, N_D, N_С, S₁, S₂, и вектор свободных членов в системе уравнений составной конструкции. Для вычисления целевой функции используем выражение:

      = СУММПРОИЗВ(A4:F4;$A$12:$F$12)

      Функция СУММПРОИЗВ(массив1;массив2;массив3; ...) перемножает соответствующие элементы заданных массивов и возвращает сумму их произведений.

      Массив1, массив2, массив3, ... — от 2 до 30 массивов, чьи компоненты нужно перемножить, а затем сложить.

      Число1, число2, ... — от 1 до 30 аргументов, квадраты которых суммируются.

3. Работа  с Диаграммами

 

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

      Чтобы создать в Excel базовую диаграмму, которую впоследствии можно изменять и форматировать, сначала нужно ввести на лист данные для этой диаграммы. Затем просто выделить эти данные и выбрать нужный тип диаграммы на ленте (вкладка Вставка, группа Диаграммы).

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

4. Visual Basic for Applications

 

      Visual Basic для приложений (VBA -Visual Basic for Applications) является инструментальным средством разработки приложений в среде основных компонентов Office: Word, Excel, PowerPoint, Access, FrontPage и Outlook. По замыслу разработчиков он может использоваться именно как средство разработки приложений, а не только в качестве инструмента настройки пользовательского интерфейса и редактирования макросов.

      VBA непосредственно связан с языком Visual Basic (VB). Основное различие между ними формулируется следующим образом: проекты VBA выполняются только с помощью приложения, поддерживающего VBA, в то время как Visual Basic позволяет создавать полностью автономные приложения. С другой стороны, синтаксис языков VBA и VB практически одинаков. Оба языка имеют почти одинаковые интегрированные среды разработки.

      Visual Basic для приложений является объектно-ориентированным  языком, предоставляющим возможности визуального программирования. VBA содержит иерархию объектов, каждому из которых соответствует свой набор методов и свойств. Объекты представляют собой фундаментальные «строительные» блоки – почти все, что делается в среде VBA, включает модификацию объектов.

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

1. Основное приложение - то приложение, в котором разрабатывается проект VBA.
2. Среда разработки - IDE - окно с меню, другие окна и элементы, которые применяются при разработке проекта.
3. Формы. С помощью VBA можно разработать формы и процедуры обработки событий, возникающих в этих формах.
4. Код - результат программирования.
5. Модули кода. Содержат процедуры, каждая из которых относится к автоматизации решения одной конкретной задачи.
6. Модули класса. Содержат собственные объекты пользователя, описание которых включается в модули класса. Каждый модуль класса содержит об одном типе объекта. Только Word позволяет хранить объекты не только в рабочих документах, но и в шаблонах, а Power Point и Exсel хранят модули только в рабочих документах.

      Процедура – поименованный набор инструкций.

      Процедура имеет три основные части

1. Описание процедуры – большая часть кода приложения.
2. Непосредственно описание, которое рассматривается как инструкция для описания переменных, констант, типов данных, а также ссылок на функции, которые хранятся во внешних библиотеках.
3. Инструкция – единица кода, которая является операцией, описанием или областью определения.

2.5. Порядок работы программы

      При открытии книги MS Excel автоматически запускается форма-заставка, которая отображается 5 с, после чего она закрывается и запускается Основная пользовательская форма.

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

      Если  основная пользовательская форма будет  закрыта, то запустить ее можно будет  из редактора Visual Basic. Для этого необходимо открыть форму в редакторе Visual Basic и выбрать пункт Run Sub/UserForm меню Run или нажать кнопку F5.

      После ввода исходных данных, необходимо перейти на лист Решение и выполнить команду Сервис — Поиск решения, после чего в ячейках А12:F12 будет результат решения.

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

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

      Представление графических результатов  на листе Excel

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

      Так, например,  для построения графика зависимости силы F от реакции S1 необходимо выбрать силу F и реакцию S1.

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

3. БЛОК  СХЕМЫ И ОПИСАНИЕ ПРОЦЕДУР VBA

1. Функция решения системы линейных уравнений методом Гаусса

Рис.3.1.

4. СПИСОК ИСПОЛЬЗУЕМЫХ ОПЕРАТОРОВ

 

      В Microsoft Excel включено четыре вида операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические  операторы

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