按总数分组数据 [英] Grouping data according to total

问题描述

假设我有下表来处理：

 项目名称总单位
a 3 
b 4 
c 1 
d 5 
e 2 
f 5 
g 8 
h 12 
i 8 
j 10 
k 4 
l 7 
m 9 
n 19 
o 15 
p 6 
q 3 
  

我想让项目名称分组，总单位不超过20，例如。

所以如果我将项目加起来一个f，它会给我一个20.所以这组项目要被分组，并通过Excel给出唯一的标识符。

我想很容易地确定具体项目进入的文件号。所以一进入项目名称和总单位，就可以返回一个号码，指出项目应该进入哪个文件号。

 项目名称总单位文件号
a 3 1 
b 4 1 
c 1 1 
d 5 1 
e 2 1 
f 5 1 
g 8 2 
h 12 2 
i 8 3 
j 10 3 
k 4 4 
l 7 4 
m 9 4 
n 19 5 
o 15 6 
p 6 7 
q 3 7 
  

我希望得到的最终结果总计为总计，并且总和等于或小于20的项目名称分组并给出文件编号。

可以让Excel做这样的事情吗？

解决方案

不要做VBA，你可以用Excel的内置功能轻松做到。 SUMIF（）函数将在此帮助很多

将以下公式放入单元格 C2 （假设上面的设置）

  = IF（A2 =a，ROUNDDOWN（（B2-1）/ 20 ，0）+ 1，IF（SUMIF（$ C1：C $ 2，C1，$ B1：B $ 2）+ B2> 20，C1 + 1，C1））
   
 
 
 
 
如果名称为a 然后根据a中的单位检查需要多少个文件
 
 
对于所有其他名称：将当前文件中的先前单位（即上面单元格中的文件编号）相加，并添加当前项目单位。如果数字超过20，则添加1到文件号，否则使用相同的文件号码
 
 
 
 
 
我已经测试过了，但让我知道如果您有任何问题。
 
Assuming that I have the table below to work with:
project name    total units
a               3
b               4  
c               1
d               5
e               2
f               5
g               8
h               12
i               8
j               10  
k               4
l               7 
m               9
n               19
o               15
p               6
q               3 
I would like to have the project names grouped with the total units not exceeding 20 for example.

So if I add up project a up to f, it will give me a total of 20. So this group of projects to be grouped and given a unique identifier by Excel.

I want to easily determine which file number the specific project goes into. So as soon as I enter the project name and the total units, it can return a number to me saying which file number the project should go into. 
project name    total units   file number
a               3             1
b               4             1
c               1             1
d               5             1
e               2             1
f               5             1 
g               8             2
h               12            2
i               8             3
j               10            3
k               4             4 
l               7             4
m               9             4
n               19            5
o               15            6
p               6             7
q               3             7 
The final outcome I would like to have whereby the total units are summed up and the project names  with sum equal or less than 20 is grouped and given a file number. 

Is it possible to have Excel do such thing?
解决方案
Don't do with VBA, what you could easily do with Excel's inbuilt functions. The SUMIF() function will help a lot here

Place the following formula into cell C2 (assuming the setup above)
=IF(A2="a",ROUNDDOWN((B2-1)/20,0)+1,IF(SUMIF($C1:C$2,C1,$B1:B$2)+B2>20,C1+1,C1))
The formula is doing the following:


If the name is "a" then check how many files needed based on units in "a"
For all other names: sum the previous units in the current file (i.e. file number in cell above) and add the current project units. If the number exceeds 20 add 1 to the file number, otherwise use the same file number


I have tested this, but let me know if you have any problems.

                        
这篇关于按总数分组数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！