excel函数使用技巧.docx
《excel函数使用技巧.docx》由会员分享,可在线阅读,更多相关《excel函数使用技巧.docx(42页珍藏版)》请在冰豆网上搜索。
excel函数使用技巧
excel中根据A列汉字自动在B列生成拼音字母的方法
下图所示的excel表格,A列是姓名列,根据A列的汉字姓名,自动在B列和C列分别自动生成姓名列的拼音字母。
下面是小编整理出来的详细的操作步骤,分享给有需要的朋友。
第一步,由于Excel中没有现成的函数套用可以解决此问题,首先自定义一个函数。
方法如下:
1.运行“工具→宏→VisualBasic编辑器”命令(或者直接按“Alt+F11”组合键),进入VisualBasic编辑状态。
2.运行“插入→模块”命令,插入一个新模块。
再双击插入的模块,进入模块代码编辑状态。
3.将以下的代码输入其中,保存,关闭VisualBasic编辑窗口,返回Excel编辑状态。
FunctionPinYin(HzAsString)
DimPinMaAsString
DimMyPinMaAsVariant
DimTempAsInteger,iAsInteger,jAsInteger
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,",")
Fori=1ToLen(Hz)
Temp=Asc(Mid(Hz,i,1))
IfTemp<0Then
Temp=Abs(Temp)
Forj=791To1Step-2
IfTemp<=Val(MyPinMa(j))Then
PinYin=PinYin&MyPinMa(j-1)&""
ExitFor
EndIf
Next
EndIf
Next
PinYin=Trim(PinYin)
EndFunction
第二步,在空白列,比如F2单元格,输入公式:
=PinYin(A2),就可以在F2单元格得到A2单元格汉字的拼音。
然后下拉复制完成A列所有的汉字转拼音。
提示:
公式中的“PinYin”就是第一步自定义函数的名称。
第三步,在B2单元格输入公式:
=LEFT(F2,FIND("",F2)-1),就可以提取出F2单元格中的第一个汉字的拼音。
然后下拉复制完成A列中汉字“姓”的拼音提取。
第四步,在C2单元格输入公式:
=TRIM(SUBSTITUTE(F2,B2,"")),即可得到剩余的“名”字部分的汉字的拼音。
下拉复制公式。
第五步,如果需要拼音的首字母大写,可以再用PROPER函数来转换一下。
最终完成的效果如下图所示。
Excel2007函数公式实例集
2010-01-2813:
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