如何枚举Excel中的重复行? [英] How do I enumerate duplicate rows in excel?

问题描述

我有如下数据:

Monkey
Donkey
Elephant
Panda
Donkey
Donkey
Monkey

我想用数字来区分行，为每个重复项计数:

I want to differentiate the rows by a number, counting up for every duplicate:

Monkey      1
Donkey      1
Elephant    1
Panda       1
Donkey      2
Donkey      3
Monkey      2

我设法获得了行中重复项的数量，但是我不知道如何像这样枚举它们.

I have managed to get the amount of duplicates for the rows, but I don't know how to go about enumerating them like this.

使用excel是否有可能，还是我必须编写脚本?

Is it even possible with excel, or will I have to write a script?

推荐答案

假定该列表位于A列中，从第1行开始.在单元格B1中，您可以输入:

Assume that the list is in column A, starting at row 1. In cell B1 you can enter:

=COUNTIF(A1,A1)

当然会给出1.(或者您可以输入1的硬编码值，这取决于您.)

Which will of course give 1. (Or you can just enter a hard-coded value of 1, it's up to you.)

在B2单元格中输入公式

In cell B2, enter the formula

=COUNTIF($A$1:A2,A2)

，然后将其复制到列表的其余部分.请注意，列表开头($ A $ 1)的单元格是绝对的，而列表的末尾(在这种情况下为A2，尽管随着将公式向下复制到行中而会改变)是绝对的.实际上，您要做的是从列表的开始一直到该行(包括该行)，计算匹配项的数量.因此，单元格B5将返回2，因为在$ A $ 1和A5之间有2个驴子.单元格B6将返回3，因为在$ A $ 1和A6之间有3个驴，依此类推.

and copy that down for the rest of your list. NOTE CAREFULLY the fact that the cell at the start of the list ($A$1) is absolute, and the end of the list (in this case A2, though that will change as you copy the formula down the rows) is relative. What you're essentially doing is counting up the number of matching items from the start of the list, down to and including that row. So cell B5 will return 2, since there are 2 Donkeys between $A$1 and A5. Cell B6 will return 3, since there are 3 Donkeys between $A$1 and A6, and so on.

这篇关于如何枚举Excel中的重复行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！