学习EXCEL的心得.docx

1、学习EXCEL的心得1、怎样设置数据重复输入数据有效性设置选项卡允许：自定义公式：=COUNTIF(C:C,C5)=12、单元格数据只输入一次，就不能在更改，需密码才行注：绿色区域的单元格只能输入一次数据代码如下：在VBA中的工作表写下以下代码-此方法对整个工作表中指定的区域有效（本编码密码：123）方法一：Dim APrivate Sub Worksheet_Change(ByVal Target As Range)If Intersect(Target, a1:b55555,d1:f55555) Is Nothing Then Exit SubApplication.EnableEvent

2、s = FalseIf A Then b = InputBox(改变内容，请输入密码！) If b 123 Then MsgBox 密码错误，数据不能更改! Target = A End If End If Application.EnableEvents = TrueEnd SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)If Target.Count 1 ThenExit SubElseTarget.Cells(1, 1).SelectA = TargetEnd IfEnd Sub方法二：Dim aPrivate

3、 Sub Worksheet_Change(ByVal Target As Range) If Target.Count = 1 Then If Intersect(Target, a1:b55555,d1:f55555) Is Nothing Then Exit SubApplication.EnableEvents = FalseIf a Then b = InputBox(改变内容，请输入密码！) If b 123 Then MsgBox 密码错误，数据不能更改! Target = a End If End If End IfApplication.EnableEvents = True

4、End SubPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)Target.Cells(1, 1).Selecta = TargetEnd Sub方法三Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Count = 1 Then If Intersect(Target, a1:c10,e1:e10) Is Nothing Then Exit Sub If Target.Value = Then ActiveSheet.Unprot

5、ect Else Dim bZ: b = InputBox(请输入密码, 检查权限) If b = 123 Then Exit Sub Else If b = Then Cells(11, ActiveCell.Column).Select Exit Sub Else GoTo Z End If End If End If End If End Sub3、公式自动填充1【可保护工作表】)在D列输入数据，10列和11列自动向下填充公式，此代码在工作表中要在工作表保护后使用自动筛选功能,需要确保:1,保护工作表时工作表处于自动筛选模式(即显示自动筛选的下拉按钮),2,保护时指定允许筛选Privat

6、e Sub Worksheet_Change(ByVal Target As Range)ActiveSheet.Unprotect 123 & 解除工作表保护Application.ScreenUpdating = FalseApplication.EnableEvents = False k = Range(c6556).End(xlUp).RowRange(Cells(6, 1), Cells(k, 2).FillDownRange(Cells(6, 10), Cells(k, 10).FillDownApplication.EnableEvents = TrueApplication.

7、ScreenUpdating = TrueProtect Password:=123, AllowFiltering:=True & 保护工作表并启用自动筛选End Sub2【可保护工作表：把要自动填充公式的列设置为可编辑区域】) 在D列输入数据，2行4列的公式自动向下填充公式，此代码在工作表中Private Sub Worksheet_Change(ByVal Target As Range)Application.ScreenUpdating = FalseApplication.EnableEvents = False k = Range(b6556).End(xlUp).Row Cel

8、ls(2, 4).Copy Range(Cells(2, 4), Cells(k, 4) Application.EnableEvents = TrueApplication.ScreenUpdating = TrueEnd Sub3）、A1单元格输入数据,F1单元格就自动填充公式:=sum(B1:E1),当A2单元格输入数据,F2单元格就自动填充此公式 此代码在工作表中Private Sub Worksheet_Change(ByVal Target As Range)If Target.Column 1 Then Exit SubTarget.Offset(0, 5) = =sum(B &

9、 Target.Row & :E & Target.Row & )End Sub4、保护单元格上面色的代码为指定区域的单元格保护Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)If Intersect(Target, a1:a55555,g1:g55555,i1:x55555) Is Nothing Then Exit Sub If Target.Locked = True Then MsgBox 此单元格已保护，不能编辑! Cancel = True End If End Sub

10、给个工作表保护的示例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

11、).ClearContents 清空a工作表(B2:B10)区域的值(注册信息) .Protect Password:=721223 保护a工作表 .EnableSelection = xlNoSelection 禁止在工作表上进行任何操作 End With 结束对对象a工作表的5、在条件语句中如何实现符合某个时期的条件的记录进行统计比如有1-12月份的记录单,需要实现对每个月里些数据的统计汇总/(格式如何?)解答：以下公式,A列为日期列,B列为数据,要求计算1月份的累计: =SUM(IF(MONTH(A:A)=1,B:B,0) 此为数组公式,在输完公式后,不要ENTER,而要CTRL+SHI

12、FT+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”。或：若改其它活页薄要如何修改下列式子?( 假设活页薄名称

13、为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、编号不需要输入，主要是编号的相对引用。例如： 编

14、号在b2,公式在c2,则编号为：b2 2、有关数据区域必须是绝对引用，也就是数据区的行列要用$符号修饰。例如：从a1到h50是数据，则应该写为：$a$1:$h$50 3、第n列也就是你准备返回第几列的值。例如：你输入编号后，要得到姓名，而姓名在数据区域的第5列，n就是5。 4、false此处取值有两种，一是true,一是false。两者的区别是 true为相似匹配，false为精确匹配。9、A列记录几百条，如何对这列计数(重复的数值不计)我只能做到新建一列，B列，然后第一个单元格countif（$A$1:$A$100,A1)，然后拖动到全部新列。最后在新列下面用sumif(B1:B100,1)

15、 谁有更好地方法？解答：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

16、:$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。如何求得某行或列不重复数字的个

17、数解答： B5:Z5行中不重复数字的个数：输入=SUM(1/COUNTIF(B5:Z5,B5:Z5)-1后按CTRL+SHIFT+Enter10、如何只对单元格的公式进行保护方法1、1：先用鼠标点左上角，（行标与列标交叉处）全选工作表，2：格式单元格格式把锁定与隐藏的勾全取掉，3：编辑定位定位条件公式4：格式单元格格式把锁定与隐藏的勾打上（选隐蔽，公式也看不到了）5：工具保护保护工作表设置密码勾选允许的操作。方法2、我介绍的这种方法，很简单，不用保护菜单，也一样达到保护公式的目的。操作步骤如下：定位公式单元格数据有效性设置选项卡允许：自定义；公式：0，其他采用默认设置即可方法3、Private

18、 Sub Worksheet_SelectionChange(ByVal Target As Range) Dim rg As Range For Each rg In Target.Cells If rg.HasFormula Then Application.EnableEvents = False Cells(Target.Row, 1).Select End If Application.EnableEvents = True Next End Sub11、让不同类型数据用不同颜色显示 在工资表中，如果想让大于等于2000元的工资总额以“红色”显示，大于等于1500元的工资总额以“蓝色

19、”显示，低于1000元的工资总额以“棕色”显示，其它以“黑色”显示，我们可以这样设置。 1.打开“工资表”工作簿，选中“工资总额”所在列，执行“格式条件格式”命令，打开“条件格式”对话框。单击第二个方框右侧的下拉按钮，选中“大于或等于”选项，在后面的方框中输入数值“2000”。单击“格式”按钮，打开“单元格格式”对话框，将“字体”的“颜色”设置为“红色”。 2.按“添加”按钮，并仿照上面的操作设置好其它条件(大于等于1500，字体设置为“蓝色”；小于1000，字体设置为“棕色”)。 3.设置完成后，按下“确定”按钮。 看看工资表吧，工资总额的数据是不是按你的要求以不同颜色显示出来了12、建立分

20、类下拉列表填充项 我们常常要将企业的名称输入到表格中，为了保持名称的一致性，利用“数据有效性”功能建了一个分类下拉列表填充项。 1.在Sheet2中，将企业名称按类别(如“工业企业”、“商业企业”、“个体企业”等)分别输入不同列中，建立一个企业名称数据库。 2.选中A列(“工业企业”名称所在列)，在“名称”栏内，输入“工业企业”字符后，按“回车”键进行确认。 仿照上面的操作，将B、C列分别命名为“商业企业”、“个体企业” 3.切换到Sheet1中，选中需要输入“企业类别”的列(如C列)，执行“数据有效性”命令，打开“数据有效性”对话框。在“设置”标签中，单击“允许”右侧的下拉按钮，选中“序列”

21、选项，在下面的“来源”方框中，输入“工业企业”，“商业企业”，“个体企业”序列(各元素之间用英文逗号隔开)，确定退出。 再选中需要输入企业名称的列(如D列)，再打开“数据有效性”对话框，选中“序列”选项后，在“来源”方框中输入公式：=INDIRECT(C1)，确定退出。 4.选中C列任意单元格(如C4)，单击右侧下拉按钮，选择相应的“企业类别”填入单元格中。然后选中该单元格对应的D列单元格(如D4)，单击下拉按钮，即可从相应类别的企业名称列表中选择需要的企业名称填入该单元格中。 提示：在以后打印报表时，如果不需要打印“企业类别”列，可以选中该列，右击鼠标，选“隐藏”选项，将该列隐藏起来即可。1

22、3、Excel就能自动判断、即时分析并弹出警告你能想象当你在该输入四位数的单元格中却填入了一个两位数，或者在该输入文字的单元格中你却输入了数字的时候，Excel就能自动判断、即时分析并弹出警告，那该多好啊!要实现这一功能，对Excel来说，也并不难。 例如我们将光标定位到一个登记“年份”的单元格中，为了输入的统一和计算的方便，我们希望“年份”都用一个四位数来表示。所以，我们可以单击“数据”菜单的“有效性”选项。在“设置”卡片“有效性条件”的“允许”下拉菜单中选择“文本长度”。然后在“数据”下拉菜单中选择“等于”，且“长度”为“4”。同时，我们再来到“出错警告”卡片中，将“输入无效数据时显示的出

23、错警告”设为“停止”，并在“标题”和“错误信息”栏中分别填入“输入文本非法!”和“请输入四位数年份。”字样。 很显然，当如果有人在该单元格中输入的不是一个四位数时，Excel就会弹出示的警告对话框，告诉你出错原因，并直到你输入了正确“样式”的数值后方可继续录入。神奇吧?其实，在Excel的“数据有效性”判断中，还有许多特殊类型的数据格式可选，比如“文本类型”啊，“序列大小”啊，“时间远近”啊，如你有兴趣，何不自作主张，自己设计一种检测标准，让你的Excel展示出与众不同的光彩呢。14、使用vlookup函数的问题当时有两千多人的考试成绩要与花名册挂接，考试成绩放在sheet km1中，花名册放

24、在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下的所有准考证号都转

25、化为文本，再使用vlookup函数，一切正常！vlookup函数查找区域必须转化为文本！)15、如何自动填充内容A1：A20是编号，B1：B20是姓名，C1：C20是性别，当我在A21单元格输入A1：A20范围内的任意一个编号时，B21出现对应的姓名，C21出现对应的性别。该如何做，请帮忙。解答：B21单元格公式“=IF(A21=0,VLOOKUP(A21,A1:C20,2,FALSE)”；C21单元格公式“=IF(A21=0,VLOOKUP(A21,A1:C20,3,FALSE)”这个公式也适用于A列编号不排序的情况，如果升序的话会更简单一点。问：以上公式中的false有什么用？能否省略？答

26、：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、公式的自动填充一张表中某几个不连续的列有公式，我不想一次全复制下去，最好是让

27、它自动填充上一行的公式，这样用多少就自动填下去，不知道有没有办法。解答：Dim Mrow, Xrow, i As IntegerMrow = Range(a65536).End(xlUp).RowXrow = Range(d65536).End(xlUp).Row + 1Application.ScreenUpdating = FalseFor i = Xrow To MrowIf Cells(i, 1) ThenCells(i, 4) = Cells(i - 1, 4) + Cells(i, 2) - Cells(i, 3)Cells(i, 6) = Cells(i, 2) * Cells(

28、i, 5)Cells(i, 8) = Cells(i - 1, 8) - Cells(i, 7) + Cells(i, 6)Cells(i, 7) = Cells(i, 5) * Cells(i, 3)End IfNext iApplication.ScreenUpdating = True18、打开一张工作表时系统提示要求输入用户名和密码解答：If Application.InputBox(请输入密码：) = ThenSheets(.).Visible = TrueSheets(.).SelectRange(.).SelectElse: c = 对不起，密码不正确d = 警告MsgBox p

29、rompt:=c, Title:=dEndEnd If19、excel的六大“条件”功能EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其是使用其“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈EXCEL的条件功能（为方便起见，笔者在此以如图 1包含工程基本情况的二维表格为例）。一、条件求和。1、单条件求和：统计c1公司施工的工程总建筑面积，并将结果放在e18单元格中，我们只要在e18单元格中输入公式“=sumif（d2：d17,c1公司,e2：e17）”即完成这一统计。友情提醒：如果对excel的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。2、多条件求和：统计c2公司施工的质量等级为“合格”的工程总建筑面积，并将结果放在e19单元格中，我们用“条件求和”功能来实现：选“工具向导条件求和”命令（若没有此命令选项，可以用“加