函数中级应用笔记

目录TOC\o"1-5"\h\z第一讲数组基础 1什么叫数组 1数组长什么样 1数组里的符号 1数组的输入 1数组修改与删除 1数组维数 1数组维数的扩展 1数组的“降维” 1二维引用 1数组可以分为三种 2以普通公式输入法输入的数组公式结果不同 2识别公式是普通公式还是数组公式 2第二讲数组运算 2相同大小区域的数组运算 2数组公式（内存数组）的优势 2二维引用与单个单元格的数组运算 2二维引用与一维引用的数组运算 2一行多列与一列多行的数组运算 3计算前三大销量之和 4计算不重复个数 4第三讲基础统计（-）多条件计数 5基本用法 5\o"CurrentDocument"区分大小写: 6\o"CurrentDocument"条件内容变化 6\o"CurrentDocument"条件符号变化 7\o"CurrentDocument"区域变化 7第四讲基础统计（二）多条件求和 8 8区域变化 8\o"CurrentDocument"求和区域包含文本（2007、2010版整列引用问题） 9\o"CurrentDocument"第五讲基础统计（=）其他多条件统计 11\o"CurrentDocument"多条件平均 11多条件平均基本用法 12其它情况 12统计区域可以包含文本 12其他多条件平均的方法 13其他多条件统计 13多条件统计各种方法比较 13第六讲 时间与日期函数 18指定年份判断闰年 18\o"CurrentDocument"指定月份最大天数 19指定期间天数 20第七讲字符串函数 21提取数字 21案例1：按条件组合数字 22案例2:数字分列 22其他数字截取方法 23\o"CurrentDocument"SUBSTITUTE函数 25第八讲文本函数TEXT 25\o"CurrentDocument"Text的条件格式 25TEXT在很大程度上可以取代IF 27\o"CurrentDocument"练习一：利用占位符插入字符 27\o"CurrentDocument"练习二：利用REPT函数构建重复占位符 27\o"CurrentDocument"练习三：根据身份证号提取出生日期 28\o"CurrentDocument"练习四：补位 28\o"CurrentDocument"练习五：万位分隔符 28\o"CurrentDocument"练习六：查找字符出现的位置 28练习七:反向查找 29练习八:中文转数字 29练习九:交叉合并 29第九讲区域不一致的数组（一）Frequency 30\o"CurrentDocument"Frequency的特点 30\o"CurrentDocument"Frequency的基本用法 30计算不重复个数 315. 计算连续出现的最大次数 32第十讲区域不一致的数组（二）Mmult 33MM函数的计算特点 33一行与一列 33一行与多列 34多行与一列 34一歹!|与一行 34\o"CurrentDocument"多行与多列 35\o"CurrentDocument"无法得出正确结果的分布 35MM函数的用处 35第十一讲：查找与引用函数（一）二维间接引用 361. 三个间接引用函数的共性 36\o"CurrentDocument"3. Index 36\o"CurrentDocument"直接引用与间接引用混用 37交集和并集 37\o"CurrentDocument"If/Choose 37连接字符串 3737\o"CurrentDocument"第十二讲：查找与引用函数（二）各种查找 38\o"CurrentDocument"单条件查找 38\o"CurrentDocument"If（{l,O}…结构 38\o"CurrentDocument"反向与错位单条件查找 39\o"CurrentDocument"多条件查找 40\o"CurrentDocument"一对多查找 40\o"CurrentDocument"查找指定单元格的位置 41\o"CurrentDocument"不规则转置 41不规则转置的延伸（隔列多行求最值） 43第十三讲：查找与引用函数（三）增强Excel技巧功能 43函数与数据透视表 44\o"CurrentDocument"函数与数据有效性 47函数与动态图表 50第一讲数组基础.什么叫数组数组就是一组数！很多数整齐有序的排列.数组长什么样口,2,3;4,5,6}.数组里的符号①一对半角大括号；②半角逗号：表示横向排列（分隔列数据）：③半角分号：表示纵向排列（分隔行数据）④先横后纵例如：ABC11232456Al：C2返回{1,2,3;4,5,6}注：帮助记忆（分行逗列）.数组的输入选取单元格区域，按CTRL+SHIFT+ENTER组合键注：必须在编辑状态下输入，用鼠标点下公示栏或键盘F2.数组修改与删除快速选取数组:可选中数组中任一单元格后CTRL+/,03、07版本还可以通过选中数组中任一单元格后按组合键，10版本还可以通过定位当前数组来选中整个数组.数组维数一维数组；二维数组；三维引用.数组维数的扩展①单个的数可以向纵横两个方向扩展②一行多列的数组可以纵向扩展（不可以横向扩展，即使是倍数个单元格也不行）③多行一列的数组可以横向扩展（不可以纵向扩展，即使是倍数个单元格也不行）④多行多列的数组无法扩展.数组的"降维”多维引用可以降成二维数组；没听过二维数组可以降为一维数组.二维引用①一行多列，一列多行称为一维引用，多行多列称为二维引用，一维引用是特殊的二维引用②二维引用分直接引用和间接引用. 数组可以分为三种①常量数组：②二维引用；③运算数组. 以普通公式输入法输入的数组公式结果不同①对于一列数据，结果放在不同行有不同的结果②对于一行数据，结果放在不同的列有不同的结果. 识别公式是普通公式还是数组公式①大括号不是绝对的标志②数组公式中每一个数都分别参与计算第二讲数组运算相同大小区域的数组运算例如：CDEFGH1JKLM81231020301040909456405060160250360选中K8:M9,输入C8:E9*G8:I9后按组合键注：相同大小区域的数组运算的运算规律是数组的相同位置的数参与运算，且返回结果区域的相对位置数组公式(内存数组)的优势①内存数组可以进行再运算②内存数组与公式参数的数组运算有区别，但习惯上都叫做数组公式例如：仁SUM(C8:E9*G8:I9)｝返回910严格说属于公式参数的数组参与运算二维引用与单个单元格的数组运算CDEGH1KLM2012310101010203021456101010405060选中K2O：M20,在其中输入｛=C2O:E21*G1O｝与仁C2O:E21*G2O:I21｝结果相同注:二维引用与单个单元格的数组运算返回二维引用中各个位置的数分别与单个单元格运算后所组成的二维引用二维引用与一维引用的数组运算例一：CDEGH1KLM251231020301040902645610203040100180选中K25:M26,输入仁C25:E26*G25:I25｝与仁C25:E26*G25:I26｝结果相同例二:CDEGH1KLM2812310101010203029456404040160200240选中K28:M29,输入｛=C28:E29*G28:G29｝与仁C28:E29*G28:I29｝结果相同注:①二维引用与一维多列的运算返回二维引用中的各列分别与对应的一行多列中的各列数字运算后所组成的与原二维引用位置相同的二维数组②二维引用与多行一列的运算返回二维引用中的各行分别与对应的多行一列中的各行数字运算后所组成的数组5.一行多列与一列多行的数组运算CDEGH1KLM33123101010102030341234040404080120选中K33:M34,输入｛=C33:E33*G33:G34｝与｛=C33:E34*G33:I34｝结果相同注：①一行多列与一列多行的数组运算与一列多行与一行多列的数组运算结果相同②一行多列与一列多行或一列多行与一行多列的运算等价于特殊的二维引用与一维引用的运算③不能扩展的方向数据个数必须一致才能进行数组运算，多出部分会变成错误值#N/A例：CDEFGHIJKLM3812310201040#N/A3940110104122040423#N/A434412310201040#N/A4545640100#N/A扩展错误造成的结果选中区域过大，多选中的区域也会显示#N/A,这只属于显示错误；不能扩展方向数据个数不同造成的#N/A是本身计算错误注：①只粘贴数值不粘贴格式与公式快捷键：CTRL+C与CTRL+V后，CTRL后V②ROW(1:10)与ROW(ALAIO)的区别是ROW(LIO)计算机需要运行计算1到10行的每个单元格，而ROW(A1:A10)计算机只运行计算1到10行的A列单元格，因此ROW(1:10)比较省字符，但是占用电脑内存，影响计算速度③二维数组只能扩展不能收缩④本节课例子只用了乘法，其他的四则运算同乘法规律相同⑤二维数组间的运算结果(单个单元格、一行多列、一列多行相当于特殊的二维数组)等价于参与运算的二维数组的能扩展的方向扩展成相同的行数与列数后再进行运算的结果

.计算前三大销量之和CDEFG54品名销量55甲52356乙59757丙578第一78558T589第二76459戊651第66260己648221161庚785221162辛64863壬76464癸662G61单元格中仁SUM(LARGE($D$55:$D$64,ROW(1:3)))}.计算不重复个数C68品名69戊70T71丙72甲73己74己75丙76己77戊78乙79己80T81戊如图所示，计算C列中不重复项个数，法1：{=SUM(1/COUNTIF(C69:C81,C69:C81))}法2：{=SUM(IF(MATCH(C69:C81,C69:C81,)=ROW(1:13),1))}注：①法2中MATCH最后的逗号不可省，若省代表模糊查找，只写逗号不加参数相当于省略了零0,代表精确查找②法2中IF省略第二个参数，则若不满足条件时返回FALSE③公式中省略标点符号相当于省略了一个参数，若有符号没有参数只是省略了一个数法3：{=COUNT(0/(MATCH(C69:C81,C69:C81,)=ROW(1:13)))}法4：{=SUM(1*(MATCH(C69:C81,C69:C81,)=ROW(1:13)))}法5：{=SUM(N(MATCH(C69:C81,C69:C81,)=ROW(1:13)))}注：法5中的N()函数返回转化为数值后的值，具体如下

数值或引用返回值数字该数字日期该日期的序列号TRUE1FALSE0错误值错误值其他值0第三讲基础统计（一）多条件计数基本用法语法结构：=SumProduct（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N））{=Sum（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N））}例子：EFG1JKL11员工姓名部门职级部门职级人数人数12赵一伤生产部X生产部X2213钱二败生产部Y生产部Y3314孙三毁生产部Y市场部X1115李四摧市场部X市场部Z1116周五输生产部Y人事部X1117吴六破生产部X18郑七灭市场部Z19王八衰人事部XK12中：=SUMPRODUCT(($F$12:$F$19=I12)*($G$12:$G$19=J12))L12中：{=SUM(($F$12:$F$19=I12)*($G$12:$G$19=J12))}计算原理：计算原理条件区域仁条件1条件区域2=条件2结果TRUETRUE1TRUEFALSE0TRUEFALSE0FALSE乘以TRUE0TRUEFALSE0TRUETRUE1FALSEFALSE0FALSETRUE0因条件区域1=条件1返回的是由TRUE和FALSE组成的数组，而sumproduct的参数要求是由数字组成的数组，因此2个条件之间不能用逗号，而一般通过条件之间相乘将逻辑值转化成数字，或者在其外套一个N函数、或者同1相乘（除卜或者加上（减去）0,但是这种方法没有直接相乘简洁（除非只有一个参数）。Sumproduct（true,false）返回的结果是错误值#value,完全无法计算，而su

mproduct({true,fake})或者sumproduct的参数是带true和false的单元格引用返回的结果是0；另sum(true,false)是可以计算的，结果是1,但若是sum({true,false})或者sum的参数是带true和false的单元格引用，则无法计算，结果是0。区分大小写:EFGIJK34员工姓名部门职级职级SumExact35赵一伤生产部XX8336钱二败生产部XX8537孙三毁生产部X38李四摧市场部X39周五输生产部X40吴六破生产部X41郑七灭市场部X42王八衰人事部XJ35单元格中：{=SUM(N($G$35:$G$42=I35))}K35单元格中：{=SUM(N(EXACT($G$35:$G$42,I35)))}条件内容变化数据源：EFG46员工姓名部门职级47赵一伤生产部X48钱二败生产部Y49孙三毁生产部Y50李四摧市场部X51周五输生产部Y52吴六破生产部X53郑七灭市场部Z54王八衰人事部X数组公式中内容的变化:IJK46部门职级J列公式47生产部X4849引用2{=SUM((F47:F54=I47)*(G47:G54=J47))}50常量2{=SUM((F47:F54="生产部)*(G47:G54=J47))}51混合2{=SUM((F47:F54=LEFT(I47,2)&"部”)*(G47:G54=J47))}52通配符0{=SUM((F47:F54="*部")*(G47:G54=J47))}53函数14{=SUM(ISNUMBER(FIND("SU",F47:F54))*(G47:G54=J47))}54函数24{=SUM((RIGHT(F47:F54尸部")*(G47:G54;J47))}

条件符号变化数据源：EFG58员工姓名部门工资59赵一伤生产部100060钱二败生产部110061孙三毁生产部120062李四摧市场部130063周五输生产部140064吴六破生产部150065郑七灭市场部160066王八衰人事部1700条件符合变化:IJK58部门工资J列公式59生产部120060生产部>12006162不等于3{=SUM(N(F59:F66<>I59))}63大于5{=SUM(N(G59:G66>J59))}64Countif5=COUNTIF(G59:G66,">1200")=COUNTIF(G59:G66,M>"&J59)=COUNTIF(G59:G66,J60)65>12000=SUM(N(G59:G66=J60))66">H&1200错误框=SUM(N(G59:G66">"&1200))区域变化数据源：EFG70部门一月工资二月工资71生产部1000170072生产部1100160073生产部1200150074市场部1300140075生产部1400130076生产部1500120077市场部1600110078人事部17001000

区域变化:IJK70部门工资J列公式71生产部12007273一月工资1{=SUM((E71:E78=I71)*(F71:F78=J71))}74二月工资1{=SUM((E71:E78=I71)*(G71:G78=J71))}75全部2{=SUM((E71:E78=I71)*(F71:F78=J71))+SUM((E71:E78=I71)*(G71:G78=J71))}76全部2{=SUM((E71:E78=I71)*(F71:G78=J71))}77Countifs#VALUE!=COUNTIFS(E71:E78,I71,F71:G78,J71)注：①自动计算改手动计算，03版在工具一一选项一一重新计算一一手动计算；07版在公式一一计算选项一一手动：手动计算时候按F9即可②03版不支持整行整列引用③其他多条件计数的方法：Countlfs、数据库函数、数据透视表第四讲基础统计（二）多条件求和.基本用法语法结构：=SumProduct（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N）*求和区域）{=Sum（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N）*求和区域）}计算原理、区分大小写、条件内容变化、条件符号变化同多条件计数相同.区域变化数据源EFGH40部门职级一月工资二月工资41生产部X1000170042生产部Y1100160043生产部Y1200150044市场部X1300140045生产部Y1400130046生产部X1500120047市场部Z1600110048人事部X17001000

区域变化:JKLM40部门职级一月工资K列及L列公式41生产部X>12004243一月工资25002500{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:G48)}{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J43))}44二月工资29002900{=SUM((E41:E48=J41)*(F41:F48=K41)*H41:H48)}{=SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J44))}45Sumifs2500#VALUE!=SUMIFS(G41:G48,E41:E48,J41,F41:F48,K41)=SUMIFS(G41:H48,E41:E48,J41,F41:F48,K41)46加条件4400=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*(G41:G48>1200)*G41:G48)+SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*H41:H48)47加条件4400=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*((G41:G48>1200)*G41:G48+H41:H48))48加条件4400=SUMPRODUCT((E41:E48=J41)*(F41:F48=K41)*(G41:H48>{1200,0})*G41:H48)注：sumifs必须每个参数的区域相同；加条件是指一月份工资大于1200的一月份工资和与二月工资和的合计；公式仁SUM((E41:E48=J41)*(F41:F48=K41)*G41:H48*(G40:H40=J44))｝理论分析：条件1-2条件3结果1TRUEFALSE100000000乘以等于00000110000000sum可以对区域(二维数组)求和，sumproduct也可以对区域(二维数组)求和，因此其条件参数与求和参数可以不是相同的大小，但必须不能出现计算导致的错误值：2003版本无法整行整列引用；.求和区域包含文本(2007、2010版整列引用问题)注：无论是EXCEL2003还是EXCEL2007其求和区域都不能有不能计算的文本例如下表所示数据源G列数字是文本格式EFGH65部门职级工资66生产部X1000100067生产部Y1100100068生产部Y1200100069市场部Xaaaa对G列进行求和SUM(G65：G68)结果返回0,因为G列数字是文本格式，在不改变其格式的

情况下可以进行求和的方法，即强制将文本型数字转成数值型数字的办法:方法公式结果双负号{=SUM(-G66:G68)}3300乘以1或者除以1{=SUM(1*G66:G68)}{=SUM(G66:G68/1)}3300加0或者减0{=SUM(G66:G68+0)}{=SUM(G66:G68-0)}33000& {=SUM(0&G66:G68)}0公式中本身有四则运算Value函数其他一些函数COUNTIF和SUMIF注：countif和sumif的条件区域是可以忽略数字的格式的，但是sumif的求和区域不可以(求和区域需是数字格式)例如=COUNTIF(G66:G68,G66)返回结果1,=SUMIF(G66:G68,G66)返回结果0,ffij=SUMIF(G66:G68,G66,H66:H68)jg0^M1000其他包含文本情况：JKL65部门职级66生产部X6768文本型1000{=SUM((E66:E68=J66)*(F66:F68=K66)*G66:G68)}69纯文本#VALUE!{=SUM((E66:E69=J66)*(F66:F69=K66)*G66:G69)}70不含表头1000{=SUM((E66:E68=J66)*(F66:F68=K66)*G66:G68)}71包含表头#VALUE!{=SUM((E65:E68=J66)*(F65:F68=K66)*G65:G68)}72IfError1000{=SUM(IFERROR((E65:E68=J66)*(F65:F68=K66)*G65:G68,0))}73空注：①整列引用时可以用iferror将错误值转换为0；②条件求和时(sum,sumif)求和区域含假空单元格不行，返回#VALUE,含有真空单元格行，含有空格不行，返回#VALUE；③countifs和sumifs的条件参数最多是127个；④其他多条件求和的方法：sumlfs、数据库函数、数据透视表第五讲基础统计(三)其他多条件统计.多条件平均源数据：DEFG5员工姓名部门职级工资6赵一伤生产部X10007钱二败生产部Y10018孙三毁生产部Y10029李四摧市场部X100310周五输生产部Y100411吴六破生产部X100512郑七灭市场部Z100613王八衰人事部X1007效果IJKLM5部门职级求和/计数平均平均6生产部X1002.5250.6251002.57生产部Y1002.333375.8751002.3338市场部X1003125.37510039市场部Z1006125.75100610人事部X1007125.8751007K6中公式：｛=SUM(($E$6:$E$13=I6)*($F$6:$F$13=J6)*$G$6:$G$13)/SUM(($E$6:$E$13=I6)*($F$6:$F$13=J6))｝L6中公式：｛=AVERAGE(($E$6:$E$13=I6)*($F$6:$F$13=J6)*$G$6:$G$13)｝M6中公式：｛=AVERAGE(IF(($E$6:$E$13=I6)*($F$6:$F$13=J6),$G$6:$G$13)))L6中公式结果不正确，现分析如下：条件结果乘以平均区域等于结果110001000010010010020010030010040110051005010060010070条件结果中数组公式｛=(E6:E13W6)*(F6:F13n6)｝；平均区域中数组公式｛=G6:G13｝；结果中数组公式｛=D17:D24*F17:F24｝,对结果列进行求平均数，平均数计算中的0算个数。M6中公式的计算原理分析:IF1000FALSEFALSEFALSEFALSE1005FALSEFALSE其中数组公式：IF(($E$6:$E$13W6)*($F$6:$F$13=J6),$G$6:$G$13)平均数计算中FALSE不算个数.多条件平均基本用法{=Average(lf«条件区域1=条件1)*(条件区域2=条件2)*…*(条件区域N=条件N),平均区域))}此结构也适用于多条件求和和多条件计数，不过多条件计数没有最后一个参数，或者最后一个参数为1..其它情况区分大小写、条件内容变化、条件符号变化、区域变化、整行整列引用的问题等用法同多条件计数和多条件求和：条件中区分大小写套EXACT函数条件内容变化条件可以是常量、单元格引用、函数、结合；通配符会返回错误结果条件符号变化等于、小于、大于、不等于、大于等于、小于等于……都可以使用区域变化区域的扩展必须遵循数组扩展的规律整行整列引用的问题03版不支持整行整列引用4.统计区域可以包含文本DF1K条件结果统计区域相乘IF(条件结果，统计区域)371■=>文本«=>#VALUE!文本380文本#VALUE!FALSE39010020FALSE40010030FALSE41010040FALSE42110051005100543010060FALSE44010070FALSEI列中{=D38:D45*F38:F45},因为统计区域中文本与条件结果的数字相乘后出现了错误值，sum或者average中的参数含有错误值时最终结果返回错误值；K列中{=IF(D38:D45,F38:F45)},IF条件函数，无论条件结果是0还是1,统计区域都不会出现错误值，然后average函数会忽略文本及FALSE进行求平均数注：①理在多条件求和中用IF函数避免了错误值的出现，相对于iferror还省略了字符②并不是所有的错误都可以避免：没有符合条件的会出错，因为0不能作为除数.其他多条件平均的方法Sumlfs除以Countlfs、Averagelfs,数据库函数、数据透视表；IF函数是多条件统计的通用格式.其他多条件统计可以分成2类：0不起作用的公式结构:仁统计函数（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N）*统计区域）｝例如Sum（求和）、Product（求乘积）、Max（正数当中求最大值）、Min负数当中求最小值）0起作用的公式结构：｛=统计函数（If（（条件区域1=条件1）*（条件区域2=条件2）*…*（条件区域N=条件N）,统计区域））｝例如Average（平均）、Max（负数当中求最大值）、Min（正数当中求最小值）、Stdev（方差）3）非函数方法：数据库函数、数据透视表7.多条件统计各种方法比较源数据：DEFG1J68部门销售员商品销量部门商品69一部A00001订书机10070一部订书机70二部A00002钢笔664071三部A00003钢笔1003072二部A00004笔记本1283073一部A00005订书机1262074四部A00006铅笔904075一部A00001订书机720076二部A00002钢笔719077三部A00003钢笔14720求满足条件的销量笔数、平均销量、最小销量、销量等法一：利用数据筛选功能法二:IJKLM71销售笔数销量平均销量最小销量723298909963.33720073乘3298900会影响结果不能用74套If3298909963.333720075*lfs3298909963.33376库函数3298909963.3337200

各单元格中公式:乘销售笔数J73{=SUM((D69:D77=I69)*(F69:F77=J69))}销量K73{=SUM((D69:D77=I69)*(F69:F77=J69)*G69:G77)}平均销量L730会影响结果不能用最小销量M730会影响结果不能用套IF销售笔数J74{=SUM(IF((D69:D77=I69)*(F69:F77=J69),1))}销量K74{=SUM(IF((D69:D77=I69)*(F69:F77=J69),G69:G77))}平均销量L74{=AVERAGE(IF((D69:D77=I69)*(F69:F77=J69),G69:G77))}最小销量M74{=MIN(IF((D69:D77=I69)*(F69:F77=J69),G69:G77))}*1FS销售笔数J75=COUNTIFS(D69:D77,I69,F69:F77,J69)销量K75=SUMIFS(G69:G77,D69:D77,I69,F69:F77,J69)平均销量L75=AVERAGEIFS(G69:G77,D69:D77,I69,F69:F77,J69)最小销量M75库函数销售笔数J76=DCOUNT(D68:G77,4,I68:J69)销量K76=DSUM(D68:G77,4,I68:J69)平均销量L76=DAVERAGE(D68:G77,4,I68:J69)最小销量M76=DMIN(D68:G77,4,I68:J69)法三：数据透视表选中源数据，如下图所示DFFG6统计（三）其他多条件统计■--t -r 二部A00004笔记本6720一部A00005笔记本6850■•部A00001订书机14420一Xtr—npA00002订书机6380-*Ar-mA00003铅笔14250二部A00004铅笔14780a2-r-354CCCC插入数据透视表，如下图所示选择放置数据透视表位置中选择现有工作表，在表中要放置透视表的位置选择一个单元格，作为透视表的左上角第一个单元格，然后确定，如下图所示选中部门、商品、销量并放置指定位置，如下图所示在以下区域间拖动字段在以下区域间拖动字段因为有销售笔数、销量、平均销量、最小销量四个统计值需要统计，因此要拖动四个销量到2数值栏中，如下图所示然后分别改下求和方式，左键选中要修改的“求和项:销量〃，选择“值字段设置"，如下图所示选择要添加到报表的学£上移(U)BB部门下移包消售员，商品移到头9)泊里移至末尾(D在以下区域间拖动字段V移或到报表言选y报表谛选局移动到行如筌逋移动到列标筌E够/到数值X!!除字绘U行标签 %值字段”⑼部门 ▼后品 ▼求襁倒!二一求和项消置3▼ 求和防销里2▼求和项:消里4▼——推迟布局更新在计算类型中选择计数，然后确定，如下图所示然后分别将其他的调整为求和项、然后分别将其他的调整为求和项、平均项、最小值项，如下图所示退出数据透视表字段列表后，效果如下所示数据部门退出数据透视表字段列表后，效果如下所示数据部门,商品▼计数项:销量求和项:销量3平均值项:销量2最小值项:销量4日二部笔记本1128301283012830钢笔21383069156640二部汇总326660888676640H三部钢笔2247501237510030三部汇总2247501237510030T四部铅笔1904090409040四部汇总1904090409040日一部订书机3298909963.37200一部汇总3298909963.37200总计99034010037.86640如果表格不够直观，可以选择部门，只保留"一部"，如下图所示 M部门m M部门m二部fWS)MWFSSKM)-二部汇忘-三部三部汇应-四部四部汇忘w一部一部汇应*5S(Y)■(^)-□BB同样选中商品，只保留"订书机"，效果如下图所示数据部门,T商品.T计数项:销量求和项:销量3平均值项:销量2最小值项:销量4-一部订书机329890996337200一部汇总3298909963.37200总计329890996337200选择数据透视表工具中的设计，如下图所示数据透at表工具则iSH-选择总计，选中其中对行和列禁用，选择分类汇总，选中其中的不显示分类汇总，如下图所示报震布局交行_J|对行和列自用（N）T在晶的底部显示所有分类工总面二|在络的及部显示所有分美工号。）（口工总中包含建选谀（Dj|仅对行启用报震布局交行_J|对行和列自用（N）T在晶的底部显示所有分类工总面二|在络的及部显示所有分美工号。）（口工总中包含建选谀（Dj|仅对行启用0数据部门.r商品K计数项:销量求和项:销量3平均值项:销量2最小值项销量4T一部订书机329890996337200J仅对列启用©最终效果图如下注：数据库函数和透视表对表格的要求较高：表格需要有一行表头，对应有各列记录内容，记录内容中不能有空单元格及非单元格。数据库函数还必须有辅助单元格来设置条件第六讲时间与日期函数.指定年份判断闰年DE5日期闰/平年61900/7/7平年72100/7/7平年82012/7/7闰年92013/7/7平:车102014/7/7平年112015/7/7平年源数据如上表所示1）利用日期本身的规则来判断①当年3月0日的日期是否是29DEGG中公式61900/7/7平年TRUE=DAY(DATE(YEAR(D6),3,0))=2972100/7/7平年FALSE=DAY(DATE(YEAR(D7),3,0))=2982012/7/7闰年TRUE=DAY(DATE(YEAR(D8),3,0))=2992013/7/7平年FALSE=DAY(DATE(YEAR(D9),3,0))=29102014/7/7平年FALSE=DAY(DATE(YEAR(D10),3,0))=29112015/7/7平年FALSE=DAY(DATE(YEAR(D11),3,0))=291900年是个特例，2月有29日但是不是闰年，因此用这个办法判断不准确②当年2月29日的文本格式是否能转成日期DEGG中公式61900/7/7平年60=-(YEAR(D6)&--"&2&"-"&29)72100/7/7平年#VALUE!=-(YEAR(D7)&”・”&2&”・"&29)82012/7/7闰年40968=-(YEAR(D8)&"-"&2&"-"&29)92013/7/7平年#VALUE!=-(YEAR(D9)&,,-"&2&M-n&29)102014/7/7平年#VALUE!=-(YEAR(D10)&"-"&2&M-M&29)112015/7/7平年#VALUE!=-(YEAR(D11)&"-"&2&"-"&29)2月没有29日的会变成错误值，但是也无法避免1900年的特例2）利用闰年规则来判断如下表所示：年份分别除以4、100、400后余数为0的个数为偶数个的是平年余数为01900200020122013除以41110除以1001100除以4000100结果平年闰年闰年平年

结果显示如下:DEM5日期闰/平年E列公式61900/7/7平年=IF(MOD(SUM(-(MOD(YEAR(D6),{4;100;400})=0)),2),"H^","¥^")72100/7/7平年=IF(MOD(SUM(-(MOD(YEAR(D7),{4;100;400})=0)),2),"闰年"，"平年")82012/7/7闰年=IF(MOD(SUMHMOD(YEAR(D8),{4;100；400})=0》,2)「闰年平年")92013/7/7平年=IF(MOD(SUMHMOD(YEAR(D9),{4;100；400})=0)),2)：闰年*"平年")102014/7/7平年=IF(MOD(SUMHMOD(YEAR(D10),{4;100;400})=0)),2)「闰年平年")112015/7/7平年=IF(MOD(SUM(-(MOD(YEAR(D11),{4;100;400))=0)),2),"闰年"，"平年").指定月份最大天数1）数组解法：DEF25月份最大天数E列公式26131{=COUNT(-(D25&H-"&ROW($1:$33)))}27228{=COUNT(-(D26&"-"&ROW($1:$33)))}28331{=COUNT(-(D27&"-"&ROW($1:$33)))}29430{=COUNT(-(D28&"-H&ROW($1:$33)))}30531{=COUNT(-(D29&"-"&ROW($1:$33)))}31630{=COUNT(-(D30&"-"&ROW($1:$33)))}32731{=COUNT(-(D31&"-"&ROW($1:$33)))}33831{=COUNT(-(D32&"-"&ROW($1:$33)))}34930{=COUNT(-(D33&"-"&ROW($1:$33)))}351031{=COUNT(-(D34&"-H&ROW($1:$33)))}361130{=COUNT(-(D35&"-"&ROW($1:$33)))}371231{=COUNT(-(D36&"-"&ROW($1:$33)))}分析：每个月最多31天，构建每个月的31天日期或更多日期，如果各天的日期存在则能转换为数字，如果不存在就会变成错误值，因此统计非错误值的数字个数就是天数2）下个月的第一天减1DEF25月份最大天数E列公式26131=DAY(DATE(YEAR(TODAY()),D26+1,0))27228=DAY(DATE(YEAR(TODAY()),D27+1,0))28331=DAY(DATE(YEAR(TODAY()),D28+1,0))29430=DAY(DATE(YEAR(TODAY()),D29+1,0))3)EoMonth解法DEF25月份最大天数E列公式26131=DAY(EOMONTH(DATE(YEAR(TODAY()),D26,1),0))27228=DAY(EOMONTH(DATE(YEAR(TODAY()),D27,1),0))28331=DAY(EOMONTH(DATE(YEAR(TODAY()),D28,1),0))29430=DAY(EOMONTH(DATE(YEAR(TODAY()),D29,1),0))还可以用lookup查找最后一个非错误值来得到当月有多少天

.指定期间天数如下表所示，求起止日期之间的指定日期天数（要求是动态的，若起止日期、条件发生变化，天数能自动计算）DEFGH150起止条件数组算法512011/3/12011/4/30星期日88528538利用数组方法计算（如下表）GHI50条件数组H列中公式51星期日8{=SUM(N(MOD(ROW(INDIRECT(D51&":"&E51)),7)=1))}528{=SUM(N(MOD(ROW(INDIRECT(D51&":"&E51)),7)=FIND(RIGHT(G51),H六日一二三四五＞1))}538{=SUM(N(TEXT(ROW(INDIRECT(D51&W:1,&E51))；AAAA")=G51))}注:返回一周的第几天除了用weekday还可以使用mod（,7）及FIND（RIGHT（G51二六日一二三四五"）；返回2个数之间的行可用indirect后用row；text可以通过第二个参数达到日期函数的目的利用算法计算（如下表）GII中公式50条件算法51星期日8=INT((WEEKDAY(D51-FIND(RIGHT(G51),"A日一二三四五H))+E51-D51)/7)分析公式如下:EFGHI67星期二9677368星期三9666469星期四9655570星期五9644671星期六9633772星期日8622173星期一86112小妖分析：F列是对应E列中的星期在时间段中出现的次数，星期六和星期日是交界处，因此只需用7乘以F71中的次数9就是整数63,63满足除以7取整后等于9的最小整数，即63再减小除以7取整后是8,满足F列中的规律，再同时间段天数进行对比，减去时间段天数60后，形成H列数组，H列数组经观察同WEEKDAY返回的结果是相同的，但是weekday返回的是升序的结果，而H列中是降序的，经观察起始日期向前减3天后对应H67中7,向前推4天对应H68中6,正好符合find函数返回的结果序列自我理解1（补头不动尾使其开始日期为所求星期）：某个时间段中某个星期出现的次数，即这个时间段的起始时间之前的、要求的星期数的第二天与这个时间段的终止时间之后的、要求的星期数的前一天之间的天数是7的多少倍数（例如上题求2011-3-1至2011-4-30之间的周日的个数，也就是求2011-3-1之前的最近一次周一的日期2011-2-28日至终止日期2011-4-30之后的最近一次周六的日期2011-4-30日之间的天数是7的多少倍数，就代表着2011-3-1至2011-4-30之间有多少个周日；此时间段的天数必定是7n+6天，而终止日期之后的最近一次要求的星期的前一天离终止日期的天数必定小于等于6,定小于7,因此可以只计算时间段的起始时间之前的最近一次要求的星期的第二天与终止时间之间的天数是7的多少倍即可)，对于上题，周1至周日为一个周期或者一个间距，有多少个这样的周期或者间距就有多少个周日；理解延伸2(断头不动尾使其开始日期为所求星期)：求某个时间段中的某个星期的出现次数，只需把此时间段开头的时间去除,使其第一天为要求的星期数，然后除以7查看倍数加1即可(类似植树问题)第七讲字符串函数1.提取数字1)数字字符串的前面:CD5030个030672.1平方72.171.2KG1.2D列中{=LEFT(C5,COUNT(-LEFT(C5,ROW($1:$10))))}中心思路：依次提取左边1:10个字符，然后判断数字位数，再用left提取本题还可以用lookup实现{=LOOKUP(99,--LEFT(C5,ROW(1:10)))},不过lookup提取不了。开头的数字字符串2)数字在字符串的中间：CDE15苹果10个101016第05节050517水4.5公斤4.54.5D列中仁MID(C15,MATCH(0,0*MID(C15,ROW($1:$10),1),),COUNT(-(0&MID(C15,ROW(INDIRECT("1:"&LEN(C15))),1))))}E歹lj中仁MID(C15,MATCH(0,0*MID(C15,ROW($1:$10),1),),COUNT(-(0&MID(C15,ROW($1:$9),1))卜9+LEN(C15)))中心思路：用MID分列后，用MATCH查找数字开始位数，用COUNT统计数字位数，最后用MID截取数字注意：①indirect可以构建区域，row可以和indirect构建数组；②处理小数点的方法是在其前面加上0后再用N函数、-、0+、1*等方法使其转换为数字。再用其他函数判断③本题字符串中没有负数，如果有负数，处理方法同小数点一样用indirect,或者用row(l:9)后减去9加上字符串长度(9减去字符串长度正好是多出的数字0的个数，用总数字个数减去多出的数字个数就是字符串中数字的位数，-(9-len())=-9+LEN()3)字符串中多组数字的提取CD15A1班30人13016只有1袋子3个1317共12斤06两1206D列中｛=SUM(RIGHT(SMALL(IF(ISERROR(1*MID(C25,ROW($1:$9),1)),,MID(C25,ROW($1:$9),1)+ROW($1:$9)/1%),ROW($1:$9)))*10A(9-ROW($1:$9)))}还可以用large®^[{=SUM(RIGHT(LARGE(IF(ISERROR(1*MID(C25,ROW($1:$9),1))„MID(C25,ROW($1:$9),1)+ROW($1:$9)/1%),ROW($1:$9)))*10A(ROW($1:$9)-1))}中心思路：用MID将字符串分列，将数字与文本区别(用IFERROR或者if(number)),在不改变数字特征的情况下对数字的前后顺序有所区别(用数字+row(l:9)/l%)，用small使数字在不改变序列的情况下连在一起，用right。提取数字(只提取1位，因为mid分列分的是1位)，用合并数字方法把数字合并(sum(数组*10N9-row(l:9))));此种方法缺点首先是无法判断小数点或者负数，其次当字符串中的第一个数字是0时候，提取不到第一个0(可以用if函数加个判断)此题还可以用LOOKUP来求解，暂不讲解D歹l^{=SUM(RIGHT(SMALL((C$46:C$54=C56)*(D$46:D$54+ROW($l:$9)/l%),ROW($l:$9)))*10A(9-ROW($l:$9))))E歹^{=LEFT(SUM((C$46:C$54=C56)*(D$46:D$54)*10"(9-ROW($1:$9))),COUNTIF(C$46:C$54,C56))}分析：E列中的使用条件是相同的条件(甲乙丙)是各自相连的，旦数字只能是一位.案例2：数字分列如下图所示，将数字按乘号分列C6741.7*28.5*21.636841.74*28.543*21.6469417*28.5*21.7解题如下:CDEF6741.7*28.5*21.6341.728.521.636841.74*28.543*21.6441.7428.54321.6469417*28.5*21.741728.521.7D67中公式(横拉下拉)=TRIM(MID(SUBSTITUTE($C67,"*",REPT("",99)),99*(COLUMN(A1)-1)+1,99))注意:此类型题(按符号分列)常用思路,将符号替换成99个空格(因为99是2位字符中的最大数)，然后依次在99*(N-1)+1位置截取99个空格(N是正整数)；因为把符号替换成了99个空格，因此截取第一段数字只需截取到99即可(99位数内肯定有且只有一段数和空格)，第99位必定是空格，并且从100位开始有99-len(第一段数字)个空格，从99*(N-1)+1位开始就有99*(N-1)-len(前N-1段数字)个空格，若出现第99*(N-1)+1位置正好在第N个字段中间，则99*(N-1)-len(前N-1段数字)必然小于0,因此只要前N-1段数字小于99即可，即只要字符串(不含符号)的位数小于替换的空格数此公式均可达到分列目的.其他数字微取方法例如，如下图所示，取出Al：A5单元格的数字，并求和。A1张'R102李四-5.23王小五89.544赵二小姐10565兰色幻想0.7651)错位截取法公式仁SUM(TEXT(RIGHT(A1:A5,COLUMN(A:L)),"#.000;-0.000;0;!0")*TEXT(RIGHT(A1:A5,COLUMN(A:L)+1),"!0；!0;!0;!1"))}具体步骤：第一步：巧妙利用TEXT函数，在格式代码中将文本数据强制显示为0；另外，利用TEXT函数特性对文本型数值的正负零进行格式化处理。{=TEXT(RIGHT(A1:A5,COLUMN(A:I)),"0.000;-0.000;0;!0")}第二步：利用错位法比第一步多截取一位字符，再将文本数据强制为1,其他数据为0,如此就找到了刚好变成文本数据的准确位置(即第二步区域中每行第一个1出现的位置。){=TEXT(RIGHT(A1:A5,COLUMN(A:I)+1),"!0;!0;!0;!1")}第三步：再利用两个区域数组相乘，将精确位置之外的1滤掉。从而达到将数字提取出来的效果。{=TEXT(RIGHT(A1:A5,COLUMN(A:L)),"0.000;-0.000;0;!0")*TEXT(RIGHT(A1:A5,COLUMN(A:L)+1),"!0;!0;!0；!1")}最后，将区域的数值相加，得到最后结果。{=SUM(TEXT(RIGHT(A1:A5,COLUMN(A:L)),"#.000;-0.000;0;!0")*TEXT(RIGHT(A1:A5,COLUMN(A:L)+1),"!0;!0;10;!1"»)2)MM+TEXT法公式：{=SUM卜-RIGHT(A1:A5,MMULT(--ISNUMBER(-TEXT(RIGHT(A1:A5,COLUMN(1:1)),"G/通用格式;G/通用格式;0；!A")),TRANSPOSE(COLUMN(1:1))a。)))}具体步骤：第一步：巧妙利用TEXT函数，在格式代码中将文本数据强制显示为A；仁TEXT(RIGHT(A1:A5,COLUMN(1:1)),"G/通用格式;G/通用格式;0;!A")}第二步：利用-操作让文本报错，再利用-ISNUMBER操作将数字置1,错误值置0。最终，变成MM函数需要的第一参数。{=--ISNUMBER卜TEXT(RIGHT(A1:A5,COLUMN(1:1)),"G/通用格式;G/通用格式;0;!A"))}第三、四步：根据MM函数的第一参数数组(5行9列)，构造第二参数(9行1列)，最终目的是将每行数据相加，得到数字位数。{=MMULT(-ISNUMBER(-TEXT(RIGHT(A1:A5,COLUMN(1:1))JG/通用格式;G/通用格式;0;!A")),TRANSPOSE(COLUMN(1:1))A0)}第五步：利用-RIGHT操作，将数字提取出来并转成数字，再用SUM求和。{=SUM(--RIGHT(A1:A5,MMULT(--ISNUMBER(-TEXT(RIGHT(A1:A5,COLUMN(1:1)),"G/通用格式;G/通用格式;0;!A")),TRANSPOSE(COLUMN(1:1))a。)))}3)略去TEXT函数，在第2种方法基础上，精简公式得出。其他思路一致。公式仁SUM(--RIGHT(A1:A5,MMUIT(TSNUMBER(-(RIGHT(A1:A5,COLUMN(1:1)))),ROW(1:256)"0)))}4)分层、排序、取数公式=SUM(-RIGHT(A1:A5,RIGHT(SMALL(LEN(TEXT(MID(A1:A5,COLUMN(A:CU),99),"G/通用格式;0;0;\0"))+100AROW(1:5),ROW(1:5)*99),2)))具体步骤：第一步：利用MID函数，按位取出单元格数据；{=MID(A1:A5,COLUMN(A:CU),99)}第二步：利用TEXT函数，将文本置0,文本型数字(正数)常规显示。仁TEXT(MID(Al:A5,C。LUMN(A:CU),99),"G/通用格式;0;0;\0,,)}第三步：利用LEN取数据长度。仁LEN(TEXT(MID(A1:A5,COLUMN(A:CU),99),"G/通用格式;0;0;\0"))}第四、五步：利用+10Arow(l:5)方法实现数据分层，并取第9,18,27,36,45。。。小数据，实现取出每一行最大数据的要求。{=SMALL(LEN(TEXT(MID(A1:A5,COLUMN(A:CU),99),"G/通用格式;0;0;\0"))+100AROW(1:5),ROW(1:5)*99)}第六、七步：两次RIGHT结束。{=SUM(-RIGHT(A1:A5,RIGHT(SMALL(LEN(TEXT(MID(A1:A5,COUJMN(A:CU),99),"G/通用格式;0;0;\0"))+100AROW(1:5),ROW(1:5)*99),2)))}5)利用FIND+3/17生成数字位置，通过分层、排序、取数，达到目的公式{=SUM(--RIGHT(A1:A5,LEN(A1:A5)-MOD(SMALL旧ND(COLUMN(A:J)-1,A1:A5&1/17)-1+ROW(1:5)/1%%%,10*(ROW(1:5)-1)+1),10A6)))}具体步骤第一步：利用FIND函数，在单元格区域中查找0~9，后接皿7(目的是生成包含小数点在内的0~9的字符串)容错。同时，返回0~9在单元格字符串内的位置，位置越靠前的数字，返回的数字越小。可据此，查找每行中最小的数字。{=FIND(COLUMN(A:J)-1,A1:A5&V17))第二、三步：分层，排序，取第1、11、21、31、41小的数字。{=SMALL(FIND(COLUMN(A:J)-1,A1:A5&V17)-1+ROW(1:5)/1%%%,10*(ROW(1:5)-1)+1)}第四、五步：还原数据，并返回数字所占位数。{=LEN(A1:A5)-MOD(SMALL(FIND(COLUMN(A:J)-1,A1:A5&V17)-1+ROW(1:5)/1%%%,10*(RO\A/(1:5)-1)+l),10A6))第六步：SUM、RIGHT结束。{=SUM(-RIGHT(A1:A5,LEN(A1:A5)-MOD(SMALL(FIND(COLUMN(A:J)-1,A1:A5&1/17)-1+ROW(1:5)/1%%%,10*(ROW(1:5)-1)+1),10A6))))6)公式比较取巧，利用替代、替换函数，将汉字，'R替换成空值，再将文本数值转成数值，求和计算。公式仁SUM(1*SUBSTITUTE(REPLACE(A1:A5,1,LENB(A1:A5)-LEN(A1:A5),""),"'R",""))}7)利用0〜9及小数点的CODE数值特性，将其他字符置0。公式仁SUM(-RIGHT(A1:A5,MMULT(-ISNUMBER(1/(ABS(CODE(MID(A1:A5,COLUMN(A:J),1))-51.5)<7)),ROW(1:10)A0)))}具体步骤：第一步{=TSNUMBER(1/(ABS(CODE(MID(A1:A5,COLUMN(A:J),1))-51.5)<7))}第二步仁MMULT(-ISNUMBER(V(ABS(CODE(MID(A1:A5,COLUMN(A:J),1))-51.5)<7)),ROW(1:10)AO)}第三步仁SUM(--RIGHT(A1:A5,MMULT(--ISNUMBER(1/(ABS(CODE(MID(A1:A5,COLUMN(A:J),1))-51.5)<7)),ROW(1:10)A0)))}其中"0〜9"、"!'、"，的code值在[45,57]区间内，所以数字的CODE值满足大于44.5,且小于57.5,数字的code值用A来代替则有44.5<A<57.5,由于此条件用函数表达比较麻烦，可通过绝对值来表达abs(A-X)«则X-Y=44.5,X+Y=57.5,解二元一次方程组，X=51.1,Y=7,因此有公式ABS(CODE(MID(A1:A5,COLUMN(A:J),1))-51.5)<75.SUBSTITUTE函数三个技巧:find查找不到指定文本后返回错误值，为了规避错误值，通常在查找的文本后加上指定的文本；如需要还可以再用substitute函数将这些文本替换掉；在不同的文本中查找不同指定条件在不同文本中的位置，可以将其统一为一种指定条件后查找；统计单个文本中指定条件出现的次数，可以通过substitute来将其替换，根据减少的字符数量来判断指定条件的个数第八讲文本函数TEXTText的条件格式Text的结构：text(值,格式)Text的格式：TEXT函数的条件格式基本与自定义格式一致①TEXT函数的第二个参数是文本，要用双引号:从自定义格式中复制代码到公式中后给代码加双引号②TEXT的中的格式代码可以在自定义格式的代码的基础上简化，简化后的代码也可以用在自定义格式中(就同录制宏相似，会出现多余的代码)③自定义格式颜色代码不能用于TEXT函数；例如在自定义中输入[<10][红色];[绿色],单元格中的数字会根据条件小于10的显示红色，大于10的显示绿色，但是此代码用于TEXT中不会改变数字的颜色(函数不能改变单元格的属性)④文本型数字在自定义格式中的表现与TEXT函数不同:例如文本型数字01235(数字前面加个；或者单元格格式为文本型)在C17单元格，输入公式TEXT(C17,"[DBNuml][$-804]G/通用格式")后结果显示一千二百三十五，但是通过在设置单元格格式自定义中输入[DBNuml][$-804]G/通用格

式，则结果不变，还是文本数字01235,如果是常规型或者是数字型则自定义格式和TEXT函数相同注：文本型数字转换技巧：复制空单元格后选择性粘贴，选择加后确定即可把文本型数字转换成数值型数字（有'符号的情况下也可以）2）在TEXT函数的条件格式中可以使用变量注:在自定义格式中写函数是无效的，无论在自定义格式中写什么都会被默认为文本结构一：text（数据源，"正值显示格式;负值显示格式;0值显示格式;文本显示格式"）例如将C列单元格内容按"正值显示2位小数，负值只显示横杠，0显示零，文本不显示”原则显C（元数据）D（结果）E（公式）231212.00=TEXT(C23,"0.00;-;^；")24-32.6-二TEXT(C24/0.00;・;零;”)250零=TEXT(C25,-0.00;-;零;")26二=TEXT(C26,"0.00;-;?;")结构二：text（数据源,"［条件1］显示格式;［条件2］显示格式;否则显示格式;文本显示格式"）例如将C列单元格内容按“小于等于10显示小于十，大于50显示大于五十，文本显示文本,其他显示空白”原则显示C（元数据）D（结果）E（公式）309小于十=TEXT（C30,"［＜=10］小于匕［＞50］大于五七文本"）3155大于五十=TEXT（C31,"［〈=10］小于十;［＞50］大于五十;;文本"）3245=TEXT（C32,"［＜=10］小于十;［＞50］大于五十;;文本"）33二文本=TEXT（C33,”［＜=10］小于十;［＞50］大于五十;;文本“）3）例如各式使用公式查找*号的位置CDE75A-1-1-11*50129=TEXT(FINDC75&"*"),"[>"&LEN(C75)&"]!0;0")76T-4-5-010=TEXT(FIND C76&"*"),"[>"&LEN(C76)&"]!0;0")77B-3-4-12*59=TEXT(FINDC77&"*"),"[>"&LEN(C77)&"]!0;0")78A-1-5-3*028=TEXT(FINDC78&”**)，”[>"&LEN(C78)&"]!0;0")4）部分符号在条件格式中的特殊意义注：有特殊意义的符号用!或'固定有特殊含义的字符：0代表占位符,#代表占位符,@代表文本占位符,D代表天数,M代表月，丫代表年,E代表四位数年,B代表特殊含义，.代表特殊含义如果要想显示上述文本，则必须在前面加上!或者'例1：C列中的数据按“小于等于10的显示10,,大于50的显示50,文本显示D,其他的显示空白CDE44910=TEXT(C44,,,[<=10]1!0;[>50]5\0;"""";!D")455550=TEXT(C45,"[<=10]1!0;[>50]5\1丁;!DH)4645=TEXT(C46,"[<=10]1!0;[>50]5\2;1,MW";!D")47三D=TEXT(C47,"[<=10]1!0;[>50]5\3;MMM";!D")例2：（设置上下限）以0.3和.5为界限，判断区间CDIEI

440.9000>0.5=TEXT(C52,"[>n&0.5&"]>!0!.5;[<"&0.3&n<!0!,3;!0!.3~!0!.5Z(Bj")450.40000.3~0.5之间二丁£*T953/[>"&0.5&»!0!.5;[<”&0.3&"]<!0!.3;!0!.3~!0!.5之间”)460.2000<0.3=TEXT(C54,"[>"&O.5&1>!O!,5;[<"&O.3&n<!O!,3;!O!.3~!O!.5±|0j")注：text的格式是文本，类似INDIRECT的参数，文本用双引号,与公式及引用链接用&,运算符号属于文本TEXT在很大程度上可以取代IF例1：90分以上为A,80分以上为B,其余为DDEFG60姓名语文IFTEXT61F82BB62G95AA63K75DD64L82BBF列公式：=IF(E61>=90,"A",IF(E61>=80,"B","D"))G列公式：=TEXT(E61,"[>90]A;[>80]!B;!D")例2：90分以上为A,80分以上为B,60分以上为C,其余为DDEHI60姓名语文IFTEXT61F82BB62G95AA63K75CC64L59DDH列中公式：=IF(E61>=90,"A",IF(E61>=80,"B",IF(E61>=60,"C","D")))I列中公式：=TEXT(E61,"[>90]A;[>80]!B;!"&TEXT(E61,"[>60]C;(D"))注：还有一个函数可以取代IF就是VLOOKUP函数；TEXT也可以嵌套例3：如果原数值大于99则不显示，否则显示原值用IF：=IF(原数值>99,"",原数值)用TEXT：=TEXT(原数值"v=99];")用text函数只需要输入一次原数值，而用IF需要输入两次原数值练习一：利用占位符插入字符CDE84200802032008-02-03=REPLACE(REPLACE(C84,5,；-"),8„"-")85200802032008-02-03=TEXT(C85fH0-00-00H)874702r47c02=TEXT(C87；r0c00")880618r6c18=TEXT(C88；rOcOO")89532r5c32=TEXT(C89,"r0c00")其中87至89行主要为间接引用作准备练习二：利用REPT函数构建重复占位符

CDE911234567891$2$3$4$5$6$7$8$9$=TEXT(C91,REPT("0$",9))92987654321102030405060708090=TEXT(C91,REPT("0!0",9))练习三：根据身份证号提取出生日期C DEF100身份证号码出生性别10614250272032599972-03-25男1074503221981122200121981-12-22男E列中公式：=TEXT(MID(C106,7,6+(LEN(C106)=18)*2),"#-00-00")*lF列中公式：=TEXT(-MMID(C106,9,9)J女;男")6,练习四：补位将"*"号前后的数字统一补足4位数，不足4位的前面补"0"CD111原格式公式1121*12*234*2540001*0012*0234*025411310*234*56*2110010*0234*0056*02111141*1*25*360001*0001*0025*0036D