如何高效运用Excel(上).ppt

1、2020-08-06,如何高效運用EXCEL（上）,培 訓 大 綱,一、培訓目的 5分鐘 二、Excel的基本知識 10分鐘 三、Excel函數的基本知識 5分鐘 四、數學/查詢/統計/邏輯函數 30分鐘 五、數組函數（上） 30分鐘 六、各種函數的組合運用 30分鐘 七、Excel小技巧 10分鐘 課時Total：120分鐘,2020-08-06,一、培訓目的,1、熟悉Excel工作介面中，各部份的名稱 2、熟悉Excel的基本操作 3、學會“數學/查詢/統計/邏輯函數”使用方法 4、瞭解“數組函數”的基本知識 5、學會各種函數的組合運用，增加工作效率、 減少重複勞動 6、瞭解Excel操作

2、小技巧，增強Excel學習興趣,2020-08-06,二、Excel的基本知識,Excel簡介,微軟（Microsoft）公司推出的Office辦公軟件中包括有：Word、Excel、PowerPoint、Outlook、Access等。 Excel是一個重要組成，也是目前最流行的關於試算表處理的軟件之一。 它具有強大的計算、分析和圖表等功能，是公司最常用表格製作軟件。 在Word中也有表格，在Excel表格與Word表格的最大不同在於Excel表格具有強大的數位運算和數位分析能力。Excel中內置的公式和函數，可能幫忙用戶進行複雜的計算。由於Excel在資料運算方面有強大的功能，使它成為用戶

3、辦公必不可少的一個常用辦公軟件。,2020-08-06,二、Excel的基本知識,啟動Excel,方法一： 单击【开始】 【程式集】 【Microsoft Office】 【Microsoft Office Excel 2003】菜单 即可启动Excel应用程序。,方法二： 单击【开始】 【執行】 輸入“Excel” 【回車】即可启动Excel应用程序。,2020-08-06,二、Excel的基本知識,Excel 工作窗口,工作表標籤,欄標（列标）,儲存格（单元格）,列號（行号）,工具欄,標題欄,任務窗格,菜單欄,繪圖工具欄,名稱框,公式編輯框,2020-08-06,三、Excel函數的基本知

4、識,1、什麽是函數,Excel中的函數其實是一些預定義的公式，它們使用一些稱為參數的特定數值按特定的順序或結構進行計算。用戶可以直接用它們對某個區域內的數值進行一系列運算，如分析和處理日期值和時間值、確定貸款的支付額、確定單格中的資料類型、計算平均值、排序顯示和運算文本資料等等。 例如，Sum函數對單格或單格區域進行加法運算。 函數是否可以是多重的呢？也就是說一個函數是否可以是另一個函數的參數呢？當然可以，這就是嵌套函數的含義。所謂嵌套函數，就是指在某些情況下，您可能需要將某函數作為另一函數的參數使用。 例如，圖1中所示的公式使用了嵌套的 Average函數，並將結果與50相比較。這個公式的含

5、義是：如果單格F2到F5的平均值大於50，則求F2到F5的和，否則顯示數值0。,圖1 嵌套函數,2020-08-06,三、Excel函數的基本知識,圖2 函數的結構,在學習Excel函數之前，我們需要對於函數的結構做以必要的瞭解。如圖2所示，函數的結構以函數名稱開始，後面是左圓括號、以逗號分隔的參數和右圓括號。如果函數以公式的形式出現，請在函數名稱前面鍵入等號（=）。在創建包含函數的公式時，公式選項板將提供相關的幫助。,2、函數的種類,Excel函數一共有11類，分別是資料庫函數、日期與時間函數、工程函數、財務函數、資訊函數、邏輯函數、查詢和引用函數、數學和三角函數、統計函數、文本函數以及用戶

6、自定義函數。,本次主要學習常用之函數種類（上面藍色字體部份）,2020-08-06,四、數學/查詢/統計/邏輯函數,數學函數：Sum（求和）,1、行或列求和 以圖為例，對列或欄內的若干儲存格求和 例如，求總共請假天數，可以在D16中輸入：=SUM(D7:D15) 2、區域求和 常用于對一張工作表中的所有資料求總計 例如，D16的公式還可以寫成：=SUM(D7:D10,D11:D15),2020-08-06,四、數學/查詢/統計/邏輯函數,數學函數：Sumif（條件求和）,可對滿足某一條件的單格區域求和。該條件可以是數值、文本或運算式。 例如，計算考核等級為“A”同事的請假天數。則在D18中輸入

7、公式 =SUMIF(AQ7:AQ15,A,D7:D15)。其中： 1、“AQ7:AQ15”為提供邏輯判斷依據的區域 2、“A”為判斷條件，即只統計AQ7:AQ15中值為“A”的儲存格 3、“D7:D15”為實際求和的區域。,2020-08-06,四、數學/查詢/統計/邏輯函數,數學函數：Round（四捨五入）,Round(number,num_digits)，它的功能就是根據指定的位元數，將數字四捨五入 這個函數有兩個參數，分別是number和num_digits 其中number就是將要進行四捨五入的數字；num_digits是希望得到的小數點位數 例如，以圖為例，B2中為初始資料0.123

8、456，B3的初始資料為0.234567 在C2中輸入“=ROUND(B2,2)”，小數點後保留兩位數字，得到0.12、0.23 在D2中輸入“=ROUND(B2,4)”，小數點后保留四位數字，得到0.1235、0.2346,2020-08-06,四、數學/查詢/統計/邏輯函數,查詢函數：Vlookup（依首欄數值、返回指定欄處的數值）,Vlookup用於在表格或數值陣列的首欄查找指定的數值，並由此返回表格或陣列當前欄中指定列處的數值。 語法為：Vlookup (lookup_value,table_array,col_index_num,range_lookup) lookup_value表

9、示要查找的值，必須位於查找區域的最左列 table_array表示用於查找資料的區域 col_index_num為區域中待返回的值所在欄序號 例如，為1時，返回區域中第一欄的數值；為2時，返回區域中第二行的數值 range_lookup為一邏輯值，指明查找時是精確查找，還是近似查找,下面舉例說明Vlookup的實際運用（精確查找、近似查找分別說明）,2020-08-06,四、數學/查詢/統計/邏輯函數,查詢函數：Vlookup（依首欄數值、返回指定欄處的數值）,（1）精確查找 根據區域最左欄的值，對其他欄的資料進行精確的查找 例如：根據工資表創建各個員工的工資條，此工資條為應用Vlookup函

10、數建立。員工Sandy（編號A001）的工資條姓名欄創建公式為： =VLOOKUP(A21,A3:H12,2,False) 語法解釋：在A3:H12範圍內（即“編號”欄中）精確找出與A21儲存格相符的行，並將第二欄的內容返回至A21儲存格中,2020-08-06,四、數學/查詢/統計/邏輯函數,查詢函數：Vlookup（依首欄數值、返回指定欄處的數值）,（2）近似查找 根據定義區域最左列的值，對其他列資料進行不精確值的查找 例如：按“項目總額”對應“獎金比例” 規則是，項目總額大於對應標準時，給予對應之獎金比例（項目總額在05000元時，獎金比例為1%，以此類推） 假如某項目的項目總額為130

11、00元，則其對應的獎金比例公式應為： =VLOOKUP(A11,A4:B8,2,Ture) 即可求得具體的獎金比例為5%，如圖,2020-08-06,四、數學/查詢/統計/邏輯函數,查詢函數：Match（返回指定數值在指定區域中的位置）,語法為：Match(lookup_value,lookup_array,match_type) lookup_value為要查找的值、lookup_array為要查找的區域(必須是一列或一欄) match_type為匹配形式，有“0”、“1”、“1”三種選擇： “0”：準確的查找 “1”：查找小於或等於查找值的最大值，查找區域須為昇序排列 “1”：查找大於或等

12、於查找值的最小值，查找區域須降序排列 例如，薪資結構中四職等共有10級，如圖 1、=MATCH(B13,C2:C11,1)：全薪1405，在C2:C11中第5個位置 2、=MATCH(B14,C2:C11,0)：全薪1437，在C2:C11中第6個位置 3、=MATCH(B13,C2:C11,-1)：因为C2:C11不是降排列，传回错误,2020-08-06,四、數學/查詢/統計/邏輯函數,統計函數：Average（求算數平均值、不計算文字/邏輯值/空格）,語法為：Average(number1,number2,.) 其中number1,number2,.為要計算平均值的130個參數 如果引用

13、中有文字、邏輯值或空格，則將被忽略。但是，如果單格包含零值則計算在內 例如，圖中C13為選手Annie的參賽平均分數 公式為：=Average(C3:C12),統計函數：AverageA（求算數平均值、且計算文字/邏輯值/空格）,不僅數字，而且文字、邏輯值或空格，也計算在內 例如，圖中C14 公式為：=AverageA(C3:C12),2020-08-06,四、數學/查詢/統計/邏輯函數,統計函數：Count（計數、不計算錯誤值/空值/邏輯值）,語法形式為Count(value1,value2,.) 其中value1,value2,.為包含或引用各種類型資料的參數130個 計數時，將把數字、日

14、期或以文字代表的數計算進去；但是錯誤值、空值、邏輯值則被忽略 例如，圖中計算有幾個評委給出了有效分數 公式為：=Average(C3:C12),統計函數：CountA（計數、且計算錯誤值/空值/邏輯值）,計數時，錯誤值、空值、邏輯值，也計算在內 例如，圖中求評委的人數 公式為：=AverageA(C3:C12),2020-08-06,四、數學/查詢/統計/邏輯函數,統計函數：Countif（條件計數）,用來計算給定區域內滿足特定條件的數目 語法為：Countif(range,criteria) range為需要計算滿足條件數目的區域 criteria為條件。可以為數字、運算式或文本。條件可以為

15、32、“32”、“32”等 例如，計算每位學生成績大於90分的課程數 以B13為例，公式為： =Countif(B4:B10,90) 語法解釋：計算B4到B10區域中有多少個數值大於90,2020-08-06,四、數學/查詢/統計/邏輯函數,統計函數：Max（求最大值）,語法為：Max(number1,number2,.),統計函數：Min（求最小值）,語法為：Min(number1,number2,.),統計函數：Large（求區域中第K個最大值）,語法為：Large(array,k),統計函數：Small（求區域中第K個最小值）,語法為：Small(array,k),統計函數：Median

16、（求區域中的中位數）,語法為：Median(number1,number2,.),統計函數：Mode（求區域中出現頻率最多的數）,語法為：Mode(number1,number2,.),2020-08-06,四、數學/查詢/統計/邏輯函數,統計函數：Rank（比較排序）,一個數值在區域中的比較排序 語法為：Rank(number,ref,order) number為需要找到排位元的數位 ref為需要比較排序的區域 order為一數位用來指明排位元的方式： 如果order為0或省略，則Excel將ref當作按降序排列的資料清單進行排序 如果order不為零，Excel將ref當作按昇序排列的資料

17、清單進行排序 例如，某單位的年終獎金分配的序，公式為： =RANK(C3,C3:C12),2020-08-06,四、數學/查詢/統計/邏輯函數,邏輯函數：And（與）,當And的參數全部滿足某一條件時，返回結果為Ture，否則為False 語法為：And(logical1,logical2,.) logical1,logical2,.表示130個條件值 例如，在B2中輸入數字50， 在C2中寫公式=AND(B230,B260) 由於B2等於50的確大於30、小於60。所以兩個條件值（logical）均為真，則返回結果為Ture,2020-08-06,四、數學/查詢/統計/邏輯函數,邏輯函數：O

18、r（或）,在其參數組中，任何一個參數邏輯值為Ture，即返回Ture。它與And函數的區別在於，And函數要求所有函數邏輯值均為真，結果方為真。而Or函數僅需其中任何一個為真即可為真。 例如，圖中如果在B4單格中的公式寫為=OR(B1:B3)則結果等於Ture,邏輯函數：Not（非）,當參數邏輯值為Ture時，返回的結果為False 例如，=NOT(2+2=4)，由於2+2的結果的確為4，該參數邏輯值為Ture，因此返回結果為False,2020-08-06,四、數學/查詢/統計/邏輯函數,邏輯函數：If（條件）,用於執行真假值判斷後，根據真假值返回不同結果 語法為：If(logical_te

19、st,value_if_Ture,value_if_False) logical_test表示任意值或公式 logical_test返回結果為Ture，返回value_if_Ture的值或公式，否則返回value_if_False的值或公式 例如，如圖，按平均分判斷成績是否合格（平均分超過60分為合格） 則B12公式為：=IF(B1160,合格,不合格) 語法解釋：如果B1160，則顯示“合格”，否則顯示“不合格”,2020-08-06,四、數學/查詢/統計/邏輯函數,邏輯函數：If（條件）,涉及If函數的多層嵌套的應用舉例 實際應用中，成績通常分為多個等級，比如優秀、良、中、合格、不合格等。

20、可以使用多層嵌套的辦法來實現。 例如，平均分90時為優秀。如圖，則F12公式為： =IF(F1160,IF(AND(F1190),優秀,合格),不合格) 語法解釋： 如果F1160，則執行IF(AND(F1190) 這裏為嵌套函數，繼續判斷F11是否90（為讓大家回憶And函數的應用，寫成AND(F1190)，實際可寫為F1190）。如果滿足在F12中顯示優秀，不滿足顯示合格 如果F11以上條件都不滿足，則在F12中顯示不合格,2020-08-06,五、數組函數（上）,數組函數（數組公式）的定義 數組函數可以產生一個以上的結果。 數組函數的參數是數組，即輸入有多個值；輸出結果可能是一個，也可能

21、是多個這一個或多個值是公式對多重輸入進行複合運算而得到的新數組中的元素 例如，如圖，計算產品總金額，一般做法是計算出每種產品金額，然後再計算出總金額。用數組函數只鍵入一個公式就可以完成 公式為：=SUM(B2:B4*C2:C4) 操作步驟：選定D4，輸入公式=SUM(B2:B4*C2:C4)，按下Shift+Ctrl+Enter鍵 語法解釋：“B2:B4*C2:C4”表示B2:B4範圍內每格和“C2：C4”內相對應的格相乘，也就是把每個數量和單價相乘，相乘的結果用“SUM”函數再相加，就得到了總金額,2020-08-06,五、數組函數（上）,數組函數Sumproduct（乘積之和） 在給定的幾

22、組數組中，將數組間對應的元素相乘，並返回乘積之和 語法為：Sumproduct(array1,array2,array3,.) array1,array2,array3,.為230個數組，其相應元素需要進行相乘並求和 例一，如圖，數組函數=SUMPRODUCT(B2:C4*D2:E4) 語法說明：兩個數組的所有元素對應相乘，然後把乘積相加，即3*2+4*7+8*6+6*7+1*5+9*3=156,2020-08-06,五、數組函數（上）,數組函數Sumproduct（乘积之和） 例二，如圖（计算符合2个及以上条件数据个数） 以统计男作業員人數為例 公式為：=SUMPRODUCT(B2:B11=

23、E2)*(C2:C11=F1) 語法解釋： 条件1B2:B11=E2是数组1，返回10个逻辑判断值： Ture,False,False,Ture,False,Ture,False,Ture,Ture,False 条件2C2:C11=F1是数组2，返回10个逻辑判断值： Ture,False,Ture,Ture,Ture,False,False,Ture,Ture,Ture 逻辑值True与False参与计算时：Ture=1，False=0，Ture*Ture=1，Ture*False=False*Ture=0，False*False=0 (B2:B11=E2)*(C2:C11=F1)=数组1*

24、数组2 =Ture,False,False,Ture,False,Ture,False,Ture,Ture,False*Ture,False,Ture,Ture,Ture,False,False,Ture,Ture,Ture =Ture*Ture,False*False,False*Ture,Ture*Ture,False*Ture,Ture*False,False*False,Ture*Ture,Ture*Ture,False*Ture =1;0;0;1;0;0;0;1;1;0。构成了一个新的数组3。 =SUMPRODUCT(B2:B11=E2)*(C2:C11=F1) =SUMPRODUC

25、T(数组1*数组2) =SUMPRODUCT(数组3) =SUMPRODUCT(1;0;0;1;0;0;0;1;1;0) =4,2020-08-06,五、數組函數（上）,數組函數Frequency（求頻率分佈） 求區域中資料的頻率分佈 語法為：Frequency (data_array,bins_array) data_array為引用資料的區域 bins_array為對data_array進行頻率計算的分段點 例如，如圖，計算員工年齡分佈情況 公式為：=FREQUENCY(C4:C11,C13:C16) 語法解釋：C4:C11為引用區域，C13:C16為分段點 輸入數組公示后就可以計算出年齡在25歲以下、2630歲、3135歲、3640歲和40歲以上各區間中的數目,2020-08-06,六、各種函數的組合運用,結合之前學習的各種函數，大家一起來理解一下 製造二部作業員之蘋果樹考核表是如何製作的 蘋果樹考核的規則為： 作業員按得分區分為A、B、C、D、E共5個等級： 1）每日對作業員進行考核評分，并依評分得出當日蘋果顏色： 綠蘋果