還一格格複製、貼上?Excel超好用的三個小技巧,讓資料整理快速又輕鬆

2019.03.07 by
一周進步
一周進步 查看更多文章

「一周進步」(微信公眾號:weekweekup)是目前備受歡迎的青年學習型自媒體之一,致力於做出最好的 Office 教程,致力成為年輕人突破自我的學習入口。

一周進步
日常操作中,我們常常需要從別處,複製一段已經寫好的文字資料,再貼到 Excel 表格中。比如打好了一行名字,已經用空格隔開了,但貼到 Excel 時,名字並不會按照間隔分開,而是全部都在一個儲存格內,然後就只好用剪下貼上的笨拙方法將內容分離。

在上一次的文章中,我向大家介紹了Word中的文字剖析,這一次我們來看看,在Excel中怎麼玩轉資料剖析。

日常操作中,我們常常需要從別處,複製一段已經寫好的文字資料,再貼到Excel表格中。比如打好了一行名字,已經用空格隔開了,但貼到Excel時,名字並不會按照間隔分開,而是全部都在一個儲存格內,然後就只好用剪下貼上的笨拙方法將內容分離。

一周進步

每當遇到這種情況時,我都希望它是一個成熟的Excel,學會自己把資料放到合適的儲存格裡。後來我發現,雖然它不能自己把資料分隔開,但是微軟為我們提供了幾種整理的方法,讓我們一起來學習一下。

一、資料剖析工具

資料剖析的作用:可以將拆分儲存格的內容,分發到多個相鄰的儲存格的各個組成部分。例如,如果工作表中包含一列的「全名」,您可以將該列拆分為兩個列「名字」和「姓氏」的列。

一周進步

選中需要進行資料剖析的儲存格後,在「資料」選單中找到「資料工具」,點擊「資料剖析」,就會出現「資料剖析精靈」,在這個窗口中我們可以看到有兩個選項:「分隔符號」和「固定寬度」。

1. 按分隔符號

用分隔字串,如逗號或表格資料分隔每個字段。

一周進步

操作步驟:

  1. 選擇包含要拆分的文字儲存格或列。
  2. 點擊「資料」選項中的「資料剖析」。
  3. 在「資料剖析精靈」中,選擇「分隔符號」,點擊「下一步」。
  4. 選擇資料的分隔符號,可在「預覽分欄結果」窗口預覽資料。點擊「下一步」。
  5. 選擇「欄位的資料格式」或使用Excel所選格式。
  6. 選擇「目標儲存格」,在工作表上顯示資料剖析的位置。點擊「完成」。
一周進步

2. 按固定寬度

每列文字加空格對齊,設置分列線確定分割的文字間隔。

操作步驟:

  1. 選擇包含要拆分的文本的儲存格或列。
  2. 點擊「資料」選項中的「資料剖析」。
  3. 在「資料剖析精靈」中,選擇「固定寬度」,點擊「下一步」。
  4. 設置文字寬度,可在「預覽分欄結果」窗口預覽資料。點擊「下一步」。
  5. 選擇「欄位的資料格式」或使用Excel所選格式。
  6. 選擇「目標儲存格」,在工作表上顯示資料剖析的位置。點擊「完成」。
一周進步

注意:區域包含要拆分的列,可以包含任意數量的行,但不可以超過一列。必須保留足夠的空列,否則將會顯示是否取代既有數據。

3. 特殊使用方法

當我們遇到一串既沒有分隔符號,又沒有空格分開的資料時,可以將相同的一個字作為分隔點。

下圖以xxx是xxx訓練營的講師為例,由於講師的名字長度不相同,所以不能按照固定寬度進行分列,此時我們需要將「是」字作為分隔符號,在勾選分隔符號時,可以勾選「其他」並輸入「是」字,便可以將講師名字和對應課程分開。

一周進步

4. 資料剖析結果的「二次處理」

用上面同樣的方法,資料剖析後得到省和市的名稱,但是剖析之後「省」字就被吞掉了,所以我們需要對結果進行二次處理。這裡用到的是「&」這個連接符號。

在旁邊的空列中輸入「=B2&"省"」,這個操作的意思是:通過連接符號,將B2儲存格中的值和「省」字連接起來,特別要注意的是,這裡的引號必須是英文狀態下輸入的。完成連接後,將需要的結果複製,在對應的儲存格中「右鍵-貼上為值」,便完成了資料剖析結果的二次處理。

一周進步

5. 資料剖析結果的「格式處理」

Excel的儲存格格式默認的是通用格式,如果數字是0開頭的一般都顯示不出來,但如果我們需要截取出一段包含0開頭的資料串,則需要將剖析資料得到的資料進行格式處理。

在「資料剖析精靈」的第3步中,資料剖析格式默認是「通用格式」,我們可以將資料那一列的儲存格格式改為「文字」,這樣以0開頭的資料串就都能完整呈現了。

一周進步

除了可以將截取到的資料,處理成文字格式,還可以通過同樣的操作,處理成日期格式。

二、截取字串函數──MID函數

從文字字串中,指定的起始位置起,返回指定長度的字串。

一周進步

操作步驟:

  1. 輸入=mid,按Tab鍵。
  2. 選取準備從中截取字串的儲存格。
  3. 輸入準備截取的第一個字串的位置。
  4. 輸入所要截取的字串長度。
一周進步

當然,除了MID函數,LEFT和RIGHT函數也可以根據指定個數截取取字串,再結合LEN和LENB函數,便可以用LENB() - LEN()這樣的公式截取中文字串。

三、自動填入

1. 自動填入

通常情況下,我們都會用自動填滿工具填入。將游標放到選定的儲存格的右下角,會變成十字架圖標,下拉或點擊兩次可自動填入。除了使用自動填滿工具,還可以用「Ctrl + E」快速鍵填入。

因此,當我們要對一組資料長度相同的資料,或者是有規律的資料做資料剖析時,可以將函數和快速自動填入結合起來使用。

一周進步

2. Excel 2016快速填入

在2016版本中的Excel新增了快速填入,在第一行準確輸入需截取的字串,在第二行輸入第一個字符就會自動顯示出快速填入的內容,此時只要按下Enter鍵便完成快速填入。

一周進步

對於不夠有規律的資料,Excel也可以根據輸入多個資料後找到規律,完成快速填入。

一周進步

四、寫在最後

在這篇文章中,詳細介紹了「資料剖析」這一功能的使用,包括基礎使用步驟、特殊使用方法以及資料剖析後的資料處理,學會了這些技巧,日後遇到需要做類似的資料處理時,就可以省去許多繁瑣的步驟,輕鬆點幾下就能整理好資料。

以上就是我發現的一些在Excel中進行字串截取的操作技巧,資料剖析主要用於資料格式較為相似、有固定的間隔或間隔符的情況;Excel 2016中的快速填入則是字串截取函數和自動填入的結合。

用好這些技巧,就能丟掉複製貼上的笨拙操作,面對一大串資料都能快速地拆分、整理。

分享完畢,希望對你有幫助。

本文授權轉載自:一周進步;作者:清清

延伸閱讀

每日精選科技圈重要消息