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

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

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

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

  • Выберите все номера телефонов в столбце C и дайте им имя, например «Телефоны».

Вот несколько формул массива, с помощью которых можно найти номер телефона:

=SUMPRODUCT(--(LNames=F2),--(FNames=F1),Phones)

=INDEX(Phones,MATCH(F1&F2,FNames&LNames,))

=INDEX(Phones,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))))

Если вы используете этот подход (полагаясь на функцию СТРОКА), вам может потребоваться настроить часть формул «-1», чтобы отразить количество строк, которые появляются перед фактическими данными. В этом случае строка уменьшается на единицу, потому что первая строка таблицы данных состоит из заголовков для каждого столбца; сами данные начинаются в строке 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 в качестве решения, поскольку для этого потребуется добавление таблицы критериев.

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

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

Этот совет (10478) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Pulling_a_Phone_Number_with_a_Known_First_and_Last_Name [Получение номера телефона с известными именем и фамилией].