连接两列之间的排列 [英] Concatenate permutations between two columns
问题描述
名称城市
--------- ------
John London
Maxx NY
Ashley DC
巴黎
解决方案必须是:
John-london
John-NY
John-DC
John-Paris
Maxx-london
Maxx-NY
。
。
。
.so。
只需将一列中所有元素的文本添加到其他列中所有元素的文本。如果没有提供宏或VB的解决方案,我将不胜感激。
这是一个可以使用的Array Formula,虽然您需要根据多少条目修改矩阵的大小有
= CONCATENATE(索引(A:A,MMULT(ROW(A1:A3)),TRANSPOSE(ROW(B1:B4 ))/ TRANSPOSE(ROW(B1:B4)))), - ,INDEX(B:B,MMULT(ROW(A1:A3)/ ROW(A1:A3),转置(ROW(B1:B4)) ))
假设列A是名称和列B是城市,您将选择12个单元格(3行高,4列宽),将上述公式粘贴到第一个单元格中,然后按 Ctrl + Shift + Enter
创建数组公式。
如果你想看到一个更简单的版本,看看它在 INDEX
引用之前做了什么,请查看同一个区域:
= CONCATENATE(MMULT(ROW(A1:A3)),TRANSPOSE(ROW(B1:B4))/ TRANS POSE(ROW(B1:B4))), - ,MMULT(ROW(A1:A3)/ ROW(A1:A3)),TRANSPOSE(ROW(B1:B4))))
这是一个显示多个单元格输出的单一公式的截图(公式分为2行) :
I need help with an excel assignment.
Name City
---------------
John London
Maxx NY
Ashley DC
Paris
Solution for this must be:
John-london
John-NY
John-DC
John-Paris
Maxx-london
Maxx-NY
.
.
.
.so on.
Simply I have to add text of all elements in one column to text of all elements in other column. I will appreciate if a solution without macros or VB is provided.
Here's an Array Formula you can use, though you will need to modify the size of the matrix depending on how many entries you have
=CONCATENATE(INDEX(A:A,MMULT(ROW(A1:A3),TRANSPOSE(ROW(B1:B4))/TRANSPOSE(ROW(B1:B4)))),"-",INDEX(B:B,MMULT(ROW(A1:A3)/ROW(A1:A3),TRANSPOSE(ROW(B1:B4)))))
Assuming Column A is Names and Column B is Cities, you would select 12 cells (3 rows high, 4 columns wide), paste the above formula in the first cell and press Ctrl + Shift + Enter
to create the array formula.
If you want to see a little simpler version to see what it does before the INDEX
references, check with the same area:
=CONCATENATE(MMULT(ROW(A1:A3),TRANSPOSE(ROW(B1:B4))/TRANSPOSE(ROW(B1:B4))),"-",MMULT(ROW(A1:A3)/ROW(A1:A3),TRANSPOSE(ROW(B1:B4))))
Here's a screenshot (with the formula split in 2 lines) of the single formula displaying the output over multiple cells:
这篇关于连接两列之间的排列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!