일정간격 띄엄띄엄 떨어진 셀값 가져오기/합계

일정 간격으로 떨어진 값을 간격 없이 가져오기
1. OFFSET / COLUMN 기본 개념
2. 일정하게 띄어진 셀 값들 가져오기
3. 활용 : 띄엄띄엄 떨어진 셀들의 합계 가져오기

일정간격값 가져오기 예제.xlsx

안녕하세요 Excelcopycat입니다. 오늘도 엑셀 노가다를 해결해줄 또 다른 수식을 알아보려해요. 시트에서 년간 정보를 관리한다면 특정 값들이 일정한 간격으로 나열되어 있을거예요. 그런 자료들 중에 한가지만 다른 시트로 요약을 한다면...

보통은 아래처럼 각각의 셀에 =가져올값셀클릭 을 하죠. 

맞는 방법이예요. 다만 데이터가 많으면 시간도 걸리고, 시트 왔다 갔다 정신도 없고, 실수도 할 수 있죠. 그래서 이번에는 간단하게 일정간격으로 떨어져 있는 셀들의 값을 가져오는 방법을 알아보겠습니다.

혹시 반대의 경우를 찾으신다면, 포스팅 끝에 링크 걸어두었어요. 참조하시면 되는데, 논리는 이것과 비슷해요. 그래서 이 포스팅도 보시고 가시면 이해가 더 쉬울 것 같네요.

1. 관련 함수 기본 사용법

OFFSET

OFFSET 함수는 지정된 셀에서 상하로 몇칸, 좌우로 몇칸 움직여서 그 위치에 있는 셀을 가져오는 거예요. 아래 그림으로 보면 이해가 더 쉬우실거예요.

다시 설명하자면, OFFSET(시작셀,상하로몇칸,좌우로몇칸) 입니다.
시작셀 :  A1으로 했고 고정하기 위해 달러를 F4키 한번 눌러서 입력했어요.
상하로이동 : 0이면 제자리, 1이면 아래로 한칸, -1이면 위로 한칸입니다.
좌우로이동 : 0이면 제자리, 1이면 오른쪽으로 한칸, -1이면 왼쪽으로 한칸입니다.

=OFFSET($A$1,0,0) : 제자리의 값을 가져오라는 것이라서 답은 1입니다.  
=OFFSET($A$1,1,1) : A1에서 아래로 한칸, 오른쪽으로 한칸 떨어진 곳의 셀을 가져오라는 것이니 답은 5가 나오죠.
=OFFSET($A$1,5,2) : 아래로 5칸 오른쪽으로 2칸 이동, 즉 C6 셀을 가져오는거죠.

제자리는 0이고 음수 사용하면 위로 좌로, 양수는 아래로, 우로 움직이는 것 기억해 주세요.

---

COLUMN

이 함수는 셀번호의 열이 몇번째 열인지 알려주는 거예요. 예제를 보면 쉽게 이해됩니다.
=COLUMN(A1) --- 1
=COLUMN(B1) --- 2
=COLUMN(C1) --- 3
열의 순서를 알려주는거라서 행이 천이든 만이든 상관 없습니다.
=COLUMN(A100) --- 1
=COLUMN(D1500) --- 4

---

2. 일정하게 떨어진 셀 값들 가져오기

단순히 수식만 외워서 할 수도 있지만, 이 함수는 아주 자주 사용하는 것은 아니라서 이해를 하는 것이 나중에 기억하기에 좋을 것 같아 풀어서 설명해볼게요.
아래 단계는 입력 순서가 아니라 이해를 위한 순서인점 말씀 드립니다.

1단계 : 각 셀을 OFFSET으로 값 가져오기

1월 계획은 아래와 같이 OFFSET으로 가져올 수 있어요.
=OFFSET(시작셀,상하이동,좌우이동)
=OFFSET($B$4,0,0)
시작 셀이 가져오려는 값이니까 0칸씩 이동한 것이죠.

2월 계획 값은 어때요? 역시 B4에서 시작하면 상하 이동은 없고(0), 우측으로 2칸 이동한 곳에 있죠? 수식으로 보면 =OFFSET($B$4,0,2) 입니다.
3월 계획은 =OFFSET($B$4,0,4) 입니다.
뭔가 규칙이 보이죠? 0,2,4,6..... 네 2단계로 가시죠.

---

2단계 : 일정간격 계산식

자 이제 0,2,4,6의 값을 다른 방식으로 표현해볼게요.
0=1*2-2
2=2*2-2
4=3*2-2
6=4*2-2
또 다시 규칙이 보이시죠? 셀을 오른쪽으로 복사해가면서 1씩 늘어나는 함수를 사용하고, 거기에 곱하기 2, 빼기 2를 하면 되겠군요!!

---

3단계 : COLUMN 함수 입히기

앞에서 COLUMN(A1) 값이 1인거 기억나시죠? 그리고 B1 C1 즉 오른쪽으로 갈수록 2,3 이렇게 숫자가 증가하죠.
그럼 위의 수학 공식(?)에 COLUMN 함수를 넣어보죠.

0=COLUMN(A1)*2-2
2=COLUMN(B1)*2-2
4=COLUMN(C1)*2-2
6=COLUMN(D1)*2-2

---

4단계 : OFFSET과 결합

1단계 OFFSET 수식에 0,2,4,6 부분이 있었죠? 0 값에 3단계에서 만든 식을 대입해보죠.
=OFFSET($B$4,0,0)
=OFFSET($B$4,0,COLUMN(A1)*2-2)

자 이제 이 수식을 복사해서 오른쪽으로 붙여넣어 보세요. 그러면 2칸씩 떨어진 값들을 가져옵니다.

COLUMN 안의 셀 A1은 달러로 고정시키면 안돼요. 오른쪽으로 복사붙여넣을 때 A1도 같이 B1 C1 D1 으로 이동해야 하니까요.

---

3. 활용 : 띄엄띄엄 셀들의 합계 가져오기

위에서는 일정 간격으로 떨어진 값들을 순서대로 가져오기를 하였습니다. 셀 값을 그냥 가져왔죠. 그런데 그 열의 합계를 가져와야 하는 경우도 있죠. 물론 해당 항목들의 합계를 계산한 다음, OFFSET-COLUMN 함수로 가져오면 됩니다만 다른 방법도 있답니다.

더하기 함수는 SUM이죠? 범위 합계 모양은 =SUM(셀:셀) 이죠.
위 예시를 보면 =SUM(C4:C6) 이네요.

OFFSET($C$4,0,COLUMN(A1)*2-2 = OFFSET($C$4,0,0) = C4
두번째 셀은 OFFSET($C$6,0,COLUMN(A1)*2-2 = OFFSET($C$6,0,0) = C6
즉, SUM 함수 안에 있는 C4와 C6 대신에 OFFSET함수를 넣으시면 합계를 계산하면서 값을 가져오게 됩니다.
=SUM(OFFSET($C$4,0,COLUMN(A1)*2-2):OFFSET($C$6,0,COLUMN(A1)*2-2))

아니면 OFFSET 함수 끝에 범위를 추가해서 SUM 속에 넣어도 됩니다.
=SUM(OFFSET($C$4,0,COLUMN(A3)*2-2,3,1))
끝에 3과 1이 추가되었죠?
OFFSET($C$4,0,COLUMN(A3)*2-2,3,1) 이거는 조금 간단히 쓰면 OFFSET($C$4,0,0,3,1) 이죠. C4에서 상하/좌우 0번 이동 후 3행 범위(자기셀은1로카운팅), 1열 범위(자기셀은1로카운팅) 즉 C4:C6 이란 범위를 뜻하는 것이예요.
이 범위의 숫자를 더하라(SUM)는 수식입니다.

아래 예시 보시면서 연습해 보세요.

---

OFFSET 함수 조금은 헷갈릴 수 있어요. 그래도 COLUMN과 함께 노가다를 줄여줄 수 있는 아주 소중한 함수입니다. 조금 시간 내셔서 OFFSET 함수 연습해보면 좋을 것 같아요. 2칸이면 *2-2 였죠. 3칸씩 떨어져 있으면 *3-3 이렇게 변형시키면 됩니다.

읽어주셔서 감사합니다. 즐거운 하루 보내세요~~

[관련글]

2020/11/04 - [실무 적용] - 붙어있는 셀값 일정 간격으로 벌리기