1、Excel 2007的高級應用Excel 2007 培訓第1章 數據統計與分析1.1 數據的排序1.1.1 單條件排序1、單條件排序：就是依據某列的數據規則對數據進行排序操作。可以使用快捷鍵實現。1.1.1 單條件排序2、默認排序規則：升序排序 / 降序排序：如果排序條件是數值格式，按數值的大小，從小到大進行排序；如果排序條件是文本格式，或者是包含數值的文本，排序規則是：數值格式的數字0-9、文本格式的數字、標點符號、字母a-z，漢字漢語拼音的順序。如果排序條件是邏輯值，按Fasle、True排序。1.1.1 單條件排序3、對單列數據排序：選中單列數據，點排序按鈕1.1.2 多條件排序選中數據

2、區域任意一個單元格；切換到數據菜單選項卡，點擊排序按鈕；設置排序關鍵字及升降序；添加排序條件；設置完成后，點擊確定。1.1.3 漢字筆畫排序默認情況，漢字以拼音的順序進行排序。1.1.4 自定義排序1.1.5 按顏色和圖標排序按單元格中字體和填充的不同顏色進行排序，或者按單元格數值使用的不同圖標進行排序。設置顏色設置圖標按顏色或圖標排序1.1.6 利用函數進行排序有時候，可能希望按某列的數值進行排序，但不希望打亂整個表格的原有順序，而只希望返回相應數值的順序序數，這樣的排序需求，可以用函數來實現。1.2 數據的篩選1.2.1 自動篩選1、單條件篩選所謂單條件篩選，就是將符合單一條件的數據篩選出

3、來。1.2.1 自動篩選2、多條件篩選所謂多條件篩選，就是將符合多個條件的數據篩選出來。1.2.1 自動篩選3、自定義篩選模糊篩選范圍篩選前N名篩選通配符篩選1.2.1 自動篩選4、清除篩選結果方法一：下拉菜單法方法二：快捷按鈕法1.2.2 高級篩選高級篩選與自動篩選就本質而言，沒有太大的不同，一個最主要的區別是進行高級篩選操作的同同時，可以直接將篩選后的數據負責到其他單元格區域中保存起來。1、單條件高級篩選2、多條件高級篩選1.2.2 高級篩選3、把結果直接復制到其他工作表原表中建立條件區域切換到其他工作表，選擇任意單元格切換到數據，單擊高級篩選設置篩選條件及復制到的位置完成1.3 數據的分

4、類匯總1.3 數據的分類匯總在Excel中，利用軟件的一種內置的統計功能，可以自動對數據進行【分類匯總】。要正確使用【分類匯總】功能，必須對作為匯總條件列的數據進行排序，然后才能依據數據值列中的分類項目，對指定列的數據進行分類匯總。1.3.1 數據分類匯總1、直接分類匯總1.3.1 數據分類匯總2、嵌套分類匯總嵌套分類匯總，就是指多次分類匯總如：先按省份，再按學院分類匯總1.3.1 數據分類匯總3、清除分類匯總1.3.2 數據分類打印和保存1、分類明細數據的打印2、分類匯總數據的保存1.3.3 刪除重復的數據行第第2 2章章 EXCEL 2007 EXCEL 2007 圖表圖表 圖表是以圖形化

5、方式表示數值數據。它能夠清晰直觀地表現數據之間的關系，反映事件發展的趨勢。 Excel 2007中提供了11種標準圖表類型。27第第2 2章章 Excel 2007 Excel 2007 圖表圖表 Excel 圖表綜述 圖表的創建 更改圖表名稱和圖表位置 更改圖表類型 更改圖表數據 更改圖表布局 設置圖表樣式 使用趨勢線與誤差線分析數據 創建圖表應用舉例第2章 Excel中的圖表2.1 Excel 圖表綜述u 分析數據規律，展示數據間的相互關系及變化分析數據規律，展示數據間的相互關系及變化趨勢30u 自動更新功能自動更新功能2.1 Excel 圖表綜述2.1 Excel 圖表綜述u 了解圖表了

6、解圖表數據系列圖表類型坐標軸圖例網格線繪圖區2.1 Excel 圖表綜述u 柱形圖柱形圖經常用于表示以行和列排列的數據。對于顯示隨時間的變化很有用u 折線圖折線圖與柱形圖類似，也可以很好地顯示在工作表中以行和列排列的數據。區別在于折線圖可以顯示一段時間內連續的數據，特別用于顯示趨勢u 餅圖餅圖適合于顯示個體與整體的比例關系。顯示數據系列相對于總量的比例，每個扇區顯示其占總體的百分比，所有扇區百分數的總和為100%u 條形圖條形圖用于比較兩個或多個項之間的差異u 面積圖面積圖面積圖圖是以陰影或顏色填充折線下方區域的折線圖圖，適用于要突出部分時間系列時，特別適合適合于顯示隨時間改變的量2.1 Ex

7、cel 圖表綜述u XY（散點）圖（散點）圖適合于表示表格中數值之間的關系，特別適合用于比較兩個可能互相關聯的變量。雖然散點圖可用于大型數據集，但其缺陷是，如果不使用VBA或插件，將很難給點加上標簽u 股價圖股價圖常用于顯示股票市場的波動，可使用它顯示特定股票的最高價/最低價與收盤價u 曲面圖曲面圖適合于顯示兩組數據的最優組合，但難以閱讀u 圓環圖圓環圖與餅圖一樣，圓環圖顯示整體中各部分的關系。但與餅圖不同的是，它能夠繪制超過一列或一行的數據。圓環圖不容易閱讀2.1 Excel 圖表綜述u 雷達圖雷達圖可用于對比表格中多個數據系列的總計，很適合適合用于進行績效評估，但不太常用。雷達圖圖可顯示4

8、6個變量之間的關系。u 氣泡圖氣泡圖與散點圖相似，但氣泡圖不常用且通常不易理解。氣泡圖是一種特殊的XY散點圖，可顯示3個變量的關系。氣泡圖最適合用于較小的數據集2.1 Excel 圖表綜述u 2007 2007 圖表新特性圖表新特性外觀改善制作步驟簡單化 圖表工具選項卡制作過程智能化 上下文選項卡 實時預覽2.2 圖表的創建圖表也稱為數據圖表，是以圖形的方式顯示圖表也稱為數據圖表，是以圖形的方式顯示ExcelExcel工作工作表中的數據，可直觀體現工作表中各數據間的關系。表中的數據，可直觀體現工作表中各數據間的關系。Excel 2007Excel 2007中提供了中提供了1111種標準圖表類型

9、，其中最常用種標準圖表類型，其中最常用的圖表類型包括：柱形圖、條形圖、餅圖、折線圖、的圖表類型包括：柱形圖、條形圖、餅圖、折線圖、XYXY散點圖、面積圖和圓環圖。散點圖、面積圖和圓環圖。常用圖常用圖表類型表類型圖表，是依據工作表中的數據繪制出來的。創建圖表必圖表，是依據工作表中的數據繪制出來的。創建圖表必須先切換至須先切換至“插入插入”選項卡，應用選項卡，應用“圖表圖表”組中的選項組中的選項創建圖表。創建圖表。 1 切換至切換至該選項卡該選項卡2 單擊此單擊此按鈕按鈕3 單擊該單擊該圖標圖標創建的簇狀創建的簇狀圓柱形圖表圓柱形圖表2.2 圖表的創建創建圖表后，認識一下圖表中各元素。創建圖表后，

10、認識一下圖表中各元素。圖表區圖表區系列圖例系列圖例水平軸水平軸水平軸標簽水平軸標簽垂直軸標簽垂直軸標簽垂直軸垂直軸數據點數據點主要網格線主要網格線默認創建的不含標題的圖表默認創建的不含標題的圖表2.2 圖表的創建 數據點與數據系列：數據點是指圖表中用于表示工作表中某數據的圖形表現形式；數據系列是指圖表中描繪一組數據的相關數據點。 坐標軸與坐標軸標簽：坐標軸一般分為垂直坐標軸和水平坐標軸。坐標軸上類似刻度的標記旁顯示的即為坐標軸標簽。 網格線：是指圖表中為了方便查看數據的輔助線條，一般情況下只顯示主要水平網格，垂直網格線自動設置為無。 系列圖例：是用于顯示每個數據系列的標識名稱和符號，圖例有可能

11、是方塊、菱形、小三角形或其他形式。 繪圖區：繪圖區主要用于承載圖表主要信息，如坐標軸、數據點、數據系列、網格線等對象的區域。 圖表中的標題：圖表中的標題分為兩類：圖表主標題和坐標軸標題。默認情況下，圖表主標題一般位于繪圖區頂端中心位置，水平坐標軸標題位于水平坐標軸線下方，垂直坐標軸標題位于垂直坐標軸左側。2.3 更改圖表名稱和圖表位置2.3.1 更改圖表名稱如果要更改圖表名稱，應選擇圖表，切換至圖表工具如果要更改圖表名稱，應選擇圖表，切換至圖表工具“布局布局”選項卡。選項卡。 2 單擊單擊1 切換至該切換至該選項卡選項卡3 輸入圖表名稱，輸入圖表名稱，按按Enter鍵完成圖鍵完成圖表名稱設置表

12、名稱設置2.3.2 更改圖表位置如果要更改圖表的位置，應選擇圖表，切換至圖表工具如果要更改圖表的位置，應選擇圖表，切換至圖表工具“設計設計”選項卡。選項卡。2 單擊此單擊此按鈕按鈕1 切換至該切換至該選項卡選項卡打開的打開的“移動移動圖表圖表”對話框對話框3 選擇圖表移選擇圖表移動目標位置動目標位置4 輸入名稱輸入名稱5 單擊此單擊此按鈕按鈕2.4 更改圖表類型創建圖表后，在功能區中自動顯示圖表工具的創建圖表后，在功能區中自動顯示圖表工具的“設設計計”選項卡，應用選項卡，應用“類型類型”組中的組中的“更改圖表類型更改圖表類型”可將當前圖表類型更改為其他圖表類型。可將當前圖表類型更改為其他圖表類

13、型。 “三維柱三維柱形形”圖表圖表1 切換至切換至該選項卡該選項卡2 單擊此單擊此按鈕按鈕3 切換至切換至該選項卡該選項卡4 單擊此單擊此圖標圖標5 單擊此單擊此按鈕按鈕2.5 更改圖表數據默認創建的圖表，將當前工作表中的所有數據做為默認創建的圖表，將當前工作表中的所有數據做為數據源創建圖表，用戶可根據需要更改數據。數據源創建圖表，用戶可根據需要更改數據。 打開的打開的“選擇數據選擇數據源源”對話框對話框2 單擊此單擊此按鈕按鈕3 單擊此單擊此按鈕按鈕1 切換至該切換至該選項卡選項卡5 單擊此單擊此按鈕按鈕4 重新選擇重新選擇數據源數據源更改數據源得更改數據源得到的圖表效果到的圖表效果更改行列

14、數據更改行列數據后的圖表效果后的圖表效果2 單擊此單擊此按鈕按鈕3 單擊此單擊此按鈕按鈕1 打開打開“選擇數據選擇數據源源”對話框對話框如果用戶希望可以更改數據行、列間的位置，可如果用戶希望可以更改數據行、列間的位置，可切換至圖表工具切換至圖表工具“設計設計”選項卡，單擊選項卡，單擊“數據數據”組中的組中的“切換行切換行/ /列列”按鈕。按鈕。 2.6 更改圖表布局默認創建的圖表不包含圖表標題，用戶如果想為圖默認創建的圖表不包含圖表標題，用戶如果想為圖表添加標題，可使用更改圖表布局的方式添加。表添加標題，可使用更改圖表布局的方式添加。 更改布局后更改布局后的圖表效果的圖表效果1 單擊此單擊此按

15、鈕按鈕2 單擊此單擊此圖標圖標2.7 設置圖表樣式2.7.1 套用圖表樣式進行圖表類型、數據源和布局設置后，用戶還可以進行圖表類型、數據源和布局設置后，用戶還可以進一步根據需要更改圖表格式，如套用圖表格式。進一步根據需要更改圖表格式，如套用圖表格式。 1 單擊此單擊此按鈕按鈕2 單擊此單擊此圖標圖標套用格式后套用格式后的圖表效果的圖表效果2.7.2 設置標簽文本格式默認創建的圖表中除標題文字外，水平軸標簽、垂直軸默認創建的圖表中除標題文字外，水平軸標簽、垂直軸標簽、圖例等各部分中的文本格式均為標簽、圖例等各部分中的文本格式均為1010號宋體，用戶號宋體，用戶可根據需要重新設置標簽文本格式。可根

16、據需要重新設置標簽文本格式。1 選擇垂選擇垂直標簽直標簽2 切換至切換至該選項卡該選項卡3 輸入輸入7，按按Enter鍵鍵更改文本字更改文本字號后的效果號后的效果更改格式后更改格式后的圖表效果的圖表效果4 切換至切換至該選項卡該選項卡6 單擊該單擊該圖標圖標5 單擊該單擊該圖標圖標8 單擊該單擊該 圖標圖標7 單擊該單擊該按鈕按鈕2.7.3 添加標題用戶可切換至圖表工具的用戶可切換至圖表工具的“布局布局”選項卡，選擇選項卡，選擇“標簽標簽”組中組中“圖表標題圖表標題”、“坐標軸標題坐標軸標題”為圖表添加標題、為圖表添加標題、坐標軸標題。坐標軸標題。2 單擊此單擊此按鈕按鈕豎排的標豎排的標題效果

17、題效果1 選擇要添加選擇要添加標題的圖表標題的圖表4 選擇此選擇此命令命令3 選擇此選擇此命令命令2.7.4 設置數據點及數據系列數據點用于表示各數據間的關系，用戶可為其設置邊框數據點用于表示各數據間的關系，用戶可為其設置邊框顏色、填充色或立體陰影等效果。顏色、填充色或立體陰影等效果。2 切換至切換至該選項卡該選項卡4 單擊該單擊該圖標圖標更改選擇某更改選擇某數據點填充數據點填充色后的效果色后的效果3 單擊該單擊該按鈕按鈕1 選擇圖表中選擇圖表中要更改格式的要更改格式的數據系列數據系列從該面板中從該面板中選擇要使用選擇要使用的樣式的樣式數據點輪廓數據點輪廓顏色更改后顏色更改后的效果的效果6 單

18、擊該單擊該按鈕按鈕5 選擇圖表中選擇圖表中要更改格式的要更改格式的數據點數據點“形狀輪廓形狀輪廓”下拉面板下拉面板7 單擊該單擊該圖標圖標2.7.5 設置背景墻及基底用戶可以根據需要更改圖表背景墻及基底背景顏色。用戶可以根據需要更改圖表背景墻及基底背景顏色。1 單擊該單擊該按鈕按鈕2 選擇此選擇此命令命令設置背景墻設置背景墻設置的背設置的背景墻效果景墻效果3 單擊該單擊該按鈕按鈕4 單擊該單擊該按鈕按鈕5 單擊該單擊該按鈕按鈕6 單擊該單擊該按鈕按鈕設置基底設置基底設置的基設置的基底效果底效果1 單擊該單擊該按鈕按鈕2 單擊該單擊該按鈕按鈕3 單擊該單擊該按鈕按鈕4 單擊該單擊該按鈕按鈕5 單

19、擊該單擊該圖標圖標6 單擊該單擊該按鈕按鈕2.7.6 設置三維效果如果用戶創建的是三維圖表，可以對其進行立體旋轉，如果用戶創建的是三維圖表，可以對其進行立體旋轉，使圖表效果更加鮮明。使圖表效果更加鮮明。2 切換至切換至該選項卡該選項卡更改旋轉值更改旋轉值后的效果后的效果3 單擊該單擊該按鈕按鈕1 選擇更選擇更改立體效果改立體效果的圖表的圖表 4 設置旋設置旋轉值轉值5 單擊該單擊該按鈕按鈕2.8 使用趨勢線與誤差線分析數據趨勢線和誤差線是Excel在進行數據分析時的一種重要手段。趨勢線能夠以圖形的方式顯示某個系列中數據的變化趨勢，而誤差線則能以圖形的方式表示出數據系列中每個數據標記的可能誤差量

20、。2.8 使用趨勢線與誤差線分析數據2.8.1 添加趨勢線與誤差線誤差線是指在數據圖表中的數據與實際值的差值所誤差線是指在數據圖表中的數據與實際值的差值所繪制的曲線。繪制的曲線。1 選擇數選擇數據系列據系列創建的標創建的標準誤差線準誤差線添加誤差線添加誤差線2 單擊此單擊此按鈕按鈕3 選擇此選擇此命令命令趨勢線是用來預測數據的走勢，以便用戶進行數據趨勢線是用來預測數據的走勢，以便用戶進行數據分析，也被稱為回歸分析。分析，也被稱為回歸分析。1 選擇數選擇數據系列據系列創建的線創建的線性趨勢線性趨勢線添加趨勢線添加趨勢線2 單擊此單擊此按鈕按鈕3 選擇此選擇此命令命令2.8.2 設置誤差線與趨勢線

21、用戶可根據需要設置誤差線顯示方向、誤差量、線條顏用戶可根據需要設置誤差線顯示方向、誤差量、線條顏色、線型、陰影等效果。色、線型、陰影等效果。4 切換至切換至該選項卡該選項卡1. 設置誤差線2 單擊此單擊此按鈕按鈕3 選擇此選擇此命令命令1 選擇選擇要進行要進行設置的設置的誤差線誤差線5 選擇該選擇該選項選項6 選擇該選擇該選項選項7 切換至切換至該選項卡該選項卡11 切換至切換至該選項卡該選項卡8 選擇該選擇該選項選項9 單擊此單擊此按鈕按鈕10 單擊該單擊該圖標圖標12 設置設置線條寬度線條寬度13 單擊該單擊該按鈕按鈕更改樣式更改樣式后的標準后的標準誤差線誤差線用戶也可為趨勢線設置線條顏色

22、、線型、陰影等效果，用戶也可為趨勢線設置線條顏色、線型、陰影等效果，除此之外還設置趨勢預測除此之外還設置趨勢預測/ /回歸分析類型、超勢名稱、回歸分析類型、超勢名稱、趨勢預測等。趨勢預測等。2. 設置趨勢線2 單擊此單擊此按鈕按鈕3 選擇此選擇此命令命令1 選擇選擇要進行要進行設置的設置的誤差線誤差線4 切換至切換至該選項卡該選項卡5 選擇該選擇該選項選項6 自定義趨自定義趨勢線名稱勢線名稱7 設置趨設置趨勢周期勢周期8 切換至切換至該選項卡該選項卡9 設置線設置線條粗細條粗細10 單擊此單擊此按鈕按鈕11 選擇該選擇該選項選項12 單擊該單擊該按鈕按鈕設置設置1周期周期指數趨勢線指數趨勢線后

23、的效果后的效果2.9 應用舉例u 組合圖表的制作u 動態圖表2.9.1 組合圖表的制作 在日常工作中，創建比較不同類型數據的圖表是很有用的。例如，你在一個制造公司工作，想要分析前幾個月的銷售單價和每個月的總銷售收入，并且希望知道哪里出現了問題，比如高的銷售單價而低的交易額。當然此問題可以制作兩個不同的圖表，一個繪制每月的銷售單價，一個繪制每月的交易額。但是，將要單獨分析兩個圖表。因此，我們更愿意創建像下面所示的圖表。$0.00 $1,000.00 $2,000.00 $3,000.00 $4,000.00 $5,000.00 $6,000.00 $7,000.00 $8,000.00 0204

24、06080100120一月二月三月四月五月交易總額銷售單價銷售單價交易總額($)2.9.1 組合圖表的制作步驟步驟1: 在工作表中輸入下面的數據月份月份銷售單價銷售單價交易總額交易總額($)($)一月50900二月1003000三月301200四月1047000五月875050六月15075002.9.1 組合圖表的制作步驟步驟2: 選擇數據，在功能區“插入”選項卡中插入柱形圖2.9.1 組合圖表的制作現在，創建了一個帶有兩個系列的柱狀圖，均繪制在相同的坐標軸上，但與我們剛才的意圖不相符，因為在交易額和銷售單價之間的比例不同，我們甚至看不到銷售單價系列。2.9.1 組合圖表的制作下一步將“交易

25、總額”系列的圖表類型改為折線步驟步驟3:選擇要改變為不同類型的系列，本例中為“交易總額”系列。2.9.1 組合圖表的制作步驟步驟4: 改變所選擇系列的圖表類型2.9.1 組合圖表的制作添加第二個坐標軸上面的圖表仍難以分析，因為交易總額的比例是遠大于銷售單價的比例。結果，難以讀取銷售單價，并且柱狀圖也不可分辨。所以，交易總額應該移到第二個坐標軸中，允許坐標軸有不同的比例。步驟步驟5: 選擇想要放置在第二個坐標軸上的數據系列。在本例中，是“交易總額”系列。步驟步驟6: 在“格式”或“布局”選項卡中，在左側“當前所選內容”中，確保下拉框中為“系列交易總額”，然后單擊“設置所選內容格式”。步驟步驟7:

26、 在彈出的“設置數據系列格式”對話框中，打開“系列選項”，單擊“次坐標軸”，然后單擊“關閉”。2.9.1 組合圖表的制作下面介紹的內容將對已完成了組合圖表進行格式設置。步驟步驟8: 將圖例移到底部。單擊功能區中的“圖表布局”選項卡，然后單擊“圖例”，選擇“在底部顯示圖例”。步驟步驟9: 改變第二個坐標軸標簽來顯示貨幣符號。在坐標軸標簽上單擊右鍵，選擇“設置坐標軸格式”。步驟步驟10: 添加坐標軸標題。在功能區“布局”選項卡中，選擇“坐標軸標題”。在下拉菜單中，選擇“主要縱坐標軸標題”，然后選擇“旋轉過的標題”。輸入標題。對“次要縱坐標軸”進行相同的操作。步驟步驟11: 添加圖表標題。在功能區“

27、布局”選項卡中，單擊“圖表標題”，輸入標題名。2.9.2 動態圖表的制作所謂動態圖表就是要實現數據與圖表動態交互。所謂動態圖表就是要實現數據與圖表動態交互。在實際中很有實際意義，就是圖表一直能夠隨著數在實際中很有實際意義，就是圖表一直能夠隨著數據的變化而變化。據的變化而變化。1 1、利用條件篩選制作動態圖、利用條件篩選制作動態圖表表1 利用條件篩選制作動態圖表步驟步驟1 1：首先將工作表變成篩選表，再繪圖。：首先將工作表變成篩選表，再繪圖。 1 利用條件篩選制作動態圖表步驟步驟2 2：進行按條件篩選。：進行按條件篩選。 1 利用條件篩選制作動態圖表步驟步驟2 2：隨意按條件篩選。：隨意按條件篩

28、選。 第3章 創建數據透視表3.1 什么是數據透視表數據透視表是用來從Excel數據列表、關系數據庫文件或OLAP多維數據集等數據源的特定字段中總結信息的分析工具。它是一種交互式報表，可以快速分類匯總、比較大量的數據，可以隨時選擇頁、行和列中的不同元素，以快速查看數據源的不同統計結果，同時還可以隨意顯示和打印出用戶所感興趣區域的明細數據。3.1 什么是數據透視表數據透視表有機的綜合了數據排序、篩選、分類匯總等數據分析的方法的優點，可以方便地調整分類匯總的方式，靈活地以多種不同方式展示數據的特征。一張數據透視表僅靠鼠標移動字段位置，即可以變換出各種類型的報表。應用數據透視表也是解決Excel函數公司速度瓶頸的重要手段之一。3.2 數據透視表的數據源1.Excel數據列表：標題行不能有空白單元格或者合并的單元格；2.外部數據源3.多