计算发生次数-Excel [英] Calculate Occurrence Number - Excel

问题描述

我有一个简单的问题，但无法从搜索中得到答案.我需要一列来计算值的第n次出现.最好在这张图片中说明

I have simple problem, but I've not be able to get an answer from searching. I require a column to calculate the number of the nth occurrence of a value. It's best explained in this picture

我需要一种计算列B的方法.

I require a method to calculate column B.

非常感谢您的帮助.

推荐答案

您是否只想提供B列中A列的不同条目的计数?还是只是添加公式以在链接中提供表格?

Are you looking to merely provide a count of the distinct entries of column A in column B? Or merely add a formula to come up with the table in your link?

如果是后者，则写在单元格B2中的公式为:

If the latter, then the formula to write in cell B2 is:

=COUNTIF(A$2:A2,A2)

然后将其复制/粘贴到B列.注-如果您的数据既是日期"又是时间"，但是该单元格的格式仅显示日期，则可能无法获得所需的结果.您需要通过底数"计算插入新列，以将日期/时间值四舍五入为日期(Excel日期时间为十进制，整数部分决定日期，其余0.0-> 1.0决定一天中的时间) )

then copy/paste it down column B. Note - if your data is both a Date and Time, but the cell is formatted to only display a date, you may not get the results you want. You'd need to interject a new column with a "floor" calculation to round the date/time value to a date (Excel date times are decimal, with integer part dictating the date, and remaining 0.0 -> 1.0 dictating the time of day)

如果您只想导出A列中不同条目的计数表，则数据透视表将为您执行此操作-简单地添加一个数据透视表以覆盖A列中的数据，然后在A列中选择A列行类别，然后将其拖到值类别中，确保将字段设置为计数".然后，您应该有一个表，其中一列中包含数据集中的不同条目，而另一列中包含它们的出现次数.

If you just want to derive a table of the counts of distinct entries in column A, then a pivot table will do this for you - simple add a pivot table to cover the data in column A, then select column A into the rows category, and then also drag it into the values category, ensuring the field is set to "Count of". You should then have a table with the distinct entries in your data set in one column, and the count of their occurrences in the other column.

这篇关于计算发生次数-Excel的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！