某Excel表格如下。
A | B | C | D | |
1 | CreditBankCode | BeneficiaryName | Narration | Amount |
2 | 011 | BENSON MATHIAS | April 2024 Salary | 72558.95 |
3 | 057 | SAIFULLAHI RABIU | April 2024 Salary | 45000 |
4 | 076 | ABDULKADIR AUWALU | April 2024 Salary | 55000 |
5 | 001 | 001ABDULKADIR | April 2024 Salary | 85000 |
6 | 002 | 002ABDULKADIR | April 2024 Salary | 105000 |
7 | 003 | 003ABDULKADIR | April 2024 Salary | 115000 |
要求将一行拆成多行,规则是:如果Amount小于50000则不拆分;如果Amount大于50000,则每30000拆成一条记录。
A | B | C | D | |
10 | CreditBankCode | BeneficiaryName | Narration | Amount |
11 | 011 | BENSON MATHIAS | April 2024 Salary | 30000 |
12 | 011 | BENSON MATHIAS | April 2024 Salary | 42558.95 |
13 | 057 | SAIFULLAHI RABIU | April 2024 Salary | 45000 |
14 | 076 | ABDULKADIR AUWALU | April 2024 Salary | 30000 |
15 | 076 | ABDULKADIR AUWALU | April 2024 Salary | 25000 |
16 | 001 | 001ABDULKADIR | April 2024 Salary | 30000 |
17 | 001 | 001ABDULKADIR | April 2024 Salary | 30000 |
18 | 001 | 001ABDULKADIR | April 2024 Salary | 25000 |
19 | 002 | 002ABDULKADIR | April 2024 Salary | 30000 |
20 | 002 | 002ABDULKADIR | April 2024 Salary | 30000 |
21 | 002 | 002ABDULKADIR | April 2024 Salary | 45000 |
22 | 003 | 003ABDULKADIR | April 2024 Salary | 30000 |
23 | 003 | 003ABDULKADIR | April 2024 Salary | 30000 |
24 | 003 | 003ABDULKADIR | April 2024 Salary | 30000 |
25 | 003 | 003ABDULKADIR | April 2024 Salary | 25000 |
使用 SPL XLL,输入公式:
=spl("=E@b(?.news((t=~4,100.iterate@a(30000,,!if(t>50000,t-=30000))|t);?.~1,?.~2,?.~3,~))",A2:D7)
函数 E@b 去掉表格的标题,news 根据规则将一行拆为多行,100.iterate 循环迭代 100 次,符合条件时停止迭代(这里假设最多拆分 100 行,可根据需要扩大)。~ 表示序列的当前成员,~1 表示当前成员的第 1 个子成员,符号 | 用于合并序列。