EXCEL笔记总结Word格式.docx
- 文档编号:21932269
- 上传时间:2023-02-01
- 格式:DOCX
- 页数:10
- 大小:20.37KB
EXCEL笔记总结Word格式.docx
《EXCEL笔记总结Word格式.docx》由会员分享,可在线阅读,更多相关《EXCEL笔记总结Word格式.docx(10页珍藏版)》请在冰豆网上搜索。
4.筛选后复制数据
用定位工具中的“可见单元格”,再复制粘贴
5.运用筛选中的文本筛选和数值筛选工具
6.高级筛选的条件区域设置
如上表:
即设置为筛选条件为“所有一车间的数据或发生额大于3000的数据”;
如下表:
即设置为为筛选条件为“所有一车间发生额大于3000的数据”
部门
发生额
一车间
>
3000
7.高级筛选中的条件设置须用公式的情形
(1)如须筛选并复制表头的,则条件设置中须有错误的表头;
(2)如筛选后不复制表头的,则条件设置中的表头为空
第五讲:
分类汇总和数据有效性
1.使用分类汇总前,要先排序
2.复制粘贴汇总统计结果
运用定位工具,选取可见单元格
3.数据有效性主要用于设置单元格录入数据的条件
设置C列付款方式中仅能输入现金、转账、支票
(1)选取数据有效性中的序列;
(2)来源中,录入现金、转帐、支票,并用英文的逗号(,)隔开
第六讲:
数据透视表
1.
第七讲:
认识公式与函数
1.公式运算符
算术运算符:
+-*/%&
^
比较运算符:
=>
<
>
=<
技巧:
A1单元格中的数据为文本类型的30,运用公式“=A1+0”后,即可进行公式运算
2.绝对引用和相对引用
相对引用:
A1
绝对引用:
$A$1
混合引用:
$A1A$1
示例1:
排名函数=RANK(H5,$H$5:
$H$11)
示例2:
九九乘法表
3.TRUE相当于1;
FALSE相当于0
4.运用定位工具定位单元格后,选择输入公式的位置
5.CTRL+ENTER快捷键:
批量复制
第八讲:
IF函数
1.=IF(E2="
男"
"
先生"
女士"
)
解释:
如果E2是男,则在输入公式的单元格显示“男”,否则显示“女士”
2.=IF(B2="
理工"
LG"
(IF(B2="
文科"
WK"
CJ"
)))
如果B2是理工,则在输入公式的单元格显示“LG”,如果B2是文科,则在输入公式的单元格显示“WK”,如果B2是财经,则在输入公式的单元格显示“CJ”
3.=IF(G2="
本地"
H2+30,IF(G2="
本省"
H2+20,H2+10))
如果G2是本地,则总分+30分,如果G2是本省,则总分+20分,如果G2是外省,则总分+10分
4.=IF(I4>
=600,"
第一批"
IF(I4>
=400,"
第二批"
落榜"
))
录取情况
600分含600显示第一批
400-600含400分显示第二批
400分以下落榜
因为I4>
=600时,已经把大于和等于600进行处理了。
在处理此类数据输入公式,要注意数据的逻辑顺序。
5.=IF(G6="
A级"
10000,0)+IF(G6="
B级"
9000,0)+IF(G6="
C级"
8000,0)+IF(G6="
D级"
7000,0)+IF(G6="
E级"
6000,0)
用+代替IF嵌套,即如果G6是E级,即运算为0+0+0+0+6000;
如果是D级,即运算为0+0+0+7000
6.IF函数与ISERROR函数
=IF(ISERROR(D35/C35),0,D35/C35)
如果D35/C35是错误的,则显示为0,否则显示正常运算结果
7.AND函数与OR函数
=IF(AND(A3="
B3>
=60),1000,0)
对于60岁以上(含)的男性员工给予1000元奖金
=IF(OR(B15>
60,B15<
40),1000,0)
对于60岁以上或40岁以下的员工给予1000元奖金
=IF(OR(AND(A20="
B20>
60),AND(A20="
女"
B20<
40)),1000,0)
对于60岁以上的男员工或40岁以下的女员工给予1000元奖金
注意:
用IF函数,必须先理清条件的逻辑结构
第九讲:
COUNTIF函数
1.count函数
(1)公式:
=COUNT(F:
F)
统计F列中的计数
(2)公式:
=COUNTIF(E:
E,H8)
统计H8单元格的数据在E列中出现的次数
(3)公式:
=COUNT(B2:
G2)
统计B2:
G2中的计数
2.countif函数
=COUNTIF(B2:
G2,"
=60"
G2中数值大于60的数据个数
=60条件要加””
=COUNTIF(A2:
A3,A2&
"
*"
统计A2单元格的数据在A2:
B3中个数
如果须统计的单元格数值长度超过15位,须采取A2&
的形式转换为以文本格式形式进行查询
=IF(COUNTIF(G:
G,A2&
)=0,"
未体检"
已体检"
if函数与countif函数嵌套
3.在条件格式中引用countif函数
在条件格式工具中的“新建规则”中输入公式
如把重复数据设置为红色字体或红色背景
4.在数据有效性中引用countif函数
在数据有效性工具中对选定的单元格中输入公式=COUNTIF(C:
C,C1)<
2,则可禁止输入重复数据
5.多条件统计函数countifs
公式:
=COUNTIFS(D:
D,I5,E:
E,J5)
多条件统计用逗号隔开,D:
D,I5为条件1,E:
E,J5为条件2
第十讲:
sumif函数
1.公式:
=SUMIF(E:
E,H8,F:
按E列中单元格与H8单元格相同的条件,然后统计求和与该E列单元格对应的F列中数据
如果公式是=SUMIF(E:
E,H8,E:
E),则,E:
E可省略。
2.按条件进行统计求和
=SUMIF(F:
F,"
=500"
把F列中大于或等于500的数据进行统计求和
3.统计求和时,数值长度大于15位情形的处理
=SUMIF(A:
A,F3&
B:
B)
通过&
把数值按文本格式进行统计求和
4.输入条件范围必须与统计求和范围一致
公式1(正确):
=SUMIF(D:
D,H4,F:
公式2(错误):
D,H4,$F$2:
$F$9)
如果把统计求和范围输入为与条件范围对应的第一个单元格,则视作为统计范围的简写,如=SUMIF(D:
D,H4,F1),则系统将自动纠错,视为=SUMIF(D:
F)。
同样,用此办法可统计求和多列数据,如下表中,
A
B
C
D
E
F
G
H
I
J
科目划分
邮寄费
5.00
150.00
交通工具消耗
600.00
手机电话费
1,300.00
公积金
15,783.00
出租车费
14.80
话费补
180.00
采暖费补助
925.00
出差费
1,328.90
抵税运费
31,330.77
20.00
资料费
258.00
招待费
953.00
工会经费
1,421.66
办公用品
18.00
过桥过路费
50.00
258.50
1,010.00
1,755.00
36.00
运费附加
56.00
养老保险
267.08
1,016.78
2,220.00
52.00
独子费
65.00
277.70
1,046.00
2,561.00
60.00
70.00
278.00
教育经费
1,066.25
2,977.90
78.00
350.00
失业保险
1,068.00
3,048.40
408.00
1,256.30
误餐费
3,600.00
80.00
560.00
修理费
1,260.00
6,058.90
其他
95.00
用公式=SUMIF(A:
J,L3,B1)可统计求出表中邮寄费的总和。
(其中L3是另一张表的单元格,数据为邮寄费)
5.多条件的统计求和函数sumifs
(1)方法一:
A,J5&
K5,G:
G)
增加辅助列,把多列数据用&
进行连接,再把查找条件用&
连接后进行统计求和
(2)方法二:
=SUMIFS(G:
G,E:
E,J5,F:
F,K5)
G:
G为统计求和列,E:
E,J5为条件1,F:
F,K5为条件2.
6.在数值为唯一时,sumif可替代vlookup函数
如:
公式1:
A,M4,J:
J)
与公式2:
=VLOOKUP(M4,$A$2:
$J$16,10,0)
在同一工作表中,效果一样
7.数据有效性的设置
可运用公式设置某类商品的出库量输入值累计相加不大于入库总量
F,F3,G:
G)<
SUMIF(A:
A,F3,B:
公式2:
VLOOKUP(F3,$A$2:
$B$7,2,0)
设置的条件相同
第十一讲:
VLOOKUP函数
1.基本应用
=VLOOKUP(G6,$B$2:
$E$11,4,0)
(1)G6:
所需要查找比对的数据单元格
(2)$B$2:
$E$11:
选取B2:
B11后按F4键,改为绝对引用,即引用查找数据源的范围。
(如为列的绝对引用范围则为$B$:
$E$。
)其中,B2单元格所在列,必须有G6单元格数据,即G6单元格数据必须是在所引用范围中最左边的列中;
且B2:
B11间的列中有需引用的数据。
(3)4:
从选取的数据源范围内的第一列起,所需要引用的数据所在列的序数
(4)0:
精确查找(如要模糊查找,则为1)
2.跨表引用数据
=VLOOKUP(A2,数据源!
A:
B,2,0)
(1)数据源!
B:
即所选的其他工作表中的A列到B列数据,引用后必须在公式中加逗号
3.通配符查找
=VLOOKUP(A2&
数据源!
B:
E,4,0)
(1)A2&
:
即所引用的数据比所需查找比对的数据要长,运用在所引用的数据为简称的情形,如A2单元格数据为“三木实业”,所引用的数据为“三木实业有限公司”
=VLOOKUP(G9,$C$8:
$D$13,2,1)
(1)模糊查找一般运用于近似值查询
(2)模糊查找的结果系统默认为小于或等于其的最近值
5.数字格式
公式一:
=VLOOKUP(F4&
$A$2:
$C$6,3,0)
运用于把数值格式转为文本格式,再与所引用的文本数据进行查找对比
公式二:
=VLOOKUP(F12-0,$A$10:
$C$14,3,0)
运用于把文本格式转为数值格式,再与所引用的数值数据进行查找对比。
F12-0,换成F12+0,F12*1或F12/1均可。
公式三:
=IF(ISNA(VLOOKUP(F20*1,$A$18:
$C$22,3,0)),VLOOKUP(F20&
$A$18:
$C$22,3,0),VLOOKUP(F20*1,$A$18:
$C$22,3,0))
运用于把所需查找比对及引用数据中存在文本格式与数值格式混用情形
6.HLOOKUP函数
=HLOOKUP(B14,$1:
$3,3,0)
与VLOOKUP函数引用范围转换,$1:
$3,3中的数值均指行
求应纳所得税款
起征点
3500
级别
应纳税所得额(元)
税率
速扣数(元)
计算个税
不超过1500
3%
税前月薪
个税
1500
1500~4500
10%
105
300
4500
4500~9000
20%
555
20000
3120
9000
9000~35000
25%
1005
8000
345
35000
35000~55000
30%
2755
4000
15
55000
55000~80000
35%
5505
12500
1245
80000
超过80000
45%
13505
解答:
=IF(F7<
=3500,0,VLOOKUP(F7-3500,$A$6:
$D$12,3,1)*(F7-3500)-VLOOKUP(F7-3500,$A$6:
$D$12,4,1))
第十二讲:
match函数和index函数
1.match函数
=MATCH(A2,数据源!
$A:
$A,0)
即在名称为数据源的工作表A列中查找与A2单元格数据相同的单元格,并数出其在数据源的工作表A列中的行数,并为精确查找。
2.index函数
=INDEX(数据源!
$B:
$B,MATCH(A2,数据源!
$A,0))
即在名称为数据源的工作表B列中,根据match函数查找出来的行数,把处在同一行的B列单元格数据引用出来。
3.单元格引用原理
=$A2*D$1
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- EXCEL 笔记 总结
![提示](https://static.bdocx.com/images/bang_tan.gif)