- Detail

Application of Excel custom function in water quality work

excel is a very familiar application software, which is very convenient to use. In particular, its powerful function brings us great convenience, but sometimes we may use several functions or adopt nested functions to realize a specified function, which is not convenient to apply; Or if you use a custom formula for calculation, there is no ready-made formula available. Fortunately, we can create a user-defined function (UDF), which runs exactly the same as the functions in Excel. There are several advantages in establishing user-defined functions: establishing functions with their own special functions and names; It can combine complex, nested and multiple original functions, give play to greater power, and make use and maintenance more convenient

for example, in the calculation of water quality, the calculation formula of permanganate index is very cumbersome, and the result must be rounded to 0.1, and the problem of every 5 odd entries and even rounding must be considered. It is troublesome to calculate step by step with a calculator; If you don't pay attention to using cell reference calculation in Excel, it may cause errors in calculation, and it has to be established every time, which is very inconvenient. Now we use the VBA device of Excel to build our own functions to solve this problem

first enter excel, In [tools → [macro → [visual basic device (you can also press the combination key [alt+f11), select [insert → [add module] in "visual basic device", change the name of the module to "permanganate index concentration calculation formula", enter the following common ball screws and trapezoidal screw functions in the code window:

public function cimn (C as single, V0 as single, V1 as single, V2 as single, V as integer) 'permanganate index calculation formula

dim dotlocation As Integer' defines decimal point operation. In addition, drainage and exhaust device position variables

'******************************************************************************* Reflector cover and tailgate

cimn = (((((10 + V1) * 10/V2 - 10) - (((10 + V0) * 10/V2 - 10) * (100 - V)/100)) * c * 8000/V

'**************************************************************************************************************, Automatically fill up the last digit 0

if cimn 1 then

cimn = 0 cimn

end if

'determine the position of the decimal point

dotlocation = instr (cimn, ".")

'judge whether the second digit after the decimal point is 5

if mid (cimn, dotlocation + 2, 1) = 5 then

if len (cimn) = dotlocation + 2 and mid (cimn, dotlocation + 1, 1) mod load is low 2 = 0 then

cimn = left (cimn, dotlocation + 1)' is 5 and there is no number after 5 and the previous digit of 5 is even, Round() function directly

else

cimn = round (cimn, 1)

end if

else

cimn = round (cimn, 1) 'not 5, round() function directly

end if

cimn = format (round (cimn, 1), "#0.0")

end function <./p>

we know that functions in Excel have a description to help use, and we also need to add a description to this function. Select the "object browser" in the toolbar (as shown in Figure 1), and select the "permanganate index concentration calculation formula" module we made, Add a description of this function in its [right click] → [attribute. This description will appear in the description of the function in Excel. If you want to keep the software confidential, right-click on the "module", and [vbaproject attribute] → [set password in protection, and others will not see your source program.

Figure 1 describes the function

at this time, exit, return to the excel interface, and save this file as: type is "Microsoft Excel add in", in Excel 2003, it will automatically change the save location to "c:documents and settingswzwemc (computer user name) application datamicrosoftaddins" (the system is installed in c: ＼ windows). Of course, you can also copy this file "permanganate index concentration calculation formula.Xla" directly to the office ＼ Library (under the installation path of office), In Excel 97, it can only be placed in the latter position. Using functions is simple, Click [tools → [add in], and check the "permanganate index concentration calculation formula" you created, as shown in Figure 2. Directly enter "=cimn()" in the cell, and select the corresponding parameters (as shown in Figure 3) 。 Is it a little professional like the functions provided by Microsoft office

Figure 2 add in

Figure 3 uses the user-defined function

if you copy the excel file that calls this macro to another machine to run, the prompt "the current document to be opened contains links to other documents, do you want to update the current workbook with changes in other workbooks" will appear, and the originally calculated data can be displayed, This is because in [tools → [options → [recalculation] in Excel, generally select "save external link data", but you can't recalculate, because you can't link this macro, and this function doesn't exist on other machines at all.

in addition, we can add relevant instructions for the "add in", which is more conducive to the maintenance of custom functions. (as shown in Figure 3), find the "permanganate index concentration calculation formula.Xla" , click [right click → [attribute → [Summary (as shown in Figure 4), and add the required content to the description to describe the function. The "title" part will appear in the box of "add in", and the "remarks" part will appear in the function description part below. At this time, see whether it is professional enough. In Excel

Related Topics

- ·Booster jab keeping Covid infections low in Nursin
- ·Chaos erupts in Oslo after Norway ends COVID-19 re
- ·China draw 1-1 with Netherlands in Billie Jean Kin
- ·Staffing shortages, absentee leaders linked to hig
- ·McGowan’s bizarre back to school plan - Today News
- ·Serial pervert jailed for trying to film up school
- ·CoCT still waiting on funds from govt to help fire
- ·New documentary Roadrunner seeks to understand Ant

LINK

网站地图
疾病文章
服装定做
订做服装
定制服装
职业装定制
职业装订制
制作工作服
定做工服
工作服
工作服定做
液压式万能试验机
硬度测量仪
硬度计
拉力机
塑料专机
上海和平眼科医院
廊坊友谊医院
广州华佑医院
武汉仁安眼耳鼻喉医院
冲击试验机
鳞状色素上皮病变治疗
电子试验机
工服定做
昆明哪家医院专治风湿
离婚律师
西宁皮肤病医院
药品库
佛山癫痫病医院
江门牛皮癣医院
六安尖锐湿疣医院
枣庄性病医院
北京离婚律师推荐
依兰琳卡
工作服门户
重庆肛肠医院
嘉兴甲状腺医院
潍坊男科医院排名
寻医问药
大连治疗甲亢专业医院
NewsTop
Article SiteMap
Column SiteMap

Copyright © 2011 JIN SHI