엑셀 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값을 입력한것 입니다.

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

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

-감사합니다.-

엑셀 TIP(팁)-드롭다운 목록을 통해 데이터를 정확하게 입력할 수 있는 데이터유효성검사에 대해알아봅니다.

 

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

오늘은 지정한 값을 드롭다운 목록을

통해 선택하여 데이터를 정확하게 입력

할 수 있도록 도와주는 데이터 유효성 검사에

대해 알아 보겠습니다. 어떤범위의 데이터를

반복적으로 입력 해야 할경우 미리 입력될

데이터를 지정해 놓고 드롭다운 목록을 통해

선택해서 입력 하므로 오타걱정 없이 입력

하실 수 있습니다.

물론 목록지정시 오타가 없어야 겠죠.

기본적인 내용은 위 이미지와 같습니다.

예를 들어보면

위의 그림에서 직위 부분에 부장,차장,과장

대리,사원을 지정 입력 할려고 할 경우

이 부분을 목록으로 지정해서 정확하게

입력할 것 입니다. 먼저

직위란 부분에 데이터가 들어갈 부분(A)를

드래그 해서 모두 선택합니다. 선택한 상태

에서 상단메뉴의 데이터(B)를 클릭 후

나타나는 리본메뉴에서 데이터유효성검사

(C)를 클릭하면 데이터유효성검사창(D)이

뜹니다. 여기서 제한대상에 (H)부분을 클릭

해서 목록으로 바꾸어줍니다.

그리고 원본 부분에 부장,차장,과장,대리,

사원을 오타 없이 정확하게 입력합니다.

반드시 콤마(,)로 구분해야 합니다.

그리고 확인(G)을 클릭합니다.

그러면 입력하고자 하는 셀을 클릭(A)하면

셀 우측 하단에 화살표(B)가 나타나는데

이것을 클릭해보면 데이터유효성 창에서

지정한 목록들이 나열됩니다.

여기서 원하시는 직위를 선택하시면

정확하게 입력을 하실 수 있습니다.

​

다른 유형을 한번 해보겠습니다.

위 그림에서 데이터가 입력될 사용권한

부분을 드래그해서(A)선택 후 메뉴의

데이터(B)를 클릭 후 리본메뉴의

데이터유효성검사(C)를 선택하면 뜨는

창(D)에서 (E)부분을 클릭해서 제한대상을

목록 으로 바꿔주고 화살표(G)부분을 클릭

합니다.

그리고 입력될 데이터가 있는 부분(H)

범위를 드래그 해서 선택하면 됩니다.

그러면 따로 입력하지 않아도 목록범위

가 지정됩니다. 그리고 확인합니다.

입력될 셀(J)을 클릭후 화살표(K)를 클릭해

나타난 목록에서 해당하는 목록을 선택

하면 됩니다. 그러면 지정된 목록에 오타가

없으면 정확하게 입력이 되겠죠.

또한 목록지정시 한글자음 입력 후 키보드

한자키를 눌러 특수문자도 기입가능 합니다.

-감사합니다.-

엑셀 TIP(팁)-엑셀의 데이터 계급 구분함수에 대해 알아봅니다

 

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

오늘은 IF함수의 응용을 통한 데이터 계급 구분

함수와 IF함수의 AND조건과 OR조건이 있는데

이전에 포스팅한것(2023년1월13일자)에

이어 이번에는 IF함수의 AND와 OR조건을 같이

사용해서 데이터를 구하는 방법에 대해 알아

보겠습니다. 데이터 계급 구분함수라는 것은

IF함수의 참과 거짓의 개념을 이용해 수,우,미,

양, 가 등의 구분을 한다던지 매우우수,우수,

보통, 취약 이런식의 등급을 나누는 함수를

말합니다.

우선 순위를 한번 구해보겠습니다.순위를

구하는 포스팅은 2022년12월10일자에

되어 있습니다. 참고하시면 됩니다.

J31셀을 클릭후 =RANK(I31,$I$31:$I$35,0)

를 입력후 엔터를 치면 순위를 구하게 됩니다.

순위를 구하는 함수는 RANK함수이고 평균을

이용해 절대참조(F4)를 이용하고 내림차순

으로 구합니다.

위 그림은 자동채우기 핸들을 드래그한 결과

입니다. 그러면 이제 평균을 이용해

계급 구분함수를 구해보겠습니다.

j24셀을 클릭후

=IF(H24<60,"유급",IF(H24<70,"양",

IF(H24<80,"미",IF(H24<90,"우","수"))))를

입력하고 엔터를 치면 위와 같이 구해

집니다. 여기서 H24의 평균값이 60점미만

일경우는 "유급"이고 그렇지 않으면 

거짓값을 입력해야 하는데 여기에 

IF함수를 지속적 으로 추가해가며 

우측의 비고의 점수 분포에 해당하는

등급을 입력하면 됩니다.

맨 마지막에 H24<90이면 "우"이고 거짓

이면 "수"값을 입력하고 마지막 괄호를

IF함수를 사용한 만큼 닫아야 합니다.

나온 결과값의 셀에서 우측 하단의 자동채우기

핸들(A)을 아랫방향으로 드래그하시면 그러면

위와 같은 등급값을 구해진 것을 알수 있습니다.

한번더 알아보겠습니다.

위의 그림에서 보면 비고(A)를 참고로 등급을

표시해 보겠습니다. J45셀을 클릭후

=IF(I45<70,"취약",IF(I45<80,"보통",

IF(I45<90,"우수","매우우수")))를 입력후

엔터치면 위와 같이 등급이 구해집니다.

I45셀의 평균값이 70미만일경우"취약"이고

거짓일경우 추가 IF함수를 입력하여 마지막의

I45셀의 평균값이 90미만일경우 "우수"이고

거짓일경우"매우우수"가 되게 하시고 마지막

괄호를 IF함수 수에 맞게 닫으시면 됩니다.

마찬가지로 자동채우기핸들을 드래그 해서

나머지 등급을 자동으로 구하시면 됩니다.

이번에는 앞서 포스팅했던(2023년1월13일자)

IF함수의 AND조건과 OR조건을 조합한 예시를

한번 알아 보겠습니다.

문제를 분석해보면 성적이 90점이상이고

결석횟수가 2번 미만이면의 조건이므로

AND조건을 사용하고 성적이 60점미만

이거나 출석점수가16미만 이므로

여기서는 OR조건을 사용하시면 됩니다.

따라서 J7셀을 클릭후

=IF(AND(I7>=90,D7<2),"A+",

IF(OR(I7<60,E7<16),"F","B")) 을 입력하고

엔터치면 됩니다. 즉 AND조건이 맞으면

"A+"표시하고 OR조건이 맞으면"F"표시하고

그렇지 않으면 "B"를 표기한다는 의미입니다.

이렇게 구한 값을 자동채우기 핸들을

드래그해서 나머지를 구하시면 됩니다.

아래 동영상을 참고하시면 도움이 되실듯

합니다.

-감사합니다.-