Steve는 3035551212와 같이 A 열에 전화 번호를 가지고 있으며 처음 세 자리 (지역 번호)를보고 B 열에 해당 지역 번호와 관련된 주를 반환 할 수 있기를 원합니다. 그는 이것을 달성하는 가장 좋은 방법에 대해 궁금해합니다.

Excel에는 데이터를 조작하려고 할 때 작업을 더 쉽게 만드는 많은 기능이 있습니다. 이 경우 VLOOKUP 함수를 사용하여 지역 번호를 해당 상태에 일치시키는 것은 간단합니다.

원하는 정보를 검색하는 함수를 적용하기 전에 검색하려는 데이터가 포함 된 간단한 데이터 테이블을 만들어야합니다. 이 표에서는 워크 시트 내에서 지역 번호별로 정렬 된 고유 열에 지역 번호와 주가 나란히 있어야합니다. 예를 들어, F 열에 지역 번호를 입력하고 G 열에 해당 지역 번호에 대한 주를 입력 할 수 있습니다. 지역 번호와주는 여러 웹 사이트에서 찾을 수 있거나 원하는 경우 고유 한 표를 만들 수 있습니다.

두 개의 열에 데이터를 가져 오면 해당 열을 선택하고 선택한 범위의 이름을 만듭니다. (이름이 지정된 범위를 만드는 방법은 _ExcelTips._의 다른 문제에서 다루었습니다.) 예를 들어 범위 이름을 StateCodes와 같이 지정할 수 있습니다. 이 이름 지정은 반드시 필요한 것은 아니지만 조회 수식을 더 쉽게 사용할 수 있도록합니다.

전화 번호가 A1 셀에 있고 전화 번호 옆 열에 주 이름이 반환되기를 원한다고 가정하면 셀 B1에 다음과 같이 입력합니다.

=VLOOKUP(VALUE(LEFT(A1,3)),StateCodes,2,FALSE)

VALUE 및 LEFT 함수는 전화 번호에서 처음 세 문자 만 가져 오는 데 사용됩니다. 그런 다음 VLOOKUP 수식에 사용되어 StateCodes 테이블에서 지역 코드를 찾습니다. Excel은 지역 번호에 해당하는 주 이름을 반환합니다.

지역 코드 (조회에 필수적인)를 추출 할 수있는 또 다른 방법은 다음과 같이 FLOOR 함수를 사용하는 것입니다.

=VLOOKUP(FLOOR(A1/10000000,1),StateCodes,2,FALSE)

이 방법을 사용하려면 전화 번호를 숫자로 저장해야하므로 10,000,000으로 나눌 수 있습니다.

여기에 설명 된 접근 방식은 전화 번호가 항상 지정된 형식 (3035551212)이면 훌륭하게 작동합니다. 전화 번호의 형식이 다른 경우 (아마도 괄호와 대시를 사용하는 형식) 수식이 작동하지 않으므로 전화 번호에서 지역 코드가 실제로 어디에 있는지 확인하도록 조정해야합니다. 단일 형식이 아닌 전화 번호가있는 경우 모든 베팅이 해제되고 조회 작업이 훨씬 더 어려워집니다.

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (8063)은 Microsoft Excel 97, 2000, 2002 및 2003에 적용됩니다. 여기에서 Excel (Excel 2007 이상)의 리본 인터페이스에 대한이 팁의 버전을 찾을 수 있습니다.

link : / excelribbon-Determining_a_State_from_an_Area_Code [지역 번호에서 주 결정].