专栏首页完美Excel精通Excel数组公式004:数学数组运算

精通Excel数组公式004:数学数组运算

excelperfect

先来看一个示例。下图1所示的工作表中,在单元格区域B2:B5中是一组成本数据,在单元格B8中是净成本率,想要计算每项净成本,然后将它们相加。一种方法是,在辅助列中逐项计算净成本,然后使用SUM函数将这些净成本数值相加,正如下图1所示。

图1

可以创建一个单个单元格数组公式计算出结果吗?实际上,上图1中的计算过程已经暗示了如何构建数组公式。注意到,单元格B2至B5都与单元格B8相乘,似乎可以将整个列与B8相乘,于是可以创建数组运算:

B2:B5*B8

下图2展示了一个使用数组运算B2:B5*B8的数组公式(数组乘以单项)。由于该数组运算位于SUMPRODUCT函数作为第1个参数,因此这个数组公式不需要按Ctrl+Shift+回车键。

图2

在上图2的下方,展示了数组运算的过程。第1个数组中的每个数字乘以0.85。

下图3展示了如何使用一个数组公式来计算出结果,而不需要中间的计算净成本的公式。

图3

从上例中,我们可以看到,可以从一个数组中减去单个数字。其实,也可以使用单个数字减去一个数组。再看一个示例。

下图4所示的工作表,给出一系列折扣,计算某产品的净成本。在单元格区域A2:A5中是系列折扣。先计算每次折扣后的值,即使用1减去折扣,再将这些值相乘得到净等效成本,,最后将其与零售价相乘得到结果。

图4

有了上文中的基础后,现在创建一个数组公式来获得相同的结果应该很简单。上述一步一步得出最后结果的计算步骤给创建数组公式打下了基础,一个有用的创建单个单元格数组公式的技巧是,从上面的结果开始向前追溯,从而得到最终的公式:

1. 注意到单元格B10中的公式:=ROUND(B9*B7,2),引用了包含公式=PRODUCT(B2:B5)的单元格B7,将其代入=ROUND(B9*B7,2)中,得到:

=ROUND(B9*(PRODUCT(B2:B5),2)

2. 而在单元格区域B2:B5中,包含了所有的折扣后的值的运算,都是使用1减去一组折扣值,可以创建数组操作:1-A2:A5。将其代入上述公式,得到:

=ROUND(B9*(PRODUCT(1-A2:A5),2)

3. 将上述公式输入到单元格后,按Ctrl+Shift+回车键。结果如下图5所示。

图5

综上,我们可以看到,构造数组操作有三种可能:

1. 数组与数组的操作

2. 数组与单项的操作

3. 单项与数组的操作

本文分享自微信公众号 - 完美Excel(excelperfect),作者:fanjy

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-06-12

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Excel小技巧48: 随意选择任意单元格

    在工作表中操作时,很多情况下我们需要选择单元格或单元格区域,甚至选择不相邻的单元格或单元格区域。此时,我们通常的方法是按住Ctrl键,再逐一选取单元格或单元格区...

    fanjy
  • Excel小技巧23:便捷的Ctrl键

    使用Ctrl键来选择不相邻的单元格或单元格区域,恐怕是很多使用Excel的人最喜爱的功能了。例如,你可以按照下面的操作来选择单元格:

    fanjy
  • Excel小技巧33:工作表数据输入技巧

    很多人喜欢在单元格中输入完后,按下回车键,Excel会自动移到下一个单元格。我们可以设置这个移至的下一个单元格是下方的单元格还是右侧的单元格。

    fanjy
  • Range单元格对象常用属性(二)

    Range单元格对象的常用属性,本节主要介绍单元格对象的End属性和Count属性。

    无言之月
  • 精通Excel数组公式009:提供多个值的数组公式

    如下图1所示,我们在单元格区域中使用数组公式生成序号,这样,使用者就不能够随意删除其中一个单元格中的序号,只能选中该区域后全部删除。

    fanjy
  • Excel小技巧46: 在单元格中输入连续数字的6种方法

    很多时候,我们都需要在工作表中输入连续的数字,特别是用作数据唯一标识时。下面,我们将介绍6种输入连续数字的方法。

    fanjy
  • 经济实惠的Rank函数,让你嗨的飞起来!

    Excel里的排名操作看似很简单,但其实其中有很多我们不知道的小“秘密”,掌握了这些小技巧,让你在用Excel排名的时候速度快的飞起,方便快捷,心情也嗨得飞起来...

    博文视点Broadview
  • 精通Excel数组公式003:数组公式是个啥

    1. 引用数组,包含一个以上的单元格引用,例如单元格区域、工作表引用和定义的名称。

    fanjy
  • Java集合--线程安全(CopyOnWrite机制)

    贾博岩
  • “协力抗疫,码力全开”线上公益黑马+19号+同乘码

    疫情爆发时期, 同乘交通工具会有交叉感染的风险, 小程序「同乘码」提供了快速建聊天群的二维码. 主要亮点:

    大副007

扫码关注云+社区

领取腾讯云代金券