Excel應用集錦.doc

目錄1、求滿足單個條件的數據之和22、求同時滿足兩個條件的數據之和23、公式計算并附加文字24、滿足某一個條件，將某兩列相乘后求和35、在表頭欄里做個下拉框，以篩選本列內容。36、EXCEL如何生成目錄47、SUMPRODUCT統(tǒng)計某區(qū)域中滿足2個條件的單元格的個數58、COUNTBLANK統(tǒng)計某區(qū)域中為空值的單元格的個數59、COUNT統(tǒng)計某區(qū)域中所有單元格的個數510、COUNTA統(tǒng)計某區(qū)域中不為空值的單元格的個數511、COUNTIF統(tǒng)計某區(qū)域中滿足1個條件的單元格的個數512、EXCEL保護部分單元格513、寫入今天日期614、線路里程與數字轉換615、EXCEL中設置下拉框616、RAND隨機填充數值617、時間與日期的計算6Excel應用集錦1、求滿足單個條件的數據之和示例如下表：公式：=SUMIF(B2:B7,甲廠,D2:D7)2、求同時滿足兩個條件的數據之和示例如下表：公式：=SUMPRODUCT(D2:D7)*(B2:B7=甲廠)*(C2:C7=C15)3、公式計算并附加文字示例如下表：公式：=甲廠共&SUMIF(B2:B7,甲廠,D2:D7)&方4、滿足某一個條件，將某兩列相乘后求和示例如下表：公式：=SUM(IF(A2:A11=B,B2:B11*C2:C11,0)，輸入后按ctrl+shift+enter錄入公式。5、在表頭欄里做個下拉框，以篩選本列內容。操作如下：1、選擇數據區(qū)域，包含要做下拉框的表頭；2、右鍵-“創(chuàng)建列表”，或依次點菜單“數據”-“列表”-“創(chuàng)建列表”；3、彈出窗口根據選擇的列表區(qū)域是否含有標題進行選擇，然后“確定”。ok如果想刪除效果，依次點菜單“數據”-“列表”-“轉換為區(qū)域”，再“確定”即可。效果如下：若想篩選多個條件，左鍵點“自定義”，交集選“與”，兩個選“或”，如下圖：6、EXCEL如何生成目錄1、在第一個工作表的適當地方，或者另外插入一個工作表。 用宏3.0取出各工作表的名稱： 按Ctrl+F3出現(xiàn)自定義名稱對話框，取名為X，在“引用位置”框中輸入： =MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100) 確定 2、用HYPERLINK函數批量插入連接：在目錄工作表（一般為第一個sheet）的A2單元格輸入公式：=HYPERLINK(#&INDEX(X,ROW()&!A1,INDEX(X,ROW() 將公式向下填充，直到出錯為止，目錄就生成了。7、SUMPRODUCT統(tǒng)計某區(qū)域中滿足2個條件的單元格的個數示例：=SUMPRODUCT(A1:B9=-20)，為A1:B9中20且20的單元格的個數，但結果中包含為空值的單元格。8、COUNTBLANK統(tǒng)計某區(qū)域中為空值的單元格的個數示例：=COUNTBLANK(A1:B9)，為A1:B9中為空值的單元格的個數。9、COUNT統(tǒng)計某區(qū)域中所有單元格的個數示例：=COUNT(A1:B9)，為A1:B9中所有單元格的個數，包含為空值的單元格。10、COUNTA統(tǒng)計某區(qū)域中不為空值的單元格的個數示例：=COUNTA(A1:B9)，為A1:B9中不是空值的單元格的個數。11、COUNTIF統(tǒng)計某區(qū)域中滿足1個條件的單元格的個數示例：=COUNTIF(A1:B9,X)，為A1:B9中值為X的單元格的個數。12、EXCEL保護部分單元格用以下的方式，可以讓你隨心所欲的結任一單元格進行保護：1、全選你工作表中的內容，選菜單欄中的格式項下的“單元格”項。2、去掉勾選“保護”項內的”鎖定，之后“確定退出。3、選定你要進行保護的單元格，重復1、選菜單欄中的格式項下的“單元格”項。4、在“保護”項內，勾選“鎖定”之后退出。5、在菜單“工具”下選“保護”內之“保護工作表”。6、在打開的窗口內只選定以下二項：“保護工作表及鎖定的單元格內容”和下面的”選定未鎖定的單元格“后輸入密碼，確定即可。7、你會發(fā)現(xiàn)，除了這些鎖死不能動外，其他的你一樣還可以編輯。注意退出前要保存文檔。13、寫入今天日期=TEXT(TODAY(),yyyy-mm-dd)，為：2010-03-01，或：=TEXT(TODAY(),yyyy年mm月dd日)，為：2010年03月01日。14、線路里程與數字轉換示例：要在A1中將數字1013.42以里程形式顯示：K1+013.42，則方法如下：選中A1單元格，右鍵“設置單元格格式”“數字”“自定義”“寫入K0+000.00”“確定”在A1中輸入1013.42，則顯示結果：K1+013.42。15、EXCEL中設置下拉框示例：要在某個或某些單元格中設置下拉框，方法如下：選中某個或某些單元格，“數據”“有效性”“設置”“序列”在“來源”中輸入“合格,不合格,良好”確定即可，注意，“合格,不合格,良好”輸入時不需加“”，不同選項間用半角逗號分隔。16、RAND隨機填充數值在某單元格中隨機生成某區(qū)間數值，公式一：=RAND()*100，自動生成0100之間任意數值，包含小數；公式二：=RAND()*100+5，自