Получение номера телефона с известными именем и фамилией

У Кимма есть рабочий лист с тремя столбцами. Столбец A содержит фамилию человека, столбец B содержит имя, а столбец C содержит номер телефона человека. Если Кимм знает имя и фамилию человека (скажем, они находятся в ячейках F1 и F2 соответственно), она задается вопросом, какую формулу поиска она бы использовала, чтобы вернуть номер телефона для первого человека, совпадающего с этим именем и фамилией.

На самом деле существует несколько различных формул, которые можно использовать для определения номера телефона. Большинство подходов включают использование формул массива, которые всегда вводятся в ячейку с помощью Ctrl + Shift + Enter . Чтобы формулы было легче понять, лучше всего работать с именованными диапазонами. Например, установите следующие имена:

  • Выберите все фамилии в столбце A и дайте им имя, например LNames.
  • Выделите все имена в столбце B и дайте им имя, например FNames.
  • Выберите все номера телефонов в столбце C и дайте им имя, например Phones.

Вот несколько формул массива, которые можно использовать для поиска номера телефона:

 = SUMPRODUCT (- (LNames = F2), - (  FNames = F1), Phones) = INDEX (телефоны, MATCH (F1 и F2, FNames и LNames,)) = INDEX (телефоны, INDEX (MATCH (F1 и F2, FNames и LNames, 0),)) = OFFSET (C1, MATCH (F1 и F2, FNames и LNames,)  ,) 

Кроме того, вы можете создать формулу массива, основанную на функции СТРОКА, как показано здесь:

 = INDEX (Phones, SUMPRODUCT ((F1 & F2 =  FNames & LNames) * (ROW (FNames) -1))) = INDEX (Phones, MIN (IF ((FNames = F1) * (LNames = F2), (ROW (Phones) -1)))) 

Если вы используете этот подход (полагаясь на функцию ROW), вам может потребоваться отрегулировать часть формулы «-1», чтобы отразить количество строк, которые ppear перед вашими фактическими данными. В этом случае строка уменьшается на единицу, потому что первая строка таблицы данных состоит из заголовков для каждого столбца; сами данные начинаются в строке 2.

Обратите внимание, что все формулы, использованные до сих пор, основаны на сочетании имени с фамилией для проведения сравнения. В некоторых случаях это может привести к «ложным срабатываниям». Например, предположим, что в ваших данных есть два имени – Том Астонфилд и Томас Тонфилд. Поскольку регистр букв в именах не имеет значения в этих формулах, когда вы объединяете имена и фамилии этих людей, они полностью совпадают. Таким образом, если вы ищете номер телефона Томаса Тонфилда и его имя появляется в списке после Тома Астонфилда, то вы всегда будете получать номер телефона Тома вместо номера Томаса.

эта потенциальная проблема, вы можете включить какой-то разделитель между именем и фамилией. Используя одну из формул, приведенных ранее в этом совете, все, что вам нужно сделать, это внести корректировку, как показано здесь:

 = INDEX (Phones, SUMPRODUCT ((F1 & ":" & F2 = FNames &  ":" & LNames) * (ROW (FNames) -1))) 

Добавление двоеточия между именем и фамилией действует как разделитель, устраняя вероятность ложных срабатываний.

Вы также должны понимать, что если ячейки F1 и F2 содержат имя, которое вообще не отображается в данных, информация, полученная вами из формулы, будет ошибочной. В зависимости от варианта используемой функции вы можете вернуть фактическое состояние ошибки (например, # N/A или #REF) или вы можете получить обратно неверные данные. В тех случаях, когда возвращается условие ошибки, вы можете изменить формулу, чтобы учесть возможность не найти совпадение, следующим образом:

 = IF (ISERROR (INDEX (Phones,  MATCH (F1 & ":" & F2, FNames & ":" & LNames, 0))), "no phone", INDEX (Phones, MATCH (F1 & ":" & F2, FNames & ":" & LNames, 0))) 

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

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

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

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

Оцените статью
Frestage.ru
Добавить комментарий