Excel中LET内用变量作行数时MAKEARRAY无法正常工作

问题现象 #

构建甘特图时需按周匹配活动并与前一周活动对齐，使用LET函数编写公式时：

• idx变量计算的匹配结果数值正确，但将其作为MAKEARRAY的行数参数时，仅生成1行空白行
• 若将idx替换为固定数值（如4），MAKEARRAY可生成预期行数的数组

原公式（意大利语Excel环境，分号替代逗号） #

=LET(
dataAll;        PromoTbl[[SO_inizio]:[SO_ fine]];
daySel;         I$5;
cliente;        DASHBOARD!$B$2;
filtroCliente;  (PromoTbl[[L6]:[L6]]=cliente);
filterWk;   ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel+7;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel;row)))+1)>=1);
filterWkBef;  ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel-1;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel-7;row)))+1)>=1);
dataWk;         SORT(FILTER(dataAll;filtroCliente*filterWk;"NA"));
dataWkBef;      SORT(FILTER(dataAll;filtroCliente*filterWkBef;"NA"));
listWk;         BYROW(dataWk;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
listWkBef;      BYROW(dataWkBef;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
firstRow;       TAKE(listWk;1);
idx;            VALUE(IFNA(MATCH(firstRow;listWkBef;0);1));
spaces;         MAKEARRAY(idx;1;LAMBDA(r;c;"space"));
newArr;         VSTACK(DROP(spaces;1);listWk);
newArr
)

问题根源 #

firstRow是通过TAKE(listWk;1)返回的单行数组，而非单个标量值。因此MATCH(firstRow;listWkBef;0)返回的结果也是一个数组（即使仅含一个元素），而MAKEARRAY的行数参数要求是标量数值，数组输入会被Excel隐式转换，最终导致生成行数异常。

解决方案 #

将idx转换为标量值，通过INDEX提取数组中的单个元素即可：

修改idx的定义行：

idx;            VALUE(IFNA(INDEX(MATCH(firstRow;listWkBef;0);1);1));

修改后的完整公式 #

=LET(
dataAll;        PromoTbl[[SO_inizio]:[SO_ fine]];
daySel;         I$5;
cliente;        DASHBOARD!$B$2;
filtroCliente;  (PromoTbl[[L6]:[L6]]=cliente);
filterWk;   ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel+7;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel;row)))+1)>=1);
filterWkBef;  ((BYROW(PromoTbl[[SO_ fine]:[SO_ fine]];LAMBDA(row;MIN(daySel-1;row)))-BYROW(PromoTbl[[SO_inizio]:[SO_inizio]];LAMBDA(row;MAX(daySel-7;row)))+1)>=1);
dataWk;         SORT(FILTER(dataAll;filtroCliente*filterWk;"NA"));
dataWkBef;      SORT(FILTER(dataAll;filtroCliente*filterWkBef;"NA"));
listWk;         BYROW(dataWk;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
listWkBef;      BYROW(dataWkBef;LAMBDA(row;IFERROR(TEXT(CHOOSECOLS(row;1);"gg-mmm-aa") & "|" & TEXT(CHOOSECOLS(row;2);"gg-mmm-aa");"")));
firstRow;       TAKE(listWk;1);
idx;            VALUE(IFNA(INDEX(MATCH(firstRow;listWkBef;0);1);1));
spaces;         MAKEARRAY(idx;1;LAMBDA(r;c;"space"));
newArr;         VSTACK(DROP(spaces;1);listWk);
newArr
)

内容的提问来源于stack exchange，提问作者Paolo De Laurentiis