CC BY
14
ХИМИЧЕСКИЕ НАУКИ / CHEMISTRY
DOI: https://doi.org/10.23670/IRJ.2021.112.10.020
ИНТЕГРИРОВАНИЕ В EXCEL БЕЗ МАКРОСОВ НА ПРИМЕРЕ ИНТЕГРАЛА АРРЕНИУСА
Научная статья
Бондарчук И.С.1, Бондарчук С.С.2' *
1 ORCID: 0000-0001-6995-0547;
2 ORCID: 0000-0001-6842-9143;
1 Томский государственный университет, Томск, Россия;
2 Томский государственный педагогический университет, Томск, Россия;
2 Институт проблем химико-энергетических технологий СО РАН, Бийск, Россия
* Корреспондирующий автор (isbs[at]mail.ru)
Аннотация
В статье предлагается эффективный (достаточно точный, простой и интуитивно понятный быстро исполняемый и имеющий короткий программный код) алгоритм, опирающийся на широкие вычислительные возможности Excel без использования макросов VBA. В качестве примера приводится реализация вычисление интеграла Аррениуса в одной ячейке Excel. Точность вычислений анализируется сравнением с часто используемыми в химической кинетике аппроксимациями расчета интеграла для диапазона значений, имеющих место при протекании газо- и твердофазных реакций.
Данный алгоритм может быть использован для численного интегрирования других "неберущихся" интегралов, встречающихся при решении практических задач и обработке данных.
Иллюстрирующие текст примеры (скриншоты листов электронных таблиц) содержат комментарии и допускают быстрое воспроизведение и проверку.
Ключевые слова: численное интегрирование, интеграл Аррениуса, электронные таблицы, Excel.
INTEGRATION IN EXCEL WITHOUT MACROS ON THE EXAMPLE OF ARRHENIUS INTEGRAL
Research article
Bondarchuk I.S.1, Bondarchuk S.S.2' *
1 ORCID: 0000-0001-6995-0547;
2 ORCID: 0000-0001-6842-9143;
1 Tomsk State University, Tomsk, Russia;
2 Tomsk State Pedagogical University, Tomsk, Russia;
2 Institute for Problems in Chemical and Energetic Technologies SB RAS, Russia
* Corresponding author (isbs[at]mail.ru)
Abstract
The article proposes an efficient (accurate, simple and intuitively clear, fast executable and having a short program code) algorithm based on the broad computing capabilities of Excel without using VBA macros. As an example, the implementation of the calculation of the Arrhenius integral in one Excel cell is given. The accuracy of the calculations is analyzed by comparison with the often-used approximations in chemical kinetics for calculating the integral for the range of values that occur during gas and solid-phase reactions. This algorithm can be used for the numerical integration of other nonelementary integrals encountered in solving practical problems and data processing. Illustrative examples (screenshots of a spreadsheet) contain comments and can be quickly reproduced and checked.
Keywords: numerical integration, Arrhenius integral, spreadsheets, Excel.
Introduction
One of the most accessible computational tools for data processing [1], [3], [5], [6] and solutions to relatively simple engineering problems [7], [8], [9] for researchers who are not professional programmers in their field are Excel spreadsheets.
A large number of mathematical models include terms describing any processes associated with the rate of chemical reactions [10], [11], [14], [15]. The most important parameter on which the rate constant of a chemical reaction k depends is the temperature T. To describe the dependence k(T), the empirical formula (function, law) of Arrhenius [16], which has the form
/< = ^exp(-^L) 8.3145 m^K , (1)
where E is the activation energy (J mol-1), and A =const is the pre-exponential factor (also known as frequency factor) whose dimension determines the dimension of the reaction rate constant.
Although the Arrhenius eq 1 is most often used to describe the temperature dependence k(T), it is not always the best approximation of the experimental data [17, 18]. In particular, the "extended" empirical equation [18]
k = A^Tm^exp(--^) (2)
R • T
depends on three parameters (A, m, and E) can more accurately approximate the experimental data than the traditionally used eq 1.
In the process of implementing models in many methods, it must calculate (or estimate the value) of the integral of the Arrhenius function.
The simplest and most accessible computing environment for researchers is spreadsheets, in particular, Excel with its sufficiently developed apparatus for computational and statistical functions. This is evidenced by several monographs and publications on solving problems of chemical kinetics using Excel tools [2], [3], [4], [6]. The use of spreadsheets does not require a special study of programming disciplines and is a convenient tool for automating calculations. An important circumstance is that Excel allows you to implement a short and "transparent" program code (in the proposed algorithm, the integral value is calculated in one cell of the spreadsheet), when the problem is solved without using VBA macros.
Theory and algorithm
When solving many problems of chemical kinetics, it must calculate the value of the integral of the Arrhenius function, which is called the Arrhenius integral IA in the literature. This integral is nonelementary, i. e., it cannot be expressed via elementary functions.
To estimate the value of the integral IA a large number of approximate solutions [17] have been published to this day; they vary in complexity and precision. The approximation data are based on the expansion (or the rational and the exponential approximations) of Arrhenius function via the dimensionless parameter x = E/RT
(E,T) = f e-(E/RT)dT = f e-xdT ^ e-xp(x), Ja Ja
e
J0
where p(x) is some function.
However, many of these approximations are gross or even inaccurate, especially for low values of the parameter x [17]. So, for example, for the Coats-Redfern formula [19]
RT2 / DT\
IA(E,T)^(l-2^)e-(E/^ (3)
the error reaches tens of percentage for small values of x<10.
For the range of values of the complex xE [5; 100] realized during gas and solid-phase reactions, one of the most accurate for calculating the Arrhenius integral is the Junmeng approximation [17]
RT2 E/RT + 0.25403 \n(E/RT) + 036665 KT) A( , ) E E/RT+ 0.24598 \n(E/RT) + 2.41457 .
Using approximate formulas eq 3, 4, the estimated value of the Arrhenius integral I A with a change in temperature in the range from T1 to T2 is determined by the relation
I = f e-(E/RT)dT = Ia(E, T2) - Ia(E, T±)
So, for example, according to the most accurate approximation eq 4 for E/R = 4000 K, the value of the Arrhenius integral is
I e-(E/RT)dT = 0.07308457 .
J500
But if we bear in mind the simplicity and accuracy of calculations for the important range of variation of the complex x = E/RT, then it is more expedient to use the numerical calculation of the integral, the effective algorithm for which is presented below. Moreover, the numerical calculation, in addition to the higher accuracy for the entire interesting range of variation of the E/R values are implemented by a shorter spreadsheet program code.
Known quadrature formulas can be obtained directly from the definition of the integral and its geometric interpretation I = f^ f(x) dx as the area of a curvilinear trapezoid for function f(x). For the problem in question, the midpoint rule [20] is the best solution in terms of precision and implementation complexity. According to this rule, the value of the integral is determined by the sum m of the areas of rectangles of equal width h = (b - a)/m, a subinterval [a, b] integration limits interval. The height of each rectangle is determined by the value of the function f(x) for the average x in each subinterval of
in
I = h^f(a+ (i-0.5)h). (5)
i=i
The approximation error of eq 5 is proportional to h2.
As an example, the following is an algorithm and the result of calculating the Arrhenius integral for the parameter E /R=4000 K in the temperature range from 7\=500 K to T2 =600 K.
A
T
1
Following the eq 5 of the middle rectangles, the current temperature value 7j for 10000 nodes of integration at each node (middle of the subinterval) within the limits of integration from 71 to 72 is determined by the relation
7\ = 71 + (i - 0.5)A7, i = 1,2,... ,10000 , A7 = (72 - 7^/10000.
The value of the integral is determined via the sum
10000
7=ArIexp(^) . (6)
¿=1 '
To generate a sequence of numbers (1, 2, ..., 10000) MS Excel ROW(A1 :A10000) function is used in the CSE format (Ctrl + Shift + Enter) of an array formula [21].
Figure 1 shows the program code in MS Excel and the results of the calculation of the Arrhenius integral /4 for f/R = 4000 K in the temperature range from 500 K to 600 K both for the proposed "single-cell" implementation for 10000 integration subintervals and the most precise approximation formula [17]. Copying formulas by dragging the fill handle is shown with bold arrows.
А В С D E F G H
1
This study
2 Г1 = 500
3 4 T2 = 600 =(D3-D2)/10000
5 6 E/R / = ♦7,3084967274E-02 AT = ^0,01
4000 array formula (CSE-formula)
/ 8 9 {=SUM(1/EXP(B6/(D2+(ROW(A1:A10000)-0,5)*H5)))*H5}
Error, %= 5,00E-08
10
Junmeng's approximate formula [17]
11 E/(RTi) = 8,00 ♦ -=B$6/D2
12 E/(RT2) = 6,67 ▼
13 =(Ell+0,25403*LN(Ell)+0.36665)/(Ell+0,24598*LN(Ell) +2,4l457)/Ell*D2/EXP(Ell)
14 W.Ti) = 1,706871Е-02|Ф
15 Ia(E,T2) = 9,015328Е-02т Error, % = 5,50E-04
J.O
17 / = 7,3084565255E-02< — =E15-E14
15
19 Simpson's rule 7,3084967311E-02 to approximate
Fig. 1 - Worksheet of the Arrhenius integral calculation
The table below is the difference (%) of the calculated values of the Arrhenius integral for an interesting range of variations of the complex compared with the values obtained using the Simpson method with an accuracy of 10-10.
Table 1 - Relative errors of Arrhenius integral approximations in percentages
E/й, K 2000 4000 8000 16000 32000 64000
x = £У(Д7) 3,3-4,0 6,7-8,0 13,3-16,0 26,7-32,0 53,3-64,0 106-128
approximation Error-values (%)
Coats-Redfem 46,4 11,8 3,0 0,8 0,2 0,05
Junmeng 6^10-2 6•10-4 2•10-4 1-10-6 2•10-5 4•10-4
eq (6) 8•10-9 5•lO-8 2•lO-7 9•lO-7 3•10-6 1 •10-5
It is easy to increase if required, the accuracy of calculations in the region of large values of ER> 30000 K for eq 6 by increasing the number of integration intervals.
The most accurate formula found by the authors for calculating the extended Arrhenius integral is the approximation Lei [18]
J, (x, 7, q) = — [V(x + q + 1)2 + 4x - x - q - lje*,
T2
I = J Tie-(E/RT)dT * Iq(x,T2) - Iq(x,T1) .
Tl
Figure 2 shows a screenshot of the Excel sheet for calculating the value of the extended integral of the extended Arrhenius function (2), from which it can be seen that the proposed approach is 6 orders of magnitude more accurate than the best approximation [18].
1 This study
2 ТЛ = 500
3 4 h. — 600 =(D3-D2)/10000
5 6 / 8 E/R I = •3,0895945573 E-03 AT = 0,01
4000 array formula (CSE-formula)
q {=SUIV /EXP( ( (D2+ (CTPOKA(A1:A10000J-0,5]*H5) ЛВ9 B6/(D2 +(ROW(A1:A10000)-0,5)*ÎH5) ) ГН5}
У -u,b
10 11 Error, % = 4Д6Е-08
Lei X.-W. approximate formula [18]
12 x1 = 8,00 t 4- —=B$6/D2
13 %2 6,67 \
xt
15 =(((Е12+В$9+1)л2+4*Е12)л0,5-Е12-В$9-1)/2
16 / EXP(E12)/E12*D2A(B$9+1)
17 8,018233E-04t^
18 IQ(.x2,T2,q) = 3,896629Е-03т Error, % = 0,17
20 ! = 3,0948060469E-03^— =E25-E24
¿-¿. 23 Т/1
Simpson's rule to approximate 3,0895945586E-03
Fig. 2 - Worksheet of the modified Arrhenius integral calculation
A comparative analysis of the numerical estimates of the Arrhenius integral shows that for the range of E/RT values implemented in practical studies, the accuracy of the proposed calculation algorithm is several orders of magnitude higher than any used approximations.
Conclusion
The algorithm allows calculating with high accuracy the value of the Arrhenius integral (in one Excel cell) for the entire interesting range of values of the E/RT complex instead of using any of its approximations. The algorithm is easily generalized to the integral of the extended Arrhenius function.
The algorithm is implemented by a simple short program code, does not require programming experience in high-level languages, and can be easily mastered by researchers. The code and calculation results are illustrated with screenshots of Excel worksheets - the most common software for analyzing numerical data. The code is annotated showing all the formulas used and provides complete instructions for numerical integration.
Конфликт интересов Conflict of Interest
Не указан. None declared.
Список литературы / References
1. De Levie R. How to use Excel® in Analytical Chemistry and in General Scientific Data Analysis / R. De Levie. -Cambridge: Cambridge University Press, 2004. - 487 p.
2. Бондарчук С.С. Статобработка экспериментальных данных в MS Excel: учебное пособие / С.С. Бондарчук, И.С. Бондарчук. - Томск: Изд-во Томского гос. пед. ун-та, 2018. - 431 с.
3. Liengme B.V. A guide to Microsoft Excel 2013 for scientists and engineers / B.V. Liengme. -Amsterdam: Academic Press, 2016. pp. 273-287.
4. Бондарчук И.С. Кинетика гомогенных химических реакций: методы решения в Excel / И.С. Бондарчук, С.С. Бондарчук. - Томск: Изд-во Томского гос. пед. ун-та, 2019. - 263 с.
5. Бондарчук И.С. Методология решения задач физической химии инструментом Solver MS Excel / И.С. Бондарчук, И.А. Курзина, С.С. Бондарчук // Высшее образование сегодня. - 2014. - № 9. - С. 22-24.
6. Бондарчук И.С. Идентификация кинетических параметров процесса десорбции / И.С. Бондарчук, И.А. Жуков и др. // Перспективы развития фундаментальных наук: сб. труд. XII Межд. конф / Томск: Изд-во Томского политех. унта, 2015. - С. 380-382.
7. Бондарчук И.С. Критерий формообразования частиц в процессе плазмохимического синтеза порошков / И.С. Бондарчук, А.С. Жуков // Химическая физика и актуальные проблемы энергетики: сб. докл. и ст. Всерос. молод. конф. / Томск: Изд-во Томского политехи. ун-та, 2012. - С. 34-38.
8. Bondarchuk I.S. Identification of kinetic triplets by results of derivatographic analysis / I.S. Bondarchuk, S.S. Bondarchuk, B.V. Borisov // Heat and Mass Transfer in the Thermal Control System of Technical and Technological Energy Equipment. MATEC Web of Conferences. - 2018, - P. 01010(1)-01010(4).
9. Бондарчук И.С. Идентификация кинетических параметров твердофазных реакций / И.С. Бондарчук, И.А. Жуков и др. // Полифункциональные химические материалы и технологии. Мат-лы межд. науч. конф. / Томск: Издательский Дом ТГУ, 2015. - Т. 2. - С. 34-37.
10. Zhukov A. Energetic borides: combustion synthesis and properties / A. Zhukov, M. Ziatdinov, I. Bondarchuk et al. // Proc. of the 46th Int. Ann. Conf. of the Fraunhofer ICT. - 2015. - P. P75(1)-P75(5).
11. Vorozhtsov A. The mutual Influence of nanometal additives on heat release rate in energetic condensed systems /
A. Vorozhtsov, N. Rodkevich, I. Bondarchuk et al. // Int. J. Energetic Materials Chem. Prop. - 2017. - V. 16 (4). - P. 329-336.
12. Bondarchuk I. Modeling the evolution of the aerosol cloud of toxicants in the atmosphere / I. Bondarchuk, S. Bondarchuk, B. Borisov // MATEC Web of Conferences. - 2017. - V. 141: Smart Grids 2017: The Fifth Int. Youth Forum, 9-13 October, 2017, Tomsk, Russia. - P. 01013(1) - P. 01013(6).
13. Vorozhtsov A. Ballistic characteristics of solid propellants containing dual oxidizer/ A. Vorozhtsov, V. Arkhipov, S. Bondarchuk et al. // Proc. European Conference for Aerospace Sciences EUCASS. 2005. - P. 5.03.01(1)-P. 5.03.01(8).
14. Vorozhtsov A. Thermokinetic investigation of the aluminum nanoparticles oxidation / A. Vorozhtsov, N. Rodkevich, I. Bondarchuk et al. // Int. J. Energetic Materials Chem. Prop. - 2017. - V. 16 (4). - P. 309-320.
15. Zhukov I. Plasma-chemical method for producing metal oxide powders and their application / I. Zhukov., S. Vorozhtsov, V. Promakhov et al. // Journal of Physics: Conference Series. - 2015. - V. 652. - P. 012027(1)-012027(5). 16. Arrhenius S. About the reaction rate in the inversion of cane sugar/ S. Arrhenius // Z. Phys. Chem. - 1889. 4. - P. 226-248.
16. Arrhenius S. About the reaction rate in the inversion of cane sugar / S. Arrhenius // Z. Phys. Chem. - 1889. 4, -P. 226-248.
17. Junmeng C. A new formula approximating the Arrhenius integral to perform the non-isothermal kinetics / C. Junmeng, H. Fang et al. // Chem. Eng. J. - 2006. - V. 124 (1-3). - P. 15-18.
18. Lei X.-W. New analytical approximate solution of the generalised temperature integral for kinetic reactions / X.-W. Lei, J.-B. Liu et al. // Materials Science and Technology. - 2020. - V. 36. - P. 1655-1662.
19. Coats A.W. Kinetic Parameters from Thermogravimetric Data / A.W. Coats, J. P. Redfern // Nature. - 1964. - V. 201. -P. 68-69.
20. Burden R.L. Numerical Analysis / R.L. Burden, J.D. Faires. - Boston: Cengage Learning, 2011. - 888 p.
21. Microsoft support. [Electronic resource]. URL: https://support.microsoft.com/en-us/office/video-array-formulas-8ff8257a-b28e-4e81-b4f8-30f793412dfa (accessed 21.08.2020).
Список литературы на английском языке / References in English
1. De Levie R. How to use Excel® in Analytical Chemistry and in General Scientific Data Analysis / R. De Levie. -Cambridge: Cambridge University Press, 2004. - 487 p.
2. Bondarchuk S.S. Statobrabotka ehksperimental'nykh dannykh v MS Excel: uchebnoe posobie [Statistical processing of experimental data in MS Excel: a tutorial] / S.S. Bondarchuk, I.S. Bondarchuk. - Tomsk: Publishing house of Tomsk state ped. un-ty, 2018. - 431 p [in Russian].
3. Liengme B.V. A guide to Microsoft Excel 2013 for scientists and engineers / B.V. Liengme. -Amsterdam: Academic Press, 2016. pp. 273-287.
4. Bondarchuk I.S. Kinetika gomogennykh khimicheskikh reaktsij: metody resheniya v Excel [Kinetics of Homogeneous Chemical Reactions: Methods of Solution in Excel] / I.S. Bondarchuk, S.S. Bondarchuk. - Tomsk: Publishing house of Tomsk state ped. un-ty, 2018. - 263 p [in Russian].
5. Bondarchuk I.S. Metodologiya resheniya zadach fizicheskoj khimii instrumentom Solver MS Excel [Methodology for solving problems of physical chemistry using the Solver MS Excel tool] / I.S. Bondarchuk, I.A. Kurzina, S.S. Bondarchuk // Vysshee obrazovanie segodnya [Higher education today]. - 2014. - № 9. - P. 22-24 [in Russian].
6. Bondarchuk I.S. Identifikatsiya kineticheskikh parametrov protsessa desorbtsii [Identification of the kinetic parameters of the desorption process] / I.S. Bondarchuk, I.A. Zhukov, V.V. Promakhov // Perspektivy razvitiya fundamental'nykh nauk: sb. trud. XII Mezhd. konf. [Prospects for the development of fundamental sciences: collection of articles XII International Conference / Tomsk: Publishing House of Tomsk Polytechnic University, 2015. - pp 3 80-3 82 [in Russian].
7. Bondarchuk I.S. Kriterij formoobrazovaniya chastits v protsesse plazmokhimicheskogo sinteza poroshkov [The criterion for the particles formation in the process of plasma-chemical synthesis of powders] /I.S. Bondarchuk, A.S. Zhukov // Khimicheskaya fizika i aktual'nye problemy ehnergetiki: sb. dokl. I-st. Vseros. konf. [Chemical physics and current problem of power engineering: collection of reports and articles All-Russian] / Tomsk Polytechnic University. - Tomsk: Publishing House of Tomsk Polytechnic. University, 2012. - 34-38 pp [in Russian].
8. Bondarchuk I.S. Identification of kinetic triplets by results of derivatographic analysis / I.S. Bondarchuk, S.S. Bondarchuk,
B.V. Borisov // Heat and Mass Transfer in the Thermal Control System of Technical and Technological Energy Equipment. MATEC Web of Conferences. - 2018, - P. 01010(1)-01010(4).
9. Bondarchuk I.S. Identifikatsiya kineticheskikh parametrov tverdofaznykh reaktsij [Identification of kinetic parameters of solid-phase reactions] /I.S. Bondarchuk, I.A. Zhukov et al. // Polifunktsional'nye khimicheskie materialy i tekhnologii [Multifunctional chemical materials and technologies] / Tomsk: TSU Publishing House, 2015. - Vol. 2. - 34-37 pp [in Russian].
10. Zhukov A. Energetic borides: combustion synthesis and properties / A. Zhukov, M. Ziatdinov, I. Bondarchuk et al. // Proc. of the 46th Int. Ann. Conf. of the Fraunhofer ICT. - 2015. - P. P75(1)-P75(5).
11. Vorozhtsov A. The mutual Influence of nanometal additives on heat release rate in energetic condensed systems / A. Vorozhtsov, N. Rodkevich, I. Bondarchuk et al. // Int. J. Energetic Materials Chem. Prop. - 2017. - V. 16 (4). - P. 329-336.
12. Bondarchuk I. Modeling the evolution of the aerosol cloud of toxicants in the atmosphere / I. Bondarchuk, S. Bondarchuk, B. Borisov // MATEC Web of Conferences. - 2017. - V. 141: Smart Grids 2017: The Fifth Int. Youth Forum, 9-13 October, 2017, Tomsk, Russia. - P. 01013(1) - P. 01013(6)
13. Vorozhtsov A. Ballistic characteristics of solid propellants containing dual oxidizer/ A. Vorozhtsov, V. Arkhipov, S. Bondarchuk et al. // Proc. European Conference for Aerospace Sciences EUCASS. 2005. - P. 5.03.01(1)-P. 5.03.01(8).
14. Vorozhtsov A. Thermokinetic investigation of the aluminum nanoparticles oxidation / A. Vorozhtsov, N. Rodkevich, I. Bondarchuk et al. // Int. J. Energetic Materials Chem. Prop. - 2017. - V 16 (4). - P. 309-320.
15. Zhukov I. Plasma-chemical method for producing metal oxide powders and their application / I. Zhukov., S. Vorozhtsov, V Promakhov, I. Bondarchuk et al. // Journal of Physics: Conference Series. - 2015. - V 652. - P. 012027(1)-012027(5).
16. Arrhenius S. About the reaction rate in the inversion of cane sugar / S. Arrhenius // Z. Phys. Chem. - 1889. 4, -P. 226-248.
17. Junmeng C. A new formula approximating the Arrhenius integral to perform the non-isothermal kinetics / C. Junmeng, H. Fang et al. // Chem. Eng. J. - 2006. - V 124 (1 3). - P. 15-18.
18. Lei X.-W. New analytical approximate solution of the generalised temperature integral for kinetic reactions / X.-W. Lei, J.-B. Liu et al. // Materials Science and Technology. - 2020. - V 36. - P. 1655-1662.
19. Coats A.W. Kinetic Parameters from Thermogravimetric Data / A.W. Coats, J. P. Redfern // Nature. - 1964. - V. 201. -P. 68-69.
20. Burden R.L. Numerical Analysis / R.L. Burden, J.D. Faires. - Boston: Cengage Learning, 2011. - 888 p.
21. Microsoft support. [Electronic resource]. URL: https://support.microsoft. com/en-us/office/video-array-formulas-8ff8257a-b28e-4e81-b4f8-30f793412dfa (accessed: 21.08.2020).
