Excel应用与技巧.doc

Excel应用与技巧Excel应用与技巧目 录一、有关Excel中零值的设置二、批量制作利息催收通知单三、在EXCEL中“随叫随到”四、奇怪的Excel问题五、让Excel的格式自动变化六、了解Excel公式的错误值七、你的复制“所见即所得”吗？八、用Excel函数自动排名次九、重名检索与姓氏频率统计第二页十、用Excel函数排序与筛选十一、用Excel统计2000年中国甲A联赛十二、EXCEL2000使用技巧十招十三、Excel2000工作薄安全攻略十四、Excel文件的加密与隐藏十五、Excel使用技巧42招一、有关Excel中零值的设置在Excel数据处理中，零（0）值是一个比较特殊的数值，它就像一个幽灵一样让人难以捉摸，时而出现，时而隐藏，下面就我们来看看对付零值的几个比较有效的方法。1、在Excel中我们输入诸如“8.0”、“010”等数值时，只要光标移出该单元格之后，上述数字就会自动变成“8”、“10”整数格式，Excel这一举动让人哭笑不得。要想避免这种情况，可进行下列操作：选中输入“8.0”、“010”这类格式的单元格，单击鼠标右键，在弹出的菜单中选择“设置单元格格式”，接下来在弹出的窗口中选择“数字”标签，在“分类”列表中选中“文本”项，单击“确定”。这样，在这些单元各种就可以输入诸如“8.0”、“010”等格式的数字了。2、在默认情况下，零值将显示为0（零），太多的零值让我们的工作表显得十分凌乱，不便于我们的统计工作，其实我们可以通过修改选项，灵活地显示或隐藏零值，方法如下：在菜单栏中选择“工具”、“选项”命令，打开“选项”对话框，单击“视图”标签，在“窗口选项”里把“零值”复选框前面的对号去掉，单击“确定”按钮，就可以看到原来显示有0的单元格全部变成了空白单元格。若要在单元格里重新显示0，用上述方法把“零值”复选框前面的打上对号即可。3、我们还可以用一个特殊的格式来隐藏选定单元格的零值，使隐藏的零值只会出现在编辑栏或正在编辑的单元格中，而不会被打印。方法如下：按住Ctrl键用鼠标左键一一选定需要隐藏零值的单元格，在菜单栏中选择“格式”、“单元格”命令，在“单元格格式”对话框选择“数字”选项卡，在“分类”列表框中选择“自定义”选项，然后在右边的“类型”文本框中输入“0;_0;”，单击“确定”按钮。要将隐藏的零值重新显示出来，可选定单元格，然后在“单元格格式”对话框的“数字”选项卡中，单击“分类”列表中的“常规”选项，这样就可以应用默认的格式，隐藏的零值就会显示出来。二、批量制作利息催收通知单在银行信贷部门的日常工作中，经常要利用会计部门提供的贷款欠息情况表，向每个贷款欠息户发放贷款利息催收通知单催收利息。由于贷款欠息户数量较多，采用常规的逐户编辑打印利息催收通知单的方法势必耗费大量的工作时间，经过一段时间的摸索，通过对EXCEL 2000和WORD 2000灵活运用，找到了一条快速、准确完成大批量制作贷款利息催收通知单工作的捷径。现将制作过程介绍如下，供有类似要求的读者朋友参考。一、利用EXCEL 2000实现利息小写金额向中文大写金额的转换1、数据导入。运行EXCEL 2000，点击工具栏上“打开文件”按钮，找到会计部门提供的贷款欠息情况表文 件后，点击“打开”，EXCEL 2000自动弹出“文本导入向导”3步骤之1：如设置无误，点击“下一步”按钮，进入“文本导入向导”3步骤之2，在这里你可以选择合适的分隔符号，在预览窗中可预览分列效果，满意后点击“下一步”按钮，进入“文本导入向导”3步骤之3，在这里你可以对每列数据格式进行简单设置(当然可以在EXCEL 2000中进行高级设置)，如果我们不是导入全部数据列，还可以选择不导入某列，当所有设置完成后，点击“完成”按钮，将数据导入EXCEL 2000中，并将文件另存为EXCEL文件格式(本文设存盘文件名为“应交利息.XLS)。2、格式转换。由于在EXCEL 2000中系统提供的中文大写数字格式与我们日常使用的中文大写金额格式相差 较远，如小写金额“138.59元”在EXCEL 2000中只能转换成“壹佰叁拾捌.伍玖”，因此我们必须想办法实现正确的格式转换。经过实验，发现通过设置中间字段，利用IF判断函数、TRUNC截尾取整函数及TEXT函数可以很好地解决这个问题。1)、打开“应交利息.XLS”文件，在D1至G1单元格按顺序增加“元位”、“角位”、“分位”、“应交利 息(大写)”四个字段。2)、为了能将“应交利息”C列中小写金额转换成中文大写金额，我们需将小写金额按“元位”、“角位 ”、“分位”进行分解转换，并在相应位置添加“元”、“角”、“分”符号，以备将来合并之用。具体分解通过下列公式实现：D2=IF(TRUNC(C2)=0,TEXT(TRUNC(C2),DBNum2G/通用格式)&元)E2=IF(TRUNC(C2*10)-TRUNC(C2*10,-1)=0,TEXT(TRUNC(C2*10)-TRUNC(C2*10,-1),DBNum2G/通用格式 )&角)F2=IF(TRUNC(C2*100)-TRUNC(C2*100,-1)=0,TEXT(TRUNC(C2*100)-TRUNC(C2*100,-1),DBNum2G/通用 格式)&分)在G2单元格使用公G2=IF(AND(D2,F2,E2=),D2&零&F2,IF(AND(D2,E2=,F2=),D2&整 ,D2&E2&F2)通过对元、角、分位是否为零的判断，将元、角、分按照中文大写金额的书写规律进行合并，得到所需规范的中文大写金额。使用自动填充柄功能将D3至G11单元格公式设置好，得到如下效果图。至此我们完成了应收利息由小 写金额向大写金额的转换工作，将文件存盘后退出，进入一步工作。三、在EXCEL中“随叫随到”Excel中在执行很多命令前，都必需先选定目的工作区域，该工作区域可能是一个单元格，也可能是数个连续或不连续的单元格或甚至是整个工作表。而大部份用户的习惯则是每次要用时直接用鼠标去选择。但是当你的目标区域不在当前屏幕上，或工作区域很大，选择时得翻滚屏幕，这时，如果你时间很紧张，而鼠标又不够灵活的话，你可能会气得要砸掉鼠标。而如果你需对该区域进行反复选定以执行不同的操作时，那更是忍无可忍。要解决上述问题，其实很简单，便是利用Excel的命名功能。你只需选择一次，以后只要在 “名称框”中选择其名称，计算机便会自动为你选择该区域。定义名称的步骤很简单：1.选择好你要的目的工作区域（即涂黑。如要选择不连续的区域，请按住“Ctrl”键。）2.用鼠标点一下 “名称框”，让光标进入该输入框，然后输入任何你想用的名称后按 “Enter”键。参见图中的红线区。注意：不要使用鼠标单击其他地方的方法来代替“Enter”键，否则系统不予认可。这样，命名工作便完成了。以后，当你需要使用该区域时，只要轻松点击 “名称框”右边的黑色小三角，然后在显示的清单中选择相应的名称，该区域便由计算机自动选择好，不用你再去辛苦的翻滚屏幕了。另外，你如果在该区域中插入新的栏或列，该名称所指定的区域也会作相应的扩大，不用你操心。在一个工作簿中，随你喜欢，你可加入多个名称，其数量的多少只受可用内存的限制。而当你想取消某个名称时，可选择菜单“插入”-“名称”-“定义”，系统弹出“定义名称”对话框。参见下图。该对话框其实是一个Excel 名称管理窗口，不但可删除己有名称，也可增加新的名称。在该窗口左边清单中，选择好欲删除的名称后，按右边的删除按钮便可将其删除。删除名称不会删除该名称所代表的工作区域中的数据。要在该窗口中增加名称，只要先在左上角的名称输入框中输入想要的名称，然后点击右下角的红色箭咀按钮，这时整个窗口缩小成一行，方便你对工作表进行区域选择，选好后， 再点击红色箭咀按钮，回到图所示窗口（这两步倒过来做也可以），最后按“添加”按钮即可。四、奇怪的Excel问题问题的提出：一系统用户提出在Excel中有部分数字没有“自动求和”功能，而且状态栏上没有“自动计算”的结果，这些均是带有小数点和千位分隔符的数据（如图1所示），但是，没有小数点和千位分隔符的数据却有“自动求和”和“自动计算”功能（如图2所示），此文件不是由用户建立的Excel文件，而是由我们自己的一套ERP管理系统打印输出的文件，其扩展名是prn，在使用之前已经将其转换为excel文件了。图 1图 2问题的解决：1. 通过平时使用Excel的经验，笔者首先想到的是应该检查需要求和的单元格的格式是否正确，经过查证以上图1中数字单元格的格式是“常规”形式，据笔者所知“常规”格式的单元格对数据的求和没有什么影响。所以问题不应出在这个地方。2. 既然数据单元格的格式没有问题，接下来就应该查看一下状态栏上是否设有“自动计算”的求和功能，具体的做法是：在状态栏上单击鼠标右键看看是否将“求和”功能选中（如图3所示），经查看状态栏上有“自动计算”的求和功能。所以问题也不是出在这个地方。图 33.这时笔者回过头来仔细研究了一下用户所提到的问题情况，发现问题很可能出在小数点和千位分隔符上，因为只有带有小数点和千位分隔符的单元格没有“自动求和”和“自动计算”功能，而没有小数点和千位分隔符的单元格却有“自动求和”和“自动计算”功能，接着笔者想将部分带有小数点和千位分隔符的单元格的千位分隔符去掉，结果发现无法去掉这些数据的千位分隔符，在此过程中笔者突然发现一个很奇特的现象（现象如图4）：小数点跑到千位分隔符的前面去了（在设置单元格格式时正常情况应该是小数点在千位分隔符的后面），也就是说小数点的位置和千位分隔符的位置颠倒了。这时笔者长长地出了一口气，问题原来在这里呀！接下来应该是查找小数点和千位分隔符的设置方法了。图 44. 既然问题的具体“病症”已经“确诊”，那么接下来的工作就是“对症下药”了，在Excel根本找不到设置小数点和千位分隔符的地方，根据笔者的经验要在控制面版的区域设置中更改，果不其然，在区域设置的数字选项中小数点的设置符号是“,”，而数字分组符号是“.”，原来有人将小数点符号和数字分组符号设置倒了，于是急忙将错误的设置改过来。5. 最后的一步就是重新启动Excel，再将此文件打开，于是以前的“自动求和”和“自动计算”功能又重新展现在我们面前了。正确的显示效果如图5。 希望各位朋友能与我一同探讨关于“computer”在使用中的一些问题和经验！图 5五、让Excel的格式自动变化作为会计人员，经常要对一些金额进行控制。比如预算、开支等。但怎么让表格自动计算并提醒你，那些项目快要超出预算或己经超支呢？或是，你经常要交总结材料给老板，对于表格上的一些项目，需突出显示它们的区别，以便引起老板注意，你是否每次都是不辞劳苦的对些单元格格式进行反复设置？其实，利用Excel的条件格式，可让你一劳永逸。请看下面的一个例子（图一）。图一在图一中，笔者的目地是要对每天的开支进行控制。于是用SUMIF函数对在另一张工作表中的日常开支明细进行汇总。对于汇总后的结果，笔者利用条件格式，对每天不同的开支数量用不同的格式显示，如果日开支等于0，则将字符颜色设成跟底色一样，以免看到0；如开支小于50元，用缺省格式，如果在50-100之间，则用黄底红字，如超过100元以上，则用红底黑字显示，以示警告。（具体的设置情况如图二。）图二这样，电脑根据不同的汇总结果，便显示不同的效果，笔者一眼看上去，便知哪天又严重超支了。（虽然如此，笔者仍是无法降低开支，该花的还是要花 *o*）。条件格式是支持拖拉填充的，所以，笔者只要设好B1单元格的公式及条件格式，然后往下一拖即成。简单快速。条件格式的设置方法为：1. 选中要设置条件格式的单元格；2. 选择菜单“格式”/“条件格式”，系统弹出“条件格式”设置对话框。见下图三。在缺省情况下，对话框只显示一种情况，如大家要求对多种情况进行判断并作相应格式设置，可按“添加”按钮。最多可有三种逻辑判断（参见图二），因为还存在一种以上三种情况都不是的情形，所以，真正来说，它可以设置四种状态下的格式。如上面的例子中，开支在0-50元的情况便是系统需进行的第四种判断；图三3. 系统预设为对该单元格的值进行判断，根据实际情况可选择大于、介于、小于等，然后在其后面的输入框中输入条件。在条件输入框中可以直接内容也可以输入单元格名称参照，以让系统对该单元格的实际内容进行比较。另外，点击“单元格数值”可选择判断条件为公式。当选择条件为公式时，其后的逻辑判断框变成公式输入框。请注意：在输入公式时，请用“=”号开头。对于公式，系统按公式返回的值是否为“真”进行逻辑判断。4. 设好条件后，按“格式”按钮，便进入格式设置画面。（参见图四）根据自己的喜好，选择相应的格式。然后按“确定”返回。这时可在对话框中间看到格式预览。图四5. 依次设置其他条件格式，最后按“确定”返回编辑窗口，条件格式设置完毕。这时你在该单元格输入不同的内容，其格式便会随着你定义的条件而自动变化。6. 大家可利用格式刷将条件格式快速套用到其它地方。条件格式的删除如需要对己设置的条件格式进行删除，可用下面的方法进行删除：1. 选择要删除条件格式的单元格；2. 选择菜单“格式”/“条件格式”，在系统弹出“条件格式”设置对话框中按“删除”按钮。或者1. 选择没有设置条件格式的单元格；2. 按下工具列的格式刷，对要删除条件格式的单元格进行格式复制。如要删除工作表中全部或部分相同条件格式，可先按“Ctrl+G”，之后在对话框中按“定位条件”，系统显示定位条件对话框（参见图五），在该框中，选择“条件格式”，之后，根据需要选择“全部”或“相同”，再按“确定”，回到编辑窗口，这时，所有设置了条件格式的单元格都被选中，再按上面所讲的第一种删除方法删除即可。图五六、了解Excel公式的错误值经常用Excel的朋友可能都会遇到一些莫名奇妙的错误值信息：# N/A！、#value！、#DIV/O！等等，出现这些错误的原因有很多种，如果公式不能计算正确结果，Excel将显示一个错误值，例如，在需要数字的公式中使用文本、删除了被公式引用的单元格，或者使用了宽度不足以显示结果的单元格。以下是几种常见的错误及其解决方法。 1#！ 原因：如果单元格所含的数字、日期或时间比单元格宽，或者单元格的日期时间公式产生了一个负值，就会产生#！错误。 解决方法：如果单元格所含的数字、日期或时间比单元格宽，可以通过拖动列表之间的宽度来修改列宽。如果使用的是1900年的日期系统，那么Excel中的日期和时间必须为正值，用较早的日期或者时间值减去较晚的日期或者时间值就会导致#！错误。如果公式正确，也可以将单元格的格式改为非日期和时间型来显示该值。 2#value! 当使用错误的参数或运算对象类型时，或者当公式自动更正功能不能更正公式时，将产生错误值#value!。 原因一：在需要数字或逻辑值时输入了文本，Excel不能将文本转换为正确的数据类型。 解决方法：确认公式或函数所需的运算符或参数正确，并且公式引用的单元格中包含有效的数值。例如：如果单元格A1包含一个数字，单元格A2包含文本学籍，则公式=A1+A2将返回错误值#value!。可以用SUM工作表函数将这两个值相加（SUM函数忽略文本）：=SUM（A1:A2）。 原因二：将单元格引用、公式或函数作为数组常量输入。 解决方法：确认数组常量不是单元格引用、公式或函数。 原因三：赋予需要单一数值的运算符或函数一个数值区域。 解决方法：将数值区域改为单一数值。修改数值区域，使其包含公式所在的数据行或列。 3#DIV/O! 当公式被零除时，将会产生错误值#DIV/O!。 原因一：在公式中，除数使用了指向空单元格或包含零值单元格的单元格引用（在Excel中如果运算对象是空白单元格，Excel将此空值当作零值）。 解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。 原因二：输入的公式中包含明显的除数零，例如：=5/0。 解决方法：将零改为非零值。 4#NAME? 在公式中使用了Excel不能识别的文本时将产生错误值#NAME?。 原因一：删除了公式中使用的名称，或者使用了不存在的名称。 解决方法：确认使用的名称确实存在。选择菜单插入|名称|定义命令，如果所需名称没有被列出，请使用定义命令添加相应的名称。 原因二：名称的拼写错误。 解决方法：修改拼写错误的名称。 原因三：在公式中使用标志。 解决方法：选择菜单中工具|选项命令，打开选项对话框，然后单击重新计算标签，在工作薄选项下，选中接受公式标志复选框。 原因四：在公式中输入文本时没有使用双引号。 解决方法：Excel将其解释为名称，而不理会用户准备将其用作文本的想法，将公式中的文本括在双引号中。例如：下面的公式将一段文本总计：和单元格B50中的数值合并在一起：=总计：&B50 原因五：在区域的引用中缺少冒号。 解决方法：确认公式中，使用的所有区域引用都使用冒号。例如：SUM（A2:B34）。 5#N/A 原因：当在函数或公式中没有可用数值时，将产生错误值#N/A。 解决方法：如果工作表中某些单元格暂时没有数值，请在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。 6#REF! 当单元格引用无效时将产生错误值#REF！。 原因：删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。 解决方法：更改公式或者在删除或粘贴单元格之后，立即单击撤消按钮，以恢复工作表中的单元格。 7#NUM！ 当公式或函数中某个数字有问题时将产生错误值#NUM！。 原因一：在需要数字参数的函数中使用了不能接受的参数。 解决方法：确认函数中使用的参数类型正确无误。 原因二：使用了迭代计算的工作表函数，例如：IRR或RATE，并且函数不能产生有效的结果。 解决方法：为工作表函数使用不同的初始值。 原因三：由公式产生的数字太大或太小，Excel不能表示。 解决方法：修改公式，使其结果在有效数字范围之间。 8#NULL！ 当试图为两个并不相交的区域指定交叉点时将产生错误值#NULL！。 原因：使用了不正确的区域运算符或不正确的单元格引用。 解决方法：如果要引用两个不相交的区域，请使用联合运算符逗号（，）。公式要对两个区域求和，请确认在引用这两个区域时，使用逗号。如：SUM（A1:A13，D12:D23）。如果没有使用逗号，Excel将试图对同时属于两个区域的单元格求和，但是由于A1:A13和D12:D23并不相交，所以他们没有共同的单元格。七、你的复制“所见即所得”吗？在Excel 2000 中，出于某些目的，如果你对工作表的部份栏或列予以隐藏后，然后对工作表正常显示的内容进行复制贴上时，你会发现，隐藏的内容也同时被复制进来。或是你使用了“分类汇总”功能后，在将汇总结果复制到另一张工作表或Word中时，就会发现，表面上你复制的是显示的那几行汇总项目，而贴上的结果却包括了汇总与汇总之间的详细项目，非常烦人。一般用户的解决办法是：在选择要复制的区域时，利用我 “CTRL”加鼠标一行一行甚至一个单元格一个单元选好，再复制、贴上，这样就可避免那些不需要贴上的单元格内容被贴进来。有的用户则是先直接复制，再贴上，然后再删掉不要的项目，不但浪费时间，更失去了工作的乐趣。其实，Excel 2000 提供了一个功能，可以很方便的让你的复制做到“所见即所得”。步骤如下：1、选择要复制的区域；2、选择菜单“编辑”/“定位”或直接按“CTRL+G”快捷键，计算机显示“定位”对话框。见图1。图13、按“定位条件”按钮。计算机显示“定位条件”对话框。选择“可见单元格”。见图2中红线区。图24、按确定后回到工作表窗口。5、在选定区域按鼠标右键，选择“复制”或直接按“CTRL+C”快捷键。这是你会发现，计算机显示的画面与普通复制不一样，有很多个流动框，表示处在多重选择下。6、点击目地区域的左上角储存格，按鼠标右键，选择“贴上”或直接按“CTRL+V”快捷键。7、你会发现计算机己按你的要求贴上了你要的项目，干净利落八、用Excel函数自动排名次 笔者认为无论对原数据清单进行排序或筛选最好不要破坏原清单的原貌。EXCEL的函数十分丰富，不用宏，用函数也能解决数值自动排名。方法如下。 如第一行为表头，A列(例如A2:A101，下同)为姓名，B列数据，在C2单元格输入公式“=IF(A2=0,0,INT(CONCATENATE(INT(B2),200-ROW(A1)”。公式中ROW(A1)为A1单元格所在的行数即为1，(该公式下拉时依次为2、3、4.),用200来减是为了CONCATENATE函数中的第2个参数保持3位数，CONCATENATE函数是一个拼合函数这里把B列的数据和它所在的行数拼合成一个数据。这样在对它进行排序后该数据包含了它所在行数的信息。CONCATENATE函数INT函数套用是为了把原来的文本变为数字。 在D2单元格输入公式“=LARGE(C:C,ROW(A1)”即对B列数值(包含所在行的信息)按大小排列。 在F2单元格(为了与原始清单分开中间空了一列)输入公式“=IF(D2=0,0,200-RIGHT(D2,3)”，函数 RIGHT(D2,3)即为D2单元格数据的后3位数，用200来减即为此数据所在的行数。 在G2单元格输入公式“=IF($F2=0,0,INDEX($A$2:$B$15,$F2,COLUMN(A1)”，并拖到H2单元格。INDEX函数为引用函数，即根据F2单元格所标明的行数在$A$2:$B$15单元格矩阵中引用姓名及得分。 在I2单元格输入公式“=IF(H2=0,0,IF(H2=H1,I1,ROW(A1)”，本来G、H列就是按得分大小排列的，但可能有平列名次，所以选用上述公式。 最后把C2到I2单元格的公式下拉，程序就完成了。 点击此处下载示范工作簿九、重名检索与姓氏频率统计人数较多(例如500人左右)的机关、团体、单位的人事管理部门，或者户籍管理部门，都会遇到重名的问题。例如笔者所在单位783人就有12人6对重名。在用EXCEL电子表格制作各类管理文件时重名会带来很多问题(例如以姓名作参数用VLOOKUP函数，来查找该人的信息时就会出错)。因此有一个方便快速的重名检索办法就十分必要。(笔者根据经验建议凡用EXCEL电子表格进行办公业务自动化管理的单位，应给每个人设立一个代码，像居民身份证号码一样是终身的唯一的，不要把调离、退休等人员的代码用于新增人员)。方法如下：先制作空表格：把本文所附的只有2行的表头打开，下拉菜单“编辑”、点击“定位”在引用位置栏输入“B2:H1001”、按“确定”、再下拉菜单“编辑”、点击“填充”、“向下填充”空表格制作完成。(这是大量填充单元格的最快方法)。然后把姓名清单从A2单元格开始拷贝至A列。这样检索程序操作就完成了。用该检索程序，在奔III 733机器上1秒钟内便完成了783人的重名检索。下面简单介绍B2至H2单元格的公式，B2单元格“=IF(A2=0,0,SUBSTITUTE(A2, ,)”中SUBSTITUTE函数是去掉A2单元格中的名字的前、后、中间的空格，C2单格“=IF(B2=0,0,IF(ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE)=TRUE,0,ROW(A1)”中ISERROR(VLOOKUP(B2,B3:B$1001,1,FALSE)=TRUE,0,ROW(A1)即如在B3到B1001单元格中找不到与B2相同的姓名时为零，否则为从第1个姓名开始计数的行数。意即B3往下有重名时标明行数，否则为零。D2单元格“=LARGE(C:C,ROW(A1)”就是把重名所在行的行数从大到小进行排列。E2单格“=IF(D2=0,0,INDEX(B$2:B$1001,D2)”就是在B列根据D2单元格标明的行数查找重名的姓名。F2单元格“=IF(E2=0,0,IF(ISERROR(VLOOKUP(E2,E3:E$1001,1,FALSE)=TRUE, ROW(A1),0)”与C2单元格的公式相似，只是根据条件取舍相反。即让已检出的重名只出现一次。G2单元格的式“=IF(ROW(A1)COUNTIF(F:F,0),0,INDEX(E$2:E$1001,LARGE(F:F,ROW(A1)” 就是对F列标明的行数，按大到小进行排列并在E列查找重名的姓名。H2单元格的式“=IF(G2=0,0,COUNTIF(B:B,G2)”就是对B列在G列列出的重名进行计数。下表为工作表的前三行。此程序稍作改变便能用来统计姓氏的频数与频率。方法如下：先制作空表格：把本文所附的只有4行的表头打开，用上述方法填充B4:l1002单元格.再把姓名清单从A3单元格开始拷贝至A列。这样姓氏的频数与频率统计程序操作就完成了。下表为工作表的前五行。用该程序对笔者所在单位783人统计有160个姓氏，张姓最多有95人出现频率为12.1%。样本太少不具全国姓氏的频数与频率统计上的意义，但似乎张姓为中国第一大姓。B到F列的公式与重名检索工作表的公式极相似，G到K列的公式在笔者的“排序与筛选”一文中有详细说明。笔者在奔III 733计算机上制作一张统计10000人姓氏频数与频率的空表需时6分37秒，复制这样一张空表瞬时就能完成，在空表上填充10000人的姓名后统计姓氏频数与频率的时间为2分42秒。填充完后文件大小为4996k。笔者所以测试以上时间是笔者有一个强烈的愿望：把程序用于全国千分之一到万分之一抽样人口即12万到120万人的姓氏频数与频率的统计。笔者在此请求网友支持，提供你能到的某一群体人员的姓氏或姓名样本，和所在省市。笔者每收集到1万个样本便在网站公布一次姓氏频数与频率的统计结果。点击此处下载示范工作簿十、用Excel函数排序与筛选Execl本身具有很方便的排序与筛选功能，下拉“数据”菜单即可选择排序或筛选对数据清单进行排序或筛选。但也有不足，首先无论排序或筛选都改变了原清单的原貌，特别是清单的数据从其它工作表链接来而源数据发生变化时，或清单录入新记录时必须从新进行排序或筛选。其次还有局限，例如排序只能最多对三个关键字(三列数据)排序，筛选对同一列数据可用“与”、或“或”条件筛选，但对不同列数据只能用“与”条件筛选。例如对某张职工花名册工作簿，要求筛选出年龄大于25岁且小于50岁或年龄大于50岁或小于25岁都是可行的，如同时要求性别是男的或女的也是可行的。但要求筛选出女的年龄在22岁到45岁，男的年龄在25岁到50岁时Execl本身具有的筛选功能则无能为力了。再者排序与筛选不能结合使用，即不能在排序时根据条件筛选出来的记录进行排序。例如有一张职工资料清单，其中有的职工已经退休，对在职职工的年龄进行排序时无法剔除已退休职工的数据。本文试图用Execl的函数来解决上述问题。一、用函数实现排序题目如有一张工资表，A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为津贴、D1为奖金、E1为工资、F1收入合计。现要求对职工收入从多到少排序，且在职工总收入相同时再按工资从多到少排序，在职工总收入和工资相同时再按奖金从多到少排序，在职工职工总收入和工资、奖金相同时再按津贴从多到少排序。方法G1单元格填入公式“=if(F2=0,10100,INT(CONCATENATE(999-f2,999-e2,999-d2,999-c2)”，CONCATENATE是一个拼合函数，可以把30个以下的单元的数据拼合成一个数据，这些被拼合的数据之间用逗号分开。用f2、e2等被拼合的数据用999来减，是为了使它们位数相同。(假定任何一个职工的总收入少于899元)。被拼合成的函数是文本函数，CONCATENATE与INT函数套用是为了使文本转换为数字。最外层的if函数是排序时用来剔除不进行排序的记录，在本例中指收入为零的记录。(在上文提到的职工年龄排序，则公式改为“if(f2=退休,10100,.)”,即剔除了退休职工。)第二步把G1单元格的公式拖放到G500单元格(最简便的方法是点击G1单元格后向G1单元格右下方移动鼠标，见到黑十时双击鼠标就完成了G1到G500的填充)。第三步在在H2单元填入公式“=MATCH(SMALL(G:G,ROW(A1),G:G,0)”与第二步一样拖放到H501单元格。此公式实际上是把三列公式合成一列公式，ROW(A1)即为A1的行数是1，随着向下拖放依次为2、3、4.,SMALL(G:G,ROW(A1)为G列中最小的数随着向下拖放依次为第2、第3、.小的数，MATCH(SMALL(G:G,ROW(A1),G:G,0)即为G列各行的数据中最小、第2、第3小等的数据在第几行。第四步把A1至F1单元格的表头复制到I1至N1单元格，在I2单元格输入公式“=INDEX($A$2:$F$501,$H2,COLUMN(A$1)”INDEX函数是一个引用函数，即把$A$2:$F$501单元格列阵第$H2行第COLUMN(A$1)列的数据放入I2单元格。然后把I2单元格的公式拖放到N2单元格，点击N2单元格后向N2单元格右下方移动鼠标见到黑十时双击鼠标就完成了I2到N501单元格的填充到此全部完成。以上叙述看似繁杂实际非常简单，只要把A1至F1的表头复制到I1至N1单元格，再分别在G1、H2、I2单元格输入公式然后向下拖放，即使对EXCEL应用不熟练的同志一分锺内便能完成。对上述程序稍作变化还可得到更多用度。上面例子数据是从大到小排列的，如H列的函数中的SMALL改为LARGE，上面例子数据就从小到大排列了。如H2单元格的公式为“=IF(O1=1,MATCH(SMALL(G:G,ROW(A1),G:G,0),MATCH(LARGE(G:G,ROW(A1),G:G,0)”并把H2单元格的公式向下拖放。这样在O1单元格输入1上面例子数据是从大到小排列的，O1单元格输入1以外的数上面例子数据就从小到大排列了。如在H列前插入若干列，如插入一列，则现在的H列输入类似G列的公式，例如“=if(F2=0,10100,d2)”,现在的I列的公式为“=IF(P1=1,MATCH(SMALL(G:G,ROW(A1),G:G,0),MATCH(SMALL(H:H,ROW(A1),H:H,0)”即在P单元格输入1以外的值就实现了按奖金大小排序.这样只要通过改变P1(原来的O1单元格)单元格内容的改变就能立即得到按不同要求的排序。二、用函数实现筛选题目如有一张职工名册表，A2:F501,共6列500行3000个单元格。表头A1为姓名代码(1至500)、B1为姓名、C1为性别、D1为年龄、E1为学历、F1职称。现要求对职工的性别、年龄、学历、职称进行交错筛选，例如要求在同一张表上筛选出1、女的年龄在22岁到45岁，男的年龄在25岁到50岁，2、女博士，3、男博士后。方法第一步在G2单元格输入公式”=IF(OR(AND(C2=女,D2=22,D2=25,D2第二步在K1单元格输文字”筛选选择”，A1到F1表头复制到L1到Q1，在L2单元格输入公式“=IF($J2=0,0,INDEX($A$2:$F$501,$J2,COLUMN(A$1)”，然后向右拖放到Q2，再向下拖放。INDEX函数的含义上文已说明。第三步在P1单元格输入1或2或3便可实现上述三种筛选。点击此处下载示范工作簿十一、用Excel统计2000年中国甲A联赛2000年中国甲A联赛程序由4个工资簿组成。第1个工作簿足球2001.xls由9张工作表组成。每个球队有一个代码，在代码表上可查到。本工作簿只需每轮比赛后在比分表上录入各球队间的比分，其他各表的统计数据都是自动生成的。在各轮排名表、主客场统计表、胜负表和队间比表上还有用红色底色标明的可选项，在这些单元格可输入你所关注的球队的代码来查询有关该球队的统计数据。第2个工作簿足球2002.xls是用来统计计算各球队及队员进球及进球时间。本工作表需在每轮比赛后在C列录入轮次、D列录入球队代码、E列录入队员号码、某队获得乌龙球时其球员号填入40，I、J列分别录入所进球在上下半场的时间。这些数据在每轮赛后由中国足协网/发布。录入上述数据后便可通过射手榜、总射手榜、进球时间分布和乌龙球等4张表查阅各类统计数据。由于作者录入的数据与中国足协发布的统计资料可能有差别，因此本工作簿只供球迷参考。第3个工作簿足球2003.xls是对下一轮各对阵球队的比分进行预测，前5轮是根据该两队上一年主客场的得分能力、第6轮开始根据前5轮主客场的得分能力来预测，没有考虑其他各类因素因此准确率不高，作者用此程序对982000年预测的比分准确率都为14，胜平负预测准确率为4550之间。第4个工作簿足球2004.xls 是用来统计计算各球队及队员红黄牌数、处罚和停赛场次。本工作表需在每轮比赛后在C列录入轮次、D列录入球队代码、E列录入队员号码、I、J、K、L列分别录入所得黄牌、黄红牌、红牌及被处罚定赛场数。这些数据在每轮赛后由中国足协网/发布。录入上述数据后便可通过各队处罚汇总及停赛名单与场次两工作表查阅处罚与停赛的统计资料。由于作者对中国足协竞赛规则的理解不尽准确与中国足协发布的统计资料可能有差别，因此本工作簿只供球迷参考。中国足球甲A联赛从94年开始，由于作者没有9495年的技术数据因此上述所有工作簿都只有962000年的统计数据。由于本人最近才与黄花菜先生的网站有接触，因此在今年甲A联赛结束后才发布本程序。如诸位有兴趣2001年联赛赛程确定后本人再把新的程序贡献给诸位。点击此处下载2000年中国甲A联赛工作簿十二、EXCEL2000使用技巧十招1、Excel文件的加密与隐藏如果你不愿意自己的Excel文件被别人查看，那么你可以给它设置密码保护，采用在保存文件时用加密的方法就可以实现保护目的，在这里要特别注意的是，自己设定的密码一定要记住，否则自己也将被视为非法入侵者而遭拒绝进入。给文件加密的具体方法为：A、单击“文件”菜单栏中的“保存或者（另存为）”命令后，在弹出的“保存或者（另存为）”的对话框中输入文件名；B、再单击这个对话框中“工具”栏下的“常规选项”按钮，在弹出的“保存选项”的对话框中输入自己的密码；这里要注意，它提供了两层保护，如果你也设置了修改权限密码的话，那么即使文件被打开也还需要输入修改权限的密码才能修改。C、单击“确定”按钮，在弹出的密码确认窗口中重新输入一遍密码，再点击“确认”，最后点“保存” 完成文件的加密工作。当重新打开文件时就要求输入密码，如果密码不正确，文件将不能打开。2、对单元格进行读写保护单元格是Excel执行其强大的计算功能最基本的元素，对单元格的读写保护是Excel对数据进行安全管理的基础。对单元格的保护分为写保护和读保护两类，所谓写保护就是对单元格中输入信息加以限制，读保护是对单元格中已经存有信息的浏览和查看加以限制。对单元格的写保护有两种方法：A、对单元格的输入信息进行有效性检测。首先选定要进行有效性检测的单元格或单元格集合，然后从数据菜单中选择“有效数据”选项，通过设定有效条件、显示信息和错误警告，控制输入单元格的信息要符合给定的条件。B、设定单元格的锁定属性，以保护存入单元格的内容不能被改写。可分为以下步骤：（1）选定需要锁定的单元格或单元格集合；（2）从格式菜单中选择“单元格”选项；（3）在设置单元格格式的弹出菜单中选择“保护”标签，在弹出的窗口中，选中“锁定”；（4）从工具菜单中选择“保护”选项，设置保护密码，即完成了对单元格的锁定设置。对单元格的读保护有三种方法：A、通过对单元格颜色的设置进行读保护。例如：将选定单元格或单元格集合的背景颜色与字体颜色同时设为白色，这样，从表面看起来单元格中好像是没有输入任何内容，用户无法直接读出单元格中所存储的信息。B、用其他画面覆盖在需要保护的单元格之上，遮住单元格的本来面目，以达到读保护目的。例如：使用绘图工具，画一不透明矩形覆盖在单元格之上，从格式菜单中选定矩形的“锁定”选项，然后保护工作表，以保证矩形不能被随意移动。这样，用户所看到的只是矩形，而看不到单元格中所存储的内容。C、通过设置单元格的行高和列宽，隐藏选定的单元格，然后保护工作表，使用户不能直接访问被隐藏的单元格，从而起到读保护的作用。3、快速填充相同数据如果你希望在不同的单元格中输入大量相同的数据信息，那么你不必逐个单元格一个一个地输入，那样需要花费好长时间，而且还比较容易出错。我们可以通过下面的操作方法在多个相邻或不相邻的单元格中快速填充同一个数据，具体方法为：A、同时选中需要填充数据的单元格。若某些单元格不相邻，可在按住Ctrl键的同时，点击鼠标左键，逐个选中；B、输入要填充的某个数据。按住Ctrl键的同时，按回车键，则刚才选中的所有单元格同时填入该数据。4、使用Excel中的“宏”功能宏的概念，相信使用过WORD的人都会知道，她可以记录命令和过程,然后将这些命令和过程赋值到一个组合键或工具栏的按钮上,当按下组合键时,计算机就会重复所记录的操作。在实践工作中，它可以代替经常输入大量重复而又琐碎的数据，具体宏的定义方法如下：:A、打开工作表，在工作表中选中要进行操作的单元格；B、用鼠标单击菜单栏中的“工具”菜单项，并从弹出的下拉菜单中选择“宏”子菜单项，并从随后弹出的下级菜单中选择“录制新宏”命令；C、设定好宏后，我们就可以对指定的单元格，进行各种操作，程序将自动对所进行的各方面操作记录复制。5、连续两次选定单元格我们有时需要在某个单元格内连续输入多个数值，以查看引用此单元格的其他单元格的效果。但每次输入一个值后按回车键，活动单元格均默认下移一个单元格，非常不便。解决此问题的一般做法是通过选择“工具”“选项”“编辑”，取消“按回车键后移动”选项的选定来实现在同一单元格内输入许多数值，但以后你还得将此选项选定，显得比较麻烦。其实，采用连续两次选定单元格方法就显得灵活、方便：单击鼠标选定单元格，然后按住Ctrl键再次单击鼠标选定此单元格。此时，单元格周围将出现实线框。6、在工作表之间使用超级连接首先须要在被引用的其他工作表中相应的部分插入书签，然后在引用工作表中插入超级链接，注意在插入超级链接时，可以先在“插入超级链接”对话框的“链接到文件或URL”设置栏中输入目标工作表的路径和名称，再在“文件中有名称的位置”设置栏中输入相应的书签名，也可以通过“浏览”方式选择。完成上述操作之后，一旦使用鼠标左键单击工作表中带有下划线的文本的任意位置，即可实现中文Excel2000在自动打开目标工作表并转到相应的位置处，这一点与WORD的使用很相似。7、快速清除单元格的内容首先用鼠标选定该单元格,然后按住键盘上的Delete键，此时你会发现你只是删除了单元格内容,它的格式和批注还保留着。那么如何才能彻底清除单元格呢,请看下面的两种操作步骤：A、选定想要清除的单元格或单元格范围；B、选择“编辑”菜单中的“清除”命令,这时显示“清除”菜单；C、选择要清除的命令,可以选择“全部”、“格式”、“内容”或“批注”中的任一个。8、快速修改单元格次序在实际操作的过程中，我们有时需要快速修改单元格内容的次序。在拖放选定的一个或多个单元格至新的位置的同时，按住Shift键可以快速修改单元格内容的次序。具体方法如下：A、首先用鼠标选定单元格，同时按下键盘上的Shift键；B、接着移动鼠标