[算表] 覺得公式太冗長...
軟體: EXCEL
版本: 任何版本
自己做了一個記錄悠遊卡消費/餘額的表如下,
A | B | C | D | E |
1 | DATE CAT AMT DES REM
2 | 1 悠遊卡: 141
3 | 2 Traffic 24 MRT 悠遊卡: 117
4 | Traffic 17 MRT 悠遊卡: 100
5 | 3 Traffic 0 加值: 300 悠遊卡: 400
6 | 4
7 | 5
8 | 6 Traffic 28 MRT 悠遊卡: 372
9 | Traffic 28 MRT 悠遊卡: 344
10 | 7
E4=IF(LEFT(D4,2)="加值",
LEFT(E3,5)&(RIGHT(E3,3)+RIGHT(D4,3)),
LEFT(E3,5)&(RIGHT(E3,3)-C4))
E5=IF(LEFT(D5,2)="加值",
LEFT(E4,5)&(RIGHT(E4,3)+RIGHT(D5,3)),
LEFT(E4,5)&(RIGHT(E4,3)-C5))
E8=IF(LEFT(D8,2)="加值",
LEFT(E5,5)&(RIGHT(E5,3)+RIGHT(D8,3)),
LEFT(E5,5)&(RIGHT(E5,3)-C8))
總覺得自己的公式寫的很冗長,有欄位總要被重覆判斷好幾次,
有沒有高手可以指導一下,讓公式變的簡潔一點?
謝謝大家!
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 124.10.83.252
→
12/18 15:20, , 1F
12/18 15:20, 1F
→
12/18 15:35, , 2F
12/18 15:35, 2F
→
12/18 15:36, , 3F
12/18 15:36, 3F
→
12/18 15:37, , 4F
12/18 15:37, 4F
→
12/18 15:47, , 5F
12/18 15:47, 5F
→
12/18 16:10, , 6F
12/18 16:10, 6F
→
12/18 16:56, , 7F
12/18 16:56, 7F