다중조건-Vlookup,Match,Index,Sumproduct

조건 1개

• IF()
• VLOOKUP()
• HLOOKUP()

 

조건 2개

예) 대리점관련 자료.

다중조건문.xlsx

 

어떤 대리점의 직원수를 구하고자 할때 아래와 같은 데이터가 있다면 어떻게 구해야할까?
은평점의 직원수를 구해보도록 하자. (함수를  더 알수록 수식은 더 간단해 짐)

(1) IF와 VLOOKUP

=
IF(H5=C4,VLOOKUP(H4,B5:E8,2,FALSE),
IF(H5=D4,VLOOKUP(H4,B5:E8,3,FALSE),
IF(H5=E4,VLOOKUP(H4,B5:E8,4,FALSE))))

첫번째 조건은 IF를 이용하여, 만약 H4 셀값이 C4값과 같다면, C열에서 H5셀값의 행위치를 찾아 행위치에 있는 셀값을 구하라
C4값과 다르고, D4값과 같다면 H5셀값의 행위치를 찾아 행위치에 있는 셀값을 구하라..
D4도 다르다면, H5셀값의 행위치를 찾아 행위치에 있는 셀값을 구하라. 라는 의미로 수식을 만듭니다.

( 열의 값(IF문으로 계속 나열되는 직원수,평수,테이블수의 값)이 적을경우는 가능하지만,
   IF문이 7개이상 나열되기 힘들기에 한계가 있음을 명심해야합니다.)

(2) VLOOKUP와 MATCH

IF함수가 7개를 넘으면 안되기 때문에, VLOOKUP함수를 이용하되, IF문을 대체할수 있는방법은 바로 MATCH함수입니다.

MATCH함수는 자리수를 돌려주는 함수이기에,
셀의 값이 아닌 기준행이나 열에서 몇번째에 위치하고 있느냐를 알려주는 함수이므로,
VLOOKUP 함수의 인수로 사용할수 있기 때문입니다.

=VLOOKUP(H4, B5:E8, MATCH(H5,C4:E4)+1, FALSE)

H4값을 B5~E8의 범위에서 찾아라,
그리고 MATCH함수의 결과값에 1를 더한값만큼 우측으로 이동한 셀값을 구하라 라는 말입니다.
--> 은평점을 B열에서 찾아 은평점의 행에서 우측으로 MATCH결과값의 1을 더한 수만큼 이동하여 셀값을 구하라

여기서 MATCH(H5,C4:E4)의 의미는..
H5(직원수)의 값을 C4~E4의 범위에서 찾아서 몇번째에 위치하는지 구하라라는 말
C4의값이 바로 직원수이므로, 결과치는 1을 돌려줍니다. 즉, MATCH(H5,C4:E4)=1의 값으로 치환됩니다.

그런데 VLOOKUP함수의 기준행이 1이므로, 1이라는 값을 그대로 쓰면,
은평점이라는 값이 구하여 지므로, MATCH(H5,C4:E4)+1 을 함으로써 2라는 값을 돌려받아,
은평점기준으로 2만큼 이동한 셀의 값을 구하는것입니다.

즉 행의값은 VLOOKUP으로 구하고, 열의값은 MATCH값으로 구하여, 행과 열이 교차된 셀의 값을 구하는 것입니다.

(2-1) HLOOKUP와 MATCH

=HLOOKUP(H5,C4:E8,MATCH(H4,B5:B8)+1,FALSE)

VLOOKUP함수는 검색기준이 행에서 검색되는 함수라면, HLOOKUP함수는 검색기준이 열에서 검색되는 함수이므로,
행부터 검색하느냐 열부터 검색하느냐 차이일뿐..VLOOKUP함수 사용방법과 동일합니다.

 

(3) MATCH와 INDEX

=INDEX(C5:E8, MATCH(H4,B5:B8), MATCH(H5,C4:E4))

Vlookup&Match를 보면, 행을 기준으로 해서 찾거나, 열을 기준으로해서 찾았다.
그럼 행과 열을 한방에 쓸수는 없을까?, VLOOKUP과 HLOOKUP 함수를 합쳐놓은 듯한 함수는 없을까?
바로 그럴때 쓰는것이 INDEX함수!!

=INDEX(범위, 행의위치값, 열의위치값)

MATCH함수는 자체 독립적으로 사용되기 보다는, 좌표에 대한 위치값만을 돌려주기때문에 보통 인수로 사용됩니다.
위에서 보듯 VLOOKUP 함수나, HLOOKUP함수, INDEX함수등의 인수로 사용됩니다만,
보통 INDEX 함수와 함께 주로 많이 사용됩니다.

(4) <배열> SUMPRODUCT =SUMPRODUCT((B5:B8=H4)*(C4:E4=H5)*(C5:E8))

     <배열> SUM            {=SUM((B5:B8=H4)*(C4:E4=H5)*(C5:E8))}  --> Shift+Ctrl+Enter

배열을 이용한 함수

배열함수의 단점은 사용하기 편하다고 마구 사용했다가는 연산속도가 느려지는 단점이 있습니다.
쉬트안에서 수십개 수백개가 사용되면 수식이 계산되는것을 한참 기다려야할지도 모릅니다. 때문에 조심해서 사용해야합니다.
하지만, 무지 간편하다는 장점이 있어서~

<참조>
램프천사
http://opencast.naver.com/XL850