Excel公式在垂直列表中转置水平值

Excel公式在垂直列表中转置水平值

本文介绍了Excel公式在垂直列表中转置水平值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在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公式在垂直列表中转置水平值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-21 14:38