(엑셀) INDEX 함수, MATCH 함수, COUNTIF함수를 이용하여 중복데이터 제거하기

목 차
1. 들어가기
2. 사전준비
  1) INDEX 및 MATCH 함수는 아래와 같이 설명한다.
  2) INDEX, MATCH 조합 함수
  3) COUNTIF 함수
3. INDEX, MATCH, COUNTIF 함수 조합
  1) 함수 조합
  2) 에러메시지 출력 방지를 위해 IFERROR을 붙인다.
4. 마치며

 

1. 들어가기

주식 1분봉을 패턴화시키면, 중복된 데이터가 생긴다.

09:00~15:19분까지 380개의 1분봉이 생기는데,

이러한 1분봉은 하루 30개 정도로 이루어진다.

 

패턴의 중복이 많은 380개 봉을 30여개 봉으로 줄이는 작업을 위해서는

중복값 없애는 함수를 알 필요가 있다.

 

 

(파이썬) 주식 자동 매매를 위한 알고리즘 패턴 찾기 (1) 엑셀 이용

---

2. 사전준비

1) INDEX 및 MATCH 함수는 아래와 같이 설명한다.

  ① INDEX함수는 특정범위에서 행과 열을 이용하여 값을 찾는다.

  ② MATCH함수는 특정값과 일치하는 행번호를 반환한다.

 

2) INDEX, MATCH 조합 함수

• 전체범위에서 특정값1의 위치를 찾아서, 그 줄에 해당하는 특정값2를 찾는다
• 함수 형태 : INDEX(범위, MATCH(특정값, 범위, 0), 찾고자하는 열)

 

3) COUNTIF 함수

• 범위 내에서 특정값의 갯수를 카운팅한다.
• 함수 형태 : COUNTIF(범위, 특정값)

그림1. aa 갯수는 3개이다.

 

---

3. INDEX, MATCH, COUNTIF 함수 조합

1) 함수 조합

• 중복값 제거를 위해 함수를 조합해보자.
• 함수 형태 : INDEX(찾는범위, MATCH(0, COUNTIF(머릿글 범위, 찾는범위),0))

① COUNTIF 함수부터 설명한다.

    D3열에 셀을 두고나서, 머릿글 범위에 주의한다.

    머릿글($D$2:$D2)에 있는 "구분"과 찾는범위($B$3:$B$15)에서 겹치는 게 없으므로 0이 반환된다.

    즉, MATCH(0,(0,0,0,0,0.....), 0)의 형태가 되므로, B3열의 1번째인 aa가 반환된다.

    배열함수이다. CTRL + SHIFT + ENTER을 눌러서 실행하자.

 

② 자동채우기를 통해 D4~D7까지 채운다.

     ※ D7의 0은 B13~B15 빈칸을 범위에 넣었으므로, 0이 반환되는데,

         빈칸(" ")을 변환한 숫자 0이므로 어쩔 수 없이 출력된다.

 

그림2. D열(녹색)에 중복제거된 데이터이다.

 

2) 에러메시지 출력 방지를 위해 <그림2>에서 IFERROR을 붙인다.

• 함수 형태 : IFERROR(A, " ") → 에러가 아니면 A가 출력되고, 에러이면 " "(빈칸)이 출력된다.

---

4. 마치며

중복값의 제거는 엑셀에서 중복값 없애기 기능을 이용할 수 있다.

(경로) 데이터 탭 > 중복된 항목제거

 

다만, 복잡하면서도 어려운 중복값 없애기가 필요한 이유는,

로데이터를 입력했을 때,

자동으로 중복값을 없애고 그 갯수를 세어보기 위해서이다.

갯수를 세어본 후, 多빈도 패턴을 알아보는 첫걸음이 될 것이다.

 

위 함수는 엑셀 2016 이전의 버전에서 활용된다.

2017 이후의 엑셀버전이면 UNIQUE 함수를 사용하자... 쉽게 실행된다. @.@

UNIQUE 함수를 적극 활용하자.