Excel Column函數(shù)和Columns函數(shù)用法的7個實(shí)例，含1:1/$1:$1、返回指定行及之后的內(nèi)容和求奇偶列之和

在 Excel 中，Column函數(shù)用于返回引用單元格或單元格區(qū)域的列號；它既可省略參數(shù)又可指定參數(shù)，如果省略參數(shù)，則返回公式所在單元格的列號；如果要以數(shù)組形式返回引用單元區(qū)域的列號，則需要按 Ctrl + Shift + 回車。Columns函數(shù)用于返回數(shù)組或引用單元格區(qū)域的列數(shù)，它不能省略參數(shù)。

Columns函數(shù)常與 Index、Indirect、Char、SumProduct、Mod、If、Sum 和 Row 等函數(shù)組合使用，例如 Index + Column 組合實(shí)現(xiàn)返回引用單元格區(qū)域中由行號和列號指定的單元格引用，Indirect + Char + Column + Row 組合返回指定行及之后的內(nèi)容，SumProduct + Mod + Column 組合實(shí)現(xiàn)求偶數(shù)列或奇數(shù)列之和。

一、Excel Column函數(shù)和Columns函數(shù)的語法

1、Column函數(shù)表達(dá)式：COLUMN([Reference])

中文表達(dá)式：COLUMN([引用單元格或單元格區(qū)域])

2、Columns函數(shù)表達(dá)式：COLUMNS(Array)

中文表達(dá)式：COLUMNS(數(shù)組或?qū)卧駞^(qū)域的引用)

3、說明：

A、Column函數(shù)用于返回引用單元格或單元格區(qū)域的列號。參數(shù) Reference 可以省略，若省略，則返回公式所在列的列號。如果Reference 為引用單個單元格，則返回該單元格的列號。如果 Reference 為引用一個單元格區(qū)域，若為水平引用（如 A1:C1），按 Ctrl + Shift + 回車，則以數(shù)組形式返回 A1 到 C1 的列號；按回車，只返回 A1 的列號。Reference 不能引用多個區(qū)域。

B、Columns函數(shù)用于返回數(shù)組或引用單元格區(qū)域的列數(shù)，參數(shù) Array 不能省略。

二、Excel Column函數(shù)的使用方法及實(shí)例

（一）返回公式所在單元格與指定單元格列號的實(shí)例

1、選中 B1 單元格，輸入公式 =COLUMN()，按回車，返回 2；選中 A1，輸入公式 =COLUMN(C1)，按回車，返回 3；操作過程步驟，如圖1所示：

圖1

2、說明：

公式 =COLUMN() 省略了參數(shù)，默認(rèn)返回公式所在列號，公式在 B1，因此返回 B1 的列號 2；公式 =COLUMN(C1) 的參數(shù)為 C1，盡管公式在 A1 單元格，仍然返回 C1 的列號 3。

（二）以數(shù)組返回指定列號的實(shí)例

1、假如要以數(shù)組形式返回 A1 到 D1 的列號。雙擊 E2 單元格，把公式 =COLUMN(A1:D1) 復(fù)制到 E2，按 Ctrl + Shift + 回車，返回 1，選中 E2，按住 Alt 鍵，分別按 M 和 V，打開“公式求值”窗口，按回車求值，結(jié)果為 1；雙擊 E3，把公式 =INDEX(COLUMN(A1:D1),,2)復(fù)制到 E3，按 Ctrl + Shift + 回車，返回 2，選中 E3，同樣方法打開“公式求值”窗口，按回車求值，返回 1 到 4 的數(shù)組；操作過程步驟，如圖2所示：

圖2

2、公式說明：

A、從演示中可知，盡管按 Ctrl + Shift + 回車，公式 =COLUMN(A1:D1) 仍然不能返回數(shù)組，因?yàn)樾枰?=COLUMN(A1:D1) 放到引用的函數(shù)中才能返回數(shù)組，把 COLUMN(A1:D1) 放到Index函數(shù)返回了數(shù)組 {1;2;3;4}。

B、公式 =INDEX(COLUMN(A1:D1),,2) 用于返回數(shù)組或引用單元格區(qū)域中由行號和列號指定的值或值的引用，COLUMN(A1:D1) 返回{1;2;3;4} 為它的第一個參數(shù)（即數(shù)組），第二個參數(shù)為行號（省略了），第三個參數(shù)為列號（值為 2）；公式的意思：返回數(shù)組 {1;2;3;4} 中第二列的值，即返回 2。

（三）返回一行所有列號 Column(1:1) 或一列的列號 Column(A:A) 的實(shí)例

1、雙擊 A2 單元格，把公式 =INDEX(COLUMN( 復(fù)制到 A2，單擊第一行行號 1，自動輸入 1:1，接著輸入“),3)”，按 Ctrl + Shift + 回車，返回 3；雙擊 B2，把 =INDEX(COLUMN( 復(fù)制到 B2，單擊第一列列號 A，自動輸入 A:A，接著再輸入“),1)”，按 Ctrl + Shift + 回車，返回 1；操作過程步驟，如圖3所示：

圖3

2、公式說明：

A、公式 =INDEX(COLUMN(1:1),3) 中，COLUMN(1:1) 用于以數(shù)組形式返回第一行所有列號，也可以用 COLUMN($1:$1) 表示，$ 表示絕對引用。

B、公式 =INDEX(COLUMN(A:A),1) 中，COLUMN(A:A) 用于以數(shù)組形式返回第一列的列號，A:A 表示引用第一列，同樣可以用 COLUMN($A:$A) 表示。

三、Excel Columns函數(shù)的使用方法及實(shí)例

（一）返回數(shù)組的列數(shù)實(shí)例

1、雙擊 A1 單元格，把公式 =COLUMNS({3,8,4,7,9,2}) 復(fù)制到 A1，按回車，返回 6 列；再次雙擊 A1，把公式的第二、四個逗號（,）改為分號（;），按回車，返回 2 列；操作過程步驟，如圖4所示：

圖4

2、公式說明：

公式 =COLUMNS({3,8,4,7,9,2}) 用于返回數(shù)組的列數(shù)；數(shù)組只有一行共有 6 個元素，因此返回 6 列；而公式 =COLUMNS({3,8;4,7;9,2}) 的數(shù)組有三行，每行兩個元素，因此返回兩列；數(shù)組中，分號（;）是行與行的分隔符，逗號（,）是元素與元素的分隔符。

（二）返回引用單元區(qū)域的列號實(shí)例

雙擊 C3 單元格，把公式 =COLUMNS(A1:D1) 復(fù)制到 C3，按回車，返回 4；選中 C1；操作過程步驟，如圖5所示：

圖5

2、公式說明：

公式 =COLUMNS(A1:D1) 用于返回引用單元格區(qū)域 A1:D1 的列數(shù)，A1 到 D1 共四列，因此返回 4。

四、Excel Column函數(shù)的擴(kuò)展應(yīng)用實(shí)例

（一）Indirect + Char + Column + Row 組合返回指定行及之后的內(nèi)容

1、假如要返回表格第 5 行及之后的內(nèi)容。雙擊 A9 單元格，把公式 =INDIRECT(CHAR(COLUMN()+64)&ROW(A5)) 復(fù)制到 A9，按回車，返回“沙糖桔”，選中 A9，把鼠標(biāo)移到 A9 右下角的單元格填充柄上，鼠標(biāo)變?yōu)楹谏犹枺?）后，按住左鍵，往右拖，則返回第 5 行的內(nèi)容；選中 D9，用同樣方法往下拖，則返回第 6、7 行的內(nèi)容；操作過程步驟，如圖6所示：

圖6

2、公式 =INDIRECT(CHAR(COLUMN()+64)&ROW(A5)) 說明：

A、COLUMN() 用于返回公式所在列的列號，當(dāng)公式在 A9 時，返回 1；則 CHAR(COLUMN()+64) 變?yōu)?CHAR(1+64)，64 是 ASCII 碼表中 @ 的編碼，而 65 是大寫 A 的編碼，Char函數(shù)用于返回 ASCII 碼對應(yīng)的字符，CHAR(65) 返回 A；之所以用 COLUMN()+64，是因?yàn)橥彝蠒r，COLUMN() 會返回 B、C、D 等列號 2、3、4，64 + 2 是 B 的 ASCII 編碼，64 + 3 是 C 的 ASCII 編碼，其它的以此類推，這樣就實(shí)現(xiàn)了返回往右拖時所經(jīng)過單元格的列號，再用Char函數(shù)返回它們對應(yīng)列的字母。

B、ROW(A5) 用于返回 A5 的行號 5；當(dāng)往下拖時，A5 會變 A6，則 ROW(A6）返回 A6 的行號 6；其它的以此類推。

C、則公式變?yōu)?=INDIRECT("A"&5)，最后用Indirect函數(shù)返回對 A5 的引用，即返回 A5 的內(nèi)容“沙糖桔”。

（二）SumProduct + Mod + Column 組合實(shí)現(xiàn)求奇數(shù)列或偶數(shù)列之和

1、求偶數(shù)列之和；雙擊 E8 單元格，把公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=COLUMN(A1))*B$2:E$6) 復(fù)制到 E8，按回車，返回求和結(jié)果 4717。求奇數(shù)列之和；雙擊 E9，把公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=0)*B$2:E$6) 復(fù)制到 E9，按回車，返回 5457；操作過程步驟，如圖7所示：

圖7

2、求偶數(shù)列之和公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=COLUMN(A1))*B$2:E$6) 說明：

A、$B 表示對 B 列的絕對引用，以確保往右往時，B 列不會變 C 列、D 列等；$B:$E 表示始終在 B 至 E 列求和；COLUMN($B:$E) 以數(shù)組形式返回 B 至 E 的列號，即返回 {2;3;4;5}。

B、則 MOD(COLUMN($B:$E),2) 變?yōu)?MOD({2;3;4;5},2)，接著把數(shù)組中的每個元素與 2 取模；第一次從數(shù)組中取出 2，然后與 2 取模（即取余數(shù)），結(jié)果為 0；第二次從數(shù)組中取 3，然后與 2 取模，結(jié)果為 1；其它的以此類推，最后返回 {0;1;0;1}。取模用于找出全部偶數(shù)列。

C、COLUMN(A1) 用于返回 A1 的列號 1；則 MOD(COLUMN($B:$E),2)=COLUMN(A1) 變?yōu)?{0;1;0;1}={1}，進(jìn)一步計算，從數(shù)組中取出每個元素與 1 比較，如果等于 1，則返回 True，否則返回 False，最后返回 {FALSE;TRUE;FALSE;TRUE}。

D、則公式變?yōu)?=SUMPRODUCT({FALSE;TRUE;FALSE;TRUE}*B$2:E$6)，進(jìn)一步計算，第一次從數(shù)組中取第一個元素 FALSE，返回 B2:E6 中與之對應(yīng)的 B2，由于為假，所以返回 0；第二次取出數(shù)組第二個元素 TRUE，返回 B2:E6 中與之對應(yīng)的 C2，由于為真，因此返回 C2 中的值239；第三次從數(shù)組中取第三次元素 FALSE，返回 B2:E6 中與之對應(yīng)的 D2，由于為假，所以返回 0；第四次取出數(shù)組中第四個元素 TRUE，返回 B2:E6 中與之對應(yīng)的 E2，由于為真，因此返回 E2 中的值 457；其它的以此類推，最后返回{0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329}。

E、則公式進(jìn)一步變?yōu)?=SUMPRODUCT({0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329})，最后對數(shù)組中的元素求和，結(jié)果為 4717。

提示：以上公式是以 $B:$E 中 B 為第一列，如果以 A 列為第一列，則是求奇數(shù)列之和。

3、求奇數(shù)列之和公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=0)*B$2:E$6) 說明：

求奇數(shù)列之和公式與求偶數(shù)列之和公式的區(qū)別在于：前者用取模為 0 找奇數(shù)列，后者用取模為 1 找偶數(shù)列，即前者用 MOD(COLUMN($B:$E),2)=0 找奇數(shù)列，后者用 MOD(COLUMN($B:$E),2)=COLUMN(A1) 找偶數(shù)列（注意：這里以 $B:$E 中的 B 列為第一列），其它的都一樣。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。