[의뢰]DB 검색

 

오늘은 DB검색 프로그램을 만들어 보려고한다.

 

실제로 의뢰하여 납품한 녀석이다.

 

이것을 위한 준비사항이 가장 급선무가 있다.

난 배포문제 때문에 365를 쓰지 않을거야....라는 낡은 생각.....

생각해보자. 이글을 읽는 본인이 과연 1년동안 몇번의 의뢰를 받고 몇번을 납품했는지..

그런거 아님..그냥 365 세계에 바로 들어오길 바란다.

 

뭔가 대단해 보이고 복잡해 보이는 저 프로그램은 실제로는

=CHOOSECOLS(FILTER(내역DB!A5:T95,
    ISNUMBER(SEARCH(C7,내역DB!T5:T95))*
    (내역DB!H5:H95 >= OUTPUT!I3)*(내역DB!J5:J95 >= OUTPUT!J3)*
    (내역DB!L5:L95 >= OUTPUT!K3)*(내역DB!N5:N95 >= OUTPUT!L3)*
    (IF(D3="",1,내역DB!C5:C95 = OUTPUT!D3)),"없음")
    ,1,2,3,4,5,6,7,8,10,12,14,16,18,19)

6줄이 전부이다.

 

자 그럼 코드 리뷰를 시작해보도록 하겠다.

크게는 365 Filter 함수가 시작이고 그 끝이다.

=FILTER(배열(영역), 조건, 못찾을때 메세지)

 

이것을 보고 처음에 올려준 코드 6줄을 다시 보자

 

=CHOOSECOLS(
 FILTER(영역,ISNUMBER(SEARCH(조건))*(조건1)*(조건2)*(조건3)*조건(4),못찾았다 메세지),
 1,2,3,4,5,6,7,8,10,12,14,16,18,19)

 

 

CHOOSECOLS 함수는 결과값에서 몇번째 컬럼을 가지고 오겠다는 뜻이다. 

CHOOSECOLS(영역, 1,3,4) 의 뜻은 영역에서 1,3,4 번의 열을 가져오겠다는 뜻이다.

 

이렇게 컬럼을 따로 했던 이유는 의뢰인이

내역 DB시트에서 단가, 금액중 단가 부분만 가져오길 바랬기 때문에 이렇게 했고, 이런 특수한 사항이 아니면 그냥 FILTER함수 하나만 사용하면 된다.

FILTER(내역DB!A5:T95,
  ISNUMBER(SEARCH(C7,내역DB!T5:T95))         '= 필터의 부분일치
  *(내역DB!H5:H95 >= OUTPUT!I3)			   '= 재료비 단가보다 큰 금액 	
  *(내역DB!J5:J95 >= OUTPUT!J3)			   '= 노무비 단가보다 큰 금액
  *(내역DB!L5:L95 >= OUTPUT!K3)              '= 경비 단가보다 큰 금액
  *(내역DB!N5:N95 >= OUTPUT!L3)              '= 합계 단가보다 큰 금액  
  *(IF(D3="",1,내역DB!C5:C95 = OUTPUT!D3)),  '= 공종이 비어 있음 1 
  "없음")

 

 FILTER함수가 검색하는 내역DB!T5:T95를 보자

찾으려는 열의 시작과 끝을 CONCAT함수로 묶어준다.

=CONCAT(A5:S5)

 

이렇게 묶인것들이 T열에 조합되게 된다.

그래서 FILTER함수에서는 조건열을 전체 열을 검색하는것이 아니라 조합된 T열 하나만 검색하면 된다.

 

다시 정리하면

FILTER함수로 DB시트에서 출력하고 싶은 전체 영역을 잡는다.

FILTER함수로 조건을 검색할 영역을 각 행의 끝에 만들어 놓고 그 열만 검색한다.

 

자 그럼 FILTER 함수의 조건절에 왜 ISNUMBER + SEARCH 함수의 조합이 있을까.....

 

우리가 조합한 T 열에서 검색어를 검색하면 그것이 있는지 SEARCH함수는 몇번째에 있는지 숫자를 반환한다.

이를 ISNUMBER함수로 숫자가 맞는지 아닌지를 보게되면 조합문자열에 그 검색어가 있는지를 확인할수 있다.

 

엑사남HAJA뽀리너준빠....이런식으로 조합도니 문자열이 있다면 

 

SEARCH("엑사남HAJA뽀리너준빠","HAJA")를 검색하면 4를 반환하게 되는 원리이다.

 

따라서 부분검색은 AND * / OR + 로 표기되기에

 

각 부분검색들은 * 로 이어붙히면 된다.

 

예를 들어 엑사남 * 준빠 이런식이면 엑사남과 준빠가 포함된 열이 검색되는 원리이다.

 

(IF(D3="",1,내역DB!C5:C95 = OUTPUT!D3))

 

마지막이다. 이 구문은 부분 검색이 아니라 정확한 일치이다.

 

0은 FALSE이고 1은 TRUE이다. 앞에서 아무리 TRUE 값을 반환해도 뒤 값이 0이면 0이듯, 혹시라도 정확하게 일치해야 하는 값이 없어서 부분검색으로 일치하는 값이 거짓으로 나오는 것을 막기 위해서 혹시라도 값이 없으면 1을 반환하고 값이 있으면 일치 여부만 판단하라는 뜻이다.

 

또 정리를 해보자.

 

FILTER(내역DB!A5:T95,
  ISNUMBER(SEARCH(C7,내역DB!T5:T95))         '= 필터의 부분일치
  *(내역DB!H5:H95 >= OUTPUT!I3)			   '= 재료비 단가보다 큰 금액 	
  *(내역DB!J5:J95 >= OUTPUT!J3)			   '= 노무비 단가보다 큰 금액
  *(내역DB!L5:L95 >= OUTPUT!K3)              '= 경비 단가보다 큰 금액
  *(내역DB!N5:N95 >= OUTPUT!L3)              '= 합계 단가보다 큰 금액  
  *(IF(D3="",1,내역DB!C5:C95 = OUTPUT!D3)),  '= 공종이 비어 있음 1 
  "없음")

 

부분 일치값을 구한후 단가들이 없다면 0이기 때문에 기존값들은 당연히 조건에서 TRUE값을 반환할 것이고, 값이 있다면 그 값보다 큰값들만 반환할 것이다. 마지막으로 정확히 일치해야 할값이 없다면 에러를 발생할 수 있으니 1을 반환하여 부분검색된 값만 반환하게 되는것이다.

 

자 365함수는 수식 하나로 이 모든것이 이뤄진다고 하였다. 근데 단점이 있다..

 

이런식으로 수식이 공개되며, 누가 이걸 삭제하거나 수정하게 된다면 엉뚱한 값이 나올 수 있다.

 

그래서 시트 보호를 통한 셀을 보호해야 한다.

 

[B12] 에 Ctrl + 1을 눌러서 셀 서식을 보자

 

보호 탭을 열면

본인이 잠김 / 숨김 에 모두 체크를 한것이 보일 것이다.

일반적으로 셀에 같은 방식으로 하게 되면

기본적으로 잠김에 체크가 되어 있다.

 

우리가 원하는 셀만 잠김과 숨김을 해야 다른 셀을 클릭했을 때 경고 메세지가 안 나오기 때문에 최초에 전체 셀을 ctrl + a 를 눌러서 전체를 선택한 후 셀 서식에 잠김을 모두 풀어준다.

 

그 다음 실제 셀 수식을 숨기거나 보호를 해줘야 할 곳에 잠김을 체크하거나 숨김을 체크해준 후

메뉴의 검토탭에 들어가서 시트보호를 누른 후

체크된 기본값에 암호만 설정하면된다.

오늘은 1234로 하겠다.

 

이제 한번 다시 [b12]를 선택해보자.

 

 

수식이 나오지 않음을 알수 있다.

시트보호를 하게되면 본인이 잠김을 설정한 곳은 에러를 발생한다.

따라서 검색어를 입력하는 부분은 반드시 잠김을 미리 풀어줘햐 하는것이다.

 

 

 

파일을 오픈하면 자동적으로 전체화면으로 바뀌는 구문이다. 

 

검색시트에서 더블클릭을 하였을 경우 전체화면과 일반화면이 서로 교차하게 된다.

 

마지막으로 틀고정으로 하였을 시 맨아래서 값을 본 후 다시 검색을 하게되면 값들이 숨겨져서 안보일수 있어서

처음으로 커서값을 옮기라는 뜻이다.

 

이상이다. 이 파일은 12시까지만 공개한다.