Excel Hacks: Tips & Tools for Streamlining Your Spreadsheets, 2/e

David Hawley, Raina Hawley

  • 出版商: O'Reilly
  • 出版日期: 2007-07-24
  • 售價: $1,230
  • 貴賓價: 9.5$1,169
  • 語言: 英文
  • 頁數: 412
  • 裝訂: Paperback
  • ISBN: 0596528345
  • ISBN-13: 9780596528348
  • 相關分類: Excel
  • 海外代購書籍(需單獨結帳)

買這商品的人也買了...

商品描述

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:
  • 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.
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.

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 Workbook

Chapter 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 Handle

Chapter 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 Worksheet

Chapter 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 Data

Chapter 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 Chart

Chapter 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 Cell

Chapter 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 Criteria

Chapter 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 Excel

Index

 
 

商品描述(中文翻譯)

描述



每天有數百萬用戶創建和分享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. 從損壞的工作簿中提取數據```