Excel公式在垂直列表中转置水平值 [英] Excel formula to transpose horizontal values in vertical list

问题描述

我在D1:E2中有文字.单击按钮后通过使用宏将其复制到下一个单元格F1:G2.之后，用户可以在F1:G2中编辑文本.然后，通过按下按钮(使用前面描述的宏)，可以再次复制D1:E2，以便将H1:I2预先填充.因此，通过单击宏按钮代码将D1:E2复制到下一个可用单元格.一键单击F1:G2，再单击H1:I2等.

I have text in D1:E2. By using macro after clicking button it is copied to the next cells F1:G2. After that user can edit text in F1:G2. Then by pressing the button (with previously described macro) D1:E2 can be copied again so H1:I2 will be prefilled. So by one clicking macro button code is copying D1:E2 to the next available cells. One click F1:G2, second click H1:I2 etc.

现在我需要以垂直顺序显示D1:E2，F1:G2，H1:I2，J1:K2的所有值，所以:

Now I need to display all values from D1:E2, F1:G2, H1:I2, J1:K2 in vertical order so:

Text 1  Text 2  Text 3  Text 4  Text 5  etc

将会是:

Text 1  
Text 2  
Text 3  
Text 4  
Text 5
etc

哪种公式最适合这种情况?它还应跳过空单元格并避免出现错误消息.因此，如果H1:I2，J1:K2中没有文本，它将跳过它们.

What formula would work the best for that situation? It should also skip empty cells and avoid error messages. So if there is no text in H1:I2, J1:K2 it would just skip them.

注意！单元格D1:E2，F1:G2，H1:I2，J1:K2合并并包装.如果有帮助，我可以将模型编辑为D1:E1，F1:G1，H1:I1，J1:K1

Note! Cells D1:E2, F1:G2, H1:I2, J1:K2 are merged and wrapped. If it helps I can edit my model to be D1:E1, F1:G1, H1:I1, J1:K1

推荐答案

好，感谢Scott Craner，您可以在此处找到原始帖子 https://superuser.com/questions/1213491/.

Ok, thanks goes to Scott Craner and original post can be found here https://superuser.com/questions/1213491/.

解决方案是使行高* 2(因此13 * 2 = 26)，垂直合并单元格并使用公式:

Solution is to make row height * 2 (so 13*2=26), unmerge cells vertically and use formula:

=INDEX($1:$1;(ROW(1:1)-1)*2+4)

这篇关于Excel公式在垂直列表中转置水平值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！