VLOOKUP按指定次数重复数据
如下图,有这样一道题,要求按照B列的指定次数,重复C列的班级名称,结果放入E列。
看到这道题,好学的表亲们,大概会立刻想起祝老师的某个基础操作教程动画,函数了得的亲们,会立刻想起某个多维数组套路。
但咱们这里只想VLOOKUP。
如果用VLOOKUP,这题怎么做?
很简单。
只需要两步。
第一步,A2输入公式:=A1+B2,向下填充
第二步,E2输入公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",向下拖动。
然后……结果……
如下图:
我们结合两个公式,解释下其中过程。
第一个公式:A1+B2,是计算相关次数的累计值,比较好理解。
第二个公式:=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",看起来是常用的VLOOKUP套路,但其实有两个很有意思的地方。
其一,VLOOKUP的查找值——ROW(A1)。在公式的下拉过程中,通过查找1,2,3,4,5,(电脑配音,12345,上~山~打~老~虎)……来返回结果。
其二,屏蔽VLOOKUP错误值的方式。如果VLOOKUP查找不到相关数值,比如此例中的1和2,通常会返回错误值#N/A,而我们通过IFERROR,使它返回公式所在单元格的下一个单元格的值。
比如,我们在E2输入公式,VLOOKUP函数的错误值则返回E3,公式向下拖动,E3的错误值返回E4……如此类推,直至VLOOKUP函数返回正确值——则之前通过IFERROR函数判断为错误值的单元格,自然统一更新为相应的正确值(……脑海里播放多米诺骨牌从依次跌倒到依次站起的画面)。
然后再进行新一轮循环判断、数据更正。
最后的&””,是函数里常用的屏蔽零值的技法,以便在VLOOKUP公式下拉过界时,返回的零值显示为空白。
以上两个公式,除了VLOOKUP(ROW(A1)……)的技巧外,还利用了函数的另外一个技巧,我们姑且称之为上下其手。
何谓上下其手?简而言之,便是拿公式所在单元格的上下单元格结果为己用。
第一个公式,=A1+B2,是上手,拿公式所在单元格的上一个单元格的值为己用。很多人比较熟悉常用,已经很了解了。
第二个公式,=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),E3)&"",是下手,拿公式所在单元格的下一个单元格的值为己用。大家用的可能就比较少了。因为少用,所以才显得比较有意思。大家有闲时,不妨多想下,兴许别有收获哈。
==========我是似水温柔的分割线==========
通过辅助列的方式,我们实现了按指定次数重复数据。
下面咱们要做的,便是丢掉辅助列,直接用一个公式得出结果。
即,我们需要把A列累计次数求和的数据,放入VLOOKUP公式的查找范围中,以便直接得出所需要的结果。
我们可以使用这样的公式:
SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>")
这是一个累计求和的多维数组套路,类似的套路还有MMULT、INDIRECT、SUBTOTAL等。
这个公式,是通过OFFSET函数,制作多维求和统计范围,比如B2:B2,B2:B3,B2:B4……
最后使用SUMIF进行求和。
我们把这一段放入VLOOKUP函数中:
E2=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET($B$2,,,ROW($1:$4),),"<>"),$C$2:$C$5),2,0),E3)&""
如此,这个公式便正式写完了。
当然,如果用LOOKUP,公式可以简洁:
=LOOKUP(ROW(A1),SUMIF(OFFSET($B$1,,,ROW($1:$5),),"<>")+1,$C$2:$C$6)&""