excel實用技巧(學(xué)習(xí)心得-非常實用)

1、精選優(yōu)質(zhì)文檔-傾情為你奉上Excel實用技巧這是本人學(xué)習(xí)EXCEL函數(shù)時積累的學(xué)習(xí)心得和EXCEL的一些技巧(以函數(shù)為主)，非常實用，對學(xué)習(xí)EXCEL很有幫助。1、 sum求和可以是不連續(xù)的多個區(qū)域,它可以用來求和,中間用英文逗號分隔開，也可以用來作多條件計數(shù)，如sum(A1:A10=60)*(A1:A10H2)+1。其中A2是其中一個條件一般為班級，H2是另一條件，一般為成績。意思是先選出滿足A2條件的人數(shù)，再同時滿足H2的人數(shù)，再加1為該生的班級名次。5、 下拉菜單可用：數(shù)據(jù)有效性序列可以選擇既定區(qū)域，也可輸入文字、符寫等，不過一定要英文逗號隔開。利用數(shù)據(jù)的有效性可以快速判斷一些唯一數(shù)據(jù)是

2、否有效，防止重復(fù)輸入，如身份證號；也可以利用數(shù)據(jù)的有效性建立下拉菜單快速輸入經(jīng)常要輸入的數(shù)據(jù)。利用數(shù)據(jù)的有效性可以實現(xiàn)自動切換中英文輸入法，先選定好列，數(shù)據(jù)有效性輸入法模式模式關(guān)閉（英文）或者有效數(shù)據(jù)IME模式打開。6、 斜線表頭設(shè)計辦法：1、強(qiáng)制換行實現(xiàn)。2、上下標(biāo)實現(xiàn)。3、插入文本框?qū)崿F(xiàn)（改變文本框默認(rèn)屬性）。4、利用WORD制好表頭后再復(fù)制到EXCEL粘貼。excel斜線單元格：先用工具畫出斜線，再插入藝術(shù)字，設(shè)置好一個藝術(shù)字格式后，再按ctrl，同時鼠標(biāo)拖動復(fù)制多個，修改文字后再拖到相應(yīng)位置即可。7、 excel不僅可以替換單元格中的字符，而且也可以替換公式中的字符?？焖龠x取整列、整行

3、的有效數(shù)據(jù)：選中整列、整行最左上單元格，按ctrl+ahift+下箭頭+右箭頭（注意必須是連續(xù)數(shù)據(jù)才能一次性選?。?。Shift+方向鍵可以連續(xù)選取某方面上的單列或單行（可以沒數(shù)據(jù)）8、 可以利用excel鎖定、隱藏和保護(hù)工作表的功能，把公式隱藏和鎖定起來不讓使用者查看和修改。也可以通過選擇性粘貼，把結(jié)果轉(zhuǎn)換為固定的數(shù)值。9、 對于一些復(fù)雜的公式，我們可以利用“公式求值”功能，分段檢查公式的返回結(jié)果，以查找出錯的地方。10、 空行快速刪除：多空行時，利用自動篩選，選空白，刪除即可?？梢酝ㄟ^“選項”功能，為表格設(shè)置密碼，防止別人打開。11、 利用宏可以快速完成既定的枯燥的頻繁的重復(fù)機(jī)械性工作。錄制

4、宏就是把一些操作像用錄音機(jī)一樣錄下來中，到用時只要執(zhí)行這個宏即可。但位置是絕對引用。12、 ceiling為向上舍入取整的函數(shù)（收尾法），如ceiling(4.43,1), 4.43為待舍入的數(shù)值，1為基數(shù)（意思是收尾法后加1）。INF函數(shù)為向下舍入最接近的整數(shù)，即去尾法求整數(shù)，concatenate函數(shù)能合并多個單元格內(nèi)容，并可將若干文字串和某些單元格合并到一起。求優(yōu)生線應(yīng)該是向上舍入，即收尾法，用roundup函數(shù). If函數(shù)表達(dá)式必須為判斷比較語句（如。等）。13、 分?jǐn)?shù)的輸入：如果直接輸入“1/5”，系統(tǒng)會將其變?yōu)椤?月5日”，解決辦法是：先輸入“0”，然后輸入空格，再輸入分?jǐn)?shù)“1/5

5、”。14、 序列“001”的輸入 如果直接輸入“001”，系統(tǒng)會自動判斷001為數(shù)據(jù)1，解決辦法是：首先輸入“”（西文單引號），然后輸入“001”。15、 日期的輸入 如果要輸入“4月5日”，直接輸入“4/5”，再敲回車就行了。如果要輸入當(dāng)前日期，按一下“Ctrl+；”鍵。16、 對多個工作表進(jìn)行同時操作：在底部工作表上右鍵“選定全部工作表”也可用shift或ctrl加鼠標(biāo)選中想要工作表，對選中的工作可同時進(jìn)行頁面設(shè)置、格式設(shè)置、查找替換等。17、 將Excel信息以圖片形式保存。將Excel中的信息以圖片的形式放入Word文檔或其他圖像處理軟件中，實現(xiàn)方法是：先在Excel工作表中選擇需要的

6、所有單元格，在按住“Shift”鍵的同時點擊“編輯”菜單中的“復(fù)制圖片”命令，在隨后彈出的對話框中選中“如屏幕所示”復(fù)選框并確定，最后進(jìn)入該圖片的目標(biāo)工作表或其他文檔中點擊“編輯”菜單中的“粘貼”命令就可以了18、 =if（SUM（A1：A50，SUM（A1：A5），0） 此式就利用了嵌套函數(shù)，意思是，當(dāng)A1至A5的和大于0時，返回這個值，如果小于0，那么就返回0。19、 round函數(shù)：按指定位數(shù)對數(shù)字進(jìn)行四舍五入。如輸入=round(3.158，2）則會出現(xiàn)數(shù)字3.16，即按兩位小數(shù)進(jìn)行四舍五入。rounddown：按指定位數(shù)舍去數(shù)字指定位數(shù)后面的小數(shù)。如輸入=rounddown(3.15

7、8，2）則會出現(xiàn)數(shù)字3.15，將兩位小數(shù)后的數(shù)字全部舍掉了。roundup：按指定位數(shù)向上舍入指定位數(shù)后面的小數(shù)。如輸入=roundup(3.158，2）則會出現(xiàn)數(shù)字3.16，將兩位小數(shù)后的數(shù)字舍上去，除非其后為零。20、 DAVIRAGE函數(shù)返回數(shù)據(jù)庫或數(shù)據(jù)清單中滿足指定條件的列中數(shù)值的平均值. .語法：DAVERAGE(database,field,criteria) 參數(shù)：Database構(gòu)成數(shù)據(jù)表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列（是寫列標(biāo)題），Criteria為一組包含給定條件的單元格區(qū)（可以是多條件，但必須把標(biāo)題和條件的那個條件區(qū)域同時寫進(jìn)去）21、 Vlook

8、up函數(shù)可以從信息表中查找出單個信息制作工資條，單個員工信息，單個學(xué)生成績等。用法：vlookup(查找值，查找區(qū)域，返回列數(shù)，精確查找false還是模糊查找true).第一個參數(shù)為區(qū)域時，結(jié)果不成為內(nèi)存數(shù)組。第二個參數(shù)可以整列引用，一般不影響速度，第二個參數(shù)可以是常量數(shù)組，也就是說可以不是引用區(qū)域，是手工輸入的常量數(shù)組，也可以是引用區(qū)域后用F9查看后的結(jié)果，這樣原來的引用區(qū)域可以刪除而不影響查找效果，相當(dāng)于部分代替了IF函數(shù)）。第四個參數(shù)如果為false可以寫成0或省略，但英文逗號不能省。這個函數(shù)可以實現(xiàn)跨工作薄操作。Vlookup在查找區(qū)域中查不到查找值會返回錯誤提示，消錯的辦法如：if(

9、countif(b:b,e3),vlookup(e3,b:c,2),”),計算B列滿足E3的個數(shù)，如果大于0，則為true,返回第二個參數(shù)的值，否則返回空值，達(dá)到消錯目的。Vlookup函數(shù)只返回第一個查找到的值，不理會不會返回下面的重復(fù)記錄；查找值必須在查找區(qū)域的最左邊存在，且完全一致；查找值與查找區(qū)域里的的相同值單元格格式都必須完全一致。當(dāng)有重復(fù)值記錄時，Lookup查找返回的是最后一個，Vlookup返回的是第一個。如果沒重復(fù)記錄用lookup查找快很多。Sumif也可以部分替代vlookup，條件是查找值必須是唯一的(否則就真是求和了)，且返回值必須為數(shù)值。查找速度也很快。Hlooku

10、p的用法與vlookup用法完全一樣，包括所有特性、使用要求等都一樣。唯獨不同的是Hlookup是水平查找（行查找），而vlookup是垂直查找（列查找），Vlookup模糊查找有點不同，查找區(qū)域第一列必須升序排列，第四個參數(shù)不同，為true,或1或沒有，逗號都不能有。使用不多，if可以部分代替。22、 Match函數(shù)：=match(需查找的值，查找的單元格區(qū)域，查找方式）。查找方式有三種：為1時，查找小于等于需查找的值，用升序排列；為0時，查找等于需查找的值，任意排列；為-1時，查大于等于需查找的值，降序排列。23、 =COLUMN()返回當(dāng)前列號. =Row(單元格或單元格區(qū)域，或省略)。

11、當(dāng)省略時，表示返回函數(shù)所在單元格行號。當(dāng)為區(qū)域時返回第一行行號。24、 today()返回系統(tǒng)當(dāng)前日期。Yeat()、month 、day()分別返回某單元格日期中的年份、月份和日（參數(shù)必須填寫）。Date返回代表特定日期的序列號，用法DATE（year,month,.day）（如果參數(shù)中的年、月、日超過范圍自動向前面進(jìn)位，稱溢出）。Datedif(起始日期，結(jié)束日期，返回類型)，其中返回類型很多，如 “y”,“m”,“d”,“md”，“ym”，“yd”等。 Now()返回系統(tǒng)當(dāng)前時間。Rand()返回按要求的隨機(jī)數(shù)的函數(shù)。subtotal是返回數(shù)據(jù)清單和數(shù)據(jù)庫的分類匯總。25、 把數(shù)值強(qiáng)制變

12、為文本：1、函數(shù)text().2、=要改變的單元格&。文本變成數(shù)值：加0或者乘1或前面加都可以。26、 單元格結(jié)果和公式的快速切換：Ctrl+；關(guān)閉自動計算功能：工具，選項，重新計算里設(shè)置一下即可（對龐大的數(shù)據(jù)源計算非常有效），當(dāng)數(shù)據(jù)修改好后需要計算時只要按下F9就可以全部重算。27、 excel查看函數(shù)計算結(jié)果F9，28、 MATCH($A1,$A$1:$A$9,)表示在$A$1:$A$9查找$A1，并返回其出現(xiàn)的位置。如果沒有重復(fù)數(shù)據(jù)，則與ROW（）返回的相同。如果遇到重復(fù)數(shù)據(jù)則返回第一次出現(xiàn)該數(shù)據(jù)的位置值。與IF、NOW配合使用非常方便的可以找出重復(fù)數(shù)據(jù)。如：=IF(MATCH($A1,

13、$A$1:$A$9,)=ROW(),重復(fù))（此為無標(biāo)題行的情況，如有標(biāo)題行則NOW（）減去行數(shù)。29、 &是連接符，一般連接文本,單元格或單元格區(qū)域或者多條件連接等，如：A10&B10，A2:A10&B2:B10。或者A10&”|”&B10，A2:A10&”|”&B2:B10(多增加的“|”是為了避免兩個關(guān)鍵字合并后產(chǎn)生歧義而進(jìn)行的一種特殊處理。連接文本必須加英文引號。30、 單條件求和常用函數(shù)：countif(條件區(qū)域，條件)。Sumif(條件區(qū)域，條件，求和區(qū)域).averageif(條件區(qū)域，條件，平均區(qū)域)。三個函數(shù)都可以使用通配符（英文狀態(tài)哦）表示0任意數(shù)，？只表示一個字符。在通配符

14、前加可使它變成普通符號，而失去通配符作用。sumif函數(shù)可以整列、整行引用而基本上不影響速度。但他們的1、3個區(qū)域參數(shù)一般不能套用函數(shù)（除間接引用函數(shù)，如offset,index,indirect等），只第2個條件函數(shù)可以套用。單條件求和一般避免合并單元格里求和，避免跨工作表求和31、 多條件求和常用函數(shù)：countifs(條件區(qū)域1，條件1,條件區(qū)域2， 條件2。)。Sumif(求和區(qū)域，條件區(qū)域1，條件1，條件區(qū)域2， 條件2。).，averageifs(平均區(qū)域，條件區(qū)域1，條件1，條件區(qū)域2， 條件2。)可以作為單條件求和函數(shù)使用，可以用比較運(yùn)算符、可以整行整列引用，引用區(qū)域只能套用間

15、接引用函數(shù)，可以用通配符。但與單條件函數(shù)相比也有一些不同：一是多條件求和所有的參數(shù)都不能省，二是引用區(qū)域可以錯位，但引用區(qū)域必須絕對是一樣大,否則出錯。這三個函數(shù)是07版才有。03版主要采用sumproduct(條件區(qū)域1=條件1)*( 條件區(qū)域2=條件2)這是計數(shù)公式。如果求和再乘一個求和區(qū)域。Sum也可以采用相同的公式完成計數(shù)，求和等功能，不過必須是數(shù)組，即三鍵結(jié)束。Sumproduct不支持通配符（可以用其它函數(shù)配合才能使用），可以使用比較運(yùn)算符，可以套用函數(shù)，慎用使用函數(shù)錯位。慎用整行整列引用，原因1是03版不支持，2求和區(qū)域不能包括文本字符（如表頭）或空單元格。3是這個函數(shù)整行整列引

16、用嚴(yán)重影響速度。32、 最好的多條件統(tǒng)計函數(shù)是數(shù)據(jù)庫函數(shù)，全部是D字母打頭函數(shù)，所有數(shù)據(jù)庫函數(shù)使用方法完全一樣能實現(xiàn)幾乎所有的統(tǒng)計工作。參數(shù)必須帶表頭，且表頭必須完全一致。當(dāng)然它必須有輔助單元格，可以使用通配符，可以整行整列引用，可以用比較運(yùn)算符，但要避免使用合并單元格，跨工作表和工作薄33、 日期其實是整數(shù)，時間是小數(shù)。Ctrl+;可以快速輸入系統(tǒng)當(dāng)前日期。Ctrl+shift+;快速輸入當(dāng)前時間?？焖佥斎氘?dāng)年日期：如201224，就只要輸入24即可。Now()和today()均表示當(dāng)前日期。把處在不同單元格中的年、月、日合并用date(年、月、日單元格)。把日期分列用year(日期單元格)

17、，manth(日期)，day(日期)即可,不能用left函數(shù)截取，最好不用連接符，它不會自動判斷錯誤。時間的合并與分列與日期的基本相同，只是換time函數(shù)34、 制作數(shù)據(jù)下拉選項菜單：數(shù)據(jù)有效性序列，在來源中輸入相關(guān)的選項字符，特別注意每個選項一定要用英文速號隔開，否則出錯。在來源中也可以選定已有的相關(guān)選項字符。Weekday(日期或引用日期單元格,2)返回當(dāng)前日期星期幾；weeknum(日期或引用日期單元格,)返回當(dāng)前日期第幾周。Text是文本字符串函數(shù)，但經(jīng)常被當(dāng)作日期函數(shù)使用。因為它的第二個參數(shù)經(jīng)常是單元格格式設(shè)置中的自定義格式。如text(單元格，0.00)表示兩位小數(shù)。Text函數(shù)后

18、的文本數(shù)字變右對齊的數(shù)值非常簡單，就是在text前加二個負(fù)號，或乘1或除1或加0等都可以。從身份證中提取出生日期：如身份號在b3單元格：=if(len(b3)=18,date(mid(b3,7,4),mid(b3,11,2),mid(B3,13,2),date(mid(b3,7,2),mid(b3,9,2),mid(b3,11,2)。有多種更簡單的計算方法。如：text(mid(b3,7,11)-500, 0-00-00, ); text(if(len(b3)=18,mid(b3,7,8),mid(b3,7,6),0-00-00)等。從身份證中提取性別：假如身份證號在b3單元格：=if(len

19、(b3)=18,if(mod(mid(b3,17,1),2),”男”,”女”)，if(mod(right(b3,1),2),”男”,”女”)，或者=if(mod(mid(b3,15.3),2),”男”,”女”)等等。35、 在單元格中，所有左邊齊的均是文本字符，自動右對齊是數(shù)值。Len和lenb都是計算字符串長度的函數(shù)，前者不分全角并角計算字符數(shù)，后者全角等于二個半角計算字符長度。字符串提取函數(shù)：left(b3,4)指在b3單元格的字符中從左邊起提取4個字符(半角)，rightb(b3,4)指從b3單元格的字符中從右邊提取4個字符(半角)；mid(b3,4,6)指從b3單元格字符中從第4個字符起提取6個字符(半角)，Asc把單元格中的所有字