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


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

MS Word, Excel, PowerPoint 등을 실행하게 되면, 제일 처음 시작화면(splash screen) 이 뜨고 그 뒤에 워드나 엑셀, 파워포인트창이 나오게 된다.


성능 좋은 PC 에서야 워낙 순식간에 시작화면이 나타났다가 사라지기 때문에 큰 문제가 없을 수 있겠으나,


그렇지 않은 PC 에서는 워드, 엑셀, 파워포인트 창이 바로 뜨기를 원할 지도 모른다.


이에 시작화면 없이 바로 실행되는 방법에 대해 소개를 하려 한다.


먼저 자신의 PC 에 설치된 MS Office 의 경로를 알아야 한다.


아래는 일반적인 경로이다.


Windows X86


C:\Program Files (x86)\Microsoft Office\Office14


Windows X64


C:\Program Files\Microsoft Office\Office14



실행창(윈도우로고키 + R) 에서 바로 실행하는 방법


OFFICE

 실행 명령

 Word

winword /q

 Excel

excel /e

 Powerpoint

 powerpnt /s


이를 활용하여 바로가기로 만들어 사용할 수도 있다.



저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
Creative Commons License
office 2002,2003 문서 즉, office 2007 이전 문서들은 파일확장자가 doc, xls, ppt 로 bianary 형식을 갖고 있다.

office 2007 문서부터는 파일확장자가 docx, xlsx, pptx 로 open xml 파일형식을 갖고 있다.

즉, office 2007 이전 문서의 파일들은 ms 오피스 이외의 프로그램에서는 읽는 것이 힘들었으나, 이후의 문서들은 open xml 형식으로 작성되어 zip format 으로 되어 있어, 일반적인 압축프로그램들로 압축해제가 가능하며, 그 내용들(xml 파일들로 구성)을 확인할 수 있다.

docx, xlsx, pptx 등의 확장자로 된 office 2007 이후의 문서들을 이전버전의 Office (office xp, 2002, 2003) 등에서 읽어 들이기 위해서는 ms 에서 제공하는 호완팩을 설치하면 2007 이전의 Office 에서도 문서파일을 읽을 수 있다.

참고 사이트 : http://support.microsoft.com/kb/924074

위 참고사이트를 통해, 호완팩을 설치하기전 주의 사항을 확인하고 설치해야 한다.

호완기능팩 다운로드 : http://www.microsoft.com/downloads/details.aspx?displaylang=ko&FamilyID=941b3470-3ae9-4aee-8f43-c6bb74cd1466


또한, Office 2007 이전버전의 파일형식(doc, xls, ppt 등) 을 이후 버전의 형식(Open XML)으로 변환도 가능하다.
툴 이름은 b2xtranslator 로 현재 버전이 b2xtranslator_setup_r649.msi (Windows 용)이다.

변환프로젝트 : http://b2xtranslator.sourceforge.net/index.html
다운로드 : http://sourceforge.net/projects/b2xtranslator/files/

사용방법은 탐색기를 실행하고, 해당 Office 파일에서 마우스 우측버튼을 누른후, Conver to xlsx, docx, pptx  를 클릭하면 된다.

확인해본 결과, 엑셀은 변환이 거의 정확하게 되는데 반해, 워드는 개체의 경우 조금 깨지긴 하지만, 그외에는 거의 정확하게 변환이 되는 것을 확인해 보았다.


Open XML 관련 참고 :
http://kingcrap.com/category/Open%20XML
http://office.microsoft.com/en-us/word-help/introducing-the-office-open-xml-formats-RZ010243529.aspx?section=2

저작자 표시 비영리 동일 조건 변경 허락
신고
크리에이티브 커먼즈 라이선스
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

티스토리 툴바