エクセル 大文字を小文字に変換

お世話になります。
エクセル2007を使用しています。

セルの同列内に氏名が全て大文字表記で羅列されています。

氏名に含まれる一部の「ヤ」、「ユ」、「ヨ」のみ
大文字から小文字に⇒の後のように変換したいと思います。

A1セル　タナカ　アキラ
A2セル　ヤマダ　シユン⇒ヤマダ　シュン
A3セル　コダマ　ユウジ
A4セル　キシダ　ヨウスケ
A5セル　ヨシダ　トシユキ　
　 ・
　・
　 ・

関数で=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"ヤ","ャ"),"ユ","ュ"),"ヨ","ョ")を
使用(オートフィル)すると、A2は思惑通り変換されますが、A3はコダマ　ユウジ⇒コダマ　ュウジ、
A４はキシダ ョウスケ、A５はョシダ　トシュキとなり問題が生じます。

置換の場合も同様となります。

「ヤ」、「ユ」、「ヨ」のみ名前の読みがおかしくならないよう小文字に
変換を行えればと思うのですが、何か方法はありますでしょうか。

よろしくお願い致します。

No.7 ベストアンサー 回答者： bunjii 回答日時： 2015/01/12 19:02

>おっしゃる通り受注ソフトの拗音を口座名義に合わせる事によりデータ上での照合は取れるのですが、照合したデータを再び受注ソフトにインポートして結果を取り込む為にはシステム上、氏名(金額を除く氏名以外のその他の一部項目含む)が完全一致している必要があります。

入金チェックの考え方を柔軟にしないと目的を達成できません。
受注データテーブルの氏名（拗音込み）と銀行口座名相当の氏名（拗音変換後）を併記して、振込通知のデータを直接照合すれば逆変換は必要ないと思われます。
銀行口座名で同姓同名があるときは自動的に判定できませんので、人力による判定にすべきです。
振込金額と売上金額には振込手数料の負担等の要件で不一致が付き物です。
他に受注日と振込日の整合性等もチェックに使える要素です。（受注日より前に振り込まれることは有り得ない）
間違いを起こさないシステムを構築するには専門のプログラマーに有償で依頼すべきです。
チェックミスによる信用失墜や未収金の損失で事業の運営が困難になることを考えればプログラマーへ支払う費用を捻出できるのではないでしょうか？

この回答へのお礼

先日はありがとうございました。
お礼が遅くなり申し訳ございません。
元々、ある一定の作業までをマクロで行い、入金名義違いや
入金額間違いがある事から目視は必須で考えておりました。
最終的に試行錯誤の上、目的を達する事ができ現在運用をしております。
今後ともよろしくお願い致します。

お礼日時：2015/02/13 12:41

No.8 回答者： MackyNo1 回答日時： 2015/01/13 21:41

>おっしゃる通り受注ソフトの拗音を口座名義に合わせる事によりデータ上での照合は取れるのですが、 照合したデータを再び受注ソフトにインポートして結果を取り込む為にはシステム上、 氏名(金額を除く氏名以外のその他の一部項目含む)が完全一致している必要があります。

その為、最初に受注ソフトよりエクスポートしたデータの拗音を含めた氏名に口座名義を合わせる必要がありました。
Excelの機能の利用の仕方で苦労されているように思われますが、基本的に照合したデータを受注ソフトに取り込むときに、照合した結果（変換前の対応する拗音入りのデータ列）を利用すれば、簡単に取り込み用データを作成できると思います。

＞氏名(金額を除く氏名以外のその他の一部項目含む)が完全一致している必要があります。

２つのデータベースの違いを簡単に抽出するには、フィルタオプションの設定（１つのデータベースを検索条件範囲にする）の機能を利用すれば、簡単に一致データ(=不一致データ）を検索・抽出することができます。

No.6 回答者： MackyNo1 回答日時： 2015/01/10 22:14

>受注ソフトからエクスポートしたデータはお客様名が拗音を含んだ正しいお名前となりますが、

入金データは銀行の口座名義が拗音を使わない為、照合の際に「シュン」と「シュン」等が一致せず躓いておりました。

基本的に名前に拗音が含まれているかどうか文字列だけでは判定できないので、このような目的なら、置換操作またはSUBSTITUTE関数を使用して、単純に拗音を普通文字に変換して統一することをお勧めします。

このような統一をしておけば、COUNTIF関数などで簡単にデータの照合ができます。

この回答へのお礼

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

NO.5様の回答にもご返答させて頂きましたが、システム上の
仕様により普通文字に統一という事が今回はできかねました。

また、ご指摘頂きましたCOUNTIF関数での照合については見当がついておりましたので、
その方法で最終的にデータの照合を行おうと考えております。

また不明な点が出た際は再度ご質問させて頂く可能性もございますので、その際は何卒よろしくお願い致します。

お礼日時：2015/01/12 16:56

No.5 回答者： bunjii 回答日時： 2015/01/10 16:57

>受注ソフトからエクスポートしたデータはお客様名が拗音を含んだ正しいお名前となりますが、入金データは銀行の口座名義が拗音を使わない為、照合の際に「シュン」と「シュン」等が一致せず躓いておりました。

考え方を逆転させてください。
受注ソフトの拗音を銀行口座に合わせる方法にすれば変換テーブルを使わなくてもSUBSTITUTE関数で簡単に変換できます。
同姓同名の場合でも結果は同じことになるので複数がマッチしたときは別の要素（金額等）で判定すれば良いでしょう。

この回答へのお礼

度々ご回答ありがとうございます。

おっしゃる通り受注ソフトの拗音を口座名義に合わせる事によりデータ上での照合は取れるのですが、
照合したデータを再び受注ソフトにインポートして結果を取り込む為にはシステム上、
氏名(金額を除く氏名以外のその他の一部項目含む)が完全一致している必要があります。

その為、最初に受注ソフトよりエクスポートしたデータの拗音を含めた氏名に口座名義を合わせる必要がありました。

現在本件について試行錯誤しながら作業を行っておりますが、色々とその他課題がありまして
完了までもう少し時間がかかりますので、作業完了後にベストアンサーを決めさせて頂きたいと思います。

再度ご質問させて頂く可能性もございますので、その際は何卒よろしくお願い致します。

お礼日時：2015/01/12 16:48

No.4 回答者： WindFaller 回答日時： 2015/01/09 19:55

こんばんは。

今、ちょっと見てみたら、本格的な正規表現置換なのですね。しかし、専門ツールを使うのは、ちょっと一般の人には荷が重いような気がします。

私は、とても数式は敵わないので、VBAでやってみましたが、
例えば、イトウ　チユリ（伊藤千由李)　さんの名前は、ユが拗音化しませんが、
ウエノ　ジユリ（上野樹里)　さんは、ユが拗音化しますから、ここらは、規則性を見出すのが難しいですね。
だから、特別　JOYのジヨイさんのヨや、ジユリさんのユは、拗音化するようにしました。
アリヤス（有安)のヤは変化しません。


'//
'実行プログラム
Sub Test01()
Dim c As Range
　For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
　If c.Value Like "[ァ-ヶ]*" Then
　　c.Offset(, 1).Value = WordSmaller(c.Value)
　End If
　Next c
End Sub
'拗音化　ユーザー定義関数
Function WordSmaller(wd As Variant, Optional bln As Boolean = False)
　'引数 wd は文字, bln は、True or False 全ての引数の文字を出すか、変更のある分だけ出すか(False)
　Dim Matches As Object
　Dim Match As Object
　Dim buf As String
　Dim buf2 As String
　Dim a As String, b As String
　Dim i As Long, cnt As Integer
　If Len(wd) > 0 Then
　　buf = wd
　Else
　　Exit Function
　End If
　With CreateObject("VBscript.RegExp")
　　.Global = True
　　.Ignorecase = False
　　'拗音の規則性
　　.Pattern = "[キシチヒリミ]([ヤユヨ])[ウン].*|ジ([ヤユヨ])."
　　'人名にギジヂビの濁音が含まれるは分からない
　　Set Matches = .Execute(buf)
　　If Matches.Count > 0 Then
　　　For Each Match In Matches
　　　　For cnt = 0 To Match.SubMatches.Count - 1
　　　　　a = Match.SubMatches(cnt)
　　　　　If a <> "" Then
　　　　　　i = Asc(a): b = Chr(i - 1)
　　　　　　buf2 = Replace(Match.Value, a, b, , 1)
　　　　　　buf = Replace(buf, Match.Value, buf2, , 1)
　　　　　End If
　　　　Next cnt
　　　Next
　　　WordSmaller = buf
　　Else
　　　If bln Then '標準
　　　　WordSmaller = buf
　　　Else
　　　　WordSmaller = ""
　　　End If
　　End If
　End With
End Function
'//

この回答への補足

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

記載のVBAを実行したところ、意図しておりました正規表現置換ができました。

関数では出来ないと思い半ば諦めていたので感激しております。
ありがとうございました。

追加での質問となり恐縮ですが、もし可能でしたらご回答頂けますと幸いです。

'//
'実行プログラム
Sub Test01()
Dim c As Range
　For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))
　If c.Value Like "[ァ-ヶ]*" Then
　　c.Offset(, 1).Value = WordSmaller(c.Value)
　End If

上記の　For Each c In Range("A1", Cells(Rows.Count, "A").End(xlUp))は
当初の質問では便宜上A列にて記載しておりましたが、実際はF列を参照する為、"F1"と"F"に変更しました。

VBAを実行しますとG列に正規表現置換された値のみが表示され、
拗音に変更する必要のない値はG列では空白セルとなっていますが、
F列の「ヤ」、「ユ」、「ヨ」を拗音に変更する必要のない値もそのままG列に表示することは可能でしょうか？

つまりG列には拗音に変更された値と、変更する必要のない値両方が表示されるという状態です。

また、その上で置換されたF列を列ごとコピーして、C列にデータをそのまま貼り付け、
その後にE・F・G列を列ごと削除するというVBAを実行したいと考えています。
※D列には他の必要な値が入っておりますので、C列への貼り付けで間違いございません。

補足日時：2015/01/10 18:41

No.3 回答者： MackyNo1 回答日時： 2015/01/09 15:41

基本的にExcelを使用されているなら以下のようにPHONETIC関数でふりがなを表示すれば、入力通りのふりがなで表示できます(A1セルに漢字の名前が入力されている場合）。

=PHONETIC(A1)

他のアプリケーションから、コピーあるいはエクスポートしたデータでふりがな情報がないなら、以下の操作でふりがな情報を追加することができます。

漢字の名前範囲を選択してから、Alt+F11キーでVBEを起動し、Ctrl＋Gキーでイミディエイトウィンドウを表示して、そこに以下のコマンドを入力してEnterしてください。
selection.setphonetic

ただし上記のマクロコードで表示されるふりがなは、必ずしも正確なふりがなではない可能性がありますので、PHONETIC関数で表示したデータと元のデータが同じでないセルを「=PHONETIC関数のセル＝元のセル」のような数式を入力し、FALSEが返る行のみフィルタで抽出して（拗音に変更されたセルや誤まったふりがなが付いたセルを）最終チェックされるとよいと思います。

この回答へのお礼

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

データ上の氏名はカタカナ表記のみとなっており漢字表記はございません為、
今回お教え頂いた方法は使用できませんでしたが、後学の為に実践させて頂きました。

NO.2様のお礼にも記載させて頂きましたが、質問の意図が伝わりづらく申し訳ございませんでした。

またいずれ機会がございましたらよろしくお願い致します。

お礼日時：2015/01/10 16:26

No.2 回答者： bunjii 回答日時： 2015/01/09 15:05

回答No.1の追加です。

A5の"ヨシダ　トシユキ"を"ヨシダ　トシュキ"ではなく"ヨシダ　トシユキ"とする場合は変換テーブルを用意してVLOOKUP関数で変換する必要があるでしょう。
D列とE列に変換テーブルを作成したときに次の数式を使うと目的に合った変換ができます。
当然ですが、データが多くなれば変換テーブルを作成するための労力が増えます。
B1=IF(A1<>"",IFERROR(VLOOKUP(LEFT(A1,FIND("　",A1)-1),D:E,2,FALSE)&"　",LEFT(A1,FIND("　",A1)))&IFERROR(VLOOKUP(MID(A1,FIND("　",A1)+1,LEN(A1)),D:E,2,FALSE),MID(A1,FIND("　",A1)+1,LEN(A1))),"")
B1セルを下へコピーします。

この回答へのお礼

ご回答ありがとうございます。
NO.1の回答の後、NO.2の追加までに1時間弱お時間がありましたので、
私の質問にその後も試行錯誤して頂いていたのだなと大変嬉しく思い感謝しております。

説明不足で申し訳ございません。ご推察の通り銀行の口座名で拗音を使わないことに関係しております。

仕事上、お客様からの入金確認をする際に、現在はインターネットバンキングからデータをCSVでDLし、
CSVを印刷した紙ベースの入金一覧(口座名義と入金額が記載)と注文情報を取り込んでいる受注ソフト上で
目視で照合していますが、受注ソフトからエクスポートした入金一覧のCSVとインターネットバンキングからDLした
入金データのCSVを使用し、関数やVBAでマクロを実行し入金確認の作業が簡易化できないかと考えての質問でした。

受注ソフトからエクスポートしたデータはお客様名が拗音を含んだ正しいお名前となりますが、
入金データは銀行の口座名義が拗音を使わない為、照合の際に「シュン」と「シュン」等が一致せず躓いておりました。

NO.2の回答となりますと、お客様のご名義は千差万別となります事から
おっしゃる通り変換テーブルを作成する労力がかなり必要となるため見送らせて頂きました。

当初の質問時に詳細をお伝えさせて頂いた方が良いかとも考えましたが、
意図が分かりづらくなるかと思った為、簡易的に質問させて頂きました。

結果として説明不足となり申し訳ございませんでした。

お礼日時：2015/01/10 16:21

No.1 回答者： bunjii 回答日時： 2015/01/09 14:18

>氏名に含まれる一部の「ヤ」、「ユ」、「ヨ」のみ大文字から小文字に⇒の後のように変換したいと思います。

「小文字」ではなく「拗音」と言った方が良いと思います。
ヤ、ユ、ヨ以外にも拗音がありますので事例を精査する必要があるでしょう。
銀行の口座名で拗音を使わない習慣があるようですが関係しているのでしょうか？
拗音を普通文字に変換する場合は単独の文字を置き換えるだけで良いのですが逆の場合は文字列のパターンが多いので難しいと思います。
姓と名前の文字列パターンを比較するためのテーブルを作成して対比する方法にしないと誤変換になります。
カナ文字の氏名を姓と名前に分割して其々の先頭文字以外を全て変換すると言う手法で良ければ数式で対応できるでしょう。
A列に提示されたように氏名があるとき姓と名前の間に全角の空白文字があると仮定すれば次の式をB1へ設定して下へ必要数コピーすれば質問の範囲の処理が出来るでしょう。
B1=LEFT(A1,1)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(A1,2,FIND("　",A1)-1),"ヤ","ャ"),"ユ","ュ"),"ヨ","ョ")&LEFT(SUBSTITUTE(A1,LEFT(A1,FIND("　",A1)),""),1)&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(MID(SUBSTITUTE(A1,LEFT(A1,FIND("　",A1)),""),2,LEN(SUBSTITUTE(A1,LEFT(A1,FIND("　",A1)),""))),"ヤ","ャ"),"ユ","ュ"),"ヨ","ョ")