7.12 区间关联：利用关联表
例 1：
有数据如下：
Table1.xlsx:
| Quantity | Price |
|---|---|
| 221 | |
| 87 | |
| 33 | |
| 73 | |
| 162 | |
| 227 | |
| 403 | |
| 288 | |
| 78 | |
| 213 | |
| 374 | |
| 152 |
Table2.xlsx:
| StartQuantity | EndQuantity | Price |
|---|---|---|
| 0 | 50 | 15 |
| 50 | 100 | 13.75 |
| 100 | 300 | 13 |
| 300 | 500 | 12.5 |
计算 Table1 中 Price 列的值，计算规则是用 Quantity 在 Table2 中查找，若数量大于 StartQuantity 且小于等于 EndQuantity，则返回此行的 Price，写入 Table1 中的 Price 列。
脚本：
| A | |
|---|---|
| 1 | =T(“Table2.xlsx”) |
| 2 | =T(“Table1.xlsx”).run(Price=A1.segp@r(StartQuantity,Quantity).Price) |
A2 用 segp 函数查询 Quantity 的值位于 Table2 的 StartQuantity 构成的区间的哪个段号，再从 Table2 中取出对应段号的行的价格返回。选项 @r 表示形成左开右闭的区间，比如数量 50 应该算成是第 1 行所在的区间。
运行结果：
| Quantity | Price |
|---|---|
| 221 | 13 |
| 87 | 13.75 |
| 33 | 15 |
| 73 | 13.75 |
| 162 | 13 |
| 227 | 13 |
| 403 | 12.5 |
| 288 | 13 |
| 78 | 13.75 |
| 213 | 13 |
| 374 | 12.5 |
| 152 | 13 |
例 2：
有汽车充电数据如下 (Table3.xlsx)：
| CardId | Starttime | Endtime | Quantity | Price |
|---|---|---|---|---|
| 0000012541 | 2018/4/30 17:11:28 | 2018/4/30 18:20:17 | 47.32 | |
| 0000012541 | 2018/4/30 15:04:54 | 2018/4/30 16:07:07 | 42.7 | |
| 0000012541 | 2018/4/30 14:49:22 | 2018/4/30 14:49:45 | 0 | |
| 0000012541 | 2018/4/30 13:56:16 | 2018/4/30 14:46:00 | 35.88 | |
| 0000012541 | 2018/4/24 18:00:19 | 2018/4/24 18:00:38 | 0 | |
| 0000012541 | 2018/4/24 11:31:54 | 2018/4/24 11:33:05 | 0.13 | |
| 0000012541 | 2018/4/24 11:31:00 | 2018/4/24 11:31:54 | 0 | |
| 0000012541 | 2018/4/24 11:04:45 | 2018/4/24 11:05:45 | 0.13 | |
| 0000012541 | 2018/4/21 17:34:06 | 2018/4/21 17:35:00 | 0.13 | |
| 0000012541 | 2018/4/21 17:32:32 | 2018/4/21 17:33:37 | 0.17 | |
| 0000012541 | 2018/4/21 17:27:25 | 2018/4/21 17:28:38 | 0.35 | |
| 0000012882 | 2018/5/31 19:20:05 | 2018/5/31 20:22:39 | 45.92 | |
| 0000012881 | 2018/5/28 6:46:01 | 2018/5/28 7:19:06 | 24.17 | |
| 0000012881 | 2018/5/24 6:52:40 | 2018/5/24 7:23:08 | 22.22 | |
| 0000012881 | 2018/5/24 6:50:33 | 2018/5/24 6:51:32 | 0 | |
| 0000012881 | 2018/5/19 15:08:47 | 2018/5/19 15:44:21 | 25.74 | |
| 0000012881 | 2018/5/19 15:07:14 | 2018/5/19 15:08:04 | 0.13 | |
| 0000012882 | 2018/5/18 14:36:07 | 2018/5/18 14:36:22 | 0 | |
| 0000012882 | 2018/5/18 14:23:44 | 2018/5/18 14:23:57 | 0 | |
| 0000012882 | 2018/5/18 14:22:29 | 2018/5/18 14:23:44 | 0 | |
| 0000012882 | 2018/5/18 8:51:31 | 2018/5/18 9:32:28 | 29.6 | |
| 0000012882 | 2018/5/18 8:47:20 | 2018/5/18 8:48:38 | 0.44 | |
| 0000012882 | 2018/5/18 8:46:01 | 2018/5/18 8:47:20 | 0 | |
| 0000012882 | 2018/5/18 8:40:14 | 2018/5/18 8:43:48 | 2.05 | |
| 0000012882 | 2018/5/18 8:39:23 | 2018/5/18 8:40:14 | 0 | |
| 0000012882 | 2018/5/18 8:36:52 | 2018/5/18 8:37:26 | 0 | |
| 0000012882 | 2018/5/18 8:23:55 | 2018/5/18 8:26:14 | 0.8 | |
| 0000014529 | 2018/5/15 15:45:25 | 2018/5/15 15:47:09 | 0.58 | |
| 0000014529 | 2018/5/15 15:44:04 | 2018/5/15 15:44:42 | 0 | |
| 0000014529 | 2018/5/15 15:43:03 | 2018/5/15 15:44:04 | 0 | |
| 0000012882 | 2018/5/15 15:41:29 | 2018/5/15 15:42:17 | 0.05 | |
| 0000012881 | 2018/5/15 15:26:44 | 2018/5/15 15:27:00 | 0 | |
| 0000012881 | 2018/5/15 14:39:42 | 2018/5/15 15:26:05 | 34.82 | |
| 0000015872 | 2018/5/15 14:36:48 | 2018/5/15 14:37:14 | 0 | |
| 0000012881 | 2018/5/15 14:30:08 | 2018/5/15 14:36:04 | 3.93 | |
| 0000012881 | 2018/5/15 14:29:05 | 2018/5/15 14:30:08 | 0 | |
| 0000012881 | 2018/5/15 14:27:47 | 2018/5/15 14:28:26 | 0 |
有充电时段对应的电价数据如下 (Table4.xlsx)：
| StartHour | 6 | 8 | 12 | 16 | 22 |
| EndHour | 8 | 12 | 16 | 22 | 6 |
| Price | 0.9094 | 1.0354 | 0.6574 | 0.9094 | 0.4054 |
计算 Table3 中 Price 列的价格，计算规则是用充电开始时间 Starttime 的小时数在 Table4 中查找所处时段的价格。
脚本：
| A | |
|---|---|
| 1 | =T@b(“Table4.xlsx”) |
| 2 | =T(“Table3.xlsx”).run(Price=A1(3).array().to(2,)(A1(1).array().to(2,).pseg(hour(Starttime)))) |
A2 用 Table4 中第 1 行第 2 列开始的 StartHour 序列形成区间，在此区间查找 Table3 中 Starttime 中的小时所处的分段号，从 Table4 第 3 行的 Price 中取出对应分段号的价格赋给 Table3 的 Price 字段。
运行结果：
| CardId | Starttime | Endtime | Quantity | Price |
|---|---|---|---|---|
| 0000012541 | 2018-04-30 17:11:28 | 2018-04-30 18:20:17 | 47.32 | 0.9094 |
| 0000012541 | 2018-04-30 15:04:54 | 2018-04-30 16:07:07 | 42.7 | 0.6574 |
| 0000012541 | 2018-04-30 14:49:22 | 2018-04-30 14:49:45 | 0 | 0.6574 |
| 0000012541 | 2018-04-30 13:56:16 | 2018-04-30 14:46:00 | 35.88 | 0.6574 |
| 0000012541 | 2018-04-24 18:00:19 | 2018-04-24 18:00:38 | 0 | 0.9094 |
| 0000012541 | 2018-04-24 11:31:54 | 2018-04-24 11:33:05 | 0.13 | 1.0354 |
| 0000012541 | 2018-04-24 11:31:00 | 2018-04-24 11:31:54 | 0 | 1.0354 |
| 0000012541 | 2018-04-24 11:04:45 | 2018-04-24 11:05:45 | 0.13 | 1.0354 |
| 0000012541 | 2018-04-21 17:34:06 | 2018-04-21 17:35:00 | 0.13 | 0.9094 |
| 0000012541 | 2018-04-21 17:32:32 | 2018-04-21 17:33:37 | 0.17 | 0.9094 |
| 0000012541 | 2018-04-21 17:27:25 | 2018-04-21 17:28:38 | 0.35 | 0.9094 |
| 0000012882 | 2018-05-31 19:20:05 | 2018-05-31 20:22:39 | 45.92 | 0.9094 |
| 0000012881 | 2018-05-28 06:46:01 | 2018-05-28 07:19:06 | 24.17 | 0.9094 |
| 0000012881 | 2018-05-24 06:52:40 | 2018-05-24 07:23:08 | 22.22 | 0.9094 |
| 0000012881 | 2018-05-24 06:50:33 | 2018-05-24 06:51:32 | 0 | 0.9094 |
| 0000012881 | 2018-05-19 15:08:47 | 2018-05-19 15:44:21 | 25.74 | 0.6574 |
| 0000012881 | 2018-05-19 15:07:14 | 2018-05-19 15:08:04 | 0.13 | 0.6574 |
| 0000012882 | 2018-05-18 14:36:07 | 2018-05-18 14:36:22 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 14:23:44 | 2018-05-18 14:23:57 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 14:22:29 | 2018-05-18 14:23:44 | 0 | 0.6574 |
| 0000012882 | 2018-05-18 08:51:31 | 2018-05-18 09:32:28 | 29.6 | 1.0354 |
| 0000012882 | 2018-05-18 08:47:20 | 2018-05-18 08:48:38 | 0.44 | 1.0354 |
| 0000012882 | 2018-05-18 08:46:01 | 2018-05-18 08:47:20 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:40:14 | 2018-05-18 08:43:48 | 2.05 | 1.0354 |
| 0000012882 | 2018-05-18 08:39:23 | 2018-05-18 08:40:14 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:36:52 | 2018-05-18 08:37:26 | 0 | 1.0354 |
| 0000012882 | 2018-05-18 08:23:55 | 2018-05-18 08:26:14 | 0.8 | 1.0354 |
| 0000014529 | 2018-05-15 15:45:25 | 2018-05-15 15:47:09 | 0.58 | 0.6574 |
| 0000014529 | 2018-05-15 15:44:04 | 2018-05-15 15:44:42 | 0 | 0.6574 |
| 0000014529 | 2018-05-15 15:43:03 | 2018-05-15 15:44:04 | 0 | 0.6574 |
| 0000012882 | 2018-05-15 15:41:29 | 2018-05-15 15:42:17 | 0.05 | 0.6574 |
| 0000012881 | 2018-05-15 15:26:44 | 2018-05-15 15:27:00 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:39:42 | 2018-05-15 15:26:05 | 34.82 | 0.6574 |
| 0000015872 | 2018-05-15 14:36:48 | 2018-05-15 14:37:14 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:30:08 | 2018-05-15 14:36:04 | 3.93 | 0.6574 |
| 0000012881 | 2018-05-15 14:29:05 | 2018-05-15 14:30:08 | 0 | 0.6574 |
| 0000012881 | 2018-05-15 14:27:47 | 2018-05-15 14:28:26 | 0 | 0.6574 |
