Excel公式练习80： 双向查找并求和

excelperfect

本次的练习是：下图1显示了一周中每天在不同时间段售卖的咖啡数量，现在要求周二和周四12点之后一共卖出的咖啡数量（这段时间卖出的咖啡数量如图中阴影所示）。

图1

为方便起见，我们定义了命名区域，如上图1中所示：

命名区域data代表数据单元格区域C5:G14；

命名区域times代表时间区域B5:B14；

命名区域days代表星期几区域C4:G4。

要求公式不能动态定位要求和的单元格域，且尽可能使用命名区域使公式易读。

先不看答案，自已动手试一试。

解决方案

有多种方法可以实现，但最常见且简单的方法是使用SUMPRODUCT函数。

在单元格I5中的公式为：

=SUMPRODUCT(data*(times>0.5)*((days='周二')+(days='周四')))

公式中的0.5代表中午12点，因为Excel使用小数来处理1天中的时间，例如上午6点是0.25，中午12点是0.5，下午6点是0.75。

这样，公式中的：

times>0.5

转换为：

{FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE}

将其与数据区域data相乘：

data*(times>0.5)

转换为：

{0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

10,11,8,9,7;

2,4,4,6,5;

2,0,2,0,0;

3,4,3,2,0;

2,0,1,0,0}

而公式中的：

(days='周二')+(days='周四')

转换为：

{0,1,0,1,0}

上述两个矩阵相乘，得到：

{0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

0,0,0,0,0;

0,11,0,9,0;

0,4,0,6,0;

0,0,0,0,0;

0,4,0,2,0;

0,0,0,0,0}

传递给SUMPRODUCT函数，得到结果：

36

如下图2所示。

图2

小结：熟练掌握矩阵运算，是编写简单易行公式的重要技能。

注：本次的练习整理自exceljet.net。

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料。