엑셀 특정 문자가 포함된 셀들 우측의 셀값의 합을 구하고자 할때

월간 보안관제 동향과 같은 통계데이터를 생산할 경우, 탐지된 공격들을 카테고리화 해서 통계를 내고자 할 경우, 엑셀에서 작업방법

TMS, IPS 등에서 탐지된 공격들을 웹공격, 네트워크 공격, 악성코드, 등등으로 분류하고 이들의 통계를 내고자 한다.

아래와 같은 Lawdata 를 가정해 보자

Attack 명이 Malwr- 로 시작하는 것은 악성코드로

Web- 으로 시작하는 것은 웹공격

DDoS- 로 시작하는 것은 네트워크 공격

Attack-Scan 으로 시작하는 것은 웹공격

으로 분류하고 그 카테고리의 총합들을 구하고자 한다.

즉, 엑셀에서는 특정 조건값에 부합하는 셀들의 합을 구하는 것이 될것이다.

비슷하게 이전에 한개 이상의 조건이 일치하는 셀들의 합을 구하는 함수는

=sum(($A1:$A10=일치할값)*($B1:$B10=두번째일치할값)*......*($G1:$G10)) 입력후, CTRL+SHIFT+ENTER

$A1:$A10 는 첫번째로 일치할 값을 찾을 범위

$B1:$B10 는 두번째로 일치할 값을 찾을 범위

$G1:$G10 는 합을 구할 범위

이 방법을 이용해 보려 했으나, 완전히 일치하는 값을 찾는 것이 아닌 특정 문자열이 포함된 셀들의 합을 구하는 것이다 보니 불가능해 보였다.

이때 가능한 함수가 바로, sumif() 다. sumifs() 도 있는데 이 함수는 뒤에서 추가 설명

range 는 값을 찾을 셀의 범위

criteria 는 찾을 값

sum_range 는 합을 구할 셀의 범위

우선, Web- 으로 시작하는 셀들의 합을 구해보자. 조건값으로 * 을 사용한 이유는 문자열 Web- 을 포함한 셀을 찾아야 하기 때문이다.

=SUMIF($A$2:$A$17,"Web-*",$B$2:$B$17)

위처럼 악성코드, 네트워크 공격 들에 대해 각각 셀들의 합을 구할 수 있다

sumif()와 sumifs() 함수차이는

sumif() 는 단일 조건에 대해 만족하는 셀들의 합을 구하는 함수

sumifs() 는 하나 이상의 조건을 만족하는 셀들의 합을 구하는 함수(두 개가 아닌 하나 이상이다)

사용법에서 약간 차이가 있다.

sum_range 는 합을 구할 셀의 범위 로 sumif() 함수에서는 맨 뒤에 있었는데, sumifs() 함수에서는 가장 먼저 위치한다.

criteria_range1 는 첫번째 조건값을 찾을 셀의 범위

criteria1 은 찾을 첫번째 조건값

criteria_range2 는 두번째 조건값을 찾을 셀의 범위

criteria2 은 찾을 두번째 조건값

조건값을 하나만 사용할 경우, sumif() 함수와 똑같다. 사용법만 틀릴뿐

단, sumifs() 함수에서 두개 이상의 조건값들의 논리연산은 OR 가 아니라 AND 이다

즉, 위 LawData 에서 웹공격으로 분류될 조건값이 Web-* 과 Attack-Scan* 두가지에 대해 그 합을 구하고자 할경우,

sumifs() 함수를 쓰면 공격명이 Web-* 으로 시작하는 셀이면서 Attack-Scan* 으로 시작하는 셀의 합을 구하고자 하기 때문에 논리적으로 맞지가 않는다.

그래서 위에서 Web-* 으로 시작하는 조건과 Attack-Scan* 으로 시작하는 조건에 대한 합을 각각 구한 후, 다시 그 합을 구한 것이다.

그럼 sumifs() 함수는 언제 사용하는 것인가?

위 데이터에서 1월달에 사과를 사 먹은 총 금액을 구하고자 할 경우,

첫번째 조건은 1월

두번째 조건은 사과

구하고자 하는 값을 그 가격

sumif(), sumifs() 함수를 이용하면 쉽게 통계데이터를 구할 수 있다.