索引/匹配/生成多行和多列 [英] Index/Match/Sumproduct multiple rows and columns

问题描述

我正在努力获得特定帐户的年终总额。假设我们现在在七月。帐户 5340 的年份总额应为 2800 。

在下面的公式中， AT29 = 5340 和 AT28 = 7

我尝试了

= SUM（INDEX（AV $ 4：AV $ 25 MATCH（AT29，AU $ 4：AU $ 24,0））：INDEX（AV $ 4：BG $ 25 MATCH（AT29，AU $ 4：AU $ 24 0），7）），但这只能给我找到第一行。

= SUMPRODUCT（（AU4： AU24 = AT29）* AV4：BG24）但是给我全年。

= SUMPRODUCT（（AU4 ：AU24 = AT29）* INDEX（AV4：BG24，，AT28））
但是只给了我7月份的金额。

= SUMPRODUCT（（AU4：AU24 = AT29）*（SUM（INDEX（AV4：AV24，1）：INDEX（AV4：BG24，AT28））））
，但这给我一些我不知道是什么是XD

解决方案

因为您的数据是在主索引列中排序的，所以您可以使用单个OFFSET函数的单个SUM函数执行此操作。

要确定区域你将要求，从数据的上方和左边的左上角开始 - 让我们说A1（我不能从你的例子中得知你的页面是如何实际设置的）。您将要下移行数，直到您匹配您要查找的帐户为止。您将需要移至右侧1列，因为您将始终从1月开始。然后，您将转到数据行中与该帐号相匹配的行数。你去测试时间线中有几个月的列数。一起，这看起来像：

  = SUM（OFFSET（A1，MATCH（A12，A1：A9,0），1 ，COUNTIFS（A2：A9，A12），A13））
  

注意：这假设您的账户从A2：A9，并且您在单元格A12中输入了一个指定的帐号，并且已经计算了单元格A13中迄今为止的当前月份数。

TL; DR ：总结一个从左上方开始的特定帐户的第一个实例的2D框，与该帐户的实例一样，下降了多少行，并转到迄今为止有几个月的栏目数量很多。

I am trying to get year to date total for a particular account. Suppose we're in July now. The year to date total for account 5340 should be 2800.

In the formulas below, AT29=5340 and AT28=7

I have tried

=SUM(INDEX(AV$4:AV$25,MATCH(AT29,AU$4:AU$24,0)):INDEX(AV$4:BG$25,MATCH(AT29,AU$4:AU$24,0),7)) but that only gives me the first row found.

=SUMPRODUCT((AU4:AU24=AT29)*AV4:BG24) but that gives me the whole year.

=SUMPRODUCT((AU4:AU24=AT29)*INDEX(AV4:BG24,,AT28)) but that just gives me the sum for July only.

=SUMPRODUCT((AU4:AU24=AT29)*(SUM(INDEX(AV4:AV24,,1):INDEX(AV4:BG24,,AT28)))) but that gives me something I have no idea what it is XD

解决方案

Because your data is ordered on the primary index column, you can do this with a single SUM function overtop of a single OFFSET function.

To determine the area you will be summing, start at the top left corner above and to the left of your data - let's say that's A1 (I can't quite tell from your examples how your page is actually set up). You will want to move down the number of rows until you MATCH the account you're looking for. You'll want to move to the right 1 column, because you will always be starting in January. Then you go for as many rows as there are matches for that account number within your data. You go for as many columns as there are months in your test timeline. Together, this looks something like:

=SUM(OFFSET(A1,MATCH(A12,A1:A9,0),1,COUNTIFS(A2:A9,A12),A13))

Note: This assumes your accounts go from A2:A9, and that you've typed up a specified account number in cell A12, and that you've calculated the number of months in the year to date in cell A13.

TL;DR: sum a 2D box that starts at the top left with the first instance of the particular account, goes down for as many rows as there are instances of that account, and goes to the right for as many columns as there are months in the year to date.

这篇关于索引/匹配/生成多行和多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！