我想從資料源中每隔 3 行創建一個表。
1st cell: =TRIM(RIGHT(SUBSTITUTE($B1," ",REPT(" ",20)),20))
2nd cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
3rd cell: =TRIM(RIGHT(SUBSTITUTE($B7," ",REPT(" ",20)),20))
...
我希望單元格自動填充這種模式 {B1,B4,B7,B10,B13..1 3x}
但是當我選擇前三個單元格并嘗試自動填充時，電子表格會這樣做
1st cell: =TRIM(RIGHT(SUBSTITUTE($B1," ",REPT(" ",20)),20))
2nd cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
3rd cell: =TRIM(RIGHT(SUBSTITUTE($B3," ",REPT(" ",20)),20))
4th cell: =TRIM(RIGHT(SUBSTITUTE($B4," ",REPT(" ",20)),20))
5th cell: =TRIM(RIGHT(SUBSTITUTE($B7," ",REPT(" ",20)),20))
6th cell: =TRIM(RIGHT(SUBSTITUTE($B6," ",REPT(" ",20)),20))
...
我如何實作這一目標？
uj5u.com熱心網友回復：
清除一個完整的列（例如 Col C，為了在此處進行解釋）。然后將以下公式放入 C1 中（如果您想在 C1 中放置標題，則在 C2 中）：
=ArrayFormula(QUERY({IFERROR(REGEXEXTRACT(B:B,"\S $"))},"WHERE Col1 Is Not Null SKIPPING 3"))
這一個公式應該一口氣產生所有結果，沒有拖累。
REGEXEXTRACT將嘗試"\S $"從每個單元格中拉入B:B（即“所有非空格字符到字串末尾”）。如果在任何單元格（例如，空單元格）中都找不到它，IFERROR則將回傳 null 而不是錯誤。
QUERY然后對此采取行動，回傳所有非空結果的每 3 個條目。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/438820.html
標籤：谷歌表格
上一篇：如何從日期范圍中制作日期串列，并在Google表格中的行上添加其他資料？
下一篇：'requests[0].update_cells.range'中的值無效（type.googleapis.com/google.apps.sheets.v4.GridRange）