excel 控件教程.doc

excel 控件教程本文介绍excel 控件相关的一些知识点。excel 控件有两种，分别是【窗体控件】和【ActiveX控件】。两种控件做出相同的效果，但它俩也有很多不同的地方。Excel 控件是放置于窗体上的一些图形对象，可用来显示或输入数据、执行操作或使窗体更易于阅读。这些对象包括文本框、列表框、选项按钮、命令按钮及其他一些对象。控件提供给用户一些可供选择的选项，或是某些按钮，单击后可运行宏程序。第一，excel 控件之窗体控件介绍excel 控件，其中，“窗体”有16个命令按钮，依次为：标签、编辑框，分组框、按钮、复选框、选项按钮，列表框、组合框，组合式列表编辑框、组合式下拉编辑框，滚动条、微调项，控件属性、编辑代码，切换网格、执行对话框。“控件工具箱”还包含通过其他程序安装的自定义ActiveX控件，例如：通过Microsoft Internet Explorer安装的Active Movie控件。第二，excel 控件在哪里找出来首先，看看excel 控件在哪里？Excel2010版，单击“开发工具”选项卡-插入-表单控件，就可以找到excel 控件工具箱。如果你的Excel选项卡中没有“开发工具”，参考显示excel 开发工具调出Excel开发工具。excel 控件2003版的调出方法是：在工具栏单击鼠标右键，勾选【窗体】、【控件工具箱】。【窗体】调出的是【窗体控件】的工具栏、【控件工具箱】调出的是【ActiveX控件】的工具栏。第三，excel 控件第一个实例：以【微调项】为例（【微调项】Excel2007改为【数值调节钮】），介绍它在两种控件的制作方法。 单击【窗体控件】中的【微调项】控件，然后在表格上拖动一个区域（区域大小决定控件的大小）。插入好控件后，对控件的属性进行设置，右键单击控件，单击【设置控件格式】，在弹出的【设置控件格式】对话框中选择【控制】选项卡。【当前值】：是控件当前的赋值，可以不去管它，它随着控件的变化而变化，不是固定的值； 【最小值】：如果我们需要用控件调整一年中月份的变化，最小值可设定为1； 【最大值】：比如需要用控件调整一年中月份的变化，最大值可设定为12； 【步长】：步长是指每次单击按钮值的增加或减少的幅度，月份的变化步长可设定为1； 【单元格链接】：这个设置决定控件在表格中的赋值对象，也就是单击控件时希望哪个单元格随着变化。【三维阴影】：如果希望控件具有立体阴影效果，可以勾选【三维阴影】前的复选框。 控制属性设置好后，单击【确定】完成。 第四，excel 控件之ActiveX控件ActiveX控件，比窗体工具条中的控件要更灵活，是VBE中用户窗体控件的子集，在Excel工作表中和VBE编辑器中都是可用的，尤其在要对使用控件时发生的不同事件进行控制时。我们能捕获这些控件的事件，这也是其灵活的主要原因。这些事件可能是单击、双击、变化(例如对组合框控件项目进行新的选择)，用户能离开这个控件并转移焦点到另一个控件或返回到Excel界面，等等。ActiveX控件也有一个长的属性列表，如字体(Font)、标题(Caption)、名称(Name)、单元格链接(Linked Cell)、高度(Height)，等等。这些属性取决于控件的类型，但所有控件都有诸如名称(Name)属性和一些其它的属性。ActiveX 控件也具有可自定义的属性。例如，可指定当用户指向控件时指针的外观。您可编写宏，响应与 ActiveX 控件相关的事件。可在 Excel 窗体上和 VBA 程序窗体和对话框中使用 ActiveX 控件。在使用控件时，如果不使用带有控件事件的VBA代码，则很少使用ActiveX控件；如果您不熟悉VBA，您应该选择使用窗体工具条控件。第五，如何识别excel 控件属于哪类控件要确定控件类型，可以右键单击该控件，会显示快捷菜单：(1)如果快捷菜单包含“属性”命令，则该控件为“控件工具箱”中的 ActiveX 控件，且您处于“设计”模式。 (2)如果快捷菜单包含“指定宏”命令，则该控件为“窗体”工具栏控件。 (3)如果快捷菜单包含“编辑文字”命令，则该对象为“绘图”工具栏对象。如果右键单击控件以显示快捷菜单时却没有响应，则该控件是 ActiveX 控件且您不在“设计”模式下。若要显示 ActiveX 控件的快捷菜单以编辑属性或查看宏代码，则必须处于设计模式。第六，excel 控件中“窗体工具条”和“控件工具箱”按钮变灰，无法使用的原因为什么会出现excel 控件无法使用，有多种原因，其中，“编辑框”控件、“组合式列表编辑框”控件、“组合式下拉编辑框”控件、“执行对话框”控件在现行Excel工作簿中是不可用的，因此始终变灰显示，这些控件仅与Excel5.0对话工作表一起使用。当前处于单元格的数据输入模式下，因此“窗体”和“控件工具箱”工具栏上的所有按钮都变暗。您可能不经意地进入了被控件覆盖的单元格的数据输入模式，或者网格线被隐藏您无法方便地看见单元格。按Esc以退出单元格数据输入模式。 “控件属性” 按钮永远不会对“窗体”工具栏上的“标签” 、“按钮” 和“分组框” 控件启用。 如果不处于“设计”模式且激活了 ActiveX 控件，则“属性” 和“查看代码” 按钮不会启用。第七，excel 控件无法打印打印工作表时，须将“打印对象”复选框或“PrintObject”属性设置为“True”，才能打印控件。第八，excel 控件无法调整复选框或选项按钮大小在“窗体”工具栏和“控件工具箱”的“复选框”和“选项按钮”控件中，控件内部及文本左侧的复选框和选项按钮的大小是固定的。没有办法调整大小。 第九，excel 控件如何与单元格网络线对齐若要使控件按照单元格网格线对齐，请按住 Alt 再拖动控件。看完以上教程，应该对excel 控件有了大致的了解，剩下的，还得自己动手实践。excel隔列求和的两种方法excel隔列求和在实际工作也是很常见的。比如下图所示的，分别对各个地区2011年和2010年数据分别合计。要求在C、D列完成excel隔列求和。下面提供几种解法供参考：excel隔列求和方法一：最原始的办法就是分别对各个单元格相加，比如：C3单元格公式为：=E3+G3+I3+K3，然后右拉下拉。excel隔列求和方法二：C3单元格公式为：=SUMIF($E$2:$L$2,C$2,$E3:$L3)，右拉下拉得到其余求和。这种解法是利用sumif条件求和来实现的。Sumif函数使用链接：2011年第13期技术讲座：sumif和countif函数应用excel隔列求和方法三：C3单元格公式为：=SUMPRODUCT($E3:$L3*(MOD(COLUMN($E3:$L3),2)=2-COLUMN(A1)，仍然是右拉下拉复制公式得到其余结果。公式解释：COLUMN函数可以得到所在列的列数，比如公式“=COLUMN(E3)”得到的结果就是“5”。MOD函数是取余数的，比如“=MOD(3,2)”就是计算3除以2所得到的余数，结果为1。公式的核心就是mod取余的结果:mod(奇数，2）=1，mod(偶数,2)=0上面提供了几种excel隔列求和的思路，根据实际数据源稍微变通就可以完成隔列求和。EXCEL实例讲解：快速选择固定区域这里有一个月份统计报表，见下图：每个月都会做一个这样的统计报表，其中只有访问量和发布数量的数据是需要变动的，其余都是固定不变的。如何快速选中和删除固定不变（即图中加粗部分）和需要变动数据（即图中未加粗部分）两个区域？如何快速制作类似的新表呢？具体方法如下：一、加粗部分的选中：利用Ctrl键选择不相邻单元格的方法，按住Ctrl键，选中所加粗部分的单元格，然后，单击“插入名称定义”或者直接在文档左上角地址栏直接输入一个名称，这里我们输入“固定区域”，按回车键，确认。二、未加粗部分的选中：由于这部分都是相邻单元格组成，所以直接拖动鼠标选中即可，然后，同样单击“插入名称定义”或者直接在文档左上角地址栏直接输入一个名称，这里我们输入“删除区域”，按回车键，确认。三、快速编辑删除区域内容的方法：在左上角地址栏直接单击刚才我们定义的“删除区域”，就可以快速选中此区域，然后按del键删除内容，在输入需要的新内容即可。利用定义名称快速选中区域的方法，对于快速选中不相邻单元格内容是相当实用的。如果下次需要再次选择，只需要选择定义的名称即可。在我们做报表时，对每个月变动的区域操作，可以节省时间，减少不必要重复的工作量。excel指定工作表计算选项设置excel自动重算和手动重算设置正确与否，在公式很多文件很大的情况下，影响到速度和节省保存时间。Excel自动重算是指每次更改值、公式或名称时均计算所有相关公式。excel手动计算是指每次更改值、公式或名称，只计算当前单元格的公式。2010版除了通过excel选项设置以外，也可以单击公式计算选项手动重算和自动重算等等。通常情况下，自动重算和手动重算设置都影响到整个工作薄。如果仅设定指定的工作表不进行重新计算，该如何实现。按下ALT+F11组合键，打开VBE窗口，“视图”菜单属性，在“属性”窗口中，选择要设定的工作表，将EnableCalculation属性值设为False。这样工作簿重算时，该表仍不会进行重算。但此设置在关闭工作簿后即失效。为了每次打开工作簿都能使用此设置，可在Thisworkbook中加入一段代码：Private Sub Workbook_Open() Sheet1.EnableCalculation = FalseEnd Sub或在新模块中加入auto_open子过程Sub Auto_Open() Sheet1.EnableCalculation = FalseEnd Sub下面是自动重算和手动重算设置相关的截图。根据需要更改相应的工作表和EnableCalculation的值true和false来实现手动重算和自动重算。excel两列合并的几种方法excel两列合并的运用很常见。excel两列合并视具体的数据源不同，合并两列的方法也不同。excel两列合并应用一：日期合并把AB两列的日期合并在C列，由于日期比较特殊，直接使用连接符号合并会出现格式问题，所以C2单元格输入公式：=TEXT(A2,yyyy-m-d)&/&TEXT(B2,yyyy-m-d)，来合并两列。excel两列合并应用二：文本和百分号合并A列为文字，B列为百分比。在C列合并两列内容，并保留百分比符号。如果直接使用=A2&B2，就得不到预期的效果。D3、D4单元格的公式才能正确合并两列。excel两列合并应用三：市区合并A列为市，B列为区，C列合并两列内容，并在最前面添加“四川省”三个字。C1输入公式：=四川省&A1&B1，下拉复制完成两列合并。excel两列合并相关文章：excel合并单元格数据的方法/show.asp?id=310excel合并日期时间的方法/show.asp?id=1115excel多列变(合并)一列的方法/show.asp?id=1197excel隔行填充颜色的三种方法当excel工作薄数据量很多，我们使用excel隔行填充颜色，可以增强数据的阅读性。excel隔行填充颜色的方法也很多，本文总结几种常见方法。excel隔行填充颜色方法一：添加一辅助列，如A列，分别输入1、2、1、2这样的数字，然后启用筛选，再选中其中的奇数或者偶数行填充颜色。动画演示：excel隔行填充颜色方法二：使用条件格式：选择区域，然后单击开始条件格式：设置公式为：=mod(row(),2)=0，单击“格式”，选择一种颜色。如果是隔列填充颜色，奇数列，则公式为：=Mod(Column(),2)=1，如果是偶数列，则公式为：=Mod(Column(),2)=0。思路解释：行号使用row函数提取，隔行即对行号的奇偶性进行判断，奇偶性的判断有两种，一个是直接用ISODD或者ISEVEN函数，还有一个是MOD(ROW(),2)。隔行填充颜色，使用条件格式。excel隔行填充颜色方法三：使用插入表格功能实现excel隔行填充颜色，具体的操作，可以参考下面的演示：“此文档中包含宏”的设置方法我们在使用2007、2010高版本保存文件时，有时会提示“隐私问题报告”“此文档中包含宏、Active控件、XML扩展包或web组件、其中可能包含个人信息，并且这些信息不能通过“文档检查器”进行删除”。出现“此文档中包含宏”提示，可以参考下面的设置。此文档中包含宏（2010版设置） 2010版，单击文件选项，打开Excel选项信任中心信任中心设置个人信息设置文档特定设置，取消勾选“保存时从文件属性中删除个人信息”复选框。假如文件中存在宏代码，需要保存为XLSM、XLSB、XLSA中的一种，不能保存为XLSX。此文档中包含宏（2003版设置） 2003版，单击“工具”-“选项”-“安全性”-取消勾选“保存时将文件属性删除个人信息”。隐藏excel公式计算错误值的两技巧因为一些数据源等等关系，有时我们写出的公式会出现excel公式计算错误。比如下面的截图，H3单元格的公式：=F3/G3，当除数为0或者空，都会产出DIV/0错误。工作表中出现这样的excel公式计算错误值不美观，如何屏蔽公式返回的错误值呢？有两种方法可以实现。excel公式计算错误值屏蔽方法一：在Excel2003中，使用=IF(ISERROR(公式),公式)，套在本例公式就变为：=IF(ISERROR(F2/G2),F2/G2)。公式原理是返回空文本的方法来屏蔽错误，如果公式较长，看起来就会很繁琐。在Excel2007中，新增了一个函数可以方便的实现此项功能的函数，=IFERROR(公式,)。本例为：=IFERROR(F2/G2,)。excel公式计算错误值屏蔽方法二：使用条件格式。设置条件格式当单元格值为错误时，将字体颜色设置为和单元格底色相同的颜色，以假乱真。下面是excel公式计算错误值屏蔽的截图：宏表函数实现excel提取公式有时我们需要使用excel提取公式，来更直观的学习。比如下面这个截图，在公式编辑栏可以看见C2单元格的公式。如果我们需要用excel提取公式显示在C3单元格。该如何做呢？也就是在C3单元格显示C2单元格的公式。excel提取公式有两种方法：excel提取公式方法一：空格+公式单击C2单元格，在公式编辑栏复制公式，然后单击C3单元格，输入空格，再粘贴公式。这种excel提取公式，实际的结果就是将公式当做文本显示。excel提取公式方法二： GET.CELL宏表函数完成选择C3单元格，单击公式定义名称，弹出“新建名称”对话框，名称输入“提取公式”，引用位置输入：=get.cell(6,C2)，确定。然后在C3单元格输入：=提取公式，回车，就可以完成excel提取公式。上面两种excel提取公式的方法，第一种，因为是文本，所以不会自动更新。第二种使用的宏表函数，当数据源和C2单元格公式更新，C3单元格提取的公式也会随着自动更新。批量取消隐藏工作表的方法一个群友问到如何批量取消隐藏工作表。在一个excel工作薄里隐藏了十几张工作表，想将其取消隐藏，使用excel内置的功能：“格式工作表取消隐藏”，只能逐张慢慢取消隐藏工作表，如何批量取消隐藏工作表呢？要解决批量取消隐藏工作表，就只能使用VBA代码完成。下面提供两端批量取消隐藏工作表的VBA代码，都可以实现。批量取消隐藏工作表代码一：按下Alt+F11键,在打开的Microsoft Bisual Basic窗口中,选择插入模块.，复制下面的代码，按F5键运行，就完成了批量取消隐藏工作表。Sub 批量取消隐藏工作表() Dim i As Integer For i = 1 To Worksheets.Count Worksheets(i).Visible = True NextEnd Sub批量取消隐藏工作表代码二：Sub 批量取消隐藏工作表() For Each AA In Sheets AA.Visible = True NextEnd Sub两段代码使用任意一段都可以批量取消隐藏工作表。excel取不重复值的四种方法excel取不重复值方法一：高级筛选A列姓名有重复的，将A列姓名取不重复值之后放在C列。操作方法是：单击数据高级，打开“高级筛选”对话框，进行如下图设置。excel取不重复值方法二：SQL单击数据现有连接，单击最下面的“浏览更多”，按照路径找到文件，弹出的“导入数据”对话框，单击“属性”，打开“连