1、排序
1.1 排序可以嵌套，开始栏中选排序，选自定义排序，选择主要排序和次要排序，实现排序的嵌套
1.2 排序中可以自定清单，自己定义想要的排序标准。自定义排序中选择自定义序列
2、筛选
2.1 筛选的高级应用--文本删选---从多到少、从少到多
2.2 筛选的高级应用--日期筛选---开始于一个日期，截至于一个日期
2.3 筛选的高级应用---自定义筛选---自己定义筛选标准
2.4 筛选的高级应用---色彩筛选---自定义色彩分布之后，进行重点颜色筛选
3、冻结窗口
3.1 冻结窗口---冻结的是选中行以上（选中列以左）的部分
3.2 同时冻结行和列----选中要冻结行与列交叉位置的右对角表格，进行冻结窗口即可
3.3 分割视窗----选中行，点击视图中的拆分窗口即可拆分
4、交叉分析筛选器（切片器）与格式化表格
4.1 表格格式化很简单，没必要刻意记录
4.2 切片器往往配合数据透视表进行数据的筛选
4.3 切片器可以多个，主要是筛选不同标题的数据，让表格动态化起来
4.4
5、打印
5.1 打印分页
如果打印每页都想有第一页的标题的话，选择
图片.png
6、函数
6.1
AND 函数返回逻辑值：如果所有参数值均为“真（TRUE）”，返回“真（TRUE）”，反之返回“假（FALSE）”。
=AND(测试条件1, [测试条件2], ...)
只有全部为真时才能返回真，有一个假就是假。 1 为真，0 为假
logical1 必需。 要测试的第一个条件，其计算结果可以为 TRUE 或 FALSE。
logical2, … 可选。 要测试的其他条件，其计算结果可以为 TRUE 或 FALSE。
6.2
IF 函数根据提供的条件参数，条件计算结果为 TRUE 时，返回一个值；条件计算结果为 FALSE 时，返回另一个值。
=IF(logical_test, [value_if_true], [value_if_false])
=IF(条件, [条件为 TRUE 时的返回值], [条件为 FALSE 时的返回值])
6.3
IFERROR 函数根据提供的初始条件的计算结果，如果计算结果错误，返回提供的第二个参数；否则返回初始条件的计算结果。
=IFERROR(value, value_if_error)
=IFERROR(条件, 条件为错误时的返回值)
图片.png
6.4
ARABIC 函数将罗马数字转换为阿拉伯数字。
=ARABIC(text)
=ARABIC(字符串)
Text 必需。 用引号括起来的字符串、空字符串 (“”) 或对包含文本的单元格的引用。
图片.png
6.5
VLOOKUP 函数在指定单元格区域的第一列查找指定的查找值，返回所在行中指定列的值。 简单地说，VLOOKUP 函数根据给定的一个值，在目标区域的第一列查找并匹配出该值，之后返回该值所在行指定列的数据。
=VLOOKUP(lookup_value, table_array, col_index_num,[range_lookup])
=VLOOKUP(查找值, 单元格区域, 列数, [匹配模式])
Lookup_value 必需。 需在指定单元格区域中查找的值。
Table_array 必需。 在其中查找数据的数组或单元格区域， 使用对区域或区域名称的引用。
Col_index_num 必需。 table_array 中将返回的匹配值的列号。
Range_lookup 可选。 一个逻辑值，指定查找精确匹配值还是近似匹配值。
如果为 TRUE 或省略，则返回近似匹配值。 如果找不到精确匹配值，则返回小于 lookup_value 的最大值。
如果为 False，则将查找精确匹配值。
这里需要注意的是，VLOOKUP函数有一个参照的概念，分为相对参照和绝对参照。使用美元符号是为了锁定区域。
图片.png
不过上述公式存在问题，如图所示，输入不存在的用户，会显示错误
图片.png
我们可以使用IFERROR函数来单独显示错误信息，但是如果想要显示汉字，则需要双信号，如图所示
图片.png
为了美观，可以在设置其余不显示错误信息
图片.png
此时又有一个小bug，没输入时，显示查无此人
图片.png
此时可以在添加一个IF函数进行判断，如果输入框为空，则查询位置也为空，否则执行最后的语句。
图片.png
为防止输入内容错误，可以设置输入内容的有效性，
图片.png
当输入错误后就会显示
图片.png
也可以设置输入内容提醒，例如：
图片.png
6.6
MATCH 函数从提供的单元格区域中查找指定值，并返回指定值在单元格区域中的相对位置。
MATCH 函数从提供的单元格区域中查找指定值，并返回指定值在单元格区域中的相对位置，用户可以指定匹配模式是精确匹配或模糊匹配
=MATCH(lookup_value, lookup_array, [match_type])
=MATCH(查找值, 单元格区域, [匹配模式])
lookup_value 必需。 查找值。
lookup_array 必需。 从其中搜索的单元格区域。
match_type 可选。指定查找值的匹配模式是精确查找还是模糊查找。匹配模式如下：
1或省略：匹配小于或等于lookup_value（查找值）的最大值。这时，lookup_array（单元格区域）中的值必须按升序排列。
0：匹配完全等于lookup_value（查找值）的第一个值。这时，lookup_array（单元格区域）中的值可按任意序列排序。
-1： 匹配大于或等于lookup_value（查找值）的最小值。这时，lookup_array（单元格区域）中的值必须按降序排列。
匹配文本值时，不区分大小写；
如果匹配模式为精确匹配，查找值为文本格式时，查找值可以使用通配符：
“？”号：匹配任意单个字符；
“”号：匹配任意一串字符；
如果查找实际的”?”、””号时，字符前增加波形符（~）。
图片.png
6.7
LOOKUP 函数（数组形式）从提供的数组的第一行或第一列查找指定的值，返回数组最后一行或最后一列中同一位置的值。
'''
=LOOKUP(lookup_value, array)
=LOOKUP(查找值, 数组)
'''
lookup_value 必需。 查找值。
array 必需。 包含要与 lookup_value 进行比较的文本、数字或逻辑值的单元格区域。
如果数组列数多于行数LOOKUP 会在第一行中搜索 lookup_value 的值。
如果数组是正方的或者行数多于列数，LOOKUP 会在第一列中进行搜索。
使用 HLOOKUP 和 VLOOKUP 函数，可以通过索引以向下或遍历的方式搜索，但是 LOOKUP 始终选择行或列中的最后一个值。
如果 LOOKUP 函数找不到 lookup_value，则会与 数组中小于或等于 lookup_value 的最大值进行匹配。
数组中的值必须按升序（按小到大）排列，否则LOOKUP函数可能无法返回正确的值。各类型数值升序排列如下：
数字：-1、0、1、2；
文本：a、b、c、D、e（不区分大小写）；
逻辑值：FALSE、TRUE
图片.png
6.8
INDEX 函数（数组形式）根据指定的行号与列号返回表格或数组中的元素。
=INDEX(array, row_num, [column_num])
=INDEX(数组, 行号, [列号])
Array 必需。 单元格区域或数组常量。
如果数组只包含一行或一列，则相对应的参数 Row_num 或 Column_num 为可选参数。
如果数组有多行和多列，但只使用 Row_num 或 Column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。
Row_num 必需。 选择数组中的某行，函数从该行返回数值。
如果省略 Row_num，则必须有 Column_num。
如果将值省略或设为 0，表示对所有行的引用。
Column_num 可选。 选择数组中的某列，函数从该列返回数值。
如果省略 Column_num，则必须有 Row_num。
如果将值省略或设为 0，表示对所有列的引用
图片.png
图片.png
图片.png
6.9
YEARFRAC 函数返回给定起始日期和结束日期之间的天数在全年天数的百分比。
=YEARFRAC(start_date, end_date, [basis])
=YEARFRAC(起始日期, 结束日期, [类型])
Start_date 必需。 起始日期。
End_date 必需。 结束日期。
Basis 可选。 要使用的日计数基准类型。 可用的日计数基准类型及含义如下：
0 或省略 → 美国（NASD）30/360
1 → 实际天数/实际天数
2 → 实际天数/360
3 → 实际天数/365
4 → 欧洲 30/360
图片.png
6.10
YEAR 函数返回给定日期的年份，介于 1900 至 9999 之间的整数。
=YEAR(serial_number)
=YEAR(日期)
Serial_number 必需。 要查找的年份的日期，或日期的引用，或计算结果为日期的表达式。
这个公式对格式要求蛮高的，必须是标准格式
图片.png
6.11
WORKDAY 函数返回在给定起始日期之前或之后，与该日期相隔给定的工作日的日期。 返回起始日到给定工作日之后的日期。
=WORKDAY(start_date, days, [holidays])
=WORKDAY(起始日期, 工作日, [假期])
Start_date 必需。 起始日期。
Days 必需。 start_date 之前或之后不含周末及节假日的工作日天数。 Days 为正值将返回未来的日期；为负值将返回过去的日期。
Holidays 可选。 一个可选日期列表，其中包含需要从工作日历中排除的一个或多个日期，例如各种省/市/自治区和国家/地区的法定假日及非法定假日。
图片.png
2016-10-1是周六
6.12
DAYS 函数返回两个日期之间的天数。
=DAYS(end_date, start_date)
=DAYS(结束日期, 开始日期)
如果两个日期参数为标准日期格式，则使用 EndDate–StartDate 计算两个日期之间的天数。
如果任何一个日期参数为文本，该参数将被视为 DATEVALUE(date_text) 并返回整型日期，进而参与计算。
图片.png
6.13
Excel 公式：从文本中提取数字
图片.png
1、数字在文本开始处 123ACDE
这种情况提取数字相对简单些，以下是通用公式：
如图所示，公式中的ROW()，代表想要截取的长度
图片.png
图片.png
值得注意的是这的“-”
图片.png
2、数字在文本结尾处 asbda134432
主需要将LEFT(左)，改成RIGHT(右)即可
图片.png
6.14
Excel 公式：对比两列数据，提取相同和不同
1、提取两列数据相同的数据
=INDEX(左侧列,SMALL(IF(COUNTIF(右侧列,左侧列数据区域)>0,ROW(左侧列数据区域),大于总数据的一个数字), ROW(A1)))&""
2、提取左侧列有，右侧列没有的数据
3、提取左侧列没有，右侧列有的数据
6.15
COUNT 单纯的统计行数或列数
图片.png
COUNTIFS 统计在多个条件限制下的个数
图片.png
当你想筛选数字大小时，可以使用“”标注，不过在使用这类函数时，可以选择使用技巧，如第二张图所示。“>”&B2 这是说明将符号和B2内容连接起来
图片.png
图片.png
这样的好处就是，你只需要单独修改个别单元格内容即可，就不用每次都修改公式。
7
7.1 文本分列
图片.png
图片.png
图片.png
图片.png
7.2
F4重复上一步操作
7.3删除重复数据
图片.png
7.4 表格的转置---复制，在粘贴中可以选择粘贴内容转置
图片.png
7.5单元格插入批注，直接右击选择就看可以
图片.png
8
8.1 进行排名
RANK.EQ（数值，引用，排位方式）---数值是指需要排名的数据，引用是指进行对比的数据，而排位方式是递增/递减，默认是递减(0)
图片.png
此时的排序是递增的，递增递减主要用于时间排序中需要注意。
图片.png
8.2 LEFT、LEFTB 函数从提供的文本第一个字符开始返回指定个数的字符。LEFT 函数将全角和半角字符都计数为1，LEFTB 函数将全角字符计数为2，半角字符计数为1。
=LEFT(text, [num_chars])
=LEFTB(text, [num_bytes])
=LEFT(文本, [提取字符的个数])
=LEFTB(文本, [提取字符的字节数])
Text 必需。 包含要提取的字符的文本字符串。
num_chars 可选。 指定要由 LEFT 提取的字符的数量。
Num_chars 必须大于或等于零。
如果 num_chars 大于文本长度，则 LEFT 返回全部文本。
如果省略 num_chars，则假定其值为 1。
Num_bytes 可选。 按字节指定要由 LEFTB 提取的字符的数量。
=LEFT(text, [num_chars])，符号也算一位
图片.png
=LEFTB(文本, [提取字符的字节数]) 这里的B是字节的意思，汉字是两个字节，所以第一个提取的是 我是1
图片.png
MID函数：
=MID(text, start_num, num_chars)
=MIDB(text, start_num, num_bytes)
=MID(文本, 开始位置, 字符个数)
=MIDB(文本, 开始位置, 字节数)
文本 必需。 包含要提取字符的文本字符串。
start_num 必需。 文本中要提取的第一个字符的位置。 文本中第一个字符的 start_num 为 1，以此类推。
num_chars 必需。 指定希望 MID 函数从文本中返回字符的个数。
Num_bytes 必需。 指定希望 MIDB 函数从文本中返回字符的个数（字节数）。
如果 start_num 大于文本长度，则 MID 返回空文本 (“”)。
如果 start_num 小于文本长度，但 start_num 加上 num_chars 超过了文本的长度，则 MID 只返回至多直到文本末尾的字符。
MID、MIDB 函数返回文本类型数据，即使截取的数据是数字。因此希望结果参与下一步数字计算，注意需要先转换为数字类型。
图片.png
8.3
使用 INDEX + MATCH 函数按条件查找
图片.png
公式：
=INDEX(A13:D24,MATCH(G14,B13:B24,0),MATCH(F15,A13:D13,0),1)
MATCH(G14,B13:B24,0)---是返回编号的行数
图片.png
MATCH(F15,A13:D13,0)---返回编号的列数
图片.png
然后行与列交叉位置便是想要的信息。
网友评论