EXCEL笔记

EXCEL笔记第一讲：认识EXCEL1.名称框的快捷查找、选取作用可在名称框中输入A10000，即可直接找到A10000单元格；如果输入A1：C10000，即可直接选取A1：C10000区域。2.直接到表格最后一行快捷键CTRL+END3.单元格录入数据分行ALT+ENTER4.特殊的填充5.格式刷的运用第二讲：单元格格式第三讲：查找、替换和定位1.按值查找2.按格式查找3.开启单元格匹配4.模糊查找（1）通配符？如果只查找两个字符，须注意勾选“单元格匹配”（2）通配符*示例：查找张*（姓张名*），用张~*即可5.用名称框定位区域示例：A9000：B100006.选取区域后在名称框中命名后定位区域7.运用定位工具将合并单元格拆分的单元格进行批量复制思路：先把空白单元格定位，再在编辑栏中输入=D4（其中D4为第一个拆分单元格上面一个单元格），按CTRL+ENTER？如何把拆分后的单元格再批量合并为一个单元格步骤：（1）先排序；（2）分类汇总；（3）选取表头下一个单元格至最后一行单元格，定位空白单元格后再合并单元格；（4）删除分类汇总；（5）复制合并单元格的一列，选择性粘贴（选格式）到须合并的一列。第四讲：排序和筛选1.简单排序点定排序条件的一个单元格，再点升序或降序排列2.利用排序插入行，即达到隔行插入效果3.每面打印表头在页面设置中，选打印标题中的工作表的顶端标题行中设置4.筛选后复制数据用定位工具中的“可见单元格”，再复制粘贴5.运用筛选中的文本筛选和数值筛选工具6.高级筛选的条件区域设置如上表：即设置为筛选条件为“所有一车间的数据或发生额大于3000的数据”；如下表：即设置为为筛选条件为“所有一车间发生额大于3000的数据”部门发生额一车间30007.高级筛选中的条件设置须用公式的情形（1）如须筛选并复制表头的，则条件设置中须有错误的表头；（2）如筛选后不复制表头的，则条件设置中的表头为空第五讲：分类汇总和数据有效性1.使用分类汇总前，要先排序2.复制粘贴汇总统计结果运用定位工具，选取可见单元格3.数据有效性主要用于设置单元格录入数据的条件？设置C列付款方式中仅能输入现金、转账、支票步骤：（1）选取数据有效性中的序列；（2）来源中，录入现金、转帐、支票，并用英文的逗号（,）隔开第六讲：数据透视表部门发生额一车间30001.第七讲：认识公式与函数1.公式运算符算术运算符：+－*/%&^比较运算符：===技巧：A1单元格中的数据为文本类型的30，运用公式“=A1+0”后，即可进行公式运算2.绝对引用和相对引用相对引用：A1绝对引用：$A$1混合引用：$A1A$1示例1：排名函数=RANK(H5,$H$5:$H$11)示例2：九九乘法表3.TRUE相当于1；FALSE相当于04.运用定位工具定位单元格后，选择输入公式的位置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+70006.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(B1560,B1540),1000,0)解释：对于60岁以上或40岁以下的员工给予1000元奖金=IF(OR(AND(A20=男,B2060),AND(A20=女,B2040)),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函数（1）公式：=COUNTIF(B2:G2,=60)解释：统计B2：G2中数值大于60的数据个数注意：=60条件要加””（2）公式：=COUNTIF(A2:A3,A2&*)解释：统计A2单元格的数据在A2：B3中个数注意：如果须统计的单元格数值长度超过15位，须采取A2&*的形式转换为以文本格式形式进行查询（3）公式：=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: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:F)公式2（错误）：=SUMIF(D:D,H4,$F$2:$F$9)注意：如果把统计求和范围输入为与条件范围对应的第一个单元格，则视作为统计范围的简写，如=SUMIF(D:D,H4,F1)，则系统将自动纠错，视为=SUMIF(D:D,H4,F:F)。同样，用此办法可统计求和多列数据，如下表中，用公式=SUMIF(A:J,L3,B1)可统计求出表中邮寄费的总和。（其中L3是另一张表的单元格，数据为邮寄费）5.多条件的统计求和函数sumifsABCDEFGHIJ科目划分发生额科目划分发生额科目划分发生额科目划分发生额科目划分发生额邮寄费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独子费65.00出差费78.00手机电话费350.00失业保险1,068.00出差费3,048.40出差费78.00手机电话费150.00出差费408.00出差费1,256.30误餐费3,600.00招待费80.00邮寄费150.00出差费560.00修理费1,260.00出差费6,058.90其他95.00（1）方法一：=SUMIF(A: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：=SUMIF(A:A,M4,J:J)与公式2：=VLOOKUP(M4,$A$2:$J$16,10,0)在同一工作表中，效果一样7.数据有效性的设置如：可运用公式设置某类商品的出库量输入值累计相加不大于入库总量示例：公式1：=SUMIF(F:F,F3,G:G)SUMIF(A:A,F3,B:B)公式2：=SUMIF(F:F,F3,G:G)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）数据源!A:B：即所选的其他工作表中的A列到B列数据，引用后必须在公式中加逗号3.通配符查找公式：=VLOOKUP(A2&*,数据源!B:E,4,0)解释：（1）A2&*：即所引用的数据比所需查找比对的数据要长，运用在所引用的数据为简称的情形，如A2单元格数据为“三木实业”，所引用的数据为“三木实业有限公司”4.模糊查找公式：=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级别应纳税所得额(元)税率速扣数(元)计算个税0不超过150