文字列データの振り分け、エクセル化

質問

以下のように項目名が揃っておらず、そのままでは表計算ソフトになじまないような配列のテキストデータがあります。これを一人分のデータを一列に項目名ごとに配列したデータ（エクセルないしCSV）にしたいのですが、どのような手順を踏むのが最善でしょうか。

更新日　2008/9/18
氏名　両津カンキチ
所属機関　亀有公園前派出所
更新日　2008/9/18
氏名　大空翼
生年月日　1900/1/1
所属機関　ナンカツ小
キーワード　サッカー
（以下略、約5000人分）

qa3589110.htmlに類似の質問がありましたが、ピボットではデータ欄で文字列が扱えず、ほかの対処法が思いつきませんでした。Excelは2002を使用しております。どうぞ宜しくお願いいたします。

回答No.5

noname#70958 · ベストアンサー

＞項目を網羅したものを拾うところまではできます。
ということなので、それは省略します。

以下、元のデータが【A2セル以下】にあるとします。
※１行目は空けておきます

１．項目名の切り出し
　B2：　=LEFT(A2,FIND(" ",SUBSTITUTE(A2,"　"," "))-1)

２．更新日の行でカウントアップして誰のデータかナンバリング
　C2：　=C1+(B2="更新日")

３．ナンバーと項目名から一意なキーを生成
　D2：　=C2&"_"&B2

４．データ部分の切り出し
　E2：　=TRIM(SUBSTITUTE(A2,B2,""))

５．B2～E2を下方にフィル

６．F1,G1,H1,……に項目名を書き出す（手打ち）

７．行番号と項目名に応じてデータをルックアップ
　F2：　=VLOOKUP((ROW()-1)&"_"&F$1,$D:$E,2,0)

８．F2を右方・下方にフィル

以上ご参考まで。

お礼

Kater_Kurz様、この上ないアドバイスをいただき感謝申し上げます。目からうろこが落ちる思いがいたしました。ご教示いただいた方法で作業をしたいと思います。ありがとうございました。

回答No.4

imogasi

#3です。＃３のお礼に関して
＞御記載いただいた結果でも、両津の更新
日と氏名がエラー、・・・
私はテスト例ではうまく行きました。回答では、そのシートの内容をコピーして載せています。
＄の有無、＄のつける場所など中心にチェックしてみてください。
考えを理解して、修正してもらえれば幸いです。
考え方は、各列の該当分に上から連番をフリ、その連番とＳｈｅｅｔ２の行番号を関連づけているのが特徴です。
あったりなかったりする項目は、その行までの名前の数で、所属番号としています。

お礼

imogasi様、お忙しいところ補足をいただき、ありがとうございました。ご教示いただいた方法を参考にさせていただこうと存じます。

回答No.3

imogasi

これは関数では難問（ややこしい）と思う。
ーー
ＶＢＡ向きの課題と思う。
プログラムが組めれば、項目ＩＤ（例　氏名、キーワードなど）によって、所定列に振り分けれは済む。ただし、どの人の項目か注意が必要。人によって、データのない項目があるようだから。
ーーー
関数で考えてみた。複雑なので、付いてこれるかな。また件数も多いようなので、関数設定数セル数も多くなるので、エクセルが動かないといった事態もあるかもしれない。
ーー
氏名はデータの１人分の塊の中で、必ずあるものと仮定。
下記で説明している関数式は、９９行までの例にしている。５０００人なら範囲の終わりを５０００ｘ項目数予想数に、そのように修正すること。
ーー
（１）エクセルにデータを読み込み（人手でエクセル操作）
Ａ列のデータになる。
（２）姓と名の間にスペースがあれば、
姓＋スペース＝＝＞姓＋特殊記号（％など）で置換する。質問例では
姓と名は詰まっているようだ。なので下記説明では省略。置き替えた場合は、作業の終了後、氏名列で％－－＞スペースに置き換えること。下記（３）で姓と名が別列になり、困るのでこういうことをする。
（３）データー区切り位置、の操作で、項目名とデータに分離
（人手でエクセル操作）
Ａ列＋Ｂ列のデータになる。
（４）項目名について、何番目の人のデータなのか、割り出し番号化
（関数式作成）
（５）Ｓｈｅｅｔ２で（４）の番号を元に、項目を横方向に考え、
データをＳｈｅｅｔ１のＢ列から組み替え。（関数式作成）
ーー
例データ
下記は（３）を終わった状態から説明
当初はＡ列＋Ｂ列にデータあり。
Ｅ－Ｊ列は下記で説明する式で出来た結果（を先回り表示した部分）
更新日 氏名 生年月日 所属機関 更新日 キーワード
更新日 2008/9/18 1 1
氏名 両津カンキチ 1
所属機関 亀有公園前派出所 1
更新日 2008/9/18 2 2
氏名 大空翼 2
生年月日 1900/1/1 1
所属機関 ナンカツ小 2
キーワード サッカー 2
更新日 2008/10/18 3 3
氏名 三浦健 3
更新日 2008/10/18 4 4
氏名 三浦勇 4
所属機関 ナンカツ小 4
キーワード 空手 4
ーーーーーー
Ｅ１：Ｊ１に
氏名 生年月日 所属機関 更新日 キーワード
を入力しておくこと（式の中で参照しているので必須）。更新日を最初にしてないのは、氏名を主にしたことから来る事情によるもの。
Ｅ２の式
=IF(MID($A2,1,LEN(E$1))=E$1,MAX(E$1:E1)+1,"")
下方向に式を複写。
結果
上記Ｅ列
Ｆ２の式
=IF(MID($A2,1,LEN(F$1))=F$1,COUNTIF($A$2:$A2,"氏名"),"")
この式を下方向に式を複写。
Ｇ２の式
=IF(MID($A2,1,LEN(G$1))=G$1,COUNTIF($A$2:$A2,"氏名"),"")
Ｈ２の式
=IF(MID($A2,1,LEN(H$1))=H$1,COUNTIF($A$2:$A3,"氏名"),"")
Ｉ２の式
=IF(MID($A2,1,LEN(I$1))=I$1,COUNTIF($A$2:$A3,"氏名"),"")
それぞれ下方向に式を複写。
結果
上表のとおり。
ーーーーーーーーーーーー
Ｓｈｅｅｔ２に行って
Ａ１：Ｅ１に
更新日 氏名 生年月日 所属機関 キーワード
を入れる。項目見出し。
Ａ２に
=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$I$1:$I$100,0),2)
Ｂ２に
=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$F$1:$F$100,0),2)
Ｃ２に
=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$G$1:$G$100,0),2)
Ｄ２に
=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$J$1:$J$100,0),2)
Ｅ２に
=INDEX(Sheet1!$A$1:$B$100,MATCH(ROW()-1,Sheet1!$I$1:$I$100,0),2)
ト入れて下方向に式を複写。
結果
更新日 氏名 生年月日 所属機関 キーワード
#N/A #N/A 亀有公園前派出所 #N/A #N/A
サッカー 1900/1/1 ナンカツ小 2008/9/18 サッカー
#N/A #N/A #N/A 2008/10/18 #N/A
空手 #N/A ナンカツ小 2008/10/18 空手
#N/A #N/A #N/A #N/A #N/A
ーー
＃Ｎ/Ａを出さないようにするには、長くなるので説明を省くが、
下方向のものはＧｏｏｇｌｅでimogasi方式で照会すると出てくる
例の中に説明している。
途中の存在しない項目の＃Ｎ/ＡはＩＳＥＲＲＯＲなどで、エラーを捉え、ＩＦ文で判別し、エラーの場合は、空白（""）でおき変える。

お礼

imogasi様、たいへん参考になりました。ありがとうございます。
(5)のindex+match関数についてはこれから勉強したいとおもいますが、とりあえずアドバイスの通り試行してみました。
得られた結果（sheet2）は項目とデータが合致していなかったり、データがあるのにエラー値が出てしまうようです。（御記載いただいた結果でも、両津の更新日と氏名がエラー、翼の更新日欄にキーワード、氏名欄に生年月日、生年月日欄に所属、所属欄に更新日データが反映されているように見えます）。index関数について勉強して修正ができるか考えてみます。
エラー値の非表示についてもアドバイスいただきありがとうございます。とりあえずこのままで問題ありませんが、必要に応じて、ご指示の通り対処したいとおもいます。
詳しい方にご回答いただき大変助かります。心より御礼申し上げます。

回答No.2

n-jun

項目には一貫性がないのでしょうか？

あとテキストデータの区切りは半角スペース？全角スペース？

お礼

ありがとうございます。項目は一貫していませんが、共通項目もあるので、項目を網羅したものを拾うところまではできます。区切りには規則性はかならずしもありませんが、半角スペースの箇所が多いようなので、項目名／データ間に区切りを入れることに関しては対応できます。

回答No.1

ocean-ban

ここが参考になると思います。

http://www.f3.dion.ne.jp/~element/msaccess/AcTipsCSV001.html

http://www.morgan.co.jp/kiso/3file-4.htm

お礼

アドバイスありがとうございます。ただ今回の難点はCSV⇔Excelの互換性にではなく、項目のずれにあります。