Физика: практикум в Excel

ФИЗИКА

ПРАКТИКУМ В EXCEL

О.А. СДВИЖКОВ 
Н.П. МАЦНЕВ 

Рекомендовано Межрегиональным учебно-методическим советом 

профессионального образования в качестве учебного пособия

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

среднего профессионального образования на базе основного общего образования 

(протокол № 2 от 17.02.2021)

УЧЕБНОЕ ПОСОБИЕ

Москва 
ИНФРА-М 

202

УДК [53+004.9](075.32)
ББК 22.3:32.973-018.2я723
 
С27

Сдвижков О.А.

С27  
Физика: практикум в Excel : учебное пособие / О.А. Сдвижков, 

Н.П. Мацнев. — Москва : ИНФРА-М, 2024. — 274 с. — (Среднее профессиональное 
образование). — DOI 10.12737/1014621.

ISBN 978-5-16-015002-4 (print)
ISBN 978-5-16-107498-5 (online)
В практикуме приведены программные коды пользовательских функ-

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

Каждая глава включает: краткие сведения по физике; код одной или не-

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

Соответствует требованиям федеральных государственных образова-

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

Предназначено для студентов учреждений среднего профессиональ-

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

УДК [53+004.9](075.32)

ББК 22.3:32.973-018.2я723

А в т о р ы:

Сдвижков О.А., кандидат физико-математических наук, доцент, до-

цент Российского государственного университета туризма и сервиса 
(введение; гл. 1; гл. 2: 2.1—2.6; гл. 3: 3.2, 3.3; гл. 4: 4.1—4.4, 4.7; гл. 5; гл. 
6; гл. 7: 7.1—7.3, 7.7; гл. 8); 

Мацнев Н.П., кандидат технических наук, доцент, доцент Техноло-

гического университета имени дважды Героя Советского Союза, летчика-
космонавта А.А. Леонова (г. Королев) (гл. 2: 2.7—2.11; гл. 3: 3.1, 
3.4; гл. 4: 4.5, 4.6; гл. 7: 7.4—7.6)

Р е ц е н з е н т:

Фоминский В.Ю., доктор физико-математических наук, профессор 

кафедры физики твердого тела Научного исследовательского ядерного 
университета «МИФИ»

ISBN 978-5-16-015002-4 (print)
ISBN 978-5-16-107498-5 (online)

© Сдвижков О.А., Мацнев Н.П., 

2021

Данная книга доступна в цветном  исполнении 
в электронно-библиотечной системе Znanium.com

Предисловие

Данный практикум — первое учебное пособие, в котором рассматриваются 
технологии создания пользовательских функций 
Excel и их применения к типовым задачам элементарной физики. 
В пособие вошли задачи по механике, молекулярной физике и электродинамике. 
Выбор программного комплекса Excel обусловлен 
доступностью, уникальными вычислительными возможностями 
и встроенной средой программирования, позволяющей создавать 
дополнительные средства автоматизации вычислений, поддерживаемые 
на уровне встроенных средств.
Применение информационных технологий, в том числе технологий 
Excel, — не дань времени или моде, а способ получения результата 
за наименьшее время, т.е. способ повышения производительности 
труда. Именно поэтому идет интенсивный переход к информационным 
технологиям, а их применение — одна из самых 
актуальных задач. 
В программном комплексе Excel нет инструментов, предназначенных 
для решения физических задач, но есть программная 
среда разработки процедур VBA (Visual Basic for Applications), позволяющая 
создавать такие инструменты. В пособии приведены 
программные коды (всего их около 90) самых простых процедур 
VBA — пользовательских функций (user-defi ned functions), с помощью 
которых решаются задачи элементарной физики, причем 
создание каждой из них занимает, как правило, всего несколько 
минут; затем созданная функция применяется всякий раз, когда 
в ней есть необходимость.  
Обращение к пользовательской функции открывает диалоговое 
окно с полями ввода значений ее параметров. Ввод этих значений 
возвращает в диалоговом окне значение функции, по команде ОК 
оно появляется в активной ячейке. Пользовательскими функциями 
в пособии задаются и необходимые таблицы приложений физических 
величин, причем так, что каждое табличное значение находится 
как значение соответствующей пользовательской функции. 
Ко всем пользовательским функциям созданы описания, поясняющие 
их назначения.
Для простоты применения переменным созданных пользовательских 
функций присвоены информативные имена, включающие, 
как правило, и размерность.

Приведенные в практикуме программные коды пользовательских 
функций вводятся непосредственно или с помощью их копирования 
по ссылке http://oas.ucoz.com/load/physics_codes/1-1-0-20. 
Каждая тема, приведенная в пособие, включает следующие материалы:

1) краткие сведения по физике;
2) код одной или нескольких пользовательских функций и их 
описание;
3) пошаговые решения типовых задач с применением пользовательских 
функций, проиллюстрированные рисунками диалоговых 
окон, фрагментами рабочих листов и т.д., сделанными с помощью 
скриншотов. 
Пособие написано на базе Excel 2013, другие версии Excel применяются 
по аналогии. 

Введение

В1. ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ 

Пользовательская функция Excel — это созданный пользователем 
инструмент автоматизации вычислений, избавляющий пользователя 
от необходимости введения расчетной формулы; для получения 
результата достаточно вызвать пользовательскую функцию 
и ввести значения параметров. 
Пользовательская функция создается в редакторе VB, кнопка 
вызова которого находится на вкладке Разработчик. Если этой 
вкладки на ленте нет, то надо:
1) открыть вкладку Файл;
2) выбрать опцию Параметры; 
3) выбрать опцию Настроить ленту, открывающую диалоговое 
окно Параметры Excel (рис. В1);

Рис. В1

4) поставить флажок в поле Разработчик (рис. В1) и подтвердить 
ОК.
Общий вид, который имеет код функции пользователя (в квадратных 
скобках необязательные данные): 
Function Имя(х1 [As Type],…, хN [As Type]) [As Type]

VBA Excel инструкции по присваиванию значений 
переменной Имя
End Function
В пособии применяются следующие типы данных: Single — 
обычная точность (4 байта), Double — двойная точность (8 байт), 
Integer — целое число (2 байта), String — текстовые данные (10 
байт), которые вводятся в двойных кавычках.
Например, код функции пользователя Путь_км, возвращающей 
величину пройденного пути по скорости и времени движения, 
можно записать в виде:
Function Путь_км(Скорость_км_час As Single, Время_час As _
Single)  As Single 
Путь_км = Скорость_км_час* Время_час 
End Function
Код описания этой функции, которое будет отображаться в диалоговом 
окне функции, записывается в следующем виде (пробел 
и нижнее подчеркивание — перенос строки кода):
Sub InstallFunc1()
Application.MacroOptions Macro:="Путь_км", Description:= _
"Возвращает величину пройденного пути"
End Sub
Стандартный модуль, содержащий область, в которую вводится 
код пользовательской функции, вызывается указанными далее шагами.

1. Открывается Excel и активизируется вкладка РАЗРАБОТЧИК (
рис. В2).

Рис. В2

2. Кнопкой Visual Basic (рис. В2) вызывается редактор VB 
(рис В3).

Рис. В3

3. Активизация меню Insert (рис. В3) открывает опции (рис. В4).

Рис. В4

4. Выбор Module (рис. В4) вызывает стандартный модуль, содержащий 
область, в которую вводится код функции пользователя 
(рис. В5).

Рис. В5

Разделительная черта между кодом функции и кодом ее описания 
ставится автоматически. Чтобы описание функции отобра-

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

Рис. В6

Созданная пользовательская функция Путь_км, например, 
когда надо найти путь, пройденный автомобилем за 4,5 ч при скорости 
75 км/ч, применяется следующим образом.
1. Кнопкой 
 (см. рис. В2) вызывается диалоговое окно Вставка 
функции.
2. Открывается список категорий функций и выбирается опция 
Определенные пользователем (рис. В7).

Рис. В7

3. В появившемся списке выбирается функция Путь_км 
(рис. В8).

Рис. В8

4. Команда ОК вызывает диалоговое окно функции, в которое 
вводятся значения параметров и в котором появляется результат; 
над ним находится описание функции (рис. В9).

Рис. В9

5. По команде ОК результат появляется в ячейке, которая была 
активной.
По аналогии применяются другие пользовательские функции.
В созданных пользовательских функциях именами переменных, 
как правило, являются наименования физических величин или их 
сокращений, после которых указывается размерность; все слова 
в имени связываются нижними подчеркиваниями. Они применяются 
также, когда в VBA записать размерность в явном виде нельзя, 
например, вместо м/с вводится м_с. 

Некоторые пользовательские функции содержат переменную 
Тип_0_1_..._N, принимающую значения 0, 1, …, N; она применяется, 
когда проводятся вычисления по различным расчетным формулам 
в зависимости от значения, принимаемого этой переменной. 

В2. ИНСТРУМЕНТ «ПОДБОР ПАРАМЕТРА»

В пособии широко применяется инструмент Подбор параметра; 
с его помощью находится значение независимой ячейки, при котором 
в зависимой ячейке установливается заданное значение.  
Например, пусть независимая ячейка А1, а зависимая ячейка С1 
содержит формулу 2*А1+3, и надо найти значение ячейки А1, при 
котором в ячейке С1 будет значение 5. Эта задача решается с помощью 
инструмента Подбор параметра следующим образом. 
1. Выделяется зависимая ячейка.
2. Командами Данные  Анализ «что если»  Подбор параметра 
открывается диалоговое окно Подбор параметра и ставится 
задача (рис. В10).

Рис. В10

3. Команда ОК возвращает сообщение о результатах подбора 
параметра (решение найдено или не найдено); в данном случае оно 
имеет вид, показанный на рис. В11.

Рис. В11