Excelで複数の列の中の文字を一列に並べる！

質問

Excelに関して質問です。

複数の列に入力されている文字を一つの列にまとめる方法が知りたいです。
その際、文字が入力されていない箇所は詰めて並べたいと思います。

範囲はB4～AD８３の間です。
ただ、状況として範囲内の空白には書式（関数）が入っている状態です。

例
　A　　　B　　　C　　　　　D　　　E
１文字１　空白　文字２　　文字３　　空白
２空白　文字４　文字５　　空白　　　空白

F
文字１
文字２
文字３
文字４
文字５

何卒、ご教授よろしくお願いします。

回答No.1

msMike · ベストアンサー

１．セル F1 に次式を入力して、此れを下方にズズーッとオート
＿＿フィル(→添付図左から２列目)
＿＿ =OFFSET(A$1,(ROW(A1)-1)/5,MOD(ROW(A1)-1,5))
２．列Ｆ全体を選択
３．[コピー]→[値の貼り付け]
４．列Ｆ全体を選択
５．[ジャンプ]→[セル選択]
６．“定数”に目玉入れ、その下の“数値”以外のチェック外し
７．[OK]をツン(→添付図右から２列目)
８．[編集]→[削除]→“上方向にシフト”に目玉入れ
９．Enterキーを「エイヤッ！」と叩き付け！(→添付図右端)

お礼

わかりやすい説明ありがとうございます。

回答No.5

bunjii

>$A$1:$E$2⇒$B$4:$AD$83に変更してセルＡ１に式を埋め込みオートフィルでやってみたのですが、空白ができるだけでした。
回答No.2とNo.3の両方を満足する数式を改めて提示します。
A1=IFERROR(INDEX($B$4:$AD$83,INT(SMALL(INDEX(($B$4:$AD$83<>"")*(ROW($A$1:$AD$80)*100+COLUMN($A$1:$AC$80))+($B$4:$AD$83="")*10000,0,0),ROW())/100),MOD(SMALL(INDEX(($B$4:$AD$83<>"")*(ROW($A$1:$AC$80)*100+COLUMN($A$1:$AC$80))+($B$4:$AD$83="")*10000,0,0),ROW()),100)),"")
この数式で試してください。
Excel 2007/2010/2013/2016で再現できるはずです。

回答No.4

imogasi

下記はＶＢＡで処理してみます。
質問者は関数で、でしかやる気がない、のかもしれないが（その場合は下記は無視してください）、他の読者、将来のこの質問のＷＥＢ照会での読者もあるのでコードを上げておく。
仕事などでエクセルを使うのなら、VBA習得は必須と思う。
ーー
この質問は、エクセル表の「データの組換え」タイプの問題だが、関数では式が複雑になり、関数の中上級者でないと式の意味さえ分かりにくいものになる場合が多い。
各列のデータがＥ列（第５列）までと決まっているらしいので、やや易しくなっているかと思う。
下記VBAは行ごとデータ列数がバラバラでもOK。
実質のコード行は７－８行。
ーー
コードに注釈を詳しく加えておいたので参考に。
例データ　Ｓｈｅｅｔ１に
ーは空白セルです。OKWEBの画面上で、左詰めにならないようにあえてーを入れたもの。
省力化してセルのデータは１文字にしているが、長い文章や数値でも構わない
日付のセルがある場合はもう一細工いるが略。
a　 b　 c
d　 e　 ー　 f　 g
h　 i　　 ー j　 k
l　 ー　 m
空白行
n　 o　 p　 q　 r　 s
ーーー
コード
標準モジュールに下記をコピペ
シート名の指定部分をを質問者の場合のシート名に修正する。
テスト以外（データ数が多いとき）では、MsgBoxの行は削除して実行してください。
ーー
VBAコード内をどこでもよい、クリックしておいて、F5キーを押す（実行）
Sub test01()
Set sh1 = Worksheets("Sheet1") '元データのシート名　Sheet1 適宜変えること
Set sh2 = Worksheets("Sheet2") '結果を出すシートのシート名　Sheet2　適宜変えること
'--
lr = sh1.Range("A100000").End(xlUp).Row 'データの最終行を取得
MsgBox lr
k = 1 '結果の最初行は第１行目とする
'--各行の処理を繰り返す
For i = 1 To lr
rr = sh1.Range("XFD" & i).End(xlToLeft).Column 'その行のデータのある最右列を取得
If rr = 1 Then GoTo p1 '空白行ならスキップ
MsgBox rr
For j = 1 To rr 'その行の各列の処理を繰り返し
If sh1.Cells(i, j) <> "" Then '空白セルならばスキップ
sh2.Range("A" & k) = sh1.Cells(i, j) 'そのセルのデータをＳｈｅｅｔ２のＡ列へセット
k = k + 1 '次は次行へセットする準備
End If
Next j
p1:
Next i
End Sub
ーーー
結果　Ｓｈｅｅｔ２　のＡ列に
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
p
q
r
s

お礼

非常に丁寧にありがとうございます。

回答No.3

bunjii

回答No.2の数式の変更方法で次の部分は範囲の指定をシフトさせる必要があることを漏らしました。（処理対象は29列×80行とする）
(ROW($A$1:$E$2)*100+COLUMN($A$1:$E$2))
　　　　↓
(ROW($A$1:$AC$80)*100+COLUMN($A$1:$AC$80))

お礼

つたない説明なのに相当詳しく解説してくださりありがとうございます。
$A$1:$E$2⇒$B$4:$AD$83に変更してセルＡ１に式を埋め込みオートフィルでやってみたのですが、空白ができるだけでした。

せっかく教えて頂けたのに自分の理解不足でできなくて申し訳ないです。
可能であれば、引き続きよろしくお願い致します。

回答No.2

bunjii

>ただ、状況として範囲内の空白には書式（関数）が入っている状態です。
「書式（関数）」は「数式」の誤りではないでしょうか？
用語を誤ると処理方法を考え難くなります。
また、空白と提示されているセルは文字列の"空白"ですか？、それとも文字数が0の""でしょうか？

提示された模擬データのA1:E2の"空白"を""に置き換えてExcel 2013で検証してみました。
F1セルに次の数式を入力して下へオートフィルコピーすると貼付画像のようになります。
=IFERROR(INDEX($A$1:$E$2,SMALL(INDEX(($A$1:$E$2<>"")*(ROW($A$1:$E$2)*100+COLUMN($A$1:$E$2))+($A$1:$E$2="")*10000,0,0),ROW())/100,MOD(SMALL(INDEX(($A$1:$E$2<>"")*(ROW($A$1:$E$2)*100+COLUMN($A$1:$E$2))+($A$1:$E$2="")*10000,0,0),ROW()),100)),"")
実際のデータに合わせて数式を修正してみると良いでしょう。

>範囲はB4～AD８３の間です。
その範囲では数式をA1セルから下へ設定しないと数式とデータ範囲が重なるため不都合となります。
また、数式中の$A$1:$E$2を$B$4:$AD$83に置き換える必要があります。

お礼

ご丁寧にありがとうございました。