1일1함수 (10) COLUMN함수 : VLOOKUP함수를 좀 더 편하게 쓰자.

오늘은 COLUMN함수입니다. 지난번에 잠깐 언급했던 ROW함수와 짝을 이루는 함수입니다.
그때 제대로 설명못했으니, 이번에COLUMN함수를 설명하는 김에 ROW함수를 잠깐 언급하겠습니다.

ROW(reference)

reference  행 번호를 구할 셀 또는 셀 범위입니다.

• reference를 생략하면 ROW 함수를 입력한 셀의 참조가 사용됩니다.

• reference가 셀 범위이고 ROW를 세로  (배열: 여러 가지 결과를 만들거나 행과 열로 구성되는 인수 그룹에 대해 연산이 이루어지는 한 개의 수식을 작성하기 위해 사용됩니다. 배열 범위는 공통 수식을 공유하며 배열 상수는 한 개의 인수로 사용되는 상수 그룹입니다.)로 입력한 경우에는 참조의 행 번호가 세로 배열로 반환됩니다.

• reference는 여러 개의 영역을 참조할 수 없습니다.

ROW()라고 괄호안에 아무것도 입력하지 않으면 현재의 행번호가 반환된다는점이 활용하기에 좋겟지요.

이번에는 COLUMN함수입니다.

COLUMN 함수 구문에는 다음과 같은  (인수: 동작, 이벤트, 메서드, 속성, 함수 또는 프로시저에 정보를 제공하는 값입니다.)가 사용됩니다.

• reference  열 번호를 반환하려는 셀 또는 셀 범위 (범위: 시트의 둘 이상의 셀입니다. 범위 내의 셀은 인접될 수도 있고 인접되지 않을 수도 있습니다.)로서 선택 항목입니다.
  • reference 인수를 생략하거나 이 인수가 셀 범위를 가리키는 경우 COLUMN 함수를 가로 배열 수식으로 입력하면 COLUMN 함수는 참조 대상의 열 번호를 가로 배열로 반환합니다.

    수식을 배열 수식으로 입력하려면  수식 셀부터 시작하여 배열 수식을 포함할 범위를 선택하고 F2 키를 누른 다음 Ctrl+Shift+Enter를 누릅니다.

  • reference 인수가 셀 범위이고 COLUMN 함수를 가로 배열 수식으로 입력하지 않은 경우 COLUMN 함수는 가장 왼쪽에 있는 열의 번호를 반환합니다.
  • reference 인수를 생략하면 이 인수가 가리키는 대상이 COLUMN 함수가 표시되는 셀인 것으로 기본 처리됩니다.
  • reference 인수는 여러 개의 영역을 가리킬 수 없습니다.

설명이 깁니다만 우리가 지금 알수 있고, 알아야할 것은 COLUMN함수를 쓰면 해당참조셀의 열번호가 반환된다는 것입니다. COLUMN(A10)이라면 답은 A열의 번호인 1이 반환되겠지요.

COLUMN함수를 이용해서 지난번에 배웠던 VLOOKUP함수를 쓸 때 좀 더 편하게 할 수 있느 방법을 알아보겠습니다.
아래는 1번시트(SHEET1)의 모습입니다. 학생들의 점수가 나열되어있습니다.

2번시트(SHEET2)에 다음과 같이 표를 만들고, 아래처럼 식을 입력하였죠?

국어점수란 (B2셀)에는 어떤 수식을 썼냐면,

=VLOOKUP($A2,SHEET1!$A$2:$F:$5,2,FALSE)

를 썼습니다. 그리고 이제 오른쪽으로 드래그해서 붙여넣기하면, 위에서 보시는 것처럼 모든 점수가 국어점수와 똑같이 됩니다.
그것은 드래그해서 채워넣기하면, 셀주소는 절대주소로 입력하지 않는한 행이나 열이 변함에 따라 바뀌지만, 마지막에 있는 숫자2는 상수이기때문에 셀이 변함에 따라 바뀌지 않고 언제나 2입니다. 따라서, C2셀부터 F2셀까지 모든 셀에 B2와 똑같은 수식이 들어가기때문에 똑같은 값이 나오는 것입니다.
그렇다고 C2셀에는 3을, D2셀에는 4를, E2셀에는 5를 일일히 수작업으로 넣어주긴 귀찮습니다.

이제, B2셀의 수식을 다음과 같이 바꿔봅시다.

=VLOOKUP($A2,SHEET1!$A$2:$F:$5,COLUMN(SHEET1!B1),FALSE)

VLOOKUP함수의 인수중 다른 부분은 똑같고, "두번째 셀의 값을 찾아라"는 의미의 2가 COLUMN(SHEET1!B1)로 바뀌었습니다.
이 수식의 의미는 SHEET1의 B1셀의 열의 번호를 반환하라는 의미입니다. B1셀이니까 열번호는 2, 따라서 결과적으로는 앞에서 썼던 =VLOOKUP($A2,SHEET1!$A$2:$F:$5,2,FALSE)와 같은 수식입니다만, 오른쪽으로 드래그해보면 달라집니다.
위에서 보시는 것처럼, 오른쪽으로 드래그하니까 COLUMN(SHEET1!B1)에서 B1부분이 C1,D1,E1,F1으로 바뀌는 것을 볼 수 있습니다.

게다가 이렇게하면 또다른 장점이 있습니다. 다른 이유로 중간에 열을 하나 삽입해야한다면, 숫자2를 넣어서 만들었던 식이라면 참조해야할 위치가 달라지기때문에 일일이 수정을 또 해줘야합니다만, COLUMN함수를 이용해 셀주소를 참조하게 해 둔 수식은 더이상 손 댈 필요가 없습니다. 위의 그림처럼, 가운데 B열을 하나 삽입하는 경우에도 C2셀의 수식은 그대로 유지되어서, 결과값도 변동이 없습니다.

또한, SHEET1 에서 한 열을 추가한다고 해도,

자동으로 셀의 주소가 C2로 바뀌었습니다. 참~~ 편하죠? ^^*

2009/03/18 - [컴퓨터/엑셀] - 1일1함수(2) : VLOOKUP 세로방향 데이터 목록에서 값 가져오기
2009/03/17 - [컴퓨터/엑셀] - 하루에 엑셀 함수를 하나씩 익히자.(1) MOD함수를 이용해 한 줄 건너 노란색으로 칠하기