excel查找和引用函数的学习与应用,笔记4,权亮

1 修改补充员工档案表
蓝色底为所求的区域.
提示:生日为身份证中间八位.年龄为现在日期到的年头,工龄为现在的日期到入职时间的年头,以上的主要是使用日期函数来求出.
直辖市和所属区县用vlookup函数,寻找,后面附地区代码表.
解答:
Datedif函数格式为: datedif函数：DATEDIF(start_date,end_date,unit)
第一个参数start_date：起始时间
第二个参数end_date：结束时间
第三个参数unit：函数返回的类型
参数1和参数2可以是带具体的时间、也可以是其他函数的结果。
参数3：unit为返回结果的代码
"y"返回整年数
"m”返回整月数
"d"返回整天数
"md”返回参数1和参数2的天数之差，忽略年和月
"ym“返回参数1和参数2的月数之差，忽略年和日
"yd”返回参数1和参数2的天数之差，忽略年。按照月、日计算天数
Today函数: 返回当前系统的日期.

出生日期:首先使用mid函数,取出来身份证,第七位开始,连续八位的数字.取出来是文本,在后面的运算中无法当做日期进行运算,所以我们要用日期函数date函数来求出来出生日期.
=DATE(MID(D2,7,4),MID(D2,11,2),MID(D2,13,2))

年龄:我们 用datedif函数,today函数,求出来出生日期和现在日期的差值,以年显示.
=DATEDIF(F2,TODAY(),“y”)

工龄,以年显示:
=DATEDIF(E2,TODAY(),“Y”)

不足一年的工资:
=DATEDIF(E2,TODAY(),“ym”)

所属省自治区直辖市:身份证前两位数字对应省直辖市
=VLOOKUP(LEFT(D2,2),全国地区表!$A$1:$B$5944,2,FALSE)

所属区县:身份证前六位数字对应所属区县
=VLOOKUP(LEFT(D2,6),全国地区表!$A$1:$B$5944,2,FALSE)

2 利用vlookup函数进行模糊匹配

解答:
Vlookup函数:格式为LOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

特别说明,第四个参数,选择近似匹配时候,我们要求第二个参数的首列必须是升序.他将会返回小于或等于的最大值.也就是找到比自己小的,或者一样大的,继续寻找,如果没有比自己小的或者一样大的时候,就返回最后一次找到的值.

3 利用sum函数,和offset函数累积求和.

提示:累积求所在行和上方所有行的数量列数字的总和.
可以利用offset函数,求第一行,就显示第一行,求前两行,就显示前两行数字.
解答:
首先要绝对引用b2单元格,当我们需要累积第二行时候,用offset函数,显示b2单元格,1行1列.当我们需要累积第三行时候,用offset函数显示b2单元格的2行,1列.
方法如图.

4 查找相同的内容的个数.
提示:

方法1:使用countif函数,匹配的内容的数组格式,之后外面嵌套一个sum函数,求一共有的多少个.

方法2:使用match函数找到匹配的序列,之外套一个count函数,来计算出一共有多少个匹配数量,

Match函数:格式为MATCH(lookup_value, lookup_array, [match_type])
MATCH 函数语法具有下列参数：
lookup_value 必需。 要在 lookup_array 中匹配的值。 例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。
lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
• lookup_array 必需。 要搜索的单元格区域。
• match_type 可选。 数字 -1、0 或 1。 match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。 此参数的默认值为 1。

Count函数:格式为COUNT(value1, [value2], …)
COUNT 函数语法具有下列参数：
value1 必需。 要计算其中数字的个数的第一项、单元格引用或区域。
value2, … 可选。 要计算其中数字的个数的其他项、单元格引用或区域，最多可包含 255 个。
如果参数为数字、日期或者代表数字的文本（例如，用引号引起的数字，如 “1”），则将被计算在内。

逻辑值和直接键入到参数列表中代表数字的文本被计算在内。

如果参数为错误值或不能转换为数字的文本，则不会被计算在内。

如果参数是一个数组或引用，则只计算其中的数字。 数组或引用中的空白单元格、逻辑值、文本或错误值将不计算在内。

若要计算逻辑值、文本值或错误值的个数，请使用 COUNTA 函数。

若要只计算符合某一条件的数字的个数，请使用 COUNTIF 函数或 COUNTIFS 函数。

Countif函数:格式为=COUNTIF(要检查哪些区域? 要查找哪些内容?)

解答:

或者

两种方法结果都是可以求出来个数.

5 标记匹配的单元格

解答:将各组的名字与上面被查找的名字对应上,变回编程深色背景.此时我们需要使用条件格式
位置位于开始下面的条件格式,新建规则.输入函数式.

最后结果如同最开始的图一样,被查找的名字都被增加了颜色.

6 动态显示价格并条件加深相关单元格的颜色.
设置查询序号后做一个序列的数据验证,查询价格也是最后在我们输入两个信息后,查询价格,能自动显示产品价格.并且并将对应的行和列背景加深.

提示:我们需要用到match函数,可以得到产品型号规格的位置,也就是行列信息,这样可以定位在表格中的位置,然后用index函数.

Index函数:格式为INDEX(array, row_num, [column_num])
INDEX 函数的数组形式具有下列参数:
数组 必需。 单元格区域或数组常量。
如果数组只包含一行或一列, 则相应的 row_num 或 column_num 参数是可选的。
如果数组具有多行和多列, 并且仅使用 row_num 或 column_num, 则 INDEX 返回数组中整个行或列的数组。
row_num 必需。 选择数组中的某行，函数从该行返回数值。 如果省略 row_num, 则需要 column_num。
column_num 可选。 选择数组中的某列，函数从该列返回数值。 如果省略 column_num, 则需要 row_num。

如果同时使用 row_num 和 column_num 参数, INDEX 将返回 row_num 和 column_num 交叉处的单元格中的值。
row_num 和 column_num 必须指向数组中的一个单元格;否则, INDEX 将返回 #REF! 错误。
如果将 row_num 或 column_num 设置为 0 (零), 则 INDEX 将分别返回整列或整行的值的数组。 若要使用以数组形式返回的值, 请以数组公式的形式输入 INDEX 函数

解答:首先我们设置查询型号的数据验证,来源选择型号的所有单元格.

同理可以设置查询规格的数据验证.

最后在产品价格输入函数,如图.

接下来设置背景色,我们的目的是,查询型号选择一个,那一行背景变色,同理查询规格列也会发生变化.


在“新建规则”下的“使用公式确定要设置格式的单元格”列表框中，输入一个公式。
公式必须以等号 (=) 开头且必须返回逻辑值 TRUE (1) 或 FALSE (0)。
单击“格式”以显示“设置单元格格式”对话框。
选择当单元格值符合条件时要应用的数字、字体、边框或填充格式，然后单击“确定”。
可以选择多个格式。 选择的格式将在“预览”框中显示出来。

如下图所示,输入公式,这个公式的含义就是,

点击确定,之后新建规则,如下图所示.

结果如下:

7 使用indirect函数,数据验证,进行一个二级关联
要求,首先在a2单元格可以选择,中国的省直辖市自治区,其次,选择后,b2可以显示a2选择省市的下一级城市.
后面配了一个中国省市表.

解答:
我们先选择省市表,定位到常量,然后点击名称管理器的根据所选内容创建,然后只选择首行,因为我们的名称在首行.之后就在名称管理器里可以看到所有的地区表

之后点击数据验证,序列,之后如图输入公式,确定

这样我们就能选择直辖市,省市了.

在关联的市区b2设置数据验证,序列,如下图设置.

INDIRECT(ref_text, [a1])
INDIRECT 函数语法具有以下参数：
Ref_text 必需。 对包含 A1 样式的引用、R1C1 样式的引用、定义为引用的名称或对单元格的引用作为文本字符串的单元格的引用。 如果 ref_text 不是有效的单元格引用, 则间接返回 #REF! 。
如果 ref_text 引用另一个工作簿 (外部引用), 则必须打开另一个工作簿。 如果源工作簿未打开, 则间接返回 #REF! 。
注意 Excel Web App 中不支持外部引用。
如果 ref_text 引用的单元格区域超出1048576的行限制或列限制 16384 (XFD), 则间接返回 #REF! 错误。
注意 此行为不同于早于Microsoft Office Excel 2007 的 Excel 版本, 这将忽略超过的限制并返回值。
A1 可选。 一个逻辑值，用于指定包含在单元格 ref_text 中的引用的类型。
如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。
如果 a1 为 FALSE，则将 ref_text 解释为 R1C1 样式的引用。

这样,我们就能关联a2单元格.每选一个省,就能选出来他的市,同时我们还学会了批量设置名称,

8 使用index函数和match函数,进行引用

提示:
根据上面的成绩和分数等级划分进行引用,其次,分数登记是从100分,从上往下降序.

解答:
分数等级输入公式如下图

Match函数帮助我们定位在在几行,特别的,查找匹配我们选择-1,也就是查找的是大于或者等于查找值的最小值.之后我们根据分数登记划分表,对分数等级列进行引用.
结果如下: