셀에 포함된 고유 텍스트만 추추할 수 있을까요?

함수/공식
작성자
태양비
작성일
2021-10-27 18:02
조회
424
엑셀버전 : 엑셀2016

운영체제 : 윈도우10

안녕하세요. 대전에 사는 직장인입니다.

저는 특허 데이터를 분석하는 업무를 하고 있는데요.

한 "셀"에 포함되어 있는 여러 데이터 중에서 특정 고유 데이터들만 추출하고, 이의 갯수를 산출할 수 있는 방법이 있을까 하여 질문을 올립니다.

 

첨부파일(1.xlsx)에 보면 한 셀에 "|(콜론)"을 구분자로 하여 여러 개의 특허번호(ex, JP2004000008A, AR64032A1 등)가 입력되어 있습니다.

이들 특허 번호중 제일 앞 2자리 영문자(예, JP, AR 등)가 국가코드인데요.

한 셀에 포함되어 있는 중복이 제거된 국가 코드만 추출하고, 그 국가 코드의 갯수가 얼마 인지를 구하는 방법을 알고 싶습니다.

첨부 파일을 예로 설명을 드리면, 첨부파일의 2행 처럼 "EP1318542A1 | EP1318542A4 | EP1318542B1 | JP04112198B2 | JP2002158181A | US20030001134A1 | US6787053B2 | WO2002023608A1"(A열)에서 중복되지 않은 국가 코드 "EP, JP, US, WO"(B열)를 추출하고, 그 갯수인 "4"(C열)를 어떻게하면 구할 수가 있을까요?

 

파워쿼리 강의도 들어보고 여러 강좌도 봤는데 제 능력으로는 알 수가 없더라구요. ㅠ..ㅠ

 

도움 부탁드립니다.

스크랩
공유
회원등급 : 4레벨
포인트 : 212 EP
총질문 : 5 개 (마감율 : 40%)
채택답변 : 0 개
전체 7

  • 2021-10-27 22:58

    @태양비 님 고유값은 아래처럼

    개수는 아래처럼 구하면됩니다 유첨파일 확인해보세요

    첨부파일 : 1.xlsx


    • 2021-10-29 10:39

      @엑셀은즐거워 님 출장을 다녀와서 이제서야 답변을 봤습니다. 먼저 답변 감사합니다. ^ ^

      그런데, 제가 질문을 조금 잘못드린 것 같습니다. 죄송합니다. (- -) m(_ _)m (- -)

      제가 구하고 싶은 건 데이터 전체에 있는 국가코드 및 갯수가 아니라, 각 행마다 '특허 번호' 셀에 있는 고유 특허 국가 코드와 코드 갯수를 구하고 싶은 거였습니다. ^ ^;;

      그러니까, 제가 첨부해드린 파일에서 보면 2행의 특허번호 셀에 포함된 고유 국가 코드인 'EP, JP, US, WO'를 추출하고, 그 갯수인 '4'를 구한 것처럼 3행의 특허번호 셀에 포함된 고유 국가코드 및 갯수, 4행의 특허번호 셀에 포함된 고유 국가코드 및 갯수... 이렇게 각 행마다 구하고 싶은 거였습니다.

      행이 몇 개 안되면 텍스트 나누기 한 후에 고유 코드 뽑고, 이를 병합 및 갯수 카운트 하는 방법으로 하면 되는데, 저희가 많을 때는 거의 90만행의 데이터를 처리하는 경우도 있어서 상기 방법으로는 사용하는 함수가 많아 시간이 너무 많이 걸리고, 어떤 때는 컴퓨터가 다운되어 버리더라구요. ㅠ.ㅠ

      그래서, 좀 간단하고 빠르게 할 수 있는 방법이 있을까 해서요. ^ ^;;


  • 2021-10-28 07:18

    @태양비 님 역시 Data가 많을 때는 Query가 답이네요. Excel로 처리하다보니 시간이 많이 걸립니다. 파일 첨부하니 참고하십시오.

    일단 자료를 다른 Sheet로 Copy를 하고 데이터에  메뉴에 있는 텍스트 나누기 기능을 이용하여 아래 사진과 같이 밑 작업을 하였습니다. 그 이후 VBA 프로그램 작업을  통하여 첨부와 같은 결과가 나왔습니다. 처리양이 많아 시간이 많이 걸리니 참고하십시오.


  • 2021-10-28 08:23

    @태양비 님 파일과 결과를 누락했군요. 30개국 18200건입니다.

    첨부파일 : 1_답변1111.xlsm


    • 2021-10-29 10:42

      @Yukon 님 출장을 다녀와서 이제서야 답변을 봤습니다. 먼저 답변 감사합니다. ^ ^

      앞에 댓글 달아주신 분께도 말씀드렸는데, 제가 질문을 조금 잘못드린 것 같습니다. 죄송합니다. (- -) m(_ _)m (- -)

      제가 구하고 싶은 건 데이터 전체에 있는 국가코드 및 갯수가 아니라, 각 행마다 '특허 번호' 셀에 있는 고유 특허 국가 코드와 코드 갯수를 구하고 싶은 거였습니다. ^ ^;;

      그러니까, 제가 첨부해드린 파일에서 보면 2행의 특허번호 셀에 포함된 고유 국가 코드인 'EP, JP, US, WO'를 추출하고, 그 갯수인 '4'를 구한 것처럼 3행의 특허번호 셀에 포함된 고유 국가코드 및 갯수, 4행의 특허번호 셀에 포함된 고유 국가코드 및 갯수... 이렇게 각 행마다 구하고 싶은 거였습니다.

      행이 몇 개 안되면 텍스트 나누기 한 후에 고유 코드 뽑고, 이를 병합 및 갯수 카운트 하는 방법으로 하면 되는데, 저희가 많을 때는 거의 90만행의 데이터를 처리하는 경우도 있어서 상기 방법으로는 사용하는 함수가 많아 시간이 너무 많이 걸리고, 어떤 때는 컴퓨터가 다운되어 버리더라구요. ㅠ.ㅠ

      그래서, 좀 간단하고 빠르게 할 수 있는 방법이 있을까 해서요. ^ ^;;


      • 2021-11-02 09:44

        @태양비 님 필요하신 상태로 다시 만들어 보았지만 시험하여 보니 역시 1000행하는 처리하는데 약15초간 걸립니다. 물론 제 LapTop이 10년전 것이라서 신형 컴퓨터는 이것의 20배 정도 속도가 나오기는 하지만 90만행이라면 일반 PC에서는 어렵지 않을까하고요. Excel이 아니라 속도가 빠른 DB에서 처리하는 것이 맞을 듯 싶습니다. Access만해도 Excel의 몇십배는 빠르고 MS SQL Server나 MySql등을 이용하시는 것이 맞을 듯합니다. 물론 Programming은 대동 소이하지만 처리 속도가 그렇네요. 좋은 결과가 나오지 않아 같이 답답합니다.

        첨부파일 : 특허코드분석.xlsm


        • 2021-11-02 10:46

          @Yukon 님 와우~~ 대단하십니다. ^ ^

          이 정도면 데이터를 나눠서라도 작업할 수 있으니 시간이 많이 세이브될 것 같습니다.

          정말정말 감사드립니다.

          매크로로 만드신 것 같은데, 어떻게 하신 건지 방법을 알 수 있을까요? ^ ^;;


전체 7,856
번호 카테고리 제목 작성자 작성일 추천 조회
알림
🎉 오피스 분야 30주 연속 1위! - 「 진짜쓰는 실무엑셀 」 전자책이 출간되었습니다! (12)
오빠두엑셀 | 2022.09.28 | 추천 6 | 조회 356
오빠두엑셀 2022.09.28 6 356
공지사항 함수/공식
[신규 기능 업데이트!] 👉 이제 게시글 작성시 스크린샷 복/붙이 가능합니다! 😎 (3)
오빠두엑셀 | 2022.08.04 | 추천 5 | 조회 1753
오빠두엑셀 2022.08.04 5 1753
공지사항 함수/공식
⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (98)
오빠두엑셀 | 2021.10.28 | 추천 126 | 조회 7529
오빠두엑셀 2021.10.28 126 7529
42747 함수/공식
New 다중 유효성 목록상자가 안되네요... 함수 점검을 요청드립니다. 엑셀파일첨부파일
회장님 | 00:20 | 추천 0 | 조회 14
회장님 00:20 - 14
42745 기능/도구
New 드롭다운에 대해 다시 한번 문의드립니다 엑셀파일 (1)
Dominica | 2022.10.03 | 추천 0 | 조회 16
Dominica 2022.10.03 - 16
42743 함수/공식
New 첨부와 같이 바꿀 대상을 찾지 못 했다는 창이 나타날 시 조치는 첨부파일 (1) 답변완료
구름나그네 | 2022.10.03 | 추천 0 | 조회 20
구름나그네 2022.10.03 - 20
42741 피벗테이블
New 피벗테이블 '나중에 레이아웃 업데이트'기능이 안됩니다. 엑셀파일첨부파일 (2)
월마리아 | 2022.10.03 | 추천 0 | 조회 19
월마리아 2022.10.03 - 19
42737 함수/공식
New 드롭다운과 INDEX MATCH 함수에 대해 문의드립니다 엑셀파일 (3) 답변완료
Dominica | 2022.10.03 | 추천 0 | 조회 31
Dominica 2022.10.03 - 31
42736 차트/그래프
New 예제 파일 다운 받아서 동영상 보고 따라할려고 예제 다운로드 하면 저렇게 나오는데요 첨부파일 (1)
희완 | 2022.10.03 | 추천 0 | 조회 19
희완 2022.10.03 - 19
42735 함수/공식
New 함수조언 구합니다. 시작월과 종료월 사이값을 기준으로 함수짜려합니다. 엑셀파일
엑셀공부중 | 2022.10.03 | 추천 0 | 조회 21
엑셀공부중 2022.10.03 - 21
42729 함수/공식
New 재무 함수를 잘 모르겠어요 ..
abcdr12**** | 2022.10.03 | 추천 0 | 조회 35
abcdr12**** 2022.10.03 - 35
42724 VBA
New 매크로 1출로 가능한 필터링 자동화 에서 (data 누락현상) 첨부파일 (7)
스타트67 | 2022.10.03 | 추천 0 | 조회 34
스타트67 2022.10.03 - 34
42720 피벗테이블
New 자료가 이런 모양이어도 피벗테이블 만드는 게 가능할까요? 첨부파일 (1)
신지 | 2022.10.03 | 추천 0 | 조회 43
신지 2022.10.03 - 43
42717 함수/공식
New 엑셀 표에서 특정 크기 이상만 별도 산출하는 법 (1)
naver_60fe15011326f | 2022.10.02 | 추천 0 | 조회 30
naver_60fe15011326f 2022.10.02 - 30
42715 함수/공식
New 엑셀 카드 내역 정리, 키워드 분류 자동화 공식 사용법 파일 오류 체크를 부탁 드립니다. 엑셀파일
엑셀 초보자 | 2022.10.02 | 추천 0 | 조회 31
엑셀 초보자 2022.10.02 - 31
42714 문서서식
New 엑셀 카카오톡 문자보내기 자동화 서식 (1)
vaikal | 2022.10.02 | 추천 0 | 조회 32
vaikal 2022.10.02 - 32
42710 기능/도구
New 메세지박스 3초후 자동 닫힘 (1)
임종수 | 2022.10.02 | 추천 0 | 조회 32
임종수 2022.10.02 - 32
42705 VBA
New VBA 네이버부동산 웹크롤링 Redim vaReturn(1 To r, 1 To c) 9 런타임 오류 발생 관련 엑셀파일첨부파일 (2)
이호일 | 2022.10.02 | 추천 0 | 조회 34
이호일 2022.10.02 - 34
42702 피벗테이블
New 피벗테이블에서 새로고침 후에 발생되는 문제 관련 문의드립니다. 첨부파일 (1)
바람의 영혼 | 2022.10.02 | 추천 0 | 조회 28
바람의 영혼 2022.10.02 - 28
42701 VBA
New get_DB 함수와 Filtered_DB 함수// ADODB 속도 문의 (2) 답변완료
hsb**** | 2022.10.02 | 추천 0 | 조회 28
hsb**** 2022.10.02 - 28
42696 VBA
New VBA 리스트박스 목록 선택 시 자동 누적 입력하기 안됨 해결방법 엑셀파일
GMART | 2022.10.01 | 추천 0 | 조회 34
GMART 2022.10.01 - 34
42695 파워쿼리/피벗
New 파워쿼리 병합 후 정렬에 대해서 질문드립니다. 엑셀파일첨부파일 (5)
월마리아 | 2022.10.01 | 추천 0 | 조회 39
월마리아 2022.10.01 - 39
42689 VBA
New 셀의 이미지를 복사하는 매크로... 첨부파일
실런 | 2022.10.01 | 추천 0 | 조회 36
실런 2022.10.01 - 36
42686 문서서식
New 영상 자료파일
뿌삐뿌찌빵꾸똥 | 2022.10.01 | 추천 0 | 조회 37
뿌삐뿌찌빵꾸똥 2022.10.01 - 37
42684 문서서식
New 조건부 서식 색 적용 안됨 첨부파일 (2)
아드키 | 2022.10.01 | 추천 0 | 조회 34
아드키 2022.10.01 - 34
42678 VBA
New VBA코드 오류뜨는데 왜 일까요ㅠㅠ 첨부파일 (2) 답변완료
엑셀공부중 | 2022.10.01 | 추천 0 | 조회 39
엑셀공부중 2022.10.01 - 39
42676 함수/공식
New 함수조언 구합니다. 엑셀파일 (1) 답변완료
엑셀공부중 | 2022.09.30 | 추천 0 | 조회 40
엑셀공부중 2022.09.30 - 40
42672 기능/도구
New 조건부 서식이 적용되지 않습니다. 첨부파일
dollffin | 2022.09.30 | 추천 0 | 조회 35
dollffin 2022.09.30 - 35
42670 피벗테이블
New 관계형데이터베이스 파워피벗 엑셀파일 (2) 답변완료
겸둥이 | 2022.09.30 | 추천 0 | 조회 45
겸둥이 2022.09.30 - 45
42669 함수/공식
New 출퇴근 자료를 이용하여 근태 테이블 작성하기 (index, match, iferror 함수 사용) 엑셀파일 (1)
공학 | 2022.09.30 | 추천 0 | 조회 44
공학 2022.09.30 - 44
42667 VBA
New M365 2206 버전의 VBA 자동화 오류 관련하여 문의를 드리고자 합니다
lililililil | 2022.09.30 | 추천 0 | 조회 27
lililililil 2022.09.30 - 27
42665 함수/공식
New if 함수 논리 설정 시 범위 선택 첨부파일 (2)
구찌갱 | 2022.09.30 | 추천 0 | 조회 35
구찌갱 2022.09.30 - 35
42664 문서서식
New 엑셀 날짜관련 질문드립니다. 첨부파일 (1)
엑셀잘할래요 | 2022.09.30 | 추천 0 | 조회 36
엑셀잘할래요 2022.09.30 - 36