如何在PHP中实现Excel的SUMPRODUCT函数功能？

数据表格 #

需求与问题 #

我需要在PHP中实现电子表格公式=SUMPRODUCT(A1:A24,B1:B24)的功能，以下是我尝试的代码，但逻辑有误：

$a = array( 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.35, 0.20, 0.05, 0.05, 0.20, 0.35, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72 );
$b = array( 97.29, 67.68, 66.97, 67.57, 67.80, 94.71, 136.23, 146.77, 139.06, 95.39, 58.66, 48.90, 37.07, 61.31, 95.26, 140.80, 228.09, 318.25, 407.95, 413.11, 409.11, 394.49, 300.57, 162.24 );

$a_total = 0;
$b_total = 0;

foreach( $a as $row ) {
    $a_total += $row;
}
foreach( $b as $row ) {
    $b_total += $row;
}

$sumproduct = $a_total * $b_total;

错误分析 #

你当前的代码逻辑完全不符合SUMPRODUCT的要求：SUMPRODUCT是将两个数组对应位置的元素相乘，再把所有乘积相加，而你是先分别计算两个数组的总和，再将总和相乘，这是完全不同的计算逻辑。

正确实现方式 #

方法一：foreach循环遍历累加 #

$a = array(0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.35, 0.20, 0.05, 0.05, 0.20, 0.35, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72);
$b = array(97.29, 67.68, 66.97, 67.57, 67.80, 94.71, 136.23, 146.77, 139.06, 95.39, 58.66, 48.90, 37.07, 61.31, 95.26, 140.80, 228.09, 318.25, 407.95, 413.11, 409.11, 394.49, 300.57, 162.24);

$sumproduct = 0;
// 遍历数组，对应位置元素相乘后累加
foreach ($a as $index => $value) {
    $sumproduct += $value * $b[$index];
}

echo $sumproduct; // 输出：1602.3474

方法二：array_map + array_sum简洁实现 #

$a = array(0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.35, 0.20, 0.05, 0.05, 0.20, 0.35, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72, 0.72);
$b = array(97.29, 67.68, 66.97, 67.57, 67.80, 94.71, 136.23, 146.77, 139.06, 95.39, 58.66, 48.90, 37.07, 61.31, 95.26, 140.80, 228.09, 318.25, 407.95, 413.11, 409.11, 394.49, 300.57, 162.24);

// 用array_map处理对应元素相乘，再用array_sum求和
$sumproduct = array_sum(array_map(function($x, $y) {
    return $x * $y;
}, $a, $b));

echo $sumproduct; // 输出：1602.3474

内容的提问来源于stack exchange，提问作者user5834867