Excel 常用技巧Word文档格式.docx
《Excel 常用技巧Word文档格式.docx》由会员分享,可在线阅读,更多相关《Excel 常用技巧Word文档格式.docx(12页珍藏版)》请在冰点文库上搜索。
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)。
如果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,行业评估模型!
$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,税务登记信息!
$K$9000,9,0),"
(3)逆向查找
逆向查找是一个数组公式,在输入公式后同时按下“Ctrl、Shift、Enter”三个键,此时公式会自动加上大括号,公式见《Excel实战技巧精粹》第387页
如《纳税评估分析模型》“评估分析——基本参数设置”B4
$B$9000,$B$4),VLOOKUP($B$4,IF({1,0},税务登记信息!
$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$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$29=评估分析疑点清册!
$L$7,ROW(评估分析疑点清册!
$L$29)),ROW(1:
1))))