[054] 특정문자가 포함되었는지의 여부 확인하기

<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">안녕하세요?<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;"> <p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">특정 데이터내에서 특정문자가 포함되었는지의 여부를 확인하는 경우가 종종 있습니다.<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">그런데 찾고자 하는 특정문자가 한개라면 간단히 FIND 함수나 SEARCH 함수를 이용할 수 있겠지만...<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">찾고자 하는 특정문자가 여러개 이상이라면 일일이 하나씩 확인하기가 성가실 겁니다.<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;"> <p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">이러한 경우 대표적 배열수식인 SUMPRODUCT 함수를 이용하면 조금은 쉬워집니다. 아래 그림과 같이 A열 데이터에 D2:D6셀 사이의 데이터가 포함되었는지 여부를 알고자 합니다. 포함되었다면 '맞음' 아니면 '틀림'이라고 표시하려고 합니다.<p style="margin: 0px; padding: 0px; text-align: left; color: rgb(92, 92, 92); text-transform: none; text-indent: 0px; letter-spacing: normal; font-family: gulim; font-size: 12px; font-style: normal; font-variant: normal; font-weight: 400; text-decoration: none; word-spacing: 0px; white-space: normal; orphans: 2; background-color: transparent; -webkit-text-stroke-width: 0px;">. <img src="https://t1.daumcdn.net/cfile/cafe/991086425C9071FC1E" class="txc-image" width="1024" style="clear: none; float: none;" border="0" vspace="1" hspace="1" exif="{}" data-filename="2019-03-19_13-34-45.jpg" actualwidth="1024" id="A_991086425C9071FC1E9FA5"/> A2 셀을 선택하고 수식을...=IF(SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$6,A2))),"맞음","틀림") 로 입력합니다. <img src="https://t1.daumcdn.net/cfile/cafe/99E451425C9071FC0D" class="txc-image" width="1024" style="clear: none; float: none;" border="0" vspace="1" hspace="1" exif="{}" data-filename="2019-03-19_13-35-29.jpg" actualwidth="1024" id="A_99E451425C9071FC0DD2D6"/> 해당 수식을 복사해서 채웁니다. <img src="https://t1.daumcdn.net/cfile/cafe/999032425C9071FD19" class="txc-image" width="1024" style="clear: none; float: none;" border="0" vspace="1" hspace="1" exif="{}" data-filename="2019-03-19_13-35-40.jpg" actualwidth="1024" id="A_999032425C9071FD19FD13"/> 배열수식을 이용해서 특정문자의 포함여부를 알아봤습니다. 자세한 내용은 첨부파일을 참고로... 그럼 또...! =======================================================================================첨부파일 <a href='javascript:fileFilterViewer("http://cfile293.uf.daum.net/attach/994314495C9072D20AD00F", "/cfile293/99/4314495C9072D20AD00F", "190318_특정단어_포함여부_확인하기_배열수식.xlsx", "grpid%3D1GIVp%26fldid%3DK81s%26dataid%3D75%26fileid%3D4%26regdt%3D20190319134103&url=http%3A%2F%2Fcfile293.uf.daum.net%2Fattach%2F994314495C9072D20AD00F")'><img src="https://t1.daumcdn.net/daumtop_deco/icon/icon.hanmail.net/editor/p_etc_s.gif" border="0" alt="첨부파일" class="vam"/> 190318_특정단어_포함여부_확인하기_배열수식.xlsx</a> =======================================================================================