喬紅qqqhhh9988@主要內容*第1章基礎知識與基本操作*第2章公式、常用函數與地址引用第3章工作簿與工作表第4章格式化工作表*第5章圖表與打印輸出*第6章數據處理與管理*第7章函數與應用第8章數據分析第9章其他第1章基礎知識與基本操作?一、Excel2007簡介?Excel2007是目前市場上功能最強大的電子表格制作軟件,它和Word、PowerPoint、Access等組件一起,構成了Office2007辦公軟件的完整體系。Excel不僅具有強大的數據組織、計算、分析和統計功能,還可以通過圖表、圖形等多種形式形象地顯示處理結果,更能夠方便地與Office2007其他組件相互調用數據,實現資源共享。顯示模式顯示比例1.2Excel2007的新界面和以前的版本相比,Excel2007的工作界面顏色更加柔和,更貼近于WindowsVista操作系統。Excel2007的工1.“文件”菜單單擊Excel工作界面左上角的Office按鈕,可以打開“文件”菜單。在該菜單中,用戶可以利用其中的命令新建、打開、保存、打印、共享以及發布工作簿。‘「V1Murn番返侵用的文格1m?用快相打開(Q)2成3：計篇，1Ii招，?霣hl新⑤407生度，賺te(另存鳩.5析售業絕表la與j|Hfi)0?7S臺-場毎曰置業觳統計聚8五臺颺?髩日鷺此載氏什表??(&?2五ft藺賊月實業觀什表耦上半條員rwiti^s2^雌(D)?10月份ftlww業番學釬喊筠成本it算聚U?發布(L!)?財計ira4dQj朔幻07芘度考樓擊07莊度，S極2P?UJz\*B33tXteilAJ2.快速訪問工具欄Excel2007的快速訪問工具欄中包含最常用操作的快捷按鈕,方便用戶使用。單擊快速訪問工具欄中的按鈕,可以執行相應的功能鏟建打開/賄電子BB件WST印nHHSQS?

_y恢復升序排序味利E序其他命2%能區下方西示($)功給區帝＜1姻1)3.標題欄標題欄位于窗口的最上方,用于顯示當前正在運行的程序名及文件名等信息。如果是剛打開的新T作簿文件,用戶所看到的文件名是Bookl,這是Excel2007默認建立可以最小化、最的文件名。單擊標題欄右端的按鈕,大化或關閉窗口。_SH番用供計褰?MicrosoftExcel4.功能區功能區是在Excel2007工作界面中添加的新元素，它將舊版本Excel中的菜單欄與工具欄結和在一起，以選項卡的形式列出Excel2007中的操作命令。默認情況下，Excel2007的功能區中的選項卡包括:“開始”詵項卡、“插入”詵項卡、“頁面布局”選項卡、“公式X:卡、U>1fl'“u_?>A!■介."l州%SfWRW‘斯WffJtan?M5.狀態欄與顯示模式狀態欄位于窗口底部,用來顯示當前工作區的狀態。Excel2007支持3種顯錄模式,分別為“普通”模式、“頁面布局”模式與“分頁預覽”模式,單擊Excel2007窗口左下角的按鈕可以切換顯示模式。就緒□-啟動/退出/關閉-窗口/工作簿/工作表?電子表格、工作簿(.xlsx.xlsm)、工作表標簽、?工作表與單元格-文件的建立與打開-文件的保存與加密保存-保存工作區:視圖/保存工作區Book2功能區編輯欄HA列標行號-垂直滾動條工作表格區水平滾動條工作表標簽Jheetl狀態欄顯示比例、、、、Vk、、、、、、＜、、、、、、、、、、、、、、、、、、、、、、、、、、、VX、、、、、、、、、、、、、、V＜、、、、、、、、、、、、、、、、、、、、BIQ23

i5jzi~i89i10,11i12:13,14J516.一1?.!__Excel窗口中的各種元素“文件”菜單快速訪問工具欄標題欄r^.MAJUfiiSH該郎蓽聞現E力_|-，XAA?11?至觀X?械—Aymx.tir

/i雉唎—賊■4jB/U，A.a’丨塞3-"?'%,粒3*酌玲，

田格式，J_.-^'A**5?*it；ri?-|雜-腳-.f^■利對柿式醉華元慵蝴第1章基礎知識與基本操作數據類型與數據輸入?數值型數據與輸入-日期格式與輸入?分數形式3/8;3-8；當前日期CTRL+;-文本型、邏輯型數據與輸入?身份證數據錄入___-輸入大批數據的方法?多個工作表數據錄入?等差數列?在單元格內輸入/顯示多行文本:-開始/對齊方式組第1章基礎知識與基本操作?三、選定/修改/刪除-選定行/列/單元格-修改、刪除數據刪除與清除的區別？：開始第1章基礎知識與基本操作?四、輸入序列與自定義序列一自動填充序列?用填充柄填充數據序列?等差數列:開始的兩個單元格數據，然后拖曳?用命令填充等差或等比序列-開始1填充序列?自動填充文字序列?自定義序列-在工作表中,選擇要在填充序列中使用的項目列表。一單擊“Office按鈕/“Excel選項”。--“常用”,/“使用Excel時采用的首選項”下單擊“編輯自定義列表”。-“從單元格中導入序列”/“導入”第1章基礎知識與基本操作?五、移動插入/復制插入/交換數據?移動插入-Shift+左鍵-右鍵拖曳?復制插入-Ctrl+Shift+左鍵—右鍵拖曳?交換數據-相鄰的兩行或兩列做移動插入,即可實現數據交換第1章基礎知識與基本操作?六、插入行/列/單元格?插入行/列-選定行、列或單元格（選定的數量與將要插入的數量是等同的）一插入菜單3行（列）,或者右鍵插入?插入單元格-選定單元格區域（選定的數量與將要插入的數量是等同的）-插入菜單3單元格,或者右鍵插入1951第1章基礎知識與基本操作?七、命名行/列/單元格區域?命名的好處-通過名字引用單元格區域，要比用地址引用更加直觀；-通過名字可以快速選定單元格區域?命名的方法-單元格區域命名:公式=＞定義名稱-為選定區域的行/列命名，公式=＞根據所選內創建第1章基礎知識與基本操作?八、批注?批注是為單元格加注釋?■—個單元格加批注后,右上角會出現一個三角標識?當鼠標指向這個標識的時候,顯示批注信息?審核=>新建批注,或者右鍵插入批注_第2章公式、常用函數與地址引用?二課時?本章重點:?運算符、公式與常用函數?相對地址、絕對地址和混合地址?公式的復制對公式中地址引用的影響?難點:?相對地址、絕對地址和混合地址的正確使用第2章公式、常用函數與地址引用■—、公戎:■以等號開始，可以包含函數、地址引用、運算符、常量。運算符含義優先渡負號或單目減高%百分號A乘方\/乘、除+、-加、減&文本連接運算舉例關系運〉|?心^^ll^、、s、、、、、第2章公式、常用函數與地址引用-單元格區域的表示方法-簡單公式舉例?1.地址引用三種形式：?在公式輸入時，按F4鍵可在幾種引用之間轉換。?在將公式復制到其他單元時起作用（移動時不變化）o舉例:復制/移動/插入/刪除單元格對公式，.的影響、_。。.第2章公式、常用函數與地址引用?數組公式:-數組的概念:對一組數據進行運算，結果也是一組數據?注意:-使用“Ctrl+Shift+Enter”組合鍵代替“Enter”才能正確顯不結果-運算結果不能單獨改變舉例第2章公式、常用函數與地址引用二、函數函數可以完成特殊功能或操作,使用函數可以簡化和縮短公式,甚至可以完成公式不能完成的功能或操作。（-）函數格式、輸入與嵌套25?函數格式:函數名（自變量1,自變量2,......）?*一個函數的自變量可以有0—30個第2章公式、常用函數與地址引用?函數輸入(調用):-插入/函數-按鈕fx-或shift+f3?函數嵌套:將某一函數作為另一函數的參數調用。最多可以嵌套七層。?例:=if(average(a2:a6)>50，sum(b2:b6)，0)第2章公式、常用函數與地址引用-函數嵌套:將某一函數，作為另一函數的參數調用。最多可以嵌套七層。?例:=if(average(a2:a6)〉50，sum(b2:b6)，0)第2章公式、常用函數與地址引用（二）常用函數1.求和函數SUM（參數1,參數2,...）功能:求一系列數據的累加和。舉例2.算術平均值函數AVERAGE（參數1,參數2,...）。功能:求一系列數據的算術平均值。3.修剪平均值TRIMMEAN（數據區，百分比）?先從數據系列的頭部和尾部除去一定百分比的數據點，然后再求平均值。?比如，我們在計算選手平均分數中常用去掉一個最高分，去掉一個最低分，xx號選手的最后得分，就可以使用該函數來計算。4.最大值函數MAX（參數1,參數2,...）。功能:求一系列數據的最大值。5.最小值函數MIN（參數1,參數2,...）功能:求一系列數據的最大值。6.求數據集中第K個最大值,ARGE（array，k）與第K個最小值SMALL（數據區域，k）第2章公式、常用函數與地址引用?7.統計個數函數COUNT、COUNTA.COUNTBLANK?(1)COUNT(參數1，參數2，...)?功能：求一系列數據中數值型數據的個數。?(2)COUNTA(參數1，參數2,...)?功能：求“非空”單元格的個數。?(3)COUNTBLANK(參數1，參數2,...)?功能：求“空”單元格的個數。......第2章公式、常用函數與地址引用\19518.U!舍五入函數ROUND(數值型參數,N)。?功能:按指定的位數對數值進行四舍五入。?ROUND(number,num_digits)-Number需要進行四舍五入的數字。-Num.digits指定的位數,按此位數進行四舍五入。?說明-如果num_digits大于0,則四舍五入到指定的小數位。-如果num_digits等于0,則四舍五入到最接近的整數。-如果num_digits小于0,則在小數點左側進行四舍五入。第2章公式、常用函數與地址引用9.條件函數IF(邏輯表達式，表達式1，表達式2)?功能:執g真假值判斷，根據邏輯計算的真假值，返回不向結果。?IF(logical_test，value_ifLtrue，value_ifLfalse)10.條件求知函數SUMIF(條件數i[Z,“條件”，步知姑捉、?功能/根據指定條件對若干單元格求和。?SUMIF(range,criteria,sum_range)-Range為用于條件判0^的單元格區域。-Criteria為確定哪些單元格將被相加求和的條件。-Sum_range是需要求和的實際單元格。第2章公式、常用函數與地址引用11.條件計數COUNTIF(條件數據區，“條件”)O?功能:計算區域中滿足給定條件的單元格的個數。?COUNTIF(range,criteria)-Range為需要計算其中滿足條件的單元格數目的單元格區域。-Criteria為確定哪些單元格將被計算在內的條件。?AVERAGEIF函數?返回某個區域內滿足給定條件的所有單元格的平均值(算術平均值)。?語法?AVERAGEIF(range,criteria,average_range)錯誤值錯誤值錯誤值出現的原因舉例說明#DIV/O!被除數為0=3/0#N/A引用了無法使用的數值#NAME?不能識別的名字=SUN(A1:A4)#NULL!交集為空=SUM(A1:A3B1:B3)#NUM!數據類型不正確=SQRT(-4)#REF!引用無效單元格引用單元格被刪除#VALUE!不正確的參數或運算符-4+”a”########免度不夠，加寬即可第3章工作簿與工作表?一、選定/移動/復制工作表-選定工作表-在工作簿內“移動/復制”工作表-不同的工作簿之間“移動/復制”工作表-插入/刪除/重新命名工作表第3章工作簿與工作表?二、保護工作表?目的:-不允許對工作表中的某些或全部單元格進行修改操作；-根據需要選擇是否允許插入行/列、刪除行/列等操作。?即可以對整個工作表進行保護，也可以只保護指定的單元格區域。保護工作表f'x'0保護工作表及技定的單元格內容(Q)取消工作衷保護時使用的$碼(E):允許此工作表的所有用戶進行(0)$選定未拽定的單元格勹設S弟元格格式□設置列格式[□設3行格式j摘入列□摘入行口插入超H接3瞧列I破定II取消I人、、、、、、、、、、、、、、、、、、、第3章工作簿與工作表?1.保護工作表的條件?條件：-被保護的單元格區域處在“鎖定”狀態；-執行工具菜單=>保護=>保護工作表。-默認情況下,工作表中的每個單元格都處于“鎖定”狀態；k、、、、YSS、、、、Y\、VW\、、、、、、VV\、、、、、、、、、VVV\、、、、、、、、、、、、、、、、、\\YS、、、、、、、、、、、、、、、、、、、、VWVVV\、、、、、、、、、、、U、\^X、、、、、、YS、、、、、、、、、、、、、、、、、、、、、、、VV\、、、、、、、、VvVWi第3章工作簿與工作表?2.保護工作表的步驟①選定修改的單元格區域,右鍵=＞設置單元格格式3保護3取消“鎖定”；②選定修改的單元格區域,右鍵=＞設置單元格格式=＞保護=＞選中“鎖定”（單元格缺省是鎖定的）；③審核3保護工作表。④或開始一格式下拉-----=＞保護工作表第3章工作簿與工作表?3.撤銷工作表的保護?將要撤銷的工作表變成當前工作表；?單擊工具菜單=>保護=>撤銷工作表保護;如果保護工作表時設置了密碼,必須輸入密碼后才可以撤銷對工作表的保護。第3章工作簿與工作表?4.隱藏工作表?隱藏工作表-開始----格式下拉3隱藏和取消隱藏?取消隱藏第3章工作簿與工作表?5.隱藏公式?隱藏公式后，在編輯欄和單元格均看不到公式，只能在單元格看到公式的計算結果。?隱藏公式:①選定要隱藏的單元格區域a設置單元格格式=>保護3隱藏；②保護保護工作表。?恢復顯示公式:撤銷工作表保護;第3章工作簿與工作表?三、保護工作簿?目的:-禁止刪除、移動、重命名或插入工作表；-也可以禁止執行移動、縮放、隱藏和關閉工作簿窗口等操作。?如果保護工作簿時設置了密碼,只有知道密碼才能取消保護。第3章工作簿與工作表?五、同時顯示一個工作簿的多個工作表-在默認情況下,一個工作簿內的所有工作表在一個窗口中打開,通過單擊工作表標簽顯示不同工作表中的內容。-若希望同時看到一個工作簿內的多個工作表:①視圖=>新建窗口；②視圖O全部重排或并排查看；第3章工作簿與工作表?六、同時顯示多個工作簿的工作表-打開多個工作簿后,同時顯示多個工作簿的工作表的操作與上述操作一樣,只是放棄選擇“當前活動工作簿的窗口”復選框。kUY^^XVWV^sxs^SV^VVvwvv^VvVvvvvVVvVW^V^s'V^wvvSSVWwV^vvWvvVvsV^WvsY^VVVvWVVsV^VWV^VVvVvvvwwvvVVvvVWtfvwvvVVVSvvVVSXVwvv^VsxSXV^VV^^第3章工作簿與工作表?七、拆分窗口?一個工作表窗口可以拆分為“2個窗格”或“4個窗格”，分隔條將窗格分開。?窗口拆分后，能方便地瀏覽一個工作表的不同部分。?拆分的方法:-鼠標拖曳滾動條上的“拆分條”；-或者，視圖=＞拆分；?取消拆分的方法:-雙擊滾動條上的“拆分條”；-或者，視圖=＞取消拆分；?wWV\wvft^、、WvwVV\、\wWY\vW%Wvwv、、、、vk\W^\YWvwVWv<v<W\、、、、、、WgttWKV^VWVvVwVv￥S、、v\、\\、、\W$X、、、、WWVWVSX、、wvV<SSW^\、Ws\,第3章工作簿與工作表?八、凍結窗格?如果工作表過大，在向下或向右滾動顯示時會看不到表頭，這時可以采用“凍結”行或列的方法，凍結始終要顯示的前幾行或前幾列。?凍結窗格的方法:①選擇一個單元格；②視圖3凍結窗格。?撤銷凍結的方法:-視圖=>凍結窗格下拉-----取消凍結窗格第4章格式化工作表?改變數據顯示格式:開始一格式下拉一設置單元格格式“常規”格式按鈕名稱與按鈕改變后的數據格式1210.6貨幣樣式”￥1,210.601210.6%“百分比樣式”121060%1210.6，“千位分隔樣式”1,210.601210.6心‘增加小數位數”1210.6001210.6￡“減少小數位數”1211第4格式化工作表、改變數據的顯示格式選定要改變顯示格式的單元格區域；開始--格式下拉=>設置單兀格格式—---數字,或者右鍵3設置單元格格式3數字；單元格格式1?在“分類”列表中選擇數據格式的類第4章格式化工作表?二、自定義數據的顯示格式?格式/單元格/數字/分類框----自定義-0與#的區別:-設置千、百萬格式V‘_L

多參、ULLtE、1951第4章格式化工作表三、數據的格式修飾數據的格式修飾-包括改變數據的字體、字型、字號、顏色等。BIU▼方法:-開始----格式下拉=>設置單元格格式格式工具欄的按鈕-單元格格式=>字體k、、、V\、、、、、、、、V\、、、、、第4章格式化工作表條件格式?條件格式用于為滿足條件的單元格數據設置特定的文字格式、邊框和底紋。?選定單元格區域二格式菜單=＞條件格式色階wxxwswxxxww.Sxwwxxxwxw.wwxxkxwsxxxxwxswswwswxwxwwxwwsx'?色階作為一種直觀的指示,可以幫助了解數據分布和數據變化。雙色刻度使用兩種顏色的漸變來幫助比較單元格區域。顏色的深淺表示值的高低。?使用雙色（或三色）刻度設置所有單元格的格式?選擇區域、表或數據透視表中的一個或多個單元格。?開始”“樣式”組中,單擊“條件格式”旁邊的箭頭,然后單擊“色階”。?選擇雙色刻度（或三色）。?使用數據條設置所有單元格的格式?使用圖標集設置所有單元格的格式?新建規則?管理規則第4章格式化工作表?五、表格的格式修飾-調整行高/列寬-隱藏行/列/標號?頁面布局---工作表選項組---查看-對齊方式?開始---對齊方式組-大標題的合并居中與合并單元格?開始…對齊方式組----合并后居中按鈕第5章圖表與打印輸出?一、圖表的類型與組成Excel提供了十幾種圖表類型,每個圖表類型又細分為多個子類型。—柱形圖-條形圖-折線圖-餅圖-面積圖-XY散點圖-圓環圖-股價圖第5章圖表與打印輸出?圖表的組成?一士圖表由以下部分組成:-圖表標題-坐標軸與坐標軸標題-圖例—繪圖區?二、創建圖表?插入圖表--ALT+F1?設置格式--ctrl+l-創建常用圖表-柱形圖、折線圖、XY散點圖、餅圖、圓環圖-*創建/修飾組合圖表第5章圖表與打印輸出?三、圖表編輯與格式修飾-圖表編輯-圖表的格式修飾-*調整柱形圖數據標志間距與應用舉例第5章圖表與打印輸出打印設置-打印頁面的基本設置?頁面布局-添加“頁眉/頁腳”?插入/文本組/頁眉和頁腳?五、打印預覽與打印-打印預覽?文件/打印/打印預覽?視圖/工作薄視圖:分頁預覽-打印輸出.文件/打印第5章圖表與打印輸出?打印技巧:-使用分頁符-打印區域與重復標題的設置-打印非連續區域?方法1:復制為圖片?方法2:使用照相機復制圖片-照相機?區別:照相機得到的圖片會隨原圖變化而變化，菜單復制的圖片不會改變第6章數據處理與管理?一、數據清單?第一行必須包含每列的標題。數據區域內不應有任何空行或空列。?每列應僅包含一種類型的數據,例如,文本放到一列,而數值放到另一列。記錄單”的使用-用“記錄單”編輯數據清單一用“記錄單”查找數據清單中符合條件的記錄第6章數據處理與管理二、篩選與局級篩選-自動篩選與應用舉例—局級篩選與應用舉例?條件設置一與（同行）、或（錯行）?公式作為篩選條件-公式的結果作為條件來篩選記錄:-條件區的第一行為空，但是仍然是條件區域的一部分。（由于系統通過公式中的地址引用來確定條件所在的歹ij,因此不需要表頭。）-公式中用作“條件”的引用必須用相對地址引用，公式中其他所有的引用都必須用絕對引用。排序?1.排序原則：-數值：按數值大小-字母與符號：0~9空格!”#$%&()*，./:;?_八」｛1｝?+<=>A~Z-漢字?按拼音字母排序：“大”<“小”?按筆劃排序：按筆劃多少?按“自定義序列”排序-邏輯值排序原則：FALSE<TRUE第6章數據處理與管理?注意：-無論升序還是降序排列，空白單元格總是排在最后面所有錯誤值的優先級相同；排序時，隱藏的行/列也會參與排序，最好取消隱藏?2、簡單排序?3.自定義序列排序?排序技巧舉例：快速制作工資條第6章數據處理與管理?五、分類匯總?分類匯總是指在數據清單中按某一列數據的值對數據清單進行分類后，按不同的類對數據進行統計。?分類匯總時要:-確定數據清單中的一列為分類字段；-分類字段中的同類別的數據在相鄰的單元格（預排序）；-確定分類匯總的方式（總和、個數、平均值）；-確定要統計哪些數據列。第6章數據處理與管理?六、數據透視表?數據透視表是對原有的數據清單重組并建立一個統計報表。?數據透視表是一種交互式的、交叉制表的Excel報表,用戶對數據進行匯總和分析。?OfficeExcel2007中，“數據透視表和數據透視圖”命令被分成以下兩個命令:?“數據透視表”、“數據透視圖”?“插入”選項卡上的“表”組中的“數據透視表”組內命令第6章數據處理與管理?七、分級顯示?分級顯示可以隱藏數據表中的若干行/列,只顯示指定的行/列數據。?分級顯示通常用于隱藏數據表的明細數據行/列,而只顯示匯總行/列。?一般情況下,匯總行在明細行的下面,匯總列在明細列的右側。第6章數據處理與管理?八、合并計算?用三維公式實現合并計算按位置合并計算?按分類合并計算第6章數據處理與管理?九、列表?為了更好地對工作表中的數據區進行獨立的管理,可以將每一個數據區創建為一個列表。?一個工作表中可以創建多個列表,一個列表相當于一個獨立的數據集,可以對列表進行篩選、添加行和創建數據透視表等操作。?Excel函第7章函數與應用數一共11類,分別是數據庫工作函數、日期與時間函數、工程函數、信息函數、財務函數、邏輯函數、統計函數、查找和引用函數、文本函數、數學和三角函數和用戶自定義函數。?函數的構成:-函數名:不區分大小寫。-參數表:用逗號分隔的參數1,參數2,...,參數N(N<30)構成；，參數可以是常數、單元格地址、單元格區域、單元格區i或的名綠或函邊I。-返回值:根據參數表給出的參數計算得到的結果。第7章函數與應用?函數嵌套?函數嵌套是指一個函數可以作為另一個函數的參數使用。?例如:ROUND(AVERAGE(A2:C2)，0)-AVERAGE的返回值作為ROUND的參數；-Excel函數嵌套最多可嵌套七級。函數的輸入?單擊/x?插入菜單3函數?快捷鍵:Shift+F3第7章函數與應用與三:\1951XWWXXWXKWWX'?絕對值函數ABSABS(number)返回數字的絕對值(對值的實數-例子:solValue)numberABS(5.8)=5.8ABS(-4.3)=4.3?取整函數INT需要計算其絕?INT(number)-將數字向下舍入到最接近的整數()-number需要進行向下舍入取整的實數-例子:1NT(8.9)將8.9向下舍入到最接近的整數(8)INT(-8.9)將-8.9向下舍入到最接近的整數(-9)A24NT(A2)返回單元格A2中正實數的小數部分第7章函數與應用?求余函數MOD?MOD(number,divisor)()?返回兩數相除的余數,結果的正負號與除數相同,符號不同取互補數。?number被除數?divisor除數?例子,MOD(7,4)=3第7章函數與應用?平方根函數SQRT(number)?返回number的平方根()?例子:SQRT(4)=2?說明:如果參數Number為負值，函數SQRT返回錯誤值#NUM!第7章函數與應用?隨機函數RAND()?返回大于等于0及小于1的均勻分布隨機()數,每次計算工作表時都將返回一個新的數值。?若要生成a與b之間的隨機實數，請使用:RAND()*(b-a)+a?例子:產生1-20之間的整數:INT(RAND()*20)+lFORECAST(x,known_y’s,known_x’s)已知線型趨勢值函數FORECAST^數值為己有的x值和y值,再利用線性回歸對新值進行預測()x為需要進行預測的數據點known_y’s為因變量數組或數據區域known_x's為自變量數組或數據區域第7章函數與應用?中位數函數MEDIAN?MEDIAN(numberl,number2,...)?返回給定數值集合的中值()，如果參加統計的數據的個數為偶數()，返回位于中間的兩個數的平均值。?例子:MEDIAN(1,5,2)=2MEDIAN(2,1,4,3)=2.5(2和3的平均數)?說明:-參數應為數字，或者是包含數字的名稱、數組或引用:-如果數組或引用參數包含文本、邏輯值或空白單元格，則這些值將被忽略:但包含零值的單元格將計算在內。序函數RANKRANK（number,ref,order）返回一個數字在數字列表中的排位。數字的排位是其大小與列表中其他值的比值（如果列表已排過序,則數字的排位就是它當前的位置）。Ref為數字列表數組或對數字列表的引用。Ref中的非數值型參數將被忽略。Order為一數字,指明排位的方式。如果order為（）零）或,MicrosoftExcel對數字的排位是基于ref為按照降序排歹I的列表。如果,MicrosoftExcel對數字的排位是基于ref為按照升序排列的列表。函數RANK對重復數的排位相同。但重復數的存在將影響后續數值的排位。例如,在一列按升序排列的整數中,如果整數10出現兩次,|其排位為財11的排位為7、、、（沒有排位為第7章函數與應用?眾數函數MODE眾?數(Mode):一組中出現最頻繁的數或數列。?MODE(numberl,number2,...)?返回在某一數組或數據區域中出現頻率最多的數值?如果數據集合中不含有重復的數據，則MODE數返回錯誤值#N/A?例子:MODE(2,5,6,5,8,6,11,6,8)=6MODE(2,6,5,6,2,6,5,5)=6第7章函數與應用?眾數、中位數和平均數:?在一組數值中，眾數（）是出現頻率最高的數值，而中位數（）是位于中間的.值，平均數（）是平均后的值，所有這些求中函數都不能單獨地完全描繪所有數據。?假設數據分布在三個區域中,其中一半分布在一個較小數值區中,另外一半分布在兩個較大數值區中。函數AVERAGE和函數MEDIAN可能會返回位于數據點稀疏處的中間值；而函數MODE則會返回位于數據點密集處的較小值。第7章函數與應用?FREQUENCY(data_array,bins_array)?頻數函數FREQUENCY以一列垂直數組返回某個區域中數據的頻率()分布?data.array為一數組或對一組數值的引用，用來計算頻率。?bins_array為間隔的數組或對間隔的引用，該間隔用于奸data_array中的藪值進行分組。?bins^array要求從小到大存放，每個數據表示一個分段點，統計的結果包含該數據以及小于該數據的個數?計算結果是一個數組，用“Ctrl+Shift+Enter”輸入?例子:統計“職工情況表”不同年齡段的人數。第7章函數與應用?標準差函數STDEV?標準差(StandardDeviation):反映一組數據于數據的平均值的離散程度。標準差函數STDEV(參數1,參數2,...)功能:返回自變量表列中數據的標準偏差。自變量為數值型。——標準差反映數據相對于均值的離散程度。第7章函數與應用方差:（加平方是為了消除+_號的影響）標準差:（便于與平均值對比）?如果標準差比較小,說明數據于平均值的離散程度就比較小,平均值能夠反映數據的均值,具有統計意義。?如果標準差比較大,說明數據與平均值的離散程度就比較大（數據中含有非常大或非常小的數）,平均值在一定程度上失去數據“均值”的意義。.?邏輯函數第7章函數與應用?邏輯與函數AND?AND(logicall,logical2,...)?所有參數的邏輯值為真時，返回TRUE；只要一個參數的邏輯值為假，即返回FALSE。?說明:-參數必須是邏輯值TRUE或FALSE,或者包含邏輯值的數組或引用。-如果數組或引用參數中包含文本或空白單元格，則這些值將被忽略。-如果指定的單元格區域內包括非邏輯值，則AND將返第7章函數與應用?邏輯或函數OR(logical1,logical,...)?任何一個參數邏輯值為TRUE,即返回TRUE;所有的參數均為FALSE,才返回FALSEo?邏輯非函數NOT?NOT(logical)?函數值取參數的反值。參數值為TRUE則函數值為FALSE；反之，參數值為FALSE則函數值為TRUE。?例子:工資補助表1951rw

算櫬準如下T補助1的計算標準是:局級500,處級300,其余人員100。補助2的計算標準是:處級以上且工資低于2000的補助100o在H3輸入公式計算“補助1”:=IF(OR(F3=”正局級”，F3=”副局級”)，500，IF(OR(F3=”正處級”，F3=”副處級”)，300，100))在13輸入公式計算“補助2”:=IF(AND(NOT(F3=”科員”)，G3<2000)，100,0)或=IF(AND(F3<〉”科員”，G3<2000)，100,0)圖5.66邏輯函數的應用舉例第7章函數與應用?數據庫函數?數據庫函數的格式:DatabaseFunction(database,field,criteria)其中:?database是數據清單區?field用于用于指明要統計的列，field可以是:-字段()名所在的單元格地址；-帶英文雙引號的“字段名”:-字段在數據清單中的位置，“1”表示第1列，等等。?Criteria:條件區，約定與高級篩選一樣。第7章函數與應用?條件區:條件區的第一行是字段名或者是數據清單中字段名所在單元格地址的引用,或為空白（公式作為篩選條件）；?從第二行開始設置篩選條件:篩選條件出現在不同的行表示“或”關系，篩選條件出現在相同行表示“與”關系；?在條件中允許出現代表任意一個字符串，“?”代表任意一個字符；第7章函數與應用?求和函數DSUM(database，field,criteria)?功能:對database區的field字段求滿足條件區條件的數據的累加和?說明:-DSUM和SUM不同，SUM求任意數據的累加和，DSUM求數據清單中一列數據中滿足條件的數據累加和；-DSUM包含了SUMIF的功能。SUMIF只能對給定的一個條件求數據的累加和，而DSUM可以對給定的多個條件求指定的列數據的累加和。第7章函數與應用?其他數據庫函數?平均值函數DAVERAGE?最大值函數DMAX?最小值函數DMIN?統計數值型數據的個數DCOUNT-省略field字段,對數據清單區求滿足條件的個數?統計個數DCOUNTA-省略field字段,對數據清單區求滿足條件的個數第7章函數及應用五、財務函數及應用1.償還函數PMT(rate,nper,pv,fv,type)舊&第7章函數與應用六、日期函數及其應用100（1）取日期的天數函數DAY（日期參數）功能:用整數（1?31）返回日期在一個月中的序號。例如:DAY（2008/10/l）=lo?（2）取日期的月份函數MONTH（日期參數）功能:用整數（1?12）返回日期的月份。例如:MONTH（2008/10/1）=10。(3)取日期的年函數YEAR(日期參數)功能:用位整數返回日期的年份。Ui例如:YEAR(2008/7/l9)=2008o?(4)取當前日期和時間函數NOW()功能:返回當前計算機系統的日期和時間，包拮牟、月、曰相時尚O說明:NOWO函數的值會根據計算機系統的不同的日期和時間自動更新。例如:若輸入=NOW()顯示2006/7/1915:48:57,按F9鍵或編輯其他單元格，系統重新計算日期和時間。?(5)取當前日期函數TODAYQ功能:返回當前計算機系統的日期，包括年、月、曰。說明:TODAYO函數的值會根據計算機系統的不同的日期自動更新。?【例1.64】根據圖69的“B列”職工的出生日期，計算職工到目前為止的年齡。如果在“職工情況表”中填寫“出生日期”，要比填寫“年齡”更科學。因為隨著年齡的增長，所填寫的“年齡”已經失去意義。因此最好填寫“出生日期”，再根據“出生日期”計算到目前夫止的年齡。操作是:在C2輸入公式=YEAR(TODAY())-YEAR(B2),然后再復制到C3:C8o?【例1.65】統計圖69的數據表中1960年以前出生的人數(不含1960年出生的)。操作是:(1)建立條件區:在E7單元格輸入公式:=YEAR(B2)<1960。(2)在E4單元格輸入公式計算1960年以前出生的人數:=DCOUNT(A1:C8，，E6:E7)圖69日期函數應用舉例第7章函數與應用七、文本函數及其應用?1.文本函數?(1)字符串長度函數LEN(S)、LENB(S)?功能:返回字符串的長度。?LEN與LENB的區別是:LEN中的一個漢字為1個字符；LENB中的一個漢字為2個字符。?例如:LEN(“計算機CPU”)=6;LENB(“計算機CPU’>9□第7章函數與應用?(2)截取子串函數MID(S,m,n)、MIDB(S,m,n)?功能:返回S串從m位置開始的共n個字符或文字。?MID（”計算機中央處理器CPU”,4,5）=”中央處理器”。?MIDB（”計算機中央處理器CPU”,7,4）=”中央。?MID(“CPU”,2,1)=“P”O第7章函數與應用?（3）截取左子串函數LEFT（S［，n］）?功能:返回S串最左邊的n個字符。如果省略n，獻Un=l。?LEFT（”計算機中央處理器CPU”，4）=”計算機中”?LEFTB（”計算機中央處理器CPU”，4）=”計算”?（4）截取右子串函數RIGHT（S［,n］）、RIGHTBtS［,n］）?功能:返回S串最右邊的n個字符。如果省略n，獻認n=l。?RIGHT（”計算機中央處理器CPU”，6）=“處理器CPU”?RIGHTB（”計算機中央處理器CPU”,9）=“處理器CPU”第7章函數與應用?(6).數值轉文本函數TEXT(數值型數據，格式)?功能:按給定的“格式”將“數值型數據”轉換成文本型數據。?例如:TEXT(12?345，”$##,##0?00”)=”$12?35”TEXT(37895，”yy-mm-dd”)=03-10-01第7章函數與應用?(7)文本轉數值函數VALUE(S)?例如:VALUE(”12.80”)=12.8VALUE(”AB”)=#VALUE!?(8)大小寫字母轉換函數LOWER(S)、UPPER(S)?LOWER與UEEER都不改變文本中的非字母的字符。?轉換為小寫字母函數LOWER(S)例如:=LOWER(”aBCdE”)的結果是”abcde”。?轉換為大寫字母函數UPPER(S)例如:=UPPER(“aBCdE”)的結果是“ABCDE”。HL#,蜘ULLtE、1951八、查找和引用函數及應用第7章函數與應用1.按列查找函數VLOOKUP（查找值，數據區，列標，匹配類型）。?功能:VLOOKUP函數在第二個自變量給定的數據區域的最左列中查找第一個自變量給定的值，然后將第三個自變量指定列值返回到單元中。?匹配的類型:?若省略=true=l,即返回近似值（小于查找值的最大值，數據區域的第一列必須排序）?若為0=false,要求精確匹配值。第8章數據分析六課時本章重點:用假設方法求解線性回歸分析規劃求解第8章數據分析?_、用假設方法求解?目的：研究當變量在一定范圍內變化時，與之相關的的公式的結果。?1.使用“單變量求解”命令求特定解一108通過調整一個單元格中的值,從而求得指定元格中的特定值的方法?*目標單元格要包含公式。第8章數據分析?2.運用模擬運算表作模擬分析-單變量輸入“模擬運算表”?對一個變量輸入不同的值,模擬它對一個或多個公式影響的結果-雙變量輸入“模擬運算表”?對兩個變量輸入不同的值,模擬它對一個公式的影響結果第8章數據分析?二、線性回歸分析114?在大量觀察數據的基礎上,利用數理統計方法建立因變量與自變量之間的回歸關系函數表達式（稱回歸方程式）。?當研究的因果關系只涉及因變量和一個自變量時,叫做一元回歸分析；當研究的因果關系涉及因變量和兩個或兩個以上自變量時,叫做多元回歸分析。第8章數據分析?［例1］對銷售額進行多元回歸分析預測,數據見圖。ABCD11銷售額（萬元〉電視廣告費用（萬元）報紙廣告費用（萬元）年份296050151994390020201995495040151996592025251997695030331998794035231999894025422000994030252001第8章數據分析解:本題可用二元線性回歸分析來求解:設定變量:￥=銷售額，乂1=電視廣告費用，X2=報紙廣告費用方程為:Y=a1X1+a2X2+b通過線性回歸分析確定apa2，b的值，從而確定方程。第8章數據分析1.操作方法與步驟(1)建立數據模型(2)工具一加載宏/“分析工具庫”復選框(3)工具一數據分析/“回歸”(4)設置“回歸”對話框中參數:“Y值輸入區”，“X值輸入區”，“輸出區域”(5)根據結果得出方程第8章數據分析?三、規劃求解-“規劃”是數學概念,它是指運用微積分和線性代數的方法,在滿足一組約束條件的情況下,求出一個多變量函數極值的模型。數學規劃是運籌學的一個分支,主要包括線性規劃、非線性規劃、動態規劃和整數規劃等。-規劃求解可求得工作表上某個單元格（目標單元格）中公式的最優值。（對直接或間接與目標單元格中公式相關聯的一組單元格中的數值進行調整,最終在目標單元格公式中求得期望的結果）第8章數據分析?意義:規劃求解用于在生產或工作中的一些問題或項目在受多個因素的制約的前提下如何獲得最佳的結果。如獲得最大的利潤、最小的成本等。____?注意事項:-在創建模型過程中，對“規劃求解”模型中的可變單元格數值應用約束條件，而且約束條件可以引用其他影響目標單元格公式的單元格。-添加規劃求解命令:工具/加載宏/規劃求解第8章數據分析?1、求解線性規劃問題118-例：?工廠生產3種玩具：狗、娃娃、大熊貓;?3種玩具的凈利潤（元）：90、75.50?3種玩具各用機工（小時）：3、4、5?3種玩具各用手工（小時）：4、3、2第8章數據分析?工廠極限生產能力:機工:＜=400h手工:〈二280h?要求:a.生產玩具狗的數量〈=50件生產玩具大熊貓數量〉=32件?求滿足以上條件時,如何安排各玩具的生產數量以獲得最大利潤。第8章數據分析1.建立數據表如圖:2.進行規劃求解在規劃求解參數設置中輸入:目標單元格地址可變單元格地址約束條件（如有多個逐一添加）3.單擊求解結果:如生產20只玩具狗、45個芭比娃娃、32只大熊貓，可以得到最大利潤;第8章數據分析求解的結果可形成:報告告果報報結性值算感限運敏極第8章數據分析?*關干規劃求解方法的若干選項-在調用規劃求解工具時，單擊“選項”，可以打開“規劃求解選項”對話框:-1）最長運算時間?在此設定求解過程的時間。可輸入的最大值為32767（秒），默認值100（秒）可以滿足大多數小型規劃的求解要求。第8章數據分析-2）迭代次數?在此設定求解過程中迭代運算的次數，限制求解過程的時間。可輸入的最大值為32767?默認值100次可滿足大多數小型規劃的求解要求。-3）精度?在此輸入用于控制求解精度的數字，以確定約束條件單元格中的數值是否滿足目標值:?上下限。精度值必須表示為小數（0到1之間），輸入數字的小數位越多，精度越高。-4）允許誤差?在此輸入滿足整數約束條件并可被接受的目標單元格求解結果與真實的最佳結果間的百分偏差。這個選項只應用于具有整數約束條件的問題。設置的允許誤差值越大，求解過程就越快。&第8章數據分析-5）收斂度?在此輸入收斂度數值,當最近五次迭代后目標單元格中數值的變化小于”收斂度”框叫置的數值時,椒劃求解”停止運行。收斂度只應用于非線性規劃求解問題,并且必須表示夕小數（0到1之間）。設置的數值越小,收斂度就越高。例如,0_0叨1表示比001更小的個對差別。收斂度越小,”規劃求解”得到結果所需的時間就越長。-6）采用線性模型?當模型中的所有關系都是線性的,并且希望解決線性優化問題時,選中此復選框可加速求解進程。-7）顯示迭代結果?如果選中此復選框,每進行一次迭代后都將中斷”規劃求解”,并顯示當前的迭代結果。-8）自動按比例縮放?如果選中此復選框,當輸入和輸出值量級差別很大時,可自動按比例縮放數值。例如,基于百萬美元的投資將利潤百__________________________________________________________________________________________________________________第8章數據分析-9）假定非負?如果選中此復選框,則對于在“添加約束”對話框的“約束值”框中沒有設置下限的所有可變單元格,假定其下限為零。-10）估計?指定在每個一維搜索中用來得到基本變量初始估計值的逼近方案。?正切函數:使用正切向量線性外推。?二次方程:用二次方程外推法,提高非線性規劃問題的計算精度。第8章數據分析-11）導數?指定用于估計目標函數和約束函數偏導數的差分方案。?向前差分:用于大多數約束條件數值變化相對緩慢的問題。?中心差分:用于約束條件變化迅速,特別是接近限定值的問題。雖然此選項要求更多的?計算,但在”規劃求解”不能返回有效解時也許會有幫助。第8章數據分析-12）搜索?指定每次的迭代算法,以確定搜索方向。?牛頓法:用牛頓法迭代需要的內存比共鈍法多,但所需的迭代次數少。?共軛法:比牛頓法需要的內存少,但要達到指定精度需要較多次的迭代運算。當間題較大和內存有限,或步進迭代進程緩慢時,可用此選項。-13）裝入模型?顯示”裝入模型"對話框,輸入對所要加載的模型的引用。-14）保存模型?顯示”保存模型”對話框,在其中可指定保存模型的位置。只有需要在工作表上保存多個漠型時,才使用此命令。第一個模型會自動保存。第8章數據分析?2.求解方程組的解XY+Z=O^+2-4=0第8章數據分析移動平均?移動平均法是根據時間序列資料,逐項推移,依次計算移動平均,來反映現象的長期趨勢。特別是現象的變量值受周期變動和不規則變動的影響,起伏較大,不能明顯地反映現象的變動趨勢時,運用移動平均法,消除這些因素的影響,進行動態數據的修勻,以利于進行長期趨勢的分析和預測。第8章數據分析?簡單移動平均的計算公式:?設xi為時間序列中的某時間點的觀測值，其樣本數為N;每次移動地求算術平均值所采用的觀測值的個數為n(n的取值范圍:2<n〈t-l)，則在第t時間點的移動平均值久Af—(x,++???+X{_n+])=—nn?式中:Mx——第t時間點的移動平均值，?也可當作第t+1時間點的預測值，?即:yi+i,i或yA-i?移動平均分析工具及其公式可以基于d?、的、均、值、'，、溉、第8章數據分析?［例1］某公司1994至2005年銷售額數據如圖8.30所示。進行三年移動平均,并預測2006年銷售額。操作步驟:?建立模型A,1,,：年份變際銷ft?2199428031995300419%350'5199?370'6V99S420"71999440一J820004909200148010200250011200?520122004630132005770J8章數據分析27?設置“移動平均”對話框,確定。確定I取消幫助QD|3間隔（S）■|$c$i17囝表輸出（C）17標準誤差0?移動乎均輸出選項輸出區域必:新工作表組（P）新工作薄00鋪入輸入區域（X）:[7標志位于第一行（DI遛a過ai1951?工具4數據分析/“移動平均”第8章數據分析移動平均910三年移動平均誤差1112136781357911數據點10005000?結果如圖。單元格區域D5:D12即為標準誤差。根據公式yi+1=MtW2006年的銷售額預測應為M2005,所d^j640。123451FGHIJABCD年份實際銷售粕例J28019953001996350I

3401r、例，37038036.9684551998-12041033.66501651999柳45036.968455200049。47029.4392029100148049024.49489742002500150014.1421356200?5201550jr47.95831522004630\640/88.88194422005770V/+實際值預醐第8章數據分析?8.5指數平滑?指數平滑是在移動平均的基礎上的進一步擴展。指數平滑法是用過去時間數列值的加權平均數作為趨勢值,越靠近當前時間的指標越具有參考價值,因此給予更大的權重,按照這種隨時間指數衰減的規律對原始數據進行加權修勻。所以它是加權移動平均法的一種特殊情形。其基本形式是根據本期的實際值Yt和本期的趨勢值,分別給以不同權數a和1-a,計算加權平均數作為下期的趨勢值。第8章數據