如何做好EXCEL表格

1、品質改變世界三一集團20152015年泵送春節(jié)培訓年泵送春節(jié)培訓如何做好EXCEL表格 2 2目錄第二講 Excel使用技巧第一講 Excel簡介第四講 Excel圖表制作第三講 Excel函數(shù)與公式 3 3目錄第二講 Excel使用技巧第一講 Excel簡介第四講 Excel圖表制作第三講 Excel函數(shù)與公式 4 4Excel簡介一、Excel界面Excel是微軟開發(fā)的一套功能完整、操作簡易的電子計算表軟體，提供豐富的函數(shù)及強大的圖表制作功能，有助于高效地建立與管理資料。功能區(qū)工作表編輯區(qū)功能頁次快速存取工具列顯示比列視圖布局工作表切換標簽 5 5Excel簡介二、認識功能頁次Excel中

2、默認功能操作分為8大頁次，包括文件、開始、插入、頁面布局、公式、數(shù)據(jù)、審閱和視圖。各頁次中收錄相關的功能群組，方便使用者切換、選用。 6 6Excel簡介1、文件功能頁次“文件”頁次，可以執(zhí)行與文件有關的命令，例如新建、打開新文件、選項、權限、保存及傳送文件、打印等。 7 7Excel簡介1、文件功能頁次保護工作簿功能：此工作薄是最終狀態(tài)，只讀，不能編輯此工作薄需要密碼才能打開，打開后，可編輯此工作表某些項不能編輯此工作薄結構或窗口不能編輯可設置編輯權限，需要聯(lián)機 8 8Excel簡介2、開始功能頁次快速設置單元格格式常用字體格式、單元格邊框、拼音等設置對齊方式、文字方向、自動換行、合并單元格

3、等設置粘貼選項、格式刷等設置條件格式、表格套用格式、單元格樣式設置插入/刪除單元格、行、列，單元格大小等設置求和、排序、篩選、查找等設置 9 9Excel簡介3、插入功能頁次插入迷你圖、切片器、超鏈接等插入圖片、形狀、剪切畫、固定格式圖表等繪制柱形圖、折線圖、餅圖等各種類型圖表創(chuàng)建數(shù)據(jù)透視表/圖、插入表格等插入文本框、頁眉頁腳、藝術字、對象等插入公式、符號 1010Excel簡介4、頁面布局功能頁次縮放打印的寬度、高度、縮放比例設置設置頁邊距、紙張方向、紙張大小、打印區(qū)域、背景等設置主題版式、顏色、字體、效果等網(wǎng)格線、標題打印設置圖片的對齊、組合、旋轉等設置 1111Excel簡介5、公式功能

4、頁次創(chuàng)建、編輯、刪除和查找工作薄中使用的名稱，可在公式中使用這些名稱替代單元格引用插入財務、邏輯、文本、數(shù)學等函數(shù)顯示公式、追蹤公式引用的單元格、錯誤檢查、顯示監(jiān)視窗口等指定計算公式的條件、自動或手動等計算 1212Excel簡介6、數(shù)據(jù)功能頁次刷新數(shù)據(jù)，顯示數(shù)據(jù)連接等從其它地方獲取數(shù)據(jù)數(shù)據(jù)排序、篩選、高級篩選等將有規(guī)律的列分成多個單獨的列，刪除重復項、設置數(shù)據(jù)有效性、下拉菜單、模擬分析等創(chuàng)建組、取消組合、分類匯總等 1313Excel簡介7、審閱功能頁次簡繁字體轉換、翻譯等拼寫檢查、信息檢索、同義詞庫等新建批注、批注顯示等保護工作表、共享工作薄、修訂、保護指定單元格等 1414Excel簡介

5、8、視圖功能頁次顯示標尺、編輯欄、網(wǎng)格線、標題視圖選擇、自定義視圖、全屏顯示等顯示比列調整、縮放選定區(qū)域新建窗口、排列窗口、凍結窗格、拆分窗口、并排查看、同步滾動窗口等設置宏操作：查看、錄制等 1515目錄第二講 Excel使用技巧第一講 Excel簡介第四講 Excel圖表制作第三講 Excel函數(shù)與公式 1616Excel使用技巧1、設置Excel文件自動恢復的保存位置當運行Excel時，出現(xiàn)意外情況，停止工作了，在Excel中可設置自動保存，并設置自動保存恢復的文件位置。選擇文件/選項，打開“Excel選項”對話框，然后在左側選擇“保存”選項，在右側界面的“自動恢復文件位置”文本框中可以

6、看到恢復文件的默認保存位置，此時在該文本框中可輸入常用文件夾路徑，保存到常用的文件夾中，方便查找。2、設置工作簿中默認的工作表張數(shù)在工作中經(jīng)常使用的工作表一般在1-2張，其他的工作表用不上，為了讓工作界面看起來更加簡潔，就需要設置工作薄中默認的工作表張數(shù)。Excel工作簿默認的工作表數(shù)量是3張，如在實際操作中用不到這么多或需要更多張的工作表，此時可選擇文件/選項組，打開“Excel選項”對話框，選擇“常規(guī)”選項，在右側的“包含的工作表數(shù)”數(shù)值框中即可設置默認工作表張數(shù)。 1717Excel使用技巧3、快速移動/復制單元格快速移動和復制單元格，能有效快速地替換單元格和復制相同的數(shù)據(jù)，同時也能提高

7、編輯速度。先選定單元格，然后移動鼠標指針到單元格邊框上，顯示 符號時，按下鼠標左鍵并拖動到新位置，然后釋放按鍵即可移動。若要復制單元格，則在釋放鼠標之前按下Ctrl鍵。4、快速修改單元格次序修改單元格次序，能迅速調換不同單元格的次序，在有多類數(shù)據(jù)的表格中運用最為方便。選定單元格，按下Shift鍵，移動鼠標指針至單元格邊緣，顯示 符號時，進行拖放操作。上下拖拉時鼠標在單元格間邊界處會變成一個水平“工”狀標志，左右拖拉時會變成垂直“工”狀標志，釋放鼠標按鈕完成操作后，單元格間的次序即發(fā)生了變化。 1818Excel使用技巧5、凍結單元格首行或首列用戶在處理數(shù)據(jù)量較大的表格時能根據(jù)自己的需要固定表格

8、的某個區(qū)域，方便數(shù)據(jù)的查看與對比。選擇視圖/窗口組，單擊“凍結窗格”按鈕 ，在彈出的下拉列表中選擇“凍結首行”或“凍結首列”選項，可使用戶在滾動工作表其他部分時保持首行或首列可見。6、凍結拆分的窗格為了方便固定和查看所需的單元格數(shù)據(jù)，可以運用凍結拆分單元格來實現(xiàn)。根據(jù)自己的需要選擇凍結的單元格區(qū)域，選擇視圖/窗口組，單擊“凍結窗格”按鈕，在彈出的下拉列表中選擇“凍結拆分窗格”選項，系統(tǒng)自動以被選擇的單元格為分界線，將工作表分為4個區(qū)域。 1919Excel使用技巧7、不打印出表格的背景顏色有時在打印表格時會連同表格的背景顏色一起打印出來，這時我們就可以通過設置不打印表格背景顏色。選擇頁面布局/

9、工作表選項組，單擊右下角的 按鈕，打開“頁面設置”對話框，并選擇“工作表”選項卡。選中“打印”欄中的 復選框，單擊確認即可。在“單色打印”下，字體顏色全部使用黑色打印，單元格底紋填充顏色不會被打印出來。8、將工作表的行號和列標也打印出來有時在打印工作表時需要將行號和列標也顯示出來，我們就可以設置將行號和列標一并打印出來。打開“頁面設置”對話框，選擇“工作表”選項卡，在“打印”欄選中 復選框，然后單擊確定按鈕，再進行打印時便可打印輸出行號和列標。 2020Excel使用技巧9、讓粘貼數(shù)據(jù)隨源數(shù)據(jù)自動更新在編輯表格數(shù)據(jù)時，有時修改一些統(tǒng)一性數(shù)據(jù)時，全部修改非常麻煩，這時只需利用“粘貼連接”功能來粘

10、貼修改數(shù)據(jù)。復制需要的數(shù)據(jù)，選擇需要粘貼的單元格后單擊鼠標右鍵，選擇選擇性粘貼/粘貼鏈接命令。可適用于在Excel之間、外部文件中的數(shù)據(jù)粘貼，使復制的數(shù)據(jù)與數(shù)據(jù)源之間保持關聯(lián)粘貼，讓粘貼的數(shù)據(jù)始終跟隨數(shù)據(jù)源進行自動更新。10、用下拉列表快速輸入數(shù)據(jù)如果某些單元格區(qū)域中要輸入的數(shù)據(jù)很有規(guī)律，這時可以設置下拉列表實現(xiàn)輸入操作。選擇單元格，選擇數(shù)據(jù)/數(shù)據(jù)工具組，單擊“數(shù)據(jù)有效性”按鈕 ，在彈出的下拉列表中選擇“數(shù)據(jù)有效性/設置”，在“有效性條件”的“允許”下拉列表框中選擇“序列”，在“來源”文本框中輸入如“泵車、拖泵、車載泵”等，單擊確認即可。 2121Excel使用技巧11、繪制平直直線在繪制平直

11、直線時，可以運用快捷鍵來快速繪制。在Excel繪制直線時，按Shift鍵，則繪制出來的直線就是平直的。另外，按下Shift鍵繪制矩形即變?yōu)檎叫?、繪制橢圓形即變?yōu)閳A形。12、將圖表另存為圖片文件將圖表另存為文件，可以使圖表能應用更多的格式進行編輯。打開“另存為”對話框，在“保存類型”下拉列表框中選擇“網(wǎng)頁（*.htm，*.html）”選項，然后單擊保存，之后在保存位置打開工作簿生成的文件夾，打開該文件夾（后綴名為.files），在其中便可找到圖表對應的圖片（后綴名為.png）。 2222Excel使用技巧常用快捷鍵說明Ctrl+Shift+(取消隱藏選定范圍內所有隱藏的行。Ctrl+Shift

12、+&將外框應用于選定單元格。Ctrl+Shift_從選定單元格刪除外框。Ctrl+Shift+:輸入當前時間。Ctrl+;輸入當前日期Ctrl+1顯示“單元格格式”對話框。Ctrl+Y重復上一個命令或操作（如有可能）。Ctrl+Z使用“撤消”命令來撤消上一個命令或刪除最后鍵入的內容。Alt+Enter 可在同一單元格中另起一個新行。Ctrl+9隱藏選定的行。Ctrl+0隱藏選定的列。Ctrl+Alt+V 顯示“選擇性粘貼”對話框。Ctrl+N新建工作薄Ctrl+O打開工作薄 2323Excel使用技巧案例1 工資條打印 具體的操作方法如下： Step1：添加“輔助列”列。在單元格E2-

13、E4中輸入等差序列1、3、5；Step2：復制表頭到單元格區(qū)域A5：D7中，并在單元格E5-E7中輸入等差序列2、4、6；Step3：在E8-E10中輸入等差序列3、5、7；Step4：將“輔助列”按降序排列即可。 2424目錄第二講 Excel使用技巧第一講 Excel簡介第四講 Excel圖表制作第三講 Excel函數(shù)與公式 2525Excel函數(shù)與公式一、Excel常用函數(shù)函數(shù)：是預定義的公式，通過使用一些參數(shù)的特定值來按特定的順序和結構執(zhí)行計算。函數(shù)的使用方式=函數(shù)名稱（參數(shù)1，參數(shù)2，參數(shù)3)函數(shù)的嵌套特性一個函數(shù)可以引用其他函數(shù)的結果作為參數(shù)Excel最多支持七層嵌套函數(shù)常用函數(shù)有

14、：IFANDORMID LEFT RIGHT財務函數(shù) 2626Excel函數(shù)與公式1、IF函數(shù)如果指定條件的計算結果為 TRUE，IF 函數(shù)將返回某個值；如果該條件的計算結果為 FALSE，則返回另一個值。例如，如果 A1大于10，公式 =IF(A110,大于10,不大于10) 將返回“大于10”，如果 A1小于等于10，則返回“不大于10”。IF(logical_test, value_if_true, value_if_false) 條件為真 條件為假 2727Excel函數(shù)與公式2、AND/OR函數(shù)AND函數(shù)所有參數(shù)的計算結果為 TRUE 時，返回 TRUE；只要有一個參數(shù)的計算結果為

15、FALSE，即返回 FALSE。AND(logical1, logical2, .)OR函數(shù)在其參數(shù)組中，任何一個參數(shù)邏輯值為 TRUE，即返回 TRUE；任何一個參數(shù)的邏輯值為 FALSE，即返回 FALSE。OR(logical1, logical2, .) 2828Excel函數(shù)與公式3、MID LEFT RIGHT函數(shù)MID 返回文本字符串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MID(text, start_num, num_chars)LEFT 根據(jù)所指定的字符數(shù)，返回字符串中第一個字符或前幾個字符。LEFT(text, num_chars)RIGHT 根據(jù)所指定的字符

16、數(shù)返回文本字符串中最后一個或多個字符。RIGHT(text,num_chars) 2929Excel函數(shù)與公式4、財務函數(shù)PMT：基于固定利率及等額分期付款方式，返回貸款的每期付款額。FV：基于固定利率及等額分期付款方式，返回某項投資的未來值。PV：返回投資的現(xiàn)值?，F(xiàn)值為一系列未來付款的當前值的累積和。NEPR：基于固定利率及等額分期付款方式，返回某項投資的總期數(shù)。RATE：返回年金的各期利率。TYPE ：數(shù)字 0 或 1，用以指定各期的付款時間是在期初還是期末。PMT(rate, nper, pv, fv, type) 3030Excel函數(shù)與公式二、Excel常用公式公式：是可以進行以下操

17、作的方程式：執(zhí)行計算、返回信息、操作其他單元格的內容、測試條件等等。公式始終以等號 (=) 開頭。工作表中可輸入的公式類型：=5+2*3 將 5 加到 2 與 3 的乘積中。=A1+A2+A3 將單元格 A1、A2 和 A3 中的值相加。=SQRT(A1) 使用 SQRT 函數(shù)返回 A1 中值的平方根。=TODAY() 返回當前日期。=UPPER(hello) 使用 UPPER 工作表函數(shù)將文本“hello”轉換為“HELLO”。=IF(A10) 測試單元格 A1，確定它是否包含大于 0 值。 3131Excel函數(shù)與公式二、Excel常用公式公式的組成：包含下列部分內容或全部內容：函數(shù) 、引

18、用和常量 。函數(shù)：PI() 函數(shù)返回值 PI：3.14159.引用：A2 返回單元格 A2 中的值。常量：直接輸入公式中的數(shù)字或文本值，如 2。運算符：（脫字號）運算符運表示數(shù)字的乘方，而 *（星號）運算符表示數(shù)字的乘積。 3232目錄第二講 Excel使用技巧第一講 Excel簡介第四講 Excel圖表制作第三講 Excel函數(shù)與公式 3333Excel圖表制作一、Excel常用圖表圖表用于以圖形形式顯示數(shù)值數(shù)據(jù)系列，使您更容易理解大量數(shù)據(jù)以及不同數(shù)據(jù)系列之間的關系。 3434Excel圖表制作一、Excel常用圖表（1）柱狀圖使用最普遍的圖表類型，用于顯示一段時間內的數(shù)據(jù)變化或說明各項之間

19、的比較情況。例如各品牌泵車在第一季每個月的銷量： 3535Excel圖表制作一、Excel常用圖表（2）折線圖可以顯示隨時間而變化的連續(xù)數(shù)據(jù)（根據(jù)常用比例設置），因此非常適用于顯示在相等時間間隔下數(shù)據(jù)的趨勢。例如各品牌泵車在第一季每個月的銷量走勢： 3636Excel圖表制作一、Excel常用圖表（3）餅圖只能有一組數(shù)列數(shù)據(jù)，每個數(shù)據(jù)項都有唯一的色彩，餅圖適合用來表現(xiàn)各個項目在全體數(shù)據(jù)中所占的比率。例如各品牌泵車在第一季每個月的銷量占比： 3737Excel圖表制作一、Excel常用圖表（4）條形圖可以顯示每個項目之間的比較情形，Y軸表示類別項目，X軸表示值，條形圖主要是強調各項目之間的比較，

20、不強調時間。例如各品牌泵車在1月的銷量排名： 3838Excel圖表制作一、Excel常用圖表（5）面積圖面積圖強調數(shù)量隨時間而變化的程度，也可用于引起人們對總值趨勢的注意。例如各品牌泵車在第一季銷量分布： 3939Excel圖表制作一、Excel常用圖表（6）散點圖顯示若干數(shù)據(jù)系列中各數(shù)值之間的關系，或者將兩組數(shù)字繪制為 xy 坐標的一個系列。例如冰熱兩種飲料會隨著氣溫變化而影響銷售量，氣溫越高，冷飲的銷量越好： 4040Excel圖表制作一、Excel常用圖表（7）股價圖通常用來顯示股價的波動。創(chuàng)建一個簡單的成交量-開盤-盤高-盤低-收盤股價圖，應根據(jù)按成交量、開盤、盤高、盤低和收盤次序輸

21、入的列標題來排列數(shù)據(jù)。 4141Excel圖表制作一、Excel常用圖表（8）曲面圖主要是以平面顯示數(shù)據(jù)的變化趨勢，用不同的顏色和圖案表示在同一數(shù)據(jù)范圍內的區(qū)域，當類別和數(shù)據(jù)系列都是數(shù)值時，可以使用曲面圖。例如 4242Excel圖表制作一、Excel常用圖表（9）圓環(huán)圖像餅圖一樣，圓環(huán)圖顯示各個部分與整體之間的關系，但是它可以包含多個數(shù)據(jù)系列數(shù)據(jù)系列。例如各品牌泵車在第一季每月銷量占比： 4343Excel圖表制作一、Excel常用圖表（10）氣泡圖和散布圖類似，不過氣泡圖是比較3組數(shù)值，水平軸的數(shù)值（X軸）在第一欄中，而對應的垂直軸數(shù)值（Y軸）及氣泡大小值則列在相鄰的欄中。例如各米段本車銷售情況，X軸代表臂架長度，Y軸代表銷量，而氣泡的大小則是市場份額。 4444Excel圖表制作一、Excel常用圖表（11）雷達圖可以用來做多個維度數(shù)據(jù)的比較。例如，我們可以雷達圖來了解泵車各廠家的優(yōu)勢和劣勢。 4545Excel圖表制作二、Excel數(shù)據(jù)透視表一種可以動態(tài)地改變版面布置，以便按照不同方式分析數(shù)據(jù)的動態(tài)表格。對于匯總、分析、瀏覽和呈現(xiàn)匯總數(shù)據(jù)非常有用。源數(shù)據(jù)一定是要規(guī)則清