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

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

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

一、銷貨收入毛利彙總表

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 1

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

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 3

三、CONCATENATE函數

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 5

四、SUBSTITUTE函數

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 7

五、選擇性貼上複製值

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 9

六、不帶公式的報表標題

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 11

七、報表整理的小技巧

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

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 13

資料整理必學的文字函數

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

每天學習,每天充電:Excel函數文章合集

Excel文字函數整理報表:Concatenate、Substitute、選擇性貼上值 15
加入Line社群,口袋裡的Excel小教室!
Ask ChatGPT
Set ChatGPT API key
Find your Secret API key in your ChatGPT User settings and paste it here to connect ChatGPT with your Tutor LMS website.