在Excel中，如何将范围相乘 [英] In Excel, how to multiply ranges

问题描述

在Excel中，我想知道如何将一个单元格范围(例如A1:F10)乘以另一范围的单元格(静态范围例如H1:M10)，然后将答案仅放在一个单元格中(例如Cell P1)....然后向下移动一行，并将新范围(例如A2:F11)乘以相同的静态范围(例如H1:M10)，然后将答案放在第一个答案下方的单元格中(例如P2)，依此类推...

In Excel, I want to know how to multiply one range of cells (say A1:F10) by another range of cells (static range say H1:M10) then put the answer in one cell only (say Cell P1) .... then move down one row and multiply the new range (say A2:F11) by the same static range (say H1:M10) then put the answer in the cell below the first answer (say P2) and so on....

相乘时，我需要A1乘以H1，B1乘以I1，A2乘以H2，依此类推...

When multiplying I need A1 to multiply by H1, B1 multiplied by I1, A2 multiplied by H2 and so on...

我不知道，请谢谢您的帮助.有关信息，我的范围是2800个单元格乘以2800个单元格，范围相当大.有没有在Excel中执行此操作的方法，或者我需要使用VB?非常感谢你...

I have no clue, please thank you for your help... For info my range is a range of 2800 cells by 2800 cells so fairly large. Is there a way of doing this in Excel or do I need to use VB? thank you so much...

推荐答案

您可以为此使用Sumproduct.对于第一个范围，请使用相对参考，对于第二个范围，请使用绝对参考.

You can use Sumproduct for that. Use a relative reference for the first range and an absolute reference for the second range.

考虑以下屏幕截图:

单元格P1中的公式为

=SUMPRODUCT(A1:F10*$H$1:$M$10)

复制下来.在单元格P2中，它将更改为

Copy down. In cell P2 it will change to

=SUMPRODUCT(A2:F11*$H$1:$M$10)

，依此类推，递增每行中第一个范围的行.第二个范围固定为带有$符号的H1至M1，向下复制时不会更改.

and so on, incrementing the rows of the first range in each row. The second range is fixed to H1 to M1 with the $ signs and will not change when copied down.

这篇关于在Excel中，如何将范围相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！