같은 양식 여러 개 데이타 추출

편의 상 같은 시트에 예시했고 실전의 많은 데이타인 경우 별도 시트에서 작업하시는 것이 훨씬 편하겠지요. 별도 시트에서는 아래 17행 이후를 입력하고 B21의 INDEX 범위만 원본 시트의 해당영역을 드래그하여 지정해주면 됩니다.

 

	B	C	D	E	F	G
2	사원번호	사원이름	전화번호	주소
3	2	갑	345	홍은동
4	1	을	213	역삼동
5	3	병	254	사당동
6
7	사원번호	사원이름	전화번호	주소
8	4	정	823	신월동
9	7	무	762	상계동
10	6	기	353	면목동
11
12	사원번호	사원이름	전화번호	입사일자
13	9	경	234	우이동
14	8	신	328	대방동
15	10	임	763	개봉동
16
17		data 행		3
18		양식간격		1
19
20	사원번호	사원이름	전화번호	주소	21
21	2	갑	345	홍은동	0	0
22	1	을	213	역삼동	0	1
23	3	병	254	사당동	0	2
24	4	정	823	신월동	1	0
25	7	무	762	상계동	1	1
26	6	기	353	면목동	1	2
27	9	경	234	우이동	2	0

 

원본 데이타가 B2:E15에 입력되어 있을 때 데이타만 뽑으려 하면,

 

E17 =3       ; (양식 당 데이타 행 수)

E18 =1       ; (양식 간격)

F20 =ROW()+1  (복사 작업을 개시하는 행의 번호)

F21 =INT((ROW()-$F$20)/$E$17)      ; 원본의 몇째 블럭인지 계산

G21 =MOD(ROW()-$F$20,$E$17)      ; 블럭의 몇째 행에 해당하는지 계산

B21 =INDEX($B$2:$E$16,$F21*($E$17+$E$18+1)+$G21+2,COLUMN()-1)  원본에서 데이타 가져 옴.

 

B21 잡아 E21까지 복사, B21:G21 잡아 아래로 하염 없이 복사하면 원하는 표를 얻으실 수 있습니다. (CTRL+C, 아래로 화면 이동, 대충 행 번호 가늠하여 SHIFT+클릭(전체 범위 지정됨), SHIFT에서 손 떼고 CTRL+V 하는 것이 편함)

 

위에 나열된 수식은 설명을 위해서 적은 것이고, F21과 G21을 쓰지 않으려면

 

B21 =INDEX($B$2:$E$16,INT((ROW()-$F$20)/$E$17)*($E$17+$E$18+1)+MOD(ROW()-$F$20,$E$17)+2,COLUMN()-1)

 

를 입력하여 B21을 E21까지, B21:E21 잡아 아래로 복사하면 같은 효과를 얻습니다.

 

아래는 간략한 설명입니다.

 

위와 같이 같은 양식이 행으로 반복되는 경우 데이타만 뽑고 싶을 때라면 INDEX함수를 쓰는 것이 좋습니다. 전체 데이타 영역을 참조영역으로 잡아 x, y 축 좌표만 지정하면 알아서 찾아 줍니다. 위의 예에서 양식 당 데이타 수는 3개 (님의 경우는 20일 것입니다), 양식 간 행 띄움은 1행입니다. 이걸 지정해 줘야 몇 칸을 종횡으로 뛸 것인지 INDEX 함수에게 알려줄 수 있습니다.