工地用电三级箱装配:Excel技巧大全（一）

Excel技巧大全（一）

转载

杂谈

Excel技巧大全(一)

EXCEL中错误提示信息的含义及解决办法　　 EXCEL中错误提示信息的含义及解决办法
       在Excel中输入计算公式后，经常会因为输入错误，使系统看不懂该公式。
      在单元格中显示错误信息，常常使一些初学者手足无措。现将Excel中最常见的一些错误信息，以及可能发生的原因和解决方法列出如下，以供初学者参考。
      (1)####
      错误原因：输入到单元格中的数值太长或公式产生的结果太长，单元格容纳不下。
      解决方法：适当增加列的宽度。
      (2)#div/0！
      错误原因：当公式被零除时，将产生错误值#div/0！
      解决方法：修改单元格引用，或者在用作除数的单元格中输入不为零的值。
      (3)#N/A
      错误原因：当在函数或公式中没有可用的数值时，将产生错误值#N/A。
      解决方法：如果工作表中某些单元格暂时没有数值，在这些单元格中输入#N/A，公式在引用这些单元格时，将不进行数值计算，而是返回#N/A。
      (4)#NAME？
      错误原因：在公式中使用了Microsoft Excel不能识别的文本。
      解决方法：确认使用的名称确实存在。如所需的名称没有被列出，添加相应的名称。如果名称存在拼写错误，修改拼写错误。
      (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的方法就能做到。请看一个在页脚左边插入全文件名的例子：
      Sub InsertFullName()
      ActiveSheet.PageSetup.LeftFooter = ActiveWorkbook.FullName
      End Sub
      如果改变其中的“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 (Visual Basic for Application) 语句输入到Auto_Open下面：
      Sub Auto_Open()
      Sheets(“Sheet1”).Activate
      '取消工作表保护
      ActiveSheet.Unprotect
      '将当日销售值拷贝到上日销售一栏
      x = MsgBox(“把当日销售值拷贝到上日销售栏吗？”, vbYesNo)
      If x = vbYes Then
      Range(“B5:B8”).Copy
      Range(“C5”).Select
      Selection.PasteSpecial Paste:=xlValues
      Application.CutCopyMode = False
      End If
      '将日期增加一天
      x = MsgBox(“把日期增加一天吗？”,vbYesNo)
      If x = vbYes Then
      Range(“C2”)= Range(“C2”)+ 1
      End If
      '重新保护工作表
      ActiveSheet.Protect
      End Sub
      将文件保存并关闭。重新打开此文件，体验一下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”表示要汇总的单元格。)
      这样，我们就完成了汇总报表的编制工作。以后每月只要将下属单位上报的报表文件拷贝到指定的目录中，就轻松完成了报表的合并。你也试试吧，只要开动脑筋，巧妙运用办公软件，就能提高工作效率，轻松实现办公自动化！

用Excel 2000建立简单、实用的进销存系统　　 用Excel 2000建立简单、实用的进销存系统

      现在市面上进销存管理软件主要以《速达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.

0

顶