EXCELL知识.docx

EXCELL知识.docx

《EXCELL知识.docx》由会员分享，可在线阅读，更多相关《EXCELL知识.docx（18页珍藏版）》请在冰豆网上搜索。

EXCELL知识

1．在用Excel进行有关数据汇总时，往往会出现误差，即自动求和结果比实际手工求和的结果大。

　　在财务工资表中，涉及到“工资税金”的计算问题，笔者依照“工资税金”的计算方法，制定出Excel的计算公式，根据每个职工的收入项进行相关的计算后，将运算结果自动填入到每个职工的“税金”单元格内。

但在进行“税金栏目”纵向汇总求和时，却发现自动求和的结果比手工实际求和的结果多几分钱。

　　经过分析发现，在用Excel求和计算时（尤其是含有小数位的数据），一般会在单元格的“格式属性”中设置保留两位小数，而Excel的自动计算功能往往会精确到小数点后两位数以上，只不过是由于用户的设置原因，在表中看不出来而已。

这样，Excel在求和时是按精确位相加的，而不是按显示的两位小数相加，这就产生了舍入误差。

　　解决办法：

在工资表的每个职工“税金栏目”计算公式里，事先加入“四舍五入”函数，即：

工资税金=ROUND（税金计算公式,2），计算结果保留两位小数。

使得Excel在数据计算时，按“显示位”得出计算结果，避免按“精确位”相加所带来的隐性误差，从而使出现的问题迎刃而解。

2．金额分解成百十元角分的形式

解决办法：

=IF（$A2=0,"",MID（REPT（"",7-LEN（$A2*100））&"¥"&$A2*100,COLUMN（A$1）,1））

直接用7减,超过万的不行

3.利用"条件求和向导"对数据按条件进行汇总

在一个表格中经常需要将表中的数据按一定的条件进行求和计算。

如一个人事职称档案表，需要统计"助理工程师"、"工程师"、"会计师"等职称的人数各为多少。

对类似问题，利用"条件求和向导"便可很好地解决。

具体方法是选择"工具"*"加载宏"，在打开的窗口中选中"条件求和向导"，然后按"确定"按钮，调用加载宏;

选择"工具"*"向导"*"条件求和"，进入求和向导;

在求和向导中首先单击"输入"框右边的小按钮，然后在表格中直接选定包含数据的区域，再单击"下一步"，在"求和列"中选定求和数据所在列，在"条件"框中添加求和的条件，根据向导的步骤指定存放汇总结果的单元格，即将光标置于输入框中，直接在表格中选中相应单元格即可，最后单击"完成"按钮。

在汇总结果单元格中将出现预期的汇总结果，且一旦设定，汇总结果单元格会随着数据变化自动汇总，一劳永逸。

要取消汇总结果只需点击汇总结果单元格，将公式去掉即可。

当然，熟悉函数公式的用户，可直接定义单元格的公式，效果是一样的。

4．笔者于“在Word中精确绘制调整斜线表头”一文（）中曾向大家介绍过Word中绘制斜线表头的实用技巧，今天再向大家介绍一则在Excel中设置斜线表头的秘技，相信会对经常使用Excel统计数据的朋友有帮助的。

本文所介绍的方法仅限于最简单的斜线表头，即只有两个标题的斜线表头。

技术要点：

使用“单元格格式”对话框中的“边框”选项卡设置斜线，使用“对齐”选项卡结合空格键调整文字位置。

具体操作步骤如下。

1.单击工作表的的A1单元格，输入斜线表头的内容，比如本例中有两个标题“项目名称”和“日期”，先输入“项目名称”，然后按快捷键Alt+回车键，这样可以在单元格内向下换行，再按Alt+回车键一次，输入“日期”。

2.按Ctrl+回车键，这样可以在不离开该单元格的情况下选中该单元格。

这时的单元格应如图1所示。

图1选中A1单元格

3.按快捷键Ctrl+1，打开“单元格格式”对话框。

（如果在单元格上单击右键，然后从弹出的快捷菜单中选择命令“设置单元格格式”，也能打开该对话框，但使用快捷键会更快一些。

）

4.在“单元格格式”对话框中，单击“边框”选项卡，然后单击如图2所示的左斜线按钮。

图2单击左斜线按钮

5.在“单元格格式”对话框中，单击“对齐”选项卡，将文本的水平和垂直对齐方式都设置为“两端对齐”，如图3所示。

设置完毕，单击“确定”按钮，可以看到如图4所示的效果。

图3设置单元格对齐方式

图4出现斜线

6.观察表头中的文本，显然项目名称的位置不是很合适。

双击A1单元格，将插入点定位到“项目名称”左边，并按两次空格键，让“项目名称”的位置向右一些。

按Ctrl+回车键离开编辑状态并选中A1单元格，如果发现“项目名称”换行了，可以通过调整该列的宽度将其调整到合适为止，同样可以调整行宽，使表头的大小变得合适。

当然，也可以改变表头中文本的大小。

最后我们可以得到如图5所示的表头斜线效果。

图5最终的表头斜线效果

三．真正隐藏工作表杜绝Excel重要数据外泄

在平时使用Excel工作的过程中，我们常常会把一些比较重要的工作表通过菜单的“格式-工作表-隐藏”隐藏起来，然而对于稍微懂得Excel操作的人来说，这种方法简直是形同虚设，因为使用“格式/工作表/取消隐藏”就可以把隐藏的内容再次显示出来。

其实要实现真正的隐藏也不难。

　　1、选择菜单“工具/宏/VisualBasic编辑器”命令，打开MicrosoftVisualBasic程序窗口，在左侧可以看到“工程”和“属性”面板。

　　2、在“工程”面板中选择要隐藏的工作表，并在“属性”面板中设置“Visible”属性值为“2-xlSheetVeryHidden”。

如果设置为0-xlSheetHidden则为一般性隐藏，通过“格式/工作表/取消隐藏”仍可以将其取消。

　　3、选择“工具/VBAProject属性”命令，打开“VBAProject工程属性”对话框，选择“保护”选项卡，勾选“查看时锁定工程”，并设置查看工程属性的密码，单击“确定”按钮。

　　4、按下Ctrl+S保存文件，返回Excel界面即可看到工作表已经被隐藏。

此时菜单“格式/工作表/取消隐藏”也是处于灰色状态。

如此一来，即便有人知道这种操作方法，但如果没有密码的话也不能显示出隐藏的工作表，因此大大增强了Excel的安全性能。

四．使用中国式排名效果

如果有并列的名次，则下一个名次将按并列数量顺延，如有两个并列第2名，则没有第3名，而是排到第4名。

在许多情况下，这种排列方式不符合中国人的日常排名习惯。

在中国人的习惯中，无论有几个并列第2名，之后的排名仍应该是第3名，即并列排名不占用名次。

　　以下图表格为例，如果希望实现中国式排名效果，可有3种方法实现。

图1Excel表格

　　方法1：

使用COUNTIF函数

　　{=SUM（IF（A$2:

A$11>A2，1/COUNTIF（A$2:

A$11，A$2:

A$11）））+1}

　　方法2：

使用FREQUENCY函数

　　{=SUM（--（FREQUENCY（IF（A$2:

A$11>=A2，A$2:

A$11），A$2:

A$11）>0））}

　　{=SUM（--（FREQUENCY（A$2:

A$11，IF（A$2:

A$11>=A2，A$2:

A$11））>0））}

　　方法3：

使用MATCH=ROW比较法

　　{=SUM（--IF（A$2:

A$11>=A2，MATCH（A$2:

A$11，A$2:

A$11，）=ROW（$2:

$11）-1））}

五、、Excel统计成绩时的四个难题及解决

　　对于教师而言，经常需要用Excel进行学生成绩统计，会被一些常见问题难住。

碰到的难题主要有：

如何统计不同分数段人数、如何在保持学号不变前提下进行排名、如何将百分制转换成不同分数段与如何用红色显示不及格的分数等，本文着重对这些问题的解决方法与技巧加以分析和讨论。

　　本文假设读者已对Excel的基本操作已经有一定基础，已经掌握如何进行求和、求平均和如何使用自动填充柄进行复制公式等操作，本文对这些一般性操作不做详细介绍，仅对一些难度较大的操作技巧进行讨论。

　　二、Excel统计学生成绩时的四个难题

　　假设在统计学生成绩时，我们需要统计出如图1所示的相关结果。

　　在

　　图1　学生成绩统计所需要的结果图

　　这里，假设学号、姓名、成绩等列及行15都已经事先输好，需要让Excel统计其他的相关数据结果。

这时，成绩统计中主要难解决的问题及它们在图中的位置如下：

　　问题1：

如何统计不同分数段的学生人数?

（图中A16~E16）

　　问题2：

如何在保持学号顺序不变的前提下进行学生成绩名次排定?

（图中F2~F13）

　　问题3：

如何将百分制转换成不同的等级分?

（图中“等级1”与“等级2”列

　　问题4：

如何使不及格的分数以红色显示?

（图中红色显示部分，即第12行）

　　三、解决统计学生成绩时的四个难题的方法

　　下面，针对上面提出的四个难题分别讨论解决的方法与技巧。

　　1、统计不同分数段的学生人数

　　统计不同分数段的学生人数是非常常见的需求，其所需结果如图1中A16~E16所示。

这里，假设需要统计90~100、80~89、70~79、60~69及低于60分五个不同分数段的人数。

　　通常，统计不同分数段最好的方法是利用COUNTIF（X，Y）函数。

其中有两个参数，第一个参数X为统计的范围，一般最好用绝对引用;第二个参数Y为统计条件，要加引号。

　　对于小于60分的人数只要用一个COUNTIF（）函数，如在E16单元格中输入公式：

=COUNTIF（$C$2:

$C$13,"<60"）。

　　对于其他在两个分数之间的分数段的人数统计，需要用两个COUNTIF（）函数相减。

如在A16单元格中输入公式：

=COUNTIF（$C$2:

$C$13,"<=100"）-COUNTIF（$C$2:

$C$13,"<90"），即用小于等于100的人数减去小于90的人数。

　　如果要统计80~89、70~79与60~69分数段的人数，只要利用自动填充柄将该公式复制到右边三个单元格，再把"<=100"与"<90"作相应的修改，就可以得到正确的结果。

　　2.保持学号顺序不变的前提下进行成绩排名

　　学生成绩排定在学生成绩统计中经常用到。

特别要强调的是，这里所谈的方法不是一般的排序，因为那样会使学生的学号顺序发生变化。

这里所需要的是在保持学号顺序不变的情况下进行学生成绩名次排定的功能，其所需结果如图1中F2~F13所示。

　　要进行保持学号顺序不变的情况下进行学生成绩名次的排定，最好使用RANK（X，Y，Z）函数。

其中有三个参数，第一个参数X为某个学生的成绩所在单元格;第二个参数Y为整个班级成绩所在的区域;第三个参数Z是可选的，表示统计方式，若省写或写0，则成绩高的名次靠前，一般都使用这种方式，如果写1，则成绩高的名次靠后，这种情况一般较少用。

　　为了在保持学号顺序不变的前提下进行学生成绩名次排定，可以在F2单元格中输入公式：

=RANK（C2,$C$2:

$C$13,0），然后，利用自动填充柄将其复制到下方的几个单元格。

注意，这里$C$2:

$C$13用的是绝对地址，是为了保证公式在复制时此处不变，因为作为第二个参数，这里都是指整个班级成绩所在的区域，这个区域是相同的

3、将百分制转换成不同的等级分

　　将百分制转换成不同的等级分有多种不同的划分方法，其所需结果如图1中“等级1”与“等级2”列所示。

这里，“等级1”列是将百分制的分数转换成A（90~100）、B（80~89）、C（70~79）、D（60~69）与E（低于60）五个等级;“等级2”列是将百分制的分数转换成优（90~100）、良（75~89）、中（60~74）与不及格（低于60）四个等级。

具体使用哪种等级划分方法可根据实际情况自己确定。

　　在百分制转换成不同的等级分时，一般使用IF（X，Y，Z）函数。

其中有三个参数，第一个参数X为条件，不能加引号;第二个参数为条件成立时的结果，如果是显示某个值，则要加引号;第三个参数为条件不成立时的结果，如果是显示某个值，同样要加引号。

该函数可以嵌套，即在第二个或第三个参数处可以再写一个IF函数。

　　为了得到“等级1”列所要的等级结果，可以在D2单元格中输入公式：

=IF（C2>=90,"A",IF（C2>=80,"B",IF（C2>=70,"C",IF（C2>=60,"D","E"）））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　为了得到“等级2”列所要的等级结果，可以在E2单元格中输入公式：

=IF（C2>=90,"优",IF（C2>=75,"良",IF（C2>=60,"中","不及格"））），然后，利用自动填充柄将其复制到下方的几个单元格。

　　4、使不及格的分数以红色显示

　　统计学生成绩时经常需要将不及格的分数用红色显示，其结果如图1中红色显示部分（如第12行）。

　　使不及格的分数以红色显示需要使用“格式”菜单中的“条件格式”命令。

该命令会弹出一个对话框，其中要求确认条件与相应的格式。

　　对于“成绩”列，可先选中C2：

C13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“小于”，右边填写60，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

---

　　对于“等级1”列，可先选中D2：

D13，然后使用“格式”菜单中的“条件格式”命令，在弹出的对话框中，左边使用默认的“单元格数值”，中间选“等于”，右边填写E，然后单击右边的“格式”按钮，从中选择红色，最后单击两次“确定”按钮。

“等级2”列类似。

对于其他的一些统计计算要求，如怎样计算各分数段的百分比、如何计算机优良率与合格率等功能，应该比较简单，本文此处不赘述。

如何破解EXCEL工作表保护密码方法

超级无敌有用！

！

！

方法:

1\打开文件

2\工具---宏----录制新宏---输入名字如:

aa

3\停止录制（这样得到一个空宏）

4\工具---宏----宏,选aa,点编辑按钮

5\删除窗口中的所有字符,替换为下面的内容:

（复制吧）

6\关闭编辑窗口

7\工具---宏-----宏,选AllInternalPasswords,运行,确定两次,等2分钟,再确定.OK,没有密码了!

!

内容如下：

PublicSubAllInternalPasswords（）

'Breaksworksheetandworkbookstructurepasswords.BobMcCormick

'probablyoriginatorofbasecodealgorithmmodifiedforcoverage

'ofworkbookstructure/windowspasswordsandformultiplepasswords

'

'NormanHarkerandJEMcGimpsey27-Dec-2002（Version1.1）

'Modified2003-Apr-04byJEM:

Allmsgstoconstants,and

'eliminateoneExitSub（Version1.1.1）

'RevealshashedpasswordsNOToriginalpasswords

ConstDBLSPACEAsString=vbNewLine&vbNewLine

ConstAUTHORSAsString=DBLSPACE&vbNewLine&_

"AdaptedfromBobMcCormickbasecodeby"&_

"NormanHarkerandJEMcGimpsey"

ConstHEADERAsString="AllInternalPasswordsUserMessage"

ConstVERSIONAsString=DBLSPACE&"Version1.1.12003-Apr-04"

ConstREPBACKAsString=DBLSPACE&"Pleasereportfailure"&_

"tothemicrosoft.public.excel.programmingnewsgroup."

ConstALLCLEARAsString=DBLSPACE&"Theworkbookshould"&_

"nowbefreeofallpasswordprotection,somakesureyou:

"&_

DBLSPACE&"SAVEITNOW!

"&DBLSPACE&"andalso"&_

DBLSPACE&"BACKUP!

BACKUP!

!

BACKUP!

!

!

"&_

DBLSPACE&"Also,rememberthatthepasswordwas"&_

"putthereforareason.Don'tstuffupcrucialformulas"&_

"ordata."&DBLSPACE&"Accessanduseofsomedata"&_

"maybeanoffense.Ifindoubt,don't."

ConstMSGNOPWORDS1AsString="Therewerenopasswordson"&_

"sheets,orworkbookstructureorwindows."&AUTHORS&VERSION

ConstMSGNOPWORDS2AsString="Therewasnoprotectionto"&_

"workbookstructureorwindows."&DBLSPACE&_

"Proceedingtounprotectsheets."&AUTHORS&VERSION

ConstMSGTAKETIMEAsString="AfterpressingOKbuttonthis"&_

"willtakesometime."&DBLSPACE&"Amountoftime"&_

"dependsonhowmanydifferentpasswords,the"&_

"passwords,andyourcomputer'sspecification."&DBLSPACE&_

"Justbepatient!

Makemeacoffee!

"&AUTHORS&VERSION

ConstMSGPWORDFOUND1AsString="YouhadaWorksheet"&_

"StructureorWindowsPasswordset."&DBLSPACE&_

"Thepasswordfoundwas:

"&DBLSPACE&"$$"&DBLSPACE&_

"Noteitdownforpotentialfutureuseinotherworkbooksby"&_

"thesamepersonwhosetthispassword."&DBLSPACE&_

"Nowtocheckandclearotherpasswords."&AUTHORS&VERSION

ConstMSGPWORDFOUND2AsString="YouhadaWorksheet"&_

"passwordset."&DBLSPACE&"Thepasswordfoundwas:

"&_

DBLSPACE&"$$"&DBLSPACE&"Noteitdownforpotential"&_

"futureuseinotherworkbooksbysamepersonwho"&_

"setthispassword."&DBLSPACE&"Nowtocheckandclear"&_

"otherpasswords."&AUTHORS&VERSION

ConstMSGONLYONEAsString="Onlystructure/windows"&_

"protectedwiththepasswordthatwasjustfound."&_

ALLCLEAR&AUTHORS&VERSION&REPBACK

Dimw1AsWorksheet,w2AsWorksheet

DimiAsInteger,jAsInteger,kAsInteger,lAsInteger

DimmAsInteger,nAsInteger,i1AsInteger,i2AsInteger

Dimi3AsInteger,i4AsInteger,i5AsInteger,i6AsInteger

DimPWord1AsString

DimShTagAsBoolean,WinTagAsBoolean

Application.ScreenUpdating=False

WithActiveWorkbook

WinTag=.ProtectStructureOr.ProtectWindows

EndWith

ShTag=False

ForEachw1InWorksheets

ShTag=ShTagOrw1.ProtectContents

Nextw1

IfNotShTagAndNotWinTagThen

MsgBoxMSGNOPWORDS1,vbInformation,HEADER

ExitSub

EndIf

MsgBoxMSGTAKETIME,vbInformation,HEADER

IfNotWinTagThen

MsgBoxMSGNOPWORDS2,vbInformation,HEADER

Else

OnErrorResumeNext

Do'dummydoloop

Fori=65To66:

Forj=65To66:

Fork=65To66

Forl=65To66:

Form=65To66:

Fori1=65To66

Fori2=65To66:

Fori3=65To66:

Fori4=65To66

Fori5=65To66:

Fori6=65To66:

Forn=32To126

WithActiveWorkbook

.UnprotectChr（i）&Chr（j）&Chr（k）&_

Chr（l）&Chr（m）&Chr（i1）&Chr（i2）&_

Chr（i3）&Chr（i4）&Chr（i5）&Chr（i6）&Chr（n）

If.ProtectStructure=FalseAnd_

.ProtectWindows=FalseThen

PWord1=Chr（i）&