数据库与excel的几个技巧.doc

数据库与excel的几个技巧一、例子：如何实现关联表字段更新有三个表分别是销售表、客户表、地区表：销售表有两个字段分别是客户编码和地区名称,其中地区名称都为空；客户表有两个字段是客户编码和地区代码，地区表有两个字段是地区代码和地区名称，现在想用sql语句更新销售表的地区名称字段为地区表中对应的地区名称，如何实现？UPDATE多表更新（转）(2008-05-12 15:29:04)转载标签：update多表更新sql 分类：php网络编程 在开发中,数据库来回换,而有些关键性的语法又各不相同,这是一件让开发人员很头痛的事情.本文总结了Update语句更新多表时在SQL Server,Oracle,MySQL三种数据库中的用法.我也试了SQLite数据库,都没成功,不知是不支持多表更新还是咋的. 在本例中: 我们要用表gdqlpj中的gqdltks,bztks字段数据去更新landleveldata中的同字段名的数据,条件是当 landleveldata 中的GEO_Code字段值与gdqlpj中的lxqdm字段值相等时进行更新. SQL Server语法:UPDATE table_name WITH ( .n ) | view_name | rowset_function_limited SET column_name = expression | DEFAULT | NULL | variable = expression | variable = column = expression ,.n FROM ,.n WHERE | WHERE CURRENT OF GLOBAL cursor_name | cursor_variable_name OPTION ( ,.n ) SQL Server示例: update a set a.gqdltks=b.gqdltks,a.bztks=b.bztks from landleveldata a,gdqlpj b where a.GEO_Code=b.lxqdm Oracle语法: UPDATE updatedtable SET (col_name1,col_name2.)= (SELECT col_name1,col_name2. FROM srctable WHERE where_definition) Oracel 示例: update landleveldata a set (a.gqdltks, a.bztks)= (select b.gqdltks, b.bztks from gdqlpj b where a.GEO_Code=b.lxqdm)MySQL语法: UPDATE table_references SET col_name1=expr1 , col_name2=expr2 . WHERE where_definition MySQL 示例: update landleveldata a, gdqlpj b set a.gqdltks= b.gqdltks, a.bztks= b.bztks where a.GEO_Code=b.lxqdm多表关联修改关联字段的值sql语句update cfg_dealer t set vincerange = (select vinceid from cde_city a where vincename = vincerange and a.cityname=t.cityrange), t.cityrange = (select a.cityid from cde_city a where a.cityname = t.cityrange and vincename = vincerange)where t.cid = 3130;将城市表中的省份id城市id赋值给经销商表中的省份城市高亮效果If e.Row.RowType = DataControlRowType.DataRow Then 当鼠标放上去的时候 先保存当前行的背景颜色并给附一颜色 e.Row.Attributes.Add(onmouseover, currentcolor=this.style.backgroundColor;this.style.backgroundColor=Azure,this.style.fontWeight=;) 当鼠标离开的时候 将背景颜色还原的以前的颜色 e.Row.Attributes.Add(onmouseout, this.style.backgroundColor=currentcolor,this.style.fontWeight=;) End If一个UPDATE 语句中更新两个关联表中的多个字段?大家好，我是新手。有个问题需要大家帮忙看看。我需要在一个UPDATE 语句中更新两个关联表中的多个字段，我试写了下面的语句，结果报错如下：第 1 行: （ 附近有语法错误。；第 4 行: = 附近有语法错误。请各位高手帮我看看。谢谢大家！update （select a.daco,b.branch,c.custcd,c.statcod2 from #accntab_temp a inner join dwdbsmas_temp b on (um=substring(b.custcod,1,6) and a.ccy=b.ccycode and a.brcr=b.brca ) inner join tblcustcode c on (um=c.custcd) where( um between 260000 and 269993) and substring(a.acod,1,7)=2507009 and (duct=MA and b.txtype=FD ) or (duct=FD and b.txtype=NM ) or (duct=CA and b.txtype=CD ) )and substring(b.custcod,7,1)=S and a.ldbl0 and len(a.acod)=7 and substring(b.branch,1,7)F ）set acod=substring(acod,1,7)+2060201,daco=(case when abs(ldbl)=amt then amt when abs(ldbl)amt then amt when abs(ldbl)SUM(IF(COUNTIF(Sheet1!C:C,Sheet1!$A$1:$A$3),1),IF(ROW()COUNTA(Sheet1!A:A),INDEX(Sheet1!A:A,SMALL(IF(COUNTIF(Sheet1!C:C,Sheet1!$A$1:$A$3),65536,ROW($1:$3),ROW()-SUM(IF(COUNTIF(Sheet1!C:C,Sheet1!$A$1:$A$3),1),INDEX(Sheet1!A:A,SMALL(IF(COUNTIF(Sheet1!C:C,Sheet1!$A$1:$A$3),ROW($1:$3),65536),ROW()公式以CTRL+SHIFT+回车结束。将公式向下复制。在SHEET2工作表B1单元格输入公式：=IF(A1=,VLOOKUP(A1,Sheet1!A:B,2,)将公式向下复制。在SHEET2工作表C1单元格输入公式：=IF(ROW()SUM(IF(COUNTIF(Sheet1!C:C,Sheet1!$A$1:$A$3),1),IF(ROW()COUNTA(Sheet1!C:C),INDEX(Sheet1!C:C,SMALL(IF(COUNTIF(Sheet1!A:A,Sheet1!$C$1:$C$3),65536,ROW($1:$3),ROW()-SUM(IF(COUNTIF(Sheet1!A:A,Sheet1!$C$1:$C$3),1),A1)公式以CTRL+SHIFT+回车结束。将公式向下复制。在SHEET2工作表D1单元格输入公式：=IF(C1=,VLOOKUP(C1,Sheet1!C:D,2,)将公式向下复制。假设数据从A2,B2开始在A列后插入辅助列B列，在b2输入=IF(ISERROR(VLOOKUP(A2,C:E,3,0),0)，下拉至结尾，在c列后插入辅助列D列，在D2输入=IF(ISERROR(VLOOKUP(C2,A:B,2,0),1)，下拉至结尾，然后选择A,B列，数据-排序，在出现的对话框中，主要关键字选“B列”，次要关键字选“A列”，确定，再选择C,D列，数据-排序，在出现的对话框中，主要关键字选“D列”，次要关键字选“C列”，确定即可满足要求了。例：A.B两列.如何找出两例相同的数据.其实是B列包含A列所有的数据.也就是在B列找出A列的内容.然后排在A列前面.？假设这些数据没有标题行的话：（如果C列有数据的情况下），请在A列和B列后面插入一列然后，在C列的C1单元格里输入公式： =VLOOKUP(B1,$A$1:$A$150,1,FALSE)上述公式的意思是，当B1与“A1至A150”中一个数据相同，则返回这个数据到C1单元格。在这个公式当中用到了绝对引用的概念，意思是在你向C2及C3等单元格拖公式的时候，这个范围不会因复制公式而变化，“$”就是这个作用。例如上面的公式当用鼠标拖动复制到C2单元格的时候就会是： =VLOOKUP(B2,$A$1:$A$15,1,FALSE)在用EXCEL里“VLOOKUP”这个函数的时候切记两个关键点：1。A1至A150这个范围里的数据一定要升序排列。（当然，根据需要，你的范围不一定是A1至A150，也可能是A1至A2800）2。要查找的这一列一定要在第一列也即A列，否则会出错。VLOOKUP公式简述：（需要搜索的值，所搜索的范围，返回第几列的值，精确还是模糊匹配）其中：-要搜索的值是B1，-搜索范围为A1至A150（在第一列，并升序排列）-返回的是第一列里的值-精确（EXCEL里的函数说明我看不明白，但一定是用false,不信你用true试试结果一定不对)如有不明白，可以到EXCEL里查看这个函数的帮助。两种用于比较 Microsoft Excel 工作表两列中的数据和查找重复项的方法。方法 1：使用工作表公式若要使用工作表公式比较两列中的数据，请按照下列步骤操作： 1. 启动 Excel。 2. 在新的工作表中，输入下面的数据（保留列 B 为空）： 3. A1:1 B1:C1: 34. A2:2 B2:C2: 55. A3:3 B3:C3: 86. A4:4 B4:C4: 27. A5:5 B5:C5: 08. 在单元格 B1 中键入以下公式： =IF(ISERROR(MATCH(A1,$C$1:$C$5,0),A1)9. 选择单元格 B1:B5。 10. 在 Microsoft Office Excel 2003 和 Excel 的较早版本中，指向“编辑”菜单上的“填充”，然后单击“向下”。在 Microsoft Office Excel 2007 中，在“编辑”组中单击“填充”，然后单击“向下”。重复的数字显示在列 B 中，如下所示： 11. A1:1 B1:C1: 312. A2:2 B2:2 C2: 513. A3:3 B3:3 C3: 814. A4:4 B4:C4: 215. A5:5 B5:5 C5: 0回到顶端方法 2：使用 Visual Basic 宏Microsoft 提供的编程示例只用于说明目的，不附带任何明示或默示的保证。这包括但不限于对适销性或特定用途适用性的默示保证。本文假定您熟悉所演示的编程语言和用于创建和调试过程的工具。Microsoft 支持工程师可以帮助解释某个特定过程的功能。但是，他们将不会修改这些示例以提供额外的功能，也不会构建过程以满足您的特定要求。 要使用 Visual Basic 宏比较两列中的数据，请按照下列步骤操作： 1. 启动 Excel。 2. 按 Alt+F11 启动 Visual Basic 编辑器。 3. 在插入菜单上，单击模块。 4. 在模块表中输入下面的代码： 5. Sub Find_Matches()6. Dim CompareRange As Variant, x As Variant, y As Variant7. Set CompareRange equal to the range to which you will8. compare the selection.9. Set CompareRange = Range(C1:C5)10. NOTE:If the compare range is located on another workbook11. or worksheet, use the following syntax.12. Set CompareRange = Workbooks(Book2). _13. Worksheets(Sheet2).Range(C1:C5)14. 15. Loop through each cell in the selection and compare it to16. each cell in CompareRange.17. For Each x In Selection18. For Each y In CompareRange19. If x = y Then x.Offset(0, 1) = x20. Next y21. Next x22. End Sub23. 按 Alt+F11 返回 Excel。 24. 输入下面的数据（保留 B 列为空）： 25. A1:1 B1:C1: 326. A2:2 B2:C2: 527. A3:3 B3:C3: 828. A4:4 B4:C4: 229. A5:5 B5:C5: 030. 选择区域 A1:A5。 31. 在 Excel 2003 及较早版本的 Excel 中，指向“工具”菜单上的“宏”，然后单击“宏”。在 Excel 2007 中，单击“开发工具”选项卡，然后单击“代码”组中的“宏”。 32. 单击 Find_Matches，然后单击执行。重复的数字显示在列 B 中。匹配的数字将放在第一列的旁边，如下所示： A1:1 B1:C1: 3A2:2 B2:2 C2: 5A3:3 B3:3 C3: 8A4:4 B4:C4: 2A5:5 B5:5 C5: 0列如 把不同的信息用其他颜色表示出来，或者标出来也可以，但是顺序不能变就是把第二列的和第一列的比较，不相同的区分出来，但第二列和第一列的原顺序不变, 用格式-条件格式 就可以了用条件格式注意我用的是EXCEL2010 与其它EXCEl对话框稍有不同，需要注意最左边要用公式为，右边为图中的公式，设好字体样式，最后用格式刷复制格式。 excel如何按颜色筛选数据 具体如何操作的，谢谢是2003年，应该怎么做呢是单元格的填充颜色，不是字体有颜色的，2007的，数据-筛选-按颜色-选择颜色2003版本的貌似没这个功能。没有直接的功能，不过有间接的，一是函数：Sub 筛选a列填充色为淡黄色的行() For i = 2 To 100 If Cells(i, 1).Interior.ColorIndex 6 Then Rows(i).Hidden = True End If Next MsgBox (这是筛选结果) Rows().Hidden = False End Sub你可以建分别筛选红色、黄色.以及显示全部的按钮，为每个按钮输入代码。二是添加辅助列：请参考下面的地址，动态图讲的非常详细了/thread-351595-1-1.html用第一种函数吧例：用EXCEL如何查找两列中相同与不同的数据？在D2列输入： =IF(ISNA(VLOOKUP(C2,$A$2:$B$700,2,0),VLOOKUP(C2,$A$2:$B$700,2,0) 向下填充公式。 在E2列输入 =IF(ISNA(VLOOKUP(A2,$C$2:$D$700,1,0),A2,) 向下填充公式。 在F2列输入 =IF(ISNA(VLOOKUP(E2,$A$2:$B$700,2,0),VLOOKUP(E2,$A$2:$B$700,2,0) 向下填充公式。我们工作中经常会遇到这种需求，有两个数据表，想要知道两个表的公共部分和独有部分，高级筛选就可以达到此目的。例如库房里有一个总的件号明细表，今天有一个销售明细表，想要知道今天销售的那些是库房里还有的，哪些是库房里没有的，并分别表示在两个表里，其实就是求出两个表的公共部分，就可以用高级筛选功能来实现。 实例：如上图，左方是库房存货，右方是今天销售的货号，想求出两者的公共部分。 步骤一：把光标放在左方数据表的任意单元格，从“数据筛选高级筛选”里调出“高级筛选”对话框，并在“数据(列表)区域”和“条件区域”分别如图示填上内容。 步骤二：点击确定按钮得到如下图/excel/gao090408.html所示结果。 步骤三：在库存表的最右一列里填上一个“1”，并把“1”复制到整个表的最右一列。如下图。 步骤四：点击菜单“数据筛选全部显示”(2007里是“清除”按钮)，使筛选后隐藏的内容显示出来。如下图。 步骤五：再次调出“高级筛选”对话框，这次把上次的“条件区域”和“数据(列表)区域”互换，填上如上图示的内容，点击确定，得出结果后在右侧的数据区域里写上一个“1”并复制到整列，再点击菜单“数据筛选全部显示”(2007里是“清除”按钮)，使筛选后隐藏的内容显示出来。 标有“1”的数据行就是两个表的公共部分，没有标“1”的行就是独有部分。 技巧要点： 1、要用于筛选的两个列的标题行内容必需一致，如本例中A列和H列的标题都是“代号”，并且在填写条件时的“数据区域”和“条件区域”里的内容要包含有标题，如本例是“$H$1H$6”，而不是“$H$1H$6”。 2、为什么要标“1”并复制。因为筛选实际上上是隐藏不符合条件的行，而在隐藏状态下，许多操作都是不行的，所以要取消隐藏，而取消隐藏后，结果就看不到了，所以要在隐藏状态下给符合条件的行最后加上一个“1”以示区别，这样当取消隐藏后仍能根据是否有“1”而看到结果。 3、用于筛选的两列里不能有空白单元格，如本例里的两个“代号”列，要连续，不能有空白单元格。 4、结果显示出来后，隐藏的是整行，所以你在看左面的数据结果时会发现右边的数据表也少了行数Excel中提供了两种数据的筛选操作，即“自动筛选”和“高级筛选”。如何区分这两种筛选模式，以便熟练掌握和应用，让我们来看看吧： 自动筛选“自动筛选”一般用于简单的条件筛选，筛选时将不满足条件的数据暂时隐藏起来，只显示符合条件的数据。图1为某单位的职工工资表，打开“数据”菜单中“筛选”子菜单中的“自动筛选” 命令，以“基本工资”字段为例，单击其右侧向下的列表按钮，可根据要求筛选出基本工资为某一指定数额或筛选出基本工资最高（低）的前10个（该数值可调整）记录。还可以根据条件筛选出基本工资在某一范围内符合条件的记录，“与”、“或”来约束区分条件。如图2，根据给定条件筛选出基本工资大于等于300且小于350的记录。另外，使用“自动筛选”还可同时对多个字段进行筛选操作，此时各字段间限制的条件只能是“与”的关系。如筛选出“基本工资”和“职务工资”都超过380的记录。 高级筛选 “高级筛选”一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。 例如我们要筛选出“基本工资”或“职务工资”超过380且“实发”工资超过700的符合条件的记录，用“自动筛选”就无能为力了，而“高级筛选”可方便地实现这一操作。如图3所示，将“基本工资”、“职务工资”和“实发”三字段的字段名称复制到数据表格的右侧（表格中其他空白位置也可以），在图中所示位置输入条件，条件放在同一行表示“与”的关系，条件不在同一行表示“或”的关系。图4即为上述操作在新的位置（B20起始位置）筛选的结果。两种筛选操作的比较由此我们不难发现，“自动筛选”一般用于条件简单的筛选操作，符合条件的记录显示在原来的数据表格中，操作起来比较简单，初学者对“自动筛选”也比较熟悉。若要筛选的多个条件间是“或”的关系，或需要将筛选的结果在新的位置显示出来那只有用“高级筛选”来实现了。 一般情况下，“自动筛选”能完成的操作用“高级筛选”完全可以实现，但有的操作则不宜用“高级筛选”，这样反而会使问题更加复杂化了，如筛选最大或最小的前几项记录。 在实际操作中解决数据筛选这类问题时，只要我们把握了问题的关键，选用简便、正确的操作方法，问题就能迎刃而解了。Excel 中如何找出两列数据中不重复的记录在工作中经常会遇到这样的需求：现在有两列数据，要在 A 列中找出 B 列中没有的记录，在 B 列中找出 A 列中没有的记录。现在和大家分享一下这个方法。我们先用一个简单的例子看一下。现在有两列数据，可以是分别在不同的数据单(sheet)中。我们先来做个简单的眼力测验，看下面图中左侧，两列数据中不重复的部分把它找出来。收起这个图片展开这个图片这里数据比较少，我们很容易发现：(左) 列的的“周”、“郑”、“王”是 (右) 列没有的。(右) 列的“李”、“吴”是 (左) 列没有的。现在我们看一下在 Excel 中如何用公式来实现这里功能。稍微有点难度，但是我们每步都会讲解得尽可能清晰，请不要担心。 1. 我们先需要把两列数据定义到“数据块”中，并且分别为它们起个名字。分别叫“左边”和“右边”。相当于上面图中的 (左) 列和 (右) 列。 2. 我们选中 (左) 列中所有的数据（这里位于第一张数据单的 A 列），然后选公式选项卡 - 定义名称 - 名称中填上“左边