怎么让Excel按人头打印出工资条?

怎么让Excel按人头打印出工资条?

 　　使用Excel按人头打出工资条，有用Word邮件合并功能的，也有用VBA功能的，也有采用编写公式直接产生的。可参看天极软件办公栏目的文章(Word、Excel配合按人头打印工资条、仅需一个公式让Excel按人头打出工资条)但我觉得，对于普通公司员工来说，这些方法都显得专业性太强。其实，变通一下，也可以不用公式，直接让Excel按人头打出工资条的，方法简单，适合Excel新手使用。

　　假定公司有员工100人，工资数据在A2:R101区域。在工作表的第一行A1:R1区域为工资项目，如图1所示。我们需要的工资条是第一行为工资项目，第二行为各员工的工资，第三行为空行，以便我们打印后分割。

　　图1

　　第一步：先选中A列，点击右键，在弹出的快捷菜单中点击插入命令，插入一个辅助列。在A2、A3、A4单元格分别输入数字2、5、8。选中这三个单元格，拖动填充句柄向下至A101单元格，为这些单元格添加序号。

　　第二步：复制A1:R1单元格，然后在A102单元格单击，并粘贴。选中A102:R102单元格，将鼠标定位于填充句柄，按下右键，向下拖动填充句柄至R200单元格。松开右键，在弹出的菜单中选择复制单元格命令，如图2所示。这样就复制了99个工资项目，加上第一行的那个，正好100个。

　　图2

　　第三步：在A1单元格中输入数字1，在A102、A103、A104单元格分别输入数字4、7、10。然后选中A102:A104单元格向下拖动填充句柄，至A200单元格，为所有的工资项目添加序号。

　　第四步：在A201、A202、A203分别输入数字3、6、9。然后选中这三个单元格，向下拖动填充句柄至A300单元格。

　　好了，现在您肯定明白了。我们在工资项目、工资数据、空行分别添加了相互间隔的序号。现在，我们只要根据A列序号，升序进行排列，那么就可以得到所需要的工资条了，如图3所示。

　　图3

　　最后要做的，就是选中A列数据，点击右键，在弹出的快捷菜单中选择隐藏命令，将该辅助列隐藏起来，如图4所示。

　　图4

Excel利用“选择性粘贴”进行计算

 　　Excel在工作中的应用非常广泛，今天我们介绍一个比较特别的使用技巧：选择性粘贴中的运算。

　　考试成绩出来了，但在利用Excel进行统算时遇到了一点小麻烦，原因是物理和化学试卷满分各为100分，但领导要求与中考统一(本地区中考物理满分为80分，化学为70分)，也就是需要分别折成80%和70%，但在H2单元格中输入=H2*0.8回车后出现不能计算该公式。公式中的单元格引用指向的是公式结果，从而造成循环引用。的错误提示，如图1。

　　图1(点击看大图)

　　虽然后来利用添加辅助列(利用公式把最终折算后的分数添加到辅助列中)的方法解决了，但总觉得麻烦。经过反复查找后终于利用选择性粘贴在原数据上直接完成了折算。具体方法如下：

　　1、在一空白单元格中输入0.8，然后到其它任意单击元格中单击一下，再返回右键单击0.8所在的单元格，左键选择复制。

　　2、选中物理这列下的所有原始分数，然后单击编辑→选择性粘贴，在打开的选择性粘贴对话框中的运算项下选择乘，最后点确定退出，就能把物理这列的原始分数一次性折算为80%了。最后再把0.8删除就大功告成了，如图2。

　　图2(点击看大图)

在Excel中怎样使用行列号进行计算

 　　Q：我记得Excel可以使用行号列标进行计算，但是怎么也搞不定。能不能详细介绍一下这个功能?

　　A：首先，选择工具菜单下的选项，打开重新计算标签，找到工作簿选项，勾选其中的接受公式标志选项。下面以具体实例进行说明：在工作表的B1和C1单元格中分别输入标题2005和2006;在A3和A4单元格中分别输入销售和成本;然后在单元格B2至C4之间输入一些数字。要计算并在B5和B6单元格中显示收入情况结果，则你只需在该单元格中输入=销售-成本即可。这是因为在同一列中可以省略列标。

　　轻松创建公式

　　要得出2006年与2005年收入之差，则只需在单元格中输入公式=2006收入tuLaoShi.com-2005收入。

Excel实例:在间隔数据中排定名次

 　　用Excel表格做了学生考试成绩汇总表，格式如图1所示。需要根据AA列中相应科目的成绩排出名次，并将结果放到AB列相应的单元格中。

　　图1

　　排定名次要使用RANK函数，其语法是RANK(number,ref,order)其中，参数number是需要找到排位的数字;而参数ref则是数字列表的引用;第三个参数如果省略则是按降序排列，这正是我们需要的。比如公式=RANK(A3,A2:A6)的意思就是要得到A3单元格数据在A2:A6单元格数据中的排名。

　　显然，在本例中要使用RANK函数，但有一个问题是必须要解决的。假设我们要针对语文学科的总分来排名次。学生们的语文总tulaoshi.com成绩分布在AA4、AA12、AA20等单元格中，所处单元格区域并不连续，每8行有一个成绩。怎样才能将它们选中，并作为排名的区域呢?

　　这个问题也曾经困扰了我很长时间，最后发现利用求余数函数可以使问题得到顺利解决。不过，有些准备工作是要做的。

　　还是以语文成绩的排名为例。

　　先将AA列的数据复制到别的位置，等准备工作完成后再粘贴回来。

　　先选中AA4单元格，输入公式=1/(MOD(ROW(),8)-4)，回车后即可发现出现#DIV/0!的错误提示。拖动该单元格填充句柄向下至AA379，则会从AA4单元格开始，每8行出现相同的错误提示，其它各行均为数字。

　　点击功能区开始选项卡编辑功能组查找和选择按钮，在弹出的菜单中点击定位条件命令，打开定位条件对话框。选中公式单选项，并只保留选中随后出现的错误复选项，如图2所示。确定后就可以发现，凡是出现错误的提示的单元格就处于被选中状态了。

　　图2

　　现在点击功能区公式选项卡定义的名称功能组定义名称按钮，在打开的新建名称对话框的名称输入框中输入ymzf。确定关闭对话框。

　　以后只要我们在名称框中输入ymzf，回车，就可以再次选中AA列中全部语文学科对应的单元格了，如图3所示。

　　图3

　　按照上面的方法，只要能让错误提示分别出现在相应学科所在行，那么就可以利用定位条件来选中它们。因为语文学科所处的单元格所在行除以8的余数为4，所以我们采用公式=1/(MOD(ROW(),8)-4)制造了除数为0的错误提示。那么数学、英语等其它学科则可以分别根据其行数除以8的余数不同，重复上面的操作过程，只是将公式分母中-4分别变成-5、-6、-7、-0、1、2、3就可以了。将所到的各学科区域分别以sxzf、yyzf等名称命名。

　　但准备工作仅仅做这些还是不够的。因为我们排出的名次应该放在AB列而不是AA列。所以我们还要在再用上面的

　　方法在AB列中选中各学科对应的区域，并分别以ymmc、sxmc、yymc等名称命名，以便将来在这些区域中输入不同的公式。

　　至此，我们的准备工作才算是完成了。现在我们可以将临时放到别处的总分粘贴回AA列单元格中了，再剩下的事儿就是用RANK函数排名的问题了。咱还是先根据语文成绩排名吧。

　　先在名称栏输入ywmc，回车，将AB列语文学科所对应的单元格全部选中，此时AB372单元格会处于被激活状态。我们只要在编辑栏输入公式=RANK(AA372,ywzf)，并按下Ctrl+Enter就可以在全部选中的单元格中输入公式并得到名次结果了。最后的结果如图1所示。

　　其它学科的名次排定依此法办理。够简单吧?

　　至此，我们针对各学科的排名工作就算是大功告成了。

把Excel表中数据导入数据库

 　　这是第二次了，市场部那边又来要求改数据。他们要改的是数据库某张表中类似商品价格等的数据，需要改的地方又多，我们上次是靠新来的兄弟一个个给Update进去的，这次老大去教了他们Update语句，把烦人的皮球踢给他们了。但这样一个个更新很明显不是办法，我想通过excel直接把数据导入数据库应该是可行的吧，就开始找方法了

　　我想至少有这样两种比较容易实现的方法：

　　1、直接用Sql语句查询

　　2、先用excle中的数据生成xml文件，再把xml导入数据库

　　第一种方法(方法二以后再试)，找到联机丛书里实现此功能的Sql语句如下：

　　SELECT*

　　FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0',

　　'DataSource="c:Financeaccount.xls";UserID=Admin;Password=;Extendedproperties=Excel5.0')...xactions

　　语句是有了，但我还是试了很久，因为各个参数具体该怎么设置它没有说。Data Source就是excel文件的路径，这个简单;UserId、Password和Extended properties这三个属性我改成了各种各样的与本机有关的用户名、密码以及excel版本都不对，最后用上面例子里的值User ID=Admin;Password=;Extended properties=Excel 5.0才成功了，晕啊;最后个xactions更是查了很多资料，其实就仅仅是excel文件里所选的工作表名而已，怪我对excel不够熟悉了，另外注意默认的Sheet1要写成[Sheet1$]

　　最后，看看我成功的测试

　　数据库里建好一个表testTable_1，有5个字段id, name, date, money, content，C盘下book1.xls文件的sheet1工作表里写好对应的数据并设好数据类型，执行如下插入语句：

　　insertintotestTable_1([name],[date],[money],[content])

　　Select[姓名],[日期],[金额],[内容]

　　FROMOpenDataSource('Microsoft.Jet.OLEDB.4.0',

　　'DataSource="C:Book1.xls";

　　UserID=Admin;Password=;Extendedproperties=Excel5.0')...[Sheet1$]

　　select里的列名我一开始用*代替，但发现输出顺序与我预期的不同，是金额、内容、日期、姓名，不知道具体有什么规律，就老老实实写名字了。操作成功

　　回过头来看看市场部的要求，假设在我这张表里实现，可以先判断如excel里存在与记录相同的name字段(name要唯一非空)时就删除记录，之后再插入，这样简单，但自增的id字段会因为插入而改变，那是不行的了。可行的方法是先读出excel里全部记录，然后用游标一条条分析，如果存在这个name就更新否则就插入。OK，下次就不用让他们再对着文档一条条Update了

Excel中将15位身份证号转换为18位

 　　假设A列自A2起是身份证号(15位或18位)。www.Tulaoshi.com

　　1、身份证号全部改为18位，输入数组公式：=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19)

,ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

　　注意：数组公式输入方法：输入公式后不要按回车，而是按Ctrl+Shift+Enter。

　　2、身份证号全部改为15位，输入公式：=IF(LEN(A2)=15,A2,LEFT(REPLACE(A2,7,2,),15))

　　3、计算出生日期：=IF(A2"",TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")+0,)

　　4、判断性别：=IF(A2"",IF(MOD(RIGHT(LEFT(A2,17)),2),"男","女"),)

　　最终结果如图：

Excel:重复名次也可以查姓名成绩

 　　当老师的，对分析学生成绩大概有瘾。这不，本来我们已经把学生各学科的成绩、总分、名次都排出来了，并按照总分进行了升序排序，但现在又有任课老师过来要求希望能够把自己学科的前10名的学生姓名及成绩找出来。按理说，这个要求并不是很困难，但是麻烦就在于学生各科名次有可能相同，这样的话，前10名的学生其实不一定是10个人，有可能更多。每个学科都要这么做的话，工作量也不小，所以，还是得靠函数和公式来帮忙。

　　图1 原始成绩表

　　原始的成绩表如图1所示。姓名位于C2:C92单元格，语文成绩位于D2:D92单元格区域。我们就以查找语文学科的前10名成绩及学生姓名为例。为方便比较结果，图1中我们已经将数据按语文成绩降序进行了排序，实际操作中是不需要事先排序的。

　　一、名次表的建立

　　前面我们说过，我们不太容易确定排在前10名的学生共有多少，所以，我们需要使用公式将它们找出来。当然，最好顺便将名次表填写出来。完成结果如图2所示。

　　图2 成绩排序

　　将鼠标定位于X3单元格，然后在编辑栏输入公式=TEXT(SUMPRODUCT(($D$2:$D$92=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92)),"第G/通用格式名")，回车后就可以得到第1名的结果。选定X3单元格，向下拖动其填充句柄至出现第11名为止。

　　这里用到了几个函数，感觉上比较复杂。其实思路是这样的：ROW(1:1)的结果是1，而LARGE($D$2:$D$92,1)的结果是在指定的单元格区域中最大的一个数;那么公式中($D$2:$D$92=LARGE($D$2:$D$92,ROW(1:1)))可以理解为拿D2:D92单元格区域中的数据与该区域中最大值比较，大于或等于该值及小于该值的则会分别以TRUE、FALSE的结果保存在一个数组中。

　　公式中COUNTIF($D$2:$D$92,$D$2:$D$92))部分则会统计D2:D92单元格区域中每一个数值出现的次数，也分别保存到一个数组中。所以，我们所用公式中SUMPRODUCT(($D$2:$D$92=LARGE($D$2:$D$92,ROW(1:1)))/COUNTIF($D$2:$D$92,$D$2:$D$92))在执行时会得到一个类似于SUMPRODUCT({TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;}/{1;1;2;2;1;2;2;1;2;2;2;2;1;})的结果。两个数组中的对应的数据分别相除，再将所有的商相加，正是分数所对应的名次。这种方法即使名次是并列的，也不会影响显示效果。

　　至于最外层的TEXT函数，则是将得到的结果转换为按指定数字格式表示的文本。也就是本来内层公式运算的结果是数字1，现在我们将它显示为第1名。

　　二、分数的查找

　　将鼠标定位于Y3单元格，在编辑栏中输入如下公式=INDEX($D$2:$D$92,MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),$

D$2:$D$92+1/ROW($D$2:$D$92),0))，然后按下Ctrl+Sh

　　ift+Enter快捷键，完成数组公式的输入。这一步很关键的，否则不会出现正确的结果。

　　向下拖动Y3单元格的填充句柄向下至最后一个单元格完成公式的复制。

　　我们还是简单解释一下公式的思路。

　　由于D2:D92区域中有很多数据是重复的，这给我们造成了困难。所以，我们要想办法使每一数据都变成唯一。公式中$D$2:$D$92+1/ROW($D$2:$D$92)就是给D2:D92区域中每一个数据都加了该数据对应行数的倒数。由于每一数据对应的行数是不一样的，这样，就会使每一数据都变成了唯一的值，并保存到了一个数组中。

　　公式中的LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1))还是返回了上面所得数组中的最大值。本例中的结果是{96.5｝。

　　公式中MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),$D$2:$D$92+1/ROW($D$2:$D$92),0)返回的是刚刚得到的最大值在数组中的位置。本例中的结果是{1｝。

　　这样，其实Excel最后执行的查询就是INDEX($D$2:$D$92,1)了，自然可以返回在$D$2:$D$92区域中的第一个值了。

　　三、姓名的查找

　　将鼠标定位于Z3单元格，在编辑栏中输入公式=INDEX($C$2:$C$92,MATCH(LARGE($D$2:$D$92+1/ROW($D$2:$D$92),ROW(1:1)),

$D$2:$D$92+1/ROW($D$2:$D$92),0))，同样按下Ctrl+Shift+Enter快捷键完成数组公式的输入。

　　向下拖动Z3单元格的填充句柄向下至最后一个单元格完成公式的复制。最后的效果如图3所示。

　　图3 完成公式的复制(点击看大图)

　　其实您肯定已经明白了，姓名的查找与前面分数的查找是一样的。公式本身也没有什么大的变化。所以，明白了前面的方法，要查找别的什么东西也就方便了。

　　其它的学科可以照此办理。只要注意变换一下公式中的单元格区域就可以了，我这里就不罗嗦了。