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

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

포스트 목차.
01. 문제
02. 문제해결 방법
03. 예제 및 설명

01. 문제

문제는 특정 영역에 있는 데이터를 추출하여 가로로 정렬하는 방법입니다.

VBA를 사용하여 이러한 문제를 해결하는 방법은 예전에 다룬적이 있으니 이번에는 엑셀의 함수만을 이용하여 해결해봅시다.

02. 문제 해결 방법
(1) VBA를 사용하는 방법
VBA를 사용하여 해결하는 방법은 다음링크를 참조하면 좋을 것 같다.
https://eggdrop.tistory.com/15

Excel [지식인 해결 시리즈] 002. 공백 셀 제거 후 정렬

(2) 수작업
사실 위의 예제와 같이 데이터가 얼마 없을 때는 수작업으로 하는 것이 더 빠를수도 있다.

1. 우선 데이터가 있는 1행을 선택하여 Ctrl+c를 누릅니다.

2. 선택하여 붙여넣기
붙여 넣을 셀을 선택한 후 마우스 우클릭 또는 (ctrl+alt+v)를 이용하여 선택하여 붙여넣기를 실행합니다.
선택하여 붙여넣기 상자가 열리면 우측 하단의 행/열 바꿈을 체크합니다.

선택하여 붙여넣기의 행/열 바꿈 체크

 

선택하여 붙여넣은 결과

3. 반복작업 수행
위의 작업을 데이터가 있는 모든 셀에 반복하면 데이터만 추출하는 것이 가능합니다.

선택하여 붙여넣기에 대해 더 알고 싶으면 다음 링크를 참고합시다.
https://eggdrop.tistory.com/6

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

(3) 함수로 작업
질문자의 데이터는 많지 않기 때문에 수작업이 더 효율적일 수 있다.
데이터가 더 많아진다면 다음 함수들을 사용하여 데이터를 추출하는 방법도 고려해 보자

사용자가 추출하고자 하는 데이터들(셀에 색칠한 영역)

여기서는 세로로 길게 늘어진 주소 데이터를 이용하여, 우리가 원하는 데이터만 표기하여 위의 데이터 영역에 있는 값들을 가져오려고 한다.

다음과 같은 작업을 거쳐 문제를 해결할 것이다.
1) 행과 열의 정보를 이용하여 주소를 만든다.
2) 배제해야되는 주소를 판단한다.
3) 배제해야되는 주소에 있는 데이터 외의 데이터를 가져온다.

1. ADDRESS 함수
질문자는 셀D2에 있는 데이터를 추출하고 싶어한다.
주소 D2는 D열(4열), 2행을 뜻한다. 즉 2행,4열을 알면 D2라는 주소를 만들수 있다.

위의 예처럼 2와 4를 입력하여 2행 4열의 주소 텍스트를 반환하는 역할을 하는 것이 ADDRESS 함수이다.

함수 실행 결과

행번호(row_num), 열번호(column_num)는 필수 파라미터이고 다음과 같은 생략가능 파라미터도 지원한다.
[abs_num]: 절대주소/상대주소

[a1]: 주소의 형태

0을 선택 시에 R행번호C열번호 스타일로 표기, 1을 선택 시에 알파벳 열번호 + 숫자 행번호 스타일로 표기
[sheet_text]: 시트명 지정

2. INDIRECT 함수
INDIRECT 함수는 텍스트로 주소(ref_text)를 입력받으면, 해당 주소에 있는 내용을 셀에 표기해주는 기능을 수행한다.

위의 그림은 INDIRECT함수의 파라미타러 D14셀을 입력한 예를 표기한다. D14셀에는 D3이라는 주소값이 들어있다.
따라서 INDIRECT 함수는 D3셀에 있는 Q라는 문자를 출력할 것이다.

INDIRECT 함수도 생략가능한 파라미터를 갖는다.

[a1]: 주소의 형태를 뜻한다.
생략하거나 TRUE를 입력하면 A1스타일, FALSE를 입력하면 R1C1스타일로 ref_text를 해독하게 된다.

3. MOD 함수
나눗셈의 나머지를 구하는 함수이다.
첫번째 파라미터를 두번째 파라미터로 나눴을 때 발생하는 나머지를 반환한다.

위의 예제는 3을 12로 나눈 나머지이므로, 3이 될 것이다.

4. ROUNDDOWN 함수
ROUNDDOWN 함수는 내림 함수이다.
엑셀에서 나누기를 수행하면 몫 + 나머지의 형태로 계산이 되는것이 아니라 다음처럼 소수로 계산이된다.

여기에서 내림 함수를 사용하면 몫을 구할 수 있다.

두번째 파라미터는 내림을 실행할 소수 자리수를 의미한다. 0을 입력하면 1의 자리에서 내림하는 효과가 있다.

문제 해결을 위해 필요한 요소들을 다뤘으니 실제로 문제를 해결해보자.

03. 예제 및 설명

사용자가 추출하고자 하는 데이터들(셀에 색칠한 영역)

A열은 명확하게 1구/2구/3구를 나타내므로 추출할 데이터에서 제외시키자.

B1부터 M12까지의 셀을 주소로 표현해보자.
B1부터 M12까지는 12 x 12 개의 셀로 구성되어 있다. 총 144개의 셀이다.

이 144개의 셀에 대해서 다음과같이 숫자로 행번호와 열번호를 지정하는 것이 가능하다.

처음 12개의 데이터는 1행의 데이터이고, B(2)에서 M(13)까지 반복이 되어야한다.

연번은 1에서부터 144까지 있으며, 우리는 연번만 이용하여 행번호와 열번호를 추출해야한다.

1) 행번호 추출
행번호는 1이 12개 나온 후에 2가 12개 나오는 식으로 12가 될때까지 반복된다.
이것은 왼쪽에 있는 연번을 12로 나눈 몫을 활용하여 계산하는 것이 가능하다.

12를 12로 나눈값은 0이지만 우리는 12번째 값에 1이 나오길 원하므로, 연번에 미리 1을 빼주고 12로 나눈 몫을 구한 후 다시 1을 더해주는 방식으로 행번호를 구할 수 있다.

2) 열번호 추출
열번호는 2에서 13이 반복된다. 2에서 13을 144까지 복사해 나가도 되지만 우리는 수식으로 열번호를 추출해보자.

열번호는 연번을 12로 나눈 나머지를 사용하면 구할 수 있다. 12를 12로 나눈나머지는 0이지만 우리는 이 위치의 값이 12가 되길 원하기 때문에, 연번에 1을 뺀수를 12로 나눈후에 1을 더하는 식으로 열번호를 구할수 있다.
우리는 B열(2열)부터 조회해야되기 때문에 위에서 구한 열번호에 1을 더 더해서 열번호를 최종적으로 확정할 수 있다. 이렇게되면 연번이 증가함에 따라 2에서 13까지의 숫자가 계속 반복되게 된다.

3) 레인정보가 담긴 1행, 5행 9행 배제시키기

OR를 이용하여 행번호가 1,5,9일 경우 FALSE를 반환하는 셀을 하나 만든다.

4) 레인 1L, 2L 배제시키기
위에서 레인 1L, 2L의 경우 데이터가 없기 때문에 이 내용 또한 배제시켜야된다.

AND와 OR를 사용하여 (2행, 3행, 4행), (2열,3열)의 데이터의 경우 FALSE를 반환하는 셀을 만든다.

5) 배제할 셀 조건 합치기
3) 4)에서 구한 논리값을 AND 연산하여 TRUE인 경우만 데이터를 추출하도록 한다.

6) 행번호 열번호를 이용하여 주소 만들기

7) 추출해야되는 셀에 있는 데이터 가져오기

데이터 추출여부 셀 (T열) 데이터가 TRUE인 경우 U열에 있는 주소값에 해당하는 데이터를 INDIRECT로 가져온다

8) 위의 작업을 144개 셀에 대해 적용한 결과는 다음과 같다.

추출데이터 V열에는 우리가 추출하고자 하는데이터만 추출이된다.

이제 이 데이터를 선택하여 복사하기를 이용하여 값만 복사하여 옆셀에 복사하자

이후에 내림차순으로 한번 정렬하여 공백데이터를 밑으로 보낸 후에 , 데이터 가 있는 영역에서 오름차순으로 정렬을 하면 사용자가 원하는 데이터를 추출 할 수 있다.