EXCEL实用技巧培训-洪翊

6、IF函数（COUNTIF/SUMIF）
例，
在B12单元格中输入： =IF(B11＞=60,”合格”,”不合格”)
当逻辑判断式为真 时，返回的值
当逻辑判断式为 假时，返回的值
逻辑判断式
6、IF函数（COUNTIF/SUMIF）
COUNTIF函数
功能：统计符合条件要求的单元格的数量 例，
在成绩录入的D4单元格 输入：
=HLOOKUP(C4,分数等级表!B1:K4,3)
在成绩录入的D5单元格 输入：
=HLOOKUP(C4,分数等级表!B1:K4,4)
8、Vlookup/Hlookup函数
功能：
Vlookup用于在表格或数值数组的首行查找指 定的数值，并由此返回表格或数组当前列中指 定行处的数值 Hlookup用于在表格或数值数组的首列查找指 定的数值，并由此返回表格或数组当前行中指 定列处的数值 当比较值位于数据表的首行，并且要查找下面 给定行中的数据时，请使用Hlookup 当比较值位于数据表的最左列，并且要查找右 面给定列中的数据时，请使用Vlookup
Hale Waihona Puke =VLOOKUP(C6,分数等级表二!$A$2:$D$11,3)运算，
以此类推……
9、MOD函数在美化表格中的应用
功能：设置间隔式底纹 例，
9、MOD函数在美化表格中的应用
选中需要设置间隔式底纹的区域 “格式→条件格式”，弹出的“条件格式” 对话框（如图） 在左侧的下拉菜单中选择“公式”，并在 右侧区域输入=MOD(ROW()，2),然后点击 “格式”按钮
TRUNC函数——对小数进行取整
（同INT函数功能） • TRUNC(number) • number就是需要进行取整的数字
5、ROUND/TRUNC函数
使用方法：
B2单元格 C2单元格 D2单元格
=ROUND(A2,2) =ROUND(A2,0) =TRUNC(A2) 或
=INT(A2)
2、AVERAGE/TRIMMEAN函数
TRIMMEAN函数
用途：求数据集的内部平均值（即可以按照要 求去掉所有数据中最高的和最低的一些数据， 然后求平均值） 使用方法：TRIMMEAN（array,percent) 例，
需要求内 部平均值 的所有数 据区域 需要去除 的数据占 全部数据 的比例
Function_num
1 2
对应功能 AVERAGE（求平均值） COUNT（数值计数）
所引用区域
3
4 5
COUNTA（计数）
MAX（求最大值） MIN（求最小值）
9
SUM（求和）
8、Vlookup/Hlookup函数
例1，excel中有两张表格（如下图所示），分别 是分数等级表（上图）和成绩录入（下图）：
在B5单元格中输入=countif(B2:B4,”>20”), 可以求出在B2到B4这三个单元格中大于20 的数字有几个
6、IF函数（COUNTIF/SUMIF）
SUMIF函数
功能：统计符合条件要求的单元格中数字 之和 例，
在B5单元格中输入=sumif(B2:B4,”>20”), 可以求出在B2到B4这三个单元格中大于20 的数字之和
所要 查找 的值
返回对应 该行序号 的单元格
VLOOKUP(lookup_value,table_array,col_index_num,rang_lookup)
返回对应 该列序号 的单元格
8、Vlookup/Hlookup函数
例2，excel中有两张表格（如下图所示），分别是分数 等级表二（左图）和成绩录入（右图）：
3、COUNT/COUNTA函数
用途：计数
COUNT——统计所选中的单元格区域中数字的个数 COUNTA——统计所选中的单元格区域中非空单元 格的个数（包括数字、文字等）
例，
4、常用函数快捷方式
在excel表格的右下角有 一个常用函数快捷方式的 区域（如同所示），可以 右键点击该区域，使用六 种常用函数 这六种常用函数，分别是：
在成绩录入的D4单元格输入： =VLOOKUP(C4,分数等级表二!A2:D11,3) 在成绩录入的E4单元格输入： =VLOOKUP(C4,分数等级表二!A2:D11,4)
8、Vlookup/Hlookup函数
上面两个例子，在下拉完 成其他类似函数运算时， 出现了问题—— 我们在D4单元格输入了 =VLOOKUP(C4,分数等级表 二!A2:D11,3)，需要D5单 元格按照公式 =VLOOKUP(C5,分数等级表 二!A2:D11,3)进行运算， 然而在下拉时，系统会自 动将D5单元格的公式变为 =VLOOKUP(C5,分数等级表 二!A3:D12,3)
9、MOD函数在美化表格中的应用
在弹出的“单元格格式”对话框中，选择 “图案”选项卡，然后选择需要的颜色， “确定”
点击“条件格式”对话框中的“确定”按钮
平均值（AVERAGE） 计数（COUNTA） 计数值（COUNT） 最大值（MAX） 最小值（MIN） 求和（SUM）
5、ROUND/TRUNC函数
功能：
ROUND函数——对小数按照位数要求四舍五入
（亦可通过设置“单元格格式”实现同样功能） • ROUND(number,num_digits) • number就是将要进行四舍五入的数字 • num_digits则是需要保留小数点后几位
注：如果需要求和的单元格是不连续的， 则可按住Ctrl键分别拖过它们。
2、AVERAGE/TRIMMEAN函数
AVERAGE函数
用途：求算数平均数
使用方法：同SUM函数相同，只需把刚才所有 “SUM”的部分替换成“AVERAGE”
• =AVERAGE(B3:G3) • =AVERAGE(B3:B10) • =AVERAGE(B3:G10)
9
1、SUM函数
行或列求和
H3单元格 =SUM(B3:G3) B11单元格 =SUM(B3:B10)
1、SUM函数
区域求和
H11单元格
=SUM(B3:G10)
1、SUM函数
鼠标拖拉法
选中存放求和结果的单元格，点击工具栏 中的“∑”（自动求和）按钮 用鼠标拖过需要求和的所有单元格，然后 回车即可
7、SUBTOTAL函数
7、SUBTOTAL函数
为了分别统计各个专业的人数，可以在D1单 元格中输入：
=SUBTOTAL(3,D3:D30)
然后利用“数据→筛选→自动筛选”功能即 可
7、SUBTOTAL函数
用途：对筛选列表中的可见单元格进行运算 语法及功能
SUBTOTAL（function_num,ref1,ref2,…）
EXCEL实用技巧培训
财务部
洪翊
主要内容
SUM函数
AVERAGE/TRIMMEAN函数
1
2 3
COUNT/COUNTA函数
常用函数快捷方式 ROUND/TRUNC函数
4
5
主要内容
IF函数（COUNTIF/SUMIF） SUBTOTAL函数
6
7 8
Vlookup/Hlookup函数
MOD函数在美化表格中的应用
8、Vlookup/Hlookup函数
在D4单元格输入
=VLOOKUP(C4,分数等级表二!A2:D11,3)之后，用
鼠标选中公式中A2:D11部分，然后按F4键，即可 看到A2:D11部分变为了$A$2:$D$11，这样在下拉 就不会出现问题了，即D5单元格按照公式 =VLOOKUP(C5,分数等级表二!$A$2:$D$11,3)运算， D6单元格按照公式
8、Vlookup/Hlookup函数
使用方法：
HLOOKUP(lookup_value,table_array,row_index_num,rang_lookup)
用于查找数据的区域，所 要查找的数据必须与该区 域首行（或最左列）的数 据进行比较 FALSE（精 确匹配）或 TRUE（近 似匹配）