Excel電子表格的使用技巧課件

Excel的使用技巧

快速填入相同的內(nèi)容快速復(fù)制上一單元格求字符串中某字符出現(xiàn)的次數(shù)如何在EXCEL中設(shè)置單元格編輯權(quán)限（保護部分單元格）Excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復(fù)錄入去掉默認的表格線增加工作簿的頁數(shù)相對地址、絕對地址、混合地址數(shù)據(jù)的輸入輸入相同數(shù)據(jù)的兩種情況數(shù)據(jù)的行列對調(diào)跨表操作數(shù)據(jù)兩次選定單元格兩列數(shù)據(jù)巧合并用快捷鍵設(shè)置單元格文本自動調(diào)整為合適的列寬繪制斜線表頭

1.

快速填入相同內(nèi)容

選中多個單元格后，輸入字符，然后按Ctrl+Enter組合鍵，即可在選中的每個單元格中填入上述相同的字符。

2.

快速復(fù)制上一單元格

選中下面的單元格，按Ctrl+'（'為西文的單引號）組合鍵，即可將上一單元格的內(nèi)容快速復(fù)制下來。

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.點“工具”→“保護”→“保護工作表”，輸入兩次密碼，點兩次“確定”即可。5.Excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色比如：A1>1時，C1顯示紅色0<A1<1時，C1顯示綠色A1<0時，C1顯示黃色

點“格式”→“字體”→“顏色”，點擊紅色后點“確定”。2.條件2設(shè)為：公式=AND（A1>0,A1<1）點“格式”→“字體”→“顏色”，點擊綠色后點“確定”。3.條件3設(shè)為：公式=A1<0點“格式”→“字體”→“顏色”，點擊黃色后點“確定”。6.EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復(fù)錄入1.用數(shù)據(jù)有效性定義數(shù)據(jù)長度。用鼠標選定你要輸入的數(shù)據(jù)范圍，點“數(shù)據(jù)”一“有效性”一“設(shè)置”，“有效性條件”設(shè)成“允許”“文本長度”“等于”“5”（具體條件可根據(jù)你的需要改變）。還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好后點“確定”。2.用條件格式避免重復(fù)。選定A列，點“格式”一“條件格式”，將條件設(shè)成“公式=COUNTIF($A：$A,$A1)>1”,點“格式”一“字體”一“顏色”，選定紅色后點兩次“確定”。COUNTIF(range,criteria)：計算某個區(qū)域中滿足給定條件的單元格數(shù)目。

這樣設(shè)定好后，輸入數(shù)據(jù)如果長度不對會有提示，如果數(shù)據(jù)重復(fù)字體將會變成紅色。8.增加工作簿的頁數(shù)單擊“插入”菜單下的“工作表”，即可在底部看到一個新名稱的工作表。一個工作簿最多可以有255張工作表。9.相對地址、絕對地址、混合地址相對地址的形式是地址直接用列號行號組成。絕對地址的形式為：$A$1,$B$1。有些公式引用單元格是用混合型地址。10.數(shù)據(jù)的輸入日期和時間的輸入當(dāng)前系統(tǒng)日期的輸入：ctrl+；當(dāng)前系統(tǒng)時間的輸入：ctrl+shift+；12小時制輸入時間，在時間數(shù)字后空一格，并鍵入字母a或p，例如，8：00a改變?nèi)掌诟袷皆凇案袷?單元格”中修改Word里：Alt+Shift+D：當(dāng)前日期Alt+Shift+T：當(dāng)前時間公式的輸入公式以等號“=”開頭，Excel有很多可利用的公式。如果想在工作簿中看到的是公式，而不是具體的值，則在“工具一選項一視圖”選項卡中，選中“窗口選項”下的“公式”復(fù)選框。注意，這樣在打印的時候打印的也是公式。11.輸入相同數(shù)據(jù)的兩種情況同時在多個單元格中輸入相同的數(shù)據(jù)。方法：選擇要輸入數(shù)據(jù)的單元格，如果不連續(xù)可以使用Ctrl鍵進行選擇，然后輸入數(shù)據(jù)，再按下Ctrl+Enter鍵就可以了。同時在多張工作表中輸入或編輯相同的數(shù)據(jù)選中多個工作表后后在相應(yīng)單元格中輸入數(shù)據(jù)，選擇多個工作表的方法也是使用Ctrl或Shift鍵。12.

數(shù)據(jù)的行列對調(diào)選取要對調(diào)的數(shù)據(jù)范圍，復(fù)制。在目的單元格上單擊鼠標右鍵，選擇“選擇性粘貼”，在對話框中選擇“轉(zhuǎn)置”，確定。14.

兩次選定單元格有時，我們需要在某個單元格內(nèi)連續(xù)輸入多個測試值，以查看引用此單元格的其他單元格的效果。但每次輸入一個值后按Enter鍵，活動單元格默認下移一個單元格，非常不便。單擊鼠標選定單元格，然后按住Ctrl鍵再次單擊鼠標選定此單元格（此時，單元格周圍將出現(xiàn)實線框）。15.兩列數(shù)據(jù)巧合并在EXCEL表格中，如果有兩列數(shù)據(jù)需要合并起來，如果是數(shù)字的話，一般情況下可以使用四則運算的方法進行計算；但是如果有一列是文字，另一列是數(shù)字（或者文字），那么可以先在這兩列后面的一個空列中單擊，并輸入=號，然后輸入第一列的第一個單元格的相對引用名，輸入&號，然后再輸入第二個列單元格的相對引用名，最后按一下回車鍵即可。然后，鼠標移動到這個單元格的拖動柄上，向下拖動知道需要合并的數(shù)據(jù)最后一行。16.用快捷鍵設(shè)置單元格文本在Excel中，熟練使用快捷鍵可以幫助我們設(shè)置單元格文本。在選擇好要設(shè)置格式的單元格后，如果要設(shè)置為粗體，可以按Ctrl+B；如果要設(shè)置為斜體，可以按Ctrl+I；如果要設(shè)置為下劃線，可以按Ctrl+U。17.自動調(diào)整為合適的列寬

在Excel中，如果一個單元格中文字內(nèi)容過長，就無法為系統(tǒng)全部顯示出來，手工調(diào)整又很麻煩。這時可以將鼠標移動到該列的列標與下一列的交界處，當(dāng)鼠標指針變?yōu)閮蓚€方面的箭頭時，雙擊，這時系統(tǒng)就會把列寬設(shè)置為該列中最寬的那個單元格的寬度。

18.

繪制斜線表頭

在Excel經(jīng)常需要繪制斜線表頭，簡單的兩分式的表頭可以先把表格繪制好，并在需要繪制表頭的單元格中單擊，然后單擊格式菜單下的單元格命令項，在單元格格式對話框中點開邊框選項卡，然后根據(jù)繪制的需要單擊外邊框和下面的邊框線。19.Excel做成績表時自動實現(xiàn)同分同名次

在用Excel統(tǒng)計成績時，我們一般會根據(jù)成績高低進行排序，然后按序列自動填充出名次。這種方法得出的名次與總分沒有關(guān)聯(lián)，即使成績相同，也會填充出不同的名次。如果數(shù)據(jù)較少，我們可以采用手動的方法將成績相同的人員改成相同的名次，但數(shù)據(jù)較多時就很麻煩了。經(jīng)過實踐，以下三種方法可以自動實現(xiàn)同分同名次的操作。假設(shè)有1000名考生，字段位置如下圖所示。成績相同，名次不同1.使用排位函數(shù)RANK

在L2單元格中輸入函數(shù)“=RANK(K2,K$2:K$1001,0)”，回車后L2單元格中會出現(xiàn)名次“1”，然后選定L2，拖動其填充柄向下填充即可自動實現(xiàn)。

該函數(shù)的功能是返回一個數(shù)字(上述函數(shù)中的K2，此處采用相對應(yīng)用，填充時隨行的變化而變化)在數(shù)字列表(上述函數(shù)中的K$2:K$1001，此處采用絕對引用，填充時不發(fā)生變化)中的排位。

數(shù)字的排位是其大小與列表中其他值的比值。該函數(shù)在使用時，即使總分沒有排序，它也可以直接求出所對應(yīng)總分的名次;如果總分已排過序，則數(shù)字的排位就是它當(dāng)前的位置。上述函數(shù)中最后的“0”指明排位的方式，當(dāng)其為0或省略時，Excel對數(shù)字的排位是按照降序排列的。

如果不為零，Excel對數(shù)字的排位則是按照升序排列的。該函數(shù)對重復(fù)數(shù)的排位相同，而下一名次則將前面的重復(fù)數(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，名次所對應(yīng)的單元格K2采用相對引用。即求出在K2:K1001區(qū)域中大于名次所對應(yīng)總分的單元格的個數(shù)，然后加上1就可以得出該單元格中數(shù)值的名次。該操作不管有幾個重復(fù)的，大于它的個數(shù)卻是固定的，所以重復(fù)數(shù)據(jù)的名次也就是相同的了。同RANK函數(shù)一樣，該函數(shù)不管是否已經(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ù)字單元格，所以該總分處在第幾位上名次就是幾。上述語句的意思就是如果當(dāng)前總分和上一個總分相同，則輸入上一個總分的名次，否則執(zhí)行COUNT語句，統(tǒng)計個數(shù)。20.按Shift鍵把Excel單元格區(qū)域轉(zhuǎn)換為圖片Shift鍵在Excel里有這樣的妙處：在按下Shift鍵的同時點擊“編輯”菜單，原來的復(fù)制和粘貼選項就會變成“復(fù)制圖片”和“粘貼圖片”。利用這一功能，我們就能把選定的單元格區(qū)域方便地轉(zhuǎn)換為圖片。

具體操作方法如下：首先選中需要復(fù)制成圖片的單元格區(qū)域，然后按住Shift鍵，依次選擇“編輯→復(fù)制圖片”命令，接著彈出“復(fù)制圖片”窗口，選擇“圖片”單選項后點擊“確定”按鈕。這時選定的表格區(qū)域就已經(jīng)被復(fù)制成圖片了。

再到需要使用這張圖片的地方選擇“粘貼”命令即可(或者按Shift鍵再選擇“編輯→粘貼圖片”命令)，比如將其在Word中粘貼。另外，如果選擇“如打印效果”單選項，那么被轉(zhuǎn)換為圖片的數(shù)據(jù)區(qū)域是按照打印效果來處理的，比如設(shè)置有粗邊框，那么粘貼出來的圖片上就會有粗邊框。21.制作漂亮的工資條新建一Excel文件，在sheet1中存放工資表的原始數(shù)據(jù)，假設(shè)有O列。第一行是工資項目，從第二行開始是每個人的工資。在sheet2中我們來設(shè)置工資條。根據(jù)實際情況，工資條由三行構(gòu)成，一行對應(yīng)工資項目，一行對應(yīng)一個人的工資數(shù)據(jù)，然后是一個空行用來方便切割。這樣三行構(gòu)成一個工資條。工資項目處在行號除以3余數(shù)為1的行上；空行處在行號能整除3的行上。以上兩行不難設(shè)置，關(guān)鍵是工資數(shù)據(jù)行，牽扯到sheet1與sheet2中數(shù)據(jù)的對應(yīng)，經(jīng)分析不難看出“sheet1中的數(shù)據(jù)行=INT((sheet2中的數(shù)據(jù)行+4)/3)”。這樣我們在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)：判斷一個條件是否滿足，如果滿足返回一個值，如果不滿足則返回另一個值。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ù)為0，則產(chǎn)生一個空行；如果余數(shù)為1，則固定取sheet1中第一行的內(nèi)容；否則運用INDEX函數(shù)和INT函數(shù)來取Sheet1對應(yīng)行上的數(shù)。最后來設(shè)置一下格式，選定A1：O2設(shè)上表格線，空行不設(shè)。然后選定A1：O3，拖動N3的填充柄向下自動填充，這樣有數(shù)據(jù)的有表格線，沒有數(shù)據(jù)的沒有表格線。最后調(diào)整一下頁邊距，千萬別把一個工資條打在兩頁上。怎么樣，還滿意吧?22.圖表編輯操作1.創(chuàng)建圖表2.編輯圖表3