Информатика_Excel

Рис.6. Иллюстрация способа определения числа π.

Уравнение окружности имеет вид X2+Y2=1, откуда Y = 1−X2 . Если генерировать случайные точки, равномерно распределенные по площади квадрата, то доля точек, расположенных ниже дуги, и даст ве-

личину, равную одной четверти числа π.

Для генерации случайных чисел, равномерно распределенных в диапазоне от 0 до 1, используется функция СЛЧИС().

Итак, создайте таблицу на чистом листе рабочей книги. Первый столбец будет озаглавлен Х, второй – Y, третий – Z. В первых двух столбцах должны содержаться координаты точек – случайные числа, равномерно распределенные в диапазоне от 0 до 1, то есть функция СЛЧИС(). Z равно единице, если точка находится ниже дуги, и нулю, если выше. Для этого используйте функцию ЕСЛИ.

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

СЧЁТ.

Произведите расчет вначале для 100 точек.

Определите, сколько точек должно быть использовано при расчетах, чтобы точность определения составляла три знака, то есть чтобы было получено значение: 3,14. Для этого несколько раз нажмите клавишу F9, что вызовет пересчет всех формул листа, в том числе - функций генерации случайных чисел. Если первые три цифры полученного числа отличаются от 3,14, увеличьте число точек.

Постройте график функции Y(X) для точек, расположенных ниже дуги окружности.

Результат работы

Рабочий лист с расчетом числа π, график функции, умение объяснить результаты.

41

Дополнительные задания

1.Проверьте качество генератора случайных чисел. Для этого, сгенерируйте большое количество случайных чисел и подсчитайте, какие доли сгенерированных чисел попали в диапазон от 0 до 0.1, от 0.1 до 0.2 и т.д. В идеальном случае эти должны быть равны 0.1.

2.Постройте средство определения экстремума (минимума) функции Y=X2 в диапазоне от -2 до 2 с помощью случайных чисел. Для нахождения экстремума генерируются значения Х, по ним определяются значения Y. Среди полученных значений Y выбирается наименьшее.

3.Создайте формулу, которая определит, при каком значении аргумента достигается экстремум функции.

11. Моделирование динамических процессов

Цель работы – изучить принципы моделирования динамических процессов

Общие сведения

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

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

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

Переменные задачи: Н – количество людей, не знающих о товаре; З1 – количество людей, знающих о товаре фирмы 1; З2 – количество людей, знающих о товаре фирмы 2; И1 – количество людей, имеющих товар фирмы 1; И2 – количество людей, имеющих товар фирмы 2; Р1 – коэффициент воздействия рекламы фирмы 1: доля людей, не знающих о товаре, которые узнали о товаре фирмы 1 за единицу времени; Р2 – коэффициент воздействия рекламы фирмы 2: доля людей, не знающих о товаре, которые узнали о товаре фирмы 2 за единицу времени; С – коэффициент распространения слухов: количество обсуждений на одну пару, состоящую из человека, имеющего товар, и человека, не знающего о нем, за единицу времени; Б – коэффициент благосостояния – доля людей, купивших товар, среди людей, знающих о нем, за единицу времени.

42

Производная обозначается символом «'». Процесс описывается системой уравнений:

Уравнения, описывающие систему, выглядят следующим образом:

Н'=-(Н*И1*С+Н*Р1)-(Н*И2*С+Н*Р2); З1'=Н*И1*С+Н*Р1-З1*Б; З2'=Н*И2*С+Н*Р2-З2*Б;

И1'=З1*Б; И2'=З2*Б.

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

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

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

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

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

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

43

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

Н(0)= 100(%);

З1=0; З2=0; И1=0; И2=0.

Если решение производится не с помощью специализированных средств, а, например, средствами Excel, то следует вручную перейти к разностной форме, используя определение производной:

X' = lim	x(t +	t) − x(t)	.
df t→0		t

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

(H(t+ t)-Н(t))/ t=-(Н(t)*И1(t)*С+Н(t)*Р1)-(Н(t)*И2(t)*С+Н(t)*Р2);

(З1(t+ t)-З1(t))/ t=Н(t)*И1(t)*С+Н(t)*Р1-З1(t)*Б; (З2(t+ t)-З2(t))/ t=Н(t)*И2(t)*С+Н(t)*Р2-З2(t)*Б; (И1(t+ t)-И1(t))/ t=З1(t)*Б;

(И2(t+ t)-И2(t))/ t=З2(t)*Б.

Отсюда путем умножения на t и переноса значения переменных в момент t в правую часть каждого уравнения могут быть получены итеративные формулы пересчета значений всех переменных:

H(t+ t)=Н(t)-(Н(t)*И1(t)*С+Н(t)*Р1+Н(t)*И2(t)*С+Н(t)*Р2)* t; З1(t+ t)=З1(t)+(Н(t)*И1(t)*С+Н(t)*Р1-З1(t)*Б)* t;

З2(t+ t)=З2(t)+(Н(t)*И2(t)*С+Н(t)*Р2-З2(t)*Б) t; И1(t+ t)=И1(t)+З1(t)*Б* t;

И2(t+ t)=И2(t)+З2(t)*Б* t.

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

44

Задание

Постройте графики переходных процессов для переменных З1, З2, И1, И2 при указанных выше начальных условиях и t=0,01 единицы времени. В качестве единицы времени выбирается 1 месяц. Значения коэффициентов: С=0,5; Р1=0.6; Р2=0.4; Б=0.5. Период моделирования должен составлять 10 месяцев.

Порядок выполнения

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

Параметры модели должны задаваться явным образом (в отдельных ячейках).

График строится обычным образом.

Результат работы

Графики переходных процессов, изменяющиеся при изменении параметров модели.

Умение интерпретировать изменения графиков.

Дополнительные задания

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

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

12. Макросы в MS Excel

Цель работы – научиться использовать в Excel макросы

Общие сведения

Макрос – это запись определенной последовательности действий в

Excel. Такая запись выполняется на языке Visual Basic for Applications (VBA).

45

Создавать макросы можно вручную, записывая последовательность команд на языке VBA, или автоматически – «заставить» Excel отслеживать все Ваши действия и записывать их в соответствующий модуль.

На листе рабочей книги можно располагать различные управляющие элементы25 и «заставить» эти элементы работать. Добавление новых управляющих элементов на лист производится с помощью панели инструментов Элементы управления26. Данная панель, в частности, содержит инструмент Кнопка. Нажмите этот инструмент и укажите мышкой место и размер будущей кнопки на листе рабочей книги. Выбрав из контекстного меню на новой кнопке команду Свойства, можно вывести окно свойств данного элемента управления. К числу этих свойств относятся: Caption – надпись на кнопке, BackColor и ForeColor – цвета фона и надписи соответственно. Можно задать и другие свойства.

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

Чтобы узнать, что задает то или иное свойство, выделите его в окне свойств и нажмите клавишу F1.

Полезно запомнить или записать значение свойства Name (оно – самое первое в списке свойств). Это имя, по которому одна кнопка отличается от другой. По имени можно обратиться к кнопке из программы, чтобы изменить ее свойства.

Лучше не изменять имя, даваемое автоматически, так как каждый созданный объект должен иметь уникальное имя.

Только что созданная кнопка находится в режиме Конструктора. Это можно видеть на панели Элементы управления: кнопка Конструктор (на ней изображены треугольник, линейка и карандаш) нажата. В режиме конструктора можно изменять свойства созданных элементов управления, перемещать их, изменять их размеры (для этого служат 8 квадратиков по периметру элемента управления). Для удаления элемента управления достаточно выделить его и нажать клавишу Delete.

Если щелкнуть мышкой по нажатой кнопке Конструктор, она «отожмется» и все созданные Вами элементы управления станут работоспособными. Например, щелчок по ней вызовет ее нажатие.

Самое важное для кнопки – та программа, которая выполняется при ее нажатии. Для того чтобы написать такую программу, следует дважды щелкнуть по кнопке в режиме Конструктора. Откроется окно Visual

25Это элементы, аналогичные тем, которые есть в диалоговых окнах: тексто-

вые поля, кнопки, переключатели, флажки и проч.

26Для включения панели выберите менюÆВидÆПанели инструмен-

тов…Æ Элементы управления.

46

Basic. В нем Вы сразу увидите автоматически созданную заготовку программы:

Private Sub CommandButton1_Click()

End Sub

Первая строка отмечает начало программы. Первое слово (Private) не столь важно в данный момент. Второе (Sub27) обозначает программу. Далее идет имя программы. Оно состоит из имени объекта, с которым эта программа связана (в данном случае это кнопка по имени CommandButton1) и, через символ подчеркивания, – того действия, при котором данная программа активизируется (Click – щелчок). Пустые круглые скобки обязательны и обозначают, что это имя программы.

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

Для первого опыта создания программы предлагается вывести на экран сообщение в диалоговом окне.

Это делается командой

MsgBox “Привет, мир!“

В кавычках указывается выводимый текст28.

Для запуска программы следует вернуться в окно Excel и выключить режим конструктора29.

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

Измените текст в окне программы на нижеследующий

Private Sub CommandButton1_Click()

SayHello

End Sub

27Subroutine – процедура.

28Почти во всех книгах с описанием языков программирования имеется пример аналогичной сложности, удобный для первого знакомства с языком. Во всем мире такие задачи получили название задач класса Hello, world.

29Окно Visual Basic лучше пока не закрывать, чтобы облегчить переход к нему в случае необходимости исправления ошибок или модификации программы. Все открытые окна видны в строке состояния Windows.

47

Sub SayHello()

MsgBox "Привет, мир!"

End Sub

Теперь работа «программного обеспечения» усложнилась: программа

CommandButton1_Click вызывает программу SayHello.

Для создания более сложных программ проще всего использовать режим автозаписи.

Перейдите на лист Excel и нажмите меню Æ Макрос Æ Начать запись30. Запомните предлагаемое автоматически имя макроса или придумайте свое.

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

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

После выполнения примерно десяти действий выберите меню Æ Макрос Æ Остановить запись.

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

Если Вы забыли имя макроса или создали несколько макросов, их имена и записанные в них команды можно найти в окне Visual Basic в части, озаглавленной Проект, в папке Модули31.

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

Задание

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

2.Записать макрос автоматической нумерации строк таблицы и связать его с новой кнопкой на рабочем листе.

3.Для трех листов рабочей книги создать кнопки перехода с листа на лист. На каждом листе требуется создать кнопки перехода на два других листа.

30Макрос это по сути дела программа.

31Навигация здесь похожа на навигацию в Проводнике.

48

Порядок выполнения работы

1.Порядок выполнения первой части описан в указанном разделе. Главная цель этой работы – освоить приемы работы с редактором Visual Basic, поэтому постарайтесь выяснить у преподавателя все неясные вопросы.

2.Создание макроса для автоматической нумерации строк таблицы производится в следующем порядке:

выделите столбец ячеек, в которых будут располагаться номера; начните запись макроса с помощью меню Æ Сервис Æ Макрос Æ

Начать запись… Назовите макрос Нумерация;

на появившейся панели инструментов Остановка записи включите кнопку Относительные ссылки, если она выключена;

занесите 1 в первую ячейку интервала, не снимая выделения с интервала;

выберите меню Æ Правка Æ Заполнить Æ Прогрессия... Æ OK;

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

Запуск макроса производится в окне, вызываемом через меню Æ Сервис

ÆМакрос Æ Макросы;

просмотрите текст макроса; на рабочем листе создайте кнопку и свяжите ее с макросом Нумера-

ция.

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

Результат работы

Работающие пользовательские кнопки на рабочем листе. Умение создавать программы с помощью автозаписи.

13. Создание функции пользователя

Цель работы – получение начальных знаний о программировании на VBA

Общие сведения

Visual Basic For Applications – это сочетание простого в освоении языка программирования и всех вычислительных возможностей Excel. VBA позволяет быстро получать ощутимые результаты – конструировать профессиональные приложения, решающие достаточно сложные задачи в среде

49

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

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

Задание

Создать пользовательскую функцию перевода оценки в баллах в словесную формулировку: из 5 эта функция делает ОТЛИЧНО, из 4 – ХОРОШО и т.д. Если число меньше 1 или больше 5, то результат, выдаваемой функцией, равен НЕТ ОЦЕНКИ.

Порядок выполнения работы

Функции пользователя создаются в стандартном модуле редактора VBA. Редактор VBA активизируется кнопкой Сервис Æ Макрос Æ Редактор Visual Basic. Для создания модуля VBA необходимо вызвать редактор VBA, выбрать команду Вставка Æ Модуль. На листе модуля набрать текст программы расчета функции.

Текст начинается со строки

Function ОЦЕНКА (Балл As Integer) As String

В отличие от простого программного модуля Sub, рассмотренного выше, для вычисления значения функции ей требуется передать параметр Балл (целое число от 1 до 5). Описание действий по вычислению функции должно находиться в последующий строках программы. Программа должна завершаться строкой

End Function

.

Для программирования можно использовать функцию iif, аналогичную рассмотренной выше функции ЕСЛИ32. Разница состоит в названии, в том, что аргументы разделяются запятыми и в том, что VBA иногда не требует заключать аргументы в круглые скобки.

32 Функции, используемые в VBA, отличаются от функций рабочего листа.

50