學會了效率高！Excel實戰(zhàn)技巧208例

內(nèi)容簡介

本書內(nèi)容是作者從自身的Excel教學網(wǎng)站、專業(yè)論壇、今日頭條、抖音

視頻、淘寶技術店鋪、微信解答群等平臺上與100萬粉絲的溝通中，精心挑

選出來的熱門、高頻、實用案例。

8大專題內(nèi)容，講解了Excel的基礎操作、公式函數(shù)、統(tǒng)計求和、數(shù)據(jù)分

析、透視表分析、圖表分析、VBA應用等實戰(zhàn)精華內(nèi)容，幫助Excel新手成

為應用高手。

208個純技巧，涵蓋行政、文秘、人事、財務、會計、市場、銷售等多

個崗位內(nèi)容，幫助讀者活學活用Excel經(jīng)典技巧，早做完工作早下班，早升

職加薪。

本書適合Excel職場人士、Excel發(fā)燒友、Excel初學者或感興趣的人

閱讀。

圖書在版編目（CIP）數(shù)據(jù)

學會了效率高！:Excel實戰(zhàn)技巧208例/鄭廣學著.—北京：

中國鐵道出版社有限公司，2019.7

ISBN978-7-113-25786-6

Ⅰ.①學…Ⅱ.①鄭…Ⅲ.①表處理軟件Ⅳ.①TP391.13

中國版本圖書館CIP數(shù)據(jù)核字（2019）第091682號

書名：學會了效率高！Excel實戰(zhàn)技巧208例

作者：鄭廣學

責任編輯：張亞慧讀者熱線電話/p>

責任印制：趙星辰封面設計：

出版發(fā)行：中國鐵道出版社有限公司（100054，北京市西城區(qū)右安門西街8號）

印刷：北京銘成印刷有限公司

版次：2019年7月第1版2019年7月第1次印刷

開本：700?mm×1?000?mm1/16印張：24.75字數(shù)：286千

書號：ISBN978-7-113-25786-6

定價：69.00元

版權所有侵權必究

凡購買鐵道版圖書，如有印制質(zhì)量問題，請與本社讀者服務部聯(lián)系調(diào)換。電話：（010）51873174

打擊盜版舉報電話：（010）51873659

前言

產(chǎn)業(yè)信息化的大潮已經(jīng)到來，誰也抵擋不了，我相信很多職場中

的朋友，此刻會有這種感覺，原來上大學期間學了那么多的數(shù)理化、

微積分、高等數(shù)學等知識，到最后都不如辦公軟件技能來得實在。

非常正確，你的感覺沒有錯。其中最為突出的就是Excel，無所不

在的Excel表格，已經(jīng)貫穿職場中的每一個角落，如果你的Excel技

術高超，那么在公司會更加受老板喜歡，效率也會比其他人要高很多，

反之則會做很多無用功，別人幾分鐘做完的事兒你要用幾個小時甚至

更長時間。

這時候，你開始想要學習，提高自己，于是你找了諸如“×××

從入門到精通”“×××大全”“×××寶典”等這類教程來學習，也

投入了大量時間和精力，但是最后卻發(fā)現(xiàn)在工作中面對實際問題的時

候，依然一籌莫展，想知道這是為什么？因為你缺乏實踐、缺乏實戰(zhàn)、

缺乏實實在在的在職場中會真正面對的鮮活實例。

學會了效率高！Excel實戰(zhàn)技巧208例

無理論純實戰(zhàn)教學！這就是我寫作本書的初衷，這也是我們

Excel?880工作室無理論純實戰(zhàn)的精神內(nèi)核，全部來源于一線職場工作

中遇到的真實問題，涵蓋各行各業(yè)，本書中的每一個實例都有具體的

應用場景，會讓你在學習的時候有極強的代入感，拋棄枯燥的理論，

直接解決問題，學一個案例就解決一個真正的問題，然后在實際工作

中舉一反三，這樣學到的知識才能真正為你所用。

可能大家有疑問，你哪里來的這么多實例？下面我就告訴大家我

是怎么得來的這么多實例，也給大家分享一下我的Excel學習歷程。

還在讀大學的時候，因為討厭按計算器去算煩瑣的各種理科計算

題，自從發(fā)現(xiàn)Excel的強大計算能力后，就徹底地愛上了它。從此以

后所有的計算類問題我就都在Excel中解決，各種計算題都想著搬到

Excel中去算，這個階段我主要是靠百度幫助去學習。

后來參加了工作，我就開始在“百度知道”里瘋狂答題，也加入

了一些Excel愛好者的QQ群，這個階段的求知欲爆棚，我在“百度知

道”和QQ群里瘋狂答題，每天的業(yè)余時間都用在解決網(wǎng)友各種各樣

的提問，遇到不會的問題就到處找資料，沒日沒夜地研究學習，Excel

技術在這個時候開始突飛猛進。

接下來我就開始兼職進行Excel咨詢定制服務，接受付費定制，來

幫網(wǎng)友定制一些比較復雜的表格系統(tǒng)。想必大家現(xiàn)在已經(jīng)知道我為什

么會有那么多真正的實例來講了，因為這些都是我親自解決過的來自

各地網(wǎng)友提出來的問題，有一部分還是付費定制解決的問題，這些實

例的價值遠超那些閉門造車的所謂實例。

現(xiàn)在，我是自由職業(yè)者，開設Excel880工作室，專業(yè)做Excel咨

II.

前言

詢定制機培訓服務，也是自媒體作者，在今日頭條運營Excel實例教

學，發(fā)表Excel技術相關文章及視頻，現(xiàn)有粉絲130多萬人，為回饋

廣大粉絲，也為了讓更多的人能更好、更快地將Excel技能學以致用，

特此挑選精華案例，編寫本書，詳細講解給大家，讓大家領略真正的

Excel實例教學，即學即用！

本書的主要特色有兩點：

一是案例熱門，深受廣大讀者喜歡：書中案例都是作者從實

際教學、今日頭條、抖音、淘寶店鋪、微信平臺等100多萬讀者

喜愛的內(nèi)容中，精心挑選出來的高頻案例。

二是技巧實用，能馬上活用于職場：書中技巧都是從行政、

文秘、人事、財務、會計、市場、銷售等十多個職場關鍵崗位內(nèi)

容中篩選出來的，可以邊學邊用。

本書的細節(jié)特色如下：

（1）完備的功能查詢：工具、按鈕、菜單、命令、快捷鍵、理論、

實戰(zhàn)演練等應有盡有，內(nèi)容詳細、具體，是一本自學手冊。

（2）豐富的案例實戰(zhàn)：本書中安排了208個精辟范例，對Excel

2016軟件的常用功能進行了非常全面、細致的講解，讀者

可以邊學邊用。

（3）細致的操作講解：44個專家提醒放送，930多張圖片全程

圖解，讓讀者可以掌握軟件的核心技能與操作技巧，提升

實戰(zhàn)精通技巧與經(jīng)驗。

.III

學會了效率高！Excel實戰(zhàn)技巧208例

（4）超值的資源贈送：430分鐘與本書同步的視頻講解，460多

個與書中同步的素材和效果文件，讓讀者可以輕松學習，

快速掌握各類工作表的制作方法和技巧。

本書由鄭廣學著，參與編寫的人員還有劉華敏等人，在此表示感

謝。由于作者知識水平有限，書中難免有錯誤和疏漏之處，懇請廣大

讀者批評、指正，聯(lián)系微信：157075539。

本書及下載中所采用的圖片、數(shù)值等，均為所屬公司、網(wǎng)站或個

人所有，本書引用僅為說明（教學）之用，絕無侵權之意，特此聲明。

編者

2019年3月

IV.

目錄

第1章基礎操作實戰(zhàn)應用

實例001Excel快速切換多個工作簿的幾種方式詳解/3

實例002讓Excel編輯欄字體變大的方法，方便查看和編輯公式/4

實例003不得不學的8個Excel批量操作技巧，再也不用加班了/6

實例004Excel中那些讓你可以成倍提高工作效率的簡單技巧/9

實例005Excel必學操作小技巧之快速隔行插入空行/13

實例006在Excel中刪除多余的空行，輔助列+公式操作技巧/14

實例007Excel中如何使合并的單元格保留內(nèi)容/16

實例008合并單元格正確篩選出合并區(qū)多行內(nèi)容的解決方案/18

實例009Excel表用條件格式隔行顯示不同顏色/19

實例010表格一行輸入完成后回車自動跳轉(zhuǎn)到下一行開頭/21

實例011Excel中如何輸入文字后把多個單元格變成統(tǒng)一顏色/22

實例012Excel中快速給行數(shù)不一致的合并單元格填充連續(xù)編號/24

實例013Excel表格中帶45°對角斜線的表頭制作方法/25

實例014Excel表格中繪制多條斜線的表頭制作方法/26

學會了效率高！Excel實戰(zhàn)技巧208例

實例015一秒美化表格的方法之套用表格格式設置底紋和邊框/27

實例016Excel篩選、隱藏行以及刪除行后仍然保持序號連續(xù)/28

實例017Excel中已有數(shù)字快速輸入平方米和立方米的方法/29

實例018專業(yè)會計才知道的Excel小技巧之超快速輸入多個0/30

實例019Excel多種日期樣式統(tǒng)一顯示為一種樣式的兩種方法/31

實例020Excel自帶的批注太丑了！可做成任意形狀的花式批注/32

實例021Excel中快速將不同產(chǎn)品編碼的數(shù)據(jù)用空白行隔開/34

實例022Excel快速進入“打印”預覽頁面的三種方法/36

實例023Excel小技巧之按實際打印尺寸設置行高和列寬的方法/36

實例024多張不同列數(shù)不同排版的表格排版打印到一張A4頁面的技巧/37

實例025Office小妙招之批量保存Excel中的多張圖片/39

實例026Excel財務小技巧，人民幣數(shù)字金額轉(zhuǎn)漢字大寫公式/40

實例027Excel銷售計劃與完成情況表，從0開始的表格美化操作演示/41

實例028Excel輸入身份證號碼后顯示為帶18個方框的數(shù)字/43

實例029用Excel做英語4線3格作業(yè)本的基礎操作演練/45

實例030用Excel制作田字格中文字帖，你會嗎/46

實例031利用排序完成隔行插入空行制作簡單工資條/49

實例032Excel制作帶指紋和稅號編號的印章/51

公式函數(shù)實戰(zhàn)應用

第2章公式函數(shù)實戰(zhàn)應用

實例033會計常用的17個Excel公式圖解說明/57

實例034用公式設定條件格式，對戶主所在行標紅/61

實例035Excel之眾里尋他，數(shù)據(jù)查找匹配函數(shù)VLOOKUP中文精解/63

II.

目錄

實例036用VLOOKUP函數(shù)輔助將亂序數(shù)據(jù)相同部門的多個姓名合并/64

實例037Excel利用VLOOKUP對小分類快速劃分大類（快速分組）/65

實例038Excel中用公式法制作橫道圖（甘特圖）/66

實例039Excel中六舍七入取整的兩種公式寫法/67

實例040Excel中取整公式擴展推廣為M舍N入/67

實例041Excel函數(shù)公式進階之COUNTIF統(tǒng)計*等帶通配符數(shù)據(jù)/68

實例042Excel多條件取值終極解法高級應用SUMPRODUCT函數(shù)/69

實例043Excel表多條件查找（行列交叉查找）INDEX+MATCH/70

實例044不同職稱獎學金匹配：Excel的IF函數(shù)多條件判斷示例/71

實例045Excel中逆向查找最好記的公式套路LOOKUP萬能查找函數(shù)應用/72

實例046匯總時總怕加錯了前面的匯總行？讓SUBTOTAL來幫你解決/73

實例047Excel表中快速將數(shù)據(jù)隨機打亂順序（隨機函數(shù)RAND應用）/75

實例048Excel必學函數(shù)套路之中國式排名三種函數(shù)公式算法/75

實例049Excel巧用函數(shù)構(gòu)造HTML代碼批量插入圖片/76

實例050Excel中提取單列不重復數(shù)據(jù)的函數(shù)公式/78

實例051逐一將幾個單元格數(shù)據(jù)連接到一起的兩種函數(shù)方法/79

實例052批量將多個單元格數(shù)據(jù)連接到一起的函數(shù)方法/80

實例053Excel中大量快遞地址按省、市、區(qū)、縣不同級別分列/82

實例054用函數(shù)從混合文本中提取名字及銀行卡號并分列顯示/83

實例055批量快速計算某月的第一天和最后一天/84

實例056Excel函數(shù)查找本行最后一個數(shù)據(jù)所在的列位置/85

實例057Excel中數(shù)字日期與中文日期互相轉(zhuǎn)換的函數(shù)實例講解/86

實例058指定區(qū)間的隨機數(shù)函數(shù)，批量隨機生成指定范圍日期/88

實例059用函數(shù)分別提取姓名和手機號碼，中文和英文或數(shù)字分離/89

實例060Excel中雙條件返回多行數(shù)據(jù)，萬金油函數(shù)方法擴展講解/90

.III

學會了效率高！Excel實戰(zhàn)技巧208例

第3章統(tǒng)計求和實戰(zhàn)應用

實例061Excel高級求和技巧之按項目小計求和/95

實例062Excel高級求和技巧之數(shù)字帶單位求和/95

實例063Excel多條件求和公式SUMIFS演示/96

實例064Excel表格怎樣自動求和？3種典型批量求和方法/97

實例065對不等行的合并單元格對應數(shù)據(jù)快速求和的函數(shù)技巧/98

實例066Excel中對合并單元格對應的多行數(shù)據(jù)快速求和/99

實例067Excel插入新表后自動匯總求和，多表匯總求和技巧/100

實例068Excel函數(shù)技巧之對間隔相同列數(shù)的數(shù)據(jù)求和/101

實例069OFFSET+MATCH函數(shù)對本行下屬相同部分的數(shù)據(jù)求和/102

實例070在Excel中如何按指定條件求最大值/103

實例071Excel中如何快速、批量計算工資補貼/104

實例072Excel用函數(shù)統(tǒng)計不重復項目個數(shù)的基本方法/105

實例073學會這6招，讓Excel數(shù)據(jù)既能顯示文字信息又能參與計算/105

實例074一步一步教你利用公式在Excel中求解一元二次方程/107

實例075Excel必學之計算星期和周期的幾個函數(shù)公式/110

實例076Excel中時間與分鐘加減計算的兩種方法/111

實例077Excel中日期計算不用愁，年齡、工齡的間隔天數(shù)、月數(shù)一個

公式搞定/112

實例078Excel中計算一個日期屬于第幾個季度的3種方法及中文表示法/113

實例079Excel中計算時間差，用多少小時多少分鐘來表示的公式/115

實例080Excel計算已知日期序列在指定日期區(qū)間的遺漏日期/116

實例081Excel多條件計數(shù)函數(shù)求多個銷售區(qū)間的銷售員個數(shù)/117

IV.

目錄

實例082告別計算器，Excel輕松計算學分成績點/118

實例083身份證提取生日、性別及年齡15位+18位通用公式/119

實例084根據(jù)身份證號所提取的出生日期設置生日倒計時提醒/120

實例085如何在Excel中不改變單元格各屬性，復制粘貼單元格中的公式/121

實例086在Excel中輸入編號以0開頭的數(shù)字/122

實例087用公式根據(jù)銷售提成比例基準表計算銷售提成/123

第4章數(shù)據(jù)分析實戰(zhàn)應用

實例088顯示帶顏色箭頭+數(shù)據(jù)形式，銷售同比數(shù)據(jù)美化/127

實例089在Excel中臨時隱藏數(shù)據(jù)的兩種便捷方法（可快速恢復）/128

實例090利用函數(shù)和輔助列篩選去除重復數(shù)據(jù)，保留最后出現(xiàn)的行/129

實例091如何刪除日期數(shù)據(jù)中后面的時間只保留日期部分/131

實例092Excel日期、時間快速計算函數(shù)/133

實例093Excel中篩選數(shù)據(jù)不成功的原因分析及解決辦法/133

實例094Excel中跨文件查找引用數(shù)據(jù)的公式寫法操作詳解/135

實例095一對多查找數(shù)據(jù)的萬金油函數(shù)套路講解/136

實例096快速從Excel表中的兩列相似數(shù)據(jù)中找出異同/138

實例097多條件一對多查找函數(shù)根據(jù)分類和品牌查找多個型號數(shù)據(jù)/139

實例098提取中英文混合字符串漢字的函數(shù)套路/140

實例099函數(shù)套路之提取前后指定字符之間的數(shù)據(jù)及雙分隔符分列/141

實例100Excel隨機從指定幾個數(shù)據(jù)中進行取數(shù)（模擬數(shù)據(jù)）/142

實例101從多個日期對應價格中查詢產(chǎn)品最新價格的數(shù)組公式/143

實例102通過產(chǎn)品名稱和型號兩個條件來查找對應價格的方法/143

.V

學會了效率高！Excel實戰(zhàn)技巧208例

實例103VLOOKUP查找數(shù)據(jù)出現(xiàn)錯誤的幾種常見原因及解決方案/145

實例104在VLOOKUP計算公式中通配符的弊端/147

實例105用VLOOKUP快速返回整行或多列數(shù)據(jù)的方法/147

實例106通過型號和日期查找季度及型號對應的數(shù)據(jù)交叉查詢/149

實例107Excel小妙招之如何在不同行列中制作選擇下拉按鈕/151

實例108按關鍵字模糊匹配分組匯總及排序，函數(shù)及透視表綜合

實操案例/152

實例109文本混合數(shù)字表不能按數(shù)字順序排序的解決方案/155

實例110只使用2列數(shù)據(jù)實現(xiàn)二級聯(lián)動下拉菜單的方法/156

實例111Excel表中財務數(shù)字金額拆分函數(shù)實例/159

實例112Excel表中禁止同列輸入重復數(shù)據(jù)（數(shù)據(jù)驗證公式實現(xiàn)）/160

實例113Excel某列中禁止重復輸入身份證號碼（數(shù)據(jù)驗證限定輸入）/162

實例114更換重復輸入時出錯警告提示框中的提示語/163

第5章數(shù)據(jù)透視表實戰(zhàn)應用

實例115Excel數(shù)據(jù)透視表新手操作中的常見疑難解答/167

實例116Excel數(shù)據(jù)透視表常用必學技巧之篩選銷售業(yè)績?yōu)榍?名的員工/170

實例117Excel表中妙用透視表合并相同行的最快方法/172

實例118巧用透視表實現(xiàn)傳說中的一對多部門名單合并/175

實例119Excel透視表中如何讓更改的數(shù)據(jù)源隨時刷新/178

實例120透視表5秒鐘完成1000行數(shù)據(jù)制作，按部門分月匯總并美化/179

實例121銷售數(shù)據(jù)按月統(tǒng)計銷售額、成本、利潤透視表操作/180

實例122Excel透視表應用之快速列出多個產(chǎn)品的最新單價/183

VI.

目錄

實例123Excel透視表匯總后行標簽如何保持與數(shù)據(jù)源表順序一致/185

實例124Excel透視表應用之三步完成成績排名（不用函數(shù)?。?188

實例125數(shù)據(jù)源增加和減少行后透視表如何做到自動更新計算內(nèi)容/189

實例126用函數(shù)和透視表按銷量區(qū)間分段匯總數(shù)據(jù)報表/192

實例127學生成績按多年級多班級分科統(tǒng)計最高分和平均分/194

實例128銷售流水數(shù)據(jù)按季度匯總統(tǒng)計，透視表+圖表實例/199

實例129在透視表中如何調(diào)整字段排列順序/201

實例130提取不重復數(shù)據(jù)的兩種快捷操作，刪除重復值+數(shù)據(jù)透視表/203

實例131HR必學的Excel技巧之快速生成標準考勤表/205

實例132日期按周分組匯總，電商關鍵詞數(shù)據(jù)分析/207

實例133快速統(tǒng)計電話客服每天的接待數(shù)量（分列+數(shù)據(jù)透視表）/209

實例134按分類匯總求和貨品數(shù)量及百分比/211

實例135利用透視表切片器制作交互數(shù)據(jù)報表/213

實例136快速列出不同部門最近2天的銷售業(yè)績/215

實例137按分公司分別篩選出銷售額前3名的銷售員/217

第6章圖表分析實戰(zhàn)應用

實例138圖表高級應用：Excel點擊切換4種不同類型圖表/221

實例139不同數(shù)量級數(shù)據(jù)在一個圖表中顯示比例不對稱的解決方案/224

實例140圖表美化基礎，季度銷售額柱形圖中加入平均線組合圖表/226

實例141圖表美化詳解：銷售目標完成對比三維條形圖/229

實例142讓人眼前一亮的Excel多彩半圓環(huán)文字描述圖表/231

實例143散點圖高級應用：Excel制作動態(tài)數(shù)軸區(qū)間圖表/234

.VII

學會了效率高！Excel實戰(zhàn)技巧208例

實例144銷售目標與實際銷售額對比，高低點連接線折線圖/237

實例145利用切片器快速制作高大上的精美動態(tài)圖表/238

實例146Excel折線圖上的最高點和最低點自動標記出來/239

實例147動態(tài)圖表入門之下拉列表選擇不同員工業(yè)績數(shù)據(jù)柱狀圖/241

實例148Excel動態(tài)圖表其實就這么簡單，4個季度切換產(chǎn)品銷售額/243

實例149Excel中根據(jù)捐款額度點亮燈泡的動態(tài)圖表實例應用/245

實例150動態(tài)圖表的美化操作，Excel透視表+切片器+圖表三合一/247

實例151切片器動態(tài)圖表，收入分析表跨年度多月份動態(tài)對比/249

實例152Excel組合圖表之柱形圖+圓環(huán)折線圖/250

實例153使用Excel快速制作WIFI條形圖/252

實例154使用Excel制作簡易漂亮的滑珠進度圖/253

實例155Excel積木方格百分比圖表，可用于重點突出顯示/255

實例156制作高檔次圓形水箱百分比效果圖形/257

實例157制作漂亮的圓環(huán)缺角百分比效果圖形/259

實例158手把手教你做柏拉圖（帕累托曲線）/260

實例159手把手教你制作瀑布圖表（麥肯錫細分瀑布圖）/263

實例160Excel圖表美化之制作發(fā)光的折線圖/266

實例161利用填充圖片制作漂亮的水果銷售數(shù)據(jù)柱形圖/267

實例162凸顯品位的扁平簡約風格年度計劃文字卡片完成百分比圖表/268

實例163在Excel中制作多邊形水箱動畫按鈕/275

第7章VBA高級實戰(zhàn)應用

實例164Excel文件中的工作表太多，簡單VBA代碼快速添加目錄表/279

VIII.

目錄

實例165一行代碼實現(xiàn)Excel表中實時模糊篩選（VBA+文本框控件）/280

實例166Excel工作表中VBA編輯器窗口設置/283

實例167用VBA將Excel文件中多個工作表一鍵拆分為多個單獨的

文件/284

實例168Excel表關閉保存時彈出驗證碼VBA高級技術，怎么破/286

實例169VBA編輯器之不可不知的5個操作技巧/290

實例170根據(jù)開始日期和連續(xù)天數(shù)，補齊日期到每一天/291

實例171隨意批量添加任意多個可點擊切換的多選框的方法/292

實例172將多個Excel文件中的指定Sheet合并到一個文件中/294

實例173用VBA代碼實現(xiàn)文字復制方便粘貼到QQ等其他窗口/297

實例174錄入利器炫酷又實用的三級彈出式關聯(lián)菜單VBA開源/298

實例175VBA參數(shù)化自動壓縮文件，可多文件及多文件夾混合壓縮/300

實例176A表直接跨表查看單號對應的B表一對多付款明細數(shù)據(jù)/301

實例177批量智能合并，適用于產(chǎn)品數(shù)據(jù)等智能合并情景/304

實例178不用VBA自定義函數(shù)也能搞定繁體字與簡體字互轉(zhuǎn)/306

實例179使用VBA代碼也能實現(xiàn)在Excel中點擊縮放圖片/307

實例180在Excel工作簿中，如何設置開發(fā)工具/310

實例181Excel工作簿中，VBA代碼編輯后是否需要保存/311

實例182Excel批量刪除手機號列表中的座機號/312

實例183淘寶多店鋪按尺碼表多列存放的銷售數(shù)據(jù)轉(zhuǎn)4列統(tǒng)計/313

實例184用VBA代碼實現(xiàn)可展開和折疊的純工作表樹形菜單/315

實例185Excel制作進銷存出入庫表自動生成實時庫存表/317

實例186VBA批量實現(xiàn)在表格已有數(shù)據(jù)行下方插入兩行空白行/318

實例187在Excel窗體中快速繪制100個按鈕/320

實例188Excel制作高仿APP圓形進度條動畫/323

.IX

學會了效率高！Excel實戰(zhàn)技巧208例

第8章商業(yè)綜合實戰(zhàn)應用

實例189無須PS，Excel也可以輕松更換工作證件照背景/329

實例190Excel跨表制作聯(lián)動下拉菜單列表/332

實例191Excel下拉菜單跨表顯示對應圖片，用于照片查找和顯示/333

實例192HR必學技巧，制作動態(tài)Excel版簡歷查詢系統(tǒng)/335

實例193Excel中把電話號碼中間幾位顯示為星號的簡單方法/337

實例194Excel工作表中插入的控件按鈕無法刪除操作/338

實例195在Excel工作表中指定一個表頭在多頁中重復打印/340

實例196在一張表中分別計算按校區(qū)年級和班級的分組排名/342

實例197在Excel工作表中，如何制作多顏色、多形狀的聯(lián)動按鈕/344

實例198幾百個產(chǎn)品ID一鍵生成二維碼，一分鐘搞定/347

實例199Excel中多張圖片飛速排版技巧，別再苦兮兮一張一張拖啦/350

實例200Excel中不為人知的小技巧之橫向排序/353

實例201Excel中利用條件格式對連續(xù)的不同產(chǎn)品區(qū)間間隔上色/355

實例202根據(jù)Excel表批量生成Word工資調(diào)整通知單/358

實例203做水印要學PS軟件?NO，在Excel中分分鐘做出水印圖片

秒殺PS/361

實例204Excel中添加可打印的背景水印/364

實例205Excel目標計劃與實際完成對比柱形圖，大柱子套小柱子

繪圖技巧/369

實例206制作函數(shù)版點菜系統(tǒng)，自動生成點菜單和單價總價/371

實例207淘寶生意參謀數(shù)據(jù)，快速篩選刪除多個不需要的搜索詞/375

實例208快速繪制組織結(jié)構(gòu)圖，手把手教你怎么運用SmartArt/376

X.

基礎操作實戰(zhàn)應用

第1章

第1章?基礎操作實戰(zhàn)應用

學前提示

本章主要講解的是Excel基礎知識及操作技巧，盡量從日常工作和

生活的各方面精選簡單易學、實用性強的內(nèi)容，全面詳細地進行講解，

幫助讀者掌握軟件的基礎知識與技巧運用，通過實戰(zhàn)與理論結(jié)合教學

的方法，從入門到精通軟件。

本章知識重點

Excel快速切換多個工作簿的幾種方式詳解

Excel中那些讓你可以成倍提高工作效率的簡單技巧

在Excel中刪除多余的空行，輔助列+公式操作技巧

一秒美化表格的方法之套用表格格式設置底紋和邊框

Excel制作帶指紋和稅號編號的印章

.1

學會了效率高！Excel實戰(zhàn)技巧208例

學完本章后你會做什么

掌握合并單元格正確篩選出合并區(qū)多行內(nèi)容的解決方案

掌握Excel中已有數(shù)字快速輸入平方米和立方米的方法

掌握利用排序完成隔行插入空行制作簡單工資條的方法

視頻演示

制作花式批注制作帶指紋和稅號編號的印章

2.

基礎操作實戰(zhàn)應用

第1章

實例001Excel快速切換多個工作簿的幾種方式詳解

我們在工作中使用Excel文件時，在工作量較大、事情較多的情況

下，經(jīng)常會打開多個Excel工作簿，以便工作需求。下面詳細介紹幾種

入門的Excel快速切換多個工作簿的方式，不僅簡單，而且實用。

任務欄窗口：最常用的一種方法就是直接移動鼠標，單擊任務

欄中的Excel文件窗口進行切換，如圖1-1所示。當打開的文件過多，

導致文件名稱壓縮不能完全顯示出來時，用戶可以將鼠標停留在Excel

文件上，在彈出的預覽列表框中選擇需要切換的工作簿選項。

“切換窗口”下拉列表：單擊菜單欄中的“選項”命令，在其功

能區(qū)中，1單擊“切換窗口”下拉按鈕；在彈出的下拉列表框中，2選

擇需要切換的工作簿選項，即可進行快速切換，如圖1-2所示。

1單擊

2選擇

圖1-1?任務欄中的Excel文件窗口圖1-2?“切換窗口”下拉列表

“自定義快速訪問工具欄”快捷按鈕：在Excel窗口的左上角“自

定義快速訪問工具欄”中，1單擊“切換窗口”快捷按鈕；在彈出的下

拉列表中，2選擇需要切換的工作簿選項即可，如圖1-3所示。

.3

學會了效率高！Excel實戰(zhàn)技巧208例

1單擊

2選擇

圖1-3“自定義快速訪問工具欄”快捷按鈕

專家提醒

用戶可以在“自定義快速訪問工具欄”中自定義快捷工具按鈕，

將比較常用的功能按鈕添加至該欄中。在Excel窗口的左上角，單

擊“自定義快速訪問工具欄”下拉按鈕，在彈出的下拉列表框中選

擇“其他命令”選項，彈出“Excel選項”對話框，在“自定義快

速訪問工具欄”選項卡中，選擇需要的功能選項后，單擊“添加”|“確

定”按鈕，即可完成自定義添加快捷工具按鈕的操作。

讓Excel編輯欄字體變大的方法，方便查看和

實例002編輯公式

在Excel工作表中，當在編輯欄中輸入公式時，由于電腦分辨率不

夠大，會導致編輯欄字體很小，視覺效果差、不清晰，影響查看和編

輯。下面將介紹讓Excel編輯欄字體變大的操作方法，方便用戶查看和

編輯公式，特別是Excel教學的用戶，學會以后就不需要再另外截圖放

大了。

STEP01在Excel文件中，單擊菜單欄中的“文件”|“選項”命令，

如圖1-4所示。

4.

基礎操作實戰(zhàn)應用

第1章

STEP02彈出“Excel選項”對話框，在“常規(guī)”選項卡中的“新建

工作簿時”選項區(qū)中，1單擊“字號”右側(cè)的數(shù)值框下拉按鈕；2在彈

出的下拉列表框中選擇20，如圖1-5所示。

1單擊

單擊

2選擇

圖1-4?單擊“選項”命令圖1-5?選擇字號大小參數(shù)

STEP03然后單擊“確定”按鈕，彈出信息提示框，提示用戶需

要重新啟動程序軟件，字號才能生效，單擊“確定”按鈕，如圖1-6

所示。

STEP04執(zhí)行操作后，重新啟動Excel程序軟件即可，輸入公式查看

效果，如圖1-7所示。

查看

單擊

圖1-6?單擊“確定”按鈕圖1-7?輸入公式查看效果

.5

學會了效率高！Excel實戰(zhàn)技巧208例

不得不學的8個Excel批量操作技巧，再也

實例003不用加班了

使用Excel程序軟件時，有許多操作是可以批量進行的，下面介紹

8個Excel批量操作技巧，學會這些操作技巧，可以為用戶節(jié)省許多時

間，提高辦公效率，不用再加班！

批量錄入相同內(nèi)容：新建一個工作簿，1單擊選中需要的單元

格；2輸入“不加班”，按【Ctrl+Enter】組合鍵；3即可批量錄入相

同內(nèi)容，如圖1-8所示。

2輸入

1選中3錄入

圖1-8?批量錄入相同內(nèi)容

批量打開工作簿：當用戶需要同時用到多個Excel文件時，用

戶可以在按住【Ctrl】鍵的同時使用鼠標左鍵選中需要打開的文件，按

【Enter】鍵即可批量打開工作簿。

批量關閉工作簿：當用戶工作完成后，需要關閉打開的多個文

件時，按住【Shift】鍵的同時，單擊右上角的“關閉”按鈕即可。

批量填充空值：在打開的工作簿中，1選中有空白單元格的一

列；按【F5】鍵彈出“定位”對話框，在其中單擊“定位條件”按鈕，彈

出“定位條件”對話框，2在其中選中“空值”單選按鈕；3單擊“確定”

按鈕；4在第一個空白單元格中輸入公式=A3，然后按【Ctrl+Enter】

6.

基礎操作實戰(zhàn)應用

第1章

組合鍵；5即可批量填充空白單元格，如圖1-9所示。

1選中???????

2選中

3單擊

（圖1）（圖2）

4輸入

5填充

（圖3）（圖4）

圖1-9?批量填充空值

批量為城市添加省份：1選中需要添加省份的單元格；右擊，

在彈出的快捷菜單中，2選擇“設置單元格格式”選項；彈出“設置單

元格格式”對話框，在“數(shù)字”選項卡中的“分類”選項區(qū)中，3選擇

“自定義”選項；4在“類型”下方的文本框中輸入“廣東@”；5單

擊“確定”按鈕；6即可批量為城市添加省份，如圖1-10所示。

批量將每個部門生成一個表：首先創(chuàng)建一個數(shù)據(jù)透視表，然后

在菜單欄中選擇“分析”菜單，1在下方的功能區(qū)中單擊“選項”下拉

列表；在彈出的下拉列表框中，2選擇“顯示報表篩選頁”選項，彈出

提示對話框，單擊“確定”按鈕，3即可批量將每個部門單獨生成一個

表，如圖1-11所示。

.7

學會了效率高！Excel實戰(zhàn)技巧208例

4輸入

6添加

1選中

2選擇3選擇5單擊

圖1-10?批量為城市添加省份

1單擊

2選擇

3生成

圖1-11?批量將每個部門生成一個表

批量將單價上調(diào)10%：如今通貨膨脹、物價上漲速度很快，當

用戶需要將單價上調(diào)10%報價給客戶時，也可以進行批量操作，操作方

法十分簡單，首先在空白單元格中輸入1.1，并復制該單元格，然后選中

需要上調(diào)單價的單元格，右擊，在彈出的快捷菜單中，選擇“選擇性粘

貼”選項，彈出相應對話框，在“運算”選項區(qū)中，1選中“乘”單選

按鈕；2單擊“確定”按鈕；3即可批量將單價上調(diào)10%，如圖1-12

所示。

批量將負數(shù)變成0：當用戶需要將負數(shù)清零時，可以應用“替

換”來進行批量操作，按【Ctrl+H】組合鍵，可以快速彈出“查找和

替換”對話框并調(diào)出“替換”選項卡，此時用戶可以在“查找內(nèi)容”

8.

基礎操作實戰(zhàn)應用

第1章

右側(cè)的文本框中輸入“-*”，在“替換為”右側(cè)的文本框中輸入0，然

后單擊“全部替換”按鈕，即可批量將負數(shù)清零。

1選中3上調(diào)

2單擊

圖1-12?批量將單價上調(diào)10%

Excel中那些讓你可以成倍提高工作效率的

實例004簡單技巧

使用Excel辦公的人群有很多，但精通的人卻很少，例如有些簡單

又實用的操作技巧，就有很多人不知道該怎么用。下面將介紹幾個可

以幫助用戶成倍提高工作效率的令人“相見恨晚”的操作小技巧。

提升10倍效率的Excel轉(zhuǎn)壓縮包技巧

細心的用戶都會發(fā)現(xiàn)，在2007以上的版本中，Excel文件拓展名

稱格式用的是.xls，而現(xiàn)在2016版本中Excel文件拓展名稱格式用的

是.xlsx，相對之前的版本來說不僅多了一個x，還將文件大小進行了壓

縮，用戶還可以通過將Excel工作簿的拓展名稱格式修改成壓縮文件格

式，如.zip與.rar格式。當用戶需要將Excel工作簿中的大量圖片導出

或修改多個Excel文件表名稱時，就可以通過該技巧來提高工作效率。

.9

學會了效率高！Excel實戰(zhàn)技巧208例

1．導出Excel中的圖片

通常情況下需要把Excel工作表中的圖片導出來時，都會通過第三

方軟件來一張一張地另存出來，其實不用這么麻煩，首先選擇Excel工

作簿，按【F2】鍵或單擊，選中文件名，1然后將拓展名稱格式.xlsx

更改為.zip，將其轉(zhuǎn)換為壓縮包格式；將壓縮包解壓，打開xl|media

文件夾，2即可查看從Excel工作簿中導出的圖片，如圖1-13所示。

2查看

1更改

圖1-13?導出Excel中的圖片

2．修改多個文件表名稱

一些企業(yè)人員在年末做年度匯總總結(jié)時，匯總表一般都會延用去

年用過的表格格式，工作表通常會用年/月/日來進行命名，挨個將

文件名進行更改實在太麻煩，此時用壓縮文件的方式就可以解決問題，

下面介紹具體的操作步驟。

STEP01打開2017年的一個工作簿，在左下角的“上一個”或“下

一個”工作表按鈕上右擊，在彈出的窗口中，可以查看所有的工作表

名稱，如圖1-14所示。

10.

基礎操作實戰(zhàn)應用

第1章

STEP02關閉工作簿，將工作簿的拓展名稱格式.xlsx更改為.zip，

然后再雙擊壓縮包并打開，在解壓文件夾中，打開xl文件夾，選擇

workbook.xml文件，如圖1-15所示。

查看

選擇

圖1-14?查看所有的工作表名稱圖1-15?選擇workbook.xml文件

STEP03將選擇的文件復制到桌面上，在文件上右擊，在彈出的菜

單列表中選擇“用記事本打開該文件”選項，如圖1-16所示。

STEP04按【Ctrl+H】組合鍵，彈出“替換”對話框，1在“查找

內(nèi)容”右側(cè)的文本框中輸入2017年；2在“替換為”右側(cè)的文本框中

輸入2018年；3然后單擊“全部替換”按鈕，如圖1-17所示。

選擇

1輸入

2輸入

3單擊

圖1-16?選擇“用記事本打開該文件”選項?圖1-17?單擊“全部替換”按鈕

.11

學會了效率高！Excel實戰(zhàn)技巧208例

STEP05執(zhí)行操作后保存并退出，復制文件至壓縮文件夾中并替換，

如圖1-18所示。

STEP