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

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

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

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

No.5 ベストアンサー 回答者： noname#70958 回答日時： 2008/09/20 02:01

＞項目を網羅したものを拾うところまではできます。

ということなので、それは省略します。

以下、元のデータが【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様、この上ないアドバイスをいただき感謝申し上げます。目からうろこが落ちる思いがいたしました。ご教示いただいた方法で作業をしたいと思います。ありがとうございました。

お礼日時：2008/09/20 14:58

No.4 回答者： imogasi 回答日時： 2008/09/19 22:59

#3です。

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

この回答へのお礼

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

お礼日時：2008/09/20 15:00

No.3 回答者： imogasi 回答日時： 2008/09/19 10:32

これは関数では難問（ややこしい）と思う。

ーー
ＶＢＡ向きの課題と思う。
プログラムが組めれば、項目ＩＤ（例　氏名、キーワードなど）によって、所定列に振り分けれは済む。ただし、どの人の項目か注意が必要。人によって、データのない項目があるようだから。
ーーー
関数で考えてみた。複雑なので、付いてこれるかな。また件数も多いようなので、関数設定数セル数も多くなるので、エクセルが動かないといった事態もあるかもしれない。
ーー
氏名はデータの１人分の塊の中で、必ずあるものと仮定。
下記で説明している関数式は、９９行までの例にしている。５０００人なら範囲の終わりを５０００ｘ項目数予想数に、そのように修正すること。
ーー
（１）エクセルにデータを読み込み（人手でエクセル操作）
Ａ列のデータになる。
（２）姓と名の間にスペースがあれば、
姓＋スペース＝＝＞姓＋特殊記号（％など）で置換する。質問例では
姓と名は詰まっているようだ。なので下記説明では省略。置き替えた場合は、作業の終了後、氏名列で％－－＞スペースに置き換えること。下記（３）で姓と名が別列になり、困るのでこういうことをする。
（３）データー区切り位置、の操作で、項目名とデータに分離
（人手でエクセル操作）
Ａ列＋Ｂ列のデータになる。
（４）項目名について、何番目の人のデータなのか、割り出し番号化
（関数式作成）
（５）Ｓｈｅｅｔ２で（４）の番号を元に、項目を横方向に考え、
データをＳｈｅｅｔ１のＢ列から組み替え。（関数式作成）
ーー
例データ
下記は（３）を終わった状態から説明
当初はＡ列＋Ｂ列にデータあり。
Ｅ－Ｊ列は下記で説明する式で出来た結果（を先回り表示した部分）
更新日氏名生年月日所属機関更新日キーワード
更新日2008/9/1811
氏名両津カンキチ1
所属機関亀有公園前派出所1
更新日2008/9/1822
氏名大空翼2
生年月日1900/1/11
所属機関ナンカツ小2
キーワードサッカー2
更新日2008/10/1833
氏名三浦健3
更新日2008/10/1844
氏名三浦勇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/A2008/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関数について勉強して修正ができるか考えてみます。
エラー値の非表示についてもアドバイスいただきありがとうございます。とりあえずこのままで問題ありませんが、必要に応じて、ご指示の通り対処したいとおもいます。
詳しい方にご回答いただき大変助かります。心より御礼申し上げます。

お礼日時：2008/09/19 21:51

No.2 回答者： n-jun 回答日時： 2008/09/19 09:39

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

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

この回答へのお礼

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

お礼日時：2008/09/19 20:56

No.1 回答者： ocean-ban 回答日時： 2008/09/19 07:48

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

http://www.f3.dion.ne.jp/~element/msaccess/AcTip …

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

この回答へのお礼

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

お礼日時：2008/09/19 20:51