數(shù)據(jù)庫與excel的幾個技巧

1、數(shù)據(jù)庫與excel的幾個技巧一、例子：如何實現(xiàn)關(guān)聯(lián)表字段更新有三個表分別是銷售表、客戶表、地區(qū)表：銷售表有兩個字段分別是客戶編碼和地區(qū)名稱,其中地區(qū)名稱都為空；客戶表有兩個字段是客戶編碼和地區(qū)代碼，地區(qū)表有兩個字段是地區(qū)代碼和地區(qū)名稱，現(xiàn)在想用sql語句更新銷售表的地區(qū)名稱字段為地區(qū)表中對應(yīng)的地區(qū)名稱，如何實現(xiàn)？UPDATE多表更新（轉(zhuǎn)）(2008-05-12 15:29:04)轉(zhuǎn)載標(biāo)簽：update多表更新sql 分類：php網(wǎng)絡(luò)編程 在開發(fā)中,數(shù)據(jù)庫來回?fù)Q,而有些關(guān)鍵性的語法又各不相同,這是一件讓開發(fā)人員很頭痛的事情.本文總結(jié)了Update語句更新多表時在SQL Server,Oracle

2、,MySQL三種數(shù)據(jù)庫中的用法.我也試了SQLite數(shù)據(jù)庫,都沒成功,不知是不支持多表更新還是咋的. 在本例中: 我們要用表gdqlpj中的gqdltks,bztks字段數(shù)據(jù)去更新landleveldata中的同字段名的數(shù)據(jù),條件是當(dāng) landleveldata 中的GEO_Code字段值與gdqlpj中的lxqdm字段值相等時進(jìn)行更新. SQL Server語法:UPDATE table_name WITH ( < table_hint_limited > .n ) | view_name | rowset_function_limited SET column_name = e

3、xpression | DEFAULT | NULL | variable = expression | variable = column = expression ,.n FROM < table_source > ,.n WHERE < search_condition > | WHERE CURRENT OF GLOBAL cursor_name | cursor_variable_name OPTION ( < query_hint > ,.n ) SQL Server示例: update a set a.gqdltks=b.gqdltks,a.b

4、ztks=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

5、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多表關(guān)聯(lián)修改關(guān)聯(lián)字段的值sql語句update cfg_dealer t   set vincerange = (sel

6、ect vinceid                            from cde_city a                 

7、0;         where vincename = vincerange and a.cityname=t.cityrange),       t.cityrange     = (select a.cityid               &

8、#160;            from cde_city a                           where a.cityname = t.cityrange    &#

9、160;                        and vincename = vincerange)where t.cid = '3130'將城市表中的省份id城市id賦值給經(jīng)銷商表中的省份城市高亮效果If e.Row.RowType = DataControlRowType.DataRow Then  

10、;          '當(dāng)鼠標(biāo)放上去的時候 先保存當(dāng)前行的背景顏色并給附一顏色             e.Row.Attributes.Add("onmouseover", "currentcolor=this.style.backgroundColor;this.style.backgroundColor='Azure',this.styl

11、e.fontWeight=''")            '當(dāng)鼠標(biāo)離開的時候 將背景顏色還原的以前的顏色             e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=currentcolor,this.style.fontWe

12、ight=''")        End If一個UPDATE 語句中更新兩個關(guān)聯(lián)表中的多個字段?大家好，我是新手。有個問題需要大家?guī)兔纯?。我需要在一個UPDATE 語句中更新兩個關(guān)聯(lián)表中的多個字段，我試寫了下面的語句，結(jié)果報錯如下：第 1 行: '（' 附近有語法錯誤。；第 4 行: '=' 附近有語法錯誤。請各位高手幫我看看。謝謝大家！update （select a.daco,b.branch,c.custcd,c.statcod2 from #accntab

13、_temp a inner join dwdbsmas_temp b on (um=substring(b.custcod,1,6) and a.ccy=b.ccycode and a.brcr=b.brca )                                             

14、0;                   inner join tblcustcode c on (um=c.custcd)        where  ( um between '260000' and '269993') and substring(a.acod,1,7)='2507009' and   (duct=

15、'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.ldbl<>0 and len(a.acod)=7 and substring(b.branch,1,7)<>'F' ）set acod=

16、substring(acod,1,7)+'2060201',daco=(case when abs(ldbl)=amt then amt when abs(ldbl)>amt then amt when abs(ldbl)<amt then abs(ldbl) end ) ,branch=substring(branch,1,6)+'F'例：如何在excel中篩選出兩列中相同的數(shù)據(jù)并配對排序假定原數(shù)據(jù)在SHEET1工作表中，請在SHEET2工作表A1單元格輸入公式：=IF(ROW()>SUM(IF(COUNTIF(Sheet1!C:C,Shee

17、t1!$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+回車結(jié)束。將

18、公式向下復(fù)制。在SHEET2工作表B1單元格輸入公式：=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,)將公式向下復(fù)制。在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

19、()-SUM(IF(COUNTIF(Sheet1!A:A,Sheet1!$C$1:$C$3),1),A1)公式以CTRL+SHIFT+回車結(jié)束。將公式向下復(fù)制。在SHEET2工作表D1單元格輸入公式：=IF(C1="","",VLOOKUP(C1,Sheet1!C:D,2,)將公式向下復(fù)制。假設(shè)數(shù)據(jù)從A2,B2開始在A列后插入輔助列B列，在b2輸入=IF(ISERROR(VLOOKUP(A2,C:E,3,0),"",0)，下拉至結(jié)尾，在c列后插入輔助列D列，在D2輸入=IF(ISERROR(VLOOKUP(C2,A:B,2,0),&q

20、uot;",1)，下拉至結(jié)尾，然后選擇A,B列，數(shù)據(jù)-排序，在出現(xiàn)的對話框中，主要關(guān)鍵字選“B列”，次要關(guān)鍵字選“A列”，確定，再選擇C,D列，數(shù)據(jù)-排序，在出現(xiàn)的對話框中，主要關(guān)鍵字選“D列”，次要關(guān)鍵字選“C列”，確定即可滿足要求了。例：A.B兩列.如何找出兩例相同的數(shù)據(jù).其實是B列包含A列所有的數(shù)據(jù).也就是在B列找出A列的內(nèi)容.然后排在A列前面.？假設(shè)這些數(shù)據(jù)沒有標(biāo)題行的話：（如果C列有數(shù)據(jù)的情況下），請在A列和B列后面插入一列然后，在C列的C1單元格里輸入公式： =VLOOKUP(B1,$A$1:$A$150,1,FALSE)上述公式的意思是，當(dāng)B1與“A1至A150”中一個

21、數(shù)據(jù)相同，則返回這個數(shù)據(jù)到C1單元格。在這個公式當(dāng)中用到了絕對引用的概念，意思是在你向C2及C3等單元格拖公式的時候，這個范圍不會因復(fù)制公式而變化，“$”就是這個作用。例如上面的公式當(dāng)用鼠標(biāo)拖動復(fù)制到C2單元格的時候就會是： =VLOOKUP(B2,$A$1:$A$15,1,FALSE)在用EXCEL里“VLOOKUP”這個函數(shù)的時候切記兩個關(guān)鍵點：1。A1至A150這個范圍里的數(shù)據(jù)一定要升序排列。（當(dāng)然，根據(jù)需要，你的范圍不一定是A1至A150，也可能是A1至A2800）2。要查找的這一列一定要在第一列也即A列，否則會出錯。VLOOKUP公式簡述：（需要搜索的值，所搜索的范圍，返回第幾列的值

22、，精確還是模糊匹配）其中：-要搜索的值是B1，-搜索范圍為A1至A150（在第一列，并升序排列）-返回的是第一列里的值-精確（EXCEL里的函數(shù)說明我看不明白，但一定是用false,不信你用true試試結(jié)果一定不對)如有不明白，可以到EXCEL里查看這個函數(shù)的幫助。兩種用于比較 Microsoft Excel 工作表兩列中的數(shù)據(jù)和查找重復(fù)項的方法。方法 1：使用工作表公式若要使用工作表公式比較兩列中的數(shù)據(jù)，請按照下列步驟操作： 1. 啟動 Excel。 2. 在新的工作表中，輸入下面的數(shù)據(jù)（保留列 B 為空）： 3. A1:1 B1:C1: 34. A2:2 B2:C2: 55. A3:3 B

23、3: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 中，在“編輯”組中單擊“填充”，然后單擊“向下”。重復(fù)的數(shù)字顯示在列 B 中，如下所示： 11. A1:1 B1:C1: 312. A2:2 B

24、2:2 C2: 513. A3:3 B3:3 C3: 814. A4:4 B4:C4: 215. A5:5 B5:5 C5: 0回到頂端方法 2：使用 Visual Basic 宏Microsoft 提供的編程示例只用于說明目的，不附帶任何明示或默示的保證。這包括但不限于對適銷性或特定用途適用性的默示保證。本文假定您熟悉所演示的編程語言和用于創(chuàng)建和調(diào)試過程的工具。Microsoft 支持工程師可以幫助解釋某個特定過程的功能。但是，他們將不會修改這些示例以提供額外的功能，也不會構(gòu)建過程以滿足您的特定要求。 要使用 Visual Basic 宏比較兩列中的數(shù)據(jù)，請按照下列步驟操作： 1. 啟動 E

25、xcel。 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. '

26、 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 compar

27、e 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. 輸入下面的數(shù)據(jù)（保留 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: 03

28、0. 選擇區(qū)域 A1:A5。 31. 在 Excel 2003 及較早版本的 Excel 中，指向“工具”菜單上的“宏”，然后單擊“宏”。在 Excel 2007 中，單擊“開發(fā)工具”選項卡，然后單擊“代碼”組中的“宏”。 32. 單擊 Find_Matches，然后單擊執(zhí)行。重復(fù)的數(shù)字顯示在列 B 中。匹配的數(shù)字將放在第一列的旁邊，如下所示： A1:1 B1:C1: 3A2:2 B2:2 C2: 5A3:3 B3:3 C3: 8A4:4 B4:C4: 2A5:5 B5:5 C5: 0列如 把不同的信息用其他顏色表示出來，或者標(biāo)出來也可以，但是順序不能變就是把第二列的和第一列的比較，不相同的區(qū)

29、分出來，但第二列和第一列的原順序不變, 用格式->條件格式 就可以了用條件格式注意我用的是EXCEL2010 與其它EXCEl對話框稍有不同，需要注意最左邊要用公式為，右邊為圖中的公式，設(shè)好字體樣式，最后用格式刷復(fù)制格式。 excel如何按顏色篩選數(shù)據(jù) 具體如何操作的，謝謝是2003年，應(yīng)該怎么做呢是單元格的填充顏色，不是字體有顏色的，2007的，數(shù)據(jù)-篩選-按顏色-選擇顏色2003版本的貌似沒這個功能。沒有直接的功能，不過有間接的，一是函數(shù)：Sub 篩選a列填充色為淡黃色的行() For i = 2 To 100 If Cells(i, 1).Interior.ColorIndex &

30、lt;> 6 Then Rows(i).Hidden = True End If Next MsgBox ("這是篩選結(jié)果") Rows().Hidden = False End Sub你可以建分別篩選紅色、黃色.以及顯示全部的按鈕，為每個按鈕輸入代碼。二是添加輔助列：請參考下面的地址，動態(tài)圖講的非常詳細(xì)了1595-1-1.html用第一種函數(shù)吧例：用EXCEL如何查找兩列中相同與不同的數(shù)據(jù)？在D2列輸入： =IF(ISNA(VLOOKUP(C2,$A$2:$B$700,2,0),"",VLOOKUP(C2,$A$2:$B$700,2,0) 向下填

31、充公式。 在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) 向下填充公式。我們工作中經(jīng)常會遇到這種需求，有兩個數(shù)據(jù)表，想要知道兩個表的公共部分和獨有部分，高級篩選就可以達(dá)到此目的。例如庫房里有一個總的件號明細(xì)表，今天有一個銷售明細(xì)表，想要知道今天銷售的那些是庫房里還有的，哪些是庫房里沒有的，并分別表示在兩個表里，其實就是求出兩個表的公共部分

32、，就可以用高級篩選功能來實現(xiàn)。 實例：如上圖，左方是庫房存貨，右方是今天銷售的貨號，想求出兩者的公共部分。 步驟一：把光標(biāo)放在左方數(shù)據(jù)表的任意單元格，從“數(shù)據(jù)篩選高級篩選”里調(diào)出“高級篩選”對話框，并在“數(shù)據(jù)(列表)區(qū)域”和“條件區(qū)域”分別如圖示填上內(nèi)容。 步驟二：點擊確定按鈕得到如下圖 步驟三：在庫存表的最右一列里填上一個“1”，并把“1”復(fù)制到整個表的最右一列。如下圖。 步驟四：點擊菜單“數(shù)據(jù)篩選全部顯示”(2007里是“清除”按鈕)，使篩選后隱藏的內(nèi)容顯示出來。如下圖。 步驟五：再次調(diào)出“高級篩選”對話框，這次把上次的“條件區(qū)域”和“數(shù)據(jù)(列表)區(qū)域”互換，填上如上圖示的內(nèi)容，點擊確定，

33、得出結(jié)果后在右側(cè)的數(shù)據(jù)區(qū)域里寫上一個“1”并復(fù)制到整列，再點擊菜單“數(shù)據(jù)篩選全部顯示”(2007里是“清除”按鈕)，使篩選后隱藏的內(nèi)容顯示出來。 標(biāo)有“1”的數(shù)據(jù)行就是兩個表的公共部分，沒有標(biāo)“1”的行就是獨有部分。 技巧要點： 1、要用于篩選的兩個列的標(biāo)題行內(nèi)容必需一致，如本例中A列和H列的標(biāo)題都是“代號”，并且在填寫條件時的“數(shù)據(jù)區(qū)域”和“條件區(qū)域”里的內(nèi)容要包含有標(biāo)題，如本例是“$H$1H$6”，而不是“$H$1H$6”。 2、為什么要標(biāo)“1”并復(fù)制。因為篩選實際上上是隱藏不符合條件的行，而在隱藏狀態(tài)下，許多操作都是不行的，所以要取消隱藏，而取消隱藏后，結(jié)果就看不到了，所以要在隱藏狀態(tài)下

34、給符合條件的行最后加上一個“1”以示區(qū)別，這樣當(dāng)取消隱藏后仍能根據(jù)是否有“1”而看到結(jié)果。 3、用于篩選的兩列里不能有空白單元格，如本例里的兩個“代號”列，要連續(xù)，不能有空白單元格。 4、結(jié)果顯示出來后，隱藏的是整行，所以你在看左面的數(shù)據(jù)結(jié)果時會發(fā)現(xiàn)右邊的數(shù)據(jù)表也少了行數(shù)Excel中提供了兩種數(shù)據(jù)的篩選操作，即“自動篩選”和“高級篩選”。如何區(qū)分這兩種篩選模式，以便熟練掌握和應(yīng)用，讓我們來看看吧： 自動篩選“自動篩選”一般用于簡單的條件篩選，篩選時將不滿足條件的數(shù)據(jù)暫時隱藏起來，只顯示符合條件的數(shù)據(jù)。圖1為某單位的職工工資表，打開“數(shù)據(jù)”菜單中“篩選”子菜單中的“自動篩選” 命令，以“基本工資

35、”字段為例，單擊其右側(cè)向下的列表按鈕，可根據(jù)要求篩選出基本工資為某一指定數(shù)額或篩選出基本工資最高（低）的前10個（該數(shù)值可調(diào)整）記錄。還可以根據(jù)條件篩選出基本工資在某一范圍內(nèi)符合條件的記錄，“與”、“或”來約束區(qū)分條件。如圖2，根據(jù)給定條件篩選出基本工資大于等于300且小于350的記錄。另外，使用“自動篩選”還可同時對多個字段進(jìn)行篩選操作，此時各字段間限制的條件只能是“與”的關(guān)系。如篩選出“基本工資”和“職務(wù)工資”都超過380的記錄。 高級篩選 “高級篩選”一般用于條件較復(fù)雜的篩選操作，其篩選的結(jié)果可顯示在原數(shù)據(jù)表格中，不符合條件的記錄被隱藏起來；也可以在新的位置顯示篩選結(jié)果，不符合的條件的記

36、錄同時保留在數(shù)據(jù)表中而不會被隱藏起來，這樣就更加便于進(jìn)行數(shù)據(jù)的比對了。 例如我們要篩選出“基本工資”或“職務(wù)工資”超過380且“實發(fā)”工資超過700的符合條件的記錄，用“自動篩選”就無能為力了，而“高級篩選”可方便地實現(xiàn)這一操作。如圖3所示，將“基本工資”、“職務(wù)工資”和“實發(fā)”三字段的字段名稱復(fù)制到數(shù)據(jù)表格的右側(cè)（表格中其他空白位置也可以），在圖中所示位置輸入條件，條件放在同一行表示“與”的關(guān)系，條件不在同一行表示“或”的關(guān)系。圖4即為上述操作在新的位置（B20起始位置）篩選的結(jié)果。兩種篩選操作的比較由此我們不難發(fā)現(xiàn)，“自動篩選”一般用于條件簡單的篩選操作，符合條件的記錄顯示在原來的數(shù)據(jù)表格

37、中，操作起來比較簡單，初學(xué)者對“自動篩選”也比較熟悉。若要篩選的多個條件間是“或”的關(guān)系，或需要將篩選的結(jié)果在新的位置顯示出來那只有用“高級篩選”來實現(xiàn)了。 一般情況下，“自動篩選”能完成的操作用“高級篩選”完全可以實現(xiàn)，但有的操作則不宜用“高級篩選”，這樣反而會使問題更加復(fù)雜化了，如篩選最大或最小的前幾項記錄。 在實際操作中解決數(shù)據(jù)篩選這類問題時，只要我們把握了問題的關(guān)鍵，選用簡便、正確的操作方法，問題就能迎刃而解了。Excel 中如何找出兩列數(shù)據(jù)中不重復(fù)的記錄在工作中經(jīng)常會遇到這樣的需求：現(xiàn)在有兩列數(shù)據(jù)，要在 A 列中找出 B 列中沒有的記錄，在 B 列中找出 A 列中沒有的記錄?，F(xiàn)在和大家分享一下這個方法。我們先用一個簡單的例子看一下?，F(xiàn)在有兩列數(shù)據(jù)，可以是分別在不同的數(shù)據(jù)單(sheet)中。我們先來做個簡單的眼力測驗，看下面圖中左側(cè)，兩列數(shù)據(jù)中不重復(fù)的部分把它找出來。收起這個圖片展開這個圖片這里數(shù)據(jù)比較少，我們很容易發(fā)現(xiàn)：(左) 列的的“周”、“鄭”、“王”是 (右) 列沒有的。(右) 列的“李”、“吳”是 (左) 列沒有的。現(xiàn)在我們看一下在 Excel 中如何用公式來實現(xiàn)這里功能。稍微有點難度，但是我們每步都會講解得盡可能清晰，請不要擔(dān)心。 1. 我們先需要