[엑셀] 문자검색 - 셀에 특정 값이 포함되었는지 여부 체크하는 방법(회의록,참석자,출석체크) [SEARCH, ISNUMBER]

안녕하세요!
이번에는 제가 예전에 유용하게 사용했던 엑셀 함수를 정리해서 공유드리고자 합니다.

원래 이 기능을 찾아보게 된 계기는 회사에서 출원/등록된 특허 리스트를 엑셀로 관리하였었는데
상반기, 하반기마다 리스트를 결산해서 직원 별 출원, 등록 건수를 체크하고, 직무발명 보상을 하곤 했었어요.

처음에는 리스트에 이름을 보고 한명한명씩 수기로 체크했었는데, 데이터 수가 늘어나고 업무가 반복되다 보니
편하게 할 수 있는 방법이 없을까해서 방법을 찾아보았었어요.

예제 파일은 다음과 같습니다.

해당셀에특정값포함여부확인.xlsx

0.01MB

여기서 예시는 일자별 회의 참석자 테이블이 있다고 가정했을 때

• 각 참석자별 출석 여부 체크
• 이를 바탕으로 시작일~종료일 사이에 있는 회의에 대해 기간 내 회의 참석횟수

를 확인할 수 있는 엑셀 문서를 예시로 만들고, 같이 살펴보고자 합니다.

[기본 데이터]

기본 데이터는 아래와 같이 기준일자 (시작일/종료일)와 일자별 참석자 리스트가 될거에요.

[엑셀 수식]

먼저, 일자에서 기준 범위에 들어가는 셀을 강조해서 표시해보려고 했어요

1. 조건부 서식-셀 강조 규칙-다음 값의 사이에 있음 선택!

2. 왼쪽 칸을 클릭하고, 시작일 칸을 클릭 & 오른쪽 칸을 클릭하고 종료일 칸을 클릭

3. 그리고, 이제 일자와 참석자 열 사이에 '기간내 여부' 열을 추가하고, 다음 수식을 적용해 해당 일자가 기간 내 포함일 경우 1, 아닐 경우 0을 표시해 줍니다. (이를 바탕으로 나중에 기간 내 열린 회의를 대상으로 참석 횟수를 카운팅해요)

"=IF(AND(일자셀>=시작일자셀, 일자셀<=종료일자셀, 1, 0)"

4. 참석자열 오른쪽에 모든 사람(여기서는 10명) 별로 이름을 적은 열을 추가해 줍니다.

5. 맨 오른쪽에는 참석자 수 열을 만들고 각 행별로 참석자 이름셀(여기서는 김민준 열부터 정민서 열까지)을 선택해서 SUM을 미리 적용해 둡니다.

6. 그리고 각 열의 아래에 다음 수식을 적용합니다. [하이라이트!]
(*optional) 조건부 서식을 이용해 값이 1인 경우 또 강조표시를 추가해 주었습니다.

"=IF(ISNUMBER(SEARCH(찾을문자(이름), 참석자 셀)), 1(있을경우 출력값), 0(없을 경우 출력값))"

7. 기간내 여부 열과 각 사람별 열을 선택한 뒤 SUMPRODUCT 수식을 적용합니다.

"=SUMPRODUCT(기간내 여부 열($C$5:$C$11), 각 사람별(열 별) 참석횟수(E5:E11))"

[엑셀 수식 동작 원리]

1. SEARCH 함수 : 특정 문자 포함여부 검색

= SEARCH("김민준", "김민준 이서준 최도윤 최서현 성민서")
: '김민준' 이라는 문자가 포함되어 있으므로 '김민준' 단어의 시작지점인 =1 반환
= SEARCH("홍길동", "김민준 이서준 최도윤 최서현 성민서")
: '홍길동' 이라는 문자가 포함되어 있지 않으므로, #VALUE! 에러 반환

2. ISNUMBER 함수 : 특정 문자 포함여부를 한번 더 필터링

SEARCH 함수 결과(#1) 셀 안에 특정 문자가 포함되어 있으면 숫자,
그렇지 않을 경우 #VALUE! 오류를 반환
1) 만약 앞에서 숫자가 나왔을 경우 TRUE 출력 (SEARCH 함수 결과 특정 문자가 포함되었을 경우)
= ISNUMBER(SEARCH("김민준", "김민준 이서준 최도윤 최서현 성민서"))
= ISNUMBER(1) = TRUE

2) 만약 셀안에 특정 문자를 포함하고 있지 않을 경우, ISNUMBER 함수는 FALSE를 반환합니다.
= ISNUMBER(SEARCH("홍길동", "김민준 이서준 최도윤 최서현 성민서"))
= ISNUMBER(#VALUE!) = FALSE

3. IF 함수로 참/거짓 출력값을 지정합니다.

= IF(ISNUMBER(SEARCH("김민준", "김민준 이서준 최도윤 최서현 성민서")), 1, 0)
= IF(TRUE, 1, 0)
= 1

출처
https://www.oppadu.com/if-%ED%95%A8%EC%88%98-%ED%8A%B9%EC%A0%95-%EB%AC%B8%EC%9E%90-%ED%8F%AC%ED%95%A8/

IF 함수 특정 문자 포함 조건 검색 방법 - 오빠두엑셀

---