英文:
Average of BYROW and BYCOL results in Excel
问题
- 我想找到第1行总和和第1列总和的平均值，并重复这个过程对所有行和列，得到一个包含20个值的数组。
- 我想使用
LARGE(Array, k)函数来找到步骤1中数组中存储的前10个最大值。
英文:
I have a 20x20 table for which I applied the BYROW and BYCOL functions to get the row totals and column totals.
Function 1:
B22=BYROW(B2:U21,LAMBDA(row,SUM(row)))
Function 2:
V2=BYCOL(B2:U21,LAMBDA(column,SUM(column)))
How can I achieve the following steps?
- I want to find the average of Row 1 total and Column 1 total and repeat this for all rows and columns resulting in an array of 20 values.
- I want to use
LARGE(Array, k)function to find the top 10 values of the values stored in the array in step 1.
答案1
得分: 3
将它们全部放在一起：
=TAKE(SORT(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),1,-1),10)
[![enter image description here][1]][1]
---
根据 @user11222393 的说法，要获取值来自的行/列：
=TAKE(SORT(HSTACK(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),SEQUENCE(COUNT(B22#),1,1,1)),1,-1),10)
由于相对行和相对列号相同，它将返回一个单一的相对编号。
[![enter image description here][2]][2]
---
如果你想要更漂亮一点：
=TAKE(SORT(HSTACK(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),LET(r,SEQUENCE(COUNT(B22#),1,2,1),"CLM: "&CHAR(65+r)&" rw: "&r)),1,-1),10)
[![enter image description here][3]][3]
[1]: https://i.stack.imgur.com/Hhmx7.png
[2]: https://i.stack.imgur.com/mbsWw.png
[3]: https://i.stack.imgur.com/jUDpl.png
英文:
Just put them all together:
=TAKE(SORT(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),1,-1),10)
As stated by @user11222393, to get the row/column from where the value comes:
=TAKE(SORT(HSTACK(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),SEQUENCE(COUNT(B22#),1,1,1)),1,-1),10)
Since relative row and relative column number are the same it will return a single relative number.
If you want it a little prettier:
=TAKE(SORT(HSTACK(BYROW(HSTACK(TRANSPOSE(B22#),V2#),LAMBDA(RW,AVERAGE(RW))),LET(r,SEQUENCE(COUNT(B22#),1,2,1),"CLM: "&CHAR(65+r)&" rw: "&r)),1,-1),10)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论