常常在學校榜單,活動名單...等各式各樣的有著一大串姓名的表格中,公告者為了防止個資外洩,通常會將每個人姓名中間那個字改成"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)' 會有相同的效果)
(5)操作後結果:
(5)操作後結果:
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)的內容,完整函式如下:
8.大功告成!
其實EXCEL或GOOGLE試算表的內建函式有非常多,包羅萬象,且網路上都有詳盡的介紹
及範例,只要肯接觸,必定可學習到很多實用的方法及工具,解決學習上或工作上的眾多
問題。
9.謝謝收看!
GOOGLE試算表:步驟1為滑鼠點選B2儲存格。步驟6則是在B2函式內輸入"A2"。其餘操作類似EXCEL |
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欄數字)
(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 , "很少" , "中間") ) '
再點C2儲存格,再於上方輸入' =IF(A2>1000 , "很多" , IF(A2<500 , "很少" , "中間") ) '
(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欄次數)
(1)語法:REPT(字串 , 重複次數)
(2)功能:括號內有二個引數,以1個逗號隔開,左邊第1引數是要被重複顯示的字串
右邊第2引數是字串要重複的次數
(3)範例:REPT("棒" , 5)
意思:連續顯示5次"棒",即 => 棒棒棒棒棒
範例:REPT("很好" , 3)
意思:連續顯示5次"很好",即 => 很好很好很好
範例:REPT(A2 , B2)
意思:將A2儲存格內的字串,連續顯示B2次
(4)依下圖操作:(請先完成上方第1列說明及左側A欄文字及B欄次數)
6.使用REPLACE函數:會根據您指定的字元數,以不同的文字字串來取代文字字串的某一部分
(1)語法:REPLACE(字串 , 開始位置 , 取代字數 , 要取代的文字)
(2)功能:括號內有四個引數,以3個逗號隔開,左邊第1引數是指其中某些字元要被取代的字串
第2引數是指字串中要被取代的開始字元位置
(1)語法:REPLACE(字串 , 開始位置 , 取代字數 , 要取代的文字)
(2)功能:括號內有四個引數,以3個逗號隔開,左邊第1引數是指其中某些字元要被取代的字串
第2引數是指字串中要被取代的開始字元位置
第3引數是指要被取代的字元數,第4引數是指要取代的文字
(3)範例:REPLACE("classroom" , 3 , 4 , "*")
意思:"classroom"的第3字元(即"a")開始,連續4個字元(即"assr"),以"*"取代
(3)範例:REPLACE("classroom" , 3 , 4 , "*")
意思:"classroom"的第3字元(即"a")開始,連續4個字元(即"assr"),以"*"取代
最後結果變成 => "cl*oom"
範例:REPLACE("我有5008元" , 2 , 3 , "X")
範例: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儲存格,再於上方輸入完整函式
8.大功告成!
其實EXCEL或GOOGLE試算表的內建函式有非常多,包羅萬象,且網路上都有詳盡的介紹
及範例,只要肯接觸,必定可學習到很多實用的方法及工具,解決學習上或工作上的眾多
問題。
9.謝謝收看!
沒有留言:
張貼留言