澳门太阳娱乐集团官网-太阳集团太阳娱乐登录

EXCEL如何自动将姓名转换为拼音?
分类:软件下载

问题:EXCEL怎样自动将人名转变为拼音?

 代码如下

汉字调换拼音类

回答:

function pinyin($_string, $_code='utf-8')
{
$_datakey = "a|ai|an|ang|ao|ba|bai|ban|bang|bao|bei|ben|beng|bi|bian|biao|bie|bin|bing|bo|bu|ca|cai|can|cang|cao|ce|ceng|cha".
"|chai|chan|chang|chao|che|chen|cheng|chi|chong|chou|chu|chuai|chuan|chuang|chui|chun|chuo|ci|cong|cou|cu|".
"cuan|cui|cun|cuo|da|dai|dan|dang|dao|de|deng|di|dian|diao|die|ding|diu|dong|dou|du|duan|dui|dun|duo|e|en|er".
"|fa|fan|fang|fei|fen|feng|fo|fou|fu|ga|gai|gan|gang|gao|ge|gei|gen|geng|gong|gou|gu|gua|guai|guan|guang|gui".
"|gun|guo|ha|hai|han|hang|hao|he|hei|hen|heng|hong|hou|hu|hua|huai|huan|huang|hui|hun|huo|ji|jia|jian|jiang".
"|jiao|jie|jin|jing|jiong|jiu|ju|juan|jue|jun|ka|kai|kan|kang|kao|ke|ken|keng|kong|kou|ku|kua|kuai|kuan|kuang".
"|kui|kun|kuo|la|lai|lan|lang|lao|le|lei|leng|li|lia|lian|liang|liao|lie|lin|ling|liu|long|lou|lu|lv|luan|lue".
"|lun|luo|ma|mai|man|mang|mao|me|mei|men|meng|mi|mian|miao|mie|min|ming|miu|mo|mou|mu|na|nai|nan|nang|nao|ne".
"|nei|nen|neng|ni|nian|niang|niao|nie|nin|ning|niu|nong|nu|nv|nuan|nue|nuo|o|ou|pa|pai|pan|pang|pao|pei|pen".
"|peng|pi|pian|piao|pie|pin|ping|po|pu|qi|qia|qian|qiang|qiao|qie|qin|qing|qiong|qiu|qu|quan|que|qun|ran|rang".
"|rao|re|ren|reng|ri|rong|rou|ru|ruan|rui|run|ruo|sa|sai|san|sang|sao|se|sen|seng|sha|shai|shan|shang|shao|".
"she|shen|sheng|shi|shou|shu|shua|shuai|shuan|shuang|shui|shun|shuo|si|song|sou|su|suan|sui|sun|suo|ta|tai|".
"tan|tang|tao|te|teng|ti|tian|tiao|tie|ting|tong|tou|tu|tuan|tui|tun|tuo|wa|wai|wan|wang|wei|wen|weng|wo|wu".
"|xi|xia|xian|xiang|xiao|xie|xin|xing|xiong|xiu|xu|xuan|xue|xun|ya|yan|yang|yao|ye|yi|yin|ying|yo|yong|you".
"|yu|yuan|yue|yun|za|zai|zan|zang|zao|ze|zei|zen|zeng|zha|zhai|zhan|zhang|zhao|zhe|zhen|zheng|zhi|zhong|".
"zhou|zhu|zhua|zhuai|zhuan|zhuang|zhui|zhun|zhuo|zi|zong|zou|zu|zuan|zui|zun|zuo";

class cntopy {

多谢今日头条的邀约,我们好,笔者是Excel玄妙之旅,潜心于Excel知识和技艺的分享

$_datavalue = "-20319|-20317|-20304|-20295|-20292|-20283|-20265|-20257|-20242|-20230|-20051|-20036|-20032|-20026|-20002|-19990".
"|-19986|-19982|-19976|-19805|-19784|-19775|-19774|-19763|-19756|-19751|-19746|-19741|-19739|-19728|-19725".
"|-19715|-19540|-19531|-19525|-19515|-19500|-19484|-19479|-19467|-19289|-19288|-19281|-19275|-19270|-19263".
"|-19261|-19249|-19243|-19242|-19238|-19235|-19227|-19224|-19218|-19212|-19038|-19023|-19018|-19006|-19003".
"|-18996|-18977|-18961|-18952|-18783|-18774|-18773|-18763|-18756|-18741|-18735|-18731|-18722|-18710|-18697".
"|-18696|-18526|-18518|-18501|-18490|-18478|-18463|-18448|-18447|-18446|-18239|-18237|-18231|-18220|-18211".
"|-18201|-18184|-18183|-18181|-18012|-17997|-17988|-17970|-17964|-17961|-17950|-17947|-17931|-17928|-17922".
"|-17759|-17752|-17733|-17730|-17721|-17703|-17701|-17697|-17692|-17683|-17676|-17496|-17487|-17482|-17468".
"|-17454|-17433|-17427|-17417|-17202|-17185|-16983|-16970|-16942|-16915|-16733|-16708|-16706|-16689|-16664".
"|-16657|-16647|-16474|-16470|-16465|-16459|-16452|-16448|-16433|-16429|-16427|-16423|-16419|-16412|-16407".
"|-16403|-16401|-16393|-16220|-16216|-16212|-16205|-16202|-16187|-16180|-16171|-16169|-16158|-16155|-15959".
"|-15958|-15944|-15933|-15920|-15915|-15903|-15889|-15878|-15707|-15701|-15681|-15667|-15661|-15659|-15652".
"|-15640|-15631|-15625|-15454|-15448|-15436|-15435|-15419|-15416|-15408|-15394|-15385|-15377|-15375|-15369".
"|-15363|-15362|-15183|-15180|-15165|-15158|-15153|-15150|-15149|-15144|-15143|-15141|-15140|-15139|-15128".
"|-15121|-15119|-15117|-15110|-15109|-14941|-14937|-14933|-14930|-14929|-14928|-14926|-14922|-14921|-14914".
"|-14908|-14902|-14894|-14889|-14882|-14873|-14871|-14857|-14678|-14674|-14670|-14668|-14663|-14654|-14645".
"|-14630|-14594|-14429|-14407|-14399|-14384|-14379|-14368|-14355|-14353|-14345|-14170|-14159|-14151|-14149".
"|-14145|-14140|-14137|-14135|-14125|-14123|-14122|-14112|-14109|-14099|-14097|-14094|-14092|-14090|-14087".
"|-14083|-13917|-13914|-13910|-13907|-13906|-13905|-13896|-13894|-13878|-13870|-13859|-13847|-13831|-13658".
"|-13611|-13601|-13406|-13404|-13400|-13398|-13395|-13391|-13387|-13383|-13367|-13359|-13356|-13343|-13340".
"|-13329|-13326|-13318|-13147|-13138|-13120|-13107|-13096|-13095|-13091|-13076|-13068|-13063|-13060|-12888".
"|-12875|-12871|-12860|-12858|-12852|-12849|-12838|-12831|-12829|-12812|-12802|-12607|-12597|-12594|-12585".
"|-12556|-12359|-12346|-12320|-12300|-12120|-12099|-12089|-12074|-12067|-12058|-12039|-11867|-11861|-11847".
"|-11831|-11798|-11781|-11604|-11589|-11536|-11358|-11340|-11339|-11324|-11303|-11097|-11077|-11067|-11055".
"|-11052|-11045|-11041|-11038|-11024|-11020|-11019|-11018|-11014|-10838|-10832|-10815|-10800|-10790|-10780".
"|-10764|-10587|-10544|-10533|-10519|-10331|-10329|-10328|-10322|-10315|-10309|-10307|-10296|-10281|-10274".
"|-10270|-10262|-10260|-10256|-10254";
$_tdatakey = explode('|', $_datakey);
$_tdatavalue = explode('|', $_datavalue);

 /**
  * @var array $chineseSpellList 拼音编码对应表
  * @access private
  */
 private $chineseSpellList = array(
    'a'=>-20319,
    'ai'=>-20317,
    'an'=>-20304,
    'ang'=>-20295,
    'ao'=>-20292,
    '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,
    '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,
    '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,
    'e'=>-18710,
    'en'=>-18697,
    'er'=>-18696,
    'fa'=>-18526,
    'fan'=>-18518,
    'fang'=>-18501,
    'fei'=>-18490,
    'fen'=>-18478,
    'feng'=>-18463,
    'fo'=>-18448,
    'fou'=>-18447,
    'fu'=>-18446,
    '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,
    '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,
    '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,
    '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,
    '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,
    '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,
    '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,
    'o'=>-14922,
    'ou'=>-14921,
    '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,
    '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,
    '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,
    '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,
    '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,
    'wa'=>-12838,
    'wai'=>-12831,
    'wan'=>-12829,
    'wang'=>-12812,
    'wei'=>-12802,
    'wen'=>-12607,
    'weng'=>-12597,
    'wo'=>-12594,
    'wu'=>-12585,
    '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,
    '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,
    '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
    );
 
 /**
  * 取汉字全数拼音
  * @param string $chinese 要转移的方块字
  * @param string $delimiter 分隔符
  * @param int $length 再次来到的长短
  * @return string
  */
 public function getFullSpell($chinese, $delimiter = ' ', $length =

EXCEL如何自动将姓名转变为拼音?以下是自己的消除方式,不是用VBA,而是依据word来操作

$_data = (php_version>='5.0') ? array_combine($_tdatakey, $_tdatavalue) : _array_combine($_tdatakey, $_tdatavalue);
arsort($_data);
reset($_data);

0) {
  $spell = $this->getChineseSpells($chinese, $delimiter);
  if ($length) {
   $spell = substr($spell, 0, $length);
  }
  return $spell;
 }
 
 /**
  * 取汉字第三个拼音
  * @param string $chinese 要转移的汉字
  * @param int $length 重返的尺寸
  * @return string
  */
 public function getFirstSpell($chinese, $length = 0) {
  $spell = $this->getChineseSpells($chinese, ' ', 1);
  if ($length) {
   $spell = substr($spell, 0, $length);
  }
  return $spell;
 }

操作思路:

if($_code != 'gb2312') $_string = _u2_utf8_gb($_string);
$_res = '';
for($i=0; $i<strlen($_string); $i++)
{
$_p = ord(substr($_string, $i, 1));
if($_p>160) { $_q = ord(substr($_string, ++$i, 1)); $_p = $_p*256 + $_q - 65536; }
$_res .= _pinyin($_p, $_data);
}
return preg_replace("/[^a-z0-9]*/", '', $_res);
}

 /**
  * 取多个汉字码对应的拼音
  * @param int $num 汉字码
  * @param string $blank 空白字符
  * @return string
  */
 private function getChineseSpell ($num, $blank = '') {
  if ( $num>0 && $num<160 ) {
   return chr($num);
  } elseif ($num<-20319||$num>-10247) {
   return $blank;
  } else {
   foreach ($this->chineseSpellList as $spell => $code)

第一:把excel中的姓名粘贴到word中,依附word增多拼音,再把word中增添拼音的结果粘贴到excel中

function _pinyin($_num, $_data)
{
if ($_num>0 && $_num<160 ) return chr($_num);
elseif($_num<-20319 || $_num>-10247) return '';
else {
foreach($_data as $k=>$v){ if($v<=$_num) break; }
return $k;
}
}

{
    if ($code > $num) break;
    $result = $spell;
   }
   return $result;
  }
 }

其次:用PHONETIC函数获得拼音

function _u2_utf8_gb($_c)
{
$_string = '';
if($_c < 0x80) $_string .= $_c;
elseif($_c < 0x800)
{
$_string .= chr(0xc0 | $_c>>6);
$_string .= chr(0x80 | $_c & 0x3f);
}elseif($_c < 0x10000){
$_string .= chr(0xe0 | $_c>>12);
$_string .= chr(0x80 | $_c>>6 & 0x3f);
$_string .= chr(0x80 | $_c & 0x3f);
} elseif($_c < 0x200000) {
$_string .= chr(0xf0 | $_c>>18);
$_string .= chr(0x80 | $_c>>12 & 0x3f);
$_string .= chr(0x80 | $_c>>6 & 0x3f);
$_string .= chr(0x80 | $_c & 0x3f);
}
return iconv('utf-8', 'gb2312', $_string);
}

 /**
  * 取汉字拼音
  * @param string $chinese 要更动的方块字
  * @param string $delimiter 分隔符
  * @param int $first 是或不是只回去第二个
  * @return string
  */
 private function getChineseSpells($chinese, $delimiter = ' ',

其三:用substitute函数将拼音声调去掉(这一步视供给,如无需删除声调能够忽视)

function _array_combine($_arr1, $_arr2)
{
for($i=0; $i<count($_arr1); $i++) $_res[$_arr1[$i]] = $_arr2[$i];
return $_res;
}  

$first=0)
 {
  $result = array();
  for ($i=0; $i<strlen($chinese); $i++) {
   $p = ord(substr($chinese,$i,1));
   if ($p>160) {
    $q = ord(substr($chinese,++$i,1));
    $p = $p*256 + $q - 65536;
   }
   $result[] = $this->getChineseSpell($p);
   if ($first) {
    return $result[0];
   }
  }
  return implode($delimiter, $result);
 }
}

图片 1

接下去详细介绍操作方法:

1)第一:把excel中的姓名粘贴到word中,依赖word增添拼音,注意粘贴的时候接纳只保留文本那一个选项,增加拼音后再粘贴回Excel

图片 2

介怀粘贴到Word的时候选择只保留文本那叁个选项,否则word不只怕增多拼音

图片 3

其次:用PHONETIC函数取得拼音

在C3单元格输入公式= =PHONETIC(B3),然后下拉公式,得到全数人名拼音

图片 4

其三:用substitute函数将拼音声调去掉(这一步视供给,如无需删除声调能够忽视

在D3单元格输入公式=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"ā","a"),"á","a"),"ǎ","a"),"à","a"),"ē","e"),"é","e"),"ě","e"),"è","e"),"ī","i"),"í","i"),"ǐ","i"),"ì","i"),"ō","o"),"ó","o"),"ǒ","o"),"ò","o"),"ū","u"),"ú","u"),"ǔ","u"),"ù","u"),"ǖ","ü"),"ǘ","ü"),"ǚ","ü"),"ǜ","ü")

其一公式利用substitute函数把拼音中的字母为韵母的唱腔都替换到了不带声调的韵母,也就让拼音的腔调去除了

图片 5

自己是Excel奇妙之旅,静心于Excel知识和本领的分享,关心Excel美妙之旅,带给你越多的实战本事

回答:

EXCEL怎样自动将人名调换为拼音?

感谢悟空小秘的诚邀!

EXCEL怎么着自动将人名调换为拼音,由于EXCEL中未有汉字自动调换拼音的成效,所以要在VBA中自定义一个转移函数。

具体操作步骤

首先步,要把EXCEL文书档案转变为 XLSM格式,启用宏。

图片 6

其次步,在文书,设置,自定义功效区中勾选开垦工具。

图片 7

第三步,打开VBA编辑器。

图片 8

第四步,插入模块。

图片 9

第五步,在模块中输入如下代码:

Function pinyin(p As String) As String

i = Asc(p)

Select Case i

Case -20319 To -20318: pinyin = "a "

Case -20317 To -20305: pinyin = "ai "

Case -20304 To -20296: pinyin = "an "

Case -20295 To -20293: pinyin = "ang "

Case -20292 To -20284: pinyin = "ao "

Case -20283 To -20266: pinyin = "ba "

Case -20265 To -20258: pinyin = "bai "

Case -20257 To -20243: pinyin = "ban "

Case -20242 To -20231: pinyin = "bang "

Case -20230 To -20052: pinyin = "bao "

Case -20051 To -20037: pinyin = "bei "

Case -20036 To -20033: pinyin = "ben "

Case -20032 To -20027: pinyin = "beng "

Case -20026 To -20003: pinyin = "bi "

Case -20002 To -19991: pinyin = "bian "

Case -19990 To -19987: pinyin = "biao "

Case -19986 To -19983: pinyin = "bie "

Case -19982 To -19977: pinyin = "bin "

Case -19976 To -19806: pinyin = "bing "

Case -19805 To -19785: pinyin = "bo "

Case -19784 To -19776: pinyin = "bu "

Case -19775 To -19775: pinyin = "ca "

Case -19774 To -19764: pinyin = "cai "

Case -19763 To -19757: pinyin = "can "

Case -19756 To -19752: pinyin = "cang "

Case -19751 To -19747: pinyin = "cao "

Case -19746 To -19742: pinyin = "ce "

Case -19741 To -19740: pinyin = "ceng "

Case -19739 To -19729: pinyin = "cha "

Case -19728 To -19726: pinyin = "chai "

Case -19725 To -19716: pinyin = "chan "

Case -19715 To -19541: pinyin = "chang "

Case -19540 To -19532: pinyin = "chao "

Case -19531 To -19526: pinyin = "che "

Case -19525 To -19516: pinyin = "chen "

Case -19515 To -19501: pinyin = "cheng "

Case -19500 To -19485: pinyin = "chi "

Case -19484 To -19480: pinyin = "chong "

Case -19479 To -19468: pinyin = "chou "

Case -19467 To -19290: pinyin = "chu "

Case -19289 To -19289: pinyin = "chuai "

Case -19288 To -19282: pinyin = "chuan "

Case -19281 To -19276: pinyin = "chuang "

Case -19275 To -19271: pinyin = "chui "

Case -19270 To -19264: pinyin = "chun "

Case -19263 To -19262: pinyin = "chuo "

Case -19261 To -19250: pinyin = "ci "

Case -19249 To -19244: pinyin = "cong "

Case -19243 To -19243: pinyin = "cou "

Case -19242 To -19239: pinyin = "cu "

Case -19238 To -19236: pinyin = "cuan "

Case -19235 To -19228: pinyin = "cui "

Case -19227 To -19225: pinyin = "cun "

Case -19224 To -19219: pinyin = "cuo "

Case -19218 To -19213: pinyin = "da "

Case -19212 To -19039: pinyin = "dai "

Case -19038 To -19024: pinyin = "dan "

Case -19023 To -19019: pinyin = "dang "

Case -19018 To -19007: pinyin = "dao "

Case -19006 To -19004: pinyin = "de "

Case -19003 To -18997: pinyin = "deng "

Case -18996 To -18978: pinyin = "di "

Case -18977 To -18962: pinyin = "dian "

Case -18961 To -18953: pinyin = "diao "

Case -18952 To -18784: pinyin = "die "

Case -18783 To -18775: pinyin = "ding "

Case -18774 To -18774: pinyin = "diu "

Case -18773 To -18527: pinyin = "dong "

Case -18526 To -18519: pinyin = "fa "

Case -18518 To -18502: pinyin = "fan "

Case -18501 To -18491: pinyin = "fang "

Case -18490 To -18479: pinyin = "fei "

Case -18478 To -18464: pinyin = "fen "

Case -18463 To -18449: pinyin = "feng "

Case -18448 To -18448: pinyin = "fo "

Case -18447 To -18447: pinyin = "fou "

Case -18446 To -18240: pinyin = "fu "

Case -18239 To -18238: pinyin = "ga "

Case -18237 To -18232: pinyin = "gai "

Case -18231 To -18221: pinyin = "gan "

Case -18220 To -18212: pinyin = "gang "

Case -18211 To -18202: pinyin = "gao "

Case -18201 To -18185: pinyin = "ge "

Case -18184 To -18184: pinyin = "gei "

Case -18183 To -18182: pinyin = "gen "

Case -18181 To -18013: pinyin = "geng "

Case -18012 To -17998: pinyin = "gong "

Case -17997 To -17989: pinyin = "gou "

Case -17988 To -17971: pinyin = "gu "

Case -17970 To -17965: pinyin = "gua "

Case -17964 To -17962: pinyin = "guai "

Case -17961 To -17951: pinyin = "guan "

Case -17950 To -17948: pinyin = "guang "

Case -17947 To -17932: pinyin = "gui "

Case -17931 To -17929: pinyin = "gun "

Case -17928 To -17923: pinyin = "guo "

Case -17922 To -17760: pinyin = "ha "

Case -17759 To -17753: pinyin = "hai "

Case -17752 To -17734: pinyin = "han "

Case -17733 To -17731: pinyin = "hang "

Case -17730 To -17722: pinyin = "hao "

Case -17721 To -17704: pinyin = "he "

Case -17703 To -17702: pinyin = "hei "

Case -17701 To -17698: pinyin = "hen "

Case -17697 To -17693: pinyin = "heng "

Case -17692 To -17684: pinyin = "hong "

Case -17683 To -17677: pinyin = "hou "

Case -17676 To -17497: pinyin = "hu "

Case -17496 To -17488: pinyin = "hua "

Case -17487 To -17483: pinyin = "huai "

Case -17482 To -17469: pinyin = "huan "

Case -17468 To -17455: pinyin = "huang "

Case -17454 To -17434: pinyin = "hui "

Case -17433 To -17428: pinyin = "hun "

Case -17427 To -17418: pinyin = "huo "

Case -17417 To -17203: pinyin = "ji "

Case -17202 To -17186: pinyin = "jia "

Case -17185 To -16984: pinyin = "jian "

Case -16983 To -16971: pinyin = "jiang "

Case -16970 To -16943: pinyin = "jiao "

Case -16942 To -16916: pinyin = "jie "

Case -16915 To -16734: pinyin = "jin "

Case -16733 To -16709: pinyin = "jing "

Case -16708 To -16707: pinyin = "jiong "

Case -16706 To -16690: pinyin = "jiu "

Case -16689 To -16665: pinyin = "ju "

Case -16664 To -16658: pinyin = "juan "

Case -16657 To -16648: pinyin = "jue "

Case -16647 To -16475: pinyin = "jun "

Case -16474 To -16471: pinyin = "ka "

Case -16470 To -16466: pinyin = "kai "

Case -16465 To -16460: pinyin = "kan "

Case -16459 To -16453: pinyin = "kang "

Case -16452 To -16449: pinyin = "kao "

Case -16448 To -16434: pinyin = "ke "

Case -16433 To -16430: pinyin = "ken "

Case -16429 To -16428: pinyin = "keng "

Case -16427 To -16424: pinyin = "kong "

Case -16423 To -16420: pinyin = "kou "

Case -16419 To -16413: pinyin = "ku "

Case -16412 To -16408: pinyin = "kua "

Case -16407 To -16404: pinyin = "kuai "

Case -16403 To -16402: pinyin = "kuan "

Case -16401 To -16394: pinyin = "kuang "

Case -16393 To -16221: pinyin = "kui "

Case -16220 To -16217: pinyin = "kun "

Case -16216 To -16213: pinyin = "kuo "

Case -16212 To -16206: pinyin = "la "

Case -16205 To -16203: pinyin = "lai "

Case -16202 To -16188: pinyin = "lan "

Case -16187 To -16181: pinyin = "lang "

Case -16180 To -16172: pinyin = "lao "

Case -16171 To -16170: pinyin = "le "

Case -16169 To -16159: pinyin = "lei "

Case -16158 To -16156: pinyin = "leng "

Case -16155 To -15960: pinyin = "li "

Case -15959 To -15959: pinyin = "lia "

Case -15958 To -15945: pinyin = "lian "

Case -15944 To -15934: pinyin = "liang "

Case -15933 To -15921: pinyin = "liao "

Case -15920 To -15916: pinyin = "lie "

Case -15915 To -15904: pinyin = "lin "

Case -15903 To -15890: pinyin = "ling "

Case -15889 To -15879: pinyin = "liu "

Case -15878 To -15708: pinyin = "long "

Case -15707 To -15702: pinyin = "lou "

Case -15701 To -15682: pinyin = "lu "

Case -15681 To -15668: pinyin = "lv "

Case -15667 To -15662: pinyin = "luan "

Case -15661 To -15660: pinyin = "lue "

Case -15659 To -15653: pinyin = "lun "

Case -15652 To -15641: pinyin = "luo "

Case -15640 To -15632: pinyin = "ma "

Case -15631 To -15626: pinyin = "mai "

Case -15625 To -15455: pinyin = "man "

Case -15454 To -15449: pinyin = "mang "

Case -15448 To -15437: pinyin = "mao "

Case -15436 To -15436: pinyin = "me "

Case -15435 To -15420: pinyin = "mei "

Case -15419 To -15417: pinyin = "men "

Case -15416 To -15409: pinyin = "meng "

Case -15408 To -15395: pinyin = "mi "

Case -15394 To -15386: pinyin = "mian "

Case -15385 To -15378: pinyin = "miao "

Case -15377 To -15376: pinyin = "mie "

Case -15375 To -15370: pinyin = "min "

Case -15369 To -15364: pinyin = "ming "

Case -15363 To -15363: pinyin = "miu "

Case -15362 To -15184: pinyin = "mo "

Case -15183 To -15181: pinyin = "mou "

Case -15180 To -15166: pinyin = "mu "

Case -15165 To -15159: pinyin = "na "

Case -15158 To -15154: pinyin = "nai "

Case -15153 To -15151: pinyin = "nan "

Case -15150 To -15150: pinyin = "nang "

Case -15149 To -15145: pinyin = "nao "

Case -15144 To -15144: pinyin = "ne "

Case -15143 To -15142: pinyin = "nei "

Case -15141 To -15141: pinyin = "nen "

Case -15140 To -15140: pinyin = "neng "

Case -15139 To -15129: pinyin = "ni "

Case -15128 To -15122: pinyin = "nian "

Case -15121 To -15120: pinyin = "niang "

Case -15119 To -15118: pinyin = "niao "

Case -15117 To -15111: pinyin = "nie "

Case -15110 To -15110: pinyin = "nin "

Case -15109 To -14942: pinyin = "ning "

Case -14941 To -14938: pinyin = "niu "

Case -14937 To -14934: pinyin = "nong "

Case -14933 To -14931: pinyin = "nu "

Case -14930 To -14930: pinyin = "nv "

Case -14929 To -14929: pinyin = "nuan "

Case -14928 To -14927: pinyin = "nue "

Case -14926 To -14923: pinyin = "nuo "

Case -14922 To -14922: pinyin = "o "

Case -14921 To -14915: pinyin = "ou "

Case -14914 To -14909: pinyin = "pa "

Case -14908 To -14903: pinyin = "pai "

Case -14902 To -14895: pinyin = "pan "

Case -14894 To -14890: pinyin = "pang "

Case -14889 To -14883: pinyin = "pao "

Case -14882 To -14874: pinyin = "pei "

Case -14873 To -14872: pinyin = "pen "

Case -14871 To -14858: pinyin = "peng "

Case -14857 To -14679: pinyin = "pi "

Case -14678 To -14675: pinyin = "pian "

Case -14674 To -14671: pinyin = "piao "

Case -14670 To -14669: pinyin = "pie "

Case -14668 To -14664: pinyin = "pin "

Case -14663 To -14655: pinyin = "ping "

Case -14654 To -14646: pinyin = "po "

Case -14645 To -14631: pinyin = "pu "

Case -14630 To -14595: pinyin = "qi "

Case -14594 To -14430: pinyin = "qia "

Case -14429 To -14408: pinyin = "qian "

Case -14407 To -14400: pinyin = "qiang "

Case -14399 To -14385: pinyin = "qiao "

Case -14384 To -14380: pinyin = "qie "

Case -14379 To -14369: pinyin = "qin "

Case -14368 To -14356: pinyin = "qing "

Case -14355 To -14354: pinyin = "qiong "

Case -14353 To -14346: pinyin = "qiu "

Case -14345 To -14171: pinyin = "qu "

Case -14170 To -14160: pinyin = "quan "

Case -14159 To -14152: pinyin = "que "

Case -14151 To -14150: pinyin = "qun "

Case -14149 To -14146: pinyin = "ran "

Case -14145 To -14141: pinyin = "rang "

Case -14140 To -14138: pinyin = "rao "

Case -14137 To -14136: pinyin = "re "

Case -14135 To -14126: pinyin = "ren "

Case -14125 To -14124: pinyin = "reng "

Case -14123 To -14123: pinyin = "ri "

Case -14122 To -14113: pinyin = "rong "

Case -14112 To -14110: pinyin = "rou "

Case -14109 To -14100: pinyin = "ru "

Case -14099 To -14098: pinyin = "ruan "

Case -14097 To -14095: pinyin = "rui "

Case -14094 To -14093: pinyin = "run "

Case -14092 To -14091: pinyin = "ruo "

Case -14090 To -14088: pinyin = "sa "

Case -14087 To -14084: pinyin = "sai "

Case -14083 To -13918: pinyin = "san "

Case -13917 To -13915: pinyin = "sang "

Case -13914 To -13911: pinyin = "sao "

Case -13910 To -13908: pinyin = "se "

Case -13907 To -13907: pinyin = "sen "

Case -13906 To -13906: pinyin = "seng "

Case -13905 To -13897: pinyin = "sha "

Case -13896 To -13895: pinyin = "shai "

Case -13894 To -13879: pinyin = "shan "

Case -13878 To -13871: pinyin = "shang "

Case -13870 To -13860: pinyin = "shao "

Case -13859 To -13848: pinyin = "she "

Case -13847 To -13832: pinyin = "shen "

Case -13831 To -13659: pinyin = "sheng "

Case -13658 To -13612: pinyin = "shi "

Case -13611 To -13602: pinyin = "shou "

Case -13601 To -13407: pinyin = "shu "

Case -13406 To -13405: pinyin = "shua "

Case -13404 To -13401: pinyin = "shuai "

Case -13400 To -13399: pinyin = "shuan "

Case -13398 To -13396: pinyin = "shuang "

Case -13395 To -13392: pinyin = "shui "

Case -13391 To -13388: pinyin = "shun "

Case -13387 To -13384: pinyin = "shuo "

Case -13383 To -13368: pinyin = "si "

Case -13367 To -13360: pinyin = "song "

Case -13359 To -13357: pinyin = "sou "

Case -13356 To -13344: pinyin = "su "

Case -13343 To -13341: pinyin = "suan "

Case -13340 To -13330: pinyin = "sui "

Case -13329 To -13327: pinyin = "sun "

Case -13326 To -13319: pinyin = "suo "

Case -13318 To -13148: pinyin = "ta "

Case -13147 To -13139: pinyin = "tai "

Case -13138 To -13121: pinyin = "tan "

Case -13120 To -13108: pinyin = "tang "

Case -13107 To -13097: pinyin = "tao "

Case -13096 To -13096: pinyin = "te "

Case -13095 To -13092: pinyin = "teng "

Case -13091 To -13077: pinyin = "ti "

Case -13076 To -13069: pinyin = "tian "

Case -13068 To -13064: pinyin = "tiao "

Case -13063 To -13061: pinyin = "tie "

Case -13060 To -12889: pinyin = "ting "

Case -12888 To -12876: pinyin = "tong "

Case -12875 To -12872: pinyin = "tou "

Case -12871 To -12861: pinyin = "tu "

Case -12860 To -12859: pinyin = "tuan "

Case -12858 To -12853: pinyin = "tui "

Case -12852 To -12850: pinyin = "tun "

Case -12849 To -12839: pinyin = "tuo "

Case -12838 To -12832: pinyin = "wa "

Case -12831 To -12830: pinyin = "wai "

Case -12829 To -12813: pinyin = "wan "

Case -12812 To -12803: pinyin = "wang "

Case -12802 To -12608: pinyin = "wei "

Case -12607 To -12598: pinyin = "wen "

Case -12597 To -12595: pinyin = "weng "

Case -12594 To -12586: pinyin = "wo "

Case -12585 To -12557: pinyin = "wu "

Case -12556 To -12360: pinyin = "xi "

Case -12359 To -12347: pinyin = "xia "

Case -12346 To -12321: pinyin = "xian "

Case -12320 To -12301: pinyin = "xiang "

Case -12300 To -12121: pinyin = "xiao "

Case -12120 To -12100: pinyin = "xie "

Case -12099 To -12090: pinyin = "xin "

Case -12089 To -12075: pinyin = "xing "

Case -12074 To -12068: pinyin = "xiong "

Case -12067 To -12059: pinyin = "xiu "

Case -12058 To -12040: pinyin = "xu "

Case -12039 To -11868: pinyin = "xuan "

Case -11867 To -11862: pinyin = "xue "

Case -11861 To -11848: pinyin = "xun "

Case -11847 To -11832: pinyin = "ya "

Case -11831 To -11799: pinyin = "yan "

Case -11798 To -11782: pinyin = "yang "

Case -11781 To -11605: pinyin = "yao "

Case -11604 To -11590: pinyin = "ye "

Case -11589 To -11537: pinyin = "yi "

Case -11536 To -11359: pinyin = "yin "

Case -11358 To -11341: pinyin = "ying "

Case -11340 To -11340: pinyin = "yo "

Case -11339 To -11325: pinyin = "yong "

Case -11324 To -11304: pinyin = "you "

Case -11303 To -11098: pinyin = "yu "

Case -11097 To -11078: pinyin = "yuan "

Case -11077 To -11068: pinyin = "yue "

Case -11067 To -11056: pinyin = "yun "

Case -11055 To -11053: pinyin = "za "

Case -11052 To -11046: pinyin = "zai "

Case -11045 To -11042: pinyin = "zan "

Case -11041 To -11039: pinyin = "zang "

Case -11038 To -11025: pinyin = "zao "

Case -11024 To -11021: pinyin = "ze "

Case -11020 To -11020: pinyin = "zei "

Case -11019 To -11019: pinyin = "zen "

Case -11018 To -11015: pinyin = "zeng "

Case -11014 To -10839: pinyin = "zha "

Case -10838 To -10833: pinyin = "zhai "

Case -10832 To -10816: pinyin = "zhan "

Case -10815 To -10801: pinyin = "zhang "

Case -10800 To -10791: pinyin = "zhao "

Case -10790 To -10781: pinyin = "zhe "

Case -10780 To -10765: pinyin = "zhen "

Case -10764 To -10588: pinyin = "zheng "

Case -10587 To -10545: pinyin = "zhi "

Case -10544 To -10534: pinyin = "zhong "

Case -10533 To -10520: pinyin = "zhou "

Case -10519 To -10332: pinyin = "zhu "

Case -10331 To -10330: pinyin = "zhua "

Case -10329 To -10329: pinyin = "zhuai "

Case -10328 To -10323: pinyin = "zhuan "

Case -10322 To -10316: pinyin = "zhuang "

Case -10315 To -10310: pinyin = "zhui "

Case -10309 To -10308: pinyin = "zhun "

Case -10307 To -10297: pinyin = "zhuo "

Case -10296 To -10282: pinyin = "zi "

Case -10281 To -10275: pinyin = "zong "

Case -10274 To -10271: pinyin = "zou "

Case -10270 To -10263: pinyin = "zu "

Case -10262 To -10261: pinyin = "zuan "

Case -10260 To -10257: pinyin = "zui "

Case -10256 To -10255: pinyin = "zun "

Case -10254 To -10254: pinyin = "zuo "

Case Else: pinyin = p

End Select

End Function

Function getpy(str)

For i = 1 To Len(str)

getpy = getpy & pinyin(Mid(str, i, 1))

Next i

End Function

图片 10

第六步,输入拼音转变函数。

图片 11

飞速复制函数完毕改造。

图片 12

EXCEL怎么样自动将姓名转变为拼音操作演示达成。

李先生简要介绍:创办计算机高校,全职从事Computer讲授二十余年。

头条号:李先生计算机教学课堂,轻便实用,清晰明了,专门系统传授计算机知识,软件使用本领,接待关心。

回答:

此时此刻用Excel须求动用VBA来兑现,操作过于复杂,在小白不懂的动静下,也或者会有安全性难题。

提出换一种思路,那正是使用网站来产生拼音调换后,再粘贴回Excel中。操作步骤如下:

打开转换网站

操作步骤如下

图片 13

回答:

英特网有不少用VBA制作而成的自定义函数,寻找下姓名转拼音能够找到大多的。然后能够像使用普通工作表函数那么选拔,生僻字保险持续,但三千常用汉字正确率依旧不行高的

本文由澳门太阳娱乐集团官网发布于软件下载,转载请注明出处:EXCEL如何自动将姓名转换为拼音?

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文