엑셀 배우기, DATE/ EDATE/ RANDBETWEEN/ VLOOKUP 함수 활용 및 셀서식!

오늘은 다중 조건을 만족하는 셀의 합계를 구할 수 있는 SUMIFS 함수를 다뤄봅니다. 자주 이야기하지만 엑셀은, 특히 함수는 창의성입니다. 수학과도 많이 닮은 부분인데, 실제적인 문제를 수학적 계산으로 풀어 내듯이, 엑셀 역시 실무적 문제를 함수로 풀어낸다는 점입니다. 그래서 엑셀의 모든 함수를 전문가처럼 다룰 필요는 없지만, 최대한 많은 함수를 알아두는 건 꼭 필요합니다. 

그래야, 어떠한 상황에 어떠한 함수가 필요할지 고민을 해볼 수 있기 때문이고, 그래서 엑셀과 함수가 창의성이란 이야기입니다. 

그렇다면 SUMIFS함수가, 보다 구체적으로 다중조건을 만족하는 셀의 합계를 구할 수 있는 함수가 엑셀 실무에서 어떤 식으로 사용될 수 있는지를 확인해 보겠습니다. 여러분이 매출 장부를 아래와 같이 일별로 관리해 왔는데, 주기적으로 월별/품목별 매출 정보(판매 수량, 판매 금액 등등)를 확인하고 보고해야할 필요가 있다고 하겠습니다. 조건이 두개이지요? '월별' 그리고 '품목별', 이러한 경우에 아주 유용한 함수가 SUMIFS 함수라는 겁니다. 

바로 본론으로 들어가기 전에, 일단 위의 표를 만든 과정부터 설명해 드리겠습니다. 일단 빨간 점선의 박스는 SUMIFS 함수와는 관련이 없답니다. 다만, 이전 포스팅의 복습 차원에서, 그리고 위의 특정 데이터(날짜)를 하나씩 만들기 귀찮아서, 또 얼마나 다양한 함수를 자유롭게 활용 가능한지도 보여드리고 싶어서 말입니다.

일단 A열의 1부터 12까지의 숫자들은 제가 직접 채워 넣었습니다. 그리고 B열은DATE 함수로 매월 첫째날을 만들었답니다. DATE 함수 구성은 '=DATE(년, 월, 일)'인데, 아래의 B열에서는 '년' 인수를 2017로 고정하고, '월'을 A열로 지정하고, '일'에는 숫자 '1'을 넣었습니다. 이렇게하면 DATE 함수로 매월 첫째 날짜를 만들 수 있습니다.

그리고 C열에는 EDATE 함수로 B열의 매월 첫재날을 기준으로 매월 말일을 계산했습니다. EDATE 함수는 시작일 기준으로 특정 개월수 뒤의 날짜를 반환합니다. 그러니 매월 첫재 날 기준 1개월 후의 날짜를 EDATE 함수로 구할 수 있고, 이 값에서 1을 빼주면, 매월 말일이 되는 겁니다. 즉, EDATE 함수로 1월1일의 1개월 후 값을 받으면, 2월 1일이되고 여기에서 1을 빼주면, 1월 31일이 되는 겁니다. EDATE 함수의 구조는 아래처럼 '=EDATE(시작일, 개월수)' 입니다. 

다음으로 D2:G24 범위의 표서식 안의 첫 열의 데이터, 즉 날짜 데이터들은 B열과 C열 값들 사이에서 RANDBETWEEN함수로 난수를 발생시켰습니다. 이렇게해서 D열 날짜 데이터를 '임의로' 만들어 냈는데, 실무에서는 당연히 매출이 발생할 때마다 해당 날짜 데이터는 직접 입력하는 부분입니다. 

또한 RNADBETWEEN 함수 구성은 아래처럼, '=RANDBETWEEN(최소값, 최대값)'인데, '최소값'과 '최대값'을 포함해서 범위 내의 난수를 발생시킵니다. 즉, 아래의 '=RNADBETWEEN(B3, C3)'는 1월 1일부터 1월 31일 중의 아무 날짜나 임의로 만들어 내라는 것입니다.

다시 말씀드리지만, 위의 '날짜'와 '상품' 데이터들은 각 매출이 발생할 때마다, 직접 입력하는 값들입니다. 그런데 저는 E열, 상품 데이터도 그냥 난수로 만들었습니다. 텍스트를 어떻게 난수로 발생시켰을까 궁금하실텐데, 텍스트를 임의로 발생시킨 것이 아닙니다. 아래처럼, 그냥 1부터 4까지의 숫자를 난수로 만들고, 발생시킨 각 난수 값(숫자)에 셀서식으로 '상품'이란 텍스트를 더했을 뿐입니다.

방법은 아주 간단합니다. 셀을 선택된 상태에서 컨트롤 키와 숫자 1을 동시에 눌러 셀서식 창을 활성화 시킵니다. 그리고, '범주'에서 사용자 지정을 선택하고, '형식' 입력 창에 "상품"을 추가해줍니다. 엑셀에서는 텍스트를 처리할 때, 쌍따옴표(" ")로 묶어주어야 합니다. 그리고 이렇게 해두면, 해당 셀은 '상품 1'로 표시되지만, 실제 셀의 값은 여전히 숫자 1이고, 따라서 텍스트가 아닌 숫자 셀에는 함수를 활용할 수 있게됩니다.

예를들어, 1년, 2주, 3일 등등의 이러한 기간 데이터에 숫자와 텍스트를 함께 입력한다면, 해당 셀들은 텍스트로 인식되어 함수를 제대로 활용할 수가 없습니다. 이러한 경우에, 셀서식으로 1(년), 2(주), 3(일) 등으로 텍스트가 자동으로 입력되게 만들 수 있다는 겁니다.  또한 매번 상품 1, 상품 2, 상품 3, 이런식으로 텍스트를 입력할 필요없이, 숫자 1, 2, 3만 입력해주면 '상품' 텍스트는 자동으로 입력된다는 장점도 있습니다.

서론이 정말 길어고 있는데, 마지막으로 VLOOKUP 함수만 확인하고, 본론인 SUMIFS 함수로 들어가겠습니다. 매번 매출 발생 시마다, 상품 가격을 직접 입력한다면 그것만큼 불편한 것은 없을 겁니다. 엑셀은 '불편' 없이 자동화하려고 하는 건데 그러한 불편을 감수할 필요는 없습니다. 그리고 실제 품목이 4개가 아닌 40개라면, 그걸 다 기억할 수도 없고, I와 J열처럼 한쪽에 기록해 놨다 하더라도, 매번 눈으로 찾아 직접 입ㄺ하는는 수고를 할 수는 없습니다. 이때 필요한 것이 VLOOKUP 함수입니다. 

VLOOKUP 함수는 가장 대표적인 참조 함수중의 하나입니다. 아래처럼 각 상품명이 입력되면, 전체 상품 가격 테이블(I2:J6)을 참조해서 해당 상품의 '가격'을 불러옵니다. 표서식으로 해서 수식이 좀 이상하게 보일텐데, VLOOKUP 함수 구성은 간단합니다. '=VLOOKUP(찾을 값, 참조할 범위, 지정한 범위에서 참조할 값의 열 번호, FALSE), 'FALSE'는 정확히 일치하는 값을 불러온다는 의미입니다. 거의 대부분의 경우에서 항상 'FALSE'로 두시면 됩니다.

 F3 셀에서 사용된 VLOOKUP 함수의 의미는' I2:J6' 범위에서 'E3' 값이 위치하고 있는 행의 '2'번째 열의 값을 불러오라는 겁니다. 표서식을 쓰지 않았다면, '=VLOOKUP(E3, $I$2:$J$6, 2, FALSE)'로 보이겠지요. VLOOKUP 함수에서의 또다른 핵심은, 참조할 범위를 절대 주소로 지정해야한다는 겁니다. $I$2:$J$6'의"$"표시가 해당 범위를 절대 주소로 지정하는 역할을 하는데, F3 셀에서 사용한 수식을 채우기핸들로 복사할 때, 절대 주소로 지정되지 않으면 해당 범위가 바뀌어 버리는 사고가 생깁니다.

 

절대 주소는 다음에 다시 정리할 기회가 있기를 바라면서, 이제 정말 본론으로 SUMIFS 함수로 넘어갑시다!

엑셀배우기, SUMIFS 함수로 월별/품목별 판매수량/매출액 구하기!