1일1함수(17) MATCH함수: 찾는 데이터가 있는 위치를 알려준다
지난번에 엑셀로 이원목적 분류표를
만들었습니다. 여러가지 경우의 수를 생각하면서 수식을 만들다보니 이렇게 고생할바에야 그냥 개수를 세는게 낫겠다는 생각도
들었지만, 써보니 편하다고 말씀하시는 분들이 많아 보람도 느낍니다. 사소한 몇가지 오류를 수정하여 이전포스트에 올려두었습니다.
그리고 또 한가지 느낀 점은, 엑셀을 거의 사용하지 않으시는 분들이 의외로 많다는 것이었습니다. 제가 이것을 만들때는 어느정도는 엑셀을 사용할 줄 아시는 분들을 대상으로 만들었기에, 엑셀을 처음 다뤄보시는 분들이 난감해 하는 것을 볼때 제가 편하자고 만든 것이 또 어떤 분에게는 불편함이 되는구나 하는 생각도 들었습니다.
그래서, 다음 버전업때는 엑셀 초보이신 분들도 큰 불편없이 사용할 수 있게 대대적으로 바꾸어 볼까 합니다.
예를 들면, 주관식 문항수와 객관식 문항수를 입력하면, 자동으로 그 수만큼 표가 만들어져서, 행을 삭제하거나 추가하는 수고를 덜어드리는 것이라든지, 아니면 HWP파일에서처럼 주관식,객관식 밑에 빈칸이 있어도 오류가 생기지 않도록 하는 방법을 연구한다던지 하는 것입니다.
혹시 좋은 생각이 있으시면 제안해주시면 고맙겠습니다.
(P.S. 사실은 수식이 이렇게 복잡할 필요가 없었습니다. 엑셀의 기본적인 상식을 깜빡한 탓입니다. 다시 이원목적분류표를 다시 수정해서 올렸습니다.)
2009/04/18 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적 분류표 간단버전
이번주는 엑셀로 만든 이원목적분류표안에 있는 함수들을 알아보는 시간으로 하겠습니다.
사용된 수식들은 다음과 같습니다.
문항수합계의 수식은
=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
문항배점합계의 수식은
=SUM(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
평가목표 및 난이도 합계의 수식은
=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
1번 답의 개수의 수식은
=COUNTIF(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1),N8)
다중답의 개수의 수식은
=SUMPRODUCT(N(LEN(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1))>1))
객관식 문항수의 수식은
=MATCH("주관식",A:A,0)-MATCH("유형",A:A,0)-2
객관식 점수 합계의 수식은
=SUM(INDIRECT("D"&MATCH("유형",A:A,0)+2&":D"&MATCH("주관식",A:A,0)-1))
주관식 문항수의 수식은
=MATCH("계",A:A,0)-MATCH("주관식",A:A,0)
주관식 점수 합계의 수식은
=SUM(INDIRECT("D"&MATCH("주관식",A:A,0)&":D"&MATCH("계",A:A,0)-1))
보시면 아시겠지만, 모든 수식에 MATCH함수가 사용되었습니다. 이것을 사용할 수 밖에 없었던 것이, 과목마다 객관식 문항수와 주관식 문항수가 다르기 때문에 개수를 세거나 합을 구할때 일괄적으로 어디서부터 어디까지 구하라는 수식을 정할 수가 없었기 때문입니다.
(그런데 지금생각해보니 틀 자체는 한 객관식 30개, 주관식 10개 정도로 주고, 칸이 남으면 기록하지 말고 비워두도록하는게 더 쉬웠을것 같다는 생각도 드네요 ^^;)
아무튼, 오늘은 MATCH 함수에 대해 알아보겠습니다.
간단히 말하면, 주어진 값이 주어진 배열의 어느 위치에 있는가를 보여주는 함수가 되겠습니다.
이전에 배웠던 VLOOKUP이나 HLOOKUP함수는 몇번째인지 위치를 주고, 그 위치에 있는 값을 찾았지만, MATCH함수는 그와는 반대로, 값을 주고 그 값이 몇번째 위치에 있는지 찾는 함수입니다.
예를 들어, 학생들의 점수가 있는 표에서, 몇 번 학생이 만점을 받았는지를 알고 싶다고 합시다. 물론 CTRL+F를 해서 찾을 수도 있지만, 그 위치를 이용해서 다른 수식에 사용하려는 것이 우리의 목적이므로 MATCH함수를 사용합니다.
만점을 받은 학생의 번호는 4번입니다. 위와같이, 만점자의 번호를 알려주는 수식은 다음과 같습니다.
이 수식을 해석하면 다음과 같습니다.
순서는 물론 위에서부터 또는 왼쪽에서부터 세는 것입니다. 위의 그림에서는 위에서부터 4번째에 100이라는 점수가 있으므로, 4가 반환됩니다.
따라서, 아까 언급했던 이원목적분류표에 나왔던 다음과 같은 수식은 어떻게 해석이 될까요?
MATCH("유형",$A:$A,0)
예, A열 끝에서 끝까지의 범위중에서 "유형"이라는 글자와 완전히 똑같은 글자가 몇번째에 있는지 찾으라는 뜻이 되겠습니다.
그런데, 만약 위의 예에서 100점을 맞은 학생이 두 명 이상 있다면 어떤 결과가 될까요?
위의 그림처럼, 2번 학생과 4번 학생이 100 점을 받았다면, 처음(위에서부터) 나오는 2번 학생의 번호가 출력됩니다.
옆으로 늘어선 배열에서는, 왼쪽에서부터 세어서 먼저 나오는 값이 반환됩니다. 여기서도 2번이 출력됩니다. 그러면 만약에 위에서처럼 범위를 한 개의 열이나 한 개의 행만을 주는 것이 아니라 여러 열과 여러 행으로 이루어진 배열을 범위로 주면 어떻게 될까요?
위에서처럼, 배열의 범위를 ABC세 열에 걸쳐서 주었더니 #N/A 오류가 발생했습니다.
2009/04/15 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적분류표
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/03/18 - [컴퓨터/엑셀] - 1일1함수(2) : VLOOKUP 세로방향 데이터 목록에서 값 가져오기
그리고 또 한가지 느낀 점은, 엑셀을 거의 사용하지 않으시는 분들이 의외로 많다는 것이었습니다. 제가 이것을 만들때는 어느정도는 엑셀을 사용할 줄 아시는 분들을 대상으로 만들었기에, 엑셀을 처음 다뤄보시는 분들이 난감해 하는 것을 볼때 제가 편하자고 만든 것이 또 어떤 분에게는 불편함이 되는구나 하는 생각도 들었습니다.
그래서, 다음 버전업때는 엑셀 초보이신 분들도 큰 불편없이 사용할 수 있게 대대적으로 바꾸어 볼까 합니다.
예를 들면, 주관식 문항수와 객관식 문항수를 입력하면, 자동으로 그 수만큼 표가 만들어져서, 행을 삭제하거나 추가하는 수고를 덜어드리는 것이라든지, 아니면 HWP파일에서처럼 주관식,객관식 밑에 빈칸이 있어도 오류가 생기지 않도록 하는 방법을 연구한다던지 하는 것입니다.
혹시 좋은 생각이 있으시면 제안해주시면 고맙겠습니다.
(P.S. 사실은 수식이 이렇게 복잡할 필요가 없었습니다. 엑셀의 기본적인 상식을 깜빡한 탓입니다. 다시 이원목적분류표를 다시 수정해서 올렸습니다.)
2009/04/18 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적 분류표 간단버전
이번주는 엑셀로 만든 이원목적분류표안에 있는 함수들을 알아보는 시간으로 하겠습니다.
사용된 수식들은 다음과 같습니다.
문항수합계의 수식은
=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
문항배점합계의 수식은
=SUM(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
평가목표 및 난이도 합계의 수식은
=COUNTA(INDIRECT("R"&MATCH("유형",$A:$A,0)+2&"C"&COLUMN()&":R"&ROW()-1&"C"&COLUMN(),FALSE))
1번 답의 개수의 수식은
=COUNTIF(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1),N8)
다중답의 개수의 수식은
=SUMPRODUCT(N(LEN(INDIRECT("C"&MATCH("유형",A:A,0)+2&":C"&MATCH("주관식",A:A,0)-1))>1))
객관식 문항수의 수식은
=MATCH("주관식",A:A,0)-MATCH("유형",A:A,0)-2
객관식 점수 합계의 수식은
=SUM(INDIRECT("D"&MATCH("유형",A:A,0)+2&":D"&MATCH("주관식",A:A,0)-1))
주관식 문항수의 수식은
=MATCH("계",A:A,0)-MATCH("주관식",A:A,0)
주관식 점수 합계의 수식은
=SUM(INDIRECT("D"&MATCH("주관식",A:A,0)&":D"&MATCH("계",A:A,0)-1))
보시면 아시겠지만, 모든 수식에 MATCH함수가 사용되었습니다. 이것을 사용할 수 밖에 없었던 것이, 과목마다 객관식 문항수와 주관식 문항수가 다르기 때문에 개수를 세거나 합을 구할때 일괄적으로 어디서부터 어디까지 구하라는 수식을 정할 수가 없었기 때문입니다.
(그런데 지금생각해보니 틀 자체는 한 객관식 30개, 주관식 10개 정도로 주고, 칸이 남으면 기록하지 말고 비워두도록하는게 더 쉬웠을것 같다는 생각도 드네요 ^^;)
아무튼, 오늘은 MATCH 함수에 대해 알아보겠습니다.
MATCH(lookup_value,lookup_array,match_type)
lookup_value 테이블에서 찾으려는 값입니다.
match_type lookup_array에서 lookup_value를 찾는 방법을 지정하는 숫자로 -1, 0, 1 세 가지가 있습니다.
lookup_value 테이블에서 찾으려는 값입니다.
- lookup_value는 lookup_array에서 찾으려는 값입니다. 예를 들어 전화 번호부에서 어떤 사람의 번호를 찾으려고 할 때 그 사람의 이름을 사용하여 찾지만 원하는 데이터는 그 사람의 전화 번호입니다.
- lookup_value는 숫자, 텍스트, 논리값 등의 값이거나 숫자, 텍스트 또는 논리값에 대한 셀 참조일 수 있습니다.
match_type lookup_array에서 lookup_value를 찾는 방법을 지정하는 숫자로 -1, 0, 1 세 가지가 있습니다.
- match_type이 1이면 MATCH는 lookup_value보다 작거나 같은 값
중에서 최대값을 찾습니다. Lookup_array는 반드시 오름차순(...-2, -1, 0, 1, 2,...A-Z, FALSE,
TRUE)으로 정렬되어 있어야 합니다.
- match_type이 0이면 MATCH는 lookup_value와 같은 첫째 값을 찾습니다. Lookup_array는 임의의 순서이어도 됩니다.
- match_type이 -1이면 MATCH는 lookup_value보다 크거나 같은 값 중 가장 작은 값을
찾습니다. Lookup_array는 TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., 등으로
내림차순으로 입력되어야 합니다.
- match_type을 생략하면 1로 간주됩니다.
- MATCH는 lookup_array에서 일치하는 값이 아니라 값의 위치를 표시합니다. 예를 들어 MATCH("b",{"a","b","c"},0)은 array {"a","b","c"}에서 b의 상대 위치 2를 표시합니다.
- MATCH는 텍스트의 대/소문자를 구분하지 않습니다.
- 일치하는 문자를 찾지 못하면 #N/A 오류 값이 반환됩니다.
- match_type이 0이고 lookup_value가 텍스트이면 lookup_value에 와일드카드 문자인 물음표(?)와 별표(*)를 사용할 수 있습니다. 물음표는 한 문자에 해당하고 별표는 개수에 상관없는 임의의 문자열에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표(~)를 입력합니다.
간단히 말하면, 주어진 값이 주어진 배열의 어느 위치에 있는가를 보여주는 함수가 되겠습니다.
이전에 배웠던 VLOOKUP이나 HLOOKUP함수는 몇번째인지 위치를 주고, 그 위치에 있는 값을 찾았지만, MATCH함수는 그와는 반대로, 값을 주고 그 값이 몇번째 위치에 있는지 찾는 함수입니다.
예를 들어, 학생들의 점수가 있는 표에서, 몇 번 학생이 만점을 받았는지를 알고 싶다고 합시다. 물론 CTRL+F를 해서 찾을 수도 있지만, 그 위치를 이용해서 다른 수식에 사용하려는 것이 우리의 목적이므로 MATCH함수를 사용합니다.
만점을 받은 학생의 번호는 4번입니다. 위와같이, 만점자의 번호를 알려주는 수식은 다음과 같습니다.
=MATCH(100,$C$2:$C$6,0)
이 수식을 해석하면 다음과 같습니다.
| =MATCH | |
| (100, | 100이란 숫자를 |
| $C$2:$C$6 | C2:C6의 범위에서 몇번째에 있는지 찾되 |
| ,0) | 정확히 100 인 것만 찾는다. (99나 101은 안된다) |
순서는 물론 위에서부터 또는 왼쪽에서부터 세는 것입니다. 위의 그림에서는 위에서부터 4번째에 100이라는 점수가 있으므로, 4가 반환됩니다.
따라서, 아까 언급했던 이원목적분류표에 나왔던 다음과 같은 수식은 어떻게 해석이 될까요?
MATCH("유형",$A:$A,0)
예, A열 끝에서 끝까지의 범위중에서 "유형"이라는 글자와 완전히 똑같은 글자가 몇번째에 있는지 찾으라는 뜻이 되겠습니다.
그런데, 만약 위의 예에서 100점을 맞은 학생이 두 명 이상 있다면 어떤 결과가 될까요?
옆으로 늘어선 배열에서는, 왼쪽에서부터 세어서 먼저 나오는 값이 반환됩니다. 여기서도 2번이 출력됩니다. 그러면 만약에 위에서처럼 범위를 한 개의 열이나 한 개의 행만을 주는 것이 아니라 여러 열과 여러 행으로 이루어진 배열을 범위로 주면 어떻게 될까요?
위에서처럼, 배열의 범위를 ABC세 열에 걸쳐서 주었더니 #N/A 오류가 발생했습니다.
2009/04/15 - [컴퓨터/엑셀] - 엑셀로 작성한 이원목적분류표
2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/03/18 - [컴퓨터/엑셀] - 1일1함수(2) : VLOOKUP 세로방향 데이터 목록에서 값 가져오기
댓글
댓글 쓰기