使用 Stata 编程寻找一串数字中总和超过某个值的数字组合

最近有个小伙伴问了这么一个会计实操方面的问题，问题大致是这样的，她有一列费用数据，需要根据费用来开发票，但是每笔费用开一张发票太浪费了，所以公司要求合计超过 30000 的多笔费用合开一张发票，那么该如何找到所有合计金额超过 30000 的费用组合呢？

首先我们用 Stata 生成一个示例数据：

cd "~/Desktop"
* 生成示例数据
clear all
set obs 500
gen 序号 = _n
set seed 1234
gen 费用 = uniform() * 10000

list in 1/10

*>     +-----------------+
*>     | 序号       费用 |
*>     |-----------------|
*>  1. |    1   9472.316 |
*>  2. |    2   522.2338 |
*>  3. |    3   9743.183 |
*>  4. |    4   9457.483 |
*>  5. |    5   1856.478 |
*>     |-----------------|
*>  6. |    6   9487.334 |
*>  7. |    7   8825.376 |
*>  8. |    8   9440.776 |
*>  9. |    9   894.2585 |
*> 10. |   10   7505.445 |
*>     +-----------------+

例如前 5 笔费用的总和是 31051.7，刚好大于 30000，可以合开一张发票。那么如何按顺序找到所有这种刚好超过 30000 的组合呢？当然不能重复对某比费用开票。

下面我们就通过 Stata 编程来解决这个问题：

gen group = .
local j = 1
forval i = 1/`=_N' {
 local tempsum = 0
 forval z = `j'/`=_N' {
  local tempsum = `tempsum' + 费用[`z']
  if `tempsum' > 30000 {
   qui replace group = `z' if _n == `z'
   local j = `z' + 1
   continue, break
  }
 }
 di "`j'"
}

这里我进行了两层的循环，外层循环 i 循环的是所有的观测值，内层循环 j 找到每个超过 30000 的费用组合，group 变量用来标记每个组合结果：

list in 1/10

*>     +-------------------------+
*>     | 序号       费用   group |
*>     |-------------------------|
*>  1. |    1   9472.316       . |
*>  2. |    2   522.2338       . |
*>  3. |    3   9743.183       . |
*>  4. |    4   9457.483       . |
*>  5. |    5   1856.478       5 |
*>     |-------------------------|
*>  6. |    6   9487.334       . |
*>  7. |    7   8825.376       . |
*>  8. |    8   9440.776       . |
*>  9. |    9   894.2585       . |
*> 10. |   10   7505.445      10 |
*>     +-------------------------+

group = 5 就表示前 5 组是一个合适的开票组合，group = 10 表示 6～10 是一个合适的开票组合，以此类推。

可以看到最后有 5 笔费用凑不齐 3w，所以填充 group 的时候最后三个没法填充：

forval i = 1/10000{
 qui replace group = group[_n + 1] if missing(group)
 qui count if missing(group)
 if r(N) <= 5 {
  continue, break
 }
}

最后剩余的 5 笔费用合开一张发票：

replace group = _N if missing(group)

然后计算每组的和：

bysort group: egen sum = sum(费用)
bysort group: replace sum = . if _n > 1
save temp, replace

list in 1/10

*>     +------------------------------------+
*>     | 序号       费用   group        sum |
*>     |------------------------------------|
*>  1. |    1   9472.316       5    31051.7 |
*>  2. |    2   522.2338       5          . |
*>  3. |    3   9743.183       5          . |
*>  4. |    4   9457.483       5          . |
*>  5. |    5   1856.478       5          . |
*>     |------------------------------------|
*>  6. |    6   9487.334      10   36153.19 |
*>  7. |    7   8825.376      10          . |
*>  8. |    8   9440.776      10          . |
*>  9. |    9   894.2585      10          . |
*> 10. |   10   7505.445      10          . |
*>     +------------------------------------+

由于这位小伙伴最后还想得到一个 Excel 文件，然后 sum 一列需要使用合并单元格。所以我们需要再把这个数据输出成 Excel 文件。

如果直接输出的话就没法设置合并单元格，所以我们可以使用 putexcel 按照我们想要的格式写入 Excel 文件。

由于 sum 列需要使用合并单元格的格式写入，所以我们需要先生成每个合并单元格的范围：

gen z = _n
bysort group: replace z = . if _n != 1 & _n != _N
keep group z
drop if missing(z)
replace z = z + 1
tostring z, replace
bysort group: gen z1 = "C" + z[1] + ":" + "C" + z[_N]
drop z
duplicates drop group, force
save temp2, replace

list in 1/10

*>     +-----------------+
*>     | group        z1 |
*>     |-----------------|
*>  1. |     5     C2:C6 |
*>  2. |    10    C7:C11 |
*>  3. |    16   C12:C17 |
*>  4. |    24   C18:C25 |
*>  5. |    31   C26:C32 |
*>     |-----------------|
*>  6. |    38   C33:C39 |
*>  7. |    43   C40:C44 |
*>  8. |    50   C45:C51 |
*>  9. |    57   C52:C58 |
*> 10. |    64   C59:C65 |
*>     +-----------------+

例如第一组是需要把 sum 的值填充到 C2:C6 合并的单元格中。

然后就可以 temp1.dta 和 temp2.dta 了：

use temp, clear
merge m:1 group using temp2
drop _m
bysort group: replace z1 = "" if _n > 1

list in 1/10

*>     +---------------------------------------------+
*>     | 序号       费用   group        sum       z1 |
*>     |---------------------------------------------|
*>  1. |    1   9472.316       5    31051.7    C2:C6 |
*>  2. |    2   522.2338       5          .          |
*>  3. |    3   9743.183       5          .          |
*>  4. |    4   9457.483       5          .          |
*>  5. |    5   1856.478       5          .          |
*>     |---------------------------------------------|
*>  6. |    6   9487.334      10   36153.19   C7:C11 |
*>  7. |    7   8825.376      10          .          |
*>  8. |    8   9440.776      10          .          |
*>  9. |    9   894.2585      10          .          |
*> 10. |   10   7505.445      10          .          |
*>     +---------------------------------------------+

然后就可以写入 Excel 文件了：

* 保存到 excel 文件
putexcel set "temp.xlsx", replace
qui {
 putexcel A1 = "序号"
 putexcel B1 = "费用"
 putexcel C1 = "开票"
}
forval i = 1/`=_N' {
 di "`i'"
 qui {
  putexcel A`=`i' + 1' = `=序号[`i']'
  putexcel B`=`i' + 1' = `=费用[`i']'
  if !missing(`=sum[`i']') {
   putexcel `=z1[`i']' = `=sum[`i']', merge hcenter vcenter
  }
 }
}
putexcel save

这样我们就完整了这个工作！

点击这里跳转到 RStata 短书平台获取附件：使用 Stata 编程寻找一串数字中总和超过某个值的数字组合