Excel数据块之间的运算原理

作者:

最近看到一个比较有趣的问题, Excel 中以下表达式代表什么含义:

= B1:D1 * A2:A4

与想象中向量乘积不一样,此处答案是一个 3×3 的矩阵。在输入框选中 B1:D1 * A2:A4然后按 F9 便能看到结果。这不光适用于表格区域,也适用于运算结果和直接输入的数据块,比如 =(B1:D1>1)*{1;2;3}。原理何在?

事实上, Excel 在进行区域之间的计算时,均为按位置运算,包括比较、符号运算,包括乘法。当 A 为 M×N 的区域(此处区域可以是一个 Excel 表格区域,也可以是计算得到的结果矩阵), B 为 X×Y 的区域,那么在计算 A×B 时, A 和 B 都先扩充为 max(M, X)×max(N, Y)的矩阵,然后按位置相乘,得到的结果为一个 max(M, X)×max(N, Y)的矩阵。

但需要注意的是扩充的规则。只有两种方式, N/A 填充(这里 N/A 为一个 Excel 的错误类型)和重复填充。如果只有一行或者一列,则为重复填充;否则为 N/A 填充,即多余的格子都为 N/A。

当然,这里 N/A 事实上不是填充出来,而是在计算过程中产生的。但最终结果一样,并且填充的方式更容易被理解,所以这里不做区分,还是按照填充的说法。

一个简单的例子为,比如{1, 2, 3} 扩充为 4×5 的矩阵后为以下结果:

1 2 3 N/A N/A
1 2 3 N/A N/A
1 2 3 N/A N/A
1 2 3 N/A N/A

而{1 1 1; 1 2 3}扩充为 4×5 的矩阵后为以下结果:

 1   1   1  N/A N/A
 1   2   3  N/A N/A
N/A N/A N/A N/A N/A
N/A N/A N/A N/A N/A

利用这种规则,可以一个公式写出九九乘法表:选中一个 9×9 的区域,然后输入数组公式:

{={1,2,3,4,5,6,7,8,9}*{1;2;3;4;5;6;7;8;9}}

Q. E. D.

类似文章:
编程 » Excel
在编辑 Excel 文件时经常遇到的一个问题是,我这边用得好好地,换台机器就变了个样,或者根本用不了。下面是我在日常工作中总结的一些避免这些情况的小技巧。
编程 » Excel
Excel 中可设置每个单元格显示数字和文本的格式:
编程 » Excel
此篇为学习笔记。
编程 » Excel
香港这边没有 WIND ,部门的彭博也不在我的机器上,为了取个股票价格数据都得跑来跑去。一怒之下,我写了一个 Excel 的函数,用来获取行情数据。
编程 » VBA, Excel
如果不提供错误处理方法, VBA在出错时会停留在出错之处。Excel/VBA 提供 On Error关键词来处理程序运行过程中的错误,具体有下面两种用法:
国外的很多做高频交易的对冲基金的一大准则便是不要持仓过夜。在国内投资股票也一样,除去无法把握隔夜风险的判断以外,还有一个原因:
编程 » Excel
此篇为学习笔记。