函数使用技巧自我总结超实用.docx

函数使用技巧自我总结超实用.docx

《函数使用技巧自我总结超实用.docx》由会员分享，可在线阅读，更多相关《函数使用技巧自我总结超实用.docx（8页珍藏版）》请在冰豆网上搜索。

函数使用技巧自我总结超实用

函数使用技巧

1选中数据函数：

首先在EXCEL的空白格内输入“=”，然后选中要选取的数据，按ENTER即可。

2以下图为例：

1年龄计算函数：

以宋欢为例，应选中E4格，输入“=DATEDIF（C4,D4,”Y”）

2天数计算函数：

以宋欢为例，应选中I4格，输入“=DATEDIF（H4,D4,”D”）

3年资计算函数：

以宋欢为例，应选中K4格，输入“=ROUND（I4/365,2）

.以下图为例：

1如果计算宋欢的个人养老保险，应选中F5格，输入=ROUND（E5*0.08,1）

.以下图为例

1如果计算缴费基数总和：

输入=SUM（E4:

E174）即可，其他依次类推。

5.VLOOKUP函数的应用

语法

VLOOKUP（lookup_value,table_array,col_index_num,range_lookup）

Lookup_value为需要在表格数组（数组：

用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。

数组区域共用一个公式；数组常量是用作参数的一组常量。

）第一列中查找的数值。

Lookup_value可以为数值或引用。

若lookup_value小于table_array第一列中的最小值，VLOOKUP将返回错误值#N/A。

Table_array为两列或多列数据。

请使用对区域的引用或区域名称。

table_array第一列中的值是由lookup_value搜索的值。

这些值可以是文本、数字或逻辑值。

不区分大小写。

Col_index_num为table_array中待返回的匹配值的列序号。

Col_index_num为1时，返回table_array第一列中的数值；col_index_num为2，返回table_array第二列中的数值，以此类推。

如果col_index_num：

小于1，VLOOKUP返回错误值#VALUE!

。

大于table_array的列数，VLOOKUP返回错误值#REF!

。

Range_lookup为逻辑值，指定希望VLOOKUP查找精确的匹配值还是近似匹配值：

如果为TRUE或省略，则返回精确匹配值或近似匹配值。

也就是说，如果找不到精确匹配值，则返回小于lookup_value的最大数值。

table_array第一列中的值必须以升序排序；否则VLOOKUP可能无法返回正确的值。

可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。

有关详细信息，请参阅默认排序次序。

如果为FALSE，VLOOKUP将只寻找精确匹配值。

在此情况下，table_array第一列的值不需要排序。

如果table_array第一列中有两个或多个值与lookup_value匹配，则使用第一个找到的值。

如果找不到精确匹配值，则返回错误值#N/A。

跨表查询：

首先，选定要查询的数据所在的行列，接着选中引用表的数据，所选引用表第一列必须和查询数据一样，然后输入引用数据所在列数，接着输入逻辑值0，按ENTER键，选中数据下拉就行了。

二．VLOOKUP的错误值处理。

　　我们都知道，如果找不到数据，函数总会传回一个这样的错误值#N/A，这错误值其实也很有用的。

比方说，如果我们想这样来作处理：

如果找到的话，就传回相应的值，如果找不到的话，我就自动设定它的值等于0，那函数就可以写成这样：

　　=if（iserror（vlookup（1,2,3,0））,0,vlookup（1,2,3,0））

　　这句话的意思是这样的：

如果VLOOKUP函数返回的值是个错误值的话（找不到数据），就等于0，否则，就等于VLOOKUP函数返回的值（即找到的相应的值）。

　　这里面又用了两个函数。

　　第一个是iserror函数。

它的语法是iserror（value），即判断括号内的值是否为错误值，如果是，就等于true，不是，就等于false。

　　第二个是if函数，这也是一个常用的函数的，后面有机会再跟大家详细讲解。

它的语法是if（条件判断式，结果1，结果2）。

如果条件判断式是对的，就执行结果1，否则就执行结果2。

举个例子：

=if（D2=””,”空的”,”有东西”），意思是如D2这个格子里是空的值，就显示文字“空的”，否则，就显示“有东西”。

（看起来简单吧？

其实编程序，也就是这样子判断来判断去的。

）

6.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可以是其他公式。

7、counta函数使用技巧

语法

COUNTA（value1,value2,...）

Value1,value2,...为所要计算的值，参数个数为1到30个。

在这种情况下，参数值可以是任何类型，它们可以包括空字符（""），但不包括空白单元格。

如果参数是数组或单元格引用，则数组或引用中的空白单元格将被忽略。

如果不需要统计逻辑值、文字或错误值，请使用函数COUNT。

此函数抓取文本型数据。

8、抓取数据函数：

在单元格内输入“=”，鼠标点击要抓取的数据单元格，，按enter键即可。

9、excel将文本转化为日期：

选中->数据->分列->下一步->下一步->列数据格式选择日期（YMD）->完成

10、SUBTOTAL函数，分类汇总后的求和函数

Function_numFunction_num函数

（包含隐藏值）（忽略隐藏值）

1101AVERAGE

2102COUNT

3103COUNTA

4104MAX

5105MIN

6106PRODUCT

7107STDEV

8108STDEVP

9109SUM

10110VAR

11111VARP

例如，subtotal（3，E2:

E10）表示筛选后E2到E10文本型数值求和！

11、多条件求和

SUMPRODUCT函数

？

“=SUMPRODUCT（（A2:

A17="郑州"）*（B2:

B17=2）*（C2:

C17））”,SUMPRODUCT（）是多条件求和函数，即同时满足几个条件的数据之和，其中（（A2:

A17="郑州"）表示第一个条件，即A2：

A17范围内，单元格数值为郑州；（B2:

B17=2）表示第二个条件，即B2：

B17单元格中数值为2；（C2:

C17）表示要求和的数值，即把C2：

C17范围内同时满足上述条件的数值加在一起。

注意各个条件之间要用*连接起来，*号相当于逻辑AND。

文本内容要用引号，数据可不用。

SUMIFS函数

对区域（区域:

工作表上的两个或多个单元格。

区域中的单元格可以相邻或不相邻。

）中满足多个条件的单元格求和。

例如，如果需要对区域A1:

A20中符合以下条件的单元格的数值求和：

B1:

B20中的相应数值大于零（0）且C1:

C20中的相应数值小于10，则可以使用以下公式：

=SUMIFS（A1:

A20,B1:

B20,">0",C1:

C20,"<10"）要点SUMIFS和SUMIF函数的参数顺序有所不同。

具体而言，sum_range参数在SUMIFS中是第一个参数，而在SUMIF中则是第三个参数。

如果要复制和编辑这些相似函数，请确保按正确的顺序放置参数

COUNTIFS函数用法

例如A列中是0-100随机的数字，

我们要统计A列中大于10小于30的数字的个数

B1输入公式=COUNTIFS（A:

A,">10",A:

A,"<30"）

就行了

也就是=COUNTIFS（区域，条件，区域，条件，区域，条件）

ceiling函数定义（进位取整）

　　将参数Number向上舍入（沿绝对值增大的方向）为最接近的significance的倍数。

例如，如果您不愿意使用像“分”这样的零钱，而所要购买的商品价格为$4.42，可以用公式=CEILING（4.42,0.1）将价格向上舍入为以“角”表示。

CEILING函数

语法：

　　CEILING（number,significance）

　　参数：

　　Number待舍入的数值。

　　Significance基数。

　　说明：

　　如果参数为非数值型，CEILING返回错误值#VALUE!

。

　　无论数字符号如何，都按远离0点方向舍入。

如果数字已经为Significance的倍数，则不进行舍入。

　　如果Number和Significance符号不同，CEILING返回错误值#NUM!

。

参考事例

　　示例：

　　CEILING（2.5,1）等于3

　　CEILING（-2.5,-2）等于-4

　　CEILING（-2.5,2）等于#NUM!

　　CEILING（1.5,0.1）等于1.5

CEILING（0.234,0.01）等于0.24

if和and/or函数的运用

=if（and（a1>=10,a1<20）,"合格","不合格"）

and就是"并且"的意思,如果A1大于等于10并且小于20则显示合格,否则显示不合格

=if（or（a1<10,a1>=20）,"不合格","合格"）

or就是"或者"的意思,如果A1小于10或者大于等于20则显示不合格,否则显示合格.