Excel 数据块之间的运算原理

作者: , 共 849 字

最近看到一个比较有趣的问题, 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/VBA。用得多了自然比较关注工作效率,所以我在 Excel 键盘流上略有心得,这里给大家分享一下。网上有很全的快捷键列表,但那不是我想写的,因为太多记不下来不实用。这里只列举一下我在实际工作中的的确确总是在用的,为自己方便,也共享给大伙们。
编程 » Excel, 数据库
在前面的文章里,我已经提到Excel 数据本身可以当做一张 SQL 查询的数据表,并在 Excel 内进行数据库运算操作。数据库查询函数可以用我之前写的Excel 数据库操作函数类。我们可以用以下方式
Excel 多表合并和查询是一个应用很广泛的问题。下面是一个简单的例子,我们需要从两张数据表里,得出每个行业的股票波动率平均值。第一个数据表保存了股票和行业的对应关系,有两列,第一列为股票名,第二列为每只股票对应的行业。第二张表保存了各个股票在各个交易日的收盘价和前收盘价,有四列,第一列是股票名,第二列为交易日,第三列和第四列分别为股票在这个交易日的前收盘价和收盘价。
编程 » Excel, VBA
某些时候需要打开 Excel 文件来获取或者写入数据,但又不希望跳出打开的 Excel 文件窗口,可以用下面的代码:
编程 » Excel, VBA
VBA 的 Date 类型比较奇怪。
如果一个日期(或者时间),如果用字符串,比如"2010-10-04"的形式,各个系统都没有什么区别。在某些时候,将日期用数字表示,将大大增加对日期查找、比较的效率。而如果用数字来表示日期,在不同的系统之间差别就大了。
相似度: 0.069
编程 » Excel, VBA
Excel VBA 出错时给出的错误信息极少,需要充分利用各种工具来进行调试。
编程 » Excel, VBA
效果图:
编程 » Excel
香港这边没有 WIND ,部门的彭博也不在我的机器上,为了取个股票价格数据都得跑来跑去。一怒之下,我写了一个 Excel 的函数,用来获取行情数据。
国外的很多做高频交易的对冲基金的一大准则便是不要持仓过夜。在国内投资股票也一样,除去无法把握隔夜风险的判断以外,还有一个原因:
编程 » Excel
此篇为学习笔记。