실무에 가장 많이 쓰는 직장인 엑셀함수 – 기본편

 

11월 25일, 또 하나의 직장인 영화가 개봉을 한다는 소식입니다. 취업만 하면 인생 제대로 즐길 거라 생각했던 햇병아리 회사원 '도라희'(발음 주의)의 이야기인데요. 완벽한 커리어우먼이 될 거라는 환상도 잠시, 첫 출근날 부장님께 탈탈 털려 초단기 퇴사 위기에 처하네요. '다시 해'라는 말과 함께 애써 작성한 기사는 허공으로 흩어지고, 밤새 준비한 보고서는 갈기갈기 찢어지니, 아. 말단 사원의 마음도 함께 찢어집니다. 만약 저와 같이 엑셀로 숫자를 다루는 것이 주 업무인 직장인이라면, 다시 하는 데만 해도 한참이 걸릴 테니까요.

출처: 네이버영화 <열정같은소리하고있네>

매번 이렇게 탈탈 털리고 혼나다가 처음부터 자료를 다시 정리하다 보면 회사생활의 반은 엑셀이라는 것을 마음속 깊이 느끼고는 합니다. 입사 전에 잘 배워둔 엑셀 하나가 직장생활을 얼마나 편하게 하는지도요. 그래서 직장생활 중 가장 많이 사용하게 되는 직장인 필수 엑셀 함수를 모아보았습니다.

 

엑셀이랑 썸 타듯 SUM만 쓰세요?

 

대개는 SUM함수만으로도 가능합니다. 가장 많이 쓰이기도 하고요. 하지만 조금 더 레벨이 있는 데이터에서는 목록별 합계가 필요할 때도 있습니다. 이런 경우 많이 사용하는 함수가 바로 SUMIF와 DSUM입니다.

 

함수를 테스트하기 전에 데이터를 하나 만들었어요. 각 항목의 숫자들은 RAND함수를 사용하였습니다. 참고로 RAND함수를 사용하여 숫자를 생성한 후에는 값으로 붙여넣기를 해주어야 합니다. 그렇지 않으면 숫자가 계속 변하거든요.

위 이미지에 쓰인 데이터는 예제용으로 실제와는 관련성이 없음을 알려드립니다.

/SUMIF

SUMIF는 위 데이터 중 부서명 별로 합계를 구할 때 사용합니다. 일일이 부서명을 구분하여 합할 필요가 없어졌죠. 단 한번의 수식으로 끝낼 수 있습니다. 

/DSUM

DSUM은 데이터베이스 내에서 셀이나 수, 텍스트 조건에 맞는 값을 찾아 합계를 계산해주는 함수입니다. 보통 부품이나 제품의 모델명이 많이 혼재되어있을 때 사용하면 간편하게 합계를 낼 수 있게 도와줍니다.

 

 

셀의 개수를 세는 네 가지 함수

 

셀의 개수를 셀 땐 COUNT 함수를 생각하기 마련입니다. 하지만 COUNT에도 4가지가 있어요. COUNT, COUNTA, COUNTIF, COUNTIFS. 상황에 따라 조금씩 차이가 있으니 네 가지 모두 익혀두었다가 적절한 때에 골라서 사용하세요.

 

/COUNT

COUNT는 지정한 범위 내에 숫자가 있는 셀의 수를 세어 결과값으로 보여줍니다.

 

/COUNTA

COUNTA는 지정된 범위 내에서 빈칸이 아닌 셀의 수를 결과값으로 보여줍니다. 셀이 숫자로 채워져 있든 텍스트로 채워져 있든 상관없습니다. 채워져 있기면 하면 카운팅합니다.

 

/COUNTIF

COUNTIF는 지정된 범위 내에서 조건에 맞는 값을 가진 셀의 개수를 구할 수 있습니다. 대신 조건은 딱 하나만 설정할 수 있어요. 조건은 숫자나 텍스트, 수식 등이 가능합니다. 대신 수식이나 텍스트로 조건을 만들 경우 조건 부분에 “”(큰 따옴표)를 넣어주어야 합니다.

 

/COUNTIFS

COUNTIFS는 COUNTIF와 같은 특징을 가지는데요, 조건을 하나만 지정할 수 있는 COUNTIF와는 다르게 두 개 이상의 조건을 지정하고 그에 맞는 결과값을 얻을 수 있습니다.

 

COUNTIF 함수 사용한 결과 / 4.9이상의 값을 가진 셀을 조건으로 설정

 

COUNTIFS 함수를 사용한 결과 / 4.9 이상 7 미만의 값을 가진 셀을 조건으로 설정

두 개의 데이터를 하나로 정리할 때 불러오기

 

고수든 고수가 아니든 가장 많이 사용하고 또 이것만 있어도 엑셀의 절반이 해결되는 VLOOKUP과 HLOOKUP입니다. 두 함수의 차이점은 세로 값을 가져오느냐 가로 값을 가져오느냐의 차이인데요, 테스트 화면을 보시면 금방 이해하실 거예요.

 

여기 DATA(1)과 DATA(2)가 있는데요, DATA(1)은 계속 정리해오던 파일이고, DATA(2)는 새로 추가된 데이터라고 합시다. 그런데 DATA(2)의 사원명의 순서가 DATA(1)과 맞지가 않죠. 하나씩 선별해서 복사, 붙여넣기 하지 않고 한번에 가져올 때 사용하면 편합니다. 

/VLOOKUP

 

VLOOKUP은 데이터가 세로로 길게 늘어져 있는 경우 사용합니다. 먼저 찾고자하는 셀을 지정합니다. 그리고 새로운 데이터 중에서 찾고자하는 셀을 포함하여 숫자나 텍스트 데이터가 있는 부분까지 영역을 설정한 후 지정된 영역 중 몇 번째 열을 가지고 올 것인지 선택하면 됩니다. 마지막 논리부분은 정확하게 일치하는 것을 찾으려면 FALSE를, 비슷하게 일치하는 것을 찾으려면 TURE(또는 생략)을 입력해주면 되는데요, 보통은 숫자 0을 적어도 무방합니다. 

/HLOOKUP

HLOOKUP은 앞서 말씀드린 것처럼 데이터가 가로로 길게 나열되어 있는 경우에 사용합니다. 그래서 지정된 영역 중 몇 번째 행에 있는지 숫자를 설정하는 것만 VLOOKUP과 다릅니다. 

 두 가지 데이터를 하나로 합치거나 나누거나

가끔이지만 두 개의 셀 내용을 하나로 합쳐야 할 때가 있습니다. 또 아주 가끔이지만 하나의 셀 내용을 두 개로 나눠야 할 때가 있습니다. 셀 내용을 나누는 것은 ‘메뉴>데이터>텍스트나누기’를 사용하면 편하지만 합치는 함수는 쓸 때마다 검색 찬스를 쓰고 있으실 거예요. 미리 적어두었다가 사용하면 편하실 겁니다. 바로 CONCATENATE입니다.

/CONCATENATE

CONCATENATE는 여러 셀의 내용을 하나로 합쳐주는 함수인데요, 함수 뒤에는 셀만 지정해주면 되기 때문에 간편하게 합칠 수 있습니다. 예제 데이터에서는 중간에 ‘/’를 넣어서 구분해주었습니다. 이것과 비슷하게 사용할 수 있는 것이 ‘&’인데요, 두 개의 셀을 합치는데는 복잡하지 않지만 여러 개의 셀을 합칠 때는 다소 수식이 복잡해질 수 있습니다. 

 보기 싫은 에러 메시지 없이 깔끔하게

여러 수식을 적용하다보면, 특히 통계나 재무, 회계에서는 많은 셀이 수식 에러들로 채워지는 경우가 많습니다. 내부에서 보는 용도라면 그냥 두어도 괜찮을지 모르지만 외부 발송이나 보고용으로 작성된 엑셀파일이라면 출력했을 때 보기에 좋지 않을 겁니다. 이럴 때 에러들을 숫자 0으로 바꿔주거나 빈칸으로 바꿔주는 수식이 바로 IFERROR입니다. 

먼저, 대표적으로 나타나는 에러들을 정리해보았습니다. 

/IFERROR

 

IFERROR는 숫자나 셀을 지정할 수 있습니다. 그리고 결과값으로 숫자나 텍스트를 출력할 수도 있고요. 하지만 처음부터 IFERROR 함수를 사용하기보다 검증이 모두 완료된 후 마지막에 적용하는 것이 더욱 정확한 엑셀을 완성할 수 있습니다. 

지금까지 엑셀 함수 10가지에 대해서 알아보았습니다. 이미 엑셀의 고수님들이 보시기에는 정말 기본 중의 기본이라고 하실 수도 있고 처음 엑셀을 접하는 분들은 어렵게 따라하시기도 하겠죠.

엑셀을 사용하면 할수록 참 직장인을 위한 정직한 프로그램인 것 같다는 생각이 듭니다. 아는 만큼 더 세련되고 더 간단해지거든요. 아무리 좋은 스마트기기가 나온다고 하여도 알아서 제 입맛에 맞는 수식을 만들어주진 못하잖아요. 우리 다함께 엑셀의 달인이 되어서 퇴근 시간을 앞당겨보는 건 어떨까요?

Microsoft excel 2016 버전을 사용하여 작성하였습니다.

설명에 오류가 있거나 수식이 잘못 표기된 경우,

그리고 추가 설명을 더 해주실 분은 댓글에 남겨주세요.

↓예제파일 다운 받기↓

자주쓰는 엑셀함수_샘플.xlsx