이럴 때 필요해요

이름을 정의할 때

이렇게 사용해요

오늘은 앞서 배운 VLOOKUP( ) 및 OFFSET( ) 함수와 '이름정의' 기능을 종합 응용하여 보도록 하겠습니다.

(1) 먼저 위 표에서 C7셀을 클릭하여 입력된 수식을 살펴 보시기 바랍니다.

    =VLOOKUP($B$7,범위,COLUMN(B7),FALSE)

    우선 위 수식에서 생소한 함수(COLUMN)에 대해 설명부터 드려야할 것 같습니다.

    COLUMN( )함수는 지정한 영역에 대해 열번호를 구하는 함수이며 참조하는 셀(여기서는 B7)이

    A열에 있으면 1, B열은 2, C열은 3 ... 의 형태로 구현합니다.

    보다 이해를 돕기 위해 아래의 그림과 같이 C7셀을 클릭한 상태로 '수식입력줄'에서 마우스로 이 부분만 선택한 다음,

    키보드에서 F9키를 눌러 보세요.

제대로 되었다면 아래 그림과 같이 나타 날 것입니다.

COLUMN( )함수의 인수로 입력한 'B7'의 값은 2가 되는 것이죠.

즉, A열은 1, B열은 2, C열은 3 ...G열은 7 등과 같이 입력한 셀이 위치한 열번호를 구해 줍니다.

그래도 이해가 안되신다면 지금 아무 빈 셀에나 '함수마법사'에서 COLUMN함수를 선택한 다음,

인수의 입력란에 B7 이라고 입력해 보세요.

아마 Enter를 치기 전에 아래의 그림과 같이 수식결과(=2)가 나타날 것입니다.

(2) 다음은 '이른정의'와 OFFSET( )함수와의 만남에 대한 설명입니다

    먼저 '수식'메뉴에서 '이름정의' 탭을 선택해 보세요. 아래의 그림과 같이 나타 날 것입니다.

하단부의 '참조대상' 대화창에는 아래와 같은 수식이 입력되어 있습니다.

앞에서 배운 '이름정의'에서는 그냥 단순히 특정범위를 드래그하여 입력해 주었는데

수식이 입력되어 있으니까 좀 복잡해 보이죠

자 그럼, 위 수식에서 'COUNTA9'이름정의-2!$B$10:$B$18)' 부분부터 풀어 보도록 하겠습니다.

여기서 사용된 '이름정의-2'! 라는 것은 그냥 단순히 '이름정의-2'라고 정의된 Sheet의 명칭을 뜻하는 것입니다.

즉, 아래의 그림과 같이 이 부분의 이름을 붙인 것입니다. 

 COUNTA('이름정의-2'!$B$10:$B$18)에서 '이름정의-2'!를 빼고나면 COUNTA($B$10:$B$18)'만 남게됩니다.

여기서 COUNTA($B$10:$B$18)는 무슨 의미의 수식일까요

COUNTA( ) 함수는 지정한 영역내에서 값이 있거나 비어 있지 않은 셀의 개수를 구해 줍니다.

아무 빈셀에나 '함수마법사'에서 COUNTA( )함수를 실행하여 인수의 입력창에 '$B$10:$B$18'라고 입력한 다음, Enter해 보세요.

수식결과값은 9라는 수로 나타 날 것입니다. 즉, B10에서 B18까지의 영역에서 '비어 있지 않은 셀의 개수'는 9개 라는 얘기입니다.

자 그럼 이쯤에서 '=OFFSET('이름정의-2'!$B$10,0,0,COUNTA('이름정의-2'!$B$10:$B$18),6)' 를 다시 간략하게 정리하면

'=OFFSET($B$10,0,0,9,6)'로 축소됩니다.

이것을 또 앞에서 배운 'OFFSET( )' 함수의 개념

즉, 'OFFSET(특정위치, 이동행 수, 이동열 수, 구하려는 행개수, 구하려는 열개수)'에 대입하면 B10셀(특정위치)을 기준하여,

행이나 열의 이동없이 (0,0),9개의 행과 6개의 열 범위내에 있는 값을 모두 구현합니다.

'수식'메뉴에서 '이름정의'탭을 선택한 다음 '이름정의' 대화창이 뜨면 '범위'라는 이름을 클릭하여 아래의 그림과 같은 상태에서

마우스로 '참조대상' 부분을 클릭해 보세요.

아마 아래와 같이 점선으로 깜빡이는 표시를 보실 수 있을 것입니다.

즉, 이것은 위에서 길게 설명한 OFFSET( )함수로 지정한 '범위'라는 이름으로 정의된 구역은 점선으로 표시된 부분임을 나타 내 주는 것 입니다.

(3) 여기까지 되었으면 다시 위에서 VLOOKUP( ) 함수로 값을 찾을 셀들, 즉, C7 부터 G7 까지의 범위 중에서

    아래 그림과 같이 C7셀만 남기고 모두 지운 다음

다시 C7셀을 클릭해서 옆으로 '드래그' (G7까지)하여 '채워넣기' 한 다음,

각 셀 (C7 부터 G7 까지)을 클릭하여 '수식입력줄'에 나타나는 수식을 살펴 보세요.

이제 '=VLOOKUP($B$7,범위,COLUMN(B7),FALSE)'라는 수식이 이해되시나요, 그리고

앞에서의 의문, 즉 왜 굳이 COLUMN( )함수를 사용해야 하는지를 이해하시겠나요.