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

Question

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

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

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

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

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

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

tom04 · Accepted Answer

こんばんは！
一例です。

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

kagakusuki · Answer

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

kagakusuki · Answer

ANo.3です。

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

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

tom04 · Answer

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

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

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

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

CoalTar · Answer

関数自体は難しくないけど、考え方が難しい。ただ、改良は簡単なので。
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：見つかった文字の数
"ァアィイゥウェエォオカガキギクグケゲコゴサザシジスズセゼソゾタダチヂッツヅテデトドナニヌネノハバパヒビピフブプヘベペホボポマミムメモャヤュユョヨラリルレロヮワヰヱヲンヴＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ"
を入れ替えればひらがなにも対応できると思います。

kagakusuki · Answer

　今仮に、元データの表が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行目以下に貼り付けて下さい。

　以上です。

KURUMITO · Answer

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

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

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

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

こんばんは！

ANo.3です。

ANo.3です。

No.1です！

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

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

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

　ANo.3です。

　ANo.3です。