Excel使用技巧2017.9.ppt

1、1.Excel的使用技巧,快.速填入相同的內(nèi)容 快速復制上一單元格 求字符串中某字符出現(xiàn)的次數(shù) 如何在EXCEL中設置單元格編輯權限（保護部分單元格） Excel中當某一單元格符合特定條件，如何在另一單元格顯示特定的顏色 EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復錄入 去掉默認的表格線 增加工作簿的頁數(shù),相對地址、絕對地址、混合地址 數(shù)據(jù)的輸入 輸入相同數(shù)據(jù)的兩種情況 數(shù)據(jù)的行列對調(diào) 跨表操作數(shù)據(jù) 兩次選定單元格 兩列數(shù)據(jù)巧合并 用快捷鍵設置單元格文本 自動調(diào)整為合適的列寬 繪制斜線表頭,Excel做成績表時自動實現(xiàn)同分同名次 按Shift鍵 把Excel單元格區(qū)域轉(zhuǎn)換為圖片 制作漂亮的工資

2、條 圖表編輯操作,1. 快速填入相同內(nèi)容,選中多個單元格后，輸入字符，然后按Ctrl+Enter組合鍵，即可在選中的每個單元格中填入上述相同的字符。,2. 快速復制上一單元格,選中下面的單元格，按Ctrl+ （為西文的單引號）組合鍵，即可將上一單元格的內(nèi)容快速復制下來。,3. 求字符串中某字符出現(xiàn)的次數(shù),例如：求A1單元格中字符“a”出現(xiàn)的次數(shù)： =LEN(A1)-LEN(SUBSTITUTE(A1,“a” ,“ ”) LEN(text)：返回文本字符串的個數(shù) SUBSTITUTE（text,old_text,new_text,instance_num）: 將字符串中的部分字符串以新字符串替換

3、。,4. 如何在EXCEL中設置單元格編輯權限（保護部分單元格）,先選定所有單元格，點“格式”“單元格”“保護”，取消“鎖定”前面的“”。,再選定你要保護的單元格，點“格式”“單元格”“保護”，在“鎖定”前面打上“”。,3. 點“工具”“保護”“保護工作表”，輸入兩次密碼，點兩次“確定”即可。,5. Excel中當某一單元格符合特定條件，如何在另一單元格顯示特定的顏色,比如： A11時，C1顯示紅色 0A11時，C1顯示綠色 A10時，C1顯示黃色,方法如下： 單擊C1單元格，點“格式”“條件格式”， 條件1設為： 公式=A11,點“格式” “字體”“顏色”，點擊紅色后點“確定”。,2. 條件

4、2設為：公式=AND（A10,A11）,點“格式” “字體”“顏色”，點擊綠色后點“確定”。,3. 條件3設為：公式=A10 點“格式”“字體”“顏色”，點擊黃色后點“確定”。,4. 三個條件設定好后，點“確定”即可。,6. EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復錄入,1. 用數(shù)據(jù)有效性定義數(shù)據(jù)長度。 用鼠標選定你要輸入的數(shù)據(jù)范圍，點“數(shù)據(jù)”一“有效性”一“設置”，“有效性條件”設成“允許”“文本長度”“等于”“5”（具體條件可根據(jù)你的需要改變）。還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好后點“確定”。,2. 用條件格式避免重復。 選定A列，點“格式”一“條件格式

5、”，將條件設成 “公式=COUNTIF($A：$A,$A1)1”,點“格式”一“字體”一“顏色”，選定紅色后點兩次“確定”。 COUNTIF(range,criteria)：計算某個區(qū)域中滿足給定條件的單元 格數(shù)目。,這樣設定好后，輸入數(shù)據(jù)如果長度不對會有提示，如果數(shù)據(jù)重復字體將會變成紅色。,7. 去掉默認的表格線,單擊“工具”菜單中的“選項”，再單擊對話框中的“視圖”，找到“網(wǎng)格線”，使之失效（將左邊的“”去掉）。,8. 增加工作簿的頁數(shù),單擊“插入”菜單下的“工作表”，即可在底部看到一個新名稱 的工作表。一個工作簿最多可以有255張工作表。,9. 相對地址、絕對地址、混合地址,相對地址的形

6、式是地址直接用列號行號組成。 絕對地址的形式為：$A$1,$B$1。 有些公式引用單元格是用混合型地址。,10. 數(shù)據(jù)的輸入,日期和時間的輸入 當前系統(tǒng)日期的輸入：ctrl + ； 當前系統(tǒng)時間的輸入：ctrl + shift +； 12小時制輸入時間，在時間數(shù)字后空一格，并鍵入 字母a或p，例如，8：00 a 改變?nèi)掌诟袷皆凇案袷?單元格”中修改,Word 里：Alt+Shift+D：當前日期 Alt+Shift+T：當前時間,數(shù)值的輸入,負數(shù)輸入：直接輸入負號和數(shù)字，或用圓括號輸入。 分數(shù)輸入：先輸入0和一個空格，再輸入分數(shù)。直接輸入分數(shù) 則認為是日期。 輸入據(jù)用自動設置小數(shù)點或尾隨零的數(shù)

7、字。在“工具一選項一 編輯”選項卡中設置自動小數(shù)點,公式的輸入,公式以等號“=”開頭，Excel有很多可利用的公式。如果想在工作簿中看到的是公式，而不是具體的值，則在“工具一選項一視圖”選項卡中，選中“窗口選項”下的“公式”復選框。注意，這樣在打印的時候打印的也是公式。,11. 輸入相同數(shù)據(jù)的兩種情況,同時在多個單元格中輸入相同的數(shù)據(jù)。 方法：選擇要輸入數(shù)據(jù)的單元格，如果不連續(xù)可以使用Ctrl鍵 進行選擇，然后輸入數(shù)據(jù)，再按下Ctrl+Enter鍵就可以了。 同時在多張工作表中輸入或編輯相同的數(shù)據(jù)選中多個工作表后 后在相應單元格中輸入數(shù)據(jù)，選擇多個工作表的方法也是使用 Ctrl或Shift鍵。

8、,12. 數(shù)據(jù)的行列對調(diào),選取要對調(diào)的數(shù)據(jù)范圍，復制。 在目的單元格上單擊鼠標右鍵，選擇“選擇性粘貼”，在對話 框中選擇“轉(zhuǎn)置”，確定。,13. 跨表操作數(shù)據(jù),設有名稱為sheet1、sheet2和sheet3的三張工作表，現(xiàn)要用sheet1的D8單元格的內(nèi)容乘以40%，再加上sheet2的B8單元格內(nèi)容乘以60%作為sheet3的A8單元格的內(nèi)容。則應該在sheet3的A8單元格輸入以下算式：=sheet1！D8*40%+sheet2！B8*60%。,14. 兩次選定單元格,有時，我們需要在某個單元格內(nèi)連續(xù)輸入多個測試值，以查看引用此單元格的其他單元格的效果。但每次輸入一個值后按Enter鍵

9、，活動單元格默認下移一個單元格，非常不便。單擊鼠標選定單元格，然后按住Ctrl鍵再次單擊鼠標選定此單元格（此時，單元格周圍將出現(xiàn)實線框）。,15. 兩列數(shù)據(jù)巧合并,在EXCEL表格中，如果有兩列數(shù)據(jù)需要合并起來，如果是數(shù)字的話，一般情況下可以使用四則運算的方法進行計算；但是如果有一列是文字，另一列是數(shù)字（或者文字），那么可以先在這兩列后面的一個空列中單擊，并輸入=號，然后輸入第一列的第一個單元格的相對引用名，輸入如果總分已排過序，則數(shù)字的排位就是它當前的位置。上述函數(shù)中最后的“0”指明排位的方式，當其為 0或省略時，Excel 對數(shù)字的排位是按照降序排列的。 如果不為零， Excel 對數(shù)字的

10、排位則是按照升序排列的。該函數(shù)對重復數(shù)的排位相同，而下一名次則將前面的重復數(shù)計算在內(nèi)。,2. 使用計數(shù)函數(shù)COUNTIF,在L2單元格中輸入函數(shù) “=COUNTIF(K$2:K$1001,“”&K2)+1”， 然后向下自動填充即可實現(xiàn)該操作。,該函數(shù)的功能是計算區(qū)域中滿足給定條件的單元格個數(shù)。上述函數(shù)中采用絕對引用指定計算區(qū)域K$2:K$1001，名次所對應的單元格K2采用相對引用。即求出在K2:K1001區(qū)域中大于名次所對應總分的單元格的個數(shù)，然后加上1就可以得出該單元格中數(shù)值的名次。該操作不管有幾個重復的，大于它的個數(shù)卻是固定的，所以重復數(shù)據(jù)的名次也就是相同的了。同RANK函數(shù)一樣，該函數(shù)

11、不管是否已經(jīng)按總分進行排序均可進行上述操作。,3. 采用IF語句加COUNT函數(shù),該操作與上述兩種方法不同，必須先將總分按照降序排列。然后將第一名的名次“1”輸入，再在L3單元格中輸入函數(shù)“=IF(K3=K2,L2,COUNT($K$2:K3)”，確定后向下自動填充也可實現(xiàn)同分同名次的操作。,COUNT函數(shù)的功能是返回指定區(qū)域中數(shù)字單元格的個數(shù)。因為K列全是數(shù)字單元格，所以該總分處在第幾位上名次就是幾。上述語句的意思就是如果當前總分和上一個總分相同，則輸入上一個總分的名次，否則執(zhí)行COUNT語句，統(tǒng)計個數(shù)。,20. 按Shift鍵 把Excel單元格區(qū)域轉(zhuǎn)換為圖片,Shift鍵在Excel里有

12、這樣的妙處：在按下Shift鍵的同時點擊“編輯”菜單，原來的復制和粘貼選項就會變成“復制圖片”和“粘貼圖片”。利用這一功能，我們就能把選定的單元格區(qū)域方便地轉(zhuǎn)換為圖片。,具體操作方法如下： 首先選中需要復制成圖片的單元格區(qū)域，然后按住Shift鍵，依次選擇“編輯復制圖片”命令，接著彈出“復制圖片”窗口，選擇“圖片”單選項后點擊“確定”按鈕。這時選定的表格區(qū)域就已經(jīng)被復制成圖片了。,再到需要使用這張圖片的地方選擇“粘貼”命令即可(或者按Shift鍵再選擇“編輯粘貼圖片”命令)，比如將其在Word中粘貼。 另外，如果選擇“如打印效果”單選項，那么被轉(zhuǎn)換為圖片的數(shù)據(jù)區(qū)域是按照打印效果來處理的，比如設

13、置有粗邊框，那么粘貼出來的圖片上就會有粗邊框。,21. 制作漂亮的工資條,新建一Excel文件，在sheet1中存放工資表的原始數(shù)據(jù)，假設有O列。第一行是工資項目，從第二行開始是每個人的工資。,在sheet2中我們來設置工資條。根據(jù)實際情況，工資條由三行構成，一行對應工資項目，一行對應一個人的工資數(shù)據(jù)，然后是一個空行用來方便切割。這樣三行構成一個工資條。工資項目處在行號除以3余數(shù)為1的行上；空行處在行號能整除3的行上。以上兩行不難設置，關鍵是工資數(shù)據(jù)行，牽扯到sheet1與 sheet2中數(shù)據(jù)的對應，經(jīng)分析不難看出“sheet1中的數(shù)據(jù)行=INT(sheet2中的數(shù)據(jù)行+4)/3)”。,這樣我

14、們在sheet2的A1單元格中輸入公式“=IF(MOD(ROW(),3)=0,”“,IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$N,INT(ROW()+4)/3),COLUMN()”。確認后選擇A1單元格，把鼠標放在A1單元格的右下角，鼠標變成“+”時，向右拖動鼠標自動填充至O列，這樣工資條中的第一行就出來了。選定A1：O1，把鼠標放在O1單元格的右下角，鼠標再次變成“+”時，向下拖動鼠標自動填充到數(shù)據(jù)的最后一行，工資條就全部制作完成了。,IF(logical_test,value_if_ture,value_if_false)：判斷一個條件是否

15、滿足，如果滿 足返回一個值，如果不滿足則返回另一個值。 MOD(number,divisor): 返回兩數(shù)相除的余數(shù)。 INDEX(arraym,row_num,column_num):在給定的單元格區(qū)域中，返回特定行 列交叉處單元格的值或引用。Array：單元格區(qū)域或數(shù)組常量；row_num：數(shù) 組或引用中要返回值的行序號。如果忽略，則必須有column_num參數(shù)； column_num：數(shù)組或引用中要返回值的列序號。如果忽略，則必須有 row_num參數(shù)。 INT(number)：將數(shù)值向下取整數(shù)為最接近的整數(shù)。,該公式運用IF函數(shù)，對MOD函數(shù)所取的引用行號與3的余數(shù)進行判斷。如果余數(shù)

16、為0，則產(chǎn)生一個空行；如果余數(shù)為1，則固定取sheet1中第一行的內(nèi)容；否則運用INDEX函數(shù)和INT函數(shù)來取Sheet1對應行上的數(shù)。 最后來設置一下格式，選定A1：O2設上表格線，空行不設。然后選定A1：O3，拖動N3的填充柄向下自動填充，這樣有數(shù)據(jù)的有表格線，沒有數(shù)據(jù)的沒有表格線。最后調(diào)整一下頁邊距，千萬別把一個工資條打在兩頁上。怎么樣，還滿意吧?,22. 圖表編輯操作,1.創(chuàng)建圖表 2.編輯圖表 3.圖表的保存,1. 創(chuàng)建圖表,創(chuàng)建圖表的操作步驟： (1) 在工作表中選擇數(shù)據(jù)區(qū)域。按住“Ctrl”鍵同時拖動鼠標， 可以選擇數(shù)據(jù)區(qū)域中不連續(xù)的單元格區(qū)域。,(2) 單擊常用工具欄的圖表向?qū)О粹o，屏幕出現(xiàn)圖表向?qū)У?步對話框。,(3) 選擇柱形圖中的“簇狀柱形圖”，單擊“下一步”按鈕，屏 幕出現(xiàn)圖表向?qū)У牡诓?。,(4) 該對話框有“數(shù)據(jù)區(qū)域”和“系列”兩個選項卡?！皵?shù)據(jù)區(qū)域”選項 卡用于確定圖表所包含的數(shù)據(jù)區(qū)域。在數(shù)據(jù)區(qū)域欄中顯示目前包 含在圖表中的所有數(shù)據(jù)單元格的范圍?！跋盗小边x項卡用于修改數(shù) 據(jù)系列的名稱、數(shù)值和分類軸標志。 (5) 選擇系列產(chǎn)生在“列”，單擊“