excelで文字列を部分抽出→別セルへ反映させたい

質問

初めて質問します。

３００００件のデータを処理しなくてはならず、手動でやっていたら、日が暮れてしまいました。

エクセルの質問箱をたくさん読んでみて、似たような質問の回答から同じようにやってみましたが、

よくできなくて大変困っています。

図のように、
漢字データの列のデータが含まれるものを、ひらがな＋漢字の列の中から探し、
その値をB列に入れていきたいです。

A　　　　　B　　　　　　　　　　　　　　　　C 漢字データ　　　　　　D ひらがな＋空白+漢字

1　　　　　びょういん 病院　　　　　　　　病院　　　　　　　　 　　きゅうぎじょう　球技場
2　　　　　　　　　　　　　　　　　　　　　　 学校　　　　　　　　 　がっこう　学校
3　　　　　　　　　　　　　　　　　　　　　　 球技場　　　　　　　えきしゃ　駅舎
4　　　　　　　　　　　　　　　　　　　　　　 駅舎 　　　じむしょ　事務所
5　　　　　　　　　　　　　　　　　　　　　　　事務所　　　　　　　　　　がっこうのじむしょ　学校の事務所

C列にもD列にもそれぞれ重複するデータがあり、C列は絶対に重複データを消すことができません。
また、重複ではないけれども同じ文字列を含むセルもC列、D列それぞれにたくさん存在します。

（しかし、それらについては、最悪すべて手動でもいいと思っています。）

手動でやっていると一日５００件もできません(泣）

どうか、いい方法を教えていただければうれしいです。

よろしくお願いします。

回答No.2

kagakusuki · ベストアンサー

確認したいのですが、「ひらがな＋空白+漢字」の欄の空白は必ず全角文字の空白になっていると考えて宜しいのでしょうか？
　それと、例えば「吟醸酒　万歳亀」の様に、漢字の部分の途中に空欄が挟まっているものはないと考えて宜しいのでしょうか？
　もし、上記2つの条件が満たされているのでしたら、次の様な方法があります。

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

=IF(COUNTIF($E:$E,"*　"&$C2),VLOOKUP("*　"&$C2,$E:$E,1,FALSE),"")

或いは

=IF(COUNTIF($E:$E,"*　"&INDEX($C:$C,ROW())),VLOOKUP("*　"&INDEX($C:$C,ROW()),$E:$E,1,FALSE),"")

　次に、B2セルをコピーして、B3以下に（リスト中の最も下の行にまで）貼り付けて下さい。

　以上です。

　因みに、2つ目に挙げた関数は、1つ目に挙げた関数を改良して、C列やE列のセルに対して、セルの切り取り、挿入、削除等の、セルの位置関係がずれてしまう編集作業を行った場合でも、正しい結果が表示される様にしたものです。

　尚、もしも、、「ひらがな＋空白+漢字」の欄の空白が、半角の空白の場合と、全角の空白の場合の両方の場合がある場合には、対応する関数を別途、回答致しますので、補足欄等を使用して御知らせ願います。

お礼

さっそくのご回答ありがとうございます！
なんだか解決の兆しがみえてきました・・！！
空白に関しては、半角全角はすべて、置換可能です。現在はすべて半角の空白にしてあります。

>>「ひらがな＋空白+漢字」の欄の中に、漢字の部分は同じで、ひらがなの部分は異なっている文字列が存在した場合には、B列に表示させるのは、上から数えて最初に現れた文字列のみと考えれば宜しいのでしょうか

...> この場合は、一番上に現れた文字列が現れてしまうことは問題ありませんが、そのことがわかるのが一番いいです。というのも、もし、そのような文字列が存在する場合は、「修正が必要なセル」ですので最終的にはすべて目で確認しなければならないからです。

回答No.4

kagakusuki

ANo.2です。

>空白に関しては、半角全角はすべて、置換可能です。現在はすべて半角の空白にしてあります。

　そうでしたか、御質問文中における例では、全角の空白が入っているものの方が多かったため、ANo.2では全角文字の空白に対応する様な関数としていたのですが、半角の空白に対応させるのでしたら、ANo.2で挙げましたB2セルに入力する関数中の全角の空白となっている箇所を、全て半角の空白に置換されて、

=IF(COUNTIF($E:$E,"* "&$C2),VLOOKUP("* "&$C2,$E:$E,1,FALSE),"")

或いは、

=IF(COUNTIF($E:$E,"* "&INDEX($C:$C,ROW())),VLOOKUP("* "&INDEX($C:$C,ROW()),$E:$E,1,FALSE),"")

として下さい。

>この場合は、一番上に現れた文字列が現れてしまうことは問題ありませんが、そのことがわかるのが一番いいです。

　それでしたら、例えばF2セルに次の関数を入力してから、F2セルをコピーして、F3以下に貼り付けて下さい。

=IF(ISNUMBER(1/($E2<>VLOOKUP("* "&REPLACE($E2,1,FIND(" ",$E2),),$E:$E,1,FALSE))),SUBSTITUTE(CELL("address",INDEX($E:$E,MATCH("* "&REPLACE($E2,1,FIND(" ",$E2),),$E:$E,0))),"$",)&":"&VLOOKUP("* "&REPLACE($E2,1,FIND(" ",$E2),),$E:$E,1,FALSE),IF($E2="","",IF(COUNTIF($E2,"*? ?*"),"",IF(COUNTIF($E2,"*?　?*"),"空白が全角文字です","文字列の形式が誤っています"))))

　尚、上記の関数において、

COUNTIF($E2,"*?　?*"),"空白が全角文字です"

という箇所の中の「*?　?*」という部分における「?」で挟まれている空白のみは全角文字の空白で、その他の箇所における空白は、

COUNTIF($E2,"*? ?*"),"",

という箇所のものも含めて、全て半角文字の空白ですから、間違えないよう注意なさって下さい。
　それと、この関数はE列に入力されている文字列のみをチェックするものであり、C列の文字列に関してはチェックする様にはなっておりません。
　E列において、もし、空白以降の漢字の部分が同じ熟語になっている文字列が複数ある場合、上から数えて最初に現れた文字列を基準として、「漢字部分が共通の文字列の中で、最初に現れた文字列」と、ひらがなの部分が異なっているものがありますと、その「ひらがなの部分が異なっている文字列」が入力されている行のF列のセルに、「漢字部分が共通の文字列の中で、最初に現れた文字列」が入力されているセルのセル番号と、「漢字部分が共通の文字列の中で、最初に現れた文字列」が表示されます。
　例えば、E3セルに

かっこう 学校

と入力されていて、E9セルに

がつこう 学校

と入力されていて、F12セルには

がっこう 学校

と入力されている場合には、F9セルとF12セルには、共に

E3:かっこう 学校

と表示されます。
　又、E列の文字列が、「ひらがな＋半角の空白＋漢字」の形式（正確には「文字列＋半角の空白＋文字列」の形式）となっていない場合には、「文字列の形式が誤っています」と表示されます。
　尤も、ひらがなの部分に漢字や記号、英数字等が入力されていたり、漢字の部分にひらがなやカタカナ、記号、英数字等が入っていたりしていましても、この関数では検出する事は出来ませんので注意して下さい。（文字列がひらがなのみから成ってはいない事を検出する関数に関しては、計算処理に時間が掛かりそうなものしか、今の処は思い付く事が出来ませんでした）
　それから、

>C列にもD列にもそれぞれ重複するデータがあり
>C列は絶対に重複データを消すことができません。

という事でしたので、若しかすると、E列の重複箇所も削除する訳にはいかないのかもしれないと考えまして、F2セルに入力する関数は、ひらがな部分も含めて完全に一致している重複に関しては、検出は行うようにはなっておりません。

回答No.3

kagakusuki

もう2点、確認したい事項がありました。

●もし、「ひらがな＋空白+漢字」の欄の空白が、半角の空白の場合と、全角の空白の場合の両方の場合がある場合に、Excelの置換機能を使用して、例えば、半角の空白の全てを全角の空白に置き換えてしまう訳にはいかないのでしょうか？

●

>C列にもD列にもそれぞれ重複するデータがあり

とありますが、「ひらがな＋空白+漢字」の欄の中に、漢字の部分は同じで、ひらがなの部分は異なっている文字列が存在した場合には、B列に表示させるのは、上から数えて最初に現れた文字列のみと考えれば宜しいのでしょうか？

お礼

あぁ！！できました！！涙が出るほどうれしいです！！
さっきまで途方に暮れていたので、、、。
全くもってそのままコピペしただけですが、うまくできました。
本当に本当にありがとうございました。（涙）

回答No.1

tom04

こんばんは！
VBAになってしまいますが、一例です。

質問内では「学校」を含む文字列がE列で重複していますね？
その場合は最初にヒットした（上位の行）のデータになってしまいます。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i As Long
Dim k As Long
Application.ScreenUpdating = False
For i = 2 To Cells(Rows.Count, 3).End(xlUp).Row
If WorksheetFunction.CountIf(Columns(5), "*" & Cells(i, 3) & "*") Then
k = WorksheetFunction.Match("*" & Cells(i, 3) & "*", Columns(5), False)
Cells(i, 2) = Cells(k, 5)
End If
Next i
Application.ScreenUpdating = True
MsgBox "処理が完了しました。"
End Sub 'この行まで

※　データが30000行程度あるというコトなので、若干時間がかかるかもしれません。
お役にたちますかね？m(_ _)m

お礼

早速のご回答ありがとうございます。
なにせ、VBAは何もわからず、、、とりあえず、おっしゃるとおりにやってみましたが、応用がまったくきかないもので、、、、。
ですが、すばやいご回答大変ありがとうございました。