Classification:InternalUseClassified-InternalUseOnly武漢可口可樂飲料有限公司EXCEL高級教程EXCEL高級培訓2015-10-24Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧1公式與函數(shù)Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧1.1使用公式1.1.1公式運算符Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧1.1.2公式的運算順序Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧1.1.2輸入和顯示公式 Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧常用函數(shù)介紹Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧SUMIF：用途：根據(jù)指定條件對若干單元格、區(qū)域或引用求和。語法：SUMIF(range，criteria，sum_range)參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達式等組成的判定條件，Sum_range為需要求和的單元格、區(qū)域或引用。實例：某單位統(tǒng)計工資報表中職稱為“中級”的員工工資總額。假設工資總額存放在工作表的F列，員工職稱存放在工作表B列。則公式為“=SUMIF(B1:B1000，"中級"，F(xiàn)1:F1000)”，其中“B1:B1000”為提供邏輯判斷依據(jù)的單元格區(qū)域，"中級"為判斷條件，就是僅僅統(tǒng)計B1:B1000區(qū)域中職稱為“中級”的單元格，F(xiàn)1:F1000為實際求和的單元格區(qū)域。Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧VLOOKUP：用途：在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。當比較值位于數(shù)據(jù)表首列時，可以使用函數(shù)VLOOKUP代替函數(shù)HLOOKUP。語法：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)參數(shù)：Lookup_value為需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。Table_array為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。Col_index_num為table_array中待返回的匹配值的列序號。Col_index_num為1時，返回table_array第一列中的數(shù)值；col_index_num為2，返回table_array第二列中的數(shù)值，以此類推。Range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。實例：如果A1=23、A2=45、A3=50、A4=65，則公式“=VLOOKUP(50，A1:A4，1，TRUE)”返回50。Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧COUNTIF：用途：統(tǒng)計某一區(qū)域中符合條件的單元格數(shù)目。語法：COUNTIF(range，criteria)參數(shù)：range為需要統(tǒng)計的符合條件的單元格數(shù)目的區(qū)域；Criteria為參與計算的單元格條件，其形式可以為數(shù)字、表達式或文本(如36、">160"和"男"等)。其中數(shù)字可以直接寫入，表達式和文本必須加引號。實例：假設A1:A5區(qū)域內(nèi)存放的文本分別為女、男、女、男、女，則公式“=COUNTIF(A1:A5，"女")”返回3。Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧COUNTIF：用途：統(tǒng)計某一區(qū)域中符合條件的單元格數(shù)目。語法：COUNTIF(range，criteria)參數(shù)：range為需要統(tǒng)計的符合條件的單元格數(shù)目的區(qū)域；Criteria為參與計算的單元格條件，其形式可以為數(shù)字、表達式或文本(如36、">160"和"男"等)。其中數(shù)字可以直接寫入，表達式和文本必須加引號。實例：假設A1:A5區(qū)域內(nèi)存放的文本分別為女、男、女、男、女，則公式“=COUNTIF(A1:A5，"女")”返回3。Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧2應用圖表Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧3數(shù)據(jù)透視表Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechnologyDepartmentHuBeiO.U.Classified-InternalUseOnlyEXCEL高級應用技巧Classified-InternalUseOnlyClassified-InternalUseOnlyInformationTechno