Excel技巧2.docx

Excel技巧2.docx

《Excel技巧2.docx》由会员分享，可在线阅读，更多相关《Excel技巧2.docx（12页珍藏版）》请在冰豆网上搜索。

Excel技巧2

Excel技巧

制作动态表头提高工作效率

用Excel来管理表格，表格的标题基本上是由名称加年份、月份和表格名称组成的。

每隔一段时间打印这些表格时，都要将所有表格标题中的部分内容（如单位名称、年份或月份）重新进行更改，操作时不仅繁琐，而且容易出错。

如果能做一个动态表头，将所有标题需要变动的部分做成单元格的引用，只要改动一处，所有的标题将一起跟着改变，这样就方便多了。

后来经过尝试实现了这个目标：

1.制作表格标题初始化界面

为了操作方便，我们先插入一张新工作表，命名为“初始化”。

在C7单元格中输入“请输入单位名称:

”的文字提示，这样D7单元格可用于输入单位名称。

接下来右击工具栏，选择“窗体”命令，打开“窗体”工具栏。

在C9单元格中输入“时间:

”，D9单元格用于显示标题中的年份。

单击“窗体”工具栏中的〔微调框〕按钮，在紧靠着D9单元格后绘制一个微调框对象。

类似地，在紧靠着F9单元格后面再绘制一个微调框用于调整表格标题中的月份。

在E9和G9单元格中分别输入“年”和“月”字，并设为右对齐。

右击“微调框1”，选择“设置控制格式”命令，此时会打开“对象格式”对话框，切换到其中的“控制”选项卡。

在“当前值”框中输入一个默认的年份如“2005”，在“最小值”和“最大值”框中输入年份的上限和下限，“步长”为“1”，在“单元格链接”框中输入要显示年份单元格的绝对地址如“$D$9”，设置完成后，单击〔确定〕按钮，这样微调框1和D9单元格就链接好了。

类似地，可以将“微调框2”和F9单元格链接起来，要注意的是最大值和最小值受到月份本身的限制。

2.设置表格标题

打开“加班记录”工作表，在表格标题位置处输入公式“=初始化!

D7&&初始化!

D9&&"年"&&初始化!

F9&&"月份"&&"加班记录"”，按回车键（此时还不能看到最终效果），设置好标题的格式。

类似地，只要稍作改变就可以将其他表格的标题一一制作好。

3.调试表格标题

以上操作完成后我们就可以来调试了。

回到“初始化”表中，输入学校名称并选择某年某月，此时返回到其他工作表中，我们就可以看到完整的表格标题了。

需要注意的是，用日期函数也可以让标题根据系统时间自动改变，但不方便调整到之前的某个年份或月份。

excel中以代号完成自动跳转

问：

平时工作中产品有很多规格，而且型号都很冗长，所有的规格都编上号如A、B、C、D，怎样在EXCEL中只输入一个代号，回车后其会自动跳转成对应的内容？

答：

单独用一个表把所有的型号放在那里，选中第一个含有型号的单元格，按以下操作：

插入菜单--名称---定义，名称填A，确定

你输入的时候填：

=A回车

则会达到你的要求

Excel四舍五入不再出错

作者：

祥华    来源：

家用电脑    投递者：

orz    [2008-01-0710:

58]    阅读：

5180人

∙204

顶一下

∙当我们在使用Excel电子表格制作数据报表时，为了避免大量小数的运算，常常会用到四舍五入的方法，但是这样做可能会造成数值运算的误差，而且不容易找到原因。

当我们在使用Excel电子表格制作数据报表时，为了避免大量小数的运算，常常会用到四舍五入的方法，但是这样做可能会造成数值运算的误差，而且不容易找到原因。

如果对计算出的数值精度要求不高，可以在Excel菜单栏上点击“格式”－“单元格”，在“单元格格式”窗口中设置“小数位数”的数字（如图1），决定保留几位小数，超过的位数就会被舍掉。

图1在“单元格格式”窗口中设置“小数位数”的数字

但是，这样会产生一个问题。

假设A的数值是34.52，四舍五入后是35；B是67.8，四舍五入后是68，按理说四舍五入后两数的相加值应该是103，可为什么Excel计算出的却是102呢？

原来，这是因为数字相加时，Excel按照没有四舍五入前的数字进行运算，才会发生实际与计算出的数值有差异的情况。

解决这个问题最简单的方法，就是在Excel菜单栏上单击“工具”－“选项”，在“重新计算”标签页中勾选“以显示精度为准”一项（如图2），然后点击“确定”按钮。

此时，程序会弹出一个提示信息告诉你这样做会造成计算数值的误差，然后点击“确定”即可。

图2勾选“以显示精度为准”一项

    如果你需要计算的资料是会计报表之类的信息，最好不要用这种方式处理。

    经过设置后，计算出的数值就变正确了，是不是很方便呢？

Excel表中身份证号码提取出生年月,性别,年龄的使用技巧

作者：

webmaster    来源：

    投递者：

stranger    [2008-08-2913:

19]    阅读：

7364人

∙9

顶一下

∙其实如果简单的从身份证号码中判断出该身份证主人的出生年月、性别、年龄是一件很简单的事，不过，我们在Excel里处理表格的时候，怎样快速地通过身份证号码字段就能在别的单元格中得到出生年月、性别、年龄这些字段...

1.Excel表中用身份证号码中取其中的号码用:

MID（文本,开始字符,所取字符数）;

2.15位身份证号从第7位到第12位是出生年月日，年份用的是2位数。

18位身份证号从第7位到第14位是出生的年月日，年份用的是4位数。

从身份证号码中提取出表示出生年、月、日的数字，用文本函数MID（）可以达到目的。

MID（）——从指定位置开始提取指定个数的字符（从左向右）。

对一个身份证号码是15位或是18位进行判断，用逻辑判断函数IF（）和字符个数计算函数LEN（）辅助使用可以完成。

综合上述分析，可以通过下述操作，完成形如1978-12-24样式的出生年月日自动提取：

假如身份证号数据在A1单元格，在B1单元格中编辑公式

=IF（LEN（A1）=15,MID（A1,7,2）&"-"&MID（A1,9,2）&"-"&MID（A1,11,2）,MID（A1,7,4）&"-"&MID（A1,11,2）&"-"&MID（A1,13,2））

回车确认即可。

如果只要“年-月”格式，公式可以修改为

=IF（LEN（A1）=15,MID（A1,7,2）&"-"&MID（A1,9,2）,MID（A1,7,4）&"-"&MID（A1,11,2））

3.这是根据身份证号码（15位和18位通用）自动提取性别的自编公式，供需要的朋友参考：

说明：

公式中的B2是身份证号

根据身份证号码求性别：

=IF（LEN（B2）=15,IF（MOD（VALUE（RIGHT（B2,3））,2）=0,"女","男"）,IF（LEN（B2）=18,IF（MOD（VALUE（MID（B2,15,1））,2）=0,"女","男"）,"身份证错"））

根据身份证号码求年龄：

=IF（LEN（B2）=15,2007-VALUE（MID（B2,7,2））,if（LEN（B2）=18,2007-VALUE（MID（B2,7,4））,"身份证错"））

4.Excel表中用Year\Month\Day函数取相应的年月日数据;

另一方法：

这是根据身份证号码（15位和18位通用）自动提取性别和出生年月的自编公式，供需要的网友参考：

说明：

公式中的B2是身份证号

1、根据身份证号码求性别：

=IF（LEN（B2）=15,IF（MOD（VALUE（RIGHT（B2,3））,2）=0,"女","男"）,IF（LEN（B2）=18,IF（MOD（VALUE（MID（B2,15,3））,2）=0,"女","男"）,"身份证错"））

2、根据身份证号码求出生年月：

=IF（LEN（B2）=15,CONCATENATE（"19",MID（B2,7,2）,".",MID（B2,9,2））,IF（LEN（B2）=18,CONCATENATE（MID（B2,7,4）,".",MID（B2,11,2））,"身份证错"））

3、根据身份证号码求年龄：

=IF（LEN（B2）=15,year（now（））-1900-VALUE（MID（B2,7,2））,if（LEN（B2）=18,year（now（））-VALUE（MID（B2,7,4））,"身份证错"））

用Excel制作自动记录的考勤表

作者：

佚名    来源：

pconline    投递者：

wenchao    [2009-05-0819:

14]    阅读：

3201人

∙4

顶一下

∙作为一项规章制度，几乎每个公司、部门都要进行考勤登记，其结果一般都和工资挂钩。

传统的考勤登记方式不外乎以下两种，你可以用Excel制作简单、快捷、准确的考勤表。

作为一项规章制度，几乎每个公司、部门都要进行考勤登记，其结果一般都和工资挂钩。

传统的考勤登记方式不外乎以下两种：

一是在制作、印刷好的考勤表上填写员工的姓名，然后由考勤员每天记录各员工的迟到、早退、病假、事假、旷工等情况，再在月末进行统计；另一种就是使用“打卡机”，在人员比较多的单位用得比较多，末了也要考勤员记录和统计。

传统考勤方式的缺点不言自明：

工作量大、出错几率高、需要专用设备、统计分析麻烦。

你可以用Excel制作简单、快捷、准确的考勤表。

1.制作考勤表的基本框架

首先要进行的工作当然就是新建工作簿，在工作表中输入姓名、日期，制订考勤表的基本框架。

（1）启动Excel，新建一个工作簿，命名为“2004年员工考勤表”，在工作簿中建立12个工作表，分别记录每个月的考勤状况，将每个月的工作表依次命名为“一月”、“二月”、“三月”等。

（2）在当月工作表的A1、B1单元格中分别输入“姓名”、“日期”，在A2单元格中输入第1个员工的姓名，接着选择A2、A3单元格，然后点击“合并及居中”按钮将两个单元格合并为1个单元格，按照以上方法录入其他员工的姓名。

（3）在单元格C1中输入“1-1”，程序将自动转成“1月1日”（这里假定考勤从1月1日开始）。

用鼠标点选单元格C1，并移动鼠标到单元格的右下角，待出现实心“十”字的填充柄后，按住左键拖动到需要的位置，这样在1月1日后便形成了一个日期序列。

（3）点击日期序列最后的“自动填充选项”智能填充标记，选择“以工作日填充”命令，程序便自动将星期六、日去掉，不计入考勤日期。

（4）默认情况下日期格式是某月某日，其实我们可以统一去掉前面的月。

框选整个日期行，然后点击“格式→单元格”菜单命令，在出现的“单元格格式”对话框中点击“数字”选项卡。

在“分类”列表中点选“自定义”项，在类型框中输入“d"日"”，然后点击“确定”按钮。

这样选区中的日期格式就全变了。

2.录制记录上下班时间的宏

在制作完考勤表的基本框架后，我们接着就需要录制一个记录员工上下班时间的宏：

（1）点选工作表中的任意单元格，然后点击“工具→宏→录制新宏”菜单命令，进入“录制新宏”对话框。

在“宏名”框中输入准备录制的宏的名称——“返回当前时间”，“确定”后即进入宏的录制阶段。

（2）输入公式“=NOW（）”，然后按回车键，接着点击“停止录制”工具栏中的“停止录制”按钮结束录制。

这样，“返回当前时间”宏便录制完了，执行此宏将在活动单元格中返回当前的日期和时间。

3.制作宏按钮

接下来就是在B列中制作一些按钮，用来调用“返回当前时间”的宏，以记录员工上下班时间。

下面我们就一起来看看其具体制作步骤：

（1）点击“视图→工具栏→绘图”菜单命令，打开“绘图”工具栏，接着选择“绘图”工具栏中的“绘图→对齐→对齐网格”项。

这一步的主要目的是使后面绘制的按钮尺寸正好和单元格一样大。

（2）点击“视图→工具栏→窗体”菜单命令，打开“窗体”工具栏，然后点击“窗体”工具栏上的“按钮”，接着在单元格B2中拖动鼠标绘制一个按钮，Excel将自动打开“指定宏”对话框，点选其中的“返回当前时间”宏，并点击“确定”按钮。

这一步的目的是将“返回当前时间”宏指定给按钮，这样，点击按钮便会执行此宏。

（3）点击按钮，将按钮上的文字更改为“上班”，并对文字大小、字体进行设置。

然后点击“绘图”工具栏上的“选择对象”按钮，并按住Ctrl键，用鼠标将刚才制作的按钮拖到B3中，即将制作好的按钮复制到B3中，并将按钮上的问题更改为“下班”。

注意：

如果不按下“绘图”工具栏中的“选择对象”按钮，则点击按钮后，将执行“返回当前时间”的宏命令。

（4）确认“绘图”工具栏上的“选择对象”按钮被选中，点击“下班”按钮，然后按住Shift键再点击“上班”按钮，将两个按钮同时选中。

点击Ctrl键，用鼠标将选中的两个按钮拖动复制到B列的其余单元格中。

4.记录出勤

有了前面的制作，出勤的记录就非常简单了：

只需点选待插入上下班时间的单元格，点击同一行中的“上班”或“下班”按钮即可。

当然如果是“事假”、“病假”等则需要直接输入。

不过点击“上班”、“下班”按钮返回了很长一串数字，其实我们只需要诸如“13:

30”这样的时间即可。

因此，需要作如下设置：

选中整个考勤记录区域，然后点击“格式→单元格”菜单命令，在出现的“单元格格式”对话框中点击“数字”选项卡，在分类框中选中“时间”，在“类型”框中点中“13:

30”，最后点击“确定”即可。

接下来我们需要做的一件事情是：

把出勤记录由公式结果转换成具体的时间数值。

否则当公式被重新计算后，其结果将更改。

选中表中的所有数据单元格，点击右键选择“复制”命令，点选“编辑→选择性粘贴”菜单命令，在“选择性粘贴”对话框中选中“数值”项，然后点击“确定”按钮即可。

5.月末统计

一般来说，月末会是考勤员最忙的时候。

为什么呢？

因为他们需要对整月的考勤进行统计，但在本例中，只需要用简单的公式和函数，便能很容易进行月末统计，具体操作如下：

（1）在日期行之后的单元格中依次输入“迟到”、“早退”、“病假”、“事假”等需要统计的项目。

并将这几列中的单元格上下两两合并，使之对应于姓名行。

（2）点击单元格Z2，然后键入公式“=COUNTIF （C2:

Y2，">830"）”（这里假设上班时间为8:

30），并按回车键，Z2单元格中便会出现“XX”所有迟于8:

30上班的工作日天数。

同理在AA2单元格中输入公式“=COUNTIF（C3:

Y3，"<17:

30”"）”（假设下班时间为17:

30），并按回车键，AA2单元格中便会出现“XX”所有早于17:

30下班的工作日天数。

（3）点击AB2单元格输入公式“=COUNTIF （C2:

Y3，"事假"）”，并按回车键确认，AB2单元格中便出现了“XX”本月的事假次数。

按照类似的方法在单元格AC2、AD2、AE2输入公式，公式的具体内容跟AB2中的类似，只需将“事假”改成相应的“年假”、“病假”、“产假”即可。

这样“马英伟”的本月考勤就统计完了。

（4）接下来其他人的统计就不用这么麻烦了，我们可以利用Excel的公式和相对引用功能，用拖拉的方法瞬间便能完成所有人的考勤统计。

用鼠标选中Z2:

AE2区域，将鼠标移到所选区域的右下角，等光标变成黑色的实心“十”字状时按住左键往下拖，直到最后一行。

（5）点击“工具→选项”菜单项，在出现对话框中点击“重新计算”选项卡，并点击“重算活动工作表”按钮。

这样所有员工的考勤就全部统计出来了。

注意：

一般无法将所有员工整个月的考勤在一个屏幕中显示出来，这时我们就需要对窗口进行拆分和冻结。

此例中可以将“姓名”、“日期”所在的列和行冻结起来，这样不管我们怎样移动滚动条，都能看到相应的姓名和日期。

6.改进宏

前面宏命令的实质就是向当前活动单元格中插入一个返回当前系统时间的公式。

为了不让因重新计算而改变各单元格的数值，我们前面采用“复制→选择性粘贴”的方法，将公式结果转化成数值。

其实，我们只要对宏命令稍加修改便能解决此问题，避免每次无聊的重复操作。

具体修改步骤如下：

（1）点击“工具→宏→宏”菜单命令，在出现的“宏”窗口中选择“返回当前时间”，然后点击“编辑”按钮。

（2）在弹出来的VB编程器窗口，我们可以看到“返回当前时间”宏的程序代码，将语句“Active Cell.FormulaR1C1 = "=NOW（）"”改成“ActiveCell = NOW（）”，然后保存退出。

这样，再点击“上班”、“下班”按钮，插入活动单元格中的就不再是“=NOW（）”公式，而是具体的时间数值了。

以后不管重算多少次，插入的数值绝不会随系统时间的变化改动一次。

Excel条件格式醒目标注条件单元格

作者：

佚名    来源：

华军    投递者：

wenchao    [2009-11-2620:

22]    阅读：

42人

∙0

顶一下

∙对于Excel表格中的不同数据，我们可以按照不同的条件和要求设置它显示的格式，以便把不同的数据更加醒目地表示出来，这就是Excel单元格中条件格式的应用。

对于Excel表格中的不同数据，我们可以按照不同的条件和要求设置它显示的格式，以便把不同的数据更加醒目地表示出来，这就是Excel单元格中条件格式的应用。

也就是说，我们可以根据单元格中数据所满足的不同条件为单元格设置不同的格式。

那么，如果我们希望某单元格的格式根据其他单元格数据所满足的条件来进行相应设置，能不能实现呢？

当然能，在Excel2007中，用条件格式同样可以让这种希望变成现实。

如图1所示工作表，考试号在A列，总分成绩在G列，毕业学校则在H列，各科成绩分布在C至F列，现在我们根据指定的条件为相应单元格设置格式。

一、根据单条件设置

现在我们希望把毕业学校为“工业职专”的所有学生的考试号填充颜色，并设置字体加粗。

当然，不能进行排序等操作。

先选中A2单元格，点击功能区“开始”选项卡“样式”功能组中“条件格式”按钮下的小三角形，在弹出的菜单中点击“新建规则”命令，打开“新建格式规则”对话框。

选中“选择规则类型”列表中“使用公式确定要设置格式的单元格”项目，在“为符合此公式的值设置格式”下方的输入框中输入公式“=$H2="工业职专"”（注：

不包含外边的双引号）。

然后再点击对话框右下角的“格式”按钮，打开“设置单元格格式”对话框。

在新打开的对话框中点击“字体”选项卡，设置“字形”为“加粗”。

再点击“填充”选项卡，在颜色列表中点击需要的颜色，一路确定下来，关闭所有对话框。

最后，选中A2单元格，点击功能区“开始”选项卡“剪贴板”功能组中“格式刷”命令按钮，然后刷选A3以下至最后一行的全部单元格。

现在就可以看到，所有“毕业学校”为“工业职专”的A列单元格均实现了指定的格式（图2）。

二、根据多条件设置

有时候用来设置格式的条件比较复杂，可能要根据多个条件来设置。

比如设置“毕业学校”为“工业职专”、G列“总分”大于等于580分的考试号所在单元格填充颜色并设置字体加粗。

那么该如何设置呢?

其实还是使用前面的方法，在“新建格式规则”对话框的公式输入框中输入公式“=AND（$H2="工业职专",$G2>580）”（注：

不包含外边的双引号），然后设置相应的格式。

别忘了完成后要用格式刷刷选A列其他的单元格区域，就一切OK了。