excel實用技術基礎

《excel實用技術基礎》由會員分享，可在線閱讀，更多相關《excel實用技術基礎（80頁珍藏版）》請在裝配圖網上搜索。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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