[엑셀] 행 또는 열로 이루어진 데이터 표 형식으로 정렬하기

[표1] 원본데이터

행/열	A	B	C	D	E	F
1	한국	중국	일본	미국	타이완	넘버원

이런식의 데이터를 2*3 혹은 3*2의 표 형식으로 변환하고 싶다면 어떻게 해야 할까?

물론 일일히 수작업을 진행할 수도 있겠지만 데이터가 매우 많아진다면 무척 곤란해질 것이고, 우리의 퇴근시간 또한 늦어질 것이다.

필자가 생각하는 문제해결도를 이야기식으로 제시하니 차근차근 따라와보시길 바란다. 급하신 분들은 맨 아래로 내려보면 되지만 본인이 원하는 업무에 활용하기 위해서는 한 번 읽어보시는걸 권장드린다.

 

1. 뭘 해야 할까?

데이터를 살펴보니 1행 6열의 데이터. 내가 하고 싶은 것은 2*3(2행 3열)로 정렬.

그동안 엑셀 배웠던 것을 생각하면... 행열을 자유롭게 변환해주는 함수는 없었으니 기존에 알고 있던 함수를 활용해보자. 일정 조건(ex. D1 값 -> 2행 1열)에 따라 데이터를 추출해주는 함수는? Hlookup이 적합하겠네! 그런데 원본 데이터에는 Hlookup 함수를 활용하기 위한 1행 순번 데이터가 존재하지 않으니 행을 추가하여 데이터를 추가해줘야겠다.

[표2] 수정한 데이터

행/열	A	B	C	D	E	F
1	1	2	3	4	5	6
2	한국	중국	일본	미국	타이완	넘버원

 

2. Hlookup을 사용하기 위해 함수 인수값을 생각해보자.

=Hlookup(표의 첫행에서 찾으려는 값, 데이터 검색 표, 값 추출할 행, 논리값)

- 첫번째 인수값에는 뭘 넣어야 하는 거지...?? 결과를 2행 3열로 보여주는데 우리가 추가한 순번 데이터를 활용하려면 결과표에도 순번이 있어야하겠네.

[표3] 인수값 예상표

1	2	3
4	5	6

- 두번째 인수값에는 우리가 수정한 데이터인 [표2] 값이 들어가면 되겠네.

- 세번째 인수값에는 [표2]의 두번째 행을 뽑아내고 싶은 거니까 2를 입력해야겠다.

- 네번째 인수값에는 예외를 허용할 필요가 없으니 FALSE 값을 지정해야겠다.

 

3. 인수값 예상표 변형하기

생각해보니 엑셀에서 함수를 적용하려면 일직선 방향으로 밖에 안되잖아? (함수 작성하고 드래그해보시면 행방향 OR 열방향 밖에 지정이 안되고 대각선으로는 끌어지지 않는다.)

그러면 아까 [표2]에서 했던 것 처럼 인수값 예상표에도 순번을 붙여보자!!

[표4] 결과표 순번 

	1	2	3
1	(인수값 1)	(인수값 2)	(인수값 3)
2	(인수값 4)	(인수값 5)	(인수값 6)

 

4. 저 결과표에서 인수값을 어떻게 뽑아낼까?

규칙을 찾아내기 위해 일단 열은 3열로 고정시켜보자. 1행3열은 인수값으로 3, 2행3열은 인수값으로 6가 들어가야 한다. 뭔가 구구단이랑 비슷하게 생겼네?

그러면 이번엔 2열로 고정시켜보자. 1행2열은 인수값으로 2, 2행2열은 인수값으로 5가 들어간다. 방금 전의 구구단에서 1 뺀 거잖아?! 이를 일반화해서 수식으로 표현해볼까?

[공식1] 인수값 = (행 순번-1)*3 + 열 순번

 

5. Hlookup 함수 완성하기

생각해낸 [공식1]을 사용해서 함수를 완성해보자. 그런데 함수 적용 후 드래그하는 과정을 생각해보면 행 순번은 다른 열로 이동하면 안되고, 열 순번은 다른 행으로 이동하면 안되니까 절대참조를 활용해줘야겠네 !!

B5셀에 삽입된 함수 값 : HLOOKUP(($A5-1)*3+B$4,$A$1:$F$2,2,FALSE)

엑셀 실제 예시

 

6. (심화) 조금 더 깔끔하게 해볼까?

모종의 이유로 행열을 변환해야 할 일이 많아진 상황이다.

이제 함수는 구현했으니 좀 편하긴 한데 함수에 곱해져 있는 3 값을 계속 바꿔줘야해서 좀 귀찮은데? 이건 어떻게 못바꾸나? 마지막 열 순번을 참조해서 곱하게 하면 편하지 않을까?

수정된 B5셀에 삽입된 함수 값 : HLOOKUP(($A5-1)*$D$4+B$4,$A$1:$F$2,2,FALSE)    * 위와 결과는 같음

 

 

데이터의 모양에 따라 Vlookup을 사용해야 하는 상황도 있겠지만 원리는 모두 위와 같다. 데이터 양이 적은 경우에는 수기로 옮기는 것이 더 빠르겠지만.. 데이터가 100개 이상 넘어가는 경우에는 위 함수를 활용하셔서 퇴근시간을 앞당겨 행복한 저녁시간을 보내길 바란다.