解答网友提问。在一个二维表中,如何分组计算值区域中每个值的个数?
我把这题的难度再提高一点,如果行标题还有重复项,如何实现汇总且计数?
案例:
下图1是各员工的业绩得分表,其中的姓名有不规律的重复项。
请计算出每个人各项评分的总数,效果如下图2、3所示。
![](https://p0.ssl.img.360kuai.com/t01e635d8b322dfe603.jpg?size=787x278)
![](https://p0.ssl.img.360kuai.com/t0178f38611bee34cfa.jpg?size=273x215)
![](https://p0.ssl.img.360kuai.com/t01b42fc3bb0960b5f8.jpg?size=208x190)
解决方案1:
1.按Alt+D+P -->在弹出的对话框中选择“多重合并计算数据区域”-->点击“下一步”
![](https://p0.ssl.img.360kuai.com/t01218d846f96ea5db2.jpg?size=385x379)
2.点击“下一步”。
![](https://p0.ssl.img.360kuai.com/t0127df58af6895fa4f.jpg?size=407x268)
3.选中整个数据表区域 -->点击“添加”按钮 -->点击“下一步”
![](https://p0.ssl.img.360kuai.com/t015279ea0857f25b08.jpg?size=359x323)
4.选择“现有工作表”及所需放置的位置 -->点击“完成”
![](https://p0.ssl.img.360kuai.com/t014f4a5921ff963d94.jpg?size=517x255)
默认的数据透视表是这样的。
![](https://p0.ssl.img.360kuai.com/t0127f68b39664fb974.jpg?size=350x714)
![](https://p0.ssl.img.360kuai.com/t0183d126daecb7edc4.jpg?size=843x296)
5.将“列”和“值”的字段对调一下。
![](https://p0.ssl.img.360kuai.com/t018e1728d3770ef44f.jpg?size=348x708)
![](https://p0.ssl.img.360kuai.com/t017f83d61a17aaa608.jpg?size=347x297)
6.点开“列标签”旁边的箭头 -->在弹出的菜单中取消勾选“(空白)”-->点击“确定”
![](https://p0.ssl.img.360kuai.com/t01b0cf8b81f72961c7.jpg?size=441x393)
这就是想要的结果。
![](https://p0.ssl.img.360kuai.com/t0178f38611bee34cfa.jpg?size=273x215)
解决方案2:
1. 选中数据表的任意单元格 -->选择菜单栏的“数据”-->“从表格”
![](https://p0.ssl.img.360kuai.com/t014892e5e5a2307de4.jpg?size=325x225)
2.在弹出的对话框中保留默认设置 -->点击“确定”
![](https://p0.ssl.img.360kuai.com/t01642e771be57bc0e1.jpg?size=214x98)
表格已经上传至PowerQuery。
![](https://p0.ssl.img.360kuai.com/t01e7d443e4c1b8a8e2.jpg?size=1200x241)
3.选中“姓名”列 -->选择菜单栏的“转换”-->“逆透视列”-->“逆透视其他列”
![](https://p0.ssl.img.360kuai.com/t01dc0eaede0e8e306e.jpg?size=564x429)
![](https://p0.ssl.img.360kuai.com/t019a809013fe967de1.jpg?size=290x785)
4.删除“属性”列。
![](https://p0.ssl.img.360kuai.com/t01e1274045c44313f8.jpg?size=414x959)
![](https://p0.ssl.img.360kuai.com/t01d81fc0528025aaac.jpg?size=199x776)
5.选择菜单栏的“添加列”-->“自定义列”
![](https://p0.ssl.img.360kuai.com/t015d0e58ef4c3f0296.jpg?size=320x152)
6.在弹出的对话框的公式处输入“1”-->点击“确定”
![](https://p0.ssl.img.360kuai.com/t01508fd43592a7ca5f.jpg?size=702x395)
![](https://p0.ssl.img.360kuai.com/t01dfa09600b1f345c3.jpg?size=294x782)
7.选中“值”列 -->选择菜单栏的“转换”-->“透视列”
![](https://p0.ssl.img.360kuai.com/t011019f4d04883d03e.jpg?size=396x971)
8.在弹出的对话框的下拉菜单中选择“自定义”-->点击“确定”
![](https://p0.ssl.img.360kuai.com/t015afacf5144b13654.jpg?size=702x224)
![](https://p0.ssl.img.360kuai.com/t01e236b638cff0bb6c.jpg?size=359x179)
9.通过拖动将列按字母顺序排列。
![](https://p0.ssl.img.360kuai.com/t01b302d0790f4e9265.jpg?size=359x180)
10.选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”
![](https://p0.ssl.img.360kuai.com/t01e15a1582d5b663f3.jpg?size=193x135)
11.在弹出的对话框中选择“表”-->选择“现有工作表”及所需上传至的位置 -->点击“加载”
![](https://p0.ssl.img.360kuai.com/t015df78d49ef3823e9.jpg?size=402x328)
右侧绿色的表格就是最终统计结果。
![](https://p0.ssl.img.360kuai.com/t01e0f06fb17fdb6422.jpg?size=1172x279)
","gnid":"9d4202dceed344b51","img_data":[{"flag":2,"img":[{"desc":"","height":"278","title":"","url":"https://p0.ssl.img.360kuai.com/t01e635d8b322dfe603.jpg","width":"787"},{"desc":"","height":"215","title":"","url":"https://p0.ssl.img.360kuai.com/t0178f38611bee34cfa.jpg","width":"273"},{"desc":"","height":"190","title":"","url":"https://p0.ssl.img.360kuai.com/t01b42fc3bb0960b5f8.jpg","width":"208"},{"desc":"","height":"379","title":"","url":"https://p0.ssl.img.360kuai.com/t01218d846f96ea5db2.jpg","width":"385"},{"desc":"","height":"268","title":"","url":"https://p0.ssl.img.360kuai.com/t0127df58af6895fa4f.jpg","width":"407"},{"desc":"","height":"323","title":"","url":"https://p0.ssl.img.360kuai.com/t015279ea0857f25b08.jpg","width":"359"},{"desc":"","height":"255","title":"","url":"https://p0.ssl.img.360kuai.com/t014f4a5921ff963d94.jpg","width":"517"},{"desc":"","height":"714","title":"","url":"https://p0.ssl.img.360kuai.com/t0127f68b39664fb974.jpg","width":"350"},{"desc":"","height":"296","title":"","url":"https://p0.ssl.img.360kuai.com/t0183d126daecb7edc4.jpg","width":"843"},{"desc":"","height":"708","title":"","url":"https://p0.ssl.img.360kuai.com/t018e1728d3770ef44f.jpg","width":"348"},{"desc":"","height":"297","title":"","url":"https://p0.ssl.img.360kuai.com/t017f83d61a17aaa608.jpg","width":"347"},{"desc":"","height":"393","title":"","url":"https://p0.ssl.img.360kuai.com/t01b0cf8b81f72961c7.jpg","width":"441"},{"desc":"","height":"215","title":"","url":"https://p0.ssl.img.360kuai.com/t0178f38611bee34cfa.jpg","width":"273"},{"desc":"","height":"225","title":"","url":"https://p0.ssl.img.360kuai.com/t014892e5e5a2307de4.jpg","width":"325"},{"desc":"","height":"98","title":"","url":"https://p0.ssl.img.360kuai.com/t01642e771be57bc0e1.jpg","width":"214"},{"desc":"","height":"241","title":"","url":"https://p0.ssl.img.360kuai.com/t01e7d443e4c1b8a8e2.jpg","width":"1200"},{"desc":"","height":"429","title":"","url":"https://p0.ssl.img.360kuai.com/t01dc0eaede0e8e306e.jpg","width":"564"},{"desc":"","height":"785","title":"","url":"https://p0.ssl.img.360kuai.com/t019a809013fe967de1.jpg","width":"290"},{"desc":"","height":"959","title":"","url":"https://p0.ssl.img.360kuai.com/t01e1274045c44313f8.jpg","width":"414"},{"desc":"","height":"776","title":"","url":"https://p0.ssl.img.360kuai.com/t01d81fc0528025aaac.jpg","width":"199"},{"desc":"","height":"152","title":"","url":"https://p0.ssl.img.360kuai.com/t015d0e58ef4c3f0296.jpg","width":"320"},{"desc":"","height":"395","title":"","url":"https://p0.ssl.img.360kuai.com/t01508fd43592a7ca5f.jpg","width":"702"},{"desc":"","height":"782","title":"","url":"https://p0.ssl.img.360kuai.com/t01dfa09600b1f345c3.jpg","width":"294"},{"desc":"","height":"971","title":"","url":"https://p0.ssl.img.360kuai.com/t011019f4d04883d03e.jpg","width":"396"},{"desc":"","height":"224","title":"","url":"https://p0.ssl.img.360kuai.com/t015afacf5144b13654.jpg","width":"702"},{"desc":"","height":"179","title":"","url":"https://p0.ssl.img.360kuai.com/t01e236b638cff0bb6c.jpg","width":"359"},{"desc":"","height":"180","title":"","url":"https://p0.ssl.img.360kuai.com/t01b302d0790f4e9265.jpg","width":"359"},{"desc":"","height":"135","title":"","url":"https://p0.ssl.img.360kuai.com/t01e15a1582d5b663f3.jpg","width":"193"},{"desc":"","height":"328","title":"","url":"https://p0.ssl.img.360kuai.com/t015df78d49ef3823e9.jpg","width":"402"},{"desc":"","height":"279","title":"","url":"https://p0.ssl.img.360kuai.com/t01e0f06fb17fdb6422.jpg","width":"1172"}]}],"original":0,"pat":"art_src_0,fts0,sts0","powerby":"cache","pub_time":1712657822000,"pure":"","rawurl":"http://zm.news.so.com/8a87f18e7fa9ac9fed77250450da8e79","redirect":0,"rptid":"21148dd07e7cc31e","rss_ext":[],"s":"t","src":"潘可弟","tag":[{"clk":"keconomy_1:excel","k":"excel","u":""}],"title":"值区域是文本的 Excel 业绩表,如何统计每个人的各类评分总数?
竺底星3849excel表格里的求和公式是什么? -
韩迹燕19164489956 ______ 如果对一个单元格内的表达式求和,需要通过“自定义名称”的方式,定义名称“求和”,数据来源=evaluate(表达式所在单元格) 然后在表达式后面的单元格输入 =求和 即可得到结果.
竺底星3849用电脑怎么计算一组数据的总和 -
韩迹燕19164489956 ______ 1、首先打开一个Excel的表格,需要计算总收益. 2、然后在工具栏找到公式选项,点击一下. 3、然后进入新的界面,找到自动求和,点击一下. 4、点击自动求和之后,进入新的界面,点击求和选项进入. 5、选中想要总和出现的单元格,和计算的一列数值,可以看到公式计算. 6、然后就会出现这一组数据的总和了.
竺底星3849电子表格中一整行或一整列求和怎么做? -
韩迹燕19164489956 ______ 准备工具/材料:装有windows 10的电脑一台,Microsoft Office 家庭和学生版 2010 excel软件. 1、打开要操作的EXCEL表格,用鼠标选择要求和的列的下一个单元格. 2、在开始选项卡中找到项目“编辑”,然后单击公式“自动求和”. 3、返回EXCEL表,发现整个列值已成功求和.
竺底星3849excel表格中求和怎么算啊 -
韩迹燕19164489956 ______ 在单元格中输入 =SUM(A1:B5) 这样就可以计算 A1到B5 2*5范围内的和
竺底星3849电子表格中如何运用自动求和公式?? -
韩迹燕19164489956 ______ 1、电脑打开Excel表格. 2、打开Excel表格后,列出要求和的数据. 3、如果是比较多的数据SUM求和公式,输入公式=SUM(E19:E37),E19是第一个数据的单元格,E37是最后一个数据的单元格. 4、输入自动求和公式后,按回车确定公式就可以了.
竺底星3849excel表格求和 -
韩迹燕19164489956 ______ 在“工具”菜单上,单击“选项”,再单击“视图”选项卡. 选中或清除“状态栏”复选框.
竺底星3849excel 怎么求总和 -
韩迹燕19164489956 ______ 很简单啊:在总金额中输入=SUM(A2:A17*B2:B17),然后再按住Shift+Ctrl+回车即可,这是求数组公式相乘的方法,实际的公式是{=SUM(A2:A17*B2:B17)},多了大括号
竺底星3849在EXCEL中怎样才能求出数据的总和
韩迹燕19164489956 ______ 全选,并留出一个放总和的单元格,在常用工具栏里点“自动求和”就行啦:)
竺底星3849Excel怎么算总和 -
韩迹燕19164489956 ______ 我用的是OFFICE 2007 首先选择要求和的一组数字,点击自动求和 在右下角已经显示了框选的数字组的总和... 如图
竺底星3849怎么用excel求和? -
韩迹燕19164489956 ______ =sum(目标求和区域)