半小时教你成为excel高手-第3章
按键盘上方向键 ← 或 → 可快速上下翻页，按键盘上的 Enter 键可回到本书目录页，按键盘上方向键 ↑ 可回到本页顶部！
————未阅读完？加入书签已便下次继续阅读！
体方法如下：
Ａ、首先用鼠标选定单元格，同时按下键盘上的Shift键；
Ｂ、接着移动鼠标指针到单元格边缘，直至出现拖放指针箭头，然后进行拖放操
作。上下拖拉时鼠标在单元格间边界处会变成一个水平“工”状标志，左右拖拉时会变
成垂直“工”状标志，释放鼠标按钮完成操作后，单元格间的次序即发生了变化。
、在Excel中利用公式来设置加权平均
加权平均在财务核算和统计工作中经常用到，并不是一项很复杂的计算，关键是要
理解加权平均值其实就是总量值（如金额）除以总数量得出的单位平均值，而不是简单
的将各个单位值（如单价）平均后得到的那个单位值。在Excel中可设置公式解决（其
实就是一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果
就是这些量值的加权平均值。
10、用Excel绘制函数图像
函数图像能直观地反映函数的性质，用手工方法来绘制函数图像效果不太好，而
用Excel绘制函数图像非常简便，所作图像非常标准、漂亮，具体方法如下：
Ａ、首先打开电子表格的操作窗口，然后用鼠标选择菜单栏中的“新建”命令，这
时屏幕上会出现一个空白的电子表格；
Ｂ、然后在A列的A1格输入“X=”，表明这是自变量，再在A列的A2及以后的格内逐次从
小到大输入自变量的各个值；实际输入的时候，通常应用等差数列输入法，先输入前三
个值，定出自变量中数与数的距离，然后点击A2格，按住鼠标拖到A4格选中这三项，使
这三项变成一个黑色矩形，再用鼠标指向这黑色矩形的右下角的小方块，当光标变成
“＋”后，按住鼠标拖动光标到适当的位置，就完成自变量的输入；
Ｃ、接着在B列的B1格输入函数式y=f（x）的一般函数表达式，如y=1/x；也可在点
击工具栏上的函数图标“fx”后，在出现的不同函数列表的选项中选择需要的一种；输
入结束后，点击函数输入对话框旁的勾号，B2格内马上得出了计算的结果。这时，再选
中B2格，让光标指向B2矩形右下角的方框，当光标变成“＋”时按住光标沿B列拖动到
适当的位置即完成函数值的计算。注意一定要把该函数中自变量x的位置输入前面A列自
变量的绝对位置A2格，这样下面计算的时候才会对不同的自变量进行计算；
Ｄ、最后点击工具栏上的“图表向导”图标，在出现的各种图表格式图标中选择“X，Y
散点图”，然后在出现的“X，Y散点图”类型中选择“无数据点平滑线散点图”；这
时，可按住鼠标察看即将绘好的函数图像。另外，对于自变量仅为正值的函数，也可用
“图表向导”中的“折线图”格式绘制。
在Excel中自动推测出生年月日及性别的技巧
大家都知道，身份证号码已经包含了每个人的出生年月日及性别等方面的信息（对
于老式的15位身份证而言，7…12位即个人的出生年月日，而最后一位奇数或偶数则分别
表示男性或女性。如某人的身份证号码为420400700101001，它的7…12位为700101，这
就表示该人是1970年元月1日出生的，身份证的最后一位为奇数1，这就表示该人为男性
；对于新式的18位身份证而言，7…14位代表个人的出身年月日，而倒数第二位的奇数或
偶数则分别表示男性或女性）。根据身份证号码的这些排列规律，结合Excel的有关函
数，我们就能实现利用身份证号码自动输入出生年月日及性别等信息的目的，减轻日常
输入的工作量。
Excel中提供了一个名为MID的函数，其作用就是返回文本串中从指定位置开始特定
数目的字符，该数目由用户指定（另有一个名为MIDB的函数，其作用与MID完全一样，
不过MID仅适用于单字节文字，而MIDB函数则可用于汉字等双字节字符），利用该功能
我们就能从身份证号码中分别取出个人的出生年份、月份及日期，然后再加以适当的合
并处理即可得出个人的出生年月日信息。
提示：MID函数的格式为MID（text；start_num；num_chars）或
MIDB（text；start_num；num_bytes），其中Text是包含要提取字符的文本串；Start_num
是文本中要提取的第一个字符的位置（文本中第一个字符的start_num为1，第二个为
2……以此类推）；至于Num_chars则是指定希望MID从文本中返回字符的个数。
假定某单位人员管理希望通过身份证号码自动得出员工的出生年月日及性别，现以
此为例，将有关步骤向广大用户作一简要介绍：
1。 选中整个B列，然后执行“格式”菜单中的“单元格”命令，打开“单元格格
式”窗口。
2。 选择“数字”选项卡。
3。 在“分类”栏中选择“文本”选项，然后单击“确定”按钮，关闭“单元格格
式”窗口，将所有包含有身份证号码的单元格设置为文本格式。
4。将光标移至C3单元格中，然后输入“=〃19〃&MID（B3；7；2）&〃年〃&MID（B3；9；2）&〃月
〃&MID（B3；11；2）&〃日〃”内容。其中MID（B3；7；2）就是从身份证号码的第7位开始取2位
数，得出该员工的出生年份，MID（B3；9；2）就是得出该员工的出生月份，而
MID（B3；11；2）则是该员工的出生日期，这些信息再加上年、月、日等文字就会组成该员
工的准确出生年月日“1970年1月1日”。
5。 接下来我们应将光标移至D3单元格中，然后输入
“=IF（MID（B3；15；1）/2=TRUNC（MID（B3；15；1）/2）；〃女〃；〃男〃）”。这就表示取身份证号
码的第15位数，若能被2整除，这表明该员工为女性，否则为男性。
6。 最后我们只需利用自动填充功能对其他各个员工的出生年月日、性别进行填充
即可。
上面都是以15位身份证为例进行介绍的，18位身份证的操作方法与此类似，广大用
户若使用的是18位身份证，只需对有关函数的取值位置进行适当调整即可（如将
“=〃19〃&MID（B3；7；2）&〃年〃&MID（B3；9；2）&〃月〃&MID（B3；11；2）&〃日〃”修改为
“=MID（B3；7；4）&〃年〃&MID（B3；11；2）&〃月〃&MID（B3；13；2）&〃日〃”）。
这样就实现了由Excel自动填充员工出生年月日、性别的功能，从而极大地减轻了
用户录入数据时的工作量，有兴趣的读者不妨一试。
在EXCEL中增加自动填充序列
中文ＥＸＣＥＬ９７作为一种功能强大、技术先进的电子表格软件，给我们的办
公带来了极大方便。
在ＥＸＣＥＬ中提供了自动填充功能，我们在使用时，可以通过拖动“填充柄”来
完成数据的自动填充。例如要输入甲、乙、丙、丁……，可以先在指定单元格输入甲，
然后将鼠标移至单元格的右下角的小方块处，直至出现“＋”字，按住鼠标左键，向下
（右）拖动至目的单元格，然后松开即完成了自动填充。可是有时我们会发现有一些数
据序列不能自动填充，例如车间一、车间二、车间三等，填充方法有两种：
第一种：单击“菜单”栏上的“工具”，选“选项”→“自定义序列”，这时就可
以在“输入序列”栏输入要定义的序列。需要注意的是每输入完成一项就要回车一次，
表示一项已经输入完毕，全部输入完成以后单击“添加”→“确定”，这样我们自定义
的序列就可以使用了。
第二种：首先把你要添加的序列输入到一片相临的单元格内，例如要定义一个序列
：车间一、车间二、车间三，把这三项分别输入到单元Ｈ１：Ｈ３，单击“工具”→
“选项”→“自定义序列”→“导入”，在“导入序列所在的单元格”所指的对话框中
输入Ｈ１：Ｈ３，单击“导入”→“添加”→“确定”，这样新序列就产生了。
定义的序列如果不再使用，还可删除，方法是：单击“工具”→“选项”→“自定
义序列”，在“自定义序列”框中，单击要删除的序列，再单击“删除”→“确定”。
发掘Execl XP合并拆分技巧
本人在用Excel XP管理教职工档案时，经常遇到合并或拆分单元格数据的情况，经过一
段时间的使用后摸索出了一套自己的“合并与拆分”理论，供同行们参考。
所谓合并数据是指将两列中的数据合并到一列中，拆分数据是指将一列中的数据拆
分到两列中，图1为“毕业院校”和“专业”两列的拆分及合并的效果图。
合并数据
要将“毕业院校”和“专业”两列中数据合并为“毕业院校及专业”，有“真”、
“假”两种合并方法，所谓真合并就是将两列数据真正合并到一列中，而假合并表面上
看是合并为一列了，实际数据还是存在于两列中，具体操作跟我一起来看看吧。
1、真合并
如果将一列中的数据通过再输入合并到另一列中，那不是我们希望看到的解决办
法，快捷而准确的操作方法是：
（1）将需要合并的“毕业院校”和“专业”两列数据通过“剪切”、“粘贴”移动
到一个新工作表中，如命名为“合并”工作表。
（2）在“合并”工作表中将XLS文件另存为TXT文件，单击“文件→另存为”，保存
类型选择“文本文件（制表符分隔）”，单击＇保存＇按钮后根据提示连续单击两次＇确定＇
按钮即可。
（3）在Execl中单击＇打开＇按钮，重新打开TXT文件，在“文本导入向导→步骤之1”
中采用默认设置，单击＇下一步＇按钮，在“步骤之2”分隔符号中，去掉“Tab键”前复
选框的选择，单击＇完成＇按钮，这时两列中数据合并到一列中。
（4）将合并后的数据再通过“剪切”、“粘贴”操作移动到原工作表的原位置处，
合并操作完成。
提示：合并后的数据在原来两列数据（如毕业院校和专业）之间会有一个空格，而有
时根据实际需要去掉或加宽空格，可采取如下措施：
方法一：对TXT文件进行编辑
XLS文件中列与列之间数据转换为TXT文件后是以制表符分隔的，此时合并后的数据
在原来两列数据之间会有一个空格，改变TXT文件中两列之间距离（即改变空格的大
小），对应的XLS文件中原两列数据之间的距离也会随之发生改变，若TXT文件中两列数
据之间距离增大或减小为零，则合并的XLS文件中两列数据之间距离也会随之增大或减
小为零。如果你要将两列数据无缝合并，建议应用下面两种方法。
方法二：利用函数ConcateName
ConcateName函数可以实现将几个文本字符串合并为一个文本字符串，语法为：
ConcateName （text1；text2； ……），text1； text2； ……为1到30个将要合并成单个文
本项的文本项，这些文本项可以为文本字符串、数字或对单个单元格的引用，如图2所
示。
图2
方法三：利用“&”运算符
利用“&”运算符也可将几个文本字符串合并为一个文本字符串，“&”运算方式同
“＋”、“…”等运算方式一样，如图2所示。
2、假合并
我们也来一个“瞒天过海”吧，呵呵，在对单元格设置边框时，利用工具栏中的
“外部框线”将两个单元格框在一起，这样两个单元格中数据就在一个表格中了，打印
出来后保证不留一点“作假”痕迹。此招对付较少单元格效果不错，要应付大量的单元
格，奉劝你还是来点“真”的吧！否则够你忙的了。
拆分数据
合并方法掌握了，拆分也不难，拆分的方法与前面介绍的合并方法相似，先将XLS
文件另存为TXT文件，再对TXT文件进行编辑，将需要拆分的数据用“Tab”键或空格分
开，然后再用Excel重新打开编辑后的TXT文件，这样原来在一起的数据就被拆分开了。
处理含有大量信息的表格时，以上合并与拆分的方法很有效，有兴趣的朋友可以试
一试。＇＇
ExcelHome精华版整理
（2003年11月14日）
By 徒然客
从身份证号码中提取性别
Q：A1单元格中是15位的身份证号码，要在B1中显示性别（这里忽略15位和18位身份证号
码的判别）
B1=if（mod（right（A1；1）；2）》0；〃male〃；〃female〃）
请问这个公式有无问题，我试过没发现问题。但在某个网站看到作者所用的是如下公式
：
B1=if（mid（A1；15；1）/2=trunc（mid（A1；15；1）/2）；〃female〃；〃male〃）
A：leaf
道理都是一样的，不过你的公式比那个公式优质
提取性别（无论是15位还是18位）
=IF（LEN（A1）=15；IF（MOD（MID（A1；15；1）；2）=1；〃男〃；〃女
〃）；IF（MOD（MID（A1；17；1）；2）=1；〃男〃；〃女〃
如果身份证号的输入已是15或18位，用公式
=IF（MOD（LEFT（RIGHT（A1；（LEN（A1）=18）＋1））；2）；〃男〃；〃女〃
xls…》exe可以么？
A：Kevin
如果只是简单的转换成EXE，当然可以。
如果你指的是脱离Excel也可以运行，好像没听说过可以。
当然，通过DDE，是可以不运行Excel但调用它的所有功能的，但前提仍然是你的计算机
上已经安装了Excel
列的跳跃求和
Q：若有20列（只有一行），需没间隔3列求和，该公式如何做？
前面行跳跃求和的公式不管用。
A：roof
假设a1至t1为数据（共有20列），在任意单元格中输入公式：
=SUM（IF（MOD（TRANSPOSE（ROW（1：20））；3）=0；（a1：t1））
按ctrl＋shift＋enter结束即可求出每隔三行之和。
跳行设置：如有12行，需每隔3行求和
=SUM（IF（MOD（（ROW（1：12））；3）=0；（A1：A12）））
能否象打支票软件那样输入一串数字它自动给拆分成单个数字？
Q：如我输入123456。52它自动给拆成￥1 2 3 4 5 6 5 2 的形式并且随我输入的长度改
变而改变？
A：Chiu
我所知函数不多，我是这样做的，如有更方便的方法，请指点
例如：
在A1输入小写金额，则：
千万：B1=IF（A1》=10000000；MID（RIGHTB（A1*100；10）；1；1）；IF（A1》=1000000；〃￥〃；0））
百万：C1=IF（A1》=1000000；MID（RIGHTB（A1*100；9）；1；1）；IF（A1》=100000；〃￥〃；0））
十万：D1=IF（A1》=100000；MID（RIGHTB（A1*100；8）；1；1）；IF（A1》=10000；〃￥〃；0））
万：E1=IF（A1》=10000；MID（RIGHTB（A1*100；7）；1；1）；IF（A1》=1000；〃￥〃；0））
千：F1=IF（A1》=1000；MID（RIGHTB（A1*100；6）；1；1）；IF（A1》=100；〃￥〃；0））
百：G1=IF（A1》=100；MID（RIGHTB（A1*100；5）；1；1）；IF（A1》=10；〃￥〃；0））
十：H1=IF（A1》=10；MID（RIGHTB（A1*100；4）；1；1）；IF（A1》=1；〃￥〃；0））
元：I1=IF（A1》=1；MID（RIGHTB（A1*100；3）；1；1）；IF（A1》=0。1；〃￥〃；0））
角：J1=IF（A1》=0。1；MID（RIGHTB（A1*100；2）；1；1）；IF（A1》=0。01；〃￥〃；0））
分：K1=IF（A1》=0。01；RIGHTB（A1*100；1）；0）
网客
公式中最后一个0改为〃〃
如何编这个公式
Q：我想编的公式是： a/＇84 … （b×4）＇
其中a是一个数值，小于或等于84；b是包含字符C的单元格的个数；C是一个符号。
这个公式的关键是要统计出包含字符C的单元格的个数，可我不会。
A：dongmu
=a/（84…countif（b；〃=c〃）*4）
chwd
我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说“c”
在D2…D30中随机出现，在上述公式中要先统计出出现“c”的单元格的个数。这个公式
如何做？
再一次感谢！
受dongmu朋友公式的启发�
