申明: 本站飛宇網 https://feiyetopro.blogspot.com/。自網路收集整理之書籍、文章、影音僅供預覽交流學習研究，其[書籍、文章、影音]情節內容， 評論屬其個人行為， 與本網站無關。版權歸原作者和出版社所有，請在下載 24 小時內刪除，不得用作商業用途；如果您喜歡其作品，請支持訂閱購買[正版]。謝謝！
03 常用小技巧
技巧是每個人最先遇到的，小小技巧能讓你的工作效率提高很多倍。我們不必掌握每個技巧，只要瞭解一些常用的就可以了。我們經常可以聽到排序、篩選、分列、查找、替換等，這些就是最常用的，下面通過一些小實例一起來認識它們。學技巧沒有捷徑，唯有勤練，趁著有空多看看書。
自動校正後遺症小例
3.1 排序
3.1.1 排序其實很簡單
Q如圖3-1所示，剛收到一份快遞費用匯總表，但中轉費沒有按從高到低排列，看起來很亂。怎麼按中轉費降冪排序呢？
圖3-1 亂序的快遞費用表
按鈕，在彈出的“排序提醒”對話方塊中，保持默認不變，按一下“確定”按鈕。
圖3-2 按中轉費降冪排序
Q剛才在彈出的“排序提醒”對話方塊有個“以當前選定區域排序”項，如果選擇這個項會怎麼樣？
A“以當前選定區域排序”只能針對你選擇的區域排序，而沒有選擇的區域不排序。正常情況下這種排序是沒有意義的，同時也很危險。從圖3-3很清楚地看出兩種方式的差別。
圖3-3 對比圖
3.1.2 按城市的發展情況排序
Q如圖3-4所示，不管按昇冪或者降冪排序都不能得到我想要的排序結果。有沒有辦法實現我需要的排序呢？如現在這幾個城市的發展情況從好到差依次是：揭陽、汕頭、潮州、梅州，按發展情況排序。
圖3-4 按城市昇冪
A正常情況下，漢字的排序是按首字母排序的，還有一種是按筆劃排序，如圖3-5所示，但這兩種情況都得不到所需要的結果。既然沒有所需要的排序結果，那就自訂一個吧。
圖3-5 排序選項
STEP 01選擇“檔”選項卡，然後選擇“選項”命令，再選擇“高級”項，按一下“編輯自訂清單”按鈕，如圖3-6所示。
圖3-6 編輯自訂列表
STEP 02如圖3-7所示，在“輸入序列”清單方塊中，依次輸入城市(注：如果儲存格中事先已經輸入好排序依據，也可以用導入序列功能)，再按一下“添加”按鈕。“自訂序列”清單方塊中就出現了新增加的序列，按一下“確定”按鈕，返回工作表。
圖3-7 輸入自訂序列
STEP 03如圖3-8所示，選擇需要排序的區域，然後按一下“排序”按鈕。在彈出的“排序”對話方塊中，添加排序條件，在“次序”下拉式清單方塊中選擇自訂序列，找到自訂的新序列，按一下“確定”按鈕。
圖3-8 自訂排序
STEP 04如圖3-9所示，得到想要的排序結果。
圖3-9 自訂排序後的效果
如果需要多條件排序，只需按一下“添加條件”按鈕，再選擇需要排序的主要關鍵字，其他操作跟上面一樣。
3.2 篩選
3.2.1 篩選中的搜索功能
Q如圖3-10所示，在搜尋引擎中輸入潮州，就會出現很多跟潮州有關的資訊。在Excel中能否辦到呢？
A海量資料篩選，沒有最快只有更快，利用篩選中的搜索功能可以讓資料篩選更方便快捷。
圖3-10 搜尋引擎
STEP 01如圖3-11所示，選擇A1區域，然後按一下“資料”選項卡下的“篩選”按鈕。
圖3-11 按一下“篩選”按鈕
STEP 02如圖3-12所示，按一下“寄件站”的篩選按鈕，在“搜索”框中輸入“潮州”，就會出現跟潮州有關的資訊，再按一下“確定”按鈕。
圖3-12 搜索功能
舊版本操作：按一下“寄件站”的篩選按鈕，選擇“文本篩選”→“包含”命令，輸入“潮州”，就會出現跟潮州有關的資訊，再按一下“確定”按鈕。
溫馨提示
使用者有需求，Excel開發者就會改進。即使目前沒有這個功能，只要需求的人多了，耐心等待，在不久的將來一定會有的。這個搜索功能就是一個明顯的例子，2010或2013版的才有，以後還會說到很多新功能，到時就不逐一說明了。
3.2.2 將帶顏色項目挑選出來
Q如圖3-13所示，在核對資料的時候，將有異常的資料用黃色填充色標示出來。現在要將這樣異常值篩選出來，逐一修改，應該怎麼做呢？
圖3-13 怎麼篩選黃色填充色
A如圖3-14所示，按一下“應收中轉費”的篩選按鈕，選擇“按顏色篩選”→“黃色填充色”命令。
舊版本操作：利用宏表函式定義一個名稱叫“顏色”，篩選輔助列數值為6的專案。
圖3-14 按顏色篩選
=GET.CELL(63,將帶顏色項
目挑選出來！A2)
=顏色
3.2.3 借助高級篩選，讓篩選資料更貼心
Q如圖3-15所示，普通篩選只允許按兩個條件篩選，有沒有辦法按多個條件進行篩選呢？
圖3-15 只允許填寫兩個條件
A如圖3-16所示，羅列出所有條件在B16：B19區域的資料，切換到“資料”選項卡，按一下“高級”按鈕，選擇“將篩選結果複製到其他位置”項，依次引用清單區域$A$1：$C$13，條件區域$B$16：$B$19，複製到$E$1：$G$1，再按一下“確定”按鈕。
圖3-16 滿足多條件的高級篩選
條件應寫在同一列，在這裡就是“或”的意思，只要滿足其中一個條件就可以了。還有一種是滿足公司或者行業中一列就行。這樣就分兩行兩列顯示，如圖3-17所示。
圖3-17 或的用法
假如要實現“且”的功能，則需要將條件寫在同一行。如圖3-18所示，就是同時滿足公司為“嘉元實業”，行業為“旅遊”的所有項目。
圖3-18 且的用法
Q如果只要公司和第1季度銷量兩列，可以實現嗎？
A如圖3-19所示，只需做小小的變動就可以了。在複製到的區域，先把表頭填寫進去就行。
圖3-19 選擇需要的列
溫馨提示
如果想將篩選的結果複製到新表格中，對於一個不存在的表格，高級篩選沒法將篩選結果複製到那裡。可以先插入一個新表格，然後再用高級篩選。
3.3 分列
3.3.1 按分隔符號分列
Q如圖3-20所示，尺寸的資料都是用*隔開的，有沒有辦法提取長、寬？
圖3-20 提取長、寬
A利用按分割符分列可以做到。
STEP 01如圖3-21所示，選擇儲存格A2：A10，然後按一下“資料”選項卡中的“分列”按鈕，並在彈出的“文本分列嚮導”對話方塊中保持默認設置不變，再按一下“下一步”按鈕。
圖3-21 文本分列第1步
STEP 02如圖3-22所示，在“分割符號”的“其他”文字方塊中輸入*，再按一下“下一步”按鈕。
圖3-22 文本分列第2步
STEP 03如圖3-23所示，設置“目的地區域”為B2儲存格，按一下“完成”按鈕。
圖3-23 文本分列第3步
3.3.2 將姓名分成兩列顯示
Q如圖3-24所示，怎麼將姓名分成姓和名，並顯示在兩列呢？
圖3-24 姓名清單
A可以利用姓一般是一個字元這個特點並用固定寬度分列做到。
STEP 01選擇A2：A12區域，將姓名複製到B列。
溫馨提示
上一例已經提到可以更改目的地區域的位置，為了防止操作方法相同，才使用複製的方法。
STEP 02按一下“資料”選項卡中的“分列”按鈕，然後在彈出的“文本分列嚮導”對話方塊中，選擇“固定寬度”項，再按一下“下一步”按鈕。
STEP 03按一下第一個漢字處，再按一下“確定”按鈕，如圖3-25所示。
圖3-25 按固定字元分列
3.3.3 將不標準日期轉換成標準日期
Q日期一般都是按Y/M/D形式顯示的，如圖3-26所示，而現在顯示成M.D.Y。這種日期不允許進行計算，怎麼轉換成可以計算的日期呢？
圖3-26 不標準日期
A標準日期是用“-”或者“/”隔開的，其他形式都是不標準的，如果要顯示其他形式，可以通過自訂儲存格得到。通過上面兩個實例，我們知道了分列的一般作用，其實分列也可以將不標準日期轉換成標準日期。
STEP 01選擇A2：A12區域，然後按一下“資料”選項卡中的“分列”按鈕，在彈出的“文本分列嚮導”對話方塊中保持默認設置不變，連續按一下兩次“下一步”按鈕。如圖3-27所示，列資料格式選擇日期MDY，目的地區域引用B2儲存格，按一下“完成”按鈕。
圖3-27 按MDY分列
STEP 02分列其實還有很多用途，如將文本轉換成真正的數位，如圖3-28所示，文本是不能直接求和的。預設數位是靠右對齊，而文本是靠左對齊，位置站錯，價值也就變了。
圖3-28 不能求和的資料
STEP 03選擇A2：A12區域，然後按一下“資料”選項卡中的“分列”按鈕，在彈出的“文本分列嚮導”對話方塊裡保持默認設置不變，直接按一下“完成”按鈕，一步到位。如圖3-29所示，雖然資料暫時還是左對齊，但性質已經變了，可以自動加總。
圖3-29 分列前後對比圖
如果要將數位轉換成文本，也可以用分列完成。在分列的時候，選擇列資料的格式為“文本”就可以了。
3.4 零散小技巧
3.4.1 閃電式數據填充
Q如圖3-30所示，現在有一些電子郵件，如何提取名字呢？
A仔細觀察，名字後面都有一個“.”分割符，自然可以按分割符號分列獲得。其實，Excel 2013提供了一個超級強大的填充功能，用“閃電式”這個詞來形容它再恰當不過了，又快又好用。
圖3-30 提取名字
在名字列，也就是B2儲存格裡輸入第一個名字Nancy，並回車。
如圖3-31所示，按一下“開始”選項卡中的“填充”按鈕，然後選擇“快速填充”項。觀察一下工作表，已完成了按名字填充。
舊版本操作：只能按分列完成，或者用函數提取。
圖3-31 快速填充名字
也許你還沒反應過來，下面再通過一個小例子來說明一下這個“快速填充”功能。如圖3-32所示，“調色配方”列每個儲存格內都有3種配色，現在要獲取中間的配色。
圖3-32 獲取中間配色
在“中間配色”列，也就是C2儲存格中輸入B：28，按Enter鍵。
按一下“開始”選項卡中的“填充”按鈕，選擇“快速填充”項。觀察一下工作表，中間配色方案已全部生成。
讓Excel為你完成資料登錄工作，挺爽吧。
3.4.2 刪除錯誤值
Q如圖3-33所示，“平均每回人數”列中出現錯誤值，很不美觀，有沒辦法將錯誤值替換成空？
圖3-33 沒有容錯產生的錯誤值
A可以利用查找功能，將錯誤值刪除。
如圖3-34所示，按正常方法操作，選擇D2：D13，然後按快速鍵Ctrl+F調出“查找和替換”對話方塊。在“查找內容”下拉式清單方塊中輸入#DIV/0！，按一下“查找全部”按鈕。Excel會告訴你，需要更改查找範圍值。為什麼會出現這個提示呢？因為你的錯誤值是由公式產生的，並不是公式存在錯誤值，錯的只是顯示出來的值。有時錯誤的操作會讓你瞭解更多，記憶更深刻。
圖3-34 錯誤的操作方法
如圖3-35所示，選擇D2：D13，然後按複合鍵Ctrl+F調出“查找和替換”對話方塊。在“查找內容”下拉式清單方塊中輸入#DIV/0！，“查找範圍”更改為“值”。按一下“查找全部”按鈕。按住Shift鍵選擇第一和最後一個錯誤值，關閉對話方塊，再按Delete鍵刪除。
圖3-35 查找範圍：值
直接用複合鍵Ctrl+H調出替換功能，然後將“查找範圍”改為“值”，再按一下“全部替換”按鈕不就可以了嗎？
做任何事情都得有自己的想法，這樣才不會被別人牽著鼻子走，這樣很好。來，我們一起來走一遍，試試你的想法能否行得通。
如圖3-36所示，按複合鍵Ctrl+H調出替換功能，“查找範圍”裡面就只有一個公式，沒有值哦。如果直接按一下“全部替換”按鈕，會提示找不到可以替換的內容。
圖3-36 直接替換效果圖
溫馨提示
這個問題正是因為一次出錯我才知道這個“查找範圍”有問題，所以特意把錯誤的做法寫出來，讓大家牢記這個錯誤。
也許你一時半會還沒法適應這個查找替換功能，先緩一緩，回頭再試驗幾次就好了。其實，這個問題用定位錯誤值再刪除，會比較容易接受。記住，條條大路通羅馬！
如圖3-37所示，選擇區域，然後按F5鍵調出“定位”對話方塊。按一下“定位條件”按鈕，在“定位條件”對話方塊中選中“公式”選項按鈕，在其下面只選中“錯誤”核取方塊，再按一下“確定”按鈕，按Delete鍵刪除錯誤值。
圖3-37 定位刪除錯誤值
溫馨提示
在大多數情況下都是用公式進行容錯，公式部分在第4章將有詳細介紹，這裡就不做解釋了。
=IF(C2,C2/B2,＂＂)
=IFERROR(C2/B2,＂＂)
3.4.3 讓資料登錄更簡單
Q如圖3-38所示，經常要輸入一些“缺陷描述”，有沒有辦法讓輸入更簡單？
圖3-38 缺陷描述
A解決方案
如果資料量不超過5個，可以利用資料有效性的序列來處理，但當資料量比較大時這種方法反而更慢。
如圖3-39所示，選擇“檔”，然後選擇“選項”命令。在“Excel選項”對話方塊中選擇“校對”項，再按一下“自定更正選項”按鈕。添加這些缺陷的首字母，更正為缺陷描述，但這個方法會導致後期輸入這些字母時，Excel會自作主張幫你改正。
圖3-39 自動校正
自動校正後遺症小例
@Magic賽琳娜：如圖3-40所示，為什麼我輸入“R”時，Excel非要跳個“日”出來呢？
圖3-40 自動校正
既然可以輸入首字母來實現自動校正，那麼查找替換也可以。如圖3-41所示，如漏焊就輸入LH，當儲存格全部輸入完後再用替換功能改正。這個至少比自動校正靠譜，沒有後遺症。當字元越長時，這種查找替換更能體現出價值，如輸入“Richell日本公司東莞代表處”，你可以簡寫為Rc。
圖3-41 查找和替換
如果會VBA函數，這種問題會變得很簡單。只要設置一個對應表，然後用VLOOKUP函數引用過來就可以了，不過這裡不涉及這個。慢慢來，一口氣吃不成胖子。
3.4.4 重複值問題
Q如圖3-42所示，有部分手機號重複，怎麼獲取不重複的手機號？
圖3-42 有部分重複的手機號
A如圖3-43所示，按一下“資料”選項卡中的“高級”按鈕，利用“高級篩選”對話方塊中的“選擇不重複的記錄”項獲得。
圖3-43 高級篩選去重複
Q除了高級篩選外，還有其他辦法嗎？
A如圖3-44所示，選擇A1：A12，然後按一下“資料”選項卡中的“刪除重複項”按鈕。在彈出的“刪除重複項”對話方塊中保持默認設置不變，按一下“確定”按鈕。
圖3-44 刪除重複項
有多列需要刪除重複也可以用這種方法。多列其實就是將各列合併起來，相當於＆（＂盧子＂＆872245780=盧子872245780），軟體會自動進行判別。
Q如果僅僅想要把重複的手機號標示出來，而不刪除，可以嗎？
A如圖3-45所示，選擇A1：A12，然後選擇“條件格式”→“突出顯示儲存格規則”→“重複值”命令，在彈出的“重複值”對話方塊中保持默認設置不變，再按一下“確定”按鈕。
圖3-45 標示重複值
如果其他文本值需要標示重複值，操作會更簡單。如圖3-46所示，選擇區域，然後選擇“快速分析工具”→“重複的值”命令，就自動幫你標示出來。如果是數值型，Excel更關心的是數字的其他分析而不是重複與否，所以沒有直接出現這個“重複的值”這個功能。版本越高，Excel越懂你的心。
圖3-46 快速分析工具
3.4.5 套用表格樣式
Q在1.3節裡，提到過隔行填充顏色，讓領導可以看得更清楚。這個怎麼做到呢？下面的對話是剛看到的，覺得這個隔行填色還有點作用。
“小張，你這個表格裡密密麻麻的這麼多資料，看得我頭大！”
“哦，老闆，要不我給您泡杯茶，慢慢看。”
“不用了，你把資料隔行填上顏色，再拿給我，不然看著看著就串列。”
“隔行填色啊？好幾千行呢……今天又要加班了。老闆，咱們公司加班有加班費嗎？”
“加班費？你如果做不出來，就準備捲舖蓋走人吧，還加班費！人家小李怎麼就不加班？上次幾萬行資料都是這樣做的。”
A其實隔行填色很簡單，只需要套用一個格式就行。
STEP 01如圖3-47所示，按一下任意儲存格，再按一下“套用表格樣式”按鈕，然後選擇你喜歡的樣式。
圖3-47 選擇樣式
STEP 02如圖3-48所示，在彈出的“套用表格式”對話方塊中，按一下“確定”按鈕。
圖3-48 確定樣式
STEP 03其實利用複合鍵Ctrl+T或Ctrl+L創建表，也可以實現隔行填充色，如圖3-49所示，但創建的填充色為預設的樣式。要改變顏色，需要重新套用樣式。
圖3-49 創建表格
3.5 本章小結
瞭解排序、篩選、分列等常用小技巧，會讓我們以後處理資料更得心應手。相似的知識點一定要一起學，不要看到什麼就學什麼，那樣反而學得不快。學習的過程就是一個積累的過程，從量變到質變的一個過程。有空的時候，多練幾次，基本上就記住了這些技巧。當技巧學得差不多的時候，就得進攻函數，那將是另外一片天地！
0 留言:
發佈留言