이번 서울시 기능경기대회 정보기술 직종의 추가문제 중 필터와 VBA 없이 검색기능을 구현하는 문제가 나왔다. 결국 함수와 다른 도구들을 이용하라는 문제인 것이다. 이 문제에 대해서 알아보려고 한다.

참고) 필터는 표의 원본에서 바로 검색하는 기능이고, 함수 검색 기능은 별도의 시트에서 함수를 사용해서 검색기능을 구현한다. 정확히 같다곤 볼 수 없다. 상황에 따라 적절히 활용할 수 있을 것이다.

문제

  • 예약정보 시트에 원본이 있다.
  • 결과는 종합검색 시트에 나타낸다. (원본과 같은 항목이 나타난다.)
  • 원본이 변경되면 종합검색 시트도 갱신되어야 한다. (데이터 변경/추가/삭제 모두 VBA 없이)
  • 체크박스에 체크한 항목에 대해서만 검색한다.
  • 상위 30개만 나타난다. (함수의 한계.. 미리 함수를 너무 많이 걸면 느려진다.)

체크박스에 체크한 항목에 대해서 검색된 결과가 아래 시트에 나타난 화면

풀이

기능대회를 치렀던 학생이라면 스스로 충분한 고민을 한 뒤 읽어보길 바란다.

문제가 어려울 때 쓰는 유명한 방법론이 있다. Divide & Conquer! 분할정복! 하나의 문제를 쉬운 문제들로 쪼개보자. (문제를 푸는 생각의 흐름 순서대로 적어본다)

  • 체크박스 만들고, 함수와 연결고리 만들기
  • 행 번호가 잘 나온다고 가정하고 표 내용 나타내기
  • 검색된 항목에 대해서 알맞은 행 번호 나타내기
  • 원본의 변경에 따른 참조범위 변경
  • 값이 없는 셀엔 값 안보이게 적용 (테두리도 안보이게)

이 문제의 핵심은 '검색된 항목에 대해서 알맞은 행 번호 나타내기' 이다. 나머지 문제들은 상대적으로 쉽다.

체크박스 만들고, 함수와 연결고리 만들기

개발도구에서 양식 컨트롤을 이용해서 구현할 수 있다. 주의할 점은 ActiveX 컨트롤은 아니다. 개인적으로 ActiveX 컨트롤은 좋아하지 않는다. 모양이 예쁘지 않고, 항상 VBA를 사용해야 하기 때문에 함수와의 궁합도 좋지 않다.

양식컨트롤 위치

체크박스를 만들었으니 이제 함수와 연결시킬 수단을 만들자. 방법은 간단하다. 컨트롤 서식 중 셀 연결을 활용하면 된다. 연결될 셀엔 True/False로 표시된다. 이제 연결된 셀을 참조하면 체크박스의 체크 유/무를 알 수 있다. 나는 체크박스가 위치할 셀에 연결해놨다. 셀서식 ;;; 은 기본 센스. (;;;는 밑에서 다룬다.)

양식컨트롤 셀연결

행 번호가 잘 나올 때 예약일자 ~ 발권일자 나타내기

이것 쉬운 문제다. 이미 행 번호가 나와있기 때문에 VLOOKUP함수도 쓸 필요 없다. 그냥 INDEX함수만으로 해결할 수 있다. 테스트 할 땐 행번호 열에 적당한 값을 써놓고 하면 된다. 별로 어려울 것이 없다.

C15 =INDEX(예약정보!$A$1:$M$100,$B15,COLUMN()-2)

검색된 항목에 대해서 알맞은 행 번호 나타내기

전체 문제의 핵심 문제이다. 문제엔 조건이 9개나 있고, 범위 지정 조건도 있다. 복잡하니까 쉽게 줄여서 구현해보자. 프로그래밍도 마찬가지지만, 특히 함수에선 이런 식으로 쉽게 쉽게 접근해야 한다. 먼저 열차종류 검색기능만 구현해보자.

조건에 맞는 첫 번째 행 번호, 두 번째 행 번호를 구해야 한다. 조건에 맞는 어떤 값을 구할 때 쉽게 쓰는 것은 배열함수이고, N번째 값을 구하는 건 SMALL, LARGE함수이다. 이 문제에선 SMALL 함수가 쓰인다. 이 둘을 적절히 조합하면 된다. 참 쉽죠?

여기서 적당히 넘어가려고 했는데, 안 쉬울 독자를 위해서 조금 자세히 적자면.. 보통 배열함수라고 하면 =SUM(IF( )) 형태를 많이 떠올린다. 하지만 Range를 넣을 수 있는 대부분의 함수에 배열함수를 적용할 수 있다. =SMALL(IF(), 3) 이라고 하면 조건에 맞는 값 중 3번째로 작은 값을 출력하게 된다. 열차종류에 따른 행 번호 검색은 다음과 같다.

B15 (행번호)
=SMALL(
    IF(예약정보!$B$2:$B$100=$C$5
        ,ROW(예약정보!$B$2:$B$100))
    ,ROW()-ROW($B$14)
)

그런데 우리의 조건 중 하나는 체크박스를 체크한 항목에 대해서만 검색되어야 한다. 이 조건까지 넣어서 함수를 만들어야 완벽해진다. CHECK=FALSE 일 경우 항상 TRUE, CHECK=TRUE 일 경우 조건에 맞으면 TRUE. 이런 경우는 OR로 처리 가능하다. 한번에 이해가 가지 않는다면 Karnaugh Map을 그려보면 조금 도움이 될 수도 있겠다. 조금 더 설명을 하면 체크박스가 선택되어 있지 않은 경우(CHECK=FALSE)라면 해당 조건은 행 번호를 구할 때 영향을 주면 안 되는 조건이 되어야 한다. 그래서 항상 TRUE 여야 한다는 것이다. 이 조건을 적용하면 다음과 같다.

열차조건에 대한 카르노맵

B15 (행번호)
=SMALL(
    IF(($B$5=FALSE)+(예약정보!$B$2:$B$100=$C$5)
        ,ROW(예약정보!$B$2:$B$100))
    ,ROW()-ROW($B$14)
)

원본의 변경에 따른 참조범위 변경

이게 뭐 대단한가 싶겠지만, 난 할말이 많다. 가끔 보면 A2:A:1048576 이렇게 범위 지정할 수도 있다. 물론 최적화가 잘 되어서 100만 행 모두를 검색하진 않겠지만, 이론적으론 좋지 않다고 생각한다. 난 정확하게 딱 맞는 영역만 지정하고 싶다. 아래 함수에서 수정해야 하는 곳은 딱 한 곳이다.

=SMALL(
    IF(($B$5=FALSE)+(예약정보!$B$2:$B$100=$C$5)
        ,ROW(예약정보!$B$2:$B$100))
    ,ROW()-ROW($B$14)
)
변경할 내용: 예약정보!$B$2:$B$100

B2:B100 을 정확하게 지정하는 방법은 OFFSET 함수를 쓰면 된다.

OFFSET(ref, rows, cols, [height], [width])

여기서 ref 를 B1 으로 설정하고 rows = 1, cols = 0, height = 99, width = 1 으로 세팅하면 된다. height 빼고는 모두 상수다. height 값도 COUNTA 함수를 사용해서 간단히 구현할 수 있다.

height =COUNTA($B:$B) - 1

이를 모두 반영하면 다음과 같다. 이것을 이름관리자에 등록하면 된다.

열차종류(이름관리자)
=OFFSET(예약정보!$B$1 // ref
        ,1 // rows
        ,0 // cols
        ,COUNTA(예약정보!$B:$B)-1 // height
) // width 생략

B15 (행번호)
=SMALL(
    IF(($B$5=FALSE)+(열차종류=$C$5)
       ,ROW(열차종류))
    ,ROW()-ROW($B$14)
)

값이 없는 셀엔 값 안보이게 적용

쉬운 것부터 해결하자. 테두리부터 칠하면 되는데 이 정도는 [엑셀계몽운동] 조건부 서식(고급) 을 참조하면 해결할 수 있을 것이다. 행번호 셀을 참조해서 테두리를 설정하면 된다.

값이 안보이게 하는 방법은 두 가지가 있다. (색깔 바꾸는 거 빼고.. -_-;;) 하나는 셀서식에서 숨기는 방법이 있고, 다른 방법은 빈 텍스트를 넣는 것이다. 나는 행 번호는 셀서식으로 숨기고, 나머지 필드들은 빈 텍스트를 넣어서 해결했다.

잠깐 셀 서식을 얘기해보자. 셀 서식의 사용자 지정의 형식은 ; 로 구분되어 4가지로 나뉜다. 양수;음수;0;텍스트 양수일 때만 표시하고 음수, 0, 텍스트일 때는 숨기면 되는 아주 간단한 방법이다. 물론 IFERROR함수를 사용해서 값이 없을 때 에러가 나지 않고 0 으로 나타나게 해줘야 한다. 셀서식 ;;; 는 자주 쓰는 표현이므로 잘 알아두면 좋다.

B15 (행번호)
=IFERROR(SMALL(
    IF(($B$5=FALSE)+(열차종류=$C$5)
       ,ROW(열차종류))
    ,ROW()-ROW($B$14)
), 0)

C15(예약일자)
=IF($B15=0
    ,"" // 행 번호가 0일경우 빈 텍스트
    ,INDEX(예약정보데이터,$B15-1,COLUMN()-2))

값이 있는 셀에만 테두리를 칠해야 하난데 이 정도는 [엑셀계몽운동] 조건부 서식(고급) 을 참조하면 해결할 수 있을 것이다.

갑작스런 마무리

SUBSTITUTE 함수의 응용과 함께 나의 풀이를 공개한다.

행번호 전체 함수

만약 본인의 답안에 대한 피드백을 받고 싶은 학생이 있다면 hellojkwjkw@gmail.com 으로 소속, 이름을 적어서 보내주세요. 시간 나는 대로 답변 드리겠습니다.