申明: 本站飛宇網 https://feiyetopro.blogspot.com/。自網路收集整理之書籍、文章、影音僅供預覽交流學習研究，其[書籍、文章、影音]情節內容， 評論屬其個人行為， 與本網站無關。版權歸原作者和出版社所有，請在下載 24 小時內刪除，不得用作商業用途；如果您喜歡其作品，請支持訂閱購買[正版]。謝謝！
第6章 快速處理字串
處理字串的基礎操作
將儲存格中的一部分字串移至其他儲存格內
Excel的方便之處不僅限於統計數值這一項，字串在迅速處理儲存格內的內容時也發揮著強大的作用。這裡，我向大家介紹一下在處理各種資料時必須掌握的字串處理技巧。
首先，我們需要掌握如何把儲存格內的字串的一部分提取至其他儲存格的函數。這裡所謂的“一部分”，指的是譬如“左數幾個字”“右數幾個字”“中間幾個字”這樣的範圍。其對應的函數為LEFT函數、RIGHT函數和MID函數。
● =LEFT(A1,3)→抽取儲存格A1左數3個字元
● =RIGHT(A1,4)→抽取儲存格A1右數4個字元
● =MID(A1,5,2)→抽取儲存格A1第5個字開始的2個字元
LEFT函數與RIGHT函數，第一參數指定儲存格的左起或右起，第二參數指定只返回多少個字元。
MID函數，第二參數指定開始提取的位置，第三參數指定抽取的字元數。
代表日期的8位元數變為日期資料
這裡，讓我們來看一下如何運用這3個函數將表示日期的8位元數值轉換成日期資料。
我前一章中曾提到過用Excel處理日期資料時，需要像下面這樣用“/”將年、月、日隔開。
2013/11/12
但是，有些公司也會用“20131112”這樣的8位數值來表示日期。但是，這並不是常規的日期形式，只是一種數值，我們也無法運用該數值計算出天數或星期。因此，我們需要先將其轉換成日期的資料形式（序列值）。
我們可以使用DATE函數製作序列值。比如，想要製作“2014/1/1”這個日期資料，首先按照下列方式，在第一參數中指定西曆年，第二參數中指定月份，第三參數指定日期。
=DATE(2014,1,1)
那麼，如何從儲存格A2的“20131112”中提取年、月、日的數值呢？請大家按照以下思路思考。
● “年”的數值，提取儲存格A2“20131112”左數4個字元“2013”
● “月”的數值，提取儲存格A2“20131112”第5個字開始的2個字元“11”
● “日”的數值，提取儲存格A2“20131112”右數2個字元“12”
像這樣，想要從目標儲存格的資料中提取一部分文字，就要用到LEFT函數、MID函數和RIGHT函數。
想要抽取儲存格A2左數第4個字元，需要在B2中輸入以下公式：
=LEFT(A2,4)
在儲存格B2中輸入=LEFT(A2,4)
接下來匯出月份數值。請按以下方式輸入MID函數，在儲存格A2中從第5個字開始提取2個字元。
=MID(A2,5,2)
在儲存格C2中輸入=MID(A2,5,2)
最後提取日期數值。為了返回儲存格A2右數2個字元，按以下公式輸入RIGHT函數。
=RIGHT(A2,2)
在儲存格D2中輸入=RIGHT(A2,2)
像這樣，分別提取出年、月、日的資料後，再按照以下方式指定DATE函數的參數，我們就能夠得到該日期的序列值。
在儲存格E2中輸入=DATE(B2,C2,D2)
上述的操作步驟可通過以下公式在1個儲存格中集中處理。
=DATE(LEFT(A2,4),MID(A2,5,2),RIGHT(A2,2))
LEFT函數、RIGHT函數和MID函數能夠從字串的左數或右數的，以及從字串中間開始只提取指定的字元數，是字串處理的基礎操作。靈活運用這些函數，可以自由應對不同的資料處理需求。
拆分字串
只從住址中選出都道府縣
“住址如果是以都道府縣為開頭的文本，現在需要把都道府縣與下級位址資料分開”。
這種操作是拆分字串的基礎。從根本上來說，為避免後期進行這樣的操作，應該在製作工作表時“就將都道府縣放入單獨的儲存格中”。但是，如果在原工作表中已經是同時出現在一個儲存格中的狀態的話就必須要拆分儲存格了。這時，我們需要掌握如何將都道府縣的資料單獨提取到其他儲存格中。
想要解決這個問題，僅僅熟知Excel中的功能和函數是不夠的，重點在於以獨立思考出多種處理方法。
首先，我們來思考這一問題“日本的47個都道府縣名是什麼類型的資料呢？”大多為3個或4個文字吧。
其中，4個字的只有“和歌山縣”、“神奈川縣”、“鹿兒島縣”這3個縣。四個字的縣名，每個都搭著“縣”字，剩餘全部都是3個字。
明白這一點，就能按照以下邏輯，從住址儲存格中提取出都道府縣的資料了。
“如果住址儲存格中的第4字為‘縣’，只抽選左數4個字元；（第4字不是‘縣’）否則，只抽選左數3個字元”。
以上邏輯若轉換為Excel函數，就是下面的公式。
=IF(MID(A2,4,1)="縣",LEFT(A2,4),LEFT(A2,3))
複製粘貼含有這一公式的儲存格，就能做到提取所有儲存格中的都道府縣名。
在儲存格B2中輸入=IF(MID(A2,4,1)="縣",LEFT(A2,4),LEFT(A2,3))，一直複製到儲存格B12
“第4字元為‘縣’”這一條件，就是”從地址儲存格的4個字中只提取1個字元的結果即為‘縣’”，可以使用MID函數實現這一點。根據這一邏輯的判定真偽結果不同，用LEFT函數改變提取的字元數，並用IF函數指定操作。
如何從住址中區分都道府縣與下級地方行政區
那麼，在前文的表格中，如何在C列中提取除都道府縣外的市町村等級別的資料呢？
在這一點上，還是“思考方法”最為重要，並且“思考有什麼更簡便的方法”也很重要。
我們需要事先瞭解Excel具體有何種類型的函數。即便不清楚，也應該思考“使用什麼函數可以完成這項處理”？
首先，想從住址中提取都道府縣的話，使用LEFT函數確定“從左開始抽取多少文字”。另一方面，想提取出市町村的話，就要考慮“從右開始提取多少文字”，此時使用RIGHT函數。
接下來的處理需要用到能夠“計算儲存格內字元數”的函數。這時我們要用到LEN函數。LEN就是Length（長度）的意思。通過以下公式，得出儲存格A1中的字元數。
=LEN(A1)
瞭解這個函數後就會獲得好的想法。
在前文的例子中，A列中有位址資料，旁邊的B列中只提取出都道府縣的資料。在這個狀態下，想要在C列中提取都道府縣以下的行政區的資料，就需要思考在A列中需要從右數提取多少字元。答案如下：
“從位址欄的字元數中減去都道府縣欄的字元數，從A列中資料的右側開始提取。”
可以利用以下公式實現這一點。從儲存格A2內右側開始，提取儲存格A2的字元數減去儲存格B2字元數的字元數。
=RIGHT(A2,LEN(A2)-LEN(B2))
將這個公式輸入儲存格C2，一直複製到資料最後一行，就可提取出所有位址中都道府縣以下的地方行政區的資料。
在儲存格C2中輸入=RIGHT(A2,LEN(A2)-LEN(B2))，一直複製到儲存格C12
如何從姓名中分別提取姓氏和名字
運用連字號“&”可以合併字串，但是要拆分字串多少有點複雜。例如，像下面這樣用半形空格隔開姓氏和名字的情況下，怎樣才能把姓氏和名字分別提取到不同儲存格中呢？
姓氏與名字以半形空格隔開的資料
這種情況下，如有半形空格等形式的“分隔文字”（將空格視為1個字元），其實也能做到把空格前後的資料提取到不同儲存格中。我們來看一下操作順序。
① 提取姓氏
首先提取姓氏資料。提取儲存格中的姓氏就是說“從左開始提取多少儲存格內的字串中的字元”，這裡要用到LEFT函數。問題在於如何提取指定的字元數。
這裡，我們需要知道“分隔文字是第幾個字”。例如，儲存格A2中的“吉田拳”，其分隔文字是半形空格，是第3個字元。接下來，用3減去1可以得出2，也就是說從左開始抽取2個字元即可得到姓氏。換句話說就是這樣：
“分隔文字為第幾個字元，用這一數位減去1所得到的數位元，就是需要從字串左邊開始提取的字元數。”
公式如下：
=LEFT(A2,FIND(" ",A2)-1)
接下來，要注意如何在第二參數中使用FIND函數。這是用於定位指定文字在儲存格內的位置的函數。
並且，這樣連續輸入兩個引號（""）則表示“空白”，如果在雙引號之間加入半形空格（" "），則表示“半形空格”。
將這一公式輸入進儲存格B2，就可以在B2中提取儲存格A2中的半形空格之前的字元，在這裡就是姓氏資料。
在儲存格B2中輸入=LEFT(A2,FIND(" ",A2)-1)
我們既然已經知道分隔文字的半形空格是第3個字元，那麼要想提取姓氏，需要從字串左側開始應該提取的字元就是3減1，即兩個。這樣，就能夠只提取出“吉田”這兩個字，也就是位於字串最左側的兩個字元。
② 提取名字
接下來，我們來提取名字。這次需要從右開始提取，所以要用到 RIGHT函數。問題在於應該如何設定“從右側開始提取的字元數”。我們可用下面的函數公式處理。
=RIGHT(A2,LEN(A2)-FIND(" ",A2))
在儲存格C2中輸入=RIGHT(A2,LEN(A2)-FIND(" ",A2))
在RIGHT函數的第二參數中，使用LEN函數和FIND函數指定了需要提取的字元數。“用儲存格A2的字元數減去儲存格A2中半形空格所在第幾個文字後得到的數位元”，按照這樣的方式進行計算。在這個例子中，儲存格A2的字元數是4（半形空格也算作1個字元）。
半形空格是第3個文字，所以4-3=1。在儲存格A2的右側開始提取1個文字，即半形空格之後的字元，也就是提取出名字。
接下來，將公式複製到下面幾行，就能進行同樣處理了。
將公式複製到其他儲存格
但是，這種處理方式，如果遇到沒有空格的情況（此例中，姓氏與名字之間沒有半形空格）就無法使用了。最初在輸入資料時的操作會給後續的操作帶來影響，因此需要謹慎考慮。原則上來說，最好的辦法就是“儘量做細緻劃分”。後面可根據實際情況再行合併儲存格或字串。
整理資料
如何判斷字串是否相同
我們在手動輸入資料時，很容易發生格式不統一的情況。如果想將儲存格中的資料整理成統一的格式，就需要花費大量的時間。
比如，在全形格式下輸入的電話號碼。為了檢查客戶名單中是否存在重複，我們需要以電話號碼為標準，使用COUNTIF函數判定是否存在重複。這時，我們需要將所有的電話號碼整理成統一的格式。即便是分別用全形和半形格式輸入的相同的電話號碼，在Excel中也不能將其判定為相同資料。
如下例，在A列中輸入了兩個相同的電話號碼，但是儲存格A2中的資料為全形格式，儲存格A3為半形格式。儲存格B2輸有EXACT函數（下列公式），用來判定兩個字串內容是否相同。
=EXACT(A2,A3)
判斷儲存格A2與儲存格A3中的字串是否相同（儲存格B2）
EXACT函數，指定參數的兩個字串如果相同為TRUE，不同則返回FALSE。因此在該例中，結果為FALSE（不同）。
這種情況下，我們需要統一資料格式。這種操作在不少處理字串相關的函數中發揮著作用。
如何把全形字元轉化成半形字元
想要將全形字元改為半形字元，我們可以使用ASC函數。
例如，利用下面的函數公式可以將儲存格A2中的全形字元變成半形字元。
=ASC(A2)
在儲存格B2中輸入=ASC(A2)
如何刪除指定文字
接下來，將儲存格B2中刪除連字號（-）後的數值提取到儲存格C2中。像這樣，想要刪除指定文字時，可以使用SUBSTITUTE函數。SUBSTITUTE意為“替換”。
=SUBSTITUTE(B2,"-"," ")
在儲存格C2中輸入=SUBSTITUTE(B2,"-"," ")
這個函數，是在第一參數指定的字串的範圍內，是將第二參數指定的文字替換為第三參數指定的文字。在這個例子中，第三參數為" "（空白），將連字號替換為空白，就是刪除連字號。
整合這些邏輯的話，就是下面的公式。先用ASC 函數轉換為半形形式的字串，再用 SUBSTITUTE 函數將連字號替換為空白。
=SUBSTITUTE(ASC(A2),"-"," ")
把上面的公式一直複製粘貼到資料最末行，表格中所有電話號碼就會變成統一的格式。
將=SUBSTITUTE(ASC(A2),"-"," ")一直複製粘貼到資料最後一行
快速處理文字
連續輸入26個英文字母
Excel設有“自動填充”功能。比如在儲存格A2中輸入“星期一”，向下複製粘貼，就能自動從“星期一”開始連續填入資料。
在儲存格A2中輸入“星期一”，向下複製粘貼，自動連續填充資料
這種自動填充其實無法連續輸入英文字母。但是，“想要從A開始按順序連續輸入專案名稱”這種需求非常常見，解決方法有兩種。
① 使用CHAR函數
比較簡單的方法就是使用CHAR函數。它可以將參數指定的字元代碼轉換成字元。
比如，英文字母A對應的字元代碼為65。也就是說，輸入下列公式的儲存格中會顯示A。
=CHAR(65)
將字元代碼65改為66則得出B。也就是說，每次增加1個字元代碼且連續輸入CHAR函數的話，就會在表格中連續輸入英文字母。比如，想要從儲存格A2開始沿列連續輸入英文字母，那麼我們可以在參數中嵌入ROW函數，輸入以下函數公式：
=CHAR(ROW()+63)
儲存格A2位於工作表的第2行，因此這一公式的ROW函數在A2中會得出2。以2為調整數值加上63，就能得到A的字元代碼65。
輸入這個公式並複製粘貼到其他儲存格中，就能輸入連續的英文字母了。
在儲存格A2中輸入=CHAR(ROW()+63)，一直複製粘貼到儲存格A27
② 使用SUBSTITUTE函數與ADDRESS函數
下列公式，可設置成在Z之後繼續輸入AA、AB的形式。
=SUBSTITUTE(ADDRESS(1,ROW()-1,4),1"")
請大家先自行解讀一下這個公式。將這個公式輸入到第2行並向下複製粘貼。
“從無到有，需要自己創造。”
這是成年人在處理工作上的基本法則。而且，在想要做到使用Excel的基礎功能無法完成的處理時，也要有這樣的精神。
如何計算特定字元在儲存格中的數量
如果只是計算儲存格內的字元數，用LEN函數就能做到。但如果想要計算儲存格內特定字元的個數，應該怎麼做？
像下圖這樣，資料表的A列中含有表示URL的字串。
A列中含有URL資料
這時，我們應該如何計算A列中斜線符號（/）的數量，並讓其顯示在B列中呢？
像這樣，想要計算儲存格內特定的字元的數量，首先需要在儲存格B2中輸入以下公式：
=LEN(A2)-LEN(SUBSTITUTE(A2,"/","")
將這個公式一直複製到資料最末行，就能提取每個URL中的斜線符號（/）的數量。
儲存格B2中輸入=LEN(A2)-LEN(SUBSTITUTE(A2,"/"," ")，一直複製粘貼到儲存格B6
在此，使用LEN函數與SUBSTITUTE函數，提取出在儲存格A2中有多少個“/”，公式如下。然後將2個數值相減即可得出想要的結果。邏輯如下：
儲存格A2的字元數減去A2儲存格字串中除去“/”之後的文字數。
首先，用LEN（A2）計算出儲存格A2的字元數，該資料為23。LEN(SUBSTITUTE(A2,"/",""))的部分，是將SUBSTITUTE函數作為參數嵌入到LEN函數的。作為參數的SUBSTITUTE函數可以將儲存格A2中的斜線（/）替換為空白，然後再用LEN函數計算出儲存格A2中除去斜線後的字元數。由此可以得出此例中的儲存格A2的字元數為20。
二者相減後可得到3，就是儲存格A2中的斜線（/）的數量。
0 留言:
發佈留言