Excel의 VLOOKUP 함수와 COLUMN 함수 를 결합하여 데이터베이스 또는 데이터 테이블의 단일 행에서 여러 값을 반환 할 수있는 조회 수식을 만들 수 있습니다.
위의 그림에 표시된 예에서 조회 수식을 사용하면 다양한 하드웨어에 관련된 모든 값 (예 : 가격, 부품 번호 및 공급자)을 쉽게 반환 할 수 있습니다.
01 of 10
Excel VLOOKUP을 사용하여 여러 값 반환
아래 나열된 단계에 따라 단일 이미지에서 여러 값을 반환하는 위 이미지에 표시된 조회 수식이 만들어집니다.
조회 수식을 사용하려면 COLUMN 함수를 VLOOKUP 내부에 중첩해야합니다.
함수를 중첩하는 것은 첫 번째 함수의 인수 중 하나로 두 번째 함수를 입력하는 것입니다.
이 자습서에서는 열 함수를 VLOOKUP의 열 인덱스 번호 인수로 입력합니다.
튜토리얼의 마지막 단계는 조회 공식을 추가 열로 복사하여 선택한 부품의 추가 값을 검색하는 것입니다.
자습서 목차
- 자습서 데이터 입력
- 데이터 테이블의 명명 된 범위 만들기
- VLOOKUP 함수 시작
- 절대 셀 참조를 사용하여 조회 값 인수 입력하기
- 테이블 배열 인수 입력
- 중첩 된 COLUMN 함수 입력
- VLOOKUP 함수 완료
- 채우기 핸들을 사용하여 조회 수식 복사
- 조회 수식으로 데이터 검색
02 중 10
튜토리얼 데이터 입력
튜토리얼의 첫 번째 단계는 데이터 를 Excel 워크 시트 에 입력하는 것입니다.
튜토리얼의 단계를 따르려면 위의 이미지에 표시된 데이터를 다음 셀에 입력하십시오 .
- 셀 D1 ~ G1에 상위 데이터 범위 를 입력하십시오.
- 셀 D4에서 G10에 두 번째 범위 입력
이 자습서 중 작성된 검색 기준과 조회 수식은 워크 시트의 2 행에 입력됩니다.
이 자습서에는 이미지에 표시된 형식이 포함되어 있지 않지만 조회 방법 작동 방식에는 영향을 미치지 않습니다.
위에서 본 것과 유사한 형식 지정 옵션에 대한 정보는이 기본 Excel 서식 자습서 에서 사용할 수 있습니다.
자습서 단계
- 위 그림 에서처럼 데이터를 셀 D1에서 G10으로 입력하십시오.
03 / 10
데이터 테이블의 명명 된 범위 만들기
명명 된 범위 는 수식의 데이터 범위 를 쉽게 참조 할 수있는 방법입니다. 데이터에 대한 셀 참조 를 입력하는 대신 범위 이름을 입력하면됩니다.
명명 된 범위를 사용하는 두 번째 이점은 수식이 워크 시트의 다른 셀로 복사되는 경우에도이 범위의 셀 참조가 절대로 변경되지 않는다는 것입니다.
따라서 범위 이름은 절대 셀 참조 를 사용하여 수식을 복사 할 때 오류를 방지하는 대신 사용할 수 있습니다.
참고 : 범위 이름에는 데이터의 머리글 또는 필드 이름 (행 4)이 포함되지 않고 데이터 자체 만 포함됩니다.
자습서 단계
- 워크 시트에서 D5에서 G10 셀을 강조 표시하여 선택합니다.
- A 열 위에있는 이름 상자를 클릭하십시오.
- 이름 상자에 "표"(따옴표 제외)를 입력하십시오.
- 키보드에서 ENTER 키를 누릅니다.
- D5에서 G10 셀의 범위 이름은 이제 "테이블"입니다. VLOOKUP 테이블 배열 인수의 이름은 튜토리얼의 뒷부분에서 사용합니다.
04 중 10
VLOOKUP 대화 상자 열기
룩업 수식을 워크 시트의 셀에 직접 입력하는 것이 가능하지만 많은 사람들이 구문을 곧바로 유지하는 것을 어렵습니다. 특히이 자습서에서 사용하는 것과 같은 복잡한 수식의 경우 더욱 그렇습니다.
이 경우 VLOOKUP 대화 상자 를 사용하는 방법도 있습니다 . 거의 모든 Excel의 함수 에는 함수의 인수를 별도의 줄에 입력 할 수있는 대화 상자가 있습니다.
자습서 단계
- 워크 시트 의 셀 E2 - 2 차원 조회 공식의 결과가 표시되는 위치를 클릭합니다.
- 리본 메뉴 의 수식 탭을 클릭하십시오.
- 리본에서 찾아보기 및 참조 옵션을 클릭하여 기능 드롭 다운 목록을 엽니 다.
- 목록에서 VLOOKUP 을 클릭하여 함수의 대화 상자를 엽니 다.
05 중 10
절대 셀 참조를 사용하여 조회 값 인수 입력하기
일반적으로 조회 값 은 데이터 테이블의 첫 번째 열에 있는 데이터 필드 와 일치합니다.
이 예에서 조회 값 은 정보를 찾고자하는 하드웨어 부분의 이름을 나타냅니다.
조회 값에 허용되는 데이터 유형은 다음과 같습니다.
이 예에서는 부품 이름이 위치 할 셀 참조 (셀 D2)를 입력합니다.
절대 셀 참조
이 튜토리얼의 다음 단계에서 셀 E2의 조회 수식을 셀 F2와 G2로 복사합니다.
일반적으로 Excel에서 수식을 복사하면 셀 참조가 변경되어 새 위치가 반영됩니다.
이 경우 F2가 셀 F2와 G2에서 오류를 생성하여 수식이 복사됨에 따라 D2 ( 조회 값 의 셀 참조)가 변경됩니다.
오류를 방지하기 위해 셀 참조 D2를 절대 셀 참조 로 변환합니다.
절대 셀 참조는 수식이 복사 될 때 변경되지 않습니다.
절대 셀 참조는 키보드의 F4 키를 눌러 생성됩니다. 이렇게하면 $ D $ 2 와 같은 셀 참조 주위에 달러 기호가 추가됩니다.
자습서 단계
- 대화 상자 에서 lookup_value 행을 클릭하십시오.
- 이 셀 참조를 lookup_value 행에 추가하려면 D2 셀을 클릭하십시오. 이것은 우리가 정보를 찾고있는 부품 이름을 입력 할 셀입니다.
- 삽입 점을 이동하지 않고 D2를 절대 셀 참조 $ D $ 2로 변환하려면 키보드의 F4 키를 누릅니다.
- 튜토리얼의 다음 단계에서 VLOOKUP 함수 대화 상자를 열어 둡니다.
06 중 10
테이블 배열 인수 입력
테이블 배열은 룩업 수식이 원하는 정보를 찾기 위해 검색하는 데이터 테이블 입니다.
테이블 배열에는 적어도 두 개의 데이터 열이 포함되어야합니다.
- 첫 번째 열에 는 조회 값 인수가 들어 있습니다 (자습서의 이전 단계)
- 두 번째 및 추가 열은 조회 수식에서 검색하여 지정한 정보를 찾습니다.
테이블 배열 인수는 데이터 테이블에 대한 셀 참조 를 포함하는 범위 또는 범위 이름 으로 입력해야합니다.
이 예제에서는 튜토리얼의 3 단계에서 만든 범위 이름을 사용합니다.
자습서 단계
- 대화 상자 에서 table_array 행을 클릭하십시오.
- 이 인수의 범위 이름을 입력하려면 "표"(따옴표 제외)를 입력하십시오.
- 튜토리얼의 다음 단계에서 VLOOKUP 함수 대화 상자를 열어 둡니다.
07 중 10
COLUMN 함수 중첩
일반적으로 VLOOKUP은 데이터 테이블의 한 열에 있는 데이터 만 반환하며이 열은 열 인덱스 번호 인수로 설정됩니다.
그러나이 예에서는 데이터를 반환하려는 3 개의 열이 있으므로 조회 수식을 편집하지 않고도 열 인덱스 번호 를 쉽게 변경할 수있는 방법이 필요합니다.
이것은 COLUMN 함수가 들어오는 곳입니다. 열 인덱스 번호 인수로 입력하면 조회 수식이 나중에 튜토리얼의 D2 셀에서 E2 및 F2 셀로 복사됨에 따라 변경됩니다.
중첩 기능
따라서 COLUMN 함수는 VLOOKUP의 열 인덱스 번호 인수로 사용 됩니다.
이 작업은 대화 상자의 Col_index_num 행에 VLOOKUP 내부에 COLUMN 함수를 중첩하여 수행합니다.
COLUMN 기능 수동 입력
함수를 중첩 할 때 Excel에서는 두 번째 함수의 대화 상자 를 열어 인수를 입력 할 수 없습니다.
따라서 COLUMN 함수는 Col_index_num 행에 수동으로 입력해야합니다.
COLUMN 함수는 단 하나의 인수, 즉 셀 참조 인 Reference 인수 만 갖습니다.
COLUMN 함수의 참조 인수 선택
COLUMN 함수의 작업은 Reference 인수로 주어진 열의 수를 리턴하는 것입니다.
즉, 열 문자를 첫 번째 열인 A 열, 두 번째 열 B 열 등과 같이 숫자로 변환합니다.
리턴 된 데이터의 첫 번째 필드는 항목의 가격입니다.이 항목은 데이터 테이블의 두 번째 열에 있습니다. 열 B의 모든 셀에 대한 셀 참조를 참조 인수로 선택하여 숫자 2를 가져올 수 있습니다. Col_index_num 인수
자습서 단계
- VLOOKUP 함수 대화 상자에서 Col_index_num 행을 클릭하십시오.
- 함수 이름 열 뒤에 열린 대괄호 " ( "
- 셀 참조를 워크 시트 에서 참조 인수로 입력하려면 B1 셀을 클릭하십시오.
- 닫는 둥근 괄호 " ) "를 입력하여 COLUMN 함수를 완성하십시오.
- 튜토리얼의 다음 단계에서 VLOOKUP 함수 대화 상자를 열어 둡니다.
08 중 10
VLOOKUP 범위 조회 인수 입력
VLOOKUP의 Range_lookup 인수는 VLOOKUP이 Lookup_value와 정확히 일치하거나 대략 일치하는 항목을 찾을 지 여부를 나타내는 논리 값입니다 (TRUE 또는 FALSE 만 해당).
- TRUE이거나이 인수 가 생략 된 경우 VLOOKUP은 Lookup_value와 정확히 일치하는 항목을 반환하거나 정확히 일치하는 항목이 없으면 VLOOKUP은 다음으로 큰 값을 반환합니다. 이 작업을 수행하는 수식의 경우 Table_array의 첫 번째 열에있는 데이터 를 오름차순으로 정렬 해야합니다.
- FALSE이면 VLOOKUP은 Lookup_value와 정확히 일치하는 것을 사용합니다. 조회 값과 일치하는 Table_array의 첫 번째 열에 둘 이상의 값이있는 경우 발견 된 첫 번째 값이 사용됩니다. 정확히 일치하는 것이 없으면 # N / A 오류가 반환됩니다.
이 튜토리얼에서는 특정 하드웨어 항목에 대한 특정 정보를 찾고 있으므로 Range_lookup을 False로 설정합니다.
자습서 단계
- 대화 상자 에서 Range_lookup 라인을 클릭하십시오.
- 이 행에 False 라는 단어를 입력하면 VLOOKUP에서 찾고자하는 데이터와 정확히 일치하는 문자열을 반환하도록 지정합니다.
- 확인을 클릭하여 조회 수식을 완료하고 대화 상자를 닫습니다.
- 셀 D2에 조회 기준을 아직 입력하지 않았으므로 # N / A 오류 가 셀 E2에 나타납니다.
- 튜토리얼의 마지막 단계에서 조회 기준을 추가 할 때이 오류가 수정됩니다.
09/10
채우기 핸들을 사용하여 조회 수식 복사
조회 수식은 한 번에 데이터 테이블의 여러 열 에서 데이터 를 검색하기위한 것입니다.
이렇게하려면 정보를 원하는 모든 필드에 조회 수식이 있어야합니다.
이 튜토리얼에서는 부품 번호를 Lookup_value로 입력 할 때 가격, 부품 번호 및 공급 업체 이름 인 데이터 테이블의 2, 3, 4 열에서 데이터를 검색하려고합니다.
데이터는 워크 시트 의 정규 패턴으로 배치되므로 셀 E2의 조회 수식을 셀 F2와 G2로 복사 할 수 있습니다.
수식이 복사되면 Excel은 수식의 새 위치를 반영하도록 COLUMN 함수 (B1)에서 상대 셀 참조 를 업데이트합니다.
또한 Excel에서는 절대 셀 참조 $ D $ 2가 변경되지 않고 수식이 복사 될 때 명명 된 범위 테이블 이 복사됩니다.
Excel에서 데이터를 복사하는 방법은 여러 가지가 있지만 가장 쉬운 방법은 채우기 핸들 을 사용하는 것입니다.
자습서 단계
- 룩업 수식이있는 셀 E2를 클릭하여 셀을 활성 셀로 만듭니다.
- 마우스 포인터를 오른쪽 아래 모서리의 검정색 사각형 위에 놓습니다. 포인터가 더하기 기호 " + "로 바뀝니다. 이것은 채우기 핸들입니다.
- 마우스 왼쪽 버튼을 클릭하고 채우기 핸들을 셀 G2로 드래그합니다.
- 마우스 버튼을 놓으면 셀 F3에 2 차원 조회 공식이 있어야합니다.
- 올바르게 완료되면 F2 및 G2 셀에 E2 셀에있는 # N / A 오류가 포함됩니다.
10/10
조회 기준 입력
조회 수식이 필요한 셀 에 복사되면 데이터 표에서 정보를 검색하는 데 사용할 수 있습니다.
이렇게하려면 검색 할 항목의 이름을 Lookup_value 셀 (D2)에 입력하고 키보드에서 Enter 키를 누릅니다.
완료되면 조회 수식을 포함하는 각 셀에는 검색중인 하드웨어 항목에 대한 다른 데이터 조각이 있어야합니다.
자습서 단계
- 워크 시트에서 셀 D2를 클릭하십시오.
- 셀 D2에 위젯 을 입력 하고 키보드에서 Enter 키를 누릅니다.
- 다음 정보는 E2에서 G2 셀에 표시되어야합니다.
- E2 - 14.76 달러 - 위젯 가격
- F2 - PN-98769 - 위젯의 부품 번호
- G2 - Widgets Inc. - 위젯 공급 업체의 이름
- 셀 D2에 다른 부분의 이름을 입력하고 셀 E2에서 G2로 결과를 관찰하여 VLOOKUP 배열 수식을 더 테스트합니다
#REF! 셀 E2, F2 또는 G2에 나타나면 VLOOKUP 오류 메시지 목록을 통해 문제가있는 위치를 확인할 수 있습니다.