对名称进行计数，将值除以计数的名称并求解欠款语句 [英] Count names, divide value by counted names and solve owes statement

问题描述

我正在尝试使用Google表格(或Microsoft Excel)创建一个公式来解决以下语句问题:

I'm trying to create a formula using Google Sheets (or Microsoft Excel) that solves following statement problem:

  A        B          C
1  Amount  Paid By    Beneficiaries
2  $20,00  James      Elizabeth, James, Michael
3  $4,50   Elizabeth  Elizabeth, Michael
4  $6,90   James      Michael

我想计算每个人的欠款(我想要获得的结果):

I want to calculate how much each individual owes to each other (the result I'm trying to obtain):

Elizabeth > Elizabeth   €2,25
Elizabeth > James       €6,67
Elizabeth > Michael     €0,00
James > Elizabeth       €0,00
James > James           €6,67
James > Michael         €0,00
Michael > Elizabeth     €2,25
Michael > James         €13,57
Michael > Michael       €0,00

现在，我创建了两个新列，分别为Number和Amount/Number.D和E.

For now, I've created two new columns, D and E, respectively called Number and Amount/Number.

首先，我使用以下公式对C列名称进行计数(将其放入单元格D2):

For the first, Number, I count the column C names using following formula (put in cell D2):

=COUNTIF(C2;"*"&"Elizabeth"&"*") + COUNTIF(C2;"*"&"James"&"*") + COUNTIF(C2;"*"&"Michael"&"*")

对于第二个Amount/Number，我使用以下公式将A列与D相除(放入单元格E2):

For the second, Amount/Number, I divide column A with D using following formula (put in cell E2):

=A2/D2 

所以我当前的语句文件如下:

So my current statement file looks following:

  A        B          C                          D      E
1  Amount  Paid By    Beneficiaries              Number Amount/Number
2  $20,00  James      Elizabeth, James, Michael  3      $6,67
3  $4,50   Elizabeth  Elizabeth, Michael         2      $2,25
4  $6,90   James      Michael                    1      $6,90

因此要计算Elizabeth > Elizabeth，我使用了以下公式:

So to calculate Elizabeth > Elizabeth, I've used the following formula:

=SUMIFS(E:E; B:B; "Elizabeth"; C:C; "*"&"Elizabeth"&"*")

因此要计算Elizabeth > James，我使用了以下公式:

So to calculate Elizabeth > James, I've used the following formula:

=SUMIFS(E:E; B:B; "James"; C:C; "*"&"Elizabeth"&"*")

以此类推.

我的问题是:是否可以使用=SUMPRODUCT()或类似的公式在不创建列D(Number)和E(Amount/Number)的情况下计算语句?解决我的问题的方法可能更简单，您的帮助将不胜感激.谢谢！

My question is: is it possible to do calculate the statement without creating columns D (Number) and E (Amount/Number), using =SUMPRODUCT() or some similar formula? There is probably an easier way to solve my problem, your help will be appreciated. Thanks!

推荐答案

Google表格

请尝试公式:

=QUERY( {"Paid By"\"Beneficiary"\ "Amount"; TRANSPOSE(SPLIT(JOIN(";"; ARRAY_CONSTRAIN(ArrayFormula( rept(B2:B&";";LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)); COUNTA(A2:A);1));";")) \ TRANSPOSE(SPLIT(TEXTJOIN(", ";1;C2:C);", ")) \ TRANSPOSE(SPLIT(JOIN(";"; ARRAY_CONSTRAIN(ArrayFormula( rept(A2:A / (LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)&";"; LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)); COUNTA(A2:A);1));";")) }; "select Col1, Col2, sum(Col3) group by Col1, Col2")

=QUERY( {"Paid By"\"Beneficiary"\ "Amount"; TRANSPOSE(SPLIT(JOIN(";"; ARRAY_CONSTRAIN(ArrayFormula( rept(B2:B&";";LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)); COUNTA(A2:A);1));";")) \ TRANSPOSE(SPLIT(TEXTJOIN(", ";1;C2:C);", ")) \ TRANSPOSE(SPLIT(JOIN(";"; ARRAY_CONSTRAIN(ArrayFormula( rept(A2:A / (LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)&";"; LEN(C2:C)-LEN(SUBSTITUTE(C2:C;",";""))+1)); COUNTA(A2:A);1));";")) }; "select Col1, Col2, sum(Col3) group by Col1, Col2")

结果是查询报告:

Paid By        Beneficiary    sum Amount
Elizabeth      Elizabeth            2,25
Elizabeth      Michael              2,25
James          Elizabeth            6,67
James          James                6,67
James          Michael             13,57

这篇关于对名称进行计数，将值除以计数的名称并求解欠款语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！