참조 열은 1칸, 기록할 열은 2 칸씩 움직일 때

두 칸씩 떨어진 것을 한 칸씩 떨어져 기록하기.xlsx

0.01MB

 

 

 

1. 문제 1

 

가. 데이터 및 원하는 표 양식

지점별, 연도별 판매량과 판매액이 있는데,

 

지점별 판매액만을 추출하고 싶을 때 몇 개가 안된다고 하면 복사해서 붙여넎기로도 가능하지만,

수식을 이용해 구하는 것이 맞는 방법입니다.

 

나. 해결 논리

중요한 것은 데이터는 2칸 씩 떨어져 있는데, 기록은 한 칸씩 떨어져서 해야 하므로

데이터의 열 변화를 2로 나눠서 기록할 열을 정하면 됩니다.

 

 

다. 수식

 

M3셀의 값은 C3셀이고,

N3셀의 값은 E3셀에 있습니다.

 

따라서, M3셀을 기준으로 한 칸 떨어진 셀의 값을 두 칸 떨어진 셀의 값으로 해야 하는데,

M3셀은 C3셀와 같은 값이므로 Offset함수를 이용하면 =OFFSET(C3,0,0)

 

 

그리고, N3셀의 수식은 2칸 떨어진 E3셀의 값이므로 =OFFSET(C3,0,2)가 됩니다.

 

그런데, 이것을 O셀에 붙여넣으려면 C3셀에서 C열이 변하면 안되고 행은 변해야 하므로 $C3라고 해야 합니다.

그런데, O3셀에 붙여넣어도 =OFFSET($C3,0,2)라고, Offset함수의 세 번째 인수인 cols가 4가 돼야 하는데 2로 고정입니다.

 

따라서, cols를 N열인 경우는 N열과 M열의 차이 1에 2를 곱한 값 2, O열의 경우는 O열과 M열의 차이 2에 2를 곱한 값 4로 해야하며, M열은 고정입니다.

 

또한 열에 해당하는 숫자를 반환해주는 함수는 Column으로, 인수는 셀 주소이며, 생략하면 현재 셀이 됩니다.

 

Offset함수와 Column함수를 결합해서 N3셀의 수식을 변경하면

=OFFSET($C3,0,(COLUMN()-COLUMN($M3))*2)가 됩니다.

 

Column()은 현재 셀의 열 번호이므로 N열은 14가 되고,

COLUMN($M3)는 M3셀의 열 번호이므로 M열 13이 됩니다.

 

여기에 2를 곱하므로 (COLUMN()-COLUMN($M3))*2는 2가 됩니다.

결국 위 수식은 =Offset($c3,0,2)와 같습니다.

 

N3셀의 수식을 P3까지 끌면 O3셀의 값은 46,250,000, P3셀의 값은 52,756,000으로서

 

아래 데이터의 G3과 i3셀의 값과 일치합니다.

 

이제 M3셀에서 P3셀까지 쉼표를 넣은 다음 P3셀의 채우기 핸들을 더블 클릭하면 완성입니다.

 

2. 문제 2

 

가. 데이터 및 원하는 표 양식

이번에는 반대로 데이터는 한 칸씩 이동하는데,

 

 

기록할 열은 2칸씩 이동하는 경우를 해보겠습니다.

 

나. 논리

논리는 위와 같은데, 기록할 열이 2씩 늘어나더라도 2로 나눠서 1,2,3씩 떨어진 값을 가져오도록 하면 됩니다.

다시 말해 위에서는 2를 곱했는데, 이번에는 2로 나누는 것입니다.

 

다. 수식

i16셀의 수식은 B16셀이므로 offset과 column함수를 이용하면

=OFFSET($B16,0,(COLUMN()-COLUMN($I16))/2)가 됩니다.

 

그리고 보너스를 구하는 수식은

4천만원이하는 없고, 4천만원이 넘으면 3%, 5천만원이 넘으면 5%로 해서 구하면

=IF(I16>50000000,I16*0.05,IF(I16>40000000,I16*0.03,0))가 됩니다.

 

조건이 크다면이므로 큰 값부터 작은 값순이 되어야 하며,

작다면이라면 작은 값부터 큰 값분으로 조건이 설정되어야 합니다.

 

이제 I16셀과 J16셀을 선택한 후 J16셀의 채우기 핸들을 P16셀까지 끌고,

 

 P16셀의 채우기 핸들을 P23셀까지 끌고,

홈 탭에서 ,(쉼표)를 눌러 천단위 구분 기호를 넎습니다. 그런데, 열너비가 좁아서 판매액 숫자가 #으로 표시되므로 i열부터 P열까지 선택한 후 열 경계선을 더블 클릭해서 열 너비를 자동 맞춤합니다.

열 너비가 좁아 숫자가 다 안보임

 

그러면 아래와 같이 완성됩니다.

 

원시 데이터가 아래와 같으므로 위 표의 판매액 데이터가 모두 맞고,

 

보너스도 4천만원을 초과한 40,410,000의 경우 *.03을 하면 1,212,300원 맞고,

5천만원을 초과한 53,350,000의 경우 *.05를 하면 2,667,500원 맞습니다.

 

두 칸씩 떨어진 것을 한 칸씩 떨어져 기록하기(완성).xlsx

0.01MB