흔히 엑셀은 마법을 부리는 똑똑한 계산 프로그램으로 알려져 있다. 과연 그럴까?

이렇게 인식되는 까닭은 몇 개의 수식만으로 몇 만개의 계산을 단 몇 초 만에 ‘짜잔~! ‘하고 뚝딱 해치워 버리는 경험을 해 보았기 때문일 것이다.

그러나 엑셀 수식 활용은 그렇게 만만하지 않다. 수식은 엑셀의 수많은 기술 중 핵심이며, 거의 모든 기능에 사용되고 있지만, 아무리 전문가라 하더라도 현란한 수식으로 매번 오류 없이 자신 있게 계산하기란 쉽지 않을 것이다.

엑셀은 다양하고 복잡한 수식을 쉽게 처리하기 위해 함수라는 기능을 제공하고 있다. 그러나 아이러니하게도 대부분의 엑셀 사용자는 함수를 제일 어렵다고 생각한다.

필자 또한 함수가 어렵다고 생각한다.

이 이유는 산수에서 이미 배워 잘 알고 있는 사칙연산의 수식이나 단수 함수로 계산하는 것 외에 실무에서는 수식에 수식을 더하고 함수에 함수를 중첩해서 사용하는 복잡한 계산이 비일비재로 일어나고 있기 때문이다.

오늘은 이런 수식을 ‘어떻게 하면 쉽게 작성할 수 있는가?’ 또는 소위 ‘네OO 지식O의 도움 없이도 내 자료를 계산할 것인가?’에 대해 말해보고자 한다.

참조를 다양하게 익혀라!

엑셀은 하나의 셀에서 계산된 수식을 다른 데이터에 적용해 아무리 많은 데이터라도 한 번에 계산하도록 만든 시스템이다. 따라서 첫 번째 수식 작성이 매우 중요하며, 복사하는 다른 셀에 해당 수식이 제대로 적용되려면 데이터와의 관계를 잘 설정해 두어야 한다. 엑셀은 수식에 셀의 값을 직접 입력하지 않고 참조하는 방법으로 계산한다. 즉, 참조란? 수식에 적용되는 피연산자의 변수를 말하며, ‘=A1 B1’이라는 수식이 있을 때 A1, B1이라는 셀의 값을 우리는 ‘참조한다’라고 한다. 이렇게 작성된 수식이 다른 셀에 복사되어 수식으로 사용되게 하는데 이때 주소는 참조 방법에 따라 변하거나 고정되도록 지정할 수 있게 된다.

참조 주소를 고정하려면 달러($) 기호를 행이나 열 주소 앞에 붙이게 되는데 ‘A1’과 같이 $가 없는 주소는 셀 위치에 따라 같이 변하게 되며 이런 주소를 ‘상대 참조’라고 한다. 반면 ‘$A$1’처럼 고정된 주소로 참조되는 것을 ‘절대 참조’라고 한다.

또한 어느 한쪽만 고정된 주소($A1, A$1)는 ‘혼합 참조’라고 부른다.

한가지 중요한 사실은 절대 참조, 상대 참조, 혼합 참조는 수식을 복사할 때만 고려되는 사항이라는 것이다. 값을 참조할 때 그 수식을 복사하여 나머지 셀을 계산한다면 반드시 참조의 방식을 생각해 보아야 한다.

※ 상대 참조의 예(금액의 수식)	※ 절대 참조와 혼합 참조의 예(세금의 수식)

위의 그림에서 금액을 구하는 수식은 ‘단가’와 ‘판매량’의 곱으로 계산할 때 각 위치마다 참조의 위치가 변경되어야 하므로 상대 참조로 작성한 후 나머지 수식을 채우기 핸들로 복사하고, 세금의 수식에서는 각 금액에 세율을 고정시켜 계산해야 하므로 금액 셀인 ‘F6’은 상대 참조, 세율은 ‘$G$3’ 또는 ‘G$3’으로 입력한 후 복사한다. 여기서 혼합 참조가 가능한 이유는 수식을 수직 방향으로 복사하며 이동하므로 G열은 고정시킬 필요가 없기 때문이다.

조금 어렵게 느껴질 수 있지만 참조는 수식 복사의 필수 사항이므로 반드시 알고 사용해야 한다. 이 참조의 형식은 연산자를 사용하는 수식이나 함수의 수식에 공통으로 적용된다.

함수의 중첩은 거꾸로 하라!

엑셀은 400여 개가 넘는 함수로 수식을 쉽게 계산할 수 있도록 도와준다. 이렇게 쉽다고 생각하는 함수도 막상 여러 개의 함수를 중첩해서 사용해야 할 경우라면 무슨 함수부터 입력해야 할지 난감할 때가 있다. 그 이유는 우리가 생각하는 수식 계산을 거꾸로 사용해서 설계해야 되기 때문이다.

예를 들어 평균을 계산한 후 계산 결과가 489.4523077.. 과 같이 긴 소수점 자릿수를 포함한 값이 나온다면 당연히 우리는 적당한 위치까지 반올림하여 계산하고 싶어 한다. 그렇다면 ROUND 함수와 AVERAGE 함수 중에 어떤 함수를 먼저 열고 중첩을 실행해야 할까?

이런 경우 생각과는 다르게 함수식에서는 ROUND 함수를 먼저 실행하고 그 안에 AVERAGE 함수를 중첩해야 한다. 좀 더 많은 함수를 한 번에 입력하려면 순서가 무척 복잡해지고 혼란스럽게 되는데 계산의 작업 순서를 거꾸로 돌려서 생각해보라.

우리의 계산 순서는 평균(AVERAGE) 후에 반올림(ROUND)으로 처리하지만 함수는 ‘=반올림 함수(평균값, 소수 이하 자릿수)’로 계산해야 한다.

소수 2자리까지 계산을 원한다면 수식은 ‘=ROUND(AVERAGE(범위),2)’로 계산하면 된다. 이처럼 함수식이 복잡해질수록 계산의 순서는 뒤죽박죽될 가능성이 있다.

따로 계산한 후 다음 수식에 대치하라!

1. 먼저 판매량의 평균 수식을 작성한다.

2. 평균 값으로 다시 반올림을 계산한다.

3. 평균의 수식을 복사하여 반올림 수식에서 평균을 참조한 I9셀을 지우고 대치한다.

최종 수식은 =ROUND(AVERAGE(E6:E70),2)가 되고 이제 두 수식이 합쳐졌음으로 평균을 작성했던 수식은 삭제해도 되는 것이다.

이와 같은 방법으로 함수를 대치해가면 아무리 복잡한 중첩 함수식이라 하더라도 쉽게 하나의 수식으로 작성할 수 있게 된다.

엑셀의 수식은 어쩌면 설계의 미학일 수 있다. 얻고자 하는 값을 수식으로 잘 설계한다면 그다음은 함수의 선택이고 그 함수의 배치를 정하면 된다. 그러나 이렇게 잘 작성된 수식도 복사 과정에서 참조를 잘못하면 엉망이 되어 버리고 만다. 오류가 없더라도 반드시 중간 수식을 열어 검산하는 습관을 키운다면 엑셀 수식의 정복은 이제 곧 성과물이다.

계산에 오류가 생겨 잘못된 결과가 도출되면(별것 아닌 것 같지만) 회사를 위기에 빠뜨릴 수도 있다. 그러나 항상 언급하는 말이지만(슬프게도) 엑셀에게 책임을 떠넘길 수는 없다는 것이다.