Excel word ppt office使用技巧大全(DOC格式)-第58部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
编 号 数 量 1 数 量 2
01 180 80
02 190 90
01 170 80
01 160 80
我如何计算 （数量 1…数量 2）值的总和。并且必须满足编号相同时； 只能减一次 数量 2。 即要计
算成： （180＋170＋160…80）＋（190…90）。 注： 编号相同时；数量 2 的值一定相同。
解答：设表格在 A1：C5 中
=SUMIF（A2：A5）…INDEX（C1：C5；MATCH（〃01〃；A1：A5；0））…INDEX（C1：C5；MATCH（〃02〃；A1：A5；0））
排名问题
在 A1：F6 区域有下面一个表格：
班级 姓名 政治 语文 数学 总分
1 小东 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小刘 95 90 92 277/5
小红 95 91 92 278 /要在 K1：K3 的单元格中分别显示总分最高的同学的班级、姓名、总分，
在 L1：L3 的单元格中分别显示总分第二的同学的班级、姓名、总分，在M1：M3 的单元格中分别
显示总分第三的同学的班级、姓名、总分 ，注意期中 277 分的有两人；不要出现第二名与第三
名都是〃明明〃的结果。
解答：定义 A2：A6 区域为班级 ；定义B2：B6 区域为姓名；定义F2：F6 区域为总分
K1=｛INDEX（ 班 级 ；MATCH（LARGE（ 总 分 ＋1…ROW（ 总 分 ）/100；ROW（A1））； 总 分 ＋1…ROW（ 总
分）/100；0））｝
L1=｛INDEX（ 姓 名 ；MATCH（LARGE（ 总 分 ＋1…ROW（ 总 分 ）/100；ROW（A1））； 总 分 ＋1…ROW（ 总
分）/100；0））｝
M1=｛INDEX（ 总 分 ；MATCH（LARGE（ 总 分 ＋1…ROW（ 总 分 ）/100；ROW（A1））； 总 分 ＋1…ROW（ 总
分）/100；0））｝
注：只要向下复制，便可得到 1~6 名的情况了。该公式可以无限排列。另注：这是数组公式，
不需要输入“｛｝”，在输完公式后，请按 ctrl＋shift＋enter，应将后面的 100 改成 100000，比
较好。
统计满足条件的数据并生成新的数据列
A B C
427 8 427/612 8 612 /924 8 924 /22 16 409 /94 16 /620 16 /955 16 /409 8 /请问：假
324
…………………………………………………………Page 325……………………………………………………………
定有如上两列数据 A 和 B，现在想要统计满足条件B＝8 的 ，并且在 C 列自动生成数据，我不
懂公式怎么写，我知道筛选能够做到，但是由于数据量比较大 ，想做一个模板，免去一些重
复劳动。
解答：
=IF（ROW（A1）》COUNTIF（B1：B8；8）；〃〃；INDEX（A1：A8；MATCH（SMALL（IF（B1：B8=8；0
；1）＋ROW（B1：B8）/100000；ROW
（A1））；IF（B1：B8=8；0；1）＋ROW（B1：B8）/100000；0）））
争议：B 栏有 100 个选取条件，公式要修改 100 次，有甚么灵活性？给字段名称 ，插入头两列 ，
B1=SUBTOTAL（9；B4：B65536）。解决掉所有问题 。控件找自动筛选要
辩解：不需要修改公式，直接复制就行了。如果是 100 个，开始时就将范围修改好就行了。
工作表间的查找与复制
我的工作是在一个 2000 多个员工的纪录（包括各种资料，如身份证号，工作记录等）的 excel
工作表 A 中查找另一份 200 多个员工名单，把员工在 A 表中的各项纪录复制到另一个表上去，
我听说用 vba 可以很容易的简化大量的复制工作，不知哪位高手可以指点指点？？
解答：如下表 sheet1 的内容
a b c
姓名 工号 部门
张三 100 mm /李四 101 mm2 /。。。。。。。
表 sheet2
a b c
姓名 部门 /李四 /。。。
要在 sheet2 表中将 sheet1 表的相同人员资料复制过来可在 b 表 单元格 b2 中输入
”=vlookup（a2；sheet1！a1：gg2000；3；false）
最好将 sheet1 的数据区先定义一下
比较运算符为何要用引号
（1） 何解在公式内，运用 〃比较运算符号〃 时，要把数据连同比较运算符号用 〃双引号〃 括住，
而其它算术符号就不需要？
例如：=Countif（A1：A30；〃》=10〃）是可以接受，但不加双引号时，便遭拒绝。
（2） 又请问：如上例，要比较的数值是要参照某单元格时，公式应怎样做？
解答：这是 CountIF（）的规定。第二个参数为字符串，必须用双引号扩起来，除非条件是等于
一个数值。
比如：=COUNTIF（A1：A30；〃》=4〃）
=COUNTIF（A1：A30；〃condition〃）
=COUNTIF（A1：A30；220）
用引用作参数（假设 B1 值为 4）：
=COUNTIF（A1：A30；〃》=〃 & B1）
ＥＸＣＥＬ中如何修改批注的字体和颜色
解答：视图－－批注 然后在批注上单击右键 选择设置批注格式
325
…………………………………………………………Page 326……………………………………………………………
如何以进阶筛选命令筛选出“序号”为单数、双数、或者是任意间距值的 records
序号 姓名 性别 年龄
1 李 1 男 30 /2 李 2 女 25 /3 李 3 女 22/4 李 4 男 31 /： ： ： ： /99 李 99 男 28
解答：在“序列”与“姓名”之间插入一个空列，在其第一格输入“条件”。在“条件”下面
输入公式“=IF（MOD（A5；2）=1；〃单数〃；〃双数〃）”，用“自动筛选”。
n2=sum（b2 到 m2） 、n3=sum（b3 到 m3） 、有没有办法让 n 那一列可以自动填充
就是说每增加一行，就会自动计算 n 那一列的值 、多谢！
解答：1：我有个笨方法，须用VBA，不知和你意否。
Private Sub Worksheet_Change（ByVal Target As Excel。Range）
If Target。Column 《 14 And Cells（Target。Row； 14）。Formula = 〃〃 Then Cells（Target。Row；
14）。Formula = 〃=SUM（RC＇…12＇：RC＇…1＇）〃
End Sub
2：假设需要被 SUM 的数据存放在B 列（第一个数据存放在单元格 B2）；将以下公式 COPY 到你希
望显示结果的单元格中：
=IF（LEN（B2）》0；SUM（B2：INDEX（B：B； ROW（）； 1））； 〃〃）
切记如果 B 列中有空单元格隔开了需要计算的数据； 必须以零填充该单元格。
3：这个问题不用想得很复杂，就是要把n2 单元格输入公式后，拖曳向下复制公式。
也许是我想得太简单了。
在工作表里有连续 10 行数据； 现在要每行间格 2 行； 要如何做
解答：1：如 sheet1！A1：D10 中有连续 10 行资料，在 sheet2 中把 sheet1 中的数据每行
间隔 2 行
sheet2！A1 中公式可用
=IF（ROW（）=1；Sheet1！A1；IF（MOD（ROW（）；3）=1；INDEX（Sheet1！A1：D10；INT（（ROW（）…1）/2）＋
1；COLUMN（））；〃〃））
然后填充公式（注意公式在 SHEET2 中的填充范围，超过范围会出错！）
2：小修改
=IF（MOD（ROW（）；3）=1；INDEX（Sheet1！A1：Z500；INT（ROW（）/3）＋1；COLUMN（））；〃〃）
当点到某单元格时单元格会出现一个下拉的列框，可以选取其中的数据
解答：＇数据＇…＇有效性＇…＇序列＇
又问：好像选取数据只能在本页面，有无高招选取另外页面的数据源，甚至是另外工作薄的？
解答：先在“插入…》名称”中定义好其它页面上需要引用的数据源的名称（AA）。然后 ＇数
据＇…＇有效性＇…＇序列＇ ，在“数据源”栏输入“=AA”。
或：若改其它活页薄要如何修改下列式子？（ 假设活页薄名称为 book1）
=Sheet1！A2：A14（book2）
查找与引用
有一个表格：
承兑行 天数 实际天数
326
…………………………………………………………Page 327……………………………………………………………
深圳工行 98 101
广州交行 105 108
鞍山工行 100 100
如果“承兑行”一栏含有鞍山字样，则“实际天数”等于“天数”；如果“承兑行”一栏不含
有鞍山字样，则“实际天数”等于“天数”加上 3，请问：如何设置？
解答：这个问题的重点在于如何判断是否含有“鞍山”字样。
如果“鞍山”在字符串中的位置是固定的，则很容易用 MID 函数找到并进行判断；
如果不是，则需要用 FIND 或 Search 函数来找到再判断。
或：如果 A 列中是承兑行，B 列是天数，C 是。。。。
可以试用一下以下的公式：
=IF（ISERROR（FIND（〃鞍山〃；A2））；B2＋3；B2）
如果 D2》20 那 E2 就显示200、如果D2》30 那 E2 就显示300 依此类推
解答：=INT（D2/10）*100 ，当然，你的单元格格式设置成格式就可以了。否则用，
=〃〃&INT（D2/10）*100
则该单元格成字符型 。当然，你也可以用 IF 函数，但它有 7 层的限制。if （D2》30；
〃300〃；IF（D2》20；〃200〃））
在两个工作表之间查找相同的内容并复制到新表
有两张工作表，内容都是电话号码、用户名称，怎样对两张工作表进行比较？（把第一张工作
表和第二张工作表中电话号码相同的项目复制到新工作表中或加上标记）
解答：先备份工作簿。
假设 SHEET1、SHEET2 两张表的结构相同，A 列为用户名，数据由第二行开始。新建 SHEET3 并
在其 A2 单元格中输入 =IF（ISNA（MATCH（Sheet2！A2；Sheet1！A：A；0））；〃 DELET THIS ROW
〃；Sheet2！A2） 并向下、向右填充。
完成后 SHEET3 中多了SHEET2 和 SHEET1 共有的用户名，以及由〃DELET THIS ROW〃填充的数据
行。剩下的就简单了！
统计偶数单元格合计数值
解 答 ： 统 计 F4 到 F62 的 偶 数 单 元 格 合 计 数 值 。 公 式 一
｛=SUM（IF（MOD（ROW（F4：F62）；2）=0；F4：F62））｝
公式二 （要有安装'分析工具箱'） ｛=SUM（IF（ISEVEN（ROW（F4：F62））；F4：F62））｝
按表 1 中A 列的编号提取表 1 中的相应数据并自动录入到表 2 中的对应的相关列
有两个工作表，工作表 1 中A 列为编号，其它列为相关数据，若在工作表 2 中的编号列单元格
中输入编号时，如何才能按表 1 中A 列的编号提取表 1 中的相应数据，自动录入到表 2 中的对
应的相关列。
解答：利用 vlookup 寒暑，该函数的用处就是把一个数据区域当成数据库，并利用条件检索
相关纪录。
有了这点认识就非常简便了！在表 2 的单元格中输入下面的公式：
=vlookup（编号，表 1！有关数据区域，第 n 列，false）
解释： 1、编号——不需要输入，主要是编号的相对引用。例如：
编号在 b2；公式在 c2；则编号为：b2
327
…………………………………………………………Page 328……………………………………………………………
2、有关数据区域——必须是绝对引用，也就是数据区的行列要用符号修饰。例如：从 a1 到
h50 是数据，则应该写为：a1：h50
3、第 n 列——也就是你准备返回第几列的值。例如：你输入编号后，要得到姓名，而姓名在
数据区域的第 5 列，n 就是 5。
4、false——此处取值有两种，一是 true；一是 false。两者的区别是 true 为相似匹配，false
为精确匹配。
用 EXCEL 做统计
如：A1，B1 单元格是时间类型，C1 是数字类型，我要计算费用用（B1…A1）*C1 得到的数据还
是时间类型、怎么办 ？我希望把时间类型变为整数类型，如0：50 （50 分钟）*3 （元/H）=2。5
元
解 答 ： 设 A1 为 3 ： 30 ， B1 为 4 ： 30 ， C1 为 3 ， D1 为 下 列 输 入 的 函 数：
=HOUR（B1）*60＋MINUTE（B1）（HOUR（A1）*60＋MINUTE（A1）） 。D1 结果等于 60 （分钟）
单元格是否有注释
我在用 VBA 写 EXCEL 的注释时，需要等程序判断该单元格是否有注释，如果有就读出来，如果
没有就添加，但我一直没找到用什么办法来知道单元格是否有注释，请各位大虾指点！！！
解答：
Sub 批注 （）
For i = 1 To 8
On Error Resume Next
a = Sheets（1）。Cells（i； 1）。ment。Text
If Err。Number = 91 Then
Sheets（1）。Cells（i； 2） = 〃左侧单元格无批注〃
Sheets（1）。Cells（i； 1）。Addment Text：=〃请输入批注内容〃
Else
Sheets（1）。Cells（i； 2） = 〃左侧单元格批注〃 & a
End If
