스프레드 시트가 클수록 INDEX 및 MATCH 함수가 더 많이 필요합니다. SUM, AVERAGE 및 MAX와 결합하여 검색 범위를 좁히십시오.

피벗 테이블은 사용자가 관계형 데이터베이스를 만들 수 있도록 개발되었습니다. 쿼리하기 쉽고, 메모리를 덜 사용하며,보다 정확한 결과를 제공합니다. 그러나 관계형 데이터베이스가 없거나 필요하지 않지만보다 강력하고 신뢰할 수있는 데이터베이스 기능이 필요한 경우 시작하십시오.

[추가 정보 : 새 PC에 15 개의 무료로 제공되는 우수한 프로그램이 필요합니다.]

인덱스, 매치 및 인덱스 일치

Excel에서 INDEX 함수는 특정 위치 (목록, 테이블, 데이터베이스)에서 항목을 반환합니다.

MATCH 함수는 값의 위치를 ​​반환합니다 , 테이블, 데이터베이스). 그리고 INDEX-MATCH 함수를 함께 사용하면 테이블에서 데이터를 추출 할 수 있습니다.

INDEX 함수 구문은 INDEX (array, row_num, [column_num])입니다. 배열은 작업중인 셀 범위입니다. Row_num은 분명히 찾고있는 데이터가 포함 된 범위의 행 번호입니다. Column_num은 찾고있는 데이터가 포함 된 범위의 열 번호입니다. INDEX 수식은 열 문자를 인식하지 않으므로 숫자를 사용해야합니다 (왼쪽에서부터 계산).

MATCH 함수 구문은 MATCH (lookup_value, lookup_array, [match_type])입니다. lookup_value는 찾고자하는 숫자 또는 텍스트이며, 값, 논리 값 또는 셀 참조 일 수 있습니다. lookup_array는 작업중인 셀 범위입니다. match_type은 일치 함수 또는 정확히 일치 또는 가장 가까운 일치를 결정합니다.

A. INDEX 함수

이 예에서 제임스 노 링턴 제독 제독은 카리브 해의 모든 해적선을 추적하는 스프레드 시트를 가지고 있습니다. Norrington의 목록은 해당 지역의 항해 차트와 일치하는 배의 전투 조직에 의해 정렬됩니다. 그는 선박이 전진하는 것을 보았을 때 지수 공식을 스프레드 시트에 입력하여 배와 용량을 식별 할 수 있습니다. 이 첫 번째 쿼리에서, Norrington은 앞으로 나아갈 우주선의 유형을 알고 싶어합니다.

1. 쿼리 (즉, 함수 및 결과)에 대한 위치 (셀 또는 셀 범위)를 선택한 다음 커서를 해당 셀로 이동하십시오. 예 : 행 18에있는 셀.

2. INDEX 함수 (등호 앞에)와 여는 괄호를 입력 한 다음 다음과 같이 데이터베이스 / 테이블 범위를 강조 표시하거나 입력하십시오. = INDEX (A2 : I16

참고 : 절대 참조 ,이 경우에는 수식을 하드 코딩하여 복사 할 때 범위가 변경되지 않음), 각 셀 참조 후 F4 키를 한 번 누릅니다. 범위를 강조 표시 할 수도 있습니다. 전체 범위를 선택한 후 F4 키를 한 번만 누릅니다. 전체 절대 참조 기호가 추가됩니다.

3. 다음으로 쉼표를 입력하여 인수 (즉, 별도 수식)를 구분 한 다음 행 번호와 쉼표를 입력 한 다음 열 번호를 입력하십시오 (예 , 숫자 여야하고 일반적인 열 문자가 아님) 및 오른쪽 괄호 (또는 Enter 키를 누르고 Excel에서 끝 괄호를 추가하게합니다.) 완전한 수식은 다음과 같습니다. = INDEX ($ A $ 2 : $ I $ 16 , 15,2).

참고 : 행 번호 매기기는 스프레드 시트의 첫 번째 숫자가 아니라 범위의 첫 번째 숫자로 시작됩니다 (예 : 이브 n 비록 Cavalleria 해적선이 Excel 행 16에 있지만, 우리의 범위가 A2에서 시작하고 I16을 통과하기 때문에 실제로 행 15입니다. A2가 1 열이면 A16은 15 열입니다.)

4. Norrington이 찾고 있던 우주선의 유형이 War Sloop인지 확인하십시오.

JD Sartain

데이터베이스에서 특정 정보를 찾으려면 index 함수를 사용하십시오. INDEX 범위

이제이 데이터베이스 테이블을 사용하여 더 많은 작업을 수행 할 수 있습니다. 범위를 다시 정의 할 필요가 없습니다. 쉽게하기 위해 범위를 한 번 정의한 다음 이름을 지정합니다. 그러면 수식에 범위 이름을 넣을 수 있습니다.

1. A2로 이동하여 A2에서 I16까지 범위를 강조 표시합니다.

2. 수식 탭에서 정의 된 이름 그룹에서

이름 정의

를 선택하십시오.

3. 팝업 대화 상자에서 이름 필드 상자에 범위의 이름을 입력하십시오. 4. 그런 다음 통합 문서 또는 통합 문서의 워크 시트 중 하나 인 범위 (범위가있는 위치)를 입력합니다. 5. 필요한 경우 의견을 입력하십시오.

6. 마지막으로 참조 할 필드에 올바른 이름과 범위가 표시되는지 확인한 다음

확인

을 클릭하십시오. 7. 범위가 실제로 Excel에 저장되었는지 확인하려면이 작은 테스트를 시도하십시오. Ctrl + G (GoTo 명령)를 누릅니다. GoTo 대화 상자에서 선박 을 선택한 다음

확인 을 클릭하면 Excel에서 범위 A2 : I16. 범위를 정의하고 저장하는 방법 C가 다시 강조 표시됩니다. SUM & AVERAGE 공식을 사용한 INDEX Norrington은 함대의 전투 능력을 평가하고 있습니다. 먼저 그는 해적들이 얼마나 많은 대포를 가지고 있는지, 배당 평균 대포 수 및 모든 해적선을 조종하는 승무원의 수를 알고 싶어합니다. 그는 다음 공식을 입력합니다 :

1. = SUM (INDEX (Ships, 8))은 334, 대포 총 수 및

2와 같습니다. = 평균 (INDEX (선박, 8))은 22.27 또는 배당 대략 22.27 캐논이다.

3. = SUM (INDEX (Ships, 7))은 모든 배의 모든 승무원의 총수 인 2350과 같습니다.

선박과 숫자 8 사이에 쉼표, 공백, 쉼표가있는 이유는 무엇입니까? Norrington이 모든 행을 원하고 8이 8 번째 열 (H 열, 대문자)을 나타 내기 때문에 Ships가 Range (쉼표 다음), Row 인수가 공백 (또는 공백)입니다.

왜 그 열의 맨 아래에 SUM 및 / 또는 AVERAGE 수식을 입력하지 않는 것이 좋습니까? 이 작은 스프레드 시트에서 네, 그렇게 쉬울 것입니다. 그러나 스프레드 시트의 행 수가 5,000 행 300 열이면 SUM 및 AVERAGE를 사용하여 INDEX

JD Sartain

03 INDEX 수식을 사용하는 것이 좋습니다.

범위가 지정되면 Norrington은 이 동일한 통합 문서를 사용하여 A 열의 쿼리를 정의하는 설명과 함께 B 열 (수식 대신 결과 표시)에 쿼리 (수식)를 작성합니다. 참고 : C 열은 B 열에있는 실제 수식을 나타냅니다.

수식이 계산되는 동안 5000 개의 레코드로 구성된 거대한 데이터베이스를 시각적으로 보거나 수 초 동안 기다릴 필요가 없습니다. 그는 Query Sheet에서 필요한 모든 정보를 얻을 수 있습니다. 특히 스프레드 시트가 많을수록 스프레드 시트의 기능이 더 커짐을 기억하십시오. 특히 많은 수식이있는 경우 더욱 그렇습니다.

JD Sartain

04 Commodore James Norrington의 해적선 정보 / 쿼리 시트. D. MAX와 함께 INDEX MATCH

이제 Norrington은 가장 많은 인구가 많은 배에 해적판이 얼마나 있는지 알고 싶어합니다. 그는 MAX 공식을 사용하여 가장 많은 수의 해적을 얻는 데 INDEX를 사용하지만 어느 배가 어느 해적선을 가지고 있는지도 알아야합니다. 따라서 MAX 공식이있는 INDEX / MATCH를 사용하여 선상에 가장 많은 해적이있는 선박을 찾습니다.

1. = MAX (INDEX (Ships, 7))는 선박 중 하나에있는 해적 중 가장 많은 수인 300과 같습니다.

2. = INDEX ($ A $ 2 : $ A $ 16, MATCH (MAX (선박), $ G $ 2 : G $ 16, 0))는 해적이 가장 많은 배인 Royal James와 같습니다. = INDEX ($ F $ 2, $ F $ 16, MATCH (MAX (선박), $ G $ 2 : G $ 16, 0))는 로얄 제임스 대위 인 Stede Bonnet과 해적 승무원이 300

JD Sartain

INDEX-MATCH 및 MAX를 사용하여 데이터베이스에서 특정 정보를 검색하십시오.