ベストアンサー

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

2008/09/19 06:26

以下のように項目名が揃っておらず、そのままでは表計算ソフトになじまないような配列のテキストデータがあります。これを一人分のデータを一列に項目名ごとに配列したデータ（エクセルないしCSV）にしたいのですが、どのような手順を踏むのが最善でしょうか。更新日　2008/9/18 氏名　両津カンキチ所属機関　亀有公園前派出所更新日　2008/9/18 氏名　大空翼生年月日　1900/1/1 所属機関　ナンカツ小キーワード　サッカー（以下略、約5000人分） qa3589110.htmlに類似の質問がありましたが、ピボットではデータ欄で文字列が扱えず、ほかの対処法が思いつきませんでした。Excelは2002を使用しております。どうぞ宜しくお願いいたします。

hegel777
お礼率58% (7/12)

オフィス系ソフト
回答数5
ありがとう数8

みんなの回答 （5）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

noname#70958

2008/09/20 02:01 回答No.5

＞項目を網羅したものを拾うところまではできます。ということなので、それは省略します。以下、元のデータが【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を右方・下方にフィル以上ご参考まで。

質問者

お礼 2008/09/20 14:58

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

ログインすると、全ての回答が全文表示されます。

その他の回答 (4)

imogasi
ベストアンサー率27% (4737/17070)

2008/09/19 22:59 回答No.4

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

質問者

お礼 2008/09/20 15:00

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

ログインすると、全ての回答が全文表示されます。

imogasi
ベストアンサー率27% (4737/17070)

2008/09/19 10:32 回答No.3

これは関数では難問（ややこしい）と思う。ーーＶＢＡ向きの課題と思う。プログラムが組めれば、項目ＩＤ（例　氏名、キーワードなど）によって、所定列に振り分けれは済む。ただし、どの人の項目か注意が必要。人によって、データのない項目があるようだから。ーーー関数で考えてみた。複雑なので、付いてこれるかな。また件数も多いようなので、関数設定数セル数も多くなるので、エクセルが動かないといった事態もあるかもしれない。ーー氏名はデータの１人分の塊の中で、必ずあるものと仮定。下記で説明している関数式は、９９行までの例にしている。５０００人なら範囲の終わりを５０００ｘ項目数予想数に、そのように修正すること。ーー（１）エクセルにデータを読み込み（人手でエクセル操作）Ａ列のデータになる。（２）姓と名の間にスペースがあれば、姓＋スペース＝＝＞姓＋特殊記号（％など）で置換する。質問例では姓と名は詰まっているようだ。なので下記説明では省略。置き替えた場合は、作業の終了後、氏名列で％－－＞スペースに置き換えること。下記（３）で姓と名が別列になり、困るのでこういうことをする。（３）データー区切り位置、の操作で、項目名とデータに分離（人手でエクセル操作）Ａ列＋Ｂ列のデータになる。（４）項目名について、何番目の人のデータなのか、割り出し番号化（関数式作成）（５）Ｓｈｅｅｔ２で（４）の番号を元に、項目を横方向に考え、データをＳｈｅｅｔ１のＢ列から組み替え。（関数式作成）ーー例データ下記は（３）を終わった状態から説明当初はＡ列＋Ｂ列にデータあり。Ｅ－Ｊ列は下記で説明する式で出来た結果（を先回り表示した部分）更新日氏名生年月日所属機関更新日キーワード更新日 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方式で照会すると出てくる例の中に説明している。途中の存在しない項目の＃Ｎ/ＡはＩＳＥＲＲＯＲなどで、エラーを捉え、ＩＦ文で判別し、エラーの場合は、空白（""）でおき変える。

質問者

お礼 2008/09/19 21:51

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

ログインすると、全ての回答が全文表示されます。