Excel基礎(chǔ)到公式運用(個人整理)

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

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

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

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

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

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

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

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

9、定審閱保護工作表加密OK批注中插入圖片：審閱新建批注選中批注外邊框點擊右鍵設(shè)置批注格式顏色與線條填充填充效果圖片打印批注：頁面布局頁面設(shè)置（點右下角小箭頭）工作表打印批注-公式、函數(shù)-公式以”=“號以引號，必須且只能返回值。 函數(shù)按特定算法計算的產(chǎn)生一個或一個組結(jié)果的預(yù)定義的特殊公式。公式組成序號公式說明1=15*3+20*2包含常量運算的公式2=A1*3+A2*2包含單元格引用的公式3=單價*數(shù)量包含名稱的公式4=SUM（A1*3,A2*2）包含函數(shù)的公式在輸入公式的狀態(tài)下，鼠標(biāo)選中其他單元格區(qū)域時，被選區(qū)域?qū)⒆鳛橐米詣虞斎氲焦嚼?。運算符符號說明例-算術(shù)運算符：負(fù)號=8*-5=-40%算

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

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

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

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

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

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

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

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

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

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

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

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

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

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