Excel 常用技巧.docx

Excel 常用技巧.docx

《Excel 常用技巧.docx》由会员分享，可在线阅读，更多相关《Excel 常用技巧.docx（12页珍藏版）》请在冰点文库上搜索。

Excel常用技巧

Excel常用技巧

1、选中整个活动工作簿2

2、复制公式2

3、数据有效性3

4、单元格条件格式3

5、单元格格式4

6、文本函数5

（1）截取从左边起N个字（LEFT）5

（2）截取从右边起N个字（RIGHT）5

（3）截取从中间第几位起N个字（MID）5

7、查找函数6

（1）纵向查找（HLOOKUP）6

（2）横向查找（VLOOKUP）6

（3）逆向查找7

8、排序函数（RANK）8

9、统计函数（SUMPRODUCT）9

10、数组公式10

1、选中整个活动工作簿

选中整个活动工作簿，单击工作簿左上角

2、复制公式

若同一列公式一样，复制公式时单击需要复制公式所在单元格，然后将鼠标移到单元格右下角，当鼠标变成黑十字时，双击鼠标左键，完成复制粘贴。

注意点：

复制公式时一定要注意区分绝对引用和相对引用

3、数据有效性

单元格下拉菜单可以通过“数据——有效性”设置序列及序列所在位置（一般放在辅助列，并隐藏本列数据，通过“格式——工作表——取消隐藏”可以显示已隐藏列）进行设置，如《纳税评估分析模型》“评估分析——基本参数设置”出口退税知识查询B12

4、单元格条件格式

若需单元格符合某些条件自动改变单元格设置，可以通过“格式——条件格式”进行设置，EXCEL2003最多可以设置3个条件，如《纳税评估分析模型》“评估分析——基本参数设置”评估项目建议B13

5、单元格格式

单元格格式一般将不允许编辑单元格（尤其是公式）“保护”设置为“锁定”、“隐藏”，允许编辑单元格则取消“锁定”前的“√”；若要真正隐藏单元格数据，需将“单元格格式——数值”设置为“自定义”，类型可以输入“；；；”三个分号，如《纳税评估分析模型》“评估分析——基本参数设置”E18

6、文本函数

（1）截取从左边起N个字（LEFT）

公式：

=LEFT（text，num_chars）。

参数：

Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。

实例：

如果A1=电脑爱好者，则LEFT（A1，2）返回“电脑”

（2）截取从右边起N个字（RIGHT）

公式：

=RIGHT（text，num_chars）。

参数：

Text是包含要提取字符的文本串；Num_chars指定函数要提取的字符数，它必须大于或等于0。

实例：

如果A1=电脑爱好者，则RIGHT（A1，3）返回“爱好者”

（3）截取从中间第几位起N个字（MID）

公式：

=MID（text，start_num，num_chars）。

参数：

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

Start_num是文本中要提取的第一个字符的位置，文本中第一个字符的start_num为1，以此类推；Num_chars指定希望MID从文本中返回字符的个数；Num_bytes指定希望MIDB从文本中按字节返回字符的个数。

实例：

如果E4=如皋国税一分局税源管理一股，则公式“=MID（E3,5,3）”返回“一分局”，如《纳税评估分析模型》“基础信息维护——纳税人登记信息”K4

7、查找函数

（1）纵向查找（HLOOKUP）

用途：

在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。

语法：

HLOOKUP（lookup_value，table_array，row_index_num，range_lookup）参数：

Lookup_value是需要在数据表第一行中查找的数值，它可以是数值、引用或文字串；Table_array是需要在其中查找数据的数据表，可以使用对区域或区域名称的引用，Table_array的第一行的数值可以是文本、数字或逻辑值。

Row_index_num为table_array中待返回的匹配值的行序号。

Range_lookup为一逻辑值，指明函数HLOOKUP查找时是精确匹配，还是近似匹配。

实例：

如《纳税评估分析模型》“评估分析——基本参数设置”A19

公式：

=IF（C11="","",IF（COUNTIF（行业评估模型!

$B$219:

$AQ$219,$C$11）,HLOOKUP（$C$11,行业评估模型!

$B$219:

$AQ$220,2,0）,""））

（2）横向查找（VLOOKUP）

用途：

在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。

当比较值位于数据表首列时，可以使用函数VLOOKUP代替函数HLOOKUP。

语法：

VLOOKUP（lookup_value，table_array，col_index_num，range_lookup）参数：

Lookup_value为需要在数据表第一列中查找的数值，它可以是数值、引用或文字串。

Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。

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

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

Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配。

如果为TRUE或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于

lookup_value的最大数值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值。

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

实例：

如《纳税评估分析模型》“评估分析——基本参数设置”B9

公式：

=IF（B4="","",IF（COUNTIF（税务登记信息!

$B$4:

$K$9000,$B$4）,VLOOKUP（$B$4,税务登记信息!

$B$4:

$K$9000,9,0）,""））

（3）逆向查找

逆向查找是一个数组公式，在输入公式后同时按下“Ctrl、Shift、Enter”三个键，此时公式会自动加上大括号，公式见《Excel实战技巧精粹》第387页

实例：

如《纳税评估分析模型》“评估分析——基本参数设置”B4

公式：

=IF（B4="","",IF（COUNTIF（税务登记信息!

$B$4:

$B$9000,$B$4）,VLOOKUP（$B$4,IF（{1,0},税务登记信息!

$B$4:

$B$9000,税务登记信息!

$A$4:

$A$9000）,2,0）,""））

8、排序函数（RANK）

用途：

返回一个数值在一组数值中的排位（如果数据清单已经排过序了，则数值的排位就是它当前的位置）。

语法：

RANK（number，ref，order）参数：

Number是需要计算其排位的一个数字；Ref是包含一组数字的数组或引用（其中的非数值型参数将被忽略）；Order为一数字，指明排位的方式。

如果order为0或省略，则按降序排列的数据清单进行排位。

如果order不为零，ref当作按升序排列的数据清单进行排位。

注意：

函数RANK对重复数值的排位相同。

但重复数的存在将影响后续数值的排位。

如在一列整数中，若整数60出现两次，其排位为5，则61的排位为7（没有排位为6的数值）。

实例：

如《纳税评估电子选案模型》“选案指标计算过渡表”BM63

公式：

=IF（BL63="","",RANK（BL63,$BL$5:

$BL$5894））

9、统计函数（SUMPRODUCT）

用途：

该函数可统计符合条件的个数或某一列符合条件的合计数。

语法：

SUMPRODUCT（array1*array2*array3*...）参数：

Array1，array2，array3，...为条件列，其条件间需要用“*”连接。

实例：

如《指标参数测算电子模型》“指标参数测算”O3

公式：

=IF（COUNTIF（增值税申报数据过渡!

$Q$3:

$Q$5002,$B3）,SUMPRODUCT（（增值税申报数据过渡!

D$3:

D$5002）*（增值税申报数据过渡!

$Q$3:

$Q$5002=$B3））,""）

10、数组公式

数组公式在输入公式后同时按下“Ctrl、Shift、Enter”三个键，此时公式会自动加上大括号

实例：

如《纳税评估分析模型》“评估文书出具——评估工作底稿”A9，下面公式中“=IF（ROW（）-8”的“8”是指本单元格从本行往上数共有多少行

公式：

=IF（ROW（）-8>COUNTIF（评估分析疑点清册!

$L$9:

$L$29,评估分析疑点清册!

$L$7）,"",INDEX（评估分析疑点清册!

B:

B,SMALL（IF（评估分析疑点清册!

$L$9:

$L$29=评估分析疑点清册!

$L$7,ROW（评估分析疑点清册!

$L$9:

$L$29））,ROW（1:

1））））