| |
New Page 2
|
|
|
※ 아래 Daily Tip은
곽승주님이 제공해주신 내용입니다
가장 자주 사용하는 함수이지만 그 사용법을 자주 까먹는 함수 중
하나가 VLOOKUP()함수입니다. 도움말의 예제를 한참이나 들여다 본 뒤 몇 번
틀리다가 사용하는 경우가 종종 있습니다. 특히 2가지 조건인 경우 VLOOKUP()함수는 그다지
도움이 되질 못하죠.
가령 다음과 같은 워크시트가 있는 경우를 상상해 보죠.
위의 예는 월별 제품별 판매수량을 기록한 것입니다. 2월의
모뎀판매량을 구하려면 어떻게 할까요? 간단합니다. [묻고 답하기]에 질문을 올리면 엑셀의 고수들이
항상 해결해줍니다. 그것도 꽁짜로… 그러나 내 머리를 잠 잘 때 베개 위에 올려두는 역할로만 끝낼
수는 없습니다.
위의 간단한 문제는
INDEX()함수와 MATCH()함수를 이용하여 해결할 수 있습니다. INDEX()함수는
주어진 영역에서 행과 열을 주어 데이터를 찾는 역할을 합니다.
INDEX(array, row_num, column_num)
즉 위의 워크시트에서 데이터 영역인 $C$4:$F$7 셀
영역에서 2번째 행, 2번째 열을 찾으면 2월의 모뎀판매수량이 됩니다.
=INDEX($C$4:$F$7,2,2)=45
모뎀이
$C$4:$F$7 셀 영역에서 2번째 행에 있고 $C$4:$F$7 셀 영역에서 2월이 두번째 열에
있다는 것을 어떻게 알아 낼까요? 그것은 MATCH()함수가 해야할 일입니다. MATCH()함수는
주어진 값을 영역에서 찾아 상대적인 위치를 돌려줍니다.
MATCH(lookup_value, lookup_array, match_type)
즉 ‘2월’은 $C$3:$F$3 셀 영역에서 2번째 위치합니다.
함수로 플어 사용해보면 다음과 같죠.
=MATCH("2월", $C$3:$F$3, 0)
=MATCH("2월", {"1월","2월","3월","4월"}, 0)
=2 |
모뎀 역시 같은 방식으로 상대적인 위치를 찾아
냅니다.
=MATCH("모뎀", $B$4:$B$7, 0)
=MATCH("모뎀", {"프린터";"모뎀";"스캐너";"플로터"}, 0)
=2* 참고: 셀에 수식을 입력한 후 수식 입력줄에서 $B$4:$B$7를 선택하고
F9 키(재계산)를 누르면
{"프린터";"모뎀";"스캐너";"플로터"} 형태로 풀이됩니다 |
그래서 위의 3개의 수식을 조립해보면 다음과 같습니다.
=INDEX($C$4:$F$7, MATCH("모뎀", $B$4:$B$7, 0),
MATCH(MATCH("2월", $C$3:$F$3, 0),0))
=INDEX({10,34,12,43;22,45,31,21;9,20,12,15;3,2,5,4},
MATCH("모뎀",{"프린터";"모뎀";"스캐너";"플로터"},0),
MATCH("2월",{"1월","2월","3월","4월"},0))
=45 |
VBA에서 이와 비슷한
문제가 있는 경우 내부적으로 Loop와 If문을 사용하여 45를 찾아낼수 있을 것입니다. 그러나
엑셀의 VBA는 엑셀 워크시트함수를 가져다가 사용할 수 있습니다. 따라서 어렵게 VB코드만을 사용하지
마시길 바랍니다.
위의 내용을 코드로 옮길 때 워크시트의 셀 영역이 들어갈 자리엔
Range개체 또는 배열을 넣어 주시면 됩니다.
Dim datarange As Range
Dim products As Range
Dim months As Range
Dim theproduct As String
Dim themonth As String
Dim ResultSet
datarange = Sheet1.Range("$C$4:$F$7")
Set products = Sheet1.Range("$B$4:$B$7")
Set months = Sheet1.Range("$C$3:$F$3")
theproduct = "모뎀"
themonth = "2월"
With
Application.WorksheetFunction
Result = .Index(datarange, .Match(theproduct, products, 0), .Match(themonth,
months, 0))
End With
MsgBox Result |
결과는 워크시트에서 계산한 것과 동일합니다. (당연한 소리를…)
영어를 잘 하려면 국어를 잘 해야 한다고 합니다. 하나의 언어를
잘 알아야 다른 언어로 쉽게 이해할 수 있다는 의미겠죠. 마찬가지로 VBA를 잘 하려면 엑셀을 잘
아셔야 합니다. 저는 엑셀은 잘 모릅니다. 아직도…
|
|
|
|
OfficeTutor 365