Excel技巧大全01.docx
《Excel技巧大全01.docx》由会员分享，可在线阅读，更多相关《Excel技巧大全01.docx（14页珍藏版）》请在冰豆网上搜索。
Excel技巧大全01
Excel技巧大全01
全文共分5部，此为第1部：
因字体比较小，欢迎复制到自己的电脑里再看，呵呵。
EXCEL中错误提示信息的含义及解决办法 EXCEL中错误提示信息的含义及解决办法
在Excel中输入计算公式后，经常会因为输入错误，使系统看不懂该公式。
在单元格中显示错误信息，常常使一些初学者手足无措。
现将Excel中最常见的一些错误信息，以及可能发生的原因和解决方法列出如下，以供初学者参考。
（1）####
错误原因：
输入到单元格中的数值太长或公式产生的结果太长，单元格容纳不下。
解决方法：
适当增加列的宽度。
（2）#div/0！
错误原因：
当公式被零除时，将产生错误值#div/0！
解决方法：
修改单元格引用，或者在用作除数的单元格中输入不为零的值。
（3）#N/A
错误原因：
当在函数或公式中没有可用的数值时，将产生错误值#N/A。
解决方法：
如果工作表中某些单元格暂时没有数值，在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
（4）#NAME？
错误原因：
在公式中使用了MicrosoftExcel不能识别的文本。
解决方法：
确认使用的名称确实存在。
如所需的名称没有被列出，添加相应的名称。
如果名称存在拼写错误，修改拼写错误。
（5）#NULL！
错误原因：
当试图为两个并不相交的区域指定交叉点时，将产生以上错误。
解决方法：
如果要引用两个不相交的区域，使用和并运算符。
（6）#NUM！
错误原因：
当公式或函数中某些数字有问题时，将产生该错误信息。
解决方法：
检查数字是否超出限定区域，确认函数中使用的参数类型是否正确。
（7）#REF！
错误原因：
当单元格引用无效时，将产生该错误信息。
解决方法：
更改公式，在删除或粘贴单元格之后，立即单击“撤消”按钮以恢复工作表中的单元格。
（8）#VALUE！
错误原因：
当使用错误的参数或运算对象类型时，或当自动更改公式功能不能更正公式时，将产生该错误信息。
解决方法：
确认公式或函数所需的参数或运算符是否正确，并确认公式引用的单元格所包含均为有效的数值。
Excel应用技巧七则 Excel应用技巧七则
王东
一、利用Excel创建序列目录
这两天在翻看《电脑报》1999年的报纸，发现在第3期软件版中有一篇文章《克隆‘目录’》，它谈的是具有序列特征的目录的建立，如DISK001，DISK002...等，它介绍利用一个小软件来实现的方法。
我在工作中曾经遇到过种情况，我是利用Excel来实现这个功能的。
比如要建立Disk001到Disk020共20个目录。
打开一个Excel的空白工作簿，在A1输入md，在B1中输入Disk001；选中A1，用鼠标拖动右下角的填充柄向下拉到A20，这样从A1到A20全为md；选中B1，用鼠标拖动右下角的填充柄向下拉到B20，结果从B1到B20为Disk001到Disk020序列。
然后选择保存，在保存对话框中选择保存类型为MS-DOS文本文件，文件名为Test.bat，即批处理文件。
系统会提示你不能保存格式，不要理会它，选择“确定”即可。
退出Excel。
将Test.bat文件复制到你需要创建目录的地方，执行它，你的序列目录就自动创建好了。
二、在工作表的页眉或页脚中加上文件的全文件名（路径加文件名）
在工作簿的页眉或页脚中打印上文件的全文件名是一个查询文件位置的好办法。
可是在Excel中用常规的方法只能插入文件名，是不能实现像Word中页眉或页脚中插入文件的全文件名的功能的。
但是用VBA的方法就能做到。
请看一个在页脚左边插入全文件名的例子：
SubInsertFullName（）
ActiveSheet.PageSetup.LeftFooter=ActiveWorkbook.FullName
EndSub
如果改变其中的“LeftFooter”代码为“CenterFooter”或是“RightFooter”
就可实现在页脚中间或右边插入文件的全文件名。
如果你想将全文件名插入页眉的左边、中间或右边，可以修改其中的“LeftFooter”代码为“LeftHeader”“CenterHeader”或是“RightHeader”即可。
三、Excel表格中打印行号和列标
Excel中一般打印时是不会打印行号（数字）和列标（字母）
的。
但由于行号和列标用于定位工作表中信息的确切位置，所以在有些特殊时候还是需要表格上标出行号行列标的。
要使行号和列标能打印机出来，你可以按以下步骤操作：
单击“文件”菜单中的“页面设置”命令，选择其中的“工作表”选项卡，选中“行号列标”复选框即可。
预览一下，你就会发现带有行号和列标的表格了。
四、在多个工作表内输入相同内容
有时，我们会遇到因为某些原因要在同一个工作簿的不同工作表中某些单元格上输入相同的内容，你这时不必利用复制、粘贴，而直接采用下述方法即可：
1.在按下Ctrl键的同时，用鼠标单击需要输入相同内容的不同工作表，使得非当前工作表的标签变为白色，这样就为这些工作表建立了联系。
2.在其中的任意一个工作表中输入所需要的内容，这样数据就会自动出现在选中的其它工作表之中。
3.输入完成之后，按下Ctrl键，使用鼠标单击先前所选择的多个工作表，解除这些工作表之间的联系。
这个方法可以在需要输入时反复使用，最适合于几个工作表中的内容既有相同的部分，又有不同部分的情况。
五、在Excel中编辑单元格中的超级链接
如果你的Excel单元格中有超级链接，你是否会认为编辑它是件很麻烦的事。
因为当你使用鼠标点击单元格时，其中的超级链接将自动打开Internet浏览器窗口，可那并不是你所希望的结果。
然而使用键盘会让你很容易编辑超级链接：
很简单，你只要先用键盘或鼠标定位到要编辑单元格邻近的单元格，然后用箭头键选择要编辑的单元格，再按下F2来编辑单元格内容。
编辑完成后，按ENTER键确认改动或者按ESC键取消改动。
其实对于任意的单元格来说，F2都是有效的，只不过对于编辑带有超级链接的单元格更有用处。
六、不用公式也可以进行快速计算
你也许会遇到这样的情况，随时想知道所选中的单元格中数值的和，或者是平均值之类的，又不想占用一格单元格来存放公式及结果。
如果选中的范围随时可能变化，用公式也处理不了这个问题。
Excel其实提供了这个功能，它的默认操作是“求和”。
你选中一些单元格，在状态栏中就会显示所选单元格的总和。
鼠标右击状态栏，在弹出的菜单中单击“平均值”，你就可以在状态栏中看到平均值了。
该方法可以计算选定单元格的平均值、总和、最小值、最大值。
你还可利用这个方法计算包含数字的单元格的数量（选中“计数值”），或者计算已填充单元格的数量（选中“计数”）。
七、使用鼠标右键拖动单元格填充柄
大家都习惯用鼠标左键拖动单元格填充柄来自动填充数据序列。
其实，使用鼠标右键拖动单元格填充柄更加灵活。
当你在一单元格内输入数据后，按住鼠标右键沿着要填充序列的方向拖动填充柄一直到你所想要填充的范围，将会出现一个菜单，其中有三组命令选项。
第一组为“复制单元格”、“以序列方式填充”、“以格式填充”、“以值填充”；第二组为“以天数填充”、“以工作日该充”、“以月该充”、“以年填充”；第三组为“线性增长”、“几何增长”、“序列…”。
此时，你可以根据自己的需要选择一种填充方式。
Excel中用自动运行宏提高工作效率 Excel中用自动运行宏提高工作效率
梁延峰
在日常工作中，我们经常需要在每次打开同一个Excel文件时都进行一些例行的操作，如改变表格的格式、更新报表日期、打印文件、对工作表进行保护或取消保护等等。
Excel的自动运行宏“Auto_Open”可在文件打开后立即完成这些例行的操作任务，既快速又准确。
假设我们在Excel文件的工作表Sheet1中有一个《销售日报表》如下图：
（图）
下面我们在这个文件中建立一个Auto_Open宏，让它在文件打开后自动完成下面任务：
1.取消工作表保护；
2.把“当日销售”列里的数据值复制到“上日销售”一列；
3.将日期增加一天；
4.恢复工作表保护。
具体做法如下：
1.
在“工具”菜单上选择“宏”子菜单，打开“宏”对话框，在“宏名”一栏里键入“Auto_Open”，再点击下面的“新建”钮，进入宏编辑状态。
注意不要把宏名字输错了，否则宏不会自动执行。
2.在宏编辑状态下，把下面VBA（VisualBasicforApplication）语句输入到Auto_Open下面：
SubAuto_Open（）
Sheets（“Sheet1”）.Activate
'取消工作表保护
ActiveSheet.Unprotect
'将当日销售值拷贝到上日销售一栏
x=MsgBox（“把当日销售值拷贝到上日销售栏吗？
”,vbYesNo）
Ifx=vbYesThen
Range（“B5:
B8”）.Copy
Range（“C5”）.Select
Selection.PasteSpecialPaste:
=xlValues
Application.CutCopyMode=False
EndIf
'将日期增加一天
x=MsgBox（“把日期增加一天吗？
”,vbYesNo）
Ifx=vbYesThen
Range（“C2”）=Range（“C2”）+1
EndIf
'重新保护工作表
ActiveSheet.Protect
EndSub
将文件保存并关闭。
重新打开此文件，体验一下Auto_Open宏是如何为你工作的吧。
如果你想用Auto_Open完成其它的操作而又不知道如何用VBA语句直接建立宏，Excel的录制宏的功能可以帮助你，但是别忘了把所录制的宏取名为Auto_Open。
关于录制宏的方法请参阅一般的Excel功能手册。
用Excel合并会计报表 用Excel合并会计报表
刘昌松
我看了《电脑报》2001年4月2日第13期《Excel在会计中的应用》一文，其中提到利用Execl汇总报表的方法，但每次都要把下属单位的报表数据手工输入子报表中，如果下属单位较多，每次都这样操作甚是麻烦。
下面介绍我在实际工作中摸索出的一种合并会计报表的方法：
利用Execl可以在不同的工作簿文件中建立链接关系的功能来合并报表。
这种方法可一劳永逸解决每次手工输入的问题，只要每次将下属单位的报表文件拷贝到指定的目录下，就轻松完成了报表合并！
应用此法的前提是：
所有的下属单位上报的报表都是Excel格式的工作簿文件，具体上报文件可以用磁盘或E-mail方式传送文件。
此法的思路是：
建立一个汇总工作簿，第一张表为汇总工作表，其后是各个下属单位的分表；把下属单位传送来的报表文件存放在指定的目录中，再将汇总工作簿中的分表和指定目录中对应下属单位的工作簿文件建立链接关系（将下属单位的报表数据引用到对应的分表中）；最后将汇总工作簿的分表数据用求和函数汇总到汇总工作表中，形成汇总报表。
1.给每个单位上报的报表文件指定文件名。
如：
A1.xls、A2.xls……并把下属单位的报表文件存放在指定的目录。
2.建立汇总工作簿。
在其中建立1+N张工作表，“1”表示第一张是汇总工作表，“N”表示下属单位的个数，有N个下属单位就建立N张分表。
然后将下属单位报表的格式，用“选择性粘贴”→“格式”，复制到分表中。
这是为了防止在建立分表和对应的下属单位工作表的链接时，因两个工作表的格式不同，而在分表中产生链接值为零的单位格。
3.建立分表与各单位报表文件的链接关系。
先打开一个下属单位的工作簿文件，选中待汇总工作表中的有效单元格（注意：
不是工作表中所有的单位格，如果不幸你将分属不同文件的两个工作表中的所有单位格建立了链接关系，那么你的电脑必定死机！
）复制下来，再打开汇总报表工作簿中该单位所属的子表，在左上第一个单位格上单击右键，选择“选择性粘贴”，在弹出的对话框中点击“粘贴链接”，就建立了该单位报表与所属子表之间的对应链接关系。
链接的下属单位报表文件作了任何改变，都会立即在所对应的分表中反映出来。
然后，对其它的单位的报表和所属子表如法建立对应链接关系。
4.将分表数据汇总到汇总工作表中。
用《Excel在会计中的应用》一文中介绍的方法在第一张汇总工作表中建立汇总的函数关系。
（函数公式为：
“=SUM（'A1:
An'!
Xn）”：
“SUM”为求和函数，“A1”表示要汇总的第一张工作表名，“An”表示要汇总的最后一张工作表名，“Xn”表示要汇总的单元格。
）
这样，我们就完成了汇总报表的编制工作。
以后每月只要将下属单位上报的报表文件拷贝到指定的目录中，就轻松完成了报表的合并。
你也试试吧，只要开动脑筋，巧妙运用办公软件，就能提高工作效率，轻松实现办公自动化！
用Excel2000建立简单、实用的进销存系统 用Excel2000建立简单、实用的进销存系统
王志军
现在市面上进销存管理软件主要以《速达2000》和《管家婆》等软件为主，这些软件确实功能强大，极大方便了广大中小型工商业用户，而利用Excel
2000的强大功能也能方便快捷地实现进销存的简单管理。
一、功能需求分析
进销存主要涉及材料的进出，产品的销售，毛利、库存的计算等等。
我们根据这一要求建立例表如下：
（图）
各列顺序为“类别、货品编号、货品名称规格、单位、库存数量、成本价、库存金额、进货数量、进货金额、累计销售数量、累计销售金额、累计销售毛利、当天销售数量、当天销售金额、当天销售毛利”。
每一行则是每一种具体的货品。
库存数量为最新的实时库存，以利于日常管理，当进货或销售时，库存数量、成本价会实时相应改变。
二、功能的实现
（一）建立相应公式
1.库存金额=库存数量×成本价。
即在G2格录入“=E2*F2”。
2.当天销售毛利=当天销售金额-当天销售数量×成本价。
即在O2格录入“=N2-M2*F2”。
然后把公式复制到每一行。
（二）进货流程
当有进货时，将进货数量、进货金额录入相应的单元格后，库存数量相应增加，成本价相应改变，然后清空录入的进货数量、进货金额单元格，以利下次录入。
要选定Q、R两列为累加区。
操作如下：
1.在Q2格录入“=E2+H2”，在R2格录入“=ROUND（（I2+G2）/（E2+H2）,0）”，然后把公式复制到每一行。
2.选择工具菜单“宏”中的“录制新宏”，给新宏确定宏名为“进货提交”，然后开始录制：
选定Q2到R16区域，复制后，点击E2单元格，选择“编辑”菜单中的“选择性粘贴”，只粘贴值。
然后选择H2到I16区域，清空。
停止录制宏。
3.为保护Q、R两列，将Q、R两列隐藏。
其实就是起到一个累加器的作用。
这样当宏录制完成后，每次只需点击相应的宏，进货数量就实时增加到库存中去了。
（三）销售流程
当销售时，将销售数量，销售金额录入相应的单元格后，库存数量相应减少，累计销售数量、累计销售金额、累计销售毛利相应累加，然后清空销售数量，销售金额的单元格，以利下次录入。
要选定STUV四列为过渡区。
操作如下。
1.在S2格录入“=E2-M2”，在T2格录入“=J2+M2”，在U2格录入“=K2+N2”，在V2格录入“=L2+O2”，然后将相应公式复制到每一行。
2.选择工具菜单“宏”中的“录制新宏”，给新宏确定宏名为“销售提交”，然后开始录制：
选定S2到S16区域，复制后，点击E2单元格，选择编辑菜单中的选择性粘贴，只粘贴值。
然后选择T2到V16区域，复制后，点击J2单元格，选择“编辑”菜单中的“选择性粘贴”，只粘贴值。
最后选择M2到N16区域，清空。
停止录制宏。
3.为保护S、T、U、V四列，将这四列隐藏。
（四）建立自定义菜单
为了操作起来更加方便、快捷，通过新建菜单和自定义宏把录制的宏放到菜单中去。
操作如下：
1.点击“视图/工具栏/自定义”，选命令中的“新菜单”，将“新菜单”拖至菜单项，用鼠标右键点击新建的“新菜单”给它重新命名，如“明月灯饰”。
2.再选“视图/工具栏/自定义”，将命令中的“宏”-“自定义按钮”
拖至新建的菜单下。
给“自定义按钮”指定新名为“进货提交”，然后在指定宏中指定刚才录制的“进货提交”宏。
3.重复以上第2步骤，新建一个新按钮，改名为“销售提交”，指定宏为刚才录制的“销售提交”宏。
需要说明的是因为列之间有相应的关联，列不能随便增删。
而行可以任意增加或删除。
这样可以方便地增加各种货品了。
当这个系统建立后，每天只要输入进货或销售的资料，每天的实时库存、每天的毛利、累计毛利都一清二楚，再利用Excel
2000的筛选、打印等功能打印出来，真是轻松上手，全不费一点功夫。
以上只是一个例子，在实际运用中，只需将相应区域扩大即可。
用Excel实现进销存自动统计 用Excel实现进销存自动统计
钟爱军
随着电脑的普及应用，每一台电脑都可以灵活方便地安装使用Excel软件。
而除财会部门外，其他部门的电脑都去装上专门的财务、商务软件肯定是不现实的。
在日常的业务核算、统计核算和会计核算三大核算工作中，相当多的工作人员还在依赖手工方式。
面对在手工下的各种大量而又十分繁琐的费时费力又费神的数据查找、登记、计算、汇总工作，除利用财务、商务软件解决外，利用Excel去解决这些问题，其实是一件十分方便且轻松的事情。
本文以任务驱动的方式，引导读者在完成任务的过程中掌握相应的操作技能。
目标任务
在一个工作簿中分别制作一个“进货”工作表、一个“销售”工作表和一个“进销存自动统计”工作表，设置好相应的公式和条件格式。
每当发生进货或销售业务而在“进货”工作表或在“销售”工作表中输入进货业务或销售业务数据时，“进销存自动统计”表中便自动计算出每一种商品的当前总进货量、当前总销售量和当前库存量。
当库存量超过或低于规定的“报警线”时，能进行特殊显示，以示警告。
操作要点及注意事项
Excel表格的制作，Excel工作表函数公式的运用，条件格式的运用。
本文所述操作在Excel97和Excel2000下运行测试通过。
除汉字外，Excel公式中的所有字符，都必须在英文（En）状态下输入。
方法与步骤
（一）新建工作簿
1．单击“开始”菜单，在弹出的开始菜单项中单击“新建office文档”，出现“新建office文档”对话框窗口。
2．“新建office文档”对话框窗口中的“常用”活页夹中，双击“空工作簿”，出现名为“Book1”的空工作簿。
3．将“Book1”保存为“进销存自动统计系统．xls”。
（二）定义工作表名称及数据
1．双击“Sheet1”工作表标签，输入“进货”后按【Enter】键。
2．双击“Sheet2”工作表标签，输入“销售”后按【Enter】键。
3．双击“Sheet3”工作表标签，输入“进销存自动统计”后按【Enter】键。
4．选择“进货”工作表，输入标题（进货日期、商品名称、进货数量）和相应各项数据。
限于篇幅，以及仅为说明问题起见，这里只列举甲、乙、丙三种商品（表1图）。
5．选择“销售”工作表，输入标题（销售日期、销售去向、商品名称、销售数量）和相应各项数据（表2图）。
6．选择“进销存自动统计”工作表，在第一行中分别输入标题内容：
商品名称、当前总进货量、当前总销售量、当前库存量（表3图）。
（三）定义公式
1．在“进销存自动统计”工作表中选择B2单元格，输入“=SUMIF（进货！
B：
B，"甲"，进货！
C：
C）”，按【Enter】键。
2．向下拖动B2单元格右下方的黑点至B4单元格，进行公式复制的操作。
3．选择B3单元格，按F2键，修改公式中的“甲”为“乙”，同样，修改B4单元格公式中的“甲”为“丙”。
如果有更多的商品，依此类推，直至修改完毕为止。
注意，从公式定义可以看出，此例中的单元格相加求和的条件依据是商品名称：
甲、乙、丙。
4．选定B2至B4单元格，向右拖动B4单元格右下方的黑点至C列，进行公式的复制操作。
5．选择C2单元格，按F2键，将公式中的“进货”修改为“销售”，同样，再分别修改C3、C4单元格公式中的“进货”为“销售”。
如果有更多的单元格需要定义公式，依此类推，直至修改完毕为止。
6．选定D2单元格，输入“=B2-C2”，按【Enter】键。
7．向下拖动D2单元格右下方的黑点至D4单元格（如果有更多的，一直向下拖动到最后一个单元格即可），完成公式的复制工作。
（四）库存报警（字符突出显示）设置
1．单击D列的列标，然后选择“格式”菜单中的“条件格式”命令。
2．在打开的“条件格式”对话框中，在“条件1”区域中进行最高库存量报警的突出显示设置：
首先，从左到右，分别选定“单元格数值”（Excel97中是“单元格数值为”）、“大于或等于”，并输入一个合适的最高库存量报警线数字。
然后，单击“格式”按钮，在打开的对话框中设置颜色为“红色”，字形为“加粗”。
最后按“确定”按钮，完成库存一旦超高即报警的突出显示设置。
3．在“条件格式”对话框中，单击“添加”按钮，随即便会增加一个“条件2”区域。
在“条件2”区域中进行最低库存量报警的突出显示设置：
首先，从左到右，分别选定“单元格数值”、“小于或等于”，并输入一个合适的最低库存量报警线数字（比如，输入1，表示当库存只剩一件或没有时，突出警示）。
然后单击“格式”按钮，再在打开的对话框中设置颜色为“蓝色”，字形为“加粗”。
最后按“确定”按钮，即完成库存超低的报警突出显示设置。
（五）日常应用
1．平时，每次只要在“进货”工作表和“销售”工作表中输入实际发生的进货或销售数据，“进销存自动统计”表中便会自动得到当前的总进货量、当前的总销售量以及当前库存量。
同时，当库存量超过或低于报警线数字时，就会以红色或蓝色并加粗字符来突出显示。
2．购入“进货”工作表中没有的新货时，需要按照上面所述方法在“进货”工作表和“进销存自动统计”工作表中增设相应的商品名称及其取数公式，公式设置还是按照前面所描述的方法，采取复制加修改的方法最快捷。
结束语
本文提供和介绍了利用Excel实现有关进销存业务自动统计的一种基本思路和基本做法，其中重点是公式和条件格式的运用。
至于商品进销存业务中的“商品编号”、“业务摘要”
、“单价”、“金额”以及“备注”等，可根据各自需要在工作表中进行相应设置；也可以对举例中的数据项标题名称进行更改；还可以对公式中单元格相加求和的条件依据进行更改，比如，“商品名称”变为“商品编号”。
“MicrosoftOffice工资表”全攻略 “MicrosoftOffice工资表”全攻略
吴群峰
如果你单位有得心应手的工资软件，或者你甘心埋头苦干不惜汗水，那就省省吧，用不着往下看了。
小型企事业单位如果没有专门的工资表软件，每月编制工资表都是一件费时费力的重复劳动。
不过只要有MicrosoftOffice2000/XP
软件，可就轻松多了。
用Excel制作工资表
步骤一：
创建工资表的基本格式（图）。
步骤二：
建立和复制计算公式。
在E3单元格内写入公式“=B3+C3+D3”，然后选定该单元格，并按着CTRL键，鼠标单元格右下变成复制符号时，拖至E5单元格，此时应发额计算完毕。
个人所得税的计算是个比较棘手的问题，MicrosoftExcel却提供了简捷的方法。
如果贵单位职工的最高月收入低于20800，就只须在F3单元格内键入“=IF
（（E3-800）>5000,（3-800）*0.2-375,IF（（E3-800）>2000,（E3-800）*0.15-125,IF（（E3-800）>500,（E3-800）*0.1-25,IF（（E3-800）>0,（E3-800）*
升级会员 