Excel word ppt office使用技巧大全(DOC格式)-第68部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
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 页中建立一个表如下：
368
…………………………………………………………Page 369……………………………………………………………
姓名 统计优数 /胡强强 /金龙鳞 / 闵生刚 /汪欲生 /王娟 /永生 /张正梅 /朱智锐 / 同样姓名
在 sheet1 的a 列，统计在 c 列，这里的姓名已经整理为没有重复的姓名。要统计出每人获得优
的数目。请问用什么函数能解决。我用了if（（sheet1！c2）=〃优〃；countif（sheet1！a：a；a2））；结果是统计
的姓名数，而不是成绩数。如果能统计出来，哪么就是一个动态的当sheet1 中数据变化时，sheet2
中数据应到跟着变化。
有位朋友指导采用下面的式子把上面的问题解决了。这里假设最大记录数为 100
=sum（（sheet1！a2：a101=a2）*（sheet1！c2：c101=〃优〃）*1） ，按 Ctrl＋Shift＋Enter ，但是这
是用到了数组运算，请问数组运算的规则是什么，看到许多地方都可以用数组解决，但不知其
所以然。帮助文件中也没说运算规则。如上式中为什么用*号？谢谢
解答：对于数组公式的含义
sum（（sheet1！a2：a101=a2）*（sheet1！c2：c101=〃优〃）*1）
我们来一部分，一部分的讲：
1、（sheet1！a2：a101=a2）
表示用 sheet1！a2：a101 区域中的每一个单元格中的内容与 a2 单元格的内容进行比较，
如果相同结果为“True ”，否则为“False ”。
2 、（sheet1！c2：c101=〃优〃）
表示用 sheet1！c2：c101 区域中的每一个单元格中的内容与 字符串〃优〃进行比较，如果相
同结果为“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（B13；1；1）；〃135〃））；IF（ISERROR（FIND（MID（B13；1；1）；〃246〃））；IF（IS
ERROR（FIND（MID（B13；1；1）；〃79〃））；IF（ISERROR（FIND（MID（B13；1；1）；〃80〃））；〃〃；〃D〃）；〃C〃）；〃
B〃）；〃A〃） 。 C14 中MID（）第二个参数为 2 ，以此类推。。。最后在目标单元格中输入：
=CONCATENATE（B14；C14；D14；E14；F14；G14；H14）
方法 2 ：表一：
AB。。。
11234567890
2ABABABCCCD
=SUBSTITUTE（A4；A1；A2）
说明：先列一个替换表；如表一；在 A4 处填如数据；在 B4 处填如上述公式
=SUBSTITUTE（A4；A1；A2）；并向右拖动 9 个同样的公式；最后一个便是结果。在将该 10 个相同
的公式向下拖；便得到其它的结果。好处：可以修改表一；产生变化。
方法 3 ：你可以把全部数据拷贝到WORD 中，再用替换命令，想怎么换就怎么换，然后在拷
贝回来。
369
…………………………………………………………Page 370……………………………………………………………
几个技巧
用“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；A1：A10；0）
我想根据题意应该为：=MATCH（A1；B10；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））））））））
370
…………………………………………………………Page 371……………………………………………………………
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。 一个单元格也可以实现
4。 =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=1
3；〃M〃；IF（A1=14；〃N〃；IF（A1=15；〃O〃；IF（A1=16；〃P〃；〃〃））））））））&IF（A1=17；〃Q〃；IF（A1=18；〃R〃；IF（A
1=19；〃S〃；IF（A1=20；〃T〃；IF（A1=21；〃U〃；IF（A1=22；〃V〃；IF（A1=23；〃W〃；IF（A1=24；〃X〃；〃〃））））））））&I
F（A1=25；〃Y〃；IF（A1=26；〃Z〃；〃〃））（数组形式输入） 。
5。 以一例：a1=1；2；3；4；5；6；7；8；9；10
6。 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 吨，之下》99
9 是千克，其余的是克。如何办
答：＇》9999＇###。00；〃吨〃；*；*。00〃千克〃
怎样用函数求出一定范围内的数值的偶数的个数
解答：1 设你的数据区域为 A1：A30
371
…………………………………………………………Page 372……………………………………………………………
｛=COUNT（IF（A1：A30/2/2=INT（A1：A30/2）；A1：A30））｝
如果你的数据区域内还包括空白格你不想计算在内的话， ｛=COUNT（IF（A1：A30/2/2=INT（A1：A
30/2）；A1：A30））…COUNTBLANK（A1：A30）｝
或：｛=sum（（even（a1：a30）=a1：a30）*1）｝
如何使某列的数据不重复
1、 我做了个宏，可惜在数据量大时（超过 1000 时）速度狂忙，各位帮我修改修改
2 、 Sub 检查重复项（）
3、 Dim i As Integer
4 、 Dim j As Integer
5、 num = Selection。Cells。Count
6、 For i = 1 To num
7、 For j = i ＋ 1 To num
8、 If （Selection。Cells（i） = Selection。Cells（j）） Then
9、 MsgBox Selection。Cells（i）。Value
10、 End If
11、 Next j
12、 Next i
13、 MsgBox 〃检索完毕〃
14、 End Sub
15、 假设数据在 B 列
16、 选中 B 列，＇数据＇…＇数据有效性＇…＇ 自定义＇
17、 在“公式”输入框中键入：=COUNTIF（B：B；B1）=1 。
