엑셀 숫자만 추출 합계 - egsel susjaman chuchul habgye

엑셀 숫자만 추출하기 공식

엑셀 숫자만 추출하기 목차 바로가기

1. 사용된 공식 요약
2. 예제파일 다운로드
3. 엑셀 숫자만 추출하기 공식 설명
4. 공식의 동작원리

함수 공식

=SUMPRODUCT(MID(0&셀,LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
'본 공식은 배열수식이므로 M365 이전버전은 Ctrl + Shift + Enter로 입력합니다.

매크로를 사용해서 숫자 추출을 자동화하는 방법은 아래 게시글에 정리해드렸습니다.

인수 설명

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

• [엑셀공식] 엑셀 숫자만 추출하기 공식

  예제파일

  다운로드

호환성

사용된 기초 함수

엑셀 숫자만 추출하기 공식 알아보기

공식 설명

본 공식은 셀 안에 입력된 값에서 숫자만 추출하는 공식입니다. 셀 안에 입력된 글자수나 사용된 언어(영어, 한국어, 중국어 등)에 상관없이 사용할 수 있습니다.

M365 버전에서는 일반 수식과 동일하게 Enter키로 입력해도 동작하지만, M365 이전 버전에서는 반드시 Ctrl + Shift + Enter로 수식을 입력해야 합니다. 그렇지 않을 경우 #VALUE! 오류를 반환합니다.

공식의 동작원리

1. 셀 안에 입력된 값 앞에 강제로 0 추가하기

   '셀 : abc12e34f
   = SUMPRODUCT(MID(0&셀,LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
   '셀 안에 입력된 텍스트 앞에 0을 추가합니다.
   = SUMPRODUCT(MID("0abc12e34f",LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))

2. LARGE 함수로 문자 추출지점 구하기

   = SUMPRODUCT(MID("0abc12e34f",LARGE(ISNUMBER(--MID(셀,ROW($1:$50),1))*ROW($1:$50),ROW($1:$50))+1,1)*10^(ROW($1:$50)-1))
   ' 공식에서 LARGE 함수 부분만 분리해서 단계별로 알아보겠습니다.
   = LARGE(ISNUMBER(--MID("abc12e34f",ROW($1:$50),1) )*ROW($1:$50), ROW($1:$50)) +1
   ' ROW($1:$50)은 배열함수에서 {1,2,3,4,5...., 50}을 반환합니다.
   = LARGE(ISNUMBER(--MID("abc12e34f",{1,2,3,4,...,50},1) )*ROW($1:$50), ROW($1:$50)) +1
   ' MID 함수로 "0abc12e34f" 에서 첫번째 문자부터 두번째, 세번째... 문자를 하나씩 분리합니다.
   ' -- 기호는 문자로 반환된 값을 숫자로 강제 변환합니다.
   = LARGE ( ISNUMBER( {a,b,c,1,2,e,3,4,f} ) * ROW($1:$50) , ROW($1:$50)) +1
   ' ISNUMBER를 통해 반환된 값의 숫자 여부를 판단합니다. 숫자일경우 1, 아닐경우 0을 반환합니다.
   = LARGE ( {0,0,0,1,1,0,1,1,0} ) * ROW($1:$50) , ROW($1:$50)) +1
   = LARGE ( {0,0,0,4,5,0,7,8,0} , ROW($1:$50) ) + 1
   = {8,7,5,4} + 1
   = {9,8,6,5}
   ' 0을 포함한 텍스트(0abc12e34f)에서, 각 9번째,8번째,6번째,5번째 값이 숫자인 것을 확인할 수 있습니다.

3. 맨 뒤에 위치한 숫자값 부터 10^제곱근을 곱하여 나열한 뒤, SUMPRODUCT로 합산하기

   ' MID 함수로 각 n번째 문자를 분리합니다.
   = SUMPRODUCT( MID( "0abc12e34f", {9,8,6,5}, 1) * 10^(ROW($1:$50)-1) )
   = SUMPRODUCT( {4,3,2,1} * 10^(ROW($1:$50)-1) )
   = SUMPRODUCT ( {4,3,2,1} * 10^( {0,1,2,3} ) )
   ' 10의 0제곱은 1, 10의 1제곱은 10, 10의 2제곱은 100... 으로 계산됩니다.
   = SUMPRODUCT ( {4*10^0, 3*10^1, 2*10^2, 1*10^3} )
   = SUMPRODUCT ( {4, 30, 200, 1000} )
   = 1234
   '결과값으로 1234를 반환합니다. (=셀에서 숫자만 추출)

4.8 11 투표

게시글평점

숫자만 선별하여 SUM적용하기

함수/공식

작성자

체리빵

작성일

2020-12-20 04:04

조회

3496

안녕하세요?

첨부된 그림을 보면, 금액이라는 문자와 숫자가 동일한 열에 기재되어 있는데요.

이런 경우 G열 전체를 SUM하더라도, 문자는 제외하고 숫자만 SUM되게 하려면

어떤 수식을 사용하면 되나요?

합계.JPG

스크랩

공유

공감

0명

회원등급 : 3레벨

포인트 : 94 EP

총질문 : 5 개 (마감율 : 0%)

채택답변 : 0 개

게시글

댓글

• 날짜 자동 기입 (1)

  2021.01.29

• 엑셀 수식 입력 단축키 (2)

  2021.01.13

• #N/A 오류와 SUM함수 (4)

  2021.01.09

• 숫자만 선별하여 SUM적용하기 (1)

  2020.12.20

• 수식 자동생성 (5)

  2020.06.21

• 반갑습니다~

  2020.06.21

• 반갑습니다~

  2020.06.21

전체 1

• 
  이하늘 님 49

  2020-12-20 14:09

  0

  @체리빵 님 =SUM(G3:G21) 와 같이 사용하시면 중간에 숫자가 있어도 합계가 계산이 됩니다.

  
  

  ---

댓글을 남기려면 로그인하세요.

소셜 로그인

« 전체선택>빈셀>이름정의 후 선택되지 않는 셀 문의드립니다.

매크로 기록 정렬 »

목록보기

전체 8,884

• 전체
• 함수/공식
• 차트/그래프
• 피벗테이블
• 문서서식
• 대시보드
• 파워쿼리/피벗
• 기능/도구
• VBA
• 구글시트

번호카테고리제목작성자작성일추천조회알림

🎉 오피스 분야 30주 연속 1위! - 「 진짜쓰는 실무엑셀 」 전자책이 출간되었습니다! (41)

⭐ [더 나은 커뮤니티 문화를 위한 Q&A 글 작성 규칙] ⭐ (117)

오빠두엑셀 | 2021.10.28 | 추천 145 | 조회 9236

New 엑셀 이름정의하기

New 스크롤 쭉 내리가다 원하는 위치 H5라면 그 자리로 가려면 어떻게 해야하나요? 컨트롤+BACKSPACE를 했더니 안되더라고요, 매크로 사용을해서 그런지...

New 여러 행의 데이터를 한 행으로 순서에 맞게 정렬할 수 있는 방법 (2)

New input type "hidden" 표시 할수있는 방법이 있을까요?

New 포함되는 값 한셀에 줄바꿈으로 표기

New 하이퍼링크 설정후 이동 클릭을 하면 에로 발생

New 연봉밴드를 그래프화 시키는방법

New 다른 이름의 거래처를 하나의 거래처 명으로 합치는방법없을까요?

New 피벗테이블로 구현하고 싶은게 있는데 잘 안되네요...

New 키워드 조합 매크로 오류 문의 (2)

New 구글 스프레드시트 권한 자동 변경 매크로 질문 (1)

New 창고 입 출고 vba 도움 요청드려요..

New 월별 마지막일 반환 함수 문의

New 셀 숫자에 따라서 해당 숫자 표시 방법 설정 문의 드립니다.

New 해당 셀에 26.7 입력하면 267,000으로 나오게 하는 방법 (3) 답변완료

New 인다이렉트 어드레스 함수 문의드립니다!

New 기호를 사용하면 글자가 작아지고 노트북에서 ctrl 더하기가 안먹혀요

New SUM함수의 자동 채우기가 궁금합니다. (3) 답변완료

New 필터설정 문의드립니다.

New 자동 필터 또는 순위별 자동 문의 드립니다 (2)

New 이름 정의를 통하여 다중유효성 목록 및 동적범위(offset함수) 활용시 오류 해결방법

New 동적범위 데이터 유효성 검사(드롭다운) 질문 (2) 답변완료

New 재고 선입선출 - 반품재고 금액 찾기

New 기말재고로 매입내역 최신날짜 순으로 역으로 찾는방법

New 3개값을 x,y축으로만 차트 만들어보기..

New 데이터 입력시 자동으로 행 높이가 바뀌는 기능 끄기 (2) 답변완료

New 필터링 후 다른 파일에 복붙할 때 쉽게 하는법 (3)

New 엑셀에서 뒤로가기(ctrl+z) 목록이 없어지는 현상에 대해서 질문드립니다.

New 분수 서식 (2) 답변완료

New 스페이스 바를 누르면 ㄱ한자키 1번 빈칸 특수문자가 입력됩니다 (4) 답변완료

• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• »
• 마지막

Powered by KBoard