極簡Excel教程 （下）——文字處理
本教程從Excel的數(shù)據(jù)錄入、數(shù)據(jù)處理、文字處理三大應(yīng)用場景入手，讓Excel小白通過45分鐘的學(xué)習(xí)，滿足90%以上的日常Excel使用需求。本篇為該Excel教程的最后一篇，文字處理部分，文末附上了Excel原始文件的獲取方法。
在閱讀本文之前，不要忘了完成上篇和中篇的學(xué)習(xí)。
四、文字處理
1、粘貼文字處理
有時候粘貼網(wǎng)頁上的表格時，明明是表格，粘出來卻變成了一段文字。例如粘貼一個pdf網(wǎng)頁中的表格（見下圖）。
粘出來的效果卻是這樣
這就很讓人崩潰了，接下來怎么辦，一個一個粘出來嗎？
這時候有兩種方法，一種是手動換行+分列；一種是先分列，再用公式或者其他方法處理；
（1）手動換行+分列
這種方法適用于行數(shù)比較少的時候，如果只是四五行，自己手動去換行就行了。如下圖
然后將在word處理完的行，粘貼至Excel里，再分列。
如下圖，首先選擇已經(jīng)分好行的數(shù)據(jù)，然后點(diǎn)擊“數(shù)據(jù)-分列-分隔符號-下一步”
而后點(diǎn)擊“空格-完成”。
即得到分割好的數(shù)據(jù)。再調(diào)整至合適列寬，呈現(xiàn)下圖結(jié)果。
（2）分列+offset公式
如果上面的表格有80行的話，一行一行地手動換行就太累了，可以采用先分列，再用offset公式的形式，可以極大地提高效率。這里為了顯示方便，仍以6行表格示例。
a)??分列
首先分列，和上面的方法一樣，選中需要分列的單元格，點(diǎn)擊“數(shù)據(jù)-分列-分隔符號-下一步-空格-完成”，得到分列好的數(shù)據(jù)。
b)??轉(zhuǎn)置數(shù)據(jù)
復(fù)制分列完成的數(shù)據(jù)，選中要粘貼區(qū)域的第一個單元格，點(diǎn)擊“粘貼-轉(zhuǎn)置”，或者右鍵“選擇性粘貼-轉(zhuǎn)置”，將之前橫向分列的數(shù)據(jù)變?yōu)樨Q著的一列。
c)??使用offset公式
offset是指定一個初始單元格或區(qū)域，根據(jù)指定的行數(shù)和列數(shù)，進(jìn)行位移，得到目標(biāo)單元格或區(qū)域。
[注意，如果offset公式結(jié)果所表示的最終區(qū)域是一個單元格，則顯示最終單元格的值；但如果最終顯示結(jié)果是一個區(qū)域，則沒有辦法正確顯示出來，會出現(xiàn)“#VALUE!”報錯。]
[offset的結(jié)果是一個區(qū)域的話，可以作為其他公式中的區(qū)域部分，例如offset定位出的新區(qū)域可以用作vlookup函數(shù)中的查找區(qū)域， VLOOKUP(L3,C2:J27,3,FALSE) 等同于VLOOKUP(L3,?OFFSET(C2:J2,0,0,26,8),3,FALSE)]
公式作用用法及實(shí)例
offset根據(jù)初始單元格或區(qū)域，進(jìn)行相應(yīng)的位移，得到最終的單元格或區(qū)域=offset ?(原始區(qū)域，移動行數(shù)，移動列數(shù)，最終區(qū)域高度，最終區(qū)域?qū)挾?
= ?OFFSET(C2:J2,0,0,26,8) → ?C2:J27
[即以C2:J2開始，不進(jìn)行行和列的偏移，重新選擇一個26列單元格高、8個單元格寬的區(qū)域，最終區(qū)域?yàn)镃2:J27]
如果不填寫最終區(qū)域高度、最終區(qū)域?qū)挾龋瑒t默認(rèn)為與引用區(qū)域相同的高度和寬度。
例如 OFFSET(A1,3,2,1,1) = OFFSET(A1,3,2)→ C4
利用offset公式，推斷出每一個單元格根據(jù)相應(yīng)的行號應(yīng)移動的數(shù)值，得出第0行的推導(dǎo)公式，隨后就可以自動填充第0行后面的數(shù)據(jù)。
[這里的行號可自行填充，一般以“0，1，2，…”開始，如下圖中的E40:E45區(qū)域里的值。行號的作用是將該行后面的數(shù)據(jù)與行號聯(lián)系起來。例如F40:I40中的數(shù)據(jù)就是以E40中的值“0”來進(jìn)行定位的。
熟練以后，可以不填充行號，直接以row()函數(shù)來構(gòu)造。row()函數(shù)可以顯示單元格所在的行號。例如E40單元格中的0，等同于公式[?= sum(row(),-40)?]]
offset公式與行號的推導(dǎo)邏輯如下。從C16單元格開始，向下移動0個、1個、2個、3個單元格，將這些值依次放在第0行，然后再移動4個、5個、6個、7個，將之放在第1行，依次類推。
[注意：這里的第0行不是Excel的行號，而是我們自己命名的行號。我們自己命名的第0行，等同于上圖表格該sheet中的第40行。]
最終我們將第0行的數(shù)據(jù)與0（E40單元格的值）聯(lián)系起來，將第1行的數(shù)據(jù)與1（E41單元格的值）聯(lián)系起來，……。
最終呈現(xiàn)結(jié)果如下。
這一方法在開始的時候最費(fèi)事，但在數(shù)據(jù)量很大時卻是最省事的方法。如果利用得當(dāng)，公式可以反復(fù)利用，能節(jié)省很多時間。
（3）分列+輔助列+排序/篩選
如果嫌公式麻煩，同時列數(shù)據(jù)較少的話，還可采用輔助列進(jìn)行排序或者篩選粘貼。
前面分類轉(zhuǎn)置的方法不再贅敘。我們在原始數(shù)據(jù)前增加一列，列標(biāo)題為“輔助列”，根據(jù)最終表格的列數(shù)在輔助列里依次輸入字母，例如這里最終表格有4列，我們就輸入A、B、C、D，然后拖動填充。
方法1：排序后手動粘貼
首先選擇要排序的區(qū)域，注意這里選擇了D14:E38。在排序過程中，Excel識別到D14:E14是標(biāo)題行，于是將標(biāo)題行排除在排序選項(xiàng)之外。
點(diǎn)擊“數(shù)據(jù)-排序”，選擇以“輔助列”、按“升序”排序，得到右邊的排序結(jié)果。根據(jù)這個結(jié)果，可以手動將原始列的內(nèi)容粘貼出去，實(shí)現(xiàn)最終結(jié)果。
方法2：篩選后手動粘貼
或者不排序，直接通過篩選，將數(shù)據(jù)結(jié)果逐一粘貼出去。
選擇包括數(shù)據(jù)標(biāo)題的數(shù)據(jù)D14:E38，點(diǎn)擊“數(shù)據(jù)-篩選”，選擇輔助列里包含A的單元格。
最終呈現(xiàn)輔助列值為A的數(shù)據(jù)如下▼，將之粘貼到一個空白sheet的指定區(qū)域。再依次，單獨(dú)篩選輔助列值為B的數(shù)據(jù)，粘貼出去，輔助列值為C的數(shù)據(jù)、輔助列值為D的數(shù)據(jù)也同樣操作方法，即得到最終想要的結(jié)果。
2、字段提取及整理
有些時候，我們需要從文字中提取一些字段，從而形成新的列，以方便進(jìn)一步的數(shù)據(jù)處理。這時候就需要我們觀察數(shù)據(jù)形式，用一些簡便方式批量處理數(shù)據(jù)后，對于沒有辦法批量處理的部分，再手動處理數(shù)據(jù)，以提高工作效率。
例如，在下圖中，有一批企業(yè)名單和地址，需要提取其中的省份及城市。
當(dāng)然這個省份、城市可以自己一行一行錄入。
但是也可以通過分列的方式做預(yù)處理，減少錄入的工作量。
分列原理：首先觀察數(shù)據(jù)，基本上這一字段的前兩個字都是省份或者直轄市名字，第四個第五個字基本能對應(yīng)城市名字。那么我們可以在這個字段上采取固定分列的形式，先將省份及城市名字大概提取出來。
[在對地址這一列處理時，應(yīng)注意復(fù)制新的一列來處理，以避免改變了原始信息。]
（1）替換無用詞組“中國”
在做分列前，我們發(fā)現(xiàn)地址里以“中國”二字開頭的數(shù)據(jù)，“中國”二字既不是我們所需要的信息，同時還會影響我們分列的準(zhǔn)確性，所以可以將“中國”這一字段替換掉。
替換方法如下：選擇“復(fù)制原始地址”列數(shù)據(jù)，通過“Ctr+H”鍵打開查找和替換對話框，在查找內(nèi)容里輸入“中國”，替換框里不用輸入，點(diǎn)擊“全部替換”，這樣就將地址列的“中國”一詞都去掉了。
（2）對地址進(jìn)行固定分列
這里的分列原則就是，將第一、第二個字單獨(dú)提取出來，將第四、第五個字單獨(dú)提取出來
選擇需要分列的區(qū)域，點(diǎn)擊“數(shù)據(jù)-分列”，打開“分列”對話框，點(diǎn)擊“固定寬度”分列，選擇“下一步”，按照之前的分列原則，點(diǎn)擊出相應(yīng)的分列線，點(diǎn)擊“完成”。
就得到如下分列結(jié)果。可以看出下圖中的I列就是我們需要的省份結(jié)果，K列就是我們所需要的城市結(jié)果。有些數(shù)據(jù)輸入不規(guī)范，所以還需要進(jìn)一步手動處理。
（3）對省份、城市進(jìn)行排序
首先將數(shù)據(jù)進(jìn)行排序。排序的好處是相同的省份、城市放在一塊，比如說將“北京”的企業(yè)都放一塊，復(fù)制、粘貼處理比較快。
排序方法如下，選擇需要排序的區(qū)域，點(diǎn)擊“數(shù)據(jù)-排序”，然后在主要關(guān)鍵字上，選擇以“省份”的“數(shù)值”，“升序”排列。隨后點(diǎn)擊“添加條件”，再對次要關(guān)鍵字進(jìn)行排序，同理，選擇以“城市”的“數(shù)值”，“升序”排列。最后點(diǎn)擊“確定”，即完成排序。
排序結(jié)果如M列和N列所示。再在M列和N列的結(jié)果進(jìn)行整理，即得到最終圖示上的“省份1”、“城市1”。
寫在后面
至此，滄海蝴蝶的極簡Excel教程就結(jié)束了，希望能對您有所幫助。
Excel數(shù)據(jù)原始文件，請搜索微信公眾號“好餓的蝴蝶”或者“hungrybutterfly”，在微信公眾號對話框中輸入“Excel數(shù)據(jù)”，提取下載地址。
