Формула для определения почтовой зоны USPS на основе почтового индекса

Я хочу упростить сортировку сотен конвертов по почтовым зонам USPS. Сложность заключается в том, что приходится вручную сортировать их для каждой из 8 зон США на основе почтового индекса происхождения: 91352.

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

У меня есть электронная таблица контактов, которая включает столбец ZIP. Я создал отдельный лист всего сайта USPS ZoneCharts на основе «913» и объединил четыре раздела в один (всего два столбца). Затем я использовал функции ВЛЕВО и ВПРАВО, чтобы получить первые три и последние три числа первого столбца и поместить их в свои собственные столбцы (теперь всего три столбца):

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.

Буду ли я использовать функцию VLOOKUP из листа контактов для поиска каждого ZIP-архива (используя функцию LEFT для использования только первых трех цифр каждого значения ZIP), а затем проверять, является ли это значение больше, чем значение ZIP_BEG, и меньше, чем ZIP_END ценность?

Какой бы строке он ни соответствовал, он вернет значение Zone. Я помещаю это как столбец после столбца ZIP на первом листе.


excel vlookup excel-formula excel-2007 zipcode
person zacourie    schedule 21.01.2014    source источник
comment
Похоже, ты во всем разобрался. Каков твой вопрос?   -  person simbabque    schedule 22.01.2014
comment
О, я делаю? Большой! За исключением того, что я не могу понять, как применять логику больше/меньше (и проверять это на основе двух столбцов) в ВПР. Мой мозг взрывается, когда я пытаюсь написать формулу. Знаете ли вы, как или куда я могу обратиться, чтобы лучше понять, как написать формулу для такого типа задач? Спасибо!   -  person zacourie    schedule 22.01.2014
comment
Я пытался сказать, что не понимаю из вашего описания, в чем проблема. ;-)   -  person simbabque    schedule 22.01.2014
comment
Возможно, будет проще создать список всех почтовых индексов на отдельном листе (например, путем перетаскивания мышью) и использовать его для поиска. Так вы избавите себя от логики.   -  person simbabque    schedule 22.01.2014
comment
Я вижу что ты тут делал. Спасибо, @simbabque!   -  person zacourie    schedule 22.01.2014

Ответы (1)


arrow_upward
4
arrow_downward

Разделение всех возможных почтовых индексов кажется жизнеспособным, но может быть «излишним» (хотя может быть полезно для обнаружения ошибок). Я предполагаю, что код, не входящий в указанный диапазон, (а) недействителен, но (б) не требует какой-либо пометки, поэтому, например, 099 либо никогда не возникнет на практике (если таблицы не будут обновлены) или можно «безопасно» рассматривать как 098.

Это сделано для того, чтобы можно было учитывать только одно значение для каждой полосы (до изменения зоны), удобно ваши ZIP_BEG, в сочетании с неточным ВПР. Синтаксис для VLOOKUP:

ВПР(искомое_значение,массив_таблиц,номер_индекса_столбца,диапазон_просмотра)

где четвертый параметр (range_lookup) является необязательным. Если вы забыли его или установили его в TRUE (или 1) по ошибке, это вызвало много проблем, но здесь он может быть очень уместным.

Цитировать:

Если значение TRUE или опущено, возвращается точное или приблизительное совпадение. Если точное совпадение не найдено, возвращается следующее наибольшее значение, которое меньше искомого_значения. Значения в первом столбце table_array должны быть расположены в порядке сортировки по возрастанию; в противном случае функция ВПР может дать неверное значение.

(Он выполняет бинарный поиск, поэтому ему нужно знать, в каком направлении больше, а в каком меньше). Ваши значения уже должны быть в нужном порядке, поэтому такая формула, как:

=VLOOKUP(A2,ZIPUP,2)

где-то в той же рабочей книге должно быть достаточно, где искомое значение (первые три цифры целевого почтового индекса) предполагается в формате A2, а ZIPUP — это имя диапазона рабочей книги из ZIP_BEG в одном столбце и Zone в другом. соответствующие строки в столбце сразу справа от этого.

Учитывая первоначальные предположения, весь список ZIP-BEG не требуется (108 «диапазонов»), поскольку при использовании только пределов достаточно 74 (и должно быть быстрее).

Если не объединять каналы таким образом, остерегайтесь форматирования, так как 005 не совпадает с 5, и это различие относится к =VLOOKUP. Вы использовали =LEFT и =RIGHT для извлечения ваших списков, и эти текстовые функции возвращают строки, хотя здесь я бы предпочел форматирование чисел самостоятельно. (Я разделил диапазоны текстом на столбцы.)

person pnuts    schedule 22.01.2014