학습(공부)하는 블로그

엑셀로 작업을 하다보면 가끔 행과 열을 바꿔야 하는 상황이 발생합니다.

행과 열을 바꾸는 방법은 두 가지가 있습니다.

아래 예제를 보겠습니다.

먼저 첫번째 방법은 복사(Ctrl+C)한 뒤에 선택하여 붙여넣기를 활용하는 방법입니다.

아래에서 행과 열을 바꿀 데이터를 선택한 뒤에 복사(Ctrl+C)를 합니다.

행과 열을 변경해서 붙여넣을 첫번째 셀이 있는 곳에서 마우스 오른쪽 버튼을 클릭한 뒤

아래 메뉴중 선택하여 붙여넣기( S)... 를 선택합니다.

선택하여 붙여넣기 창이 열리면 아래쪽에 있는 행/열 바꿈(E)에 체크하시고 확인 버튼을 누릅니다.

아래와 같이 행과 열이 변경되어 복사가 된 것을 알 수 있습니다.

이번에는 TRANSPOSE 함수를 이용하여 행과 열을 바꿔보겠습니다.

먼저 바꾼 행과 열이 들어갈 범위를 마우스로 지정합니다.(여기서는 J12에서 R18까지 입니다.)

그런 다음에 함수 마법사를 호출한 뒤에 TRANSPOSE 함수를 선택하고 확인을 클릭합니다.

필수 인수 입력창이 나오면 변환해야할 원본 데이터의 범위를 마우스로 지정한 뒤 확인 버튼을 클릭합니다.

어~ 그런데 에러 메시지가 나옵니다.

당황하실 필요 없습니다.

TRANSPOSE 함수는 배열수식으로 입력해야 한다는 점을 기억하시면 됩니다.

배열수식은 Shift 키와 Ctrl 키를 동시에 누른 상태에서 Enter 키를 누른다는 것을 배웠죠.

수식 입력창에 마우스로 클릭하시고  Shift 키와 Ctrl 키를 동시에 누른 상태에서 Enter 키를 누릅니다.

아래와 같이 제대로 행과 열이 변환된 것을 확인할 수 있습니다.

그러면 이 두 가지 방법의 차이는 무엇일까요?

선택하여 붙여넣기는 붙여넣기를 하고 난 상태이기 때문에 원본이 바뀌어도 변하지가 않습니다.

대신 TRANSPOSE 함수를 사용하여 배열수식으로 변환한 경우에는 원본이 변경되면 같이 연동이 됩니다.

그럼 정말 그런지 확인해보겠습니다.

박겨울의 수학 성적을 85점에서 100점으로 고쳐보겠습니다.

선택하여 붙여넣은 L4셀은 85점에서 변동이 없습니다.

그런데 TRANSPOSE 함수를 사용한 L14셀은 C4셀과 같이 100점으로 변경되었습니다.

원본을 두고 작업을 해야 할 경우에는 TRANSPOSE 함수를 사용하면 되겠죠.

반면에 원본을 따로 둘 필요가 없는 경우에는 선택하여 붙여넣기로 변경하는 방법이 편하겠죠.

예제 파일을 첨부합니다.

행렬변경.xlsx

도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!

앞서 ROW함수와 COLUMN함수의 쓰임새에 대해 알아보았습니다.

이번에는 반복되는 행 또는 열의 합계를 구하는 방법을 알아보겠습니다.

아래 예제는 인터넷 상에서 구한 파일인데 제가 임의로 변경한 것입니다.

아래 예제를 보시면 K열에 출력될 합계 금액은 F열과 H열, 그리고 J열의 숫자를 모두 합하는 것이죠.

이와 같이 규칙적으로 반복되는 열의 합계 구하는 방법을 알아볼거고요,

15행에 들어갈 합계는 5행부터 14행까지 범위 중 홀수열의 합계이고, 16행에 들어갈 합계는 짝수열의 합계죠.

이 합계 역시 행과 열만 다르다는 것 뿐이지 원리는 같겠죠.

그럼 우선 K열에 들어갈 수식부터 알아보겠습니다.

가장 쉬운 수식은 아래와 같죠.

=F5+H5+J5

더해야 할 셀 주소를 일일히 찾아서 더하는 방법이죠.

지금은 데이터가 세 개 밖에 없어 편리하겠지만 더해야 할 데이터가 많으면 곤란하겠죠.

두 번째 방법은 아래와 같이 SUM 함수를 사용한 방법으로 약간은 진보한 것이죠.

=SUM(F5,H6,J6)

그런데 이 수식 역시 더할 데이터가 많아지면 곤란해지기는 마찬가지입니다.

세 번째 방법은 SUMPRODUCT 함수를 활용하는 방법으로 수식은 아래와 같습니다.

=SUMPRODUCT((MOD(COLUMN($E7:$J7),2)=0)*$E7:$J7)

MOD 함수는 나눗셈의 나머지를 나타내는 함수로 다음과 같은 형식으로 사용합니다.

=MOD(숫자, 나눌수)

예를 들면 =MOD(13,3)의 결과는 1이 됩니다. 13을 3으로 나눈 나머지 값이기 때문이죠.

다시 수식을 보겠습니다.

E7셀에서 J7셀의 범위에서 우리가 구해야 할 값은 F열과 H열, J열이죠. 

해당 셀의 COLUMN 값은 6, 8, 10이 됩니다. 따라서 COLUMN() 값이 짝수인 경우만 더하면 되는 거죠.

이것을 수식으로 표현하면 아래와 같이 되죠

MOD(COLUMN($E7:$J7),2)=0

다음은 SUMPRODUCT 함수를 응용해야 하는데요,

SUMPRODUCT 함수는 본래 두 인수의 곱의 합을 구하는 함수입니다만 여기와 같이 특정 조건식에 활용하는 경우도 있습니다.

=SUMPRODUCT(조건1*조건2*……*합할범위)

이 형식은 자주 사용하는 식이니 꼭 기억해두시기 바랍니다.

따라서 원 식은 E7셀에서 J7셀 중  셀 COLUMN 값을 2로 나눈 나머지가 0인 셀만 더하면 되는거죠.

이해가 가셨나요....

또 다른 방법은 SUM 함수와 배열수식을 활용하는 방법입니다.

{=SUM(IF(MOD(COLUMN($E8:$J8),2)=0,$E8:$J8,0))}

수식은 SUMPRODUCT 함수를 사용한 것과 비슷하지만 약간 다르죠.

수식 좌우에 {} 표시가 있습니다.(이 표시가 배열수식이라는 것을 알려주는 부호입니다.)

SUM 함수를 사용하는 배열수식은 다음과 같은 형식을 가집니다.

=SUM(IF(조건,더할범위, 0))

수식을 해석하면 조건에 맞는 곳은 더하고, 그렇지 않는 경우에는 0으로 처리하라는 거죠.

만약, 조건이 두 가지 이상일 경우에는 앞서 본 SUMPRODUCT 함수를 이용하면 되겠죠.

배열수식은 반드시 수식을 마감하면서 그냥 Enter 키를 누르면 안됩니다.

Shift 키와 Ctrl 키를 누른 상태에서 Enter 키를 눌러야 합니다.(꼭 기억하세요.)

그렇지 않으면 결과가 0으로 환원되어 버립니다.

15행과 16행에 들어가는 수식도 위와 같은 경우로 따져보면,

F15셀과 F16셀에 아래와 같이 수식을 입력하는 방법이 가장 쉽지만 자료의 양이 많을 경우 곤란하다고 했죠.

다음은 SUMPRODUCT 함수와 행의 값을 반환하는 ROW 함수를 사용한 것이 위와는 다른 점이죠.

그리고 15행과 16행에서 MOD 함수의 결과값이 1과 0으로 다른 점을 잘 이해하시기 바랍니다.

마지막으로 배열수식을 활용한 예입니다.

여기서도 MOD 함수의 결과값이 1과 0으로 다른 점을 잘 이해하시죠.

일반적으로 조건이 여러 개일 경우에는 SUMPRODUCT 함수를 이용하고, 조건이 하나밖에 없을 경우에는 SUM 함수를 이용한 배열수식을 활용한다는 점을 기억해두시기 바랍니다.

배열수식에서도 2개 이상의 조건을 처리할 수는 있습니다.

IF 함수 안에 조건문을 (조건1*조건2*……) 형식으로 하면 되겠죠.

예제 파일을 첨부합니다.

반복행합계구하기.xlsx

도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!

COLUMN 함수는 열 값을 반환하는 함수입니다.

앞 시간에 배운 ROW 함수가 행 값을 반환하는 대신 COLUMN 함수는 열 값을 반환하기 때문에 두 함수는 짝을 이룬다고 보면 되겠죠.

그런데 활용하는 분야는 조금 다릅니다.

주로 한 셀에 있는 내용을 여러 칸에 나눠 입력하는 데 사용되는데요,

대표적인 것이 단위가 각각 한 칸씩 있는 세금계산서입니다.

우선 개념부터 이해하고 가겠습니다.

아래 B4셀과 B5셀에 입력된 내용은 각각 =COLUMN()과 =COLUMN(E2)입니다.

함수뒤에 () <--- 이 표시는 해당 셀을 말하는 것이죠.

B4셀에 있는 수식은 B4셀의 열 값을 반환하라는 것이죠. B열 이므로 2가 되겠죠.

반면 B5에 있는 수식은 E2셀의 열 값을 가져오라는 것이죠, E열은 5가 되겠죠.

이제 본격적으로 실습으로 들어가겠습니다.

예제파일은 아래와 같이 세금계산서와 업체데이터 두 시트로 이루어져 있습니다.

여기서 주의할 점은 업체데이터 중 사업자등록번호는 하이픈(-)까지 입력해야 한다는 점입니다.

하이픈(-)을 생략하면 세금계산서에 빈칸이 생깁니다.(직접 해보시면 이해가실 겁니다.)

참고로 이 파일은 제가 직접 작성한 것이 아니고, 인터넷 검색을 통해 가져온 것을 가지고 내용을 수정한 것임을 먼저 알려드립니다.

그럼 지금부터 수식 설명을 하겠습니다.

F4:Q4 이 범위에 있는 사업자등록번호가 한칸에 한 숫자 또는 하이픈(-)으로 들어갑니다.

가져올 데이터는 업체데이터 시트에 있는 C2셀의 데이터가 되겠습니다.

들어간 수식은 아래와 같습니다.

=MID(업체데이터!$C$2,COLUMN()-5,1)

조금 복잡해 보이죠. 원리를 이해하면 아주 쉽습니다.

우선 사업자등록번호는 하이픈(-)을 포함하면 모두 12자리로 이루어져 있습니다.

그래서 MID 함수를 이용해서 사업자등록번호를 자리수에 맞게 가져오려고 합니다.

위 수식은 업체데이터 C2셀에 있는 데이터를 한개씩 가져오라는 것은 알겠는데, COLUMN()-5가 무슨 뜻인지만 알면 되겠죠.

COLUMN()의 값은 현재 셀의 주소가 F4셀이므로 6이 되겠죠. 그런데 맨 처음 글자를 가져와야 하기 때문에 -5를 해줍니다. 그러면 F4셀의 수식은 아래와 같은 모양이 되겠죠.

=MID(업체데이터!$C$2,1,1)

그렇게 입력했으면 채우기 핸들을 이용해서 Q4셀까지 수식을 복사해줍니다.

G4에서 위 수식은 COLUMN()-5의 값이 2가 됩니다. 그래서 아래와 같은 식이 되겠죠.

=MID(업체데이터!$C$2,2,1)

같은 방법으로 H4셀부터 Q4셀까지 3번째부터 끝번째까지 한 글자만 가져오는 수식이 되는 거죠. 이해가 가셨나요?

앞서 만든 수식에서 달라져야 할 부분은 두 곳 밖에 없죠.

가져올 데이터 주소가 업체데이터 시트의 C9셀로 바뀌는 것과COLUMN()에서 얼마를 빼줘야 하는 것만 수정해주면 되죠.

맨 첫 셀이 있는 곳이 V열이죠. 알파벳 순서대로 세면 22번째 열이 됩니다.

COLUMN()-21 하면 되겠죠.

그래서 들어간 수식은 다음과 같습니다. 물론 채우기 핸들로 오른쪽으로 복사해주면 되겠죠.

=MID(업체데이터!$C$2,COLUMN()-21,1)

다음은 F14셀에 들어갈 공란수를 구하는 방법입니다.

공급가액에 들어갈 수 있는 숫자는 백억까지죠. 숫자로 표시하면 11자리까지 표시가 가능합니다.

그런데 공란수란 비어있는 칸이 몇 개인지를 말하는거죠.

공급가액에 들어갈 숫자는 아래 U16:Z19 입력된 가액을 모두 더해야 합니다.

SUM 함수를 사용한 이유입니다.

11자리에서 공급가액을 모두 더한 값의 길이를 빼면 공란수가 나오겠죠.

그래서 F14셀에는 다음과 같은 수식이 들어갑니다.

=11-LEN(SUM(U16:Z19)

U16:Z19의 범위는 U16:Z16으로 표기해도 되고, 그림과 같이 절대셀이 아닌 상대셀로 표기해도 됩니다.

다음은 공급가액을 입력할 차례입니다.

공급가액은 아까도 말했듯이 U16:Z19까지 모두 합한 금액을 각 자리수에 넣어줘야 한다고 했죠.

그래서 일단 들어갈 데이터는 SUM($U$16:$Z419)이 되는 것은 이해가시죠. 또 오른쪽을 끌어서 복사할 예정이므로 절대셀로 지정해 주어야겠죠.

다음은 모두 합한 금액을 11자리로 지정하기 위해 TEXT함수를 사용하겠습니다.

TEXT(값,"???????????") : 물음표(?)의 갯수만큼의 자리수 데이터로 인식하라는 뜻입니다.

TEXT(값, "#,###") : 수치 값을 천 단위마다 구둣점(,)을 찍어라는 뜻입니다.

따라서 위 방법대로 하면 TEXT(SUM($U$16:$Z419),"???????????")의 의미는 공급가액을 모두 합한 숫자를 11자리 문자로 인식하라는 것이죠.

다음 COLUMN()-7은 더 이상 설명하지 않아도 이해할 수 있겠죠.

=MID(TEXT(SUM($U$16:$Z$19),"???????????"),COLUMN()-7,1)

세액에 들어갈 수식도 마찬가지로 작성하면 되겠죠.

수식은 아래와 같습니다.

=MID(TEXT(SUM($AA$16:$AE$19),"??????????"),COLUMN()-18,1)

이제 마지막입니다.

B21셀에 합계금액을 구하는 수식을 입력할 예정인데요.

이건 너무 쉽죠.

U16셀에서 AE19셀까지 모두 더하면 되겠죠.

따라서 수식은 다음과 같습니다.

=SUM(U16:AE19)

여기서 잠깐 팁 한가지.

B14셀부터 E14셀까지 날짜 입력하는 곳 있죠.

오늘을 기준으로 날짜가 자동으로 들어가게 했는데요.

사용한 수식은 각 각  =YEAR(TODAY()), =MONTH(TODAY()), =DAY(TODAY()) 입니다.

예제 파일을 첨부합니다. 

tax-data.xlsx

도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!

ROW 함수는 특정 셀의 행 값을 반환하는 함수입니다.

아래 그림을 보겠습니다.

A3셀과 A4셀에 B열에 있는 수식이 들어있습니다.

A3셀에 있는 =ROW() 함수의 의미는 지금 있는 셀의 행 값을 알려달라는 것입니다.

A3셀은 3행에 있기 때문에 3이라는 숫자가 출력됩니다.

마찬가지로 A4셀에 있는 수식은 B2셀의 행 값을 알려달라는 것이죠.

B2셀은 2행에 있죠. 그래서 2라는 숫자가 나옵니다.

ROW 함수는 연번(순번, 일련번호)과 같이 1부터 순차적으로 부여되는 번호에 많이 활용됩니다.

아래 예제파일은 사상구청 홈페이지에서 가져온 모범음식점 지정현황입니다.

A열에 연번을 추가해 보겠습니다.

보통 연번은 A4셀과 A5셀에 1과 2를 입력한 뒤, A4:A5셀 범위를 지정하고 채우기 핸들을 더블클릭하죠.

자 연번이 채워졌습니다.

이번에는 원산갈비가 있는 10행을 삭제해 보겠습니다.

삭제하고 나니 아래와 같이 연번에서 7이 빠져버렸습니다.

연번을 다시 조정해야 하는 번거로움이 생겼네요.

삭제 또는 삽입을 해도 연번을 다시 조정할 필요가 없는 방법은 없을까요?

그 해답은 ROW 함수를 활용하는 방법입니다.

A4셀에 =ROW()-3 이라고 입력합니다.

이 수식의 의미는 ROW() 함수가 A4셀의 행번호를 알려달라는 거죠. 4행이니까 4가 출력이 되겠죠.

그런데 연번의 의미로 보면 1부터 시작해야 되니까 -3을 해주면 1이 되겠죠.

이해가 가시나요?

그런 다음 A4셀의 채우기 핸들을 더블클릭해줍니다.

1부터 순차적으로 연번이 입력되었습니다.

이번에는 주례원조 할매집이 있는 10행을 삭제해 보겠습니다.

앞에서와는 달리 연번에 7번이 삭제되지 않고 바로 밑에 있던 예향이 7번으로 자동 변경되고,

그 아래는 1씩 조정이 되었죠.

이와 같이 ROW 함수를 잘 활용하면 여러모로 편리하겠죠.

도움이 되셨다면 공감 꾸~욱 부탁드립니다.!!!

어제 2세대 이동전화서비스(2G)용으로 부여된 식별번호가 3세대 이동전화서비스(3G)로 전환된 사실에 대해서 간략하게 설명 드렸습니다.

오늘은 사업자별 변환용(01X → 010) 국번호 부여내용으로 변환된 전화번호를 구하는 엑셀 파일을 만들어 보겠습니다.

VLOOKUP 함수와 INDIRECT 함수, 그리고 기본적인 IF 함수를 사용할 것이고,

이중 유효성 검사와 간단한 셀서식 지정법이 활용되며, 셀 수식을 변경하지 못하도록 하는 셀보호 기능을 마지막에 활용할 예정입니다.

앞서 밝힌 내용에 대해 잘 아시는 분이나, 그냥 변경된 전화번호를 찾아주는 서비스만 필요하신 분은

다음 첨부한 파일을 그냥 다운받아서 사용하시면 되겠습니다.(대신 수식을 확인하고 싶은 분을 위해 Sheet2에는 잠금이 걸지 않으니 참고하시면 되겠습니다.)

phone-ch.xlsx

아래 화면과 같은 파일을 만들어 보겠습니다.

첨부된 파일을 열어서 범위를 확대하면 아래와 같이 VLOOKUP 함수에서 참조할 영역이 포함되어 있는 것을 알 수 있습니다.

아래 보시는 바와 같이 통신사와 구분이 입력되는 B3셀과 B4셀에 이중 데이터 유효성 검사를 지정할 예정이고,

(통신사에 따라 식별자 011, 017 등이 자동으로 유효성 검사에서 적용)

국번호 변경국에 대한 데이터는 010번호통합 정책 사이트에 있는 사업자별 변환용(01X → 010) 국번호 부여내용의 표를 참조하여 작성한 것입니다.(011~019까지를 각각 이름 정의하고 INDIRECT 함수를 이용하여 동적 참조영역을 활용)

VLOOKUP 함수에서 간격으로 찾는 방법은 배웠죠. 크거나 같다로 찾을 경우 오름차순으로 배열해야 한다는 사실도 기억하고 있죠. 

이중 데이터 유효성 검사를 위해 각 통신사별 식별번호를 선택영역을 이용해 이름을 지정하도록 하겠습니다.

G2:G4, H1:H4, I2:I3의 범위를 Ctrl키를 이용하여 모두 지정하고 

[수식] - 이름관리자에 있는 [선택 영역에서 만들기]를 클릭한 뒤,

이름 만들기 창이 열리면 [첫 행]에 체크하고 [확인] 버튼을 누릅니다.

제대로 된 지 확인을 하기 위해 [수식] - [이름관리자]를 클릭하면 아래와 같이 이름관리자가 열립니다.

값을 보니 제대로 입력이 되었네요.

그럼 B3셀에 유효성 검사를 지정하겠습니다.

B3셀을 선택하고 [데이터] - [데이터 유효성 검사] 를 클릭합니다.

아래와 같이 데이터 유효성 입력창이 열리면

제한 대상에 [목록]을 그리고 원본입력창에 마우스를 클릭한 뒤 [G2:I2]까지 범위를 설정하고 [확인] 단추를 클릭합니다.

다음은 C3셀에 이중 유효성검사를 지정할 차례입니다.

B3셀을 선택하고 [데이터] - [데이터 유효성 검사] 를 클릭하고,

데이터 유효성 창이 열리면 앞에서와 같이 제한 대상에 [목록]을 선택하고,

원본 입력창에 =INDIRECT(B3) 이라고 입력한 뒤 [확인] 버튼을 클릭합니다.

지금은 참조해야 할 B3셀에 아무것도 없기 때문에 아래와 같이 에러 메시지가 뜹니다만

상관할 필요 없이 [예]를 클릭하고 빠져 나옵니다.

이번에는 전화번호를 네자리 숫자로 표기되도록 셀서식을 고쳐보겠습니다.

E3셀을 선택하고 Ctrl키를 누른채 1번키를 누르면, 아래와 같이 셀서식 창이 나옵니다.

범주 중 최하단의 [사용자 지정]을 선택하고 형식창에 000#이라고 입력한 뒤 [확인]을 클릭합니다.

전화번호처럼 숫자라도 부족한 자리수에 0을 붙여야 하는 경우에 지정하는 방법입니다.

가령 일련번호가 000000~999999까지 입력되어야 한다면 00000# 이라는 형식이 되겠죠.

본격적으로 수식을 입력할 차례입니다.

B8셀은 B3셀에 입력된 내용이 그대로 내려와야되겠지요. 그래서 =B3이라고 입력됩니다.

구분은 나중에 010으로 수정할 예정이지만 지금은 그냥 전화번호와 같이 그냥 가져오겠습니다.

B8셀에 입력된 내용을 복사(Ctrl+C)한뒤에 C8셀과 E8셀을 선택하여 붙여넣기(Ctrl+V) 하면 되겠죠.

이번에는 변경된 국번호를 VLOOKUP 함수에서 참조할 범위를 유동적으로 지정하기 위해

각 구분자에 대한 범위에 이름을 지정하겠습니다.

K3:M32까지 범위를 지정하고 [수식] - 정의된 이름탭의 [선택 영역에서 만들기]를 선택합니다.

이름만들기 창이 열리면 아래와 같이 [왼쪽 열]을 선택하고 [확인]을 클릭합니다.

이름이 제대로 지정되었는지 [이름관리자]를 클릭해보니 아래와 같이 각 자료의 맨 첫줄만 지정되었습니다.

그래서 모두 범위를 새로 조정하겠습니다.

먼저 _011 이름을 클릭하고 참조대상 입력창에 마우스를 클릭하여 입력된 곳을 전부 지우고

마우스로 L3:M10까지 직접 지정한 뒤, 참조대상 입력창 옆에 있는 체크표시 아이콘을 클릭합니다.

위와 같이 _019까지 반복해서 범위를 수정해 줍니다.

제대로 되었는지는 아래와 같이 범위를 지정한 뒤에 이름창에 제대로 된 이름이 나오는 지 확인하면 됩니다.

이젠 변경되는 국번호를 찾는 수식을 만들 것입니다.

변경전 국번호를 찾아서 변경되는 국번호를 가져오는 것은 알겠죠.

하지만 국번호가 3~4자리에서 4자리로 통일됩니다.

그리고 국번호 오른쪽 세자리 숫자는 변동이 없다는 것을 아시겠죠.

그래서 변경전 국번호에서 변경되는 국번호 첫자리를 가져오고 나머지 세자리는 원래 있던 숫자를 붙이면 되겠죠.

그래서 수식은 아래와 같이 정의가 되겠죠

=VLOOKUP(D3, INDIRECT(C3),2)&RIGHT(D3,3)

그런데 이렇게 하면 에러가 납니다. 왜냐하면 범위 이름앞에 언더바(_)가 붙었기 때문에 그것까지 붙여줘야 합니다. 이럴 경우 연결연산자인 and(&)를 사용하면 된다고 했죠.

그래서 수식은 다음과 같이 수정하면 되겠죠.

=VLOOKUP(D3, INDIRECT("_"&C3),2)&RIGHT(D3,3)

그런데 원래 부여되지 않은 국번호에는 변경되는 국에 "통신사 문의"라는 문자를 넣어뒀습니다.

만약 원래 부여되지 않았던 국번호에 저 공식을 대입하면 아래와 같은 문자열이 나오겠죠.

통신사 문의(이어서 원래 국번호)

화면이 이상하게 나오겠죠.

"통신사 문의"가 출력되는 경우에는 해당 셀에 내용을 비우면 깔끔하겠죠.

그래서 IF 함수를 이용하겠습니다. "통신사 문의"가 나오면 비우고, 그렇지 않으면 위의 식을 적용합니다.

=IF(VLOOKUP(D3, INDIRECT("_"&C3),2)="통신사 문의","",VLOOKUP(D3, INDIRECT("_"&C3),2)&RIGHT(D3,3))

이해가 가시나요?

그런데 아래 그림을 보면 에러가 뜨네요.(D3셀에 아무것도 없기때문에 나온 것입니다.)

D3셀에 9000을 입력하면 문제가 해결되었네요.

지금부터는 화면을 좀 더 깔끔하게 하기 위한 작업입니다.

앞서 D8셀에는 "통신사 문의"라는 문자가 아니면 국번호가 출력이 됩니다.

그래서 전화번호가 들어있는 E8셀에도 D8셀이 비어 있으면 빈칸으로 만들겠습니다.

수식은 아래와 같습니다.

=IF(D8="","",E3)

이 함수는 너무 쉬워 설명이 필요 없겠죠. ^^

이번에는 변경되는 국번호인 010으로 변경할 차례입니다.

앞서도 밝혔듯 국번호와 전화번호가 비어 있는데 010 이라는 숫자가 출력되어 있으면 어색하겠죠.

그래서 이 셀 역시 D8셀이 비어 있으면, 비우려고 합니다.

=IF(D8="","","010")

이제는 부여되지 않은 국번호에 대한 안내를 만들겠습니다.

부여되지 않은 국번호에는 해당 통신사에 문의하도록 하고, 정상적으로 변환된 자료에는 번호 변경 안내 문구가 들어가면 되겠죠.

B9:E9셀까지 셀병합을 한 뒤에 아래와 같은 수식을 입력하겠습니다.

=IF(C8="",B3&"에 문의하세요!!","위 번호로 변경되었습니다!!")

위와 같이 수식을 입력하고 해당 셀은 [홈]탭을 이용해서 글자를 굵게, 색갈은 빨간색으로 변경합니다.

이제 모두 끝났습니다만 어렵게 입력한 수식에 누가 수정해버리면 난감하겠죠.

그래서 수식이 들어가거나 참조하는 범위 등에 접근을 못하게 하겠습니다.

셀 잠금 기능을 이용하는 건데요, 사용방법은 아래와 같습니다.

먼저 셀 잠금을 적용할 셀을 Ctrl키를 사용해서 아래 여러 범위를 지정합니다.

그런 뒤에 Ctrl키를 누른 상태에서 1번을 클릭합니다.(셀서식을 부르는 단축키란 것을 아까 배웠죠.)

사용자 지정 목록이 나오면 [보호]탭을 클릭하고 [잠금]에 체크를 한 뒤 [확인] 버튼을 클릭합니다.

다음, [검토]탭에 있는 [시트 보호]를 클릭하면 아래와 같이 시트 보호 창이 열립니다.

워크시트에서 허용할 내용 중 [잠기지 않은 셀 선택]에 체크 하시고 [확인]을 클릭합니다.

암호를 입력해도 됩니다만 암호를 입력하게 되면 나중에 암호를 모르면 시트 보호를 해제할 수가 없습니다.

참고하시고 적용하시기 바랍니다.

아래 그림에서와 같이 아까 잠금을 지정한 셀에는 마우스가 갈 수 없도록 되었네요.

도움이 되셨다면 공감 꾸~욱 부탁드립니다!!!

이번 시간에는 indirect 함수를 이용한 이중 유효성 검사를 배워보겠습니다.

유효성 검사에 대해서는  지난번에 배웠기 때문에 설명은 생략하겠습니다.

우선 오늘 예제파일은 아래와 같은 직원명부입니다.

이 중에서 소속에 이중 유효성 검사를 적용하고, 옆에 있는 직(급)은 그냥 유효성 검사를 적용할 예정입니다.

아래 그림을 보시면 기획감사실을 제외하고 행정지원국, 복지환경국, 도시건설국 등에 

하부 부서가 있습니다.

그리고 직(급)도 네 가지로 구성해봤습니다.

먼저 유효성 검사에 적용할 데이터에 이름을 지정하겠습니다.

① 먼저 I4셀부터 I9셀까지 마우스로 범위를 지정하고,

② Ctrl키를 누른 상태에서 J4:J10, K4:K9, L4:L8을 추가합니다.

다음은 [수식] 탭에 있는 [정의된 이름] 중에 [선택 영역에서 만들기]를 선택합니다.

이름 만들기 위한 창이 열리면 아래와 같이 [왼쪽 열]에 있는 체크는 지워주시고(안 지워도 되지만 필요없기 때문에 지웁니다. < -- 이것은 가로로 왼쪽에 있는 것을 이름 지정해 줍니다.)

[첫 행]만 체크한 뒤에 [확인]을 클릭합니다.

위에 선택 범위가 국과 직(급)이 길이가 다릅니다. 

그래서 아래와 같은 에러 메시지가 뜹니다.

여기서 [확인]을 클릭하면 행정지원국, 복지환경국 만 이름이 지정됩니다.

그래서 [확인]을 클릭하지 말고 오른쪽 상단에 있는 [닫기] 버튼을 클릭하고 빠져나옵니다.

자 제대로 이름 정의가 되었는 지 확인할 차례입니다.

앞에서 한 것처럼 [수식]탭에 [정의된 이름] - [이름 관리자]를 차례로 클릭합니다.

아래와 같이 이름이 지정된 것을 알 수 있습니다.

여기서 주의할 점은 직(급)의 경우 직_급으로 이름이 정의된 사실을 기억해 놓기 바랍니다.

자 이제 이중 유효성 검사를 설정할 차례입니다.

먼저 C5셀에서 C8셀까지를 범위 지정하고 [데이터]텝에 있는 [데이터 유효성 검사]를 클릭합니다.

유효성 설정하는 창이 나오면 아래와 같이 

제한 대상에 [목록]을 그리고 원본에는 =$H4:$K4 를 입력한 뒤에 [확인] 버튼을 클릭합니다.

이번에는 부서명에 국 하위 부서명을 이중 유효성 검사 설정할 차례입니다.

먼저 부서명이 들어갈 D5셀에서 D8셀까지 범위를 지정하고 [데이터]탭 [데이터 유효성 검사]를 선택합니다.

데이터 유효성 조건창이 열리면 아래와 같이 제한 대상을 [목록]으로 하고,

원본에 =indirect(c5) 라고 입력하고 [확인] 버튼을 누릅니다.

여기서 C5에 입력되는 값에 따라 부서명에 유효성 검사가 달라야 하므로 

앞에서 국별로 부서명을 국 이름으로 지정한 이유입니다.

즉, =indirect(c5) <---- c5에 입력되는 국 이름에 따라 부서명은 국 이름으로 유효성 검사를 설정한 이유죠.

제대로 되었다면 아래와 같은 에러 메시지가 뜹니다.

이유는 왼쪽 실/국에 아무 것도 선택되지 않았기 때문이니 무시하고 [예]를 클릭합니다.

다음은 직(급)에 데이터 유효성 검사를 설정할 차례죠.

아래와 같이 범위 설정하고 [데이터] 탭 [데이터 유효성 검사]를 클릭합니다.

데이터 유효성 조건 입력창이 열리면, 아래와 같이 제한 대상에 [목록]을 선택하고,

아까 직(급)의 범위가 직_급 이었던 것을 기억하시죠.

그래서 원본에는 =직_급 이라고 입력하고 [확인] 버튼을 클릭합니다.

자 이제 제대로 되었는 지 확인해 보겠습니다.

행정지원국을 선택한 뒤에 부서명을 보면 자치행정과, 회계재산과 등이 제대로 나오는군요.

이번 시간에는 indirect 함수를 이용하여 이중 유효성 검사를 설정하는 방법을 알아보았습니다.

실습했던 파일을 첨부합니다.

기초-3.xlsx

도움이 되셨다면 공감 꾸~욱 부탁드립니다 !!!

오늘은 지난 시간 약속대로 참조할 영역이 다른 시트일 경우에는 어떻게 하는지를 알아보겠습니다.

아래 예제를 보겠습니다.

위 예제 역시 성적표 입니다.

조금 다른 점이 있다면 위와 같이 성적표 시트가 따로 있고,

아래와 같이 가져올 참조 데이터 영역은 중간고사, 기말고사라는 시트가 따로 있는 경우입니다.

다행인 것은 중간고사 시트와 기말고사 시트의 참조영역 범위가 똑같다는 것이죠.

만약 범위가 다른 경우라면 상당히 난감한 상황이 발생합니다.

(뭐 그래도 가장 데이터가 많은 행을 기준으로 하면 그럭저럭 위기는 모면하겠죠.)

이번에도 마찬가지로 indirect 함수를 사용할 것입니다.

우선 시험종류 아래 B4 셀에 데이터 유효성 검사를 지정하겠습니다.

제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.

이젠 성적을 가져올 차례입니다.

여기서 잠깐 생각해봅시다.

B4 셀의 값이 중간고사일 경우와 기말고사일 경우 참조영역은 다음과 같이 표현할 수 있습니다.

• 중간고사일 경우 참조영역: 중간고사!$B$3:$I$12

• 기말고사일 경우 참조영역: 기말고사!$B$3:$I$12

참조영역을 가져오기 위해 indirect 함수를 사용한다고 예고했었죠.

그럼 저 영역중에 중간고사와 기말고사는 시트명으로 들어가기 때문에 참조영역이 가능합니다.

그래서 B4셀의 값을 그냥 가져오면 되겠죠.

문제는 $B$3:$I$12의 범위를 어떻게 삽입할 것인가 하는 것입니다.

indirect 함수에서 참조범위가 아닌 것은 모두 데이터로 입력해야 한다는 것을 지난 번에 배웠죠.

그래서 "!$B$3:$I$12" <---- 이 범위가 추가되면 되겠죠.

그런데 한가지 문제가 있습니다.

참조영역과 데이터를 연결하는 방법의 문제인데요,

indirect(B4"!$B$3:$I$12") <--- 이렇게 수식을 넣으면 어떻게 될까요?

입력이 되지 않습니다. 참조영역+데이터의 형식이 되기 때문입니다.

다시 말하자면 참조영역 형식도 아니고 데이터 형식도 아닌 잡탕이 되었기 때문에 인식이 안됩니다.

그래서 엑셀 프로그램이 인식하도록 약간의 기술이 필요합니다.

바로 & 라는 연결연산자가 등장하는거죠.

위 참조영역을 다시 맞게 정리하면 아래와 같습니다.

indirect(B4&"!$B$3:$I$12")

위 그림에서 C6셀에 들어간 수식은 다음과 같습니다.

=VLOOKUP($B$6,INDIRECT($B$4&"!$B$3:$I$12"),2,0)

시트 탭이 몇 개라도 형식만 똑같이 맞춰두면 동적 참조영역 얼마든지 쉽게 만들수 있겠죠!

도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^

오늘은 indirect 함수를 배워보겠습니다.

이 함수는 텍스트로 지정한 셀 주소를 반환해주는 함수인데요,

앞서 배운 offset 함수가 동적 영역을 설정하는 반면,

indirect 함수는 동적 참조영역을 설정하는 함수입니다.

무슨 말인지 헷갈리죠?

이렇게 말하는 저도 쉽게 설명 못해 미치겠습니다.

간단히 예를 들면 아~하 라고 쉽게 이해하실 수 있을 것으로 보고

바로 예제로 들어가겠습니다.

아래는 성적표입니다.

같은 시트에 성적표가 두개 있죠.

왼쪽에 있는 것은 중간고사이고 오른쪽에 있는 것은 기말고사입니다.

하단에 보시면 시험종류에 따라 학생의 이름을 입력하면 그 학생의 성적표가 나오도록 하는 것입니다.

물론 if 함수를 사용하면 되겠죠.

B16셀에 

• 중간고사가 입력되면 참조범위를 $B$3:$I$12로

• 기말고사가 입력되면 참조범위를 $K$3:$K$12로

지정해주면 되죠.

C18에는 아래과 같은 수식이 입력이 되겠죠.

=VLOOKUP(B18, IF(B16="중간고사", $B$3:$I$12, $K$3:$K$12), 2, 0)

시험이 두 개밖에 없어 중간고사가 아니면 기말고사라면 저렇게 하면 되죠.

문제는 시험이 두 개만 있는 것이 아니고 매월 한번씩 있다면 

if 함수로 일일이 범위를 지정하는 것은 노가다가 되겠죠.

그래서 이럴 경우 사용하는 것이 바로 indirect 함수입니다.

위 그림과 같이 한 시트에 찾기참조할 범위가 두 개 이상 있는 경우에는

지난 번에 배운 이름을 정의하여 각 이름을 범위로 지정해 주는 방법이 간단하겠죠.

이름 지정하는 방법은 배웠죠. 그래서 생략합니다.

대신 오늘은 데이터 유효성검사를 활용하는 법을 알아보겠습니다.

데이터 유효성검사는 셀에 입력하는 자료가 유효한지 아닌지를 검사하는 거죠.

요즘 특정 셀에 정해진 데이터 외에는 선택하지 못하게 하는 방법으로 많이 활용합니다.

B16셀에서 [데이터] - [데이터 유효성 검사] - [데이터 유효성 검사 만들기] 를 선택합니다.

아마 아래와 같은 창이 하나 나타날 것입니다.

각 부분에 대한 설명은 생략하겠습니다. (개별적으로 아시고 싶은 분들은 인터넷 검색하면 됩니다.)

아래와 같이 입력합니다.

제한 대상에 [목록]을 선택하시고, 원본: [중간고사,기말고사]라고 입력하시고 [확인]을 클릭합니다.

제대로 되었다면 아래와 같이 셀 옆에 드롭다운 표시가 나왔죠. 

이제 이 셀에서는 중간고사와 기말고사 외에는 입력이 안됩니다.

(물론 목록 추가하면 추가한 목록도 입력할 수 있겠죠.)

자 이제 돌와와서 참조할 두 곳의 범위에 이름을 중간고사, 기말고사라고 각각 붙이겠습니다.

눈치 빠르신 분은 왜 그렇게 하는지 조금 눈치를 채셨을껍니다.

이름 지정하는 것은 앞에서 배웠으니 그냥 마우스로 범위 지정해서 정하겠습니다.

이젠 성적을 가져올 차례입니다.

여기서 잠깐, indiredt 함수는 가져올 데이터가 특정 행의 데이터일 경우에는 겹따옴표("")로 묶어줍니다.

반면에 가져올 데이터가 참조되는 값일 경우에는 그냥 셀주소만 가져옵니다.

아래 그림을 보시면

K16셀에는 =INDIRECT(K5)라고 입력되었지만 K5에는 그냥 데이터가 입력되어 있기 때문에 애러가 납니다.

K17셀에는 =INDIRECT("K5") 데이터가 입력되었기 때문에 겹따옴표("")로 막았죠. 그러면 셀값이 출력되죠.

K18셀에는 =INDIRECT(N14) N14셀에는 K5라는 셀주소가 입력되어 있죠, 참조가 되는거죠. 그래서 K5의 내용을 가져옵니다.

그러면 다시 본래대로 돌아가겠습니다.

결국 범위이름을 B16셀에 입력하는 데이터와 일치시켰으니, 찾기참조를 B16셀에서 가져오면 되겠죠.

그래서 C18셀에 위 그림과 같은 아래 식이 입력된 것입니다.

=VLOOKUP($B$18, INDIRECT($B$16), 2, 0)

INDIRECT($B$16)의 값은 우리가 B16셀에서 지정해 주는 범위로 변한다는 것 이해가세요.

이번 시간에는 같은 시트에 동적 참조영역을 만드는 방법을 알아보았습니다.

다음 시간에는 시트마다 입력된 형식은 같지만 

서로 다른 시트를 동적참조영역으로 만드는 방법을 알아보겠습니다.

도움이 되셨다면 공감 꾸~욱 눌러주시고 가세요. ^^

앞서 예고했듯이 오늘은 offset 함수를 이용한 동적범위를 지정 방법을 알아보겠습니다.

범위는 나누는 방법에 따라 다르겠지만 정적범위와 동적범위로 나눌 수 있습니다.

매년 국세청에서 제공하는 갑종 근로소득세 간의세율표 처럼 한번 정해놓으면 1년 내내 사용하는 표가 있는 반면,

회원명부나 고객명부 등과 같이 1년 내내 변동되는 자료가 있을 수 있습니다.

이렇듯 한번 정해 놓으면 일정기간동안 변동이 없는 자료범위를 정적범위라고 하고,

수시로 자료의 범위가 변동되는 것을 동적범위라고 합니다.

즉, 정적범위는 고정된 범위지만, 동적범위는 유동이 가능한 범위가 되겠죠.

아래 그림을 보겠습니다.

부산 사상구청 홈페이지에서 제공하고 있는 모범음식점지정현황 중 일부를 발췌한 자료입니다.

원본에는 모두 68개 업소가 지정되어 있습니다.

G4셀에 업소명을 입력하면 H4, I4셀에 주된음식과 전화번호가 출력되도록 해보겠습니다.

H4셀에 들어갈 수식은 vlookup 함수를 배웠으니 아래와 같이 정리하면 되겠죠.

VLOOKUP($G$4,$A$3:$E$13,3,0)

마찬가지로 H4셀에 들어갈 수식은 아래와 같이 됩니다.

VLOOKUP($G$4,$A$3:$E$13,4,0)

지금은 업소명에 아무것도 입력되어 있지 않기때문에 에러(#N/A)가 뜹니다.

두번째 업소명을 입력해 보겠습니다.

네 이젠 제대로 나왔네요.

이 예제에서 보면 vlookup 함수가 찾는 범위가 아래와 같이 $A$3:$E$13입니다.

그런데 아래에 두 줄을 추가해 보겠습니다.

이러면 vlookup 함수가 찾는 범위가 $A$14:$E$15까지의 범위를 추가해야 합니다.

한두 번 정도야 범위를 확장하겠지만,

자주 이런 경우가 생기면 비효율적이죠.

그래서 동적범위를 활용하여 추가 삭제가 되면 자동으로 범위가 변동되는 것을 하겠습니다.

offset 함수는 배웠으니 생략하고, 동적범위를 지정하기 위해 알아야 할 함수가 하나 더 있습니다.

바로 counta 함수입니다. 이 함수는 지정된 범위안에 내용이 있는 셀의 숫자를 세는 함수입니다.

열 전체를 지정하는 방법은 $열:$열 형식입니다. 

예를 들어 A열 전체를 지정하는 범위는 $A:$A가 되는 겁니다.

행 전체를 지정하는 방법도 마찬가지로 $행:$행 형식입니다.

이 역시 3행을 모두 지정하면 $3:$3 이 되는거죠.

업소명에서 가장 마지막 데이터까지 는 A열 전체에서 내용이 있는 셀이 몇 개인지 세면 되겠죠.

수식으로 나타내면 아래와 같습니다.

COUNTA($A:$A)

A3셀을 기준점으로 봤을 때 아래로 몇칸을 추가하면 되는 지를 COUNTA($A:$A) 값에서 찾으면 되죠.

마찬가지로 3행 전체에서 내용이 있는 셀이 몇 개인지 세는 방법 역시 아래와 같겠죠.

COUNTA($3:$3)

그런데 여기서 주의할 사항이 하나 있습니다.

우리가 포함해야 할 범위는 5칸인데, 위와 같이 세면 G3~H3까지 3개의 셀이 추가되어 버립니다.

그래서 우리가 포함해야 할 범위는 COUNTA($3:$3) 값에서 3을 빼야됩니다.

결국 우리가 동적범위로 작업할 영역은 offset 함수로 표현하면 다음과 같습니다.

OFFSET(A3,0,0,COUNTA($A:$A),COUNTA($3:$3)-3)

지난번에 포함할 영역이 없을 때에는 맨 마지막 1,1을 생략할 수 있다고 했죠.

이번에는 기준셀에서 아무런 이동 없이 영역만 포함하는 경우에는 0,0을 생략할 수 있다는 것을 알려드릴께요.

위 수식은 이동이 없기 때문에 아래와 같이 할 수도 있습니다.

(중요한 것은 0만 생략해야지 콤마(,)까지 생략하면 안된다는 점입니다.)

OFFSET(A3,,,COUNTA($A:$A),COUNTA($3:$3)-3)

자 이번에는 범위를 이름으로 지정하는 방법을 배우겠습니다.

범위를 이름으로 지정하는 방법은 두 가지가 있습니다.

마우스로 해당 범위를 지정한 뒤에 이름상자에 직접 이름을 넣는 방법입니다.

아래 그림을 보시면 이해가 되실 겁니다.

다른 방법은 [수식]탭 의 [정의된 이름]에 있는 [이름 정의]를 사용하는 방법입니다.

먼저 [수식]탭의 [정의된 이름]에 있는 [이름 정의]를 클릭합니다.

그러면 아래와 같은 새 이름 창이 하나 뜹니다.

각 부분의 활용법은 다음과 같습니다.

① 이름을 입력합니다.

② 사용할 범위를 지정합니다.(통합 문서 또는 특정 시트에서만 사용할 수도 있음)

③ 설명을 입력합니다.(생략가능)

④ 참조대상에 방금 offset 함수로 표현되는 수식을 입력합니다.

여기서는 작업영역으로 이름을 정의하고 만들어 놓았던 수식을 참조대상에 입력합니다.

OFFSET(A3,0,0,COUNTA($A:$A),COUNTA($3:$3)-3)

입력하고 난 후에 다시 열어보면 아래와 같이 나옵니다.

특이한 점은 셀주소 앞에 해당 시트 번호가 추가된다는 점이죠.

Sheet7! <--- 이 형식입니다.(이것은 자동으로 입력되는 것이니 신경 안쓰셔도 됩니다.)

자 이제 제대로 지정되었는지 확인하는 방법입니다.

아까와 마찬가지로 [수식]탭 의 [정의된 이름]에 있는 [이름관리자]를 클릭합니다.

그러면 아래와 같은 창이 새로 뜹니다.

아까 범위 지정하고 만든 데이터라는 이름영역과 방금 만든 작업영역이 보이죠.

그중 작업영역을 클릭하면

하단에 입력된 수식이 나옵니다.

이 수식 안에 마우스 커서를 넣고 클릭 한 번만 합니다.

그러면 아래와 같이 지정된 범위가 나타나게 됩니다.

나타난 영역이 제대로 된 것을 확인할 수 있습니다.

마지막으로 vlookup 함수에서 범위를 이름으로 다시 지정해주면 끝나겠죠.

이해가 되셨나요?

유용하게 활용하세요. ^^

오늘은 offset 함수를 배우겠습니다.

offset 함수는 주로 범위를 지정할 때 자주 사용하는 함수입니다.

특히 범위를 이름으로 지정하는 경우에 자주 쓰입니다.

보통 사원명부와 같은 자료에서 내가 원하는 자료를 찾아내기 위해서는

앞에서 배운 vlookup 함수나 index match 함수를 활용하면 찾기는 쉽죠.

그런데 이 명부가 변동이 없다면 참 좋겠습니다만

대부분이 그렇지가 않죠.

사원명부에는 신입사원이 새로 생길수도 있고, 퇴직하는 사원이 있을 수도 있습니다.

즉 추가, 삭제가 자주 이루어지면 원하는 자료를 찾기 위해 범위를 새로 지정해야되죠.

한번 두번은 그럴 수 있지만 자주 이러한 일이 반복되면 짜증이 많이 나겠죠.

이럴 때 사용하는 것이 자료의 범위를 이름으로 지정하는 방법입니다.

대신 이름으로 지정한 범위도 변동사항에 따라 자동으로 변하게 하면 되겠죠.

서설이 길어졌네요.

기본적인 문법부터 알려드리겠습니다.

OFFSET(기준셀, 행 이동 칸수, 열 이동 칸수, 행 포함 칸수, 열 포함 칸수)

기준셀 : 기준이 되는 셀입니다.(시작점으로 생각하시면 됩니다.)

행 이동 칸수 : 아래로 이동할 칸수를 말합니다.

열 이동 칸수 : 오른쪽으로 이동할 칸수를 말합니다.

행 포함 칸수 : 현재 위치에서 아래로 포함할 행수를 말합니다.(자료의 높이라고 생각하시면 됩니다.)

열 포함 칸수 : 현재 위치에서 오른쪽으로 포함할 행수를 말합니다.(자료의 폭으로 보시면 됩니다.)

예제를 보겠습니다.

위 예제는 이가을 학생의 총점을 구하는 문제입니다.

눈으로 보면 A3셀을 기준으로 아래로 5칸, 오른쪽으로 6칸을 가면 원하는 자료가 있죠.

위 그림을 보시면 D18셀에 들어있는 공식을 이해할 수 있겠죠.

D18셀에 들어있는 수식은 다음과 같습니다.

OFFSET($A$3,5,6)

그런데 처음에 배운 문법에서 두 가지가 빠졌죠.

바로 포함할 행수와 열수가 빠졌습니다.

위와 같이 한 셀만 가지고 값을 가져올 때는 포함할 행수와 열수가 각각 1, 1이 됩니다.

이럴 경우에는 생략이 가능합니다.

위의 예제를 보시면 match 함수와 같이 사용해서 5행 6열을 이동하는 것은 간단하게 이해되죠.

OFFSET($A$3,MATCH("이가을",$A$4:$A$12,0),MATCH("총점",$B$3:$I$3,0))

A열에서 이가을 이름을 찾고 3행에서 총점을 찾는 방법이죠.

문자열을 직접 입력할 때는 겹따옴표("")로 싸야되는 것 꼭 기억하세요.

이제 제대로 된 offset 함수를 알아보도록 하겠습니다.

위 그림은 이가을, 박겨울, 장여름 학생의 수학과 사회 점수를 모두 더한 값을 구하는 문제입니다.

A3 셀을 기준으로 하면 아래로 5칸, 오른쪽으로 2칸 가면 이가을 학생의 수학 점수가 나옵니다.

그 지점에서 아래로 3칸 오른쪽으로 2칸에 있는 모든 자료를 더하면 되죠.

그래서 D18셀에 들어있는 수식은 다음과 같습니다.

SUM(OFFSET($A$3,5,2,3,2))

이해가 되셨습니까?

offset 함수를 이용하여 범위를 이름으로 정의하여 

자료의 추가 삭제가 되어도 자동으로 범위를 잡아주는 방법은 다음 시간에 배우겠습니다.

아래의 파일은 유정파란 분이 만든 메크로인데, 참고하시면 되겠습니다. ^^

offset이해하기.xlsx