一、Lookup:查询引用。 Lookup函数具有两种使用形式。 (一)、向量形式。 功能:在单列或单行中查询指定的值,然后返回第二个单行或单列中相同位置的值。 语法结构:=Lookup(查询值,查询值所在的范围,[返回值所在的范围])。 前提条件:查找值所在范围的值必须按“升序”排序,否则无法得到正确的结果。 目的:查询销售员的销量。 方法: 1、以【销售员】为主要关键字进行【升序】排序。 2、在目标单元格中输入公式:=LOOKUP(H3,B3:B9,D3:D9)。 解读: 1、由于Lookup函数本身的特点,在查询数据前,需要对查询值所在范围的值进行升序排序,否则无法得到正确的查询结果哦! 2、当查找值范围和返回值范围相同时,返回值的范围可以省略哦! (二)、数组形式。 作用:在对应的数据源中的第一列和第一行查找值,并返回最后一列或最后一行对应的值。 语法:=Lookup(查找值,查找值和返回值所在的范围)。 前提条件:查找值所在范围的值必须按“升序”排序,否则无法得到正确的结果。 目的:查询销售员的销量。 方法: 1、以【销售员】为主要关键字进行【升序】排序。 2、在目标单元格中输入公式:=LOOKUP(H3,B3:D9)。 解读: 使用数组形式时,查找值必须在第一行或第一例,而返回值必须在最后一行或最后一列,否则无法正确计算哦! (三)、单条件查询。 目的:查询销售员的销量。 方法: 在目标单元格中输入公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)。 解读: 1、此方法没有对数据源进行排序,也得到了正确的结果,但语法结构很明显不是在前文中说讲的,其实,此方法是Lookup函数向量用法的高级版哦。 2、当Lookup函数在查找值范围中找不到对应的值时,就进行向下匹配,原则为返回小于当前查询值的最大值对应的结果。公式:=LOOKUP(1,0/(B3:B9=H3),D3:D9)中,首先判断B3:B9=H3的结果,形成一个1和0的数组,而0/0则返回错误,暨查询值范围为0和错误构成的数组,当查询值为1时,自然返回“0”所对应的结果,暨:B3:B9=H3成立时对应的结果。 (四)、多条件查询。 目的:查询销售员在相应地区的销量。 方法: 在目标单元格中输入公式:=IFERROR(LOOKUP(1,0/((B3:B9=H3)*(E3:E9=I3)),D3:D9),'无销量')。 解读: 1、多条件查询和单条件查询的原理是相同的。 2、Iferror函数的作用是判断表达式是否返回错误,如果错误,则返回指定的值,例如本示例中当销售员在相应地区没有销售记录时,返回“无销量”。 二、Index+Match组合:快速查询一列或多列值。 (一)、单列查询。 目的:查询销售员的销量。 方法: 在目标单元格中输入公式:=INDEX(D3:D9,MATCH(H3,B3:B9,0))。 解读: 此方法为Index+Match的组合用法,要首先理解单个函数的用法哦,其Index和Match的用法请查阅历史记录中的相关文章内容。 (二)、多列查询。 目的:查询销售员的所有信息。 方法: 在目标单元格中输入公式:=INDEX($B$3:$E$9,MATCH($B$12,$B$3:$B$9,0),MATCH(C$11,$C$2:$E$2,0)+1)。 解读: 此公式中需要注意绝对引用和相对引用的使用哦! 三、Text:根据指定的格式将数字转换为文本。 作用:根据指定的数字格式将数字转换为文本。 语法结构:=Text(文本或引用,格式代码)。 (一)、设置时间格式。 方法: 在目标单元格中输入公式:=TEXT(D3,'00-00-00')或=TEXT(D3,'0!/00!/00')。 (二)、分段显示。 方法: 在目标单元格中输入公式:=TEXT(E3,'000-0000-0000')。 (三)、“上升、下降、持平”。 方法: 在目标单元格中输入公式:=TEXT(G3-F3,'上升;下降;持平')。 (四)、“超额完成X;未完成X;已完成”。 方法: 在目标单元格中输入公式:=TEXT(G3-F3,'超额完成#;未完成#;已完成')。 解读: “#”代表具体的数值哦! (五)、等级判定。 方法: 在目标单元格中输入公式:=TEXT(F3,'[>=100]优秀;[>=95]良好;及格;无业绩')。 解读: 对指定范围指定的判定为及格,如果单元格内容不为数字,则判定为无业绩。 四、Count、Countif、Countifs、Counta、Countblank:计数。 (一)、Count:统计区域中数字单元格的个数。 语法结构:=Count(统计范围)。 目的:统计实际销售次数。 方法: 在目标单元格中输入公式:=COUNT(G3:G9)。 解读结构: 区域中共7个单元格,其中的5个为数值,所以公式=COUNT(G3:G9)的统计结果为5。 (二)、Countif:单条件计数。 语法:=Countif(条件范围,条件)。 目的:按性别统计销售员人数。 方法: 在目标单元格中输入公式:=COUNTIF(C3:C9,J3)。 (三)、Countifs:多条件计数。 语法结构:=Countifs(条件1范围,条件1,条件2范围,条件2……)。 目的:按性别统计销量>=指定值的人数。 方法: 在目标单元格中输入公式:=COUNTIFS(C3:C9,I3,F3:F9,'>='&J3)。 解读: 单条件或多条件计数中,条件范围和条件必须成对出现哦! (四)、Counta:统计区域中非空单元格的个数。 语法结构:=Counta(统计区域)。 目的:统计实际销售笔数。 方法: 在目标单元格中输入公式:=COUNTA(F3:F9)。 解读: 共7个单元格,其中2个单元格为空,所以=COUNTA(F3:F9)的结果为5。 (五)、Countblank:统计区域中空单元格的个数。 语法结构:=Countblank(数值区域)。 目的:统计未销售人员数量。 方法: 在目标单元格中输入公式:=COUNTBLANK(F3:F9)。 解读: 共7个单元格,其中5个单元格非空,所以=COUNTBLANK(F3:F9)的共计结果为2。 五、Len+Month:判断当前月份所属季度。 方法: 在目标单元格中输入公式:=LEN(2^MONTH(D3))。 结束语: 文本从实际出发,对工作中经常用到的函数公式进行了罗列,共5组,对其使用技巧,你Get到了吗?如果有不懂、不明白的地方,欢迎在留言区留言讨论哦,如果亲觉着使用,别忘了“点转评”哦,有亲的支持,小编会进一步努力的哦! |
|
来自: hercules028 > 《excel》