번호가 여러 번 입력되어 있고 지급액이 각각 다른 경우 특정 번호에 해당하는 지급액 최고값의 지급일 구하기

안녕하세요.

 

구독자의 질문에 대한 답으로 강좌를 만들었습니다.

2022.03.21 - 번호가 여러 번 입력되어 있고 지급액이 각각 다른 경우 특정 번호에 해당하는 최고값 구하기

번호가 여러 번 입력되어 있고 지급액이 각각 다른 경우 특정 번호에 해당하는 최고값 구하기

 

최고값을 구하는 것으로 MAX, IF 함수와 배열수식으로 구현했습니다.

 

여기서 조금 더 나가 최고값의 지급일을 구하려고 합니다.

 

번호, 지급액, 지급일 열이 있습니다.

같은 번호가 여러 번 입력되어 있고, 각각 다른 지급액이 입력되어 있습니다.

찾으려는 값은 특정 번호에 해당하는 지급액 중 가장 큰 값의 지급일 입니다.

 

완성한 수식입니다.

=INDEX($A$2:$C$11,MATCH(MAX(IF($E$2=$A$2:$A$11,$B$2:$B$11,"")),($E$2=$A$2:$A$11)*$B$2:$B$11,0),3)

INDEX, MATCH, MAX, IF 함수를 중첩한 배열 수식입니다.

꽤나 복잡해 보이는데, 셀 범위를 반복해서 사용하고 있기 때문에 더 복잡해 보입니다.

이름 정의 기능을 써서 표현하면 휠씬 간단해 보일 것입니다.

 

수식이 작성된 [F2] 셀에 커서를 두고 <Shift + F3>을 눌러 [함수 인수] 대화상자를 나타냅니다.

 

INDEX 함수는 인수 목록이 두 종류이기 때문에 선택창이 표시됩니다.

이미 선택되어 있는 첫번째에 커서를 두고 [확인]을 클릭합니다.

[함수 인수] 대화상자를 이용하면 작성한 수식을 함수별로 구분해서 볼 수 있습니다.

 

지금 보여지는 함수는 가장 바깥쪽의 INDEX 함수의 인수 목록과 인수에 입력된 내용입니다.

수식입력줄에 있는 함수명을 클릭하면 그 함수 인수를 보여 줍니다.

 

두번째 함수인 MATCH 함수를 클릭합니다.

수식입력줄에서 세번째 함수인 MAX 함수를 클릭합니다.

수식입력줄에서 마지막 함수인 IF 함수를 클릭합니다.

 

완성된 수식을 이해하려면 제일 안쪽 함수부터 보는 것이 좋습니다.

 

IF 함수 첫번째 인수 Logical_test에 조건으로 [E2] 셀 값과 [A2:A11] 셀 범위와 같은지 비교합니다.

한 개의 값과 여러 개의 값을 비교하는 수식이 배열 수식입니다.

현재 [E2] 셀에 1이 입력되어 있어 같은 번호는 Value_if_true 인수에 입력된 [B2:B11] 셀 범위 값이 나타나고, 같지 않은 경우에는 Value_if_false 인수에 입력된 빈 값을 가지게 됩니다.

그래서 IF 함수의 결과는 배열 {100, 200, 300, "", "", "", "", "", "", ""} 이 됩니다.

 

MAX 함수는 IF 함수의 결과 중 가장 큰 값인 300을 구합니다.

MATCH 함수 Lookup_value 인수에 MAX 함수가 입력되어 있고 구해진 값 300이 표시되어 있습니다.

Lookup_array 인수에 IF 함수를 쓰지 않고 수식으로 조건식을 작성해서 [E2] 셀에 있는 번호와 같은 지급액만 원래 값을 나타내고, 번호가 같지 않은 지급액은 0으로 나타낸 목록을 표시합니다.

MATCH 함수의 결과는 300이 목록 중 몇 번째에 있는지 찾습니다. 3번째에 있는 것으로 구했습니다.

 

제일 바깥쪽 함수인 INDEX 함수는 [A2:C11] 셀 범위에서 행 번호(Row_num) 3, 열 번호(Column_num) 3 위치의 값인 44643을 구했습니다.

44643은 2022-03-23을 나타내는 숫자값입니다.

 

배열 수식이므로 [확인]을 누를 때 <Ctrl + Shift>를 누른 상태에서 눌러야 하고, 또는 <Ctrl + Shift + Enter>를 눌러도 됩니다.

 

완성 파일 다운로드

특정 번호에 해당하는 최고값의 지급일 구하기.xlsx

0.01MB

 

필요하신 분께 도움이 되길 바랍니다.