今天跟大家一起分享某生遇到的關于庫存台賬結餘彙總統計的案例。

簡單介紹一下這位學員的問題和需求。

這位學員的工作主要是統計管理各種型号産品的庫存台賬。現在需要将大量型号産品庫存信息表中的結餘數量彙總到一個工作表中。

如下圖：

如GHE、AA、600分别是三種不同型号産品，E列中的最後一行為該産品的最新結餘情況。（注:各産品型号表中的格式一緻）

現在需要在彙總表中以産品型号為行，統計每個産品最後結餘情況。

如下圖所示：

我們要達成這樣的需求其實有2個問題點。

1、将各個産品結餘明細工作表中的數據引用到彙總表中。

2、如何才能返回産品型号表中的最後結餘情況。

下面我們就帶着這兩個問題一起來解決這位同學的需求。

既然用到引用那麼我們就必須要會用indirect函數，其主要作用為返回文本字符串所指定的引用。

舉例:

A列為工作表的名稱，通過将A2單元格中的文本字符與!B2合并構建一個引用。相信大家在平時工作中使用跨表匹配的時候一定很熟悉，目标單元格引用均由工作表名稱 感歎号 單元格名稱組成，比如：GHE!B2。

這裡我們就可以通過INDIRECT(A2&"!B2")函數公式直接返回GHE工作表中B2單元格的内容。

我們再看看GHE工作表中B2單元格的内容是不是GHE。

我們看到GHE工作表中B2單元格的内容的确是GHE。

第二個問題是如何返回最後結餘情況。

以GHE工作表為例我們最終目的是要返回表中的E7單元格内容，而且需要随着行數變化而變化。

看到這裡相信很多人都會想到用OFFSET函數來完成。

Offset函數功能為以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區域。并可以指定返回的行數或列數。Reference 作為偏移量參照系的引用區域。Reference 必須為對單元格或相連單元格區域的引用；否則，函數 OFFSET 返回錯誤值#VALUE!。

如下圖：

函數公式：=OFFSET(B2,5,3,1,1)

含義：以GHE工作表所在的B2作為參照單元格，向右偏移3列，向下偏移5行即可返回E7單元格最終的結餘數。函數公式中的5表示第五行，3表示第三列，最後兩個參數為1表示隻返回一個單元格内容。

下面我們我隻要将函數公式與第一步中的indirect函數公式完成嵌套：

=OFFSET(INDIRECT(A2&"!B2"),5,3,1,1)

靜态的數據返回做好了，那麼如何做到随着行數的變化而随時變化呢？

因為表中A列的日期與E列結餘是對應的，這裡我們巧妙的将行數用count函數來代替，通過count函數統計A列數值單元格數量來作為OFFSET的第二個參數。這樣我們就能做到随着行數變化随時統計對應的最終結餘數據。

函數公式為：COUNT(INDIRECT(A2&"!A:A")) 1,加1的原因是由于GHE工作表 A列中隻有4個單元格為數值，而在上個案例中我們需要向下偏移5行，所以我們需要這基礎上加1來補充到。

最終函數公式：

=OFFSET(INDIRECT(A2&"!B2"),COUNT(INDIRECT(A2&"!A:A")) 1,3,1,1)

我們再來簡單總結梳理一下：

本案例中主要的難點在如何引用指定列的最後一行數值，這裡我們使用了offset、indirect函數完成了指定數據的引用，同時通過count函數完成了對指定單元格動态更新查找。最終實現了更具産品名稱快速統計返回對應庫存台賬的最終結餘情況。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!