Использование приложения MS Excel для моделирования различных задач

 
 
 
Кильдишов В. Д. 
 
 
 
 
 
 
 
 
 
Использование приложения  
MS Excel  
для моделирования различных задач 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Москва 
СОЛОН-Пресс 
2020 

УДК 681.3.06 
ББК 32.973.26-018.2 
     К 39 
 
       Кильдишов В.Д. 
К39    Использование приложения MS Excel для моделирования различных задач. — М.: СОЛОН-Пресс, 2020. —156 с.: ил. 
 
ISBN 978-5-91359-145-6 
 
Книга является практическим руководством по моделированию задач с 
использованием приложения MS Excel.  
Книга предназначена для школьников, студентов и преподавателей, которые хотят быть знакомы с MS Excel «на Вы», а также научиться моделированию, разработке моделей, алгоритмов и программ. 
Прочитав эту книгу, Вы научитесь использовать возможности 
MS Excel, о которых ранее не знали. 
 
                                                           ББК 32.973.26-018.2 
 
Сайт издательства «Ремонт и Сервис 21»: www.remserv.ru 
Сайт издательства «СОЛОН-ПРЕСС»: www.solon-press.ru 
 
КНИГА ---- 
 ПОЧТОЙ 
Книги издательства «СОЛОН-ПРЕСС» можно заказать наложенным платежом (оплата 
при получении) по фиксированной цене. Заказ оформляется одним из трех способов: 
1. Послать открытку или письмо по адресу: 123001, Москва, а/я 82. 
2. Оформить заказ можно на сайте w
www.solon-press.ru в разделе «Книга ---- почтой». 
3. Заказать по тел. (499) 254-44-10, (499) 795-73-26. 
Каталог издательства высылается по почте бесплатно. 
При оформлении заказа следует правильно и полностью указать адрес, по которому 
должны быть высланы книги, а также фамилию, имя и отчество получателя. Желательно 
указать дополнительно свой телефон и адрес электронной почты. 
Через Интернет вы можете в любое время получить свежий каталог издательства 
«СОЛОН-ПРЕСС», считав его с адреса www.solon-press.ru/kat.doc. 
Интернет-магазин размещен на сайте w
www.solon-press.ru. 
 
 
По вопросам приобретения обращаться: 
ООО «ПЛАНЕТА АЛЬЯНС»    Тел: (499) 782-38-89,     w
www.alians-kniga.ru 
 
ISBN 978-5-91359-145-6 
 
 
 
  
© Кильдишов В.Д., 2020 
© СОЛОН-Пресс, 2020 
 
2

Содержание 
Предисловие ..................................................................................................... 
5 
 
1. О моделировании, решении задач, программировании  
и MS Excel ......................................................................................................... 
6 
1.1. О решении задач и моделировании .......................................................... 
6 
1.2. О преимуществах  MS Excel при изучении основ  
программирования .......................................................................................... 
10 
1.3. Основные особенности использования MS Excel при решении  
задач и моделировании ................................................................................... 
14 
1.4. Создание счетчиков для автоматизации процесса моделирования ..... 
23 
 
2. Моделирование с использованием MS Excel 
........................................ 
37 
2.1. Математика ............................................................................................... 
37 
2.1.1. Вращение графиков 
............................................................................... 
37 
2.1.2. Комплексное нахождение корней уравнений 
..................................... 
40 
2.1.3. Определение пределов 
.......................................................................... 
42 
2.1.4. Встреча путников .................................................................................. 
43 
2.1.5. Переправа через речку .......................................................................... 
45 
2.1.6. Турист 
..................................................................................................... 
46 
2.1.7. Два пловца ............................................................................................. 
48 
2.1.8. Катер и плот 
........................................................................................... 
49 
2.1.9. Велосипедист 
......................................................................................... 
49 
2.1.10. Моделирования касательной в заданной точке функции ................ 
51 
2.1.11.  Единичная окружность и синус угла 
................................................ 
54 
2.1.12. Построение простейших объемных фигур........................................ 
55 
2.1.13. Решение графоаналитическим методом задач линейного 
программирования .......................................................................................... 
59 
 
2.2. Физика 
...................................................................................................... 
63 
2.2.1. Броуновское движение частиц ............................................................. 
63 
2.2.2. Траектория полета брошенного тела 
................................................... 
64 
2.2.3. Оценка эффективности бросаний тела по мишени ............................ 
68 
2.2.4. Движение тела по наклонной горке 
..................................................... 
69 
2.2.5. Маятник 
.................................................................................................. 
73 
2.2.6. Движение бруска при попадании пули ............................................... 
76 
2.2.7. Сообщающиеся сосуды ........................................................................ 
81 
2.2.8. Подъем аэростата .................................................................................. 
84 
2.2.9. Моделирование фигур Лиссажу .......................................................... 
87 
2.2.10. Робот футболист с мячом ................................................................... 
90 
2.2.11. Физическая зарядка для робота 
.......................................................... 
94 
 
3

2.2.12. Переход  КА с  одной круговой орбиты на другую ....................... 
101 
2.2.13. Трехмерное моделирование полета КА .......................................... 105 
2.3. Оптимизационные модели .................................................................... 
111 
2.3.1. Кратчайший путь между пунктом отправления и назначения 
........ 111 
2.3.2. Задача коммивояжера ......................................................................... 119 
2.3.3. Перечень покупаемой продукции при условии минимизации  
суммы оплаты 
................................................................................................ 
124 
 
2.4. Защита информации 
............................................................................ 
128 
2.4.1. Зашифрованное письмо ...................................................................... 
128 
2.4.2. Стеганографическое письмо .............................................................. 129 
2.4.3. Электронная подпись 
.......................................................................... 
131 
2.5. Другие модели ........................................................................................ 135 
2.5.1. Простейший блокнот .......................................................................... 135 
2.5.2. Курица, которая пьет воду.................................................................. 139 
2.5.3. Построения маршрута движения на карте ........................................ 
144 
2.5.4. Часы 
...................................................................................................... 
147 
2.5.5. Алфавитный хоровод .......................................................................... 150 
 
Литература ................................................................................................... 154 
 
4

Предисловие 
 
В книге раскрыты возможности MS Excel, с которые обычно не рассматриваются при традиционном изучении приложения. Эти возможности 
позволяют создавать динамические модели и иллюстрировать решение задач. Для наглядного изображения процесса моделирования  и иллюстрации задач рассмотрены практически все возможные способы работы с 
элементами диаграмм. 
В книге представлена подборка решения задач и моделей из различных разделов естественных дисциплин. Надеемся, что это поможет более 
глубоко изучить возможности MS Excel, приобрести навыки моделирования, а также отойти от классического построения графиков, расчетов в 
таблицах, форматирования результатов вычислений.  Разработка моделей 
значительно расширяет перечень применяемых функций, которые обычно 
ограничиваются основными математическими, логическими и статистическими функциями.  
Следует отметить, что физика формул, применяемых в моделях, не 
раскрывается, а даются определенные пояснения. Надеемся, что пользователи владеют нужными знаниями и смогут самостоятельно познать необходимое через Интернет для уяснения сущности решения задач и моделей.  
При создании моделей обучаемые овладевают навыками разработки 
алгоритмов и основами программирования.  Для автоматизации процесса 
моделирования и из-за имеющихся ограничений при применении стандартных функций в рассматриваемых примерах используются управляющие элементы и соответствующие процедуры VBA. В ряде моделей создаются пользовательские функции и формы. Все это позволит в дальнейшем легко освоить программирование на различных языках. В этом и 
заключается цель книги. 
Отдельные модели и примеры задач были опробованы на занятиях в 
Западно-Подмосковном институте туризма и в Одинцовском гуманитарном университете. При их рассмотрении они вызывали неподдельный интерес у студентов. 
 
 
5

1. О моделировании, решении задач, программировании и MS 
Excel 
1.1. О решении задач и моделировании  
В жизни все постоянно решают задачи и моделируют. Решение задач 
связано с конкретными исходными данными и условиями. В результате 
решения задачи получаем конкретные данные. При моделировании исходные данные и условия обычно изменяются в определенных диапазонах, а в результате моделирования получаем данные, которые располагаются в определенных диапазонах, зависящих от различных начальных 
условий. В заключении при моделировании необходимо принимать конкретное решение, основанное на изучении полученных данных и оценки  
эффективности различных вариантов модели. 
Вспомним о классических подходах в разъяснениях понятий о решении задач и моделировании.   
Решение задачи - выполнение действий или мыслительных операций, направленных на достижение цели при  заданной проблемной ситуации. Данное понятие имеет очень широкий смысл. Нам желательно его 
сузить и конкретизировать.  
Существуют различные методы решения задач, в которые включен и 
метод моделирования. Круг кажется, замкнулся. Чтобы все-таки его разорвать остановимся на решении математических задачах, математическом 
и компьютерном моделировании. Общим для них является то, что они основываются на применении определенного языка, символов, объектов и 
методов математики. 
Теперь сделаем еще один шаг для конкретизации. Остановимся на 
рассмотрении задач и моделей, которые обычно изучаются в учебных заведениях на уроках по естественным дисциплинам (математики, физики, 
информатики и т.п.). Есть задача о выходе путника из пункта А. Необходимо вычислить время, когда путник прибудет в пункт В при заданных 
условиях. Это есть классический пример решения математической задачи, 
когда в результате решения получаем время прибытия путника в пункт В.  
Но если требуется построить график движения путника, то уместно говорить о модели перемещения путника из пункта А в В. Здесь нужно учесть 
возможную скорость передвижения путника как свойство объекта. То есть 
 
6

имеем элементарную модель перемещения путника в пространстве. Если 
знаний о скорости недостаточно, то можно ввести в модель дополнительные данные о качестве дороги, остановках на отдых, средствах и способах 
передвижения, запасах топлива и т.п. Это позволит усложнить модель и 
при этом уточнить точность принятия решения при анализе полученных 
дополнительных данных. Но не всегда в модели можно учесть все данные. 
Одни данные неизвестны, другие не нужны, третьи – нельзя учесть в модели из-за различных математических или технологических трудностей. 
Таким образом, при моделировании учитываются существенные 
свойства и параметры объектов (явлений природы, событии и т.п.). Выбор 
свойств объектов определяется поставленными целями исследования или 
возможностями их учета в модели. Свойства могут быть внешними и 
внутренними по отношению объекта. Внешние свойства связаны с окружающей средой, а внутренние – с сущностью объекта. При этом все свойства могут изменяться при моделировании. Также свойства делятся на 
входные и выходные. Обычно выходные свойства зависят от входных. 
При этом входные свойства задаются в ходе моделирования, а выходные 
получаем в результате воздействия входных свойств на объект. Для более 
полного изучения объекта входные свойства задаются в виде диапазона 
данных. Хотя они могут задаваться как константы или изменяемые параметры от других входных свойств. 
Следует отметить, что прежде чем начать моделирование объекта 
решаются задачи при конкретных значений свойств (данных) для отладки 
или проверки правильности модели. Не существуют модели без этапа решения задач. Контрольные просчеты нужны не только на этапе создания, 
отладки модели, но и для проверки достоверности полученных данных 
при моделировании различных ситуаций. 
Классификация моделей разнообразна, но  нас интересуют математические и компьютерные модели. Компьютерную модель нельзя создать 
без математической модели. 
Математическая модель - это  приближённое описание какого-либо 
класса явлений внешнего мира, выраженное с помощью математической 
символики. Почему приближенное? Так как в модели все учесть нельзя! 
Стараются учесть в первую очередь только важное и необходимое.  
 
7

На основе математического описания разрабатывается алгоритм модели. Далее разрабатывается программа модели в определенной среде 
программирования.  Программа размещается в компьютере, заносятся исходные данные и запускается программа на выполнение. Компьютер 
начинает функционировать в соответствии с программой модели, обрабатываются данные и выдаются выходные данные в виде графиков или таблиц.  Таким образом, компьютерная модель есть программа алгоритма, 
который разработан на основе математического описания модели. 
Чем привлекательны компьютерные модели? Во-первых, можно в 
любое время проводить исследование реальных объектов необходимое 
число раз. Во-вторых, смело изменять исходные данные и получать результаты, которые для реальных объектов были бы «губительны». Втретьих, спокойно вносить изменения для улучшения модели или исправления ошибок. 
Современный специалист должен иметь навыки моделирования. Для 
этого он должен знать свою предметную область, иметь знания в области 
математики, уметь составлять алгоритмы, знать основы программирования. В зависимости от специфики работы объем знаний, умений и навыков по представленным составляющим может быть различным. 
Вопросы алгоритмизации и программирования рассматриваются в 
дисциплине информатика. Здесь хочется отметить, что в информатику 
входят вопросы информационных технологий. Многие часто принижают 
роль информатики и выдвигают на первый план дисциплину информационные технологии. 
Как учить и  что изучать на уроках информатики? На эти  вопросы 
четко и однозначно нельзя ответить в силу ограниченности времени и отсутствия необходимых вычислительных средств и программного обеспечения. Основными изучаемыми темами  в учебных заведениях являются 
приложения и программирование. Причем сейчас важно не только знать 
возможности приложений, но и уметь программировать. 
 С помощью приложения MS Excel можно изучить оба вопроса и затем спокойно перейти к изучению программирования на любом языке.   
При использовании приложения  MS Excel обычно решают различные расчетные задачи. Расчетные задачи характеризуются наличием кон 
8

кретных исходных данных и неизменными условиями, о чем говорилось 
выше.  
Однако моделирование при изучении приложения позволит более 
глубоко узнать возможности MS Excel и основы программирования. Приложение MS Excel имеет ряд особенностей. Но самое главное на любом 
ПК всегда присутствует стандартный пакет MS Office c приложением MS 
Excel. Не нужно выбирать язык программирования, устанавливать дополнительно среду программирования.  Нужно только иметь ПК, интерес, 
настойчивость и усидчивость.  
 
 
9

1.2. О преимуществах  MS Excel при изучении основ программирования 
Отметим следующие преимущества приложения MS Excel с точки 
зрения приобретения навыков программирования и разработки моделей:  
x MS Excel всегда включено в состав пакета MS Office. 
x MS Excel имеет достаточно большой набор различных функций. 
x MS Excel представляет возможность по созданию своих пользовательских функций. 
x MS Excel имеет понятную систему индикации ошибок. 
x MS Excel позволяет изменять формат результатов вычислений, 
а также создавать свой пользовательский формат. 
x MS Excel имеет простые правила составления выражений для 
вычислений, которые изучаются в школе. 
x Вычисления выполняются автоматически после изменения 
данных или расчетных выражений, которые вводятся после 
щелчка по клавише Enter. 
x Пользователь «видит» не только графику, но и результаты вычислений по ходу моделирования или решения задачи. 
x Пользователь может на любом этапе решения задачи просмотреть расчетные выражения, по которым производятся вычисления, а также включить режим Показывать формулы, а не их 
значения. 
x Пользователь может сформировать промежуточные вычисления, которые отражают количественные параметры модели и 
наблюдать за их изменениями.  
x MS Excel позволяет осуществлять вложения, количество которых достаточно для реализации большинства вычислений и сопоставимо с количеством вложений в других средах программирования. 
x Так как MS Excel является табличным процессором, то пользователь должен для вычислений создавать расчетные выражения 
(формулы) в каждой ячейке. Что очень полезно в плане запоминания стандартных функций MS Excel. 
 
10