如何重新分配一定范围内的值而没有1到100之间的重复项?

I have a list of values between 1 and 100, essentially a sort of ranking that occassionally skips a few numbers (for example, the first ten values are 2, 6, 6, 10, 10, 10, 10, 11, 12, 13). They're ordered ascendingly, so every number will be either higher than or equal to the number above it. Now, I wish to remove all the duplicates from this list while remaining between 1 and 100. So, for example, for the values above, something like 2, 6, 7, 10, 11, 12, 13, 14, 15, 16 would work or 2, 6, 7, 8, 9, 10, 11, 12, 13, 14. However, the formulas I've tried so far will either go over 100, go under 1, or create circular references. Given the nature of the list, there's very little chance of the amount of values exceeding 100, so if that possibility can be accounted for, it'd be a nice bonus, but it's not required.

评论
ueaque
ueaque

Please create a named range for all your numbers and name it Source. As an alternative, replace the named range Source in my formulas below with the sheet address of the range where you have your numbers.

[C2] =INDEX(Source,MATCH(0,COUNTIF($C$1:$C1,Source),0))

这是一个数组公式,需要使用Ctl + Shift + Enter进行确认。请注意,COUNTIF范围是在公式所在行的上方定义了一行。它具有一个绝对值和一个相对值。向下复制公式时,范围的末尾将扩大。

您可以使用LOOKUP()函数获得类似的结果。但是由于MATCH()找到第一个实例,所以LOOKUP()返回最后一个实例。因此,以下公式将按降序返回相同的序列。 LOOKUP()能够自行循环,并且该公式不需要启用数组。只需按Enter即可正常确认。

[E2] =LOOKUP(2,1/(COUNTIF($E$1:E1,Source)=0),Source)

请注意,结果范围的定义是从绝对值和相对端开始,如上所述,从公式的行上方开始。

点赞
评论