excel學習總結。。。。來頂一下吧 （轉）

這段時間學習了一些Excel的課程，我將與我工作有關的部分整理如下，希望自己能記住，日後操作時如果忘記也可以查閱一下。這些資料的整理以自己能明白為準繩，所以文字比較簡單，也許對自己才有用呵！1、智能標記學會使用智能標記，實現複製、序列等多種操作。 如果要增加智能標記的填充內容（序列規律），工具-->選項-->自定義序列，自行增加。 如果要進行拖動，但列很長，在選中第一個Cell、出現十字標誌時，用double click就可以填充全列，並同時在當前頁出現智能標記方便下一步操作。 2、數據透視表如果感到數據透視表中的數據太多，還是不能滿足要求（例如需要進一步匯總，將日數據匯總為月、季數據），則可以欄位右鍵選擇分組。注意，分組是可以多種分組同時存在的，例如：月與季分組同時存在。 無論是做排序、分類匯總、透視表等等，一定要保留原始的數據，copy一份數據到另一個工作表中再進行處理。 源數據第一行必須包括標題。 源數據每列只能有一種數據類型。 源數據不能有空列、空行，注意不要有最後一行留有數據匯總。 數據透視表創建之前不能有分類匯總或匯總、不能採用合併單元格。 （1）將多個欄位多次地放入數據區域，可以通過欄位設置，以不同的匯總方式顯示（例如：求和、計數等）。（2）還可以通過欄位設置中的選項，分別設置「匯總方式」（求和、計數等）和「顯示方式」（普通、佔總和百分比等）。（3）最後，還可以通過「數據透視表-->公式-->計算欄位」顯示計算後的數據。注意，只能用欄位列表中的欄位（數值）參與計算，即使是欄位列表中的欄位，如果匯總方式為計數，也會以數值參與計算。 想要最直觀地體會數據透視表，可以用數據透視圖。數據透視圖如果想用網頁來發布，可以Save為網頁，但記住要將「添加交互」點擊。 源數據添加一列，數據透視圖不能自動地增加一列，必須重新做一個圖。 3、條件格式的應用可以應用條件格式用於查錯。如果要快速地使一堆數據中符合某種條件的單元格呈現特定的格式（如字體顏色等），可以先選擇要判斷的數值範圍，再使用「格式-->條件格式-->單元格數值」設置條件。 條件格式的另一種查錯方法是對比兩列數據是否完全相同，條件格式的「單元格數值」選「不等於」，然後滑鼠往下拉，利用智能標記選擇僅填充格式。 條件格式的再一種查錯方法是對比一列長數據中是否有重複的數值，條件格式的「單元格公式」填：Countif()>=2，然後滑鼠往下拉，利用智能標記選擇僅填充格式。 要將一個大的表進行條件格式操作，可以「數據-->列表」，很快地將整個表選上，方便操作。 如果要使多行的數據閱讀起來更便捷，可以隔行應用不同的格式（如隔行為淡灰色），「格式-->條件格式-->公式」進行設置。（1）AND(LEN($D10)>0,MOD(ROW(),2)=1)：邊輸入邊變色（2）或MOD(ROW(),2)=0 ：完成輸入後，選定區域後統一變色（3）MOD函數，是求基偶數，＝0、＝1無所謂 如果要做出一個表是可以根據一定的時限性實時提醒做某事，可以用「單元格格式-->數字-->自定義」：＝B2-TODAY()，實時進行提醒。4、數據審核的方法如果數據有一定的規律性，即是數據是介於一定的數值區域內，就可以結合使用「數據-->有效性-->整數」和「視圖-->工具欄-->公式審核」去大致控制風險。如果是要預防出錯，則要先設有效性，再運用「視圖-->工具欄-->公式審核」，一旦輸錯，馬上就會以不同的格式顯示。如果是要查錯，則在數據範圍內設置「有效性」和「視圖-->工具欄-->公式審核」，出錯的數字就會顯示出來。要注意，如果設置了「有效性」，就算delete了數值，不影響單元格的有效性。除非手動將有效性重新設為「任何值」。 如果數據較少要求核對，可以使用：工具-->語音；輸入時也可以用「同步語音」，防疲勞時出錯。 檢查別人做好的數據、Excel公式：工具-->公式審核-->追蹤引用單元格（從視圖下的工具欄也可以調出來） 可以應用條件格式用於查錯。如果要快速地使一堆數據中符合某種條件的單元格呈現特定的格式（如字體顏色等），可以先選擇要判斷的數值範圍，再使用「格式-->條件格式-->單元格數值」設置條件。 條件格式的另一種查錯方法是對比兩列數據是否完全相同，條件格式的「單元格數值」選「不等於」，然後滑鼠往下拉，利用智能標記選擇僅填充格式。 條件格式的再一種查錯方法是對比一列長數據中是否有重複的數值，條件格式的「單元格公式」填：Countif()>=2，然後滑鼠往下拉，利用智能標記選擇僅填充格式。 如果兩個表要進行簡單的比較，則可以用「窗口-->與XXX並排比較」，可選擇「同步滾動」、水平或垂直並排。 如果工作薄中有很多個關聯的工作表，最終審核時，一個表的改動令其他表也要改；運用「工具-->公式審核-->監視窗口」，可以將改動過的值始終在出現在各個工作表中，提醒及核對要作改變的項目是否已經全部改過了。 5、單元格有效性的應用數據審核。結合「視圖-->工具欄-->公式審核」去大致控制風險。見上文。 如果要創建下拉菜單，有限選擇重複內容填列，例如：是/否/不適用。使用「數據-->有效性-->設置-->序列-->忽略定值&提供下拉菜單箭頭都選-->在來源中將選項都選上-->確定」創建下拉菜單，通過製作數據源並設置有效性序列完成。要注意，工作表中的數據源不能更改不能刪除。 6、Excel函數插入-->函數-->......或者直接按fx按鍵 要使用到函數的地方：（1）單元格計算；（2）條件格式對話框，例如符合偶數行就變格式為黃色；（3）數據有效性的判斷，例如相同發票號就輸入無效；（4）圖表的選項，例如增加數列，增加柱形圖等。？？ SUMIF：一定範圍內符合條件就相加。 LOOKUP：用一個數與一行或一列數據依次進行比較，發現匹配的數值後，將另一組數據中對應的數值提取出來。Lookup只能在一行或一列中查詢，而且該行或列只能以升序排列，只能模糊查詢。 VLOOKUP：Vlookup用對比數與一個「表」進行對比，而不是Lookup函數的某1列或1行。Vlookup可以在表的第一列中查到需要的數據，再以列為單位定位（數列），模糊查詢與精確查詢都可以，其中模糊查詢必須以升序排列。「V」stand for vertical. HLOOKUP: HLookup用對比數與一個「表」進行對比，而不是Lookup函數的某1列或1行。Hlookup可以在表的第一行中查到需要的數據，再以行為單位定位（數行），模糊查詢與精確查詢都可以。「H」stand for horizontal. Vlookup/hlookup+Match：Vlookup/Hlookup可以在表的第一列/行中查到需要的數據，再以一定的條件來定位，模糊查詢與精確查詢都可以。 YEAR:自動計算年齡，例如：在E2中寫出生年月，IF(E2=0,"",YEAR(TODAY())-YEAR(E2)) Countif：幫助做統計動作，也可以用在條件格式及數據有效性中。 7、Excel圖表「視圖-->工具欄-->圖表」調出編輯圖表 如果在工具欄中找不到一些命令，就可以在「工具-->自定義-->命令」，找出需要的命令拖放到相關工具欄上，找開某個工具欄，就能用到自己常用的命令。 用圖表來表達意思，要令讀者一眼就能看出信息與含義。 要將Excel的圖表應用於Word或者PPT，可以使用copy & 選擇性粘貼：（1）圖片：數據都不能改，粘貼點陣圖會比較清楚；（2）對象：粘貼後雙擊後進入Excel環境可以更改，這是嵌入文件，但與源文件沒有關聯；（3）選粘貼鏈接及對象，任何一個文件改動，都會引起另一個文件的變動。 柱形圖：是幾個可比系列數據的對比。 條形圖：適合文字標籤較長時使用。 堆積柱形圖：更適合於顯示總額隨時間變化。 折線圖：繪製連續數據，反映趨勢、走向（注意數據要等時間間距） 餅圖：個體與總體比例的關係，如佔市場份額（注意不能超過6個數據，如果超過6個數據一定要用餅圖，則用複合餅圖）散點圖：兩個變數的關係，這些數據往往與時間相關，要看是否能看出一個結論：除了個別數據，趨勢是上升還是下降？ 面積圖：顯示某個時間段中，總數與數據系列的關係，實際上就是折線圖與堆積柱形圖的結合。 柱形圖與折線圖的合併圖：先繪製兩系列柱形圖，右鍵點擊圖表類型，換為折線圖即可。如果希望柱形圖與折線圖的刻度不同（例如兩種刻度相差很懸殊，則其中一個圖表趨勢不明顯），選中系列-->右鍵-->數據系列格式-->坐標軸-->次坐標軸的數值（Y）軸-->選中次坐標軸，改格式（在刻度里修改最大值等）。稜錐圖/圓柱/圓錐（三維）：比較柱形圖個性化。 股價圖：可以用其來顯示數據的變動過程（walk）數據上升理解為陽線，數據下降理解為陰線，數值分別按「開盤、盤高、盤低、收盤」來排列，只不過盤高、盤低根據不同情況與開盤、收盤分別重合。要在Excel工作簿中移動圖表是很容易的，無論是在原數據工作表中插入或者是重新開一個Tab，單獨放一個圖表，「點擊圖表-->右鍵-->位置-->選擇...」 如果系列的名稱很長的處理方法：（1）在數據源單元格中「ALT+回車」將文字分行，（2）標題傾斜度。 比較直方圖：將各個部門贊成及不贊成的數據直觀顯示出來，可以使用比較直方圖。（1）將系列的重疊比例設為100%；（2）分類軸：刻度線標籤在圖外；（3）坐標軸：刻度分類次序反轉；（注意理解：系列就是不同顏色的柱或餅塊所代表的數列） 如果要突出圖表中的最大值與最小值，可以創建兩列，應用函數：（1）IF(B2=MAX($B$2:$B$13),B2,NA());（2）IF(B2=MIN($B$2:$B$13),B2,NA());（3）結果出來以後，選中兩列數據，複製-->點擊圖表，選擇性粘貼到圖表上來-->添加單元格為「新建系列」，數值（Y）軸在列（或「行」，做圖時看情況，可以作嘗試）-->格式化最大值、最小值系列。如果要做出目標與實際的差距，可以用以下的圖表：第一個圖用兩個系列100%重疊的方法，沒有杯子倒水的感覺；第二個圖用次坐標的方法來做（左邊的次坐標沒有顯示），就有杯子倒水的感覺，通過相關係列調整格式來達到。 8、零星總結如果要將txt文本導入Excel，數據-->導入外部數據-->選中文本文件並導入。 如果有大量的數據要輸入，可以使用「數據-->記錄單」注意，如果在記錄單中刪除一條記錄，這個動作不能撤銷。 在一堆數據中查詢，可以用Find，但Find除了精確查詢外，只能使用通配符，不能指定數值的範圍；用記錄單除了find的功能外，還能查詢「<」或「>」的條件。其實做自動篩選也可以達到目的。 如果需要與Internet上的數據實時刷新，可以用「數據-->導入外部數據-->Web查詢」，並在導入後右鍵設置數據區域屬性為「允許後台刷新」。 如果要實現對單元格或表格的保護，不讓別人修改或者看公式，但又要保留下某些單元格給別人輸入。（a）單元格格式-->保護-->默認/預設為「鎖定」，需要保護的話則要撤銷打鉤（沒鎖定的話，意思就是open，可以給用戶填寫）（b）工具-->保護-->保護工作表-->在「允許用戶進行」中只打鉤「選定未鎖定單元格」 必須先排序，才能做分類匯總（「數據-->分類匯總」）。分類匯總最多只能做一次操作（三個層次），如果想繼續做下一個層次，則可以再做一次分類匯總，記住不要選擇「替換當前分類匯總」。要刪除分類匯總，必須再操作一次「數據-->分類匯總」，選擇「全部刪除」。 9、單元格的引用：（1）相對為A1，滑鼠拖動時可以改變；（2）絕對為$A$1；（3）混合為A$1；（4）三維地址引用：Sheet1!A1 如果計算與顯示不同，例如：0.33+0.33+0.33＝1，修改可以用：工具-->自動更正選項-->錯誤檢查-->公式在區域內不一致。另一個解決的辦法也很簡單，利用Round函數為每個中間值進行四捨五入的操作，格式為：Round(數值，保留的小數位數)，它的作用就是對指定數值進行四捨五入，保留指定小數位數。 除了自動篩選，還可以應用高級篩選功能，篩選完之後自動生成一個表。（1）條件區域必須要包括第一行的表頭項目；（2）可以要求同一列的多個條件，也可以是不同列的條件「並且」及「或者」的關係。 單元格中字元前有空格如果一次性地去掉：數據-->數據分列（分隔符號的「空格」） 如果要實現不同的工作表數據合併成一個工作表，相同的項目相加：數據-->合併計算。注意，除了標題，其他文本是不能合併計算操作的；另外，如果源工作表數據變動，則合併過來的表格要重新再做一遍。 排序、自動篩選可以直接粘貼排序結果，但分類匯總不能直接粘貼匯總結果。先選中要粘貼的數據，點擊「選定可見單元格」按鈕，再copy & paste到另一個工作表。「選定可見單元格」按鈕要通過「工具-->自定義-->命令-->選擇」進行設置。
推薦閱讀：