学习EXCEL的心得.doc

学习EXCEL的心得 1、怎样设置数据重复输入数据有效性设置选项卡允许自定义公式=COUNTIF(C:C,C5)= 12、单元格数据只输入一次，就不能在更改，需密码才行注绿色区域的单元格只能输入一次数据代码如下在VBA中的工作表写下以下代码-此方法对整个工作表中指定的区域有效（本编码密码123）方法一Dim APrivate Sub Worksheet_Change(ByVal TargetAs Range)If Intersect(Target,a1:b55555,d1:f55555)Is NothingThen ExitSub Application.EnableEvents=False IfAThen b=InputBox(改变内容，请输入密码！)If b123Then MsgBox密码错误，数据不能更改!Target=A End If End If Application.EnableEvents=True End Sub Private Sub Worksheet_SelectionChange(ByVal TargetAs Range)If Target.Count1Then ExitSub ElseTarget.Cells(1,1).Select A=Target End If End Sub方法二Dim aPrivate SubWorksheet_Change(ByVal TargetAs Range)If Target.Count=1Then If Intersect(Target,a1:b55555,d1:f55555)Is NothingThen ExitSub Application.EnableEvents=False IfaThen b=InputBox(改变内容，请输入密码！)If b123Then MsgBox密码错误，数据不能更改!Target=a End If End If End If Application.EnableEvents=True End Sub Private SubWorksheet_SelectionChange(ByVal TargetAs Range)Target.Cells(1,1).Select a=Target End Sub方法三Private SubWorksheet_SelectionChange(ByVal TargetAs Range)If Target.Count=1Then IfIntersect(Target,a1:c10,e1:e10)Is NothingThen ExitSub If Target.Value=Then ActiveSheet.Unprotect ElseDim bZ:b=InputBox(请输入密码,检查权限)If b=123Then ExitSub ElseIf b=Then Cells(11,ActiveCell.Column).Select ExitSub ElseGoTo ZEndIf EndIfEndIfEndIfEndSub 3、公式自动填充1【可保护工作表】)在D列输入数据，10列和11列自动向下填充公式，此代码在工作表中要在工作表保护后使用自动筛选功能,需要确保:1,保护工作表时工作表处于自动筛选模式(即显示自动筛选的下拉按钮),2,保护时指定允许筛选Private SubWorksheet_Change(ByVal TargetAs Range)ActiveSheet.Unprotect123&解除工作表保护Application.ScreenUpdating=False Application.EnableEvents=False k=Range(c6556).End(xlUp).Row Range(Cells(6,1),Cells(k,2).FillDown Range(Cells(6,10),Cells(k,10).FillDown Application.EnableEvents=True Application.ScreenUpdating=True Protect Password:=123,AllowFiltering:=True&保护工作表并启用自动筛选EndSub2【可保护工作表把要自动填充公式的列设置为可区域】)在D列输入数据，2行4列的公式自动向下填充公式，此代码在工作表中Private SubWorksheet_Change(ByVal TargetAs Range)Application.ScreenUpdating=False Application.EnableEvents=False k=Range(b6556).End(xlUp).Row Cells(2,4).Copy Range(Cells(2,4),Cells(k,4)Application.EnableEvents=True Application.ScreenUpdating=True EndSub3）、A1单元格输入数据,F1单元格就自动填充公式:=sum(B1:E1),当A2单元格输入数据,F2单元格就自动填充此公式此代码在工作表中Private SubWorksheet_Change(ByVal TargetAs Range)IfTarget.Column1Then ExitSub Target.Offset(0,5)=sum(B&Target.Row&:E&Target.Row&)EndSub 4、保护单元格上面色的代码为指定区域的单元格保护PrivateSubWorksheet_BeforeDoubleClick(ByVal TargetAs Range,Cancel AsBoolean)IfIntersect(Target,a1:a55555,g1:g55555,i1:x55555)Is NothingThen ExitSub IfTarget.Locked=True ThenMsgBox此单元格已保护，不能!Cancel=True EndIfEndSub给个工作表保护的示例SUB PRO（）with Sheets(a)以下带点的语句是对对象a工作表的操作.Unprotect Password:=721223撤销对a工作表的保护.Range(D1)=0写入a工作表D1单元格的值为0,表示注册信息为空.Range(B13)=721223写入a工作表B13单元格的值为721223,表示系统默认管理员登录密码.Range(.Cells(14,1),.Cells(23,15).ClearContents清空a工作表(A14:O23)区域的值(操作员信息).Range(.Cells(2,2),.Cells(10,2).ClearContents清空a工作表(B2:B10)区域的值(注册信息).ProtectPassword:=721223保护a工作表.EnableSelection=xlNoSelection禁止在工作表上进行任何操作End With结束对对象a工作表的 5、在条件语句中如何实现符合某个时期的条件的记录进行统计比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/(格式如何?)解答以下公式,A列为日期列,B列为数据,要求计算1月份的累计:=SUM(IF(MONTH(A:A)=1,B:B,0)此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHIFT+ENTER. 6、有无简结一点的公式求如:a1*b1+a2*b2+b3*b3.的和解答在B4中输入公式=SUM(A1:A3*B1:B3),按CTRL+SHIFT+ENTER结束.或=SUMPRODUCT(A1:A10,B1:B10) 7、当点到某单元格时单元格会出现一个下拉的列框，可以选取其中的数据解答数据-有效性-序列又问好像选取数据只能在本页面，有无高招选取另外页面的数据源，甚至是另外工作薄的？解答先在“插入-名称”中定义好其它页面上需要引用的数据源的名称（AA）。 然后数据-有效性-序列，在“数据源”栏输入“=AA”。 或若改其它活页薄要如何修改下列式子?(假设活页薄名称为book1)=Sheet1!$A$2:$A$14(book2) 8、按表1中A列的编号提取表1中的相应数据并自动录入到表2中的对应的相关列有两个工作表，工作表1中A列为编号，其它列为相关数据，若在工作表2中的编号列单元格中输入编号时，如何才能按表1中A列的编号提取表1中的相应数据，自动录入到表2中的对应的相关列。 解答利用vlookup寒暑，该函数的用处就是把一个数据区域当成数据库，并利用条件检索相关纪录。 有了这点认识就非常简便了！在表2的单元格中输入下面的公式=vlookup(编号，表1！有关数据区域，第n列，false)解释 1、编号不需要输入，主要是编号的相对引用。 例如编号在b2,公式在c2,则编号为b 22、有关数据区域必须是绝对引用，也就是数据区的行列要用$符号修饰。 例如从a1到h50是数据，则应该写为$a$1:$h$ 503、第n列也就是你准备返回第几列的值。 例如你输入编号后，要得到姓名，而姓名在数据区域的第5列，n就是5。 4、false此处取值有两种，一是true,一是false。 两者的区别是true为相似匹配，false为精确匹配。 9、A列记录几百条，如何对这列计数(重复的数值不计)我只能做到新建一列，B列，然后第一个单元格countif（$A$1:$A$100,A1)，然后拖动到全部新列。 最后在新列下面用sumif(B1:B100,1)谁有更好地方法？解答 1、试试这个=SUM(IF(COUNTIF(A1:A100,A1:A100)=1,1,0) 2、操作A1作公式栏，A2作字段名栏，如原该两栏有数插入2行。 在A1输入=SUBTOTAL(3,A$2:A$5000)统计记录数或=SUBTOTAL(9,A$2:A$5000)数据汇总选数据-筛选-高级筛选-选择不重复的记录。 复原选数据-筛选-高级筛选-全部显示。 3、试试这个=SUM(IF($A$1:$A$100=,1/(COUNTIF($A$1:$A$100,$A$1:$A$100) 4、请解释一下,因为我单独使用COUNTIF($A$1:$A$100,$A$1:$A$100)数组公式时,它仅仅计算第一个也就是A1的个数. 5、我发觉你的这办法，只对唯一的数据进行了计数，而重复的数据全部未计入（是不是应该将重复的数据也计上一个？）打哈欠的“=SUM(IF(COUNTIF(A1:A100,A1:A100)=1,1,0)”也是这样。 TO剑魔版主你公式中的“1/(COUNTIF($A$1:$A$100,$A$1:$A$100)”像是一个倒数，怎么理解？ 6、用倒数是这个意思:如果只出现一次，数组中的相应项统计为1，其倒数为1，Sum统计计1如果出现N次，其倒数为1/N，出现了N次，求和就是Nx1/N，最后Sum统计就只计1。 如何求得某行或列不重复数字的个数解答B5:Z5行中不重复数字的个数输入=SUM(1/COUNTIF(B5:Z5,B5:Z5)-1后按CTRL+SHIFT+Enter 10、如何只对单元格的公式进行保护方法 1、1先用鼠标点左上角，（行标与列标交叉处）全选工作表，2格式单元格格式把锁定与隐藏的勾全取掉，3定位定位条件公式4格式单元格格式把锁定与隐藏的勾打上（选隐蔽，公式也看不到了）5工具保护保护工作表设置密码勾选允许的操作。 方法 2、我介绍的这种方法，很简单，不用保护菜单，也一样达到保护公式的目的。 操作步骤如下定位公式单元格数据有效性设置选项卡允许自定义；公式0，其他采用默认设置即可方法 3、PrivateSubWorksheet_SelectionChange(ByVal TargetAs Range)Dim rgAs RangeFor Eachrg InTarget.Cells Ifrg.HasFormula ThenApplication.EnableEvents=False Cells(Target.Row,1).Select EndIf Application.EnableEvents=True NextEndSub 11、让不同类型数据用不同颜色显示在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。 1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式条件格式”命令，打开“条件格式”对话框。 单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。 单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。 2.按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。 3.设置完成后，按下“确定”按钮。 看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了 12、建立分类下拉列表填充项我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。 1.在Sheet2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。 2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。 仿照上面的操作，将B、C列分别命名为“商业企业”、“个体企业”3.切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据有效性”命令，打开“数据有效性”对话框。 在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”选项，在下面的“”方框中，输入“工业企业”，“商业企业”，“个体企业”序列(各元素之间用英文逗号隔开)，确定退出。 再选中需要输入企业名称的列(如D列)，再打开“数据有效性”对话框，选中“序列”选项后，在“”方框中输入公式=INDIRECT(C1)，确定退出。 4.选中C列任意单元格(如C4)，单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。 然后选中该单元格对应的D列单元格(如D4)，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。 提示在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。 13、Excel就能自动判断、即时分析并弹出警告你能想象当你在该输入四位数的单元格中却填入了一个两位数，或者在该输入文字的单元格中你却输入了数字的时候，Excel就能自动判断、即时分析并弹出警告，那该多好啊!要实现这一功能，对Excel来说，也并不难。 例如我们将光标定位到一个登记“年份”的单元格中，为了输入的统一和计算的方便，我们希望“年份”都用一个四位数来表示。 所以，我们可以单击“数据”菜单的“有效性”选项。 在“设置”卡片“有效性条件”的“允许”下拉菜单中选择“文本长度”。 然后在“数据”下拉菜单中选择“等于”，且“长度”为“4”。 同时，我们再来到“出错警告”卡片中，将“输入无效数据时显示的出错警告”设为“停止”，并在“标题”和“错误信息”栏中分别填入“输入文本非法!”和“请输入四位数年份。 字样。 很显然，当如果有人在该单元格中输入的不是一个四位数时，Excel就会弹出示的警告对话框，告诉你出错原因，并直到你输入了正确“样式”的数值后方可继续录入。 神奇吧?其实，在Excel的“数据有效性”判断中，还有许多特殊类型的数据格式可选，比如“文本类型”啊，“序列大小”啊，“时间远近”啊，如你有兴趣，何不自作主张，自己设计一种检测标准，让你的Excel展示出与众不同的光彩呢。 14、使用vlookup函数的问题当时有两千多人的考试成绩要与花名册挂接，考试成绩放在sheet km1中，花名册放在sheet hmc中，他们共有字段为准考证号，我的想法是根据准考证号，用vlookup函数查找相应的成绩并放在相应的人员下。 sheet km的准考证号放在第一列，考试成绩放在第二列，查找范围是$a$2:$b$2265，sheet hmc的准考证号党在第一列。 公式为vlookup(a2,km!$a$2:$b$2265,2,false)公式应该没什么问题，但只能找到很少的纪录(60),究竟是什么地方除了问题，请高手指点！解答1可以用SUMIF函数解决=SUMIF(km!$A$2:$B$2131,A2,km!$B2:$B$2131)(作者注将sheet km下的所有准考证号都转化为文本，再使用vlookup函数，一切正常！vlookup函数查找区域必须转化为文本！) 15、如何自动填充内容A1A20是编号，B1B20是姓名，C1C20是性别，当我在A21单元格输入A1A20范围内的任意一个编号时，B21出现对应的姓名，C21出现对应的性别。 该如何做，请帮忙。 解答B21单元格公式“=IF(A21=0,VLOOKUP(A21,A1:C20,2,FALSE)”；C21单元格公式“=IF(A21=0,VLOOKUP(A21,A1:C20,3,FALSE)”这个公式也适用于A列编号不排序的情况，如果升序的话会更简单一点。 问以上公式中的false有什么用？能否省略？答false参数主要是用它以后在A列中的数据可以不是升序排列。 不然如果A列不是升序排列，公式会出错的。 16、&的用法有E44单元格，我希望总计=SUM(E45:E49)就是想让它经过自动求和后在一个单元格内显示总计120。 解答有多种方法实现，详细如下 1、=总计&sum(e45:e49) 2、把E44格式设为总计:#0.00;总计:-#0.00;总计:0.00; 3、将E44单元格格式自定义为总计0.000即可,方便对E44的引用计算 4、=CONCATENATE(合计,SUM(e45:e49) 17、公式的自动填充一张表中某几个不连续的列有公式，我不想一次全复制下去，最好是让它自动填充上一行的公式，这样用多少就自动填下去，不知道有没有办法。 解答Dim Mrow,Xrow,i AsInteger Mrow=Range(a65536).End(xlUp).Row Xrow=Range(d65536).End(xlUp).Row+1Application.ScreenUpdating=False Fori=Xrow ToMrow IfCells(i,1)Then Cells(i,4)=Cells(i-1,4)+Cells(i,2)-Cells(i,3)Cells(i,6)=Cells(i,2)*Cells(i,5)Cells(i,8)=Cells(i-1,8)-Cells(i,7)+Cells(i,6)Cells(i,7)=Cells(i,5)*Cells(i,3)EndIfNext iApplication.ScreenUpdating=True 18、打开一张工作表时系统提示要求输入用户名和密码解答If Application.InputBox(请输入密码)=Then Sheets(.).Visible=True Sheets(.).Select Range(.).Select Else:c=对不起，密码不正确d=警告MsgBox prompt:=c,Title:=d EndEndIf 19、excel的六大“条件”功能EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其是使用其“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈EXCEL的条件功能（为方便起见，笔者在此以如图1包含工程基本情况的二维表格为例）。 一、条件求和。 1、单条件求和统计c1公司施工的工程总建筑面积，并将结果放在e18单元格中，我们只要在e18单元格中输入公式“=sumif（d2d17,c1公司,e2e17）”即完成这一统计。 友情提醒如果对excel的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。 2、多条件求和统计c2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在e19单元格中，我们用“条件求和”功能来实现选“工具向导条件求和”命令（若没有此命令选项，可以用“加载宏”的方式来增加这一命令选项），在弹出的对话框中，按右下带“”号的按钮（此时对话框变成类似工具条的窗口形式浮于桌面上），用鼠标选定d1i17区域，并按窗口右边带红色箭头的按钮（恢复对话框状态）。 按“下一步”，在弹出的对话框中，按“求和列”右边的下拉按钮选中“建筑面积”项，再分别按“条件列、运算符、比较值”右边的下拉按钮，依次选中“施工单位”、“=”（默认）、“c2公司”选项，最后按“添加条件”按钮。 重复前述操作，将“条件列、运算符、比较值”设置为“质量等级”、“=”、“合格”，并按“添加条件”按钮。 两次点击“下一步”，在弹出的对话框中，按右下带“”号的按钮，用鼠标选定e19单元格，并按窗口右边带红色箭头的按钮。 按“完成”按钮，此时符合条件的汇总结果将自动、准确地显示在e19单元格中。 友情提醒上述操作实际上是输入了一个数组公式，我们也可以先在e19单元格中直接输入公式=sum(if(d2:d17=c2公司,if(i2:i17=合格,e2:e17)，然后在按住ctrl+shift键（非常关键！）的同时按下enter键，也可以达到上述多条件求和之目的。 （“条件求和”的另一招.=SUMPRODUCT(B9:C14,MOD(B9:C14+1,2)用Sunif()设置求和条件比较困难的时候，可以考虑上面的公式对B9:C14中的偶数进行求和。 扩展可以考虑将MOD()替换成相应的条件。 ） 二、条件计数。 统计质量等级为“合格”工程的数目，并将结果存放在i18单元格中，在i18单元格中输入公式=countif(i2:i17,合格)，当按下确定按钮后，统计结果数字5即自动在i18单元格中显示出来。 三、条件格式。 将工程造价在500万元（含500万元）以上的工程造价数值以红颜色显示出来选中f2至f17单元格；用“格式条件格式”命令，打开“条件格式”对话框；按第二个方框旁的下拉按钮，选中“大于或等于”选项，再在后面的方框中输入数字500；按上述对话框中的“格式”按钮，打开“单元格格式”对话框，在“文字”卡片下，按“颜色”旁的下拉按钮，将文字颜色设置成红色后，按“确定”按钮关闭“单元格格式”对话框，回到“条