Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : Excel 2010)
エクセル2010で住所のフリガナ表示をしたい
投稿日時: 19/02/11 22:29:20
投稿者: pyonpyon

エクセル2010で住所のフリガナ表示をしたい
 
B1に郵便番号または住所を入力
A1は「=ASC(PHONETIC(H5))」入れています
B1に住所を入れた場合はA1にはフリガナが表示されますが、
郵便番号入力して住所に変換した場合は、入力した郵便番号が
表示されてしまいます。
住所のフリガナを表示させることはできますか?
 
やりたいことは、
 
A列に郵便番号、B列に住所、C列に住所のレイアウトで
 
郵便番号がわかる時はA列に郵便番号を入力し、自動でB列、C列を表示したい。
郵便番号がわからない時はB列に住所を入力し、A列は空白、自動でC列を表示したい。
 
よろしくお願いいたします。

回答
投稿日時: 19/02/11 22:54:54
投稿者: WinArrow
投稿者のウェブサイトに移動

>A1に「=ASC(PHONETIC(H5))」
 
説明から推察すると、「H5」ではなくて「B1]ではないかと思いますが・・・・
 
PHONETIC関数は、入力時に使用した「読み」(カタカナを使用したか、郵便番号を使用したか)
を取り出す関数です。
入力された住所の読み(ルビ)を取り出す関数ではありません。
 
ですから
A1セルに、郵便番号を取り出したいおでしたら、
B1セルに、郵便番号を入力して「変換」機能で住所を表示することです。
 
 
住所の読みを取り出すユーザー定義関数を紹介します。
一番シンプルな例
 
VBE画面を開き
標準モジュールに
↓のコードを貼り付けてください。
Function GETPHONETIC(ByVal 住所 As String)
    GETPHONETIC = Application.GETPHONETIC(住所)
End Function
 
C1セルには
=GETPHONETIC(B1)
を入力します。
 
難点があります。
意図した読みに変換されない場合があります。
その場合、「値」に変換後、正しい読みを入力してください。
 
 

投稿日時: 19/02/12 17:15:36
投稿者: pyonpyon

WinArrow さん、回答ありがとうございます。
 
もう少し教えてください。下記★を参照してください。
 
 
 
>A1に「=ASC(PHONETIC(H5))」
  
説明から推察すると、「H5」ではなくて「B1]ではないかと思いますが・・・・
★おっしゃるとおりです。間違っておりました。
 
  
PHONETIC関数は、入力時に使用した「読み」(カタカナを使用したか、郵便番号を使用したか)
を取り出す関数です。
 入力された住所の読み(ルビ)を取り出す関数ではありません。
  
ですから
A1セルに、郵便番号を取り出したいおでしたら、
 B1セルに、郵便番号を入力して「変換」機能で住所を表示することです。
★現在はこの方法でやってます。
   
住所の読みを取り出すユーザー定義関数を紹介します。
 一番シンプルな例
  
VBE画面を開き
標準モジュールに
↓のコードを貼り付けてください。
Function GETPHONETIC(ByVal 住所 As String)
     GETPHONETIC = Application.GETPHONETIC(住所)
End Function
★デバッグで引数の住所を見ると、郵便番号で変換して表示された住所が入っています。
 
   
C1セルには
=GETPHONETIC(B1)
を入力します。
★これだと入力した郵便番号が表示されてしまいますので、現在と変わりありません。
★現在はB1に郵便号を入力するとB1に変換された住所が表示され、A1に郵便番号を表示しています。
★これにC1を追加して、B1に表示された読み(下記例では”とうきょうとしながわく”)を表示したいのですが・・・
★A1    B1      C1
★123-4567 東京都品川区 とうきょうとしながわく
 
★よろしくお願いします。

回答
投稿日時: 19/02/12 17:44:30
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

★これだと入力した郵便番号が表示されてしまいますので、現在と変わりありません。
★現在はB1に郵便号を入力するとB1に変換された住所が表示され、A1に郵便番号を表示しています。
★これにC1を追加して、B1に表示された読み(下記例では”とうきょうとしながわく”)

 
セルC1に入力した数式間違っていませんか?
おそらく
=PHONETIC(B1)
と入力していませんか?
 
 
因みに
「東京都品川区」に対応する虚ェ番号は存在しませんよね?
123-4567で入力した住所は、「123−4567」しか変換されませんよ。
決して、「東京都品川区」には変換されません。
 
ユーザー定義関数は、全角カタカナに変換されます。
ひらがなに変換する場合は、若干コードを変更する必要があります。
 
 
 
 
 
 

回答
投稿日時: 19/02/12 17:46:57
投稿者: WinArrow
投稿者のウェブサイトに移動

すみません
文章訂正します
 
>虚ェ番号

郵便番号
 

回答
投稿日時: 19/02/12 17:50:47
投稿者: WinArrow
投稿者のウェブサイトに移動

>「東京都品川区」に対応するゆうぶん郵便番号は存在しませんよね?
 
すみません。確認不足でした。
 
「東京都品川区」に対応する郵便番号は「140−0000」でした。

回答
投稿日時: 19/02/12 17:58:11
投稿者: WinArrow
投稿者のウェブサイトに移動

紛らわしいから
ユーザー定義関数の名前を変更します。
ひらがな変換を追加しあした。
 
Function GETADDYOMI(ByVal 住所 As String)
      GETADDYOMI = StrConv(Application.GETPHONETIC(住所), vbHiragana)
End Function
 
 
セルC1に
=GETADDYOMI(B1)
 

投稿日時: 19/02/13 00:57:18
投稿者: pyonpyon

WinArrowさん、こんばんわ&返信ありがとうございます。
 
GETADDYOMIでできました。
 
が、表示した住所が辞書登録していると、その値が表示されてしまいます。
B1に「140-0000」と入力すると、B1に「東京都品川区」と表示され、C1には「とし」と表示されます。
「とし」で「東京都品川区」と辞書登録してあるので。
 
これはもうしょうがないですかね?

回答
投稿日時: 19/02/13 11:01:46
投稿者: WinArrow
投稿者のウェブサイトに移動

Application>.GetPhoneticメソッドの実行に際して
前レスに「難点があります。」の状況を説明します。
例えば
「神戸」を「こうべ」と入力して変換したとすると、「コウベ」が取得されます。
また、
「神戸」を「かんべ」と入力して変換したとすると、「カンベ」が取得されます。
このように1回しか実行しないと、最新のフリガナが取得されますから、
意図した通りにはなりません。
辞書登録したもの対象にあります。
郵便番号をフリガナとして登録すると、郵便番号が取得されます。
 
 
GetPhoneticメソッドには、
複数のフリガナを取得できる機能がある
と説明されています。
 
複数のフリガナを表示して、妥当なフリガナを選択させるというものです。
特に「氏名」「住所」などは特殊な読みが存在するため、
 
この方法はユーザー定義関数では対応できません。
 
B列セルの文字列を使って複数のフリガナを取得し、
C列セルに「入力規則」のリストとして設定すれば、▼ボタンをクリックして、妥当なフリガナを選択することができます。
 
シートモジュールに↓のコードを貼り付けて試してみてください。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim 読み As String, Phonetic As String
    If Target.Column <> 2 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    Phonetic = Application.GetPhonetic(Target.Value)
    Do While Phonetic <> ""
        If 読み <> "" Then 読み = 読み & ","
        読み = 読み & StrConv(Phonetic, vbHiragana)
        Phonetic = Application.GetPhonetic()
    Loop
    With Target.Offset(, 1)
        With .Validation
            .Delete
            .Add Type:=xlValidateList, _
                Formula1:=読み
            .ShowError = False
        End With
        .ClearContents
    End With
 
End Sub
 
 
しかし、私の環境では、なぜか、複数のフリガナが取得できなくなって今いました。
 
原因は、不明です。
 
 
懸念される事柄があります。
 
日本語変換を簡単にするめ、辞書に「とし」とか「140−0000」というような読みを登録しても
それは、登録したPCでのみ使用することができます。
作成したブックを他PCで使うことを前提にする場合は、問題が発生します。
また、OSバージョンアップなどでは、ユーザー辞書移行を忘れてしまうことが多く、
自分のPCでも、「あれっ」ってことになります。
 
 
 

回答
投稿日時: 19/02/13 11:37:55
投稿者: WinArrow
投稿者のウェブサイトに移動

どうやら
Application.GetPhoneticメソッドは、1回目だけOKに仕様が変わったみたいです?

回答
投稿日時: 19/02/13 13:57:09
投稿者: WinArrow
投稿者のウェブサイトに移動

住所に限定するとして、
一般的なフリガナ変換ツールには限界があります。
 
日本郵便が提供している「郵便番号データ」(CSV形式)があります。
このデータをダウンロードして、郵便番号をキーにしてフリガナを取得することができます。
 
CSV形式ですからExcelに変換すれば、いくつかの制限があるものの、VLOOKUP関数でも対応できると思います。
 
https://www.post.japanpost.jp/zipcode/download.html
 
 
使い方等は、ページの中の説明をよく読んでください。

回答
投稿日時: 19/02/13 17:12:45
投稿者: WinArrow
投稿者のウェブサイトに移動

住所に関するコメント
 
日本郵便のページを見ればわかりますが、
全国的には、毎月、どこかの地域で住所が変更されています。
 
住所を扱う場合、
そのどこかに該当する可能性を考え、メンテナンスまで組考えて置く必要があると思います。
 
↑のレスではVlookup関数で対応可能と書きましたが、
同一郵便番号で、複数の住所が存在することもあり得ます。
逆に1つの住所が2つの郵便番号で登録されている可能性もあります。
これに該当するかどうかも確認しておく必要もあります。
 

投稿日時: 19/02/13 21:03:47
投稿者: pyonpyon

WinArrowさん。
 
>どうやら
>Application.GetPhoneticメソッドは、1回目だけOKに仕様が変わったみたいです?
★サンプルソースを組み込んでやってみましたが、私の所でもリストには1つしか設定されませんでした。
 
★VLOOKUP関数も郵便番号がキーのため使えません(必ず郵便番号が入力されるとは限らないので)
 
★住所のふりがなは住所を「あいうえお順」にソートしたくてトライしていたのですが
★ややハードルが高そうなので、当面、住所の「あいうえお順ソート」は見合わせます。
 
★いろいろとご指導頂きまして、ありがとうございました。
★また質問させていただいた時はよろしくお願いいたします。
★本当にありがとうございました。