全文約3000字；

閱讀時(shí)間：約9分鐘；

聽(tīng)完時(shí)間：約18分鐘；

在PMC制定生產(chǎn)計(jì)劃時(shí)，分析工廠產(chǎn)品銷(xiāo)售數(shù)據(jù)的過(guò)程中，可能會(huì)遇到這樣的情況：數(shù)據(jù)以表格形式呈現(xiàn)，但其結(jié)構(gòu)不利于直接匯總和分析。比如參考下圖展示的《產(chǎn)品銷(xiāo)售明細(xì)表》，這是一個(gè)專為銷(xiāo)售部門(mén)設(shè)計(jì)的三維報(bào)表。報(bào)表的垂直軸（列方向），從B3到B17，詳細(xì)列出了各個(gè)銷(xiāo)售小組的信息，包括第1組、第2組直至第3組等。

水平軸（行方向）的第二行，則記錄了不同的銷(xiāo)售日期，例如4月1日、4月2日等。值得注意的是，此行中的日期采用了合并單元格的形式展現(xiàn)，即連續(xù)的三個(gè)單元格（如C2至E2）被合并為一個(gè)單元格來(lái)表示單個(gè)日期。

緊接著的第三行標(biāo)識(shí)了產(chǎn)品類別，橫跨所有日期（包括那些合并的單元格范圍），如C3至E3列示了“產(chǎn)品A”、“產(chǎn)品B”和“產(chǎn)品C”。這些產(chǎn)品下方的矩陣區(qū)域C4:CN17則包含了與之相對(duì)應(yīng)的具體銷(xiāo)售數(shù)據(jù)，直觀地展現(xiàn)了各銷(xiāo)售小組在不同日期針對(duì)每種產(chǎn)品的銷(xiāo)售業(yè)績(jī)。

需求分析

目前，PMC生產(chǎn)計(jì)劃面臨的需求是對(duì)上述三維報(bào)表實(shí)施多維度的匯總分析。首先，第一種分析維度要求按照日期匯總各個(gè)小組及產(chǎn)品的銷(xiāo)售量。在這種需求下，報(bào)表布局將調(diào)整為日期沿垂直軸排列，而小組信息與產(chǎn)品種類則沿水平軸展開(kāi)，形成一個(gè)新的三維報(bào)表結(jié)構(gòu)。關(guān)鍵在于將原報(bào)表中的日期維度進(jìn)行轉(zhuǎn)換并加以匯總。

其次，第二種分析維度旨在依據(jù)日期與產(chǎn)品組合進(jìn)行匯總，依舊保持日期沿垂直軸分布，而產(chǎn)品類別則橫跨水平軸，構(gòu)成一個(gè)更為緊湊的二維表格。這種整理方式有助于直觀對(duì)比不同產(chǎn)品在各日期的銷(xiāo)售概況。

期望達(dá)成的展示效果可參考下圖示意，通過(guò)這兩種維度的重新組織和匯總，能夠?yàn)镻MC生產(chǎn)計(jì)劃提供更清晰、細(xì)化的數(shù)據(jù)支持。

3維轉(zhuǎn)2維

為了全面滿足上述分析需求，原報(bào)表需先經(jīng)過(guò)整理，轉(zhuǎn)化成一個(gè)統(tǒng)一的一維表格格式。這是數(shù)據(jù)分析的一項(xiàng)基本策略，即利用一維表作為基礎(chǔ)，因其靈活性高，可以根據(jù)不同的分析需求輕松變換為多維或二維報(bào)表。一旦擁有這樣一個(gè)核心的一維表，無(wú)論是生成用戶所需的二維視圖，還是構(gòu)建更為復(fù)雜的多維度報(bào)告，都能夠迅速且靈活地實(shí)現(xiàn)。

首先把原表的三維報(bào)表轉(zhuǎn)換成二維報(bào)表。在CP2錄入標(biāo)題：“日期”，然后在CP3錄入以下函數(shù)：

=REPTARRAY(SEQUENCE(30,,'2024-04-01'),COUNTA(B4:B17),)

函數(shù)說(shuō)明如下：

SEQUENCE 函數(shù)生成一個(gè)序列，包含30行，始于2024年4月1日，每日遞增；

COUNTA 函數(shù)計(jì)算范圍B4至B17中非空單元格的數(shù)量，這里返回值為14；

自定義函數(shù) REPTARRAY 作用是重復(fù)序列。它將由 SEQUENCE 產(chǎn)生的日期序列垂直重復(fù)14次，從而生成一個(gè)數(shù)組，該數(shù)組在垂直方向上從2024年4月1日延續(xù)至4月30日，并且整個(gè)序列總共重復(fù)了14次。

這樣，您就能獲得一個(gè)日期列，其中4月1日至4月30日的日期連續(xù)排列，并且這一序列總共往下重復(fù)了14行。

在CQ2單元格中輸入標(biāo)題“組別”。緊接著，在CQ3單元格內(nèi)錄入以下公式：

=REPTARRAY(B4:B17,30,)

函數(shù)解釋：

此函數(shù)的作用是將范圍B4至B17內(nèi)的內(nèi)容（例如不同的組別名稱：“1組”、“2組”等）重復(fù)排列，總共重復(fù)30次。這樣的操作確保了每個(gè)組別能夠與之后構(gòu)建的日期序列相對(duì)應(yīng)，形成一一對(duì)應(yīng)的關(guān)系，便于數(shù)據(jù)分析或組織展示。

在CR2單元格中，直接輸入公式 =C3:E3 來(lái)引用《產(chǎn)品銷(xiāo)售明細(xì)表》中的產(chǎn)品名稱作為列標(biāo)題，例如“產(chǎn)品A”、“產(chǎn)品B”、“產(chǎn)品C”。

在CR3單元格，錄入公式：

=WRAPROWS(TOCOL(C4:CN17),3)

關(guān)于函數(shù)的解釋如下：

TOCOL 函數(shù)的作用是將C4至CN17范圍內(nèi)的數(shù)據(jù)（覆蓋至4月30日的銷(xiāo)售數(shù)據(jù)細(xì)節(jié)）轉(zhuǎn)換成一列，這樣便于后續(xù)處理。

WRAPROWS 函數(shù)，其功能是將前面TOCOL函數(shù)生成的單一列數(shù)據(jù)，重新排列為每3行數(shù)據(jù)組成新的一行，實(shí)現(xiàn)從縱向數(shù)據(jù)到橫向分組的轉(zhuǎn)換，以便于觀察和分析。

2維轉(zhuǎn)1維

上面的數(shù)據(jù)實(shí)現(xiàn)了三維報(bào)表轉(zhuǎn)成二維報(bào)表，接下來(lái)需要把二維報(bào)表轉(zhuǎn)換成一維報(bào)表。分別定義上面的公式結(jié)果

日期：=REPTARRAY(SEQUENCE(30,,'2024-04-01'),COUNTA(B4:B17),)對(duì)應(yīng)CP3#

組別：=REPTARRAY(B4:B17,30,)對(duì)應(yīng)CQ3#

產(chǎn)品：=C3:E3，對(duì)應(yīng)CR2#

銷(xiāo)量：=WRAPROWS(TOCOL(C4:CN17),3)對(duì)應(yīng)CR3#

在CV2:CY2分別錄入{'日期','組別','產(chǎn)品','銷(xiāo)量'}

日期公式：

=TOCOL(IF(CR3#>=0,CP3#))

組別公式：

=TOCOL(IF(CR3#>=0,CQ3#))

產(chǎn)品公式：

=TOCOL(IF(CR3#>=0,CR2#))

銷(xiāo)量公式：

=TOCOL(CR3#)

效果如下圖所示：

匯總分析

擁有一維數(shù)據(jù)后，最高效的匯總分析工具莫過(guò)于數(shù)據(jù)透視表。我們選定這一系列數(shù)據(jù)，執(zhí)行以下步驟進(jìn)行配置：將“日期”拖動(dòng)到行標(biāo)簽區(qū)域，“組別”和“產(chǎn)品”移至列標(biāo)簽區(qū)域，接著，將“銷(xiāo)量”字段添加到值區(qū)域以進(jìn)行匯總計(jì)算。為進(jìn)一步優(yōu)化視圖，再次將“銷(xiāo)量”拖放到篩選區(qū)域，并設(shè)置篩選條件為僅顯示大于0的記錄。最終呈現(xiàn)的透視表效果正如下圖所示，直觀清晰地展示了按日期、組別和產(chǎn)品分類的銷(xiāo)量匯總。

除了利用數(shù)據(jù)透視表進(jìn)行數(shù)據(jù)分析，采用函數(shù)進(jìn)行分析也是一種選擇，它的優(yōu)勢(shì)在于能夠?qū)崟r(shí)反映數(shù)據(jù)變化，無(wú)需手動(dòng)刷新。以下是采用函數(shù)進(jìn)行分析的示例：

錄入函數(shù)如下：

=PIVOTBY(CV3#,HSTACK(CW3#,CX3#),CY3#,SUM,,0,,0,,CY3#>0)

該函數(shù)的運(yùn)作機(jī)制與數(shù)據(jù)透視表相似，具體解析如下：

行標(biāo)簽：CV3# 包含日期序列，作為數(shù)據(jù)分組的行軸。

列標(biāo)簽：通過(guò) HSTACK(CW3#, CX3#) 將“小組”（CW3#）與“產(chǎn)品”（CX3#）信息水平合并，形成多級(jí)列標(biāo)簽。

值標(biāo)簽：CY3# 對(duì)應(yīng)于“銷(xiāo)量”列，即我們要匯總分析的關(guān)鍵數(shù)值。

聚合函數(shù)：SUM 用于計(jì)算每個(gè)分組的銷(xiāo)量總和。

參數(shù)說(shuō)明：兩個(gè)逗號(hào)分隔的0意指不顯示額外的匯總行或列。

篩選條件：CY3# > 0 確保僅匯總銷(xiāo)量大于零的記錄，排除無(wú)效或負(fù)值數(shù)據(jù)。

通過(guò)此函數(shù)，我們直接在工作表中實(shí)現(xiàn)了與數(shù)據(jù)透視表類似的功能，即時(shí)且精準(zhǔn)地完成了按日期、小組和產(chǎn)品的銷(xiāo)量匯總分析。

公式合并

上述結(jié)果原本借助了輔助列實(shí)現(xiàn)，若希望避免使用輔助列，可以通過(guò)整合公式來(lái)達(dá)到目的。整合后的公式如下所示：

=LET(

CR, WRAPROWS(TOCOL(C4:CN17), 3),

B, B4:B17,

PIVOTBY(

TOCOL(IF(CR >= 0, REPTARRAY(SEQUENCE(30, , DATE(2024, 4, 1)), COUNTA(B)))),

HSTACK(TOCOL(IF(CR >= 0, REPTARRAY(B, 30))), TOCOL(IF(CR >= 0, C3:E3))),

TOCOL(CR),

SUM, , 0, , 0, , TOCOL(CR) > 0

)

)

函數(shù)解釋如下：

LET 函數(shù)首先定義了幾個(gè)變量以簡(jiǎn)化公式并提高可讀性：

CR 代表銷(xiāo)量數(shù)據(jù)，通過(guò) WRAPROWS 和 TOCOL 轉(zhuǎn)換為一維數(shù)組。

B 直接引用了組別范圍 B4:B17。

PIVOTBY 函數(shù)執(zhí)行了主要的數(shù)據(jù)透視操作：

行標(biāo)簽：使用 IF 條件判斷確保銷(xiāo)量大于等于0時(shí)，才應(yīng)用 REPTARRAY 生成日期序列并與組別計(jì)數(shù)匹配，最后通過(guò) TOCOL 轉(zhuǎn)換為一維數(shù)組。

列標(biāo)簽：同樣利用 IF 進(jìn)行篩選，REPTARRAY 分別重復(fù)組別信息和產(chǎn)品信息，與銷(xiāo)量條件同步，通過(guò) HSTACK 合并為多列標(biāo)簽，再分別轉(zhuǎn)換為一維數(shù)組。

值：直接使用銷(xiāo)量數(shù)據(jù) CR 經(jīng)過(guò) TOCOL 處理。

聚合函數(shù)：SUM 用于求和銷(xiāo)量。

其他參數(shù)：兩個(gè)0 表示不顯示額外的匯總項(xiàng)；最后的篩選條件 TOCOL(CR) > 0 確保僅匯總有效銷(xiāo)量。

此公式通過(guò)直接嵌套邏輯，實(shí)現(xiàn)了不依賴輔助列的復(fù)雜數(shù)據(jù)透視功能，提升了公式的一體化程度和數(shù)據(jù)處理效率。

最后總結(jié)

總之，面對(duì)原始三維報(bào)表分析的挑戰(zhàn)，我們通過(guò)一系列精心設(shè)計(jì)的步驟和高級(jí)WPS函數(shù)的應(yīng)用，成功地將數(shù)據(jù)重塑為易于分析的一維結(jié)構(gòu)，并實(shí)現(xiàn)了高效匯總。這一過(guò)程不僅克服了數(shù)據(jù)布局的限制，還展示了如何靈活運(yùn)用現(xiàn)代電子表格工具的強(qiáng)大功能來(lái)進(jìn)行復(fù)雜數(shù)據(jù)分析。

整合后的公式利用LET函數(shù)組織邏輯，不僅提高了公式的可讀性和維護(hù)性，而且通過(guò)直接在公式內(nèi)部進(jìn)行條件判斷與數(shù)據(jù)重塑，有效避免了創(chuàng)建輔助列的需要，使得數(shù)據(jù)處理流程更加精簡(jiǎn)。PIVOTBY函數(shù)的巧妙運(yùn)用，相當(dāng)于在公式層面復(fù)現(xiàn)了數(shù)據(jù)透視表的核心能力，確保了數(shù)據(jù)分析的深度和廣度。

此解決方案不僅滿足了PMC生產(chǎn)計(jì)劃對(duì)多維度匯總分析的迫切需求，還為此類復(fù)雜數(shù)據(jù)分析場(chǎng)景提供了一個(gè)高度優(yōu)化的范式。它證明了在處理實(shí)際業(yè)務(wù)問(wèn)題時(shí)，深入理解數(shù)據(jù)結(jié)構(gòu)、熟練掌握高級(jí)函數(shù)應(yīng)用以及不斷創(chuàng)新解決問(wèn)題的方法論，是提升工作效率、實(shí)現(xiàn)數(shù)據(jù)驅(qū)動(dòng)決策的關(guān)鍵。通過(guò)這種方法，企業(yè)可以更快地從海量數(shù)據(jù)中提煉出有價(jià)值的信息，為生產(chǎn)計(jì)劃的精準(zhǔn)制定和市場(chǎng)策略的靈活調(diào)整奠定堅(jiān)實(shí)的數(shù)據(jù)基礎(chǔ)。