Excel word ppt office使用技巧大全(DOC格式)-第63部分
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
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 名的情况了。该公式可以无限排列。
研究彩票，从统计入手
Q：我有一个VBA 编程的问题向你请教。麻烦你帮助编一个。我一定厚谢。
有一个数组列在 EXCEL 中如： 01 02 03 04 05 06 07 /和 01 04 12 19 25 26 32 /02 08 15
16 18 24 28 /01 02 07 09 12 15 22/09 15 17 20 22 29 32/比较，如果有相同的数就在第
八位记一个数。如 ：01 04 12 19 25 26 32 2 /02 08 15 16 18 24 28 1 /01 02 07 09 12 15
22 2 /09 15 17 20 22 29 32 0 。这个数列有几千组，只要求比较出有几位相同就行。
解答：把“01 02 03 04 05 06 07 ”放在表格的第一行，“01 04 12 19 25 26 32 2”放第二
行。
把以下公式贴到第二行第八个单元格“A9”中，按F2；再按 CTRL＋SHIFT＋ENTER。
=COUNT（MATCH（A2：G2；A1：G1；0））
去掉 XLS 文件头上菜单栏的“MIcrosoft EXCEL ”字样
A： Private Sub Workbook_Open（）
Application。Caption = 〃程香宙专用表格〃
End Sub
346
…………………………………………………………Page 347……………………………………………………………
替换数据
Q：请教各位如何用将一组数据，
如：6550894， 9852547， 2656032， 7461136， 0505867， 5564892， 7235580，0421077，
我需要把数据中的数字 1，3，5 换为符号 A 表示，2，4，6 换为符号 B 表示，依此类推将数据
中的阿拉伯数字0~9 分为几类用其它符号替换。
A： leaf
用 VBA 处理比较方便。 只用 EXCEL 函数，感觉代价太大。
假设：B13 值为 9550894 在 B14 中输入
=IF（ISERROR（FIND（MID（B13；1；1）；〃135〃））；IF（ISERROR（FIND（MID（B13；1；1）；〃246〃））；IF
（ISERROR（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）
dongmu ：表一：
AB。。。
11234567890 、2ABABABCCCD 、=SUBSTITUTE（A4；A1；A2）
说明：先列一个替换表；如表一；在 A4 处填如数据；在 B4 处填如上述公式
=SUBSTITUTE（A4；A1；A2）；并向右拖动 9 个同样的公式；最后一个便是结果。在将该 10 个相同
的公式向下拖；便得到其它的结果。好处：可以修改表一；产生变化。
ACCESS ：你可以把全部数据拷贝到WORD 中，再用替换命令，想怎么换就怎么换，然后在拷贝
回来。
复制数据再转置；不复制转置被隐藏的行或列
解答：选择需要转置的单元区域，按下 F5－－＞定位条件－－＞可见单元格－－＞复制
－－＞选择性粘贴－－＞转置。
如何始终打开默认的工作表
可不可以作到每次保存工作表时，无论保存时是在哪一个
SHEET，但是当下次再打开时，还是原来默认的那张工作表。比如 SHEET1。谢谢！
解答：Private Sub Workbook_Open（）
Worksheets（〃sheet1〃）。Activate
End Sub
如何分割文本
有一列数据，全部是邮箱的，现在想将@前面的账号与@后面的域名分割开，分为两列，如何做？
解答：采用函数分割：例如：A1： name@163。
B1：=LEFT（A1；FIND（〃@〃；A1）…1） ……》 name
C1：=RIGHT（A1；LEN（A1）…FIND（〃@〃；A1）） ……》 163。
或：数据…分列…分列…分隔符号…@就可以了
347
…………………………………………………………Page 348……………………………………………………………
两列合一列
现有两列数据 A 列与 B 列，我想把 B 列的数据合并到 A 列但必须是 B1 单元格的数放到 A1
的下面，B2 放到 A2 的下面依此类推，有什么办法呢？
解答：=INDIRECT（〃r〃&INT（（ROW（）＋1）/2）& 〃c〃&MOD（ROW（）＋1；2）＋1；0）
解释：
（一）EXCEL 表中的列、行样式有两种：
一种标记样式为：
列 （字段）以A；B；C；D。。。。。。
行 （记录）以数值 1；2；3；4；5。。。。。。
第一列第一行的单元格为 A1
另一种标记样式为（取 ROW 和 COLUMN 的首位字母）：
列 （字段）以R1；R2；R3；R4；R5。。。。。。
行 （记录）以C1；C2；C3；C4；C5。。。。。。
第一列第一行的单元格为 R1C1
（二）请参阅 INDIRECT 函数的帮助说明！！！
公式：
=INDIRECT（〃r〃&INT（（ROW（）＋1）/2）& 〃c〃&MOD（ROW（）＋1；2）＋1；0）
等同于：
=INDIRECT（〃r〃&INT（（ROW（）＋1）/2）& 〃c〃&MOD（ROW（）＋1；2）＋1；FALSE）
（三）工具……》选项……》常规……》设置；还可选取 R1C1 引用样式
每次清除数据时能否作到跳过隐藏的单元格
解答：F5…………定位条件…………常量…………确定…………Del
或：F5－＞定位条件…》可见单元格…》确定…》DEL
也就是单击 Sheet2 时，在 Sheet1 的A 列的最后一个记录的下一行自动填上“End”
在 sheet2：
Private Sub Worksheet_Activate（）
dim i as integer
i = Sheets（〃Sheet1〃）。Cells（1； 1）。CurrentRegion。Rows。Count
Sheets（〃Sheet1〃）。Cells（i ＋ 1； 1） = 〃End〃
End Sub
用函数将输入的月份转换为这个月所包含的天数
假设 A1 单元格为月份：
＝TEXT（（DATE（YEAR（NOW（））；A1＋1；1）…1）；〃d〃）
或：=DAY（DATE（YEAR（NOW（））；A1＋1；0））
介绍经验：就 SUM 函数来讲；以下动态地址可行
1。SUM（A1：A2）；SUM（A1：A2）
2。B2=〃A9〃；
348
…………………………………………………………Page 349……………………………………………………………
SUM（INDIRECT（〃a1：〃&B2））
3。B1=〃A1〃；B2=〃A9〃
SUM（INDIRECT（B1&〃：〃&B2））
4。B1=〃A1：A9〃
SUM（INDIRECT（B1））
5。SUM（INDIRECT（〃A1：〃&〃A〃&ROW（）…1））
6。SUM（INDIRECT（〃A1：〃&ADDRESS（ROW（）…1；COLUMN（））））
在 EXCEL 中如何统计字数
用｛=SUM（LEN（范围））｝试试
如何自动填充内容
A1：A20 是编号，B1：B20 是姓名，C1：C20 是性别，当我在 A21 单元格输入 A1：A20 范围内
的任意一个编号时，B21 出现对应的姓名，C21 出现对应的性别。该如何做，请帮忙。
解答：B21 单元格公式“=IF（A21=0；〃〃；VLOOKUP（A21；A1：C20；2；FALSE））”；C21 单元格公式
“=IF（A21=0；〃〃；VLOOKUP（A21；A1：C20；3；FALSE））”这个公式也适用于A 列编号不排序的情况，
如果升序的话会更简单一点。
问：以上公式中的'false'有什么用？能否省略？
答：false 参数主要是用它以后在 A 列中的数据可以不是升序排列。不然如果 A 列不是升序排
列，公式会出错的。
工作表的标签的字体和大小可以更改吗
答：在桌面上点右键─内容─外观，相关的设定都在此更改。
自定义格式的体会
在 format cell 的时候，选了 custom 后在格子里输入你想要的位数，不变的部分就照着打进
去，会变得部分打 0 就好了，（用 0 占位）。
例如：你要打的数字是 00715834123456，后 6 位是不定的，那你要打在格子里面
00715834000000。这样如果你输入最后 3 位是 012，那么会显示出 00715834000012；如果你输
入 54321，那么会显示出 00715834054321。
如果你会变得部分是在数字的中间，比如我的 item#会是 9690000001…0000002，后面的
…0000002 是不变的，那我就可以设置自定义格式为 9690000000〃…0000002〃；这样当我键入 502
的时候就会显示 9690000502…0000002。
再次显示出被隐藏掉了的行（第 1 行）
1： 选中隐藏的上、下行，右击鼠标，选“取消隐藏” （作者注：此法可行）
2：Ctrl＋A……………格式……………行……………取消隐藏（可以，能够一次显示所有隐藏的行或列）
3：另一法（工作表处于未保护状态）：假如 A1 被隐藏了在名称框中键入A1；回车按
Ctrl＋Shift＋0 或 Ctrl＋Shift＋9（只显示选定的隐藏列或行）
4：光标移到行号 4 上部变成 上下箭头状； 按住了； 拖也要把它拖出来！（慢，不好操作）
5：选择整个工作表（点击左上角），然后再选择菜单中的行，选择最适合的行高，然后就 OK！；
同样可以把隐藏的列显示出来。（这个办法最好，能够一次显示所有隐藏的行或列）
349
…………………………………………………………Page 350……………………………………………………………
如何定义有效数字
例：取两位有效数是从第一个不是零的数字起，取两位。0。0023666 取两位有效数是
0。0023 。0。2366 取两位有效数是 0。23。
解答：用函数可如下： =FLOOR（A1； SIGN（A1）*10^（INT（LOG（ABS（A1）））…1））； ＋/小数有
效；0 无效。
其它形式的数据； 自行扩展。
sheet1 工作表的 A1、A2、A3 单元格分别链接到 sheet2、sheet3、sheet4
解答：1、 =indirect（〃sheet〃&row（）＋1&〃！a1〃） 《程香宙的解释：indirect 是把文本变为单
元格引用的函数 row（）是取当前行号。例如在 a1 输入该公式，则 row（）=1，公式里的值变为
indirect（〃sheet2！a1〃）；跟＝sheet2！a1 同效，在a2 输入该公式，则 row（）=2，公式里的值变
为 indirect（〃sheet3！a1〃）》
2、使用插入…………超级链接…………书签………（选择）…………确定
用 SUMIF 函数进行条件求和，不限于一个条件时如何设置参数
例如：有一个表格登记面粉、米粉、糯米、梗米、绿豆、早米〃〃等等的进出流水帐，如果对
满足单一条件的如面粉、糯米、绿豆等分别求和是没有问题的，但如果要将同一类的求和，例
如将糯米、梗米、早米的数值加在一起，应该怎么办？
解答：提供以下公式供参考，设 A 列为名称、B 列为数量：
=SUMIF（A：A；〃糯米〃；B：B）＋SUMIF（A：A；〃梗米〃；B：B）＋SUMIF（A：A；〃早米〃；B：B）
如 B1：D1 为求和条件项，即 B1=〃糯米〃；C1=〃梗米〃；D1=〃早米〃；
上述公式还可改为：
=SUMIF（A：A；B1；B：B）＋SUMIF（A：A；C1；B：B）＋SUMIF（A：A；D1；B：B）
如何在 excel 中已有的数值前加零变成六位
比如说 25、369、1569 等，操作后变成 000025、000369、00156
解答：如果直接输入的话，可以在数值前面加“'”，如“'002020”；
如果处理现成的数据，或者从别处（比如从 A1 单元格）链接来的数据，可以用公式：
＝RIGHT（〃00000〃&A1；6）
如何提取工作表中的背景图片
解答：找个干净的地方； 去掉网纹等不需要的东西； PrintScreen 再编辑
绘制有三条斜线的表头
解答：1、用绘图工具画出斜线》》画方框》》内添加文字》》去边框
2、引用WORD 中的，然后再复制过来就可以！
350
…………………………………………………………Page 351……………………………………………………………
在 A 列有一组数据不是按照大小顺序排列在 B 列中排名
解答：方法 1、将A 列 COPY 到 B 列，再排序。
2、rank 函数（=RANK（A2：A11；A2：A11；0）（假设数据在 A2：A11 单元格�
