Excel常用功能和常用函数学习笔记

Excel学习笔记

——基于Excel for Mac 2016

第一章 Excel常用功能

第1节 认识excel

• 快速插入N行：选择N行（或列）-右键-插入，即可插入N行（或列）
• 移动列：选择列-按住shift-鼠标移动到列边缘-拖拽，即可移动列
• 自动调整多列列款：选中多列-双击某列边线
• 快速定位到表格首末：在有数据的某个单元格-定位到单元格边缘（上／下／左／右）-双击
• 快速选择行：在名称框中输入，如2:10，即可快速选中2-10行

 

第2节 Excel格式设置

• 跨越合并：分别合并多行单元格
• 带斜线表头：右键-设置单元格格式-边框-斜线
• 自定义格式：mmm-英文月份简写；mmmm-英文月份；aaa-周几；aaaa-星期几；ddd-英文星期简写；dddd-英文星期
• 将文本转换为数值：在一个单元格中输入“1”-复制该单元格-选中需要转换的区域-右键-选择性粘贴-选择操作中的“乘”-确定
• 数据-分列的使用：可以修改单元格的文本和常规格式

 

第3节 Excel查找、替换和定位

• 查找和替换通配：“*”（星号）-匹配任意字符；“?”-匹配任意一个字符
• 快速选择区域：在名称框输入A10:C20-选择A10到C20的区域
• 查找替换批注：查找替换的条件选择批注即可
• 填充区域：选中区域-输入值-按住ctrl+enter
• 让所有空值等于其上面的值：选中区域-查找空值-输入“=”-ctrl+上方向键-ctrl+enter
• 快速选中表格区域：ctrl+shift+上方向键+下方向键

 

第4节 Excel排序和筛选

• 简单排序：选中表格中所排序列中的一个数据-选择开始菜单-排序和筛选-升序（或降序），即可完成整个表格的排序。注意：不要选择某一列单独进行排序，可能导致数据错乱。
• 多条件排序：选中表格中的一个数据-开始菜单-排序和筛选-自定义排序-选择排序依据-增加排序依据-确定
• 多条件排序（2）：按照排序主次要关键字的相反顺序，逐次进行简单排序，即可实现多条件排序
• 按颜色排序：同多条件排序，排序依据选择“单元格颜色”即可
• 自定义排序：开始-排序和筛选-自定义排序-顺序选择“自定义列表”，即可按照自定义的列表进行排序
• 利用排序插入行：进行隔行插入：把需要隔行插入的内容复制到列表下方-在列表右侧插入一列-列中填充入可以交叉排序的数据（其实就是两段序列）-再针对插入的列做一次简单排序即可
• 打印时，每页都打印标题行：选择标题行-在名称框中输入“Print_Titles”-打印时就会自动在每页打印标题行
• 数字筛选：筛选满足数据要求（等于、不等于、大于、小于等）的数据
• 文本筛选：筛选文本满足要求（开头是、结尾是、包含、不包含等）的数据
• 筛选不重复记录：数据选项卡-筛选右下角的高级-[选中“将筛选结果复制到其他位置”]-选择要筛选的数据-[选择“复制到”（某个空白区域的单元格）]-勾选上“选择不重复的记录”-确定
• 高级筛选，条件区域说明：1）如果条件区域是固定值，条件区域必须写明表头；但是条件区域是公式时，表头为空即可；2）筛选条件写在表头下方，并且多个筛选条件在同一行表示条件之间是“且”关系，不再同一行表示条件之间是“或”关系；3）条件中可以直接输入“>”、“<”等条件符号进行筛选，也可以使用公式；4）有几行条件区域即表示有几种筛选几种类型的数据

 

第5节 分类汇总和数据有效性

• 分类汇总：【注意】在进行分类汇总之前，要先对汇总的字段进行排序。数据-分类汇总（office 2016 for Mac是“小记”）-选择分类字段-选择汇总方式-选定汇总项-确定
• 两层分类汇总：首先进行两个汇总字段的排序（见多条件排序）-然后进行两次分类汇总，注意在第二次分类汇总时，一定要去勾选“替换当前分类汇总”
• 复制分类汇总的结果：（office 2016 for Mac版）选择所要复制的区域-编辑-查找-定位-定位条件-选择“仅可见单元格”-复制
• 批量合并内容相同的单元格：对要合并的列排序-进行分类汇总（分类字段：要合并的列；汇总方式：计数；选定汇总项：要合并的列）-选中分类汇总的列查找空值（编辑-查找-定位-定位条件-选择“空值”）-合并单元格-删除分类汇总-复制分类汇总所留下的列格式到需要合并到列（选择合并后的列-复制-选择性粘贴-格式）
• 设置数据有效性：选择需要设置数据有效性区域-数据-数据验证（数据有效性）-设置，即可数据有效性为整数、十进制、列表、序列（序列的源，可选项之间必须是英文的逗号分隔）和文本长度等。
• 利用数据有效性进行信息保护：设置数据有效性-允许的值选择“自定义”-公式输入“FALSE”

 

第6节 Excel数据透视表

• 基本概念：【字段】一列表示的数据；【记录】一行所表示的数据
• 创建基础数据透视表：选择待统计区域任意单元格-插入-数据透视表-确定；将字段名称分别拖入行、列和值，即可创建完成基础透视表创建。数据透值表的值可以进行求和、计数和求平均值等
• 创建两个行字段的数据透视表：方式同创建基础数据透视表，只是将两个字段名称拖入行即可
• 数据透视表行标签创建组：当行标签的数据统计太详细时，可以通过对行标签创建组做一些更粗粒度的统计。具体做法：创建好数据透视表之后，选中需要进行创建组的数据-邮件-创建组，然后根据需要进行创建组即可。（Excel for Mac 2016中，创建组是在选中需要创建组的数据之后，在“数据透视表分析”页签，点击“组选择”菜单）。分组有多种方式，可以对日期进行分组，也可以对数值进行分组
• 对数据透视表进行计算（如，计算利润率）：生成数据透视表-选中数据透视表的数据-“数据透视表分析”页签-单击“字段、项目和集”-选择“计算字段”-输入“名称”-输入“公式”-确定
• 数据透视表数据格式化错误显示：选中透视表数据单元格-右键-数据透视表选项-勾选“对于错误值，显示为”-[输入显示值]-确定

 

第二章 Excel函数与公式

第7节 认识Excel函数与公式

• 公式：excel单元格中，以“=”号开始的表达式
• 基础运算符：【算数运算符】+（加），-（减），*（乘），／（除），%（除100），&（连字符）；【比较运算符】>，<，=，>=，<=，<>（不等于）。注：比较运算得到的逻辑结果（TRUE或FALSE），可以被当做1（TRUE）或0（FALSE）进行数值计算
• 公式中的文本：公式中的文本需要用英文双引号括起来
• 单元格引用：【相对引用】如A1，复制包含该引用的单元格时，行列引用都会随单元格位置而变化；【绝对引用】如$A$1，复制包含该引用的单元格时，行列引用都不会随单元格位置而变化（需要使用绝对引用时，可以选中公式中的区域表达式，然后按F4键；当然，也可以直接在区域表达式中输入“$”）；【混合引用】如$A1，A$1，复制包含该引用的单元格时，只有列（$A1）或者行（A$1）的引用会随单元格的位置而变化
• 基础函数：【求和】sum()；【平均值】average()；【最大值】max()；【最小值】min()；【排名】rank()
• 函数帮助手册：开始-点击求和符号右下角的三角形-更多函数，然后找到对应函数，即可查看该函数的作用和用法
• TIPS：按“ESC”键可推出公式输入模式

 

第8节 IF函数

• 函数语法：IF(logical_test,[value_if_true],[value_if_false])，判断是否满足某个条件，如果满足返回一个值，如果不满足则返回另一个值。注意：函数中涉及字符串时，需要用“"”（英文双引号）括起来；并且函数的参数之间用“,”（英文逗号）分隔
• IF函数嵌套：IF函数的参数可以再包含IF函数，示例：=IF(B2="理工","LG",IF(B2="文科","WK","CJ"))
• 针对连续的数据区间判断：如果要针对连续的数据进行不同的区间判断，可以从大到小（或从小到大）进行判断，示例：判断I9单元格的值，如果大于等于600，显示为“第一批”；如果大于等于400并且小于600，显示为“第二批”；如果小于400，显示为“落榜”。结果：=IF(I9>=600,"第一批",IF(I9>=400,"第二批","落榜"))
• 避免多重IF函数嵌套：如果IF条件太多，应该尽量避免多重IF函数嵌套，并使用VLOOKUP函数替代（见后面相关章节）
• ISERROR函数：结合IF函数，处理公式计算中的出错显示，示例：如果D35/C35出错，则显示为“-”；结果：=IF(ISERROR(D35/C35),"-",D35/C35)
• AND函数：AND(logical1,logical2,…)，检查是否所有参数均为 TRUE，如果所有参数值均为 TRUE，则返回 TRUE。其中，logical1,logical2,... 是 1 到 255 个结果为 TRUE 或 FALSE 的检测条件，检测内容可以是逻辑值、数组或引用
• OR函数：OR(logical1,logical2,…)，如果任一参数值为 TRUE，即返回 TRUE；只有当所有参数值均为 FALSE 时才返回 FALSE。logical1,logical2,... 1 到 225 个结果是 TRUE 或 FALSE 的检测条件

 

第9节 COUNTIF函数

• COUNTIF(range,criteria)函数：计算某个区域中满足给定条件的单元格数目。range: 要计算其中非空单元格数目的区域；criteria: 以数字、表达式或文本形式定义的条件
• 判断某区域，值大于特定值时进行计数：例如，需要计数及格科目数；示例：=COUNTIF(B2:G2,">=60")
• TIPS：如果单元格数字长度超过15位，excel函数将只取其前15位进行比较，15位之后的数字会默认忽略；为避免该问题影响COUNTIF函数的使用，可以在COUNTIF函数的条件区域连接一个“*”（星号）。示例：=COUNTIF($A$8:$A$20,A8&”*”)
• 【应用一】判断一个列表中的值是否出现在另一个列表中。示例：=IF(COUNTIF(G:G,A2)=1,"是","否")
• 条件格式：开始-条件格式-突出显示单元格规则-其他规则-选择“使用公式确定要设置格式的单元格”-输入公式内容-设置格式-确定
• 【应用二】利用COUNTIF函数，设置某区域单元格不允许输入重复值。示例：=COUNTIF(C:C,C2)<2
• COUNTIFS(criteria_range1,criteria1[,criteria_range2,criteria2,…])函数：统计一组给定条件所指定的单元格数。

 

第10节 SUMIF函数

• SUMIF(range,criteria,sum_range)：对满足条件的单元格求和。range: 要进行计算的单元格区域。criteria: 以数字、表达式或文本形式定义的条件。sum_range: 用于求和计算的实际单元格，如果省略，将使用区域中的单元格。
• 应用示例：=SUMIF(D:D,H4,F:F)
• SUMIF函数的sum_range参数简写：如，=SUMIF(D:D,H4,F1)，即sum_range参数只指定与range相对应的第一个单元格。【注意】简写sum_range参数时，如果其指定单元格与range不对应，则可能出现求和不准确的情况
• SUMIFS(sum_range,criteria_range,criteria,…)：对一组给定条件指定的单元格求和。【注意】与SUMIF函数不同，SUMIFS函数的第一个参数即为求和区域
• TIPS：如果查找区域中的值唯一，并且查找的值为数字，则SUMIF可以替换VLOOKUP函数做数据查找
• 利用SUMIF函数设置数据有效性：如，=SUMIF(F:F,F8,G:G)<=SUMIF(A:A,F8,B:B)

 

第11节 VLOOKUP函数

• VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)：搜索表区域首列满足条件的元素，确定待检索单元格在区域中的行序号，再进一步返回选定单元格的值。默认情况下，表是以升序排序的。lookup_value: 需要在数据表首列进行搜索的值，lookup_value 可以是数值、引用或字符串。table_array: 需要在其中搜索数据的信息表。Table-array 可以是对区域或区域名称的引用。col_index_num: 满足条件的单元格在数组区域 table_array 中的列序号。首列序号为 1。range_lookup: 指定在查找时是要求精确匹配，还是大致匹配。如果为 FALSE，大致匹配。如果为 TRUE 或忽略，精确匹配
• 通配符查找：要进行搜索的值（lookup_value），在信息表中（table_array）不是完整值时，可以将搜索值连接一个通配符进行查找。示例：=VLOOKUP(A2&”*”,数据源!B:E,4,TRUE)
• VLOOKUP函数大致匹配（模糊匹配）的应用情况：给定一些区间的等级划分，然后要针对区间中的一些值进行查找计算时，使用VLOOKUP大致匹配（模糊匹配）。注意：绝大多数情况下，VLOOKUP函数应该使用精确匹配
• TIPS：1）如果要进行搜索的值（lookup_value）是数字，而信息表（table_array）中是文本，进行查找时可以如是处理：=VLOOKUP(F4&”*”,$A$2:$C$6,3,TRUE)。2）如果要进行搜索的值（lookup_value）是文本，而信息表（table_array）中是数字，进行查找时可以如是处理：=VLOOKUP(F12*1,$A$10:$C$14,3,TRUE)
• ISNA(value)函数：检测一个值是否为 #N/A，返回 TRUE 或 FALSE。value: 检测值。检测值可以是一个单元格、公式，或者是一个单元格、公式，或数值的名称
• HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)：搜索数组区域首行满足条件的元素，确定待检索单元格在区域中的列序号，再进一步返回选定单元格的值。lookup_value: 需要在数据表首行进行搜索的值，可以是数值、引用或字符串。table_array: 需要在其中搜索数据的文本、数据或逻辑值表。Table_array 可为区域或区域名的引用。row_index_num: 满足条件的单元格在数组区域 table_array 中的行序号。表中第一行序号为 1。range_lookup: 逻辑值: 如果为 TRUE 或忽略，在第一行中查找最近似的匹配；如果为 FALSE，查找时精确匹配

 

第12节 MATCH函数与INDEX函数

• MATCH(lookup_value,lookup_array,match_type)：返回符合特定值特定顺序的项在数组中的相对位置。lookup_value: 在数组中所要查找匹配的值，可以是数值、文本或逻辑值，或者对上述类型的引用。lookup_array: 含有要查找的值的连续单元格区域，一个数组，或是对某数组的引用。match_type: 数字 -1、 0 或 1。 Match_type 指定了 Microsoft Excel 将 lookup_value 与 lookup_array 中数值进行匹配的方式
• INDEX(…)：在给定的单元格区域中，返回特定行列交叉处单元格的值或引用。array: 单元格区域或数组常量。row_num: 数组或引用中要返回值的行序号。如果忽略，则必须有 Column_num 参数。column_num: 数组或引用中要返回值的列序号。如果忽略，则必须有 Row_num 参数
• 利用MATCH函数和INDEX函数实现VLOOKUP函数的功能：=INDEX(数据源!B:B,MATCH(A2,数据源!A:A,0))
• COLUMN(reference)：返回一引用的列号。reference: 准备求取其列号的单元格或连续的单元格区域；如果忽略，则使用包含 COLUMN 函数的单元格。
• MATCH函数与VLOOKUP函数组合使用：=VLOOKUP($A3,数据源!$A:$K,MATCH(B$2,数据源!$A$1:$K$1,0),0)

 

第13节 邮件合并引用Excel数据

• 利用Excel数据，批量生成word文档：新建空白文档-单击“邮件”选项卡-单击“开始邮件合并”下拉列表-选择“信函”-单击“选择收件人”下拉列表-选择“使用现有列表”-选择Excel表格文件并打开-根据需要编写文档-在需要使用Excel表格数据的地方，选择“插入合并域”下拉列表-选择对应的表格列-[单击菜单栏“预览结果”，可以查看邮件合并的效果]-单击菜单栏“完成并合并”下拉列表-选择“编辑单个文档”，即可完成合并
• 利用Excel数据，批量生成outlook邮件：操作同批量生成word文档，只是在单击“开始邮件合并”下拉列表之后，选择“电子邮件”；并在完成合并时，选择“合并到电子邮件”
• 利用Excel数据，批量生成选择题：操作同批量生成word文档，只是在单击“开始邮件合并”下拉列表之后，选择“目录”
• 邮件合并后的格式设置：1）ALT+F9，显示或隐藏文档中所有域代码；2）设置数字格式，示例：{ MERGEFIELD 奖金 \# "#,##0" }；3）设置日期格式，示例：{ MERGEFIELD 日期 \@ "yyyy-MM-dd" }；4）具体格式的设置，可以参考Excel单元格格式的自定义方式

 

第14节 常用日期与时间运算

• 日期、时间与数字之间的转换：日期可以转换成整数，