初读《EXCEL Hacks》的体会

livewire

今天利用闲暇时间，大概看了一下第一章中的Hacks #1-15。
受益非浅，一针见血地指出了大多数EXCEL用户使用EXCEL时的不恰当的习惯，为了最大限度，最大效率地使用EXCEL潜能作者苦口婆心地规劝改正。
以下摘录翻译了此节中的主要观点：
some of the most common mistakes users make when setting up a spreadsheet:
当建产表格时，用户常犯的几个错误
Unnecessarily spreading data over many different workbooks， 不必要地将数据存储在多个工作簿中
Unnecessarily spreading data over numerous worksheets， 不必要地将数据分散在多个工作表中
Unnecessarily spreading data over different tables 不必要地将数据分布在不同的表格中
Having blank columns and rows in tables of data   在数据列表中有空行，空列
Leaving blank cells for repeated data  当数据与前一数据重复时留空
you should always try to keep related data in one continuous table.读者应始终要把相关的数据存储在同一张连续的表格中。
It is always a good idea to leave at least three blank rows above your table (at least three, preferably more). These can then be used for criteria for features such as Advanced Filter and Database functions.
在表格的最上面最少留出三个空行（再多几行，会更好些），以用于高级筛选和数据库函数等的条件区域。这常常是个好想法。
People also tinker with the alignment of cell data. By default, numbers in Excel are right-aligned and text is left-aligned, and there are good reasons to leave it this way. If you start changing this formatting, you will not be able to tell at a glance if the contents of a cell are text or numeric.有些读者会胡乱地修改单元格数据对齐方式。默认是数值右对齐，文本左对齐。保持这种对齐方式是有好理由的。如果改变了这种默认对齐方式。就不会一眼看出单元格的内容是文本还是数值。
Format cells as text only when completely necessary. All data entered into cells formatted as text become text, even if you meant for them to be numbers or dates. Worse still, any cell housing a formula that references a text-formatted cell also will be formatted as text. Generally, you do not want formula cells to be formatted
当完全必要时才可将单元格格式设为文本。所有输入到格式设为文本的单元格中的数据将为文本，即使想输入数值，日期时间。更为糟糕的是，如果某一单元格中的公式引用了格式设为文本的单元格，那么这个单元格也将被格式化为文本，这是我们不希望的。
Merged cells can also cause problems. The Microsoft knowledge base is full of frequently encountered problems with merged cells. As a good alternative, use "Center across selection," found on the Alignment tab of the Format Cells dialog under the Horizontal tab.
合并单元格也会导致问题。一个好的替代方法是： 单元格格式对话框－－>对齐选项卡——>水平对齐下拉框下选“Center across selection”（或许是跨选择列居中）由于没装中文版，请允许先凑合一下）
BTW, 没有跨选择行居中噢！

[ 本帖最后由 livewire 于 2010-6-3 09:39 编辑 ]

lrf2003

不错，列出了做表格时应注意事项，盼望楼主能介绍更多的内容。

yusanfengyi

楼主牛气啊，英文版的都看完了~我有这本书，不过一直没看，学海无涯，吾生有涯啊！

livewire

自定义单元格批注 Hack 28 Customize Cell Comments

1 自定义批注中自动插入的用户名。
工具-->选项-->general选项卡
注:对更改之后插入的批注有效,之前插入的批注保持原样.   SHIFT+F2: 活动单元格插入批注


2  将枯燥的批注方框替换为其它形状并加阴影效果
a. 定位到需更改批注样式的单元格
b. SHIFT+F2
c. 按ESC键退出批注内容编辑模式,或左键点击方框边框
d. 绘图工具栏-->绘图-->change autoshape 更改自动形状  选择自已喜欢的
e 点击绘图工具栏的阴影按钮, 添加阴影效果

注:双击批注边框可更改字体/填充色等其它格式.


[ 本帖最后由 livewire 于 2008-12-3 12:45 编辑 ]

livewire

Hack 70 Find the nth Occurrence of a Value
Hack 70 查找某个数据的第n次出现
Excel's built-in lookup functions can do some pretty clever stuff, but unfortunately Excel has no single function that will return the nth occurrence of specified data. Fortunately, there are ways to make Excel do this.
EXCEL内置的查找函数可以做一些相当聪明的事情。但是不幸的是EXCEL没有单一的函数能够返回指定数据的第N次出现。幸运的是，我们有方法让EXCEL完成这项工作。

You can use Excel's lookup and reference functions on a table of data to extract details corresponding to a specified value. Perhaps the most popular of these Excel functions is VLOOKUP. Although VLOOKUP is great for finding a specified value in the leftmost column of a table, you cannot use it to find the nth occurrence in the leftmost column.
我们可以对一个数据列表使用EXCEL的查找与引用函数来提取某一指定数据对应的详细信息。可能最常用的函数是VLOOKUP, 尽管VLOOKUP在数据列表最左列查找指定数据很有用，但是不能用它来找出在最左列中的第N次出现。

You can, however, use a very simple method to find any specified occurrence you choose when using VLOOKUP, or one of the other lookup functions.
然而我们可以通过一个非常简单的方法用VLOOKUP或其它一个查找函数来找到我们选择的第几次出现。
For this example, we will assume you have a two-column table of data, with column A housing first names and column B their corresponding ages, as shown in Figure 6-12
举个例子，假设有一个两列的数据列表。A列存放名字，B例是对应的年龄。如图6-12所示：

You can use a VLOOKUP function to extract a person's age based on his name. Unfortunately, some names occur more than once. You want to be able to look up the name Dave and have the VLOOKUP function find not the first occurrence, but rather, subsequent occurrences of the name. Here is how you can do this (remember, in this example, data is in columns A and B).
使用VLOOKUP函数可以根据名字查取对应的年龄。但是一些名字不止出现一次。我们想查找名字Dave, 想让VLOOKUP函数找到非第首次出现的位置，就是这个名字后续出现的位置。下面就是怎样来实现它（注：举例的数据在A列和B列）
First, select column A in its entirety by clicking the letter A at the column head, and then select Insert  Columns to insert a blank column (which will become column A). Click in cell A2 (skipping A1 because B1 is a heading), and enter this formula:
首先，点击列标题栏中的字母A，选中A列整列，然后选择插入》插入列 插入一个空白列（此列将成为A列），点击A2单元格（跳过A1单元格，因为B1是标题）输入下面的公式：
=B2&COUNTIF($B$2:B2,B2)
Copy this down as many rows as you have data in column B (click back in cell A2 and double-click the fill handle). You will end up with names such as Dave1, Dave2, Dave3, etc., as shown in Figure 6-13. Note the absolute reference to $B$2 in the COUNTIF function and the use of a relative reference for all references. This is vital to the function working correctly.
重新选择A2单元格，双击填充柄，将公式向下复制多行至B列最后数据的那一行，最后我们得到诸如Dave1, Dave2, Dave3, 等等这样的名字，如图6-13所示。注意在COUNTIF函数中$B$2的绝对引用，其它引用使用相对引用。这对公式得到正确结果是至关重要的。

If you haven't guessed already, now you can use column A as the column to find the nth occurrence of any name.
读者还没猜出来吗？现在可以在A列中找出任一名字的第N次出现的位置
Click in cell D2 and enter in the following formula:
点击D2单元格，输入以下公式：
=VLOOKUP("Dave3",$A$1:$C$100,3,FALSE)
The formula will return the age for the third occurrence of the name Dave, as shown in Figure 6-14.
这个公式将返回名字Dave第三次出现的年龄。如图6-14所示：


You can, of course, hide column A from view, as you do not need to see it.
如果我们不想看到A列，当然可以隐藏A列。
You also can use the names in column A as the Source range for a list in another cell by selecting Data  Validation  List. Then reference the cell housing this list in your VLOOKUP function.
我们也可以把A列中的名字做为另一单元格数据有效性序列的源区域， 然后在VLOOKUP函数中引用这个单元格。

[ 本帖最后由 livewire 于 2008-12-7 11:12 编辑 ]

livewire

见论坛里面有坛友焦急地询问如何修复已损坏的文档。今天抽时间翻译了HACK15
期望有所帮助。最为重要的是：定期备份重要文档。做了重要更新后及时备份。
Hack 15 Extract Data from a Corrupt Workbook
Hack 15 从损坏的工作簿中提取数据
Workbook corruption can mean the loss of vital data, costing you more than just money. This hack explores some methods that might recover your data.
工作簿损坏意味着重要数据的丢失，带来的损失不仅仅是金钱，比这要多。此HACK探索了几种可以恢复数据的方法。
Workbooks sometimes become corrupt for no apparent reason. This can cause all sorts of problems, especially if the workbook is vital and for whatever reason you have no backup. Lesson 1: always back up your data somewhere. Realistically, though, this does not always happen, and corruption can, of course, occur right before your regularly scheduled backup.
工作簿有时没有明显原因而损坏。这会带来各种问题，尤其当工作簿至关重要，且无论是何种原因都没有备份。教训1：总是备份数据到某些地方。实际上虽然不是经常做，损坏会恰恰发生在定期已计划的备份之前。
To add to your frustration, even though you know your workbook is corrupt, you sometimes might still be able to open it and even perform certain actions in it.
更会让人失望的是，即使知道工作簿已损坏，有时仍然可以打开它，甚至在里面进行一些操作。
If You Can Open Your Workbook
如果可以打开工作簿
If you can open the offending workbook, before doing anything else, be sure to save a copy of it; otherwise, you might regret it. If you have a copy, you can always seek professional help!
如果可以打开损坏的工作簿，在做任何其它操作之前，确保另存一份。否则会后悔。如果有一份拷贝，可以一直寻求专业的帮助产。
Now, try opening the workbook in a later version of Excel and simply saving. Obviously this is not possible if you already are using the latest version of Excel.
现在，试着在较高版本的EXCEL中打开，简单地保存一下。当然如果已在使用最新版本的EXCEL, 这是不可能做到的。
If this doesn't work, try opening your workbook and saving the file in HTML or HTM format, then close the file and reopen it, this time saving again in the format you require—e.g., .xls.
如果这样不能修复的话，试着打开工作簿，另存为HTML或HTM格式，然后关闭文件，重新打开HTML或HTM格式的文件，这次另存为想要的格式－比如.XLS.
When saving in HTML or HTM format, the following features will be lost:
当另存为HTML或HTM格式，以下特性将丢失：
•        Custom views
•        自定义视图
•        Unused number formats
•        未使用的数字格式
•        Unused styles
•        未使用的样式
•        Data consolidation settings
•        数据合并设置
•        Scenarios
•        方案
•        Natural language formulas (they are converted to standard range references)
•        自然语言公式（将转换为标准的单元格区域引用）
•        Custom function categories
•        自定义函数
•        Strikethrough, subscript, and superscript elements
•        中间划线，上标，下标元素
•        Change History
•        更改历史
•        Customized page setup settings for charts that are embedded on a worksheet
•        针对嵌入到工作表中的图表的自定义页面设置
•        List settings for ListBoxes and ComboBoxes from the Forms toolbar
•        窗体工具栏中的列表框和组合框的列表设置
•        Conditional formatting that is stored on an XLM macro sheet
•        保存在XLM宏工作表中的条件格式
Also, shared workbooks will no longer be shared. The "Value (Y) axis crosses at category number" setting on the Scale tab of the Format Axis dialog box is not saved if the "Value (Y) axis crosses a maximum category" checkbox is checked. The "Vary colors by point" setting in the Format Data Series dialog box is not saved if the chart contains more than one data series.
Finally, try opening your file and saving it in SYLK (.slk, for symbolic link) format. Note that when you save a workbook in this format, only the active worksheet is saved. So, you will have to do the same for each worksheet. Reopen the file and save it in a desired format such as .xls.
最后，试着打开文件，另存为SYLK格式(.slk, symbolic link符号链接)。注意当将工作簿另存为这种格式时，只有活动工作表被另存，因此，需要对每个工作表执行相同操作。重新打开这个YLK格式文件，另存为需要的格式，比如.xls.
If You Cannot Open Your File
如果不能打开文件
If your workbook is corrupt to the point that you cannot even open it, open your spreadsheet in Microsoft Word or via the Spreadsheet viewer, which can be downloaded from the Microsoft web site, then copy your data from the open file. Much of your formatting, formulas, etc., will, however, be lost.
如果工作簿损坏到甚至不能用EXCEL打开，用MS Word或Spreadsheet viewer（表格浏览器）打开, Spreadsheet viewer（表格浏览器）可从微软的网站上下载。然后从打开的文件中拷贝数据，然而很多格式和公式等等会丢失。
Next, open a new workbook and create an external link to the corrupt workbook—e.g., ='C:\Documents and Settings\Raina\My Documents\[ChookSheet.xls]Sheet1'!A1. Copy this link down as many rows and across as many columns as needed. Do the same for each worksheet in the workbook. If you cannot remember any of the names of the worksheets, create any old sheet name using the correct filename path, and Excel will display the sheet names for you when you press Enter.
下一步，打开一个新工作簿，在A1单元格内建立一个指向损坏工作簿的外部链接－例如“='C:\Documents and Settings\Raina\My Documents\[ChookSheet.xls]Sheet1'!A1. 向下复制和向右复制所需的多行多列。工作簿中的每个工作表执行相同操作。如果没记住工作表名，工作簿名路径正确的情况下使用任意旧的工作表名，EXCEL会在按ENTER键时显示工作表名。
One final thing you can do is visit the OpenOffice.org web site and download the free version of OpenOffice.org. Except for different names for different tools and commands, OpenOffice.org is very similar to Excel. OpenOffice.org is based on the same basic spreadsheet structure as Excel, making it simple for Excel users to use. In fact, about 96% of the formulas used in Excel can be created and applied by using the spreadsheet in OpenOffice.org.
一个最后可以做的事情是访问OpenOffice.org网址，下载OpenOffice.org的免费版，除不同的工具和命令有不同的名称之外，OpenOffice.org与EXCEL非常相似。OpenOffice.org基与同EXCEL相同的电子表格结构，对于EXCEL用户来说易于使用。事实上，EXCEL中96%的公式可以在OpenOffice.org表格建立和使用。
To download the free version of OpenOffice.org, go to http://download.openoffice.org/index.html and download it from the FTP site of your choice. Then install the program. OpenOffice.org is also available for Macs.
要下载免费版OpenOffice.org，访问网址http://download.openoffice.org/index.html， 根据读者的选择可以自FTP网站上下载，接着安装软件。OpenOffice.org 也有适用于苹果机的版本。
In many cases, your Excel data can be recovered. However, no VBA code can be recovered due to incompatibility between OpenOffice.org and Excel.
在很多情况下，EXCEL数据可以恢复。然而因为OpenOffice.org 和 Excel的不兼容，VBA代码不可恢复。
Sadly, if none of these methods works, you probably will have to pay to try to have your workbook recovered with special software. One source where such reputable software (for Windows) can be purchased belongs to the authors of this book and is located at http://www.ozgrid.com/Services/corrupt-file-recovery-index.htm.
郁闷的是，如果这些方法都不见效，就很可能不得不花点银子使用专用软件进行修复。可供购买象这样著名的软件（用于WINDOWS）的一个资源属于本书的作者组，位于http://www.ozgrid.com/Services/corrupt-file-recovery-index.htm.
After purchase and installation, run the ExcelFix program. Click Select File, select a corrupt file, and then click Diagnose to recover the file. You should now see the recovered file in the workbook viewer. Click Save Workbook to save the workbook into a new readable file that you can open from Excel.
购买安装后，运行ExcelFix程序，点击选择文件（Select File），选择一个损坏的文档，然后点击诊断（Diagnose）来修复文档。应该在工作簿浏览器中看到已修复的文档。点击保存工作簿（Save Workbook）保存成一个新的可被EXCEL打开的文档。
Also available is a demo version that does not enable you to save the file, but all versions of the program enable you to start again and recover as many files as you want.
演示版没有保存已修复文档的命令，但是所有的版本允许再次开始来修复所需要的多个文档。

[ 本帖最后由 livewire 于 2008-12-13 12:23 编辑 ]

livewire

Hack 14 Reduce Workbook Bloat
HACK 14  工作簿文件瘦身大法

一个里面输入了相当多数据的典型工作簿，如果只有数据的话，文件大小也就1.37 MB。如果插入一个引用四个整列作为其数据源的数据透视表，文件的大小会激增到2.4 MB。如果再设置一些格式，文件的大小会翻倍。
尤其在早期版本的EXCEL中，导致文件肥大的常见原因是：整列整行地设置格式，而不是使用的数据区域。另外一个错误的做法就是：在插入图表、数据透视表时，不是将实际的实际的数据区域作为数据源，而是引用整列。
要修复这些问题，就是要清除所有不必要的格式设置；将数据源限制在仅有用的单元格区域内。
为了数据安全起见，在修复之前，总是要先备份文件。
清除不必要的格式
首先要定位数据区域的最右下角单元格。不要依赖编辑  定位...  定位条件  最后一个单元格。这个会定位到最后一个设置了格式的单元格。
需要手工找到这个单元格，选中紧临下面一行，按CTRL+SHIFT+下箭头键，选中数据区域下面的所有行。 然后 编辑 清除  所有 来清除

同样的操作思路来清除数据区域右边所有列的不必要的格式。选中紧临右面的一列，按CTRL+SHIFT+右箭头键，选中数据区域下面的所有行。 然后 编辑 清除  所有 来清除 。

不要试图删除这些行列，这样会导致引用这些行列上的单元格的公式出现#REF!

保存文件，查看文件大小。文件  属性...  常规.

将所有模块中的宏代码和用户窗体导出保存在本地硬盘上。然后删除。
进入VBE编辑器，在项目浏览器内，右击每个模块，选择移除Module，当系统询问，是否在移除之前导出模块，选YES, 并记下存放的路径 。

最后保存工作簿。然后再重新导入每个模块和用户窗体。这样做会清除模块中的多余垃圾。
网站有这样的免费工具，但是有时会搞乱代码，甚至增加了文件大小。所有用这些工具之前，总是要先备份。因为工具的开发人员对于任何的数据损失不负有责任。

优化数据源
通过以上操作，如果文件还是过大，另一个因素是数据透视表、视图中引用了多余的单元格。人们为了避免添加新数据以后需手工更新引用区域，常常引用65,536 行（整列）。在HACK42中将介绍使用定义名称的动态区域。

清除损坏的工作表

首先备份文档
首先取消工作表隐藏，格式 工作表  取消隐藏。以使所有的工作表显示出来。从第一个工作表开始到最后一个工作表，先删除一个工作表，保存文档，再查看文件大小，如果文件尺寸大幅减速小，那么刚删掉的那个工作表很有可能已损坏。
要替换掉已损坏的工作表，建立一个新工作表，手工选择已损坏的工作表中的数据，然后剪切（不要复制），粘贴到新工作表中，然后删除损坏的工作表，保存。

[ 本帖最后由 livewire 于 2008-12-21 13:34 编辑 ]

livewire

Hack 33 Add More Levels of Undo to Excel for Windows
Hack 33 增加更多的UNDO(撤消上次操作）的次数（适用于Windows版的EXCEL)

我们都熟悉EXCEL的UNDO特性，此特性使用户能够撤消上次错误操作。但是，缺省的UNDO次数只有16次， 我们通过更改注册表可以允许撤消100次错误。
当使用EXCEL UNDO功能，UNDO到16次时，第一个UNDO将被第17个UNDO覆盖。只要一保存文档，UNDO的堆栈将被清除，UNDO的历史丢失，这是因为当你点击了SAVE命令，实际上就是告诉EXCEL你对所对的更改很满意，EXCEL为你做了决定:UNDO的历史记录不再需要啦。
读者也许会发现，仅仅能UNDO 16次是远远不够滴。我们可以通过编辑注册表来修改它。

1. 完全关闭EXCEL应用程序;
2.点“开始”－－》“运行”，在打开的窗口中输入Regedit.exe, 点击“确定”,打开注册表编辑器
3.展开HKEY_CURRENT_USER， 再展开下一层的 Software ，再展开 Software ， Microsoft ， Office，10.0（10.0 is for Excel 2002，那么11.0是2003），再展开EXECL,最后展开Options
(偶用EXCEL2000， 9.0 作示范）




选菜单“编辑”－－》“新建”DWORD值， 输入单词UndoHistory， 按ENTER键。 双击刚刚创建的UndoHistory，输入一个16~100之间的值
（也可以在注册表编辑器窗口内点鼠标右键）



100次的UNDO对大多数用户已经够用了。要注意的是：当一保存文档，UNDO的堆栈仍然会被清除干净。

[ 本帖最后由 livewire 于 2009-1-10 11:24 编辑 ]

livewire

Hack 43 Nest Dynamic Ranges for Maximum Flexibility
HACK43 最大限度灵活地组织动态区域

一个命名的动态区域嵌套在另一个命名的动态区域里，对于诸如很长的名字列表来说，是很有用的。

例如，可以建立一个名称为Jnames的区域来引用在一个排好序的名字列表中以字母J开头的所有名字。

名字列表在A列，如图3-8所示。单元格A1是标题，名称列表已排好序。
点击 插入Insert名称Names定义Define。在工作簿名称框内输入Names,在引用位置框内输入以下公式：
=OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)

点击 添加Add. 现在再回点工作簿名称框，输入名称Jnames(J可以是任何所需的字母), 在引用位置框内输入以下公式：
=OFFSET(INDIRECT(ADDRESS(MATCH("J*",Names,0)+1,1)),0,0,COUNTIF(Names,"J*"),1)

这里的"J*"用于匹配名字以字母J开头的数据。点击 添加Add.当回点公式所在的引用位置框时，所有以字母J开头的名字将被虚线框框起来。



如果需要，可以给字母表中的每一字母建立一个命名的区域，但是也许较好的方案是让命名的区域按照输入到某一工作表中某一单元格的字母来变化。要实现它，输入任意字母至任一未使用的单元格中，然后命名这个单元格为Letter。

选择 数据Data有效性Validation, 在允许框内选序列，在来源框内输入A*,B*,C*, 等等，直到所有的26个字母输完。如图3－9所示，做完后点击确定。

（为了方便起见，我在单元格区域内用公式=CHAR(ROW()+64)&"*" 生成）



点击 插入Insert名称Names定义Define。在工作簿名称框内输入Names,在引用位置框内输入以下公式后点击添加Add： =OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)

再回点工作簿名称框，输入名称LetterNames, 在引用位置框内输入以下公式后点击添加Add，点击确定：

=OFFSET(INDIRECT(ADDRESS(MATCH(Letter,Names,0)+1,1)),0,0,COUNTIF(Names,Letter),1)

结果如图3－10所示



附上我使用的示例文件：

Hack43_example.rar (140.89 KB, 下载次数: 84)

2009-1-24 09:22 上传

点击文件名下载附件

[ 本帖最后由 livewire 于 2009-1-24 09:22 编辑 ]

kyros

很好的笔记啊，为什么不继续写呢