중간값에 해당하는 값이 2개일 때 2개 모두 표시하기

예제는 https://cafe.naver.com/excelmaster/217949에서 가져왔습니다.

중간값질문.xlsx

0.01MB

 

 

 

중간값에 해당하는 월이 있다면 index와 match함수를 이용해 월을 구할 수 있는데,

2개일 경우는 보다 작은 값에 해당하는 월과 보다 큰 값에 해당하는 월을 가져와야 하므로 복잡합니다.

 

1. 일치하는 값이 1개일 때 해당하는 월 구하기

최솟값에 해당하는 월을 구하는 D4셀의 수식은 

=INDEX($C$9:$C$20,MATCH(C4,$D$9:$D$20,0))로

C4셀과 일치하는 값을 $D$9:$D$20에서 찾는데 0을 인수로 줘서 정확히 일치하는 순번을 찾으므로

MATCH(C4,$D$9:$D$20,0)의 값은 2입니다.

 

따라서 =INDEX($C$9:$C$20,2)가 되므로

$C$9:$C$20에서 두 번째 값 2월이 되는 것입니다.

 

최댓값도 마찬가지입니다.

 

 

2. 중간값 구하기에 해당하는 월의 경우의 수

중간값을 구하는 함수는 Median이며, 홀 수일 때는 순서가 중간에 위치하는 값이 중간값이 되는데,

짝수라면 (개수 + 1)/2를 구해서 작은 값과 큰 값의 평균이 중간값이 됩니다.

 

위 예제를 보면 12개로 짝수이므로 (12+1)/2 = 6.5이므로

6번째 값은 =SMALL($D$9:$D$20,6)로 구하면 55.28이고, 

 

7번째 값은 =SMALL($D$9:$D$20,7)로 구하면 57.08입니다.

 

따라서, 해당월은 5월과 8월이 됩니다.

 

그러나, 11개라면 6번째가 가운데이므로 

중간값은 55.28이고, 이에 해당하는 월은 5월입니다.

 

 

3. 중간값 구하기에 해당하는 월 구하기 방법 1

=IFERROR(INDEX(IF($B$9:$B$20=$C$3,$C$9:$C$20),MATCH(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),0)),CHOOSECOLS(INDEX(SORT(IF($B$9:$B$20=$C$3,$C$9:$D$20),2,1),MATCH(C5,SORT(IF($B$9:$B$20=$C$3,$D$9:$D$20),,1),1)),1) &","& CHOOSECOLS(INDEX(SORT(IF($B$9:$B$20=$C$3,$C$9:$D$20),2,-1),MATCH(C5,SORT(IF($B$9:$B$20=$C$3,$D$9:$D$20),,-1),-1)),1))

 

가. 사용된 함수

(1) IfError

(가) 구문

IFERROR(value, value_if_error) : value가 error가 아니면 value를 반환하고, 에러면 value_if_error에 해당하는 값을 반환합니다.

 

(나) 예제

=IFERROR(E3/F3,"에러") 

E3셀 값 3과 F3셀 값 4를 나누면 0.75로 에러가 아니므로 그 값을 그대로 반환하고,

F3가 0이면 #DIV/0! 에러가 발생하므로 "에러"란 문자열을 반환합니다.

그러나 E3가 0이고, F3이 4면 에러 없이 E3/F3이 0이므로 E3/F3의 값이 반환됩니다.

 

value_if_error에 0 또는 ""(공백)을 입력할 수도 있습니다.

 

(2) ChooseCols

(가) 구문

=CHOOSECOLS(array,col_num1,[col_num2],…)

array(배열)에서 지정한 열, 위 구문에서는 col_num1 또는 col_num2 등에 해당하는 값을 반환합니다.

열을 하나라도 지정해야 하므로 한 개는 필수이고, 두 번째 이후는 대괄호 안에 있으므로 선택 요소입니다.

 

(나) 예제

F9셀에 =CHOOSECOLS(B9:G20,3)라고 입력하면 3번째 열에 해당하는 단가를 배열형태로 반환합니다.

 

(다) ChooseRows ChooseCols는 열을 반환하는데, ChooseRows는 행을 반환하는 것입니다.

 

G9셀에 =CHOOSEROWS(B9:D20,3)라고 입력하면 범위 중 3행에 해당하는 제품코드, 출고월 및 단가가 한 줄 배열로 반환됩니다.

 

 

(3) Sort

(가) 구문

=SORT(array,[sort_index],[sort_order],[by_col])

 

array : 배열만 필수 요소이고, 나머지 인수는 모두 선택 요소입니다.

sort_index : 정렬할 행 또는 열을 나타내는 숫자

sort_order : 오름차순의 경우 1(기본값), 내림차순의 경우 -1로 표시

by_col : 행별로 정렬하려면 FALSE(기본값), 열로 정렬하려면 TRUE로 표시하여 원하는 정렬 방향을 나타내는 논리 값

 

(나) 예제

① array만 지정한 경우

K9셀에 =SORT(D9:D20)이라고 입력하면 D열 값을 기준으로 오름차순으로 정렬한 값을 배열 형태로 반환합니다.

 

② array, sort_index, sort_order를 지정한 경우

 

L9셀에 =SORT(B9:D20,3,-1)라고, sort_index를 3, 다시 말해 단가를 기준으로, 그리고, sort_order를 -1, 내림차순으로 정렬하면 L9에서 N20셀까지 단가를 기준으로 정렬돼서 표시됩니다.

 

나. 논리

원리는 중간값에 해당하는 값이 있을 때는 INDEX(IF($B$9:$B$20=$C$3,$C$9:$C$20),MATCH(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),0))으로 일치하는 단가에 해당하는 월을 찾으면 되는데,

 

에러라면

HOOSECOLS(INDEX(SORT(IF($B$9:$B$20=$C$3,$C$9:$D$20),2,1),MATCH(C5,SORT(IF($B$9:$B$20=$C$3,$D$9:$D$20),,1),1)),1)

Sort함수를 이용해 단가를 기준으로 내림차순(-1)과 오름차순(1)으로 정렬한 다음보다 적은 값(1) 또는 보다 큰 값(-1)에 해당하는(Match) 행 수를 구한 다음

단가(D열)를 비교해서 일치하는 월(C열)을 가져와야 하기 때문에 index의 인수로 C열과 D열을 지정하는데, 그러면 열이 2개인 1행이 반환되므로

ChooseCols함수를 이용해 1번째 열인 C열의 값을 가져오도록 하는 것입니다.

그러나, 보다 적은 경우와 보다 큰 경우가 있기 때문에 & 연산자로 결합해서 "5월,12월"로 표시하도록 했습니다.

 

 

4. 중간값 구하기에 해당하는 월 구하기 방법 2

=IFERROR(INDEX(IF($B$9:$B$20=$C$3,$C$9:$C$20),MATCH(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),0)),XLOOKUP(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),$C$9:$C$20,,-1) & "," & XLOOKUP(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),$C$9:$C$20,,1))

 

가. 사용된 함수

(1) XLookup

(가) 구문

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

 

lookup_value : 찾으려고 하는 값

lookup_array : 찾을 대상 열

return_array : 반환할 값이 있는 열

 

 ※ vlookup함수의 경우는 찾을 값이 있는 열을 첫 번째 열로 하고, 반환할 값이 있는 열까지를 table_array 하나로 지정하고, col_index_num으로 반환할 열을 지정하는 것과 다릅니다.

 

[if_not_found] : iferror함수와 같이 찾으려고 하는 값이 없을 때 반환할 값

 

[match_mode] : 

0 - 정확히 일치합니다. 찾을 수 없는 경우 #N/A를 반환합니다. 기본값입니다.

-1 - 정확히 일치하는 값을 찾을 수 없는 경우 다음으로 작은 항목을 반환합니다.

1 - 정확히 일치하는 값을 찾을 수 없는 경우 다음으로 큰 항목을 반환합니다.

2 - *, ?, ~ 과 같이 특별한 의미가 있는 와일드카드에 일치하는 값을 반환합니다.

 

[search_mode]

1 - 첫 번째 항목부터 검색을 수행합니다. 기본값입니다.

-1 - 마지막 항목부터 역방향 검색을 수행합니다.

2 - 오름차순 으로 정렬되는 lookup_array 사용하는 이진 검색을 수행합니다. 정렬하지 않으면 잘못된 결과가 반환됩니다.

-2 - 내림차순 으로 정렬되는 lookup_array 사용하는 이진 검색을 수행합니다. 정렬하지 않으면 잘못된 결과가 반환됩니다.

 

 

나. 논리

INDEX(IF($B$9:$B$20=$C$3,$C$9:$C$20),MATCH(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),0))가 에러가 나지 않을 때는 그 값을 사용하고, 에러면, XLookup함수를 사용하는 뒷부분의 결괏값을 반환하는 것입니다.

 

=XLOOKUP(C5,IF($B$9:$B$20=$C$3,$D$9:$D$20),$C$9:$C$20,,-1)

다시 말해 C5에 해당하는 값을 D9:D20에서 찾아 해당하는 행에 해당하는 값을 C9:C20에서 찾는데, 정확한 값이 없다면 보다 작은 값을 반환하는 것입니다.

 

IF($B$9:$B$20=$C$3,$C$9:$C$20) : B9에서 B20셀에 있는 제품코드 값이 C3셀에 있는 제품코드와 일치할 경우만 C9에서 C20에 있는 월을 반환하는 것입니다.

 

3월의 제품코드를 ABCD-D라고 수정한 후 IF($B$9:$B$20=$C$3,$C$9:$C$20) 범위로 잡고 F9키를 누르면 3월이 제품코드가 일치하지 않으므로 False로 반환되고, 나머지는 해당 월이 반환됩니다.

 

3월이 제외되므로 단가의 개수가 11개여서, 가운데값은 6번째 값이 됩니다. 따라서, 12월이되며, 첫번째 방법으로 구한 값과 두번째 방법으로 구한 값이 12월로 일치합니다.

중간값질문(답변).xlsx

0.01MB