需求描述:
已知三种产品A、B、C的单位成本(原材料、人工费、杂费)如下表所示。
Expenses | Product A | Product B | Product C |
Raw Materials | 0.1 | 0.3 | 0.15 |
Labor | 0.3 | 0.4 | 0.25 |
Overhead and miscellaneous | 0.1 | 0.2 | 0.15 |
各产品各季度的产量如下表所示,
Product | Summer | Fall | Winter | Spring |
A | 4000 | 4500 | 4500 | 4000 |
B | 2000 | 2600 | 2400 | 2200 |
C | 5800 | 6200 | 6000 | 6000 |
试汇总计算各季度各成本项的总成本。
解决方法:
使用MMULT函数。
Matrix Multiplication (MMULT) | |||||
Total Expenses | Summer | Fall | Winter | Spring | Year Total |
Raw Materials | 1870 | 2160 | 2070 | 1960 | 8060 |
Labor | 3450 | 3940 | 3810 | 3580 | 14780 |
Overhead and miscellaneous | 1670 | 1900 | 1830 | 1740 | 7140 |
Total Production Costs | 6990 | 8000 | 7710 | 7280 | 29980 |
显示公式后是这样的(局部):
Matrix Multiplication (MMULT) | ||
Total Expenses | Summer | Fall |
Raw Materials | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) |
Labor | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) |
Overhead and miscellaneous | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) | =MMULT(Table3[[Product A]:[Product C]],Table2[[Summer]:[Spring]]) |
可见,只需一个简单的MMULT(Array1, Array2)即可计算出一张汇总表,非常方便。
注意:也可以使用sumproduct函数进行计算,不过,需要将第一个表格进行转置(transpose)操作转换为以下形式。
Expenses | Raw Materials | Labor | Overhead and miscellaneous |
Product A | 0.1 | 0.3 | 0.1 |
Product B | 0.3 | 0.4 | 0.2 |
Product C | 0.15 | 0.25 | 0.15 |
然后,需要针对不同的产品和成本项调整sumproduct公式中的参数。相比使用矩阵乘法,麻烦很多。
MMULT函数的详细介绍和用法,可参考下文: