Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Формулы и функции

Определение страны по телефонному номеру в Excel с помощью формулы

forest_paw 26.03.2026 350

Определение страны по телефонному номеру в Excel с помощью формулы

Если перед вами стоит задача определить страну по телефонному номеру в Excel, используя формулы, этот материал для вас. Мы разберем решение, основанное на вложенных функциях ВПР / VLOOKUP и нескольких таблицах подстановки для телефонных префиксов разной длины.

Материал поможет, если вам нужны понятные формулы в Excel, разбор функций на русском и английском и практические приемы для расчетов без ошибок.

Международный код страны или зоны по E.164 состоит из 1–3 цифр. Более длинный префикс объединяет его с частью национального номера и может уточнять территорию по национальному плану нумерации. Общий код, например +1 в NANP, используется несколькими странами и территориями; одного кода для однозначного определения страны недостаточно.

Формула для определения страны по телефонному номеру использует пять таблиц подстановки. Одна таблица, названная SIX, содержит шестизначные префиксы для уточнения территории. Другие таблицы (FOUR, THREE, TWO, ONE) обрабатывают префиксы другой длины.
Формула для определения страны по телефонному номеру использует пять таблиц подстановки. Одна таблица, названная SIX, содержит шестизначные префиксы для уточнения территории. Другие таблицы (FOUR, THREE, TWO, ONE) обрабатывают префиксы другой длины.

Совет: создайте и проверьте именованные диапазоны через «Формулы → Диспетчер имён». В каждом диапазоне первая колонка содержит числовые префиксы без плюса, вторая — название страны, территории или общей зоны. Заголовки в диапазон поиска не включайте.

Метод ниже требует собственных справочников префиксов. Файл для скачивания здесь не приложен. Обновляйте справочники по данным ITU и администраторов национальных планов. Не обозначайте общий код названием только одной из использующих его стран; номер не определяет гражданство или фактическое местоположение владельца.

В столбце A представлены различные телефонные номера. Формула в столбце B возвращает подпись из справочника префиксов, а не сам код.
В столбце A представлены различные телефонные номера. Формула в столбце B возвращает подпись из справочника префиксов, а не сам код.

Как работает формула

  1. Формула проверяет пользовательские префиксы по убыванию длины: 6, 4, 3, 2, 1 цифра. Длины 6 и 4 на иллюстрации — расширения международного кода для уточнения территории, а не длины кода страны по E.164. Если справочнику нужны другие длины, добавьте соответствующие проверки.
  2. Для извлечения части номера используется функция ПСТР / MID.
  3. Каждый извлеченный фрагмент ищется в соответствующей именованной таблице (SIX, Four, Three, Two, One) с помощью ВПР / VLOOKUP.
  4. Функция ЕСНД (IFNA) обрабатывает только ошибку отсутствия совпадения #Н/Д, переходя к проверке кода следующей длины, если текущий не найден.
  5. Если префикс не найден ни в одном справочнике, формула возвращает «Не найдено». Ошибки преобразования в число, неверные имена диапазонов и другие ошибки ЕСНД не скрывает.

После создания всех пяти двухколоночных именованных диапазонов введите формулу в B1 и скопируйте вниз. При отсутствии расширенных префиксов можно использовать только справочники длиной 3, 2 и 1, убрав внешние два уровня. Для русской локализации:

=ЕСНД(ВПР(ПСТР(A1;2;6)+0;SIX;2;0);ЕСНД(ВПР(ПСТР(A1;2;4)+0;Four;2;0);ЕСНД(ВПР(ПСТР(A1;2;3)+0;Three;2;0);ЕСНД(ВПР(ПСТР(A1;2;2)+0;Two;2;0);ЕСНД(ВПР(ПСТР(A1;2;1)+0;One;2;0);"Не найдено")))))

Для английской локализации с запятыми между аргументами:

=IFNA(VLOOKUP(MID(A1,2,6)+0,SIX,2,0),IFNA(VLOOKUP(MID(A1,2,4)+0,Four,2,0),IFNA(VLOOKUP(MID(A1,2,3)+0,Three,2,0),IFNA(VLOOKUP(MID(A1,2,2)+0,Two,2,0),IFNA(VLOOKUP(MID(A1,2,1)+0,One,2,0),"Не найдено")))))

Ключевые моменты для успешного применения

  • Подготовьте именованные диапазоны для кодов каждой длины.
  • Храните исходный номер как текст в виде «+» и 6–15 цифр без пробелов, скобок и дефисов. Приведите номера с 00 и национальными префиксами к этому виду отдельно. Формула ниже предполагает, что ввод уже проверен; она не проверяет существование номера.
  • Используйте эту методику для очистки и анализа баз данных клиентов или логистических данных.

Этот подход демонстрирует мощь комбинации базовых функций Excel для решения нетривиальных задач анализа данных без использования макросов или сложных скриптов.

Поделиться:

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.