☰

コード：

tag-icon • tag-icon microsoft-excel
コード：

次の形式でアドレス指定された列があります:

120 Lemon Street Columbus OH 92738 (Basketball Courts)

そして、それを住所(120 Lemon Street)、市(Columbus)、州(OH)、郵便番号(92738)、説明に分割する必要があります((Basketball Courts))

これを実行する方法はありますか? これらはすべて同じ州にあるため、問題はありません。これらは異なる都市/町にあり、郵便番号も異なります。

やり方の一部しか知らない場合でも、お知らせください。どんな助けでも大歓迎です、ありがとうございます!

答え1

User1282637 さん、以下に例を示しました。これはあくまで出発点として作成したもので、最終的にはご自身の完全な回答を得ることができます。

まず、2 つのリストを作成しました。1 つはすべての州の略語で、もう 1 つはここで見つけた、認められているすべての通りの接尾辞です。

翻訳元:

ここに画像の説明を入力してください

私がリストした数式は、単にリストを変換して、あなたが書いたように、大文字で始まり、残りが小文字になるようにしたものです。

次に、何が当てはまるかを調べるだけです。

ここに画像の説明を入力してください

この部分はさまざまな方法で実行できますが、例としてこの方法を採用することにしました。これは、使用されているサフィックスに一致する番号を対応する行にリストするだけです。

括弧を使用して (バスケットボール コート) 部分を区切ります。

ここに画像の説明を入力してください

あなたの場合、「Basketball Courts」の「Courts」も通りの接尾辞なので、残りの部分を表示します。

ここに画像の説明を入力してください

次に、ストリートの接尾辞である文字列の長さを見つける必要があるため、次の式を使用します。

ここに画像の説明を入力してください

この...

ここに画像の説明を入力してください

最後に、これにより通りだけを表示できます。

ここに画像の説明を入力してください

今のところ、市と州についてはやっていませんが、このアイデアに従えば、これを実現できます。また、よりクリーンなオプションが必要な場合は、VBA についてさらに学習することを検討してください。少なくとも、この方法でこれを実行する方法についてのアイデアがいくつか伝われば幸いです。

答え2

User1282637 は、このタスクを実行する方法があるかどうかを尋ね、そのための支援を求めています。問題は、Excel の仕組みではなく、データのあいまいさに対処する方法です。郵便番号と説明の解析は簡単です (5 桁と 9 桁の郵便番号が混在していない場合)。難しい問題は、通りと都市を区別することです。そのため、それに焦点を当てます。これは、Excel の数式を使用して手順を追って説明するものではありません。問題に関する洞察を共有し、難しい部分の結果を得るためのアプローチを説明するだけです。

問題は、異なるフィールド間に区切り文字がないことです。説明や郵便番号は簡単に識別できるため、これらを剥がす場合は問題になりません。問題は、Street がどこで終了し、City がどこで始まるかを判断することです。Street 部分の次のバリエーションを検討してください (完全なリストではありません)。

120 Lemon Street
120 Lemon Drop Street
120 Lemon Street NW
120 East Lemon Street
120 Lemon Street Apt 3

通りの「単語」の数は、1 または 2 から 7 または 8 までさまざまであるため、解析には役立ちません。通りの「種類」も特に役立ちません。通りの「種類」に使用される単語は、50 ～ 100 語程度あります (street、avenue、boulevard、way、turnpike、lane、court、circle、teras など)。これに、通りの種類の省略形 (正しいものも間違ったものも) の使用を組み合わせると、リストは数百になります。さらに、この指定は、必ずしも通りフィールドの最後の単語ではありません。通りは識別するのが最も難しい部分であるため、論理的なアプローチは、残りの部分を特定し、残りが通りであることです。

都市は複数の単語で表すことができます。たとえば、Washington Court House, OH は 3 つの単語です。次に、St Marys, OH のような状況を考えてみましょう。「St」は都市名の一部ですか、それとも通りの種類を示すものですか。どのフィールドに入力しますか。または、South Euclid, OH の場合、「South」は都市名の一部ですか、それとも通りの住所の一部である方向ですか。都市には問題がありますが、それに対処する方法があります。

郵便番号を使用して都市を特定する場合も問題があります。都市名と郵便番号が必ずしも 1:1 で一致するとは限りません。

この問題に対処する最も実用的な方法は、「辞書」を使用することです。つまり、都市のリストと郵便番号ディレクトリです。これらは住所の最も明確な部分です。これらはオンラインまたは郵便局から入手できます。比較を行うには、データまたはリストのいずれかをクリーンアップする必要があるかもしれません。大文字と小文字のスタイルを同じにする必要があり、データに余分なスペースがあると、完全に一致しなくなります。

データまたはリストのいずれかに略語が使用されている場合は、その対応が必要になります。略語が使用されていないものを標準の略語に翻訳するか、相違が見つかった場合は略語辞書 (オンラインまたは郵便局から入手可能) との二次照合を行います。

ZIP は簡単に解析できるので、まずはそこから始めるのが良いでしょう。ZIP コード ディレクトリに対して ZIP コード検索を実行します。結果が ZIP の直前の単語の文字列と完全に一致する場合、レコードのどの部分が都市フィールドであるかがわかります。

完全に一致するものや明確なものがない場合、都市名の比較に進みます。都市名リストを反復処理します。名前ごとに、含まれる単語の数を決定し、ZIP の直前の単語の数と比較します。

いずれかのプロセスで一致が見つかった場合、都市名の左側に残っているものはすべて番地になります。

この種のアプリケーションは、スプレッドシートよりもデータベース アプリケーションの方がはるかに簡単です。いずれにしても、これを自動化することは簡単な作業ではないことがわかります。スプレッドシートの数式をいくつか使用してこれを行うことはできません。

プログラミングがどれだけ厳密であっても、手作業で解析する必要のあるレコードや、手作業で修正する必要のある解析エラーがまだ存在する可能性があります。レコードの数を指定しません。単純に手作業で行う方が作業が少なくなる場合があります。

数が多くてそうしなければならない場合は、リストから除外します。明確な ZIP の一致など、簡単なレコードを一致させます。その後、データの量に応じて、自動一致をプログラミングする範囲を決定します。

残っているレコードについては、ZIP と説明をすでに剥がしていると仮定して、手動のプロセスを高速化する方法があります。レコードを見て、都市の「単語」の数を視覚的に特定します。これは、頭ですばやく行う作業です。それを所定のセルに入力し、スペース区切りの数に基づいて、数式を使用して通りと都市を分割します (N 番目のスペースで分割します。ここで、N = 合計スペース + 1 - 都市名の単語数)。

答え3

これを試してみてください。住所と都市名を同じセルに入れても問題ない場合は、このシンプルなアプローチが非常にうまく機能します。都市名が 1 語だけの場合に都市名を抽出する数式がありますが、都市名が複数の単語 (例: New York) の場合は、はるかに複雑になります。

数式... 住所と市区町村: =LEFT(A2,FIND("OH",A2)-1) 州: =MID(A2,FIND("OH",A2),2) - すべてが OH であるとおっしゃったので、シンプルにしました 郵便番号: =MID(A2,FIND("OH",A2)+3,5) 説明: =TRIM(MID(A2,FIND("OH",A2)+8,30))

https://onedrive.live.com/redir?page=view&resid=D91C36B074F4D0F6!3224&authkey=!AO_MbW7Qxv4yWDo

ここに画像の説明を入力してください

答え4

コード：

Sub SplitAddress()
    Dim Addr As String
    Dim l As Integer
    Dim Desc As String
    Dim Zip As String
    Dim State As String
    Dim City As String
    
    Addr = Selection
    
    l = InStrRev(Addr, "(")
    Desc = Right(Addr, Len(Addr) - l + 1)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    Zip = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    State = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    l = InStrRev(Addr, " ")
    City = Right(Addr, Len(Addr) - l)
    
    Addr = Left(Addr, InStrRev(Addr, " ", l) - 1)
    
    Selection.Offset(0, 1) = Addr
    Selection.Offset(0, 2) = City
    Selection.Offset(0, 3) = State
    Selection.Range("B11").Offset(0, 4) = Zip
    Selection.Range("B11").Offset(0, 5) = Desc
End Sub

説明: を押してAlt+F11、表示されるウィンドウに上記のコードを貼り付けます。次に、アドレスを含むセルを選択し、コードを貼り付けたウィンドウに戻って を押しますF5。

それがうまくいけば、私たちはそれをあなたの特定の状況にさらに特化させるように取り組むことができます。

関連情報