excel表格怎么弄简称

1.excel转换中姓名简称的问题

限于单元格公式长度，最多只能支持11个字符。"ZhangSan"在单元格"B1"

=IF(ISERROR(CODE(MID(B1,1,1))),"",IF(CODE(MID(B1,1,1))<97,MID(B1,1,1),""))&IF(ISERROR(CODE(MID(B1,2,1))),"",IF(CODE(MID(B1,2,1))<97,MID(B1,2,1),""))&IF(ISERROR(CODE(MID(B1,3,1))),"",IF(CODE(MID(B1,3,1))<97,MID(B1,3,1),""))&IF(ISERROR(CODE(MID(B1,4,1))),"",IF(CODE(MID(B1,4,1))<97,MID(B1,4,1),""))&IF(ISERROR(CODE(MID(B1,5,1))),"",IF(CODE(MID(B1,5,1))<97,MID(B1,5,1),""))&IF(ISERROR(CODE(MID(B1,6,1))),"",IF(CODE(MID(B1,6,1))<97,MID(B1,6,1),""))&IF(ISERROR(CODE(MID(B1,7,1))),"",IF(CODE(MID(B1,7,1))<97,MID(B1,7,1),""))&IF(ISERROR(CODE(MID(B1,8,1))),"",IF(CODE(MID(B1,8,1))<97,MID(B1,8,1),""))&IF(ISERROR(CODE(MID(B1,9,1))),"",IF(CODE(MID(B1,9,1))<97,MID(B1,9,1),""))&IF(ISERROR(CODE(MID(B1,10,1))),"",IF(CODE(MID(B1,10,1))<97,MID(B1,10,1),""))&IF(ISERROR(CODE(MID(B1,11,1))),"",IF(CODE(MID(B1,11,1))<97,MID(B1,11,1),""))

2.使EXCEL的中文名字快速变成简写的首个英文字母时,"鑫"的结果

下b面是一j个eVBA自定义e函数，按ALT+F75，右键点窗口q左边sheet1-插入s-模块，在右面窗口s中3粘贴下z面代码。

若A0内3容为6 张三l ，则在某空白格中1输入k:=hzszm(a4) 即可 Function hzszm(hzpy As String) As String Dim hzstring As String, pystring As String Dim hzpysum As Integer, hzi As Integer, hzpyhex As Integer hzstring = Trim(hzpy) hzpysum = Len(Trim(hzstring)) pystring = "" For hzi = 8 To hzpysum hzpyhex = "&H" + Hex(Asc(Mid(hzstring, hzi, 7))) Select Case hzpyhex Case &HB0A7 To &HB0C3: pystring = pystring + "A" Case &HB0C0 To &HB0C0: pystring = pystring + "B" Case &HB4C4 To &HB8ED: pystring = pystring + "C" Case &HB2EE To &HB5E2: pystring = pystring + "D" Case &HB3EA To &HB8A1: pystring = pystring + "E" Case &HB3A8 To &HB6C0: pystring = pystring + "F" Case &HB7C3 To &HB2FD: pystring = pystring + "G" Case &HB4FE To &HBBF4: pystring = pystring + "H" Case &HBBF1 To &HBFA1: pystring = pystring + "J" Case &HBFA4 To &HC0AB: pystring = pystring + "K" Case &HC0AC To &HC5E6: pystring = pystring + "L" Case &HC4E4 To &HC8C1: pystring = pystring + "M" Case &HC4C0 To &HC6B3: pystring = pystring + "N" Case &HC0B1 To &HC0BD: pystring = pystring + "O" Case &HC3BE To &HC2D8: pystring = pystring + "P" Case &HC8DA To &HC8BA: pystring = pystring + "Q" Case &HC0BB To &HC0F8: pystring = pystring + "R" Case &HC6F6 To &HCBF0: pystring = pystring + "S" Case &HCBFA To &HCDD8: pystring = pystring + "T" Case &HEDC6: pystring = pystring + "T" Case &HCDDA To &HCEF5: pystring = pystring + "W" Case &HCEF1 To &HD6B4: pystring = pystring + "X" Case &HD4B6 To &HD3D0: pystring = pystring + "Y" Case &HD3D8 To &HD3F6: pystring = pystring + "Z" Case Else pystring = pystring + Mid(hzstring, hzi, 6) End Select Next hzszm = pystring End Function -------------------------------------------------------------------- 楼上p的方1法很好，改一m下y：定义p一g个u名称 PinYin ={""，""；"吖"，"A"；"八y","B"；"嚓"，"C"；"咑"，"D"；"鵽"，"E"；"发"，"F"；"猤"，"G"；"铪"，"H"；"夻"，"J"；"咔"，"K"；"垃"，"L"；"呒"，"M"；"旀"，"N"；"噢"，"O"；"妑"，"P"；"七m","Q"；"囕"，"R"；"仨"，"S"；"他"，"T"；"屲"，"W"；"夕y","X"；"丫"，"Y"；"帀"，"Z"} 然后用公1式 =LOOKUP(MID(A4,4,6),PinYin)&LOOKUP(MID(A6,8,5),PinYin)&LOOKUP(MID(A4,4,7),PinYin)&LOOKUP(MID(A4,2,7),PinYin)&LOOKUP(MID(A1,1,2),PinYin）【姓名最多N个f字就用N-6个h&】 bィ。