在一個(gè)含有合并單元格數(shù)據(jù)的表格進(jìn)行查詢分析時(shí)，我們遇到了挑戰(zhàn)。具體來說，表1記錄了一組項(xiàng)目統(tǒng)計(jì)數(shù)據(jù)，詳盡展示了各項(xiàng)目在不同年份的表現(xiàn)情況。現(xiàn)需構(gòu)建一張表2作為查詢報(bào)表，其功能應(yīng)為：當(dāng)用戶輸入特定年份后，報(bào)表能準(zhǔn)確呈現(xiàn)該年份內(nèi)所有項(xiàng)目的相關(guān)數(shù)據(jù)。

微軟Office LTSC 2021專業(yè)增強(qiáng)版 簡體中文批量許可版 2024年09月更新

• 類型：辦公軟件
• 大小：2.2GB
• 語言：簡體中文
• 時(shí)間：2024-09-12

查看詳情

本應(yīng)是一個(gè)簡易的查詢?nèi)蝿?wù)，卻因表1中存在合并單元格而變得復(fù)雜。源表為了保持格式整齊與視覺美感，對(duì)合并單元格的使用有所堅(jiān)持，既不允許取消現(xiàn)有合并，也不接受通過增設(shè)輔助列的方式來簡化查詢過程。這就對(duì)我們在不破壞源表結(jié)構(gòu)的前提下，實(shí)現(xiàn)高效、精準(zhǔn)的年度數(shù)據(jù)查詢提出了較高要求。

需求分析

要確保在表2中錄入查詢年份后，報(bào)表能準(zhǔn)確展示該年份內(nèi)所有項(xiàng)目的相關(guān)數(shù)據(jù)，首先需要取消合并單元格，并對(duì)由此產(chǎn)生的空白單元格進(jìn)行數(shù)據(jù)填充，將其整理成一個(gè)規(guī)范的一維數(shù)據(jù)表。關(guān)鍵挑戰(zhàn)在于如何在不借助輔助列的情況下構(gòu)建單一連續(xù)的數(shù)組。即將發(fā)布的WPS辦公軟件中的SCAN函數(shù)恰好能夠有效應(yīng)對(duì)這一難題。

利用SCAN函數(shù)，可以針對(duì)取消合并單元格后出現(xiàn)的空值進(jìn)行迭代處理。具體操作如下：對(duì)每個(gè)單元格（用變量Y表示）進(jìn)行判斷，若Y值為空，則保留前一次迭代的結(jié)果（即變量X的值）；反之，若Y非空，則將當(dāng)前單元格的Y值作為結(jié)果輸出。如此一來，SCAN函數(shù)便能自動(dòng)跳過空白單元格，連貫地串聯(lián)起非空數(shù)據(jù)，生成所需的一維數(shù)組，從而無需額外使用輔助列。

綜上所述，借助WPS即將推出的SCAN函數(shù)，通過對(duì)其迭代過程中遇到的空單元格進(jìn)行智能判斷與處理，能夠在無需輔助列的條件下，高效地將取消合并后的表格整理成標(biāo)準(zhǔn)的一維數(shù)據(jù)表，確保報(bào)表能夠準(zhǔn)確反映所查詢年份內(nèi)所有項(xiàng)目的相關(guān)數(shù)據(jù)。

單列取消

由于表格中存在多列合并的單元格，為了便于大家清晰理解函數(shù)公式的計(jì)算邏輯，我們將分步驟展示其構(gòu)成，最終再整合成完整的公式。首先，請?jiān)趩卧裰墟I入以下第一步的函數(shù)：

=SCAN("",D3:D12,LAMBDA(X,Y,IF(Y="",X,Y)))

此公式旨在處理取消合并后的單元格，通過SCAN函數(shù)逐步遍歷范圍D3:D12。當(dāng)遇到空單元格（即Y=""），函數(shù)保留前一次迭代的結(jié)果X；對(duì)于非空單元格，則返回當(dāng)前單元格的值Y。執(zhí)行完畢后，將得到已消除合并影響、填充了空白單元格的有序數(shù)據(jù)結(jié)果。

多列取消

若需對(duì)多列合并單元格進(jìn)行處理，一種簡便的做法是分別應(yīng)用上述針對(duì)單列取消合并單元格的公式邏輯。然而，隨著處理列數(shù)增多，相應(yīng)的公式將會(huì)顯著增長，顯得冗長且不易管理。為避免這種重復(fù)操作，可巧妙引入轉(zhuǎn)置函數(shù)，從而簡化整個(gè)過程。

錄入以下函數(shù)：

=TRANSPOSE(SCAN("",TRANSPOSE(B3:G12),LAMBDA(X,Y,IF(Y="",X,Y))))

借助轉(zhuǎn)置函數(shù)（如TRANSPOSE），通過兩次巧妙的應(yīng)用，我們可以高效地處理多個(gè)合并單元格，避免對(duì)多列合并單元格進(jìn)行繁瑣的重復(fù)取消操作。這種方法不僅顯著提升了處理效率，還確保了公式的簡潔性。

效果如下圖所示：

篩選查詢

上面的公式實(shí)現(xiàn)了取消合并單元格的效果，將源數(shù)據(jù)巧妙的轉(zhuǎn)換成一個(gè)標(biāo)準(zhǔn)的一維數(shù)據(jù)后，就可以配合篩選函數(shù)進(jìn)行篩選查詢了，其中查詢的條件就是篩選條件。

錄入公式：

=FILTER(TRANSPOSE(SCAN("",TRANSPOSE(B3:G12),LAMBDA(X,Y,IF(Y="",X,Y)))),E3:E12=J1)

函數(shù)釋義：

已篩選數(shù)據(jù)區(qū)域B3:G12（該區(qū)域?yàn)橐讶∠喜⒌膯卧瘢?，篩選條件設(shè)定為E3:E12列數(shù)值等于查詢條件J1（設(shè)定為“2020”）。經(jīng)過篩選，成功返回了兩行數(shù)據(jù)，分別對(duì)應(yīng)北京和寧波的項(xiàng)目A與B。至此，完成了對(duì)合并單元格數(shù)據(jù)的查詢報(bào)表設(shè)計(jì)。

以上就是excel利用SCAN函數(shù)合并單元格完整的案例，希望大家喜歡，請繼續(xù)關(guān)注腳本之家。

相關(guān)推薦：

Excel HLOOKUP函數(shù)怎么用? Excel中的高效查找技巧

最新評(píng)論