一如何设置单元格格式
数值,文本(输入身份证号)表头设计,边框(Alt+Enter,一个单元格输入两行) 数据对比,视图,新建窗口,全部重排,保存工作区。
分列可以完成数值文本转换,例如文本日期改为数值格式,分列,完成。
二查找,替换,定位
按值,按格式,单元格匹配!精确查找符*(任意文字)?(一个字)~(去除查找符作用),让下面与上面一样(定位空格,=键盘“上”,Ctrl+Enter)
定位条件,对象,批量删除图片。
三排序与筛选
主要次要排序,从次要开始升降序。工资条(表头做出,表头0,下面123,第一个1.5,2.5然后升降序),顶端标题行(页面设置)
如何去除隐藏(定位条件,可见单元格,复制到新的工作表),“高级筛选”,筛选不重复记录(选择不重复记录),复制,同行为交,不同行为列,条件区域是自己制作的
部门 科目 金额
销售部 邮寄费 >100,同行表示销售部的邮寄费且大于100的。 高级筛选,条件区域有公式(表头不能写,或者写错!) 选第一个单元格,Ctrl+Shift,然后箭头指示
四分类汇总(先排序)
格式刷与选择性粘贴,一列相同的部分一次合并(分类汇总+计数形式+查找与替换空值+合并单元格+取消分类汇总+选择左端合并的单元格格式刷刷一下)
数据有效性选择一列,数据工具+数据验证,可以数字,可以文本长度即字符长度,例如产品编码。付款方式只能输入现金,转账,支票。序列@(中间用英语逗号!)直接做出一个下拉框!
数据保护,禁止更改数据(数据工具,自定义,输入一个错的公式就不能改单元格的数据了)
五数据透视表
位于插入最左端,点击后右端可以拖拽,左上角可以修改统计方法。左侧大类别,右侧小类别。制作透视表时,日期太详细,想分为季度,右击,创建组
金额区间制作,把金额拉到行字段所在处,然后拉到值字段处,金额处右击,创建组 员工姓名,双击取消分类汇总。求数据最大最小,平均,多拖拽几次到行字段,然后数据右拖到汇总。
写公式写在表格里,在数据透视表工具选项中,选择域,项目和集。 批量建表,透视表,一个项目放在最高位置(筛选字段),再把它放在值字段处,数据透视表工具选项,左侧选项,显示报表筛选页,
六认识公式与函数
1算术运算符+,-,*,/,%(除100),&(连字符),^(乘方)。
2比较运算符=,<,>,<=,>=,<>.
比较运算符的结果TURE,FALSE,TURE*1=1,FALSE*1=0
3拖拽时只能左端运算,绝对引用,选择需要绝对引用项,然后按Fn+F4功能键 4基本函数sum,average,max,min(比较区域),rank(对谁排序,比较区域)排名 5跳跃求和,定位条件选取空单元格,然后用自动求和。或者写编辑单元格写公式,Ctrl+回车(即选择几个不连续的单元格)
大范围复制公式,左上角输入要选的最后一行单元格,按Shift+Enter,在白空格处输入公式,按Ctrl+Enter就完成大范围复制公式
保留未输入完整的公式,在等号前输入空格 汇总金额与实际相差一分钱,单击,文件,选项,高级,勾选将精度设为所显示的精度。
七 IF函数(逻辑函数)
=If(某个=”男”,”先生”,”女士”),if的嵌套。有时用and逻辑并。2~7中可能可用,尽量用少的情况。If很多时用vlookup。
Iserror函数if(iserror(H4/G4),0, H4/G4)。and函数=if(and(H4=”男”,G4>=60),1000,0)。Or函数=if(or(h4=”女”,G4>=50),1000,0)
=Iferror(出错,出错显示)
八countif函数(计数值个数的函数count)
=countif(E列,数什么),=countif(C行,”>=60”)
对于位数高于15的,例如银行卡号,统计一样的时候,=countif(区域,选什么&”*”)注意:区域用绝对引用$
If(countif(区域,数什么)=0,“未体检”,”已体检”)开始,新建格式,使用格式 例如身份证,禁止输入重复数据数据有效性=countif($C:$C,C1)<2。 Countifs(条件区域,条件,条件区域,条件)
九 SUMIF函数
=sumif(E:E,邮寄费,F:F),大于50的发生额总计=sumif(F;F,”>=500”,F:F)在同行时第三条件可以省略。同样超过15位用=sumif(E:E,条件&”*”,F:F)
跨区域的=sumif(A:I,邮寄费,$发生额)
多条件求和,=sumifs(求和区域,D:D,一车间,E:E,邮寄费),求和区域为金额,D一车间所在列,E邮寄费所在列。
对于横向表格的条件求和,=sumif(选中所有区域,条件,求和表头绝对引用)。 出库与库存的数据有效性=SUMIF(F:F,F3,G:G)<SUMIF(A:A,F3,B:B)
十 VLOOKUP函数(按行找,求列)
查找列与引用列必需包含在第二参数(二参绝对引用)中,找的东西(第一参数)必须在第二参数的最左端,第三参数为引用列在第二参数第几列,第四参数0(精确匹配),1(非精确匹配)
=Vlookup(王梅,第二参数,第三参数,0)
跨表引用,不要随便点表,先写逗号,再点其它表。
通配符查找,精确匹配,字数不同。三川实业,三川实业有限公司。例如=vlookup(一参&”*”,二参,三参,0)
1,模糊匹配(觉得没有还要找),婚介所匹配年龄,近似年龄,只找小于等于它的近似值。
算提成金额
文本与数字不同,○1数值找文本,把要找的数值变为文本,例如=vlookup(F4&“”,二参,三参,0),○2文本找数值,把要找的文本变为数值=vlookup(F8*1,二参,三参,0)。○3二参区域有文本数值,要找的也是有文本数值,很强的vlookup忽略格式,=if(isna(vlookup(F12*1,二参,三参,0)),vlookup(F12&””,二参,三参,0),vlookup(F12*1,二参,三参,0)) Hlookup函数(按列找,求行)
十一 Vlookup函数嵌套
Match(专门匹配)。Index(专门引用),column()专门找列号 =index(引用区域,match(要找值,区域,0))
混合引用,锁定行,锁定列,行列不同时锁定 ○
1横向,纵向都拖拽=vlookup($D4,数据源,column()-3,0)引用列与原数表的列有规律 ○
2无规律,vlookup($A3,引用源,match(要找值B$2,区域,0),0) 引用图片,,定义名称,照相机,添加到新建选项卡。
十二邮件合并
邮件合并
批量生成文件,其它项目输入变量,每页显示一条内容。
每页显示多个内容,选择项目。修改数据格式,Alt+F9,\#”#,##0.00”意思三位有个逗号,保留两位小数,F9刷新。修改日期格式,空格\@”yyyy-M-d”
选择题,目录。
十三常用日期函数
判断闰年平年,=if(day(date(A3,3,0))=29,”闰年”,”平年”)
开始时间,结束时间,算持续时间(相减*60*24)开始时间,持续时间,算结束时间(开始时间+持续时间/24/60),日期可以直接相加减,得到天数。
当间隔为月份时,=date(year(B5),month(B5)+间隔月,day(B5))会自动完成进位 或者=edate(日期,间隔月份)间隔月份可以为负数!
开始日期,求本月最后一天(下一月的前一天)○1=date(year(B5),month(B5)+1,0)七月零号就是六月30号○2=eomonth(日期,0)。求月初的话(上一月月末加一)=eomonth(日期,-1)+1
根据日期求本月天数(求最后一天,最后一天数值就是本月天数) =day(date(year(B5),month(B5)+1,0))
算工龄=Datedif(入职日期,离职日期,”y”)y年m月d天,ym刨除年看零多少月md刨除月看零多少天。
由日期算本年多少周=weeknum(日期,把星期几作为一周开始) 由日期算周几=weekday(日期,2)
=”第”&weeknum(B5,2)&”周”&”第”&weekday(B5,2)&天
Text整容函数,真的变格式了!=text(日期,”aaaa”)四个a为星期几
假日期变为真日期20130601,=text(日期,”0000-00-00”)*1得到数值,然后改格式就得到日了
提取十位数,10,29,129,=mid(text(A3,”000”),2,1) =Text(A3,”上升0;下降0;不变;定”)
十四条件格式与公式
Text条件格式,=text(数据,”[条件]显示;[条件]显示;”)
开始,条件格式。找重复凭证号,重复值。空列也可以设置重复值显色''
数据透视表之后,金额分析时,条件格式,数据条。用产品类别分析,插入,切片器。切片器删除,选中,按Delete
条件格式,项目选取规则,选择标记前几或者后几。 多重条件,同时选出前几,后几
条件格式改错误为白色背景,新建规则,背景填充为白色
数量大于10,标出产品类别,选中产品类别,新建规则,公式,=D3>10,有时几列根据一列判断,混合引用,=$D3>100
十五简单文本函数(使用文本函数得到文本)
Left,Right文本函数,取字符数量一样的,从左边取,=left(A3,3)从左取三个字符。从右边取,=Right(A3,3)从右边取三个字符。
Mid,从中间截取文本,=mid(E3,从第几位开始,取几位),left,rifht 合用可以达到mid的效果,=right(left(A3,4),2),mid还可以取字符数不同的,=mid(A3,4,100)取姓名
18位身份证,前六位地区,中间八位为日期,最后三位为序列号(倒数第二位是性别位)。15位(最后一位是性别位),18位同时取性别位,=right(left(A3,17),1)。=
由身份证找地区=vlookup(left(身份证,6)*1,查找列和引用列,引用列所在列数,0) 求生日,=text(mid(身份证,7,8),”0000-00-00”)
判断性别=text((-1)^right(left(A3,17),1),”[<0]男;[>0]女”) 获取文本中的信息,find函数,,从左边取,只会找到第一个位数,=left
(A3,find(”@”,A3)-1),可用来找邮箱中姓名信息@前面是姓名拼音Find如何找第二及以后横线位置=find(“-”,A3,find(“-”,A3))取域名 =mid(A3,find(“@”,A3),100) Len()求字符长度的文本函数,各种符号都可以(包括汉字),Lenb(字节数)b,代表bit,求单位=right(A3,lenb(A3)-len(A3))
提取型号,非汉字部分=right(A3,2*len(A3)-lenb(A3))
十六数学函数
四舍五入函数,=round(number,四舍五入到第几位),进位函数=roundup(number,保留几位),舍尾函数=Rounddown(number,保留几位),取整函数=int(number)取小于等于它的整数
求余函数mod,=mod(被除数,除数),=mod(1.32,1)结果为0.32 计算休假天数,1.2=1,1.5=1.5,1.6=2
一=if(mod(number,1)>=0.5,int(number)+1,int(number))二int(number*2)/2 由身份证判断男女,=if(mod(right(left(A3),1),2)=1,”男”,”女“)
基于位置规律的引用,列变为行,复制,选择性粘贴,转置。Column()找列,row()找行。Row()行间隔为3则*3,column()列间隔为一则+1,发动机,=row()*3+column()+1
Row()跳跃*n,=row()*n+column()+?,调试一下制作发动机
十七 Lookup浅谈数组
多条件求和=sum((绝对引用区域=“广州”)*(绝对引用区域=”一科”)*金额),一般数组公式,敲击shift+Ctrl+Enter。=Sumproduct()数组sum。
=Lookup(找什么,在哪找,引用列),没有精确匹配,如何让lookup有精确匹配,利用它不查找错误,=lookup(1,0/($A$2:$A$34=”客户ID”),绝对引用引用列)
十八 indirect函数(间接引用)
=indirect(“e”&row*5-25) r1c1第一行第一列。后面的数字用match匹配
跨多表引用顺序相同=indirect(A4&”!g2”),不同时,=vlookup(“张三”,indirect(A4&”!A:G”),7,0)
有错误的话,=indirect(“''&A4&”’!g2”)
制作省份,地区下拉框,第一步,定义名称,数据有效性,序列,公式=indirect(F1)
十九图表基础(七块积木)
插入,图表,图表工具,布局。选中标题=A4,使标题动态化。Ctrl+c,Ctrl+v,可以做成锥形,心形。设置格式,填充,层叠。画个矩形,使心形离得远。
二十动态图表
开发工具,插入,复选框,右击可以改名称,用if函数,和定义名称(引用位置是if函数),系列值=sheet1!彩盒,可以制作简单的动态图表。
透视表中的=Offset(基准,下移几行,右移几列,取几行,取几列),取十一行,A列中所有非空值=offset($A$1,0,0,counta($A:$A),11)counta求某列的非空单元格个数。
查找也可用offset,=offset(基准,match(工号,区域,0),1,1,1)
开发工具,插入,滚动条,复制粘贴滚动条,设置空间格式,最小值为一,关联到一个单元格,定义名称,成交量,=offset($B$1,$D$2,0,$D$4,1),横轴,日期也用offset。
二十一创建甘特图(项目管理)
二十二PPT图表链接
如何将前后并行的柱状图设为左右相邻,右击,选择数据源,添加两次,系列值为零,任意选一个看不见的系列值,改为主坐标轴,然后选择数据源,把选择的系列之往前拿两次
美化饼图,右击,三维旋转,取消自动缩放,然后调整高度。右击,设置数据系列格式。 双层饼图(平面饼图),谁在前面先做谁,谁在前面谁为次要坐标。
Excel图表链接到PPT中问题解答,改变excel,ppt也改变,粘贴选项,粘贴为链接。
二十三宏表函数(不能直接写在单元格中,定义名称) 小技巧
批量新建工作表,表头,表的名称,以月份填充,数据透视表,分析,数据透视表,报
表筛选页,月份,删除已有数据,开始,编辑。
批量修改文件名,文件,打开,Ctrl+A,选中文件名复制到excel工作表,数据,分列,
复制,转置,快速填充,ren原名修改名,快速填充。复制到记事本(与要修改的文件在同一目录下),把记事本扩展名改为bat,运行
批量提取字母与数字,复制粘贴,两端对齐,筛选,大于等于吖(a),小于等于吖。 ? 批量插入图片及文件名,插入图片,Ctrl+A,复制文件名,插入,复制,转置,设置图片
格式与单元格行高列宽一样,属性,大小和位置随单元格而变,第一和最后一个放好,Ctrl+A,格式(图片工具),左对齐,纵向分布。插入一列,删除扩展名,用快速填充。 ? 让文本公式计算的三种方法,○1分列法,=”=”&A2,复制,粘贴成值,数据,分列,
完成○2lotus1-2-3法,数据,分列,完成○3宏表函数法,=evaluate(),这个函数要定义在名称管理器中。
工资条的制作,○1开发工具,录制宏。选中表头和其上面一行,相对引用,录制宏,
选表头和其下一行,Ctrl+Shift下拉一行,停止录制宏,开发工具,插入按钮,右键,指定宏。○2排序法,表头可以批量复制。
手动分组和自动分组,○1=A1,下拉填充,定位删除文字,求和,自动建立分级显示。○2用Ctrl+回车可以填充与上面一样,随意制作表头,新建工作表,数据,分级显示,取消明细数据的下方,合并数据,计数,选择数据源。筛选,删除无用数据。复制,粘贴成值。
特殊排序,按字数排序,辅助列=len()之后排序,二级目录排序1-1,1-2,2-1…复制一列,插入一个空列,分列(以--为分隔符),之后从后向前降序排序。 ? 数据,删除重复项;复制,合并计算,计数形式;
自动添加编号,=if(C3=””,””,counta(C$3:C3)),按类别添加编号,=countif(G$3:G7,G7) ? 中式排名1,1,2=SUMPRODUCT(($A$2:$A$7>A2)/COUNTIF($A$2:$A$7,$A$2:$A$7))+1 ? 非excel公式计算100*10,=“=”&A1,复制粘贴为值,分列就可以了。宏表函数,定义名称,=evaluate($A1),=名称。
利用错误值来自动分级,分级后显示的部分对应错误值,然后自动建立分级显示。 ? 合并计算统计地区销售和,
来自网站的数据导入excel,数据,自网站。
常用函数
Subtotal分类统计求和函数,可以对分类汇总后的数据求和(不显示的不求和),109-sum。
Subtotal可以跳过有subtotal处理的单元格求和
数据库函数,语法通用,函数名(数据区域,统计字段,条件区域) ?
教程目录:
1
第1讲:认识Excel 2010
第1讲主要讲解内容:
一 Excel软件简介
1、历史上的其他数据处理软件与Microsoft Excel
2、Microsoft Excel能做些什么
3、Excel界面介绍
二 Microsoft Excel中的一些重要概念
1、Microsoft Excel的几种常用文件类型:
a.XLS/XLSX 工作簿文件 b.XLW 工作区文件;
2、工作簿、工作表、单元格:
a.新建工作表 b.更改工作表名字及标签颜色
c.插入/删除多个工作表 d.插入行/列,插入多行/列,移动行/列,调整行高列宽
e.单元格选取、整行整列选取、数据区域选取
三 使用小工具:冻结窗格、填充柄
1、冻结窗格 2、填充柄 3.顺序填充
2
第2讲:单元格格式设置
第2讲主要讲解内容:
一、 使用单元格格式工具美化表格
1、"设置单元格格式"对话框在哪里?
2、"对齐"选项卡:设置文字对齐方式
3、"边框"选项卡:设置单元格边框
4、"字体"选项卡:设置字体颜色
5、"填充"选项卡:设置单元格背景颜色
二、 单元格数字格式
1、数字格式 类型 数值 货币 会计专用 日期 时间 百分比 分数 科学计数 文本 特殊 2、什么是自定义数字格式
3、利用自定义数字格式修改日期 2013/3/16
4、利用数字格式隐藏数据
5、选学内容:数字格
3
第3讲:查找、替换及定位
第3讲主要讲解内容:
一、 查找与替换
1、按值查找 2、按格式查找
3、是否开启单元格匹配
4、模糊查找 认识通配符 ? * ~
二、 定位工具
1、通过名称框定位单元格及区域位置
2、定义名称
3、使用“定位条件”解决以下问题
a、为有批注的单元格设置红色填充色 b、为有公式的单元格设置红色填充色
c、填充解除单元格合并后遗留的空白单元格 d、批量删除图片
4
第4讲:排序与筛选
第4讲主要讲解内容:
一、 排序
1、简单排序 2、多条件排序
3、2003版本中超过3个排序条件时如何处理
4、按颜色排序 5、自定义排序次序
6、利用排序插入行
二、 筛选
1、使用筛选 2、在筛选中使用多个条件
3、高级筛选:a、筛选不重复值 b、在高级筛选中使用常量条件区域
5
第5讲:分类汇总和数据有效性
第5讲主要讲解内容:
一、 分类汇总工具
1、认识分类汇总
2、使用分类汇总前先排序
3、分类汇总的嵌套
4、复制分类汇总的结果区域
5、使用分类汇总批量合并内容相同的单元格
二、 设置数据有效性
1、设置整数数据有效性
2、设置文本长度数据有效性
3、设置序列数据有效性
4、数据有效性的其他设置
a、输入法切换 b、单元格信息 c、单元格信息保护
6
第6讲:数据透视表
第6讲主要讲解内容:
数据透视表
1、创建数据透视表
2、更改数据透视表汇总方式
3、数据透视表中的组合
4、汇总多列数据
5、在透视表中使用计算
6、利用筛选字段自动创建工作表
7
第7讲:认识公式与函数
第7讲主要讲解内容:
一、 认识Excel公式
1、运算符
2、公式中的比较判断
3、运算符优先级
4、单元格引用
相对引用:A1 绝对引用:$A$1 混合引用:$A1 A$1
二、 认识函数
1、如何使用函数
等号开头 函数名在中间 括号结尾 括号中间写参数
2、学习以下函数
SUM
8
第8讲:IF函数
第8讲主要讲解内容:
一、 使用IF函数
1、IF函数的基本用法
函数语法:IF(logical_test,[value_if_true],[value_if_false])
2、IF函数的嵌套
3、如何尽量回避IF函数的嵌套
4、用IF函数处理运算错误 Iserror函数
二、 AND函数与OR函数
1、AND函数:表示“且”的关系
2、OR函数:表示“或”的关系
第9讲:countif函数
第9讲主要讲解内容:
一、使用Countif函数
1、Count函数
2、Countif函数语法 Countif(range,criteria)
3、Countif函数计算数值区间
4、Countif函数超过15位字符时的错误
二、常见应用示例
1、在数据区域中寻找重复数据
2、在数据有效性中使用Countif函数
3、在条件格式中使用Countif函数
三、2007及以上版本中的Countifs函数
Countifs(ceiteria_range1,criterial1,
第10讲:SUMIF函数
第10讲主要讲解内容:
一、 使用Sumif函数
1、Sumif函数语法 =sumif(range,criteria,[sum_range])
2、Sumif函数计算数值区间
3、Sumif函数超过15位字符时的错误
4、关于第三参数简写时的注意事项
5、在多列中使用Sumif函数
6、使用辅助列处理多条件的Sumif
7、Sumifs函数
8、复习数据有效性
第11讲:VLOOKUP函数
第11讲主要讲解内容:
一、 使用Vlookup函数
1、Vlookup函数语法
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
2、vlookup中使用通配符
3、vlookup模糊查找
4、使用isna函数处理数字格式引起的错误
5、Hlookup函数
第12讲:VLOOKUP嵌套MATCH返回多列
第12讲主要讲解内容:
一、回顾Vlookup函数
1 、Vlookup函数语法
VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
二、 Match与Index函数
1、函数语法
MATCH(lookup_value,lookup_array,[match_type])
INDEX(array,row_num,[column_num])
2、Match+Index与Vlookup函数比较
3、使用Match与Vlookup函数嵌套返回多列结果
4、认识column函数
第13讲:邮件合并-引用Excel数据
请注意:这个邮件合并视频实际上并非本套视频中的内容,而是几年前单独录制的视频教程,加入进来用以让大家了解Word中的邮件合并工具,如果只对Excel课程感兴趣,可以跳过此节,直接学习后续课程。 课程分上下两节,本次发布的视频为第一节。
第14讲:常用日期与时间运算
第14讲主要讲解内容:
一、 认识时间和日期
1、回顾日期格式
2、时间格式
3、基本的时间与日期运算
二、 日期函数
1、Year、Month、Day函数
2、Date函数
3、Datedif函数 Datedif(开始日期,结束日期,类型)
4、Weeknum Return_type &nb
第15讲:条件格式与公式
第15讲主要讲解内容:
一、 使用简单的条件格式
1、为特定范围的数值标记特殊颜色
2、查找重复值
3、为数据透视表中的数据制作数据条
二、 定义多重条件的条件格式 &
第16讲:简单文本函数
第16讲主要讲解内容:
一、 使用文本截取字符串
1、Left函数
2、Right函数
3、Mid函数 &n
第17讲:数学函数
第17讲主要讲解内容:
一、 认识函数
1、Round函数 Roundup函数 Rounddown函数 Int函数
2、Mod函数
3、Row函数与Column函数
第18讲:Lookup-浅谈数组
第18讲主要讲解内容:
一、 回顾统计函数
1、使用SUMIF函数
2、使用SUMIFS函数
二、 认识数组
1、数组生成原理
2、SUMPRODUCT函数
三、 LOOKUP函数基本应用
1、认识LOOKUP函数
http://study.163.com/course/courseLearn.htm?courseId=670032#/learn/video?lessonId=822502&courseId=670032