根据邮政编码确定 USPS 邮政区的公式 [英] Formula to determine USPS Postal Zone based on ZIP code

问题描述

我希望通过 USPS 邮政区域对数百个信封进行分类.困难在于必须根据原始邮政编码为 8 个美国区域中的每一个手动排序它们:91352.

http://postcalc.usps.gov/ZoneCharts/

我有一个包含 ZIP 列的联系人电子表格.我已经根据913"设置了所有 USPS ZoneCharts 站点的单独工作表，并将四个部分合并为一个(总共两列).然后我使用 LEFT 和 RIGHT 函数获取第一列的前三个和最后三个数字并将它们放入各自的列中(现在总共三列):

<前>ZIP_BEG ZIP_END 区域005 098 8100 212 8214 268 8270 342 8344 344 8346 347 8349 349 8350 352 7354 359 7360 361 8362 362 7363 364 8365 366 7...等

我是否会使用联系人表中的 VLOOKUP 函数来搜索每个 ZIP(使用 LEFT 函数仅使用每个 ZIP 值的前三个数字)，然后检查该值是否既大于 ZIP_BEG 值又小于比 ZIP_END 值?

无论匹配哪一行，它都会返回 Zone 值.我把它放在第一个工作表中 ZIP 列之后的列中.

解决方案

拆分所有可能的邮政编码似乎可行，但可能矫枉过正"(尽管可能有助于检测错误).我假设不在上述范围内的代码是 (a) 无效但 (b) 不需要以任何方式标记，因此例如 099 要么在实践中永远不会出现(除非表格被更新)或者可以安全地"被视为 098.

这是为了可以只考虑每个波段的一个值(在区域更改之前)，方便您的 ZIP_BEG 值，结合 不精确的 VLOOKUP.VLOOKUP 的语法是:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中第四个参数 (range_lookup) 是可选的.忘记它或错误地将其设置为 TRUE(或 1)会造成很大的痛苦，但在这里可能非常合适.

引用:

<块引用>

如果为 TRUE 或省略，则返回精确或近似匹配.如果未找到精确匹配，则返回小于 lookup_value 的下一个最大值.table_array 第一列的值必须按升序排列；否则，VLOOKUP 可能不会给出正确的值.

(它进行二分查找，因此需要知道哪个方向多，哪个方向少).您的值应该已经按要求的顺序排列，因此公式如:

=VLOOKUP(A2,ZIPUP,2)

在同一工作簿中的某处应该就足够了，其中要查找的值(目标 Zip 的前三位数字)假定在 A2 中，并且 ZIPUP 是工作簿范围的名称一列中的 ZIP_BEG 和紧邻该列右侧的匹配行中的 Zone 范围.

鉴于最初的假设，不需要整个 ZIP-BEG 列表(108 个范围")，因为仅使用限制，74 个就足够了(并且应该更快).

如果不以这种方式聚合波段，请注意格式为 005 与 5 不同，并且这种区别与 =VLOOKUP 相关.您已经使用 =LEFT 和 =RIGHT 来提取您的列表并且这些文本函数返回字符串，尽管在这里我更喜欢自己格式化数字.(我使用文本到列"来拆分范围.)

I'm looking to make the sorting of hundreds of envelopes by USPS postal zones easier. The difficulty is in having to manually sort them for each of the 8 US zones, based on the origin ZIP code: 91352.

http://postcalc.usps.gov/ZoneCharts/

I have a spreadsheet of contacts that includes a ZIP column. I've set up a separate sheet of all of the USPS ZoneCharts site based on "913", and combined the four sections into one (two columns total). I then used the LEFT and RIGHT functions to get the first three and last three numbers of the first column and put them into their own columns (now three columns total):

ZIP_BEG  ZIP_END  Zone
005      098      8
100      212      8
214      268      8
270      342      8
344      344      8
346      347      8
349      349      8
350      352      7
354      359      7
360      361      8
362      362      7
363      364      8
365      366      7
...etc.

Would I use the VLOOKUP function from the sheet of contacts to search each ZIP (using the LEFT function to use only the first three numbers of each ZIP value) and then check whether that value is both greater than the ZIP_BEG value and less than the ZIP_END value?

Whatever row it matches, it would return the Zone value. I'm putting this as a column after the ZIP column in the first sheet.

解决方案

Splitting out every possible zip code seems viable but might be ‘overkill’ (though might be useful to detect errors). I’m assuming that a code not in the range mentioned is (a) not valid but (b) does not require to be flagged in any way, so for example 099 will either never arise in practice (unless the tables are updated) or can ‘safely’ be treated as 098.

This is to make it possible to consider only one value for each band (before a change in Zone), conveniently your ZIP_BEG ones, in conjunction with an inexact VLOOKUP. The syntax for VLOOKUP is:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where the fourth parameter (range_lookup) is optional. Forgetting it or setting it to TRUE (or 1) by mistake has caused much grief but may be very suitable here.

To quote:

If TRUE or omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. The values in the first column of table_array must be placed in ascending sort order; otherwise, VLOOKUP may not give the correct value.

(It does a binary search, so needs to know which direction is more and which less).Your values should already be in the required order, so a formula such as:

=VLOOKUP(A2,ZIPUP,2)  

somewhere in the same workbook should be sufficient, where the value to be looked up (first three digits of destination Zip) is assumed to be in A2 and ZIPUP is the name of a workbook-scoped range of ZIP_BEG in one column and Zone in the matching rows in the column immediately to the right of that.

Given the initial assumptions, the entire ZIP-BEG list is not required (108 ‘ranges’) as, using only the limits, 74 are sufficient (and should be quicker).

If not aggregating the bands in that way, beware of formatting as 005 is not the same as 5 and that distinction is relevant to =VLOOKUP. You have used =LEFT and =RIGHT to extract your lists and these text functions return strings, though here I would prefer number formatting myself. (I split the ranges with Text to Columns.)

这篇关于根据邮政编码确定 USPS 邮政区的公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！