| |
▼にしもり さん：
こんばんは。
>頭をひねりましたがわからないのでご教示ください。
>私の作っているシートをすこし詳しく述べますと以下のとおりです。
なるほど・・・、これでは私の提示した関数では作動しません。
>
>A3:住所を入力するセル（例、愛知県蒲郡市栄町1-1)
>B3:=LEFT(A3,4-SUM((MID(A3,3,1)={"都","道","府","県"})*1))
>C3:=IF(B3="東京都",IF(COUNT(FIND({"東村山","武蔵村","羽村市"},LEFT(I3,3))),LEFT(I3,FIND("市",I3)),LEFT(I3,MIN(FIND({"市","区","町","村"},I3&"市区町村",2)))),IF(COUNT(FIND({"今市市","四日市","八日市","廿日市"},LEFT(I3,3))),LEFT(I3,FIND("市",I3,FIND("市",I3)+1)),IF(IF(COUNT(FIND({"蒲郡市","大和郡","小郡市"},LEFT(I3,3))),"市",IF(COUNT(FIND({"余市郡","高市郡"},LEFT(I3,3))),"郡",MID(I3,MIN(FIND({"市","郡"},I3&"市郡",2)),1)))="市",IF(COUNT(FIND({"札幌市","仙台市","さいた","千葉市","横浜市","川崎市","名古屋","京都市","大阪市","神戸市","広島市","北九州","福岡市","静岡市","堺市"},LEFT(I3,3))),LEFT(I3,FIND("区",I3)),LEFT(I3,FIND("市",I3,2))),LEFT(I3,MIN(FIND("郡",I3))))))
>D3:=myphonetic(C3)
>E3:=RIGHT(J3,LEN(J3)-(LEN(B3)+LEN(C3)))
>F3:=myphonetic(E3)
>G3:==MID(A3,FIND("町",A3)+1,LEN(A3))
>H3:=myphonetic(G3)
>I3:=RIGHT(A3,LEN(A3)-LEN(B3)) ＜作業域１＞
>J3:=LEFT(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A3)&1234567890))-1) ＜作業域２＞
ふたつ関数を提示します。
いづれも標準モジュールに
'============================================================
Function myphonetic(rng As Range) As Variant
On Error Resume Next
Dim wk As Variant
myphonetic = ""
wk = rng.Value
If Err.Number = 0 Or IsError(wk) = False Then
myphonetic = Application.GetPhonetic(wk)
End If
On Error GoTo 0
End Function
'============================================================
Function myphonetic2(rng As Range, csrng As Range) As Variant
On Error Resume Next
Dim wk As Variant
Dim st As Long
Dim tlen As Long
Dim g0 As Long
Dim g1 As Long
myphonetic2 = ""
wk = rng.Value
If Err.Number = 0 Or IsError(wk) = False Then
st = InStr(csrng.Value, wk)
tlen = 0
myphonetic2 = ""
g1 = 0
With csrng
For g0 = 1 To .Phonetics.Count
If .Phonetics(g0).Start >= st And g1 = 0 Then
myphonetic2 = .Phonetics(g0).Text
tlen = .Phonetics(g0).Length
g1 = 1
ElseIf g1 = 1 Then
myphonetic2 = myphonetic2 & .Phonetics(g0).Text
tlen = tlen + .Phonetics(g0).Length
End If
If tlen >= Len(wk) Then
Exit For
End If
Next
End With
End If
On Error GoTo 0
End Function
使用方法
>D3:=myphonetic(C3)
関数 myphoneticは今までと同様に
=myphonetic(c3)
という指定です。
myphonetic2は、
=myphonetic2(c3,a3)
と指定してください。
>F3:=myphonetic(E3)
=myphonetic2（E3,A3)
としてください。
myphonetic関数とmyphonetic2関数では、
一長一短がありますから、どちらが良いかは試してみ決めてください。
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
