_Excel公式教程 (2016-07-23 22:58:35)
转载
▼
标签: excel公式教程 数据类型分类: Excel公式教程-原理篇
在Excel公式中,有些数据需要特定的数据类型,当类型不符合要求的时候,Excel可以把它自动转换为合适的数据类型;而有些数据却不需要特定的数据类型,当使用了不适合类型的数据时,公式的返回值可能不是你所期望的计算结果。
如何避免因数据类型不适合而造成公式的计算结果不正确呢?还是先从数据类型的分类开始说起吧。
一、分辨Excel公式中的数据类型。
在Excel公式中,数据分为五种数据类型:数字值、文本值、逻辑值、错误值和数组。其中前四种可称为单值,数组从形式上是多值,数组可以包括一个或多个单值。
1、算术运算符(+、-、*、/、%、^)的操作数要求是数字值。函数的语法中,number表示该参数需要一个数字值,例如:
ABS(number)
FIND(find_text,within_text,start_num) 其中的start_num表示是数字值
很多参数使用了有语义的英文单词,例如,DEGREES(angle),参数angle表示是一个角,这可以从理解该参数的词义来判断属于什么数值类型。
2、文本运算符(&)的操作数要求是文本值。函数语法中,text表示该参数需要一个文本值。例如:
LEN(text)
FIND(find_text,within_text,start_num) 其中的find_text和within_text表示是文本值
3、函数语法中,logical表示该参数需要一个逻辑值。例如:
IF(logical_test,value_if_true,value_if_false) 其中的logical_test表示是逻辑值
4、数组数值类型包括单元格引用和数组两种。引用运算符的操作数要求是单元格引用。函数的语法中,array表示该参数需要一个数组,reference表示该参数需要一个单元格引用。range表示该参数是一个单元格区域引用。例如:
ROWS(array) 参数array可以是任何形式的数组,包括数组常量、单元格区域、隐式数组。
ROW(reference) 参数reference必须是单元格引用,可以是单个单元格,也可以是单元格区域
COUNTBLANK(range) 参数range必须是单元格区域,不能是数组。
INDIRECT(ref_text,a1) 其中的ref_text表示是一个文本值,但该文本值要能被识别为一个引用
对于以下形式的参数,很多都可以改用数组参数:
SUM(number1,number2,…) 可改用数组参数SUM(array)
CONCATENATE(text1,text2,…) 可改用数组参数CONCATENATE(array)
二、使用比较运算符要注意数据类型对公式计算结果的影响。
比较运算符可以对不同类型的单值进行比较,不同类型单值的大小关系是:数字值
比如,单元格A1输入数字值2,A2输入公式="2"返回一个文本值。如果输入以下公式:
=A1>3 返回FALSE
而
=A2>3 返回TRUE,因为文本值总是大于数字值。
请注意EXACT函数与比较运算符的区别。EXACT函数的语法是:
EXACT(text1,text2)
EXACT函数的两个参数要求是文本值,如果不是文本值,则会自动转换为文本值后才进行比较;而比较运算符可以比较不同类型的数值,所以从不进行数值类型转换。
例如单元格A2为公式="2"返回的文本值(也可以有单元格A2中直接输入“'2”),如果输入公式:
=A2=2 返回FALSE,文本值与数字值是不相等的。
而
=EXACT(A2,2) 返回TRUE,第二个参数2被自动转换为文本值"2",然后同A2比较。
三、使用以value为参数的函数时,要注意数据类型对公式计算结果的影响。
函数的语法中,value表示该参数没有特定哪种类型。(value参数在大多数情况下表示一个单值,只有少数情况下可以是一个数组。)
IS类函数、N和T函数、TYPE函数都只有一个value参数,该参数可以是任何数值类型,Excel从来不会对这个参数进行数据类型转换。
再来看看VLOOKUP函数的语法:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
第一个参数lookup_value可以是数字值、文本值,甚至可以是逻辑值。如果lookup_value与table_array第一列的数值类型不一致,查找将失败。
HLOOKUP、LOOKUP、MATCH这几个查找函数,与VLOOKUP函数的情况相似,都要注意第一个参数与待匹配数据的数据类型一致。
要判断单元格是什么数据类型,可以用TYPE函数,例如输入公式=TYPE(A2),单元格A2如果是数字值或空单元格,则返回1,如果A2是文本值,则返回2,等等。
四、引用运算符的操作必须是单元格引用,如果数据类型不是单元格引用,公式将出错。
比如,在单元格输入公式=sum(c1:"c3"),Excel将提示公式有错而不予保存,因为区域运算符(:)后面的运算项"c3"是文本值。
如果运算项是其他表达式的运算结果,比如=SUM(C1:IF(C3>50,"C3