Balto' Story

엑셀함수 MATCH, INDEX, MAX 함수의 조합방법과 최고점수 과목 알아내기

며칠전 MATCH, INDEX, MAX 함수의 조합방법에 대한 포스팅을 해보기로 하였는데 오늘은 세가지 함수의

조합으로 시험과목중 가장 높은 점수의 과목명을 알아내는 예제를 통하여 진행해 보겠습니다.

먼저 3가지 함수의 사용법을 모르시는 분들은 과거 엑셀함수 포스팅 확인해 보시기 바랍니다.

MATCH 함수 사용법: http://yhlover.tistory.com/219

INDEX 함수 사용법: http://yhlover.tistory.com/220

MAX 함수 사용법: http://yhlover.tistory.com/221

MATCH, INDEX, MAX 함수의 조합 사용예시

[엑셀 MATCH] 지정된 범위에서 찾아야 할 값의 위치를 찾아주는 엑셀 MATCH 함수 사용법

엑셀 MATCH 함수는 찾아야 할 값이 지정된 범위의 몇번째에 위치하는지 알아내는 함수이며 참조함수 입니다.

따라서 찾을범위와 찾을 값을 정하고 찾을 값이 정확히 일치해야 하는지 여부를 지정해 주면 됩니다.

다시한번 말씀드리지만 엑셀의 행번호나 열번호를 찾는 것이 아니고 선택한 범위에서 찾아야 할 값이 몇번째에

위치하는지 순번을 알려줍니다.

엑셀 MATCH 함수 사용법

MATCH 함수는 어렵지는 않지만 좀 헷갈릴 수 있는데 match_type의 옵션값에 따라 값이 달라지기 때문입니다.

또한 MATCH 함수의 이해에 앞서 가장 중요한 것은 MATCH 함수는 찾아야 할 값을 선택한 범위의 첫번째 값부터

순차적으로 비교하여 나간다는 사실 기억해 두시기 바랍니다.

match_type 이 0일 경우에는 정확한 값을 찾는 것이므로 별로 어렵지 않으며 ②번 예제만 확인해 보시면 됩니다.

match_type 이 1일 경우에는 반드시 오름차순으로 정렬되어 있어야 원하는 값을 찾을 수 있으며 순차적으로 비교해

가면서 찾는 값보다 작거나 같은값중 최대값을 찾아오게 됩니다.

위 그림의 ①번 예제를 확인해 보면 match_type이 1이고 범위 G2:G10 에서 숫자 7을 찾는 예제입니다.

오름차순으로 정렬되어 있다는 전제하에 선택한 범위의 첫번째 값부터 비교해 나가므로 순차적으로 비교해

나가다가 G7열의 숫자 9를 만나게 되면 바로 이전의 위치인 G6셀의 위치값을 반환해 주는데 G6셀은 선택한

범위의 5번째에 위치하고 있으므로 결과값으로 숫자 5를 반환하게 됩니다.

match_type 이 -1일 경우에는 반드시 내림차순으로 정렬되어 있어야 원하는 값을 찾을 수 있으며 순차적으로 비교해

가면서 찾는 값보다 크거나 작은 값 중 최소값을 찾아오게 됩니다.

위 그림의 ③번 예제를 확인해 보면 에러가 나오게 되는데 내림차순으로 정렬되어 있지 않기 때문입니다.

역시 내림차순으로 정렬되어 있다는 전제하에 순서대로 처음부터 비교하면서 내려오게 되는데 처음 만나는 숫자가

5이므로 7보다 크거나 같은 값이 없다고 판단하고 에러를 출력하여 줍니다.

MATCH 함수 추가적으로 알아둘 점은...

1. 예제는 열을 사용했지만 행으로도 가능하며 사용법은 똑같습니다.

2. 지정한 범위에 같은값이 두개라면 먼저나온 위치를 반환합니다.

3. 선택범위는 한개의 열이나 한개의 행만 가능합니다.

제가 처음 배울때는 굉장히 헷갈리더군요^^;

[엑셀 NOW, WEEKDAY] 현재시간과 요일을 반환하는 엑셀 NOW, WEEKDAY 함수 사용법

NOW, WEEKDAY 함수는 날짜함수로서 NOW 함수는 현재시간을 반환하며, WEEKDAY 함수는 요일을 반환하여 줍니다.

뭐 딱히 어렵지 않으며 바로 사용법 시작합니다.

엑셀 NOW, WEEKDAY 함수 사용법

[엑셀 CHOOSE] 지정된 순번으로 데이타를 가져오는 엑셀 CHOOSE 함수 사용법

엑셀함수 중에서도 사용빈도가 좀 되는 CHOOSE 함수는 지정한 순번의 위치에 있는 데이타의 값을 가져오는

함수입니다.

어렵지 않으면서도 실무에서 유용하게 사용되는데 잘 알아두셨다가 실무에 적용한다면 일찍 집에가게 됩니다.^^

바로 사용법 들어가 봅니다.

엑셀 CHOOSE 함수 사용법

[엑셀 DATE, TODAY] 날짜관리와 제어가 가능한 날짜함수 엑셀 DATE, TODAY 함수 사용법

오늘의 엑셀 포스팅은 날짜관리와 제어를 해주는  시간함수 DATE함수와 TODAY함수의 사용법 알아봅니다.

날짜함수로 가장많이 쓰이는 함수는 DATE, TODAY, NOW, WEEKDAY 등 네가지인데 자주 사용되는 만큼

알아두면 유용하게 써먹을 수 있습니다.

오늘포스팅에서는 DATE함수와 TODAY함수만 설명하고 나머지는 다음번에 시간내서 포스팅 해보겠습니다.

유용한 함수이지만 어렵지 않으니 나름 핵심이라 생각되는 부분 설명해 보겠습니다.

엑셀 DATE 함수, TODAY 함수 사용법

엑셀에서 날짜는 숫자로 인식됩니다.

1900년 1월 1일을 숫자 1로 인식하여 하루가 지날때마다 숫자 1씩 증가하게 됩니다.

①번 예제부터 ⑨번 예제까지는 DATE함수의 예로 든것인데 딱히 어려울것 없는데 단지 ①번 예제의 경우에는

인수를 셀주소로 적용하여 본 경우입니다.

④, ⑤번 예제의 경우처럼 인수를 마이너스로 입력한다면 그 값만큼 날짜를 역으로 계산하여 표시해 주는데

연도의 경우 마이너스로 표시하면 오류가 나오게 됩니다.

⑨번 예제는 TODAY함수의 사용예인데 이미지를 만든날의 오늘날짜인 2011.07.27 을 표시해 줍니다.

추가적으로 G4셀의 셀서식을 숫자로 바꾼다면 숫자로 표시되게 되는데 2011.07.27은 숫자 40,751 표기되는바,

이것은 2011.07.27일이 1900.01.01일로부터 40,751번째 날이기 때문입니다.

마지막으로 ⑩번예제가 실무에서는 많이 사용될 수 있는데 오늘날짜에서 2007년 7월 27일까지의 일수를

구하는 수식으로 "42,212(2011.07.27) - 40,751(2007.07.27) = 1,461" 요렇게 정리가 됩니다.

정말 쉽죠???

[엑셀 DPRODUCT] 지정된 조건에 맞는 값들의 곱을 구하는 엑셀 DPRODUCT 함수 사용법

DPRODUCT 함수 역시 데이타베이스 함수인것을 알 수 있으며 조건에 맞는 값들의 곱을 구하는 함수입니다.

여러말 필요없이 바로 사용법 들어가 봅니다.

엑셀 DPRODUCT 함수 사용법

먼저 구문을 확인해보면 알겠지만 데이타베이스 함수의 구문은 구성이 비슷합니다.

위 그림의 ①번 예제를 확인해보면 국어점수가 7점이상인 사람들의 영어점수를 곱하는 함수인데

각각의 셀값은 C3(8), C4(7), C7(7), C8(3) 이 되는데 이것들을 곱하게되면 8*7*7*3이 되며 답은

1,176이 나오게 됩니다.

②번예제는 조건이 하나더 추가된 것인데 추가된 조건은 영어점수가 7점이상인 사람들의 곱입니다.

어려운 부분은 없으니 한번만 차근히 살펴보시기 바랍니다.

파란색 동그라미 부분은 field 인수인데 열번호를 사용해도 되며 레이블명인 "영어"를 써도 같은 결과가

나오는 예시입니다.

[엑셀 DMAX, DMIN] 조건에 맞는 데이타중 가장 큰값과 가장 작은값을 반환하는

엑셀 DMAX, DMIN 함수 사용법

DMAX, DMIN 함수는 앞에 "D"가 붙어있기에 데이타베이스 함수입니다.

MAX, MIN 함수는 지정된 범위에서 가장 큰값과 가장 작은값을 반환해 주지만 DMAX, DMIN 함수는 조건을 지정하고

지정된 조건에 맞는 데이타중 가장 큰값과 가장 작은값을 찾아주는 함수입니다.

데이타베이스 함수는 엑셀에서 표를 다루게 될 때 아주 유용한 기능들이 많은데 그렇다고 데이타베이스 함수를 다

외울필요는 없으며 "이러이러한 기능이 있다"라는 것 정도만 알아둔다면 나중에 필요할 때 찾아볼 수 있게 됩니다.

엑셀 DMAX, DMIN 함수 사용법

[엑셀 DGET] 조건에 맞는 데이타가 한개인 경우에만 값을 찾아주는 엑셀 DGET 함수 사용법

오늘의 엑셀 포스팅은 좀 특이해 보이는 함수입니다.

함수이름은 DGET 함수 입니다.

특이하다고 한것은 DGET 함수는 지정된 범위에서 지정된 조건에 맞는 데이타가 1개인 경우에만 값을 반환하는데

만약 조건에 맞는 데이타의 개수가 한개도 없으면 "#VALUE!"를 출력하여 주며

만약 조건에 맞는 데이타의 개수가 2개 이상이면 "#NUM!"을 출력합니다.

너무 간단한데 다음의 DGET 함수 사용법 확인해 보시기 바랍니다.

엑셀 DGET 함수 사용법

예제를 보면 굳이 설명이 필요없을 정도로 간단한데

①번 예제를 확인해보면 조건 "영업 85점이상"인 사람의 인원은 4명이므로 "#NUM!"을 출력합니다.

②번 예제는 조건 "국어 85점이상, 영업 90점이상인 사람"의 인원수는 0명이므로 "#VALUE!"를 출력합니다.

③번 예제를 확인해보면 조건 "국어 95점이상, 영업 90점이상"인 사람의 인원수는 1이므로 조건에 맞는 셀의 주소인

D9셀의 값 82를 출력하여 줍니다.

DGET 함수는 어떠한 경우에 사용될까 하는 생각도 들지만 엑셀이란 녀석이 워낙 많은 분들이 사용하는 프로그램이라

유용하게 사용하시는 분들도 계시리라 생각해 봅니다.

[엑셀 DCOUNT, DCOUNTA] 조건에 맞는 셀중 숫자가 들어있는 셀 또는 데이타가 들어있는 셀의 개수를 구하는 엑셀 DCOUNT, DCOUNTA 함수 사용법

DCOUNT 함수는 주어진 조건에 맞는 셀들중 숫자가 들어있는 셀의 개수를 구해줍니다.

DCOUNTA 함수는 주어진 조건에 맞는 셀들중 데이타가 들어있는 셀의 개수를 구해줍니다.

두 함수 모두 앞에 "D"가 붙어 데이타베이스 함수임을 알 수 있는데 데이타베이스 함수는 별도의 셀에 조건을

입력하고 조건을 참조하는 식으로 수식이 이루어집니다.

엑셀 DCOUNT 함수 사용법

①번예제는 국어가 85점이상인 사람들 중 영어가 숫자로 된 셀의 개수이며 한가지조건만을 만족하는 값을

찾습니다.

만약 C4셀을 지운다면 결과값은 3이 나오게 될것인데 이것은 숫자가 들어있는 셀만 세어주기 때문입니다.

(예제가 좀 부실하네요^^;)

②번예제는 국어 85점, 영어 90점 이상인 사람중 수학이 숫자로 된 셀의 개수를 세어주는 데 두가지 조건을

만족하는 경우의 예입니다.

수식에서 파란색 원처럼 열번호를 써도 되며 직접 필드명을 써넣어도 결과값은 동일합니다.

엑셀 DCOUNTA 함수 사용법

DCOUNT 함수와 사용법에 있어서 별로 다를것은 없으며 예제에서 확인해보면 C4셀의 값이 다른데

만약 DCOUNT 함수를 사용했다면 결과값은 3이 나오게 되는데 DCOUNT함수는 숫자가 들어간 셀만

구해주기 때문입니다.

하지만 DCOUNTA 함수는 데이타가 들어있지 않은 셀의 개수를 구해주기 때문에 결과값이 4가 나오게

되는 것입니다.

예제의 설명만 잘 보아도 더이상의 설명은 필요 없으리라 생각해 봅니다.

도움 되셨다면 아래 손가락버튼 클릭과 댓글은 필수 입니당~~~^^

[엑셀 AVERAGE, DAVERAGE] 조건에 맞는 데이터의 평균을 구하는 엑셀 AVERAGE, DAVERAGE 함수 사용법

오늘의 엑셀함수 포스팅은 지정된 범위의 평균을 구하는 AVERAGE 함수와 지정된 범위에서

조건에 맞는 데이터들의 평균을 구하는 데이터베이스 함수인 DAVERAGE 함수의 사용법 입니다.

DAVERAGE 함수는 AVERAGE 함수의 앞에 "D"가 붙어 데이터베이스 함수임을 알수 있는데

AVERAGE 함수와 사용법은 크게 다르지 않으며 조건에 맞는 값들만 추출하여 평균을 구하는

함수이므로 좀 더 까다로운 작업에 편리하게 사용할 수 있습니다.

따라서 AVERAGE 함수는 지정한 범위의 평균을, DAVERAGE 함수는 지정된 범위중에서 조건에

맞는 데이터의 평균을 내주는 것입니다.

엑셀 AVERAGE 함수 사용법

위 예제를 보시면 딱히 어려울것 없습니다.

②번예제처럼 빈칸이 포함되어도 결과값에는 변동이 없습니다.

③번예제는 범위가 다중이어도 계산에는 문제가 없습니다.

④번예제처럼 범위의 중복이 있어도 결과값은 마찬가지입니다.

만약 지정된 범위에 문자가 포함된 셀이 있다면 그 셀은 계산에서 제외되므로 결과값은 숫자만 들어있는

셀들의 평균값만 표시 됩니다.

엑셀 DAVERAGE 함수 사용법

위 예제를 확인해 보면 별도의 설명이 필요없으리라 생각되며 굳이 ①번예제를 풀어서 설명해 보면

""데이타베이스 범위에서($A$1:$E$10), 국어의 평균을 내는데(3), 조건은 1학년중에서 평균을 내어라(C14:G15)"

라고 해석할 수 있습니다.

도움되신분 밑에 손가락클릭과 댓글 필수입니당~~^^