엑셀 수식 복잡도 통제

Open

어느 날 타임라인을 보다가 무서운 글을 봤습니다. 정확히는 글이 무섭다기보다는 그림이 무서웠는데 웃기긴 하지만 또 한편으로는 마냥 웃을 수는 없었는데 이 그림은 단순한 형식에 근거해 약간 과장된 모양이고 실제 일하는 데는 이보다 복잡도는 더 높고 길이는 비슷하거나 약간 짧은 수식이 종종 사용되기 때문입니다.

프로그래머가 아니어서 프로그래머들이 코드를 작성할 때와 비슷한 느낌일지 잘 모르겠지만 엑셀 수식은 분명 만들 때는 수식의 구성을 잘 이해하고 제한을 잘 파악하고 수식의 동작을 잘 설명할 수 있지만 시간이 조금만 지나면 이 수식은 그저 의미 없는 셀과 사칙연산자와 함수와 괄호의 의미 없는 나열에 불과해집니다. 수식은 분명 강력한 도구이기는 하지만 복잡도 관리에 실패하면 유지보수가 불가능해지고 심지어는 결과의 의미를 파악하지 못하거나 결과가 올바른지 확인할 수 없게 될 수도 있기 때문에 복잡도를 잘 관리해야 합니다. 오늘은 이 이야기를 해보겠습니다.

수식 복잡도를 통제하는 가장 기초적인 방법은 여러 셀을 참조하는 수식을 만들지 않는 것입니다. 황당하게 들릴 수 있음을 이해하지만 복잡한 수식을 만들지 않는 가장 좋은 방법은 그런 수식을 아예 만들지 않는 겁니다. 계산을 하다 보면 여러 셀을 참조하는 수식을 쉽게 만들게 되고 또 복사하고 붙여 넣기 편하게 가능한 여러 계산을 한 수식으로 처리하려고 노력하게 되기 쉽습니다. 그런데 여러 셀을 참조한 수식은 일단 길이가 길어질 뿐 아니라 수식의 주요 부분이 무슨 역할을 했는지 쉽게 잊어버릴 수 있습니다.

특히 엑셀 수식은 수식에 주석을 달기 아주 어렵기 때문에 (불가능하진 않음) 온전히 수식만으로 동작을 이해해야 하는데 참조하는 셀이 대략 네 개 정도를 넘어가면 수식의 동작을 이해하는데 시간이 걸립니다. 또한 한 가지 수식이 참조하는 셀이 늘어나면 참조 당하는 각 셀에 있는 값의 무결성이 깨지는 상황을 눈치채기도 어려워집니다. 만약 수식의 최종 계산 결과가 에러를 내는 상황을 피하려고 셀의 값이 유효한지 검사하기 위해 ISERROR나 ISNA 같은 기능을 써서 최종 결과에 에러가 반환되지 않도록 수식을 작성하기 시작하면 분명 참조할 값의 무결성이 깨졌지만 이를 눈치채지 못할 수 있어 위험합니다. 그래서 수식을 최대한 짧게 사용하고 만약 수식이 길어지면 이는 수식 하나로 처리할 상황이 아님을 눈치 채야 합니다. 수식이 길어지면 이를 여러 셀에 걸쳐 계산하도록 분산할 것을 추천하는데 이어서 설명하겠습니다.

수집형 게임에 새 캐릭터를 추가하려고 하는데 이 캐릭터의 물리공격력을 이 캐릭터와 레벨이 비슷한 몬스터 중 아래로 다섯 레벨 몬스터와 위로 다섯 레벨 몬스터의 방어력에 기초해 계산하려고 합니다. 이를 수식으로 계산하려면 레벨 범위에 있는 몬스터들의 방어력 여러 개를 배열 모양으로 만들어 가져온 다음 이들 각각에 여러 계산을 하기 시작할 겁니다. 계산을 하기 전에 이미 배열 모양으로 값을 가져오기만 해도 이미 수식은 충분히 길어지는데 여기에 다른 보조 값을 가져와 수식으로 연결하기 시작하면 순식간에 수식이 통제하기 어려울 만큼 길어집니다. 조금만 이 상황이 지속되어도 괄호 짝을 맞추기 어려운 상황이 됩니다.

이런 상황을 완화하는 요령은 먼저 값을 가져오는 부분과 이들을 계산하는 부분을 같은 수식으로 묶지 않는 겁니다. 다른 워크시트나 파일로부터 값을 가져올 때 값을 수식 내부로 직접 가져오지 말고 값을 가져오기만 하는 역할을 하는 워크시트를 만들어 값을 가져오는 수식을 계산 수식과 섞이지 않도록 합니다. 그러면 세 가지 장점이 생기는데 먼저 값을 가져올 때 값의 위치가 바뀌면 가져온 값이 깨지므로 문제가 생겼음을 쉽게 눈치 챌 수 있고 다음으로 이 값을 어디서 가져왔는지 워크시트에 기록해 둘 수 있습니다. 하다못해 칼럼 맨 위에 ‘몬스터 데이터에서 가져온 레벨 24 몬스터 방어력’ 이라고 적어 주석 기능을 하게 할 수 있으며 마지막으로 값을 가져오는 부분이 계산과 섞이지 않으므로 값을 교체하거나 수식을 변경할 때 서로 영향을 덜 받아 하다못해 괄호 짝을 맞추기 더 쉽습니다.

다음으로 계산을 여러 단계에 걸쳐 수행한다면 각 과정을 같은 수식으로 묶으면 안됩니다. 계산은 한 번에 한 단계 씩만 수행해야 합니다. 세 값의 평균을 구한다 치면 세 값을 더하는 부분과 이들을 나눠 평균을 내는 부분을 같은 수식으로 묶으면 안됩니다. 정확히는 안 될 것 까지는 아니지만 묶지 않는 쪽을 추천합니다. 세 값을 더한 결과를 표시하는 셀을 만들고 이 값을 나눠 구한 평균을 다른 셀에 나타나도록 해야 합니다. 그러면 다시 위에서 설명한 장점을 얻을 수 있습니다. 세 값을 더한 시점의 칼럼에 설명을 기입할 수 있고 또 그 값을 나눈 결과에도 설명을 기입할 수 있고 중간 계산 과정을 나열한 워크시트를 보고 계산 과정을 따라가면 이들이 수식 하나에 합쳐진 것을 보고 따라갈 때보다 더 쉽게 계산과정을 파악할 수 있습니다.

가져올 값의 방향이 바뀐다면 이를 수식으로 처리하기 전에 테이블 설계 자체가 목표에 부합하는지 확인해야 합니다. 물론 엑셀은 가로 방향으로, 또 세로 방향으로 값을 가져올 수 있도록 여러 기능을 제공하고 있습니다. 그런데 워크시트의 아래 방향으로 갈수록 더 높은 레벨에 해당하는 값이 나오도록 테이블을 설계했는데 어떤 계산이 값을 위, 아래 방향이 아니라 좌, 우 방향으로 가져와야 하는 상황이 생긴다면 이 상황을 처리할 수식을 고안하기에 앞서 한 워크시트에 규칙이 서로 다른 데이터가 섞여 있지 않은지 의심해 봐야 합니다. 만약 그런 데이터가 섞여 있다면 이들을 서로 다른 워크시트로 분리하거나 데이터의 진행 방향을 같은 방향으로 맞추기만 해도 수식을 더 단순하게 만들 수 있습니다.

일시적으로 사용할 수식은 데이터 워크시트에 섞지 말고 다른 워크시트를 만든 다음 위 요령에 따라 현재 워크시트로부터 값을 가져간 다음 그 값을 기준으로 계산해야 합니다. 위에서 계산 대상이 될 값, 이들을 가져오는 수식, 값을 계산하는 수식을 서로 분리하라고 설명했습니다. 같은 요령으로 일시적인 계산이 필요할 때 그 계산에 사용할 수식을 데이터 워크시트에 섞어서 쓰지 않아야 합니다. 일단 데이터와 계산이 같은 워크시트에 있으면 관리하기 어려울 뿐 아니라 또 다른 계산이 이 워크시트의 임시 계산을 참조할 수도 있습니다. 동작은 하겠지만 유지보수 문제를 쉽게 일으키므로 값과 계산을 같은 워크시트에 섞지 말고 또 값을 가져갈 때는 반드시 데이터 워크시트만을 참고해야 합니다.

결론. 엑셀에서 복잡한 수식을 작성할 수 있음은 자랑이 아닙니다. 핵심은 의도한 결과를 정확히 얻어내고 미래의 내가 다시 이 워크시트와 수식을 읽어도 계산 과정을 이해할 수 있어야 하며 실수하더라도 이를 쉽게 발견할 수 있는 모양이어야 합니다. 그러려면 수식은 짧을 수록 좋고 분리되어 있을수록 좋으며 데이터와 계산이 서로 분리되어 있을 수록 좋습니다.