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
升级会员 