代码之家 › 专栏 › 技术社区 › JuliusSecret

Excel-搜索缺少的值并基于3列返回这些值-匹配/索引/如果组合?

match indexing if-statement function excel

JuliusSecret · 技术社区 · 6 年前

这对我来说太专业了,希望你们中的一个能帮助我!

看看我想要达到的目标。红色的文字显示了我想要什么。 list3 包含 list1的所有可能值。 list2 显示 list1的当前活动值,我想基于 list3返回缺少的值。

我猜我在看match/index/if函数,但我不能搞清楚。有人愿意帮我吗?<3谢谢大家! 列表3 包含的所有可能值 列表1 . 列表2 显示当前的活动值 列表1 ,我想返回基于 列表3 .

我猜我在看match/index/if函数,但我不能搞清楚。有人愿意帮我吗?<3谢谢大家!

2 回复 | 直到 6 年前

user4039065 6 年前

把这个公式放在f4里,然后填上。

=index(c:c,aggregate(15,7,row(c$2:c$11)/not(countifs(b:b,c$2:c$11,a:a,e4)),countif(e$4:e4,e4)))

当然,此公式取决于e4:e7中的“数字”列表,该列表精确且填充充分,可以捕获所有丢失的条目。





当然,这个公式依赖于e4:e7中的“数字”列表,该列表精确且足够填充,可以捕获所有缺少的条目。

jblood94 6 年前

参考下图。使用此公式获取缺少的数字:

=sumproduct(--(a2:a40<gt;a1:a39))*rows(c2:c12)-rows(a2:a40)
将A列中数据的最后一个单元格更改为A40,并将A39更改为second to last.cell.


然后,将此帮助器列作为数组公式(ctrl>+shift>+enter)输入到1列范围中,行数与上述公式返回的行数相同(它将缺少的对编码为单个数字)。我把它输入到d4:d8:。


=small(iferror(0>match(index(A2:A440,index(small(if(A2:A440<>A1:A39,行(A2:A440)-行(A1)),行(间接(“1:”&sum(--(A2:A440<>A1:A39))))))),int((RoRoRoRoRor(0>match(0>match(index(A2:A440:A440,A440,索引(sma(小(if(如果(A2:A440:A40:A40)(if(A2:A440:A440<>lt>lt;<<&&&&&gt;gt;A1 c12,mod((行(间接(“1:”&行(c2:c12)*sum(--(a2:a40<>a1:a39))-1)、行(c2:c12))+1)、a2:a40&b2:b40,0,0)、索引(小(if(a2:a40<>a1:a39、行(a2:a40)-行(a1))、行(间接(1:“&sum(--(a40<<>a40<<>a1:a22)))、int((行(间接(1:“&行(c2:c2:c2:c12))-1)、行(--(a2:a40&a40<<<<<<>gt;a1:a39)))-1)/行(c2:c12))+1)*行(c2:c12)+mod((row(directive)(1:“行(c2:c12)*和(--(a2:a40<>a1:a39)))-1)、行(c2:c12))+1)、行(directive(“1”:和(--(a2:a40<>a1:a39))*行(c2:c12)-行(a2:a40)))
再次,将A列中数据的最后一个单元格更改为A40,并将A39更改为second to last.


接下来,将此公式输入到e4并向下填充:


=索引($A$2:$A$40,int(d4/行($C$2:$C$12)))
最后,将此公式输入f4并向下填充:

=index($c$2$c$12,mod(d4,rows($c$2$c$12)))



更改A40到A列中数据的最后一个单元格A39致倒数第二单元格。

然后将此助手列作为数组公式输入(Ctrl键+轮班+输入)一列范围内的行数与上面的公式返回的行数相同(它将丢失的对编码为一个数字)。我把它输入D4:D8以下内容:

=SMALL(IFERROR(0>MATCH(INDEX(A2:A40,INDEX(SMALL(IF(A2:A40<>A1:A39,ROW(A2:A40)-ROW(A1)),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))))),INT((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1)/ROWS(C2:C12))+1))&INDEX(C2:C12,MOD((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1),ROWS(C2:C12))+1),A2:A40&B2:B40,0),INDEX(SMALL(IF(A2:A40<>A1:A39,ROW(A2:A40)-ROW(A1)),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))))),INT((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1)/ROWS(C2:C12))+1)*ROWS(C2:C12)+MOD((ROW(INDIRECT("1:"&ROWS(C2:C12)*SUM(--(A2:A40<>A1:A39))))-1),ROWS(C2:C12))+1),ROW(INDIRECT("1:"&SUM(--(A2:A40<>A1:A39))*ROWS(C2:C12)-ROWS(A2:A40))))


再次,改变A40型到A列中数据的最后一个单元格A39型致倒数第二单元格。

接下来,将此公式输入到E4并填写:

=INDEX($A$2:$A$40,INT(D4/ROWS($C$2:$C$12)))


最后,将此公式输入到F4并填写:

=INDEX($C$2:$C$12,MOD(D4,ROWS($C$2:$C$12)))