贊贊小屋

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值

Excel常常要將所拿到的原始資料進行文字方面處理,本文以銷貨毛利報表為例,介紹快捷符號和CONCATENATE函數合併文字,進而分享以SUBSTITUTE函數執行尋找與取代的任務。

上一篇文章介紹如何尋找取代,批次修改欄位標題。方法是運用Excel本身的命令工具,如此有兩個特性,一是程式參數是固定的,相較於函數來說,靈活度較低,二是計算結果直接體現,原有儲存格的資料就被改變了。有時候,這兩點在Excel操作上不是很便利,所以這裡再介紹如何用函數方式,達到相同效果。

一、銷貨收入毛利彙總表

標準的正規化表格,第一列是各個欄位,第二列開始是一筆一筆的資料。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第1張

二、快速合併文字符號「&」

輸入公式:「=”五月”&B2」,其中左右引號「””」表示是文字字串,「&」是連接字串的運算符號。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第2張

三、CONCATENATE函數

也可以輸入公式:「=CONCATENATE(”五月”,B2)」,CONCATENATE函數的用法效果,和「&」相同,一個直接在資料編輯列輸入,一個開窗依序輸入,就操作效率而言,其實也沒多大區別。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第3張

四、SUBSTITUTE函數

公式:「=SUBSTITUTE(B2,”加總-銷貨”,”總”)」。作用和上一篇文章的尋找取代相同:「將字串中的部份字串以新字串取代。」這裡是將欄位名稱精簡,把「加總-銷貨」以「總」取代掉。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第4張

五、選擇性貼上值

操作類似這樣整行函數引用的情形,之後常常原始資料會刪除或再重新排序,如此引用過來的結果將錯亂掉,所以最好養成習慣,公式引用好了,馬上選取範圍,先「Ctrl+C」快速複製,然後按右鍵:「選擇性貼上」、「貼上值」、「值與來源格式設定」,表示只貼上原儲存格的數值,並且套用原本的格式設定。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第5張

六、報表標題整理

仔細看資料編輯列,原來的引用已經去掉了,現在是單純的儲存格文字內容。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第6張

七、整理後報表

最後稍加整理新的欄位,先前留下來舊的欄位列刪掉。有興趣的讀者,可以試看看不選擇性貼上,直接把舊欄位刪掉,想必真正試過一次,便能理解用意所在。

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第7張

文字函數家族

這篇文章使用了函數「CONCATENATE」和「SUBSTITUTE」,其實以文字類函數而言,這兩個算是比較單純的,整個合併或取代,沒有考慮到字元位置的因素。以後如有適當實例,再來介紹諸如「FIND」、「REPLACE」、「SEARCH」等較為複雜的文字函數。

本文內容取自《會計人的Excel小教室(增訂版)》,書本仍然是最好的學習方法,省下一張電影票的錢,今天就買本書吧!

博客來網路書店網址:

Excel文字函數整理報表:CONCATENATE、SUBSTITUTE、選擇性貼上值 文字函數 第8張