[개발자와 화해하기] 데이터를 정리하는 법 (4) : 복잡한 가로/세로 데이터 변환

기획자와 개발자들은 협업하며 종종 부딪힌다.

정리되지 않은 수많은 아이디어들을 한아름 들고 오는 기획자.

이것도 안되고, 저것도 안된다고 하는 개발자. 

 

둘은 오늘도 서로에게 고개를 저으며 한숨을 내쉰다. 

 

이런 세상의 수많은 기획자와 개발자들 사이 갈등 속 각자 나름의 사정은 천차만별이지만, 근본적인 원인은 크게 다르지 않다.

 

바로 ‘서로가 서로의 일을 모른다’는 것

 

 

우리들은 서로의 일을 조금이나마 이해해 보려 노력할 필요가 있다.
서로를 이해하고 상대방이 바라보는 곳을 함께 바라볼 수 있어야 발전적인 대화가 가능하다.  

 

별 헤는 밤을 기획하면서 나 역시도 개발자들과 수없이 부딪히곤 했고, 다양한 방식으로 문제를 해결해 나가려 노력했다.
이 글에서는 별 헤는 밤 프로젝트를 진행하며 생겼던 사례들을 살펴보며, 초보 기획자의 입장에서 우리의 개발자들을 이해하는 시간을 가져보고자 한다. 

---

더 어려운 문제들

[개발자와 화해하기] 데이터를 정리하는 법 (1) : index 이해하기

[개발자와 화해하기] 데이터를 정리하는 법 (2) : B-tree 이해하기

[개발자와 화해하기] 데이터를 정리하는 법 (3) : 간단한 가로/세로 데이터 변환

 

이전 시간에는 바뀐 구조에 따라 숫자나 텍스트를 반복 기입하는 등 데이터의 수직/수평 구조를 변경하며 마주하거나 생각해 볼 수 있는 기초적인 문제들을 다뤘다. 그러나 그 방식들은 데이터가 지속적으로 업데이트되는(기존 데이터의 삭제나 수정, 혹은 새로운 데이터의 추가가 빈번하게 일어나는) 상황에서 유연하게 작동하기 어렵다는 단점이 있었다.

 

예시 1

예시 2

 

기존 예시 1에서 예시 2의 좌측 범위처럼 한 속성이 사라지면, 기존의 방법을 사용할 경우 해당 하는 행을 하나하나 삭제하거나, 참조할 숫자 패턴을 바꿔야만 한다. 어느 방법을 사용하든 재작업이 필요한 상황이다.

 

예시 3

 

예시 3 처럼 '속성0' 이 추가되면 우측 데이터 전체가 뒤섞인다. 심지어 각 행 데이터에 고유 id라도 부여되어 있다면 큰일이 생길 수 있는 상황이다. 우리는 이런 문제를 방지하기 위해 여러 가지 방법이 필요하다.

 

문제 1. 수정되는 데이터 반영하기

INDEX 함수 사용하기

=INDEX(a,b,[c],[d]) 는 a 범위의 b 번째 데이터를 출력해준다. a가 행(row) 이나 열(column)이 아니라 배열(array)일 경우, [c] 행의 [d] 열 값을 출력해준다. =INDEX(a,b,[c],[d]) 를 사용하면 해당 셀의 데이터가 수정되더라도 오류 없이 바로 반영된다. 

 

id=2의 명칭이 당나귀로 바뀌어도 적용되는 모습

 

여기까지는 VLOOKUP 함수도 동일하게 기능한다. 그러나 VLOOKUP은 첫 열을 기준으로 검색하기 때문에 배열의 우측에 있는 데이터만 가져올 수 있다. 반면 INDEX는 위치에 상관없이 좌측이든 우측이든 해당하는 데이터를 모두 불러올 수 있어 보다 유연하다. 지정한 범위의 첫 열 좌측에 원하는 데이터가 생길 가능성이 있을 경우 INDEX를 사용하는 것이 더 바람직하다.

 

문제 2. 추가/삭제되는 데이터 반영하기

 OFFSET 함수 사용하기

여기부터가 까다로울 수 있는 부분이다. 그리고 동시에 이제까지 마주한 많은 문제들을 해결해줄 수 있기도 하다. 지속적으로 추가되고 삭제되는 데이터를 반영하기 위해서는 '동적 범위'의 지정이 필요하다. 동적 범위는 OFFSET 함수를 주로 사용해 지정한다.

 

=OFFSET(a,b,c,[d],[e]) 는 a 셀을 기준으로 아래로 b 번째, 우측으로 c 번째 데이터를 출력해준다. b와 c가 비어있을 경우 , 아래로 [d] 행, 우측으로 [e] 열의 범위를 출력해준다. =OFFSET(a,b,c,[d],[e]) 를 사용하면 범위가 늘어나거나 줄어들더라도 즉시 반영하도록 만들 수 있다. 여기에 추가로 MATCH 함수와 IFERROR 함수, "*" 와 9E+307을 함께 사용하면 빈칸이 생기더라도 작동한다.e

id=5 행이 추가되어도 즉시 반영되는 모습

 

=OFFSET(B2,,,MAX(IFERROR(MATCH("*",B:B,-1),0),IFERROR(MATCH(9E+307,B:B,1),0))-1,1)

=OFFSET(B2
                       ,,,MAX(
                                   IFERROR(
                                                     MATCH("*",B:B,-1),0),
                                   IFERROR(
                                                    MATCH(9E+307,B:B,1),0)
                         )-1,1
)

 

 

INDEX 함수와 함께 OFFSET 함수를 활용한다면, 비로소 우리가 원하던 데이터셋을 얻을 수 있게 된다.

속성 4 가 추가됨에 따라 데이터셋이 업데이트되었다

 

 

예시와 관련된 방법은 본 글에서 나열하기에는 너무 길어 파일을 첨부하니 참고하기를 바란다.

데이터를_정리하는_법_(4)_240214.xlsx

0.01MB

 

 

내려받기 껄끄러운 이들을 위해 예시를 첨부한다.

D3 셀 예시
=INDEX(
OFFSET(Sheet1!$A$1,,,MAX(IFERROR(MATCH("*",Sheet1!$A:$A,-1),0),IFERROR(MATCH(9E+307,Sheet1!$A:$A,1),0)),MAX(IFERROR(MATCH("*",Sheet1!$1:$1,-1),0),IFERROR(MATCH(9E+307,Sheet1!$1:$1,1),0))),

QUOTIENT(ROW(Sheet1!$A1)-1,MAX(IFERROR(MATCH("*",OFFSET(Sheet1!$A$1,,,1,MAX(IFERROR(MATCH("*",Sheet1!$1:$1,-1),0),IFERROR(MATCH(9E+307,Sheet1!$1:$1,1),0))-1),-1),0),IFERROR(MATCH(9E+307,OFFSET(Sheet1!$A$1,,,1,MAX(IFERROR(MATCH("*",Sheet1!$1:$1,-1),0),IFERROR(MATCH(9E+307,Sheet1!$1:$1,1),0))-1),1),0))-1)+2,

MOD(ROW(Sheet1!$A1)-1,MAX(IFERROR(MATCH("*",OFFSET(Sheet1!$A$1,,,1,MAX(IFERROR(MATCH("*",Sheet1!$1:$1,-1),0),IFERROR(MATCH(9E+307,Sheet1!$1:$1,1),0))-1),-1),0),IFERROR(MATCH(9E+307,OFFSET(Sheet1!$A$1,,,1,MAX(IFERROR(MATCH("*",Sheet1!$1:$1,-1),0),IFERROR(MATCH(9E+307,Sheet1!$1:$1,1),0))-1),1),0))-1)+3
)

 

 

마치며

드디어 길고 길었던 개발자와 화해하기의 첫 장이 끝났다. 이론적인 부분과 실무적인 부분 두 파트를 함께 다루며 이야기가 여러 방향으로 흘렀지만, 본 장에서 다룬 이야기들로 하여금 전달하고 싶었던 핵심적인 이야기는 상대방의 일을 이해하고 좀 더 효율적이고 합리적인 업무 방식을 고민하자는 것이다. 우리는 여전히 상대방의 요구가 이해하기 어렵고, 실행하기엔 더 어렵기만 하다. 하지만 조금이라도 더 상대방의 입장에서 생각하고, 일해본다면 우리는 점차 더 나은 방식을 찾아나갈 수 있을 것이다.

 

마지막으로 부디 초보 기획자들과 나아가 개발자들에게도, 이에 더해 엑셀에 어려움을 겪는 이들, 데이터를 효율적으로 관리할 방법이 필요한 이들에게도 본 글이 조금이나마 도움이 되길 바란다.

 

---

 

<별 헤는 밤> 구경하기 👀

별 헤는 밤: 밤하늘, 별자리, 여행정보와 날씨예보까지 - Google Play 앱