Alan은 데이터 유효성 검사를 사용하여 셀에 대한 유효한 선택 항목의 드롭 다운 목록을 만들 수 있습니다. 그러나 그가 실제로 필요한 것은 더 복잡합니다. 그는 관련 항목 코드와 함께 많은 항목 이름을 가지고 있습니다. B2 셀에서 모든 항목 이름 (교반기, 모터, 펌프, 탱크 등)을 표시하는 데이터 유효성 검사 목록을 만들 수 있습니다. 사용자는이 중 하나를 선택할 수 있습니다. 그러나 다른 곳에서 셀 B2를 참조 할 때 참조에서 반환되는 항목 이름이 아닌 항목 코드를 원합니다. 따라서 기준은 교반기, 모터, 펌프, 탱크 등 대신 A, M, P, TK 등을 반환합니다.

Excel에서이 작업을 직접 수행하는 방법은 없습니다. 그 이유는 데이터 유효성 검사 목록이 항목의 1 차원 목록 만 포함하도록 설정 되었기 때문입니다. 이렇게하면 목록에 항목 이름이 쉽게 포함될 수 있습니다.

그러나 데이터 유효성 검사 목록을 사용하는 방법을 조금 확장하여 원하는 것을 얻을 수 있습니다. 다음 단계를 따르십시오.

  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 [항목 이름 대신 항목 코드 반환].