엑셀 TIP(팁)- 엑셀의 vlookup함수와match함수를 이용한 기준값에 부합하는 값을 찾는 방법에 대해 알아봅니다.

 

이번에는 엑셀에서 자주 사용하는

함수인 vlookup함수와 match함수를

이용해서 특정 기준값에 부합하는

값을 찾는 방법에 대해 알아 보겠습니다.

위 그림에서 보시면

좌측의 과목별 점수현황 표가 있는데

이 표에서 우측(A)부분에 이름에

따라 과목이 표기되게 할경우

VLOOKUP함수를 사용할수 있습니다.

H10셀을 클릭하고

=VLOOKUP(G10,$B$10:$E$17,2,FALSE)

를 입력하고 엔터를 칩니다.

그러면 위 그림처럼 이름

김수현에 해당하는 과목 엑셀이

입력됨을 알수 있습니다.

만약에 이름을 바꾸면

바뀐사람의 과목이 표기가 됩니다.

그런데 과목 부분에 자동필터를

적용해 (A)부분을 클릭해서

점수나 비고를 표기할 경우

VLOOKUP함수를 다시 수정해야

합니다.

=VLOOKUP(G10,$B$10:$E$17,2,FALSE)

즉 위 함수식에서 빨간색2에 해당하는

숫자를 일일이 바꿔줘야합니다.

그래서 이런 번거로움을 없애기 위해

이부분에 MATCH함수를 이용하면

편리합니다.

우측에 따로 표시해보면

K10셀을 클릭하고

=VLOOKUP(J10,$B$10:$E$17,

MATCH(K9,B9:E9,0),FALSE) 입력하고

엔터를 치면 됩니다.

그러면 위 그림처럼 자동필터가

적용되어 과목으로 바뀌면 그에

해당하는 값이 표기됨을 알수

있습니다.

위 그림처럼 과목을 점수로 바꾸면

이름에 맞는 점수에 해당하는 값이

표기됨을 알수 있습니다.

이렇게 이번에는 VLOOKUP함수와

MATCH함수를 활용해서 특정값에

부합하는 값을 찾는 방법에 대해

알아 보았습니다.

아래 동영상을 참고하시면 더 쉽게

이해되시리라 생각됩니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 VLOOKUP함수와 LEFT함수를 이용한 실습예제에 대해 알아봅니다.

 

유투브 구독신청~ 알림설정~ 좋아요. 감사합니다.

오늘은 엑셀의 VLOOKUP함수와 문자함수인

LEFT함수를 이용한 실습문제에 대해 한번

살펴 보겠습니다. 먼저 VLOOKUP함수는

세로로 된 범위에서 값을 참조하여 다른

항목의 값을 변환하는 함수입니다.

기본함수식은

=HLOOKUP(찾을값, 참조범위, 행번호,

일치옵션)입니다.

데이터를 추출하는 LEFT 문자함수는

기본함수식은

=LEFT(대상텍스트, 찾을문자수)입니다.

위 그림에서 보듯이 직위코드에 맞는 성과급을

찾아서 보너스와 합한 총지급액을 구하는 예제

입니다. 먼저 총지급액=보너스+성과급입니다.

VLOOKUP함수에 의해 직위코드에 맞는

성과급을 찾아야 하므로 먼저 코드를

추출합니다.

따라서 LEFT함수에 의해 LEFT(D8,2)임을

알수 있습니다.

값을 입력할 F8셀을 클릭합니다.

함수식이 들어가야 하므로

=E8+VLOOKUP(LEFT(D8,2),$I$9:$J$12,2,

FALSE)을 입력 후 엔터를 칩니다.

우측의 직위별 지급표의 데이터는 위치는

변경되면 안되므로 반드시 F4를 눌러

절대참조를 해야 한다는거 잊지마세요.

그러면 아래그림과 같이

총지급액이 구해짐을 알수 있습니다.

총지급액이 구해진 셀 우측하단의 자동채우기

핸들을 아랫방향으로 드래그 해서 나머지

값을 구하시면 됩니다.

위 그림과 같이 값이 나타남을 알수 있습니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 HLOOKUP함수와 VLOOKUP함수에 대해 알아봅니다.

 

구독신청~ 알림설정~ 좋아요   감사합니다.

오늘은 가로로 된 범위에서 값을 참조하여

다른 항목의 값을 반환하는 HLOOKUP 함수

와 세로로 된 범위에서 값을 참조하여 다른

항목의 값을 변환하는 VLOOKUP함수에

대해 알아보겠습니다. 먼저 HLOOKUP함수에

대해 살펴 보면 기본함수식은

= HLOOKUP (찾을값, 참조범위, 행번호, 일치옵션)

입니다. 여기서 찾을값은 참조범위의 첫행에서

찾을값 이며 참조범위는 자료를 찾을 표 또는

범위가 됩니다. 행번호는 조회할 범위에서 일치

하는 조회값이 첫행에 있을 경우, 몇번째 행에

있는 항목을 반환할지 지정하는 행번호이며

일치옵션은 선택항목이 정확히 일치(0, FALSE)

또는 유사일치(1, TRUE) 할지를 결정하게 됩니다.

기본값은 '유사일치'인데 이 일치옵션에 따라

변환하는 값의 오류가 나는것을 알수 있습니다.

글로 적으니 어렵게 느껴지시죠. 예시를 통해

한번 보시면 그리 어렵지 않을듯 합니다.

일단 평균값을 이용해 그 평균에 따른 등급을

구할것이므로 먼저 등급을 구할 H12셀을

클릭후

=HLOOKUP(G12,$C$21:$F$22,2,TRUE)을

입력후 엔터를 칩니다. 여기서 G12셀값의

평균점수가 찾을값이고 C21:F22까지가 참조

범위입니다. 참조범위는 반드시 첫번째 행값을

오름차순으로 먼저 정렬해야 합니다. 또한

참조 범위는 고정이어야 하므로 범위지정 후

F4를 눌러 절대참조로 바꾸어야 합니다.

그다음 행번호 입력시 찾을값이 있는

즉 미흡,보통..이 있는 행번호가 2행이라 2값을

입력 합니다.마지막에 뜨는 유사일치와 정확히

일치값을 결정해햐 하는데 유사일치는

TRUE값을 넣거나 1을 입력하면 됩니다.

정확한 일치는 찾을값과 정확히 일치하지

않으면 오류를 표시하므로 현재 위 표에서는

찾을값이 있는 평균이 70,80,90,100 이런

값이고 기준평균값에서는 92,82,73,85,,,

이런 값이므로 정확히 일치를 사용하면

안됩니다. HLOOK함수는 참조할 데이터가

가로방향 즉 행방향으로 되어있습니다.

따라서 유사일치를 선택하시면 위와 같은

등급을 구하실수 있습니다.

​

다음은 VLOOKUP함수에 대해 알아보겠습니다.

VLOOKUP함수는 기본 함수식은

= VLOOKUP (찾을값, 참조범위, 열번호, 일치옵션)

입니다.

여기서 찾을값은 참조범위의 첫열에서 찾을값

이며 참조범위는 자료를 검색할 표 또는 범위가

됩니다. 열번호는 조회할 범위에서 일치하는

조회값이 열에 있을 경우, 몇번째 열에 있는

항목을 반환할지 지정하는 열번호이며 일치옵션

은 선택항목이 정확히 일치(0, FALSE) 또는

유사일치(1, TRUE) 할지를 결정하게 됩니다.

기본값은 '유사일치'입니다.

일단 평균값을 이용해 그 평균에 따른 등급을

구할것이므로 먼저 등급을 구할 H32셀을 클릭후

=VLOOKUP(G32,$B$41:$C$44,2,TRUE)을

입력후 엔터를 칩니다. 여기서 G32셀값의

평균점수가 찾을값이고 B411:C44까지가 참조

범위입니다. 참조범위는 반드시 첫번째 열값을

오름차순으로 먼저 정렬해야 합니다. 또한

참조 범위는 고정이어야 하므로 범위지정 후

F4를 눌러 절대참조로 바꾸어야 합니다.

그다음 열번호 입력시 찾을값이 있는

즉 미흡,보통..이 있는 열번호가 2열이라 2값을

입력 합니다.마지막에 뜨는 유사일치와 정확히

일치값을 결정해햐 하는데 유사일치는

TRUE값을 넣거나 1을 입력하면 됩니다.

정확한 일치는 찾을값과 정확히 일치하지

않으면 오류를 표시하므로 현재 위 표에서는

찾을값이 있는 평균이 70,80,90,100 이런

값이고 기준평균값에서는 92,82,73,85,,,

이런 값이므로 정확히 일치를 사용하면

안됩니다. VLOOKUP함수는 참조할 데이터가

열방향 즉 세로방향으로 되어 있습니다.

따라서 유사일치를 선택하시면 위와 같은

등급을 구하실수 있습니다.

지금까지는 HLOOKUP함수와 VLOOKUP

함수를 유사일치에 대해서 살펴보았습니다.

​

이번에는 HLOOKUP함수와 VLOOKUP

함수의 정확한 일치에 대해서 알아

보겠습니다.

먼저 E7셀을 클릭후 이름을 이용할것이므로

=HLOOKUP(B7,$B$15:$F$16,2,FALSE)입력후

엔터를 칩니다. 여기서 찾을값은 이름이므로B7

값이고 참조범위는 B15:F16까지 절대참조이며

참조범위에서 학년을 구하므로 2행이며 정확한

값은 FALSE 또는 0값을 입력한것 입니다.

그러면 학년이 구해지고 자동채우기핸들을 이용해

나머지를 구하시면 됩니다.

먼저 F7셀을 클릭후 이름을 이용할 것이므로

=VLOOKUP(B7,$H$7:$I$11,2,FALSE)입력후

엔터를 칩니다. 여기서 찾을값은 이름이므로B7

값이고 참조범위는H7:I11까지 절대참조이며

참조범위에서 반을 구하므로 2열이며 정확한

값은 FALSE 또는 0값을 입력한것 입니다.

그러면 반이 구해지고 자동채우기핸들을 이용해

나머지를 구하시면 됩니다.

-감사합니다.-