Сложные формулы поиска (Microsoft Excel)

У Эдди есть серия номеров деталей в формате 123/45678 или 011/00345.

Ему нужно найти название соответствующего номера детали из другой таблицы. Эта таблица состоит из трех столбцов. Первый столбец содержит часть номера детали перед косой чертой (например, 123 или 011), а второй столбец содержит часть после косой черты (например, 45678 или 00345). Третий столбец содержит имя, которое Эдди хочет найти.

Отчасти проблема Эдди заключается в том, что в таблице два столбца, каждый из которых содержит часть номера детали, являются числовыми значениями. Таким образом, полный номер детали не 011 и 00345, а 11 и 345.

Эдди задается вопросом, как составить формулу поиска для номеров деталей (123/45678 или 011/00345), чтобы получить правильное название детали из таблицы.

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

Если вы уже ввели значение 011 до форматирования столбца как текста, оно все равно будет отображаться как 11 (без нуля в начале). Вам нужно будет не только изменить формат столбца, но и повторно ввести все фрагменты с номерами деталей, которые содержат ведущие нули.

Затем вам нужно убедиться, что ваша таблица номеров деталей включает заголовки столбцов. В этом примере убедитесь, что три заголовка столбца: Left, Right и Name. (Вы можете сделать эти заголовки полужирным и подчеркнутым шрифтом, что поможет отделить их от значений в каждом столбце.) Затем создайте имена для отдельных столбцов, выполнив следующие действия:

Подготовив таблицу номеров деталей, вы можете искать номера деталей. В ячейках справа от значений поиска (123/45678 и 011/00345) вы собираетесь ввести формулу массива. Предполагая, что номер первой части находится в ячейке A1, вы должны ввести следующую формулу массива в ячейку B1:

Помните, что для обозначения этого как формулы массива вы вводите его, нажимая Shift + Ctrl + Enter. После этого формула появится на панели формул с \ {фигурными скобками} вокруг нее. При необходимости вы можете скопировать формулу в другие ячейки столбца B.

Формула работает путем извлечения значения из столбца «Имя» таблицы, когда соответствующее значение в левом столбце объединено с косой чертой, а значение в правом столбце совпадает с содержимым ячейки A1. Если совпадений нет, формула возвращает значение ошибки # Н / Д, в противном случае возвращается желаемый номер детали.

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

Обратите внимание, что эта версия формулы (которую все равно следует вводить как формулу массива) использует функцию ТЕКСТ в двух местах, чтобы преобразовать значения из левого и правого столбцов, чтобы они имели ведущие нули.

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

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