vba 如何在excel上计算相同的项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7904040/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count same items on excel
提问by Java questioner
I need to count same items in excel. In excel sheet in rows with following data. (large amount of data in one column).
我需要在excel中计算相同的项目。在具有以下数据的行中的 Excel 表中。（一列中有大量数据）。
data: natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity, natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity, natural,amenity,highway,amenity,amenity,highway,shop,highway,place,place,sport,barrier amenity,highway,barrier,highway,highway,highway,amenity,amenity,amenity,amenity.
数据：自然，设施，公路，设施，设施，公路，商店，公路，地方，地方，体育，障碍设施，公路，障碍，公路，公路，公路，设施，设施，设施，设施，设施，自然，设施，公路便利设施,便利设施,高速公路,商店,高速公路,地方,地方,体育,障碍便利设施,高速公路,障碍,高速公路,高速公路,高速公路,便利设施,便利设施,便利设施,便利设施,自然,便利设施,高速公路,便利设施,便利设施,高速公路,商店,公路,地方,地方,体育,障碍设施,公路,障碍,公路,公路,公路,便利设施,便利设施,便利设施,便利设施。
From this how i can get count of amenity , count of shop.
从这里我如何获得便利设施的数量，商店的数量。
thank you
谢谢你
回答by Kash
Several ways to do it and listed out here: Count how often a value occurs
执行此操作并在此处列出的几种方法：计算值出现的频率
The Pivot table approach would be more organized and can be just refreshed if new entries are added. Insert a Pivot table and drag your "Data" field both in the Row Labelsand Valuesof the pivot table (which defaults to Count of Values).
数据透视表方法将更有条理，如果添加新条目，只需刷新即可。插入一个数据透视表并在数据透视表的行标签和值中拖动“数据”字段（默认为值计数）。
PS: Though you have tagged VBA for this question, please note this is not needed for this simple count.
PS：虽然你已经为这个问题标记了 VBA，但请注意，这个简单的计数不需要。
回答by JDunkerley
While I would go with @Kash is answer.
虽然我会和@Kash 一起去，但答案是。
If you know the row values you want, and asuming the data in column A, then you could use the formula:
如果您知道所需的行值，并假设 A 列中的数据，则可以使用以下公式：
=COUNTIF(A:A,"amenity")
replacing "amenity" with each value you want to count
用您要计算的每个值替换“舒适度”
回答by brettdj
My Duplicate Master addinis another potential solution. While for your question as asked I would normally go with the PivotTable suggestion from Kash (but using dynamic ranges to capture any data size changes), the addin provides further flexibility and output options that may be of use
我的Duplicate Master 插件是另一个潜在的解决方案。对于您提出的问题，我通常会采用 Kash 的数据透视表建议（但使用动态范围来捕获任何数据大小变化），该插件提供了进一步的灵活性和可能有用的输出选项
- From you question you want only a duplicate count, not values that only occur once (the addin can handle both dupes and uniques)
- Ignoring Case Sensitivity
- Ignoring any white spaces (spaces, line breaks, carriage returns, the non-printing Char 160)
- Ability to work across multiple sheets
- Highlighting, Deletion and Selection options as well as the summary option you need
- 根据您的问题，您只需要重复计数，而不是仅出现一次的值（插件可以处理重复和唯一值）
- 忽略大小写敏感
- 忽略任何空格（空格、换行符、回车、非打印字符 160）
- 能够跨多个工作表工作
- 突出显示、删除和选择选项以及您需要的摘要选项
