EXCEL使用技巧 (2)

《EXCEL使用技巧 (2)》由會員分享，可在線閱讀，更多相關(guān)《EXCEL使用技巧 (2)（10頁珍藏版）》請在裝配圖網(wǎng)上搜索。

1、EXCEL使用技巧2009-04-09 09:14一、求字符串中某字符出現(xiàn)的次數(shù)：例：求A1單元格中字符a出現(xiàn)的次數(shù)：=LEN(A1)-LEN(SUBSTITUTE(A1,a,)二、如何在不同工作薄之間復(fù)制宏：1、打開含有宏的工作薄，點“工具/宏(M)”，選中你的宏，點“編輯”，這樣就調(diào)出了VB編輯器界面。2、點“文件/導(dǎo)出文件”，在“文件名”框中輸入一個文件名（也可用默認(rèn)的文件名），注意擴展名為“.bas”，點“保存”。3、將擴展名為“.bas”的文件拷貝到另一臺電腦，打開EXCEL，點“工具/宏/VB編輯器”，調(diào)出VB編輯器界面，點“文件/導(dǎo)入文件”，找到你拷貝過來的文件，點“打開”，退出

2、VB編輯器，你的宏已經(jīng)復(fù)制過來了。三、如何在EXCEL中設(shè)置單元格編輯權(quán)限(保護部分單元格)1、先選定所有單元格,點格式-單元格-保護,取消鎖定前面的。2、再選定你要保護的單元格,點格式-單元格-保護,在鎖定前面打上。3、點工具-保護-保護工作表,輸入兩次密碼,點兩次確定即可。四、excel中當(dāng)某一單元格符合特定條件，如何在另一單元格顯示特定的顏色比如：A11時，C1顯示紅色0A11時，C1顯示綠色A1“條件格式”，條件1設(shè)為：公式 =A1=1 2、點“格式”-“字體”-“顏色”，點擊紅色后點“確定”。條件2設(shè)為：公式 =AND(A10,A1“字體”-“顏色”，點擊綠色后點“確定”。條件3設(shè)為

3、：公式 =A1“字體”-“顏色”，點擊黃色后點“確定”。4、三個條件設(shè)定好后，點“確定”即出。五、EXCEL中如何控制每列數(shù)據(jù)的長度并避免重復(fù)錄入1、用數(shù)據(jù)有效性定義數(shù)據(jù)長度。用鼠標(biāo)選定你要輸入的數(shù)據(jù)范圍，點數(shù)據(jù)-有效性-設(shè)置，有效性條件設(shè)成允許文本長度等于5（具體條件可根據(jù)你的需要改變）。還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法等，定義好后點確定。2、用條件格式避免重復(fù)。選定A列，點格式-條件格式，將條件設(shè)成“公式=COUNTIF($A:$A,$A1)1”，點格式-字體-顏色，選定紅色后點兩次確定。這樣設(shè)定好后你輸入數(shù)據(jù)如果長度不對會有提示，如果數(shù)據(jù)重復(fù)字體將會變成紅色。六、

4、在EXCEL中如何把B列與A列不同之處標(biāo)識出來？（一）、如果是要求A、B兩列的同一行數(shù)據(jù)相比較：假定第一行為表頭，單擊A2單元格，點“格式”-“條件格式”，將條件設(shè)為: “單元格數(shù)值”“不等于”=B2 點“格式”-“字體”-“顏色”，選中紅色，點兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)制。B列可參照此方法設(shè)置。（二）、如果是A列與B列整體比較（即相同數(shù)據(jù)不在同一行）：假定第一行為表頭，單擊A2單元格，點“格式”-“條件格式”，將條件設(shè)為: “公式”=COUNTIF($B:$B,$A2)=0 點“格式”-“字體”-“顏色”，選中紅色，點兩次“確定”。用格式刷將A2單元格的條件格式向下復(fù)

5、制。B列可參照此方法設(shè)置。按以上方法設(shè)置后，AB列均有的數(shù)據(jù)不著色，A列有B列無或者B列有A列無的數(shù)據(jù)標(biāo)記為紅色字體。七、在EXCEL中建立下拉列表按鈕選定你要設(shè)置下拉列表的單元格，點“數(shù)據(jù)”-“有效性”-“設(shè)置”，在“允許”下面選擇“序列”，在“來源”框中輸入你的下拉列表內(nèi)容，各項之間用半角逗號隔開，如：A,B,C,D 選中“提供下拉前頭”，點“確定”。八、阿拉伯?dāng)?shù)字轉(zhuǎn)換為大寫金額(最新收集)假定你要在B1輸入阿拉佰數(shù)字,C1轉(zhuǎn)換成中文大寫金額(含元角分),請在C1單元格輸入如下公式: =SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B10,負(fù))&TEXT(INT

6、(ABS(B1)+0.5%),dbnum2G/通用格式元;)&TEXT(RIGHT(RMB(B1,2),2),dbnum20角0分;整),),零角,IF(B12=104)*(D2:D9999=重本),1,0)輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公式符號。十一、EXCEL中某個單元格內(nèi)文字行間距調(diào)整方法。當(dāng)某個單元格內(nèi)有大量文字時，很多人都覺得很難將其行間距按自己的要求進行調(diào)整。現(xiàn)介紹一種方法可以讓你任意調(diào)整單元格內(nèi)文字的行間距：右擊單元格,點設(shè)置單元格格式-對齊,將水平對齊選擇靠左,將垂直對齊選擇分散對齊,選中自動換行，點“確定”。你再用鼠標(biāo)將行高根據(jù)你要求的行距

7、調(diào)整到適當(dāng)高度即可。注：綠色內(nèi)容為關(guān)鍵點，很多人就是這一點設(shè)置不對而無法調(diào)整行間距。十二、如何在EXCEL中引用當(dāng)前工作表名如果你的工作薄已經(jīng)保存,下面公式可以得到單元格所在工作表名: =RIGHT(CELL(filename),LEN(CELL(filename)-FIND(,CELL(filename)十三、相同格式多工作表匯總求和方法假定同一工作薄有SHEET1至SHEET100共100個相同格式的工作表需要匯總求和，結(jié)果放在SHEET101工作表中，請在SHEET101的A1單元格輸入：=SUM(單擊SHEET1標(biāo)簽，按住Shift鍵并單擊SHEET100標(biāo)簽，單擊A1單元格，再輸入：

8、）此時公式看上去內(nèi)容如下：=SUM(SHEET1:SHEET100!A1)按回車后公式變?yōu)?=SUM(SHEET1:SHEET100!A1)所以，最簡單快捷的方法就是在SHEET101的A1單元格直接輸入公式：=SUM(SHEET1:SHEET100!A1)然后按回車。十四、如何判斷單元格里是否包含指定文本？假定對A1單元格進行判斷有無指定文本,以下任一公式均可: =IF(COUNTIF(A1,*&指定文本&*)=1,有,無) =IF(ISERROR(FIND(指定文本,A1,1),無,有)十五、如何替換EXCEL中的通配符“?”和“*”？在EXECL中查找和替換時，?代表任意單個字符，*代表

9、任意多個字符。如果要將工作表中的?和*替換成其他字符，就只能在查找框中輸入?和*才能正確替換。另外如果要替換本身,在查找框中要輸入才行。十六、EXCEL中排名次的兩種方法：（一）、用RANK()函數(shù)： 假定E列為成績，F(xiàn)列為名次，F(xiàn)2單元格公式如下： =RANK(E2,E2:Ex,0) 這種方法，分?jǐn)?shù)相同時名次相同，隨后的名次將空缺。 例如：兩個人99分，并列第2名，則第3名空缺，接下來是第4名。（二）、用公式排序(中國式排名)： 假定成績在E列,請在F2輸入公式：=SUM(IF(E$2:E$1000E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)+1公式以Ctrl+Sh

10、ift+Enter三鍵結(jié)束。第二種方法分?jǐn)?shù)相同的名次也相同，不過隨后的名次不會空缺。十七、什么是單元格的相對引用、絕對引用和混合引用？相對引用、絕對引用和混合引用是指在公式中使用單元格或單元格區(qū)域的地址時，當(dāng)將公式向旁邊復(fù)制時，地址是如何變化的。 具體情況舉例說明： 1、相對引用，復(fù)制公式時地址跟著發(fā)生變化，如C1單元格有公式：=A1+B1 當(dāng)將公式復(fù)制到C2單元格時變?yōu)椋?A2+B2 當(dāng)將公式復(fù)制到D1單元格時變?yōu)椋?B1+C1 2、絕對引用，復(fù)制公式時地址不會跟著發(fā)生變化，如C1單元格有公式：=$A$1+$B$1 當(dāng)將公式復(fù)制到C2單元格時仍為：=$A$1+$B$1 當(dāng)將公式復(fù)制到D1單元

11、格時仍為：=$A$1+$B$1 3、混合引用，復(fù)制公式時地址的部分內(nèi)容跟著發(fā)生變化，如C1單元格有公式：=$A1+B$1 當(dāng)將公式復(fù)制到C2單元格時變?yōu)椋?$A2+B$1 當(dāng)將公式復(fù)制到D1單元格時變?yōu)椋?$A1+C$1 規(guī)律：加上了絕對地址符“$”的列標(biāo)和行號為絕對地址，在公式向旁邊復(fù)制時不會發(fā)生變化，沒有加上絕對地址符號的列標(biāo)和行號為相對地址，在公式向旁邊復(fù)制時會跟著發(fā)生變化。混合引用時部分地址發(fā)生變化。注意：工作薄和工作表都是絕對引用，沒有相對引用。技巧：在輸入單元格地址后可以按F4鍵切換“絕對引用”、“混合引用”和“相對引用”狀態(tài)。十八、求某一區(qū)域內(nèi)不重復(fù)的數(shù)據(jù)個數(shù)例如求A1:A100

12、范圍內(nèi)不重復(fù)數(shù)據(jù)的個數(shù)，某個數(shù)重復(fù)多次出現(xiàn)只算一個。有兩種計算方法：一是利用數(shù)組公式：=SUM(1/COUNTIF(A1:A100,A1:A100)輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公式符號。二是利用乘積求和函數(shù)：=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100)十九、EXCEL中如何動態(tài)地引用某列的最后一個單元格？在SHEET2中的A1單元格中引用表SHEET1中的A列的最后一個單元格中的數(shù)值(SHEET1中A列的最后一個單元格的數(shù)值不確定,隨時會增加行數(shù))：=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1

13、,0,1,1) 或者: =INDIRECT(sheet1!A&COUNTA(Sheet1!A:A) 注:要確保你SHEET1的A列中間沒有空格。二十、如何在一個工作薄中建立幾千個工作表右擊某個工作表標(biāo)簽,點插入,選擇工作表,點確定,然后按住Alt+Enter鍵不放,你要多少個你就按住多久不放,你會看到工作表數(shù)量在不斷增加,幾千個都沒有問題。二十一、如何知道一個工作薄中有多少個工作表方法一： 點工具-宏-VB編輯器-插入-模塊,輸入如下內(nèi)容: Sub sheetcount() Dim num As Integer num = ThisWorkbook.Sheets.Count Sheets(1)

14、.Select Cells(1, 1) = num End Sub 運行該宏，在第一個(排在最左邊的)工作表的A1單元格中的數(shù)字就是sheet的個數(shù)。方法二： 按Ctrl+F3(或者點插入-名稱-定義),打開定義名稱對話框 定義一個X 引用位置輸入:=get.workbook(4) 點確定。 然后你在任意單元格輸入=X 出來的結(jié)果就是sheet的個數(shù)。參考資料：二十二、一個工作薄中有許多工作表如何快速整理出一個目錄工作表 1、用宏3.0取出各工作表的名稱，方法： Ctrl+F3出現(xiàn)自定義名稱對話框，取名為X，在“引用位置”框中輸入：=MID(GET.WORKBOOK(1),FIND(,GET.WORKBOOK(1)+1,100) 確定 2、用HYPERLINK函數(shù)批量插入連接，方法： 在目錄工作表（一般為第一個sheet）的A2單元格輸入公式：=HYPERLINK(#&INDEX(X,ROW()&!A1,INDEX(X,ROW() 將公式向下填充，直到出錯為止，目錄就生成了。參考資料：