我在 1 列中有一個員工時間表資料，需要將其轉換為行和列。
1 列的原始資料
mon
02/05/2022
9:04
12:47
13:52
18:09
-
-
tue
03/05/2022
9:13
13:03
14:06
18:10
-
-
wed
04/05/2022
8:58
12:57
14:08
18:19
-
-
thu
05/05/2022
8:52
12:26
13:32
17:39
-
-
fri
06/05/2022
10:13
13:10
14:15
18:14
-
-
sat
07/05/2022
-
-
-
-
-
-
sun
08/05/2022
-
-
-
-
-
-
mon
09/05/2022
8:49
12:46
13:48
18:11
-
-
tue
10/05/2022
8:52
12:51
13:55
18:07
-
-
將資料轉置為長度為 8 列的表
mon 02/05/2022 9:04 12:47 13:52 18:09 - -
tue 03/05/2022 9:13 13:03 14:06 18:10 - -
wed 04/05/2022 8:58 12:57 14:08 18:19 - -
thu 05/05/2022 8:52 12:26 13:32 17:39 - -
fri 06/05/2022 10:13 13:10 14:15 18:14 - -
sat - - - - - - -
sun - - - - - - -
mon 09/05/2022 8:49 12:46 13:48 18:11 - -
tue 10/05/2022 8:52 12:51 13:55 18:07 - -
我該怎么做？我嘗試了=TRANSPOSE(QUERY函式，但沒有得到我期望的結果。
謝謝！
uj5u.com熱心網友回復：
嘗試：
={QUERY(A1:A, "skipping 8", ),
QUERY(A2:A, "skipping 8", ),
QUERY(A3:A, "skipping 8", ),
QUERY(A4:A, "skipping 8", ),
QUERY(A5:A, "skipping 8", ),
QUERY(A6:A, "skipping 8", ),
QUERY(A7:A, "skipping 8", ),
QUERY(A8:A, "skipping 8", )}
或者：
={QUERY(A:A, "skipping 8", ),
QUERY(QUERY(A:A, " offset 1", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 2", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 3", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 4", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 5", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 6", ), "skipping 8", ),
QUERY(QUERY(A:A, " offset 7", ), "skipping 8", )}
uj5u.com熱心網友回復：
嘗試：
={FILTER(A:A, MOD(ROW(A:A)-1, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-2, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-3, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-4, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-5, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-6, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-7, 8)=0),
FILTER(A:A, MOD(ROW(A:A)-8, 8)=0)}
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/478610.html