学习EXCEL得.docx - 冰点文库

资源描述

学习EXCEL得.docx

《学习EXCEL得.docx》由会员分享，可在线阅读，更多相关《学习EXCEL得.docx（18页珍藏版）》请在冰点文库上搜索。

学习EXCEL得.docx

学习EXCEL得

————————————————————————————————作者：

————————————————————————————————日期：

1、怎样设置数据重复输入

数据—有效性—设置选项卡—允许：

自定义—公式：

=COUNTIF（C:

C,C5）=1

2、单元格数据只输入一次，就不能在更改，需密码才行

注：

绿色区域的单元格只能输入一次数据

代码如下：

在VBA中的工作表写下以下代码-----此方法对整个工作表中指定的区域有效（本编码密码：

123）

方法一：

DimA

PrivateSubWorksheet_Change（ByValTargetAsRange）

IfIntersect（Target,[a1:

b55555,d1:

f55555]）IsNothingThenExitSub

Application.EnableEvents=False

IfA<>""Then

b=InputBox（"改变内容，请输入密码！

"）

Ifb<>"123"Then

MsgBox"密码错误，数据不能更改!

Target=A

EndIf

Application.EnableEvents=True

EndSub

PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）

IfTarget.Count<>1Then

ExitSub

Else

Target.Cells（1,1）.Select

A=Target

EndIf

EndSub

方法二：

Dima

PrivateSubWorksheet_Change（ByValTargetAsRange）

IfTarget.Count=1Then

IfIntersect（Target,[a1:

b55555,d1:

f55555]）IsNothingThenExitSub

Application.EnableEvents=False

Ifa<>""Then

b=InputBox（"改变内容，请输入密码！

"）

Ifb<>"123"Then

MsgBox"密码错误，数据不能更改!

Target=a

EndIf

Application.EnableEvents=True

EndSub

PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）

Target.Cells（1,1）.Select

a=Target

EndSub

方法三

PrivateSubWorksheet_SelectionChange（ByValTargetAsRange）

IfTarget.Count=1Then

IfIntersect（Target,[a1:

c10,e1:

e10]）IsNothingThenExitSub

IfTarget.Value=""Then

ActiveSheet.Unprotect

Else

Dimb

b=InputBox（"请输入密码","检查权限"）

Ifb="123"Then

ExitSub

Else

Ifb=""Then

Cells（11,ActiveCell.Column）.Select

ExitSub

Else

GoToZ

EndIf

EndSub

3、公式自动填充

1【可保护工作表】）在D列输入数据，10列和11列自动向下填充公式，此代码在工作表中

要在工作表保护后使用自动筛选功能,需要确保:

1,保护工作表时工作表处于自动筛选模式（即显示自动筛选的下拉按钮）,

2,保护时指定允许筛选

PrivateSubWorksheet_Change（ByValTargetAsRange）

ActiveSheet.Unprotect"123"&解除工作表保护

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

ProtectPassword:

="123",AllowFiltering:

=True&保护工作表并启用自动筛选

EndSub

2【可保护工作表：

把要自动填充公式的列设置为可编辑区域】）在D列输入数据，2行4列的公式自动向下填充公式，此代码在工作表中

PrivateSubWorksheet_Change（ByValTargetAsRange）

Application.ScreenUpdating=False

Application.EnableEvents=False

k=Range（"b6556"）.End（xlUp）.Row

Cells（2,4）.CopyRange（Cells（2,4）,Cells（k,4））

Application.EnableEvents=True

Application.ScreenUpdating=True

EndSub

3）、A1单元格输入数据,F1单元格就自动填充公式:

=sum（B1:

E1）,当A2单元格输入数据,F2单元格就自动填充此公式

此代码在工作表中

PrivateSubWorksheet_Change（ByValTargetAsRange）

IfTarget.Column<>1ThenExitSub

Target.Offset（0,5）="=sum（B"&Target.Row&":

E"&Target.Row&"）"

EndSub

4、保护单元格

上面色的代码为指定区域的单元格保护

PrivateSubWorksheet_BeforeDoubleClick（ByValTargetAsRange,CancelAsBoolean）

IfIntersect（Target,[a1:

a55555,g1:

g55555,i1:

x55555]）IsNothingThenExitSub

IfTarget.Locked=TrueThen

MsgBox"此单元格已保护，不能编辑!

Cancel=True

EndIf

EndSub

给个工作表保护的示例

SUBPRO（）

withSheets（"a"） '以下带点的语句是对对象"a"工作表的操作

.UnprotectPassword:

="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 '禁止在工作表上进行任何操作

EndWith '结束对对象"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,则编号为：

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）谁有更好地方法？

解答：

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（ByValTargetAsRange）

DimrgAsRange

ForEachrgInTarget.Cells

Ifrg.HasFormulaThen

Application.EnableEvents=False

Cells（Target.Row,1）.Select

EndIf

Application.EnableEvents=True

EndSub

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函数的问题

当时有两千多人的考试成绩要与花名册挂接，考试成绩放在sheetkm1中，花名册放在sheethmc中，他们共有字段为准考证号，我的想法是根据准考证号，用vlookup函数查找相应的成绩并放在相应的人员下。

sheetkm的准考证号放在第一列，考试成绩放在第二列，查找范围是$a$2:

$b$2265，sheethmc的准考证号党在第一列。

公式为：

vlookup（a2,km!

$a$2:

$b$2265,2,false）

公式应该没什么问题，但只能找到很少的纪录（<60）,究竟是什么地方除了问题，请高手指点！

解答1：

可以用SUMIF函数解决：

　　=SUMIF（km!

$A$2:

$B$2131,A2,km!

$B2:

$B$2131）

（作者注：

将sheetkm下的所有准考证号都转化为文本，再使用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'有什么用？

能否省略？

答：

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、公式的自动填充

一张表中某几个不连续的列有公式，我不想一次全复制下去，最好是让它自动填充上一行的公式，这样用多少就自动填下去，不知道有没有办法。

解答：

DimMrow,Xrow,iAsInteger

Mrow=Range（"a65536"）.End（xlUp）.Row

Xrow=Range（"d65536"）.End（xlUp）.Row+1

Application.ScreenUpdating=False

Fori=XrowToMrow

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）

EndIf

Nexti

Application.ScreenUpdating=True

18、打开一张工作表时系统提示要求输入用户名和密码

解答：

IfApplication.InputBox（"请输入密码：

"）=""Then

Sheets（"....."）.Visible=True

Sheets（"....."）.Select

Range（"..."）.Select

Else:

c="对不起，密码不正确"

d="警告"

MsgBoxprompt:

=c,Title:

End

EndIf

19、excel的六大“条件”功能

EXCEL97/2000，除了具有强大的表格功能外，更具有强大的数据统计与处理功能，尤其是使用其“条件”功能，常常能收到事半功倍的效果，在此笔者就同大家谈谈EXCEL的条件功能（为方便起见，笔者在此以如图1包含工程基本情况的二维表格为例）。

一、条件求和。

　　1、单条件求和：

统计c1公司施工的工程总建筑面积，并将结果放在e18单元格中，我们只要在e18单元格中输入公式“=sumif（d2：

d17,"c1公司",e2：

e17）”即完成这一统计。

　　友情提醒：

如果对excel的函数不太熟悉，在单元格中直接输入公式有困难，我们可以用“插入函数”命令（或直接按工具栏上的“粘贴函数”命令按钮），选中你需要的函数后，按其提示操作即可完成公式的输入。

　　2、多

展开阅读全文