이번에 알려드릴 함수는 사용법은 간단하나 개념적으론 어려운 함수입니다. 그래도 기본 함수에서 확장한 함수들(~IF, ~IFS)을 알아보았으니 지금 알아보는 것이 맞을 것 같습니다. D~ 함수도 ~IF나 ~IFS 함수처럼 기본 기능에 조건 기능이 추가된 함수입니다. 여기서 "D"는 DATABASE의 첫 글자입니다.
통상적으로 DATABASE는 통합·관리되는 데이터의 집합이자 저장소를 말합니다. 뭔 말인지 어렵죠. 설명하자면 좀 복잡합니다. 하지만 여기에서 "D"는 통상적인 개념의 데이터베이스가 아니라 데이터베이스 형식으로 만들어진 표를 의미하는 것이니 그다지 어렵진 않습니다. 레이블과 레코드로만 이루어진 표를 의미합니다. 레이블과 레코드는 이미 기초 강좌에서 설명한 바 있습니다. 잘 모르겠다면 아래 강좌를 참고해 주세요.
이미 다 설명해 드린 내용이지만 잘 모르신다면 아래 강좌를 확인하고 돌아와 주시기 바랍니다.
~IFS 함수 (SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS) - 다중 조건 기능 추가하기
엑셀 함수 중 뒤에 IFS가 붙은 함수(SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, MINIFS)에 대해 설명합니다. ~IFS로 끝난 함수는 원래 함수의 기능에 다중 조건 기능이 추가된 것임을 알아봅니다. 1개의 조건이 추가될 때마다 함수 인수 2개(criteria_range와 criteria)가 추가 되는 이유를 설명합니다. ~IF 함수와 비교하여 인수들의 위치를 알아봅니다.
https://flylight.kr/
VLOOKUP 함수 ❶ 특정 데이터 찾기
엑셀 VLOOKUP 함수의 사용법을 소개합니다. VLOOKUP 함수의 기능과 인수를 정리합니다. 사람들이 특정 값을 찾는 방법을 논리적으로 따져보고 실제 VLOOKUP 함수의 인수와 비교해 봅니다. VLOOKUP 함수를 실제로 적용해 보고 자동 채우기를 할 경우 어떤 문제가 있는지 확인합니다. 함수로 작성된 셀의 자동화 결과를 확인하고 장점을 알아봅니다.
https://flylight.kr/
엑셀 필터 ❽ 고급 필터 ① 기본 사용법(목록 범위, 조건 범위, 복사 위치)
엑셀 고급 필터 기본 사용법을 설명합니다. 고급 필터 사용 시 목록 범위, 조건 범위, 복사 위치가 무엇인지 알아보고 작성하는 방법을 소개합니다. 복사 위치에 따른 3가지 사용법을 설명하고 복사 위치에 만든 필터링 결과를 삭제하는 방법과 고급 필터 사용 시 주의해야 하는 점을 소개합니다.
https://flylight.kr/
고급 필터는 위에서 하나의 강좌만 연결했지만, 가능한 5가지 강좌를 모두 보시고 오시는 것이 좋습니다. 고급 필터의 핵심은 조건 범위를 다양하게 만들 수 있는지이고 알아볼 D~ 함수 역시 동일한 방법으로 조건 범위를 만들기 때문에 다양하게 만들 수 있는 개념을 꼭 정립하시길 바랍니다. 물론 알고 있다면 아래 내용으로 바로 들어가셔도 좋습니다.
일단 D~함수를 왜 사용하는지부터 설명하겠습니다. 예제 파일을 열어 주세요.
예제는 ~IF 함수부터 사용하고 있는 문서입니다. ~IFS 함수로 "지역이 경기이고 제품분류가 전자제품인 것의 금액 합"까지 알아보았었죠.
그러데 "지역이 경기이거나 제품분류가 전자제품인 것의 금액 합"은 구할 수 있나요? ~IFS 함수로는 불가능합니다. 모든 조건에 만족하는 것은 구할 수 있지만 한 가지 조건에만 만족하는 것들의 합은 구할 수 없습니다. 즉 AND 조건은 가능하지만, OR 조건은 할 수 없다는 것입니다.
해결 방법이 없는 것은 아닙니다. 이 경우라면 지역이 경기인 것의 합을 SUMIF로, 제품분류가 전자제품인 것의 합은 또 SUMIF로 반환하고 값을 더하는 방법을 사용하면 되겠죠. 하지만 수식은 길어질 것입니다. 3가지, 4가지, ... 조건이 늘어난다면 수식은 굉장히 길어질 것이고 수식이 길어지는 것은 오타나 참조를 잘못할 확률도 높아진다는 얘기입니다.
이걸 간편하게 해주는 함수가 DSUM 함수입니다. 기본 함수들 AVERAGE, COUNT, MAX, MIN도 모두 앞에 "D"를 붙여 사용할 수 있습니다.
필터 편에서도 같은 얘기를 했습니다. 필터 역시 서로 다른 항목의 경우 AND 조건은 가능, OR 조건은 불가능했었죠. "지역이 경기이고 제품분류가 전자제품인 것"은 필터링할 수 있지만 "지역이 경기이거나 제품분류가 전자제품인 것"은 일반 필터로는 할 수 없었습니다. 고급 필터를 사용해야 했습니다. 고급 필터의 조건 범위를 만들어야 가능했죠. D~ 함수도 같습니다. 조건 범위가 반드시 필요하다는 얘기입니다.
조건 범위를 만들겠습니다. OR 조건으로 만들어져야 하고 항목은 두 개이므로,
위 그림처럼 만들어야 합니다. "빈 셀은 모든 것을 의미한다." 했습니다. 어렵다면 역시 위에서 언급한 고급 필터 편을 확인해 주세요.
조건 범위를 만들었다면 모든 준비가 끝났습니다. 각 함수들을 정리하겠습니다.
DSUM데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 숫자의 합계 반환
DSUM(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
DAVERAGE데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 숫자의 평균 반환
DAVERAGE(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
DMAX데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 최대값 반환
DMAX(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
DMIN데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 최소값 반환
DMIN(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
DCOUNT데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 숫자 셀의 개수 반환
DCOUNT(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
DCOUNTA데이터베이스에서 조건 범위의 조건과 일치하는 레코드의 필드(열)에 있는 비어있지 않는 셀의 개수 반환
DCOUNTA(database, field, criteria)| database | 모든 조건의 항목과 찾을 값을 포함한 범위로 반드시 레이블과 레코드로 이루어진 범위 |
| field | database의 레이블명 또는 database의 열 순서 |
| criteria | 조건 범위 |
기본 함수 5가지와 추가로 DCOUNTA까지 정리했습니다. COUNT와 COUNTA는 숫자 셀만 셀 것인지, 문자도 셀 것인지의 차이였죠.
함수 정리라서 모든 것을 정리했지만 보셨듯이 인수들이 모두 같습니다. 함수의 기능은 모두 기본 함수의 기능입니다. 조건 기능만이 추가 되었는데 그 조건 기능을 고급 필터의 조건 범위로 사용한다는 것입니다.
다음, 함수 기능과 인수명을 확인했으니 각 인수들에 대해 자세히 알아보겠습니다.
세 번째 인수부터 설명하는 편이 다른 인수를 이해하기 좋겠네요.
세 번째 인수 criteria는 ~IF, ~IFS 함수의 인수에서 본 "조건"입니다. 하지만 D~ 함수에서 다른 점은 단순 값이 아니라 고급 필터의 조건 범위처럼 만든 범위라는 것입니다. 위에서 만든 조건 범위이죠. 이 조건 범위를 어떻게 만드냐에 따라 조건이 달라지니 조건을 만드는 방법은 꼭 고급 필터 편을 확인하시고요.
다음, 첫 번째 인수 database입니다. database는 VLOOKUP 함수의 table_array 인수와 유사합니다. table_array는 원본 표에서 기준값을 갖는 항목부터 찾을 값 항목의 모든 범위였죠. database도 유사합니다. 모든 조건 항목과 계산될 항목의 전체 범위입니다. 위에서는 지역과 제품분류가 조건 범위이고 실제 계산 항목은 금액(원)입니다. 즉 "지역" 항목부터 "금액(원)" 항목까지의 전체 범위를 선택하면 됩니다. 제품분류는 그 범위에 포함되기 때문입니다.
그런데 여기서 주의하실 사항이 있습니다. table_array와 database가 결정적으로 다른 점인데, table_array는 레이블을 포함할 필요가 없지만 database는 반드시 레이블을 포함해야 한다는 점입니다. 이유는 critera(조건 범위) 인수로 만든 표의 레이블을 database의 레이블에서 찾기 때문입니다. database에 레이블이 없다면 당연히 찾을 수 없겠죠. 그리고 이 얘기는 criteria 레이블의 명칭이 database 레이블의 명칭과 동일해야 한다는 얘기이기도 합니다.
다음, 두 번째 인수 field입니다. 사실 field에 관해 설명하자면 좀 길어질 것 같은데 여기에서 field는 단순하니 간단하게 설명하겠습니다. 괜히 복잡해질 필요는 없으니까요. 이것도 VLOOKUP 함수를 생각하시면 쉽습니다. VLOOKUP 함수에서 인수 table_array를 만들고 나면 그 범위는 열로 구분되죠. 그리고 찾을 값을 table_array 열의 순서인 col_index_num 인수로 찾았습니다. 여기서 field도 database의 열입니다. 첫 번째 열을 1, 두 번째 열을 2, ...로 사용할 수 있습니다. 여기까지는 col_index_num과 같습니다. 하지만 다른 점이 있는데 바로 그 찾을 열의 항목명을 사용할 수도 있다는 것입니다. 위의 예에서는 찾을 값 "금액(합)"입니다. 즉 열의 순서로도, 열의 항목명으로도 사용할 수 있다는 얘기입니다.
인수들의 설명까지 끝났습니다. 개념적으로는 굉장히 난도가 높은 함수지만 사용법은 간단합니다. 이제 "지역이 경기이거나 제품분류가 전자제품인 것의 금액 합"을 실제로 반환해 보겠습니다.
- 함수를 작성할 셀(F4)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =DSUM(
- 첫 번째 인수 힌트(database)를 확인하고 조건 항목들과 실제 계산 항목이 포함되게 범위를 참조합니다. => =DSUM(D6:F69
(범위가 길기 때문에 키보드 단축키를 활용하는 것이 좋습니다. D6셀 클릭 > ctrl+shift+→(우측 방향키) > ctrl+shift+↓(아래 방향키) > ctrl+backspace) - 쉼표로 구분, 두 번째 인수 힌트(field)를 확인하고 database 범위 중 실제 결과를 계산할 열의 순서를 입력합니다. => =DSUM(D6:F69,3
(열의 순서 대신 항목명 "금액(원)"을 입력하셔도 되고, 간단하게 F6셀을 참조해도 됩니다.) - 쉼표로 구분, 세 번째 인수 힌트(criteria)를 확인하고 이미 만들어 놓은 조건 범위를 참조 합니다. => =DSUM(D6:F69,3,H6:I8
- 함수가 하나이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =DSUM(D6:F69,3,H6:I8)
결과, 값이 정상 반환됩니다.
아마 눈치채신 분들도 있을 것 같은데 SUBTOTAL 함수를 사용하고 고급 필터를 사용해도 동일한 결과를 얻을 수 있습니다. 개념적으로는 SUBTOTAL 함수가 오히려 쉬울 수 있습니다. 다만 반드시 고급 필터를 사용해야 한다는 단점이 있죠. 하지만 필터링된 데이터만을 볼 수 있다는 장점이기도 합니다.
몇 가지 조금 더 설명하겠습니다.
위 ❸ 단계에서 인수 field는 순서, 항목명을 모두 사용할 수 있다고 했습니다. 그러니까, 숫자 3, "금액(원)", 참조 F6이 모두 사용할 수 있다는 얘기죠.
=DSUM(D6:F69,3,H6:I8)
=DSUM(D6:F69,"금액(원)",H6:I8)
=DSUM(D6:F69,F6,H6:I8)
즉, 위 세 가지 중 편한 것을 사용하면 됩니다. 물론 수식에서 문자를 사용할 경우 큰따옴표로 감싸야 한다는 것을 잊지 마시길 바랍니다.
다음, database는 조건 항목과 실제 계산할 항목이 포함되기만 하면 됩니다. 위의 경우라면 사실 주문번호부터 금액(원)까지 원본 표의 전체 범위로 선택하셔도 됩니다. 조건 항목인 지역, 제품분류 그리고 실제 계산할 항목인 금액(원)도 결국 포함되기 때문이죠. 하지만 굳이 불필요한 항목까지 database로 잡을 필요가 없으니 선택하지 않은 것뿐입니다. 이 경우라면 field의 번호는 달라지겠죠.
그리고 한 가지 더,
만약 원본의 표가 좌측 그림처럼 레이블이 2행으로 만들어진 표라면 database를 어떻게 선택해야 할까요?
이 경우 원본 표의 첫 행부터 database를 잡으면 안됩니다. 말씀드렸듯이 원본 표의 첫 행은 criteria의 레이블이 포함된 형태여야 합니다. 위의 경우 첫 행에는 criteria 범위의 레이블이 없습니다. 가장 좋은 선택은 우측 그림처럼 선택하는 것이고 역시 불필요하지만 주문범위부터 선택하셔도 됩니다. 첫 행은 빼야 한다는 것입니다.
의견글 입니다.
()
