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

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

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

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

 

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

 

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

 

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

 

 

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

 

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

---

직접 변경해보자

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

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

 

이전 시간까지는 개발자들의 요청에 대해 이해하는 이론적 접근을 했다면, 이제부터는 다분히 실무적인 접근을 할 시간이다. 이제 개발자들의 불만을 해결해 주도록 하자. 앞선 시간에서 개발자들은 아래 '예시 1'의 데이터를 '예시 2'와 같이 변경해 달라고 했다. 

예시 1

예시 2

 

우리는 여전히 '예시 1'의 데이터를 필요로 한다. 그렇다고 매 번 '예시 1'과 '예시 2'의 데이터를 각각 작성할 수는 없는 노릇이다. 한 번쯤은 변경해 줄 수도 있지만, 이는 완벽한 해결책은 아니다. 데이터는 언제든 추가될 수 있고, 수정될 수도 있고, 구조적인 변경이 생길 수도 있다. 그런 변화를 매 번 적용하고 체크하며 데이터를 변경해 주기란 쉬운 노릇이 아니다. 이를 우리가 직접 해결할 순 없을까? 우리가 추가하고 수정하거나, 구조를 변경해도 문제 없이 작동하는 매커니즘을 만들어 보자. 우리는 먼저 정확히 문제가 무엇인지 파악하는 단계가 필요하다. 위의 '예시 1'과 '예시 2'의 데이터를 보며 우선 어떻게 해결할 수 있을 지 단계적으로 생각해볼 것이다.

 

문제 1. 숫자 반복 기입하기

몫과 나머지

가장 먼저 우리는 1열의 id 값을 속성 개수에 맞게 반복 기입해야 한다. 1, 1, 1, 2, 2, 2, 3, 3, 3을 직접 입력할 수 있겠지만, 데이터가 더 많아진다면 쉽지 않을 듯 하다. 우리는 간단한 수학을 활용해 문제를 해결할 수 있다.

나눗셈의 몫을 활용하는 방법을 사용해 보자. 예를 들어 (1, 2, 3, 4, 5, 6, 7, 8, 9, 10) 을 각각 3으로 나눠 보면 (⅓, ⅔, 1, 1⅓ , 1⅔, 2, 2⅓ , 2⅔, 3, 3⅓)을 얻을 수 있다. 여기서 몫만을 취하면 (0, 0, 1, 1, 1, 2, 2, 2, 3, 3) 이 된다. 이와 같은 방식을 사용하면 자신이 원하는 패턴을 얼마든지 만들 수 있을 것이다.

 

주어진 예시 변환의 경우에는 앞에 0을 추가해 (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) 를 나눈 몫으로 (0, 0, 0, 1, 1, 1, 2, 2, 2, 3) 을 얻을 수 있다. 여기에 모든 요소에 1을 더해주면 (1, 1, 1, 2, 2, 2, 3, 3, 3, 4) 가 되므로 우리가 원했던 패턴을 얻게 된다. 그렇다면 이를 엑셀에서 어떻게 만들어 낼 수 있을까?

 

QUOTIENT 함수 사용하기

=QUOTIENT(a,b) 는  a를 b로 나눈 몫을 출력해 준다. 예시는 아래와 같은 방법으로 해결할 수 있게 된다.

 

0, 1, 2, 3... 으로 1씩 증가하는 등차수열을 자동 채우기 핸들을 사용해 만들고, 마찬가지로 QUOTIENT 함수를 자동 채우기 핸들을 사용해 만들어주면 문제를 해결할 수 있다.

 

ROW 함수 사용하기

1씩 증가하는 등차수열을 만들기 번거롭거나 오래걸릴 경우(데이터가 너무 많다면 오래걸릴 수 있다) 우리는 =ROW(a) 를 함께 사용할 수 있다. 이 함수는 a에 해당하는 행 번호를 출력해 준다. EXCEL 행 번호는 1부터 시작하니 0이 필요할 경우 =ROW(a)-1 을 사용할 수 있다.

 

 

MOD 함수 사용하기

그렇다면 (1, 2, 3, 1, 2, 3, 1, 2, 3) 과 같은 패턴은 어떻게 만들 수 있을까? 이 경우에는 나머지를 활용할 수 있다. (0, 1, 2, 3, 4, 5, 6, 7, 8, 9) 를 각각 3으로 나눈 나머지는 (0, 1, 2, 0, 1, 2, 0, 1, 2, 0) 으로, 위에서와 마찬가지로 각 요소에 1을 더해주면 우리가 원하는 (1, 2, 3, 1, 2, 3, 1, 2, 3, 1) 을 만들 수 있게 된다. 이는 엑셀에서 =MOD(a,b) 를 사용하면 된다. QUOTIENT에서와 마찬가지로 a를 b로 나눈 나머지를 출력해 준다.

 

 

문제 2. 텍스트 반복 기입하기

VLOOKUP 함수와 HLOOKUP 함수 사용하기

숫자는 알겠는데, 텍스트는 어떻게 하면 좋을까? '예시 2'의 '명칭' 열에는 id=1에는 '강아지'가, 2에는 '오리'가, 3에는 '도마뱀'이 각각 입력되어야 한다. 이런 케이스의 경우 =VLOOKUP(a,b,c,[t]) 를 사용해 해결할 수 있다.

VLOOKUP 함수는 a 값을 b 범위의 첫 열에서 열 방향으로(세로로) 검색하여, 같은 행의 c 열에 해당하는 값을 출력해주는 함수이다. [t]를 FALSE 또는 0 으로 지정하면 a 와 정확하게 일치하는 값을, TRUE 또는 1 로 지정하면 비슷하게 일치하는 값을 출력하는데, 특별한 케이스가 아닌 이상 주로 FALSE 를 사용한다. 위의 예시에서는 아래와 같이 사용할 수 있다.

 

 

여기서 주의해야 할 점은, 자동 채우기 핸들을 사용할 경우 범위가 어긋나지 않도록 하기 위해 범위에 해당하는 b 를 $를 사용해 고정해 주어야 한다는 점이다.

 

'예시 2'의 세 번째 열은 좀 까다로워 보인다. 그러나 지금까지 나온 것들을 잘 조합해 활용하면 해결할 수 있다. 아래 방법에서 사용한 핵심 아이디어는 위에서 =VLOOKUP(a,b,c,[t]) 으로 찾았던 반복 텍스트를 그대로 활용하고, c 값에도 변동을 주기 위해 =MOD(a,b)를 사용해 반복 숫자 패턴을 만들었다는 점이다.

 

이 방법들을 사용해 우리는 '예시 1'의 데이터를 '예시 2'로 성공적으로 변환할 수 있게 되었다.

 

문제 해결?

이제 우리가 대면한 대부분의 문제는 해결했다. 이 정도까지만 완료되어도 당장은 괜찮지만, 더 큰 데이터 세트와 수시로 변경되는 동적 데이터를 가지고 있을 경우는 아직 해결하지 못했다. 예를 들어 바로 위 사진에서 참조할 범위 중 한 열인 F열이 필요없어져 삭제된다고 해 보자.

 

참조 범위 중 한 열이 사라져, 해당 하는 행을 하나하나 삭제하거나, 참조할 숫자 패턴을 바꿔야만 한다. 어느 방법을 사용하든 다시 작업해야 할 것이다. 이런 번거로운 상황을 피하기 데이터 삭제는 금지하기로 했다고 해도, 데이터 추가는 문제가 없을까? 물론 아니다.

 

'속성0' 이 추가되어 우측 데이터 전체가 바뀌었다. 열이 늘어나 전부 새로 작업해야 한다. 심지어 각 행 데이터에 고유 id라도 부여되어 있다면 큰일이 생길 수 있는 상황이다. 우리는 이런 문제를 방지하기 위해 여러 가지 방법이 필요하다.

 

 

다음 시간에 계속

계획 상 다음 시간이 이번 주제의 마지막 시간이다. 이 같은 문제를 해결하기 위해서는 또다른 테크닉들이 필요해 이번 한 세션에서 다루기는 분량이 다소 과하고 피로를 유발한다고 판단되어 세션을 나눴다. 다음 시간에 동적 데이터까지 해결할 수 있는 여러 가지 테크닉을 이번과 같은 형태로 배워나가 볼 것이다. 첨언하자면 일단 무작정 따라하는 것도 좋지만, 그 후에 함수의 본질적 기능과 당면한 문제의 핵심을 파악한다면 여러 가지 방식으로 응용할 수 있고, 이 세션에서 소개되는 것보다 더 좋은 방법으로 문제를 해결할 수도 있을 것이다.

---

 

<별 헤는 밤> 구경하기 👀

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