中国式的排名 函数应用
RANK 是个排名函数 但有一个问题象上面有两个100分 也就是两并列第一 排名就从第三名开始没有第二名 也许这个美国式的排名 中国式的排名 前面有两个一样的分数并列第一后是 第二名 怎样用函数实现.
解答：在B3中复制如下的数组公式，然后往右复制：
{=SUM(IF(B1=LARGE(IF(LARGE($B$1:$H$1,COLUMN($A$1:$G$1))=IF(ISERROR(LARGE($C$1:$I$1,COLUMN($A$1:$G$1))),0,LARGE($C$1:$I$1,COLUMN($A$1:$G$1))),0,LARGE($B$1:$H$1,COLUMN($A$1:$G$1))),COLUMN($A$1:$G$1)),COLUMN($A$1:$G$1),0))}
也可换个方式不用数组，在B3中复制如下的公式，然后往右复制：
=RANK(B1,$B1:$H1)+COUNTIF($B$1:B1,B1)-1
当做日报表累计数自动加
当做日报表时,怎样让月累计数自动加上? 就是要月累计自动加上今天的当日收入数,今天只输入当日收入,我想用用前一天的月累计数加上今天的当日收入数为今天的月累计数.情况是一月一个工作薄,每一个工作薄下30个工作表,用每一天的日期为报表名.我想把月累计的公式写为 =sheet17!c5 中的 17 用 day(now())-1 的值去取代他,该怎么办呢? 以下为报表格式,谢谢大家帮我想一想.
部门-------当日收入 -----------月累计
团队收入 12.12 123.00 /写字间收入 147,258.00 147.147/房内吧收入 147,258,369.00
解答：=SUM('Sheet1:Sheet30'!C5)
在你需要月汇总的单元格填入上述公式，其作用是将工作表1到工作表30的“C5”单元格的值全部累加起来，而“C5”单元格应填入当日的收入数。
又问：月累计的公式写为 =sheet17!c5 中的 17 用 day(now())-1 的值去取代他,该怎么办呢? ”
答：用公式： =INDIRECT(ADDRESS(1,1,1,1,sheet&DAY(NOW())-1))
工资条问题
职工工资构成非常复杂，往往超过10项，因此每月发工资时要向职工提供一包含工资各构成部分的项目名称和具体数值的工资条。打印工资条时要求在每个职工的工资条间有一空行便于彼此裁开。本模板就是用EXCEL函数根据工资清单生成一便于分割含有工资细目的工资条表格。
本工资簿包含两张工资表。第1张工资表就是工资清单，称为清单。它第一行为标题行包括职工姓名、各工资细目。
第2张工作表就是供打印的表，称为工资条。它应设置为每三行一组，每组第一行为标题，第二为姓名和各项工资数据，第三行为空白行。就是说整张表被3除余1的行为标题行，被3除余2的行为包括职工姓名、各项工资数据的行，能被3整除的行为为空行。
在某一单元格输入套用函数=MOD(ROW(),3)，它的值就是该单元格所在行被3除的余数。因此用此函数能判别该行是标题行、数据行还是空行。
在A1单元格输入公式=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(),3)=1,清单!A$1,value-if-false))并往下填充，从A1单元格开始在A列各单元格的值分别为清单A1单元格的值即姓名、value-if-false、空白，姓名、value-if-false、空白，......。其中value-if-false表示MOD(ROW(),3)既不等于0又不等于1时，即它等于2时应取的值。它可用如下函数来赋值：INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())。INDEX()为一查找函数它的格式为：INDEX(reference,row-num,col-num),其中reference为查找的区域，本例中为清单表中的A到G列，即函数中的清单!$A:$G，row-num为被查找区域中的行序数即函数中的INT((ROW()+4)/3)，col-num为被查找区域中的列序数即函数中的COLUMN()。第2、5、8.......行的行号代入INT((ROW()+4)/3)正好是2、3、4......，COLUMN()在A列为1。因此公式=INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())输入A列后，A2、A5、A8......单元格的值正好是清单A2、A3、A4......,单元格的值。这样，表的完整的公式应为=IF(MOD(ROW(),3)=0,,IF(MOD(ROW(),3)=1,清单!A$1,INDEX(清单!$A:$G,INT((ROW()+4)/3),COLUMN())))。把此公式输入A1单元格，然后向下向右填充得到了完整的工资条表。
为了表格的美观还应对格式进行设置，一般习惯包括标题、姓名等文字在单元格中要取中，数字要右置，数字小数点位数也应一致，还有根据个人的爱好设置边框。本表格只需对一至三行的单元格进行设置，然后通过选择性格式设置完成全表的设置。
本工作簿的特点是1、不对清单表进行操作保持清单工作表的完整，2、全工作表只有一个公式通过填充得到全表十分方便。
例如：我的单位不大不小，有200多号人。最近领导要求把每个员工个人的工资情况打出来，分发给每位员工。每个员工的工资条上只能有两行内容：一行是分解的项目内容，如基本工资、岗位工资、总计等等；另一行是对应第一行的具体工资数额。
可以这样解决：Sheet2
1.为A1命名为K
2.为A3:A250命名为XX
从A3贴上主索引,数据要连续中间不允许有空格
3.写公式=VLOOKUP(K,DATA,2,0)
有几个字段写几个,位置随您高兴摆
4.隐藏A栏
**************************************************************************************
Sub 打印()
Application.ScreenUpdating = False '屏幕不更新
Dim c As Object '宣告c为对象,请准备空间
[xx].Select '选取变量范围
Set c = ActiveCell '设定c对象为作用单元格
Do Until IsEmpty(c.Value) '作Do循环直到无值时跳出
[k].Value = c.Value
Set c = c.Offset(1, 0) '设定c往下进一格再取主索引值
Sheets(Sheet2).PrintPreview '工作表直接打印改PrintOut
Loop
End Sub
另一回答：我是做人事管理的也遇到过你的问题,我用如下方法解决十分方便,而不用任何代码.
方法的原理是调整打印机的自定义纸张大小到恰好显示一个人的工资条的大小,请按如下：
如：我的excel工资表将项目内容放在第一、二行，行高为20.1，用a4纸横向打印
1、在页面设置中将上、下边距，页眉、页脚均设为零，
在页面设置--工作表---顶端标题行 中输入 $1:$2,即将放在
第 一、二行的项目内容设成每页标题行
打印方向为横向
2、、在文件----打印---属性---纸张----自定义中将纸张的
宽度=280 （单位：毫米）
长度=2970 （单位：毫米）
3、打印时可选1---200页，即可打印200人的工资条，一张a4可打10人
确定后预览，可调整下边距至每页显示一张工资条
我的解决办法：我只用了一个公式: if(mod(row(),3)=0,,if(mod(row)(),3)=1,sheet1!a$1,index(sheet1!$a:$g,int((row()+4/3),cllolumn())))你试一下
(解释：int((row()+4/3) 是这个意思：一个工资表，有列标题，接下来是工资记录。而我在此表基础上，加一个自动生 成的工资 条表， mod(row(),3)=0，在此表上用这个表示第三行保留空白行； if(mod(row)(),3)=1,sheet1!a$1，表示是第一行取标题列； index(sheet1!$a:$g,int((row()+4/3),column()）这是关键的地方：是指它不是第一行，也不三倍数的行，是记录 行的表示，你想第二行显示记录，则2+4/3=2 取工资 表的第二行记录；第五行显示记录，则5+4/3=3 取工资 表的第三行记录；第八行显示记录，则8+4/3=4 取工资 表的第四行记录；第十一行显示记录，则11+4/3=5 取工资 表的第五行记录；这个公式你可以根据具体情况变化：尤其是((row()+4/3),中的4这个数字，
定制单元格数字显示格式
定制单元格数字显示格式，先选择要定制的单元格或区域，》单击鼠标右键》单元格格式》选择‘数字’选项》选择‘自定义’》在“类型”中输入自定义的数字格式。
如何输入自定义的数字格式：需要先知道自定义格式中那些常用符号的含意，具体可以先不选择‘自定义’，而选择其它已有分类观看‘示例’，以便得知符号的意义。
比如：先选择‘百分比’然后马上选择‘自定义’，会发现‘类型’中出现‘0.00%’，这就是百分比的定义法，把它改成小数位3位的百分比显示法只要把‘0.00%’改成‘0.000%’就好了，把它改成红色的百分比显示法只要把‘0.00%’改成‘[红色]0.00%’就好了。
关于数据引用的问题
在一个工作簿中，假如A工作表中的单元格E8被B工作表中的某单元格引用，现在由于A工作表中插入了行，原来的E8现在可能是E28,结果造成B工作表引用数据错误，请问：如何可以使B工作表中的引用随着A表的变化也作相应的自动调整？
回答：利用“相对引用”来实现，“相对引用”是Excel中默认的引用方式
例：在工作表Sheet1中Ｃ２单元格为５，Ｄ２单元格为６，在工作表Sheet2中Ｃ３单元格中输入“＝Sheet1!C2+Sheet1!D2”，如果把
Ｃ２单元格剪切到Ｃ３，那么在工作表Sheet2中Ｃ３单元格中的公式就自动变为“＝Sheet1!C3+Sheet1!D2”
如何使EXCEL应用程序锁定不让人打开
请问我以下的操作应再如何修改成如密码输入错误则退出EXCEL.这是我编写在PERSONAL.XLS中的一个模块.代码如下,请各高手帮忙为小弟指点一二,不甚感激!
Sub auto_Open()
MsgBox 热列欢迎来海源,你吃饱了吗？
If Application.InputBox(请输入操作权限密码:123) = 123 Then
Else
'.....(在这一步中,我想退出EXCEL,但无法实现请高手指点一二)
End If
End Sub
解答：Private Sub Workbook_Open()
MsgBox 热列欢迎来海源,你吃饱了吗？
If Application.InputBox(请输入操作权限密码:123) = 123 Then
Exit Sub
Else
Application.Quit
End If
End Sub
程香宙的修改：
Sub auto_Open()
MsgBox 程香宙欢迎你的到来, vbQuestion, 联系电话：
If Application.InputBox(请输入操作权限密码:, 系统登陆) = 123 Then
Else
MsgBox 密码错误,请重输, vbCritical + vbOKOnly, 你还有两次机会
If Application.InputBox(请输入操作权限密码:, 系统登陆) = 123 Then
Else
MsgBox 密码错误,再给你一次机会!, vbCritical + vbOKOnly, 你还有一次机会
If Application.InputBox(请输入操作权限密码:) = 123 Then
Else
MsgBox 你无权进入本系统!请向程香宙申请密码!, vbCritical + vbOKOnly, 你没有机会啦!
Application.Quit
End If
End If
End If
End Sub
数组的运算法则
有excel表如下：
姓名 成绩 /王娟 优 /永生 中 /闵生刚 优 /朱智锐 中 /胡强强 良 /金龙鳞 优 /张正梅 中 /汪欲生 良 /闵生刚 中 /王娟 优 /张正梅 优 /闵生刚 中 /永生 良 /王娟 良 /其中姓名在sheet1页的a列，成绩在c列。我想在sheet2页中建立一个表如下：
姓名 统计优数 /胡强强 /金龙鳞 /闵生刚 /汪欲生 /王娟 /永生 /张正梅 /朱智锐 /同样姓名在sheet1的a列，统计在c列，这里的姓名已经整理为没有重复的姓名。要统计出每人获得优的数目。请问用什么函数能解决。我用了if((sheet1!c2)=优,countif(sheet1!a:a,a2)),结果是统计的姓名数，而不是成绩数。如果能统计出来，哪么就是一个动态的当sheet1中数据变化时，sheet2中数据应到跟着变化。
有位朋友指导采用下面的式子把上面的问题解决了。这里假设最大记录数为100
=sum((sheet1!$a$2:$a$101=$a2)*(sheet1!$c$2:$c$101=优)*1) ，按Ctrl+Shift+Enter ，但是这是用到了数组运算，请问数组运算的规则是什么，看到许多地方都可以用数组解决，但不知其所以然。帮助文件中也没说运算规则。如上式中为什么用*号？谢谢
解答：对于数组公式的含义
sum((sheet1!$a$2:$a$101=$a2)*(sheet1!$c$2:$c$101=优)*1)
我们来一部分，一部分的讲：
1、(sheet1!$a$2:$a$101=$a2)
表示用 sheet1!$a$2:$a$101 区域中的每一个单元格中的内容与 $a2 单元格的内容进行比较，如果相同结果为“True”，否则为“False”。
2、(sheet1!$c$2:$c$101=优)
表示用 sheet1!$c$2:$c$101 区域中的每一个单元格中的内容与 字符串优进行比较，如果相同结果为“True”，否则为“False”。
3、最后一部分乘以1。是强制Excel将“True”或“False”转换为数值“1”或“0”，以便sum函数可以求和。
4、至于第一部分和第二部分之间的乘号（*)的目的是，如果第一部分或者第二部分有一个的结果是“False”，那么Excel将其转换为数值“0”，相乘结果为零，表示不在求和范围内。
替换数据
请教各位如何用将一组数据，如：6550894， 9852547， 2656032， 7461136， 0505867， 5564892， 7235580，0421077，我需要把数据中的数字1，3，5换为符号A表示，2，4，6换为符号B表示，依此类推将数据中的阿拉伯数字0~9分为几类用其它符号替换。
解答：方法1。假设：B13值为9550894。在B14中输入=IF(ISERROR(FIND(MID($B$13,1,1),135)),IF(ISERROR(FIND(MID($B$13,1,1),246)),IF(ISERROR(FIND(MID($B$13,1,1),79)),IF(ISERROR(FIND(MID($B$13,1,1),80)),,D),C),B),A) 。 C14中MID()第二个参数为2，以此类推...最后在目标单元格中输入：=CONCATENATE(B14,C14,D14,E14,F14,G14,H14)
方法2：表一:
AB...
11234567890
2ABABABCCCD
=SUBSTITUTE(A4,A$1,A$2)
说明:先列一个替换表,如表一,在A4处填如数据,在B4处填如上述公式=SUBSTITUTE(A4,A$1,A$2),并向右拖动9个同样的公式,最后一个便是结果.在将该10个相同的公式向下拖,便得到其它的结果.好处:可以修改表一,产生变化.
方法3：你可以把全部数据拷贝到WORD中，再用替换命令，想怎么换就怎么换，然后在拷贝回来。
几个技巧
用“Ctrl+：”输入时间 ；用“Ctrl+；”输入日期 ；用“Ctrl+`”显示当前工作表的单元格引用情况（如果引用的话），再次按下“Ctrl+`”则回到正常的显示状态（别漏了那个点）；“Ctrl+1”：打开“单元格格式”对话框，按下ESC键关闭该对话框 ；“Ctrl+ -”：打开“删除”对话框，按下ESC键则关闭该对话框；热键ctrl+2：字体加粗或取消加粗； ctrl+3：字体加斜或取消加斜； ctrl+4：加下滑线或取消； ctrl+5：加删除线或取消； ctrl+9：隐藏当前行； ctrl+0：隐藏当前列；在Cell里，输入公式，比如 =trunc，按CTRL+SHIFT+A，出现函数参数说明；按CTRL+A，出现wizard 。快速填充：选择要填充的单元，输入公式或数值后，按CTRL+Enter。将图形与某个cell的数据联系在一起：a) 选择绘图菜单栏的任一图形 b) 在公式栏里，或按F2，输入到某个cell的联接，比如=A6 c) 回车。粘贴链接图片：a) 选择某区域(比如A2:C7) b)复制 c)按住 SHIFT，点选编辑-->粘贴链接图片 。对长公式进行错误查找：点击公式，按F9，出现出错的部分。ESC复原，CTRL+Z为undo。
如何在两个工作表之间进行数据交换
我有两个工作表，第一个表A列是姓名，B列是编号，第二个表A列也是姓名，但是顺序和第一个表的A列不一样，我想在第二个表的B列也加入编号 。
解答：用VLOOKUP函数： =VLOOKUP(A2,Sheet1!A:B,2,FALSE)，依次向下拖动
显示数值所在的单元格号
假如有A列和B列两列数字，如何找出A列的数字在B列中所在的位置， 并在第三列显示单元格号。
解答：假设数据在A1:B10，则C列公式为：=MATCH(B1,$A$1:$A$10,0)
我想根据题意应该为：=MATCH(A1,$B$10,0)
if 超过7层如何办
1. 将七层之外的IF语句，放在另外的单元格内来处理，例：C5=if(if,...,(if...),B5))),B5单元格就是存放七层之外的IF语句。依此类推，可以实现在数据库语言中CASE语句的功能。
2. IF 函数的确有七层嵌套的限制。遇到七层嵌套还解决不了的问题，可以尝试用其它的函数组合和数组公式来解决；有时用 VBA 方案可以有很好的效果。
这里给出一个解决IF函数嵌套超出范围的方法，可能比较容易使初学者看懂。其思路是：一个单元格做不了的事，分给两个或更多的单元格来做，文字内容是这样，函数内容也是这样。
例子：假如 A1＝1，则 B1＝A；A1＝2，则 B1＝B …… A1＝26，则 B1＝Z
解决方法如下：
B1 = IF(A1=1,A,IF(A1=2,B,IF(A1=3,C,IF(A1=4,D,IF(A1=5,E,IF(A1=6,F,IF(A1=7,G,IF(A1=8,H,C1))))))))
C1 = IF(A1=9,I,IF(A1=10,J,IF(A1=11,K,IF(A1=12,L,IF(A1=13,M,IF(A1=14,N,IF(A1=15,O,IF(A1=16,P,D1))))))))
D1 = IF(A1=17,Q,IF(A1=18,R,IF(A1=19,S,IF(A1=20,T,IF(A1=21,U,IF(A1=22,V,IF(A1=23,W,IF(A1=24,X,E1))))))))
E1 = IF(A1=25,Y,IF(A1=26,Z,超出范围))
根据情况，可以将 C、D、E 这些从事辅助运算的单元格放在其它任何地方
3. 一个单元格也可以实现
=IF(A1=1,A,IF(A1=2,B,IF(A1=3,C,IF(A1=4,D,IF(A1=5,E,IF(A1=6,F,IF(A1=7,G,IF(A1=8,H,))))))))&IF(A1=9,I,IF(A1=10,J,IF(A1=11,K,IF(A1=12,L,IF(A1=13,M,IF(A1=14,N,IF(A1=15,O,IF(A1=16,P,))))))))&IF(A1=17,Q,IF(A1=18,R,IF(A1=19,S,IF(A1=20,T,IF(A1=21,U,IF(A1=22,V,IF(A1=23,W,IF(A1=24,X,))))))))&IF(A1=25,Y,IF(A1=26,Z,))(数组形式输入)。
4. 以一例：a1=1,2,3,4,5,6,7,8,9,10
b=if(a1=1,一,if(a1=2,二,if(a3=3，三,.......if(a1=9,九,if(a1=10,十)))))),if超过7层不起作用，我该如何办
解答：1、b=if(a1>5,if(a1=6,六。。。。。。))，明白意思？就是截为两段再做判断，这样可以不超过7重。
2、可以用自定义数字格式。也可以用=CHOOSE(A1+1,一二三四五六七八九十)
问：实际上我的要求是现行高一成绩统计中:b=if(a1=语文,语文,if(a1=数学,数学,if(a1=英语,英语,if(a1=物理,物理,if(a1=化学,化学,if（a1=历史,历史,if(a1=政治,政治,if(a1=生物,生物“，if(a1=地理,地理)))))))),这样超过了7层。我不知如何处理。因为下面的公式要引用语、数、英、等。
答：新建一表，取名Data，找一区域设置名称为SubjectTable：
语文 Chinese
英语 English
..
=vlookup(SubjectTable,a1,2,false)可以有65536个，够了吧。其实，稍加改进，理论上，可以有达到你硬盘空间的个数。或用if和or的组合可以解决15个。
再举个例子：
=IF(A16=,,IF(B16=,样办尚未交,IF(OR(B16=内部检查中,B16=数据查询中,B16=数据查询中),CONCATENATE(IF(B16=内部检查中,品质检测中,),IF(B16=数据查询中,图纸未确认,),IF(B16=为不合格,需要修正,)),CONCATENATE(IF(C16=客户检查中,待客回复,),IF(C16=合格,待P/O生产,),IF(C16=取消,客户取消,),IF(C16=为客户设变中,客户设变中,),IF(C16=不合格,需要修正,)))))
一个单元格内格式问题
如果我做了一个表某一列是表示重量的，数值很多在1--------------1524745444444之间的数不等。这些表示重量的数。如果我想次给他们加上单位，但要求是单位是>999999吨，之下>999是千克，其余的是克。如何办
答：[>9999]###.00,吨;*,*.00千克
怎样用函数求出一定范围内的数值的偶数的个数
解答：1设你的数据区域为A1：A30
{=COUNT(IF(A1:A30/2/2=INT(A1:A30/2),A1:A30))}
如果你的数据区域内还包括空白格你不想计算在内的话， {=COUNT(IF(A1:A30/2/2=INT(A1:A30/2),A1:A30))-COUNTBLANK(A1:A30)}
或：{=sum((even(a1:a30)=a1:a30)*1)}
如何使某列的数据不重复
1、 我做了个宏，可惜在数据量大时（超过1000时）速度狂忙，各位帮我修改修改
Sub 检查重复项()
Dim i As Integer
Dim j As Integer
num = Selection.Cells.Count
For i = 1 To num
For j = i + 1 To num
If (Selection.Cells(i) = Selection.Cells(j)) Then
MsgBox Selection.Cells(i).Value
End If
Next j
Next i
MsgBox 检索完毕
End Sub
2、 假设数据在B列
选中B列，[数据]-[数据有效性]-[自定义]
在“公式”输入框中键入：=COUNTIF(B:B,B1)=1。
请问如何能使随机数不重复
我在30个单元格里使用了randbetween(1,500),随机抽取了1-500之间的任意数,但问题是这30个数中仍有机会出现重复请问如何才能使这些随机数不重复呢?
答：1、勾选迭代计算，A1输入如下公式： =IF(COUNTIF($A$1:$E$6,A1)=1,A1,RANDBETWEEN(1,500))
如果某格出现0值，点选旁边单元格拖一下即可。
2、用上面的方法得到的随机数不会变化，如何做到想变就变？增加一个条件语句，即可达到此效果，公式如下： =IF(COUNTIF($A$1:$E$6,A1)=1,IF($G$1=1,RANDBETWEEN(1,500),A1),RANDBETWEEN(1,500)) G1为控制格，当在G1输入数字1，就开始变。变化后的数据有可能不惟一了，怎么办？把1清除即可！
日期型编号
想在单元格里输入1，产生0207121这样一个数字，02是2002年，07是7月份，12是12日，1是输入的数值
答：如a1输入1，要在b1生成你要的数字，你可以在b1设公式=text(today(),yymmdd)&a1
如何将数字改变为字符串
答：=TEXT(A1,@)
在Excel中如何自定义数字格式
虽然Excel为用户提供了大量的数字格式，但还是有许多用户因为工作、学习方面的特殊要求，需要使用一些Excel未提供的数字格式，这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。
一、在Excel中创建自定义数字格式的方法
1、选择要设置格式的单元格或单元格区域。
2、单击“格式”菜单中的“单元格”命令，然后单击“数字”选项卡。
3、在“分类”列表中，单击“自定义”选项。
4、在“类型”框中，编辑数字格式代码以创建所需的格式。
在Excel自定义数字格式使用如下的表达方式：
正数的格式 负数的格式 零的格式 文本的格式
#,##0.00 [Red]-#,##0.00 0.00 TEXT@
在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。如果在表达方式中要跳过某一节，则对该节仅使用分号即可。
二、自定义数字格式实例
(一)自动添加文本
在日常财务工作中，常常需要在金额数字后加单位“元”，这时就可以使用“0.00元”的自定义数字格式，当用户在单元格中录入数字后，Excel就会自动在数字后加上单位“元”。
如果需要将单元格中录入的数字“32”自动转换成“高三十二班”格式，只要将单元格的数字格式定义为“高[DBNum1]G/通用格式班”即可。
(二)在自定义数字格式中使用颜色
要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。Excel中可以使用的颜色名称有[黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、[黄色]八种不同的颜色，此外Excel还可以使用[颜色X]的方式来设置颜色，其中X为1-56之间的数字，代表了56种不同的颜色。
例如：当用户需要将单元格中的负数数字用蓝色来表示，只要使用“#,##0.00;[蓝色]-#,##0.00”自定义数字格式，用户在单元格中录入负数时，Excel就会将数字以蓝色显示。
(三)在自定义数字格式中使用条件格式
在Excel自定义数字格式中用户可以进行条件格式的设置。当单元格中数字满足指定的条件时，Excel可以自动将条件格式应用于单元格。Excel自定义数字格式中可以使用如下六种标准的比较运算符：
运算符 含义
= 等于
> 大于
< 小于
>= 大于等于
<= 小于等于
<> 不等于
在Excel中要想设置满足指定条件数字的格式，在自定义数字格式代码中必须加入带中括号的条件，条件由比较运算符和数值两部分组成。
例如：在学生成绩工作表中，当我们想以红色字体显示大于等于90分的成绩，以蓝色字体显示小于60分的成绩时，其余的成绩则以黑色字体显示，这时只需将自定义数字格式设置为“[红色][>=90];[蓝色][<60];[黑色]”即可。
值得注意的是，当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时，你会发现在“单元格格式”的“自定义”分类类型中找不到“[红色][>=90];[蓝色][<60];[黑色]”格式，这是因为Excel自动将你所创建的“[红色][>=90];[蓝色][<60]”格式修改成“[[红色][>=90]G/通用格式;[蓝色][<60]G/通用格式;[黑色]G/通用格式”，你只需选择此格式即可达到同样的使用效果。
(四)隐藏单元格中的数值
在Excel工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;;;”(三个分号)的自定义数字格式就可达到此目的。这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元格正常引用。
在单元格显示负数为红字后又紧跟汉字
解答：1、这个使用宏可以解决，但单元格开始请不要带单位。单个单元格宏代码如下：
Sub 宏1()
'
' 宏1 Macro
' Dent 记录的宏 2002-2-16
'
Dim str1 As String
str1 = Str(ActiveCell.Value)
If Val(str1) < 0 Then
str1 = Abs(Val(str1)) & 文字
ActiveCell.Value = str1
ActiveCell.Font.Color = vbRed
End If
End Sub
2、用单元格格式解决： #,##0.00;[红色]-#,##0.00文字
检索问题
在一个工作表中，有客户名称、收款日期等字段名及相应数据。现在想为了检验收款日期是否到期，要求在工作表的某一空白单元格中键入某一日期 ，则此单元格以下的单元格可以显示收款日期字段中所有在此日期前的日期清单，请问如何实现？并要求不使用筛选功能。
解答： Sheet2!A1命名为X,键入日期
Sheet1:客户名称、收款日期等字段名及相应数据
插入名称定义：XX=OFFSET(Sheet1!$A$2,,,MATCH(X,Sheet1!$B$2:$B$65536)-1,2)
XX即是您所需要之范围
vba写在This Workbook
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
On Error GoTo 1: Target.Select
Range(Target.Value).Copy
ActiveSheet.Paste Link:=False
Application.CutCopyMode = False
1
End Sub
**********************************************************************************
Sheet2任意储存, 格键入xx按ENTER
[比较一下并没有比筛选功能快],反而复杂
SHEET2随便贴张图
键入公式=XX
微软到底对我们隐藏了多少
NumberString 这个函数是否一直存在但没有解释？NumberString(1234567890,1)就像自定格式[DBNum1]一样，它还有2的选择。其实这些特别用途的函数是否中文板设计时特别隐藏。测试结果：
NumberString(1234567890,1) ＝ 一十二亿三千四百五十六万七千八百九十
NumberString(1234567890,2) ＝ 壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
NumberString(1234567890,3) ＝ 一二三四五六七八九○
DateString()相信亦是一特别Lotus函数。
DATESTRING(23-Sep- 2002) = 2002年09月23日
DATESTRING(23Sep2002) = 2002年09月23日
DATESTRING(9/23/2002) = 2002年09月23日
DATESTRING(9-23-2002) = 2002年09月23日
求一组数中第一个出现的正数
单元格A1—G1中存放一组数，如何使A2的值等于这组数中第一个出现的正数（按A1—G1的顺序），若无正数则A2＝0.
解答：=IF(A1<=0,IF(B1<=0,IF(C1<=0,IF(D1<=0,IF(E1<=0,IF(F1<=0,IF(G1<=0,,G1),F1),E1),D1),C1),B1),A1)
或：{=IF(COUNTIF(A1:G1,>0)=0,0,INDIRECT(ADDRESS(1,SMALL(IF(A1:G1>0,COLUMN(A1:G1)),1))))}
能否使表格不能打印
解答：Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub
(用法：打开Excel,按Alt+F11,调出VBE后,按Ctrl+r显示出工程资源管理器(默认此窗口应已显示),双击ThisWorkbook,在模块中粘入代码即可!要想打印你表,用截图软件即可.无最保险办法)。
有无这样的函数
A1为一定范围内的随机值，B1—B20为一组数，有无这样的函数来确定A2的值？
当A1=1时，A2=B1
当A1=2时，A2=B2
当A1=3时，A2=B3
…………
解答：=INDIRECT(ADDRESS(A1,2))(绝)
能实现两栏数据中按特定条件进行比较并做出相应记号吗
具体意思如下：有四栏数据。A栏 B栏 C栏 D栏 F栏
对A栏及C栏数据进行查找对比，如果发现存在A=C，且B=D，则在符合条件的F栏显示已找到，对于没有查到的的显示未找到。
解答：如果是一一对应查找则f2=if(and(A2=C2,B2=D2),OK,No),复制.
如果整体查找：=IF(AND(COUNTIF($C$2:$C$5,A2)>0,COUNTIF($D$2:$D$5,B2)>0),找到,没有)
如何对文本格式的数字进行筛选
如80/24；73/56……
解答：要对A列进行筛选，条件1:符号/左边的数字>=50;条件2:符号/右边的数字>=80,达到任一条件的即被选中。试试这个公式: B1=IF(OR(LEFT(A1,FIND(/,A1)-1)*1>=50,MID(A1,FIND(/,A1)+1,LEN(A1)-FIND(/,A1))*1>=80),A1,)
我想再请教一个问题:在自动筛选中有包含、不包含之类的选项,这类选项在高级筛选中能否实现?
提供以往一个函数公式,请你研究看是否有参考:
={SUM((ISNUMBER(SEARCH(广东,$B$3:$B$13)))*($A$3:$A$13=股份制)*($C$3:$C$13))}
这是对B列单位名称中包含广东并且A列企业类型为股份制的,按C列汇总.
如何筛选出特定行
一份工作表，有几万行数据，现在想每隔50行打印一行，如何进行筛选
解答：插入一空列,输入=MOD(ROW(),50)并复制到全数据列,再用筛选法把有“0”的单元格选出.
Word加启动密码
在Word模板中录制如下宏文件保存即可:
Sub autoexec()
Dim user
user = InputBox(请输入软件启动密码)
If user = password Then
Else
MsgBox (非法用户,将退出本程序.Wu)
Application.Quit
End If
End Sub
有关大小写的转换
在EXCEL单元格中有的单元格是小写字母，有的是大小写混在一起现在我想全部转为大写、或全部转为小写，有哪些方法呢？
利用upper()函数或还有LOWER。如：a1=aBcD，upper(a1)=ABCD
在Excel中打造自己的“公式保护”菜单
工作中经常用到Excel的计算功能，有时一张工作表中需要设置很多公式，为了防止误操作就将工作表保护起来，但是如果将含有公式的单元格保护，而其它单元格不锁定，设置时需要一个个单元格选定，很麻烦而且容易出错，就想到建立一个菜单项，将这项工作变成菜单。有幸的是这一切Excel都能完成，具体操作如下：
先将工作表保护，不必输入密码。
再录制一个宏。方法是：打开“工具”栏，单击“宏”-“录制新宏”；将其命名为“公式保护”，单击“保存在”的下拉箭头，将新宏保存到“个人宏工作簿”。单击“确定”录制开始。（注意此后的每一动作都将被录制，直至“停止录制”。）
1. 将工作表取消保护；
2. 选定a1单元格，输入=测试（注：黑体为实际输入内容，标点符号均为英文输入法状态,下同）；
3. 单击工作表左上角的方框选定整个工作表；
4. 单击“格式”-“单元格”-“保护”项，将“锁定”和“隐藏”前的方框里的对号去掉；
5. 单击“插入”-“名称”-“定义”，当前工作表名称输入：公式保护，引用位置输入：=get.cell(4,indirect(rc,false)), 单击“添加”后“确定”；
6. 单击“格式”-“条件格式”，在“单元格数值”下拉框选定“公式”，后面的框内输入=公式保护 ，单击“格式…”，在图案标签内选浅绿色或者其它颜色“确定”，再“确定”；
7. 单击 “编辑”-“定位”-“定位条件…”，选定“公式”项“确定”再“确定”；
8. 重复步骤4，将“锁定”和“隐藏”前的方框里的对号选定；
9. 选定a1单元格，取消其内容，并重复步骤4，取消a1的锁定和隐藏属性；
10. 单击工具-保护-保护工作表，将工作表保护（也不必设密码）。再单击“工具”-“宏”-“停止录制”停止录制。再打开“工具”-“自定义”项，再“命令”标签栏内“类别”栏中找到“新菜单”并指定，在其右侧的命令框中的新菜单用鼠标按住拖到“工具栏”的“保护”项中的“保护工作表”下面放开，并单击右键将其命名为“公式保护”，将刚才录制名为“公式保护“的宏指定给它。至此，一个很有用的菜单项就作成了。此后，只要你将鼠标移动到“工具”-“保护”-“公式保护”的位置，工作表将执行其所指定的宏，只要你工作表中输入了公式（以=开头），含有公式的单元格将自动变为浅绿色，提醒你和别人此处有公式，小心编辑，十分醒目。编辑完公式后再次运行该命令就可以此保护工作表并锁定公式，禁止改动。
如何让EXCEL自动从头统计到当前单元格
情况如下： C列要根据A列的内容来统计B列的数据，范围从A1：An，即当A列中An有数据时，Cn自动根据An的值，统计B1：Bn的数据。
解答：=SUM(INDIRECT(B1:B & LARGE((A1:A65535<>)*(ROW(A1:A65535)),1)))，按Ctrl+Shift+Enter。
请问想要取当前单元格的列号，用什么函数
1、=CHAR(64+COLUMN())
2、Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Selection.Columns.Column > 26 Then
tt = Mid(ActiveCell.Address, 2, 2)
Else
tt = Mid(ActiveCell.Address, 2, 1)
End If
MsgBox (tt)
End Sub
3、1的公式就变化一下：=IF(COLUMN()>26,CHAR(64+INT(COLUMN()/26)) & CHAR(64+MOD(COLUMN(),26)),CHAR(64+COLUMN()))
SUMIF函数
a1至A4是10,24,30,12.B1至B4是8,15,25,35.D1至D4是25,4,3,5.用SUMIF 第一参数选取A1:B4,第二参数是>20,第三参数选取D1:D4.它得出是7。它判断的是A1:A4 。我的要求是B1:B4 也跟着判断 , 也就要得出来得是12
解答：{=SUM((A1:A4>20)*(B1:B4>20)*C1:C4)}
怎么能快速的将两个单元格的内容互换
1、
Sub ChangVal()
my1value = ActiveCell.Value
For Each a In Selection
If a.Address <> ActiveCell.Address Then
my2value = a.Value
a.Value = my1value
ActiveCell.Value = my2value
End If
Next a
End Sub
2、用鼠标先选定单元格，点住单元格边框，并按住SHIFT键，然后托拽到隔壁单元格的后面一条边框处。你能看到被托拽部分会变成灰色的“工”字形，然后放手后，单元格就互换了。
如何能到两个时间段的17:00-8:00小时数?
1.已知E4为08011500(为文本格式﹐开始时间﹐意思为8月1日 下午15﹕00)﹐G4为08100900(为文本格式﹐结束时间﹐意思为8月10日 上午9点) 问﹕如何能得到开始时间到结束时间（17:00-08:00）的总小时数?
2. 如何得到开始时间到结束时间的节假日时数?(如5.1﹐10.1﹐星期六﹐星期天)
解答：方法1：=(DATE(YEAR(NOW()),MIDB(AF4,1,2),MIDB(AF4,3,2))-DATE(YEAR(NOW()),MIDB(E4,1,2),MIDB(E4,3,2))-1)*15+IF(MIDB(E4,5,2)+MIDB(E4,7,2)/60<=8,15-(MIDB(E4,5,2)+MIDB(E4,7,2)/60),IF(MIDB(E4,5,2)+MIDB(E4,7,2)/60>=17,24-(MIDB(E4,5,2)+MIDB(E4,7,2)/60),7))+IF(MIDB(AF4,5,2)+MIDB(AF4,7,2)/60<=8,MIDB(AF4,5,2)+MIDB(AF4,7,2)/60,IF(MIDB(AF4,5,2)+MIDB(AF4,7,2)/60>=17,MIDB(AF4,5,2)+MIDB(AF4,7,2)/60-9,8))
方法2：networkdays 需要你提供一个节假日列表作为参数。我猜你不会喜欢手工输入这样一个表。以下公式功能更为强劲，它可以算出两个单元格（A2和C2）所储存的日期之间的周末天数，乘以小时数即可计算出你所说的节假日时数。但是，它无法计算国庆节等公众假期。
=IF(C2>A2,SUM(IF(WEEKDAY(C2-ROW(INDIRECT(1:& C2-A2)),2)>5,1,0)),SUM(IF(WEEKDAY(A2-ROW(INDIRECT(1:& A2-C2)),2)>5,1,0)))。这是一个数组公式，输入完成后按CTRL+SHIFT+ENTER结束。
方法3：用格式定义E4,G4为日期时间格式：****-**-** **:**。然后：(G4-E4)*24 即可得两时间内的小时数。
如何在单元格返回工作表名称
答：=RIGHT(CELL(filename),LEN(CELL(filename))-FIND(],CELL(filename)))
如何在输入数字的加减乘除按ENTE后能在另一单元格自动出现计算数值
1、编了个宏:
Sub aa()
Cells(1, 1) = Mid(ActiveCell.Formula, 2, 13)
End Sub
先在单元格里输入公式得出结果,选定得出结果的单元格运行宏就可以在A1出文本.
2、宏(测试通过)
Sub aa()
A = 2 '行'
B = 4 '列
LINE1:
If Cells(A, B) = Then
Exit Sub
Else
Cells(A, B + 1).Formula = = & Cells(A, B)
A = A + 1
GoTo LINE1:
End If
End Sub
有A1，B1，C1，D1四个单元格D1的值要随着Ａ１的变化而变化
Ａ１有三种变化，一、二、三；当Ａ１＝‘一’时，Ｄ１＝“ ”； Ａ１＝‘二’时，Ｄ１＝（Ｂ１＋Ｃ１）／３ ； Ａ１＝‘二’时，Ｄ１＝（Ｂ１＋Ｃ１）／６ 。以上可以通过ＩＦ来完成 。可我希望的是：当Ｂ１或Ｃ１为空时，不论Ａ１为什么Ｄ１都为空，这样做得到么？
解答：1、=IF(OR(B1=,C1=),,IF(A1=一,,IF(A1=二,(B1+C1)/3,IF(A1=三,(B1+C1)/6,))))
2、=IF(OR(B1=, C1=, A1=一), , IF(A1 = 二, (B1+C1)/3, (B1+C1)/6))
3、 如果考虑 A1 没有数据的话：=IF(OR(B1=, C1=, A1=一,A1=), , IF(A1 = 二, (B1+C1)/3, (B1+C1)/6))
对A列不重复的数值计数
我只能做到新建一列，B列，然后第一个单元格countif（$A$1:$A$100,A1)，然后拖动到全部新列。最后在新列下面用sumif(B1:B100,1) 谁有更好地方法。
解答：1、{=SUM(IF($A$1:$A$100=,,1/(COUNTIF($A$1:$A$100,$A$1:$A$100))))}(又问：公式中的“1/(COUNTIF($A$1:$A$100,$A$1:$A$100))”像是一个倒数，怎么理解？答：用倒数是这个意思:如果只出现一次，数组中的相应项统计为1，其倒数为1，Sum统计计1。如果出现 N 次，其倒数为1/N，出现了N次，求和就是Nx1/N，最后Sum统计就只计1。)
永恒的求和
1、=SUM(OFFSET(A1,,,ROW()-ROW(A1)))可以对A列数值自动求和。
2、=SUM(INDIRECT(R2C:R[-1]C,FALSE))
3、=SUM(INDIRECT(A2:A&ROW()-1))
4、 坚持不用R1C1栏名列号表示法还有一个方法，不过又复杂了些。首先定义一个公式：COL=IF(COLUMN()>26,CHAR(INT((COLUMN()-1)/26)+64)&CHAR(IF(MOD(COLUMN(),26)=0,26,MOD(COLUMN(),26))+64),CHAR(COLUMN()+64))。后于欲求加总之储存格输入：=SUM(INDIRECT(COL&1:&COL&ROW()-1))。则此公式复制到任何一任皆可用，又不怕产生错误值。(注：COL=IF(COLUMN()>26,CHAR(MOD(COLUMN(),26)+64)&CHAR(INT(COLUMN()/26)+64),CHAR(COLUMN()+64))。暴露了一个为人不知的缺点，如果列数到了AA列以后就不行了，虽然可用ADDRESS()解决，比用CHAR()好多了，但公式还是太长，用在一个加法中实在不值)
5、 其实用ADDRESS更好，=SUM(INDIRECT(ADDRESS(1,COLUMN())&:&ADDRESS(ROW()-1,COLUMN())))，还是一句老话，为做一个加法不是太值，这只能是技术上的讨论。
如何使用EXCEL进行动态跨表取数
有两个文件，第一个文件有31张日报表，每天一个表单；第二个文件仅一个表单；如何在第二个文件中，输入1时由函数动态取出第一张表单数（如SHEET1），输入2时取（SHEET2）数，依次类推……。，如何设公式。在同一文件中可以用INDIRECT和ADDRESS组合，可是跨表好象不行，请各位指教一二！
解答：前提是两个工作表都要打开：=INDIRECT([Book2]Sheet&A1&!$B$1)
如何使用對照表
设一对照表如下：如何使用函数当输入。当a1=34 则a2显示乙 b1=68 则b2显示丁/甲 22 33 44 66..../乙 26 34 43 62.... /丙 28 39 41 67.... /丁 27 31 49 68.... /戊 23 32 46 64.... /己 29 38 47 61.... /更 21 37 48 69.... /辛 24 36 42 63....
解答：如A列输入的数字在表中都存在，把你的数据放在D到G列，甲乙丙丁戊己辛放在最后一列，即H列。在B列输入公式“=IF(A1=,,VLOOKUP(A1,INDIRECT(ADDRESS(1,INT(0.1*A1)+IF(A1>60,1,2))):INDIRECT(ADDRESS(8,8)),7-INT(0.1*A1)+IF(A1>60,1,0),FALSE))”。如甲乙丙丁戊己辛放在第一列，即列用公式“=IF(A1=,,INDEX(D:D,MATCH(A1,INDIRECT(ADDRESS(1,INT(0.1*A1)+IF(A1>60,2,3))):INDIRECT(ADDRESS(8,INT(0.1*A1)+IF(A1>60,2,3))),0)))”
解答2：如果数据无规律，可用以下方法：如你的数据表在D到H列，在I列输入公式“=IF(ISERROR(MATCH(A$1,E1:H1,0))=TRUE,0,row())并向下填充，在B1单元格输入公式“=IF(A1=,,INDEX(D:D,LARGE(I:I,1)))”
解答3：我将解答2公式做了小修改(I列公式删除)，B1单元格输入公式：=IF(A1=,,INDEX($D:$D, IF(ISERROR(MATCH(A1,$E$1:$H$1,0))=TRUE,0,ROW($E$1:$H$1))+ IF(ISERROR(MATCH(A1,$E$2:$H$2,0))=TRUE,0,ROW($E$2:$H$2))+ IF(ISERROR(MATCH(A1,$E$3:$H$3,0))=TRUE,0,ROW($E$3:$H$3))+ IF(ISERROR(MATCH(A1,$E$4:$H$4,0))=TRUE,0,ROW($E$4:$H$4))+ IF(ISERROR(MATCH(A1,$E$5:$H$5,0))=TRUE,0,ROW($E$5:$H$5))+ IF(ISERROR(MATCH(A1,$E$6:$H$6,0))=TRUE,0,ROW($E$6:$H$6))+ IF(ISERROR(MATCH(A1,$E$7:$H$7,0))=TRUE,0,ROW($E$7:$H$7))+ IF(ISERROR(MATCH(A1,$E$8:$H$8,0))=TRUE,0,ROW($E$8:$H$8))))。
解答4：在B1单元格输入公式：{=IF(COUNTIF($E$1:$G$8,A1)=0,查无资料,INDEX($D$1:$D$8,SUM(IF($E$1:$H$8=A1,ROW($E$1:$H$8)))))}
又问：当数据区域有重复数据时，就得不到正确结果。因为，在这里你的SUM()返回的只是对一个数据求和。如果有重复数据，怎样才能得到正确结果呢？
答：在B1单元格输入公式：=IF((COUNTIF($E$1:$H$8,$A$1)=0)
+(COUNTIF($E$1:$H$8,$A$1)
< ROW( )), ,
INDEX($D$1:$D$8,SMALL(IF($E$1:$H$8=$A$1,ROW($E$1:$H$8)),ROW())))
再往下拖曳，就可依序顯示了。真是快瘋了改了十幾次，有些莫名其妙，進來編輯看公式是完整的可是發表後又老是缺東缺西的，只好多加些空白或強迫分段處理，請使用者自行修改。
或：如有重复数据,則顯示数据重复表示,代表要修改数据。{=IF(COUNTIF($E$1:$H$8,A1)=0,查無資料,IF(COUNTIF($E$1:$H$8,A1)>1,資料重複,INDEX($D$1:$D$8,SUM(IF($E$1:$H$8=A1,ROW($E$1:$H$8))))))}
如何在单元格返回工作表名称
解答：=RIGHT(CELL(filename),LEN(CELL(filename))-FIND(],CELL(filename)))
何在一列数据中统计限定范围的数据
请教各位：现A列有数字(包括绝对值为0的数字)、文本、空格,要统计数值为14到35(包括14与35)的个数；还要统计数值>35并<14的个数(不包括0),分别该用什么函数?
答：>13 and <36 公式=COUNTIF(A:A,>13)-COUNTIF(A:A,>35)
(>35 or <14) and <> 0 公式=COUNTIF(A:A,>35)+COUNTIF(A:A,<14)-COUNTIF(A:A,=0)
|
|
