Алан может использовать проверку данных для создания раскрывающегося списка допустимых вариантов для ячейки. Однако то, что ему действительно нужно, более сложное. У него есть большое количество наименований предметов с соответствующими кодами предметов. В ячейке B2 он может создать список проверки данных, в котором будут показаны все наименования элементов (мешалка, двигатель, насос, резервуар и т. Д.). Затем пользователь может выбрать один из них. Однако, когда он ссылается на ячейку B2 в другом месте, он хочет, чтобы код элемента, а не имя элемента, возвращался ссылкой. Таким образом, ссылка будет возвращать A, M, P, TK и т. Д. Вместо мешалки, двигателя, насоса, резервуара и т. Д.

В Excel нет прямого способа сделать это. Причина в том, что списки проверки данных настроены так, чтобы включать только одномерный список элементов. Это упрощает включение в список имен ваших элементов.

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

  1. Где-нибудь справа от данных рабочего листа настройте таблицу данных.

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

  1. Выделите ячейки, содержащие названия ваших элементов. (Не выбирайте коды предметов, только названия.)

  2. Откройте вкладку «Формулы» на ленте.

  3. Щелкните инструмент «Определить имя» в группе «Определенные имена». Excel отображает диалоговое окно «Новое имя». (См. Рис. 1.)

  4. В поле Имя введите описательное имя, например ItemNames. . Нажмите ОК, чтобы добавить имя и закрыть диалоговое окно.

  5. Выберите ячейку B2 (ячейку, в которой вы хотите получить список проверки).

  6. Откройте вкладку «Данные» на ленте.

  7. Щелкните инструмент Проверка данных в группе Инструменты для работы с данными. Excel отображает диалоговое окно «Проверка данных». (См. Рис. 2.)

  8. В раскрывающемся списке Разрешить выберите Список.

  9. В поле «Источник» введите знак равенства, за которым следует имя, которое вы определили на шаге 5 (например, = ItemNames).

  10. Щелкните ОК.

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

=VLOOKUP(B2,OFFSET(Itemlist,0,0,,2),2,FALSE)

Эту формулу можно использовать отдельно (чтобы поместить желаемый код элемента в ячейку) или ее можно использовать в более крупной формуле в любом месте, где вы изначально ссылались на B2.

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

=INDEX({"A","M","P","TK"},MATCH(B2,{"agitator","motor","pump","tank"},0))

Как и все формулы массива, вы вводите эту формулу, нажимая Ctrl + Shift + Enter. Самым большим недостатком этого метода является то, что постоянно обновлять формулу становится неудобно, и существует «предел жизнеспособности» того, сколько пар кодов и элементов можно включить в формулу. (Предел определяется длиной формулы, поэтому он зависит от длины имен ваших элементов.) Кроме того, этот подход хорош только для возврата кода элемента в другой ячейке, а не для включения его как части большей формулы.

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

Этот совет (12078) применим к Microsoft Excel 2007, 2010, 2013 и 2016.

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

link: / excel-Returning_Item_Codes_Instead_of_Item_Names [Возвращение кодов предметов вместо имен предметов].