У Стива есть номера телефонов в столбце A, например 3035551212, и он хотел бы иметь возможность посмотреть первые три цифры (код города) и вернуть в столбце B состояние, с которым связан этот код города. Он задается вопросом, как лучше всего этого добиться.

В Excel есть множество функций, которые облегчают жизнь, когда вы пытаетесь манипулировать данными. В этом случае просто использовать функцию ВПР для сопоставления кода области с соответствующим состоянием.

Перед применением функции для получения нужной информации вам необходимо создать простую таблицу данных, содержащую данные, которые вы хотите получить. В этой таблице вам необходимо, чтобы коды городов и штаты располагались рядом друг с другом в отдельных столбцах на листе, отсортированные по коду города. Например, вы можете поместить коды городов в столбец F, а состояния для этих кодов регионов в столбец G. Коды регионов и штаты можно найти на нескольких веб-сайтах, или вы можете создать свою собственную таблицу, если хотите.

После того, как вы разместите данные в двух столбцах, выберите эти столбцы и создайте имя для выбранного диапазона. (О том, как создать именованный диапазон, рассказывалось в других выпусках ExcelTips.) Например, вы можете назвать диапазон чем-то вроде StateCodes. Такое именование, хотя и не является строго необходимым, упрощает использование формулы поиска.

Предполагая, что номер телефона находится в ячейке A1 и вы хотите, чтобы название штата возвращалось в столбце рядом с номером телефона, в ячейке B1 вы должны ввести:

=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)

Функции VALUE и LEFT используются для извлечения только первых трех символов из номера телефона. Затем он используется в формуле ВПР для поиска кода области в таблице StateCodes. Excel возвращает название штата, которое соответствует коду города.

Другой способ получить код области (который необходим для поиска) — использовать функцию FLOOR, как показано здесь:

=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)

Обратите внимание, что этот подход требует, чтобы номер телефона хранился как число, поэтому его можно разделить на 10 000 000.

Обсуждаемые здесь подходы отлично работают при условии, что ваши номера телефонов всегда указаны в указанном формате (3035551212). Если ваши номера телефонов имеют другой формат, например, в котором используются скобки и тире, то формула не будет работать, и ее необходимо будет скорректировать, чтобы посмотреть, где на самом деле код города находится в номере телефона. Если у вас есть номера телефонов, которые представлены в разных форматах, то все ставки отключены, и задача поиска становится намного сложнее.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (8063) применим к Microsoft Excel 97, 2000, 2002 и 2003. Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и более поздних версий) здесь:

link: / excelribbon-Determining_a_State_from_an_Area_Code [Определение штата по коду города].