18. 목록과 데이터 표의 처리

정보는 배움의 원천이다. 그러나 의사결정에 맞게 걸러지고 형태가 구성되어 적절한 사람에게 전달되지 않는다면 그것은 혜택이 아니라 부담이 된다. -윌리엄 폴라드(역사가)-

 

사람들이 균형성과표와 대시보드를 볼 때 보통 처음 보게 되는 것이 차트이긴 하지만 정보의 원천은 표와 목록이다. 엑셀이 차트를 만들고, 표를 표시하고, 메뉴를 채우고, 인쇄보고서를 만드는 데이터가 모두 그 안에 들어 있다. 또한 관리자들과 경영층이 더 자세한 데이터를 원할 때 찾는 것도 그것이다. 이런 목록과 표를 관리하는 동적 능력이 있으면 당신의 시간을 현저히 절약할 수 있고, 대시보드 사용자에게는 더 많은 기능을 줄 수 있으며 오류 발생의 가능성을 줄일 수 있다. 이 장에서는 당신이 배울 다른 기법은 명령문 대신 함수를 사용하여 목록을 정렬하는 방법이다. 이는 어떤 원천에서도 데이터를 조회하고 불러올 수 있게 하며 사용자의 필요에 따라 여러 가지 방법으로 정렬할 수 있게 한다.

 

VLOOKUP은 버리고, INDEX와 MATCH의 조합을 사용한다

사람들이 엑셀을 시작하여 초보에서 중급 수준으로 나아가면서 배우는 가장 강력한 기능 중 하나가 수직 및 수평 검색 기능인 VLOOKUP 함수와 HLOOKUP 함수의 사용이다. VLOOKUP 함수는 한 목록에서 어떤 용어에 대해 첫 번째 열을 검색하고 그 항목이 발견된 행 안에서 당신이 규정한 한 열의 값을 반환한다. HLOOKUP 함수는 수평 목록에서 동일한 기능을 수행한다. 이 기능들은 대개 거래 코드와 같은 검색 항목이 주어졌을 때 구매 금액과 같은 항목을 조회하는 데 사용된다. 이 VLOOKUP 함수와 HLOOKUP 함수가 강력하기는 하지만 당신을 골치 아프게 만들 수도 있다. 이것들은 잘못 사용되기 쉬우며 만일 잘못 사용된다면 틀린 값을 반환하기 때문이다. VLOOKUP 함수와 HLOOKUP 함수의 구문은 다음과 같다.

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])

VLOOKUP 함수와 HLOOKUP 함수에서 가장 빈번하게 발생하는 오류는 정확한 일치를 찾으면서 range_lookup에 FALSE를 제대로 사용하지 않는 것이다. 만일 range_lookup이 TRUE이거나 생략되었다면 VLOOKUP 함수와 HLOOKUP 함수는 순서가 정렬되었다고 생각한다. 따라서 정확한 일치를 찾을 수 없다면 대략 일치하는 것을 반환하는데 이것은 대개 오류(#N/A)를 반환하는 것보다 나쁜 결과다. VLOOKUP의 또 하나의 단점은 검색되는 열이 반드시 왼쪽 열이어야만 한다는 점이다. 그리고 HLOOKUP으로 검색되는 행은 반드시 꼭대기 행이어야만 한다. 다른 필드들을 검새갛는 여러 개의 드롭다운 목록을 사용하는 대시보드를 만들 때 당신은 다른 열이나 행을 검색할 필요도 있을 것이다. 목록에서 데이터를 찾는 보다 강력하고 유연한 방법은 INDEX 함수와 MATCH 함수를 조합하는 방법이다. INDEX 함수의 구문은 다음과 같다. 

INDEX(array,row_num,[colunm,num])

데이터의 한 배열 또는 표에서 INDEX 함수는 지정된 수의 행과 열이 교차하는 곳의 값을 반환한다. 당신은 MATCH 함수에서 lookup_value로 사용할 값을 포함하는 행 또는 열을 결정할 수 있다. MATCH 함수는 데이터가 저장된 곳의 행 또는 열을 찾고 INDEX 함수가 그것을 조회한다. MATCH 함수의 구문은 다음과 같다.

MATCH(lookup_value,lookup_array,[match_type])

lookup_value는 검색하여 찾으려는 값, 즉 거래 코드 또는 청구 변호처럼 다른 정보를 파악하는 데 사용하는 값을 말한다. lookup_array는 검색이 이루어지는 범위의 열 또는 행이다. match_type은 그 원하는 일치의 정도를 지정한다. 당신이 균형성과표와 대시보드를 위하여 거의 항상 사용할 MATCH 함수의 구문 중 정렬되지 않은 목록에서 정확한 일치를 찾으려면 match_type은 0을 사용한다. 만일 MATCH 함수가 정확한 일치를 찾지 못한다면 오류 값인 #N/A를 반환할 것이다. 우리는 이 오류를 ISNA 함수로 인식할 수 있다. ISNA함수는 만일 ISNA 함수 안의 계산이 오류를 반환하면 참(TRUE) 값을 반환하고 만일 그 계산이 오류를 반환하지 않으면 거짓(FALSE) 값을 반환한다. ISNA 함수와 IF 함수를 결합함으로써 당신은 오류를 인식할 수 있는 반면, 사용자에게는 보이지 않게 할 수 있다(ISNA 함수의 이 설명에서는 오류를 붙잡는 방법을 설명하는 데 INDEX 함수와 MATCH 함수가 사용된다. INDEX 함수와 MATCH 함수에 대해서는 다음 절에서 설명한다.) 예를 들면, 다음 수식에서 INDEX 함수는 배열 C4:D17의 둘째 열 안의 값을 반환한다. 그 행은 MATCH 함수에 의해 결정된다. 그 MATCH 함수는 C4:D17에서 F2에 있는 검색어와 일치하는 행을 찾는다

=INDEX($C$4:$D$17,MATCH($F$2,$C$4:$C$17,0),2)

그러나 만일 누군가 F2에 배열 C4:C17 안에는 없는 용어를 입력하였다면 어떻게 할까? 그러면 MATCH 함수는 오류를 일으키고 이것은 전체 INDEX 함수가 #N/A를 반환하게 한다. 당신은 대시보드가 틀린 데이터를 반환하는 것을 원하지 않을 것이며 또 당신의 사용자가 오류를 보는 것도 원하지 않을 것이다. 여기 이러한 문제를 해결하는 방법이 있다.