> 주제 > excel > Excel의 SUBSTITUTE 함수 사용법 요약

Excel의 SUBSTITUTE 함수 사용법 요약

WBOY
풀어 주다: 2022-04-22 11:35:42
앞으로
12223명이 탐색했습니다.

本篇文章给大家带来了关于excel的相关知识,其中主要介绍了SUBSTITUTE函数的相关问题,该函数是Excel最常用的文本函数之一,在数据分析过程中,常用于字符串的整理和清洗,下面一起来看一下,希望对大家有帮助。

Excel의 SUBSTITUTE 함수 사용법 요약

相关学习推荐:excel教程

SUBSTITUTE函数的基础语法是:

SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])

最后一个参数,[替换第几个], 是可以省略的。

举几个小栗子,和大家分享下SUBSTITUTE函数的常用技巧和套路。

1,将数据中的某个值替换为另一个值

比如将B列数据里的二班,替换为一班。

C2输入公式,并向下复制填充:

=SUBSTITUTE(B2,"二班","一班")
로그인 후 복사

Excel의 SUBSTITUTE 함수 사용법 요약

这里没啥好解释的了,就是将B列中单元格中的“二班”全部替换为“一班”。

2,隐藏手机号的中间5位

C2输入公式,并向下复制填充:

=SUBSTITUTE(B2,MID(B2,4,5),"*****")
로그인 후 복사

Excel의 SUBSTITUTE 함수 사용법 요약

先使用MID函数取得B列号码中的中间五位,再用字符串“*****”替换掉这部分内容。

3,对含单位的数据求和

B7输入公式:

=SUMPRODUCT(SUBSTITUTE(B2:B6,"人",)*1)
로그인 후 복사

Excel의 SUBSTITUTE 함수 사용법 요약

先用SUBSTITUTE替换掉B列单元格中的“人”,得到文本型数字,乘以1后转换成可以计算的数值,再用SUMPRODUCT函数进行求和。

4,数据分列,将B列数据按顿号进行分列

C2单元格输入公式横向拖动,并向下复制填充。

=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100))
로그인 후 복사

Excel의 SUBSTITUTE 함수 사용법 요약

REPT(” “,100)

先使用REPT函数,将空格重复100次,得到100个空格。

SUBSTITUTE($B2,”、”,REPT(” “,100))

使用SUBSTITUTE函数将姓名中的的间隔符号顿号替换为100个空格。

MID(SUBSTITUTE($B2,”、”,REPT(” “,100)),COLUMN(A1)*100-99,100)

再使用MID函数,依次从带有空格的新字符串中的第1、第101、第201位……截取长度为100的字符。

这样得到的字符串是带有多余空格的,因此再使用TRIM函数将多余空格删除掉。

5,混合文本中,计算人数个数

Excel의 SUBSTITUTE 함수 사용법 요약

B2公式:

=LEN(A2)-LEN(SUBSTITUTE(A2,"、",))+1
로그인 후 복사

LEN(A2)取得A2字符串的长度。

LEN(SUBSTITUTE(A2,”、”,))+1,替换掉人名之间的间隔符,也就是顿号,再用LEN计算该值的长度,最后加1,是因为最后一个人名没有顿号。

用A2数值原有的长度减去被替换掉人名之间间隔符的长度,也就是人名的个数。

6,混合文本中,计算数值最大值。

Excel의 SUBSTITUTE 함수 사용법 요약

B2数组公式:

=MAX((SUBSTITUTE(A2,ROW($1:$98),)<>A2)*ROW(1:98))
로그인 후 복사

SUBSTITUTE(A2,ROW($1:$98),)<>A2

依次将数值1到98从A2替换为空,然后把替换后的结果和被替换值(1-98)进行比较 ,如果不相等,则证明A2中存在该数值。

最后将上述部分公式的运算结果,也就是逻辑值TRUE和FALSE,乘以被替换的值(1-98),用MAX函数从中取得最大值。

7,计算某个值在某个范围的最大连续次数

Excel의 SUBSTITUTE 함수 사용법 요약

B2数组公式:

=MAX((SUBSTITUTE(PHONETIC(A2:A9),REPT("A",ROW(1:9)),)<>PHONETIC(A2:A9))*ROW(1:9))
로그인 후 복사

PHONETIC(A2:A9)

将A2:A9的文本值黏合成一个值,以便SUBSTITUTE函数进行操作。

REPT(“A”,ROW(1:9))

把“A”重复1到9次。

SUBSTITUTE(PHONETIC(A2:A9),REPT(“A”,ROW(1:9)),)<>PHONETIC(A2:A9)
로그인 후 복사

思路回到示例6,SUBSTITUTE函数将REPT函数的运算结果,在PHONETIC函数的运算结果里替换掉,然后和PHONETIC函数的原值进行比较。如果后者存在替换值,则被替换掉,此时和原值不相等,返回FALSE,否则返回TRUE。

最后依然把上述公式返回的逻辑值TRUE和FALSE,分别乘以ROW(1:9),用MAX函数从中取得最大值。

相关学习推荐:excel教程

위 내용은 Excel의 SUBSTITUTE 함수 사용법 요약의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

관련 라벨:
원천:excelhome.net
본 웹사이트의 성명
본 글의 내용은 네티즌들의 자발적인 기여로 작성되었으며, 저작권은 원저작자에게 있습니다. 본 사이트는 이에 상응하는 법적 책임을 지지 않습니다. 표절이나 침해가 의심되는 콘텐츠를 발견한 경우 admin@php.cn으로 문의하세요.
인기 튜토리얼
더>
최신 다운로드
더>
웹 효과
웹사이트 소스 코드
웹사이트 자료
프론트엔드 템플릿