Курсовая работа (т): Программирование пользовательских функций MS Excel

.1 Пояснение пользовательских функций MS.Excel

Создание экранной формы и ввод исходных данных

Экранная форма для решения в MS Excel представлена ниже.

В экранной форме на каждой переменной и каждому коэффициенту задачи поставлена в соответствие конкретная ячейка на листе Excel. Имя ячейки состоит из буквы, обозначающей столбец, и цифры, обозначающей строку, на пересечении которых находится объект транспортной задачи. Так, например, следующим переменным соответствуют ячейки B13 (X1), C13 (X2), D13 (X3), E13 (X4), F13 (X5). Другим ячейкам соответствуют ячейки диапазона B14:F15. Коэффициентам соответствуют ячейки B4:F6, правым частям ограничений соответствуют ячейки G34:G16.

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

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

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

курсор в поле H13;

нажав кнопку "", вызовите окно "Мастер функций - шаг 1 из 2";

выберите в окне "Категория" категорию "Математические";

в окне "Функция" выберите функцию СУММ (рис. 3);

в появившемся окне "СУММ" в строку "Число 1" введите выражение B13:F13

курсор в поле I13;

нажав кнопку "", вызовите окно "Мастер функций - шаг 1 из 2";

выберите в окне "Категория" категорию "Математические";

в окне "Функция" выберите функцию СУММПРОИЗВ (рис. 5);

в появившемся окне "СУММПРОИЗВ" в строку "Массив 1" введите выражение B4:F4, а в строку "Массив 2" - выражение B13:F13 (рис. 6);

Левые части ограничений задачи (1) представляют собой сумму произведений каждой из ячеек, отведенных для значений переменных задачи (B13:F15), на соответствующую ячейку, отведенную для коэффициентов конкретного ограничения (B4:F6).

Формулы, описывающие ограничения модели (1)

Дальнейшие действия производятся в окне "Поиск решения", которое вызывается из меню "Сервис"

поставьте курсор в поле "Установить целевую ячейку";

введите адрес целевой ячейки $I$16 или сделайте одно нажатие левой клавиши мыши на целевую ячейку в экранной форме ¾ это будет равносильно вводу адреса с клавиатуры;

введите направление оптимизации ЦФ, щелкнув один раз левой клавишей мыши по селекторной кнопке "минимальному значению".

Ввод ограничений и граничных условий

Задание ячеек переменных

В окно "Поиск решения" в поле "Изменяя ячейки" впишите адреса $B$13:$F$15. Необходимые адреса можно вносить в поле "Изменяя ячейки" и автоматически путем выделения мышью соответствующих ячеек переменных непосредственно в экранной форме.

Задание граничных условий для допустимых значений переменных

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

В поле "Ссылка на ячейку" введите адреса ячеек переменных $B$13:$F$15. Это можно сделать как с клавиатуры, так и путем выделения мышью всех ячеек переменных непосредственно в экранной форме.

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

В поле "Ограничение" введите 0.

excel таблиц формула электронный

Добавление условия неотрицательности переменных задачи (1)

Задание знаков ограничений , , =

Нажмите кнопку "Добавить" в окне "Добавление ограничения".

В поле "Ссылка на ячейку" введите адрес ячейки левой части конкретного ограничения, например $B$17.

В соответствии с условием задачи (1) выбрать в поле знака необходимый знак, например, =.

В поле "Ограничение" введите адрес ячейки правой части рассматриваемого ограничения, например $B$16.

Аналогично введите ограничения необходимо задать для остальных ячеек.

Подтвердите ввод всех перечисленных выше условий нажатием кнопки OK.

Окно "Поиск решения" после ввода всех необходимых данных задачи (1) представлено на рис. 7.

Если при вводе условия задачи возникает необходимость в изменении или удалении внесенных ограничений или граничных условий, то это делают, нажав кнопки "Изменить" или "Удалить" (см. рис. 7).

3. Физическая постановка задач

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

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

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

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

В результате численного решения двумерной задачи движения расплава в объеме ФО установлено, что при попадании в поток дутья с нулевой начальной скоростью частица движется по горизонтальной траектории; ее вертикальное отклонение в конце фурменной полости несущественно и во всех случаях не превышает 1 мм. В случае наличия начальной вертикальной составляющей скорости ее поступления в поток дутья (5,0 - 5,5 м/с) отклонение частицы от горизонтальной траектории в конце фурменной полости не будет превышать 8 - 9 мм. Время пребывания частицы в потоке дутья незначительно и составляет для базового варианта ~ 0,015 с. Повышение диаметра частицы расплава в 3 раза приводит к увеличению времени ее пребывания в потоке дутья ~ в 1,7 раза, при этом во всех случаях горизонтальная составляющая скорости частицы не достигала величины скорости газового потока.

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

Генератор случайных чисел.

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

Генератор случайных чисел выбирает числа ξ1, ξ2, ξ3 … случайным образом из интервала 0

ξi 1. Это означает, что вероятность р(ξi) dξi для ξi оказаться между ξi и ξi + dξi есть dξi,

если 0 ξi 1. Т.е. р (ξi) = 1.

Алгоритм метода Монте-Карло в задачах переноса излучений.

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

Первый шаг выбор направления движения нейтрона. Для этого используются два первых случайных числа ξ1 и ξ2. Азимутальный угол можно выбрать равным φ = 2 ξ1, а косинус полярного угла µ = 2 ξ2  1. Такой выбор обусловлен изотропностью источника, и2 все начальные значения угловых переменных φ и µ, описывающих направление полета нейтрона, равновероятны в интервалах 0 φ 2 и 1 µ 1.

Следующий шаг  нахождение места первого столкновения. Пусть сечение в выбранном направлении на расстоянии s от источника обозначено σ(s). Тогда вероятность того, что нейтрон испытает столкновение между s и s + ds, равна:

P(s) ds = σ(s) exp [ σ(s’)] ds.

Для нахождения s  места первого столкновения используется третье случайное число ξ3:

ln ξ3 =  σ(s’).

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

. Задачи в базах MS Excel

Задача№1

Расчет КПД двигателя автомобиля

Зная необходимые технические данные автомобиля, определим КПД его двигателя. На примере внедорожника LADA-БРОНТО РЫСЬ

Решение задачи:

) Заполнили таблицу техническими характеристиками внедорожников

) (Свой внедорожник) внесли данные в строку таблицы.

) Заполнили пропущенные столбцы с помощью вычислений (на доскефизические формулы, на компьютере вычисление в таблице).

) Получили ответ (нашего внедорожника)

Пример программы MS Excel и с использованием пользовательских функций

Задача№2. Солнечная система.

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

Солнечная система.

1)      В ячейке А1 напечатали заголовок: Солнечная система.

2)      Расположили заголовок по центру относительно таблицы:

v  Выделили диапазон ячеек А1 : Е1

v  Щелкнули по кнопке Объединили и поместили в центре на панели инструментов.

3)      В ячейку А2 внесли текст: Планета

4)      В диапазон А3 : А8 ввели название планет.

)        В ячейку В2 внесли текст: Период обращения ( в земных годах).

)        В ячейку С2 внесли текст: Расстояние ( в млн. км.).

)        В ячейку D2 внесли текст: Диаметр ( в тыс. км.).

)        В ячейку Е2 внесли текст: Спутники.

)        Выделили диапазон ячеек В2 : D2, выполнили команду Формат/Ячейки на вкладке Выравнивание активизируйте флажок Перенесли по словам, нажали ОК.

)        Заполнили диапазон В3 : Е3 числами.

)        Отформатировали текст в таблице

v  Шрифт в заголовке - Arial Cyr, размер 14, синий цвет, полужирное начертание.

v  Шрифт в таблице - Times New Roman Cyr, размер 12, красный цвет, начертание полужирный курсив

12)    Текстовые данные выровняли по центру.

13)    Задали рамку для таблицы:

v  Выделили таблицу ( без заголовка), выполните команду Формат/Ячейки, вкладка Граница. Установите цвет - синий, Тип линии - двойной и щелкнули по кнопке Внешние, затем выбрали Тип линии - пунктир и щелкнули по кнопке Внутренние, нажали ОК.

v  Выделили диапазон ячеек А2 : Е2, выполнили команду Формат/Ячейки вкладка Граница, щелкнули оп кнопке с нижней границей в группе Отдельные.

14)    Задали заливку для второй строки таблицы: Выполнили команду Формат/Ячейки, вкладка Вид.

Заключение

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

·        Краткие сведения об электронной таблице MS.Excel

·        Основные пользовательские функции MS.Excel

·        Задачи в базах MS.Excel

·        Составление программной функции

В данной курсовой работе я описала Программирование пользовательских функций в MS Excel.

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

Список использованных источников

1.      Программирование в Microsoft Excel на примерах +CD-ROM.C Кашаев/БХВ-Петербург,2007-320с

.        Профессиональное программирование на VBA в Excel/2002 Уокенбах Джон/М Издательский дом

3.      Microsoft Office Excel 2007.Библия пользователя +CD-ROM. Уокенбах Джона/Диалектика 2008-816с

.        Шафрин Ю.А. Основы компьютерной технологии. Учебное пособие для 7 - 11 классов по курсу «Информатика и вычислительная техника» - Москва: ABF,1996

.        Ефимова О.В., Моисеева М.В., Ю.А. Шафрин Практикум по компьютерной технологии. Примеры и упражнения. Пособие по курсу «Информатика и вычислительная техника» - Москва: ABF,1997

.        Горячев А., Шафрин Ю. Практикум по информационным технологиям. М.: Лаборатория базовых знаний, 2001

.        Семакин И.Г., Шеина Т.Ю. Преподавание курса информатики в средней школе. М.: Лаборатория базовых знаний, 2002