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


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() 함수를 이용하면 쉽게 통계데이터를 구할 수 있다.




저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
보통 엑셀에서 IP Address 정보를 불러와서 작업해야될 경우가 있다. 해본 사람들은 알겠지만, 엑셀에서 IP Address 를 정렬하면, 원하던 결과가 안나온다.
즉, 예를 들어 아래의 데이터를 정렬 하면, 10.1.1.1 다음으로 10.1.1.2 가 아닌 10.1.1.10 이 온다. 

10.1.1.1

10.1.1.10

10.1.1.11

10.1.1.12

10.1.1.2

10.1.1.3

10.1.1.4

10.1.1.5

10.10.10.10

10.10.10.11

10.10.10.18

10.10.10.19

10.10.10.20

10.10.10.21

10.10.10.22

10.100.100.1

10.100.100.2

10.100.100.3

10.100.100.4

10.100.100.5

10.100.100.6

100.100.100.100

100.100.100.101

100.100.100.102

100.100.100.103

100.100.100.104



사실 이게 보통 짜증나는 일이 아닐 수 없다.


이제부터 이걸 제대로 정렬을 할 방법을 알아 보려 한다.
방법은 간단하다, 맨 마지막 자리의 데이터를 다른 열로 옮긴후, 해당열을 기준으로 정렬을 하면 끝난다. ^^

자 그럼 맨 마지막 자리의 데이터(즉 10.1.1.x, 10.1.1.xx, 10.1.1.xxx 에서의 x, xx, xxx 값들)를 어떻해 다른 열로 옮길 것인가가 문제이다.

이를 위한 방법은 문자열 분리에 있다.
문자열 분리를 위해 필요한 함수는 left(), mid(), right(), find() 이 네가지만 있으면 된다.
이 함수들의 사용법에 대해서는 별도 설명하지 않겠다.(내용이 너무 길어져서 ....)

자 지금부터 하려고 하는 아래의 결과값을 먼저 보자

즉, A열의 원래값에서 맨마지막 값만 따로 분리를 해서 B열과, C 열 로 나눈 것이다.

A

B

C

10.1.1.1

10.1.1.

1

10.1.1.10

10.1.1.

10

10.1.1.11

10.1.1.

11

10.1.1.12

10.1.1.

12

10.1.1.2

10.1.1.

2

10.1.1.3

10.1.1.

3

10.1.1.4

10.1.1.

4

10.1.1.5

10.1.1.

5

10.10.10.10

10.10.10.

10

10.10.10.11

10.10.10.

11

10.10.10.18

10.10.10.

18

10.10.10.19

10.10.10.

19

10.10.10.20

10.10.10.

20

10.10.10.21

10.10.10.

21

10.10.10.22

10.10.10.

22

10.100.100.1

10.100.100.

1

10.100.100.2

10.100.100.

2

10.100.100.3

10.100.100.

3

10.100.100.4

10.100.100.

4

10.100.100.5

10.100.100.

5

10.100.100.6

10.100.100.

6

100.100.100.100

100.100.100.

100

100.100.100.101

100.100.100.

101

100.100.100.102

100.100.100.

102

100.100.100.103

100.100.100.

103

100.100.100.104

100.100.100.

104



휴, 다시 보니 끔찍하기만 하다. ^^ 저걸 하기 위한 여러가지 방법들이 있겠으나, vbscript 를 짜서 할수도 있을것이고....
하지만, 그냥 함수만을 사용해서 해보려는 것이다.
물론, left((), mid(), right() 함수를 쭉쭉 써서 어떻해 어떻해 하면 나오긴 하겠지만, 생각보다 함수가 엄청 길어진다.....

좀더 쉽게 생각하는 방법을 알아보자
분리해야될 포인트는 점(.) 이다. 이 점(.)은 총 세개가 있다. 각각의 자리위치값(자릿수)를 구하면 좀더 쉽게 구할 수 있다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 첫번째 점(.)의 자리위치값 을 구해보자.
=Find(".",A1)                # a1 셀값(10.1.1.1) 에서 왼쪽부터 점(.)의 위치를 찾아라

첫번째 점(.)의 위치값은 3일 것이다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 두번째 점(.)의 자리위치값을 구해보자
=FIND(".",A1,FIND(".",A1)+1)                   # 첫번째 점(.)의 위치 다음부터 (find(".",a1)+1) 점(.)의 위치를 찾아라

두번째 점(.)의 위치값은 5 일 것이다.

A열 1행의 값(10.1.1.1)을 기준으로 하고, 세번째 점(.)의 자리위치값을 구해보자
=FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1)              # 두번째 점(.)의 위치 다음 
                                                                                       (FIND(".",A1,FIND(".",A1)+1)+1) 점
                                                                                                   (.)의 위치를 찾아라

세번째 점(.)의 위치값은 7 일 것이다.


여기서 정말 필요한 값은 세번째 점의 위치값(=7) 이다.

그럼 이제 B열의 값을 구해보자. 기준은 A1 이다
=LEFT(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))          # A1 셀값에서 왼쪽부터 7번째 자리까지의
       문자열

이 값은 10.1.1. 이 될 것이다.

C열의 값을 구해보자. 기준은 A1 이다.
=Right(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1)+1)+1))          # A1 셀값 우측에서부터 1번째 자리까지의
                    문자열

여기서의 핵심은 마지막 점(.) 다음부터 새로운 열(C열)로 표기하는 것이다. 즉, 전체 문자열길이에서 마지막 세번째 점(.)까지의 문자열의 길이를 뺀 나머지 만큼만 전체 문자열을 기준으로 우측에서부터 구하면 되는 것이다.
즉 이 값은 1 이 될 것이다.



자 분리는 끝났다. 이제 해야 될 일은 C열을 기준으로 정렬만 하면 된다. ^^

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
  1. 미나어 2010.10.27 11:33 신고

    ㄳㄳ그런데 중간에 A4가아니라 A1아닐까요?

  2. Favicon of http://bwhite.tistory.com BlogIcon V.E.N 2010.10.29 09:25 신고

    아 ...오타가 있었군요...^^ 틀린점을 알려주셔서 감사합니다. 바로 수정해야 겠네요

 ROUNDDOWN(H15,5-LEN(INT(H15)))
 
 H15 는 숫자(1234567)
 5 는 유효한 자리수(1234567 중 앞 12345 가 유효한 자리수)
 
 즉 1234567 이라는 숫자를 1234500 으로 표현하고자 할 때 위 구문을 사용하면 됨
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 columns(범위)
 예) columns(a1:j1)
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 rows(범위)
 예) rows(a1:a10)
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 =if(셀지정="찾고자하는값","ok","not ok")
 
 를 하고 찾고자하는 영역만큼 블록을 지정한 다음, 편집-채우기에서 채우기를 한다.
 자동필터를 선택하고, "ok" 가 포함된 셀로 정렬하도록 한다음 삭제한다..
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
 찾고자 하는게 문자일 경우, 빈 셀에 =istext(찾고자하는 셀지정)
 찾고자 하는게 빈셀일 경우, 빈 셀에 =isblank(찾고자하는 셀지정)
 숫자일 경우, =isnumber(찾고자하는 셀 지정)
신고
크리에이티브 커먼즈 라이선스
Creative Commons License

+ Recent posts

티스토리 툴바