贊贊小屋

Excel填滿報表空白儲存格,順利建立樞紐分析表

Excel建立樞紐分析表時,原始報表不能有空白欄位或合併儲存格。另外資料內容有空白會造成彙總統計失真,本文介紹如何複製公式、快速填滿空白儲存格。

財務人員通常都是公司裡面Excel最強的那一個,這是被加班結帳磨出來的。有些業務看到財務發出來「精美」的銷售彙總表、毛利差異分析表,總是倒吸一口氣。有時候聽到業務問那個料號的毛利率多少,財務會有點不耐煩:不是給你樞紐分析表了嗎?自己拉一下就好了呀。於是小業務私底下偷偷問交情好的小財務:那個到底怎麼拉呀?有的就直接問了:怎麼跑樞紐?

和業務交流過程中,發現到他們往住遇到「為何產生不了?」的挫敗感,江湖一點訣,說穿了不值錢,本文根據真人真事改編:

一、自動選取範圍

於C1儲存格建立樞紐分析表,Excel會自動選取適當範例:「’一、自動選取範圍’!C1:J31」,雖然很聰明地上下左右延伸到空白為止,但原始報表本身的空白欄造成日期欄位被切掉,顯然範圍需要修正。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第1張

二、空白欄位錯誤

手動選取範圍:「’2.空白欄位錯誤’!A1:J31」,建立樞紐分析表按「確定」,會跳出「樞紐分析表欄位名稱無效。」,從一節文章可知樞紐分析表是依照每個欄位配置報表,由此可理解為何不能接受空白欄位。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第2張

三、合併儲存格錯誤

C1和D1兩個儲存格合併為「商品規格」,依照上個步驟所說的樞紐分析表機制同樣是無法處理,會跳出同樣的錯誤訊息。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第2張

四、相同資料空白

實務上同一張單據往往多筆資料,例如一張訂單多個商品。很多時候ERP系統跑出來的報表,會將相同的基本資料保留空白,所以會有截圖所示的日期和訂單號碼的情形,這個報表在欄位列沒有空白和合併儲存格情形,仍然可以建立樞紐分析表。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第4張

五、含空白樞紐分析表

仔細看所建立的樞紐分析表,列標籤「日期」欄位最下面有個「(空白)」,對照上個步驟的ERP報表,可知是同一單據的日期有空白所造成。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第5張

六、儲存格參照公式

想解決空白問題,先在第一個空白處,A3和B3儲存格輸入公式:「=A2」、「=B2」,亦即參照到上一列儲存格內容。公式設定好了之後,快速組合鍵「Ctrl+C」複製,A3到B3儲存格有綠色閃光框,表示已經是剪貼簿待貼上的內容。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第6張

七、到(定位)

選取A5到B31的儲存格範圍,亦即「日期」和「訂單號碼」欄位不含第六步驟公式的部份,然後快速組合鍵「Ctrl+G」,跳出「到(定位)」對話方塊,在此按下「特殊」。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第7張

八、特殊目標

「特殊目標」中選擇「空格」,按下「確定」。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第8張

九、定位空格

回到工作表,原本選取的範圍進一步限縮到空格部分,經過這樣的操作,應能理解「到」和「特殊目標」的功能。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第9張

十、空格貼上公式

快速組合鍵「Ctrl+V」,將第六步驟複製上一列資料的公式貼上。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第10張

十一、完整樞紐分析表

再次建立樞紐分析表,不再會有第五步驟錯誤的「(空白)」。

Excel填滿報表空白儲存格,順利建立樞紐分析表 樞紐分析表 第10張

這一節範例總結兩個心得:

首先,空白欄和合併儲存格不僅在建立樞紐分析表時會有困難,許多Excel資料處理的操作,例如篩選、排序、Vlookup函數公式等,對於原始表格的要求都是一樣的,因此最好一開始編製報表時即注意格式規範。

其次,填滿空白儲存格的操作看似巧妙,其實它的效果等同於一一選取空白儲存格將公式貼上,只不過利用定位特殊目標的小技巧一次操作,提升效率。讀者可以看到還有許多其他類型的特殊目標,於適當場合可用在其它地方。

本網站所有內容皆受版權保護,网站备案编号:苏ICP备14051307号-1