excel函数使用技巧

excel中根据A列汉字自动在B列生成拼音字母的方法 下图所示的excel表格，A列是姓名列，根据A列的汉字姓名，自动在B列和C列分别自动生成姓名列的拼音字母。 　　下面是小编整理出来的详细的操作 步骤 新产品开发流程的步骤课题研究的五个步骤成本核算步骤微型课题研究步骤数控铣床操作步骤 ，分享给有需要的朋友。 　　第一步，由于Excel中没有现成的函数套用可以解决此问题，首先自定义一个函数。方法如下： 　　1. 运行“工具→宏→Visual Basic编辑器”命令（或者直接按“Alt+F11”组合键），进入Visual Basic编辑状态。 　　2. 运行“插入→模块”命令，插入一个新模块。再双击插入的模块，进入模块代码编辑状态。 　　3. 将以下的代码输入其中，保存，关闭Visual Basic编辑窗口，返回Excel编辑状态。 Function PinYin(Hz As String) Dim PinMa As String Dim MyPinMa As Variant Dim Temp As Integer, i As Integer, j As Integer PinMa = "a,20319,ai,20317,an,20304,ang,20295,ao,20292," PinMa = PinMa & "ba,20283,bai,20265,ban,20257,bang,20242,bao,20230,bei,20051,ben,20036,beng,20032,bi,20026,bian,20002,biao,19990,bie,19986,bin,19982,bing,19976,bo,19805,bu,19784," PinMa = PinMa & "ca,19775,cai,19774,can,19763,cang,19756,cao,19751,ce,19746,ceng,19741,cha,19739,chai,19728,chan,19725,chang,19715,chao,19540,che,19531,chen,19525,cheng,19515,chi,19500,chong,19484,chou,19479,chu,19467,chuai,19289,chuan,19288,chuang,19281,chui,19275,chun,19270,chuo,19263,ci,19261,cong,19249,cou,19243,cu,19242,cuan,19238,cui,19235,cun,19227,cuo,19224," PinMa = PinMa & "da,19218,dai,19212,dan,19038,dang,19023,dao,19018,de,19006,deng,19003,di,18996,dian,18977,diao,18961,die,18952,ding,18783,diu,18774,dong,18773,dou,18763,du,18756,duan,18741,dui,18735,dun,18731,duo,18722," PinMa = PinMa & "e,18710,en,18697,er,18696," PinMa = PinMa & "fa,18526,fan,18518,fang,18501,fei,18490,fen,18478,feng,18463,fo,18448,fou,18447,fu,18446," PinMa = PinMa & "ga,18239,gai,18237,gan,18231,gang,18220,gao,18211,ge,18201,gei,18184,gen,18183,geng,18181,gong,18012,gou,17997,gu,17988,gua,17970,guai,17964,guan,17961,guang,17950,gui,17947,gun,17931,guo,17928," PinMa = PinMa & "ha,17922,hai,17759,han,17752,hang,17733,hao,17730,he,17721,hei,17703,hen,17701,heng,17697,hong,17692,hou,17683,hu,17676,hua,17496,huai,17487,huan,17482,huang,17468,hui,17454,hun,17433,huo,17427," PinMa = PinMa & "ji,17417,jia,17202,jian,17185,jiang,16983,jiao,16970,jie,16942,jin,16915,jing,16733,jiong,16708,jiu,16706,ju,16689,juan,16664,jue,16657,jun,16647," PinMa = PinMa & "ka,16474,kai,16470,kan,16465,kang,16459,kao,16452,ke,16448,ken,16433,keng,16429,kong,16427,kou,16423,ku,16419,kua,16412,kuai,16407,kuan,16403,kuang,16401,kui,16393,kun,16220,kuo,16216," PinMa = PinMa & "la,16212,lai,16205,lan,16202,lang,16187,lao,16180,le,16171,lei,16169,leng,16158,li,16155,lia,15959,lian,15958,liang,15944,liao,15933,lie,15920,lin,15915,ling,15903,liu,15889,long,15878,lou,15707,lu,15701,lv,15681,luan,15667,lue,15661,lun,15659,luo,15652," PinMa = PinMa & "ma,15640,mai,15631,man,15625,mang,15454,mao,15448,me,15436,mei,15435,men,15419,meng,15416,mi,15408,mian,15394,miao,15385,mie,15377,min,15375,ming,15369,miu,15363,mo,15362,mou,15183,mu,15180," PinMa = PinMa & "na,15165,nai,15158,nan,15153,nang,15150,nao,15149,ne,15144,nei,15143,nen,15141,neng,15140,ni,15139,nian,15128,niang,15121,niao,15119,nie,15117,nin,15110,ning,15109,niu,14941,nong,14937,nu,14933,nv,14930,nuan,14929,nue,14928,nuo,14926," PinMa = PinMa & "o,14922,ou,14921," PinMa = PinMa & "pa,14914,pai,14908,pan,14902,pang,14894,pao,14889,pei,14882,pen,14873,peng,14871,pi,14857,pian,14678,piao,14674,pie,14670,pin,14668,ping,14663,po,14654,pu,14645," PinMa = PinMa & "qi,14630,qia,14594,qian,14429,qiang,14407,qiao,14399,qie,14384,qin,14379,qing,14368,qiong,14355,qiu,14353,qu,14345,quan,14170,que,14159,qun,14151," PinMa = PinMa & "ran,14149,rang,14145,rao,14140,re,14137,ren,14135,reng,14125,ri,14123,rong,14122,rou,14112,ru,14109,ruan,14099,rui,14097,run,14094,ruo,14092," PinMa = PinMa & "sa,14090,sai,14087,san,14083,sang,13917,sao,13914,se,13910,sen,13907,seng,13906,sha,13905,shai,13896,shan,13894,shang,13878,shao,13870,she,13859,shen,13847,sheng,13831,shi,13658,shou,13611,shu,13601,shua,13406,shuai,13404,shuan,13400,shuang,13398,shui,13395,shun,13391,shuo,13387,si,13383,song,13367,sou,13359,su,13356,suan,13343,sui,13340,sun,13329,suo,13326," PinMa = PinMa & "ta,13318,tai,13147,tan,13138,tang,13120,tao,13107,te,13096,teng,13095,ti,13091,tian,13076,tiao,13068,tie,13063,ting,13060,tong,12888,tou,12875,tu,12871,tuan,12860,tui,12858,tun,12852,tuo,12849," PinMa = PinMa & "wa,12838,wai,12831,wan,12829,wang,12812,wei,12802,wen,12607,weng,12597,wo,12594,wu,12585," PinMa = PinMa & "xi,12556,xia,12359,xian,12346,xiang,12320,xiao,12300,xie,12120,xin,12099,xing,12089,xiong,12074,xiu,12067,xu,12058,xuan,12039,xue,11867,xun,11861," PinMa = PinMa & "ya,11847,yan,11831,yang,11798,yao,11781,ye,11604,yi,11589,yin,11536,ying,11358,yo,11340,yong,11339,you,11324,yu,11303,yuan,11097,yue,11077,yun,11067," PinMa = PinMa & "za,11055,zai,11052,zan,11045,zang,11041,zao,11038,ze,11024,zei,11020,zen,11019,zeng,11018,zha,11014,zhai,10838,zhan,10832,zhang,10815,zhao,10800,zhe,10790,zhen,10780,zheng,10764,zhi,10587,zhong,10544,zhou,10533,zhu,10519,zhua,10331,zhuai,10329,zhuan,10328,zhuang,10322,zhui,10315,zhun,10309,zhuo,10307,zi,10296,zong,10281,zou,10274,zu,10270,zuan,10262,zui,10260,zun,10256,zuo,10254" MyPinMa = Split(PinMa, ",") For i = 1 To Len(Hz) Temp = Asc(Mid(Hz, i, 1))     If Temp < 0 Then       Temp = Abs(Temp)       For j = 791 To 1 Step -2           If Temp <= Val(MyPinMa(j)) Then               PinYin = PinYin & MyPinMa(j - 1) & " "               Exit For           End If       Next     End If Next PinYin = Trim(PinYin) End Function 　　第二步，在空白列，比如F2单元格，输入公式：=PinYin(A2)，就可以在F2单元格得到A2单元格汉字的拼音。然后下拉复制完成A列所有的汉字转拼音。 　　提示：公式中的“PinYin”就是第一步自定义函数的名称。 　　第三步，在B2单元格输入公式：=LEFT(F2,FIND(" ",F2)-1)，就可以提取出F2单元格中的第一个汉字的拼音。然后下拉复制完成A列中汉字“姓”的拼音提取。 　　第四步，在C2单元格输入公式：=TRIM(SUBSTITUTE(F2,B2,""))，即可得到剩余的“名”字部分的汉字的拼音。下拉复制公式。 　　第五步，如果需要拼音的首字母大写，可以再用PROPER函数来转换一下。最终完成的效果如下图所示。 Excel2007函数公式实例集   2010-01-28 13:02:00|  分类： Excel公式 |  标签： |字号大中小 订阅   Excel2007函数公式收集了688个实例，涉及到137个函数、7个行业、41类用途，为大家提供一个参考，拓展思路的机会。公式由{}包括的为数组公式，在复制粘贴到单元后先去掉{}然后按住Shift键+Ctrl键再按Enter键，自动生成数组公式。 对三组生产数据求和:=SUM(B2:B7,D2:D7,F2:F7) 对生产表中大于100的产量进行求和:{=SUM((B2:B11>100)*B2:B11)} 对生产表大于110或者小于100的数据求和:{=SUM(((B2:B11<100)+(B2:B11>110))*B2:B11)} 对一车间男性职工的工资求和:{=SUM((B2:B10="一车间")*(C2:C10="男")*D2:D10)} 对姓赵的女职工工资求和:{=SUM((LEFT(A2:A10)="赵")*(C2:C10="女")*D2:D10)} 求前三名产量之和:=SUM(LARGE(B2:B10,{1,2,3})) 求所有工作表相同区域数据之和:=SUM(A组:E组!B2:B9) 求图书订购价格总和:{=SUM((B2:E2=参考价格!A$2:A$7)*参考价格!B$2:B$7)} 求当前表以外的所有工作表相同区域的总和:=SUM(一月:五月!B2) 用SUM函数计数:{=SUM((B2:B9="男")*1)} 求1累加到100之和:{=SUM(ROW(1:100))} 多个工作表不同区域求前三名产量和:{=SUM(LARGE(CHOOSE({1,2,3,4,5},A组!B2:B9,B组!B2:B9,C组!B2:B9,D组!B2:B9,E组!B2:B9),ROW(1:3)))} 计算仓库进库数量之和:=SUMIF(B2:B10,"=进库",C2:C10) 计算仓库大额进库数量之和:=SUMIF(B2:B8,">1000") 对1400到1600之间的工资求和:{=SUM(SUMIF(B2:B10,"<="&{1400,1600})*{-1,1})} 求前三名和后三名的数据之和:=SUMIF(B2:B10,">"&LARGE(B2:B10,4))+SUMIF(B2:B10,"<"&SMALL(B2:B10,4)) 对所有车间人员的工资求和:=SUMIF(A2:A10,"?车间",C2) 对多个车间人员的工资求和:=SUMIF(A2:A10,"??车间*",C2) 汇总姓赵、刘、李的业务员提成金额:=SUM(SUMIF(A2:A10,{"赵","刘","李"}&"*",C2:C10)) 汇总鼠标所在列中大于600的数据:=SUMIF(INDIRECT("R2C"&CELL("col")&":R8C"&CELL("col"),FALSE),">600") 只汇总60～80分的成绩:=SUMIFS(B2:B10,B2:B10,">=60",B2:B10,"<=80") 汇总三年级二班人员迟到次数:=SUMIFS(D2:D10,B2:B10,"三年级",C2:C10,"二班") 汇总车间女性人数:=SUMIFS(C2:C11,A2:A11,"*车间",B2:B11,"女") 计算车间男性与女性人员的差:=SUM(SUMIFS(C2:C11,B2:B11,{"女","男"},A2:A11,"*车间")*{-1,1}) 计算参保人数:=SUMPRODUCT((C2:C11="是")*1) 求25岁以上男性人数:=SUMPRODUCT((B2:B10="男")*1,(C2:C10>25)*1) 汇总一班人员获奖次数:=SUMPRODUCT((B2:B11="一班")*C2:C11) 汇总一车间男性参保人数:=SUMPRODUCT((A2:A10&B2:B10&C2:C10="一车间男是")*1) 汇总所有车间人员工资:=SUMPRODUCT(--NOT(ISERROR(FIND("车间",A2:A10))),C2:C10) 汇总业务员业绩:=SUMPRODUCT((B2:B11={"江西","广东"})*(C2:C11="男")*D2:D11) 根据直角三角形之勾、股求其弦长:=POWER(SUMSQ(B1,B2),1/2) 计算A1：A10区域正数的平方和:{=SUMSQ(IF(A1:A10>0,A1:A10))} 根据二边长判断三角形是否为直角三角形:=CHOOSE((SUMSQ(MAX(B1:B3))=SUMSQ(LARGE(B1:B3,{2,3})))+1,"非直角","直角") 计算1到10的自然数的积:=FACT(10) 计算50到60之间的整数相乘的结果:=FACT(60)/FACT(49) 计算1到15之间奇数相乘的结果:=FACTDOUBLE(15) 计算每小时生产产值:=PRODUCT(C2:E2) 根据三边求普通三角形面积:=(PRODUCT(SUM(B1:B3)/2,SUM(B1:B3)/2-LARGE(B1:B3,{1,2,3})))^0.5 根据直角三角形三边求三角形面积:=PRODUCT(LARGE(B1:B3,{2,3}))/2 跨表求积:=PRODUCT(产量表:单价表!B2) 求不同单价下的利润:{=MMULT(B2:B10,G2:H2)*25%} 制作中文九九乘法表:=COLUMN()&"*"&ROW()&"="&MMULT(ROW(),COLUMN()) 计算车间盈亏:=SUM(MMULT((B3:E5>0)*B3:E5,{1;1;1;1}),MMULT((B3:E5<0)*B3:E5,{1;1;1;1})) 计算各组别第三名产量是多少:{=MAX(MMULT(COLUMN(A:E)^0,B2:G6))} 计算C产品最大入库量:{=MAX(MMULT(N(A2:A11="C"),TRANSPOSE((B2:B11)*(A2:A11="C"))))} 求入库最多的产品数量:{=MAX(MMULT(TRANSPOSE((B2:B11)*(A2:A11={"A","B","C","D"})),(A2:A11={"A","B","C","D"})*1))} 计算累计入库数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11)} 计算每日库存数:{=MMULT(N(ROW(2:11)>=TRANSPOSE(ROW(2:11))),B2:B11-C2:C11)} 计算A产品每日库存数:{=MMULT(N(ROW(2:17)>=TRANSPOSE(ROW(2:17))),(B2:B17="A")*(C2:C17-D2:D17))} 求第一名人员最多有几次:{=MAX(MMULT(N(B2:B7=TRANSPOSE(B2:B7)),ROW(2:7)^0))} 求几号选手选票最多:{=RIGHT(MAX(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)*100+B2:B10))} 总共有几个选手参选:{=SUM(1/(MMULT(N(B2:B10=TRANSPOSE(B2:B10)),ROW(2:10)^0)))} 在不同班级有同名前提下计算学生人数:{=SUM(1/MMULT(N(A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17)),ROW(2:17)^0))} 计算前进中学参赛人数:{=SUM(IFERROR(1/MMULT(N((A2:A17&B2:B17&C2:C17=TRANSPOSE(A2:A17&B2:B17&C2:C17))*(A2:A17="前进中学")),ROW(2:17)^0),0))} 串联单元格中的数字:{=MMULT(10^(COLUMNS(B:K)-COLUMN(C:L)),TRANSPOSE(B2:K2))}或=SUMPRODUCT(B2:K2,10^(COLUMNS(B:K)-COLUMN(B:K)-1)) 计算达标率:{=MMULT(TRANSPOSE(N(A2:A11<=(B2:B11))),ROW(2:11)^0)/ROWS(2:11)} 计算成绩在60-80分之间合计数与个数:求和{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)*B2:B11),ROW(2:11)^0)}，求个数{=MMULT(TRANSPOSE((B2:B11>60)*(B2:B11<80)),ROW(2:11)^0)} 汇总A组男职工的工资:{=MMULT(TRANSPOSE(N(B2:B11&C2:C11="男A组")*D2:D11),ROW(2:11)^0)} 计算象棋比赛对局次数l:=COMBIN(B1,B2) 计算五项比赛对局总次数:{=SUM(COMBIN(B2:B5,2))} 预计所有赛事完成的时间:=COMBIN(B1,B2)*B3/B4/60 计算英文字母区分大小写做密码的组数:=PERMUT(B1*2,B2) 计算中奖率:=TEXT(1/PERMUT(B1,B2),"0.00%") 计算最大公约数:=GCD(B1:B5) 计算最小公倍数:=LCM(B1:B5) 计算余数:=MOD(A2,B2) 汇总奇数行数据:=SUMPRODUCT(MOD(ROW(2:13),2)*C2:C13) 根据单价数量汇总金额:=SUMPRODUCT(MOD(COLUMN(A:I),2)*A2:I2,(MOD(COLUMN(B:J),2)=0)*B2:J2) 设计工资条:=IF(MOD(ROW(),3)=1,单行表头工资明细!A$1,IF(MOD(ROW(),3)=2,OFFSET(单行表头工资明细!A$1,ROW()/3+1,0),"")) 根据身份证号计算性别:=IF(MOD(MID(B2,15,3),2),"男","女") 每隔4行合计产值:=IF(MOD(ROW(),5)=1,SUM(OFFSET(F2,-4,,4,)),D2*E2) 工资截尾取整:=B2+MOD(一月!B2,10)-MOD(B2+MOD(一月!B2,10),10) 汇总3的倍数列的数据:{=SUM(IF(MOD(COLUMN(A:I),3)=0,A2:I10))} 将数值逐位相加成一位数:=IF(A2=0,0,MOD(A2-1,9)+1) 计算零钞:5角=INT(MOD(SUM(B2:B10),1)/0.5);2角=INT(MOD(MOD(SUM(B2:B10),1),0.5)/0.2);1角=MOD(MOD(MOD(SUM(B2:B10),1),0.5),0.2)/0.1 秒与小时、分钟的换算:=QUOTIENT(MOD($A2,IF(COLUMN()=2,A2+1,60^(3-COLUMN(A:A)+1))),60^(3-COLUMN(A:A))) 生成隔行累加的序列:=QUOTIENT(ROW()+1,2) 根据业绩计算业务员奖金:=CHOOSE(MIN(QUOTIENT(B2,10000)+1,6),0,3%,5%,7%,9%,11%)*B2 计算预报温度与实际温度的最大误差值:{=MAX(ABS(C2:C8-B2:B8))} 计算个人所得税:=ROUND(0.05*SUM(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}+ABS(H2-1600-{0,500,2000,5000,20000,40000,60000,80000,100000}))/2,0) 产生100到200之间带小数的随机数:=RAND()*(200-100)+100 产生ll到20之间的不重复随机整数:{=RANK(A2:A11,A2:A11)+10} 将20个学生的考位随机排列:{=INDEX(A$2:A$11,RANK(H2:H11,H2:H11))} 将三个学校植树人员随机分组:=OFFSET(A$1,RANK(G2,G$2:G$11),)&":"&OFFSET(B$1,RANK(G2,G$2:G$11),)&":"&OFFSET(C$1,RANK(G2,G$2:G$11),) 产生-50到100之间的随机整数:=RANDBETWEEN(-50,100) 产生1到100之问的奇数随机数:{=INDEX(IF(MOD(ROW(1:100),2),ROW(1:100),ROW(1:100)-1),RANDBETWEEN(1,100))} 产生1到10之间随机不重复数:{=LARGE(IF(COUNTIF(A$1:A1,ROW($1:$10))=0,ROW($1:$10)),RANDBETWEEN(1,12-ROW()))} 根据三角形三边长求证三角形是直角三角形:=IF(POWER(MAX(B1:B3),2)=SUM(POWER(LARGE(B1:B3,{2,3}),2)),"是","不是") 计算Al:A10区域开三次方之平均值:{=AVERAGE(POWER(A1:A10,1/30))} 计算Al:A10区域倒数之积:{=PRODUCT(POWER(A1:A10,-1))} 根据等边三角形周长计算面积:=SQRT(B1/2*POWER(B1/2-B1/3,3)) 抽取奇数行姓名:=INDEX(B:B,ODD(RANDBETWEEN(1,ROWS(1:12)-1))) 统计A1:B10区域中奇数个数:=SUMPRODUCT(N(ODD(A1:B10)=(A1:B10))) 统计参考人数:=SUMPRODUCT((EVEN(COLUMN(A1:J12))=COLUMN(A1:J12))*(MOD(ROW(A1:J12),3)=1)*(A1:J12<>"")) 计算A1:B10区域中偶数个数:=SUMPRODUCT(N(EVEN(A1:B10)=(A1:B10))) 合计购物金额、保留一位小数:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),1) 将每项购物金额保留一位小数再合计:=SUMPRODUCT(TRUNC(B2:B10*C2:C10,1)) 将金额进行四舍六入五单双:=IF((A2-TRUNC(A2,1))<=0.04,TRUNC(A2,1),IF((A2-TRUNC(A2,1))>=0.06,TRUNC(A2,1)+0.1,TRUNC((TRUNC(A2,1)+0.1)/2,1)*2)) 根据重量单价计算金额，结果以万为单位:=TRUNC(SUMPRODUCT(B2:B10,C2:C10),-4)/10000 计算年假天数:=TRUNC((TODAY()-B2)*((TODAY()-B2)>=365)/365*5) 根据上机时间计算上网费用:=(TRUNC(B2)+(B2-TRUNC(B2)>=0.5))*1.5+(MOD(B2,1)<0.5) 将金额见角进元与见分进元:见分进元=CEILING(TRUNC(A2,2),1);见角进元=CEILING(TRUNC(A2,1),1) 分别统计收支金额并忽略小数:收入合计=SUMPRODUCT(INT(B2:B8));支出合计=SUMPRODUCT(TRUNC(C2:C8)) 成绩表的格式转换:姓名=INDEX(A:A,INT((ROW(A6))/3));科目=INDEX(B$1:D$1,1,MOD((ROW(A1)-1),3)+1);成绩=INDEX($B$2:$D$7,INT((ROW(A1)-1)/3)+1,MOD((ROW(A1)-1),3)+1) 隔两行进行编号:=IF(MOD(ROW(),3)=1,INT(ROW(A3)/3),"") INT函数在序列中的复杂运用:=INT(SQRT(2*ROW(A1))+0.5);=10^INT((ROW()-1)/2);=INT(10^(ROW())/9);=INT((ROW(A2))*2/3) 统计交易损失金额:=SUMPRODUCT(B2:B11-CEILING(B2:B11,0.1)) 根据员工工龄计算年资:=C2+CEILING(B2*30,30)*(INT(B2)>0) 成绩表转换:=INDEX($A:$E,CEILING(ROW()*3/5,3)-(COLUMN()=7),MOD(ROW(B2)-1,5)+1) 计算机上网费用:=CEILING(B2,30)/30*2 统计可组建的球队总数:=SUMPRODUCT(FLOOR(B2:B10,5)/5) 统计业务员提成金额，不足20000元忽略:=FLOOR(B2,20000)/20000*500 FLOOR函数处理正负数混合区域:=FLOOR(A1*100,10*(IF(A1>0,1,-10))) 将数据转换成接近6的倍数:=MROUND(A1,6) 以超产80为单位计算超产奖:{=SUM(MROUND(B2:B11-700,80*IF(B2:B11>=700,1,-1)))/80*50} 将统计金额保留到分位:=ROUND(SUMPRODUCT(B2:B10,C2:C10),2) 将统计金额转换成以万元为单位:=ROUND(SUMPRODUCT(B2:B10,C2:C10)%%,) 对单价计量单位不同的品名汇总金额:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="G",1000,1),(D2:D10="G")*2))} 将金额保留“角”位，忽略“分”位:{=SUM(ROUNDDOWN(B2:B10*C2:C10,1))} 计算需要多少零钞:{=SUM(ROUNDDOWN(B2:B10*C2:C10,{0,-1})*{1,-1})} 计算值为l万的整数倍数的数据个数:{=SUM(N((B2:B10*C2:C10)=ROUNDDOWN(B2:B10*C2:C10,-4)))} 计算完成工程需求人数:{=SUM(ROUNDUP(B2:B11/C2:C11,))} 按需求对成绩进行分类汇总:=SUBTOTAL(HLOOKUP(G$1,{"平均成绩","科目数量","最高成绩","最低成绩","成绩合计";1,2,4,5,9},2,0),B2:D2) 不间断的序号:=SUBTOTAL(103,$B$2:B2) 仅对筛选出的人员排名次:{=CONCATENATE("第",SUM(N(IF((SUBTOTAL(103,OFFSET(优等生!A$1,ROW($2:$31)-2,)))=1,$C$2:$C$31,)>C2))+1,"名")} 判断两列数据是否相等: 计算两列数据同行相等的个数:{=SUM(N(A1:A10=B1:B10))} 计算同行相等且长度为3的个数:{=SUM((A1:A10=B1:B10)*(LEN(A1:A10)=3))} 提取A产品最后单价:{=INDEX(C:C,MAX((B2:B10="A")*ROW(2:10)))} 判断学生是否符合奖学金发放条件:=AND(B2>90,C2<>"汉族") 所有裁判都给“通过”就进入决赛:{=AND(B2:E2="通过")} 判断身份证长度是否正确:=OR(LEN(B2)={15,18}) 判断歌手是否被淘汰:{=OR(B2:E2="不通过")} 根据年龄判断职工是否退休:=OR(AND(B2="男",C2>60),AND(B2="女",C2>55)) 根据年龄与职务判断职工是否退休:=OR(AND(B2="男",D2>60+(C2="干部")*3),AND(B2="女",D2>55+(C2="干部")*3)) 没有任何裁判给“不通过”就进行决赛:{=NOT(OR(B2:E2="不通过"))} 计彝成绩区域数字个数:{=SUM(NOT(ISERROR(NOT(B2:B11)))*1)} 评定学生成绩是否及格:=IF(AVERAGE(B2:D2)>=60,"及格","不及格") 根据学生成绩自动产生评语:=IF(AVERAGE(B2:D2)<60,"不及格",IF(AVERAGE(B2:D2)<90,"良好",IF(AVERAGE(B2:D2)<100,"优秀","满分"))) 根据业绩计算需要发放多少奖金:{=SUM(IF(B2:B11>80000,1000,500))} 根据工作时间计算12月工资:=C2+SUM(IF(B2>{0,1,3,5,10},{300,500,500,500,500})) 合计区域的值并忽略错误值:{=SUM(IF(ISERROR(A1:C10),0,A1:C10))} 既求积也求和:=IF(D2<>"",PRODUCT(C2:D2),SUM(OFFSET(E2,-3,,3))) 分别统计收入和支出:收入{=SUM(IF(B2:B13>0,B2:B13))};支出{=SUM(IF(SUBSTITUTE(IF(B2:B13<>"",B2:B13,0),"负","-")*1<0,SUBSTITUTE(B2:B13,"负","-")*1))} 将成绩从大到小排列:{=IF(ROW(A1)>COUNT(B$2:B$11),"",LARGE(B$2:B$11,ROW(A1)))} 排除空值:{=INDEX($A:$B,SMALL(IF($B$1:$B$11<>"",ROW($1:$11),ROWS($1:$11)+1),ROW()),COLUMN(B2))&""} 有选择地汇总数据:{=SUM(IF(A2:A11={"A组","C组"},C2:C11))} 混合单价求金额合计:{=SUM(ROUND(B2:B10*C2:C10*IF(D2:D10="K",1000,1),2))} 计算异常停机时间:{=SUM(SUBSTITUTE(SUBSTITUTE(IF(C2:C11<>"",C2:C11,0),"修机",""),"换原料","")*1)} 计算最大数字行与文本行:{=MAX(IF(B:B<>"",ROW(A:A)))} 找出谁夺冠次数最多:{=INDEX(B:B,MIN(IF(MAX(COUNTIF(B2:B12,B2:B12))=COUNTIF(B2:B12,B2:B12),ROW(2:12))))} 将全角字符转换为半角:=ASC(A2) 计算汉字全角半角混合字符串中的字母个数:=LEN(ASC(A2))*2-LENB(ASC(A2)) 将半角字符转换成全角显示:=WIDECHAR(A2) 计算混合字符串中汉字个数:=LEN(A2)-(LENB(WIDECHAR(A2))-LENB(ASC(A2))) 判断单元格首字符是否为字母:=OR(AND(CODE(A2)>64,CODE(A2)<91),AND(CODE(A2)>96,CODE(A2)<123)) 计算单元格中数字个数:{=SUM((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))>47)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<58))} 计算单元格中大写加小写字母个数:{=SUM((CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))>64)*(CODE(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)))<91))} 产生大、小写字母A到Z的序列:大写字母=CHAR(ROW(A65))，小写字母=CHAR(ROW(A65)+32) 产生大写字母A到ZZ的字母序列:=IF(ROW()<27,CHAR(MOD(ROW()-1,26)+65),CHAR(65+(ROW()-1)/26-1))&IF(ROW()>26,CHAR(MOD(ROW()-1,26)+65),"") 产生三个字母组成的随机字符串:=CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90))&CHAR(RANDBETWEEN(65,90)) 用公式产生换行符:=A2&CHAR(10)&B2 将数字转换成英文字符:字符码=RANDBETWEEN(1,100)，升序位置=CHAR(MOD(A1-1,26)+65) 将字母升序排序:{=CHAR(SMALL(CODE(A$2:A$13),ROW(A1)))} 返回自动换行单元格的第二行数据:=RIGHT(A2,LEN(A2)-FIND(CHAR(10),A2)) 根据身份证号码提取出生年月日:=CONCATENATE(MID(B2,7,4-2*(LEN(B2)=15)),"年",MID(B2,11-2*(LEN(B2)=15),2),"月",MID(B2,13-2*(LEN(B2)=15),2),"日 ") 计算平均成绩及评判是否及格:=CONCATENATE(INT(AVERAGE(B2:D2)),": ",IF(AVERAGE(B2:D2)>=60,"","不"),"及格") 提取前三名人员姓名:=CONCATENATE(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,1)),A2:A11),"|",LOOKUP(0,0/(B2:B11=LARGE(B2:B11,2)),A2:A11),"|",(LOOKUP(0,0/(B2:B11=LARGE(B2:B11,3)),A2:A11))) 将单词转换成首字母大写:=PROPER(A2) 将所有单词转换成小写形式:=LOWER(A2) 将所有句子转换成首字母大写其余小写:=CONCATENATE(PROPER(LEFT(A2)),LOWER(RIGHT(A2,LEN(A2)-1))) 将所有字母转换成大写形式:=UPPER(A2) 计算字符串中英文字母个数:{=SUM(N(NOT(EXACT(UPPER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),LOWER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))))))} 计算字符串中单词个数:{=SUM(N(EXACT(TRIM(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1)),MID(PROPER(A2),ROW(INDIRECT("1:"&LEN(A2))),1))))} 将文本型数字转换成数值:{=SUM(VALUE(B2:B10))} 计算字符串中的数字个数:=SUMPRODUCT(N(ISNUMBER(VALUE(MID(A2,ROW($1:$100),1)*1)))) 提取混合字符串中的数字:{=MAX(IFERROR(VALUE(MID(A2,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A2&1234567890)),ROW(INDIRECT("1:"&LEN(A2))))),0))} 串联区域中的文本:=CONCATENATE(T(A2),T(B2),T(C2)) 给公式添加运算说明:=CONCATENATE("你好",B2,"2008")&T(N("公式含义:连接“你好”和单元格B2、“2008”")) 根据身份证号码判断性别:=TEXT(MOD(MID(B2,15,3),2),"[=1]男;[=0]女") 将所有数据转换成保留两位小数再求和:{=SUM(--TEXT(B2:B11*C2:C11,"0.00"))} 将货款显示为“万元”为单位:=TEXT(B2,"￥#"&""""&"."&""""&"#,万元") 根据身份证号码计算出生日期:=IF(LEN(B2)=15,19,"")&TEXT(MID(B2,7,8-(LEN(B2)=15)*2),"#年00月00日") 显示今天的英文日期及星期:="资料日期："&TEXT(TODAY(),"dddd, mmmm dd, yyyy") 显示今天每项工程的预计完成时间:=TEXT(SUM("08:00",B$2:B2),"h:mm:ss 上午/下午") 统计A列有多少个星期日:{=SUM(N(TEXT(A1:A11,"aaa")="日"))} 将数据显示为小数点对齐:=TEXT(B2,"#.0????") 计算A列的日期有几个属于第二季度:{=SUM((--(TEXT(A1:A11,"m"))>{3,6})*{1,-1})} 在A列产生1到12月的英文月份名:=TEXT((ROW())&"-1","mmmm") 将日期显示为中文大写:=TEXT("2008-8-10","[DBNum2]yyyy年m月d日") 将数字金额显示为人民币大写:=IF(MOD(B2,1)=0,TEXT(INT(B2),"[dbnum2]G/通用格式元整;负[dbnum2]G/通用格式元整;零元整;"),IF(B2>0,,"负")&TEXT(INT(ABS(B2)),"[dbnum2]G/通用格式元;;")&SUBSTITUTE(SUBSTITUTE(TEXT(RIGHT(FIXED(B2),2),"[dbnum2]0角0分;;"),"零角",IF(ABS(B2)<>0,,"零")),"零分","")) 判断单元格的数据类型:=TEXT(A2,"大于○;小于○;○;文本") 计算达成率，以不同格式显示:=TEXT(B2/800,"[>=1]0.0倍;[>0]0.00%;") 计算字母“A”的首次出现位置，忽略大小写:=TEXT(SEARCH("a",A2&"a"),"[>"&LEN(A2)&"]没找到;第"&SEARCH("a",A2&"a")&"个") 从身份证号码中提取表示性别的数字:=MID(B2,TEXT(LEN(B2),"[=15]15;17"),1) 将三列数据交换位置:{=TEXT({1,-1,0},C1:C5&";"&"!"&B1:B5&";"&A1:A5)} 计算年终奖:=TEXT(B2,"[>3]15!0!0;[>1]1!0!0!0;5!0!0;") 计算星期日完工的工程个数:{=COUNT((TEXT(B2:B10+C2:C10-1,"AAA")="日")^0)} 计算本月星期日的个数:{=SUM(N(TEXT(TODAY()-TEXT(TODAY(),"d")+ROW(INDIRECT("1:"&DAY(DATE(,TEXT(TODAY(),"m")+1,)))),"AAA")="日"))} 检验日期是否升序排列:=TEXT(N(A3>=A2),";;日期有误;") 判断单元格中首字符的类型:=TEXT(IF(AND(CODE(UPPER(A3))>64,CODE(UPPER(A3))<91),CODE(A3),A3),"[="&CODE(A3)&"]字母;;数字;汉字") 计算每个季度的天数:{=SUM(--TEXT(DATE(2008,3*ROW(A1)-ROW($1:$3)+2,),"d"))} 将数据重复显示5次:=SUBSTITUTE(TEXT(A2&"？","@@@@@"),"？","") 将表示起止时间的数字格式化为时间格式:=TEXT(B2,"#!:00-00!:00") 根据起止时间计算经过时间:=TEXT(INT(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60)/60)+MOD(((TEXT(RIGHT(B4,4),"#!:00")-TEXT(LEFT(B4,3+(LEN(B4)=8)),"#!:00"))*24*60),60.1)%,"0小时.00分钟") 将数字转化成电话格式:=TEXT(A2,"(0000)0000-0000") 在A1:A7区域产生星期一到星期日的英文全称:{=TEXT(ROW(1:7)+1,"DDDD")} 将汇总金额保留一位小数并显示千分位分隔符:{=FIXED(SUM(--FIXED(B2:B11*C2:C11,1)),1,FALSE)} 计算订单金额并以“百万”为单位显示:=FIXED(SUMPRODUCT(B2:B10,C2:C10),-6)/1000000 将数据对齐显示，将空白以“．”占位:=WIDECHAR(REPT(".",10-LEN(B2))&B2) 利用公式制作简易图表:=IF(B2>0,REPT("　",5)&"｜"&REPT("■",ABS(B2))&B2&REPT("　",5-ABS(B2)),REPT("　",5-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&"｜"&REPT("　",5)) 利用公式制作带轴的图表且标示升降:{=IF(A2<>"",A2&"┫","")&IF(A2="",REPT("〓",(MAX(ABS(B$2:B$8))+6)*2),IF(B2>0,REPT("　",4+MAX(ABS(B$2:B$8)))&IF(ROW()=2,"　",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓")))&REPT("■",ABS(B2))&B2&REPT("　",4+MAX(ABS(B$2:B$8))-ABS(B2)),REPT("　",4+MAX(ABS(B$2:B$8))-ABS(B2)-LEN(B2)/2)&B2&REPT("■",ABS(B2))&IF(ROW()=1,"　",IF(B2=OFFSET(B2,-1,0),"→",IF(B2>OFFSET(B2,-1,0),"↑","↓"))&REPT("　",4+MAX(ABS(B$2:B$8))))))} 计算单元格中数字个数:=LEN(A2)*2-LENB(A2) 将数字倒序排列:{=TEXT(SUM(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)*10^(ROW(INDIRECT("1:"&LEN(A2)))-1)),REPT(0,LEN(A2)))} 计算购物金额中小数位数最多是几:{=MAX(LEN(B2:B10*C2:C10)-LEN(INT(B2:B10*C2:C10)))-1} 计算英文句子中有几个单词:=LEN(A2)-LEN(SUBSTITUTE(SUBSTITUTE(A2,"'"," ")," ",""))+1 将英文句子规范化:=PROPER(LEFT(A2))&TRIM(RIGHT(A2,LEN(A2)-1)) 分别提取省市县名:=TRIM(MID(SUBSTITUTE($A2,"/",REPT(" ",100)),CO