数组公式返回一个没有重复的数组，没有 VBA [英] Array formula to return an ARRAY without duplicates, without VBA

问题描述

我想知道是否可以从单个单元格公式返回数组，该公式经过过滤以删除重复项，并且完全基于 Excel 公式构建.

I would like to know whether it's possible to return an array from a single cell formula, which is filtered to remove duplicates, and which is built purely on Excel formulas.

我知道返回删除重复值的列表的方法(请参阅this问题)，其中列表分布在多个单元格中.但是我特别想返回一个中间数组.

I'm aware of approaches to return a list of values where the duplicates are removed (see this question), where the list is spread over multiple cells. However I specifically want to return an array intermediate.

例如对于 A1:A5 中的列表，我可以得到一个值数组 {0.1,0.2,0.2,0.7,0.3}，我想要第二个数组 >{0.1,0.2,0.7,0.3}，作为数组公式中的中间体.当前方法使用单端锚定范围(如C$1:C1)来遍历以几何方式排列数组中的项目(通过向下拖动 C 列).我想在公式中保留未迭代的数组.然后我可以像操作任何其他数组一样操作它.

E.g. For the list in A1:A5, I can get an array of values {0.1,0.2,0.2,0.7,0.3}, from which I want a second array {0.1,0.2,0.7,0.3}, as an intermediate in an array formula. Current approaches use single-end anchored ranges (like C$1:C1) to iterate through the items in the array geometrically (by dragging down column C). I would like to leave the array un-iterated, within the formula. I can then manipulate this as I would any other array.

如果可能，所有这些都应该在一个单元格中进行.

All this should take place in a single cell if possible.

MacroMarc 和 Barry Houdini 的 答案完全有效，我对每个答案都进行了速度检查 - 差异可以忽略不计(任何差异都小于测试运行之间的差异).两者都得分 ~ 1.0±0.2 ms

Both MacroMarc's and Barry Houdini's answers are perfectly valid, and I ran a speed check on each - there was negligible difference (any difference was smaller than the variation between test runs). Both scored ~ 1.0±0.2 ms

推荐答案

我为 Range (A1:A5) 使用了一个定义的名称，并将其命名为 myList.如果您愿意，您可以执行相同操作，或替换地址 $A$1:$A$5:

I have used a defined name for the Range (A1:A5) and called it myList. You can do the same, or substitute in the Address $A$1:$A$5 if you wish:

{=INDEX(myList, N(IF({1}, MODE.MULT(IF(MATCH(myList, myList, 0) = ROW(myList), ROW(myList)*{1,1})))), 1)}

如果列列表位于工作表的下方，并且由 OP 提供的更短的 minrow 例程，则上述处理不可靠:

Above wasn't robust to handle if the column list is further down the sheet, and a shorter minrow routine courtesy of OP:

{=INDEX(myList, N(IF({1}, MODE.MULT(IF(MATCH(myList, myList, 0)=ROW(myList)-MIN(ROW(myList))+1,(ROW(myList)-MIN(ROW(myList))+1)*{1,1}))), 1)}

这对你来说应该没问题.不用说，这些都是数组公式..

This should be ok for you. Needless to say, these are array formulas..

这篇关于数组公式返回一个没有重复的数组，没有 VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！