이전 강좌에 이어 ~IF 함수 criteria(조건) 인수에 부등호와 등호를 사용하는 방법을 설명하겠습니다. SUMIF, AVERAGEIF, COUNTIF 함수의 사용법을 모르신다면 이전 강좌를 보신 후 돌아와 주세요. 모두 아는 함수라면 넘어가셔도 됩니다.
그리고 추가로 알고 있어야 하는 사항이 좀 있습니다. 일단 수식에서 숫자, 문자, 참조의 연결을 & 기호로 사용한다는 것을 알고 있어야 합니다. 잘 모르신다면 아래 강좌 확인 후 돌아와 주세요.
다음, 고급 필터 사용 시 조건 범위에 부등호 등호를 사용하는 방법도 아시는 편이 좋습니다. 이것도 모르신다면 아래 강좌를 확인해 주시기 바랍니다.
3가지 내용을 모두 알아보셨을 것으로 믿고 진행해 보겠습니다. 물론 알고 있는 얘기라면 아래 내용으로 바로 들어가셔도 됩니다. 예제 파일을 열어 주세요.
예제 파일에서 알고 싶은 것은 최저 금액(1,000,000) 이상의 개수, 금액 합, 금액 평균입니다.
먼저 개수 COUNTIF 함수부터 알아보겠습니다.
이전까지는 ~IF 함수의 range 인수 범위에서 criteria와 같은 값만 찾았으면 됐습니다. 하지만 지금은 같은 값을 찾는 것이 아니라 1,000,000 이상을 찾는 것이니 ">="를 사용해야 합니다. criteria 인수가 ">=1000000" 돼야 한다는 얘기겠죠.
- COUNTIF 함수를 작성할 셀(I7)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =COUNTIF(
- 첫 번째 인수 힌트(range)를 확인하고 금액의 모든 데이터 범위를 참조합니다. => =COUNTIF(F5:F18
- 쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인, 이상의 값을 작성해야 하니 부등호와 등호를 사용하여 입력합니다. => =COUNTIF(F5:F18,>=1000000
- 하나의 함수이므로 최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =COUNTIF(F5:F18,>=1000000)
별반 다른 게 없어 보이지만 결과는 오류가 납니다. 이상하죠.
수식 중 인수 criteria를 큰따옴표로 감싸보겠습니다.
criteria 인수를 큰따옴표로 감싸고 적용하니 문제없이 금액이 1,000,000 이상인 셀의 개수가 반환됩니다.
단지 큰따옴표로 감쌌냐 감싸지 않았냐의 차이로 결과가 나오거나 오류가 난다는 것을 알 수 있습니다. 즉, 이 얘기는 criteria에 부등호, 등호를 사용해야 한다면 문자로 인식시켜야 한다는 얘기가 됩니다.
고급 필터로 1,000,000 이상의 금액을 필터링한다고 생각해보면 왜 여기서 문자로 인식시켜야 하는지를 비교해 볼 수 있습니다.
고급 필터라면 조건 범위를 만들어야겠죠.
위 그림처럼 조건 범위를 만들어 줘야 합니다. 이때 조건 셀 H12는 >,= 기호를 사용했으니 당연히 문자입니다. 정렬 역시 좌측 정렬되어 있죠.
~IF 함수의 criteria 함수도 동일하게 생각하시면 됩니다. 부등호, 등호 같은 기호가 들어가야 한다면 문자로 처리해 줘야 한다는 것입니다. 기호가 들어가는 경우는 또 있습니다. 와일드 카드(*, ?, ~)도 기호죠. 동일합니다. 큰따옴표로 감싸 줘야 합니다.
그런데 지금처럼 최저 금액을 I4셀에서 참조할 수 있다면 굳이 1,000,000 값을 입력할 필요 없습니다. 참조하면 되죠.
그렇다면 기호(부등호, 등호 등)와 함께 I4셀을 참조하려면 어떻게 할까요? ">=I4"로 하면 될까요? 당연히 안 됩니다. 여기서 I4는 큰따옴표 안에 있으니 참조가 아니라 그냥 문자 I4입니다. 1,000,000 이상을 찾는 것이 아니라 I4 이상을 찾으라는 명령입니다. I4는 그냥 문자이니 I4 이상이란 것은 없습니다.
그럼, ">="I4는 될까요? 역시 되지 않습니다. 문자와 참조를 그냥 붙여서 사용할 수 없습니다.
즉 문자와 참조를 연결해 줘야 한다는 얘기입니다. & 기호를 사용하란 것이죠. 그래서 부등호, 등호를 사용하고 참조를 해야 한다면 결론은 ">="&I4가 돼야 한다는 얘깁니다.
- I7 셀을 클릭 선택하고 수식의 criteria에서 1000000을 지웁니다. => =COUNTIF(F5:F18,">=")
- 큰따옴표 밖에서 연결 기호 &를 입력합니다. => =COUNTIF(F5:F18,">="&)
- 연결할 셀(I4)을 클릭하여 참조합니다. => =COUNTIF(F5:F18,">="&I4)
최종, 엔터를 누르면 에러 없이 동일한 결과를 확인할 수 있습니다. 왠지
>=&I4도 될 것 같지만 되지 않습니다. 반드시 기호는 큰따옴표로 감싸서
">="&I4 로 만들어야만 에러가 발생하지 않습니다. 고급 필터처럼 문자로 만들어야 하기 때문이죠.
다음, 금액 합입니다.
- SUMIF 함수를 작성할 셀(I8)을 클릭하여 선택합니다.
- 수식 입력줄에서 수식을 시작하고 함수명을 작성합니다. => =SUMIF(
- 첫 번째 인수 힌트(range)를 확인하고 금액의 모든 데이터 범위를 참조합니다. => =SUMIF(F5:F18
- 쉼표로 구분, 두 번째 인수 힌트(criteria)를 확인하고 COUNTIF의 criteria와 동일하게 작성합니다. => =SUMIF(F5:F18,">="&I4
- 다음 sum_range를 작성할 차례입니다. 그런데 sum_range의 범위도 금액 범위입니다. range 범위와 동일하죠. 이 경우는 sum_range는 작성하지 않아도 됩니다.
물론 작성해도 됩니다. 하지만 일부러 더 작업할 이유는 없습니다. 합칠 범위 sum_range와 조건 범위 range가 같다면 생략하셔도 됩니다.
최종 엔터(닫힌 괄호 자동 생성 후 셀 완성) => =SUMIF(F5:F18,">="&I4)
결과, 1,000,000 이상의 금액 합이 반환됩니다. 금액 평균은 함수명만 다르니 직접 해 보시기 바랍니다. 같은 이유로 average_range 인수는 작성할 필요 없습니다.
금액 평균까지 결과는 위와 같습니다.
위처럼 criteria의 값을 참조하면 또 좋은 점이 생깁니다. 최저 금액인 I4셀 값을 변경하면 자동 변경된다는 점입니다. I4셀 값을 1,500,000으로 변경해 보세요.
결과, 바뀐 값 1,500,000 이상의 개수, 금액 합, 금액 평균이 자동 계산되어 나옵니다. 물론 다른 값으로 변경해도 원하는 결과로 자동화됩니다.
의견글 입니다.
()
