Excelでカタカナ・ひらがな・英数字の抽出

Excelでデータの中から文字種類別にデータを抽出す方法を教えてほしいです。

（例）
　　　A　　　　B　　　　　　　　　　　　　　　　　C
１　　番号　　書名　　　　　　　　　　　　　　　著作名
２　　１　　　　ソロモンの偽証　第１部　　　宮部みゆき
３　　２　　　　悪の教典　　　　　　　　　　　　貴志祐介
４　　３　　　　犯罪　　　　　　　　　　　　　　　フェルディナント・フォン・シーラッハ／酒寄進一
５　　４　　　　忘れられた花園　　　　　　　　ケイト・モートン／青木純子
６　　５　　　　LOVELESS（ラブレス）　　　　桜木紫乃
７　　６　　　　Harry Potter 　　　　　　　　　Rowling, J. K.

例えば、上のデータからCの列の中からカタカナ（全角／半角）を探し出し方法はありますか？
データ（フェルディナント・フォン・シーラッハ、ケイト・モートン）だけ抜き出すのではなく
どこのセルにデータがあるのか分かるようにしたいです。

　　　A　　　　B　　　　　　　　　　　　　　　　　C
４　　３　　　　犯罪　　　　　　　　　　　　　　 フェルディナント・フォン・シーラッハ／酒寄進一
５　　４　　　　忘れられた花園　　　　　　　 ケイト・モートン／青木純子

のように表示できるようになるのが理想ですが
C４、C５など、データの入っているセルが分かるようになればいいです。

カタカナだけでなく、英語・ひらがな（カタカナ・アルファベットがないもの）でも同様に探せるようにしたいです。
カタカナや英語は全角・半角入り混じっています。
一つセルに全角半角が混在している場合もあります。
B６のようにカタカナや英語が混じっているセルや
C４、C５のようにカタカナと漢字（ひらがな）が混じっているセルもあります。
関数は簡単なのは分かりますが、マクロはさっぱりです。
コードや関数を解説つきで提示していただけると幸いです。

No.1 ベストアンサー 回答者： tom04 回答日時： 2012/08/30 22:52

こんばんは！

一例です。

C列セルに全角カタカナ・半角カタカナ・半角アルファベット・全角アルファベットがある場合
その行を非表示のするVBAの一例です。
データは2行目以降にあるとします。

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

尚、行を再表示させる必要があると思いますので、再表示のVBAも一緒に載せておきます。


Sub 対象行非表示() 'この行から
'変数の宣言（i,k,Mは長整数型・strは文字列型）
Dim i As Long, k As Long, M As Long, str As String

'iは2行目からＡ列の最終行まで（ループ）
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row

'kはC列セルの1文字目から最終文字まで（ループ）
For k = 1 To Len(Cells(i, 3))

'C列セルを一文字ずつ変数（str）に置き換える
str = Mid(Cells(i, 3), k, 1)

'もし strが　全角カタカナ・半角カタカナ・半角アルファベット・全角アルファベットの場合は
If str Like "[ア-ン ア-ン A-z Ａ-ｚ]" Then

'Mを一つずつ増やしていく
M = M + 1
End If

'次の文字へ（セル内の最終文字まで）
Next k

'もしMが1以上の場合（全角カタカナ・半角カタカナ・半角及び全角アルファベットがある場合）
If M > 0 Then

'その行を非表示にする
Rows(i).Hidden = True
End If

'Mの値を「0」に！
M = 0

'次の行へ（最終行まで）
Next i
End Sub

Sub 再表示()
Rows.Hidden = False
End Sub 'この行まで

※　コードをコピー＆ペーストした場合
＞If str Like "[ア-ン ア-ン A-z Ａ-ｚ]" Then
の行の ア-ン　が両方とも全角になっているかもしれませんので、
貼り付け後コードを確認してみてください。
もし両方とも全角になっていたら、どちらかの「ア-ン」のカタカナ部を半角にしてください。

※　まず「対象行非表示」のマクロから実行してみてください。

参考になりますかね？m(_ _)m

この回答へのお礼

返事が遅くなってすみません。

If str Like "[ア-ン ア-ン A-z Ａ-ｚ]" Then

の[]内が非表示になるのですがあっていますか？
これだと

Ｊ・ｋ・ローリングといった場合に

If str Like "[ア-ン ア-ン]" Thenの場合でも
If str Like "[ A-z Ａ-ｚ]" Thenの場合でも
どちらでも表示されなくて抽出できなくなってしまうので
逆に[]内にあてはまるものを表示するようにしたいのですが
そのようにできますか？

お礼日時：2012/09/03 20:39

No.7 回答者： kagakusuki 回答日時： 2012/09/04 04:00

　ANo.3です。

　書き忘れておりましたが、Sheet5のSUMPRODUCT関数は、Sheet1のC列に入力されている文字列に対して、「先頭から順番に1文字ずつ取り出して、英字やカタカナであるのか否かを判定して行く」という処理を、「文字列の文字数」回だけ繰り返す為にSUMPRODUCT関数を使用しているだけであり、元の表の行数や列数が幾つあるのかという事には、全く関係しておりませんので、行数や列数が変わる事に関しては気にされる必要は御座いません。

この回答へのお礼

補足説明ありがとうございます。

お礼日時：2012/09/23 20:25

No.6 回答者： kagakusuki 回答日時： 2012/09/04 03:38

　ANo.3です。

>Ｄ・Ｅ・Ｆ・・・と項目が増える場合はどうすればいいのかしら？

　それは要するに、Sheet1の元データの表が、A列の「番号」、B列の「書名」、C列の「著作名」だけではなく、D列から右側の列にも他のデータが存在しているという事だと考えて宜しいのでしょうか？
　そして、「カタカナ」、「英字」、「漢字/ひらがな」の有無を判定しなければならないのは、C列のセルだけである事には変わりはないと考えて宜しいのでしょうか？
　もし、そうだとしますと、その場合には、関数の内容を修正する必要は何も御座いません。
　単に、Sheet3のSheet2～Sheet4において、A3セルをコピーして、B3～C3セルに貼り付ける際に、貼り付ける範囲をC3セル止まりにするのではなく、D2、E2、F2、G2・・・・・
にまで拡大するだけで宜しいです。

この回答へのお礼

わかりあした。
丁寧な説明ありがとうございます。

お礼日時：2012/09/23 20:24

No.5 回答者： tom04 回答日時： 2012/09/03 21:58

No.1です！

＞[　]内にあてはまるものを表示するようにしたいのですが

というコトは正反対のコードにしてしまった訳ですね！
ごめんなさい。

前回のコード内の
＞If M > 0 Then
を
＞If M = 0 Then
に変更してみてください。

これで前回とは全く逆の結果になると思います。
どうも失礼しました。m(_ _)m

この回答へのお礼

返事が遅くなって済みません。

できました！！
ありがそうございます。

お礼日時：2012/09/23 20:23

No.4 回答者： CoalTar 回答日時： 2012/08/31 14:58

関数自体は難しくないけど、考え方が難しい。

ただ、改良は簡単なので。
D2セルに
=IF(C2="","",COUNT(FIND(MID(UPPER(JIS(C2)),ROW(A$1:INDEX(A:A,LEN(C2))),1),
"ァアィイゥウェエォオカガキギクグケゲコゴサザシジスズセゼソゾタダチヂッツヅテデトドナニヌネノハバパヒビピフブプヘベペホボポマミムメモャヤュユョヨラリルレロヮワヰヱヲンヴＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ")))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式。{ }で挟まれる
下へオートフィル
UPPER：大文字にする
JIS：全角にする
MID：1文字ずつ抜き出す
FIND：1文字ずつ検索
COUNT：見つかった文字の数
"ァアィイゥウェエォオカガキギクグケゲコゴサザシジスズセゼソゾタダチヂッツヅテデトドナニヌネノハバパヒビピフブプヘベペホボポマミムメモャヤュユョヨラリルレロヮワヰヱヲンヴＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ"
を入れ替えればひらがなにも対応できると思います。

この回答へのお礼

回答りがとうございます。
入力は面倒だけれど、
ァアィイゥウェエォオカガキギクグケゲコゴサザシジスズセゼソゾタダチヂッツヅテデトドナニヌネノハバパヒビピフブプヘベペホボポマミムメモャヤュユョヨラリルレロヮワヰヱヲンヴＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ
を入れ替えれば
カタカナのみ
英語のみ
カタカナ英語まじり
フィルターを使ってＯを選択すれば漢字・ひらがなのみのデータも
抽出できますね。

関数の説明も細かくしてくださってわかりやすいです。
ありがとうございます。

お礼日時：2012/09/03 21:13

No.3 回答者： kagakusuki 回答日時： 2012/08/31 12:05

　今仮に、元データの表がSheet1に存在していて、Sheet5のA～C列を作業列として使用して、Sheet2にカタカナを含んだ著者名のデータを抽出した表を、Sheet3に英字を含んだ著者名のデータを抽出した表を、Sheet4にカタカナや英字を含んでいない著者名のデータを抽出した表を、それぞれ表示するものとします。

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

=IF(SUMPRODUCT((CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))>9504)*(CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))<9591)),COUNT(A$1:A1)+1,"")

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

=IF(SUMPRODUCT((CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))>9024)*(CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))<9083)),COUNT(B$1:B1)+1,"")

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

=IF(OR(Sheet1!$C2="",COUNT($A2:$B2)),"",COUNT(C$1:C1)+1)

　次に、Sheet5のA2～C2の範囲をコピーして、同じ列の３行目以下に貼り付けて下さい。

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

=IF(ROWS($3:3)>COUNT(Sheet5!$A:$A),"",INDEX(Sheet1!A:A,MATCH(ROWS($3:3),Sheet5!$A:$A)))

　次に、Sheet2のA3セルをコピーして、Sheet2のB3セルとC3セルに貼り付けて下さい。
　次に、Sheet2のA3～C3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

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

=IF(ROWS($3:3)>COUNT(Sheet5!$B:$B),"",INDEX(Sheet1!A:A,MATCH(ROWS($3:3),Sheet5!$B:$B)))

　次に、Sheet3のA3セルをコピーして、Sheet3のB3セルとC3セルに貼り付けて下さい。
　次に、Sheet3のA3～C3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

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

=IF(ROWS($3:3)>COUNT(Sheet5!$C:$C),"",INDEX(Sheet1!A:A,MATCH(ROWS($3:3),Sheet5!$C:$C)))

　次に、Sheet4のA3セルをコピーして、Sheet4のB3セルとC3セルに貼り付けて下さい。
　次に、Sheet4のA3～C3の範囲をコピーして、同じ列の4行目以下に貼り付けて下さい。

　以上です。

この回答へのお礼

とても見やすい表示ですね！ありがとうございます！！

ただ、実際のデータはもっと項目もデータの数も多いので
私の足りないお頭だとちゃんと応用できるか多大に不安が・・・
Ｄ・Ｅ・Ｆ・・・と項目が増える場合はどうすればいいのかしら？
=IF(SUMPRODUCT((CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))>9024)*(CODE(JIS(MID(Sheet1!$C2&" ",ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$C2&" "))),1)))<9083)),COUNT(☆$1:☆1)+1,"")
の☆の部分をＤ・Ｅ・Ｆってしていけば大丈夫なのかしら？？

お礼日時：2012/09/03 21:00

No.2 回答者： KURUMITO 回答日時： 2012/08/31 11:03

次のようにしてはどうでしょう。

C列で該当すると思われるセルに色を付けて分かるようにします。
初めのC列を選択します。
「ホーム」タブの「条件付き書式」で「新しいルール」から「数式を使用して…」にチェックをし、数式入力の窓には次の式を入力します。

=LENB(ASC(SUBSTITUTE(C1,"・","")))<>LENB(JIS(SUBSTITUTE(C1,"・","")))

この式では著者名が漢字で二名の場合に・で区切られている場合にがその影響が出ないようにしています。
同じ画面の「書式」をクリックして「塗りつぶし」のタブで黄色などを指定してOKします。
これでカタカナや英語などが入った場合にはそのセルに色が付くことになります。

この回答へのお礼

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

Ｂ列でやりたいときは、そのままＢ列で設定すれば大丈夫ですよね？

カタカナ・英語を判別したいだけなら一番分かりやすいですね！
ありがとうございます。

お礼日時：2012/09/03 21:04