跳到主要内容

如何在Excel中按组统计数据？

在Excel中，对大多数用户而言，对范围内的数据进行计数可能很容易，但是对每个组中的数据进行计数又如何呢？ 没有直接的方法或公式来按组对数据进行计数，但是我可以告诉您一种在Excel中快速按组对数据进行计数的好方法。

使用数据透视表按组对数据进行计数

箭头蓝色右气泡 使用数据透视表按组对数据进行计数

如果要按组统计如下所示的数据，则需要插入数据透视表。

文档计数按组 1

1.选择数据范围，然后单击 插页 > 数据透视表 > 数据透视表。 看截图：

文档计数按组 2

2.然后 创建数据透视表 对话框，您可以指定在新工作表或现有工作表中创建它。 如果你检查 现有工作表，您需要选择一个单元格来放置数据透视表。 看截图：

文档计数按组 3

3。 点击 OK. 而在 数据透视表归档列表 窗格，将列的标题拖到 行标签 和 价值观 部分。 看截图：

文档计数按组 4

现在，每个组的计数都显示在数据透视表中。

文档计数按组 5

相关文章：

最佳办公生产力工具

🤖 Kutools 人工智能助手：基于以下内容彻底改变数据分析： 智能执行   |  生成代码  |  创建自定义公式  |  分析数据并生成图表  |  调用 Kutools 函数...
热门特色: 查找、突出显示或识别重复项   |  删除空白行   |  合并列或单元格而不丢失数据   |   不使用公式进行四舍五入 ...
超级查询: 多条件VLookup  |   多值VLookup  |   跨多个工作表的 VLookup   |   模糊查询 ....
高级下拉列表: 快速创建下拉列表   |  依赖下拉列表   |  多选下拉列表 ....
列管理器: 添加特定数量的列  |  移动列  |  切换隐藏列的可见性状态  |  比较范围和列 ...
特色功能: 网格焦点   |  设计图   |   大方程式酒吧   |  工作簿和工作表管理器   |  资源库 （自动文本）   |  日期选择器   |  合并工作表   |  加密/解密单元格   |  按列表发送电子邮件   |  超级筛选   |   特殊过滤器 （过滤粗体/斜体/删除线...）...
前 15 个工具集:  12 文本 工具 (添加文本, 删除字符，...）   |   50+ 图表 类型 (甘特图，...）   |   40+ 实用 公式 (根据生日计算年龄，...）   |   19 插入 工具 (插入二维码, 从路径插入图片，...）   |   12 转化 工具 (小写金额转大写, 货币兑换，...）   |   7 合并与拆分 工具 (高级组合行, 分裂细胞，...）   |   ... 和更多

使用 Kutools for Excel 增强您的 Excel 技能，体验前所未有的效率。 Kutools for Excel 提供了 300 多种高级功能来提高生产力并节省时间。  单击此处获取您最需要的功能...

产品描述

Office Tab 为 Office 带来选项卡式界面，让您的工作更加轻松

  • 在Word，Excel，PowerPoint中启用选项卡式编辑和阅读，发布者，Access，Visio和Project。
  • 在同一窗口的新选项卡中而不是在新窗口中打开并创建多个文档。
  • 每天将您的工作效率提高50％，并减少数百次鼠标单击！
Comments (9)
No ratings yet. Be the first to rate!
Sunny
Sunny
This comment was minimized by the moderator on the site
Hi, Han, I did not find a formula can directly count several specific words in a cell, but I have a formula that can count one word such as colorful in a cell, then you can count other words one by one, then use SUM function to get the total number.
=(LEN($B$2)-LEN(SUBSTITUTE($B$2,C1,"")))/LEN(C1)
https://cdn.extendoffice.com/images/stories/comments/sun-comment/doc-count-specific-words.png
Reply
Report
0
0
Han
Han
This comment was minimized by the moderator on the site
How to count specific words in a pharagraph ?
There are tens of pages explain word counting for only "1" specific word. But there is NO any answer about my question. So;

Lets say;

*In A1 to A6 (A1, A2, A3,A4,A5,A6) there are some specific words. ( colorful, single, double, triple, positive)

*And there is B1 cell which has a pharagraph. >>> Hello. I am a person thinking positive. My life is colorful.How about your life ?

*In C1 cell, I want to see how many of the words from A1 to A6 cells, in B1 cell (the pharagraph) has been used ?

Is there someone who can create a formula for this ? I could not find any answers to this question.
Reply
0
0
Xerestel
Xerestel
This comment was minimized by the moderator on the site
Thanks Sir!
Very helpful
Reply
0
0
Isla
Isla
This comment was minimized by the moderator on the site
Works brilliantly! Thanks
Reply
0
0
John B
John B
This comment was minimized by the moderator on the site
This is excellently clear and I commend you for posting this. But it does not work. I wonder if you simply cut and pasted the content from somewhere and actually know how to do this. You just wasted a lot of my time. I am apparently not the first one. You should take this down.
Reply
0
0
wtf
wtf
This comment was minimized by the moderator on the site
Doesn't work.
Reply
0
0
Richard
Richard
This comment was minimized by the moderator on the site
Thank you very much!
Reply
0
0
wuchale
wuchale
This comment was minimized by the moderator on the site
does not work does not work does not work does not work does not work does not work does not work does not work
Reply
0
0
sushant rajput
sushant rajput
This comment was minimized by the moderator on the site
if there is multiple sheet in excel when what will do ?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations