엑셀 배우기, 표 자동 확장 및 동적 범위 참조에 유용한 표서식을 알아보자.

엑셀에서 가장 유용한 기능 중의 하나가 바로 '표서식'이 아닐까 합니다. 일단 표서식은 이름 그대로 쉽게 표를 그릴 수 있는 도구라고 생각하면 되는데, 단순히 선 몇줄 긋는 것 보다는 훨씬 유용한 기능이 이 표서식에 있습니다. 먼저, 표를 자동으로 확장해 주는 기능입니다. B3:H13까지의 매출 관리 또는 재고 관리 대장이 있다고 합시다.

이제 보니, 아래 표에서는 날짜 데이터를 를 추가하지 않았지만, 보통은 날짜별로 매일매일 발생하는 매입과 매출을 아래 방향으로 차곡차곡 기록해 나갈텐데요. 새로운 데이터가 발생할 때마다, 표 그리고 또 각 셀에 있는 함수 수식들을 복사해 주는 것은 여간 불편한 일이 아닙니다. 

우리는 '자동화의 엑셀'을 사용하고 있는데, 이런 수작업은 당연히 피해야합니다. 그리고 아래의 B3:H14 범위가 표 서식으로 지정한다면, 새로운 데이터를 입력할 때마다, 표의 서식과 수식들을 매번 수동으로 복사하는 불편을 피할 수 있습니다. 예를 들어, 아래처럼 B3:H13 범위의 표의 B열 바로에 A6라는 제품명을 입력하면, B3:H13범위의 표가 B3:H14로 자동으로 확장이 되는 것입니다. 당연히 전체 표의 서식과 수식들이 자동으로 복사됩니다. 

그리고 아주 중요한 두번째 표서식의 효과라면 '동적 범위'를 참조할 수 있다는 것입니다. 동적 범위를 참조하는데, OFFSET이란 함수를 사용할 수있는데, 표서식을 사용하면 이보다 훨씬 간편합니다. 위에서 B3:H13의 범위의 매출 관리 또는 재고관리 대장에서는, B열에 제품명과 C열에 입고 또는 F열에 출고량이 입력되면, 대응하는 매입 단가 또는 매출 단가를 J3:M8 범위의 표에서 자동으로 참조하여 출력할 수 있도록 함수 수식을 설정하였습니다.

VLOOKUP 함수 또는 INDEX, MATCH 함수의 조합, 어느 것을 사용하셔도 무방합니다. 저는 간단히 VLOOKUP 함수를 사용했습니다.그런데 B14셀에는 A6이라는 상품명을 입력했습니다. 그리고 아직까지 A6는 취급하고 있는 상품이 아닙니다. 당연히 해당 상품의 매입 단가와 매출 단가 정보가 없으니, C열과 F열의 대응행에 관련 데이터를 출력할 수가 없습니다. 

이런 경우에는 참조하는 범위, J3:M8에 아래와 같이 A6 상품명과 각각 해당 상품의 매입 단가 및 매출 단가 데이터를 입력해 줘야 하는데, 문제는 이렇게 새롭게 참조할 데이터를 입력한다 하더라도, B3:H13의 표에서의 VLOOKUP 함수가 각 상품의 매입 단가 또는 매출 단가를 참조하기 위한 범위는 J3:M8까지로만 설정되어 있다는 점입니다.

그리고 J9:M9에서 새롭게 입력된 A6 상품의 매입 및 매출 단가 정보를 참조하려면, B3:H13의 표에서의 VLOOKUP 함수의 참조 범위를 J3:M9 범위로 조정해줘야할 필요가 있습니다. 당연히 번거로운 작업입니다. 하지만 이 역시 표서식을 이용하면 간단히 해결되는 문제입니다.

일단 위에서 J3:M8 범위가 표서식으로 지정되어 있었다면, J9:M9에 데이터를 입력하는 즉시 해당 표가 J3:M9 범위로 자동 확장됩니다. 그리고 표서식이 적용된 열들은 자동의 각각의 머릿말 이름으로 정의가 되고, 전체 표도 하나의 이름으로 정의가 됩니다. 아래의 D14셀의 수식을 풀어보면서, 이 '이름 정의'의 의미를 확인해 보겠습니다. 

참고로 IF 함수 수식의 의미는 C열에 입력 값이 없을 때는 아무런 값도 출력하지 말고, 공백으로 두라는 의미이고, 핵심은 VLOOKUP 함수의 구조입니다.

아래에서 VLOOKUP 함수 부분만 좀더 이해하기 편하게, 따로 '=VLOOKUP(B14, 표2[#모두],2,FALSE)'로 정리하겠습니다. 애초에 J3:M8의 범위가 표 서식으로 지정되어 있기 때문에 이 표는 '표2[#모두]'란 이름을 갖게 되었습니다. 그리고 즉 VLOOKUP 함수가 참조하고 있는 범위는 J3:M8라는 특정 범위가 아닌, 해당 표의 이름을 통해 참조합니다. 그래서 이 '표2[#모두]'의 범위가 J9:M9의 범위에 새로운 데이터가 입력된 후에 확장이 되었어도, 해당 표의 새로운 범위 J3:M9을 자동으로 참조하게 되는 것입니다. 

실제로 표 서식 기능을 사용해 보면 정말 간단한 내용인데 텍스트로 풀어 쓰려니 좀 복잡해 집니다. 관련 내용으로 이해가 안되는 부분 있다면, 댓글 남겨 주시기바랍니다.