解答网友提问。在一个二维表中，如何分组计算值区域中每个值的个数？
我把这题的难度再提高一点，如果行标题还有重复项，如何实现汇总且计数？
案例：
下图1是各员工的业绩得分表，其中的姓名有不规律的重复项。
请计算出每个人各项评分的总数，效果如下图2、3所示。
解决方案1：
1.按Alt+D+P -->在弹出的对话框中选择“多重合并计算数据区域”-->点击“下一步”
2.点击“下一步”。
3.选中整个数据表区域 -->点击“添加”按钮 -->点击“下一步”
4.选择“现有工作表”及所需放置的位置 -->点击“完成”
默认的数据透视表是这样的。
5.将“列”和“值”的字段对调一下。
6.点开“列标签”旁边的箭头 -->在弹出的菜单中取消勾选“(空白)”-->点击“确定”
这就是想要的结果。
解决方案2：
1. 选中数据表的任意单元格 -->选择菜单栏的“数据”-->“从表格”
2.在弹出的对话框中保留默认设置 -->点击“确定”
表格已经上传至PowerQuery。
3.选中“姓名”列 -->选择菜单栏的“转换”-->“逆透视列”-->“逆透视其他列”
4.删除“属性”列。
5.选择菜单栏的“添加列”-->“自定义列”
6.在弹出的对话框的公式处输入“1”-->点击“确定”
7.选中“值”列 -->选择菜单栏的“转换”-->“透视列”
8.在弹出的对话框的下拉菜单中选择“自定义”-->点击“确定”
9.通过拖动将列按字母顺序排列。
10.选择菜单栏的“主页”-->“关闭并上载”-->“关闭并上载至”
11.在弹出的对话框中选择“表”-->选择“现有工作表”及所需上传至的位置 -->点击“加载”
右侧绿色的表格就是最终统计结果。
","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":"pika","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 业绩表，如何统计每个人的各类评分总数？
阎阁阳2763如何在对多张EXCEL表格汇总 -
尚厘狭18335905068 ______ 1、在表签处点由键,点 全选工作表;2、组成工作组,这样对表格的排版就同时对所有表同时排版了.
阎阁阳2763怎么样在Excel表格快速汇总多个数据表中的数据 -
尚厘狭18335905068 ______ 1、启动软件,打开我们需要汇总的文档,我们可以看到在文档的下面有几个不同的明细表.2、在表格的下面,重命名一个表格,我们暂且命名为“汇总”,然后选择单元格中左上角的第一个单元格3、在菜单栏中选择,:数据——合并计算4、再出现的对话框中函数中选择求和5、选择表格一,把表格一中的数据全部选中6、点击右侧的添加,将选中的表格一添加到所有引用位置7、参照第六步,将其余的需要汇总的表格都添加进去8、点击确定,就得到了汇总的结果
阎阁阳2763如何把excel表格中多个工作表汇总到一个工作表中 -
尚厘狭18335905068 ______ 使用冻结法可将多个Excel表格整合成到一个表格内. 1,打开Excel表格点击菜单栏“视图”→“冻结窗格”→“冻结拆分窗格”. 2,将拆分出来的窗格如图“1-11”冻结. 3,使用“冻结首行”滚动工作表其余部分保持首行可见. 4,冻结首行内容. 5,冻结表格首列,如用户首列内容不需要改可用此冻结方案. 6,将首列内容冻结. 详细方法: http://www.officezu.com/a/excel/1266.html
阎阁阳2763多个excel表数据汇总成一个表
尚厘狭18335905068 ______ 将这些文件copy到一个文件夹(只有这些Excel,且若打开某一文件,数据就能看见——即不用点其他sheet),建一新Excel,也存到该文件夹.仅打开该新Excel,按Alt+F11,左边窗口右键点,插入模块,在右边窗口粘贴如下代码: Sub Find() ...
阎阁阳2763如何将EXCEL几个工作的数据汇总到一个工作表中? -
尚厘狭18335905068 ______ 简单的问题搞复杂了,我说的是哪个COLUMN函数,根本就不用. B3公式 =if(iserror(VLOOKUP($A3,'一月'!$A:$G,2,0)),0,VLOOKUP($A3,'一月'!$A:$G,2,0)) C3公式 =if(iserror(VLOOKUP($A3,'一月'!$A:$G,3,0)),0,VLOOKUP($A3,'一月...
阎阁阳2763多个excel表格的汇总
尚厘狭18335905068 ______ 用EXCEL的合并计算功能 http://hi.baidu.com/hbcdldyh123/blog/item/a94269fcbe521085b901a0d6.html 具体操作方法请看以上链接
阎阁阳2763如何将多个表格的内容汇总到一张excel表上 -
尚厘狭18335905068 ______ 最简单的方法就是复制和粘贴,把其他表的数据复制、粘贴到一张表上.
阎阁阳2763如何将多个工作簿里的工作表的数据汇总到一个工作表 -
尚厘狭18335905068 ______ 你好:可以在一个工作簿里建多个工资表,然后把那些工作簿的表拷过来.再汇总. 也可以用公式引用,地址引用时,工作簿要用中括号,表要用到叹号.如引用...
阎阁阳2763如何将Excel中多个不同的工作表汇总成一张总表 -
尚厘狭18335905068 ______ 如果分表的格式完全一致,那么就简单了.总表只用用公式来做. 分表数据变动,总表的汇总数就会自动更新. 不过你的要求说的不是很清楚,如果不行的话可以把表格私信给我.