excel實用技術(shù)基礎(chǔ)

《excel實用技術(shù)基礎(chǔ)》由會員分享，可在線閱讀，更多相關(guān)《excel實用技術(shù)基礎(chǔ)（80頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、第四部分 Excel實用技術(shù)基礎(chǔ),,第17章Excel 技術(shù)基礎(chǔ),Excel的功能 Excel 的界面特征,工作簿的操作,基本概念 工作簿：（workbook）是Excel 專門用于計算及存放數(shù)據(jù)的文件，其文件類型為 .xls。在每一本工作簿中包含了多張工作表，最多為255張，故Excel 可在單個文件中管理多種類型的相關(guān)信息。 工作表：(worksheet)：通常稱為電子表格 (spreadsheet)，是用來存放和組織、處理、分析數(shù)據(jù)的文檔。每個工作表由行和列構(gòu)成，行與列相交處稱為單元格。,,工作簿的創(chuàng)建 創(chuàng)建新的空白工作簿 基于默認(rèn)的工作簿模板，新建工作簿 基于其他模板創(chuàng)建工作簿 工作簿

2、的保存,工作表的操作,工作表間的切換與數(shù)據(jù)傳遞 工作表間的切換 工作表間的數(shù)據(jù)傳遞,,工作表的管理 插入工作表 刪除工作表 移動工作表 復(fù)制工作表 重命名工作表 隱藏顯示工作表 隱藏顯示工作表中的行或列 選定工作簿中的工作表,,工作表中數(shù)據(jù)的輸入 單元格區(qū)域：每一行與一列交叉處構(gòu)成一個單元格，單元格是Excel工作表的基本編輯單位。 用戶可對多個單元格區(qū)域進行操作。由多個單元格組成的區(qū)域可用兩個單元格名稱來描述。,,單元格地址 單元格的列標(biāo)和行號又稱為地址。地址分為相對地址、絕對地址和混合地址三種。僅寫出列標(biāo)和行號即為相對地址，如A5、B16。在列標(biāo)和行號前均加入“$”符，便構(gòu)成了絕對地址，如

3、 $A$1、$C$20。僅在行號或列標(biāo)前加“$”符，則構(gòu)成混合地址。如$A5為絕對列相對行，而A$5為絕對行相對列。,單元格的數(shù)據(jù)輸入 通常Excel工作表由單元格組成，每一單元格可定義一種數(shù)據(jù)類型，如文本、數(shù)值、公式等。Excel中單元格的常用數(shù)據(jù)類型如表17-2所示。,表17-2 Excel單元格的常用數(shù)據(jù)類型,Excel 的單元格中可以輸入兩種數(shù)據(jù)，即常量和公式。 常量：不以等號（=）開頭的單元格數(shù)據(jù)，可以是日期、數(shù)字和文本。公式或由公式得出的數(shù)值都不是常量。 公式：公式總是以等號（=）開頭，其后為算式。（單元格內(nèi)的一系列數(shù)值、單元格引用、名稱、函數(shù)和操作符的集合，可以通過現(xiàn)有的數(shù)據(jù)計算

4、出新的數(shù)值。） 數(shù)據(jù)輸入格式的修改,,序列的自動填充輸入 填充相同的數(shù)據(jù): 填充序列數(shù)據(jù)： 等差序列 等比序列,,工作表中區(qū)域的選定 選定連續(xù)區(qū)域 選定不連續(xù)區(qū)域 選定行或列 選定全部單元格 取消選定的區(qū)域,工作表中內(nèi)容的編輯,修改單元格中的內(nèi)容 完全修改單元格中的內(nèi)容： 修改單元格中的部分內(nèi)容： 清除單元格中的內(nèi)容： 清除單元格中的格式或內(nèi)容：,工作表中內(nèi)容的編輯,刪除單元格或區(qū)域 插入單元格、行或列 工作表區(qū)域的復(fù)制、移動,工作表的格式化操作,工作表的自動格式化：（自動套用格式 ） 單元格的格式化 調(diào)整行高與列寬,工作表與圖表的打印,頁面設(shè)置 頁面： 頁邊距： 頁眉/頁腳 工作表,,調(diào)整分

5、頁 打印預(yù)覽 打印工作表 打印圖表,引用與公式,使用引用 引用的作用在于標(biāo)識工作表上的單元格或單元格區(qū)域，并指明公式中所使用的數(shù)據(jù)的位置。通過引用，可以在公式中使用工作表不同部分的數(shù)據(jù)，或者在多個公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至可應(yīng)用程序中的數(shù)據(jù)。 在默認(rèn)狀態(tài)下，Excel 工作表單元格的位置都以列標(biāo)和行號來表示，這種引用類型稱為A1引用類型。除A1引用類型外，Excel還可采用R1C1引用類型。其中R表示行號，C表示列號。,引用與公式,A1引用類型用字母標(biāo)志“列” （從 A 到 IV ，共 256 列）、數(shù)字標(biāo)志“行” （從 1

6、 到 65536）。A1類引用分為相對引用、絕對引用和混合引用三種。如A1、B2、C5是相對引用位置；而$A$1、$B$2、$C$5為絕對引用位置；$A1、A$1、$B2、B$2、$C5、C$5是混合引用位置。,表17-3引用示例,引用與公式,相對引用 相對引用是指引用相對于公式所在單元格位置的單元格，當(dāng)復(fù)制“使用相對引用的公式”時，被粘貼公式中的“引用”將被更新，并指向與當(dāng)前公式位置相對應(yīng)的其他單元格。,,絕對引用： 絕對引用是指引用工作表中固定不變的單元格。如果在復(fù)制公式時，不希望引用發(fā)生改變，即從一個單元格復(fù)制到另一個單元格時，公式不會因位置變化而發(fā)生變化，則要使用絕對引用。在不希望改變

7、的引用前加一個美元符號“$”，就可以對單元格進行絕對引用。,,混合引用： 包含一個絕對引用和一個相對引用的公式引用時，對被引用的某個單元格而言，它也許是行固定，也許是列固定。,運算符及其優(yōu)先級,公式輸入,直接輸入公式 使用編輯欄的公式區(qū)輸入,公式的編輯,公式的修改： 公式的移動或復(fù)制 公式的刪除 引用與公式舉例：,函數(shù),函數(shù)是對一個或多個執(zhí)行運算的數(shù)據(jù)進行指定的計算,并且返回計算值的公式.執(zhí)行運算的數(shù)據(jù)稱為函數(shù)的參數(shù)。參數(shù)可以是文本、數(shù)字或邏輯值。函數(shù)運行后傳回來的數(shù)據(jù)稱為函數(shù)的結(jié)果。,函數(shù),函數(shù)的分類 Excel 中的函數(shù)通常分為常用函數(shù)、工程函數(shù)、財務(wù)函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計函數(shù)、查詢與

8、引用函數(shù)、數(shù)據(jù)庫函數(shù)、文本函數(shù)、邏輯函數(shù)、信息函數(shù)等。,,函數(shù)的書寫格式 函數(shù)的一般格式為： 函數(shù)名(參數(shù)1，參數(shù)2，參數(shù)3) 如：SUM(Numberl,Number2,Number3) 函數(shù)的調(diào)用方法,常用函數(shù)介紹,數(shù)學(xué)函數(shù) 統(tǒng)計函數(shù) 文本函數(shù) 財務(wù)函數(shù) 邏輯函數(shù) 日期和時間函數(shù) 查找與引用函數(shù),公式出錯的原因及排除方法,如果公式不能正確計算出結(jié)果，Excel 將顯示一錯誤值。 出現(xiàn)錯誤值 “#####！”的原因 輸入到單元格中的數(shù)值太長，單元格容納不下?？梢酝ㄟ^增加列寬，來糾錯。 單元格公式產(chǎn)生的結(jié)果太長，單元格容納不下。可以通過增加列寬，來糾錯。 產(chǎn)生錯誤值 “#VALUE！” 使用錯

9、誤的參數(shù)或運算對象類型。 產(chǎn)生錯誤值 “#DIV/0！” 公式被0（零）除。,產(chǎn)生錯誤值 “#NAME?” 在公式中使用了Excel不能識別的文字。 產(chǎn)生錯誤值 “#NA” 在函數(shù)或公式中沒有可用數(shù)值。如果工作表中某些單元格暫時沒有數(shù)值，請在這些單元格里輸入“#NA”。公式在引用這些單元格時，將不進行數(shù)值計算，而是返回“#NA”。 產(chǎn)生錯誤值 “#REF!” 單元格引用無效。 產(chǎn)生錯誤值 “#NUM” 公式或函數(shù)中某個數(shù)字有問題。 產(chǎn)生錯誤值 “#NULL!” 試圖為兩個并不相交的區(qū)域指定交叉點。,公式錯誤的排除步驟如下： 確認(rèn)所有的圓括號都成對出現(xiàn)。當(dāng)創(chuàng)建公式時，輸入的圓括號將以彩色顯示。

10、確認(rèn)在引用單元格區(qū)域時，使用了正確的區(qū)域運算符。引用單元格區(qū)域時，使用冒號來分隔區(qū)域中的第一個單元格和最后一個單元格。,公式錯誤的排除方法,確認(rèn)已經(jīng)輸入了所有必選的參數(shù)。有些函數(shù)的參數(shù)是必選的，還要確認(rèn)沒有輸入過多的參數(shù)。 可以在函數(shù)中輸入(或稱嵌套)不超過七級的函數(shù)。 如果引用的工作簿或工作表名稱中包含非字母字符，必須用單引號把名稱引起來。 確認(rèn)每個外部引用都包含工作簿名稱和相應(yīng)的路徑。 在公式中輸人數(shù)字時不要設(shè)置格式。例如，即使需要輸入的數(shù)值是“$1,000”，也應(yīng)在公式中輸入“1000”。,公式錯誤的排除方法,Excel 圖表技術(shù),,創(chuàng)建圖表,創(chuàng)建圖表的方法： 使用圖表向?qū)?快速創(chuàng)建默認(rèn)

11、圖表（二種方法）,圖表的編輯與格式化,添加及改變圖表中的文字說明 編輯圖表中的文字說明 編輯圖例 更改圖表區(qū)的顏色、字體與圖表的屬性。 修改生成圖表的單元格區(qū)域 更改圖表區(qū)中的數(shù)值,圖表的編輯與格式化,改變圖表的位置 作為新工作表插入 作為其中的對象插入 改變圖表的顯示方式 設(shè)置與修改圖表的各種選項 圖表類型的修改,,關(guān)于圖表中的誤差線 誤差線以圖形形式顯示了與數(shù)據(jù)系列中每個數(shù)據(jù)標(biāo)志相關(guān)的可能誤差量。例如，您可以在科學(xué)實驗結(jié)果中顯示正負 5% 的可能誤差量： 支持誤差線的圖表 可向二維的面積圖、條形圖、柱形圖、折線圖、XY（散點）圖和氣泡圖的數(shù)據(jù)系列中添加誤差線。對于 XY（散點）圖和氣泡圖，

12、可單獨顯示 X 值或 Y 值的誤差線，也可同時顯示兩者的誤差線。,,什么是趨勢線 趨勢線用圖形的方式顯示數(shù)據(jù)的預(yù)測趨勢并可用于預(yù)測分析，也稱回歸分析。利用回歸分析，可以在圖表中擴展趨勢線，根據(jù)實際數(shù)據(jù)預(yù)測未來數(shù)據(jù)。例如，下面的圖表運用前四個季度的簡單線性趨勢預(yù)測第二年的趨勢。,,移動平均 移動平均可以平滑處理數(shù)據(jù)的波動，使圖像及預(yù)測趨勢的顯示更加清晰。 支持趨勢線的圖表類型 可以向非堆積型二維面積圖、條形圖、柱形圖、折線圖、股價圖、氣泡圖和 XY 散點圖的數(shù)據(jù)系列中添加趨勢線；但不能向三維圖表、堆積型圖表、雷達圖、餅圖或圓環(huán)圖的數(shù)據(jù)系列中添加趨勢線。如果更改了圖表或數(shù)據(jù)系列而使之不再支持相

13、關(guān)的趨勢線，例如將圖表類型更改為三維圖表或者更改了數(shù)據(jù)透視圖報表或相關(guān)聯(lián)的數(shù)據(jù)透視表，則原有的趨勢線將丟失。,常用圖表類型,條形圖,條形圖是用寬度相同的條形的高度或長短來表示數(shù)據(jù)變動的圖形。 條形圖有單式、復(fù)式等形式。 在表示定類數(shù)據(jù)的分布時，是用條形圖的高度來表示各類別數(shù)據(jù)的頻數(shù)或頻率,餅圖,餅圖，是用圓形及園內(nèi)扇形的面積來表示數(shù)值大小的圖形。主要用于表示總體中各組成部分所占的比例，對于研究結(jié)構(gòu)性問題十分有用。 在繪制圓形圖時，總體中各部分所占的百分比用園內(nèi)的各個扇形面積表示，這些扇形的中心角度，是按各部分百分比占3600的相應(yīng)比例確定的。,環(huán)形圖,中間有一個“空洞”，總體中的每一部分?jǐn)?shù)據(jù)用

14、環(huán)中的一段表示。 環(huán)形圖與圓形圖類似，但又有區(qū)別 餅圖只能顯示一個總體各部分所占的比例 環(huán)形圖則可以同時繪制多個總體的數(shù)據(jù)系列，每一個總體的數(shù)據(jù)系列為一個環(huán) 環(huán)形圖可用于進行比較研究。 環(huán)形圖可用于展示定類和定序的數(shù)據(jù)。,直方圖(Histogram),用矩形的寬度和高度來表示頻數(shù)分布的圖形，實際上是用矩形的面積來表示各組的頻數(shù)分布 在直角坐標(biāo)中，用橫軸表示數(shù)據(jù)分組，縱軸表示頻數(shù)或頻率，各組與相應(yīng)的頻數(shù)就形成了一個矩形，即直方圖(Histogram) 直方圖下的總面積等于1,直方圖與條形圖的區(qū)別,條形圖是用條形的長度(橫置時)表示各類別頻數(shù)的多少，其寬度(表示類別)則是固定的 直方圖是用面積表示

15、各組頻數(shù)的多少，矩形的高度表示每一組的頻數(shù)或百分比，寬度則表示各組的組距，其高度與寬度均有意義。 直方圖的各矩形通常是連續(xù)排列，條形圖則是分開排列。,折線圖的制作,折線圖也稱頻數(shù)多邊形圖(Frequency polygon) 是在直方圖的基礎(chǔ)上，把直方圖頂部的中點(組中值)用直線連接起來，再把原來的直方圖抹掉 折線圖的兩個終點要與橫軸相交，具體的做法是 第一個矩形的頂部中點通過豎邊中點（即該組頻數(shù)一半的位置）連接到橫軸，最后一個矩形頂部中點與其豎邊中點連接到橫軸 折線圖下所圍成的面積與直方圖的面積相等，二者所表示的頻數(shù)分布是一致的。,雷達圖,雷達圖（Radar Chart）是顯示多個變量的常用

16、圖示方法 在顯示或?qū)Ρ雀髯兞康臄?shù)值總和時十分有用 假定各變量的取值具有相同的正負號，總的絕對值與圖形所圍成的區(qū)域成正比 可用于研究多個樣本之間的相似程度,【例3.7】為研究某條河流的污染程度，環(huán)保局分別在上游、中游和下游設(shè)立取樣點，每個取樣點化驗水中的五項污染指標(biāo)，所得數(shù)據(jù)如表3-13。將各指標(biāo)用雷達圖表示出來，并分析該河流的主要污染源。,第十九章 數(shù)據(jù)管理,本章主要內(nèi)容,數(shù)據(jù)庫的管理功能 外部數(shù)據(jù)的獲取 記錄單的使用 數(shù)據(jù)的排序 數(shù)據(jù)的篩選 數(shù)據(jù)分類匯總,數(shù)據(jù)統(tǒng)計與分析 t檢驗 方差分析 相關(guān)分析 直方圖分析 回歸分析,第一節(jié) 數(shù)據(jù)庫的管理功能,外部數(shù)據(jù)的獲取 如果用戶要所使用數(shù)據(jù)已存在文本

17、文件中,要用Excel對該數(shù)據(jù)進行分析處理，則不必重新輸入數(shù)據(jù)，只需將其導(dǎo)入至Excel工作簿。 對于文本文件，需按一定的格式輸入，數(shù)據(jù)之間均由特殊的分隔符(如空格、逗號、Tab鍵)分隔開。,導(dǎo)入文本文件的操作步驟如下： 單擊“數(shù)據(jù)” “導(dǎo)入外部數(shù)據(jù)” “導(dǎo)入數(shù)據(jù)”命令，彈出“選取數(shù)據(jù)源”對話框。選擇所需的文件后，單擊“打開”按鈕，將打開“文本導(dǎo)入向?qū)А睂υ捒?“文本導(dǎo)入向?qū)А惫卜秩?，可按每一步要求做出選擇，每完成一步可單擊“下一步”按鈕或直接單擊“完成”按鈕以默認(rèn)格式快速導(dǎo)入文本文件。,記錄單的使用,Excel具備了數(shù)據(jù)庫的一些特點，可以把工作表中的數(shù)據(jù)做成一個類似數(shù)據(jù)庫的數(shù)據(jù)清單(二維表

18、)。,,數(shù)據(jù)清單是指：包含相關(guān)數(shù)據(jù)的一系列工作表行，清單的第一行具有列標(biāo)志。例如，可以將學(xué)生成績單中的姓名、學(xué)號、科目成績等建立數(shù)據(jù)清單，其列標(biāo)志就是姓名、學(xué)號、科目成績等。 數(shù)據(jù)清單中可使用“記錄”命令實現(xiàn)行的增加、修改、刪除與查找等操作。在數(shù)據(jù)清單中，可將字段名所在的一行定為表頭，在字段名下的每一行數(shù)據(jù)可看作為一個記錄。,記錄單的使用,數(shù)據(jù)庫的建立 記錄的增加 記錄修改 記錄刪除 記錄的條件查尋,數(shù)據(jù)的排序,排序是數(shù)據(jù)庫的基本功能之一，為了數(shù)據(jù)查找方便，往往需要對數(shù)據(jù)進行排序。排序是根據(jù)某一指定列的數(shù)據(jù)的順序重新對行的位置進行調(diào)整。 Excel 2002為用戶提供了三級排序，分別為主要關(guān)鍵

19、字、次要關(guān)鍵字、第三關(guān)鍵字。每個關(guān)鍵字均可按：“升序”(即遞增方式)或“降序”(即遞減方式)進行排序。排序可使用工具按鈕，也可使用命令菜單。,圖19-6 排序?qū)υ捒?使用菜單命令則需借助排序?qū)υ捒?,若要按行進行排序則需打開“排序選項”對話框。選擇排序方向“按行排序”，確定后再回到“排序”對話框中進行排序關(guān)鍵字的設(shè)置。,數(shù)據(jù)篩選,篩選數(shù)據(jù)只是將數(shù)據(jù)清單中滿足條件的記錄顯示出來，而將不滿足條件的記錄暫時隱藏。使用篩選功能可從一個很大的數(shù)據(jù)庫中檢索到所需的信息，實現(xiàn)的方法是使用篩選命令的“自動篩選”或“高級篩選”。 其中“自動篩選”是進行簡單條件的篩選；而“高級篩選”是針對復(fù)雜的條件進行篩選；“全

20、部顯示”是將篩選后剩余的部分?jǐn)?shù)據(jù)全部恢復(fù)為原來的數(shù)據(jù)。,數(shù)據(jù)篩選,自動篩選 高級篩選,分類匯總,分類匯總是指在數(shù)據(jù)清單中快速匯總各項數(shù)據(jù)的方法。在Excel 中提供了分類匯總的命令，通過這些命令，可直接對數(shù)據(jù)清單進行匯總。 在數(shù)據(jù)清單中執(zhí)行分類匯總功能之前，首先對數(shù)據(jù)清單中用以分類匯總的項（字段）進行排序，如下例中要按醫(yī)生的職稱匯總，則應(yīng)先按職稱排序，排序完成后進行如下操作。,匯總前先對匯總變量“職稱”進行排序,,圖19-12 分類匯總對話框,單擊“數(shù)據(jù)” “分類匯總”命令，彈出“分類匯總”對話框。在對話框中進行 “分類字段”、“匯總方式”、并選定“匯總項”等。,分類匯總報表中組及分級顯示,經(jīng)

21、過工作表中數(shù)據(jù)分類匯總，表中的數(shù)據(jù)被分類匯總成組，組下面包含各級成員。如上頁圖中“主任醫(yī)師”即為一組。在圖中，分類匯總表的最左邊可看到“分級顯示”按鈕，其中按鈕為第一級，代表總的匯總結(jié)果的范圍；按鈕為第二級，代表參加匯總的各個記錄項。上頁圖顯示的是三級全部呈現(xiàn)的結(jié)果。,數(shù)據(jù)統(tǒng)計與分析,Excel用戶提供了一組數(shù)據(jù)分析工具，如：方差分析、協(xié)方差分析、相關(guān)系數(shù)、指數(shù)平滑、直方圖、隨機數(shù)發(fā)生器工具；回歸、抽樣、排位與百分比排位等，稱為“分析工具庫”。用戶可以使用這些“分析工具”在工作表中尋找出一些數(shù)據(jù)的變化規(guī)律，幫助決策。使用“分析工具”只需為每一個分析工具提供必要的數(shù)據(jù)和參數(shù)，該工具便會使用相應(yīng)的統(tǒng)計或工程函數(shù)，在輸出表格中顯示相應(yīng)的結(jié)果。其中的一些工具在生成輸出表格時還能同時產(chǎn)生圖表。,,使用“分析工具”的操作如下： 單擊“工具”“數(shù)據(jù)分析” 命令，彈出 “數(shù)據(jù)分析”對話框。,,圖19-17 數(shù)據(jù)分析對話框,如果“數(shù)據(jù)分析”不存在，請運行安裝程序來加載“分析工具庫”。安裝完畢之后，必須通過“工具” “加載宏”命令，在“加載宏”對話框中選擇“分析工具庫”前的復(fù)選框，啟動它。,主要數(shù)據(jù)分析方法,t 檢驗 方差分析 相關(guān)分析 直方圖分析 回歸分析,