Excel技巧2

Excel技巧

1、Excel圈注表格中的无效数据：数据输入完毕后，为了保证数据的真实性，快速找到表格中的无效数据，我们可以借用Excel中的数据有效性和公式审核来实现。选中某列（如B列），单击[数据有效性]对话框，切换到[设置]选项卡，输入符合条件的数据必须满足的条件范围（如“=and(B1>=60,B1<90) ”）。点击[工具]—[审核]—[显示“审核”工具栏]，单击工具栏中的[圈释无效数据]按钮，此时表格中的无效数据都被清清楚楚地圈注出来了。
2、隐藏表格中的出错信息：大家经常会发现表格在处理完数据后出现一些类似“#NAME？”等出错信息，既不方便打印又影响了表格的整体美观，在数据量比较大的时候手工删除显然是不现实的。解决方法是：打印时，打开[页面设置]对话框，切换到[工作表]选项卡，将“错误单元格打印为”选项设置为“空白”或“-”就好了。计算时，可使用通用公式“=IF（ISERROR（公式）,””,公式）”，也能使运算过程中出错单元格填充为指定的字符或空白。
 3、两个日期之间的天数、月数和年数：计算两个日期”1995-5-12”和”2006-1-16”之间的天数、月数和年数可使用下面的公式：“=DATEDIF（A1,B1,”d”）”，其中A1和B1分别表示开始日期和结束日期，”d”表示天数，换为”m”或”y”就可以计算两个日期相差的月数和年数了。
4、Excel中限制重复数据录入：在Excel中录入数据时，有时会要求某列单元格中的数据具有唯一性，例如身份证号码、发票号码之类的数据。为了保证数据的唯一性，我们可以这样做：选定目标单元格区域（这里假设为A1：A10），依次单击[数据]—[有效性]，打开[数据有效性]对话框，在[设置]选项卡中单击[允许]下拉列表，选择[自定义]，然后在[公式]中输入“=COUNTIF($H$1:$H$10,$H1)=1”，接着，单击切换到[出错警告]选项卡，在[样式]中选择[停止]，然后分别在[标题]和[错误信息]中输入错误提示标题和信息。设置完毕后单击[确定]退出。此时，我们再在目标单元格录入数据时，Excel就会自动对数据的唯一性进行校验。当出现重复数据时，Excel中会出现前面设置的错误提示信息。
5、Excel录入时自动切换输入法：在Excel单元格中，经常遇到中英文交替输入的情况，如A列输入中文而B列却输入英文，这时就要在中英文输入法之间反复切换，这样非常麻烦而且严重影响录入效率。其实可以先打开中文输入法，选中需要输入中文的列，执行菜单[数据]—[有效性]，在[数据有效性]中切换到[输入法模式]标签分页，在[模式]下拉列表中选择[打开]，确定退出。接着选择需要输入英文的列，同样打开[输入法模式]标签分页，在[模式]下拉列表中选择[关闭（英文模式）]，确定后退出即可。
6、Excel中粘贴时避免覆盖原有内容：在工作表中进行复制或移动操作时，粘贴的内容将自动覆盖工作表中的原有内容，怎样避免这一现象呢？首先选中要复制或移动的单元格，单击复制或剪切按钮，选中要粘贴的起始单元格，按下“Ctrl+Shift+ +”组合键，在弹出的“插入粘贴”对话框中选择活动单元格移动的方向，单击“确定”按钮就可以了。另外如果按下“Ctrl+Shift+ —”组合键即可完全删除Excel中的单元格。
7、Excel中排出并列的名次：Excel中排一个顺序递增1的名次是很简单的，按关键字排序即可；在名次列的第一个单元格内输入1，第二个单元格内输入2；选中这两个单元格，在选框右下角的填充柄上双击即可。但这种名次，当有数据并列时就不合理了。下面我们以某班学生总分名次为例（存在大量总分相同的学生），介绍一下我平时用的排名次方法：1）按总分“降序”排序；2）在“总分”字段右侧添加“名次”字段，在“名次”字段第1个单元格即D2中输入1，在第2个单元格即D3中输入下列公式：=IF(EXACT(C2,C3),D2,ROW(C3)-1)，回车确认，剩下的单元格用D3单元自动填充即可；3）选中“名次”列，复制，然后单击菜单中的[编辑]—[选择性粘贴]，选中[数值]项后[确定]，至此大功告成。

8、Excel中妙用“条件格式”让行列更清晰：在用Excel处理一些含有大量数据的表格时，经常会出现错行错列的情况，其实可以将行或列间隔设置成不同格式，这样看起来就清晰明了得多。利用[条件格式]可以轻松地达到目的。先选中数据区中所有单元格，然后选择菜单命令[格式]—[条件格式]，在打开的对话框中，在最左侧下拉列表中单击[公式]，然后在其右侧的输入栏中输入公式“=MOD(ROW(),2)”，然后单击下方的[格式]按钮，在弹出的[单元格格式]对话框中单击[图案]选项卡，为单元格加上外边框，然后一路单击[确定]按钮即可。如果希望第一行不添加任何颜色，只须将公式改为“=MOD(ROW()+1,2)”即可。如果需要间隔两行添加填充颜色只须将公式改为“=MOD(ROW(),3)”就可以，以此类推。如果我们希望让列间隔添加颜色，那么只须将上述公式中的“ROW”改为“COLUMN”就可以达到目的。 　

9、Excel中单变量求解：单变量求解的意义是：已知公式的值，求产生此值的自变量值。界面非常简单，实际上单变量求解的功能本质就是用于求解一元方程的自变量X值。明白了这个道理，只要是一元方程的问题的问题就都可以套用，无论是一元一次还一元多次方程都可以解决。选择[工具]—[单变量求解]可以打开如图1所示的窗口，其中，[目标单元格]指的是公式（方程）所在的单元格，[目标值]指的是方程的值，[可变单元格]指的是方程的自变量X所在的单元格。例1：求解方程：X2+2X+1=9。由图2可以看出方程输入在B6单元，X变量放在B5单元，这样方程实际转变为B5*B5+2*B5+1=9，得出图3所示单变量求解窗口中所选择的。通过上面例子可以看出单变量求解的确可以求解一元方程自变量X的值。下面再通过一个实际例子把它转化为一元方程，然后利用单变量求解来解决。例2：计算机考试分为笔试和上机两部分，都是百分制。其中笔试80分，占总分的60%，若总分要达到90分以上，上机须多少分？总分是否能达到此要求？设上机成绩为X，方程为：80*60%+X*（1-60%）=90。根据图4各数据所在的单元格地址，方程转化为B4*B5+B6*（1-B5）=90。根据上述经验，图4的公式中没有具体的数值。如果笔试成绩或笔试比率任何一个参数发生变化，公式的值都会相应地改变。其中方程的自变量X在B6单元格，方程输入在B7单元格，目标值是90分，从而得出如图5所示的单变量求解窗口。综上所述，单变量求解的本质是求解一元方程，所以现实应用中凡是可以转换为一元方程的问题都可以用单变量求解解决。

10、Excel中模拟运算：在Excel中模拟运算分为单变量模拟运算和双变量模拟运算，模拟运算功能是指公式中一个或两个变量变化时对公式值的影响。功能描述看似简单，但是如何能把实际问题利用模拟运算解决呢？单变量模拟运算的功能本质是解决一元方程中自变量X在给定的取值集合中变化时，方程值的变化。双变量模拟运算是解决二元方程的自变量X、Y在给定的取值集合中变化时，方程值的变化。这样可以找到一个最佳方案。所以模拟运算仍然是求解方程问题，在使用模拟运算功能时模拟运算表的正确构造是关键。下面通过举例来解释：例1：计算机考试，分为笔试、上机两部分，都是百分制。其中上机80分，笔试90分，上机占总分比率可以分为：20%、30%、40%、50%、60%、70%、80%时，Z的值分别是多少？设上机比率为X，总分为Z，方程是：Z=80*X+90（1-X）。这样问题就转化为求当自变量X取值分别为20%、30%、40%、50%、60%、70%、80%时，Z的值分别是多少？如图6所示，公式输入在C4单元，公式在左侧B4单元表示自变量X，并在B4单元正下方的单元中输入了自变量X的取值集合，将会在C4正下方产生相应的方程的值。至于为什么这么安排，是按照Excel有关模拟运算表以及公式和求解结果在内的连续二维表。屏幕中的其他文字是为了表格的可读性而输入。方程转化为C4=B1*B4+B2*（1-B4）。通过这样问题的转化就非常清晰，否则在使用模拟运算时，许多人会无从下手。模拟运算操作窗口的两个选项不好理解。在模拟运算构造完毕后，选中包括自变量X和其取值集合以及方程和求解结果所在的矩形单元格区间，也就是选择图6中B4：C11这一矩形区域，然后再调用模拟运算功能。因为模拟运算表是列方向的，而横方向是空值，所以产生如图7所示的模拟运算表窗口。同样道理，双变量模拟运算如果能把问题转化为二元方程也就迎刃而解了。例2：上机都是80分的同学，笔试分数分别为：95、90、85、80、75、70。如果上机占总分比率分别为：20%、30%、40%、50%、60%、70%、80%，求解各个同学选用哪个比率总分最高？设笔试分数为X，上机成绩占总分比率为Y，总分为Z，则方程为：Z=X*（1-Y）+80*Y。这是一个典型的二元方程问题。由图8可以看出公式输入在B4单元，B4单元的正下方输入第一个变量的取值集合，B4单元的正右侧输入另一个变量的取值集合，至此双变量模拟运算表构造完毕。A5单元表示自变量X，C3单元格表示自变量Y，在双变量模拟运算中X、Y变量的代表单元格除模拟运算表数据单元格外可以任意选定，所以选择哪个单元格表示X、Y没有特殊规定，在此选择这两个单元格的原因是它们在输入的可读性文字旁边便于记忆。由此方程转换为B4=A5*（1-C3）+B1*C3，其中A5的值在运算中分别被C4～H4的值所取代，C3的值在运算中将分别被B5～B11的值所取代，因为C4～H4在模拟运算表中的同一行，B5～B11在模拟运算表中的同一列，所以在模拟运算表操作窗口中的选择如图9表示。1）选择B4：B11所在的矩形区域；2）单击[数据]—[模拟运算表]，弹出如图9所示；3）因为A5的值在运算中分别被C4～H4的值所取代，所以在输入引用行的单元格中输入$A$5；因为C3的值在运算中分别被B5～B11的值所取代，所以在输入引用列的单元格中输入$C$3。

11、用Excel打造单词默写本：在Excel中有个Exact函数，可以比较字符串是否完全相同（能够区分大小写），如果完全相同就显示为True,否则显示为False，我们完全可以用它来做一个单词默写本来帮我们背单词。1）运行Excel，分别在A1、B1、C1、D1单元格输入“单词”、“词义”、“拼写”、“结果”；2）在“单词”和“词义”两列中，输入你要默写的单词和相应单词的词义；3）选中D2单元格，点击“插入”菜单下的“函数”命令，在弹出的对话框中选择“文本”序列中的Exact函数，点击“确定”按钮。然后在打开的Exact函数对话框中，分别在Text1和Text2中输入A2、C2，表明此函数用于比较A2单元格与C2单元格中的字符串是否完全相同，C2单元格中没有输入单词或两个单元格中的字符串不相同时，D2单元格的值是False；如果C2单元格与A2单元格中的单词完全相同（包括字母大小写），D2单元格的值为True；4）选中D2单元格，拖动填充柄，填充D列以下的单元格；5）选中A列，右键单击，选择“隐藏”命令将A列隐藏。至此，单词默写本就算做好了，如果你想为默写本添加新的单词，只要点击A、B两列中间位置，选择“取消隐藏”，重复第二步和第四步骤操作即可。还等什么，还不动手做一个单词默写本在暑假里抓紧时间背单词。
12、显示星号原来如此简单：在电视抽奖时，屏幕上显示的通常是隐藏了中间四位的手机号码。其实，只要请出CONCATENATE函数即可以方便地在Excel工作表中实现这样的效果。比如，如果在B2单元格中存储了手机号码13807480818，那么公式“=CONCATENATE(LEFT(A1,3),"****",RIGHT(A1,4)”可将138****0818显示在所需要的单元格中。以上公式用到了三个函数，其中CONCATENATE函数是将几个文本字符串合并为一个文本字符串，其语法格式是CONCATENATE（text1,text2…）,这些文本项可以为文本字符串、数字或对单个单元格的引用。LEFT函数是根据所指定的字符数返回文本字符串中最左边的一个或多个字符。RIGHT函数是根据所指定的字符数返回文本字符串中最右（后）边的一个或多个字符。虽然，这三个函数声明是处理文本字符串，但实际上无论单元格数字的格式是文本、数字还是特殊的社会保险号码格式，你都可以使用这三个函数。除手机号码外，作为常见的安全措施，假设你希望只能显示身份证、社会保险号码、信用卡号或其他号码的部分数字，而用星号代替其余位，CONCATENATE函数同样能够帮你大忙。比如，对5555-5555-5555-5555这个信用卡号码来说，公式“=CONCATENATE(REPT("****-",3),RIGHT(D1,4)”，将得到****-****-****-5555的结果。其中REPT("****-",3)是重复"****-"这一文本字符串三次，即得到****-****-****-。原来要Excel显示星号竟然如此简单，你不试试？当然，在使用公式显示星号之后，别忘记把公式引用到的源数据隐藏起来，否则可就起不到保密的作用了。

13、Excel中快速插入系统时间与日期：在用Excel进行报表处理时，经常需要在表格的前端或者未尾插入当天的时间与日期。若用数字输入的话显得比较繁琐。其实可以这样来快速输入：首先选中需要时间的单元格，同时按下“Ctrl+Shift+；”组合键即可；若要输入系统日期则按下“Ctrl+；”组合键即。
14、彻底隐藏Excel工作表：在Excel中可以通过执行[格式]—[工作表]—[隐藏]将当前活动的工作表隐藏起来，在未执行进一步的工作薄设置的情况下，可以通过执行[格式]—[工作表]—[取消隐藏]来打开它。其实还可以通过通过设置工作表的隐藏性来彻底隐藏。按下“Alt+F11”组合键进入VBA编辑窗口，在左侧选中需要隐藏的工作表，按下F4键打开“属性”对话框，切换到“按分类序”标签分页，将“杂项”下的“Visable”的值选择改为“2-xlSheetVeryHidden”或“0-xlSheetVeryHidden”退出后返回Excel即可。这样就将选定的工作表隐藏起来，且“取消隐藏”也不起作用，这样就能彻底隐藏工作表了。将Visable值改还原即可取消隐藏。
15、快速切换Excel工作表：如果一个Excel工作薄中有大量的工作表，要是一个一个去切换查找很麻烦，其实可以在工作表标签左侧的任意一个按钮上右击，在弹出的工作表下拉列表中选中需要切换的工作表即可快速切换到该工作表。另外也可以按下“ctrl+pagedown”组合键从前往后快速按顺序在各个工作表之间切换，按下“ctrl+pageup”组合键可以后往前依次快速地在各个工作表之间切换，这样也能快捷地切换到需要的工作表。
16、不让Excel单元格中的零值显示：如果你在Excel中使用某此函数统计出该单元格的值为零什，它会显示出一个数字“0”，这看上去很不爽，打印出来也会包含这个“0”。怎样才能不让它显示呢？下面以求和函数sum为例为看看如何不显示零值。例如，在某工作表中对A2到E2单元格进行求和，其结果填写在F2中，由于结果可能包含0，因此，为让0不显示则在F2单元格中输入计算公式：“=IF(SUM(A2:E2)=0,"",SUM(A2:E2))”，即如果对A2到E2求和结果为0就不显示，否则显示其结果。
17、Excel中巧选择多个单元格区域：在编辑工作表时，如果要选择不相邻的单元格或单元格区域，大家通常采用的方法是：选择一个单元格或单元格区域，然后按住Ctrl键的同时选择其他单元格或区域。其实，除此之外，Excel还提供了另外一种选择多个单元格区域的方法，笔者感觉更为顺手，该方法是：选择第一个单元格或单元格区域，然后按“Shift+F8”键，并拖动鼠标选中其他不相邻的单元格或区域将它添加到选定区域中，要停止向选定区域中添加单元格或区域，请再将按“Shift+F8”键。
18、SUM函数也做减法：财务统计中需要进行加减混合运算，如果有一个连续的单元格区域B2：B20，在统计总和时需要减去B5和B10的值，用SUM函数计算时可用公式“=SUM(b2:B20,－B5, －B10)”来表示，这样显然比用公式“=SUM(b2:B4,b6:B9,b11:B20)”要来得方便些了。
19、Excel公式与结果切换：Excel公式执行后显示计算结果，按“Excel+`”键（位于键盘左上角），可使公式在显示公式内容与显示公式结果之间切换，方便了公式编辑和计算结果查看。
20、Excel粘贴时跳过空白单元格：如果你只对大块区域中含有数据的单元格进行粘贴，可以选中“选择性粘贴”对话框下面的“跳过单元格”复选框。粘贴时只会将含有数据的单元格粘贴出来，而复制时的含有的空白单元格将不会覆盖表格中的原有数据，这在需要改写数据的场合非常有用。
21、重复记录巧删除：当Excel工作表中有重复记录时，可以利用“高级筛选”功能删除重复记录，具体操作是：选中Excel工作表中的所有记录。在“数据”菜单中，指向“筛选”，单击“高级筛选”命令；单击“将筛选结果复制到其他位置”，然后在“复制到”框中，输入单元格引用；选中“选择不重复的记录”复选框，单击“确定”按钮。
22、Excel文本和函数也能一起“计算”：在要统计的单元格中输入公式“=”当月累计”&SUM(A1:A30)”按回车，最终运算的单元格中会显示结果“当月累计XXX”（XXX为求和的结果），此时文本和公式就一起被“计算”出来了。
23、Excel复制、插入一位到位：在编辑Excel工作表的过程中，当我们将某一单元格中的内容复制到另一个单元格中时，目标单元格的内容便会被覆盖掉。但有很多时候，我们所需要的操作是插入而不是替换。选选择单元格，按下“Ctrl+c”，将源单元格中的内容送入剪切板备用。然后移动鼠标至目标单元格，按下“Ctrl+Shift+ +(加号)”组合键，这时会弹出“插入”对话框，根据实际需要选择“活动单元格右移”或“活动单元格下移”，点“确定”按钮即可将源单元数据插入到目标单元格之前或之上了。

24、巧用Excel批量对比数据：每个月公司的会计都会从财务管理软件中把数据库中一个月的数据导出生成Excel，然后用每天的单据与导出的表中的数据进行比较，核对数据是否正确。上述方法比较繁琐，笔者有更简单的方法，通过两个Excel表自动对比，智能得到结果以便核对。下面就来看看怎么操作。1）首先导出数据，这个导出的Excel表由4列组成。这4列分别是“单号”、“部门”、“员工编号”和“金额”（如图1）；2）会计按照同样的格式做一张单据表并手工输入单据信息，用来和导出的表比较差异。对比的要求是在“单号”、“部门”、“员工编号”都相等的情况下比较“金额”是否相同，如果相同的话提示“金额相同”，否则显示“金额不同”；如果“单号”、“部门”、“员工编号”有一个不相等则应该提示“查无此人”；3）在导出的表中第A列前新插入一列，在新插入的列的A2中输入公式：=B2&"_" &C2&"_" &D2（第一行为标题行所以没有数据）；4）然后在数据区拖拉填充公式，为每一“单号”都建立一个这样的字符串。用同样的方法在单据表中生成一个这样的列。在导出的表中，新建一个Sheet2的工作表，把单据表中的数据复制到导出表的Sheet2中；5）接着在Sheet1中按“F2”键输入公式：=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$A$7,1,FALSE)),"查无此单",IF(EXACT(E2,VLOOKUP(A2,Sheet2!$A$2:$E$7,5,FALSE)),"金额相同","金额不同"))
25、Excel2003快速输入有相同特征的数据：我们经常会输入一些有相同特征的数据，比如员工的厂证编号、单位的职称证书号，都是前面几位相同，后面的数字不一样。我们可以快速输入有相同特征的数据，选定要输入共同特征数据的单元格区域，单击鼠标右键，在弹出的快捷菜单中选择“设置单元格格式”命令，打开“单元格格式”对话框，选中“数字”选项卡，选中“分类”下面的“自定义”选项，然后在“类型”下面的文本框中输入2006080000（注意：后面有几位不同的数据就补几个0），单击“确定”按钮即可。最后在单元格中只须输入后几位数字，如“2006083451”只要输入“3451”，系统就会自动在数据前面添加“200608”。

26、Excel2003“照相机”的妙用：如果要让Sheet2中的部分内容自动出现在Sheet1中，用Excel的照相机功能也是一种方法。操作方法如下：首先点击“工具”菜单，选择“自定义”命令，在弹出的对话框的“命令”选项卡下的“类别”中选择“工具”，在右边“命令”列表中找到“照相机”，并且将它拖到工具栏的任意位置。接着拖动鼠标选择Sheet2中需要在Sheet1显示的内容，再单击工具栏上新增加的“照相机”按钮，于是这个选定的区域就被“拍”了下来。最后打开Sheet1工作表，在要显示“照片”的位置上单击鼠标左键，被“拍摄”的“照片”就立即粘贴过来了。在Sheet2中调整“照片”的各种格式时，粘贴到Sheet1中的内容会同步发生变化，而且因为插入的是一幅自动更新的图像文件，所以“图片”工具栏对这个照片也是有效的，可以进行各种各样的调整。
27、在Excel中如何计算两个日期之间的天数：你可以使用DATEDIF函数来计算，具体方法是：在A1单元格输入前面的日期“2005-1-1”，在B1单元格输入后面的日期“2007-04-02”。接着单击C1单元格，输入公式“=DATEDIF(A1,B1,”d”)”并回车，即可得到计算结果。有一点要注意，公式中的A1和B1分别代表前后两个日期，顺序是不能颠倒的。
28、Excel2007快速隐藏Excel中的部分数据：在Excel中经常遇到某行或某列中部分数据不能够被删除而需要隐藏的情况，我们不能选中整行或整列用“隐藏”命令将它隐藏，因为会隐藏数据的单元格，右击执行“设置单元格格式”命令，然后切换到“自定义”选项卡，在“类型”输入框中输入三个分号“；；；”，确定后即可隐藏数据。
29、Excel2003制作能够自动更新标题日期的工作表：在日常处理数据工作中，一般习惯于通过在一些已有的模板或者工作表基础上，创建新的文件来提高工作效率。但是使用这些源文件时，往往会修改其中的一些不适合内容，特别是这些文件的标题中包含的日期，如2007年4月份各部门交接的商品明细表，而到5月份时则必须进行修改。针对这个问题，可以在存储标题的单元格输入以下公式：=YEAR(TODAY()&”年” &MONTH(TODAY()&”月份各部门交接商品明细表”，这样在5月份打开工作表时，标题中的日期会自动更新为新年份、月份，而不会出现错误了。
30、Excel2003让数值的单位自动输入：在记账时，经常要往固定的表格中输入“X张”、“Y把”和“Z套”等物品单位，每次输入数值后还要再输入单位，实在累人。下面介绍一个简单方法，操作如下：首先选中需要输入数据的单元格区域，接着单击“格式→单元格”命令，在弹出的对话框中切换到“数字”选项卡，选中“分类”列表中的“自定义”项，然后在“类型”中输入：“[=1]G/通用格式”张”； [=6]G/通用格式”把”；G/通用格式”套””，最后单击“确定”即可。

31、Excel2003表格快速变图片：Excel中的一些表格或图表经常需要转换成图片的形式，下面为大家介绍简单的转化方法，选中设计好的表格或图表，按住“Shift”键的同时点击“编辑”菜单，原来的“复制”命令就会变成“复制图片”，然后再到目标位置进行“粘贴图片”即可快速完成转换。

32、Excel2003/2007快速舍掉多余的小数位：当我们在Excel中进行数据处理时，有时候需要对某列(行)数据作小数位数的取舍，比如将某列所有数据保留两位小数，而舍掉多余的小数位而不是四舍五入，则可以利用ROUNDDOWN函数来轻松实现。例如在A1单元格中输入77.8775，然后在B1单元格中输入公式“=ROUNDDOWN(A1,2)”后回车，则B1中显示为77.87，然后利用填充柄对余下的单元格进行公式填充即可。
33、快速删除Excel分表：在Excel2007中打开“人员清单总表”，在表格的最后增加一列（G），在G2中输入公式“COUNTIF（参加劳保者名单!A：A，A2）”，此公式用于统计在参保名单A列与A2单元格相同的单元格个数。选中G2单元格双击其填充柄，把公式向下填充至最后一个记录，此时所有参加劳保的人G列的值显示1，其他没参加劳保的人员G列的值则显示0。右击G列中值为0的单元格，从弹出菜单中依次选择“筛选→按所选单元格的值筛选”，就可以看到表格中所有已参加劳保的人员记录消失了。然后只要把剩下的数据记录复制粘贴到另一个工作表中保存，就得到所有未参加劳保的人员清单了，最后，只要右击G列选择“删除”即可令总表恢复原状。

32、Excel2003/2007选中同一类数据单元格：在一个Excel工作表中，通常会包含多种类型的数据，诸如常见的文本、数值、公式等，有时我们需要从这些不同类型的数据中选中某种类型的数据，如何快速准确的选中呢？可以使用“定位”命令实现，具体操作步骤如下：选择菜单命令“编辑”菜单→“定位”命令，打开“定位”对话框，单击“定位条件”按钮，在“定位条件”对话框中，选择“常量”单选项，然后选中需要的类型数据复选框，如“文本”，选中后单击“确定”按钮即可。

33、Excel2003在Excel 中隐藏部分工作表：在有些时候，由于保密的目的，我们可能希望将Excel工作簿中的一些工作表保护起来，不让部分来授权的用户看到。虽然在Excel中提供了工作表保护的命令，虽然在Excel中提供了工作表保护的命令，但是只能限制对内容的改动，并不能防止未授权用户看到此工作表。而隐藏工作表命令虽然可以使用户暂时看不到此工作表，却不能提供密码保护。为此我们需要将工作薄保护和工作表隐藏两个命令结合起来实现我们的目的。具体操作如下：首先定位于要保护的工作表，选择“格式→工作表→隐藏”，将此工作表隐藏起来。然后再选择“工具→保护→保护工作薄”，并按照提示输入密码，这样就可以将此工作薄用密码保护起来，使未授权的用户不能取消对相应工作表的隐藏，也就看不到此工作表了。
34、用妙法打标准工资条：职工工资构成非常复杂，往往超过10项，因此每月发工资时要向职工提供一包含工资各构成部分的项目名称和具体数值的工资条。打印工资条时要求在每个职工的工资条间有一空行便于彼此裁开。本模板就是用EXCEL函数根据工资清单生成一便于分割含有工资细目的工资条表格。本工资簿包含两张工资表。第1张工资表就是工资清单，称为"清单"。它第一行为标题行包括职工姓名、各工资细目。第2张工作表就是供打印的表，称为"工资条"。它应设置为每三行一组，每组第一行为标题，第二为姓名和各项工资数据，第三行为空白行。就是说整张表被3除余1的行为标题行，被3除余2的行为包括职工姓名、各项工资数据的行，能被3整除的行为为空行。在某一单元格输入套用函数"=MOD(ROW(),3)"，它的值就是该单元格所在行被3除的余数。因此用此函数能判别该行是标题行、数据行还是空行。在A1单元格输入公式"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,"-if-false"))"并往下填充，从A1单元格开始在A列各单元格的值分别为清单A1单元格的值即姓名、-if-false、空白，姓名、-if-false、空白，……。其中-if-false表示MOD(ROW(),3)既不等于0又不等于1时，即它等于2时应取的值。它可用如下函数来赋值："INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"。INDEX()为一查找函数它的格式为：INDEX(reference,row-num,col-num),其中reference为查找的区域，本例中为清单表中的A到G列，即函数中的"清单!$A:$G"，row-num为被查找区域中的行序数即函数中的INT((ROW()+4)/3)，col-num为被查找区域中的列序数即函数中的COLUMN()。第2、5、8…….行的行号代入INT((ROW()+4)/3)正好是2、3、4……，COLUMN()在A列为1。因此公式"=INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())"输入A列后，A2、A5、A8……单元格的值正好是清单A2、A3、A4……,单元格的值。这样，表的完整的公式应为"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清单!A$1,INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())))"。把此公式输入A1单元格，然后向下向右填充得到了完整的工资条表。为了表格的美观还应对格式进行设置，一般习惯包括标题、姓名等文字在单元格中要取中，数字要右置，数字小数点位数也应一致，还有根据个人的爱好设置边框。本表格只需对一至三行的单元格进行设置，然后通过选择性格式设置完成全表的设置。本工作簿的特点是1、不对清单表进行操作保持清单工作表的完整，2、全工作表只有一个公式通过填充得到全表十分方便。————————————————补充：根据使用经验，只要在第一个单元格写入如下代码：=IF(MOD(ROW(),3)=0," ",IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$G,INT((ROW()+4)/3),COLUMN())))里面的主要参数说明：“Sheet1”为所要打印的数据表名，“$A:$K”中的K值为最后打印的列，如果不止于K列，请自行改变。先纵向复制第一个单元格的函数到第三行单元格。在第一列1、2行两个单元格全部划表格线第3行不划表格线，然后选择三个单元格，横向复制、纵向复制到全部数据出现。

35、Excel2003将数据一分为二：在Excel表格中，有时要对一列数据进行分列，如取后几位排序、文字提取等，这在Excel中操作非常简单。方法如下：选中要分列的那一列，在菜单中点击“数据”然后单击“分列”，选择“固定宽度”再在合适位置建立“分列线”点“下一步”、“完成”即可。另外，若选择分隔符号，则是指对含有逗号、空格等分隔符号的列进行的分列操作。它的界面与导入文本文件相同。
36、Excel2003显示被隐藏的下划线：在Excel中“填空型”下划线是我们经常用到的。那么你是否发现当下划线后无字符时，你所输入的下划线只有在编辑其单元格时才能显示，正常情况下根本无法显示并被打印。下面就让我们来解决这个问题，将光标定位在要输入下划线的地方，根据所需长度按键盘上的Shift+—（注意：在英文输入法状态下操作）。好了，看下划线乖乖的出现在眼前了。
37、Excel2003/2007巧输含小数点的数字：在Excel中，我们经常会遇到需要输入大量带有小数点的数字，例如0.0123，如按常规方法输入，不但容易出错，而且效率也很低。这里介绍一种方法，大家不妨一试：就是利用Excel中的自动设置小数点的功能。打开[工具]—[选项]—[编辑]标签页，首先勾选“自动设置小数点”复选框，再在“位数”编辑框中设置小数点右边您所需的位数。这样，在输入带小数点的数字时，就只需要直接输入数字，从而可以省略小数点的输入，它会在按回车键后自动定位。
38、批处理多个Excel工作表：这个很简单，按住Ctrl键或Shift键不放，单击选中多个工作表。然后你就可以像平常一样进行设置，比如单击[文件]—[页面设置]，将选中的多个工作表设置成相同的页面。
39、Excel2003/2007让复制Excel表格列宽不变化：从别的Excel工作薄或工作表复制数据到当前工作表后，你会发现原来正常列宽的表格，却需要重新调整列宽了。其实，这个手动调整列宽的工作可以由Excel自动完成，即在粘贴操作后，立即指向屏幕上出现的“粘贴选项”按钮，并单击按钮旁的箭头，然后单击“保留源列宽”。另外还有一个办法：那就是选择需要粘贴的单元格复制，然后在[选择性粘贴]对话框中点选[列宽]后[确定]，最后再将数据复制进去就可以了。
40、Excel表格内容之对齐法宝：1）选择需要对齐文字的列、行或单元格；2）点击[格式]—[单元格]—[水平对齐]—[分散对齐]；3）按住“Ctrl”键分别选择插入的前后两列，在弹出的“列宽”对话框中输入适当的数值；4）如果只是要让该列或行中的文字稍微拉开点距离，可以点击[格式]—[单元格]—[水平对齐]—[分散对齐]，并勾选[增加缩进]即可。
41、Excel2007快速批量去除超链接：从网上复制资料到Excel2007表格中，如果某列单元格均含有超链接，用手工一一去除超链接比较麻烦，我们可以点击“选择性粘贴”，勾选“数值”选项即可。
42、Excel快速移动表格列或行：在编辑表格的过程中，有时需要调整列和行的位置，只需将鼠标移动到此列和行的表格线上并按住左键向目标位置拖动即可，如果拖动的目标列和行中有数据，则会覆盖该列和行的数据。
43、在Excel中巧做复杂表头：在Excel2003中，制作表头是常有的事，但鉴于中美文化的差异，中文表格中的表头部分有时很复杂，制作起来比较困难，如果遇到要制作像下图中这样的表头，那就更麻烦了，下面介绍一下笔者从工作中总结出来的方法：1）首先做斜线表头部分，确定一个4×4区域，画上斜线，填入文字；2）调整文字的对齐方式及表格各列的宽度，得到理想的表格斜线头部部分，选定该4×4区域，设置框线，完成该部分制作；3）制作横向表格头部，合理合并单元格，填入文字，确定对齐方式，完成制作；4）制作纵向表格头部，合理确定表格线，完成表格制作。
44、Excel2003防止公式被误删和修改：在Excel2003中的各种表格中，特别是统计表格中会设置许多公式进行计算，有时会因为误操作或文件共享被他人修改公式造成不必要的损失，而如果对整张工作表进行保护，需要修改的单元格又因密码问题不能进行。是否有既可以修改单元格，又不会误删操作带有公式的单元格？笔者是这样设置的：按住“Ctrl”键用鼠标左键依次选定允许修改的单元格区域，然后依次点击[工具]—[保护]—[允许用户编辑区域]，在跳出的对话框中选择[新建]，会在[修改区域]对话框中显示你刚才选中的或输入你要修改的单元格区域，单击[确定]关闭，回到[允许用户编辑区域]对话框，点击左下方按钮[保护工作表]，选择[保护工作表及锁定的单元格内容]选框及下方的允许修改的操作内容复选框，并设定保护密码，确定后即可。
45、快速计算某日期处于一年中的第几个星期：如果需要在Excel中，计算某个或者一系列日期，如2007-11-26，处于一年中的第几个星期，可以在单元格中输入日期，如F40，然后在计算结果的单元格中输入公式：=INT((F40-DATE(YEAR(F40),1,0)+WEEKDAY(DATE(YEAR(F40),1,0),1)+7-WEEKDAY(F40,1))/7)，即可得到此日处于2007年中的第48个星期，此方法对于一些安排工作计划日程的表格非常有用。
46、让Excel不显示错误值“#DIV/0！”：在Excel2003表格中，如果单元格A3中输入“=A1/A2”后，在单元格A2未输入数值时，单元格A3就会显示错误值“#DIV/0！”，有什么方法可以让单元格A3在单元格A2未输入数值是变成空白的，不显示错误值，而在单元格A2中输入数值时又能正常显示呢？利用“ISERROR”函数可以解决这个问题。在单元格A3里使用“=IF（ISERROR（A1/A2）,””,A1/A2）”取代“A1/A2”。这样，当单元格A2的值是空的，则返回空值，否则，返回“A1/A2”的值。 

47、调整利率后房贷怎么还：今年银行连续几次加息，让“房奴”肩上的担子更加地沉重，利率发生变化，水涨船高，每个月固定的还款额也要随之发生变化，增加或减少多少？房贷人如果想提前还贷，所需款额如何计算？例如某企业贷款30万元，年利率为5.6%，还款期限为10年，按年或者月付款方式进行等额付款，现计算各个时期的本金和利息。先让大家了解一下PPMT函数，PPMT函数的功能是基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。其语法为：PPMT（rate,per,nper,pv,fv,type）。其中rateo为各期利率：per为用于计算其本金数额的期数，必须介于1到nper之间；nper为总投资期，即该项投资的付款期总数；pv为现值，即从该项投资开始计算时已经入账的款项或一系列未来付款当前值的累积和，也称为本金；fv为未来值或在最后一次付款后希望得到的现金余额；type为数字1或者0，用以指定各期的付款时间是在期初还是期末。1）计算第一年年初应付的本金(如图)：在单元格C5中输入以下公式：=PPMT(D2,1,C2,B2,0,1)；2）计算第一年年末应付的本金：在单元格C6中输入以下公式：=PPMT(D2,1,C2,B2,0,0)；3）计算最后一年年初应付的本金：在单元格C7中输入以下公式：=PPMT(D2,10,C2,B2,0,1)；4）计算最后一年年末应付的本金：在单元格C8中输入以下公式：=PPMT(D2,10,C2,B2,0,0)；5）计算第一个月月初应付的本金：在单元格C8中输入以下公式：=PPMT(D2/12,1,C2,B2,0,1)；6）计算第一年年初和最后一年年初应付的本利和，在单元格E5和E7中输入以下公式：=PMT(D2,C2,B2,0,1)。如果D2单元格中的利率发生变化，那么相应的单元格也会发生变化。
48、用Excel2007计算应收党费：有的地方会计制作党费交纳表，利用Excel2007的IF函数就可以轻松完成这项工作，除此之外，还可以用这种方法计算年终奖和评先进等需要用到超率累进计算的地方，具体操作如下：首先，在Excel2007中制作好基础表格，“税后工资收入”采用引用的方式输入数据，这样当工资收入发生变更时数据就可以自动更新。选定D3单元格，输入公式“=IF(C3<=400,"0.5",IF(AND(C3>400,C3<=600),"1",IF(AND(C3>600,C3<=800),"1.5",IF(AND(C3>800,C3<=1500),"2",IF(1500

Excel轻松实现自动换行

   方法一：如果有大量的单元格需要这样做，采取此种设置格式的方法：选中需要这种格式的单元格，执行“格式→单元格”命令，打开“单元格格式”对话框，切换到“对齐”标签下，选中“自动换行”选项，确定返回即可。

以后，在此类单元格中输入超过列宽的字符时，系统自动按列宽分多行进行排列。

方法二：如果只有少量单元格需要这样做，采取此种手动的方法来实现：在输入过程中，需要换行时，直接按下“Alt+Enter”组合键，继续输入就可以了。

注意：如果已经将数据全部输入完成了想换行，只要将光标定在换行处，按下上述组合键即可。

 Excel处理数据之便捷众人皆知，可在其单元格内换行就略显不便，不知你是否也遇到过此类问题？通过摸索，以下四法便能轻松实现单元格内的自动换行。

    1. 输入数据随时换行

    用户若要在输入数据时换行，只要通过Alt+Enter组合键即可轻松实现。此方法同样可使已输入内容的单元格在光标所在处换行。

    2. 单元格区域内换行

    将某个长行转成段落并在指定区域内换行。例如：A10内容很长，欲将其显示在A列至C列之内，步骤是：选定区域A10：C12（先选A10），选择“编辑→填充→内容重排”，A10内容就会分布在A10：C12区域中。此法特别适合用于表格内的注释。

    3. 调整单元格格式换行

选定单元格，选择“格式→单元格”，在弹出的对话框中单击“对齐”，选中“自动换行”复选框，单击[确定]按钮即可。

一个单元格里一行打不下文字时自动另起一行,还在本格；.右键--设置单元格格式--对齐--自动换行打勾.

    4. 文本框的巧用

    单击“视图”菜单，在“工具栏”命令中，选中“绘图”工具栏，单击该工具栏的“文本框”，为了保证文本框的边界与工作表网格线重合，需按住Alt键的同时插入文本框，然后，就可以在文本框中任意输入内容了。