Excel:快速喚醒記憶的Excel I(定義名稱篇)

有沒有試過,當打開一星期前的Excel檔來看時,不知從那裡看起?
又或是,明明是自己加上的公式,看過半天也不知公式是在攪什麼飛機.
以下將會介紹一些方法,可以在Excel裡留些記號. 其他人也可以容易看懂

A 定義名稱A I. 個別定義名稱
公作上很多時候會收到客戶寄來的xls.

看了一眼,除WTF之外,根本想不出什麼來.
(而且,實際上收到的公式不比這個簡單)
其實只要用上定義名稱,公式就可以變得清晰
如下面公式.

做法:

1. 先替A1,B1,C1各自做定義:
   選上A1,然後到菜單{公式}-> "已定義之名稱"組->"定義名稱"
   填上"名稱",然後按[確定].
   (Excel 2003或以下:菜單{插入}->"名稱"-> "定義")
   
   A1完成! 選上A1的話,"名稱方塊"會顯示定義名稱
   
   B1,C1,按同一個方法做.
   還有一個比較單簡的做法. 就是直接在左手面上角"名稱方塊"輸入名稱.
   
2. 當3個地方都做好命名,就可以把名稱直接放在公式裡:
   

A II. 區域定義名稱

除了一格一格定義,也可以把一個區域定義,例如下圖C2:C5, 加上了名稱"數學I分數"

用這個方法,如果要重覆使用範圍,就可以直接用名稱.
統一口徑,減低輸錯位置機會. 也不每次慢慢去找位置,有名即通.

做法: 跟"A I. 個別定義名稱"時一樣,只要選上整個範圍,然後在"名稱方塊"輸入名字即可.

加好名字後, 就可以把名稱放到公式
例如C7上是C2到C5 (C2:C5)的總數(327)
可以通過C8或C9顯示的公式拿出 [即 =SUM(C2:C5) 或 =SUM(數學I分數) ]

有一個方法一次過可以顯示頁面上所有名稱位置.
把頁面縮少到約35%, 可以像下圖一樣顯示名稱和範圍

A III. 區域動態定義名稱
除固定位置,定義名稱還提供了用公式的自動延伸方法.
例如下圖C2:C5, 當加上C6時, 系統自動把其放到定義名稱範圍內(得出F2結果:415).

(註:
50+77+99+101 為327
50+77+99+101+88 為415)

做法: (此例子以數字為條件,其他條件請參考例子後列表)
1. 到菜單{公式}-> "已定義之名稱"組->"定義名稱"
2. 填上"名稱"(例如下圖數學I分數自動)
3. 輸入公式到"參照到": =OFFSET($C$1,0,0,MATCH(1E+306,$C:$C,1),1)
4. 按[確定].

把新自定名稱放到所需公式, 完成!

公式解說:

$C$1	數學I欄位標題,用作定位.
MATCH(1E+306,$C:$C,1)	找出C欄下最後1行數提位置,1E+306 是個任意的大數
OFFSET(...)	用 $C$1 和 MATCH找出的行數創做一個範圍.

  其他數個常用的定義方法:

含數字連續行數	=OFFSET($A$1,0,0,COUNT($A:$A),1)
含數字或文字連續行數	=OFFSET($A$1,0,0,COUNTA($A:$A),1)
非連續,用找出最後一個數字位置 (即上面例子)	=OFFSET($A$1,0,0,MATCH(1E+306,$A:$A,1),1)
非連續,用找出最後一個文字位置	=OFFSET($A$1,0,0,MATCH("*",$A:$A,-1),1)

參考位置: http://www.ozgrid.com/Excel/DynamicRanges.htm

本篇比預算的長, 更多的"快速喚醒記憶的Excel"方法 下篇繼續.
(本篇最後更新 2012-10-02)