1. Home » 
2. Excel講座の総目次 » 
3. Excel講座 Q&A(Tips) » 
4. 複数列のデータを1列にまとめる/複数行のデータを1行にまとめる

複数列のデータを1列にまとめる/複数行のデータを1行にまとめる

更新:2025/12/13;作成:2021/8/31

複数列のデータを1列にまとめる　　Topへ

• Excel for Microsoft365では2022/8月ごろから使えるようになったVSTACK関数やTOCOL関数で容易になりました。
  VSTACK関数やTOCOL関数はExcel for Microsoft365,Excel2024 以降で使用できます。
• G1セルには　=TOCOL(A1:E5)、I1セルには =TOCOL(A1:E5,,TRUE)　と入力しています。
  TOCOL関数の引数で「行でスキャンする(規定)」と「列でスキャンする」と読み込む方向を変えることができます。
  
• 1列ごとのデータをなど、飛び飛びの列を１列に並べることもできます。
  CHOOSECOLS(A1:E5,{1,3,5})　で1,3,5列のデータを取り出します。
  • CHOOSECOLS関数はExcel for Microsoft365,Excel2024 以降で使用できます。
    この部分は2025/12/09に更新しました。
    以前は　=VSTACK(TOCOL(A1:A5),TOCOL(C1:C5),TOCOL(E1:E5)) としていました。
  この取り出した配列をTOCAL関数で列でスキャンします。
  G1セルには 　=TOCOL(CHOOSECOLS(A1:E5,{1,3,5}),,TRUE)　と入力しています。
  

空白のセルを無視する　　Topへ

• Excel for Microsoft365では2022/8月ごろから使えるようになったTOCOL関数で容易になりました。
  Excel2024でも使用できます。
• G1セルには　=TOCOL(A1:E5,1)、I1セルには =TOCOL(A1:E5,1,TRUE)　と入力しています。
  

複数行のデータを1行にまとめる　　Topへ

• Excel for Microsoft365では2022/8月ごろから使えるようになったWRAPROWS関数やTOROWS関数で容易になりました。
  これらの関数はExcel for Microsoft365,Excel2024 以降で使用できます。
• 下表のような２行のデータを１行のデータにまとめたいと思います。
  

  氏名	郵便番号	住所	性別
  		生年月日	血液型
  長谷川 麻衣子	206-0924	東京都中央区日本橋本町1-2-8	女
  		1954年3月24日	A
  小林 徳宏	207-9463	東京都品川区東品川1-2-8	男
  		1995年9月14日	A
  重松 恭子	802-7078	福岡県福岡市博多区博多駅前2-5-6	女
  		1996年8月27日	B
  黒川 卓也	984-9128	宮城県宮城郡七ヶ浜町遠山3-4-11	男
  		1986年1月6日	O
  栗田 哲哉	505-7632	岐阜県多治見市喜多町4-1-6	男
  		1988年3月12日	O
  湯原 知彦	108-4112	東京都立川市富士見町2-4-4	男
  		1988年6月3日	B

• =WRAPROWS(TOROW(A1:D14,1),6)　で１行にまとめることができます。
  • TOROW(A1:D14,1)：A1:D14のデータを空白セルを無視して１行にまとめます。
  • =WRAPROWS(TOROW(A1:D14,1),6)：項目数の６列ごとに配列を折り返して表示しています。
  

TOCOL関数が使えないバージョンで複数列のデータを1列にまとめる　　Topへ

• 「複数列のデータを1列にまとめたい」との質問がありました。
  ただし、データの入っているセルの値だけを取り出したいとのことでした。
• この質問ではINDEX($A$1:$G$6,INT((ROW(A1)-1)/7)+1,MOD(ROW(A1)-1,7)+1)　といった数式でできないとのことでした。
  この数式は 元データがA1:G6にあるのが前提なので、 7 (列数)となっています。
  この数式を下方向へコピーします。
• データ数が少ない例で説明します。確かに空欄がない場合は、提示された数式でOKでした。
  Excel for Microsoft365,Excel2021以降のバージョンでSpillが使える場合は、
  数式は =INDEX(A1:E5,INT((ROW(A1:A25)-1)/5)+1,MOD(ROW(A1:A25)-1,5)+1)　とちょっと改変しています。
  
• Spill機能が使えない場合は
  G1セルに　=INDEX($A$1:$E$5,INT((ROW(A1)-1)/5)+1,MOD(ROW(A1)-1,5)+1)　と入力して、下方向へコピーします。
  この数式は列方向(A1,B1,C1・・・)でデータを取り出しています。
  
  • この数式はINDEX関数でセル範囲 A1:E5 から 1行目の A列〜E列を取り出し、次いで、2行目の A列〜E列を取り出すような仕組みになっています。
    =INT((ROW(A1)-1)/5)+1　と　=MOD(ROW(A1)-1,5)+1 は1〜5の数値を繰り返し発生するようになっています。
    
• ちなみに、取り出す方向が行方向の場合の数式は、行と列を入れ替えて=INDEX(A1:E5,MOD(ROW(A1:A25)-1,5)+1,INT((ROW(A1:A25)-1)/5)+1)　とすることができます。
  また、↓のようなものもありました。
  =INDEX(A1:E5,MOD(ROW(A1:A25)-1,5)+1,ROUNDUP(ROW(A1:A25)/5,0))
  仕組みは同じです。
  

TOCOL関数が使えないバージョンで空白のセルを無視する　　Topへ

• =INDEX($A$1:$E$5,INT((ROW(A1)-1)/5)+1,MOD(ROW(A1)-1,5)+1)
  セルに空欄があると、下図のように 0 が返されてしまいます。
  
• 空白セルを無視するには、どのように数式を組めばよいのかわかりませんでしたので、VBAで処理することにしました。
  数式は質問者さんが提示されたもので、私にはちょっと考えつかない数式なもので・・・、
  

  Sub test() 　Dim myRng As Range, cn As Long 　Dim c As Range 　　Set myRng = Range("A1:E5") ’値があるかを判断するセル範囲 　　ReDim myData(1 To 25, 1 To 1)’空白でないセルのデータを配列に格納します 　　cn = 0 'cnの開始値を1とするため、ここでは0を設定します 　　For Each c In myRng 'セル範囲を1個づつチェックします 　　　　If c.Value <> "" Then 'セルの値が"" でない場合セルに値を入力します 　　　　　cn = cn + 1 'セルの値が""でない場合はcnの値をカウントアップします 　　　　　myData(cn, 1) = c.Value 　　　　End If 　　Next c 　　'↓G1セルから下のセルをクリアします。 　　Range(Range("G1"), Range("G1").End(xlDown)).ClearContents 　　'配列のデータをセルに書き出します 　　Range("G1").Resize(cn, 1).Value = myData End Sub

• 空白セルは無視して、複数列のデータを1列にまとまることができました。
  
• 書き出すデータがA列、B列、C列・・・といった順番にしたい場合。
  

  Sub test2() 　Dim myRng As Range, cn As Long 　Dim i As Long, j As Long 　Set myRng = Range("A1:E5") '値があるかを判断するセル範囲 　ReDim myData(1 To 25, 1 To 1) 　cn = 0 'cnの開始値を1とするため、ここでは0を設定します 　For i = 1 To 5 　　For j = 1 To 5 　　　If myRng(j, i) <> "" Then 　　　　cn = cn + 1 'セルの値が""でない場合はcnの値をカウントアップします 　　　　myData(cn, 1) = myRng(j, i) 　　　End If 　　Next j 　Next i 　'↓G1セルから下のセルをクリアします。 　Range(Range("H1"), Range("H1").End(xlDown)).ClearContents 　Range("H1").Resize(cn, 1).Value = myData End Sub

• 列ごとの順番で空白以外のデータを取り出すことができました。
  

スポンサードリンク

　PageViewCounter
　
　Since2006/2/27