excel查找函數應用:如何提取姓名的拼音首字母

2020-12-06 部落窩教育H

編按:哈嘍,大家好!如果此刻讓你快速提取漢字拼音的首字母,你會怎麼做呢?相信大多數小夥伴面對這個問題時,都會蒙圈,可能會想「這應該得用VBA解決吧,函數應該不行吧。」其實呀,這個問題用大家都會的VLOOKUP函數就能搞定,趕緊來看看吧!

在某些特殊的情況下,我們可能需要用到拼音首字母。

例如A列是員工姓名,需要在B列填寫對應的拼音首字母:

如果遇上這種問題該怎麼辦?

據說需要VBA才能做到,可是對於連函數都還沒學會幾個的普通用戶來說,難道真的只能一個一個手動輸入嗎?

實際上使用一個大家都非常熟悉的VLOOKUP函數也是可以搞定這個問題的,不過公式嘛,就有點嚇人,是這個樣子的:

看到這個公式是不是都蒙圈了。

可千萬不要被這個表面上看起來很誇張的公式嚇到,我們換一種寫法,也許你就看明白了。

怎麼樣,這個公式是不是有點看明白了。

與第一個公式的區別就在於,後面這個公式是把VLOOKUP的第二參數放在單元格區域中了,公式的本質並沒有什麼區別。

因為名單中的姓名都不超過三個字,所以先用MID函數將每個字單獨提取出來,使用VLOOKUP得到每個字的拼音首字母,再把三個VLOOKUP函數用&連接起來。

因此公式的核心其實是=VLOOKUP(MID(A2,1,1),$D$1:$E$24,2)。

對於VLOOKUP函數的用法,大家應該都比較熟悉了,格式是:VLOOKUP(查找值,查找區域,在第幾列找,怎麼找)。

在本例中,要重點說明的有下面幾個要點:

1.第一參數可以使用公式,本例中分別使用MID(A2,1,1)、MID(A2,2,1)和MID(A2,3,1)作為查找值;

2.本例中的VLOOKUP函數隻用了三個參數,同時省略了第三參數2後面的逗號,這種寫法表示使用了VLOOKUP函數模糊匹配,與第四參數為1的功能相同;

3.關於省略VLOOKUP函數最後一個參數的意義,一定要注意一點,省略參數而不省略逗號時,表示精確匹配,與第四參數為0的功能相同;

4.有時候為了避免使用單元格區域作為查找區域,第二參數可以使用常量數組的方式,本例中開頭的公式就是用到了常量數組。

當然,要完全明白這個公式的原理,僅僅解釋VLOOKUP函數還是不夠的,D列和E列又是什麼意思,這些字都是怎麼來的,為什麼非得用這些奇奇怪怪的字呢?

簡單來說,漢字在電腦裡是有一個機器編碼與之對應的,同時是按照一定的順序排列的,或者可以理解,漢字也是有「大小」的。

為了讓大家更容易理解,我們可以先用數字來做個示例:

假如有0到100之間的數字,十個數字對應同一個字母(紅框中的對應規則),我們可以使用公式=VLOOKUP(G2,$I$2:$J$12,2)得到某個數字所對應的字母。

VLOOKUP函數的這種用法就是模糊匹配,或者說是區間匹配,之前我們出過專門介紹這個用法的教程。

簡單來說,要使用模糊匹配,有兩個要點要牢記:

1.查找區域的數據必須是升序排列的;

2.區域的首列是區間所對應的下限值。

如果理解了這些要點,再來看拼音的問題,就很容易明白,D列的每個字都是該拼音開頭的第一個字(下限)。

最後一個問題,有細心的同學也許發現了,數據是從第二行開始的,為什麼公式裡的區域是從第一行開始的?

其實第一個字上面並不是空的,而是一個假空(由公式得到的空值)。

之所以這樣做,是為了避免公式中出現錯誤,例如當名字是兩個字的時候,第三個VLOOKUP中的MID就會得到一個空值:

如果在查找區域中,沒有空值的話,公式會得到錯誤值:

好了,關於這個提取拼音首字母的公式就解釋到這裡,可能這種問題並不常見,但今天的主要目的還是為了通過這個特殊的實例來分析一些公式中的細節,細節掌握的越多,你就離高手的距離越近!

最後把不需要輔助區域的公式分享給大家,有需要的話可以自己保存起來,萬一有一天用上的時候,這個公式一定能讓同事對你刮目相看:

=VLOOKUP(MID(A2,1,1),{"",0;"吖","A";"八","B";"攃","C";"咑","D";"鵽","E";"發","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"媽","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"},2)&VLOOKUP(MID(A2,2,1),{"",0;"吖","A";"八","B";"攃","C";"咑","D";"鵽","E";"發","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"媽","M";"乸","N";"噢","O";"帊","P";"七","Q";"冄","R";"仨","S";"他","T";"屲","W";"夕","X";"丫","Y";"帀","Z"},2)&VLOOKUP(MID(A2,3,1),{"",0;"吖","A";"八","B";"攃","C";"咑","D";"鵽","E";"發","F";"旮","G";"哈","H";"丌","J";"咔","K";"垃","L";"媽",

嗯,理解操作原理,然後你就可以自由發揮了!

****部落窩教育-excel提取拼音字母****

原創:老菜鳥/部落窩教育(未經同意,請勿轉載)

相關焦點

  • excel篩選技巧:不用函數公式也能快速多對多查找
    說到excel中的篩選,想必大家早已是了如指掌,不過增強版的篩選,你聽說過嗎?它可比普通的篩選厲害多了,不僅能實現excel中的一對多查找,就連複雜的多對多查找也不在話下!趕緊來看看吧!或許有些朋友也會想到那個最近很火,據說是可以讓VLOOKUP提前退休的XLOOKUP函數實現一對多查找。其實是使用公式還是用其它方式解決問題,也要看應用場景的,比如只是臨時的需要,那就完全不需要使用這麼繁瑣的萬金油公式,也不需要這個像空中樓閣似的XLOOKUP函數出馬(能使用這個函數的Excel少得可憐)。
  • EXCEL函數公式大全之利用FIND函數MID函數提取字符串中間指定文本
    EXCEL函數公式大全之利用FIND函數和MID函數組合提取字符串中間指定文本。EXCEL函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數FIND函數和MID函數。
  • EXCEL函數公式大全之利用PROPER函數LOWER函數實現英文大小寫轉換
    EXCEL函數公式大全之利用PROPER函數LOWER函數實現英文字母大小寫轉換。EXCEL函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數PROPER函數LOWER函數。
  • excel函數應用技巧:如何按不同要求,改變數字格式
    【ANSWER 1】【函數解析】我們可以直接將A2單元格的格式為日期格式,也可以得到日期值。但是我們是來玩函數的,那就還是用函數來解決吧。TEXT函數,格式寫作「yyyy-mm-dd」,年月日的英文首字母。【ANSWER 2】同答案1一樣依然使用TEXT函數,但是表達式改變了,「e-mm-dd」。這裡的e相當於yyyy,即4位的年份表達式。
  • 這個excel查找函數也很重要,index函數的使用方法
    我們之前學過幾個excel查找函數,分別是vlookup函數和hlookup函數以及match函數,這次我們還要學習另外一個查找函數,這個查找函數就是index函數,index函數是用來引用我們所需要的信息,主要分連續區域和非連續區域內的引用兩種,連續區域裡使用index公式是=index
  • excel中的經典查找引用函數之lookup函數的使用
    excel表格的眾多函數中,我們常用的查找引用函數大致有3個,分別是有縱向查找功能的vlookup函數、有橫向查找功能的hlookup函數和可以任意多條件查找引用的lookup函數。下面我們來介紹lookup函數的用法。
  • Excel中large函數直接在公式中查找最大值
    在excel中,如果想要找到一組數的最大值,可以藉助於max函數輕鬆解決。但是如果想在一組數據裡面,找到第二大的數據怎麼進行呢?或者說,我們在編寫函數公式具體應用的過程中,應該如何設置呢?在這種情況下,我們就會使用到large函數,藉助於他我們來尋找一組數據中的最值問題。
  • Excel中的單條件計數函數countif
    COUNTIF函數會統計某個區域內符合您指定的單個條件的單元格數量,記得函數返回值是滿足給定條件的單元格的數量。例如,我們可以計算以某個特定字母開頭的所有單元格的數量,或者可以計算包含大於或小於指定數字的所有單元格的數量。
  • excel中的small函數和large函數,與其他函數結合還有這功能!
    在excel中,large函數和small函數用的人比較少,但是用的少並不代表這兩個函數沒有用,反而十分,今天小編就專門寫了這篇文章來介紹一下這兩個函數,一起學習一下吧、一、基本用法。對於large函數和small函數,都只有兩個參數,分別為large(數值區域,返回的第幾個最大值),small(數值區域,返回的第幾個最小值)。
  • 如何提取上下班的打卡時間?使用vlookup與lookup函數就可以了
    Hello,大家好,今天跟大家分享下午我們如何查找數據中的第一條記錄和最後一條記錄,這也是一個粉絲提問到的問題,他們公司的打卡機是感應式的,每當人經過就會打一次卡,每天都會生成很多打卡記錄,每天都花費很多時間來統計公司員工的上下班時間。
  • 必學Excel查找與引用函數,將表格變成智能資料庫(上)
    要想在海量數據中,根據條件查找數值,查找與引用函數必不可少。今天先學會3個函數,明天工作匯報就小露一手。例如,根據姓名查找身份證號,具體操作方法如下。在工作表中選擇要存放結果的單元格B11,輸入函數「=LOOKUP(A11, A2:A8,B2:B8)」,按下【Enter】鍵,即可得到A11單元格中員工姓名對應的身份證號了,如下圖所示。
  • excel函數公式大全之利用LARGE函數和SUM函數提取前五名銷售額
    excel函數公式大全之利用LARGE函數和SUM函數提取前五名銷售額,excel函數與公式在工作中使用非常的頻繁,會不會使用公式直接決定了我們的工作效率,今天我們來學習一下提高我們工作效率的函數LARGE函數和SUM函數。
  • excel經典函數組合:index+match!工作中非常實用,案例解析掌握
    課程信息卡課程:《Excel天天訓練營》2.0圖文版章節:第2章-精通函數內容:定位查找(index\match)在excel函數裡面,index+match這一組函數做定位查找是非常實用的。通過index+match這一組函數就可以定位到兩個數據的交叉位置,即查詢結果。如果你沒有學會這些函數,那麼就無法應對大量數據的表格。現在,我們就來用函數公式實現excel自動化辦公。
  • excel中的替代函數——replace和substitute函數的應用實例
    在excel中,常用的替換函數有replace和substitute函數,這兩個函數都可以替換單元格中的部分內容,功能和ctrl+H的功能類似,但是使用函數的目的一方面不會破壞原數據,另一方面與其他函數結合可以實現更多功能,對於substitute的參數=substitute(單元格,被替換的字符串,新字符串,指定替換第幾個),第四個參數可以省略,表示全部替換。
  • Excel VBA函數篇-3.19大數據時代必備查找技能 萬條數據能奈我何
    當然不可能的啦,excel畢竟還是現在的主流辦公軟體,不管技術上面發展的多麼的高級,畢竟全民都是程式設計師的時代還是非常的遙遠的,並且你也不可能要求領導一定要去學習各種大數據處理知識吧,現在領導更多還是依賴於excel,數據也是集中展示在excel中的,那麼在大數據的衝擊下,excel能夠提升處理速度呢?
  • Excel函數公式:萬能查找函數Lookup函數的神應用和技巧
    提起查找函數,大家第一時間想到的肯定是Vlookup,其實大多數人不知道,Lookup才是查找函數之王,它幾乎能高效地實現Vlookup函數的所有功能,部分功能是Vlookup函數無法比擬的。一、語法結構和基本使用方法。
  • 將漢字自動轉換成拼音,別擔心,1分鐘就可以搞定啦
    大家好,今天跟大家分享一下如何快速提取我們漢字的拼音,這個提取拼音的操作在我們日常的工作中也是經常會遇到的,其實要得到這個結果是很容易的,我們一起來看看如何得到的吧!我們說一下這個自定義函數的幾個參數的使用方法。
  • Excel中最值得珍藏的16個函數公式
    今天蘭色分享的excel函數公式都不常用,但一旦遇到就會讓你感覺頭痛,只能到處提問和查找。今天蘭色把這些公式收集到一起。以備急時之需。:{=LOOKUP(9^9,MID(A1,MATCH(1,MID(A1,ROW(1:9),1)^0,0),ROW(1:9))*1)} 12、金額大寫轉換公式 =TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[ 13、一對多查找包含公式
  • excel技巧-使用left\right\mid函數提取欄位中某些文字符號的方法
    日常工作中,經常會遇到需要對某些單元格中的內容進行部分的欄位提取,這時候就可以用到left函數、right函數和mid函數了,這幾個函數的公式如下:=left(text,【num_chars】);=right(text,【num_chars】);=mid
  • Excel Find函數與FindB函數使用方法,含用數組一次查找多個值
    在 Excel 中,查找指定字符在源字符串中的位置,既可以用 Find函數,也可以用 FindB函數,它們都有三個參數,所不同的是,前者把漢字、字母和數字都算一個字符,後者把漢字算兩個字節,數字和字母算一個字節。