Excel 지식인 010. 엑셀에서 숫자를 특정 패턴으로 입력하기, 숫자 건너뛰어 입력하기

전체 목차
001. lookup 함수를 이용한 양도세 구간 구하기
002. 공백 셀 제거 후 정렬
003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)
004. 목록상자, 토글버튼 연동
005. 다른 시트에 있는 특정 데이터를 복사해오기
006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)
007. 상반기/하반기 매출 집계(SUMIF)
008. 콤보박스 활용하여 필터 변경하기

009. 특정 위치에 있는 데이터만 추출하기 (address, indirect)

 

포스트 목차.

• 01. 문제
• 02. 문제해결 방법
• 03. 예제 및 설명

 

01. 문제

 

문제를 정리하면, 연속된 숫자가 행방향으로 배치되는데, 30 단위마다 빈칸을 3개씩 입력하고 싶다는 것이다.

전체 데이터는 약 1000개라고 한다. 

 

이런 문제는 엑셀에서 제공하는 특정기능을 이용하여 해결하기는 힘들 것으로 보인다.

 

02. 문제해결 방법

(1) 패턴 분석

데이터는 1씩 증가하다가, 30을 만난 후에는 공백 값 세 개를 출력한 후 다시 60까지 증가한다.  

1씩 증가하는 셀 30개와 공백값이 있는 셀 3개, 즉 33개를 한 단위로 반복된다고 볼 수 있다.

 

(2) 반복 패턴 생성

고정된 주기로 반복되는 패턴은 MOD 함수를 이용하여 만들 수 있다.

 

MOD 함수로 위와 유사한 문제를 해결한 적이 있으니, 관심이 있다면 다음 링크를 참고하기 바란다.

https://eggdrop.tistory.com/40

Excel [지식인 해결 시리즈] 009. 특정 위치에 있는 데이터만 추출하기 (address, indirect)

 

(3) 공백값 처리

공백값은 MOD값이 특정 조건일 때 발생한다. 이경우는 마지막 세칸에 대해 공백값을 입력하면 된다.

MOD값을 정해두고 해당 MOD값일 때는 공백을 출력하면된다.

 

(4) 숫자 증가시키기

다행히 이 예제에서는 숫자가 1씩 증가하므로, 공백이 아닌 셀에 1을 입력해두고, 그 값을 더해나가면 된다.

 

03. 예제 및 설명

위에서 제시한 문제 해결 방법을 적용하여  문제를 풀어보자.

 

(1) 연속된 데이터 채우기

일단 1000개의 연속된 데이터를 하나 만든다.

채우기 핸들로 드래그를해서 1000까지 작성해준다.

 

(2) 33개의 패턴 만들기

A열에는 1부터 1씩 증가하는 숫자 패턴이 있다.

이것을 이용하여 33개마다 반복되는 패턴을 만들어보자.

=MOD(A1,33) 을 입력하면 A1 값을 33으로 나눈 나머지가 B1에 작성된다.

 그 후에 채우기 핸들을 더블클릭하여 B1000까지 수식을 채워보자

 

채우기 핸들의 활용에 대한 더 자세한 글은 다음 링크를 참고하기 바란다.

https://eggdrop.tistory.com/44

엑셀 이동 단축키, 채우기 핸들, 수식 채우기 팁

 

생성된 패턴은 다음과 같다.

1, 2, 3, 4, .... 31, 32, 0 이 반복되게 된다.

 

이 패턴을 그대로 사용해도 되지만 약간의 트릭을 써서 1, 2, 3, 4, ..., 32, 33 으로 끝나는 패턴을 만들어보자.

 

33으로 나누기전에 나눌 숫자에 1을 빼고, 연산된 나머지에 1을 더하면 위의 패턴을 만들 수 있다.

(나누기전에 1빼고, 나머지에 1더하기로 기억하자)

=MOD(A1-1,33)+1

변경된 수식입력 후 채우기 핸들로 B1000까지 복사해주자.  그럼 다음 결과를 얻을 수 있다.

 

1부터 33까지의 숫자 패턴이 반복된다.

 

(4) 공백 및 더할 단위 값(1) 채우기

 

이제 공백을 채워야하는데 공백이 있을 조건은 패턴값(MOD값)이 31, 32, 33일 때다.

즉 패턴값이 30보다 작거나 같을 때에는 1을 입력하고, 30보다 크면 0을 입력하면 된다.

 

IF함수를 이용하여 다음과 같이 작성해보자.

=IF(B1<=30,1,0)

채우기 핸들을 이용하여 C1000까지 값을 채운다.

위의 그림처럼 33개의 패턴중 마지막 3개만 0으로 채워지는 것을 확인할 수 있다.

 

(5) 연속된 값 만들기

연속된 값은 SUM함수를 이용하여 만들려고 한다.

다음과 같이 셀 D1에 작성한다.

 = IF(C1=0, "", SUM(C$1:C1))

수식을 해석하면 C1의 값이 0인 경우에는 공백을 입력하고, 0이 아닌경우에는 C1에서 C1까지 더한다는 뜻이다.

여기서 더할 범위를 C$1:C1으로 지정한 이유는 채우기 핸들을 이용하여 수식을 복사할 때, 다음 셀인 D2셀의 수식이 다음과 같이 적용되기를 원하기 때문이다.

 = IF(C2=0, "", SUM(C$1:C2))

절대 주소로 지정한 C$1에서의 1의 값은 채우기 핸들이나 복사를 이용하여 다른 셀에 복사해도, 바뀌지 않는다.

절대 주소 상대 주소에 대해 더 자세한 내용을 알고 싶으면 다음 링크를 참고하자.

https://eggdrop.tistory.com/50

엑셀 절대주소 vs 상대주소 (절대주소 활용법)

 

채우기 핸들을 이용하여 D1000까지 셀 값을 복사하면, 다음과같은 결과를 얻을 수 있다. 

 

이제 이 패턴을 복사 후에 선택하여 붙여넣기(값) 하면 질문자가 원하는 작업을 할 수 있다.

 

선택하여 붙여넣기에 대해 더 자세히 알고 싶으면 다음링크를 참고하기 바란다.

https://eggdrop.tistory.com/6

엑셀 매크로 (VBA) - 002. 선택하여 붙여 넣기 기능 정리