D~ 함수 (엑셀 함수 - DCOUNTA, DAVERAGE, DSUM)

이번 강좌는 살짝 두렵네요. 엄청 길어질 것 같은 예감이....

좀 어렵지만 원리만 알고 있다면 또 어렵지 않게 느껴지실 수도 있습니다.

 

엑셀에서 가장 기초 함수라고 하면 SUM. AVERAGE, COUNT, COUNTA라 할 수 있습니다. 의미만 보자면 합계, 평균, 개수를 반환하는 함수입니다. 1개의 데이터의 합계, 평균, 개수를 구할 일은 없습니다. 여러개의 값에서 합계, 평균, 개수를 구할터이고 그 말은 인수로 범위를 반환해야한다는 의미이기도 합니다. 이 개별 함수의 사용법은 엑셀 함수 - 집계,계산관련(COUNT,COUNTA, AVERAGE, SUM) 강좌를 확인하시고 반드시 사용법은 익히시기 바랍니다.

 

다음 위의 함수의 사용법을 익히셨다면 "이 함수를 조금 더 확장할 수 없을까?" 라고 생각이 이어질 수 있습니다. 가령 과일의 판매 현황표라 가정하면 합을 구하는데 가령 사과만의 판매 합을 알수 없을까? 포도만의 평균을 알수 없을까? 복숭아만 개수를 알수 없을까? 라고 말이지요. 바로 이런 조건하에 확장된 함수들이 SUMIF, AVERAGEIF, COUNTIF 입니다. 기본함수명에 IF만 붙은 것이죠.( ~IF 함수 (엑셀 함수 - COUNTIF, SUMIF, AVERAGEIF) 강좌)

 

그런데 여기까지 하고 보니 또 욕심이 생깁니다. 가령 사과가 서울지역에 판매된 것만 합을, 포도가 경기지역에 판매된 평균을, 복숭아가 충청지역에 판매된 개수를 알고싶다라는 것입니다. 바로 조건이 여러개가 되었을때를 알고 싶어지는 것입니다. 이런 경우 사용되는 함수가 SUMIFS, AVERAGEIFS, COUNTIFS 입니다. 기본함수명에  IFS만 붙은 형태이죠.( ~IFS 함수 (엑셀 함수 - COUNTIFS, SUMIFS, AVERAGEIFS) 강좌)

 

위의 함수는 지금까지 잘 사용해 오고 있습니다. 그런데 누군가는 이렇게 생각했습니다. "매번 조건을 만들고 범위를 다시 지정하는 것이 불편한데"라고 말이지요. "어차피 같은 표에서 두번, 세번 범위를 지정할 필요가 있나? 하나의 표에서 해결할 수 있으면 좋을 텐데.."라는 요구가 생기가 되었죠. 그래서 등장한 함수가 바로 오늘 하려는 DCONTA, DAVERAGE, DSUM 함수 입니다. 기본함수 앞에 D가 붙은 형태의 함수입니다. 사실 D로 시작하는 함수는 더 많이 있습니다. 그 중 꽤 많은 함수가 대부분 이렇게 기본함수 앞에 D가 붙은 형태이고요 이 D의 의미는 DATABASE를 의미합니다.

보통 DATABASE라 하면 데이터를 저장한 것을 말하는데 여기서 DATABASE라 함은 곧 표의 형태로 만들진 범위를 의미합니다. 그리고 이렇게 사용되는 함수는 계산될 값의 범위와 조건에 해당하는 범위를 한번에 지정하여 열의 위치 값으로 기본함수의 결과를 돌출하게 합니다. 좀 말이 어려운것 같지만 아래 사용법을 확인하면 이해되리라 생각됩니다.

 

 

DCOUNTA 

데이터베이스의 레코드 필드(열)에서 지정한 조건에 맞는 셀 중 비어 있지 않은 셀의 개수를 계산

DCOUNTA(database, field, criteria)

• database - 표 형태의 범위에서 지정할 셀의 범위. 반드시 레이블(표의 머리글)에 해당하는 행을 포함해야 함
• field - database 인수의 범위 중 머리글의 레이블 또는 레이블의 순서를 숫자로 지정
• criteria - 구하고자하는 함수의 조건에 해당하는 범위(하나의 레이블 아래 레이블의 하나 이상의 조건이 필요)

 

DAVERAGE

데이터베이스의 레코드 필드(열)에서 지정한 조건에 맞는 값의 평균를 계산

DAVERAGE(database, field, criteria)

• database - 표 형태의 범위에서 지정할 셀의 범위. 반드시 레이블(표의 머리글)에 해당하는 행을 포함해야 함
• field - database 인수의 범위 중 머리글의 레이블 또는 레이블의 순서를 숫자로 지정
• criteria - 구하고자하는 함수의 조건에 해당하는 범위(하나의 레이블 아래 레이블의 하나 이상의 조건이 필요)

 

DSUM

데이터베이스의 레코드 필드(열)에서 지정한 조건에 맞는 값의 를 계산

DSUM(database, field, criteria)

• database - 표 형태의 범위에서 지정할 셀의 범위. 반드시 레이블(표의 머리글)에 해당하는 행을 포함해야 함
• field - database 인수의 범위 중 머리글의 레이블 또는 레이블의 순서를 숫자로 지정
• criteria - 구하고자하는 함수의 조건에 해당하는 범위(하나의 레이블 아래 레이블의 하나 이상의 조건이 필요)

 

각 함수의 인수를 보면 모두 동일합니다. 함수의 역할만 다른 것이죠 개수, 평균, 합을 구한다는 것이고 인수가 같으니 하나의 사용법을 익히면 모두 동일하게 사용하실 수 있습니다.

 

예제를 보겠습니다.

 

 

알고 싶은 것은 섬여행의 출발인원 전제 합입니다. 일단 용어부터 정리해 보겠습니다. B2:J10 범위는 엑셀 표를 삽입한 것은 아니지만 데이터의 묶음이니 표라고 할수 있습니다. 표의 머리글(B2:J2)에는 어떤 데이터가 들어가야하는지에 대한 명칭(코드, 여행지, 분류 .... 출발시간)이 문자로 입력되어 있습니다. 이 명칭을 레이블이라 합니다.

그리고 레이블 밑으로 구체적인 정보는 레코드라고 합니다.

 

일단 기본함수인 SUM을 생각해 보겠습니다. 출발인원 전체 합을 의미하는 것이죠. 이경우 "출발인원" 레이블 밑에 레코드의 범위만 있으면 끝납니다. =SUM(G3:G10)

 

하지만 위와 같이 섬여행에 해당하는 출발인원의 전체합이라면 표의 G열에 해당하는 레코드로는 해결할 수 없습니다. D열, 즉 "분류"레이블 밑에서 섬여행에 해당하는 것들을 골라내야 되는 것이죠. 다시말해 합칠 값의 범위인 G열 범위, 조건의 범위인 D열의 범위가 필요하게 됩니다. (SUMIF)

 

만약 섬여행에 3박4일로 가는 출발인원의 합이라면 또 달라집니다. 합칠 값의 범위, 섬여행이라는 조건, 그리고 여행기간이라는 조건이 하나 더 생긴 것이죠 이경우는 조건이 2개가 되어 알아야 할 범위는 G열의 범위, D열의 범위, 그리고 E열의 범위까지를 비교해봐야 합니다.(SUMIFS)

 

이런식으로 조건은 계속해서 추가 할 수 있습니니다. 범위들이 계속 추가가 되는 것이죠.

그런데 어찌 되었던 이 범위들은 처음에 말한 표안에 있습니다. 이 표안에 없는 것들은 합을 구할 수 없습니다. 그래서 합칠값의 범위와 조건의 범위를 한번에 지정해 버리면 조건이 늘어나더라고 계속해서 범위를 추가할 필요가 없어지는 것이죠. 이렇게 한꺼번에 범위를 정한 것이 바로 인수 database 입니다.

주의할 점은 SUM, SUMIF, SUMIFS 등은 굳이 레이블에 해당하는 행은 범위에 지정하지 않아도 되나, DSUM 함수는 반드시 레이블행 을 범위에 포함시켜야 됩니다. 왜냐하면 그 레이블을 기준으로 조건과 합칠 범위를 판단하기 때문입니다.

꼭 DSUM만이 그런것이 아니고요. DAVERAGE, DCOUNTA 등 이렇게 database 인수를 갖는 모든 함수는 마찮가지로 레이블행을 반드시 포함하여 선택해야합니다.

 

그럼 또 필요한 것이 무엇인까요? database에서 최종 합칠 "열"과 어떤 조건으로 합치는 것이지에 대한 "조건"입니다. 여기서 "열"이 바로 field 입니다. 그리고 조건이 criteria 입니다. field는 database 영역에 레이블로 사용해도 되고 지정한 database의 열의 순서로도 사용할 수 있습니다. 

 

그럼 일단 사용해 보겠습니다.

 

 

1. 합을 입력할 D16셀 클릭해서 선택
2. 수식입력줄에서 =DSUM( 작성 => =DSUM(
3. database 인수 영역(조건에 해당하는 범위부터 합치 값의 범위까지)을 모두 선택합니다 여기서는 분류부터 출발인원까지 입니다. => =DSUM(D2:G10
4. 콤마입력후 field 인수인 "출발인원" 작성 => =DSUM(D2:G10,"출발인원"
5. 콤마입력후 criteria 인수 즉 조건에 해당하는 범위를 선택하여 작성합니다. => =DSUM(D2:G10,"출발인원",B12:B13
6. 하나의 함수임으로 괄호를 닫지 않고 enter로 마무리하여도 자동으로 닫힘괄호가 생기고 수식이 완성되며 결과값이 반환됩니다. => =DSUM(D2:G10,"출발인원",B12:B13)

 

결과 값이 잘 반환되었습니다. database 범위는 물론 전체 표를 다 선택하셔도 됩니다. 하지만 조건과 합칠 값이 범위만이 필요하기 때문에 분류에서 출발인원 까지만 선택했습니다. 이렇게 필요한 범위만 database 범위로 잡게되면 계산 처리면에서 좀더 효과적입니다. 

 

filed에 대해서도 말씀드리자면 현재는 레이블 "출발인원"으로 작성했습니다만 보통은 이렇게 사용하지 않습니다. databse 범위의 열의 순서로 사용합니다.

 

 

선택한 database 범위에 filed 순서는 그림과 같습니다. database 좌측부터 열의 순서부터 매겨져서  출발인원의 순서는 4가 되는 것이죠 그래서 위의 수식은 =DSUM(D2:G10,4,B12:B13) 로 다시 작성할 수 있습니다. 이렇게 순서로 사용하는 이유는 레이블을 작성하다고 오타가 날 수 있으면 여려줄로 작성된 레이블을 수식 표시줄에서 아래줄로 넘어가 버리기 때문에 보기가 힘들기 때문입니다. 가능한 숫자로 사용하시기 바랍니다.

 

다음 criteria(조건)에 대해 말씀드리겠습니다. criteria는 반드시 범위의 형태로 만들어야 합니다. 이 것도 DSUM함수 만이 아니라 D~ 시작하는 함수 즉 database 인수를 갖는 함수들은 대부분 동일합니다. 범위로만 만들면 됩니다. 사실 위 표에서는 굳이 B12:B13은 만들지 않아도 됩니다. 왜냐하면 D2:D3과 동일하기 때문이죠 그래서 위의 수식은 

=DSUM(D2:G10,4,D2:D3) 로 만드는 편이 훨씬 편합니다. 하지만 분류가 섬여행이 아니라 해외 여행이나 기차여행이라면 원래의 표에서 선택할 수 없기 때문에 B12:B13을 반드시 만들고 B13의 값을 해외 여행 또는 기차 여행으로 변경하여 사용하여야 합니다.

 

여기까지가 D~함수의 기초라 할 수 있겠네요...

조금더 확장해 보겠습니다. 일단 지금까지는 조건이 하나인 것만 생각했습니다. 섬여행의 출발인원 합이였죠. 이번에는 해외여행중 출발인원이 10명 초과인 것들의 여행경비 평균을 구해 보겠습니다.

 

일단 이번에는 합이 아니고 평균입니다. DAVERAGE를 사용해야겠네요.

처리할 값이 출발인원이 아니고 여행경비가 되었고요. 

첫번째 조건은 동일하게 분류에서 가지고 올 것 입니다 그런데 두번째 조건이 생겼네요

두번째 조건은 출발인원에서 가지고 와야 합니다. 이렇게 두가지 조건을 모두 만족해야 하는 경우 criteria 범위는 열을 추가하여 만들어야 합니다.

 

 

1. 평균울을 입력할 D16셀 클릭해서 선택
2. 수식입력줄에서 =DSUM( 작성 => =DSUM(
3. database 인수 영역(조건에 해당하는 범위부터 합치 값의 범위까지)을 모두 선택합니다 여기서는 분류부터 여행경비가 되겠네요. => =DSUM(D2:H10
4. 콤마입력후 field 인수룰 순서 5로 작성 => =DSUM(D2:H10,5
5. 콤마입력후 criteria 인수 즉 조건에 해당하는 범위를 선택하여 작성합니다. => =DAVERAGE(D2:H10,5,B12:C13
6. 하나의 함수임으로 괄호를 닫지 않고 enter로 마무리하여도 자동으로 닫힘괄호가 생기고 수식이 완성되며 결과값이 반환됩니다. => =DAVERAGE(D2:H10,5,B12:C13)

 

database 범위 선택에 감이 오나요? 조건에 해당하는 "분류","출발인원"과 처리할 값의 범위인 "여행경비"를 모두 포함하는 범위를 잡아야합니다. filed는 이번에는 5번째이 있으니 5가 되는 것이고요

 

결국 문제는 criteria를 어떻게 작성하는지가 됩니다. 나머지는 기본적인 방식입니다.

이렇게 여러개의 조건을 모두 만족하는 형태는 criteria 범위를 열을 추가하여 만들면 됩니다. 그럼 해외여행중 출발인원이 10명 초과이고 여행기간이 4박5일인 것들의 여행경비 평균도 criteria만 수정하여 작성하면 됩니다.

 

 

이렇게 만들고 criteria 범위만 수정해 주면 끝입니다.

 

조금더 확장해 보겠습니다. 이제 부터는 criteria를 어떻게 만든는지가 관건이 되겠습니다.

일단 모두 만족하는 경우는 알았습니다. 그렇다면 해외여행중 출발인원이 10명 초과 25명 미만인 것들의 여행경비 평균은 어떻게 만들까요? 조건으로 출발인원을 한번 사용했습니다.

당황스러운신가요? 어려울것 없습니다. 모두 만족하는 조건은 열을 추가해서 사용한다라는 것만 기억하고 있다면 말이지요. 출발인원을 한열 더 추가하면 됩니다.

 

 

그런데 해외여행이거나 섬여행인 경우의 여행경비 평균을 구해야하는 경우도 있을 수 있습니다. 이번에는 모두 만족하는 것이 아니라 하나만 만족해도 평균을 구해야 되는 것이죠. 함수로 생각하면 이전까지는 AND 였다면 이번에는 OR가 되는 것입니다.  이 경우는 열을 추가하는 방식이 아니라 행을 추가하는 형식으로 만들면 됩니다.

 

 

그럼 이번에는 criteria 범위를 보고 유추해 보겠습니다. 함수는 평균을 구하는 DAVERAGE를 사용했다고 가정하겠습니다.

 

 

위의 criteria로는 무엇을 구하는 것일까요? 해외여행 이거나 섬여행 이면서 출발인원이 10명초과 25미만인 것들의 평균일까요? 지금까지의 내용으로 보면 얼핏 맞는 예기 같습니다. 그런데 여기에는 행,열의 기준 순위에 대한 개념이 또 존재합니다. 이렇게 예상했다면 열을 기준으로 조건을 판단한 것입니다. 행을 기준으로 판단한다면 해외 여행이면서 출발인원이 10명 초과 25명 미만이거나 섬여행(전체)의 평균 으로 판단 될 수도 있기 때문이죠.

그럼 어떤 것이 맞을 까요. 답은 두번째 입니다. 행을 기준으로 판단한다는 것이죠 만약 첫번째 조건처럼 만들자면 두번째 행도 동일하게 작성해 주어야 합니다.

 

 

위 처럼 말이지요.

 

주요 내용은 모두 다룬것 같습니다. 가능한 쉽게 설명드리고자 했으나 좀 장황해 졌네요 어려운 부분이 있었다면 댓글 남겨주시기 바랍니다.

 

추가로 DCOUNTA에 대해 조금만 더 설명 드리겠습니다. COUNTA, COUNTIF, COUNTIFS 등 함수는 계산을 하는 함수가 아닙니다. 합을 구한거나 평균을 구하거나 하는 계산이 아니라 단순하게 셀을 세는 역할을 하는 함수입니다. 이 말은 계산할 filed를 굳이 정하지 않아도 된다는 얘기 입니다. 즉 섬여행의 개수라면 database를 출발인원이나 여행경비까지 확장할 이유가 없는 것이죠 그냥 분류하나만으로도 충분합니다 그 분류가 database 이면서 filed가 되는 것이죠. 물론 이전처럼 해도 됩니다. 출발인원을 세거나 여행경비를 세거나 모두 동일한 결과를 반환하기 때문이죠. 그런데 데이터를 처리하는 컴퓨터의 관점에서는 불필요한 영역까지 범위로 선택하는 것은 어째건 읽고 비교해봐야하는 의미없는 작업을 하게 됩는 것입니다.

 

 

엑셀함수 D~.xlsx

0.01MB