Excel基礎到公式運用(個人整理)參考模板

1、Excel-基本-加快啟動速度：在桌面Excel圖標上點右鍵屬性，在目標路徑后加一個空格再加上/e加載項：文件選項加載項管理轉到切換工作表：Crtl+PageUP/PageDown選中連續(xù)的工作表：單擊第一個工作表，按住Shift鍵再按最后一張工作表界面中列標顯示為數字而不是英文則是使用了R1C1引用樣式，要恢復為英文狀態(tài)可點擊：文件選項公式，取消勾選R1C1引用樣式修改網格線顏色：文件選項高級網格線顏色最大行1048576，最大列16384，最大單元格。Ctrl+右方向鍵可定位到單元格所在行向右連續(xù)非空值的最后一行 Ctrl+下方向鍵可定位到單元格所在列向下連續(xù)非空

2、值的最后一行Ctrl+Home 鍵可到達表格定義的左上角的單元格， Ctrl+End 鍵可以到達表格定義的右下角的單元格。在名稱框中輸入字母 + 數字，可快速定位到此單元格Shift+空格鍵：快速選定單元格所在的行Ctrl+空格鍵：快速選定單元格所在的列（避免與切換中文輸入法快捷鍵沖突）行高單位是磅，1磅約為0.35278mm，最大限制為409磅，即144.286mm。列寬的單位是字符，列寬設置數字范圍為0255，最小列寬為0（等于隱藏）。列寬的寬度約等于該列單元格中可以顯示容納數字字符的個數，標準列寬為8.38。Ctrl+Backspace：快速定位活動單元格所在的位置并顯示在當前窗口中。（

3、可用Ctrl+G/F5定位）Excel中可以表示和存儲的數字最大精確到15位有效數字，對于超出的會自動將15位以后的數字變?yōu)?，需輸入超過15位數字或需顯示以0開頭的值可在前面加單引號“ ”，或者將單元格設成文本格式。（Ctrl+1 鍵盤左側數字1）對于一些很大或很小的數值，Excel會自動以科學記數法來表示，例1.2345E+14表示1.2345×1014。Excel中日期系統默認為1900年日期系統，即以1900年1月1日作為序列值的基準日，運算時按360天一年計算。日期系統的序列值是一個整數數值，一天的數值單位就是1，1小時表示為1/24天，1分鐘表示為1/（24×6

4、0）天。邏輯值是比較特殊的一類參數，它只有TRUE（真）和FALSE（假）兩種類型，Excel中規(guī)定：數字<字母<邏輯值 （比較大小）錯誤值：1、# 出現原因：單元格所含的數字、日期或時間比單元格寬，或日期時間公式產生了負值。 2、#VALUE！ 出現原因：1）在需要數字或邏輯值時輸入了文本，Excel不能將文本轉換為正確的數據類型。 2）將單元格引用、公式或函數作為數組常量輸入。 3）賦予需要單一數值的運算符或函數一個數值區(qū)域。 3、#DIV/0！ 出現原因：公式被零除時或公式中包含的除數零。 4、#NAME？ 出現原因：1）刪除了公式中使用的名稱，或者使用了不存在的名稱 2）名

5、稱拼寫錯誤 3）在公式中輸入文本時沒有使用雙引號 4）在區(qū)域的引用中缺少冒號 5、#N/A 出現原因：函數或公式中沒有可用數值 6、#REF！ 出現原因：單元格引用無效 7、#NUM！ 出現原因：公式或函數中某個數字有問題（由公式產生的數字太大或太小，Excel不能表示） 8、#NULL！ 出現原因：為兩個并不相交的區(qū)域指定交叉點公式：通常以“=”開頭 “+”、“”號開關也可使用Excel識別其內容為公式 Ctrl+ 可以在公式與值的顯示方式間切換輸入數據：當用戶輸入數據時在工作窗口底部狀態(tài)欄左側會顯示“輸入”字樣，原有編輯欄左邊會出現“×”按鈕，單擊“×”表示取消輸入，單

6、擊“”表示確定輸入，單擊“”時和按Enter有相同的效果，但按Enter鍵后會自動跳到下一單元格，按 則不會。 Excel中默認按Enter鍵是向下激活單元格，也可設置成其它，方法：文件選項高級編輯選項方向編輯單元格內容：雙擊或者按F2，也可激活目標單元格后在工作窗口編輯欄編輯 可以在鍵盤上按Insert鍵切換“插入”或“改寫”模式日期輸入：系統默認將029之間的數字識別為2000年2029年，將3099之間的數字識別為1930年1999年。 用分隔符“ . ”來輸入日期不能進行運算。數據輸入技巧：強制換行Alt+Enter 在多個單元格輸入相同的數據：同時選中要輸入的單元格，輸入數據，在輸入

7、結束時按Ctrl+Enter 分數輸入：整數+空格+分數 可在編輯欄查看分數值 上下標：選中要設置的上下標值，Ctrl+1設置單元格格式/字體 在列表中選擇：當要在目標單元格中輸入此單元格上方的某一單元格內的內容時可按Alt+下方向鍵或點鼠標右鍵選從下拉列表中選擇 自動填充：輸入數據用填充柄下拉（順序填充，可在填充完后點右下角的填充菜單進行類型選擇） 雙擊填充柄按住Ctrl鍵下拉為復制填充 Ctrl+D向下填充 Ctrl+R向右填充（需框選出填充區(qū)域后使用） 使用填充菜單：開始編輯填充序列1 / 18粘貼：粘貼選項，復制、粘貼后在右下角出現的粘貼選項 選擇性粘貼，復制后在粘貼前點擊鼠標右鍵選擇

8、的選擇性粘貼選項查找/替換：Ctrl+F/Ctrl+H（可設置格式） 查找通配符應用：Excel中支持兩個通配符 * 和 ？ 例：要查找以E開頭以L結束可用E*L 以EX開頭L結束的五個字母單詞可用EX?L隱藏單元格數據：選中要隱藏的單元格點擊右鍵設置單元格格式數字自定義在類型處打上三個半角分號（到這一步雖隱藏了單元格表面顯示的內容但在編輯欄還是可以看到它本身的內容）再點右鍵設置單元格格式保護隱藏審閱保護工作表加密（即可完全隱藏單元格的內容）禁止編輯表格中部分區(qū)域：單擊行號和列標交叉處的按鈕（全選整張工作表Ctrl+A）Ctrl+1保護取消勾選鎖定OK選中要禁止編輯的區(qū)域Ctrl+1保護先勾選

9、隱藏再勾選鎖定審閱保護工作表加密OK批注中插入圖片：審閱新建批注選中批注外邊框點擊右鍵設置批注格式顏色與線條填充填充效果圖片打印批注：頁面布局頁面設置（點右下角小箭頭）工作表打印批注-公式、函數-公式以”=“號以引號，必須且只能返回值。 函數按特定算法計算的產生一個或一個組結果的預定義的特殊公式。公式組成序號公式說明1=15*3+20*2包含常量運算的公式2=A1*3+A2*2包含單元格引用的公式3=單價*數量包含名稱的公式4=SUM（A1*3,A2*2）包含函數的公式在輸入公式的狀態(tài)下，鼠標選中其他單元格區(qū)域時，被選區(qū)域將作為引用自動輸入到公式里。運算符符號說明例-算術運算符：負號=8*-5

10、=-40%算術運算符：百分號=60*5%=3算術運算符：乘冪=32=9 =16（1/2）=4*和/算術運算符：乘和除=3*2/4=1.5+和-算術運算符：加和減=3+2+5=0= 、<>、>、<、>=、<=比較運算符（比較大?。旱扔?、不等于、大于、小于、大于等于、小于等于=（A1=A2）判斷A1與A2相等=（B1<>”B2”）判斷B1不等于B2=（C1>=5）判斷C1大于等于5&文本運算符（將文本字符或字符串進行連接合并）：連接文本=”愛”&”點點”返回結果”愛點點“:區(qū)域運算符：冒號=SUM（A1：C10） 引用冒號兩邊

11、所引用的單元格左上角和右下角之間的所有單元格組成的矩形區(qū)域?？崭窠徊孢\算符：單個空格=SUM（A1:B5 A4:D9）引用A1:B5與A4:D9的交叉區(qū)域，公式相當于=SUM（A4:B5），聯合運算符：逗號=SUM（A1,(A1:A10,C1:C10)） 第2參數引用A1:A10和C1:C10兩個不連續(xù)的單元格區(qū)域。運算符優(yōu)先順序序號公式說明0（ ）小括號（優(yōu)先級高于其他運算符，如果公式內有多組括號進行嵌套，計算順序則由最內層括號逐級向外進行運算）1： 空格 ，冒號、單個空格、逗號2-負號3%百分比4乘冪5*和/乘和除（注意區(qū)別數學中的×和÷）6+和-加和減7&連接

12、文本8= 、<、>、<=、>=、<>比較兩個值（注意區(qū)別數學中的、）相對引用：當復制公式到其他單元格時，Excel保持從屬單元格與引用單元格的相對位置不變。 例=A1 向下復制為=A2、=A3、=A4-絕對引用：當復制公式到其他單元格時，Excel保持公式所引用的單元格絕對位置不變。 例=$A$1向下復制始終=$A$1，不隨公式復制改變混合引用：當復制公式到其他單元格時，Excel僅保持所引用單元格的行或行方向之一的絕對位置不變，而另一方向位置發(fā)生變化。 例：=$A5 向下復制為=$A6、=$A7、=$A8 向右復制始終=$A5 =A$5 向右復制為=B$5

13、、=C$5、=D$5 向下復制始終=A$5混合引用實例：跨表引用：在公式中引用其他工作表的單元格區(qū)域。 表示方式：工作表名+半角感嘆號+引用區(qū)域方法：在公式編輯狀態(tài)下，通過鼠標單擊相應的工作表標簽，然后選取相應的單元格區(qū)域。例：要在工作表Sheet1中的A1單元格中引用工作表Sheet2中的A1:B10區(qū)域，則在Sheet1表中A1單元格輸入=SUM（后單擊Sheet2工作表標簽，然后選取A1:B10區(qū)域，按回車結束。 則公式將顯示為=SUM(Sheet2！A1:B10)引用連續(xù)多工作表相同區(qū)域：當跨表引用多個相鄰的工作表中相同的單元格區(qū)域進行匯總時，可以使用三維引用進行計算而無須逐個工作表對

14、單元格區(qū)域進行引用。它的表示方式為：按工作表排列順序，使用冒號將起始工作表和終止工作表名進行連接，作為跨表引用的工作表表名。 例：工作薄內有連續(xù)工作表1、2、3、4，要在另一匯總表內計算前4張表里的同一區(qū)域（假設為B5:E10）數值，在需執(zhí)行公式的單元格內輸入=SUM（ 然后單擊1工作表標簽，按住Shift鍵后再單擊4工作表標簽，然后選取引用區(qū)域，按Enter結束輸入。顯示公式為=SUM('1:4 '! B5:E10) 如果匯總表在表2與表3之間，則公式為=SUM('1:2 '! B5:E10, '3:4 '! B5:E10)套用表格格式：開始套

15、用表格格式 方法：選擇某一區(qū)域開始套用表格格式（勾選表包含標題），可在設計選項下進行其他操作，例如勾選匯總行，會在表下方自動出現匯總，在下拉列表中有各項匯總項。-函數-函數表示：=號開頭、函數名稱、左括號、以半角逗號相間隔的參數、右括號。 公式中允許多個函數或計算式，通過運算符連接 函數的參數可以由數值、日期、文本等元素組成，可以使用常量、數組、單元格引用或其他函數。當使用函數作為另一個函數的參數時，稱為嵌套函數。有的函數沒有參數或者不需要參數，如NOW（返回日期時間格式的當前日期和時間：=NOW() ）、TODAY（返回當前日期：=TODAY()）、RAND（返回大于或等于0且小于1的平均分

16、布隨機數：=RAND()）、PI（圓周率：=PI()）等。Alt + = ：插入求和函數 Shift+F3：打開插入函數對話框生成26個大寫字母序列：=CHAR(64+COLUMN() 小寫字母序列：=CHAR(96+COLUMN()合并并換行：先點自動換行按鈕，再輸入公式 =G7&CHAR(10)&H7 10是自動換行符的ANSI編碼分離姓名和數字 LEFT：從一個文本字符串的第一個字符開始返回指定個數的字符 LENB：返回文本中包含的字符數 LEN：返回文本字符串中的字符個數 RIGHT：從一個文本字符串的最后一個字符開始返回指定個數 的字符。LENB函數按照每個雙字節(jié)字符

17、（漢字姓名）為2個長度計算，單字節(jié)字符按1個長度計算，因此LENB(B4)-LEN(B4)可以求得單元格中雙字節(jié)字符的個數。2*LEN(B4)-LENB(B4)則可以求得單元格中單字節(jié)字符的個數。 LEFT=左 RIGHT=右計算職工人數 G列數據中帶有“人”字，要在黃色填充部分求人數的和可以用SUBSTITUTE函數將“人”字刪除后再求和。 SUBSTITUTE函數獲得的結果為文本型數字，所以要使用減負（- -）運算將基轉換為數值，以便SUMPRODUCT函數求和。也可用公式 =(DAYS360(H66,TODAY()/360 來計算年齡 =IF(MOD(RIGHT(LEFT(B3,17),

18、2),"男","女")MOD：取余函數（返回兩數相除的余數）=MOD（34,9） 結果為7 余數結果符號與除數一致 被除數是除數的整數倍時結果為零判斷奇偶性： =IF（MOD（15，2）>0，”奇數”,”偶數”） 解釋：如果15除以2的余數大于0，則返回奇數，否則就返回偶數IF（條件函數）：判斷是否滿足某個條件，如果滿足就返回這個值，如果不滿足則返回另一個值。INT：將數值向下取最接近的整數 例：=INT（15.35） 結果為15TRUNC：將數字截為整數或保留指定位數的小數 例：=TRUNC（-8.72） 結果為-8 =TRUNC（9.72） 結

19、果為9ROUNDUP：無條件向上舍入數字 例： =ROUNDUP（23.416,2） 結果為23.42 （和四舍五入類似）ROUNDOWN：無條件向下舍入數字 例： =ROUNDUP（23.416,2） 結果為23.41 （直接舍去） 可用TRUNC函數代替使用。CEILING：向上舍入 例：=CEILING(123.456,0.2) 結果為123.6 FLOOR：向下舍去 例：=FLOOR(123.456,0.2) 結果為123.4 利用取舍函數控制單元格錄入數值：數據有效性在公式處輸入=C3=FLOOR(C3,0.01) C3為可變更單元格。ROUND：按指定的位數對數值進行四舍五入 例：

20、=ROUND(123.456,2) 結果為：123.46日期和時間函數：允許輸入日期區(qū)間1900-1-1至9999-12-31 當年份缺省時默認為1900年，當月份缺省為0時表示上一年的12月，當日期缺省為0時表示上一個月的月末天數。常用日期函數函數名稱功能作用TODAY和NOW用于生成當前日期和時間DATE根據指定的年份、月份和日期數返回具體的日期值YEAR、MONTH和DAY用于從日期數據中提取年份、月份和日期值YEARFRAC用于計算兩個日期序列相差的年數，結果以實數顯示EDATE根據指定月份數返回指定日期之前或之后的日期EOMONTH從任意一個日期返回指定月份數的之前或之后的月末日期W

21、ORKDAY或WORKDAY.INTL（可以隨意指定周工作日）根據源日期按指定工作日天數返回之前或之后的日期NETWORKDAYS或NETWORKDAYS.INTL（可以隨意指定周工作日）計算兩個日期差工作日天數的函數基本日期函數：快速生成當前日期和時間 Ctrl+ ；和Ctrl+Shift+ ； 這樣生成的日期和時間格式是固定不變的 =Today()和=Now() 生成的日期會自動更新 =DATE(A2,B2+1,C2) 類似都OK從特定日期提取年月日：年份=YEAR(單元格名稱) 月份=MONTH(單元格名稱) 日期=DAY(單元格名稱)如=DATE(YEAR(TODAY()+1,10,1

22、)會返回明年的國慶節(jié)日期日期之間的運算：+或- 1)計算相差天數：日期-日期=數值 2）計算之前的日期：日期-數值=日期 3）計算之后的日期：日期+數值=日期 TODAY=2014年6月29日 判斷一個日期所在的季度：公式利用了DAY (A2)返回的日期數是A2所在月份從第1日起的第幾天的來進行處理，當日期減去天數后得出當前月第0天的日期，即為上個月月末日期，再通過DAY函數得出天數。 可根據此公式算出當前日期的下一個星期天的日期。工作日函數：NETWORKDAYS：返回參數start_date 和end date之間完整的工作日數值。 工作日不包括周末和專門指定的假期。 可以使用函數 NET

23、WORKDAYS，根據某一特定時期內雇員的工作天數，計算其應計的報酬。提示:若要使用參數來指明周末的日期和天數,從而計算兩個日期間的全部工作日數,請使用NETWORKDAYS.INTL函數.時間的計算：Time：用于按指定數字生成具體的時間 hour：用于提取時間系列值中的小時數Minute：用于提取時間系列值中的分鐘數 second：用于提取時間系列值中的秒數計算下班時間：例 上班時間為2014-7-6 8：30 計算工作8小時后的時間：統計與求和 匯總大于1000的點點的合計類似匯總指定商品的銷量 計算退休日期： 計算工齡（年齡）： 小數位數開始時期 結束日期 開始-條件格式利用VLOOKUP函數查詢并匯總指定產品月銷量和1、選中區(qū)域創(chuàng)建數據表：表1 2、輸入內容：3、制作查詢表：4、輸入數組公式：公式在輸入結束后，按Ctrl +