如何将一片表格按时间分段后横向扩展成多片
Excel数据原有一片数据，记录着不同人的工作状态和时段：
A |
B |
C |
D |
E |
|
1 |
|||||
2 |
|||||
3 |
Name |
Start |
End |
Activity |
Elapsed hrs |
4 |
Krishna |
08:00 |
11:15 |
Production |
03:15 |
5 |
Ranjith |
07:00 |
10:10 |
Noload |
03:10 |
6 |
Krishna |
07:10 |
08:25 |
Break |
01:15 |
7 |
Ranjith |
07:30 |
12:30 |
Idle |
05:00 |
8 |
Krishna |
07:00 |
08:10 |
Production |
01:10 |
9 |
Ranjith |
11:55 |
14:55 |
Production |
03:00 |
计算目标：从G列开始，将该片数据从07:00-15:00每小时扩展出一片（共8片），分别计算出各种状态"Production","Idle","Noload","Tech","Break"（共5种）所耗的小时分钟数。其中前2个片区如下：
G |
H |
I |
J |
K |
L |
M |
N |
O |
P |
|
07:00-08:00 |
08:00-09:00 |
|||||||||
Production |
Idle |
Noload |
Tech |
Break |
Production |
Idle |
Noload |
Tech |
Break |
|
1:00 |
||||||||||
1:00 |
1:00 |
|||||||||
0:50 |
0:25 |
|||||||||
0:30 |
1:00 |
|||||||||
1:00 |
0:10 |
|||||||||
一行扩展多行已经有些难度了，Excel公式或power query只能勉强实现，这里难度再次提升，要求一片扩展成多片，导致以前的方法彻底不灵，有必要彻底换个新思路。
实现步骤：
1. 运行集算器（可以到润乾官网下载，用职场版，首次运行时会提示加载授权，下载个免费的就够了）
2. 用 Excel 打开要计算的文件，选中 A3:E9 区域，按 ctrl+C 复制到剪贴板。
3. 切换到集算器，选中 A1 格，注意要让光标落到 A1 的编辑状态中，用 ctrl+V，将数据粘贴进来。
4. 在集算器中继续编写脚本：
A |
B |
C |
D |
|
1 |
…(复制来的数据） |
|||
2 |
=A1.import@t() |
|||
3 |
=create(${(["Production","Idle","Noload","Tech","Break"]*8).string()}) |
/create table with 5*8 cols |
||
4 |
for A2 |
for 8 |
/loop records, loop 8 hours |
|
5 |
=max(elapse@s("07:00",3600*(B4-1)),A4.Start) |
/actual start |
||
6 |
=min(elapse@s("07:00",3600*B4),A4.End) |
/ actual end |
||
7 |
=if(C5<C6,interval@s(C5,C6),0) |
/ actual seconds |
||
8 |
=if(C7!=0,time(C7\3600,(C7%3600)\60,0)) |
/seconds to HH:mm |
||
9 |
=[null]*5 |
/5 blank cols |
||
10 |
=C9(["Production","Idle","Noload","Tech","Break"].pos(A4.Activity))=C8 |
/put HH:mm to the correct position |
||
11 |
=@|C9 |
/append up to 40 cols |
||
12 |
=A3.record(C11) |
/insert one record |
||
13 |
=C11=null |
/reset |
||
脚本函数 elapse 可计算流逝一段时间后的新时间，@s 表示流逝时间的单位是秒；函数 interval 表示 2 个时间点的间隔，@s 表示间隔的单位是秒；符号 \ 和 % 分别代表除法的商和余数。
5. 按 F9 执行，点击 A3 格，在右侧可以看到执行结果。按下 shift(以便复制列名)，点击 "copy data" 按钮，将 A3 的计算结果复制到剪贴板。
6. 返回 Excel 选中 G3 格，用 ctrl+V 粘贴，即可完成计算。最后，手工补写前两行的时间段。
英文版