根据邮政编码确定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 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 
 ...等。

我将使用联系人列表中的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 的工作簿范围的名称，区域在列的右侧的匹配行中。鉴于初始假设，整个 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 migh 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屋！