【エクセル】縦データを横に並べ変えたい

質問

1 --------------
2 aaaa
3 bbb
4 ccc
5 --------------
6 efefef
7 ggg
8 --------------
9 123456
10 mmm
11 nknknk
・
・
上記のように縦一列に並んでいるデータを、特定文字から折り返して
下記のように横に配列したいのですが、どのようにすればいいのでしょうか？

A B C
aaaa efefef 123456
bbb ggg mmm
ccc nknknk

ご教授のほど、よろしくお願い致します。

回答No.3

noname#204879 · ベストアンサー

Sheet1 において、
１．セル B1 に次式を入力して、此れを下方にズズーッとドラッグ＆ペースト
　　=IF(OR(LEFT(A1,5)="-----",A1=""),ROW(),"")
　　(その結果を添付図の左側に示す)

Sheet2 において、
２．セル A1 に式 =SMALL(Sheet1!$B:$B,COLUMN()) を入力
３．セル A2 に次式を入力
　　=IF(ROW(A1)<B$1-A$1,INDEX(Sheet1!$A:$A,A$1+ROW(A1)),"")
４．範囲 A1:A2 を右方にズズーッとドラッグ＆ペースト
５．２行目(添付図では範囲 A2:F2)を選択して、此れを下方にズズーッとドラッグ＆ペースト
　　(その結果を添付図の右上段に示す)
６．シート全体を選択して、[コピー]⇒[値の貼り付け]を実行
７．１行目を削除
　　(その結果を添付図の右下段に示す)

お礼

ありがとうございました。
大変助かりました。

補足

ご回答ありがとうございます。

試してみたところ、Sheet2の2と3の式を入力すると#NAME?が出て機能していません。

こちらのエクセルverが2003だからでしょうか・・・

回答No.7

noname#192382

すでに回答されていますが、わたしもやっとできるようになりましたので、回答させてください。
シート1に下のデータをおいて、下のマクロを実行するとシート2に答えが出てきます。
Sub Macro1()
'
' Macro1 Macro
' マクロ記録日 : 2014/2/4 ユーザー名 :
'
Dim st1gyo As Integer
Dim st2gyo As Integer
Dim mygyo As Integer
Dim mycol As Integer
mycol = 1

For st1gyo = 1 To Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

st2gyo = 1

mycol = mycol + 1

If Cells(st1gyo, 2) = "zzz" Then
st1gyo = st1gyo + 1
st2gyo = st2gyo + 1

If Cells(st1gyo, 2) <> "zzz" Then
Worksheets(2).Cells(st2gyo, mycol) = Worksheets(1).Cells(st1gyo, 2)

st1gyo = st1gyo + 1
st2gyo = st2gyo + 1
If Cells(st1gyo, 2) <> "zzz" Then
Worksheets(2).Cells(st2gyo, mycol) = Worksheets(1).Cells(st1gyo, 2)

st1gyo = st1gyo + 1
st2gyo = st2gyo + 1

If Cells(st1gyo, 2) <> "zzz" Then
Worksheets(2).Cells(st2gyo, mycol) = Worksheets(1).Cells(st1gyo, 2)

st1gyo = st1gyo + 1
st2gyo = st2gyo + 1

If Cells(st1gyo, 2) <> "zzz" Then
Worksheets(2).Cells(st2gyo, mycol) = Worksheets(1).Cells(st1gyo, 2)

st1gyo = st1gyo + 1
st2gyo = st2gyo + 1

If Cells(st1gyo, 2) <> "zzz" Then
Worksheets(2).Cells(st2gyo, mycol) = Worksheets(1).Cells(st1gyo, 2)

Else

st1gyo = st1gyo - 1
mycol = mycol + 1
End If
End If
End If
End If
End If
End If
st1gyo = st1gyo - 1
Next

End Sub

お礼

ご回答ありがとうございました。

マクロでもできるのですね。参考とさせていただきます。

回答No.6

kagakusuki

回答No.2です。

>試してみたところ、Sheet3のA2セルに式を入力すると#NAME?が表示されて機能しないようです。

>こちらのエクセルverが2003だからでしょうか。。

　#NAME?エラーは、数式の中で、そのExcelで使う事が出来る関数以外の関数名が使用されていたり、名前（セルやセル範囲に定義した名前）の指定が正しくない場合において表示されるエラーです。
　しかしながら、回答No.2ではセル或いはセル範囲を指定する際に、名前定義などは使用しておりませんし、使用している関数も、組み合わせてはいるものの、その中に含まれている個々の関数自体は、以前私がExcel2002を使用していた際にも、使用していた標準的な関数ばかりで、アドインが必要になる様な関数なども使用しておりませんから、後述のR1C1形式を使用しているのでもない限りは、もしエラーが出るとしましても、#NAME?エラーだけは出ない筈です。（回答No.2の添付画像にもあります様に、Excel2010ではありますが、動作確認は済んでおりますし、念のために回答No.2に掲載されている関数をコピーして、新規のExcelbookに貼り付けた際にも正常に動作致しましたから、回答No.2に掲載されている関数の内容自体には、誤りは無いと考えられます）

　ですから、考えられる原因としましては、

●質問者様が御使いになられているExcelにおいて、「ツール」の「オプション」の「全般」設定において、「R1C1参照形式を使用する」という設定になっている

●質問者様が式を入力された際に、どこかを間違えて入力しておられる

の2つくらいのものだと思います。

　前者に関しては、列番号の表示が、A、B、Cといったアルファベットではなく、1、2、3といった数字表示になっているかどうかで確認する事が出来ますから、もし、列番号の表示が数字になっている場合には、次のURLのページを参考にして、一旦、R1C1形式を解除してから、回答No.2の関数を入力してみて下さい。（関数を入力後に、再度R1C1形式に戻せば、関数の方も、自動的にR1C1形式に沿う様な形に書き換えられます）

【参考URL】
　日経トレンディネット > デジタル > PC > Excelの列番号が数字で表示されるのはナゼ？
　　http://trendy.nikkeibp.co.jp/article/qa/other/20020819/101538/

　Microsoft Excel A1参照形式とR1C1形式
　　http://www.geocities.jp/laut6/r1c1/r1c1.html

　一方、後者に関しましては、あるとすれば

●質問者様が入力した関数のつづりが、回答No.2で提示させて頂いたものとは異なっている

●関数内におけるどこかの箇所で、「"」、「&」、「(」等が抜けている

等が可能性が高そうですので、再度御確認願います。

お礼

補足情報をありがとうございます。

昨日のエラーは#NAME?ではなく、#N/Aでした。申し訳ございません。。
このエラーは、Sheet1のA1に「元データ」のような見出しをつけることで解決できました。

また、Sheet3に#N/Aが出たままでもSheet2に式をいれると求めている情報が表示されていました。

結論として昨日ご回答いただいたままで問題ありませんでした。

大変失礼いたしました。

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

回答No.5

bunjii

>こちらのエクセルverが2003だからでしょうか。。
IFERROR関数を外して動作を確認してください。

=OFFSET(INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW($A$1:$A$20),""),COLUMNS($A$1:A$1)),1,1)),IF(MATCH($A$1,INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW(A$1:A$20),""),COLUMNS($A$1:A$1))+1,1,1)):$A$100,0)>ROW(),ROW(),""),0)

正しく処理できているセルとエラーが表示されるセルがあると思います。

次の式を試してください。

=IF(MATCH($A$1,INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW(A$1:A$20),""),COLUMNS($A$1:A$1))+1,1,1)):$A$100,0)>ROW(),OFFSET(INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW($A$1:$A$20),""),COLUMNS($A$1:A$1)),1,1)),IF(MATCH($A$1,INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW(A$1:A$20),""),COLUMNS($A$1:A$1))+1,1,1)):$A$100,0)>ROW(),ROW(),""),0),"")

IF関数の論理式（次の式）がエラーの原因と思います。

MATCH($A$1,INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW(A$1:A$20),""),COLUMNS($A$1:A$1))+1,1,1)):$A$100,0)>ROW()

Office 2003が近い将来にサポート終了になりますので、一番安価なOffice 2010のアップグレード優待版で対応されると良いでしょう。

お礼

補足のご連絡をありがとうございます。
ご指摘の箇所でエラーが出ています。
バージョンによって利用できる関数が異なることを知りませんでしたので、今後はアップグレード検討したいと思います。

回答No.4

noname#204879

[No.3補足]へのコメント、
》 こちらのエクセルverが2003だからでしょうか
そんなの関係ない！
貴方の入力した式中の文字が間違ってます。キッパリ！
私が提示した式をコピーしてみてください。

ちなみに、「#NAME?」の意味は文字どおり「そんな名前？無いよ」と言っているのです。
試しに、何処かのセルに式 =aja_1977 とか =Mike_g とかを入力してみたら、どうなりますか？#NAME? が返るはずです。
も一つちなみに、何処かのセル(別シートでも構わない)に「12345」と入力して、そのセルに aja_1977 という名前を付けたら([挿入]⇒[名前]⇒[定義]で)、式 =aja_1977 は アラ不思議 12345 を返します。

お礼

データの中に"- "(ハイフンの後に半角スペース)があり、これが結果に#NAME?を誘発しておりました。

この部分の半角スペースを削除したところ、きちんと機能し表示されました。

大変たすかりました。
ありがとうございました。

回答No.2

kagakusuki

今仮に、御質問の縦一列に並んでいるデータが、Sheet1のA2以下に入力されていて、並べ替えた結果をSheet2に表示させるものとします。
　又、Sheet3のA列を作業列として使用するものとします。

　まず、Sheet3のA2セルに次の関数を入力して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(INDEX(Sheet1!$A:$A,ROW())="--------------","R0C"&COUNTIF(A$1:A1,"R0C*")+1,"R"&ROWS($1:2)-MATCH("R0C"&COUNTIF(A$1:A1,"R0C*"),A$1:A1,0)&"C"&COUNTIF(A$1:A1,"R0C*")))

　次に、Sheet3のA2セルをコピーして、Sheet3のA3以下に貼り付けて下さい。

　次に、Sheet2のA2セルに次の関数を入力して下さい。

=IF(COUNTIF(Sheet3!$A:$A,"R"&ROWS($A$2:A2)&"C"&COLUMNS($A$2:A2)),INDEX(Sheet1!$A:$A,MATCH("R"&ROWS($A$2:A2)&"C"&COLUMNS($A$2:A2),Sheet3!$A:$A,0)),"")

　次に、Sheet2のA2セルをコピーして、上記の関数が入力されている列の数が、Sheet1のA列において「--------------」が入力されているセルの個数を上回るのに十分な列数となるまで右方向に貼り付けて行って下さい。
　次に、Sheet2の2行目全体をまとめてコピーして、3行目以下に必要な行数となるまで貼り付けて下さい。

　以上です。

　尚、もしも、「--------------」の部分の中に含まれている「-」の個数が一定ではない場合には、Sheet3のA2セルに入力する関数を次の様なものに変更して下さい。

=IF(INDEX(Sheet1!$A:$A,ROW())="","",IF(AND(COUNTIF(INDEX(Sheet1!$A:$A,ROW()),"-*-"),SUBSTITUTE(INDEX(Sheet1!$A:$A,ROW()),"-",)=""),"R0C"&COUNTIF(C$1:C1,"R0C*")+1,"R"&ROWS($1:2)-MATCH("R0C"&COUNTIF(C$1:C1,"R0C*"),C$1:C1,0)&"C"&COUNTIF(C$1:C1,"R0C*")))

補足

ご回答ありがとうございます。

試してみたところ、Sheet3のA2セルに式を入力すると#NAME?が表示されて機能しないようです。

こちらのエクセルverが2003だからでしょうか。。

回答No.1

bunjii

>上記のように縦一列に並んでいるデータを、特定文字から折り返して下記のように横に配列したいのですが、どのようにすればいいのでしょうか？
データの最後にも仕切り用の文字列セルを設定してある場合は可能です。
下記の式で提示されたデータの範囲では検証できました。

=IFERROR(OFFSET(INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW($A$1:$A$20),""),COLUMNS($A$1:A$1)),1,1)),IF(MATCH($A$1,INDIRECT(ADDRESS(SMALL(IF($A$1:$A$20=$A$1,ROW(A$1:A$20),""),COLUMNS($A$1:A$1))+1,1,1)):$A$100,0)>ROW(),ROW(),""),0),"")

この式は入れ子の関数で配列値を扱いますので数式バーに入力後、Ctrl+Shift+Enterで確定してください。
この式を右方向と下方向に必要数だけコピーすれば目的に適うものと思います。
Excel 2013で検証していますので、Excel 2003以前では対応していない関数も含んでいます。

補足

ご回答ありがとうございます。

ご指摘のようにエクセルが2003と古いので関数が認識されないようです。

すみません。。