如何聚合数据“分组依据”它的父级并使用它在 Excel 中进行进一步计算（标准化）？
我在 Excel 工作簿中有一些数据。该数据是分层的，即跨工作表的数据之间存在父子关系。这是它在特定工作表中的外观：（右侧还有其他行（在其上方）和列，但对于此问题并不重要）
| Parent | Item | Score | | P1 | I1 | 3 | | P2 | I2 | 1 | | P1 | I3 | 6 | | P3 | I4 | 1.5 | | P4 | I5 | 4 |
我们需要拥有属于特定父级的所有项目的总和，即，按父级总计以获得每个父级的“项目总和”。 （根工作表不会有任何父级（即空白列），但各个工作表的结构是相同的）。需要将父母的孩子的分数“标准化”为0-1（编辑：即孩子们的分数总和必须为1）
我一直在玩使用数据透视表，我发现您可以按父级聚合数据。但我不确定如何使用该数据来标准化项目分数。更重要的是，Excel 工作表中的数据是相当动态的，从我使用数据透视表的最少经验来看，数据似乎不会自动刷新。
更重要的是，每个“子级工作表”都是从当前级别工作表生成的（使用宏）。因此，我们需要一种能够按父级汇总分数的方法，以便我们可以在复制时轻松地将其传播到下一个工作表（即使是手动完成）。
我正处于能够在 Excel 中执行“分组依据”（来自 SQL）的角落。有什么想法吗？
I have some data in an Excel Workbook. This data is hierarchical i.e., there is a parent-child relationship between the data across work-sheets. Here's how it looks in a particular worksheet: (There are other rows (above it) and columns to the right, but aren't important for this problem)
| Parent | Item | Score | | P1 | I1 | 3 | | P2 | I2 | 1 | | P1 | I3 | 6 | | P3 | I4 | 1.5 | | P4 | I5 | 4 |
We need to have a sum total of all Items belonging to a particular parent i.e., total by parent to get 'sum of items' for each parent. (The root worksheet won't have any parents (i.e., blank column), but the structure is the same across worksheets). The need is to 'normalize' the scores of the children of a parent on a scale of 0-1 (EDIT: i.e. sum of the scores of the children must sum to 1)
I've been playing around with pivot tables and I see that you can aggregate the data by parent. But I'm not sure how exactly can I use that data to normalize the item scores. More so, the data across the excel sheets is quite dynamic and from my minimal experience with pivot tables it seems data isn't being refreshed automatically.
More so, each 'child-level worksheet' is generated from the current level worksheet (using macros). So we need a way to be able to aggregate scores by parent so that we can easily have it propagated to the next worksheet when copied (even if it's to be done manually).
I'm just at a corner with being able to do a 'Group By' (from SQL) in Excel. Any ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果您需要查找与给定条件匹配的所有值的总和，请使用 SUMIF 函数。我假设“Parent”位于 A 列，“Item”位于 B 列，“Score”位于 C 列。在 D2 中，您必须输入以下公式： =SUMIF(A:A;A2;C:C ），然后复制下来。
但是，如果您想将分数按 0 到 1 的范围排列，则无需知道父母子女分数的总和：您只需知道父母的孩子的>最大分数。由于 MAXIF 函数不存在，我们将使用结合 MAX 和 IF 的数组函数。在 D2 中键入以下内容，然后按 Ctrl + Shift + Enter：=MAX(IF(A:A=A2;C:C)。应该添加括号以表明它是一个数组公式：{=MAX(IF(A: A=A2;C:C)}。现在您需要将孩子的分数除以该组的最高分数。
If you need to find the sum of all values matching a given criterion, use the SUMIF function. I'm assuming "Parent" is in column A, "Item" in column B ans "Score" in column C. In D2, you would have to put the following formula: =SUMIF(A:A;A2;C:C), and copy it down.
However, you don't need to know the sum of the scores of the children of a parent if you want to put the scores on a scale of 0 to 1: you only need to know the maximum score of the children of a parent. Because the MAXIF function doesn't exist, we will use an array function combining MAX and IF. Type this in D2 and press Ctrl + Shift + Enter: =MAX(IF(A:A=A2;C:C). The brackets should have been added to show that it's an array formula: {=MAX(IF(A:A=A2;C:C)}. Now you need to divide the score of the child by the maximum score of its group.