問題描述
IMPORTRANGE 將多個 Google 表格導入一個垂直列? (IMPORTRANGE to import multiple Google Sheets into one vertical column?)
我在使用 Google Sheets 中的 IMPORTRANGE 將多個工作表導入主工作表上的單個列時遇到了一點問題,每當更新其中一個工作表時,它就會自動在主工作表上創建新行。除了 IMPORTRANGE,還有更好的方法嗎?
參考解法
方法 1:
You can attach arrays onto each other by using embedded arrays. See more: Using arrays in Google Sheets
This is a basic array ={1,2,3,4}
and has an output that looks like this:
To append an array on‑top of another array you would use a semicolon ;
; instead of a comma ,
. Like so: ={1;2;3;4}
which will look like this:
To append multiple arrays together you would take one array {1;2;3;4}
and append it to another like: {1;2;3;4};{5;6;7;8}
and encase both with the embedded array syntax like so:
{{1;2;3;4};{5;6;7;8}}
However, one strict limitation is that both arrays need to be the same width, so if you have {{1,2};{1,2};{1,2,3}}
you will receive an error becuase not all the array elements have the same number of columns.
How to import multiple ranges on top of each other?
Using the above concepts, assuming both IMPORTRANGE()
's are the same width:
={IMPORTRANGE(sheet, range);IMPORTRANGE(sheet, range)}
Will import two ranges one on top of the other.
方法 2:
I'd suggest QUERY (that does not require authorisation). For example for a single row (Row2) from each of three sheets (1
, 2
and 3
), with the headers from '1'!A1:C1 :
=query({'1'!A1:C2;'2'!A2:C2;'3'!A2:C2})
The ;
is for vertical stacking where the locale's general separator is ,
.
(by user5045801、Douglas Gaskell、pnuts)