問題描述
從其他行的組合創建行 (Create rows from combination of other rows)
我正在尋找一種基於兩個不同表重複記錄/行的方法，如下所示：
表 1：
Col1 Col2
A 101
B 102
C 103
表 2：
Col3
1
2
3
重複結果：
Col1 Col2 Col3
A 101 1
B 102 1
C 103 1
A 101 2
B 102 2
C 103 2
A 101 3
B 102 3
C 103 3
在“Microsoft Access”或 SQL 中，我可以通過以下方式快速完成使用 SQL 選擇字符串：
SELECT table1.column1, table2.column1
FROM table1, table2
但我想知道是否有任何方法可以在具有查詢功能等的谷歌表格中做到這一點。
參考解法
方法 1:
You can accomplish this via a custom function created in Google Apps Script. To achieve this, follow these steps:
- In your spreadsheet, select Tools > Script editor to open a script bound to your file.
- Copy this function in the script editor, and save the project:
function REPEAT(table1, table2) {
var output = [];
output.push([]);
for (var i = 0; i < table1[0].length; i++) {
output[0].push(table1[0][i]);
}
for (var i = 0; i < table2[0].length; i++) {
output[0].push(table2[0][i]);
}
for (var i = 1; i < table2.length; i++) {
for (var j = 1; j < table1.length; j++) {
output.push([]);
for (var k = 0; k < table1[j].length; k++) {
output[output.length ‑ 1].push(table1[j][k]);
}
for (var k = 0; k < table2[i].length; k++) {
output[output.length ‑ 1].push(table2[i][k]);
}
}
}
return output;
}
- Now, if you go back to your spreadsheet, you can use this function just as you would do with any other function. You just have to provide the appropriate table ranges as parameters, as you can see here:
I hope this is of any help.
方法 2:
See if this works
=ArrayFormula(sort(split(transpose(split(textjoin(",", 1, split(textjoin(",", 1, A2:A4&"_"&transpose(B2:B4)),",")&"_"&A10:A12), ",")), "_")))
Change range to suit.
(by Jacob Spitzer、Iamblichus、JPV)