一些Excel公式的實用運用例子

一些Excel公式的實用運用例子=COUNTIF(D2:D10,">400")統計D2:D10的值大於400的個數=COUNTIF(B2:B10,"東北部")統計B2:B10的內容為"東北部"的個數=TODAY()顯示當前系統日期=NOW()顯示當前系統日期和具體時間=YEAR(B2)獲得B2單元格內(當前系統日期和具體時間)的年=MONTH(B2)獲得B2單元格內(當前系統日期和具體時間)的月=DAY(B2)獲得B2單元格內(當前系統日期和具體時間)的日=HOUR(B2)獲得B2單元格內(當前系統日期和具體時間)的時=RANK(D2,$D$2:$D$10)取D2的值在D2-D10範圍內的排名是多少=MATCH(99,C2:C10,0)統計出C2-C10範圍內值為99的個數=EXACT(A4,B4)比較A4,B4兩個單元格內的字元串內容是否相等，返回布爾值TRUE/FALSE=IF(C2>=60,IF(C2>=90,"優秀","及格"),"不及格")如果C2>=60 (如果C2>=90則顯示"優秀"否則顯示"及格") 否則顯示"不及格"=IF(AND(B2>=60,C2>=60),IF(OR(B2>=90,C2>=90),"優秀","及格"),"不及格")與上例相似，只不過是2個單元格都要進行條件判斷=VLOOKUP(B3,D2:G14,4,0)VLOOKUP（需在第一列中查找的數值,需要在其中查找數據的數據表,需返回某列值的列號,邏輯值True或False）經常用Excel建立一些表格，有時我們需要給一些表格建立很多個副表，那麼如何使這些複製表格中的數據隨原表的修改而修改呢？VLOOKUP函數可以幫我們做到這一點=HLOOKUP(B7,B1:F3,2,0)HLOOKUP與VLOOKUPHLOOKUP用於在表格或數值數組的首行查找指定的數值，並由此返回表格或數組當前列中指定行處的數值。VLOOKUP用於在表格或數值數組的首列查找指定的數值，並由此返回表格或數組當前行中指定列處的數值。當比較值位於數據表的首行，並且要查找下面給定行中的數據時，請使用函數 HLOOKUP。當比較值位於要進行數據查找的左邊一列時，請使用函數 VLOOKUP。語法形式為：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，Lookup_value表示要查找的值，它必須位於自定義查找區域的最左列。Lookup_value 可以為數值、引用或文字串。Table_array查找的區域，用於查找數據的區域，上面的查找值必須位於這個區域的最左列。可以使用對區域或區域名稱的引用。Row_index_num為 table_array 中待返回的匹配值的行序號。Row_index_num 為 1 時，返回 table_array 第一行的數值，row_index_num 為 2 時，返回 table_array 第二行的數值，以此類推。Col_index_num為相對列號。最左列為1，其右邊一列為2，依此類推.Range_lookup為一邏輯值，指明函數 HLOOKUP 查找時是精確匹配，還是近似匹配。檢查單元格 A2 是否為空白 (FALSE)=ISBLANK(A2)檢查 #REF! 是否為錯誤值 (TRUE)=ISERROR(A4)檢查 #REF! 是否為錯誤值 #N/A (FALSE)=ISNA(A4)檢查 #N/A 是否為錯誤值 #N/A (TRUE)=ISNA(A6)檢查 #N/A 是否為錯誤值 (FALSE)=ISERR(A6)檢查 10.72 是否為數值 (TRUE)=ISNUMBER(A5)檢查 COUNTRY 是否為文本 (TRUE)=ISTEXT(A3)檢查 5 是否為偶數 ISEVEN(5)FALSE檢查 -1 是否為奇數 ISODD(-1)TRUE1.excel兩張相同內容合併 execel中sheet1、 sheet2兩張表，有部分相同的內容，比如sheet1中a,b,c與sheet2中d,b,e的內容相同，現在要通過這些相同項關聯把sheet2中的e,f,h項copy到sheet1中對應的行中。如何用vba語句實現？十分著急！使用VBA？那是非常簡單的語句啊，使用循環即可。For I = 1 To 100 For J = 1 To 100 If Sheet1.Cells(I,1)=Sheet2.Cells(J,4) And .... Then Sheet1.Cells(I,n)=Sheet2.Cells(J,m) ... Exit For End If Next JNext I以上僅為示例，您稍加改動即可使用。不用vba用公式就可.在sheet1中.把a.b.c列的值串起來,放在單獨一列,如H 列,做為一個主鍵值.在sheet2中把d,e.f...........................如M列............然後在sheet中求e 列的值=vlookup(H,sheet2的範圍(如:Sheet2!A$1:M$100),3(e列的位置),false);f列=vlookup(H,sheet2的範圍(如:Sheet2!A$1:M$100),4(f列的位置),false);g列=vlookup(H,sheet2的範圍(如:Sheet2!A$1:M$100),5(g列的位置),false);////2.如何去掉execl單元格中文字前面的數字? 自己寫個函數放在模塊里，然後在單元格調用函數 =delnum(A1)Public Function delnum(zifu As String) As StringDim l As Integer, m As Integer, n As String, a As Stringl = Len(zifu)For m = 1 To ln = Mid(zifu, m, 1)If Asc(n) < 48 Or Asc(n) > 57 Thena = a & nEnd IfNext mdelnum = aEnd Function///3.excel中,列很多,行很少,怎麼能讓列印在一頁上? 使用公式先進行一下轉換就是了。以下為示例：源數據為數據區域A1:O2，即一個2行15列的數據，如下：A B C D E F G H I J K L M N O1 2 3 4 5 6 7 8 9 10 11 12 13 14 15先使用公式轉變為6行5列的數據，公式如下：[假設我們在A6單元格開始輸入公式，轉變後的數據區域為A6:E11]在單元格A6輸入以下公式：=INDIRECT(ADDRESS(IF(MOD(ROW(),2)=0,1,2),IF(MOD(COLUMN(),5)=0,5,MOD(COLUMN(),5))+INT((ROW()-6)/2)*5))並將該公式複製到數據區域A6:E11，我們可以看到，現在數據已經進行了轉換。結果為：A B C D E1 2 3 4 5F G H I J6 7 8 9 10K L M N O11 12 13 14 15公式說明：1.由於假定從單元格A6開始，因此IF(MOD(ROW(),2)=0,1,2)的結果為若為偶數行則指向第一行，否則指向第二行。2.MOD(COLUMN(),5)由於示例中指定了為5列。3.INT((ROW()-6)/2)*5)，示例中是從A6單元格開始的，因此減6行，5為列數。附加：如果不是正好滿列數，那麼應該進行一次判斷，如下：=If(Indirect(...)="","",Indirect(...))[Indirect(...)即上面示例中的公式]4.關於兩個excel表格內容進行比較 刪除的問題我現在有兩個excel表格，內容均在1000條以上。我現在要在兩個表格中找出「非共有」的紀錄。 例如：表1 表二 賬號 戶名 金額 賬號 戶名 金額 12345 ***** ***** 12345 ***** ***** 23456 ***** ***** 23456 ***** ***** 4567 ***** ***** 34567 ***** ***** 56789 ***** ***** 56789 ***** ***** 要在這兩個表格中找到表一的 4567 和 表二的 34567 因為數據量較大，手動查找太費事。假設你的賬號數據在sheet1和sheet2的A列,1000行數據定義2個名稱x=Sheet2!$A2:$A1000y=Sheet1!$A2:$A1000在sheet1的A1:A1000使用條件格式-公式,=ISERROR(MATCH(A1,x,0)),設置顏色.在sheet2的A1:A1000使用條件格式-公式,=ISERROR(MATCH(A1,y,0)),設置顏色.「非共有」的數據就可以找出來了.將(你的文件12).xls另存為(你的文件12).dbf,然後(你的文件1).dbf和(你的文件2).dbf用VFP命令連接:sele 1use (你的文件1).dbfsele 2use (你的文件2).dbfjoin with a for b->帳號=a->帳號 to c (兩個文件帳號必須對應一致,且都不重複,而且兩個文件中的:"賬號 戶名 金額"要在另存為(你的文件12).dbf時分別標成:"賬號 1戶名 1金額"；"賬號 2戶名 2金額"）如果想得到*..xls：只要導出*.xls就行了。use copy to 你的文件.xls type xl5 (office版本 xls 5)5.excel里A列為身份證號碼，要求在B列得出其出身日期？A列為個人的身份證號或企業代碼，身份證包括2類：15位的身份證，18位身份證。15位（440105610202453）的身份證的生日為1956-10-20；18位&nbsp;（440306197403150053）的身份證生日為1974-03-15。企業代碼不滿足15位或18位。現在要求在B列得到A列身份證號人的出生日期；若是企業代碼的不需要。=if(len(A1)=15,"19" & mid(A1,7,2) & "-" & mid(A1,9,2) & "-" & mid(A1,11,2),mid(A1,7,4) & "-" & mid(A1,11,2) & "-" & mid(A1,13,2))為15位時,應該沒2000年後出生的吧所以,以上應該行得通,試試看當A列是企業代碼時,公式有問題.如:A1=21341231233210,得到的是3123-32-10公式上做了點修改.=IF(OR(LEN(A1)={15,18}),IF(LEN(A1)=15,"19" & MID(A1,7,2) & "-" & MID(A1,9,2) & "-" & MID(A1,11,2),MID(A1,7,4) & "-" & MID(A1,11,2) & "-" & MID(A1,13,2)),"")=IF(LEN(A1)=15,"19" & MID(A1,7,2) & "-" & MID(A1,9,2) & "-" & MID(A1,11,2),IF(LEN(A1)=18,MID(A1,7,4) & "-" & MID(A1,11,2) & "-" & MID(A1,13,2),A1))當A列是企業代碼時,返回原企業代碼
推薦閱讀：