英文:
Google Sheets List for 80/20 Rule
问题
我有一个客户姓名列表，放在A列，他们的总销售额在B列。这两列每周都会更新。
列表按总销售额从高到低排序。
我想要使用QUERY或其他公式，以显示给我一个客户姓名列表，他们的总销售额合计尽可能接近总销售额列的80%。
ChatGPT让我失望了，所以我来找专家寻求帮助。
如果您想要的话，这里是一个虚拟表格的链接：text
我尝试了各种QUERY的变体，比如选择sum、选择avg、order by、limit等，都没有成功。我还尝试了SUMIF。我能够做到的最接近的是添加累积总额，但累积计算没有正常工作，基本上只显示了4个客户的名字占据了80%，实际上应该是25个名字。
英文:
I have a list of customer names in Column A and their total sales in Column B. Both columns are updated every week.
The list is ordered from most to least by total sales.
I want to use a QUERY or other formula that will show me a list of the customer names with the highest sales whose combined totals make up as close as possible to 80% of the grand total of the total sales column.
ChatGPT has failed me, so I've come to the experts. Please assist.
Here is a link to a dummy sheet if you'd like: text
I tried variations of QUERY select sum, select avg, order by, limit and nothing worked. Tried SUMIF. The closest I could get was adding cumulative totals, but the cumulation didn't work properly and essentially only showed 4 customer names as making up 80% when it should've been 25 names.
答案1
得分: 0
这是一个可能的解决方案：
=ARRAYFORMULA(
LET(names,A2:A,
sales,B2:B,
cumul,SCAN(,SORT(sales,1,0),LAMBDA(a,c,a+c)),
diff,ABS(0.8*SUM(sales)-cumul),
seq,SEQUENCE(ROWS(diff)),
JOIN(" ",FILTER(names,seq<=XLOOKUP(MIN(diff),diff,seq)))))
我们正在计算累积总和，取累积总和与总销售额的80%之间的差的绝对值，以找到最接近的值，最后返回适当的名称。
英文:
Here's a possible solution:
=ARRAYFORMULA(
LET(names,A2:A,
sales,B2:B,
cumul,SCAN(,SORT(sales,1,0),LAMBDA(a,c,a+c)),
diff,ABS(0.8*SUM(sales)-cumul),
seq,SEQUENCE(ROWS(diff)),
JOIN(" ",FILTER(names,seq<=XLOOKUP(MIN(diff),diff,seq)))))
We are calculating the cumulative sum, taking the absolute value of the difference between the cumulative sum and the 80% of the total sales to find the closest value and finally returning the appropriate names.
答案2
得分: 0
=filter(A2:A,lt(scan(,B2:B,lambda(a,c,a+c)),sum(B2:B*0.8)))
英文:
<!-- language-all: js -->
You may try:
=filter(A2:A,lt(scan(,B2:B,lambda(a,c,a+c)),sum(B2:B*0.8)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论