Excel(或VBA)条件转置行 [英] Excel (or VBA) Conditional Transposing Rows
问题描述
我在列标题中有几个月内有一个数据集,我想根据当前的月份复制这些行。一个问题是自动化,因为用户不断被添加/删除。
UserID用户状态Jan($)Feb $)Mar($).... Dec($)
111 AAA CT $ 55 $ 100 $ 125 $ 100
112 BBB NJ $ 50 $ 34 $ 125 $ 125
113 CCC NV $ 55 $ 100 $ 125 $ 155
114 DDD VT $ 95 $ 108 $ 75 $ 199
115 EEE NJ $ 20 $ 100 $ 125 $ 120
示例输出:
UserID用户状态月份
111 AAA CT Jan $ 55
111 AAA CT Feb $ 100
111 AAA CT Mar $ 125
111 AAA CT Apr $ 80
111 AAA CT May $ 70
。
。
。
115 EEE NJ Nov $ 50
115 EEE NJ DEC $ 120
对于公式答案:
对于前3列:
在第一列的第一个单元格中,您需要您的数据,我的是A11 :
= INDEX(A:A,QUOTIENT(ROW(1:1)-1,12)+2)
本月:
复制两列,然后复制。
在您想要数据的第四列的第一个单元格中,我的是D11:
=选择(MOD(ROW(1:1)-1,12)+1,Jan,Feb,Mar ,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
pre>
然后复制。
对于金额:
在您想要数据的第五列的第一个单元格中,我的是E11:
= INDEX($ A:$ O,QUOTIENT(ROW(1:1)-1,12)+ 2,MOD(ROW(1:1)-1,12)4)
然后将其复制。
I have a Dataset below with months in the column headers, I'd like to duplicate the rows based on the months present. One issue I had is the automation, as users are constantly being added/removed.
UserID User State Jan($) Feb ($) Mar ($) .... Dec ($) 111 AAA CT $55 $100 $125 $100 112 BBB NJ $50 $34 $125 $125 113 CCC NV $55 $100 $125 $155 114 DDD VT $95 $108 $75 $199 115 EEE NJ $20 $100 $125 $120
Sample Output:
UserID User State Month Spend 111 AAA CT Jan $55 111 AAA CT Feb $100 111 AAA CT Mar $125 111 AAA CT Apr $80 111 AAA CT May $70 . . . 115 EEE NJ Nov $50 115 EEE NJ DEC $120
解决方案For a formula answer:
For the First 3 Columns:
In the first cell in the first column you want your data, mine is A11:
=INDEX(A:A,QUOTIENT(ROW(1:1)-1,12)+2)
For the Month:
Copy over two Columns, then copy down.
In the first cell in the fourth column in which you want the data, mine is D11:
=CHOOSE(MOD(ROW(1:1)-1,12)+1,"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")
Then copy down.
For the Amounts:
In the first cell in the fifth column in which you want the data, mine is E11:
=INDEX($A:$O,QUOTIENT(ROW(1:1)-1,12)+2,MOD(ROW(1:1)-1,12)+4)
Then copy it down.
这篇关于Excel(或VBA)条件转置行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!