佛山小老鼠说Ecxel函数

佛山小老鼠说Excel函数

第六讲 函数Index Indirect Lookup Vlookup Offset与Match综合应用

一．各函数与Match函数的综合应用来引用数据

a)

与Vlookup函数

=VLOOKUP(B11,$B$4:$F$7,MATCH(C10,B3:F3,0),0)

公式解释：B11是查找值，$B$4:$F$7查找区域，第三参数用了Match返回列号，Match函数的第一个参数是查找值，第二参数是一个横向区域，即单行；第三参数是查找方法。Vlookup第四参数是查找方法——输入0精确查找。 b)

与Lookup函数

=LOOKUP(1,0/(B4:B7=B11),INDEX(C4:F7,,MATCH(C10,C3:F3,0)))

公式解释：Lookup的第一个参数是查找值，第二参数可以一个数组，B4:B7=B11产生一个数组{FALSE;TRUE;FALSE;FALSE}，然后用0除以它，又产生一个新的数组{#DIV/0!;0;#DIV/0!;#DIV/0!}，只有一个0，其它全部为错误值，这个0就是我们需要的，这样就解决了Lookup函数第二参数要按升序排序的要求了。Lookup函数第三参也是一个数组，我们用Index函数来实现，因为Index函数如果省略第二参数，刚好是返回第三参数的列区域，而Index函数第二参数也用了Match函数来找到符合条件的列。效果如图21 c)

与Index函数

=INDEX(C4:F7,MATCH(B11,B4:B7),MATCH(C10,C3:F3,0))

公式解释：这种方法相对来说简单了许多，也就是用Match函数来找Index的行参数和列参数。这公式我就不再多啰嗦了 d)

与Offset函数

=OFFSET(B3,MATCH(B11,B4:B7,0),MATCH(C10,C3:F3,0),1,1)

公式解释：Offset这个函数作用是根据某一参照单元格，经过偏移行，经过偏移列，然后得到新的引用区域的，这个新的引用区域如果是一个单元格的化，那就最后两个参数都是1，如果得到的这个新的引用区域是一个多行多列的区域的化，那么最后两个参数就是行高与列宽

Offset的第一个参数是B3，参照单元格，第二参数用Match函数来返回偏移多少行，同样用Match函数来实现偏移多少列，第四参数是指新区域的行高是1，第五参数

第 18 页 共 41 页

佛山小老鼠说Excel函数

是指新区域的列宽是1，如果最后两个参数都是1，那么新区域就是一个单元格。 e)

与Indirect函数

=INDIRECT(LOOKUP(MATCH(C10,C3:F3,0),{1,2,3,4},{\(B11,B4:B7,0)+3)

公式解释：Indirect这个函数的作用是根据单元格的引用返回引用单元格的值，这个函数有二个参数，不过我们用时都是只写它的第一个参数，因为第二参数是1或者省略的化，那么第一参数用的是A1引用样式。

我们用了Lookup这个函数返回列号， 而lookup函数的第一参数用了Match函数来找列的位置，找到相应的位置之后，就会对应相应的字母CDEF,然后我们用Match函数来找行号，行号还要加上这个公式前面的行数 f)

与数组函数

{ =INDEX(B3:F7,MAX(IF(B3:B7=B11,ROW(B3:B7)-2,0)),MATCH(C10,B3:F3,0)) } 公式解释：Index函数的第二参数用了数组，先判断符合条件用了if函数 IF(B3:B7=B11,ROW(B3:B7)-2,0)这个返回一组数{0;0;3;0;0}，符合条件就显示纵向位置，不符合的就显示0，然后用最大值函数Max从{0;0;3;0;0}中提取这个3，这个3就是Index函数的第二参，行参数，Index第三参数用了Match实现，这个我就不再多啰嗦了

第 19 页 共 41 页

佛山小老鼠说Excel函数

第七讲 函数实例 考勤表(Weekday Mod Day Month Year)

一．自动生成日期

a)

使用的公式

=IF(MONTH(DATE($B$2,$B$3,COLUMN()-3))=$B$3,DATE($B$2,$B$3,COLUMN()-3),\)

公式解释：IF函数的目的是为了当Date函数生成的日期为下一个月的日期时，就显示为空，因为每一个月的天数不一样，有的月份有30天，有的月份的有31天，有的月份只有28天，如二月份，用Month函数取出Date函数日期里的月份和我们有效性单元格B3作比较，如果是一样的，那么生成Date函数的日期 ，如果不相等，就说明是下一个月的日期，至于Date函数的三个参数，分别为年，月，日，年和月都是在有效性单元格B2和B3中，那么日我们就用Column函数生成，因为从1日开始，因此我们用了Column（A1）作为它的参数，向右拉公式是就变成123456?? 二．自动填充间隔底纹

a)

使用的公式

=MOD(ROW(),2)=0

公式解释：Mod是取余函数，返回两数相除的余数，第一个参数是被除数，第二个参数是除数，用了Row()这个函数作为它的被除数，因为Row()这个函数里没有参数，这个公式在写于那一个单元格就显示那一个单元格的行号，因此这样就把我们的的所有行为分二种情况，一种它的余数是0；一种它的余数是1，上面的公式=MOD(ROW(),2)=0 我们得到是偶数行。

三．当日期是星天六或者是星期天时，自动标示底纹

a)

使用的公式

=OR(WEEKDAY(D$4,2)=6,WEEKDAY(D$4,2)=7)=TRUE

公式解释：Or函数是这样的，如果它里面的参数有一个是成立的，那么它返回Ttrue ,Weedkay这个函数返回一个日期是一个星期的第几天，二个参数，第一个参数是日期，第二个参数是返回结果计算方式，如果是1，星期天就是一周的第一天，如果第二个参数是2，那么星期一就是一周的第一天，因此符合我们中国人的习惯，所以第二参数我们用了2，这个公式的意思是如果一个日期是星期六或者是星期天且成立的化，那

第 20 页 共 41 页

佛山小老鼠说Excel函数

么我们就执行条件格式，填充底纹。否则就不执行条件格式。 四．计算出勤数和缺勤数

a) b) c)

使用的公式

=IF(COUNTIF($D5:$AH5,AI$4)=0,\

公式解释：Countif这个函数，按条件统计单元格个数，有两个参数，第一个参数是条件所在的区域；第二参数是条件，由于当条件区域时没有这个条件时，结果会返回0，为了让报表漂亮，所以我们要用加个If 函数来屏闭这些0；如果COUNTIF($D5:$AH5,AI$4)=0，那么我们就显示为空，也就是不显示的意思，否则我们还是按照原来的COUNTIF($D5:$AH5,AI$4)进行正常计算，另外这公式我们有没有它们引用不一样，$D5:$AH5我们用了绝对列引用，为什么这样呢，因为向下填充公式行号要变的，这样来统计每一个人的，向右填公式不能让列号变，因为统计的这个区域不能变，都是这个人的，就是那个月的天数；另外AI$4这个条件用了绝对行引用，为什么要这样呢？因为我们向下填充公式是，都是统计这个“统计项”，向右填充时，这个“统计项”要变的，这样才能统计出每一个人的不同的缺勤数

五．使日期显示“周几”

a)

自定义单元格式“选择”日期格式里的那个“三”的格格式，然后左键点一下自定义，在格式代码的最前面加上一对双引号，里面输入一个“周”字

六．选择大区域的快捷键

a)

当区域比较大时我们用按住鼠标左键拖拉的方法已经是太慢了，因引我们借助于控制键Shift键，方法是这样的：选点一下你要选择的区域最左上角那个单元格，然后拖动水平和垂直滚动条到你要选择的这个区域的右下解那个单元格的，但是不能直接单击左键，要先把Shift键按下去，然后再单击鼠标的左键

七．怎样隐藏区域中的“0”值

a)

方法：选中你要隐藏的“0”的区域==》右击==》自定义单元格格式==》输入下面的代码，注意输入代码时一定要关闭输入法Ctrl+空格或者转为英文的输入状态下，才行0;-0;;@

八．考勤天数我们可以用一些符号来代替，也可以直接在里面输入数字，最后用Countif函数

和Sum函数来解决，至于那些符号我们也可以用把勾和打叉，打勾的快捷键Alt+41420;

第 21 页 共 41 页