第六次课文本函数2

函数主讲：刘彬Email:jsj081203@163.comIS类函数可以检验数值的类型并根据参数取值返回TRUE或FALSE。例如，如果数值为对空白单元格的引用，函数ISBLANK返回逻辑值TRUE，否则返回FALSE。ISBLANK()值为空白单元格。ISERR()值为任意错误值（除去#N/A）。ISERROR()值为任意错误值（#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!）ISLOGICAL()值为逻辑值。ISNA()值为错误值#N/A（值不存在）。ISNONTEXT()值为不是文本的任意项（注意此函数在值为空白单元格时返回TRUE）。ISNUMBER()值为数字。ISREF()值为引用。ISTEXT()值为文本。重复字符或字符串Rept函数可以重复文本字符串(第一个参数)任意次数。例如：=rept(“ab”,3)结果为ababab创建一个文本直方图

Rept函数可以直接在工作表中创建一个简单的直方图。有时候需要直观地综和很多值，这个功能就特别有用。这种情况，标准图表可能无法表示。如图，左边一个预算表。右侧中直观用了一个数字化的图形表示。主要公式如下：F2中使用：=IF(D2<0,REPT("n",-ROUND(D2,0)),"")G2中使用：=A2H2中使用：=IF(D2>0,REPT("n",ROUND(D2,0)),"")设置字体格式为Wingdings字体。虽然用的是字符N，但是设置了字体格式后，它自动转换为一个小方格。删除额外的空格和非打印字符Trim:删除数据前后的所有空格，用一个空格替换多个空格的内部字符串CLEAN：删除字符串中的所有非打印字符。例如：=trim(“thisisabook!”)结果为：thisisabook！计算字符串中的字符。（len()函数）=len(A1)注意：计算字符数时空格也要作为字符。下面的公式是返回A1:A3范围内的字符总数。=len(A1)+len(A2)+len(A3)不能使用=LEN(A1:A3)。这将返回不正确的结果。模拟数据表举例LENB返回文本字符串中用于代表字符的字节数。要点

函数LEN面向使用单字节字符集(SBCS)的语言，而函数LENB面向使用双字节字符集(DBCS)的语言。函数LEN始终将每个字符（不管是单字节还是双字节）按1计数。当启用支持DBCS的语言的编辑并将其设置为默认语言时，函数LENB会将每个双字节字符按2计数，否则，函数LENB会将每个字符按1计数。注意区分len(“字符a”)和lenb(“字符a”)ASC对于双字节字符集(DBCS)语言,将全角（双字节）字符更改为半角（单字节）字符。语法ASC(text)Text

为文本或对包含要更改文本的单元格的引用。如果文本中不包含任何全角字母，则文本不会更改。示例=ASC("EXCEL")

等于“EXCEL”WIDECHAR语法：WIDECHAR(text)文本

为文本或对包含要更改文本的单元格的引用。如果文本中不包含任何半角英文字母或片假名，则文本不会更改。示例=WIDECHAR(“EXCEL”)等于“ＥＸＣＥＬ”改变文本大小写三个改变文本大小写的函数(仅针对字母)：UPPER：将文本全部转换成大写。LOWER：将文本全部转换成小写。PROPER:将文本转换成”适当”的大小写。(每个词的首字母大写)从字符串中提取字符EXCEL可以提供几种函数来提取字符LEFT：从一个字符串的起始返回确定数量的字符。RIGHT：从字符串结尾返回确定数量的字符。MID：在字符串中间的任意位置上返回确定数量的字符。注意分列的使用模拟表left生产管理PMC运用Left双色球号码表制作通过左侧的金额填充数字到右侧对应的数据中模拟表人民币数字提取=LEFT(RIGHT(TEXT($A2/1%,"￥0;￥-0;￥0;"),12-COLUMN(A1)))PHONETIC函数的妙用我们在连接字符串时常使用&连接符或CONCATENATE函数，但是遇到字符串较多的情况下往往这两个办法都比较复杂，甚至无法实现。问题：连接A1:A100所有字符串方法1：=A1&A2&A3&...&A100或者=CONCATENATE(A1,A2,...,A100)点评：单元格较少的时候此法较为方便，在单元格较多的情况下则显得笨拙，甚至因为公式字符长度限制无法实现。练习1、假设A1中存放”

thiscomputerismine”，如果要提取”this”

应该在B1中如何输入公式，提取”computer”呢？2、A1中包含有FIRSTQUARTER。要得到结果Firstquarter。公式如何写？=LEFT(TRIM(A1),4)=PROPER(LEFT(A1,5))&LOWER(RIGHT(A1,8))=MID(TRIM(A1),6,8)方法2：用剪贴板复制粘贴，替换换行符的技巧点评：对于连续单元格操作较为方便快捷。但其缺点有二：一个是结果无法更新，数据有变动就需要重新操作；二是对于不连续区域难以实现。方法3：VBA点评：灵活多变，可按自己意愿进行连接。缺点是连接时需要启用宏，而且不熟悉VBA的朋友往往无法独自完成。以上总结了连接字符串的一些常用方法方法4：PHONETIC函数连接PHONETIC函数本来是MS设计为日文提取日文注音的函数，但它还有一个特殊的作用。=PHONETIC(A1:A100)PHONETIC函数可以对除纯数据、日文外的所有字符进行连接。注意：phonetic函数还有一个好处就是可以自动连接字符，屏蔽数字和空格。模拟数据表判断A1：C3中的文本是否完全一致？REPT(A1,9)=PHONETIC(A1:C3){=AND(A1:G3=A1)}Phonetic函数只针对文本替换文本SUBSTITUTE函数：替换字符串中的具体的文本。如果知道被替换的字符是什么，而不知道位置，可以使用此函数。区分大小写。例如：20102015要替换里面的1为x。=substitute(“20102015”,”1”,”x”)利用substitute删除字符串中的空格。例如：=substitute(“computer”,””,””)得到结果：compter举例Replace：替换字符串中具体位置的文本。如果知道替换文本的位置，而不知道实际为哪些文本，可以使用此函数。不区分大小写。例如：=replace(“part-455”,5,1,””)表示替换part-455字符串中第5个字符开始的1个字符。由于替换为空，则表示删除这个字符。在字符串中查找和搜索FIND：查找一个文本字符串中的子串，返回该子串的起始位置。可以确定开始查找的字符。使用这个函数比较文本时可以区分大小写。但不支持通配符比较。SEARCH：查找一个文本字符串中的子串，返回子串的起始位置。可以确定开始查找的字符位置。使用这个函数比较文本时不能区分大小写。但是可以进行通配符比较。模拟表find练习例如：=find(“m”,”BigMammaThornton”，1)得到返回值为7(区分大小写),这是字符串中第一次出现m的位置。=search(“m”,”BigMammaThornton”，1)得到返回值为5(不区分大小写),这是字符串中第一次出现m的位置。Search后可以接通配符。=search(“?g”,”BigMammaThornton”，1)得到返回值为2若要查找？和*本身，则在问号和星号前键入符号”~”即可。例如=SEARCH(“~?",”this?that”,1)模拟数据表.利用find函数分列T(value)Value

是要进行检验的值。注解如果值是文本或引用了文本，T将返回值。如果值未引用文本，T将返回空文本("")。1、计算单元格中具体字符出现的次数例1：计算Nothingcouldchangemydecision.字符串中字符”O”出现的次数。=len(a1)-len(substitute(a1,”o”,””))此公式先用substitute函数把指定字符(串)删除，然后用总字符长度减去新的字符串长度，即可得到结果。注意：它要区分大小写。如果字符串为NOthingcouldchangemydecision。则公式的结果为2。不区分大小写的查找方法1：=LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"o",""),"O",""))方法2：使用lower函数，先全部转换成小写再比较=LEN(A1)-LEN(SUBSTITUTE(LOWER(A1),"o",""))提取字符串中的第一个词

主要是确定第一个空格的位置。然后用它作为left函数的参数例如假设A1中包含字符串：thisisabook。提取this则：=left(a1,find(“”,a1)-1)但是有个问题，如果这个单元格中本身就只包含一个单词，则会报错。我们可以借助于IF函数处理：=if(iserr(find(“”,a1)),a1,left(a1,find(“”,a1)-1)提取字符串中的第二个词

=MID(A2,FIND("",A2)+1,FIND("*",SUBSTITUTE(A2,“","*",2))-FIND(“",A2))标记第2个空格的位置利用第二个空格和第一空格相减得到中间字符数提取字符串中的最后一个词

由于find只能从左向右工作。一次查找最后一个空格字符会出现问题。可用下列公式：=RIGHT(A2,LEN(A2)-FIND("*",SUBSTITUTE(A2,"","*",LEN(A2)-LEN(SUBSTITUTE(A2,"","")))))分析：1、先找出空格的个数:LEN(A2)-LEN(SUBSTITUTE(A2,"","")2、定位到最后一个空格,并更改空格符号为*

SUBSTITUTE(A2,“

”,“*”,空格个数)3、找出“*”在字符串中的位置

Find(“*”,修改最后的字符串)4、利用right函数提取数据从一个字符串中提取各列数据的通用公式=TRIM(MID(SUBSTITUTE($A2,“