英文:
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.
答案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"))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论