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

【 tulaoshi.com - excel 】

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

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

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

　　轻松创建公式

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

把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$]

　　最后，看看我成功的测试

tulaoshi　　数据库里建好一个表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实例:在间隔数据中排定名次

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

　　图1

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

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

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

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

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

　　先选中AA4单元格，输入公式=1/(MOD(ROW()Tulaoshi.com,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:重复名次也可以查姓名成绩

 　　当老师的，对分析学生成绩大概有瘾。这不，本来我们已经把学生各学科的成绩、总分、名次都排出来了，并按照总分进行了升序排序，但现在又有任课老师过来要求希望能够把自己学科的前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 完成公式的复制(点击看大图)

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

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

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

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

　　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列表实现批量录入功能

 　　我们在使用Excel的过程中，通常需要输入大量的数据。这是保证我们顺利完成各项工作的基础。但是，在录入数据的过程中，尤其是录入大量数据的时候，经常会出现一些不经意的录入错误，而这，会严重影响我们所得到的结果正确性。那么，怎样才能有效地控制这种录入错误的发生呢?除了细心、细心、再细心以外，在Excel中完成必要的设置，以最大可能地减少错误的发生也是极为重要的。

　　一、设置数据列表

　　有时我们需要录入的数据是某些重复数据中的一个，比如单位员工所属的部门。单位中部门个数是有限的，如果我们都通过键盘手工录入每位员工的工作部门，那自然是费时费力，还容易出错的。因此，我们不如为这些部门指定一个数据列表。录入时只需要在下拉列表中单击选择相应的部门，就可以了。这不仅可以提高录入速度，还会使得录入的质量得到保证。

　　首先选中要填写员工部门的所有单元格，点击功能区数据选项卡数据工具功能组中的数据有效性按钮，在弹出的菜单中点击数据有效性命令，打开数据有效性对话框。

　　点击对话框中设置选项卡，在允许下方的下拉列表中选择序列选项，然后在下面来源输入框中输入各部门名称(人事部,一车间,tuLaoShi.com二车间,生产部,技术部,办公室)，部门之间用英文的逗号隔开，如图1所示。点击确定按钮关闭对话框。

　　图1 Excel设置序列

　　我们也可以在工作表的空白单元格某列中分别输入各个部门名称，比如在H1:H6单元格区域。然后在来源下的输入框中输入=$H$1:$H$6，也可以得到同样的效果。

　　现在将鼠标定位于刚才选中的那些单元格区域任一单元格，就会在右边出现一个下拉箭头，点击它就会出现刚才我们所设置的下拉列表，如图2所示，单击其中的项目就可以完成输入了。

　　图2 Excel中的下拉列表

　　二、在其它工作表中使用

　　如果我们希望能在其它的工作表单元格区域中使用这个部门下拉列表，那么我们可以使用自定义名称完成这个任务。

　　在空白单元格列中录入相应部门名称，比如H1:H6单元格区域。然后选中此单元格区域，点击功能区公式选项卡定义的名称功能组中的定义名称按钮，打开新建名称对话框。如图3所示，在名称右侧的输入框中输入名称，比如bumen。在范围下拉列表中选择工作簿，而在引用位置右侧的输入框中会自动使用我们选中的单元格区域。确定后，就可以为我们所选的单元格区域指定bumen的名称了。

　　图3 Excel新建工作簿

　　现在要做的，就是在选定工作表的相应单元格区域后，再打开数据有效性对话框，然后在来源输入框中输入=bumen，就可以在当前的工作表中使用这个部门列表了。

　　如果觉得这个自定义名称的方法有些罗嗦的话，那下面的方法就简单多了。

　　选中已经设置好数据有效性的单元格，然后按下Ctrl+C键进行复制。再将鼠标定位于目标单

　　元格，点击功能区开始选项卡剪贴板功能组中的粘贴按钮下的小三角形，在弹出的菜单中点击选择性粘贴，打开选择性粘贴对话框。选中有效性验证单选项就可以了，如图4所示。

　　图4 Excel选择性粘贴

Excel用SUMPRODUCT实现有条件排名

 　　前些日子市里搞了一次模拟考试，下发了汇总后的成绩表。全市三所学校各个专业的学生成绩都放到了一个工作表中，格式如图1所示。为了做好成绩分析，主任要求做好两个排名：一是排出每位学生在全市相同专业的学生中的名次;二是排出每位学生在本校本专业中的名次;两个排名都以总分为依据。

　　图1(点击看大图)

　　这个工作以前也做过，每次都得将数据按专业、按学校分别筛选出来复制到不同的工作表中，然后在不同的工作表中用RANK函数进行排序。全市三所学校一千多个学生，每所学校都有七到八个专业，所以这个筛选复制工作也是费时费力，筛选复制完成后还要在十多个工作表中进行排名工作，非常麻烦。不过这一次，工作完成得却异常顺利，只需要十分钟就可以完成全部的工作了。因为，这次我们使用了SUMPRODUCT函数来完成这个有条件的排名工作。具体实现过程如下：

　　一、准备工作

　　选定总分所在的H2:H1032单元格区域，点击功能区公式选项卡定义的名称功能组中定义名称按钮，在弹出的新建名称对话框名称输入框中输入为此区域定义的名称zongfen。此时，对话框下方的引用位置后的输入框中已经自动输入我们选定的单元格区域=对口!$H$2:$H$1032，如图2所示。

　　图2

　　按同样的方法，选定学校所在单元格区域I2:I1032、专业所在单元格区域J2:J1032，分别为它们指定名称xuexiao和zhuanye。

　　完成后，这准备工作就算是结束了。

　　二、排定名次

　　在K1单元格输入标题按专业排名。点击K2单元格，输入公式=SUMPRODUCT((zhuanye=$J2)*($H2

　　在L1单元格输入标题校内专业排名。点击L2单元格，输入公式=SUMPRODUCT((zhuanye=$J2)*($H2

　　图3(点击看大图)

　　如果您也遇到类似的问题，比如平行班的成绩汇总在一张工作表中，而我们又需要学生的班内名次，那么不妨照此办理一回，呵呵，那效果，真的是谁用谁知道啊。a