wps/excel函数技巧：pivotby/groupby字段顺序调整的几种办法

如图，源数据U2:W681是化学式的元素列表，同时会有重复，要求进行透视，但透视后的化学式名称顺序，及元素顺序有要求，顺序如下表：

一般的公式如下：

=PIVOTBY(U2:U681,V2:V681,W2:W681,LAMBDA(x,IF(SUM(x)=0,"",SUM(x))),0,0,,0)

透视后结果如下：

我们可以看到数据都是对的，但顺序不符合要求。有以下办法可以实现按要求顺序呈现数据：

方法一：

根据顺序要求表格，在前面加上排序数字 ，后面再用textafter函数去除。整体公式如下：

=LET(a,XLOOKUP(U2:U681,A2:A11,ROW(A2:A11)+100&"-"&A2:A11),b,XLOOKUP(V2:V681,TRANSPOSE(B1:R1),TRANSPOSE(COLUMN(B1:R1)+100&"-"&B1:R1)),r,PIVOTBY(a,b,W2:W681,LAMBDA(x,IF(SUM(x)=0,"",SUM(x))),0,0,,0),IFERROR(TEXTAFTER(r,"-"),r))

方法二：

后期根据顺序要求使用reduce和xlookup重新排列

=LET(a,PIVOTBY(U2:U681,V2:V681,W2:W681,LAMBDA(x,IF(SUM(x)=0,"",SUM(x))),0,0,,0),REDUCE(A1:R1,A2:A11,LAMBDA(x,y,VSTACK(x,XLOOKUP(HSTACK(y,B1:R1),IF(TAKE(a,1)="",y,TAKE(a,1)),FILTER(a,TAKE(a,,1)=y))))))

方法三：

后期使用sortby函数进行两次排序

=LET(a,PIVOTBY(U2:U681,V2:V681,W2:W681,LAMBDA(x,IF(SUM(x)=0,"",SUM(x))),0,0,,0),b,SORTBY(a,XMATCH(TAKE(a,,1),VSTACK("",A2:A11)),SORTBY(b,XMATCH(TAKE(a,1),HSTACK("",B1:R1)))