数组公式可返回无重复，无VBA的ARRAY [英] 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.

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

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's 和 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的ARRAY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！