我的谷歌查詢有問題。我需要將幾行分組，有些是重復的。
在 F2 行，我
=ArrayFormula(query(TO_TEXT({A3:A\SE(G1:AK1>=B3:B;SE(G1:AK1<=B3:B C3:C-1;D3:D;"*");"*")});"SELECT * where Col1 IS NOT NULL ORDER BY Col1"))
的結果是：
我希望結果與下圖相同：
鏈接到作業表：
強制第 1 行中的日期：
=ARRAYFORMULA({IFERROR(QUERY(QUERY(SPLIT(FLATTEN(
IF(COLUMN(1:1)<=C3:C; A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×");
"select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "limit 0 label Col1'xx'"; 1)*1);
QUERY(QUERY(SPLIT(FLATTEN(
IF(COLUMN(1:1)<=C3:C; A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×");
"select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "offset 1"; 0)})
更新：
=ARRAYFORMULA(IFERROR({IFERROR(QUERY(QUERY(SPLIT(FLATTEN(
IF((MONTH(B3:B)=MONTH(D1))*(COLUMN(1:1)<=C3:C); A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×");
"select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "limit 0 label Col1'xx'"; 1)*1);
QUERY(QUERY(SPLIT(FLATTEN(
IF((MONTH(B3:B)=MONTH(D1))*(COLUMN(1:1)<=C3:C); A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×");
"select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "offset 1"; 0)}; "no data"))
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/350482.html