Excel에서 수식을 사용하여 셀 (또는 셀 범위)의 문자 계산
Excel에는 텍스트 데이터 작업에 도움이되는 몇 가지 놀라운 텍스트 기능이 있습니다.
In 경우에 따라 셀 / 범위의 총 문자 수 또는 특정 문자가 셀에서 발생하는 횟수를 계산해야 할 수 있습니다.
수를 계산할 수있는 LEN 함수가있는 동안 셀에 문자가있는 경우 수식 조합을 사용하여 나머지 작업도 수행 할 수 있습니다 (예제 뒷부분에서 볼 수 있음).
이 자습서에서는 총계 또는 Excel에서 셀 / 범위의 특정 문자.
이 자습서에서 다루는 내용 :
셀의 모든 문자 계산
단순히 셀에있는 모든 문자의 총 개수를 얻으려면 LEN 함수를 사용할 수 있습니다.
LEN 함수는 이중 텍스트 일 수있는 하나의 인수를 사용합니다. -따옴표 또는 텍스트가있는 셀에 대한 셀 참조
예를 들어 아래와 같은 데이터 세트가 있고 각 셀의 총 문자 수를 계산한다고 가정합니다.
다음은이를 수행하는 공식입니다.
=LEN(A2)
LEN 함수 자체는별로 보이지 않을 수 있지만 다른 수식과 결합하면 멋진 일을 할 수 있습니다 (예 : 셀에서 단어 수를 가져 오거나 이름과 성을 분할하는 등).
참고 : LEN 함수는 특수 문자, 숫자, 구두점 및 공백 문자 (단어 사이의 선행, 후행 및 이중 공백) 등 셀의 모든 문자를 계산합니다.
LEN 이후 함수는 셀의 모든 문자를 계산합니다. 때로는 셀에 추가 공백이있는 경우 잘못된 결과를 얻을 수 있습니다.
예를 들어 아래의 경우 LEN 함수는 셀의 텍스트에 대해 25를 반환합니다. A1, 22 개 여야합니다.하지만 여분의 공백 문자도 계산하기 때문에 잘못된 결과를 얻습니다.
추가 공백이 계산되지 않도록 먼저 TRIM 함수를 사용하여 선행을 제거 할 수 있습니다. 후행 및 이중 공백을 사용한 다음 LEN 함수를 사용하여 실제 단어 수를 얻습니다.
아래 공식은 다음을 수행합니다.
=LEN(TRIM(A2))
셀 범위의 모든 문자 계산
또한 LEN 함수를 사용하여 전체 문자 수를 계산할 수 있습니다. 전체 범위.
예를 들어 동일한 데이터 세트가 있고 이번에는 각 셀의 문자 수를 가져 오는 대신 전체 범위에 몇 개가 있는지 알고 싶습니다.
예를 들어, p>
다음 공식을 사용하여이를 수행 할 수 있습니다.
=SUMPRODUCT(LEN(A2:A7)))
이 공식이 어떻게 작동하는지 설명하겠습니다.
위 공식에서 함수의 LEN 부분은 전체 셀 범위를 취하고 각 셀의 문자를 계산합니다.
The LEN 푸의 결과 nction은 다음과 같습니다.
{22; 21; 23; 23; 23; 31}
각 숫자는 셀의 문자 수를 나타냅니다.
그리고 SUMPRODUCT 함수를 함께 사용하면이 모든 숫자를 추가하기 만하면됩니다.
이제 SUMPRODUCT 대신 SUM을 사용할 수없는 이유가 궁금하다면 이것이 배열, SUMPRODUCT는 배열을 처리 할 수 있지만 SUM 함수는 처리 할 수 없습니다.
그러나 SUM을 계속 사용하려면 아래 공식을 사용할 수 있습니다. 그러나 Ctrl + Shift +를 사용해야합니다. 일반 Enter 대신 결과를 얻으려면 입력하십시오.)
=SUM(LEN(A2:A7))
셀에서 특정 문자 계산
실제 유틸리티를 언급했듯이 LEN 함수는 다른 수식과 함께 사용되는 경우입니다.
셀의 특정 문자 (문자, 숫자, 특수 문자 또는 공백 문자 일 수 있음)를 계산하려는 경우 수식 조합을 사용하여 수행 할 수 있습니다.
예를 들어 다음과 같은 데이터 세트가 있다고 가정합니다. 아래에서 각 셀의 총 단어 수를 세고 싶을 것입니다.
단어 수를 구하는 내장 공식은 없지만 공백 문자를 세어 총계를 알 수 있습니다. 셀의 단어 수입니다.
아래는 셀의 총 공백 문자 수를 제공하는 공식입니다.
=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))+1
위의 공식은 총 공백 문자 수를 계산 한 다음 그 숫자에 1을 더하여 단어 수를 구합니다.
이 공식의 작동 방식은 다음과 같습니다.
- SUBSTITUTE 함수는 모든 공백 문자를 공백으로 바꾸는 데 사용됩니다. 그런 다음 LEN 함수는 공백 문자가 없을 때 총 문자 수를 계산하는 데 사용됩니다.
- LEN (SUBSTITUTE (A2, “”, “”))의 결과는 LEN ()에서 뺍니다. A2) 이것은 셀에있는 총 공백 문자 수를 제공합니다.
- 1이 수식에 추가되고 총 단어 수는 총 공백 문자 수보다 하나 더 많습니다 (두 단어가 하나의 문자로 구분되므로).
선행, 후행 또는 이중 공백이있는 경우 잘못된 결과를 얻게됩니다. 이 경우 TRIM 함수를 LEN 함수와 함께 사용하는 것이 가장 좋습니다.
또한 동일한 논리를 사용하여 셀에서 특정 문자 나 단어 또는 구문을 찾을 수 있습니다.
예를 들어, 아래에 표시된 데이터 세트가 있는데 여기에서 각 배치에는이를 나타내는 알파벳과 숫자 (예 : A1, J2 등)가 있습니다.
다음은 알파벳 A가있는 배치가 매달 생성 된 총 횟수를 제공하는 공식입니다.
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))
위의 수식은 다시 동일한 논리를 사용합니다. 셀에서 텍스트 길이를 찾습니다. 숫자를 세고이 둘의 차이를 가져옵니다.
위의 공식에서는 세고 싶은 문자를 하드 코딩했지만 셀에 넣은 다음 셀 참조를 사용하십시오. 이렇게하면 다음에 셀의 텍스트를 변경할 때 수식이 업데이트되므로 더 편리합니다.
대소 문자를 구분하지 않는 수식을 사용하여 특정 문자 계산
수식에 한 가지 문제가 있습니다. 셀의 특정 문자를 계산하는 데 사용됩니다.
SUBSTITUTE 함수는 대소 문자를 구분합니다. 이것은 “A”가 “a”와 같지 않음을 의미합니다. 이것이 C5 셀에서 잘못된 결과를 얻는 이유입니다 (결과가 3이어야 함).
어떻게 할 수 있습니까? 특정 문자가 대소 문자 (소문자 또는 대문자) 일 수있을 때 특정 문자의 문자 수를 가져옵니다.
공식을 대소 문자를 구분하지 않도록 설정하면됩니다. 복잡한 공식을 사용할 수는 있지만 두 케이스 (소문자 및 대문자 A)의 문자 수를 간단히 추가했습니다.
=LEN(B2)-LEN(SUBSTITUTE(B2,"A",""))+LEN(B2)-LEN(SUBSTITUTE(B2,"a",""))
십진수 전후의 문자 / 숫자 계산
이유를 모르겠지만 이것은 독자들로부터받은 일반적인 쿼리입니다. 다음과 같은 많은 포럼에서
아래에 표시된 데이터 세트가 있고 소수점 앞과 소수점 뒤의 문자 수를 세고 싶다고 가정합니다.
다음은이를 수행하는 공식입니다.
소수점 앞의 문자 / 숫자 세기 :
=LEN(INT(A2))
소수점 뒤의 문자 / 숫자 계산 :
=LEN(A2)-FIND(".",A2)
이러한 수식은 셀의 유효 숫자에만 적용됩니다. 선행 또는 후행 0이 있거나 사용자 지정 숫자 서식을 사용하여 더 많은 / 더 적은 숫자를 표시 한 경우 위의 공식은 여전히 소수점 앞뒤에 유효 숫자를 제공합니다.
수식을 사용하여 Excel에서 셀 또는 셀 범위의 문자 수를 계산할 수있는 시나리오입니다.
이 자습서가 유용하길 바랍니다.
다른 Excel 자습서 :
- COUNTIF 함수를 사용하여 Excel에서 고유 한 값 계산
- 텍스트 문자열을 포함하는 셀 수를 계산하는 방법
- Excel에서 색상이 지정된 셀 수를 계산하는 방법
- COUNTIF 함수를 사용하여 Excel에서 고유 값 계산
- Excel에서 특정 문자 앞뒤의 텍스트를 제거하는 방법