电子表格知识点总结（PPT50页)

分类汇总单纯的函数题数据透视表从外部导入数据插入图表特立独行侠第2套电子表格课堂笔记•1、【设置单元格格式】选中单元格→单击右键→设置单元格格式•2、【改行高、列宽】选中单元格→开始→“单元格”工具组点击“格式”•3、【条件格式】：以不同的字体或填充颜色突出显示满足特定条件的单元格。开始→条件格式→突出显示单元格规则→其他规则→填写比较关系和数值→格式，设置单元格格式•4、【截取字符串函数MID】：在“文本”中。将其中几个字符从一个文本中截取出来。mid(提取的文本，提取的起始位置，提取的字符个数）•5、【文本连接符&】将几个文本合并成一个•6、“班级”的公式=MID(A2,4,1)&班•7、【工作表】复制、表标签颜色、重命名：在工作表上单击右键第2套电子表格课堂笔记•8、【分类汇总】先排序再分类汇总。选择C2单元格→数据→排序→“主要关键字”选“班级”（按谁分类就按谁排序）→数据→分类汇总→分类字段（班级），汇总方式（平均值）→选定汇总项（勾选各科目，取消勾选其他）→勾选每组数据分页•9、【插入图表】按住ctrl键同时选择C1:J1，C8:J8，C15:J15，C22:J22→插入→图表→簇状柱形图→将Sheet2重命名→将图表复制粘贴第7套电子表格课堂笔记•1、【单元格合并后居中】选中A1：M1→开始→“对齐方式”工具组→合并后居中•2、【自动填充序列】第一种方法：在A3单元格输入“1”→右下角双击左键自动填充→“自动填充选项”选择“填充序列”第二种方法：A3输入“1”，A4输入“2”→选中A3和A4→右下角双击左键自动填充•3、【纸张大小、方向】页面布局选项•4、【逻辑判断函数IF】逻辑判断，如果…那么…否则。IF（判断条件，条件成立返回的结果，条件不成立返回的结果）•5、【应交个人所得税公式】•=IF(K3=1500,K3*0.03-0,IF(K3=4500,K3*0.1-105,IF(K3=9000,K3*0.2-555,IF(K3=35000,K3*0.25-1005,IF(K3=55000,K3*0.3-2755,IF(K3=80000,K3*0.35-5505,K3*0.45-13505))))))第12套电子表格课堂笔记•1、【主题】页面布局选项卡•2、【月份提取函数MONTH】在“日期和时间”中。返回日期的月份值。MONTH（要提取月份的单元格）•3、季度=IF(MONTH(A3)=3,1季度,IF(MONTH(A3)=6,2季度,IF(MONTH(A3)=9,3季度,4季度)))•4、【插入图表】按住ctrl键同时选择B2:M2,B6:M6,B10:M10,B14:M14,B18:M18→插入→图表→带数据标记的折线图→将图表放大，找到每个项目的最高点（点击两次左键，第一次是选中所有点，第二次选中最高点）→→图表工具-布局→数据标签→上方→后面的每个项目最高点操作如上→将图表复制粘贴分类汇总单纯的函数题数据透视表从外部导入数据插入图表特立独行侠第1套电子表格课堂笔记•1、【套用表格格式】选中数据范围（注意：不能选中第一行合并过的单元格）→开始→套用表格样式•2、【垂直查询函数VLOOKUP】“查找与引用”中。VLOOKUP（匹配值（单元格）,查找范围（数据区域：范围要固定，按F4绝对引用，笔记本电脑可能要同时按Fn+F4）,返回列数（数字），0）第1套电子表格课堂笔记•3、【无条件求和SUM】SUM（求和范围）•4、【多条件求和SUMIFS函数】“数学和三角函数”中。SUMIFS（求和范围，条件范围1，条件1，条件范围2，条件2，条件范围3，条件3…）•只要用sumifs求和必须先定义名称：ctrl+A选中这个数据表→公式→根据所选内容创建→只勾上“首行”第5套电子表格课堂笔记•1、【自定义单元格格式】选中日期列→单击右键→设置单元格格式→自定义→yyyy年m月d日后加“aaaa”•2、【提取星期函数WEEKDAY】在“日期和时间”中。返回一周中第几天的数值。是一个1-7之间的整数。WEEKDAY（提取日期的单元格，返回类型）•3、是否加班=IF(WEEKDAY(A3,2)=6,是,否)•4、地区=MID(C3,1,3)•5、VLOOKUP、SUMIFS用法参考第1套分类汇总单纯的函数题数据透视表从外部导入数据插入图表特立独行侠第6套电子表格课堂笔记•1、【输入001、002…的序号】先将单元格格式设置为文本→输入001→右下角双击左键自动填充•2、【为数据区域定义名称】选中B3:C7→左上角名称框中输入“商品均价”→回车符确定（或单击右键→定义名称）•3、销售额==E4*VLOOKUP(D4,商品均价,2,0)•4、【插入数据透视表（筛选）】（1）新建工作表单击右键重命名→选中A1单元格→（2）插入→数据透视表→（3）选择数据来源：表/区域:“销售情况表”$A$3:$F$83→确定→（4）根据提示拖动字段：“商品名称”拖到“报表筛选”，“店铺”拖到“行标签”，“季度”拖到“列标签”，“销售额”拖到“数值”→（5）筛选：点击“全部”右边的倒三角箭头→选择“笔记本”•5、【插入数据透视图】光标定位在数据透视表的任一单元格→插入→柱形图→簇状柱形图第8套电子表格课堂笔记•1、【排名函数RANK.EQ】在“其他函数-统计”中。RANK.EQ（排名对象，排名范围（F4绝对引用，笔记本电脑Fn+F4），排名方式）第8套电子表格课堂笔记•2、班级=IF(MID(B3,3,2)=01,法律一班,IF(MID(B3,3,2)=02,法律二班,IF(MID(B3,3,2)=03,法律三班,法律四班)))•3、【插入数据透视表（值字段设置）】（1）新建工作表单击右键重命名、改表标签颜色（2）插入→数据透视表→（3）选择数据来源：选中“2012级法律”A2:O102→确定→（4）根据提示拖动字段：“班级”拖到“行标签”，“英语”拖到“数值”→（5）值字段设置：在“数值”点击“求和项：英语”右边的倒三角箭头→值字段设置→“计算类型”改为“平均值”，同样的方法将其他各科拖到“数值”并修改值字段设置→（6）套用表格样式：选中透视表任一单元格→数据透视表工具-设计→选择样式第9套电子表格课堂笔记•1、【标记重复记录】开始→条件格式→突出显示单元格规则→重复值→设置为→自定义格式→字体→标准紫色•2、【将紫色字体排在顶端】选中表格数据区域的任一单元格→数据→排序→“主要关键字”：订单编号，排序依据：字体颜色，次序：紫色、在顶端→次要关键字→订单编号、数值、升序。•3、SUMIFS求和一定要先定义名称：选中“销售订单”A2:G678→公式→根据所选内容创建→只勾“首行”•2013年图书销售分析B4=SUMIFS(销量_本,图书名称,A4,日期,“=2013-1-1”,日期,“=2013-1-31”),其他单元格只需修改日期范围（SUMIFS的具体用法参考第一套）第9套电子表格课堂笔记•4、【插入迷你图】选中2013年图书销售分析N4单元格→插入→迷你图中的折线图→数据范围：B4:M4→迷你图工具-设计→勾上“高点”“低点”→向下填充•5、【插入数据透视表（将所选内容分组）】注意：本题要求透视表以A1单元格为起点，因此要将生成的透视表的第1、2行单击右键删除。第13套电子表格课堂笔记•1、【收费标准】=VLOOKUP(C2,收费标准!$A$3:$B$5,2,0)（查找范围要固定，F4绝对引用，笔记本电脑按Fn+F4）•2、【向上舍入函数ROUNDUP】将数字向上舍入为最接近的整数。ROUNDUP（要向上舍入的数字，舍入后保留的小数位数）•停放时间J2=H2-F2+I2-G2→右键→设置单元格格式→时间：选择“13时30分”→自定义→[hh]小时mm分钟;@•收费金额=ROUNDUP(J2/00:01:00/15,0)*E2•拟收费金额=ROUNDDOWN(J2/00:01:00/15,0)*E2•收费差值=L2-K2第14套电子表格课堂笔记•1、【删除重复记录】数据→删除重复项→取消全选→勾上“订单编号”•2、【四舍五入函数ROUND】ROUND（要四舍五入的数字，舍入后保留的小数位数）•销售额小计=ROUND(IF(F3=40,E3*0.93*F3,E3*F3,2)•所属区域=VLOOKUP(MID(G3,1,3),表3,2,0)•3、【插入数据透视表（筛选）】新建名为“北区”的工作表→在A1单元格→插入→数据透视表→表/区域:订单明细!A2:I636→数据筛选：所属区域，行标签：图书名称，数值：销售额→点击“全部”后面的倒三角箭头→勾选“北区”→选择“北区”工作表→右键→移动或复制→移至最后，勾上“建立副本”→将副本重命名，分别筛选所属区域•4、【同时选中多张工作表】选中“北区”，按住shift，在选择“东区”，则同时选中中间的所有表→选中“销售额”列，设置单元格格式•5、SUMIFS用法参考第1套：一定要先“根据所选内容创建”定义名称第15套电子表格课堂笔记•1、【条件计数COUNTIF】统计区域中满足条件的单元格数目。在“其他函数-统计”中。COUNTIF（条件范围，条件）•2、【单条件求和SUMIF】对满足单一条件的数据区域求和。SUMIF（条件范围，条件，求和范围）（注意SUMIF和SUMIFS参数位置的区别）•3、解题步骤：•（1）求考试学生数“成绩表”工作表E1单元格输入“学校班级”E2=A2&B2，向下填充“按班级汇总”I1单元格输入“学校班级”，I2=A2&B2，向下填充“按班级汇总”C2=COUNTIF(成绩单!$E$2:$E$950,I2)“按学校汇总”B2=COUNTIF(成绩单!$A$2:$A$950,A2)第15套电子表格课堂笔记•（2）求“最高分”“最低分”“平均分”①新建名为“班级汇总辅助”工作表→A1单元格→插入→数据透视表→表/区域：ctrl+A选中成绩单所有数据→行标签：学校班级，数值：三个“物理”→点击“数值”中“求和项：物理”右侧倒三角箭头→值字段设置→计算类型分别选择“最大值”“最小值”“平均值”②“按班级汇总”工作表：D2=VLOOKUP(I2,班级汇总辅助!$A$4:$D$35,2,0)E2=VLOOKUP(I2,班级汇总辅助!$A$4:$D$35,3,0)F2=VLOOKUP(I2,班级汇总辅助!$A$4:$D$35,4,0)③新建名为“学校汇总辅助”工作表→插入→数据透视表，操作同上，只需将“行标签”改为“学校名称”④“按学校汇总”工作表：C2=VLOOKUP(A2,学校汇总辅助!$A$4:$D$7,2,0)D2=VLOOKUP(A2,学校汇总辅助!$A$4:$D$7,3,0)E2=VLOOKUP(A2,学校汇总辅助!$A$4:$D$7,4,0)第15套电子表格课堂笔记•（3）“客观题平均分”“主观题平均分”“按班级汇总”G2=SUM(小分统计!C2:AP2)H2=SUM(小分统计!AQ2:AZ2)“按学校汇总”：切换到“按班级汇总”工作表，J1单元格输入“客观题班级总分”，K1输入“主观题班级总分”，J2=C2*G2，K2=C2*H2，向下填充→“按学校汇总”F2=SUMIF(按班级汇总!$A$2:$A$33,A2,按班级汇总!$J$2:$J$33)/B2，G2=SUMIF(按班级汇总!$A$2:$A$33,A2,按班级汇总!$K$2:$K$33)/B2第15套电子表格课堂笔记•（4）计算每题得分率•选中“小分统计”工作表→单击右键→移动或复制→移至最后，勾上“建立副本”→将副本重命名为“小分总和”→C2=小分统计!C2*按班级汇总!$C2→向下向右复制公式→切换到“按学校汇总”→H2=SUMIF(小分总和!$A$2:$A$33,$A2,小分总和!C$2:C$33)/$B2/分值表!B$3，向右向下复制公式（提示：注意绝对符号的使用，谁不变在谁前面加$）•（5）转置粘贴•选中数据范围复制→开始→粘贴选项下拉→选择性粘贴→选择“值和数字格式”，