excel函数代码

excel函数代码（精选8篇）

excel函数代码 篇1

用途：返回修正Bessel函数值，它与用纯虚数参数运算时的Bessel 函数值相等，

语法：BESSELI(x，n)

参数：X为参数值。N为函数的阶数。如果 n 非整数，则截尾取整。

2.BESSELJ

用途：返回 Bessel 函数值。

语法：BESSELJ(x，n)

参数：同上

3.BESSELK

用途：返回修正Bessel函数值，它与用纯虚数参数运算时的Bessel 函数值相等。

语法：BESSELK(x，n)

参数：同上

4.BESSELY

用途：返回Bessel 函数值，也称为Weber函数或Neumann函数。

语法：BESSELY(x，n)

参数：同上

5.BIN2DEC

用途：将二进制数转换为十进制数。

语法：BIN2DEC(number)

参数：Number待转换的二进制数。Number的位数不能多于10位(二进制位)，最高位为符号位，后9位为数字位。负数用二进制数补码表示。

6.BIN2HEX

用途：将二进制数转换为十六进制数。

语法：BIN2HEX(number，places)

参数：Number为待转换的二进制数。Number 的位数不能多于10位(二进制位)，最高位为符号位，后 9 位为数字位。负数用二进制数补码表示;Places为所要使用的字符数。如果省略places，函数 DEC2BIN用能表示此数的最少字符来表示。

7.BIN2OCT

用途：将二进制数转换为八进制数。

语法：BIN2OCT(number，places)

参数：Number为待转换的二进制数;Places为所要使用的字符数。

8.COMPLEX

用途：将实系数及虚系数转换为 x+yi 或 x+yj 形式的复数。

语法：COMPLEX(real_num，i_num，suffix)

参数：Real_num为复数的实部，I_num为复数的虚部，Suffix为复数中虚部的后缀，省略时则认为它为i。

9.CONVERT

用途：将数字从一个度量系统转换到另一个度量系统中。

语法：CONVERT(number，from_unit，to_unit)

参数：Number是以from_units为单位的需要进行转换的数值。From_unit是数值 number的单位。To_unit是结果的单位。

10.DEC2BIN

用途：将十进制数转换为二进制数。

语法：DEC2BIN(number，places)

参数：Number是待转换的十进制数。Places是所要使用的字符数，如果省略places，函数DEC2OCT用能表示此数的最少字符来表示。

11.DEC2HEX

用途：将十进制数转换为十六进制数。

语法：DEC2HEX(number，places)

参数：Number为待转换的十进制数。如果参数 number是负数，则省略places。Places是所要使用的字符数。

12.DEC2OCT

用途：将十进制数转换为八进制数。

语法：DEC2OCT(number，places)

参数：Number为待转换的十进制数。如果参数 number是负数，则省略places。Places是所要使用的字符数。

13.DELTA

用途：测试两个数值是否相等。如果 number1=number2，则返回1，否则返回0。

语法：DELTA(number1，number2)

参数：Number1

excel函数代码 篇2

随着计算机软件业的不断发展,计算机软件安全日益引起人们关注。二进制代码分析是计算机软件安全领域的一个重要手段。在分析某些无法获取源代码的软件时,分析其二进制代码几乎是唯一手段。人们很少直接分析二进制代码,因此一般都是先将机器代码反汇编成汇编代码。库函数识别的作用在于可以直接将反汇编代码中的大段代码替换成可理解的符号( 函数名) ,便于分析人员理解整个程序的反汇编代码。在编译优化的作用下,内联库函数在内联到目标代码后,形态可能发生变化。因此,内联库函数识别存在两个难点要解决。首先,为了对齐指令地址,或指令流水优化, 编译器可能会调整一个函数的某些指令顺序,造成内联库函数字节不连续。其次,编译器寄存器分配策略也会影响内联后的指令,使其发生变化。针对这两个问题,本文基于执行流图来识别内联库函数。

1库函数识别

1. 1执行流图

执行流图EFG ( Execution Flow Graph) G = ( V,E) ,V代表指令集合,代表指令之间执行依赖关系。边( vi, vj) 表明vi早于vj的执行。使用R/W表示指令读/写的变量集合,D表示指令的地址。

一个EFG中的边有两类。第一类边在基本块( 单进单出的指令序列) 内。对于基本块内的两个点a,b,如果满足( Ra∩ Wb) ∪( Wa∩ Rb) ∪( Wa∩ Wb) ≠Ø,Da≤Db,或者b为控制流转移指令,则存在a到b的边。另一类在基本块间。 边( a,b) 满足:

( 1) a和b分别在不同的基本块B1和B2内;

( 2) a可以在基本块B1中最后执行,b可以在基本块B2中第一个执行;

( 3) 在控制流图中,B2是B1的后继。

EFG的构造分为两步。首先,划分函数的基本块,构造控制流图。然后,在基本块内进行数据依赖分析,得到局部EFG。最后,对于每对在控制流图中直接相连的基本块,连接其所对应局部EFG中的出口点和入口点。对于局部EFG中的任意三点A,B,C,如果存在三条边( A,B) ,( B,C) , ( A,C) ,则删除边( A,C) ,因为这条边是冗余的。

1. 2指令编码

指令编码用来将一个指令转换到一个标准形式。首先, 一条指令通过下列步骤规范化来消除指令差别:

( 1) 指令规范化。使用reg1来表示指令操作数中的第一个寄存器,reg2表示第二个寄存器,以此类推。如“mov eax, ecx”“mov reg1,reg2”,“xor eax,eax”“xor reg1,reg1”。

( 2) 内存规范化。使用M表示访存操作数,如“mov eax, [ebx]”“mov reg1,M”。

其次,用二元组SR = ( m,r) 表示消除差异后的指令,其中m表示指令助记符,r为操作数特征值。r中的数字,从高到低,表示操作数的类型。如r = 1表示“op reg1”,r = 11表示“op reg1,reg1”,r = 21表示“op reg1,reg2”。表1给出了指令的所有可能特征值。

最后,SR = ( m,r) 通过调用函数ID( SR) = Hash( m) | ( r < < 16) 转换为一个32位数字,称为此指令的ID。Hash ( ) 是一个字符串函数,将不同的指令助记符映射到不同的16位整数。在同构测试时,指令只要有相同的ID,即认为这些指令是相等的。

1. 3识别

在库函数集合F中目标函数fT的过程如下:

( 1) 反汇编fT;

( 2) 构造fT的CFG;

( 3) 对于F每个内联库函数fL:

1构造fT的EFG;

2检测GT中所有与fL的EFG同构的子图;

3检测每个检测到的子图是否可以外联( 参见本文之前的工作［1］) ;

4报告每个检测到的可以外联的子图为一个库函数。

2实验

2. 1设置

实验所用开源程序列表如表2所示。相应地,分别使用Microsoft Visual C + + 10 ( MSVC ) 和Intel Compiler XE 14 ( ICC) 编译,并生成调试信息。

由于源代码和二进制代码间存在巨大差异,二进制代码中的内联函数的真实信息几乎不可能获知。内联函数和其余代码之间没有明显分界。任何代码片段都可以视为某个内联函数的实例。但由于实验对象是开源软件,因此在一个内联函数之后可以插入一些特殊代码,就可以定位到内联函数的真实信息且不会影响内联函数的EFG。

从源代码编译得到的二进制文件称为原始文件集。从修改后的源代码编译得到的可执行文件称为修改的文件集。 在修改的源代码中,一些函数的随机位置被插入宏“LIBV ( P) ”。P是一个指针或者一个变量的地址。在宏里,输出库函数的结果到屏幕,使得插入代码不会被当成死代码。然后修改宏的定义来生成修改的二进制文件集。strlen( ) 的定义为“#define LIBV( P) printf( " libvcode % d" ,strlen( ( char* ) ( P) ) ) ”。其他函数类似。printf的特殊格式有助于在汇编代码中识别插入的代码。比如在识别strlen( ) 中,代码

其中,位于loc_40B600和push eax之间的代码是strlen ( ) 的一个实例。

实验中的内联库函数有strlen( ) 、strcpy( ) 、strcmp( ) 、 memcmp( ) 。这些函数经常被MSVC内联。相应的源代码来自“Microsoft Visual Studio 10. 0 VC crt src”。汇编代码来自Visual C + + 10,使用参数/FAcs编译( 输出汇编代码,机器代码和源代码) 。编译使用这些库函数的样本代码并提取内联的代码。

对于原始文件集,如果识别出的内联函数在源代码中能找到,则认为识别结果是正确的。原型工具在原始二进制文件集中识别所有4个内联函数。函数strlen( ) 、strcpy( ) 、strc- mp( ) 在C / C + + 程序中常见。在C + + 中标准字符串类的操作包含了这些C字符串函数。在原始二进制文件集的实验中,这些识别结果也会被认为是正确的,即使识别出的函数没有在源代码中显式使用。对于修改的文件集,只统计以 “libvcode% d”为格式化字符串的printf结尾的识别结果。内联函数的真实情况由IDA的一个插件来统计,通过扫描以 “libvcode% d”为格式化字符串的printf得到。

由表3中可见,除了Win Merge,其余程序的每种内联函数数量都不相同。因为一种内联函数的文件集是通过改变插入宏的定义来生成的,这就使得理论上不同内联函数的数量应该一致。但是,一个内联函数实例的数量在没有检查二进制代码前是不确定的,因为某些被插入宏的短的目标函数可能会被编译器内联。另一方面,源代码中插入的宏在编译预处理阶段被展开,增加了一个目标函数的大小。因此,一些目标函数在宏定义为一种内联函数时会被内联,而在宏定义为另一种内联库函数时则不会被内联。

2. 2实验结果

表4和表5分别给出了MSVC和ICC在原始文件集上查准率。表中的'- ' 表示无法计算值。原始文件集里的内联库函数的真实情况未知。因此在表4和表5中查全率无法给出。表6给出了MSVC编译的修改的文件集的查全率和查准率。Win Merge中的strcmp的查准率是空的,因为strcmp在Win Merge中没有被内联。ICC编译的修改的文件集的实验结果没有列出,因为没有发现任何结果。

2. 3讨论

2. 3. 1查准率

表4表明原型工具可以准确地识别内联函数。在原始文件集中仅有少数误报。误报是因为工具无法区分字符串函数的宽字节版本和非宽字节版本。类似wscmp( ) 被误认为strcmp( ) 。两者之间代码的差别就在于操作数的大小( 宽字节版本16位,非字节版本8位) 不同。而在指令编码中, 不考虑操作数的大小,因此导致了上述误报。

2. 3. 2查全率

如表6所示,在修改的文件集中,除了memcmp( ) ,查准率都很高。这是因为memcmp( ) 的指令发生了改变。代码如下所示。

通常,有三个原因造成漏报。首先,函数的反汇编代码可能是不完整的。在反汇编中,工具在遇到间接跳转时停止反汇编。在修改的文件集中,有些代码是插入到了一个case语句中。因为switch/case语句常被编译器翻译为间接跳转, 因此这些内联函数没有被工具识别。

其次,在编译器优化后,一个内联函数可能有不同的指令序列。大多数的漏报都是由于这个原因。比如,编译器可能将一条指令替换为语义等价的指令。如test eax,eax可以替换为cmp eax,ebx,当ebx值为0时。内联函数的参数可以通过寄存器或内存来传递。传递方式也会影响一个内联函数的指令。memcmp( ) 参数count保存到了局部变量var_ 268中,而在库函数中则是一个寄存器中。其他优化,如循环判断外提和循环展开也会影响识别结果。

特别地,一个内联函数可能消失在二进制代码中。一个表达式的结果可能会在编译时被编译器计算出。如strlen( ) 的参数是一个固定字符串,编译器会直接计算出字符串的长度,而不会生成调用strlen( ) 的代码。因此,一些内联函数可能在修改后的文件集中没有出现在期望的位置,也就没被工具发现。

最后,一个内联函数在不同的编译器编译后可能有不同的指令序列。在实验中,因为内联函数的代码是从MSVC中提取,这些代码可能会和其他编译器编译的完全不同。在ICC编译的代码里,由于某些未知原因,strlen( ) 和strcpy( ) 不总是内联。memcmp( ) 和strcmp( ) 被ICC内联,但是对其编译后的二进制代码却与MSVC编译的有所不同。因此,和MSVC编译的原始文件集的实验结果对比,ICC编译的原始文件集中却只有很少的内联函数获得识别,如表5所示。

3相关工作

最负盛名的库函数识别技术是IDA FLIRT［2］。使用字节模式匹配算法来判断一个目标函数是否与IDA已知的签名匹配。DCC［3］类似于FLIRT。Hancock［4］扩展了FLIRT技术,提出一个库函数引用启发规则,认为一个函数如果在一个库函数中被静态调用,那么该函数也是库函数。这些方法的主要缺陷在于一个函数只有头n个字节作为匹配模式。 尽管通过增大n的大小,精度可以很容易获得提高。但是不能保证导入所有库函数。UNSTRIP［5］通过系统调用接口即可识别在二进制代码中的包裹函数( Wrapper Function) 。尽管如此,这个方法却只是局限于识别包裹函数,因为一个库函数可能没有call指令。

最近,文献[6]提出了一个二进制代码搜索的技术。为了计算函数间的相似性,即将函数分解为连续,且简短的代码序列。本文方法和该文献中的方法都需要和编译器优化。 而且,文献[6]中的方法的最大限制是可能产生差的结果,当匹配拥有低于100基本块的函数。

Saed等人提出了一个通过匹配语义整合图的短路径( 轨迹) 识别二进制代码中复用函数的新技术［7］。一个语义整合图整合了控制流图,寄存器流图,函数调用图。尽管这一方法和本文方法相同之处都是基于图,但是其中的匹配过程和本文却完全不同。另外,该法在图上定义了不同类型的轨迹,并且是使用图编辑距离来衡量两个图的相似度。

4结束语

识别不连续字节或有多个变种的库函数是困难的。本文介绍了一个识别内联库函数的新方法。方法的新颖性在于将库函数识别问题转化为EFG子图同构问题。在一些流行软件上设计了一定仿真,从而验证了本文方法的有效性。 如何加速子图同构测试将是本文下一步的重点研究工作。

摘要：内联库函数识别是二进制代码分析的难点问题之一。主要的挑战来自在编译优化的作用下,内联库函数在目标函数中存在多态性和不连续性。本文构建函数的执行流图,将内联库函数识别问题转化为执行流图子图同构测试问题。实验中,对四个常被编译器内联的字符串操作函数,使用MSVC10和ICC14这两个编译器在5个开源软件中进行内联库函数识别测试。实验结果表明,本文方法可以有效识别二进制代码中的内联库函数。

巧用EXCEL函数建新生学籍 篇3

一、首位带零的长学号生成方法:

学校为学生建立学籍往往要编辑学号。例如:某校的学号编辑规律是录取年份两位数、学校代码五位数、院系代码一位数、学历层次两位数、专业代码两位数、班级一位数、顺序号两位数,共计十五位数。例如:07年录取的某专业新生,编辑的学号为“071698042034012”。其中,07是录取年份,16980是学校代码,4是院系代码,20是学历层次编码,34是专业编码,0表示是单班,12是学生的顺序号。

在电子表格中生成学号时,学籍人员往往将输入学号一列定义为“文本”,否则不能保留首位零,然后一一输入,效率极低;如果使用“常规”或“数值(小数位数定义为零)”,因学号编码太长,当超过十一位时,就自动采用科学记数法,不能正常显示学号编码,且首位“零”不能保留。

而事实上只要合理利用文本合并函数“CONCATENATE”,就可轻松解决这一问题。

语法:CONCATENATE (text1,text2,...)

Text1,text2,...为1到30个将要合并成单个文本项的文本项。这些文本项可以为文本字符串、数字或对单个单元格的引用。

在实际操作中,text1、text2、……分别指电子表格中的某个单元格中的字符串,常用单元格名称代替。

假定:在A列A3单元格以下生成新生学号。先在C列C3、C4分别输入“34001”、“34002”,选中此二格,利用填充柄向下生成所需顺序号数值。然后选择B列,定义B列为“常规”,再选中B3格,在英文输入法状态下,输入“=concatenate(‘07169804203’,C3)”,敲回车键,即可在B3单元格产生十五位学号。然后用填充柄在B列向下拖拽可产生所需学号,快速简便,效率极高。生成的学号会因删除B、C两列无用数值而产生错误,所以在B列生成十五位学号后,必须全部选中,单击鼠标右键,在弹出的快捷菜单中选择“复制”,再选中A3单元格,单击鼠标右键,在弹出的快捷菜单中选择“选择性粘贴”,在弹出的“选择性粘贴”对话框中,选择“数值”,单击“确定”按键,即可将B列产生的学号全部复制到A列。然后全部删除B、C列的数值。

二、巧用身份证号生成出生年月:

建立学生学籍时,往往既要输入出生年月也要输入身份证号,输入耗时且易出错,有时往往出现身份证号与出生年月不一致的问题,给校对带来很大的麻烦。

在实际输入中,只要输入正确的身份证号,经校对无误后,可用MID函数自动生成出生年月。

MID 返回文本字符串中从指定位置开始的特定数目的字符,该数目由用户指定。

语法:MID(text,start_num,num_chars)

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num为1,以此类推。

Num_chars 指定希望MID从文本中返回字符的个数。

学生的身份证号全部为18位,几乎没有15位,前六为省、市、县区代码,中间8位为出生年月日,后4位为该生编码。而我们要用的就是中间这8位数值。

我们可用MID函数从身份证号中巧用此8位出生年月,而不用一一输入,即保证了数值的准确性、与身份证号的一致性,且简单快捷。

假定,已在F列F3单元格以下输入了学生的身份证号,现要在D列D3以下输入学生的出生年月,只要选中D3单元格,在其中输入“=MID(F3,7,8)”,意为从F3单元格的字符串中从7位起向后共选取8位数值(含第7位)返回其值在D3单元格。然后敲回车键,即可在D3单元格产生8位出生年月数值。余下的工作就是用填充柄向下填充了。

也可用下式生成日期格式出生年月,且可在身份证号单元格为空时,不返回错误信息,而返回空值。

=IF(F3=“”,“”,MID(F3,7,4)&-MID(F3,9,2)&-MID(F3,11,2))

不论哪一种输入法,输入男、女性别,都是多次敲击键盘的重复录入。有没有只需敲击一次键盘即可输入性别的方法呢?

IF函数巧用可成就性别快速输入。

IF函数执行真假值判断,根据逻辑计算的真假值,返回不同结果。

语法:IF(logical_test,value_if_true,value_if_false)

Logical_test:表示计算结果为 TRUE 或FALSE的任意值或表达式。例如,A10=100就是一个逻辑表达式,如果单元格A10中的值等于100,表达式即为TRUE,否则为FALSE。本参数可使用任何比较运算符。

Value_if_true:logical_test为TRUE时返回的值。例如,如果本参数为文本字符串“男”而且logical_test参数值为 TRUE,则IF函数将显示文本“男”。如果logical_test为TRUE而value_if_true为空,则本参数返回0(零)。如果要显示TRUE,则请为本参数使用逻辑值TRUE。Value_if_true也可以是其他公式。

Value_if_false:logical_test为 FALSE 时返回的值。例如,如果本参数为文本字符串“女”而且logical_test参数值为 FALSE,则IF函数将显示文本“女”。如果 logical_test为FALSE 且忽略了Value_if_false(即value_if_true后没有逗号),则会返回逻辑值FALSE。如果logical_test 为FALSE且Value_if_false为空(即 value_if_true后有逗号,并紧跟着右括号),则本参数返回0(零)。Value_if_false也可以是其他公式。

函数IF可以嵌套七层,用value_if_false及value_if_true参数可以构造复杂的检测条件。

假定:在B列B3开始输入姓名,C列C3开始输入性别。先在D列D3单元格输入“=IF(C3=0,“男”,“女”)”,然后用填充柄拖拽至输入完成的最后一行,会在D列看到性别全部为“男”。在B列输入姓名完成后,当该生性别为“男”时在C列输入“0”或不输入任何内容,当为该生性别“女”时,可在C列输入除“0”以外的任何一字符或数字,D列对应的性别会立即变为“女”,连输入法都不用切换。输入完成后,只要将D列性别“复制”并以“选择性粘贴”、“数值”粘贴在C列即告完成。

身份证号第十七位为单数表示“男”性,为偶数时表示“女性”。只要使用MID函数从身份证号中提取第17位数,再使用求余函数MOD对提取值进行求余运算,当余数为“1”时,说明是单数,返回函数值“男”,否则返回函数值“女”。假设从F3单元格及以下录入了身份证号,可用下列式在公式所在单元格自动产生性别。

=IF(F3=“”,“”,IF(MOD(MID(F3,17,1),2)=1,“男”,“女”)

Excel中函数功能极其强大,应用得当可以起到事半功倍的作用,是电子表格处理事务的强有力助手。函数应用的能力强弱,将直接影响到电子表格的应用效率。电子表格不仅仅是学生学籍电子管理、学生成绩管理的手段,同时也为我们提供了强大的开发应用价值,我们应在实践中不断学习和提高应用能力,发挥好这一工具的作用。

excel函数代码 篇4

(1)函数分解

LARGE函数返回数据集中第K个最大值。使用此函数可以根据相对标准来选择数值。

语法： LARGE(array,k)

Array为需要从中选择第K个最大值的数组或数据区域;K为返回值在数组或数据单元格区域中的位置(从大到小排)。

SMALL函数返回数据集中第K个最小值。使用此函数可以返回数据集中特定位置上的数值。

语法： SMALL(array,k)

Array为需要找到第K个最小值的数组或数字型数据区域;K为返回的数据在数组或数据区域里的位置(从小到大)。

(2)实例分析

假设C2：C688区域存放着员工的工资，首先在D列选取空白单元格D3，在其中输入公式“=LARGE(C2：C688,D2)”，

其中D2作为输入名次变量的单元格，如果你在其中输入3，公式就可以返回C2：C688区域中第三大的数值。

如果我们把上述公式修改为“=SMALL(C2：C688,D1)”，然后在D1单元格中输入6，就可以获得C2：C688区域倒数第六(小)的数值。

为方便起见，你可以给C2：C688区域定义一个名称“职工工资”。此后可以把上述公式修改为“=LARGE(职工工资,D2)”或“=SMALL(职工工资,D1)”。

flash AS3函数闭包代码 篇5

flash AS3函数闭包代码

。“闭包” 一词来源于以下两者的结合：要执行的代码块(由于自由变量的存在，相关变量引用没有释放)和为自由变量提供绑定的计算环境(作用域)。在 Scheme、Common Lisp、Smalltalk、Groovy、JavaScript、Ruby 和 Python 等语言中都能找到对闭包不同程度的支持。

闭包的价值在于可以作为函数对象或者匿名函数，对于类型系统而言这就意味着不仅要表示数据还要表示代码。支持闭包的多数语言都将函数作为第一级对象，就是说这些函数可以存储到变量中、作为参数传递给其他函数，最重要的是能够被函数动态地创建和返回。

package

{

importflash.display.Sprite;

/**

* ...

* @author ria.args.cn ...

*/

publicclassTestextends Sprite

{

private var array:Array = new Array;

public function Test()

{

for (var i:int = 0; i < 5; i++) {

var object:Object = new Object();

object.num = function():void {

this.number = i;

trace(this.number);

}

array.push(object);

}

}

public function run():void {

for (var i:int = 0; i < array.length; i++) {

array.num();

}

}

}

}

package

{

import flash.display.Sprite;

/**

* ...

* @author ria.args.cn...

*/

public class RunTestextends Sprite

{

public function RunTest()

{

var t:Test = new Test();

t.run();

}

}

}

先调用setup 方法，在 setup 方法的 for 语句中循环创建对象 obj，并为 obj 创建一个方法 num()，该方法将当前循环的 index 赋值给 obj 的一个属性 n，并 trace 出 n 的值，

将 for 循环生成的对象存到数组 array 中用于在 run 方法中取出。 调用 run 方法，取出 array 中的对象，并调用他们的 num() 方法。 猜猜输出的结果是什么?

结果是： 5 5 5 5 5 并不是期望中的(我期望中的)： 0 1 2 3 4

为什么呢? 研究并实验了几下才发现，原来函数闭包虽然可以记录上下文环境的 snapshot ，但却是最近状态的一个 snapshot ，比如上例在 for 循环中，虽然把循环的当前的 i 值赋给了 n ，然而 i 是属于 num() 函数之外的，是 snapshot 中的变量，所以它只记录最近的状态，也就是 for 循环的最后一次，i 等于 5。输出的就都是 5 了。按照这个原理，你也可以实验一下，把 i 的最后状态改成其他的值，比如

public function setup():void

{

for(var i:int=0;i<5;i++)

{

var obj:Object = {}

obj.num = function():void

{

this.n = i

trace(this.n)

}

array.push(obj)

}

i = 100

}

虽然在 for 循环结束之后才设置的值，不过 i 依然在那个 snapshot 的范围之内，所以输出将会是 5 个 100 。

既然知道了原理，问题解决起来就容易了。只要让 i 不在函数闭包的上下文环境的 snapshot 的范围里就好了。可以这样改写一下：

public var array:Array = []

public function setup():void{

for(var i:int=0;i<5;i++)

{

array.push(createObj(i))

}

}

public function run():void

{

for(var j:int=0;j<5;j++)

{

array[j].num()

}

}

public function createObj(index:int):Object

{

var obj:Object = {}

obj.num = function():void

{

this.n = index

trace(this.n)

}

return obj

}

Excel常用Excel函数 篇6

一、与求和有关的函数的应用

SUM函数是Excel中使用最多的函数，利用它进行求和运算可以忽略存有文本、空格等数据的单元格，语法简单、使用方便。相信这也是大家最先学会使用的Excel函数之一。但是实际上，Excel所提供的求和函数不仅仅只有SUM一种，还包括SUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2几种函数。

这里笔者将以某单位工资表为例重点介绍SUM（计算一组参数之和）、SUMIF（对满足某一条件的单元格区域求和）的使用。(说明：为力求简单，示例中忽略税金的计算。)SUM

1、行或列求和

以最常见的工资表（如上图）为例，它的特点是需要对行或列内的若干单元格求和。比如，求该单位2001年5月的实际发放工资总额，就可以在H13中输入公式： =SUM(H3:H12)

2、区域求和

区域求和常用于对一张工作表中的所有数据求总计。此时你可以让单元格指针停留在存放结果的单元格，然后在Excel编辑栏输入公式“=SUM（）”，用鼠标在括号中间单击，最后拖过需要求和的所有单元格。若这些单元格是不连续的，可以按住Ctrl键分别拖过它们。对于需要减去的单元格，则可以按住Ctrl键逐个选中它们，然后用手工在公式引用的单元格前加上负号。当然你也可以用公式选项板完成上述工作，不过对于SUM函数来说手工还是来的快一些。比如，H13的公式还可以写成： =SUM(D3:D12,F3:F12)-SUM(G3:G12)

3、注意

SUM函数中的参数，即被求和的单元格或单元格区域不能超过30个。换句话说，SUM函数括号中出现的分隔符（逗号）不能多于29个，否则Excel就会提示参数太多。对需要参与求和的某个常数，可用“=SUM（单元格区域，常数）”的形式直接引用，一般不必绝对引用存放该常数的单元格。SUMIF

SUMIF函数可对满足某一条件的单元格区域求和，该条件可以是数值、文本或表达式，可以应用在人事、工资和成绩统计中。

仍以上图为例，在工资表中需要分别计算各个科室的工资发放情况。要计算销售部2001年5月加班费情况。则在F15种输入公式为 =SUMIF($C$3:$C$12,“销售部”,$F$3:$F$12)其中“$C$3:$C$12”为提供逻辑判断依据的单元格区域，“销售部”为判断条件即只统计$C$3:$C$12区域中部门为“销售部”的单元格，$F$3:$F$12为实际求和的单元格区域。

二、常见数学函数使用技巧--四舍五入

在实际工作的数学运算中，特别是财务计算中常常遇到四舍五入的问题。虽然，excel的单元格格式中允许你定义小数位数，但是在实际操作中，我们发现，其实数字本身并没有真正的四舍五入，只是显示结果似乎四舍五入了。如果采用这种四舍五入方法的话，在财务运算中常常会出现几分钱的误差，而这是财务运算不允许的。那是否有简单可行的方法来进行真正的四舍五入呢？其实，Excel已经提供这方面的函数了，这就是ROUND函数，它可以返回某个数字按指定位数舍入后的数字。

在Excel提供的“数学与三角函数”中提供了一个名为ROUND(number,num_digits)的函数，它的功能就是根据指定的位数，将数字四舍五入。这个函数有两个参数，分别是number和num_digits。其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。如图3所示：

单元格B2中为初始数据0.123456，B3的初始数据为0.234567，将要对它们进行四舍五入。在单元格C2中输入“=ROUND(B2,2)”，小数点后保留两位有效数字，得到0.12、0.23。在单元格D2中输入“=ROUND(B2,4)”，则小数点保留四位有效数字，得到0.1235、0.2346。

对于数字进行四舍五入,还可以使用INT（取整函数），但由于这个函数的定义是返回实数舍入后的整数值。因此，用INT函数进行四舍五入还是需要一些技巧的，也就是要加上0.5，才能达到取整的目的。仍然以图3为例，如果采用INT函数，则C2公式应写成：“=INT(B2*100+0.5)/100”。

最后需要说明的是：本文所有公式均在Excel97和Excel2000中验证通过，修改其中的单元格引用和逻辑条件值，可用于相似的其他场合。附注：Excel的数学和三角函数一览表

ABS 工作表函数 返回参数的绝对值 ACOS 工作表函数 返回数字的反余弦值 ACOSH 工作表函数 返回参数的反双曲余弦值 ASIN 工作表函数 返回参数的反正弦值 ASINH 工作表函数 返回参数的反双曲正弦值 ATAN 工作表函数 返回参数的反正切值 ATAN2 工作表函数

返回给定的 X 及 Y 坐标值的反正切值 ATANH 工作表函数 返回参数的反双曲正切值 CEILING 工作表函数

将参数 Number 沿绝对值增大的方向，舍入为最接近的整数或基数 COMBIN 工作表函数

计算从给定数目的对象集合中提取若干对象的组合数 COS 工作表函数 返回给定角度的余弦值 COSH 工作表函数 返回参数的双曲余弦值 COUNTIF 工作表函数

计算给定区域内满足特定条件的单元格的数目 DEGREES 工作表函数 将弧度转换为度 EVEN 工作表函数

返回沿绝对值增大方向取整后最接近的偶数 EXP 工作表函数

返回 e 的 n 次幂常数 e 等于 2.7***04，是自然对数的底数 FACT 工作表函数

返回数的阶乘，一个数的阶乘等于 1*2*3*...*该数 FACTDOUBLE 工作表函数 返回参数 Number 的半阶乘 FLOOR 工作表函数

将参数 Number 沿绝对值减小的方向去尾舍入，使其等于最接近的 significance 的倍数 GCD 工作表函数

返回两个或多个整数的最大公约数 INT 工作表函数 返回实数舍入后的整数值 LCM 工作表函数 返回整数的最小公倍数 LN 工作表函数

返回一个数的自然对数自然对数以常数项 e（2.7***04）为底 LOG 工作表函数

按所指定的底数，返回一个数的对数 LOG10 工作表函数 返回以 10 为底的对数 MDETERM 工作表函数 返回一个数组的矩阵行列式的值 MINVERSE 工作表函数 返回数组矩阵的逆距阵 MMULT 工作表函数 返回两数组的矩阵乘积结果 MOD 工作表函数

返回两数相除的余数结果的正负号与除数相同 MROUND 工作表函数

返回参数按指定基数舍入后的数值 MULTINOMIAL 工作表函数

返回参数和的阶乘与各参数阶乘乘积的比值 ODD 工作表函数

返回对指定数值进行舍入后的奇数 PI 工作表函数

返回数字 3.14***9，即数学常数 pi，精确到小数点后 15 位 POWER 工作表函数 返回给定数字的乘幂 PRODUCT 工作表函数

将所有以参数形式给出的数字相乘，并返回乘积值 QUOTIENT 工作表函数

回商的整数部分，该函数可用于舍掉商的小数部分 RADIANS 工作表函数 将角度转换为弧度 RAND 工作表函数

返回大于等于 0 小于 1 的均匀分布随机数 RANDBETWEEN 工作表函数 返回位于两个指定数之间的一个随机数 ROMAN 工作表函数

将阿拉伯数字转换为文本形式的罗马数字 ROUND 工作表函数

返回某个数字按指定位数舍入后的数字 ROUNDDOWN 工作表函数

靠近零值，向下（绝对值减小的方向）舍入数字 ROUNDUP 工作表函数

远离零值，向上（绝对值增大的方向）舍入数字 SERIESSUM 工作表函数 返回基于以下公式的幂级数之和： SIGN 工作表函数

返回数字的符号当数字为正数时返回 1，为零时返回 0，为负数时返回-1 SIN 工作表函数 返回给定角度的正弦值 SINH 工作表函数 返回某一数字的双曲正弦值 SQRT 工作表函数 返回正平方根 SQRTPI 工作表函数

返回某数与 pi 的乘积的平方根 SUBTOTAL 工作表函数

返回数据清单或数据库中的分类汇总 SUM 工作表函数

返回某一单元格区域中所有数字之和 SUMIF 工作表函数

根据指定条件对若干单元格求和 SUMPRODUCT 工作表函数

在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 SUMSQ 工作表函数 返回所有参数的平方和 SUMX2MY2 工作表函数

返回两数组中对应数值的平方差之和 SUMX2PY2 工作表函数

返回两数组中对应数值的平方和之和，平方和加总在统计计算中经常使用 SUMXMY2 工作表函数

返回两数组中对应数值之差的平方和 TAN 工作表函数 返回给定角度的正切值 TANH 工作表函数 返回某一数字的双曲正切值 TRUNC 工作表函数

将数字的小数部分截去，返回整数

excel函数代码 篇7

关键词：excel,逻辑,逻辑函数,信息技术

Excel2010是办公软件office2010的一个小套件, 主要用于各种计算。它内置有大量的计算公式, 给用户计算提供了很大方便。其中, 逻辑函数是计算公式的一个类型, 作用是帮助用户判断事物。它包括AND、FALSE、IF、IFERROR、NOT、OR、TRUE七个函数。这些函数是实现办公自动化的重要工具。这些函数的应用, 能够给我们的工作带来很大方便, 收到事半功倍的效果。下面分别介绍其中四个重要的函数。

1 AND函数

这个函数的作用是检查一批参数是否全真。如果被检查的若干个参数全部是正确的, 那么反馈的结果为“正确”;如果其中有一个参数是错误的, 那么反馈的结果为“错误”。

1.1 任务

检验一批电子产品。这批电子产品, 有一段电路串联着A、B、C三个电子元件。现在, 检查每个电子元件的导电情况, 判断这段电路是否通电。

1.2 分析

这段电路通电, 必须是三个电子元件都通电。否则, 这段电路不能够通电。这与AND函数的功能一致, 因此, 可以使用AND函数解决这一问题。

1.3 思路

我们可以在单元格A1、B1、C1分别输入“通电”二字。在D1、E1、F1里分别输入电子元件A、B、C的监测结果。通电, 则输入“通电”二字;不通电, 则输入“不通电”三字。如果A1=D1, B1=E1, C1=F1, 出现三个真命题, 那么AND函数的判断结果为“正确”, 即通电。如果其中有一组单元格里的参数不对等, 那么AND函数判断的结果为“错误”, 即不通电。我们这里所说的“通电”, 计算机反馈的是“TRUE”, “不通电”计算机反馈的是“FALSE”。

类似地, 在A、B、C列的其他单元格全部输入“通电”, D、E、F列的第二行输入第二个电子产品电子元件的检测情况, 第三列输入第三个电子产品电子元件的检测情况, 以此类推, 输入所有电子产品电子元件的检测情况。用复制公式的方法, 把G1中的判断公式粘贴到G列的其他行。这样, G列的其他单元格就会显示出其他电子产品的通电情况。

1.4 操作

(1) 设置预设答案

在单元格A1、B1、C1里输入汉字“通电”。

(2) 输入检测情况

在单元格D1、E1、F1里分别输入电子元件A、B、C的导电情况。

(3) 输入判断公式

在G1里显示这段电路的通电情况。通电, 自动显示“TRUE”, 不通电, 自动显示“FALSE”。

单击单元格G1, 选“公式”菜单, 单击“逻辑”按钮下的AND, 打开AND函数对话框。

在logical1里输入A1=D1 (可以单击单元格A1, 敲击键盘上的“=”号, 再单击D1.) , logical2里输入B1=E1, logical3里输入C1=F1, 单击“确定”按钮。

(4) 判断第一个电子产品的通电情况

在单元格D1、E1、F1里分别输入电子元件A、B、C的检测情况, 敲击“回车”键。在G1里可以看到这段电路的通电情况。

若D1、E1、F1里输入的都是“通电”, 则G1里显示TRUE, 即通电, 否则显示FALSE, 即不通电。

(5) 判断其他电子产品的通电情况

同时选中A1、B1、C1, 向下拖动选区右下角的控制柄, 把该选区的内容粘贴到其他行。选中G1, 向下拖动其右下角的控制柄, 把G1里的判断公式粘贴到G列的其他单元格。这样, 在D2、E2、F2里输入第二个电子产品中三个串联电子元件检测情况, 就可以在G2里看到第二个电子产品的通电情况。在D、E、F列输入其他电子产品三个电子元件的检测情况, 在G列相应的单元格里, 可以看到这些电子产品的通电情况。

如果每一电子产品的预设答案都是一样的, 那么可以不在电子表格里输入预设答案, 而是把预设答案直接输入到AND函数的对话框里。

另外, 这里所说的参数与我们通常见到的参数不完全一样。通常说的参数是数, 可以是常数, 也可以是变数。这里所说的参数, 不仅仅是数, 而且可以是量、数量关系、单元格等, 包括一切表示条件的符号和关系。

使用AND函数的流程:单击插入函数的单元格, 单击“公式”菜单, 单击“逻辑”按钮, 选择“AND”函数, 在对话框里输入条件参数。

2 OR函数

这个函数的功能是检查一组参数是否同假。如果被检测的若干个参数都是假的, 那么反馈的结果是假的;如果有一个参数是真的, 那么反馈的结果就是真的。需要判断事物是否全假的例子在生活中有很多。

2.1 任务

期末考试结束, 为了帮助学习成绩不好的学生查找原因, 鼓励他们努力学习, 教师决定找数学、语文、舞蹈三科成绩都在60分以下的学生座谈。应该怎样使用计算机确定这些学生呢?

2.2 分析

一个学生的数学、语文、舞蹈成绩是一组参数。设不及格事件为假 (FALSE) , 及格事件为真 (TRUE) 。那么这个任务就是求数学、语文、舞蹈成绩全部为假的人。

2.3 思路

要找出数学、语文、舞蹈考试成绩都低于60分的人, 就需要把这些人的姓名和考试成绩输入计算机。为了便于识别三科成绩都不及格的学生, 还要有一列显示判断的结果。因此, 不妨从第3行开始, 在A列输入学生的序列号, B列输入学生的姓名, C列输入数学成绩, D列输入语文成绩, E列输入舞蹈成绩, F列显示判断结果。F列是计算机自动判断的结果, 如果需要座谈, 则显示FALSE;如果不需要座谈, 则显示TRUE。

2.4 操作

(1) 输入原始数据

合并单元格A1:F1, 并在此输入标题“考试成绩分类表”。在A2里输入“序列号”, B2里输入“姓名”, C2里输入“数学”, D2里输入“语文”, E2里输入“舞蹈”, F2里输入“分类”。输入每个学生的序列号、姓名和考试成绩。如图1, 在“分类”这一列, 放置计算机根据学生考试成绩判断的结果。

(2) 判断第一个人

单击F3, 确定判断结果显示的位置, 选“公式”菜单, 单击“逻辑”按钮, 选“OR”函数, 打开OR函数对话框, 单击logical1的后面, 单击C3, 键入“>=60”;单击logical2的后面, 单击D3, 键入“>=60”;单击logical3的后面, 单击E3, 键入“>=60”, 单击“确定”按钮。如图2。

(3) 复制公式

单击F3, 向下拖动F3右下角的控制柄, 得到判断结果。如图3。

F列的符号有两类:TRUE和FALSE。其中, TRUE表示至少有一科及格, FALSE表示各科都不及格。

从这个实例看出, 使用AND函数, 可以做“至少”这一类判断。操作的流程一般分三步:输入原始数据, 判断第一组数据, 复制第一个判断。关键是“判断第一组数据”, 这一步又可以分为几小步进行:确定判断结果存放的位置, 打开OR函数对话框, 输入参数, 单击“确定”。

3 IF函数

这个函数的功能是做“如果……, 那么……, 否则……”这一类判断。

3.1 任务

设计一个计算机自动改卷系统。试卷由20道判断题、20道单选题、20道多选题、20道问答题组成, 其中第一道判断题是“4的平方根是2”。答对记1分, 打错记0分。认为此命题真, 就在题目后面的单元格里输入“对”字, 否则输入“错”字。要求, 学生输入答案后, 计算机要对学生的回答做出判断, 并显示得分情况。

3.2 分析

就第一道判断题而言, 可以修改为“如果回答正确, 那么记1分, 否则记0分”。这符合IF函数的要求。因此, 可以使用IF函数完成这一任务。

3.3 思路

这项任务涉及到判断题的序列号、判断题、学生的判断、教师的评判和计分。教师是先对学生的回答进行评判, 而后才给分。评判和给分是一致的, 根据给分的情况, 可以看出教师的评判。所以, 在设计考试系统的时候, 可以不考虑教师的评判, 只考虑记分。我们可以在第1行输入项目名称, 从第2行起, 在A列输入题目的序列号, B列输入判断题, C列留给学生填写答案, D列显示得分情况。

判断学生的回答是否正确, 其实, 就是将学生的答案与标准答案做比较。学生的答案与标准答案一致, 给1分, 否则计0分。所以, 还需要有一列输入标准答案。不妨把标准答案放到E列。D列和E列, 设计完成之后, 都要隐藏的。不然, 学生会根据显示的分数判断自己是否作对, 而重新做题。

因为每一道题目的给分都是一样的, 所以可以对第一题进行判断, 再把它的判断公式复制到其他题。

4的平方根有两个, 即2和-2。所以, “4的平方根是2”的说法是错误的。

3.4 操作

(1) 输入试题和标准答案

输入的结果如图4。

(2) 对第一道题做出判断

D2单元格———“公式”菜单———“逻辑”按钮———IF函数, 打开IF函数对话框, 在logical-test后面输入“C2=E2”, value-iftrue后面输入“1”, value-if-false后面输入“0”, 点“确定”按钮。

(3) 复制第一道题目的判断题公式

选中D2, 向下拖动单元格D2右下角的控制柄。

(4) 隐藏标准答案和得分列

选中D列和E列, 在选区击右键, 选择“隐藏”命令。

学生只要在C列输入答案并确认, 计算机便根据设定的要求, 做出判断, 并把得分情况写进E列。教师要查看学生得分情况, 只需要取消这两列的隐藏就可以了。

另外, 还可以使用求和函数, 统计整个试卷的得分。

4 NOT函数

这是一个对参数求反的函数。如果提供的参数是真 (TRUE) 的, 那么计算的结果是假 (FALSE) 的。反过来, 如果提供的参数是假的, 那么计算的结果是真的。计算的结果只有两种情况, 这样就把被计算的事物划分成了两个类型。从表面上看, 这个函数是判断命题的真假, 其实是对一批参数进行分类。所以, 我们既可以把计算的结果看成是命题的真假, 也可以把计算的结果看成类别符号。

例如, 2013年国家对民办教师进行生活补贴。某市委为了预算将要投入的资金, 责令民调队对民办教师的数量、任职时间等与民办教师有关的信息进行调研。调研结果表明:该市大约有2万名民办教师, 平均任职年限为8.3年。然而, 当市委领导看了教育局报上来的统计数据, 大吃一惊。全市竟然有10万多民办教师, 平均任教年限为15.6年。而且, 都有证人证言。怎样才能把虚报瞒报的人找出来呢?

有人想了个主意, 计算表上民办教师的任教年龄。文件要求, 补贴文化革命以前的民办教师。文化革命以前, 教育部有文件规定:儿童8岁入学, 小学的学制为6年。小学毕业的最低年龄是14岁。作为教师, 最低应该是小学毕业。参加教育工作时的年龄低于14岁的, 一定有假。所以, 只要计算出报表上任教的年龄, 就可以剔除一部分虚报瞒报人。

4.1 任务

从民办教师统计表中找出虚报瞒报任职年岁的人。

4.2 分析

找虚报瞒报的人, 其实就是把民办教师统计表中的人分为两个类别, 任教时的岁数小于14的和大于等于14的。

在民办教师统计表上, 没有教师任教时的岁数, 但有民办教师出生年份和开始任民办教师的年份。如下表所示:

我们可以根据“开始任教年份”与“出生年份”的差是否小于14, 把民办教师分为两类。小于14, 计算结果为FALSE, 大于等于14计算结果为TRUE。并把使用逻辑函数NOT计算的结果写进D列相应的单元格。我们根据D列的参数, 就可以知道谁在弄虚作假。

4.3 思路

可以先在D1输入“分类”, 在D2单元格使用NOT函数计算第一个人任教年龄的真假。再用拖放控制柄的方法, 计算其他人任教年龄的真假。假设“开始任教年份-出生年份<14”为真命题, “开始任教年份-出生年份>=14”为假命题。函数NOT的参数设为“开始任教年份-出生年份<14”, 则在D列显示FALSE (假) 。

最后, 使用IF函数, 把“FALSE”转化成“假”写进E列。

4.4 操作

单击D1, 输入“分类”, 单击D2选“公式”菜单单击“逻辑”按钮选“NOT”函数, 打开NOT函数对话框, 在logical后面输入“C3-B3<14”, 单击“确定”按钮。如图5。

单击D2, 向下拖动D2右下角的控制点。使用IF函数把TRUE转化为空, FLASE转化为“虚假”, 并把计算的结果填写在E列。得到结果如下表所示:

这是一个真实案例。为了维护公民的人身权益, 在引用这个实例的时候, 只截取了某市“原民办教师发放补贴名单”的一小段, 而且修改了教师的姓名。

使用这个函数大致分两大步: (1) 制作第一个人的判断; (2) 复制第一个人的判断公式到其他人。

制作第一个人的判断分三步: (1) 确定判断结果显示的位置; (2) 打开NOT函数对话框; (3) 输入判断条件, 并予以确认。

使用逻辑函数判断一组参数, 并不一定比人脑快。但是, 使用它判断千万组参数, 其速度比人快得多, 而且不会出现因人脑疲乏做出错误判断的现象。

作者简介:

excel函数代码 篇8

1VLOOKUP函数功能简介

在Excel中,用户可借助查找与引用类函数来查找数据,或者动态地返回对某些单元格区域的引用,其中功能强大的VLOOKUP函数使用频率相当高。该函数的基本功能是在查找范围中的首列搜索满足条件的数据,并由此返回查找范围当前行中指定列处的数值,其语法结构为VLOOKUP(lookup_value,table_array,col_index_num,range_lookup),各参数含义如下:

(1)lookup_value查找值,可以是数值、引用或字符串,支持使用通配符“?”和“*”进行查询,但不支持使用数组作为参数来生成内存数组。

(2)table_array查找范围,可以是单元格引用区域或区域名称(如数据库或数据清单),也可以是数组。

(3)col_index_num查找范围中待返回的匹配值的列号。如该参数值为1,则返回查找范围中第1列对应的数值;如该参数值为2,则返回查找范围中第2列对应的数值,依次类推。若该参数值小于1,则返回错误值“#VALUE!”;若该参数值大于查找范围的列数,则返回错误值“#REF!”。

(4)range_lookup逻辑值,决定函数的查找方式,指明返回时是精确匹配还是近似匹配。若该参数值为TRUE或1或省略,则返回近似匹配值,即在查找不到精确匹配值的情况下,返回小于查找值的最大数值;若该参数值为FALSE或0,则返回精确匹配值,并支持无序查找;若查找不到满足条件的数据,则返回错误值“#N/A”。

2利用VLOOKUP函数处理集装箱货运数据

利用VLOOKUP函数可以轻松、高效地对集装箱货运数据进行批量处理,而且数据越多越能体现其优势,下文举例说明。

2.1装船前核对场地箱

在集装箱集港后和装船前,可能会出现堆场实际接收的集装箱与预配舱单数据不符的情况:(1)预配舱单已生成某个集装箱的货运数据,但货主未能在规定的集港时间内将该箱运进堆场,即“有据无箱”;(2)虽然预配舱单并未生成某个集装箱的货运数据,但货主误将该箱运进堆场,即“有箱无据”。为避免发生溢短装,理货公司必须在装船前向船公司提供场地箱清单(见图1),以便与预配清单(见图2)进行核对。

2.1.1查找“有箱无据”的集装箱箱号

在“Book1_场地箱清单”的D2单元格中输入公式“=VLOOKUP(B2,[Book2_预配清单.xls]Sheet1!$B$2:$B$11,1,0)”,其中:输入第1个参数时,可直接选中B2单元格作为查找值;输入第2个参数时,先切换至“Book2_预配清单”,然后选中该表B2至B11单元格区域(图2蓝色方框区域)作为查找范围;输入第3个参数值1,表示返回查找范围中的第1列;输入第4个参数值0,表示无序且精确查找。各参数之间以半角逗号隔开,输入完毕后按回车键。在D3至D11单元格中输入公式时,只需向下拖动D2单元格右下角的填充柄即可,公式对左侧单元格地址的相对引用会随之变化。查找结果如下:“Book1_场地箱清单”的D列相应位置显示箱号,表明在“Book2_预配清单”中查找到该箱号;“Book1_场地箱清单”的D列相应位置显示错误值“#N/A”,表明在“Book2_预配清单”中未查找到该箱号,属于“有箱无据”的情况,应由船公司追加舱单数据或由货主办理退关手续。

2.1.2查找“有据无箱”的集装箱箱号

在“Book2_预配清单”的D2单元格中输入公式“=VLOOKUP(B2,[Book1_场地箱清单.xls]Sheet1!$B$2:$B$11,1,0)”,其中:输入第2个参数时,先切换至“Book1_场地箱清单”,然后选中该表B2至B11单元格区域(图1红色方框区域)作为查找范围。查找结果如下:“Book2_预配清单”的D列相应位置显示箱号,表明在“Book1_场地箱清单”中查找到该箱号;“Book2_预配清单”的D列相应位置显示错误值“#N/A”,表明在“Book1_场地箱清单”中未查找到该箱号,属于“有据无箱”的情况,应由船公司删除舱单数据或通知货主集港。

2.2装船后清理退关箱

理货公司在集装箱装船后向船公司提供退关清单(见图3),船公司根据退关清单将退关箱从预配清单(见图4)中删除。在“Book4_预配清单”的D2单元格中输入公式“=VLOOKUP(B2,[Book3_退关清单.xls]Sheet1!$B$2:$D$4,3,0)”,其中:输入第2个参数时,先切换至“Book3_退关清单”,然后选中该表B2至D4单元格区域(图3红色方框区域)作为查找范围;输入第3个参数值3表示返回查找范围中的第3列,即“退关原因”。查找结果如下:“Book4_预配清单”的D列相应位置显示“#N/A”,表明在“Book3_退关清单”中未查找到该箱号;“Book4_预配清单”的D列相应位置显示“无舱单”,表明在“Book3_退关清单”中查找到该箱号,该箱因无舱单而退关,应从预配清单中将该箱数据删除,最终生成实载清单。

2.3输入集装箱跟踪数据

假设集装箱管理人在港口铁路货运站存有若干空箱,需要通过铁路运输分批发往内陆交付用箱人使用。每批空箱装车发运后,铁路运输代理人向集装箱管理人提供集装箱发运清单(见图5),其中包括发运日期、发站、到站、用箱人等信息,集装箱管理人需要在集装箱跟踪表(见图6)中查找这些箱号,并输入各箱号对应的有关信息,方法如下:

(1)在“Book6_集装箱跟踪表”的D2单元格中输入公式“=VLOOKUP(B2,[Book5_集装箱发运清单.xls]Sheet1!$B$2:$G$8,3,0)”,其中,输入第2个参数时,先切换至“Book5_集装箱发运清单”,然后选中该表B2至G8单元格区域(图5红色方框区域)作为查找范围。

(2)在E2,F2和G2等3个单元格中分别输入以上公式,并将第3个参数分别改为4,5和6。

(3)第2行的公式输入完成后,选中D2至G2单元格区域,向下拖动该区域右下角的填充柄,即可实现第3~11行公式的输入。

(4)为解除“Book6_集装箱跟踪表”数据与“Book5_集装箱发运清单”数据的关联(如果保持关联,当后表被删除、存储路径改变或其中的数据发生变化时,前表中的数据就会缺失或改变),必须将输入的数据数值化,方法如下:使“Book6_集装箱跟踪表”中的数据处于全部显示状态(如果数据处于筛选状态,则单击菜单栏下的“数据”项,选择“筛选”项下的“全部显示”项);用鼠标单击左上角的选定器,选中整张工作表并复制;选中A1单元格,依次单击鼠标右键→“选择性粘贴”→“数值”→“确定”;利用“查找和替换”(Ctrl+F)功能批量删除表中显示的错误值“#N/A”,并保存工作簿。

3注意要点

(1)如果查找值在查找范围的首列为重复出现的值,则查找和引用的结果只返回位置在前的值所对应的数据。

(2)如果查找值和查找范围位于同一工作簿的不同工作表中,则系统默认圈选的查找范围为相对引用(如B2:G8);因此,在用复制或拖曳填充柄的方法输入公式时,必须将其改为绝对引用(如$B$2:$G$8),否则会出错。

(3)必须确保箱号准确无误。由于箱号的采集、传输和处理往往要经过抄录、誊写和计算机输入等诸多环节,错漏之处在所难免,必须在使用前对其进行校验和更正。

笔者在本刊2010年第1期(总第222期)发表的《巧用Excel函数校验集装箱箱号》一文中介绍了利用Excel嵌套函数校验箱号的方法,但语句较为复杂,且存在不完善之处。本文对原公式进行以下改进和优化:用较少的数组参数替代原先较多的数值参数;摒弃通过转换ANSI代码获取箱主代码等效数值的繁琐方法;支持箱主代码为小写字母;针对箱主代码第4位必须为“U”以及箱号代码必须为11位等要求设置检验条件,使语句更为精简、严谨。如图7所示,欲校验B2单元格中箱号的正误,只要在A2单元格中输入左框中的公式即可。