英文:
Listing the Combinations in an Interaction Matrix
问题
我想自动化生成一个组合列表的过程，由交互矩阵的结果确定：
我有一个交互矩阵。 "x" 表示两个部分之间的交互：
我已经开始列出组合的过程，并已经能够列出具有各自频率的A部分项目：
我使用VLOOKUP函数将索引值与每个A部分行的"x"计数的累积总和进行比较。
我想要的是一种方法，可以在表格B4:L14中查找列R中的值，然后找到它们各自行中"x"的实例，然后查找第4行中每个"x"实例对应的B部分。
例如，单元格S5:S9应该有值[2; 3; 4; 6; 9]
问：是否可以使用内置函数来实现这一点，还是我需要编写自定义的VBA代码？
非常感谢。
英文:
I would like to automate the process of generating a list of combinations determined by the result of an interaction matrix:
I have an interaction matrix. "x" denotes an interaction between two parts:
I have started the process of listing the combinations and have been able to list Part A items with their respective frequency:
I use the VLOOKUP function to compare the index value to the aggregate which is the cumulative sum of the "x" count for each Part A row.
What I would like is a method to look up a value in column R across table B4:L14 then find the instances of "x" in their respective rows and then lookup at row 4 which Part B corresponds to each "x" instance.
e.g. cells S5:S9 should have the values [2; 3; 4; 6; 9]
Q. Is this achievable using inbuilt functions, or will I have to write custom VBA code?
Much appreciated
答案1
得分: 2
在 Office 365 中:
=LET(
rng,$C$5:$L$14,
hd,$C$4:$L$4,
rw,$B$5:$B$14,
INDEX(FILTER(hd,FILTER(rng,rw=R5)="x"),,COUNTIF($R$5:R5,R5)))
[![enter image description here][1]][1]
较旧版本
=INDEX($4:$4,AGGREGATE(15,7,COLUMN($C$5:$L$14)/(($B$5:$B$14=R5)*($C$5:$L$14="x")),COUNTIF($R$5:R5,R5)))
[![enter image description here][2]][2]
[1]: https://i.stack.imgur.com/nAWyc.png
[2]: https://i.stack.imgur.com/Eoz3u.png
英文:
with Office 365:
=LET(
rng,$C$5:$L$14,
hd,$C$4:$L$4,
rw,$B$5:$B$14,
INDEX(FILTER(hd,FILTER(rng,rw=R5)="x"),,COUNTIF($R$5:R5,R5)))
Older versions
=INDEX($4:$4,AGGREGATE(15,7,COLUMN($C$5:$L$14)/(($B$5:$B$14=R5)*($C$5:$L$14="x")),COUNTIF($R$5:R5,R5)))
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论