問題描述
使用 ARRAYFORMULA 從查找表中查找源表中的所有匹配行/值 (use ARRAYFORMULA to find all matching rows/values in a source table from a lookup table)
我有一個像這樣的源表。如您所見，每個 Lookup Value 可以有一個或多個 Result。
| Lookup Value | Result |
|‑‑‑‑‑‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑|
| a | a1 |
| a | a2 |
| a | a3 |
| b | b1 |
| b | b2 |
| c | c1 |
| c | c2 |
然後我有一個像這樣的輸入表：
| queries | results | | | | | |
|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|
| a | ... | ... | ... | ... | ... | ... |
| c | ... | ... | ... | ... | ... | ... |
每一行的 ... 應該是查找表中的轉置值。因此，例如，上表如下所示：
| queries | results | | |
|‑‑‑‑‑‑‑‑‑|‑‑‑‑‑‑‑‑‑|‑‑‑‑|‑‑‑‑|
| a | a1 | a2 | a3 |
| c | c1 | c2 | |
現在我必須使用多個公式，如下所示：
我正在嘗試用單個 ARRAYFORMULA 替換它，但它沒有 t 似乎工作。
還有其他方法嗎？基本上是從查找表中查找所有匹配的行，然後轉置它們？
參考解法
方法 1:
Suppose your "Lookup Value" and "Result" data run from A1:B (with headers in A1 and B1). And suppose that your "queries" list is in D1:D (header in D1) with the "results" header in E1.
Depending on the maximum number of possible matches in B:B for any value in A:A, you could use this in E2:
=ArrayFormula(IFERROR(VLOOKUP(D2:D,QUERY(FILTER({A2:B,COUNTIFS(A2:A,A2:A,ROW(A2:A),"<="&ROW(A2:A))},A2:A<>""),"Select Col1, MAX(Col2) Group By Col1 Pivot Col3"),SEQUENCE(1,10,2),0)))
If your maximum possible matches is fewer than 10 or more than 10, feel free to edit the second argument of the SEQUENCE function accordingly.
Understand that, with such an array formula that is asked to process a range, you wouldn't be able to put other data anywhere below or to the right of your "queries and results" that you've asked the array formula to assess or fill. So if you want data under it, you'll need to limit your VLOOKUP from D2:D to, say, D2:D50 (or whatever your max queries range would be). Likewise, if that second argument of the SEQUENCE function is 10, you'll have "reserved" 10 columns (i.e., E:N) for possible results, and you won't be able to put data there or you'll "break" the array formula. That being the case, you may want to give yourself some sort of visual line of demarcation around the area you've reserved for the formula's use (e.g., change the background color of the block or place a border around it, etc.).
方法 2:
try:
=ARRAYFORMULA(IFERROR(VLOOKUP(D:D, SPLIT(TRANSPOSE(QUERY(TRANSPOSE(IF(ISNUMBER(
QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "offset 1", 0)),
QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "limit 0", 1),
QUERY(QUERY(A:B, "select A,count(A) where A is not null group by A pivot B"), "offset 1", 0)))
,,999^99)), " "), TRANSPOSE(ROW(INDIRECT("A2:A"&COUNTUNIQUE(B:B)))), 0)))
(by IMTheNachoMan、Erik Tyler、player0)