《MicrosoftExcelFortwodays.ppt》由会员分享，可在线阅读，更多相关《MicrosoftExcelFortwodays.ppt（37页珍藏版）》请在三一文库上搜索。
1、Microsoft Excel 2003 Skills Training,Instructor:董瑞营 Mail: MSN:dry123_,Catalog,公式与格式、函数 工作表及单元格的安全设置 数据的提取(数据筛选) 数据分析(数据透视表) 数据图表 Excel数据与PPT、Word的引用,Module 1： 公式和格式函数,公式的输入： 单元格的引用： 相对引用：A1 在行中复制时，行不动，列在动，在列中复制时，列不动，行在动. 绝对引用：$A$1 f4不能动的要加$ 混合引用：$A1 , A$1,适合向两个方向上复制.分别进行讨论，只讨论默认在动的，不讨论默认不动的。 名称引用：定位
2、功能（f5），用作公式中的参数(F3),跨工作表操作。 方法：选择要命名的区域，单击名称框输入名字： 不能以数字命名 不能以地址命名 名字中间不能有空格 名字写完后要enter确认 删除：insertnamedefinedelete,Module 1： 公式和格式函数,函数 =sum(a1,b5) =sum(a1:b5) =average() =averagea() =max() =min() =count()统计数字单元格的个数 =counta()统计数字及文本的个数 =countblank()统计空单元格的个数 =if(logical_test,Value_if_true,Value_if
3、_false) =Round(number,num_digits) if嵌套（一个IF最多可以嵌套7个if）共8个if 有几段条件，用几段条件减一个IF 条件判断时从一端判断到另一端，不能从中间开始,Module 1： 公式和格式函数,逻辑函数 =and(条件1，条件2) =or(条件1，条件2) =not(条件),Module 1： 公式和格式函数,=sumif(range, criteria,data_range) Range:条件所在的区域 Criteria:条件，表达式用” “=10” “=“&a1 “=“ & average(f:f) data_range：求和的区域。 =count
4、if(range,criteria) 判断是否重复： Datavalidationsettingscustom Toolsformula auditingshow formula auditingcircle invalid data,Module 1： 公式和格式函数,=Vlookup(lookup_value,table_array,col_index_num,range_lookup) lookup_value:查找的依据，它一定在数据源的第一列，且值唯一。 table_array：数据源，命名数据源。 col_index_num：返回被找信息所在的列数。 range_lookup:是否
5、精确匹配： true:-1相似匹配。 被找依据在被找表中要升序排放 返回小于或等于查找值的最接近值 常用于数值范围的查找 false:0 精确匹配。,Module 1： 公式和格式函数,Vlookup的嵌套 =IF($D4=“,“,VLOOKUP($D4,data,5,0) =trim():去除多余的空格。 =VLOOKUP(trim(A2),data,1,0) =iserror():判断单元格中是否存在错误 True:单元格中存在错误 False:单元格中不在错误,Module 1： 公式和格式函数,ISERROR与Vlookup =IF(ISERROR(VLOOKUP(TRIM($B22)
6、,产品!$B:$J,MATCH(D$21,产品!$B$1:$J$1,0),0)=TRUE,“,VLOOKUP(TRIM($B22),产品!$B:$J,MATCH(D$21,产品!$B$1:$J$1,0),0),Match：返回被找信息所在的位置,=Match(lookup_value,table_array,type) lookup_value:查找依据 table_array:一般要绝对引用，只能为一行数据或一列数据 Type: 1:返回小于或等于lookup_value的最接近的值，且Table_array中一定要升序排放 0:精确匹配： Table_array可以没有顺序 -1:返回大于
7、或等于lookup_value的最接近的值，且Table_array中一定要降序排放 =VLOOKUP($C4&$D4,订单与产品!$A:$K,MATCH(E$3,订单与产品!$A$1:$K$1,0),0),Module 1： 公式和格式函数,=right(text,num) =left(text,num) =Mid(text,start,num) =len():计算单元格中字符个数。 =SEARCH(find_text,within_text,start_num) = FIND(find_text,within_text,start_num) =date(year,month,day) =y
8、ear(),=month() =text(number,format),Module 1： 公式和格式函数,有效性 数值的唯一性： =COUNTIF($A$4:$A$18,A4)=1 下拉列表的制作： 内容来源于其他表格: 选择要制作下拉列表的内容区域，命名 选择要使用的区域，datavalidationallowlistsource,输入名字 内容自定义：选择要使用的区域，datavalidationallowlistsource,输入内容，任意两内容之间英文半角的逗号隔开 数值的效性： =SUM($H$4:$H$25)=5000 日期的有效性：yyyy-mm-dd yy-mmm-d mm/
9、dd/yyyy 分列：datatext to columnfixed width =date(year,month,day),Module 1： 公式和格式,条件格式 自身值决定自已的格式。 由公式来决定格式。 And(条件1，条件2，条件3) Or(条件1，条件2，条件3) 自动套用格式,Module 1： 公式和格式,格式化数字,Module 2： 保护工作表,单元格安全设置 单元格级的安全性： 只给出允许操作的区域： 选择允许别人操作的区域 formatcellsprotection 取消locked 选择有公式的且不用修改的区域 formatcellsprotection 选中lock
10、ed hidden toolsprotectionprotect sheet 输入密码，取消select locked cells复制框 光标定位到原始数据表，formatsheethide toolsprotectionprotect workbookstructure加密。,Module 2： 保护工作表,只保护指定的区域： 选择所有单元格 formatcellsprotection取消locked hidden 选择要保护的单元格区域 formatcellsprotection选中locked hidden 单击toolsprotectionallow users to edit ran
11、ges 单击”new”按钮，输入区域标题，设定区域的范围，输入区域password 单击”protect sheet”按钮，输入password 文件级的安全： toolsoptionssecuritypassword to open and password to modify,Module 3：数据查询,筛选：(按条件显示记录) 自动筛选：datafilterauto filter 指定内容筛选:可以跨列操作 取极端数据top 10：（只对数值性字段有效,不可以跨列操作） 自定义：（最多可以定义两个条件，and or） *:表示多个字符 ？：只表示一个字符,Module 3：数据查询,高级
12、筛选： 创建条件区域： 将条件中用到的列的标题复制到原表格以外的某处 在复制出的标题下方写条件。 如果条件写在同一行中为and 如果条件写在同一列中为 or Datafilteradvanced filter 去除重复记录：,Module 3：数据查询,去除重复数据： 光标定位到空的单元格中 Datafilteradvanced filter Action: copy to another location List range:选择要去除重复的数据 Criteria range:空 Copy to:指定一个空的单元格,Module 3：数据查询,Subtotal(founction_num,
13、ref) 与筛选一起使用. 1:Average 2:count 3:counta 4:max 5:min 9.:sum 数据库函数： =dsum(database,field,criteria) =daverage() =dmax() =dmin() =dcount() =dcounta(),Module 4：数据分析,数据前提： 列中数据为字段，每个字段只有一个字段名 行中数据为记录 表格中不能合并单元格 表格中不能有空白列，可以有空白单元格。,Module 4：数据分析,数据透视表（数据分析） 制作透视有的方法 给原始区域命名。 按向导操作： 透视表的布局： 分析出需求中要求计算的字段（d
14、ata） 分析出需求中要求显示字段（除去计算的都是要显示） 显示指定项page 显示多项或所有项row 如果row中有多项字段，可将一项放到column中形成交叉表 Page ,row,column中可以为空，但不能放重复的内容.data可以重复的，但不能为空.,Module 4：数据分析,向透视表中添加及删除字段： 添加：回到向导中添加(右击pivottable wizard) 删除：右击字段名选择hide 透视表中字段的组合： 日期型：YYYY-MM-DD MM/DD/YYYY 自动组合 被组合的字段中每个值均为日期型 被组合的字段中不能有空单元格 将要组合的字段放到行中。 =weeknu
15、m(date) 手动组合 先将行中只保留要组合的字段,其他字放到页中 选择要组合的内容，右击组合。 再将页字段中的内容放回到应在位置,Module 4：数据分析,数值型： 自动组合 被组合的字段中每个值均为数值型 被组合的字段中不能有空单元格 将要组合的字段放到行中。 手动组合 被组合的字段中每个值均为数值型 先将行中只保留要组合的字段 选择要组合的内容，右击组合。,Module 4：数据分析,向透视表中添加新的计算字段。,Module 4：数据分析,向透视表中添加计算项 透视表中不能有组合字段 光标要定位在计算项的字段中进行计算。,Module 4：数据分析,更改行中的汇总方式： 双击行中除
16、最后一个字段以外的字段名。 如果双击内容，展开及折叠。 如果要展开或折叠所有项：右击字段名group and show detailhide detail 显示极端数据： 双击行中需求中为主语的字段（为定语的字段要放到主语的前方，或页中）advancedtop 10 autoshow,Module 4：数据分析,透视表中百分比的显示：光标定位在data中要百分比显的字段中，右击field settings % of total 如果透视表中行中的一个字段占另一个行中字段百分比时，这两个字段要分别放到行中及列中。 % of row % of column Running total in,Mod
17、ule 4：数据分析,明细数据的产生：双击data中的值： 多维（四维）数据透视表的制作： 每张原始表格都要有交叉表的结构 要划分出表格的维数。,Module 5：数据透视图表,数据透视图的操作（F11） X轴（分类轴）：透视表中行中的内容。 Y轴（数值轴）：透视表中data中的内容。 图例：透视表中column中的内容。 页：透视表中的页。 柱形图看比较：（3种，图表图表类型） 折线图看趋势变化： 饼形图看份额比较（适合数据量较少的表格）。 双轴图：（大数据与小数据并存） 双轴折线 双轴柱线图 堆积图：,Module 5：数据透视图表,对图表Y轴的修改（双击y轴） 显示单位：scaledis
18、play units 修改主刻度：scalemajor unit 显示标签：numbergeneralcustom general”m” 修改Y轴与X轴的交汇点：scale: 对图表X轴的修改（双击X轴） 字体大小：font 文字方向：alignment 修改与Y轴的交汇点：scale,Module 5：数据透视图表,对数据系列格式的修改 修改数据系列间距：双击任意一个数据系列，optionsoverlap 修改数据系列的坐标轴：双击任意一个数据系列，axissecondary axis 修改数据系列的类型：双击一数据系列，chartchart type,Module 5：数据透视图表,透视表
19、的更新： 数值的修改：修改原始表格，回到透视表中右击refresh data 数据的插入：在原表格数据的中间插入，回到透视表中右击refresh data 数据的append: insertnamedefine将原数据表名子删除 ctrl+shift+8全选原表数据，以相同的名子命名。 回到透视表中右击refresh data,Module 5：数据透视图表,透视表格式的操作： 文本字段名子的修改：直接输入 数字格式的修改：右击field settingsnumber 底纹边框的修改：透视表工具栏的第二个按钮。 打印的设置： 打印方向：文件页面设置 调整打印内容：视图分页预览 打印行及列的标题：文件页面设置工作表标题,Module 6：Excel数据引用到PPT中,转化成PPT表格: CTRL+V 适合较少表格 嵌套到PPT幻灯片中：editpaste specialpastems excel worksheet 链接到PPT幻灯片中: 复制图片到PPT幻灯片中: *wmf *emf 图标的方式复制表格：,Module 7：Excel数据引用到Word中,同PPT的方法 邮件合并的方法,Q & A,