更新日期: 2023-06-02 By 貝多羅 2569

EXCEL的樞紐分析在財務分析的應用教學-進階技巧-樞紐範圍自動調整

COUNTA函數樞紐分析新手入門教學 OFFSET函數公開資訊觀測站 EXCEL投資分析(超詳細) 財務分析

前言

前一篇文章「對投資新手的簡單易懂教學-EXCEL的樞紐分析在財務分析的應用教學」介紹Excel樞紐分析的簡單使用方式。而本篇則是對「樞紐範圍自動調整」的使用技巧加以說明。背後的概念其實就是將樞紐的範圍定義為一個名稱，然後將這個範圍用OFFSET函數控制「變化的動作」，而「變化多少」則使用COUNTA函數計算出資料實際有幾列(非空白)來控制。

所以如果想讓樞紐範圍自動調整，應該讓資料裡面盡量不要有空白，這邊也就呼應上一篇的注意事項「資料最好是經標準化的表格，裡面盡量不要有錯誤(或空白)」，也就是這個道理，如果真的沒辦法，就應該選每一欄必有資料的欄位。

之所以會提到這個是因在筆者的工作生涯裡，可惜地很多人對Excel的功能所知有限，儘管可能只是按個按鍵就能重設樞紐的範圍，但還是容易找不到設定的地方或不會按，因為樞紐範圍設定不正確造成「樞紐分析抓不到資料」，可能經常要找筆者去幫忙設定。因此如此，很多時侯筆者就乾脆設定「樞紐範圍自動調整」，讓使用的人只要最簡單地按右鍵「重新整理」即可。

使用函數

這邊要介紹在「對投資新手的簡單易懂教學-Excel在投資分析資料的應用-HLOOKUP、OFFSET等函數」一文使用過的函數「OFFSET函數」。與本次會提到的「COUNTA函數」。

OFFSET函數

傳回根據所指定列數及欄數之儲存格或儲存格範圍之範圍的參照。傳回的參照可以是單一儲存格或一個儲存格範圍。您可以指定要傳回的列數和欄數。
語法:OFFSET(reference, rows, cols, [height], [width])
Reference=資料的起始位置。
Rows(必填)=這是要左上角儲存格往上或往下參照的列數。
Cols(必填)=這是要結果的左上角儲存格向左或向右參照的欄數。
[height](選擇性)=這是要傳回參照的列數高度。Height必須是正數。
[width](選擇性)=這是要傳回參照的欄數寬度。Width必須是正數。
資料來源:微軟官網(摘錄)

COUNTA 函數

COUNTA 函數會計算範圍中不是空白的儲存格數目。
語法:COUNTA(value1, [value2], ...)
COUNTA 函數語法具有下列引數：
value1 必要。這代表您所要計算值的第一個引數。
value2, ... 選擇性。這代表您所要計算值的其他引數，最多有 255 個引數。
資料來源:微軟官網