想不到Offset函数还可以这样玩【Excel分享】

想不到Offset函数还可以这样玩

大家好，今天和大家分享“想不到Offset函数还可以这样玩“

一、今天的亮点

1、offset函数第3参数用了数组

2、offset函数第3参数用了数组里且还用了分号和逗号。它这样用，可以把单元格一行数据转为2列多行的二维数组来作Vlookup函数的第2参数的数据源，满足Vlookup函数查找值月份一定要首列，而数据源中的月份不但不在首列，而且还分布在数据源里多列如下面的图所示

3、如果有文本和数值如何降维，用T降维会所数值变成0，用N降维会把文本变成空''

想学的，跟我一起来，往下看

二、这样奇葩的报表双条件查找

根据姓名和月份查找对应的数量

1、动画操作效果

2、公式截图

=VLOOKUP(B7,IF(N(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12}))=0,'',N(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12})))&T(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12})),2,)

3、公式

4、公式解释

• OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12})，首先通过Match函数定位姓名在那一行，再减掉1，得到offset偏移的行数，从A1开始进行偏移，偏移的列数就是这今天经典之处，20%用户可能会玩offset第3参数偏移的列，用一维数组的用法，也就是{1,2,3,4,5,6,7,8,9,10,11,12}或者全是分号{1;2;3;4;5;6;7;8;9;10;11,;2},如果offset第3参数二维数组，也就是中间有逗号和分号，也就是这们今天这个{1,2;3,4;5,6;7,8;9,10;11,12}，可能会玩的人少之又少了，可能不到5%，这个公式作用就是动态把每一行数据转为2列多行来作vlookup函数的第2参数数据源
  

• 我们通过offset动态得到每一行数据转为2列多行之后，现在问题又来了，offset返回的这个2列多行单元格里又有文本姓名，又数值型数字，现在这个是多维，不能直接用，要降维才能作Vlookup函数的第2参数，怎么办呢？，这又是今天分享的亮点，我暂时也没有找到一个好方法，包含文本和数值型数字的多维用什么好的方法降维，这里来一个差点办法

• IF(N(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12}))=0,'',N(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12})))&T(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12}))，先用N降维N(OFFSET($A$1,MATCH(A8,$A$1:$A$4,)-1,{1,2;3,4;5,6;7,8;9,10;11,12}))文本变成了0，数值型数字保留，所以加个判断，如果等于0，就显示空''，否则就显示数字本身，再用&l连接T降维得到文本保留，数字为空，最后结果就是把文本和数值型数字都保留了，也起到降维的作用

• 最后用vlookup函数查找，根据月份，查找对应的数量，有的朋友又问，前面的姓名作用，就是定位那一行，姓名在单元格区域A1:M4那一行