Excel Column函數和Columns函數的使用方法,含Column($1:$5)實例

在 Excel 中，Column函數用於返回單元格的列號，一次既可以返回一列的列號，也可以返回多列的列號；而Columns函數作用是返回數組或引用單元格的列數。Column函數通常與Indirect函數、If函數、Index函數、VlookUp函數、SumProduct函數、Mod函數組合使用。以下是 Excel Column函數和Columns函數的使用方法，共列舉有6個實例，包含Column(1:1)、Column(1:$5)、Column($1:$5)、Column函數與Index函數組合、Column函數與Indirect函數組合、Column + Mod + SumProduct函數組合的實例，實例操作所用版本均為 Excel 2016。

一、Column函數和Columns函數語法

Column函數表達式：COLUMN([Reference])

Columns函數表達式：COLUMNs(Array)

說明：

1、Column函數用於返回引用單元格的列號。參數 Reference 為對單元格或單元格區域的引用，可以省略；如果省略，默認返回公式所在列的列號；如果參數 Reference 為對一個單元格區域的水平引用（如 A1:D1），將以數組形式返回所有引用單元格的列號；參數 Reference 不能一次引用多個區域。

2、如果要把公式作為數組形式輸入，需要從公式所在單元格開始框選，選中要包含的單元，按 F2，再次按「Ctrl + Shift + 回車」即可。

3、Columns函數用於返回引用的列數。Array 為數組或對單元格區域的引用，不能省略。

二、Column函數的使用方法及實例

(一）省略參數的實例

1、單擊 A1 單元格選中它，輸入公式 =column()，按回車，返回 A1 的列號 1；再次選中 A1，把滑鼠移到 A1 右下角的單元格填充柄上，按住滑鼠左鍵，往右拖，則返回 A1 到 E1 的列號；操作過程步驟，如圖1所示：

圖1

2、說明省略參數 Reference 時，返回公式 =column() 所在單元格的列號。

(二）把公式作為數組輸入的實例

1、把公式 =column() 複製到 A1 單元格，按回車，返回 A1 的列號 1；從 A1 開始框選，一直選到 E5，按 F2，顯示 A1 中的公式，再按「Ctrl + Shift + 回車」，則 A1 到 E1 五個單元格都以數組形式輸入公式，即 {=COLUMN()}，並返回每個公式所在單元格的列號；雙擊 C1 單元格，按回車，彈出「無法更改部分數組」提示窗口，說明以數組形式輸入公式 =COLUMN() 不能像在單元格中輸入公式一樣任意修改；操作過程步驟，如圖2所示：

圖2

2、以數組形式輸入的 =COLUMN() 公式也不一能一個個刪除，當選中一個刪除時，也會彈出「無法更改部分數組」提示窗口，只有一次選中所有數組公式包含的單元格才能刪除，操作過程步驟，如圖3所示：

(三）Column(1:1)、Column($1:$5) 與 Column(B:B)的實例

1、Column(1:1)返回一行所有列號

（1）選中 A1 單元格，輸入公式 =column(，單擊一下第一行行號 1，則自動輸入 1:1，同時選中第一行，接著輸入右括號)，按回車，返回 1；操作過程步驟，如圖4所示：

圖4

（2）公式說明：

A、=Column(1:1) 是以數組的形式返回第一行的所有列號，當在一個單元格中輸入 =Column(1:1) 時，只返回第一行首個單元格的列號，即返回 1；如果要看到 =Column(1:1) 返回的第一行的所有列號，需要把 =Column(1:1) 放到Index函數中，操作過程步驟，如圖5所示：

圖5

B、操作過程步驟說明：雙擊 A1 單元格，把公式改為 =INDEX(COLUMN(1:1),1)，按回車，返回 1；選中 A1，按住 Alt 鍵，按一次 M，按一次 V，打開「公式求值」窗口，單擊「求值」，以數組形式返回第一行的所有列號。

提示：=Column($1:$5)、 =Column($1:5) 與 =Column(1:$5) 都是返回 1 到 5 行的所有列號，與 =Column(1:1) 返回的結果一樣。

2、Column(B:B)返一列的列號

（1）選中 B1 單元格，輸入公式 =column(，單擊一下第二列字母 B，則自動輸入 B:B，同時選中第二列，接著輸入右括號)，按回車，返回 2；操作過程步驟，如圖6所示：

（2）=Column(B:B) 中的 B:B 表示對第 2 列的引用，它以數組形式返回第 2 列的列號，即 {2}，這個結果同樣需要把 =Column(B:B) 放到Index函數中才能看到；把 B1 中的公式改為 =INDEX(COLUMN(B:B),1)，選中 B1，按住 Alt 鍵，按一次 M，按一次 V，打開「公式求值」窗口，如圖7所示：

圖7

單擊「求值」，則 COLUMN(B:B) 返回數組 {2}，如圖8所示：

圖8

（四）Column + Indirect + Char函數返回指定行的內容實例

1、假如要返回表格 B 列第三行的內容。把公式 =INDIRECT(CHAR(COLUMN()+64)&3) 複製到 B7 單元格，按回車，返回「粉紅襯衫」，正是 B3 單元格中的內容，操作過程步驟，如圖9所示：

圖9

2、公式說明：

A、公式 =INDIRECT(CHAR(COLUMN()+64)&3) 中的 64 是字符 @ 在 ASCII 碼錶中編碼，字母 A 在 ASCII 碼錶中的編碼為 65，如果公式在 A 列，則應該用 64 + 1，則獲得 A 在 ASCII 碼錶的編號，公式在 B 列，則用 64 + 2，其它以此類推。

B、COLUMN() 返回它所在單元格的列號 2，COLUMN()+64 變為 2 + 64 = 66，66 是 B 在 ASCII 碼錶的編碼，CHAR(66) 是把 ASCII 編碼轉為字符，即返回 B，公式變為 =INDIRECT(B&3)，再用Indirect函數返回 B3 單元格的內容。

（五）Column + Mod + SumProduct函數組合求奇數列或偶數列的和

1、求偶數列的和

A、把公式 =SUMPRODUCT((MOD(COLUMN($D:$G),2)=COLUMN(A1))*D$2:G$9) 複製到 D10 單元格，按回車，返回 D2:D9 列偶數列的和 12133，操作過程步驟，如圖10所示：

圖10

2、公式說明：

A、COLUMN($D:$G) 用於以數組形式返回 D 到 G 列的列號，即 {4,5,6,7}；則 MOD(COLUMN($D:$G),2) 變為 MOD({4,5,6,7},2)，再用數組中的每個元素與 2 取模，最後返回數組 {0,1,0,1}。

B、COLUMN(A1) 以數組形式返回 A1 的列號 {1}；則 MOD(COLUMN($D:$G),2)=COLUMN(A1) 變為 {0,1,0,1}={1}；如果左邊數組中的元素等於右邊數組中的 1，返回 True，否則返回 False，最後返回數組 {False;True;False;True}。

C、則公式變為 =SUMPRODUCT({False;True;False;True}*D$2:G$9)，接著用數組中的每個元素與 D2 至 G9 中每行的數值相乘，由於 False 被轉為 0，True 轉為 1，因此公式變為 =SUMPRODUCT({0,489,0,593;0,466,0,498;0,587,0,450;0,989,0,935;0,852,0,951;0,758,0,872;0,1253,0,1209;0,608,0,623})，求和結果正是 12133，即 D2:G9 中的偶數列之和。

2、求奇數列的和

A、只需把求偶數列和公式改為 =SUMPRODUCT((MOD(COLUMN($D:$G)+1,2)=COLUMN(A1))*D$2:G$9)，即在 COLUMN($D:$G) 後加 1，如圖11所示：

圖11

B、按回車，返回 D2:G9 偶數的和 11980。

三、Columns函數的使用方法及實例

1、選中 B7 單元格，把公式 =COLUMNS({10,20,30;40,50,60}) 複製到 B7，按回車，返回 3；雙擊 B7 單元格，把公式改為 =COLUMNS(A:D)，按回車，返回 4；操作過程步驟，如圖12所示：

2、公式 =COLUMNS({10,20,30;40,50,60}) 中的數組三列兩行，因此列數為 3；公式 =COLUMNS(A:D) 是返回第 1 到第 4 列的列數，共有 4 列。