1.2公式與函數的高級應用17:0311.2.1公式的組成公式：以等號開始，由常量、單元格引用、函數和運算符組成。例如，假設A1單元格的內容為圓半徑，則計算圓的周長的公式為：=2*PI()*A1利用公式完成例fl1-2-1公式組成.xlsx中的圓周和面積的計算常量單元格的引用17:032常量數值型，123文本型，“ABC”日期與時間型數據，2015-03-27邏輯型數據，TRUE,FALSE數組常量（請通過幫助了解）輸入：(100)，1/2,0?,1.2E+12觀察結果17:033單元格的引用目的：標識單元格或區域中的數據引用方法“A1”引用樣式：列標行號“R1C1”引用樣式：R行號C列標單元格名稱引用相對引用、絕對引用和混合引用<F4>鍵切換17:034“A1”引用樣式三維引用樣式引用同一工作簿中多張工作表上的同一單元格或單元格區域中的數據三維引用的格式為：“工作表名稱的范圍！單元格或區域引用17:036“R1C1”引用樣式單元格的名稱引用17:038單元格或區域的命名名稱的使用例：在fl1-1-1中，先對考試區域命名，再計算運算符17:039算術運算符+、-、*、/、%、^比較運算符=、>、<、>=、<=、<>文本連接運算符（&）引用運算符:,聯合運算符，將多個引用合并為一個引用例如：SUM(B5:B15,D5:D15)空格交集運算符，生成一個對兩個引用中共有單元格的引用例如：SUM(B7:D7C6:C8)17:0310運算符的優先級引用運算符（:、,、（空格））負數（-）百分比（%）乘方（^）乘和除（*、/）加和減（+、-）文本連接運算符（&）比較運算符（=、>、<、<=、>=、<>）17:0311函數預定義公式Excel內置函數用戶創建的自定義函數17:0312數組公式17:0313同時執行多項計算并返回一個或多個結果對兩組或多組數組參數運算數組參數的行列數應相同使用步驟選擇結果區域輸入數組公式Ctrl+Shift+Enter自動用{}將數組公式括起來數組公式舉例在fl1-2-2職工工資表.xlsx的Sheet1中放著職工的基本工資和補貼，Sheet2中放著職工的獎金和調整幅度，請分別計算工資總計（含獎金和不含獎金），以及調整后的基本工資。17:0314數組公式使用注意事項不能直接輸入{}不能單獨更改數組公式中的某個單元格內容，否則系統報錯若要刪除，應整體選中數組公式所在單元格17:0315“R1C1”引用、名稱引用舉例在fl1-2-3商品信息.xlsx的Sheet1-Sheet4表格中，包含了幾家商場商品的銷售單價、數量、新增折扣店數量等信息，采用R1C1引用，利用數組公式計算銷售總金額、特價商品平均價格，以及新增折扣店總數。17:0316使用數組公式的好處簡潔性用傳統公式多步，用數組公式一步一致性數組公式中每個單元格的計算方法都一致保證更高的準確性安全性不能更改數組公式的局部17:0317公式審核找出有問題的公式追蹤引用單元格、追蹤從屬單元格例1-2-417:0318公式與函數中的常見問題17:0319公式求值單步執行公式，調試公式例fl1-1-4年齡.xlsx中公式的求值：=IF(LEN(C2)=15,IF(MOD(MID(C2,15,1),2)=1,“男”,“女”),IF(MOD(MID(C2,17,1),2)=1,“男”,“女”))的計算公式審核-公式求值17:0320內置工作表函數數學和三角函數邏輯函數文本函數日期和時間函數統計函數財務函數查找和引用函數17:0321工程函數數據庫函數信息函數用戶自定義函數數學函數Rand()：產生0-1之間的隨機數Round(number,num_digits）對number保留num_digits位小數Randbetween(bottom,top)產生bottom到top之間的隨機數SQRT(number):number的平方根Subtotal(function_num,ref)Sumif(range,critiera,[sum_range])Sumifs(sum_range,range1,critiera1,range2,critiera2,…)17:0322分類匯總函數17:0323數學函數舉例利用數學函數計算fl1-2-6學生信息表.xlsx中的數據身高（150.0---240.0，保留1位小數）成績（0---100的整數）月消費（0.0---1000.0，保留1位小數）調整成績（開根號乘以10，四舍五入到整數）用SUBTOTAL計算最長身高月消費<50的學生的總消費運動員的總月消費（身高>=200為運動員）調整后成績及格的運動員的總月消費17:0324注意事項產生a-b的隨機數函數公式Rand()*(b-a)+a將隨機數固定下來編輯欄中輸入=rand()后按<F9>鍵Sumif()與sumifs()的區別與用法17:0325三角函數PI()Sin()：返回給定角度的正弦值Cos()：返回給定角度的余弦值17:0326三角函數舉例打開fl1-2-8正弦函數和余弦函數.xlsxA列中輸入不同角度（0-360）B列中顯示對應的正弦函數結果C列中顯示對應的余弦函數結果繪制帶直線和數據標記的散點圖顯示17:0327三角函數舉例結果17:0328邏輯函數用于邏輯控制IFERROR(value,value_if_error)捕獲和處理表達式中的錯誤計算正確，結果為value計算錯誤，結果為value_if_error錯誤類型17:0329邏輯函數舉例為fl1-2-9學生成績.xlsx的語文成績添加等級初步調整千分考成績調整為“開根號*3并取整”通過IFERROR()判斷負分情況17:0330邏輯函數結果17:0331邏輯函數與數學、三角函數綜合邏輯函數IF(),AND(),OR()數學和三角函數ROUND(),RAND(),SIN(),SQRT(),PI()EXP(number)：enumberLN(number)：ln(number)ABS(number)：number的絕對值17:0332例1-2-10計算fl1-2-10分段函數.xlsx中的y的值其中x取值為-10----10的隨機實數結果保留2位小數請分別使用AND（）和OR（）函數計算17:0333文本函數——處理字符串LEFT(text,num):從左向右從文本中取num個字符RIGHT(text,num):從右向左取num個字符LEN(text)：文本中的字符數LENB(text)：文本中字符總共多少字節FIND(find_text,within_text)在within_text中找find_text，返回找到的字符在從左向右數第幾個UPPER(text):全部轉換成大寫REPLACE(old_text,start_num,num,new_text)替換文本17:0334文本函數舉例在Fl1-2-11供應商信息.xlsx中根據供應商聯系方式，抽取姓名和電話根據Email，抽取用戶名，并轉換為大寫抽取身份證號碼中的后6位，作為登錄密碼將身份證號碼的前3位510（四川）變更為320（江蘇）17:0335日期與時間函數用于處理日期和時間Weekday(Serial_num,serial_type)Datedif(start_date,end_date,unit)17:0336例1-2-12職工加班出差信息為fl1-2-12中的職工技術加班工資加班工資按1小時計算，不足1小時的按1小時算，不足半小時的則忽略不計統計每位員工的加班時長時間相減×24得到判斷加班的是星期幾，并確定是否雙休日Weekday(),IF()統計員工總加班時長Sumif(),數組公式17:0337例1-2-12職工加班出差信息為fl1-2-12中的職工技術加班工資統計員工雙休日總加班時長Sumifs(),數組公式計算每位員工的加班工資一般加班工資+雙休日加班工資統計每位員工出差總天數DATEDIF()計算出差補助總支出SUM(),數組公式17:0338例1-2-12結果17:0339公式審核檢驗帶有單元格引用的公式是否有誤17:0340公式審核公式-公式審核-追蹤從屬單元格17:03415.數據輸入及表格規劃合理布局表格數據，提高數據計算與分析效率17:0342常量區域計算和變化區域布局方式之二17:0343計算和變化區域常量區域1.1.3數據的輸出與顯示套用格式常規單元格格式設置自定義格式條件格式17:03441.自定義格式自定義格式的編碼組成17:0345常用格式碼及含義格式符功能及用法通用格式對未格式化的單元格使用默認格式，在列寬許可的情況下，盡可能地顯示較高的精度，在列寬不許可的情況下，使用科學記數法顯示數值#數字的占位符，不顯示無效的0。如單元格的格式碼為：#,###.##，則1233456.6789將被顯示為1,233,456.68。0234.30將被顯示為234.30作數字的占位符，如果數字的位數少于格式中的零，則顯示無效的零。如單元格的格式碼為：0,000.00，則1233456.6789將被顯示為1,233,456.68。0234.30將被顯示為0,234.30%把輸入的數字乘以100，然后以百分數的形式顯示出來"text"顯示雙引號內的文本。如設置自定義格式為："33981"0#，輸入1將被顯示為：3398101，輸入22，將被顯示為：3398122[顏色]用指定的顏色格式化單元格內容，如在定義了[紅色]的格式的單元格中輸入的內容將以紅色顯示[條件]對單元格進行條件格式化，允許的條件有：<，>，<=，>=，<>，=17:0346自定義格式的設置與應用請將fl1-1-10自定義格式.xlsx的表格最后一列“價格匯總”的數據格式化為“人民幣#,##0.00元”的格式。17:03472.條件格式當條件滿足時，格式出現區分不同數據或公式結果，當數據變化時，結果格式會自動變化17:0348fl1-1-11條件格式（1）將一季度中大于10的單元格設置為黃色填充效果；（2）將二季度中低于平均值的單元格圖案顏色設置為紅色，圖案樣式設置為25%灰色；（3）將三季度中重復值單元格設置為藍色填充效果；（4）將合計列利用自動求和功能求得各個產品的年度銷售合計，格式設置為藍色數據條和三色交通燈圖標集，并添加人民幣符號、保留兩位小數；（5）將G列數據設置為F列數字的中文大寫。17:0349實驗要求（1）掌握數值型、文本型、日期與時間型、以及邏輯型數據的輸入和運算方法。（2