Powered By Blogger

2021年5月21日 星期五

EXCEL 學習字串處理函式了解將"王小明"自動變成"王O明"的方法!

常常在學校榜單,活動名單...等各式各樣的有著一大串姓名的表格中,公告者為了防止個資外洩,通常會將每個人姓名中間那個字改成"O"或"X"等字樣,例如將"王小明"改成"王O明"。若名單只有少數幾個人,就用人工一個一個改,花的時間是不會很多,可以很輕鬆很愉快,但人數若是成千上百,那要純手工,那可真的會改到手抽筋,曠日廢時,愉快不起來啊!

當然,若不要用手工笨方法,那就要知道自動好方法快方法是怎麼做到的。而解決這個問題的最佳工具非EXCEL莫屬(GOOGLE試算表也可以),底下就從解決這種問題會用到的幾個內建文字處理函式開始學習:

(註:處理這個問題的方式有很多,不是只有單一方式而已,上網找可以找到一大堆其它方式......)


1.先執行本機的MicroSoft  EXCEL  或  上網登錄GOOGLE雲端,選擇試算表

左:本機MicroSoft  EXCEL               右:GOOGLE雲端試算表



2.在空白表格中,先輸入下列資料(A欄的範例文字,可以自己隨意輸入)

左:MicroSoft  EXCEL                       右:GOOGLE試算表




3.使用LEN函數:
 (1)語法:LEN(字串)
 (2)功能:括號內只有一個字串引數,會傳回字串的字元數
 (3)範例:LEN("我要賺大錢") = 5 (共5個字)
     範例:LEN(A2) = A2儲存格內字串的字數
 (4)依下圖操作:
       在B2儲存格使用LEN函式,自動計算A2儲存格內文字的字數,並在B2顯示
   
    (4-1)MicroSoft EXCEL做法:(若不按下方順序操作,也可以在"fx"右邊的空格內直接
            以建盤輸入' =LEN(A2)' 會有相同的效果)

EXCEL:步驟1為點選B2儲存格。步驟2為點選"fx"字樣,即在B2儲存格中使用函式功能。步驟3下拉選單中可以看見EXCEL裡有很多類型的內建函式,我們選的是"文字"。步驟4選單中可以看到很多同屬"文字"類的函式,我們選的是"LEN"。步驟6輸入"A2"是指要計算A2儲存格內文字的字數。


     (4-2)GOOGLE試算表做法:(若不按下方順序操作,也可以在"fx"右邊的空格內直接
             以建盤輸入' =LEN(A2)' 會有相同的效果)

GOOGLE試算表:步驟1為滑鼠點選B2儲存格。步驟6則是在B2函式內輸入"A2"。其餘操作類似EXCEL

 (5)操作後結果:
左:MicroSoft  EXCEL                       右:GOOGLE試算表





4.使用IF函數:[屬於"邏輯"類的函式,不屬於"文字"類函式][經常用到的函式]
 (1)語法:IF(判斷式  ,   "是"的陳述式  ,  "否"的陳述式)
 (2)功能:括號內有三個引數,以2個逗號隔開,左邊第1引數判斷式的結果只有 "是" 和 "否"
                  "是":執行括號內中間第2引數的陳述式       "否":執行括號內右邊第3引數的陳述式
 (3)範例:IF(A2>1000  ,  "超出預算"  ,  "尚有餘額")
                 意思:若A2儲存格內的數字大於1000,則顯示"超出預算"
                             否則顯示"尚有餘額"
     範例:IF(A2>1000  ,  "很多"  ,  IF(A2<500  ,  "很少"  ,  "中間") )
                 這是一個IF函式裡又包含了另一個IF函式的巢狀寫法
                 意思:若A2儲存格內的數字大於1000,則直接顯示"很多"
                             否則再次判斷A2儲存格內的數字若小於500,則顯示"很少"
                             否則顯示"中間"(即表示A2內之值介於500~1000之間)
 (4)依下圖操作:(請先完成上方第1列說明及左側A欄數字)
      先點B2儲存格,再於上方輸入' =IF(A2>1000  ,  "超出預算"  ,  "尚有餘額") '
      再點C2儲存格,再於上方輸入' =IF(A2>1000  ,  "很多"  ,  IF(A2<500  ,  "很少"  ,  "中間") ) '

EXCEL及GOOGLE試算表做法相同


 (5)操作後結果:
EXCEL及GOOGLE試算表做法相同




5.使用REPT函數:
 (1)語法:REPT(字串  ,  重複次數)
 (2)功能:括號內有二個引數,以1個逗號隔開,左邊第1引數是要被重複顯示的字串
                  右邊第2引數是字串要重複的次數
 (3)範例:REPT("棒"  ,  5)
                 意思:連續顯示5次"棒",即 => 棒棒棒棒棒
     範例:REPT("很好"  ,  3)
                 意思:連續顯示5次"很好",即 => 很好很好很好
     範例:REPT(A2  ,  B2)
                 意思:將A2儲存格內的字串,連續顯示B2次
 (4)依下圖操作:(請先完成上方第1列說明及左側A欄文字及B欄次數)
      先點C2儲存格,再於上方輸入' =REPT(A2  ,  B2) '
EXCEL及GOOGLE試算表做法相同

 (5)操作後結果:
EXCEL及GOOGLE試算表做法相同





6.使用REPLACE函數:會根據您指定的字元數,以不同的文字字串來取代文字字串的某一部分
 (1)語法:REPLACE(字串  ,  開始位置  ,  取代字數  ,  要取代的文字)
 (2)功能:括號內有四個引數,以3個逗號隔開,左邊第1引數是指其中某些字元要被取代的字串
                  第2引數是指字串中要被取代的開始字元位置
                  第3引數是指要被取代的字元數,第4引數是指要取代的文字
 (3)範例:REPLACE("classroom"  ,  3  ,  4  ,  "*")
                 意思:"classroom"的第3字元(即"a")開始,連續4個字元(即"assr"),以"*"取代
                             最後結果變成 => "cl*oom"
     範例:REPLACE("我有5008元"  ,  2  ,  3  ,  "X")
                意思:"我有5008元"的第2字元(即"有")開始,連續3個字元(即"有50"),以"X"取代
                             最後結果變成 => "我X08元"
     範例:REPLACE("abcde"  ,  3  ,  2  ,  "西西西西")
                意思:"abcde"的第3字元(即"c")開始,連續2個字元(即"cd"),以"西西西西"取代
                             最後結果變成 => "ab西西西西e"    
     範例:REPLACE("rstuvwxyz"  ,  5  ,  4  ,  REPT("*"  ,  4))
                意思:"rstuvwxyz"的第5字元(即"v")開始,連續4個字元(即"vwxy"),以4次"*"取代
                             最後結果變成 => "rstu****z"     
 (4)依下圖操作:(請先完成上方第1列說明及左側A欄文字)
      先點B2儲存格,再於上方輸入' =REPLACE(A2  ,  3  ,  4  ,  "*") '
      再點B3儲存格,再於上方輸入' =REPLACE(A3  ,  2  ,  3  ,  "X") '
      再點B4儲存格,再於上方輸入' =REPLACE(A4  ,  3  ,  2  ,  "西西西西") '
      再點B5儲存格,再於上方輸入' =REPLACE(A5  ,  5  ,  4  ,  REPT("*"  ,  4)) '
EXCEL及GOOGLE試算表做法相同

 (5)操作後結果:
EXCEL及GOOGLE試算表做法相同




7.熟悉了上面4個函式的意思及練習後,接下來就要進入主題囉!

   首先考量一般人的姓名至少為2個字,多數為3個字,4個字以上的也有,但就比較少了

 (1)若姓名只有2個字,就沒有所謂的中間字,處理方式較簡單,保留第1個字,

      並將第2個字取代成"O"即可,即 REPLACE(A2 , 2 , 1 , "O")

 (2)若姓名至少3個字,就會有所謂的中間字,假設顯示時要保留姓名頭尾2字,則

      姓名3個字的,就要將中間的1個字(3-2)取代成1個"O",

      姓名4個字的,就要將中間的2個字(4-2)取代成2個"O",

      姓名5個字的,就要將中間的3個字(5-2)取代成3個"O",

      ......

      姓名n個字的,就要將中間的n-2個字取代成n-2個"O",

 (3)因此,函式一開始便要先判斷姓名是2個字(yes)還是超過2個字(no),若為yes,則執行

      上面(1)的內容,若為no,則執行上述(2)的內容,完整函式如下:

完整函式及流程圖


 (4)依下圖操作:(請先完成上方第1列說明及左側A欄姓名)
      先點B2儲存格,再於上方輸入完整函式
EXCEL及GOOGLE試算表做法相同

 (5)操作後結果:
EXCEL及GOOGLE試算表做法相同




8.大功告成!

   其實EXCEL或GOOGLE試算表的內建函式有非常多,包羅萬象,且網路上都有詳盡的介紹

   及範例,只要肯接觸,必定可學習到很多實用的方法及工具,解決學習上或工作上的眾多

   問題。

9.謝謝收看!






本網誌其它"軌跡"

// 以下為新增的程式碼 FOR 文章分類 // 以上為新增的程式碼 FOR 文章分類