Google Sheets公式来合并两个表格并计算百分比。

huangapple go评论89阅读模式
英文:

Google sheets formula to join 2 tables and calculate percentage

问题

我正在尝试创建一个函数,将两个表格连接在一起,一个表格中有销售员,另一个表格中有其他类型的员工,通过共同的出差编号将它们连接在一起,还需要一个列来显示根据百分比表格卖出的商品数量。

我有一个电子表格,其中更清晰地显示了这些表格以及我想要实现的内容。

任何帮助将不胜感激。

英文:

I am trying to create a function that joins 2 tables where there are sellers and other table where there is other type of employees, join them together with the trip number that was made and also a column with the percentage of what was sold according to a table of percentages.

I have a spreadsheet that makes it more clear of the tables and what i am trying to achieve

Any help will be very much appreciated.

Spreadsheet

答案1

得分: 1

无法提供您所请求的翻译内容,因为这部分是关于代码的说明,不需要翻译。

英文:

Added formula to your sheet here:

=index(query({{A2:B,E2:E*xlookup("Seller",J:J,K:K,)/100};lambda(z,map(index(z,,1),index(z,,2),lambda(a,b,{a,b,xlookup(a,G:G,H:H)*xlookup(xlookup(b,M:M,N:N),J:J,K:K)/100})))(index(split(flatten(G2:index(G:G,counta(G:G))&"|"&transpose(M2:index(M:M,counta(M:M)))),"|")))},"Where Col1<>'' order by Col1"))

Google Sheets公式来合并两个表格并计算百分比。

huangapple
  • 本文由 发表于 2023年2月19日 01:03:28
  • 转载请务必保留本文链接:https://go.coder-hub.com/75494933.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定