如果有在長期輸入 Excel表格資料,同時使用樞紐分析表,應該都會遇到一種困擾:
由於建立樞紐分析表時會僵硬的指定資料來源的表格範圍,所以後續新增的表格列就不會自動進入樞紐分析表,於是每次新增資料都必須到樞紐分析表中重新指定資料來源,同時還有個副作用,就是會自動重置樞紐分析表的格式,失去之前辛苦設定的格式…或者也可以一次將資料來源設定超出現有表格多列留待後續使用,但是在樞紐分析表中就必須手動篩選去除空白的資料…總之各有辛苦都不容易。

研讀了《學不完.教不停.用不盡》的文章,了解原來可以先透過定義名稱的功能用 OFFSET 函數聰明的自動偵測表格範圍,之後在樞紐分析表的資料來源只要填入這一個定義名稱即可,於是不用再受到增減列數困擾,資料來源從此會自動更新為完整的表格範圍!

在名稱管理員新增一個名稱,就簡單叫 DATA 吧!
填入OFFSET 公式,例如:=OFFSET(資料表!$A$1,0,0,COUNTA(資料表!$A:$A),24)
下面擷錄 OFFSET 函數解說,同時以藍色字體作簡單的評論:

OFFSET(reference, rows, cols, [height], [width])

OFFSET 函數語法具有下列引數:

  • Reference    參照點直接指向表格左上角即可,例如:資料表!$A$1
    必要。 這是用以計算位移的起始參照。 Reference 必須參照一個儲存格或相鄰的儲存格範圍,否則 OFFSET 會傳回 #VALUE! 的錯誤值。

  • Rows    由於表格左上角就在參照點上,不用再平移,填 0 即可
    必要。 這是要左上角儲存格往上或往下參照的列數。 使用 5 做為 rows 引數,指出參照的左上角儲存格是 reference 下方的第五列。 Rows 可以是正數 (表示在起始參照下方) 或負數 (表示在起始參照上方)。

  • Cols    由於表格左上角就在參照點上,不用再平移,填 0 即可
    必要。 這是要結果的左上角儲存格向左或向右參照的欄數。 使用 5 作為 cols 引數,指出參照位址的左上角儲存格是 reference 右方的第五欄。 Cols 可以是正數 (表示在起始參照右方) 或負數 (表示在起始參照左方)。

  • [高度]    設計函數自動計算表格列數。例如:COUNTA(資料表!$A:$A)
    選擇性。 這是要傳回參照的列數高度。 Height 必須是正數。

  • 寬度    直接填入表格欄數。例如:24
    選擇性。 這是要傳回參照的欄數寬度。 Width 必須是正數。

arrow
arrow
    全站熱搜

    深林梢月 發表在 痞客邦 留言(0) 人氣()