出于多种原因，计算Excel^(Excel)中一列中重复条目的数量非常有用。首先是检查重复，然后是要重复的条目，但您需要知道出现的次数。如果您需要在Excel中计算列中的重复值，请通读这篇文章。

^(Count)在Excel中^(Excel)计算列中的重复值

需要的可以使用COUNTIF函数来实现。您可以计算 Excel 中重复项的频率或^(Excel)Excel中重复项的出现顺序。

计算^(Count)Excel中列中重复值的频率

让我们假设一种情况，在这种情况下，我们需要某个值在选定列表中跨列重复的实例数。如果您只需要知道每个值的重复频率，则公式为：

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<column of first cell in the list of values>$<row of last cell in the list of values>,<cell number of the first cell in the list of values>)

在哪里，

• <值列表中第一个单元格的列> 是设置列表的列。
• <值列表中第一个单元格的行> 是设置包含值的第一个单元格的行。
• <值列表中最后一个单元格的行> 是设置包含值的最后一个单元格的行。
• <值列表中第一个单元格的单元格编号> 是跨列设置值的第一个单元格的单元格编号。

例如。如果我们有一个跨列 B 从 B3 到B11的条目列表，并且我们需要从单元格 C3 到单元格^(B11)C11的列 C 中每个值的重复数。公式将变为：

=COUNTIF($B$3:$B$11,B3)

此公式需要插入到单元格 C3 中。现在单击单元格 C3 外的任意位置，然后返回。这将激活填充^(Fill)功能。请注意^(Notice)所选单元格右下角的一个小点。单击^(Click)它并将选择（同时将公式）拉到单元格C11。

计算Excel^(Excel)中列中重复出现的顺序

就像上面的示例一样，如果您需要在条目再次出现时增加重复条目的计数，请尝试以下公式：

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<cell number of the first cell in the list of values>,<cell number of the first cell in the list of values>)

在哪里，

• <值列表中第一个单元格的列> 是设置列表的列。
• <值列表中第一个单元格的行> 是设置包含值的第一个单元格的行。
• <值列表中第一个单元格的单元格编号> 是跨列设置值的第一个单元格的单元格编号。

例如。拿起前面的例子，条目集是从单元格 B3 到单元格B11，我们需要计算 C 列中出现的次数，公式将变为：

=COUNTIF($B$3:$B3,B3)

在单元格 C3 中输入此公式，然后使用填充^(Fill)函数将公式下拉到单元格C11 。

与前一种情况不同，您会注意到条目的第一次出现标记为 1，第二次出现标记为 2，依此类推。

阅读下一篇^{(Read next)}：如何在 Excel 中合并和取消合并单元格^{(How to Merge and Unmerge cells in Excel)}。

希望能帮助到你！

How to count duplicate values in a column in Excel

Kеeping a count of the number of duplicatе entries across a сolumn in Exсel is very useful for many reasons. Thе first is to check for duplication, then for entries where duplication is intended, but you need to know the number of occurrences. If you need to count duplicate values in a column in Excel, read through this article.

Count duplicate values in a column in Excel

The needful can be achieved using the COUNTIF function. You could either count the frequency of the duplicates in Excel or the order of occurrence of the duplicates in Excel.

Count the frequency of duplicate values across a column in Excel

Let us suppose a situation in which we need the number of instances where a certain value repeats in a selected list across a column. If all you need to know is the frequency of repetition of each value, the formula would be:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<column of first cell in the list of values>$<row of last cell in the list of values>,<cell number of the first cell in the list of values>)

Where,

• <column of first cell in the list of values> is the column in which the list is set.
• <row of first cell in the list of values> is the row in which the first cell containing the values is set.
• <row of last cell in the list of values> is the row in which the last cell containing the values is set.
• <cell number of the first cell in the list of values> is the cell number of the first cell in which the value is set across the column.

Eg. If we have a list of entries across column B from B3 to B11, and we need the number of duplicates for each value in column C, from cell C3 to cell C11. The formula would become:

=COUNTIF($B$3:$B$11,B3)

This formula needs to be inserted in cell C3. Now click anywhere outside cell C3 and then back on it. This would activate the Fill function. Notice a small dot at the right-bottom corner of the selected cell. Click on it and pull the selection (and simultaneously the formula) down to cell C11.

Calculate the order of occurrence of duplicates across a column in Excel

Just as in the above example, if you need the count of the duplicate entry to increase as and when the entry reoccurs, try the following formula:

=COUNTIF($<column of first cell in the list of values>$<row of first cell in the list of values>:$<cell number of the first cell in the list of values>,<cell number of the first cell in the list of values>)

Where,

• <column of first cell in the list of values> is the column in which the list is set.
• <row of first cell in the list of values> is the row in which the first cell containing the values is set.
• <cell number of the first cell in the list of values> is the cell number of the first cell in which the value is set across the column.

Eg. Picking up the previous example, the set of entries is from cell B3 to cell B11 and we need to count of occurrences in column C, the formula would become:

=COUNTIF($B$3:$B3,B3)

Enter this formula in cell C3 and pull the formula down to cell C11 using the Fill function.

Unlike in the previous case, you will notice that the first occurrence of an entry is marked 1, the second 2, and so on.

Read next: How to Merge and Unmerge cells in Excel.

Hope it helps!

Related posts

• 如何在Excel中使用Percentile.Exc function

• 如何使用NETWORKDAYS function在Excel

• 如何计算在Excel的Yes or No entries数

• Excel，Word or PowerPoint上次无法启动

• 如何在Excel中使用Automatic Data Type feature

• 运行时error 1004，不能在Excel运行Macro

• Microsoft Excel Tutorial，用于初学者

• 如何在Excel使用EDATE and EOMONTH Functions

• 如何在Excel中使用INT and LCM Functions

• 如何使用OR function Excel确定条件是否为真

• 如何在Excel中使用DCOUNT and DCOUNTA Function

• 如何在Excel中创建Organization Chart

• Excel中Mean的Calculate Standard Deviation and Standard Error

• 如何在Excel到Insert and Calculate Formulas和Functions

• 如何更改默认File Format，用于保存Word，Excel，PowerPoint

• 如何在Microsoft Excel中使用MID and MIDB function

• Convert CSV至Excel（XLS or XLSX）在Windows 10中使用Command line

• 如何在Excel中使用Rept Function

• 如何在Excel工作表中Freeze and Split Panes

• 如何在Microsoft Excel中使用HLOOKUP function