[办公应用]excel高效数据处理

《[办公应用]excel高效数据处理》由会员分享，可在线阅读，更多相关《[办公应用]excel高效数据处理（42页珍藏版）》请在金锄头文库上搜索。

1、数据高效处理excel北京杰飞电脑培训中心北京杰飞电脑培训中心目录单元格格式的定义,表格的打印公式与函数的基础 逻辑函数使用 日期函数 数学函数 文本函数 统计函数 查找引用函数 数据库函数图表的制作 数据的处理:排序,筛选,分类汇总北京杰飞电脑培训中心北京杰飞电脑培训中心公式与函数 =算术运算符 +-*/%比较运算符 = 文本连接符 逗号,将两个区域联合生成一个区域;空格 将两上区域相交部分生成一个区域;北京杰飞电脑培训中心北京杰飞电脑培训中心地址引用相对地址引用当把一个含有单元格地址引用的公式填充或复制到 一个新的位置时，公式中单元格地址会随之发生变 化。绝对地址引用混合地址引用北京杰飞电

2、脑培训中心北京杰飞电脑培训中心EXCEL数组北京杰飞电脑培训中心北京杰飞电脑培训中心EXCEL数组例数组函数用于同时进行多个计算并返回一种或多种结果。数组 函数对两组或多组被称为数组参数的数值进行运算。每个参数 必须有相同数量的行和列； 多个条件区域之间可以用乘号相连。北京杰飞电脑培训中心北京杰飞电脑培训中心公式中错误检查错误值产生原因#!计算结果太长,宽度不够,或者是包含无效的日期#DIV/0公式中引用了一个空单元格,或者除数为0#N/A公式中没有可用的数值,或者缺少函数参数#NAME?公式中引用了一个不可用的数据或者参数#NULL!进行了不正确的单元格引用或者区域运算#NUM使用了无效的数

3、值.或者计算结果太大或者太小#REF应用了无效的单元格#VALUE!错误地引用了单元格或引用了错误的数值北京杰飞电脑培训中心北京杰飞电脑培训中心文本函数的使用LEFT, RIGHT :从字符串的最左端位置提取指定数量的字符;LEN返回字符串的个数LOWER将所有字母转换为小写字母UPPER将所有字母转换为大写字母PROPER:所有字母转换为首字母大写FIND和SEARCH函数:文本查找函数,都是用于定位某一个字符吕在指 定的字符串中的起始位置的序号示例:从编码中提取合同号=RIGHT(A21,LEN(A21)-SEARCH(“A“,A21,8)+1)提取账户=LEFT(B30,FIND(“,B

4、30)-1)EXACT比较两个字符串：如果它们完全相同=IF(EXACT(B39,C39)=FALSE,B39-C39,EXACT(B39,C39)北京杰飞电脑培训中心北京杰飞电脑培训中心文本函数substitute函数是替换字符串的函数，用法如下： substitute（字符串，“替换的部分替换后的“,“从第几个开始替 换“） 例：substitute(“A-B-CC“,“-“,“ “)就可以把-替换为空格即：“A B CC“获取有多少个人员上课=LEN(B2)-LEN(SUBSTITUTE(B2,“,“,“)+1合并订单号=CONCATENATE(A2,B2,C2,D2)REPT按照给定的

5、次数重复显示文本。可以通过函数 REPT 来不断 地重复显示某一文本字符串，对单元格进行填充。让身份证号码前六位隐藏显示 =CONCATENATE(REPT(“*”,6),RIGHT(C12,12) 选择性粘贴数 据从单元格中提取有用数据并合并起来=CONCATENATE(LEFT(C19,3),A19,IF(B19=“男“,“先生“,“女士“)北京杰飞电脑培训中心北京杰飞电脑培训中心函数的使用北京杰飞电脑培训中心北京杰飞电脑培训中心函数的使用MID函数返回文本字符串从指定位置开始的特定数 目的字符； IF函数用于执行真假值判断。MOD函数的功能返回两个数相除的余数；DATE函数的功能返回代表

6、特定日期的序列； 1、判断性别：IF(MOD(MID(E18,17,1),2)=0,“女“,“男 “) 2、出生日期 =DATE(MID(E18,7,4),MID(E18,11,2),MID(E18,13, 2)北京杰飞电脑培训中心北京杰飞电脑培训中心函数的使用TODAY函数返回当前的日期 TODAY() NOW函数返回当前的时间 YEAR函数返回某个日期对应的年份； DATEDIF返回两个日期相差的天数，月数与年数； ROUNDDOWN向下舍入数字年龄： =DATEDIF(G18,TODAY(),“Y“)； 工龄：=ROUNDDOWN(NOW()-K18)/365,0)合同到期： DATE(

7、YEAR(M18)+1,MONTH(M18),DAY(M18)-1)提前三十天提醒 =IF(N18-TODAY()=DAY(TODAY(),“还有“使用MATCH函数返回指定元素所在位置.=MATCH(A11,B1:B7,0);=MATCH(A12,A1:D1,0)INDEX函数实现查找:返回数据清单或数组中的元 素值,此元素由行序号和列序号索引值给定.如成绩 表中指定条件查找学生某项成绩. =INDEX(B2:F13,6,4) ;自己动手完成某个人总分Index与MATCH结合查找内容=INDEX($A2:$D7,MATCH($A12,$A$2:$A7,0),COL UMN(B1)北京杰飞电

8、脑培训中心北京杰飞电脑培训中心查找引用函数Index与MATCH结合实现双条件查找=INDEX(B2:D4,MATCH(B8,A2:A4,0),MATCH(A8,B1:D1,0 )Index与MATCH结合实现反向查询=INDEX(A2:A13,MATCH(MAX(F2:F13),F2:F13)OFFSET以指定的引用为参照系，通过给定偏移量得到新 的引用。返回的引用可以为一个单元格或单元格区域。=OFFSET(B1,0,$I$1)实现动态查询北京杰飞电脑培训中心北京杰飞电脑培训中心合并计算通过汇总两个或两个以上源区域中数据进行计算；一种是通过位置合并计算，二是通过类别进行计算；源区域可以是当

9、前工作表的其它部分。或者是其它 工作表。或者是其它工作薄北京杰飞电脑培训中心北京杰飞电脑培训中心数据有效性整数:限制单元格只能输入某一范围的整数;小数:同上,但是包含这一范围的整数;序列:如男,女;日期:限制单元格只能输入某个范围的日期.时间:文本长度:限制单元格可以输入的字符串长度;自定义:LEN(A1)=9表示字符串长度等于9;LEFT(A1)=“A”以A开头的字FIND(“A”,A1)=1,区分大小写必须以大写开始;RIGHT(A1,2)=“公司”要求输入的信息的是后两个字为”公司”北京杰飞电脑培训中心北京杰飞电脑培训中心数据有效性数据有效性保护公式；先使用定位条件找到表中的公式.然后对

10、其进行单元 格格式设置保护数据有效性自定义0 。然后再到审阅中保护工作表；数据有效性限制单元格只能输入当年日期=YEAR(C4)=YEAR(NOW()数据有效性-限制单元格只能输入数字；=ISNUMBER(E6)数据有效性-限制单元格只能输入百分占比小数介于0或者1北京杰飞电脑培训中心北京杰飞电脑培训中心数据有效性数据有效性-限制单元格只能固定电话号码 自定义=LEN（-C3）=7;解释：-C3是负负得正；如果是文本 或者前面有0可以去除；如C3=“0123456”c3=123456而LEN 则是限制为7位；数据有效性-限制单元格只能EMAIL地址 =COUNTIF(I12,“?*?*.?*”

11、)解释：？表示单个，*表示任意多个 ；数据有效性-限制单元格只能输入特定编码 = COUNTIF(I12,“KCCBP？”)*RIGHT（I12，6）； = COUNTIF(I12,“KCCBP？”)判断单元格是否以KCCBP 开头，后面紧跟6个字符，如果是则返回1，否则返回0；有效性 中,非0数字表示符合条件,0表示不符合条件.数据有效性-限制单元格不能重复输入 =COUNTIF(A:A,A1)=1北京杰飞电脑培训中心北京杰飞电脑培训中心条件格式标识不及格成绩复制条件格式:格式刷,复制-选择性粘贴格式使用公式标志不在使用的状态:=$k3=“不在使用”显示下一个工作日的工作任务对设定的日期定义

12、一个名称:NEXTWORKDAY,名称的内容 为:=WORKDAY($D$1,1)返回若干个工作日之前或者之后日期,取 值为1表示之后.如果取值-1,表示为之前;=$A3= NEXTWORKDAY标识重复值:1出现重复就标识:=COUNTIF($C$2:$C$27,$C2)1;第2次重得以后才标识=COUNTIF($C$2:$C2,$C2)1首次与其他重复次数标识不同的颜色.北京杰飞电脑培训中心北京杰飞电脑培训中心排序包含标题的数据排序；超过三列数据排序：先以主要与次要进行排序。然 后再进行一次排序；自定义序列进行排序；按行方式排序以及按住笔划方式；北京杰飞电脑培训中心北京杰飞电脑培训中心数据

13、筛选自动筛选 多重标题的自动筛选 自定义功能筛选复杂数据 以F字母开头或名字第四个字母是M的顾客.不包含1980年出生,金额大 于200,产品名称以G开头S结尾.或者BREAD 筛选不重复值 定义一个辅助列=COUNTIF(A$2:A2,A2) 根据筛选结果动态汇总计算;使用SUBTOTAL函数,在筛选的时 候包含管理费,或包含合计 高级筛选 了解高级筛选 设置高级筛选条件区域 1同一行中的条件之间关系是逻辑”与”;2不同行中条件之间”或”条件3条件 区域中的空白单元格表示任意条件,即保留学生所有的记录做筛选 多个筛选条件的相互关系 高级筛选中通配符的使用北京杰飞电脑培训中心北京杰飞电脑培训中

14、心分类汇总与透视表分类汇总分类汇总多重分类汇总数据透视表北京杰飞电脑培训中心北京杰飞电脑培训中心图表-选用何种图表1.用于比较:1.分类之间的比较,可以选用柱形图,条形图,基于时间变化的 比较:折线图,柱形图2.用于构成:1.不同时间变化的:饼图,条形图,瀑布图,随时间变化的堆积 柱形图,百分比柱形图,堆积面积图,百分比面积图3.用于相关:1.2个变量,散点图与背离式条形图.北京杰飞电脑培训中心北京杰飞电脑培训中心市场规模增长图(柱形图)1、添加主标题,副标题,及脚注. 2、设置图表字体样式,一般限制三种 以内. 3、把环比增长变成折线图:选选择环 比增长,然后更改图表类型，最后 上环比增长成

15、为次坐标是次坐标轴 4、环比增长与市场规模数据分离 纵坐标轴，最大刻度150，主要刻 度单位30。次要坐标轴最小0。5， 最大设为0。3 5、隐藏坐标轴，把边线设为0坐标轴 标签为0北京杰飞电脑培训中心北京杰飞电脑培训中心市场规模增长图(堆积柱形图)堆积柱形图除了可以呈现市场整体规模 外,还可以同时呈现市场各个组成部 分的情况. 为总计添加数据标签,位置为轴内侧,隐 藏总计列.北京杰飞电脑培训中心北京杰飞电脑培训中心市场结构组成图(复合饼图)北京杰飞电脑培训中心北京杰飞电脑培训中心产品需求分析图为什么不采用饼图 1、当项目的数量较多的时候 2、调查问卷的内容，被设计 成多项选择，这样的结果使 各个项目大于100。