Excel에서 배열 수식 을 사용하여 여러 기준을 사용하여 데이터베이스 또는 데이터 테이블에서 정보를 찾는 조회 수식을 만들 수 있습니다.
배열 수식에는 INDEX 함수 내에 MATCH 함수가 중첩되어 있습니다.
이 자습서에는 여러 기준을 사용하여 샘플 데이터베이스에서 티타늄 위젯 공급자를 찾는 조회 수식을 작성하는 단계별 예제가 포함되어 있습니다.
아래의 튜토리얼 토픽의 단계에 따라 위의 이미지에서 볼 수있는 공식을 작성하고 사용하는 방법을 안내합니다.
01 of 09
자습서 데이터 입력
튜토리얼의 첫 번째 단계는 데이터 를 Excel 워크 시트 에 입력하는 것입니다.
튜토리얼의 단계를 따르려면 위의 이미지에 표시된 데이터를 다음 셀에 입력하십시오 .
- 셀 D1 ~ F2에 상위 데이터 범위 를 입력하십시오.
- 셀 D5에서 F11에 두 번째 범위를 입력하십시오.
이 자습서에서 생성 된 배열 수식 을 수용하기 위해 행 3과 4는 공백으로 남습니다.
이 자습서에는 이미지에 표시된 형식이 포함되어 있지 않지만 조회 방법 작동 방식에는 영향을 미치지 않습니다.
위에서 본 것과 유사한 형식 지정 옵션에 대한 정보는이 기본 Excel 서식 자습서에서 사용할 수 있습니다.
02 of 09
INDEX 함수 시작
INDEX 함수는 여러 양식이있는 Excel의 소수 중 하나입니다. 이 함수 에는 배열 양식 과 참조 양식이 있습니다.
Array Form은 데이터베이스 또는 데이터 테이블에서 실제 데이터를 반환하고 Reference Form은 셀 참조 또는 테이블의 데이터 위치를 제공합니다.
이 튜토리얼에서는 데이터베이스의 공급자에 대한 셀 참조가 아닌 티타늄 위젯의 공급자 이름을 알고 싶어하므로 Array Form을 사용합니다.
각 양식에는 기능을 시작하기 전에 선택해야하는 인수 목록이 있습니다.
자습서 단계
- F3 셀을 클릭하여 활성 셀로 만듭니다. 여기서 중첩 된 함수를 입력합니다.
- 리본 메뉴의 수식 탭을 클릭하십시오.
- 리본에서 조회 및 참조 를 선택하여 기능 드롭 다운 목록을 엽니 다.
- 목록에서 INDEX 를 클릭하여 Select Arguments 대화 상자 를 불러옵니다.
- 대화 상자에서 row_num, col_num 옵션을 선택하십시오.
- 확인을 클릭하여 INDEX 함수 대화 상자를 엽니 다.
03 of 09
INDEX 함수 배열 인수 입력
첫 번째 인수 는 Array 인수입니다. 이 인수는 원하는 데이터를 검색 할 셀 범위 를 지정합니다.
이 자습서에서는이 인수가 샘플 데이터베이스가 됩니다.
자습서 단계
04 of 09
중첩 된 MATCH 함수 시작
한 함수 를 다른 함수 안에 중첩하는 경우 두 번째 또는 중첩 된 함수의 대화 상자 를 열어 필요한 인수 를 입력 할 수 없습니다.
중첩 된 함수는 첫 번째 함수의 인수 중 하나로 입력해야합니다.
이 자습서에서는 중첩 된 MATCH 함수와 해당 인수가 INDEX 함수 대화 상자의 두 번째 행 인 Row_num 행에 입력됩니다.
함수를 수동으로 입력 할 때 함수의 인수는 쉼표 "," 로 구분됩니다.
MATCH 함수의 Lookup_value 인수 입력
중첩 된 MATCH 함수를 입력하는 첫 번째 단계는 Lookup_value 인수를 입력하는 것입니다.
Lookup_value 는 데이터베이스에서 검색하려는 검색어의 위치 또는 셀 참조 입니다.
일반적으로 Lookup_value 는 하나의 검색 기준 또는 용어 만 허용합니다. 여러 기준을 검색하려면 Lookup_value 를 확장해야합니다.
이 작업은 앰퍼샌드 기호 " & "를 사용하여 두 개 이상의 셀 참조를 연결 하거나 결합하여 수행됩니다.
자습서 단계
- INDEX 함수 대화 상자에서 Row_num 행을 클릭하십시오.
- 함수 이름에 일치하는 항목을 입력 한 다음 열린 둥근 괄호 " ( "
- 셀 참조를 대화 상자에 입력하려면 D3 셀을 클릭하십시오.
- 두 번째 셀 참조를 추가하려면 셀 참조 D3 뒤에 앰퍼샌드 " & "를 입력하십시오.
- 이 셀 참조를 대화 상자에 입력하려면 E3 셀을 클릭하십시오.
- 셀 참조 E3 뒤에 쉼표 "," 를 입력하여 MATCH 함수의 Lookup_value 인수 항목을 완료하십시오.
- 자습서의 다음 단계에서 INDEX 함수 대화 상자를 열어 두십시오.
튜토리얼의 마지막 단계에서 Lookup_values가 워크 시트의 셀 D3 및 E3에 입력됩니다.
09 년 5 월
MATCH 함수에 Lookup_array 추가
이 단계에서는 중첩 된 MATCH 함수에 Lookup_array 인수 를 추가하는 방법에 대해 설명합니다.
Lookup_array 는 MATCH 함수가 자습서의 이전 단계에서 추가 한 Lookup_value 인수를 찾기 위해 검색 할 셀 범위입니다.
Lookup_array 인수에 두 개의 검색 필드를 식별 했으므로 Lookup_array에 대해 동일한 작업을 수행해야합니다. MATCH 함수는 지정된 각 용어에 대해 하나의 배열 만 탐색합니다.
여러 배열을 입력하기 위해 우리는 다시 배열을 연결 하기 위해 앰퍼샌드 " & "를 사용합니다.
자습서 단계
이 단계는 INDEX 함수 대화 상자 의 Row_num 행에 이전 단계에서 입력 한 쉼표 뒤에 입력해야합니다.
- 쉼표 뒤에 Row_num 행을 클릭하여 현재 항목의 끝에 삽입 포인터를 놓습니다 .
- 범위를 입력하려면 워크 시트의 D6에서 D11 셀을 강조 표시하십시오. 이 함수가 검색 할 첫 번째 배열입니다.
- 셀 참조 D6 : D11 다음에 앰퍼샌드 " & "를 입력하십시오. 함수가 두 개의 배열을 검색하기를 원하기 때문입니다.
- 범위를 입력하려면 워크 시트에서 E6에서 E11 셀을 강조 표시하십시오. 함수가 검색 할 두 번째 배열입니다.
- MATCH 함수의 Lookup_array 인수 항목을 완료하려면 셀 참조 E3 뒤에 쉼표 "," 를 입력하십시오.
- 자습서의 다음 단계에서 INDEX 함수 대화 상자를 열어 두십시오.
06 년 6 월
일치 유형 추가 및 MATCH 함수 완료
MATCH 함수의 세 번째이자 마지막 인수 는 Match_type 인수입니다.
이 인수는 Lookup_value와 Lookup_array의 값을 일치시키는 방법을 Excel에 알려줍니다. 선택 항목은 1, 0 또는 -1입니다.
이 인수는 선택 사항입니다. 생략되면 함수는 기본값 1을 사용합니다.
- Match_type = 1 또는 생략 된 경우 : MATCH는 Lookup_value보다 작거나 같은 최대 값을 찾습니다. Lookup_array 데이터는 오름차순으로 정렬 되어야합니다.
- match_type = 0 인 경우 : MATCH는 Lookup_value와 정확히 일치하는 첫 번째 값을 찾습니다. Lookup_array 데이터는 순서에 관계없이 정렬 할 수 있습니다.
- Match_type = -1 : MATCH가 Lookup_value보다 크거나 같은 가장 작은 값을 찾습니다. Lookup_array 데이터는 내림차순으로 정렬해야합니다.
자습서 단계
이 단계는 INDEX 함수 대화 상자 의 Row_num 행에 이전 단계에서 입력 한 쉼표 뒤에 입력해야합니다.
- Row_num 행의 쉼표 다음에 " 0 "을 입력하십시오. 중첩 된 함수가 셀 D3 및 E3에 입력하는 항 목과 정확히 일치하는 항목을 반환하기를 원하기 때문입니다.
- 닫기 대괄호 " ) "를 입력하여 일치 기능을 완료하십시오.
- 자습서의 다음 단계에서 INDEX 함수 대화 상자를 열어 두십시오.
07 09
INDEX 기능으로 돌아 가기
MATCH 함수가 완료되었으므로 열린 대화 상자 의 세 번째 행으로 이동하여 INDEX 함수의 마지막 인수 를 입력합니다.
이 세 번째이자 마지막 인수는 Column_num 인수로 Excel에 D6에서 F11 범위 의 열 번호를 알려주며 함수에서 반환하려는 정보를 찾습니다. 이 경우, 티타늄 위젯을 공급 합니다 .
자습서 단계
- 대화 상자에서 Column_num 행을 클릭하십시오.
- D6에서 F11 범위의 세 번째 열에있는 데이터를 찾고 있으므로이 행에 3 번 " 3 "(따옴표 제외)을 입력하십시오.
- 확인을 누르거나 인덱스 기능 대화 상자를 닫지 마십시오. 이 튜토리얼의 다음 단계 인 배열 수식 작성을 위해 열려 있어야합니다.
08 년 9 월
배열 수식 만들기
대화 상자를 닫기 전에 중첩 된 함수를 배열 수식 으로 변환해야합니다.
배열 수식을 사용하면 데이터 테이블에서 여러 용어를 검색 할 수 있습니다. 이 튜토리얼에서는 열 1의 위젯과 열 2의 티타늄이라는 두 가지 용어를 비교하려고합니다.
Excel에서 배열 수식을 만들 려면 동시에 키보드의 CTRL , SHIFT 및 ENTER 키를 누릅니다 .
이 키들을 함께 누르는 효과는 함수를 중괄호 {}로 둘러 싸서 배열 수식임을 나타냅니다.
자습서 단계
- 이 자습서의 이전 단계에서 완성 된 대화 상자를 열어 둔 상태에서 키보드의 CTRL 및 SHIFT 키를 누른 상태에서 ENTER 키를 눌렀다 놓습니다.
- 올바르게 끝나면 대화 상자가 닫히고 # F3 셀에 함수가 입력 된 셀에 # N / A 오류가 나타납니다.
- D3 및 E3 셀이 비어 있기 때문에 # N / A 오류가 셀 F3에 나타납니다. D3 및 E3은이 튜토리얼의 5 단계에서 Lookup_values를 찾기 위해 함수에 지시 한 셀입니다. 데이터가이 두 셀에 추가되면 오류가 데이터베이스의 정보로 대체됩니다.
09 09
검색 기준 추가
이 자습서의 마지막 단계는 검색 용어를 워크 시트에 추가하는 것입니다.
이전 단계에서 언급했듯이 열 1의 위젯 과 열 2의 티타늄 이라는 용어를 비교하고자합니다.
수식에서 데이터베이스의 해당 열에있는 두 용어가 일치하는 경우에만 세 번째 열의 값이 반환됩니다.
자습서 단계
- D3 셀을 클릭하십시오.
- 위젯 을 입력 하고 키보드의 Enter 키를 누릅니다.
- E3 셀을 클릭하십시오.
- Titanium 을 입력 하고 키보드의 Enter 키를 누릅니다.
- 공급 업체의 이름 Widgets Inc. 는 F3 셀에 표시되어야합니다. 이는 Titanium 위젯을 판매하는 유일한 공급 업체이기 때문에 함수의 위치입니다.
- 셀 F3을 클릭하면 완전한 함수
{= INDEX (D6 : F11, MATCH (D3 & E3, D6 : D11 & E6 : E11, 0), 3}}
워크 시트 위의 수식 입력 줄에 나타납니다.
참고 : 이 예에서는 티타늄 위젯에 대한 공급 업체는 하나뿐입니다. 둘 이상의 공급 업체가있는 경우 데이터베이스에서 처음 나열된 공급 업체가이 함수에 의해 반환됩니다.