ПРИМЕНЕНИЕ ФУНКЦИЙ VBA EXCEL В ЭЛЕМЕНТАРНОЙ ФИЗИКЕ

ПРИМЕНЕНИЕ ФУНКЦИЙ VBA EXCEL В ЭЛЕМЕНТАРНОЙ ФИЗИКЕ

Научная статья

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

Российский государственный университет туризма и сервиса, Пушкино, Россия

* Корреспондирующий автор (oasdv[at]yandex.ru)

Аннотация

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

Ключевые слова: скорость, ускорение, сила, масса, молекула, ток, код, диалоговое окно.

APPLYING VBA EXCEL FUNCTIONS IN ELEMENTARY PHYSICS

Research article

Sdvizhkov O.A.*

Russian State University of Tourism and Service, Pushkino, Russia

* Corresponding author (oasdv[at]yandex.ru)

Abstract

In the VBA Excel environment, the basic formulas of elementary physics are programmed with custom functions (about 60 names). They allow for online solving of common problems of such sections of elementary physics as kinematics, dynamics, statics, thermal phenomena, molecular kinetic theory, electric current and electrodynamics. All custom functions have descriptions explaining their purpose and application technologies. Many of the functions help in finding the values of several parameters. Also, the custom functions of VBA Excel are programmed with application tables of physical quantities necessary for solving problems. The application of the developed custom functions is demonstrated on specific tasks.

Keywords: velocity, acceleration, force, mass, molecule, current, code, dialog box.

1. Введение

Цель исследования – разработка на языке программирования VBA Excel кодов пользовательских функций [1], [3], [6], выполняющих расчеты по основным физическим формулам [2], [4], то есть информатизация физических расчетов с помощью пользовательских функций VBA Excel. При этом код должен позволять пользователю находить значение любого параметра, входящего в расчетную формулу, чтобы минимизировать число пользовательских функций, необходимых для расчетов. Применение пользовательских функций VBA Excel объясняется тем, что они поддерживаются на уровне встроенных средств, то есть запуск пользовательской функция VBA Excel вызывает диалоговое окно ввода данных, на нем же возвращается результат, что очень удобно пользователю.

Приведенные в статье программные коды публикуются впервые. Все разработанные по теме исследования программные коды приведены в [11], некоторые из них опубликованы в материалах конференций [7], [8], [9] и в статье [10]. Физические расчеты в Excel без применения пользовательских функций VBA рассматриваются в [5].

Как правило, физические формулы реализуются в пользовательских функциях [11] следующим образом. Если формула связывает n физических величин, то реализующая ее функция будет содержать n+1 переменных, из которых n соответствуют переменным формулы, а (n+1) -я переменная, являющаяся строковой, показывает значение какой переменной будет вычисляться при запуске функции. Имя (n+1) -й переменной состоит, как правило, из обозначений остальных переменных, связанных нижними подчеркиваниями, нужное значение параметра указывается в двойных кавычках. Такой подход позволяет каждой функцией решать и прямую, и обратные задачи. Следует заметить, что нижние подчеркивания применяются в программных кодах [11] и при указании размерностей переменных, так как в них символы *, / использовать для обозначений нельзя. Например, то, что переменная Скорость имеет в формуле размерность м/с, записывается в виде Скорость_м_с.

Таблицы физических величин (плотности, удельные значения и так далее) также представлены в [11] пользовательскими функциями. Например, плотности веществ возвращает функция ПЛОТНОСТИ, ее диалоговое окно показано на рисунке 1.1.

 

 

Рис. 1.1 – Применение функции ПЛОТНОСТИ

 

2. Пользовательские функции механики

По этому разделу разработаны около 20 пользовательских функций. В частности [2, 4], уравнения движения тела, брошенного горизонтально с начальной скоростью v₀ из точки (0, y₀), записываются в виде:

        (2.1) Откуда следует, что время полета T = t_max находится по формуле (h = y₀):        (2.2) Вычисление скорости тела дает:        (2.3) Тангенс угла α между вектором скорости и осью Ох определяется формулой:        (2.4) Поэтому при предельном значении t справедливы формулы:        (2.5)

Код функции пользователя ГОРИЗОНТ, выполняющей расчеты по формулам (2.1) – (2.5), приведен в листинге 2.1.

Листинг 2.1. Код функции ГОРИЗОНТ и ее описания

Function ГОРИЗОНТ(Нач_ск_м_с, Нач_выс_м, Время_с, _

T_x_y_v_a As String)

Select Case T_x_y_v_a:Case "T"

ГОРИЗОНТ = (2 * Нач_выс_м / 9.8) ^ (1 / 2)

Case "x":ГОРИЗОНТ = Нач_ск_м_с * Время_с

Case "y":ГОРИЗОНТ = Нач_выс_м - 9.8 * Время_с ^ 2 / 2

Case "v"

ГОРИЗОНТ = (Нач_ск_м_с ^ 2 + 9.8 ^ 2 * Время_с ^ 2) ^ (1 / 2)

Case "a"

ГОРИЗОНТ = Application.WorksheetFunction.Acot(Нач_ск_м_с / _

9.8 / Время_с) * 180 / 3.141593

End Select:ГОРИЗОНТ = Round(ГОРИЗОНТ, 4)

End Function

Sub InstallFunc5()

Application.MacroOptions Macro:="ГОРИЗОНТ", Description:= _

"Находит значения параметров движения тела, " & _

"брошенного горизонтально"

End Sub

Задача 2.1. Мячик брошен горизонтально со скоростью 5 м/с на высоте 19,6 м. Применяя функцию ГОРИЗОНТ, найти:

1. Через какой промежуток времени он упадет на Землю;
2. На каком расстоянии по горизонтали находится точка падения;
3. Скорость мячика в точке падения;
4. Под каким углом он упадет на Землю.

Технология решения. 1. Вызывается функция ГОРИЗОНТ и вводятся данные (рис. 2.1).

Рис. 2.1 – Применение функции ГОРИЗОНТ в задаче 2.1.1

 

1. Вызывается функция ГОРИЗОНТ, вводятся числовые данные и “x” (рис. 2.2).

Рис. 2.2 – Применение функции ГОРИЗОНТ в задаче 2.1.2

 

1. Вызывается функция ГОРИЗОНТ, вводятся числовые данные и “v” (рис. 2.3).

Рис. 2.3 – Применение функции ГОРИЗОНТ в задаче 2.1.3

 

4. Вызывается функция ГОРИЗОНТ, вводятся числовые данные и “a” (рис. 2.4).

Рис. 2.4 – Применение функции ГОРИЗОНТ в задаче 2.1.4

 

3. Молекулярная физика

По этому разделу также разработаны около 20 пользовательских функций.

Как известно [2], [4], масса m₀ молекулы вещества, молярная масса которого равна М, N_A=6,022·10²³ – число Авогадро, находится по формуле:

         (3.1) Количество вещества ν в массе m вычисляется по формуле        (3.2) а число молекул – по формуле        (3.3)

Код функции пользователя МИКРО, возвращающей число молекул, массу молекулы или количество вещества, приведен в листинг 3.1.

Листинг 3.1. Код функции МИКРО и ее описания.

Function МИКРО(Масса_кг_моль, Mасса_кг, _

N_m0_nu As String)

Select Case N_m0_nu

Case "N":МИКРО = Mасса_кг / Масса_кг_моль * 6.022 * 10 ^ 23

Case "m0":МИКРО = Масса_кг_моль / 6.022 / 10 ^ 23

Case "nu":МИКРО = Mасса_кг / Масса_кг_моль

End Select

End Function

Sub InstallFunc2()

Application.MacroOptions Macro:="МИКРО", Description:= _

"При N находит число молекул, при m0 - массу молекулы, " & _

"при nu - количество вещества"

End Sub

Код функции пользователя МОЛЬ, возвращающей молярную массу заданного вещества, приведен в листинге 3.2.

Листинг 3.2. Код функции МОЛЬ и ее описания.

Function МОЛЬ(Вещество As String)

Select Case Вещество

Case "Азот":МОЛЬ = 0.0280134:Case "Алюминий":МОЛЬ = 0.0269815

Case "Вода":МОЛЬ = 0.018015:Case "Водород":МОЛЬ = 0.0020159

Case "Воздух":МОЛЬ = 0.029:Case "Серебро":МОЛЬ = 0.10787

Case "Гелий":МОЛЬ = 0.0040026:Case "Золото":МОЛЬ = 0.196967

Case "Кислород":МОЛЬ = 0.0319988:Case "Медь":МОЛЬ = 0.06354

Case "Углекислый газ":МОЛЬ = 0.044:Case "Олово":МОЛЬ = 0.11869

End Select

End Function

Sub InstallFunc1()

Application.MacroOptions Macro:="МОЛЬ", Description:= _

"Возвращает молярную массу вещества (кг/моль)"

End Sub

Задача 3.1. Какую массу имеет молекула серебра?

Технология решения. Вызывается функция МИКРО и вводятся данные (рис. 3.1).

 

Рис. 3.1 – Применение функции МИКРО в задаче 3.1

  Функция ОУМКТ (листинг 3.3) применяется при решении задач на основное уравнение молекулярно-кинетической теории:   Листинг 3.3. Код функции ОУМКТ и ее описания Function ОУМКТ(Давление_Па, Концент_1_м3, _ Энергия_Дж, E_n_p As String):Select case E_n_p Case “E”:ОУМКТ = 3 * Давление_Па /Концент_1_м3/2 Case “n”:ОУМКТ = 3 * Давление_Па / Энергия_Дж / 2 Case “p”:ОУМКТ = 2 * Концент_1_м3 * Энергия_Дж / 3 End Select End Function Sub InstallFunc3() Application.MacroOptions Macro:="ОУМКТ", _ Description:= "При E находит величину энергии, " & _ "при n –концентрации, при р - давления газа" End Sub Задача 3.2. Каково давление газа, если средняя кинетическая энергия его молекул 2,5·10^-20 Дж, а концентрация 3·10²⁵ м^-3? Технология решения. Вызывается функция ОУМКТ, вводятся числовые данные и “p” (рис. 3.2).  

Рис. 3.2 – Применение функции ОУМКТ в задаче 3.2

  Задача 3.3. Определите концентрацию молекул одноатомного газа при температуре 300 К и давлении 414 кПа. Технология решения. Вызывается ОУМКТ, вводятся числовые данные и “n” (рис. 3.3).  

Рис. 3.3– Применение функции ОУМКТ в задаче 3.3

  Задача 3.4. Применяя функцию ОУМКТ, определите среднюю кинетическую энергию молекул газа, который в 1 м³ содержит 2,5·10²⁶ молекул, а его давление 1114 кПа. Технология решения. Вызывается ОУМКТ, вводятся числовые данные и “Е” (рис. 3.4).

Рис. 3.4 – Применение функции ОУМКТ в задаче 3.4

 

4. Электричество (электростатика, законы постоянного тока, электромагнетизм)

По этому разделу разработаны около 15 пользовательских функций. В частности, как известно [2, 4], сопротивление проводника R, имеющего длину l (м), площадь сечения S (мм²) и удельное сопротивление ρ (Ом·мм²/м), находится по формуле:

         (4.1)

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

Листинг 4.1. Код функции СП и ее описания

Function СП(Сопротив_Ом,Уд_сопр_Ом_мм2_м, Длина_м, _

Площадь_мм2, R_L_S_Ro As String)

Select case R_L_S_Ro

Case “R”:СП = Уд_сопр_Ом_мм2_м * Длина_м / Площадь_мм2

Case “L”:СП = Сопротив_Ом * Площадь_мм2 / Уд_сопр_Ом_мм2_м

Case “S”:СП = Уд_сопр_Ом_мм2_м * Длина_м / Сопротив_Ом

Case “Ro”:СП = Сопротив_Ом * Площадь_мм2 / Длина_м

End Select

End Function

Sub InstallFunc8()

Application.MacroOptions Macro:="СП", Description:= _

"При R находит сопротивление, при L – длину, " & _

"при S – площадь, при Ro – уд. сопротивление"

End Sub

Удельные сопротивления ρ некоторых веществ возвращает функция УДС, код которой приведен в листинге 4.2.

Листинг 4.2. Код функции УДС и ее описания

Function УДС(Вещество As String)

Select Case Вещество

Case "Алюминий": УДС = 0.028:Case "Вольфрам": УДС = 0.055

Case "Железо": УДС = 0.1:Case "Латунь": УДС = 0.071

Case "Манганин": УДС = 0.43:Case "Медь": УДС = 0.017

Case "Никелин": УДС = 0.4:Case "Платина": УДС = 0.1

Case "Нихром": УДС = 1.1:Case "Свинец": УДС = 0.21

Case "Серебро": УДС = 0.016:Case "Сталь": УДС = 0.12

End Select

End Function

Sub InstallFunc9()

Application.MacroOptions Macro:="УДС", Description:= _

"Возвращает удельное сопротивление вещества (Ом*мм2/м) "

End Sub

Задача 4.1. Рассчитать сопротивление медной проволоки длиной 10 м и площадью поперечного сечения 0,5 мм².

Технология решения. Вызывается функция СП, удельное сопротивление находится с помощью функции УДС, также вводятся числовые данные и “R” (рис. 4.1).

Рис. 4.1 – Применение функции СП в задаче 4.1

  Задача 4.2. Какова длина алюминиевой проволоки сечением 0,7 мм², если ее сопротивление 4 Ом? Технология решения. Вызывается функция СП и вводятся данные (рис. 4.2).

Рис. 4.2 – Применение функции СП в задаче 4.2

 

В магнитном поле с индукцией В на проводник с током I, длина которого l, действует сила Ампера, величина которой находится по формуле , α – угол между направлением тока в проводнике и вектором магнитной индукции. Для задач, связанных с силой Ампера, в [11] есть функция АМПЕР (листинг 4.3).

Листинг 4.3. Код функции АМПЕР и ее описания

Function АМПЕР(Сила_Н, Сила_тока_А, Индукция_Тл, _

Длина_м, Угол_град, F_I_B_L As String):Угол_град = _

Application.WorksheetFunction.Radians(Угол_град)

Select Case F_I_B_L: Case "F":АМПЕР = Сила_тока_А _

* Индукция_Тл * Длина_м * Sin(Угол_град)

Case "I":АМПЕР = Сила_Н/ Индукция_Тл/ Длина_м/ Sin(Угол_град)

Case "B":АМПЕР = Сила_Н/ Сила_тока_А / Длина_м/ Sin(Угол_град)

Case "L":АМПЕР = Сила_Н/ Сила_тока_А / Индукция_Тл _

/ Sin(Угол_град):End Select

End Function

Sub InstallFunc6()

Application.MacroOptions Macro:="АМПЕР", _

Description:= "При F находит силу Ампера, при " & _

"I - силу тока, при В - индукцию, при L – " & _

"длину проводника"

End Sub

Задача 4.3. На прямой проводник длиной 0,5 м, перпендикулярный магнитному полю с индукцией 2·10^-2 Тл, действует сила 0,15 Н. Найти силу тока в проводнике.

Технология решения. Вызывается функция АМПЕР, вводятся числовые данные и “I” (рис. 4.3). Продолжение ввода (рис. 4.4).

Рис. 4.3 – Применение функции АМПЕР в задаче 4.3

Рис. 4.4 – Применение функции АМПЕР в задаче 4.3 (продолжение ввода)

 

5. Выводы

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

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

3. Применение разработанных пользовательских функций VBA Excel не вызывает трудностей.

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

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

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

Конфликт интересов Не указан.

Conflict of Interest None declared.

Список литературы / References

1. Гарнаев А.Ю. MS Excel 2002: разработка приложений. / А.Ю. Гарнаев – СПб.: БХВ-Петербург, 2003. – 768 с.
2. Гофман Ю.В. Формулы, задачи физики. Справочник. / Ю. В. Гофман – К.: Наук. думка, 1977. – 576 с.
3. Джелен Билл Применение VBA и макросов в Microsoft Excel: Пер. с англ. / Билл Джелен, Трейси Сирстад – М.: Издательский дом «Вильямс», 2006. – 624 с.
4. Кухлинг Х. Справочник по физике: Пер. с нем. 2-е изд. / Х. Кухлинг – М: Мир, 1985. – 520 с.
5. Ларсен Р.У. Инженерные расчеты в Excel: Пер. с англ. / Р. У. Ларсен – М.: Издательский дом «Вильямс», 2002. – 544 с.
6. Сдвижков О.А. Excel –VBA. Словарь-справочник пользователя / О. А. Сдвижков. – М.: Эксмо, 2008. – 224 с.
7. Сдвижков О.А. Функции VBA Excel для решения ряда задач динамики / Сдвижков О.А. // Модернизация сферы образования и науки с учетом мировых научно-технологических трендов: сборник научных трудов по материалам Международной научно-практической конференции 13 июля 2020 г. – Белгород: АПНИ, 2020. 19 – 33 с.
8. Сдвижков О.А. Функции VBA Excel для решения ряда задач механики жидкостей / Сдвижков О.А. // Модернизация сферы образования и науки с учетом мировых научно-технологических трендов: сборник научных трудов по материалам Международной научно-практической конференции 13 июля 2020 г. – Белгород: АПНИ, 2020. 34 – 38 с.
9. Сдвижков О.А. Функции VBA Excel для решения задач на тепловые явления / Сдвижков О.А. // III Международная научно-практическая конференция (08 августа 2020 г.) / – Уфа: НИЦ Вестник науки, 2020. 69 – 80 с.
10. Сдвижков О.А. Применение Excel в кинематике / Сдвижков О.А. // «Физика в школе», 2020, №7, с. 30 – 39.
11. Физика(пользовательские функции) [Электронный ресурс]. – URL: https://oas.ucoz.com/load/fizika_polzovatelskie_funkcii/1-1-0-29 (дата обращения: 15.08.2020)

Список литературы на английском языке / References in English

1. Garnaev A.Ju. MS Excel 2002: razrabotka prilozhenijj [MS Excel 2002: Application Development]. / A.Ju. Garnaev // SPb.: BKhV-Peterburg, 2003. – 768 p. [in Russian]
2. Gofman Ju.V. Formuly, zadachi fiziki. Spravochnik. [Formulas, Problems of Physics. Reference Book.] / Ju. V. Gofman // K.: Nauk. dumka, 1977. – 576 p. [in Russian]
3. Jelen Bill, Syrstad Tracy. Primenenie VBA i makrosov v Microsoft Excel: Per. s angl. [Application of VBA and Macros in Microsoft Excel: Translated from English] / Bill Jelen, Tracy Syrstad // M.: Publishing house «Vil'jams», 2006. – 624 p. [in Russian]
4. Kukhling Kh. Spravochnik po fizike: Per. s nem. 2-nd ed. [Handbook of Physics: Translated from German 2nd Ed.] / Kh. Kukhling // M: Mir, 1985. – 520 p. [in Russian]
5. Larsen R.U. Inzhenernye raschety v Excel: Per. s angl. [Engineering Calculations in Excel: Translated from English] / R. U. Larsen // M.: Publishing house «Vil'jams», 2002. – 544 p. [in Russian]
6. Sdvizhkov O.A. Excel –VBA. Slovar'-spravochnik pol'zovatelja [Excel-VBA. Dictionary-User's Guide] / O. A. Sdvizhkov. // M.: Ehksmo, 2008. – 224 p. [in Russian]
7. Sdvizhkov O.A. Funkcii VBA Excel dlja reshenija rjada zadach dinamiki [VBA Excel Functions for Solving a Number of Dynamics Problems] / O.A. Sdvizhkov // Modernizacija sfery obrazovanija i nauki s uchetom mirovykh nauchno-tekhnologicheskikh trendov: sbornik nauchnykh trudov po materialam Mezhdunarodnojj nauchno-prakticheskojj konferencii 13 ijulja 2020 g. [Modernization of Education and Science With the World Scientific and Technological Trends: Proceedings of the International Scientific-Practical Conference July 13, 2020] – Belgorod: APNI, 2020. pp. 19 – 33 [in Russian]
8. Sdvizhkov O.A. Funkcii VBA Excel dlja reshenija rjada zadach mekhaniki zhidkostejj [VBA Excel Functions for Solving a Number of Problems in Fluid Mechanics] / O.A. Sdvizhkov // Modernizacija sfery obrazovanija i nauki s uchetom mirovykh nauchno-tekhnologicheskikh trendov: sbornik nauchnykh trudov po materialam Mezhdunarodnojj nauchno-prakticheskojj konferencii 13 ijulja 2020 g. [Modernization of Education and Science With the World Scientific and Technological Trends: Proceedings of the International Scientific-Practical Conference July 13, 2020] – Belgorod: APNI, 2020. pp. 34 – 38 [in Russian]
9. Sdvizhkov O.A. Funkcii VBA Excel dlja reshenija zadach na teplovye javlenija [Functions of VBA Excel for Solving Problems on Thermal Phenomena] / O.A. Sdvizhkov // III Mezhdunarodnaja nauchno-prakticheskaja konferencija (08 avgusta 2020 g.) [III International Scientific and Practical Conference (August 08, 2020)] / – Ufa: NIC Vestnik nauki, 2020. 69 – 80 s. [in Russian]
10. Sdvizhkov O.A. Primenenie Excel v kinematike [The Use of Excel in Kinematics] / O.A. Sdvizhkov // «Fizika v shkole» ["Physics at School"], 2020, №7, pp. 30 – 39. [in Russian]
11. Fizika (pol'zovatel'skie funkcii) [Physics (User Functions)] [Electronic resource]. – URL: https://oas.ucoz.com/load/fizika_polzovatelskie_funkcii/1-1-0-29 (accessed: 15.08.2020) [in Russian]