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

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

2021/8/31

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

• 「複数列のデータを1列にまとめたい」との質問がありました。
  ただし、データの入っているセルの値だけを取り出したいとのことでした。
• この質問ではINDEX($A$1:$G$6,INT((ROW(A1)-1)/7)+1,MOD(ROW(A1)-1,7)+1)　といった数式でできないとのことでした。
  この数式は 元データがA1:G6にあるのが前提なので、 7 (列数)となっています。
  この数式を下方向へコピーします。
• データ数が少ない例で説明します。確かに空欄がない場合は、提示された数式でOKでした。
  Microsoft365など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))
  仕組みは同じです。
  

空白のセルがある場合は？　　Topへ

• セルに空欄があると、下図のように 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