실무 적용

붙어있는 셀값 일정 간격으로 띄우기

excelcopycat 2020. 11. 3. 18:04

일렬로 있는 셀값을 일정 간격으로 벌리기
2칸 띄어 값 불러오기
3칸 띄어 값 가져오기
OFFSET 함수, COLUMN 함수 조합

일정간격 벌어지게 값 가져오기.xlsx

안녕하세요. Excelcopycat입니다.
지난번에는 떨어져 있는 값들을 일렬로 정리하는 법을 포스팅했었어요.
관련글은 제일 밑에 링크 걸어두었습니다.
이번에는 반대의 케이스예요. 일렬로 잘 정리된 data를 일정간격으로 띄우는 방법입니다.
아래 예시를 보시면 월별 목표를 세웠는데, 상사가 목표 옆에 실적을 추가하라네요.. 하.... 시킬 때 첨부터 말하지...
아무튼 목표값은 이미 만들었으니 새로운 틀로 값을 가져와야 하는데, 이게 원본은 1칸씩 이동하는데, 새로운 표는 2칸씩 넘어가니...
=셀 해서 복사 붙여 넣으면 2칸씩 이동한 값이 적히게 됩니다. 그래서 보통은 =셀클릭 =셀클릭 =셀클릭 반복하면서 시간이 좀 걸리지만 아무튼 표를 완성 합니다. 아래처럼요.


이 수작업을 OFFSET 함수와 COLUMN 함수를 이용해서 간단히 해보겠습니다.

우선 OFFSET 개념을 말씀드리면,
지정한 첫번째 셀에서 상하로 몇칸, 좌우로 몇칸 이동한 셀의 값을 가져와라 입니다. 여기서 0은 제자리, 음수는 위와 왼쪽, 양수는 아래와 오른쪽을 의미합니다.
=OFFSET($C$3,0,0) 은 C3 이고요,
=OFFSET($C$3,0,1) 은 D3 이고요,
=OFFSET($C$3,1,0) 은 C4 입니다.

가져오려는 값을 OFFSET 함수로 표현해보면 위와 같아요.
흠...0 1 2 규칙이 보이네요.

자 다시 정리해볼게요.
입력칸은 오른쪽으로 2칸씩 이동하는데, 원본칸은 오른쪽으로 1칸씩 이동해요.
아래 표를 봐주세요. 오...입력칸을 2로 나누면 원본칸이 되네요!!

이거를 다른 형식으로 풀면 아래와 같아요. 단 괄호 주의해주세요.

0=(1-1)/2
1=(3-1)/2
2=(5-1)/2
3=(7-1)/2
4=(9-1)/2

이제 규칙이 더 잘 보이시죠? 입력칸이 2칸씩 이동하면 원본에선 1칸씩만 이동하는 거예요. 자 이제 마지막으로 1,3,5 부분을 COLUMN 수식으로 바꿔주겠습니다.
COLUMN 은 셀의 열 순서를 알려주는 함수입니다.
COLUMN(A1)=1 COLUMN(B1)=2 이렇게요.

0=(COLUMN(A1)-1)/2   =(1-1)/2

OFFSET과 최종 합체를 해볼까요?

=OFFSET($C$3,0,(COLUMN(A1)-1)/2)

위의 수식을 복사하여 E7 셀에 붙여 넣으면 COLUMN(A1)은 COLUMN(C1)이 되죠.
그럼 수식이 =OFFSET($C$3,0,(3-1)/2)  =OFFSET($C$3,0,1) 이 되죠.
또 G7 셀에 붙여 넣으면 COLUMN(C1)은 COLUMN(E1)이 되죠.
그럼 수식이 =OFFSET($C$3,0,(5-1)/2)  =OFFSET($C$3,0,2) 가 되죠.


위의 예시는 2칸 띄어서 값을 가져온 경우이고요.
상사가 목표 옆에 실적과 차이를 추가해보라고 하면 이젠 쉽죠. 3칸 띄우는 거니까 나누기 2대신에 3으로 바꾸기만 하시면 되요. 

참고로 각 셀의 /2 를 /3 으로 바꾸시면 시간이 오래 걸리죠.
간단하게 하려면 해당셀들의 범위를 선택하시고 Ctrl h 누르시면 바꾸기 창이 뜹니다. 거기서 위에는 /2 아래는 /3 적으시고 모두바꾸기 누르세요. 그럼 /2가 싹 /3으로 바뀝니다. 위에 2 아래 3 적으면 D2의 셀번호 2까지 3으로 바뀌니까 꼭 /2 /3 으로 적어주세요.


상사가 이리 해봐 저리 해봐.. 할 때 정말 힘들죠. 그러나 우리는 빠르고 정확하게 계산해서 칼퇴를 해야 하니, 여러 엑셀 기능들을 알면 좋겠죠 ^^ 조금이나마 여러분의 야근 시간을 줄이는데 도움이 되었길 바라며 마치겠습니다. 읽어주셔서 감사합니다.


[관련글]

2020/10/26 - [실무 적용] - 일정간격 띄엄띄엄 떨어진 셀값 가져오기/합계