실용적인 Excel 기술 공유: 숫자를 추출하는 방법은 무엇입니까?

이전 글 "실용적인 Excel 팁 공유: 표 보기 효율성을 높이는 7가지 방법"에서는 표 보기 효율성을 높이는 7가지 팁에 대해 알아보았습니다. 오늘은 Excel에서 숫자를 추출하는 방법에 대해 이야기하고 몇 가지 일반적인 추출 상황을 소개하여 설명하겠습니다.

수식을 사용하여 셀의 숫자를 추출합니다. 특정 데이터 구조를 기반으로 특정 패턴을 찾아 해당 수식을 설계해야 합니다. 물론 소위 만능추출 공식도 있지만 매우 복잡하고 계산도 많이 필요하기 때문에 글 마지막에 간략하게만 설명하겠습니다.

첫 번째 유형의 상황: 숫자가 왼쪽에 있습니다.

예 1: 숫자가 왼쪽에 있고 자릿수가 고정되어 있습니다.

이 예에서는 데이터가 매우 규칙적이며 숫자는 모두 왼쪽 세 자리에 있습니다. 학번을 별도로 추출하려면 왼쪽 함수인 =LEFT(만 사용하면 됩니다. A2,3)

공식도 매우 간단합니다. 첫 번째 매개변수는 숫자를 추출할 셀이고, 두 번째 매개변수는 추출할 셀의 개수입니다(왼쪽부터 세기). 예제 1의 이러한 유형의 데이터의 경우 사용 시 숫자 길이에 따라 두 번째 매개변수를 수정하면 됩니다.

예 2: 숫자는 왼쪽에 있습니다. 자릿수는 고정되어 있지 않지만 명확한 구분 기호가 있습니다.

숫자 길이가 고정된 3자리가 아닌 경우 아래와 같이 이전 방법은 작동하지 않습니다. 이때 데이터 소스의 규칙을 찾은 후 그 규칙을 이용하여 동작해야 합니다. 현재 데이터에는 일률적으로 "-" 기호가 있습니다. "-" 기호의 위치를 ​​기준으로 숫자의 길이를 확인한 다음 왼쪽을 사용하여 추출할 수 있습니다.

이 경우 도움을 주기 위해 또 다른 기능, 즉 찾기 기능을 사용해야 합니다.

=FIND("-",A2,1) 공식을 사용하여 "-" 위치를 결정하세요. =FIND("-",A2,1)可以确定"-"的位置。

find函数有三个参数，第一个参数是要找什么内容，第二个参数是从哪里找，第三个参数是从第几个字开始找（从左边算起）。

=FIND("-",A2,1) 意思是在A2单元格找“-”，从开始位置找（左边第一位）。公式的结果是数字，代表“-”在单元格的位置（第几个字符），此时我们需要提取的数字长度就是find的结果减1，因此方法就有了：=LEFT(A2,FIND("-",A2,1)-1)

find 이 함수에는 세 개의 매개변수가 있습니다. 첫 번째 매개변수는 찾을 내용, 두 번째 매개변수는 찾을 위치, 세 번째 매개변수는 검색을 시작할 단어(왼쪽부터)입니다.

=FIND("-",A2,1)는 A2 셀의 처음부터(왼쪽 첫 번째) "-"를 찾는다는 의미입니다. 수식의 결과는 셀 안의 "-" 위치(어떤 문자)를 나타내는 숫자입니다. 이때, 우리가 추출해야 할 숫자의 길이는 find 빼기 1의 결과이므로 방법은 다음과 같습니다. : =LEFT(A2 ,FIND("-",A2,1)-1)

이 방법은 보다 명확한 구분 기호를 찾을 수 있는 한 광범위한 적용 범위를 갖습니다. (기호, 한자, 문자 등의 내용이 될 수 있습니다.) 이 방법을 사용할 수 있습니다.

예 3:

숫자는 왼쪽에 있고, 자릿수는 고정되어 있지 않으며 명확한 구분 기호가 없습니다.

데이터가 이렇다면 이전 방법을 사용할 수 없습니다. 숫자의 길이가 불확실하고 구분 기호가 없습니다. 유일한 규칙은 데이터에 숫자와 한자만 있다는 것입니다. 문자 및 기타 기호).

엑셀에는 문자 길이가 두 가지가 있습니다. 반각 문자(영어 모드로 입력한 숫자, 문자, 기호)는 길이가 1인 반면, 전자 문자(한자, 한자 기호 등)는 길이가 1입니다. 2의 길이. 예를 통해 이를 이해할 수 있습니다.

len 함수는 셀 내용의 길이를 구체적으로 계산하는 함수입니다. 이 함수의 길이는 우리가 일반적으로 이해하는 것과 일치합니다. 이는 문자의 "수"와 동일합니다. 전각과 반각의 차이점을 살펴보겠습니다.

🎜🎜 lenb 함수는 len 함수와 동일하게 작동합니다. 차이점은 길이를 계산할 때 반각과 전각이 구별된다는 것입니다. 내용을 보면 확연한 차이를 알 수 있습니다. 전각을 기준으로 한 텍스트의 길이는 전각과 반각의 2배이며, 숫자와 문자는 두 경우 모두 완전히 동일합니다. 🎜🎜예 3의 경우 len과 lenb를 사용하여 숫자의 길이를 확인한 다음 left를 사용하여 추출해야 합니다. 🎜

아래 그림과 같이 =LEN(A2)*2-LENB(A2) 공식을 사용하여 숫자의 길이를 결정하세요. =LEN(A2)*2-LENB(A2)可以确定数字的长度，如下图所示。

为什么len*2-lenb就会得到数字的长度呢？

根据前面两种情况下的对比结果，lenb统计长度，文字长度是len统计值的两倍，而数字长度与len统计值一致，所以公式len*2-lenb就可以计算出数字的长度了。

数字的长度统计出来以后，解决方法显而易见：=LEFT(A2,LEN(A2)*2-LENB(A2))

例三这种情况同样比较典型，只要记住是len*2-lenb就可以了，如果确实记不住，那么遇到问题的时候可以单独算一算，先把数字的长度算出来，再组合left就行了。

例四：数字在左边，并且含有字母等半角字符。

这种情况很复杂，唯一的规律就是数字在左边。来看看如何解决吧。

可以利用公式：=-LOOKUP(1,-LEFT(A2,ROW(:)))。

关于这个公式，要解释的话估计还得五千字，所以各位只需要记得套路即可，唯一有可能修改的就是最后面那个9，当最长的数字超过9位时，就要修改这个9，可以直接用99来完成。

最后，这个公式适用于任何数字在左边的情况。

第二类情况：数字在右边

数字在右边的情况与数字在左边非常类似，以下仅做简单说明：

数字位数固定的直接用right提取；

数字位数不固定但有分隔符号的使用find函数配合mid函数 完成提取：=MID(A2,FIND("-",A2,1)+1,9)

（注：公式中最后一个参数值“9”需要根据数字的最大位数修改，譬如提取的数字最大位数是10，则需要写成10或者大于10的数字。）

数字位数不固定同时没有分隔符的还是可以使用len和lenb的组合：=RIGHT(A2,LEN(A2)*2-LENB(A2))

什么规律都没有的继续让lookup发大招：=-LOOKUP(1,-RIGHT(A2,ROW(:)))

第三类情况：数字在中间

数字在中间这种非常特殊，比较规范的数据源中一般不会出现这样的情况，举一个例子吧：

例如图中这种数据，如果数字前面或者后面的长度固定，可以先用函数将内容变成前面讨论过的情况再去处理：

公式比较简单，不做解释了，至于后面一步怎么做，根据前面的学习你已经会了吧？

下面要说的这个方法就是一种比较通用的方法了，有人称其为万能提取公式：

=-LOOKUP(0,-MID(A1,MIN(FIND(ROW(:)-1,A1&1/17)),ROW(:)))

왜요? len*2-lenb는 숫자의 길이를 가져오나요?

앞서 두 사례의 비교 결과 lenb 통계적 길이에 따르면, 텍스트 길이는 len 통계값의 2배이고, 숫자 길이는 len 통계값과 일치하므로 len*2-lenb 공식으로 계산할 수 있습니다. 숫자의 길이. 숫자의 길이를 세어보면 해결책은 분명합니다: =LEFT(A2,LEN(A2)*2-LENB(A2))

예 3의 이 상황도 매우 일반적입니다. 기억하세요. len *2-lenb이면 충분합니다. 실제로 기억이 나지 않으면 문제가 발생하면 먼저 숫자의 길이를 계산한 다음 왼쪽으로 결합하면 됩니다.

예 4:

숫자는 왼쪽에 있으며 문자와 같은 반각 문자를 포함합니다.

🎜🎜🎜이것 이 상황은 매우 복잡하며 유일한 규칙은 숫자가 왼쪽에 있다는 것입니다. 해결 방법을 살펴보겠습니다. 🎜🎜공식: =-LOOKUP(1,-LEFT(A2,ROW($1:$9)))을 사용할 수 있습니다. 🎜🎜🎜🎜정보 이 수식에 대한 설명은 아마도 5,000 단어가 소요될 것이므로 루틴만 기억하면 됩니다. 수정할 수 있는 유일한 것은 가장 긴 숫자가 9 자리를 초과하는 경우 9를 수정해야 합니다. . 99를 사용하여 완료하세요. 🎜🎜마지막으로, 🎜이 공식은 숫자가 왼쪽에 있는 모든 상황에 적용됩니다 🎜. 🎜🎜🎜🎜두 번째 유형의 상황: 숫자가 오른쪽에 있음 🎜🎜🎜🎜숫자가 오른쪽에 있는 상황은 왼쪽에 있는 숫자와 매우 유사합니다. 🎜🎜If 자릿수는 고정되어 있으므로 오른쪽을 사용하여 직접 추출하세요. 🎜🎜🎜🎜자릿수는 고정되어 있지 않습니다. 그러나 구분 기호가 있는 경우 찾기 기능과 mid 기능을 사용하여 추출을 완료합니다: =MID(A2,FIND(" -",A2,1)+1,9)🎜🎜🎜🎜(🎜참고: 🎜수식의 마지막 매개변수 값 "9"는 최대 자릿수에 따라 수정되어야 합니다. 예를 들어 최대 자릿수인 경우 추출된 자릿수는 10이므로 10 또는 10보다 큰 숫자로 작성해야 합니다.)🎜🎜자릿수가 고정되어 있지 않고 구분 기호가 없는 경우에도 len을 사용할 수 있습니다: = 오른쪽(A2,LEN(A2)*2-LENB(A2))🎜🎜🎜🎜계속해서 규칙 없이 큰 움직임을 사용하여 조회할 수 있도록 합니다. =-LOOKUP(1,- RIGHT(A2,ROW($1:$9) ))🎜🎜🎜🎜🎜🎜세 번째 유형의 상황: 숫자가 중간에 있습니다🎜🎜🎜🎜숫자가 중간에 있습니다. 이것은 매우 특별합니다. 이러한 상황은 일반적으로 보다 표준화된 데이터 소스에서는 발생하지 않습니다. , 예: 🎜🎜🎜 🎜예를 들어 사진 속 데이터의 경우 숫자 앞이나 뒤의 길이가 고정되어 있으면 함수를 사용하여 앞서 설명한 상황으로 내용을 변경한 후 처리할 수 있습니다. 🎜🎜🎜🎜공식은 상대적으로 간단하므로 생략하겠습니다. 설명하지 마세요. 다음 단계는 Do it입니다. 이전 연구를 바탕으로 이미 알고 계시죠? 🎜🎜아래에 언급된 방법은 보다 일반적인 방법입니다. 어떤 사람들은 이를 범용 추출 공식이라고 부릅니다: 🎜🎜=-LOOKUP(0,-MID(A1,MIN(FIND(ROW($1:$10) )-1 ,A1&1/17)),ROW($1:$9)))🎜🎜🎜🎜🎜배열 수식을 끝내려면 "🎜Ctrl+shift+Enter🎜" 세 키를 눌러야 합니다. 🎜🎜🎜🎜요약🎜🎜🎜🎜 수식을 사용하여 셀의 숫자를 추출하는 방법에 대해서는 위에서 대부분의 상황을 설명했지만, 물론 소수점이 포함된 상황 등 전부는 아닙니다. 더욱 특별합니다. 🎜

마지막 배열 수식은 좀 더 일반적인 수식이라고 볼 수 있습니다. 강조해야 할 점은 대부분의 문제는 비표준 데이터 소스로 인해 매우 번거롭다는 것입니다. 따라서 이름, 수량 등 데이터의 한 가지 속성만 셀에 저장하는 습관을 기르십시오. 가격 및 기타 정보는 별도로 저장됩니다. , 이는 통계 효율성을 크게 향상시킵니다.

물론 수식 활용 능력을 향상시킨다는 관점에서 보면 숫자 추출과 같은 문제도 함수 연습에 좋은 주제입니다. 이전에 배웠던 함수들을 조합해서 더 많은 수식을 생각해 숫자를 추출해보시길 바랍니다.

관련 학습 권장 사항: excel 튜토리얼

위 내용은 실용적인 Excel 기술 공유: 숫자를 추출하는 방법은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!