Excel Column函数和Columns函数用法的7个实例，含1:1/$1:$1、返回指定行及之后的内容和求奇偶列之和

在 Excel 中，Column函数用于返回引用单元格或单元格区域的列号；它既可省略参数又可指定参数，如果省略参数，则返回公式所在单元格的列号；如果要以数组形式返回引用单元区域的列号，则需要按 Ctrl + Shift + 回车。Columns函数用于返回数组或引用单元格区域的列数，它不能省略参数。

Columns函数常与 Index、Indirect、Char、SumProduct、Mod、If、Sum 和 Row 等函数组合使用，例如 Index + Column 组合实现返回引用单元格区域中由行号和列号指定的单元格引用，Indirect + Char + Column + Row 组合返回指定行及之后的内容，SumProduct + Mod + Column 组合实现求偶数列或奇数列之和。

一、Excel Column函数和Columns函数的语法

1、Column函数表达式：COLUMN([Reference])

中文表达式：COLUMN([引用单元格或单元格区域])

2、Columns函数表达式：COLUMNS(Array)

中文表达式：COLUMNS(数组或对单元格区域的引用)

3、说明：

A、Column函数用于返回引用单元格或单元格区域的列号。参数 Reference 可以省略，若省略，则返回公式所在列的列号。如果Reference 为引用单个单元格，则返回该单元格的列号。如果 Reference 为引用一个单元格区域，若为水平引用（如 A1:C1），按 Ctrl + Shift + 回车，则以数组形式返回 A1 到 C1 的列号；按回车，只返回 A1 的列号。Reference 不能引用多个区域。

B、Columns函数用于返回数组或引用单元格区域的列数，参数 Array 不能省略。

二、Excel Column函数的使用方法及实例

（一）返回公式所在单元格与指定单元格列号的实例

1、选中 B1 单元格，输入公式 =COLUMN()，按回车，返回 2；选中 A1，输入公式 =COLUMN(C1)，按回车，返回 3；操作过程步骤，如图1所示：

图1

2、说明：

公式 =COLUMN() 省略了参数，默认返回公式所在列号，公式在 B1，因此返回 B1 的列号 2；公式 =COLUMN(C1) 的参数为 C1，尽管公式在 A1 单元格，仍然返回 C1 的列号 3。

（二）以数组返回指定列号的实例

1、假如要以数组形式返回 A1 到 D1 的列号。双击 E2 单元格，把公式 =COLUMN(A1:D1) 复制到 E2，按 Ctrl + Shift + 回车，返回 1，选中 E2，按住 Alt 键，分别按 M 和 V，打开“公式求值”窗口，按回车求值，结果为 1；双击 E3，把公式 =INDEX(COLUMN(A1:D1),,2)复制到 E3，按 Ctrl + Shift + 回车，返回 2，选中 E3，同样方法打开“公式求值”窗口，按回车求值，返回 1 到 4 的数组；操作过程步骤，如图2所示：

图2

2、公式说明：

A、从演示中可知，尽管按 Ctrl + Shift + 回车，公式 =COLUMN(A1:D1) 仍然不能返回数组，因为需要把 =COLUMN(A1:D1) 放到引用的函数中才能返回数组，把 COLUMN(A1:D1) 放到Index函数返回了数组 {1;2;3;4}。

B、公式 =INDEX(COLUMN(A1:D1),,2) 用于返回数组或引用单元格区域中由行号和列号指定的值或值的引用，COLUMN(A1:D1) 返回{1;2;3;4} 为它的第一个参数（即数组），第二个参数为行号（省略了），第三个参数为列号（值为 2）；公式的意思：返回数组 {1;2;3;4} 中第二列的值，即返回 2。

（三）返回一行所有列号 Column(1:1) 或一列的列号 Column(A:A) 的实例

1、双击 A2 单元格，把公式 =INDEX(COLUMN( 复制到 A2，单击第一行行号 1，自动输入 1:1，接着输入“),3)”，按 Ctrl + Shift + 回车，返回 3；双击 B2，把 =INDEX(COLUMN( 复制到 B2，单击第一列列号 A，自动输入 A:A，接着再输入“),1)”，按 Ctrl + Shift + 回车，返回 1；操作过程步骤，如图3所示：

图3

2、公式说明：

A、公式 =INDEX(COLUMN(1:1),3) 中，COLUMN(1:1) 用于以数组形式返回第一行所有列号，也可以用 COLUMN(:) 表示，$ 表示绝对引用。

B、公式 =INDEX(COLUMN(A:A),1) 中，COLUMN(A:A) 用于以数组形式返回第一列的列号，A:A 表示引用第一列，同样可以用 COLUMN($A:$A) 表示。

三、Excel Columns函数的使用方法及实例

（一）返回数组的列数实例

1、双击 A1 单元格，把公式 =COLUMNS({3,8,4,7,9,2}) 复制到 A1，按回车，返回 6 列；再次双击 A1，把公式的第二、四个逗号（,）改为分号（;），按回车，返回 2 列；操作过程步骤，如图4所示：

图4

2、公式说明：

公式 =COLUMNS({3,8,4,7,9,2}) 用于返回数组的列数；数组只有一行共有 6 个元素，因此返回 6 列；而公式 =COLUMNS({3,8;4,7;9,2}) 的数组有三行，每行两个元素，因此返回两列；数组中，分号（;）是行与行的分隔符，逗号（,）是元素与元素的分隔符。

（二）返回引用单元区域的列号实例

双击 C3 单元格，把公式 =COLUMNS(A1:D1) 复制到 C3，按回车，返回 4；选中 C1；操作过程步骤，如图5所示：

图5

2、公式说明：

公式 =COLUMNS(A1:D1) 用于返回引用单元格区域 A1:D1 的列数，A1 到 D1 共四列，因此返回 4。

四、Excel Column函数的扩展应用实例

（一）Indirect + Char + Column + Row 组合返回指定行及之后的内容

1、假如要返回表格第 5 行及之后的内容。双击 A9 单元格，把公式 =INDIRECT(CHAR(COLUMN()+64)&ROW(A5)) 复制到 A9，按回车，返回“沙糖桔”，选中 A9，把鼠标移到 A9 右下角的单元格填充柄上，鼠标变为黑色加号（+）后，按住左键，往右拖，则返回第 5 行的内容；选中 D9，用同样方法往下拖，则返回第 6、7 行的内容；操作过程步骤，如图6所示：

图6

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

A、COLUMN() 用于返回公式所在列的列号，当公式在 A9 时，返回 1；则 CHAR(COLUMN()+64) 变为 CHAR(1+64)，64 是 ASCII 码表中 @ 的编码，而 65 是大写 A 的编码，Char函数用于返回 ASCII 码对应的字符，CHAR(65) 返回 A；之所以用 COLUMN()+64，是因为往右拖时，COLUMN() 会返回 B、C、D 等列号 2、3、4，64 + 2 是 B 的 ASCII 编码，64 + 3 是 C 的 ASCII 编码，其它的以此类推，这样就实现了返回往右拖时所经过单元格的列号，再用Char函数返回它们对应列的字母。

B、ROW(A5) 用于返回 A5 的行号 5；当往下拖时，A5 会变 A6，则 ROW(A6）返回 A6 的行号 6；其它的以此类推。

C、则公式变为 =INDIRECT("A"&5)，最后用Indirect函数返回对 A5 的引用，即返回 A5 的内容“沙糖桔”。

（二）SumProduct + Mod + Column 组合实现求奇数列或偶数列之和

1、求偶数列之和；双击 E8 单元格，把公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=COLUMN(A1))*B$2:E$6) 复制到 E8，按回车，返回求和结果 4717。求奇数列之和；双击 E9，把公式 =SUMPRODUCT((MOD(COLUMN($B:$E),2)=0)*B$2:E$6) 复制到 E9，按回车，返回 5457；操作过程步骤，如图7所示：

图7

2、求偶数列之和公式 =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) 以数组形式返回 B 至 E 的列号，即返回 {2;3;4;5}。

B、则 MOD(COLUMN($B:$E),2) 变为 MOD({2;3;4;5},2)，接着把数组中的每个元素与 2 取模；第一次从数组中取出 2，然后与 2 取模（即取余数），结果为 0；第二次从数组中取 3，然后与 2 取模，结果为 1；其它的以此类推，最后返回 {0;1;0;1}。取模用于找出全部偶数列。

C、COLUMN(A1) 用于返回 A1 的列号 1；则 MOD(COLUMN($B:$E),2)=COLUMN(A1) 变为 {0;1;0;1}={1}，进一步计算，从数组中取出每个元素与 1 比较，如果等于 1，则返回 True，否则返回 False，最后返回 {FALSE;TRUE;FALSE;TRUE}。

D、则公式变为 =SUMPRODUCT({FALSE;TRUE;FALSE;TRUE}*B$2:E$6)，进一步计算，第一次从数组中取第一个元素 FALSE，返回 B2:E6 中与之对应的 B2，由于为假，所以返回 0；第二次取出数组第二个元素 TRUE，返回 B2:E6 中与之对应的 C2，由于为真，因此返回 C2 中的值239；第三次从数组中取第三次元素 FALSE，返回 B2:E6 中与之对应的 D2，由于为假，所以返回 0；第四次取出数组中第四个元素 TRUE，返回 B2:E6 中与之对应的 E2，由于为真，因此返回 E2 中的值 457；其它的以此类推，最后返回{0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329}。

E、则公式进一步变为 =SUMPRODUCT({0,239,0,457;0,215,0,562;0,439,0,651;0,549,0,690;0,586,0,329})，最后对数组中的元素求和，结果为 4717。

提示：以上公式是以 $B:$E 中 B 为第一列，如果以 A 列为第一列，则是求奇数列之和。

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

求奇数列之和公式与求偶数列之和公式的区别在于：前者用取模为 0 找奇数列，后者用取模为 1 找偶数列，即前者用 MOD(COLUMN($B:$E),2)=0 找奇数列，后者用 MOD(COLUMN($B:$E),2)=COLUMN(A1) 找偶数列（注意：这里以 $B:$E 中的 B 列为第一列），其它的都一样。

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。