EXCEL问题集合7

接：EXCEL问题集合6[@more@]

工资条问题

职工工资构成非常复杂，往往超过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, "联系电话：013838751304"

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，点选"编辑--&gt粘贴链接图片" 。对长公式进行错误查找：点击公式，按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，则

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/604457/viewspace-997242/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/604457/viewspace-997242/