《Excel辦公技巧查詢與套用寶典》是2014年機械工業出版社出版的圖書，作者是張軍翔。
基本介紹
- 中文名：Excel辦公技巧查詢與套用寶典
- 作者：張軍翔
- 出版社：機械工業出版社
- 出版時間：2014年4月1日
- 頁數：540 頁
- 裝幀：平裝
- ISBN：9787111466666
內容簡介,圖書目錄,
內容簡介
本書共分17章，含蓋面廣、重點突出。即包括工作簿、工作表、行列等基本操作，又包含數據排序、篩選、模擬分析、假設分析、數據透視表分析，圖表等分析多方面技巧。最關鍵是技巧在選擇上確保實用、易學易懂，每個配圖都模擬了真實操作環境，讓讀者一看就知道這個操作對自己確實有用。本書結構編排合理，圖文並茂，實例豐富，可操作性強，可幫助用戶提升Excel的操作水平。無論是初學者還是經常使用Excel的行家，都可以從本書中受益。適合不同水平、不同辦公環境的用戶閱讀和學習。
圖書目錄
前言
第1章 操作工作簿
技巧1 使用模板創建新工作簿
技巧2 自定義工作簿默認的保存類型
技巧3 自定義工作簿默認的保存位置
技巧4 固定工作簿在最近使用的文檔列表
技巧5 查看工作簿的保存路徑
技巧6 實現自動啟動常用工作簿
技巧7 重新設定工作簿數據的默認字型、字號
技巧8 自定義工作簿默認工作表數
技巧9 清除打開的工作簿的記錄
技巧10 快速定位到資料夾
技巧11 快速合併與拆分多個工作簿數據
技巧12 加密工作簿
技巧13 設定只允許用戶進行部分操作
技巧14 繁簡轉換不求人
技巧15 外語翻譯助手
技巧16 向“快速訪問工具列”中添加命令
第2章 操作工作表
2.1 工作表的編輯
技巧1 操作工作表時快速切換至任意工作表
技巧2 批量重命名工作表名稱
技巧3 將工作表複製或移至另一工作簿中
技巧4 改變工作表標籤顏色
技巧5 設定工作表圖片背景
技巧6 防止他人對工作表的增刪
技巧7 指定工作表中的可編輯區域
技巧8 只保護工作表中的特定單元格或區域
技巧9 隱藏工作表內的部分數據
技巧10 徹底隱藏工作表
2.2 單元格、行、列操作
技巧11 準確定位多個任意的單元格區域
技巧12 選取多個工作表的相同區域
技巧13 一次插入多行或多列
技巧14 一次插入不連續的多行或多列
技巧15 隔行插入空行
技巧16 快速刪除所有空行
技巧17 設定合適的行高或列寬
技巧18 不連續行（列）的行高（列寬）一次性設定
技巧19 拖動交換兩列數據的位置
第3章 數據的輸入及自動填充
3.1 數據輸入
技巧1 讓數據前面的0不省略
技巧2 如何輸入生僻字
技巧3 記憶式輸入提高輸入效率
技巧4 不連續單元格中相同數據一次輸入
技巧5 多個工作表中相同數據一次輸入
技巧6 插入專業型數學公式
技巧7 讓輸入數據後自動添加小數點
技巧8 快速輸入指定格式日期
技巧9 快速輸入指定格式時間
技巧10 輸入大寫人民幣金額
技巧11 文字與數值在同一單元格並存且能計算
技巧12 部分重複數據快速輸入有捷徑
技巧13 改變大金額數據的顯示單位
技巧14 按小數點對齊小數
3.2 數據批量填充技巧
技巧15 連續單元格填充遞增的數字
技巧16 連續單元格快速填充相同日期
技巧17 只填充工作日日期
技巧18 妙用填充引用單元格的格式
技巧19 快速填充區域內的空白單元格
技巧20 自定義填充序列
技巧21 讓空白單元格自動填充上面的數據
技巧22 超大數據塊的一次性填充
3.3 導入外部數據
技巧23 導入其他Excel表格中數據
技巧24 導入文本檔案數據
技巧25 導入Word文檔中表格數據
技巧26 導入網頁中的有用表格
技巧27 根據網頁內容更新工作表數據
第4章 數據編輯與處理
4.1 常用編輯技巧
技巧1 數據隨單元格的列寬自動換行
技巧2 在任意位置上強制換行
技巧3 設定文字上下標效果
技巧4 妙用〈F4〉進行重複操作
技巧5 “拆分視窗”滾屏查看數據
技巧6 “凍結視窗”滾屏查看數據
技巧7 文本格式數字轉換為數字格式
技巧8 將數字轉換為日期
技巧9 連結到其他資料文檔
技巧10 滑鼠指向連結對象時顯示提示文字
技巧11 阻止Excel自動創建超連結
技巧12 如何編輯有超連結的單元格
4.2 數據合併拆分
技巧13 合併單元格時保留所有文本內容
技巧14 取消單元格合併的數據到未合併的區域中
技巧15 如何批量合併相同內容的單元格
技巧16 將一個單元格中的數據拆分到多列
4.3 數據複製
技巧17 貼上數據時匹配目標區域格式
技巧18 數據行列轉置生成新表格
技巧19 複製數據表保持原行高列寬
技巧20 多次複製、貼上相同的檔案和圖片
技巧21 選擇性貼上實現簡易數據計算
技巧22 選擇性貼上功能計算時忽略空單元格
技巧23 讓貼上數據隨原數據自動更新
技巧24 將表格快速轉換為圖片
技巧25 將公式計算結果轉換為實際值
技巧26 一次性複製公式到多個工作表中
4.4 查找和替換數據
技巧27 查找數據並突出顯示出來
技巧28 一次性找到所有空值單元格
技巧29 一次性找到所有設定了公式的單元格
技巧30 妙用通配符進行模糊查找
技巧31 替換的同時自動設定格式
技巧32 一次性取消Excel表中的換行符
技巧33 批量刪除特定的字元
技巧34 只在指定區域中進行替換
第5章 表格美化與列印
5.1 表格美化
技巧1 跨列居中顯示表格的標題
技巧2 標題套用會計用雙下劃線
技巧3 設定表格標題傾斜顯示
技巧4 套用表格樣式
技巧5 自定義表格樣式
技巧6 設定默認表格樣式
技巧7 自定義單元格樣式
技巧8 單元格樣式的修改
技巧9 將單元格樣式套用到其他工作簿中
技巧10 強大的格式刷
5.2 表格頁面設定與列印
技巧11 手動直觀調整頁邊距
技巧12 讓表格顯示在紙張的正中間
技巧13 在任意需要的位置上分頁
技巧14 刪除所有手動分頁符
技巧15 在頁眉頁腳中使用圖片
技巧16 設定頁腳起始頁碼為所需的頁數
技巧17 把頁面設定複製到其他工作表中
技巧18 列印指定的頁
技巧19 列印工作中某一特定區域
技巧20 列印整個工作簿
技巧21 一次性列印多張工作表
技巧22 將數據縮印在一頁紙內列印
技巧23 列印超寬工作表
技巧24 將表格列標題列印到每一頁中
技巧25 將行、列號一起列印出來
技巧26 在列印時不列印單元格中的顏色和底紋效果
技巧27 將工作表上的格線線列印出來
技巧28 將表格中添加的批註信息也列印出來
技巧29 巧妙列印工作表背景
第6章 條件格式
6.1 條件格式設定
技巧1 當數值大於或小於指定數值時特殊顯示
技巧2 當數值介於指定值之間時特殊顯示
技巧3 當排名前N名時顯示特殊格式
技巧4 讓值班安排表中只值班一次的特殊顯示
技巧5 當文本包含某個值時突出顯示
技巧6 將日期是本月的記錄特殊顯示
技巧7 用數據條顯示出不同級別人員的工資
技巧8 複製使用條件格式
技巧9 清除建立的條件格式規則
技巧10 禁止公式返回的錯誤值
6.2 在條件格式中使用公式
技巧11 工作表自動隔行填色
技巧12 當兩個單元格採購價格不同時就突出顯示出來
技巧13 為不包含某一特定字元的單元格設定格式
技巧14 標記特定年齡段的人員
技巧15 標識出表格中非數字單元格
技巧16 特殊顯示雙休日
技巧17 只在不及格成績的單元格上顯示圖示集
技巧18 突出顯示部門總經理
技巧19 將銷售業績高於平均值的標註為“優”
第7章 數據有效性設定
7.1 數據有效性設定
技巧1 創建下拉列表提高輸入效率
技巧2 選中單元格時給出輸入提示
技巧3 限定輸入指定範圍內的日期
技巧4 複製單元格的數據有效性設定
技巧5 在數據有效性序列中引用其他工作表的單元格區域
技巧6 在多個工作表中同時設定相同的有效性驗證
技巧7 為什麼數據有效性不顯示下拉箭頭
技巧8 為什麼單元格禁止輸入任何內容但數據有效性中
看不到設定
技巧9 圈釋無效數據
7.2 設定數據有效性時使用公式
技巧10 限制輸入空格
技巧11 禁止輸入重複值
技巧12 設定數據有效性使單元格只能輸入漢字
技巧13 限定輸入小數不超過兩位
技巧14 禁止錄入錯誤格式的產品規格
技巧15 限定單元格輸入必須包含指定內容
技巧16 把不符合要求的數據找出來
技巧17 提示出貨數量大於進貨數量
技巧18 使用有效性保護公式單元格不被修改
技巧19 創建二級聯動下拉列表
技巧20 創建多級聯動下拉列表
技巧21 帶提示功能的下拉列表實現多列快速錄入
第8章 表格數據的排序、篩選
8.1 排序的技巧
技巧1 按姓名的漢字筆畫排序
技巧2 設定按行排序
技巧3 有相同記錄時按第二個關鍵字排序
技巧4 自定義排序規則
技巧5 分類匯總後如何按匯總值排序
技巧6 在受保護的工作表中實現排序
技巧7 如何將多行內容隨機重新排序
技巧8 巧妙恢復排序前的數據
技巧9 對字母和數字組成的混合文本進行有規律的排序
8.2 數據篩選實用技巧
技巧10 篩選出大於指定數值的記錄
技巧11 篩選出介於指定數值間的記錄
技巧12 或條件篩選
技巧13 篩選大於平均值的記錄
技巧14 篩選數據表中排名前N位的記錄
技巧15 篩選出各門科目成績都大於等於90分的記錄
技巧16 篩選只要一門科目成績大於90分的記錄
技巧17 篩選出不重複記錄
技巧18 篩選出指定時間區域的記錄
技巧19 模糊篩選獲取同一類型的數據
技巧20 如何對雙行標題列表進行篩選
技巧21 自動篩選奇數行或偶數行的數據
技巧22 如何使用篩選列表中的搜尋功能
技巧23 在受保護的工作表中使用篩選
技巧24 解決自動篩選日期時不按年月日分組的問題
技巧25 將高級篩選結果輸出到其他位置
技巧26 使用公式作為篩選條件
技巧27 一次性取消所有工作表的自動篩選
第9章 表格數據的分析
9.1 數據的合併計算
技巧1 快速刪除表格中的重複記錄
技巧2 按位置合併計算多表數據
技巧3 按類別合併計算多表數據
技巧4 巧用合併計算檢測兩列數據差異
技巧5 讓合併計算結果自動更新
9.2 數據分類匯總
技巧6 創建多級分類匯總
技巧7 如何複製分類匯總結果
技巧8 只顯示分類匯總的結果
技巧9 取消分類匯總
9.3 模擬分析
技巧10 巧用單變數模擬運算錶快速計算員工業績獎金
技巧11 使用單變數求解產品的成本定價
技巧12 雙變數模擬運算表求解不同利率與貸款金額下的
月還款金額
技巧13 建立不同銷量的銷售方案
技巧14 規劃求解確定最優運輸方案
技巧15 使用“規劃求解”求解出所有產品利潤最大化
生產方案
第10章 用數據透視表統計分析數據
10.1 數據透視表創建與編輯
技巧1 用表格中部分數據建立數據透視表
技巧2 在當前工作表中顯示數據透視表
技巧3 重新更改數據透視的數據源
技巧4 設定默認的數據透視表樣式
技巧5 打開檔案時自動刷新數據透視表
技巧6 在每個分級之間添加空白行
技巧7 將數據透視錶轉換為普通表格
技巧8 刪除數據透視表
技巧9 根據數據透視表創建數據透視圖
10.2 報表數據的處理與分析
技巧10 更改統計結果的計算方式
技巧11 更改值的顯示方式（如顯示為占總和的百分比）
技巧12 顯示欄位的明細數據
技巧13 在數據透視表中設定按行排序
技巧14 禁止他人通過雙擊單元格查看明細數據
技巧15 如何清除欄位下拉列表中已刪除數據源的項目
技巧16 添加切片器快速實現數據篩選
技巧17 按工齡分組統計各工齡段的人數
技巧18 按周分組匯總產量
技巧19 按年、季度、月份分組匯總銷售額
技巧20 按小時分組統計生產量
10.3 自定義計算公式
技巧21 自定義公式求解各銷售部門獎金
技巧22 自定義公式計算學生總分
技巧23 自定義公式判斷應付金額與已付金額是否平賬
技巧24 自定義公式計算商品的加權平均單價
技巧25 查看當前數據透視表中自定義的公式
技巧26 刪除建立的計算欄位
第11章 用圖表比較分析數據
11.1 圖表新建技巧
技巧1 選擇不連續數據源建立圖表
技巧2 重新更改圖表的數據源
技巧3 用迷你圖展示一組數據
技巧4 給迷你圖添加文本
技巧5 更改迷你圖的類型
技巧6 向圖表中追加新數據
技巧7 更改圖表中系列的名稱
技巧8 混合使用不同的圖表類型
技巧9 隱藏的數據也可以作為圖表數據源
11.2 圖表編輯技巧
技巧10 準確選中圖表中要操作對象
技巧11 圖表標題與表格標題相連結
技巧12 按需要重設刻度值的數字類型
技巧13 更改刻度的最小值
技巧14 將垂直軸顯示在圖表正中位置
技巧15 讓相差懸殊的數據清晰顯示（對數刻度）
技巧16 次坐標軸在哪種情況下需要啟用
技巧17 設定數據系列分離（重疊）顯示
技巧18 重新調整數據系列的顯示次序
技巧19 拖動分離出餅圖中最大扇面
技巧20 套用圖表樣式快速美化
技巧21 在圖表中使用圖形圖片修飾
技巧22 在圖表中添加移動平均線
技巧23 在圖表中添加趨勢線
技巧24 實現縮放圖表時橫縱比不變
技巧25 快速取消圖表的所有格式
技巧26 製作靜態圖表
技巧27 將自己建立的圖表保存為模板
技巧28 複製到Word中使用
技巧29 將Excel圖表複製到幻燈片中使用
技巧30 只允許對指定圖表進行編輯
技巧31 保護圖表不被隨意更改
第12章 公式編輯與數據源引用必備技巧
12.1 公式的輸入與編輯
技巧1 快速獲取函式提示信息
技巧2 使用函式嚮導
技巧3 查看長公式中某一步的計算結果
技巧4 直觀查看單元格引用情況
技巧5 將公式轉換為數值
技巧6 快速複製公式的方法
技巧7 大範圍複製公式
技巧8 檢查公式的錯誤
技巧9 查看公式每步計算結果
技巧10 隱藏公式實現保護
12.2 公式中數據源的引用
技巧11 公式中引用相對引用數據源
技巧12 公式中引用絕對引用數據源
技巧13 引用其他工作表的單元格
技巧14 引用其他工作簿中的數據源
技巧15 引用多工作表的同一單元格計算
技巧16 〈F4〉鍵快速更改數據源的引用方式
技巧17 利用名稱框來定義名稱
技巧18 使用“定義名稱”功能來定義名稱
技巧19 根據所選內容批量創建名稱
技巧20 引用定義的名稱創建公式
技巧21 定義公式為名稱
技巧22 創建動態引用名稱
技巧23 計算動態數據平均數
技巧24 讓定義的名稱只套用於當前工作表
技巧25 查看當前工作簿中定義的所有名稱
技巧26 修改定義的名稱
技巧27 刪除不再使用的名稱
第13章 文本與邏輯函式
13.1 本章套用函式功能查詢
函式1 MID函式—返回特定數目的字元
函式2 LEFT函式—返回從文本左側開始指定個數的字元
函式3 RIGHT函式—返回最後一個或多個字元
函式4 FIXED函式—將數字按指定的小數位數進行取整
函式5 TEXT函式—將數值轉換為按指定數字格式表示
的文本
函式6 EXACT函式—比較兩個字元串是否完全相同
函式7 REPT函式—按給定次數重複顯示文本
函式8 SEARCH函式—返回第一個文本字元串的起始
位置的編號
函式9 REPALCE函式—替換文本字元串中的部分文本
函式10 SUBSTITUTE函式—在文本字元串中用new_text
替代old_text
函式11 CONCATENATE函式—將兩個值合併到另一個
單元格中
函式12 NOT函式—對參數值求反
函式13 OR函式—任何參數邏輯值為真時返回真
函式14 AND函式—檢驗一組數據是否都滿足條件
函式15 IF函式—根據指定條件判斷其“真”（TRUE）、
“假”（FALSE）
13.2 文本函式範例
技巧1 提取出產品編碼
技巧2 按兩位小數位數取整
技巧3 將文本型日期轉換為正確的日期格式顯示
技巧4 禁止手機號碼的後幾位數字
技巧5 如何處理文本中多餘的空格
技巧6 在班級前面統一加上“高三”字樣
技巧7 檢驗原始分數與查卷結果是否一致
技巧8 輸入多個相同的符號
技巧9 從最左側提取出長地址中的省市名稱
技巧10 合併編碼的時候顯示前面的0
技巧11 合併幾個單元格數據日期顯示為序列號怎么辦
技巧12 在項目編碼中提取出契約號
技巧13 將公司名稱格式化
技巧14 統計出實際參加人數
技巧15 輸入的身份證號碼長度是否正確
技巧16 判斷員工的綜合評定成績
技巧17 篩選出符合條件的應聘人員
技巧18 判斷是否錄取面試人員
技巧19 判斷是否為員工發放獎金
技巧20 根據業績計算應發總獎金
技巧21 根據職工性別和職務判斷退休年齡
第14章 數學函式
14.1 本章套用函式功能查詢
函式1 SUM函式—將指定的所有參數數字相加
函式2 SUMIF函式—對符合指定條件的值求和
函式3 SUMIFS函式—對滿足多個條件的單元格求和
函式4 SUMPRODUCT函式—將數組對應元素相乘並
返回乘積之和
函式5 ABS函式—返回數字的絕對值
函式6 ROUNDUP函式—向上捨入數字
函式7 ROUNDDOWN函式—向下捨入數字
函式8 MOD函式—返回兩數相除的餘數
函式9 INT函式—數字向下捨入到最接近的整數
函式10 CEILING函式—將向上捨入為最接近的倍數
函式11 ROUND函式—四捨五入為指定的位數
函式12 RAND函式—返回隨機實數
14.2 數學函式範例
技巧1 根據銷量和單價統計出總銷售額
技巧2 根據每月預算費用計算總預算費用
技巧3 統計同時滿足兩個條件的總和
技巧4 統計出各部門的工資總額
技巧5 統計某個時段的銷售總金額
技巧6 按多個條件統計
技巧7 統計某一日期區域的銷售金額
技巧8 統計總銷售額
技巧9 計算商品折後的總金額
技巧10 統計出指定部門獲取獎金的人數（去除空值）
技巧11 統計出指定班級分數大於指定值的人數
技巧12 統計指定專櫃各類別產品的銷售金額合計值
技巧13 使用SUMPRODUCT函式統計非工作日銷售金額
技巧14 求絕對值
技巧15 計算物品的快遞費用
技巧16 將金額按小數位數向下捨入計算
技巧17 統計出缺勤的人數
技巧18 只統計某兩個產品的合計金額
技巧19 使用通配符對某一類數據求和
技巧20 統計加班時長
技巧21 計算平均銷售量時取整
技巧22 以6秒（不足6秒按6秒計算）為單位計算
通話費用
技巧23 隨機獲取編號
第15章 統計函式
15.1 本章套用函式功能查詢
函式1 AVERAGE函式—計算算術平均值
函式2 AVERAGEA函式—返回參數的平均值
函式3 AVERAGEIF函式—返回滿足條件的平均值
函式4 AVERAGEIFS函式—返回滿足多重條件的平均值
函式5 HARMEAN函式—返回數據集合的調和平均值
函式6 TRIMMEAN函式—求數據集的平均值
函式7 COUNT函式—統計函式數字的單元格個數
函式8 COUNTIF函式—計算滿足條件的單元格個數
函式9 COUNTIFS函式—計算滿足多重條件的單元格的
個數
函式10 COUNTBLANK函式—計算區域中空白單元格的
數目
函式11 MIN函式—返回數據集中的最小值
函式12 MINA函式—返回列表中的最小值
函式13 MAX函式—返回數據集中的最大數值
函式14 MAXA函式—返回列表中的最大值
函式15 LARGE函式—返回某一數據集中的某個最大值
函式16 GROWTH函式—對給定的數據預測指數增長值
15.2 統計函式範例
技巧1 計算商品的平均價格
技巧2 忽略單元格中的0值求出平均值
技巧3 在含有文本值的區域中計算平均銷售額
技巧4 計算每個月的平均收入金額
技巧5 統計各班級平均分
技巧6 排除老店計算出平均利潤
技巧7 計算一車間女職工平均工資
技巧8 求介於某一區間內的平均值
技巧9 統計指定店面所有男裝品牌的平均利潤
技巧10 統計所有女裝品牌的平均利潤
技巧11 統計出成績表中缺考的人數
技巧12 統計出缺考人數合計
技巧13 統計出工資大於指定值的記錄條數
技巧14 統計出成績大於平均分數的學生人數
技巧15 使用通配符統計出某一類數據的記錄條數
技巧16 統計各店面男裝的銷售記錄條數（雙條件）
技巧17 計算出五天的平均產量
技巧18 根據評審打分計算選手的得分
技巧19 返回女職工的最大年齡
技巧20 返回上半個月單筆最高銷售金額
技巧21 返回成績表中的最高分（包含文本時）
技巧22 返回最低利潤額
技巧23 返回成績表中的最低分（包含文本時）
技巧24 返回排名前3位的銷售金額
技巧25 計算銷售金額前3名合計值
技巧26 統計數據表中前5名的平均值
技巧27 分別統計各班級第一名成績
技巧28 預測出指數增長值
第16章 查找函式
16.1 本章套用函式功能查詢
函式1 LOOKUP函式—向量形
函式2 LOOKUP函式—數組形
函式3 HLOOKUP函式—返回表格或數組中指定行的
所在列中的值
函式4 ROW函式—返回引用的行號
函式5 VLOOKUP函式—返回該區域相同行上任何
單元格中的值
函式6 INDIRECT函式—返回由文本字元串指定的引用
函式7 INDEX函式—返回數據清單或數組中的元素值
函式8 MATCH函式—返回數據清單或數組中的元素的
位置
函式9 OFFSET函式—通過給定偏移量得到新的引用
16.2 查找函式範例
技巧1 查找指定員工的信息
技巧2 輸入編號查詢指定員工的全部信息
技巧3 根據產品編號查詢庫存數量
技巧4 根據不同的返利率計算訂單的返利金額
技巧5 快速查詢任意月份每位銷售員的業績
技巧6 根據多條件計算員工年終獎
技巧7 在銷售表中自動返回產品單價
技巧8 統計出銷售記錄條數
技巧9 按指定的範圍計算平均值
技巧10 查找指定月份指定專櫃的銷售金額
技巧11 返回成績最高的學生的姓名
技巧12 查詢總金額最高的對應的店鋪（反向查詢）
技巧13 使用ROW函式控制處份序列的顯示行數
技巧14 按奇數月與偶數月統計銷量
技巧15 實現數據的動態查詢
技巧16 對每日出庫量累計求和
