☰

Excel에서 셀의 첫 번째 숫자를 선택하는 방법은 무엇입니까?

tag-icon • tag-icon microsoft-excel microsoft-excel-2013 string-manipulation
Excel에서 셀의 첫 번째 숫자를 선택하는 방법은 무엇입니까?

셀에 숫자와 일부 텍스트를 모두 저장하는 테이블이 있는데 숫자를 기반으로 산술 연산을 수행하고 싶습니다. 그래서 내 테이블은 다음과 같습니다

Number1       | Number2       | Product
2             | 3             | 6
3 (some text) | 40            | 120
4             | 5 (here too)  | 20

그래서 그 추악한 텍스트가 있는 경우에도 여전히 자동으로 열 3을 생성할 수 있기를 원합니다. 숫자가 아닌 첫 번째 문자까지 하위 문자열을 숫자로 구문 분석하는 함수가 필요한 것 같습니다. 엑셀로 할 수 있나요?

추신:숫자는 1자리 이상일 수 있습니다. 정수라고 가정해도 안전하지만 부동 소수점에도 적용되는 솔루션을 보는 것이 좋을 것입니다. :)

감사해요,

답변1

숫자가 문자열의 시작 부분에 있는 경우 숫자를 얻으려면 다음을 사용할 수 있습니다.

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

기본 공식으로 숫자의 끝을 찾아 MID() 함수의 끝으로 반환합니다.

여기에는 많은 일이 일어나고 있습니다.

SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))이 부분은 숫자를 반복하면서 각 숫자의 마지막 인스턴스를 }}}.

SUBSTITUTE의 세 번째 기준은 인스턴스입니다. 로 인스턴스 수를 찾습니다 LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")). 숫자를 반복하고 한 번에 하나씩 아무것도 바꾸지 않습니다. 그런 다음 원래 문자열과 새 문자열의 길이 차이를 찾습니다.

따라서 A2의 경우 반복할 때 22를 찾고 외부 대체는 마지막 것을 }}}. 이것은 단지 임시 자리 표시자일 뿐입니다.

Aggregate 함수는 다기능 함수입니다. 14는 우리가 함수를 사용하고 있는 함수를 알려줍니다 Large(). 6오류를 무시하도록 함수에 지시합니다 . 많은 반복이 아무것도 찾지 못하고 오류를 반환한다는 점에서 이는 중요합니다.

끝에는 각 숫자의 마지막 인스턴스에 반복을 통해 배치된 1임시 항목을 검색하는 검색 기능에서 가장 높은 수익을 원하는 기능을 알려줍니다 .}}}

따라서 Aggregate는 발견된 최대 수를 반환합니다. Mid 함수의 길이 기준에 전달합니다.

이제 우리는 문자열 앞의 숫자를 찾았습니다.

따라서 우리는 이들 중 두 개를 곱하여 원하는 출력을 얻을 수 있습니다(모든 수학 함수는 반환된 문자열을 숫자로 바꿉니다).

=MID(A2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))),1))*MID(B2,1,AGGREGATE(14,6,SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))),1))

여기에 이미지 설명을 입력하세요

한 가지 주의사항Aggregate 함수는 Excel 2010에 도입되었습니다. 이전 버전에서는 작동하지 않을 수 있습니다.

이전 버전이 있는 경우 다음과 같은 더 긴 공식을 사용해야 합니다.

=MID(A2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(A2)-LEN(SUBSTITUTE(A2,{1,2,3,4,5,6,7,8,9,0},"")))))))*MID(B2,1,MAX(IF(ISNUMBER(SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},""))))),SEARCH("}}}",SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"}}}",LEN(B2)-LEN(SUBSTITUTE(B2,{1,2,3,4,5,6,7,8,9,0},"")))))))

이는 위의 내용과 거의 동일하며 최대값을 찾기 전에 먼저 오류를 테스트해야 합니다.

답변2

숫자가 항상 공백으로 텍스트와 구분된다고 가정하면 다음과 같이 사용할 수 있습니다. 예에 A2에서 시작하는 데이터가 있다고 가정하면 C2의 첫 번째 결과는 일반적으로 다음과 같습니다.

=A2*B2

단순 셀 참조를 소스 셀이 숫자인 경우 숫자로 처리하고 텍스트도 있는 경우 숫자를 추출하는 수식으로 바꿔야 합니다. 따라서 C2는 다음과 같습니다.

=IF(ISNUMBER(A2),A2,LEFT(A2,FIND(" ",A2)-1)) * IF(ISNUMBER(B2),B2,LEFT(B2,FIND(" ",B2)-1))

소스 셀이 숫자가 아닌 경우 첫 번째 공백을 찾아 그 앞의 문자를 반환합니다. 기술적으로 반환되는 것은 숫자가 포함된 텍스트 값이지만 Excel은 제품을 계산할 때 이를 숫자로 처리할 만큼 똑똑합니다(숫자로 변환하기 위해 다른 함수 내에 중첩할 필요가 없음). 숫자가 부동 소수점인 경우에도 여전히 작동합니다.

주의 사항: 이는 "깨끗한" 소스 데이터, 즉 숫자 항목 또는 숫자로 시작하고 뒤에 공백이 오는 텍스트 항목에 의존합니다. 이를 따르지 않는 셀이 있는 경우 데이터를 정리하거나 일종의 오류 검사를 포함해야 합니다. 가장 좋은 해결책은 모든 텍스트가 별도의 셀에 있도록 두 개의 데이터 열을 분할하는 것입니다.

관련 정보