買這商品的人也買了...
-
$980$774 -
$2,100$2,058 -
$990$891 -
$560$532 -
$620$490 -
$680$537 -
$880$695 -
$680$578 -
$690$587 -
$860$731 -
$550$435 -
$820$648 -
$490$387 -
$450$351 -
$520$411 -
$490$387 -
$550$435 -
$420$332 -
$650$553 -
$580$458 -
$650$429 -
$550$435 -
$680$537 -
$450$356 -
$360$284
相關主題
商品描述
Description
Millions of users create and share Excel spreadsheets every day, but few go deeply enough to learn the techniques that will make their work much easier. There are many ways to take advantage of Excel's advanced capabilities without spending hours on advanced study. Excel Hacks provides more than 130 hacks -- clever tools, tips and techniques -- that will leapfrog your work beyond the ordinary.
Now expanded to include Excel 2007, this resourceful, roll-up-your-sleeves guide gives you little known "backdoor" tricks for several Excel versions using different platforms and external applications. Think of this book as a toolbox. When a need arises or a problem occurs, you can simply use the right tool for the job. Hacks are grouped into chapters so you can find what you need quickly, including ways to:You can either browse through the book or read it from cover to cover, studying the procedures and scripts to learn more about Excel. However you use it, Excel Hacks will help you increase productivity and give you hours of "hacking" enjoyment along the way.
- Reduce workbook and worksheet frustration -- manage how users interact with worksheets, find and highlight information, and deal with debris and corruption.
- Analyze and manage data -- extend and automate these features, moving beyond the limited tasks they were designed to perform.
- Hack names -- learn not only how to name cells and ranges, but also how to create names that adapt to the data in your spreadsheet.
- Get the most out of PivotTables -- avoid the problems that make them frustrating and learn how to extend them.
- Create customized charts -- tweak and combine Excel's built-in charting capabilities.
- Hack formulas and functions -- subjects range from moving formulas around to dealing with datatype issues to improving recalculation time.
- Make the most of macros -- including ways to manage them and use them to extend other features.
- Connect Excel to the world -- use XML and take advantage of web sites and services from Google and Amazon to get information into and out of your spreadsheets.
Table of Contents
Credits
Preface
Chapter 1. Reducing Workbook and Worksheet Frustration
1. Create a Personal View of Your Workbooks
2. Enter Data into Multiple Worksheets Simultaneously
3. Prevent Users from Performing Certain Actions
4. Prevent Seemingly Unnecessary Prompts
5. Hide Worksheets So That They Cannot Be Unhidden
6. Customize the Templates Dialog and Default Workbook
7. Create an Index of Sheets in Your Workbook
8. Limit the Scrolling Range of Your Worksheet
9. Lock and Protect Cells Containing Formulas
10. Find Duplicate Data Using Conditional Formatting
11. Find Data That Appears Two or More Times Using Conditional Formatting
12. Tie Custom Toolbars to a Particular Workbook
13. Outsmart Excel's Relative Reference Handler
14. Remove Phantom Workbook Links
15. Reduce Workbook Bloat
16. Extract Data from a Corrupt WorkbookChapter 2. Hacking Excel's Built-in Features
17. Validate Data Based on a List on Another Worksheet
18. Control Conditional Formatting with Checkboxes
19. Identify Formulas with Conditional Formatting
20. Count or Sum Cells That Meet Conditional Formatting Criteria
21. Highlight Every Other Row or Column
22. Create 3-D Effects in Tables or Cells
23. Turn Conditional Formatting and Data Validation On and Off with a Checkbox
24. Support Multiple Lists in a ComboBox
25. Create Validation Lists That Change Based on a Selection from Another List
26. Use Replace... to Remove Unwanted Characters
27. Convert Text Numbers to Real Numbers
28. Extract the Numeric Portion of a Cell Entry
29. Customize Cell Comments
30. Sort by More Than Three Columns
31. Random Sorting
32. Manipulate Data with the Advanced Filter
33. Create Custom Number Formats
34. Add More Levels of Undo to Excel for Windows
35. Create Custom Lists
36. Boldface Excel Subtotals
37. Convert Excel Formulas and Functions to Values
38. Automatically Add Data to a Validation List
39. Hack Excel's Date and Time Features
40. Enable Grouping and Outlining on a Protected Worksheet
41. Prevent Blanks/Missing Fields in a Table
42. Provide Decreasing Data Validation Lists
43. Add a Custom List to the Fill HandleChapter 3. Naming Hacks
44. Address Data by Name
45. Use the Same Name for Ranges on Different Worksheets
46. Create Custom Functions Using Names
47. Create Ranges That Expand and Contract
48. Nest Dynamic Ranges for Maximum Flexibility
49. Identify Named Ranges on a WorksheetChapter 4. Hacking PivotTables
50. PivotTables: A Hack in Themselves
51. Share PivotTables but Not Their Data
52. Automate PivotTable Creation
53. Move PivotTable Grand Totals
54. Efficiently Pivot Another Workbook's DataChapter 5. Charting Hacks
55. Explode a Single Slice from a Pie Chart
56. Create Two Sets of Slices in One Pie Chart
57. Create Charts That Adjust to Data
58. Interact with Your Charts Using Custom Controls
59. Four Quick Ways to Update Your Charts
60. Hack Together a Simple Thermometer Chart
61. Create a Column Chart with Variable Widths and Heights
62. Create a Speedometer Chart
63. Link Chart Text Elements to a Cell
64. Hack Chart Data So That Empty or FALSE Formula Cells Are Not Plotted
65. Add a Directional Arrow to the End of a Line Series
66. Place an Arrow on the End of a Horizontal (X) Axis
67. Correct Narrow Columns When Using Dates
68. Position Axis Labels
69. Tornado Chart
70. Gauge Chart
71. Conditional Highlighting Axis Labels
72. Create Totals on a Stacked Column ChartChapter 6. Hacking Formulas and Functions
73. Add Descriptive Text to Your Formulas
74. Move Relative Formulas Without Changing References
75. Compare Two Excel Ranges
76. Fill All Blank Cells in a List
77. Make Your Formulas Increment by Rows When You Copy Across Columns
78. Convert Dates to Excel Formatted Dates
79. Sum or Count Cells While Avoiding Error Values
80. Reduce the Impact of Volatile Functions on Recalculation
81. Count Only One Instance of Each Entry in a List
82. Sum Every Second, Third, or Nth Row or Cell
83. Find the Nth Occurrence of a Value
84. Make the Excel Subtotal Function Dynamic
85. Add Date Extensions
86. Convert Numbers with the Negative Sign on the Right to Excel Numbers
87. Display Negative Time Values
88. Use the VLOOKUP Function Across Multiple Tables
89. Show Total Time As Days, Hours, and Minutes
90. Determine the Number of Specified Days in Any Month
91. Construct Mega-Formulas
92. Hack Mega-Formulas that Reference Other Workbooks
93. Hack One of Excel's Database Functions to Take the Place of Many Functions
94. Extract Specified Words from a Text String
95. Count Words in a Cell or Range of Cells
96. Return a Worksheet Name to a Cell
97. Sum Cells with Multiple Criteria
98. Count Cells with Multiple Criteria
99. Calculate a Sliding Tax Scale
100. Add/Subtract Months from a Date
101. Find the Last Day of Any Given Month
102. Calculate a Person's Age
103. Return the Weekday of a Date
104. Evaluate a Text Equation
105. Lookup from Within a CellChapter 7. Macro Hacks
106. Speed Up Code While Halting Screen Flicker
107. Run a Macro at a Set Time
108. Use CodeNames to Reference Sheets in Excel Workbooks
109. Connect Buttons to Macros Easily
110. Create a Workbook Splash Screen
111. Display a "Please Wait" Message
112. Have a Cell Ticked or Unticked upon Selection
113. Count or Sum Cells That Have a Specified Fill Color
114. Add the Microsoft Excel Calendar Control to Any Excel Workbook
115. Password-Protect and Unprotect All Excel Worksheets in One Fell Swoop
116. Retrieve a Workbook's Name and Path
117. Get Around Excel's Three-Criteria Limit for Conditional Formatting
118. Run Procedures on Protected Worksheets
119. Distribute Macros
120. Delete Rows Based on a Condition
121. Track and Report Changes in Excel
122. Automatically Add Date/Time to a Cell upon Entry
123. Create a List of Workbook Hyperlinks
124. Advanced Find
125. Find a Number Between Two Numbers
126. Convert Formula References from Relative to Absolute
127. Name a Workbook with the Text in a Cell
128. Hide and Restore Toolbars in Excel
129. Sort Worksheets
130. Password-Protect a Worksheet from Viewing
131. Change Text to Upper- or Proper Case
132. Force Text to Upper- or Proper Case
133. Prevent Case Sensitivity in VBA Code
134. Display AutoFilter CriteriaChapter 8. Cross-Application Hacks
135. Import Data from Access 2007 into Excel 2007
136. Retrieve Data from Closed Workbooks
137. Automate Word from Excel
138. Automate Outlook from ExcelIndex
商品描述(中文翻譯)
### 描述
數百萬用戶每天創建和分享 Excel 試算表,但很少有人深入學習能使他們的工作變得更輕鬆的技巧。有許多方法可以利用 Excel 的高級功能,而不必花費數小時進行高級學習。《Excel Hacks》提供了超過 130 個技巧——巧妙的工具、提示和技術——將您的工作提升到非凡的境界。
現在擴展到包括 Excel 2007,這本資源豐富、實用的指南為您提供了多個 Excel 版本在不同平台和外部應用程序中使用的鮮為人知的「後門」技巧。可以將這本書視為一個工具箱。當需要或出現問題時,您可以簡單地使用適合該工作的工具。技巧被分組到章節中,以便您可以快速找到所需的內容,包括以下方法:
- 減少工作簿和工作表的挫折感——管理用戶與工作表的互動,查找和突出顯示信息,處理垃圾和損壞。
- 分析和管理數據——擴展和自動化這些功能,超越它們設計的有限任務。
- 破解名稱——學習如何不僅命名單元格和範圍,還如何創建適應您試算表中數據的名稱。
- 充分利用樞紐分析表——避免使其令人沮喪的問題,並學習如何擴展它們。
- 創建自定義圖表——調整和組合 Excel 的內建圖表功能。
- 破解公式和函數——主題範圍從移動公式到處理數據類型問題,再到改善重新計算時間。
- 充分利用宏——包括管理宏和使用它們擴展其他功能的方法。
- 將 Excel 連接到世界——使用 XML,利用 Google 和 Amazon 的網站和服務將信息進出您的試算表。
您可以瀏覽這本書或從頭到尾閱讀,學習程序和腳本以深入了解 Excel。無論您如何使用它,《Excel Hacks》都將幫助您提高生產力,並在過程中帶來數小時的「破解」樂趣。
### 目錄
**致謝**
**前言**
**第 1 章 減少工作簿和工作表的挫折感**
1. 創建工作簿的個人視圖
2. 同時在多個工作表中輸入數據
3. 防止用戶執行某些操作
4. 防止看似不必要的提示
5. 隱藏工作表以便無法顯示
6. 自定義模板對話框和默認工作簿
7. 創建工作簿中工作表的索引
8. 限制工作表的滾動範圍
9. 鎖定和保護包含公式的單元格
10. 使用條件格式查找重複數據
11. 使用條件格式查找出現兩次或更多次的數據
12. 將自定義工具欄綁定到特定工作簿
13. 智勝 Excel 的相對引用處理器
14. 移除虛幻的工作簿鏈接
15. 減少工作簿膨脹
16. 從損壞的工作簿中提取數據
**第 2 章 破解 Excel 的內建功能**
17. 根據另一工作表上的列表驗證數據
18. 使用復選框控制條件格式
19. 使用條件格式識別公式
20. 計數或求和符合條件格式標準的單元格
21. 突出顯示每隔一行或一列
22. 在表格或單元格中創建 3-D 效果
23. 使用復選框開啟和關閉條件格式和數據驗證
24. 在 ComboBox 中支持多個列表
25. 創建根據另一列表的選擇而變化的驗證列表
26. 使用替換...刪除不需要的字符
27. 將文本數字轉換為實數
28. 提取單元格條目的數字部分
29. 自定義單元格註解
30. 按超過三列排序
31. 隨機排序
32. 使用高級篩選操作數據
33. 創建自定義數字格式
34. 為 Windows 版 Excel 添加更多的撤銷級別
35. 創建自定義列表
36. 粗體顯示 Excel 小計
37. 將 Excel 公式和函數轉換為數值
38. 自動將數據添加到驗證列表
39. 破解 Excel 的日期和時間功能
40. 在受保護的工作表上啟用分組和大綱
41. 防止表格中的空白/缺失字段
42. 提供遞減的數據驗證列表
43. 將自定義列表添加到填充手柄
**第 3 章 命名技巧**
44. 通過名稱引用數據
45. 在不同工作表上使用相同的名稱來引用範圍
46. 使用名稱創建自定義函數
47. 創建可擴展和收縮的範圍
48. 嵌套動態範圍以獲得最大靈活性
49. 在工作表上識別命名範圍
**第 4 章 破解樞紐分析表**
50. 樞紐分析表:本身就是一個技巧
51. 分享樞紐分析表但不分享其數據
52. 自動化樞紐分析表的創建
53. 移動樞紐分析表的總計
54. 高效地樞紐分析其他工作簿的數據
**第 5 章 圖表技巧**
55. 從圓餅圖中爆炸一個切片
56. 在一個圓餅圖中創建兩組切片
57. 創建隨數據調整的圖表
58. 使用自定義控件與圖表互動
59. 更新圖表的四種快速方法
60. 破解一個簡單的溫度計圖表
61. 創建具有可變寬度和高度的柱狀圖
62. 創建速度計圖表
63. 將圖表文本元素鏈接到單元格
64. 破解圖表數據,使空白或 FALSE 公式單元格不被繪製
65. 在線系列的末尾添加方向箭頭
66. 在水平 (X) 軸的末尾放置箭頭
67. 使用日期時修正狹窄的列
68. 定位軸標籤
69. 龍捲風圖
70. 儀表圖
71. 條件突出顯示軸標籤
72. 在堆疊柱狀圖上創建總計
**第 6 章 破解公式和函數**
73. 為您的公式添加描述性文本
74. 在不改變引用的情況下移動相對公式
75. 比較兩個 Excel 範圍
76. 填充列表中的所有空白單元格
77. 當您跨列複製時,使公式按行增量
78. 將日期轉換為 Excel 格式的日期
79. 在避免錯誤值的情況下求和或計數單元格
80. 減少波動函數對重新計算的影響
81. 僅計數列表中每個條目的單一實例
82. 求和每第二、第三或 N 行或單元格
83. 查找某個值的第 N 次出現
84. 使 Excel 小計函數動態
85. 添加日期擴展
86. 將右側帶負號的數字轉換為 Excel 數字
87. 顯示負時間值
88. 在多個表中使用 VLOOKUP 函數
89. 將總時間顯示為天、時和分
90. 確定任何月份中的指定天數
91. 構建 Mega-公式
92. 破解引用其他工作簿的 Mega-公式
93. 破解 Excel 的一個數據庫函數以取代多個函數
94. 從文本字符串中提取指定單詞
95. 計數單元格或單元格範圍中的單詞
96. 將工作表名稱返回到單元格
97. 根據多個條件求和單元格
98. 根據多個條件計數單元格
99. 計算滑動稅率
100. 從日期中加/減月份
101. 查找任何給定月份的最後一天
102. 計算一個人的年齡
103. 返回日期的星期幾
104. 評估文本方程
105. 在單元格內查找
**第 7 章 宏技巧**
106. 在停止螢幕閃爍的同時加速代碼
107. 在設定時間運行宏
108. 使用代碼名稱引用 Excel 工作簿中的工作表
109. 輕鬆將按鈕連接到宏
110. 創建工作簿啟動畫面
111. 顯示「請稍候」消息
112. 在選擇時使單元格被勾選或取消勾選
113. 計數或求和具有指定填充顏色的單元格
114. 將 Microsoft Excel 日曆控件添加到任何 Excel 工作簿
115. 一次性為所有 Excel 工作表設置密碼保護和解除保護
116. 檢索工作簿的名稱和路徑
117. 繞過 Excel 的三個條件限制以進行條件格式
118. 在受保護的工作表上運行程序
119. 分發宏
120. 刪除行