电子表格知识点总结
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
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)
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第1套电子表格课堂笔记
• 1、【套用表格格式】选中数据范围（注意：不能选中第一行 合并过的单元格）→开始→套用表格样式 • 2、【垂直查询函数VLOOKUP】“查找与引用”中。 VLOOKUP（匹配值（单元格）,查找范围（数据区域：范围 要固定，按F4绝对引用，笔记本电脑可能要同时按Fn+F4）, 返回列数（数字），0）
第15套电子表格课堂笔记
• （2）求“最高分”“最低分”“平均分” ①新建名为“班级汇总辅助”工作表→A1单元格→插入→数据透视表→表/ 区域：ctrl+A选中成绩单所有数据→行标签：学校班级，数值：三个“物 理”→点击“数值”中“求和项：物理”右侧倒三角箭头→值字段设置→计 算类型分别选择“最大值”“最小值”“平均值” ②“按班级汇总”工作表：D2=VLOOKUP(I2,班级汇总辅助!$A$4:$D$35,2,0)
第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套电子表格课堂笔记
• 3、【无条件求和SUM】SUM（求和范围） • 4、【多条件求和SUMIFS函数】“数学和三角函数”中。SUMIFS （求和范围，条件范围1，条件1，条件范围2，条件2，条件范围3， 条件3…） • 只要用sumifs求和必须先定义名称：ctrl+A选中这个数据表→公式 →根据所选内容创建→只勾上“首行”
班",IF(MID(B3,3,2)="03","法律三班","法律四班")))
• 3、【插入数据透视表（值字段设置）】（1）新建工作表单击右键重命 名、改表标签颜色（ 2）插入→数据透视表→（3）选择数据来源：选 中“2012级法律”A2:O102→确定→（4）根据提示拖动字段：“班级” 拖到“行标签”，“英语”拖到“数值”→（5）值字段设置：在“数 值”点击“求和项：英语”右边的倒三角箭头→值字段设置→“计算类 型”改为“平均值”，同样的方法将其他各科拖到“数值”并修改值字 段设置→（6）套用表格样式：选中透视表任一单元格→数据透视表工 具-设计→选择样式
第8套电子表格课堂笔记
• 1、【排名函数RANK.EQ】在“其他函数-统计”中。 RANK.EQ（排名对象，排名范围（F4绝对引用，笔记本 电脑Fn+F4），排名方式）
第8套电子表格课堂笔记
• 2、班级=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第2套电子表格课堂笔记
• 1、【设置单元格格式】选中单元格→单击右键→设置单元格格式 • 2、【改行高、列宽】选中单元格→开始→“单元格”工具组点击 “格式” • 3、【条件格式】：以不同的字体或填充颜色突出显示满足特定条 件的单元格。开始→条件格式→突出显示单元格规则→其他规则 →填写比较关系和数值→格式，设置单元格格式 • 4、【截取字符串函数MID】：在“文本”中。将其中几个字符从 一个文本中截取出来。mid(提取的文本，提取的起始位置，提取 的字符个数） • 5、【文本连接符&】将几个文本合并成一个 • 6、“班级”的公式=MID(A2,4,1)&"班" • 7、【工作表】复制、表标签颜色、重命名：在工作表上单击右键
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第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、【插入数据透视图】光标定位在数据透视表的任一单元格→插入 →柱形图→簇状柱形图
第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
第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.1105,IF(K3<=9000,K3*0.2-555,IF(K3<=35000,K3*0.251005,IF(K3<=55000,K3*0.3-2755,IF(K3<=80000,K3*0.355505,K3*0.45-13505))))))
第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套：一定要先“根据所选内容创建”定义名称
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)
第9套电子表格课堂笔记
• 4、【插入迷你图】选中2013年图书销售分析N4单元格→插入 →迷你图中的折线图→数据范围： B4:M4→迷你图工具-设计→ 勾上“高点”“低点”→向下填充 • 5、 【插入数据透视表（将所选内容分组）】注意：本题要求 透视表以A1单元格为起点，因此要将生成的透视表的第1、2行 单击右键删除。
“按学校汇总”：切换到“按班级汇总”工作表，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
第9套电子表格课堂笔记
• 1、【标记重复记录】开始→条件格式→突出显示单元格规则→重 复值→设置为→自定义格式→字体→标准紫色 • 2、【将紫色字体排在顶端】选中表格数据区域的任一单元格→数 据→排序→“主要关键字”：订单编号，排序依据：字体颜色， 次序：紫色、在顶端→次要关键字→订单编号、数值、升序。 • 3、SUMIFS求和一定要先定义名称：选中“销售订单”A2:G678 →公式→根据所选内容创建→只勾“首行” • 2013年图书销售分析B4=SUMIFS(销量_本,图书名称,A4,日 期,“>=2013-1-1”,日期,“<=2013-1-31”),其他单元格只需修改 日期范围（SUMIFS的具体用法参考第一套）
ຫໍສະໝຸດ Baidu
第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)
第2套电子表格课堂笔记
• 8、【分类汇总】先排序再分类汇总。选择C2单元格→数 据→排序→“主要关键字”选“班级”（按谁分类就按谁 排序）→数据→分类汇总→分类字段（班级），汇总方式 （平均值）→选定汇总项（勾选各科目，取消勾选其他） →勾选每组数据分页 • 9、【插入图表】按住ctrl键同时选择C1:J1，C8:J8， C15:J15，C22:J22→插入→图表→簇状柱形图→将Sheet2 重命名→将图表复制粘贴
第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套
第15套电子表格课堂笔记
• （3）“客观题平均分”“主观题平均分” “按班级汇总” G2=SUM(小分统计!C2:AP2)
H2=SUM(小分统计!AQ2:AZ2)
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第1套电子表格课堂笔记
• 1、【套用表格格式】选中数据范围（注意：不能选中第一行 合并过的单元格）→开始→套用表格样式 • 2、【垂直查询函数VLOOKUP】“查找与引用”中。 VLOOKUP（匹配值（单元格）,查找范围（数据区域：范围 要固定，按F4绝对引用，笔记本电脑可能要同时按Fn+F4）, 返回列数（数字），0）
第15套电子表格课堂笔记
• （2）求“最高分”“最低分”“平均分” ①新建名为“班级汇总辅助”工作表→A1单元格→插入→数据透视表→表/ 区域：ctrl+A选中成绩单所有数据→行标签：学校班级，数值：三个“物 理”→点击“数值”中“求和项：物理”右侧倒三角箭头→值字段设置→计 算类型分别选择“最大值”“最小值”“平均值” ②“按班级汇总”工作表：D2=VLOOKUP(I2,班级汇总辅助!$A$4:$D$35,2,0)
第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套电子表格课堂笔记
• 3、【无条件求和SUM】SUM（求和范围） • 4、【多条件求和SUMIFS函数】“数学和三角函数”中。SUMIFS （求和范围，条件范围1，条件1，条件范围2，条件2，条件范围3， 条件3…） • 只要用sumifs求和必须先定义名称：ctrl+A选中这个数据表→公式 →根据所选内容创建→只勾上“首行”
班",IF(MID(B3,3,2)="03","法律三班","法律四班")))
• 3、【插入数据透视表（值字段设置）】（1）新建工作表单击右键重命 名、改表标签颜色（ 2）插入→数据透视表→（3）选择数据来源：选 中“2012级法律”A2:O102→确定→（4）根据提示拖动字段：“班级” 拖到“行标签”，“英语”拖到“数值”→（5）值字段设置：在“数 值”点击“求和项：英语”右边的倒三角箭头→值字段设置→“计算类 型”改为“平均值”，同样的方法将其他各科拖到“数值”并修改值字 段设置→（6）套用表格样式：选中透视表任一单元格→数据透视表工 具-设计→选择样式
第8套电子表格课堂笔记
• 1、【排名函数RANK.EQ】在“其他函数-统计”中。 RANK.EQ（排名对象，排名范围（F4绝对引用，笔记本 电脑Fn+F4），排名方式）
第8套电子表格课堂笔记
• 2、班级=IF(MID(B3,3,2)="01","法律一班",IF(MID(B3,3,2)="02","法律二
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第2套电子表格课堂笔记
• 1、【设置单元格格式】选中单元格→单击右键→设置单元格格式 • 2、【改行高、列宽】选中单元格→开始→“单元格”工具组点击 “格式” • 3、【条件格式】：以不同的字体或填充颜色突出显示满足特定条 件的单元格。开始→条件格式→突出显示单元格规则→其他规则 →填写比较关系和数值→格式，设置单元格格式 • 4、【截取字符串函数MID】：在“文本”中。将其中几个字符从 一个文本中截取出来。mid(提取的文本，提取的起始位置，提取 的字符个数） • 5、【文本连接符&】将几个文本合并成一个 • 6、“班级”的公式=MID(A2,4,1)&"班" • 7、【工作表】复制、表标签颜色、重命名：在工作表上单击右键
分类汇总 特立独行 侠 单纯的函 数题
插入图表 从外部导 入数据
数据透视 表
第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、【插入数据透视图】光标定位在数据透视表的任一单元格→插入 →柱形图→簇状柱形图
第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
第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.1105,IF(K3<=9000,K3*0.2-555,IF(K3<=35000,K3*0.251005,IF(K3<=55000,K3*0.3-2755,IF(K3<=80000,K3*0.355505,K3*0.45-13505))))))
第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套：一定要先“根据所选内容创建”定义名称
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)
第9套电子表格课堂笔记
• 4、【插入迷你图】选中2013年图书销售分析N4单元格→插入 →迷你图中的折线图→数据范围： B4:M4→迷你图工具-设计→ 勾上“高点”“低点”→向下填充 • 5、 【插入数据透视表（将所选内容分组）】注意：本题要求 透视表以A1单元格为起点，因此要将生成的透视表的第1、2行 单击右键删除。
“按学校汇总”：切换到“按班级汇总”工作表，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
第9套电子表格课堂笔记
• 1、【标记重复记录】开始→条件格式→突出显示单元格规则→重 复值→设置为→自定义格式→字体→标准紫色 • 2、【将紫色字体排在顶端】选中表格数据区域的任一单元格→数 据→排序→“主要关键字”：订单编号，排序依据：字体颜色， 次序：紫色、在顶端→次要关键字→订单编号、数值、升序。 • 3、SUMIFS求和一定要先定义名称：选中“销售订单”A2:G678 →公式→根据所选内容创建→只勾“首行” • 2013年图书销售分析B4=SUMIFS(销量_本,图书名称,A4,日 期,“>=2013-1-1”,日期,“<=2013-1-31”),其他单元格只需修改 日期范围（SUMIFS的具体用法参考第一套）
ຫໍສະໝຸດ Baidu
第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)
第2套电子表格课堂笔记
• 8、【分类汇总】先排序再分类汇总。选择C2单元格→数 据→排序→“主要关键字”选“班级”（按谁分类就按谁 排序）→数据→分类汇总→分类字段（班级），汇总方式 （平均值）→选定汇总项（勾选各科目，取消勾选其他） →勾选每组数据分页 • 9、【插入图表】按住ctrl键同时选择C1:J1，C8:J8， C15:J15，C22:J22→插入→图表→簇状柱形图→将Sheet2 重命名→将图表复制粘贴
第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套