图1
注意:为了让输入的身份证号码能正确显示出来,请将单元格设置为文本格式。
这是第二次了,市场部那边又来要求改数据。他们要改的是数据库某张表中类似商品价格等的数据,需要改的地方又多,我们上次是靠新来的兄弟一个个给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('Mi图老师crosoft.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按人头打出工资条,有用Word邮件合并功能的,也有用VBA功能的,也有采用编写公式直接产生的。可参看天极软件办公栏目的文章(Word、Excel配合按人头打印工资条、仅需一个公式让Excel按人头打出工资条)但我觉得,对于普通公司员工来说,这些方法都显得专业性太强。其实,变通一下,也可以不用公式,直接让Excel按人头打出工资条的,方法简单,适合Excel新手使用。
假定公司有员工100人,工资数据在A2:R101区域。在工作表的第一行A1:R1区域为工资项目,如图1所示。我们需要的工资条是第一行为工资项目,第二行为各员工的工资,第三行为空行,以便我们打印后分割。
图1
第一步:先选中A列,点击右键,在弹出的快捷菜单中点击插入命令,插入一个辅助列。在A2、A3、A4单元格分别输入数字2、5、8。选中这三TuLaoShi.com个单元格,拖动填充句柄向下至A101单元格,为这些单元格添加序号。
第二步:复制A1:R1单元格,然后在A102单元格单击,并粘贴。选中A102:R102单元格,将鼠标定位于填充句柄,按下右键,向下拖动填充句柄至R200单元格。松开右键,在弹出的菜单中选择复制单元格命令,如图2所示。这样就复制了99个工资项目,加上第一行的那个,正好100个。
图2
第三步:在A1单元格中输入数字1,在A102、A103、A104单元格分别输入数字4、7、10。然后选中A102:A104单元格向下拖动填充句柄,至A200单元格,为所有的工资项目添加序号。
第四步:在A201tuLaoShi.com、A202、A203分别输入数字3、6、9。然后选中这三个单元格,向下拖动填充句柄至A300单元格。
好了,现在您肯定明白了。我们在工资项目、工资数据、空行分别添加了相互间隔的序号。现在,我们只要根据A列序号,升序进行排列,那么就可以得到所需要的工资条了,如图3所示。
图3
最后要做的,就是选中A列数据,点击右键,在弹出的快捷菜单中选择隐藏命令,将该辅助列隐藏起来,如图4所示。
图4
Q:我记得Excel可以使用行号列标进行计算,但是怎么也搞不定。能不能详细介绍一下这个功能?
A:首先,选择工具菜单下的选项,打开重新计算标签,找到工作簿选项,勾选其中的接受公式标志选项。下面以具体实例进行说明:在工作表的B1和C1单元格中分别输入标题2005和2006;在A3和A4单元格中分别输入销售和成本;然后在单元格B2至C4之间输入一些数字。要计算并在B5和B6单元格中显示收入情况结果,则你只需在该单元格中输入=销售-成本即可。这是因为在同一列中可以省略列标。
轻松创建公式
要得出2006年与2005年收入之差,则只需在单元格中输入公式=2006收入-2005收入。
用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(),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等名称命名,以便将来在这些区域中输入不同的公式。
(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/ejc/)至此,我们的准备工作才算是完成了。现在我们可以将临时放到别处的总分粘贴回AA列单元格中了,再剩下的事儿就是用RANK函数排名的问题了。咱还是先根据语文成绩排名吧。
(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/ejc/)先在名称栏输入ywmc,回车,将AB列语文学科所对应的单元格全部选中,此时AB372单元格会处于被激活状态。我们只要在编辑栏输入公式=RANK(AA372,ywzf),并按下Ctrl+Enter就可以在全部选中的单元格中输入公式并得到名次结果了。最后的结果如图1所示。
其它学科的名次排定依此法办理。够简单吧?
至此,我们针对各学科的排名工作就算是大功告成了。