第一章EXCEL高級應(yīng)用

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