英文:
Excel mapping over two sheets using a concat
问题
我在Excel文件中有以下两张表格：
表格 "缺失模型"：
列 A 包含产品的文章编号。在随后的 B 到 H 列（也可能只有一个条目，因此只有 B 列中的一个值）中，有产品所属的模型的标识。
第二张表格 "模型"，列 A 包含 id_m，列 B 包含名称，列 C 包含颜色。名称和颜色的组合，即列 B 和 C，以空格分隔，与 "缺失模型" 中列 B 到 H 的模型标识完全匹配。
现在，在一个新的表格中，我想在列 A 中有文章编号，并在列 B 中有对应的 id_m，其中 "缺失模型" 的列 B 到 H 与 "模型" 的列 B 和 C 的连接应该发生。
因此，例如在 "缺失模型" 中我有以下条目：
| A | B | C |
|---|---|---|
| 123 | model green | model red |
| 456 | model blue |
在 "模型" 中有以下条目：
| A | B | C |
|---|---|---|
| 1 | model | green |
| 2 | model | blue |
| 3 | model | red |
我希望得到一个新表格如下：
| A(文章编号) | B (id_m) |
|---|---|
| 123 | 1 |
| 123 | 3 |
| 456 | 2 |
我该如何实现这个？
英文:
I have the following two sheets in an Excel file:
Sheet "Missing_models":
Column A contains the article number of a product. In the subsequent columns B to maximum H (can also only be only one entry, so only a value in B), there are designations for models to which the product belongs.
In the second sheet "Models", column A contains the id_m, column B contains the name, and column C contains the color. The combination of name and color, i.e., columns B and C, separated by a space, exactly matches the model designations in columns B to H in "Missing_models".
Now, in a new sheet, I want to have the article number in column A and the corresponding id_m in column B, where the mapping between columns B-H of "Missing_models" and the concatenation of columns B and C from "Models" should take place.
So for example in missing_models i have the following entry:
| A | B | C |
|---|---|---|
| 123 | model green | model red |
| 456 | model blue |
In models there are the entries:
| A | B | C |
|---|---|---|
| 1 | model | green |
| 2 | model | blue |
| 3 | model | red |
From that i would like to have a new sheet like this:
| A(articelnumber) | B (id_m) |
|---|---|
| 123 | 1 |
| 123 | 3 |
| 456 | 2 |
How can I achieve this?
答案1
得分: 2
这将在Microsoft 365中执行：
=LET(missing, missing_models!A1:C2,
models, FILTER(models!B:B&" "&models!C:C,models!A:A<>""),
a, DROP(REDUCE(0,SEQUENCE(ROWS(missing)),LAMBDA(x,y,LET(z,INDEX(missing,y,),
d, DROP(z,,1),VSTACK(x,TAKE(z,,1)&",",TOCOL(d,3))))),1),
HSTACK(TEXTBEFORE(a,","),
INDEX('models'!A:A,XMATCH(TEXTAFTER(a,","),models))))
首先将missing_models数据转换成它的排列，其中文章编号和型号由逗号分隔。
然后将a中逗号分隔符前面的值与models工作表的列A中的ID堆叠在一起，其中该工作表的列B和C的连接满足a中逗号分隔符后面的值。
基本上，它查找缺失型号的行号，以及models中列B和C的连接到a中逗号分隔符后面的值，并返回models中列A的值。复杂之处在于缺失型号的存储方式，因此需要使用排列解决方案。
有关排列部分的更多解释可以在此处找到。
英文:
This would do in Microsoft 365:
=LET(missing, missing_models!A1:C2,
models, FILTER(models!B:B&" "&models!C:C,models!A:A<>""),
a, DROP(REDUCE(0,SEQUENCE(ROWS(missing)),LAMBDA(x,y,LET(z,INDEX(missing,y,),
d, DROP(z,,1),VSTACK(x,TAKE(z,,1)&","&TOCOL(d,3))))),1),
HSTACK(TEXTBEFORE(a,","),
INDEX('models'!A:A,XMATCH(TEXTAFTER(a,","),models))))
It first changes the missing_models data into a list of it's permutations where the article number and model are separated by delimiter ,.
Then it stacks the values in front of the ,-delimiter of aand the ID from column A of the models sheet, where the concatenation of column B and C from that sheet meet the value after the ,-delimiter of a.
Basically it looks up the row number of the missing models to the concatenation of column B & C in models and returns the value in column A from models.
The complication is the way the missing models are stored, hence the permutations solution.
Further explanation on the permutations part can be found here
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论