Excel生產月報表統計:INDIRECT、VLOOKUP、SUMIF函數組合應用

目錄

Excel參照其他工作表的儲存格資料時,除了滑鼠點選,可以利用INDIRECT函數設計公式間接引用,本文以生產月報表的統計為範例,搭配VLOOKUP及SUMIF具體介紹如何應用。

Excel強大之處在於將工作表分成一格一格的座標方格紙,每個儲存格可以方便快速地參照引用其他儲存格內容,進而將此參照設計在函數公式裡。這麼做衍生的好處之一,來源資料的內容有更新時,引用儲存格或函數公式也會跟著更新。

不過,實際操作常會遇到把來源資料所在的工作表刪除,此時會發現,引用的參照或者函數公式被切斷,變成顯示錯誤,這錯誤無法修復,即使後來補上相同名稱、相同內容結構的工作表,仍然沒辦法將參照修補上,必須再重新操作連結一次,徒增重工。在此要介紹如何運用INDIRECT函數,建立相對較為穩定的儲存可參照。

一、製造業生產月報表

每個月都有一份,目前是一到六月,希望將其彙總。

二、引用其他工作資料

最直接、最原始的方法為儲存格參照:「=一月!D2」。

三、「#REF!」錯誤訊息

此時如果將一月份工作表刪除,原來儲存格參照等於被切斷,顯示「=#REF!」,意思是參照失效。

四、公式連結被切斷了

直接參照連結被切斷了,只能重新再引用,例如即使把更新後的一月再補入,仍然是「=#REF!」。

五、設計INDIRECT函數

設使一開始並非直接引用,而是間接引用:「=INDIRECT(“‘一月'”&”!D2″)」,如此即使後來「一月」工作表刪除,同樣會「#REF!」參照失效,但只要此活頁簿中,再次補入「一月」工作表,又會重新建立和「一月」工作表「D2」儲存格的連結,因為透過INDIRECT函數公式,這個連結是絶對建立在工作表名稱和儲存格位址上的,讀者可自行嘗試看看。注意到如果是純粹文字,是遵守「=INDIRECT(+”‘工作表名稱'”&”!儲存格位址”)」這樣的規律,工作表名稱兩旁有英文單撇號「’」,儲存格位址前面有驚嘆號「!」。

六、VLOOKUP函數應用

INDIRECT函數不僅僅用簡單的直接引用,它等於是開了一道大門,將參照引用由滑鼠操作切換成函數公式,因此在很多場合會都能用上,例如VLOOKUP函數:「=VLOOKUP(B1,INDIRECT(A4&”!C:D”),2,0)」。

七、SUMIF函數組合公式

同樣地,將INDIERCT函數嵌入到SUMIF函數公式中:「=IFERROR(SUMIF(INDIRECT($A5&”!C:C”),B$1,INDIRECT($A5&”!D:D”)),0)」,從這裡可以見得,INDIRECT是相對較為進階、不容易設計閱讀的函數,但在某些特定場合,沒有此函數會是相當麻煩,反過來說,巧妙使用INDIRECT函數大大提升效率,尤其是自動批量引用參照的案例。

INDIRECT間接引用的好處

引用參照其他儲存格時,無論相同工作表或者跨工作表,滑鼠游標點選太過容易,以至於都會忘了其實在編輯列裡非常清楚,Excel是以特定文字方式表示參照連結,縱然滑鼠點選的好處是方便,然而滑鼠點選如同執行指令,不能建立重複大量循環的執行規則,INDIRECT函數剛好填補了這個空缺,能以函數公式間接引用的方式,依照工作表名稱和儲存格位址建立連結。實務工作中很多場合可以設計INDIRECT函數公式達到繁瑣操作的自動化,除了這一節所介紹範例,往後再以適當範例和各位分享。

相關文章