.,日常EXCEL函數應用,EXCEL操作界面及設置,函數與公式通用技巧,常用函數,.,Whereisit？,EXCEL操作界面及設置,在“視圖”選項卡中，通過勾選或取消勾選復選框來設置界面中顯示或者隱藏編輯欄、行號列標、工作表標簽等。,“公式”選項卡中，有“公式審核”、“保護”等常用函數公式。,.,Whereisit？,EXCEL操作界面及設置,右鍵單擊工具欄“自定義快速訪問工具欄”彈出自定義快速訪問工具欄選項卡，可以設置自定義訪問工具欄。,EXCEL操作界面及設置,函數與公式通用技巧,常用函數,.,函數與公式通用技巧,實例,.,函數與公式通用技巧,.,函數與公式通用技巧,限制：,突破：,.,函數與公式通用技巧改變工作表的計算精度,在實際工作中，許多用戶在計算數字時會設置單元格格式為2為小數的數值、貨幣格式或者是整數值。但在使用公式進行數據處理的過程中，經常會發現幾個單元格之和與合計單元格顯示的數據之和不相等。,因為excel將數值按照15位計算精度帶入計算所致。如圖，在excel選項中勾選“將精度設為所顯示的精度”，可以解決上述情況的發生，但這樣也降低了工作薄中所有工作表的計算精度，但不影響其他工作薄。此外，也可以用ROUND函數對數值進行修約后再求和。,.,函數與公式通用技巧分布查看公式計算結果,方法一：選中包含公式的單元格，單擊“公式”選項卡中的“公式求值”按鈕，彈出“公式求值”對話框，然后多次單擊“求值”按鈕，“求值”域中將按照公式計算的順序逐步地顯示公式的計算過程。,方法二：功能鍵除了可以重算工作薄外，還可以用于查看公式的運算結果。當一個函數公式比較長時，可以用鍵對各個部分進行分段計算，以便公式差錯和更正。在分段選擇時，注意要包含一個完整的運算對象。,EXCEL操作界面及設置,函數與公式通用技巧,常用函數,.,常用函數IF、IFERROR,根據對指定的條件計算結果為TRUE或FALSE，返回不同的結果?？梢允褂肐F對數值和公式執行條件檢測。MicrosoftExcel還提供了其他一些函數，它們可根據條件來分析數據。例如，如果要計算某單元格區域內某個文本字符串或數字出現的次數，則可使用COUNTIF和COUNTIFS電子表格函數。若要計算基于某區域內一個文本字符串或一個數值的總和，可使用SUMIF和SUMIFS工作表函數。,.,=IFERROR(value,value_if_error),如果公式計算出錯誤則返回您指定的值；否則返回公式結果。使用IFERROR函數來捕獲和處理公式中的錯誤。Value是需要檢查是否存在錯誤的參數。Value_if_error是公式計算出錯誤時要返回的值。計算得到的錯誤類型有：#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!。,常用函數IF、IFERROR,.,常用函數AND、OR,所有參數的計算結果為TRUE時，返回TRUE；只要有一個參數的計算結果為FALSE，即返回FALSE。通過AND函數可以檢驗多個不同的條件，而不僅僅是一個條件。,=AND(logical1,logical2,.),=OR(logical1,logical2,.),在其參數組中，任何一個參數邏輯值為TRUE，即返回TRUE；任何一個參數的邏輯值為FALSE，即返回FALSE。,注意：參數的計算結果必須是邏輯值（如TRUE或FALSE），而參數必須是包含邏輯值的數組如果數組或引用參數中包含文本或空白單元格，則這些值將被忽略。如果指定的單元格區域未包含邏輯值，則函數將返回錯誤值#VALUE!。,.,常用函數LARGE,=LARGE(array,k),返回數據集中第k個最大值。使用此函數可以根據相對標準來選擇數值。Array為需要從中選擇第k個最大值的數組或數據區域。K為返回值在數組或數據單元格區域中的位置（從大到小排）。,注解如果數組為空，函數LARGE返回錯誤值#NUM!。如果k0或k大于數據點的個數，函數LARGE返回錯誤值#NUM!。如果區域中數據點的個數為n，則函數LARGE(array,1)返回最大值，函數LARGE(array,n)返回最小值。,=SMALL(array,k)返回數據集中第k個最小值。,.,常用函數MAX,=MAX(number1,number2,.),返回一組值中的最大值。注解參數可以是數字或者是包含數字的名稱、數組或引用。邏輯值和直接鍵入到參數列表中代表數字的文本被計算在內。如果參數為數組或引用，則只使用該數組或引用中的數字。數組或引用中的空白單元格、邏輯值或文本將被忽略。如果參數不包含數字，函數MAX返回0（零）。如果參數為錯誤值或為不能轉換為數字的文本，將會導致錯誤。如果要使計算包括引用中的邏輯值和代表數字的文本，請使用MAXA函數。,=MIN(number1,number2,.)返回一組值中的最小值。=MEDIAN(number1,number2,.)返回給定數值的中值。中值是在一組數值中居于中間的數值。=MODE(number1,number2,.)返回在某一數組或數據區域中出現頻率最多（眾數）的數值。,.,常用函數AVERAGE,=TRIMMEAN(array,percent)返回數據集的內部平均值。函數TRIMMEAN先從數據集的頭部和尾部除去一定百分比的數據點，然后再求平均值。當希望在分析中剔除一部分數據的計算時，可以使用此函數。,=AVERAGE(number1,number2,.),返回參數的平均值（算術平均值）。,注釋：參數可以是數字或者是包含數字的名稱、單元格區域或單元格引用。邏輯值和直接鍵入到參數列表中代表數字的文本被計算在內。如果區域或單元格引用參數包含文本、邏輯值或空單元格，則這些值將被忽略；但包含零值的單元格將被計算在內。如果參數為錯誤值或為不能轉換為數字的文本，將會導致錯誤。若要在計算中包含引用中的邏輯值和代表數字的文本，請使用AVERAGEA函數。若要只對符合某些條件的值計算平均值，請使用AVERAGEIF函數或AVERAGEIFS函數。,.,常用函數AVERAGEIF,=AVERAGEIF(range,criteria,average_range),注解：忽略區域中包含TRUE或FALSE的單元格。如果average_range中的單元格為空單元格，AVERAGEIF將忽略它。如果range為空值或文本值，則AVERAGEIF會返回#DIV0!錯誤值。如果條件中的單元格為空單元格，AVERAGEIF就會將其視為0值。如果區域中沒有滿足條件的單元格，則AVERAGEIF會返回#DIV/0!錯誤值。您可以在條件中使用通配符，即問號(?)和星號(*)。Average_range不必與range的大小和形狀相同。求平均值的實際單元格是通過使用average_range中左上方的單元格作為起始單元格，然后加入與range的大小和形狀相對應的單元格確定的。,range是要計算平均值的一個或多個單元格，其中包括數字或包含數字的名稱、數組或引用。criteria是數字、表達式、單元格引用或文本形式的條件，用于定義要對哪些單元格計算平均值。例如，條件可以表示為32、32、32、蘋果或B4。average_range是要計算平均值的實際單元格集。如果忽略，則使用range。,.,常用函數SUM,=SUM(number1,number2,.),說明直接鍵入到參數表中的數字、邏輯值及數字的文本表達式將被計算，請參閱下面的示例一和示例二。如果參數是一個數組或引用，則只計算其中的數字。數組或引用中的空白單元格、邏輯值或文本將被忽略。請參閱下面的第三個示例。如果參數為錯誤值或為不能轉換為數字的文本，將會導致錯誤。,.,常用函數COUNT,=COUNT(value1,value2,.),注解如果參數為數字、日期或者代表數字的文本（例如，用引號引起的數字，如1），則將被計算在內。邏輯值和直接鍵入到參數列表中代表數字的文本被計算在內。如果參數為錯誤值或不能轉換為數字的文本，則不會被計算在內。如果參數為數組或引用，則只計算數組或引用中數字的個數。不會計算數組或引用中的空單元格、邏輯值、文本或錯誤值。若要計算邏輯值、文本值或錯誤值的個數，請使用COUNTA函數。若要只計算符合某一條件的數字的個數，請使用COUNTIF函數或COUNTIFS函數。,=COUNTIF(range,criteria)計算某個區域中滿足給定條件的單元格數。例如：=COUNTIF(A2:A7,=20),.,常用函數LEFT、MID、RIGHT,=LEFT(text,num_chars)=MID(text,start_num,num_chars)=RIGHT(text,num_chars),Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一個字符的位置。文本中第一個字符的start_num為1，以此類推。Num_chars指定希望函數從文本中返回字符的個數。,根據所指定的字符數，LEFT返回文本字符串中第一個字符或前幾個字符。,MID返回文本字符串中從指定位置開始的特定數目的字符，該數目由用戶指定。,RIGHT根據所指定的字符數返回文本字符串中最后一個或多個字符。,.,常用函數CONCATENATE,=CONCATENATE(text1,text2,.),將兩個或多個文本字符串合并為一個文本字符串。,注解您也可使用連接符號(&)計算運算符代替CONCATENATE函數來連接文本項。例如=A1&B1與=CONCATENATE(A1,B1)返回相同的值。,.,常用函數SUBSTITUTE,=SUBSTITUTE(text,old_text,new_text,instance_num),Text為需要替換其中字符的文本，或對含有文本的單元格的引用。Old_text為需要替換的舊文本。New_text用于替換old_text的文本。Instance_num為一數值，用來指定以new_text替換第幾次出現的old_text。如果指定了instance_num，則只有滿足要求的old_text被替換；否則將用new_text替換Text中出現的所有old_text。,在文本字符串中用new_text替代old_text。,.,常用函數RAND、RANDBETWEEN,=RAND(),注解若要生成a與b之間的隨機實數，請使用：RAND()*(b-a)+a如果要使用函數RAND生成一隨機數，并且使之不隨單元格計算而改變，可以在編輯欄中輸入“=RAND()”，保持編輯狀態，然后按F9，將公式永久性地改為隨機數。,返回大于等于0及小于1的均勻分布隨機實數，每次計算工作表時都將返回一個新的隨機實數。,=RANDBETWEEN(bottom,top),返回位于指定的兩個數之間的一個隨機整數。每次計算工作表時都將返回一個新的隨機整數。Bottom函數RANDBETWEEN將返回的最小整數。Top函數RANDBETWEEN將返回的最大整數。,.,常用函數RANK,=RANK(number,ref,order),Number為需要找到排位的數字。Ref為數字列表數組或對數字列表的引用。Ref中的非數值型參數將被忽略。Order為一數字，指明排位的方式。如果order為0（零）或省略，MicrosoftExcel對數字的排位是基于ref為按照降序排列的列表。如果order不為零，MicrosoftExcel對數字的排位是基于ref為按照升序排列的列表。注解函數RANK對重復數的排位相同。但重復數的存在將影響后續數值的排位。例如，在一列按升序排列的整數中，如果整數10出現兩次，其排位為5，則11的排位為7（沒有排位為6的數值）。,返回一個數字在數字列表中的排位。,.,常用函數ROUND,=ROUND(number,num_digits),ROUND函數可將某個數字四舍五入為指定的位數。,說明如果num_digits大于0（零），則將數字四舍五入到指定的小數位。如果num_digits等于0，則將數字四舍五入到最接近的整數。如果num_digits小于0，則在小數點左側進行四舍五入。若要始終進行向上舍入（遠離0），請使用ROUNDUP函數。若要始終進行向下舍入（朝向0），請使用ROUNDDOWN函數。若要將某個數字四舍五入為指定的倍數（例如，四舍五入為最接近的0.5倍），請使用MROUND函數。,.,常用函數ROW、COLUMN,說明：如果省略reference，則假定是對函數所在單元格的引用。如果reference為一個單元格區域，并且函數作為數組輸入，則函數將以數組的形式返回reference的行號或列號。如果參數reference為一個單元格區域，并且函數不是以數組公式的形式輸入的，則COLUMN函數將返回最上側的行號或最左側列的列號。Reference不能引用多個區域。,=ROW(reference),=ROWS(array)返回引用或的行數。=COLUMNS(array)返回數組或引用的列數。,=COLUMN(reference),.,常用函數MATCH,=MATCH(lookup_value,lookup_array,match_type),MATCH函數可在單元格區域（區域:工作表上的兩個或多個單元格。區域中的單元格可以相鄰或不相鄰。）中搜索指定項，然后返回該項在單元格區域中的相對位置。,.,常用函數INDEX,數組形式:=INDEX(array,row_num,column_num)引用形式:=INDEX(reference,row_num,column_num,area_num),Array為單元格區域或數組常量。Reference對一個或多個單元格區域的引用。如果為引用輸入一個不連續的區域，必須將其用括號括起來。如果引用中的每個區域只包含一行或一列，則相應的參數row_num或column_num分別為可選項。Row_num引用中某行的行號，函數從該行返回一個引用。Column_num引用中某列的列標，函數從該列返回一個引用。Area_num選擇引用中的一個區域，返回該區域中row_num和column_num的交叉區域。選中或輸入的第一個區域序號為1，第二個為2，以此類推。如果省略area_num，則函數INDEX使用區域1。,.,常用函數INDEX,.,常用函數OFFSET,=OFFSET(reference,rows,cols,height,width),以指定的引用為參照系，通過給定偏移量得到新的引用。返回的引用可以為一個單元格或單元格區域。并可以指定返回的行數或列數。,Reference作為偏移量參照系的引用區域。Rows相對于偏移量參照系的左上角單元格，上（下）偏移的行數。行數可為正數或負數。Cols相對于偏移量參照系的左上角單元格，左（右）偏移的列數。列數可為正數或負數。Height高度，即所要返回的引用區域的行數。Height必須為正數。Width寬度，即所要返回的引用區域的列數。Width必須為正數。,.,常用函數VLOOKUP、HLOOKUP,