混合引用

EXCEL使用技巧匯編

EXCEL使用技巧匯編

一、求字符串中某字符出現的次數：

例：求A1單元格中字符"a"出現的次數：

=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))

二、如何在不同工作薄之間復制宏：

1、打開含有宏的工作薄，點“工具/宏(M)…”，選中你的宏，點

“編輯”，這樣就調出了VB編輯器界面。

2、點“文件/導出文件”，在“文件名”框中輸入一個文件名

（也可用默認的文件名），注意擴展名為“.bas”，點“保存”。

3、將擴展名為“.bas”的文件拷貝到另一臺電腦，打開EXCEL，

點“工具/宏/VB編輯器”，調出VB編輯器界面，點“文件/導入文

件”，找到你拷貝過來的文件，點“打開”，退出VB編輯器，你的宏

已經復制過來了。

三、如何在EXCEL中設置單元格編輯權限(保護部分單元格)

1、先選定所有單元格,點"格式"->"單元格"->"保護",取消"鎖定"

前面的"√"。

2、再選定你要保護的單元格,點"格式"->"單元格"->"保護",在"鎖

定"前面打上"√"。

3、點"工具"->"保護"->"保護工作表",輸入兩次密碼,點兩次"確定

"即可。

四、excel中當某一單元格符合特定條件，如何在另一單元格顯示

特定的顏色

比如：

A1〉1時，C1顯示紅色

0

A1<0時，C1顯示黃色

方法如下：

1、單元擊C1單元格，點“格式”>“條件格式”，條件1設為：

公式=A1=1

2、點“格式”->“字體”->“顏色”，點擊紅色后點“確定”。

條件2設為：

公式=AND(A1>0,A1<1)

3、點“格式”->“字體”->“顏色”，點擊綠色后點“確定”。

條件3設為：

公式=A1<0

點“格式”->“字體”->“顏色”，點擊黃色后點“確定”。

4、三個條件設定好后，點“確定”即出。

五、EXCEL中如何控制每列數據的長度并避免重復錄入

1、用數據有效性定義數據長度。

用鼠標選定你要輸入的數據范圍，點"數據"->"有效性"->"設置"，

"有效性條件"設成"允許""文本長度""等于""5"（具體條件可根據你的

需要改變）。

還可以定義一些提示信息、出錯警告信息和是否打開中文輸入法

等，定義好后點"確定"。

2、用條件格式避免重復。

選定A列，點"格式"->"條件格式"，將條件設成“公式

=COUNTIF($A:$A,$A1)>1”，點"格式"->"字體"->"顏色"，選定紅

色后點兩次"確定"。

這樣設定好后你輸入數據如果長度不對會有提示，如果數據重復

字體將會變成紅色。

六、在EXCEL中如何把B列與A列不同之處標識出來？

（一）、如果是要求A、B兩列的同一行數據相比較：

假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，

將條件設為:

“單元格數值”“不等于”=B2

點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。

用格式刷將A2單元格的條件格式向下復制。

B列可參照此方法設置。

（二）、如果是A列與B列整體比較（即相同數據不在同一行）：

假定第一行為表頭，單擊A2單元格，點“格式”->“條件格式”，

將條件設為:

“公式”=COUNTIF($B:$B,$A2)=0

點“格式”->“字體”->“顏色”，選中紅色，點兩次“確定”。

用格式刷將A2單元格的條件格式向下復制。

B列可參照此方法設置。

按以上方法設置后，AB列均有的數據不著色，A列有B列無或者

B列有A列無的數據標記為紅色字體。

七、在EXCEL中建立下拉列表按鈕

選定你要設置下拉列表的單元格，點“數據”->“有效性”->

“設置”，在“允許”下面選擇“序列”，在“來源”框中輸入你的

下拉列表內容，各項之間用半角逗號隔開，如：A,B,C,D

選中“提供下拉前頭”，點“確定”。

八、阿拉伯數字轉換為大寫金額(最新收集)

假定你要在B1輸入阿拉佰數字,C1轉換成中文大寫金額(含元角

分),請在C1單元格輸入如下公式:

=SUBSTITUTE(SUBSTITUTE(IF(-RMB(B1),IF(B1>0,,"負

")&TEXT(INT(ABS(B1)+0.5%),"[dbnum2]G/通用格式

元;;")&TEXT(RIGHT(RMB(B1,2),2),"[dbnum2]0角0分;;整"),),"

零角",IF(B1^2<1,,"零")),"零分","整")

九、EXCEL中怎樣批量地處理按行排序

假定有大量的數據(數值)，需要將每一行按從大到小排序，如何操

作？

由于按行排序與按列排序都是只能有一個主關鍵字,主關鍵字相同

時才能按次關鍵字排序。所以，這一問題不能用排序來解決。解決方

法如下：

1、假定你的數據在A至E列，請在F1單元格輸入公式：

=LARGE($A1:$E1,COLUMN(A1))

用填充柄將公式向右向下復制到相應范圍。

你原有數據將按行從大到小排序出現在F至J列。如有需要可用

“選擇性粘貼/數值”復制到其他地方。

注：第1步的公式可根據你的實際情況（數據范圍）作相應的修

改。如果要從小到大排序,公式改

為:=SMALL($A1:$E1,COLUMN(A1))

十、巧用函數組合進行多條件的計數統計

例：第一行為表頭，A列是“姓名”，B列是“班級”，C列是

“語文成績”，D列是“錄取結果”，現在要統計“班級”為“二”，

“語文成績”大于等于104，“錄取結果”為“重本”的人數。統計

結果存放在本工作表的其他列。

公式如下：

=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="

重本"),1,0))

輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號

"{}"。

十一、EXCEL中某個單元格內文字行間距調整方法。

當某個單元格內有大量文字時，很多人都覺得很難將其行間距按

自己的要求進行調整。現介紹一種方法可以讓你任意調整單元格內文

字的行間距：

右擊單元格,點"設置單元格格式"->"對齊",將"水平對齊"選擇"靠左

",將"垂直對齊"選擇"分散對齊",選中"自動換行"，點“確定”。你再用

鼠標將行高根據你要求的行距調整到適當高度即可。

注：綠色內容為關鍵點，很多人就是這一點設置不對而無法調整

行間距。

十二、如何在EXCEL中引用當前工作表名

如果你的工作薄已經保存,下面公式可以得到單元格所在工作表

名:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-

FIND("]",CELL("filename")))

十三、相同格式多工作表匯總求和方法

假定同一工作薄有SHEET1至SHEET100共100個相同格式的工

作表需要匯總求和，結果放在SHEET101工作表中，請在SHEET101

的A1單元格輸入：

=SUM(單擊SHEET1標簽，按住Shift鍵并單擊SHEET100標簽，

單擊A1單元格，再輸入：）

此時公式看上去內容如下：

=SUM('SHEET1:SHEET100'!A1)

按回車后公式變為

=SUM(SHEET1:SHEET100!A1)

所以，最簡單快捷的方法就是在SHEET101的A1單元格直接輸

入公式：

=SUM('SHEET1:SHEET100'!A1)

然后按回車。

十四、如何判斷單元格里是否包含指定文本？

假定對A1單元格進行判斷有無"指定文本",以下任一公式均可:

=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","無")

=IF(ISERROR(FIND("指定文本",A1,1)),"無","有")

十五、如何替換EXCEL中的通配符“?”和“*”？

在EXECL中查找和替換時，?代表任意單個字符，*代表任意多個

字符。如果要將工作表中的"?"和"*"替換成其他字符，就只能在查找框

中輸入~?~和~*~才能正確替換。另外如果要替換~本身,在查找框中

要輸入~~才行。

十六、EXCEL中排名次的兩種方法：

（一）、用RANK()函數：

假定E列為成績，F列為名次，F2單元格公式如下：

=RANK(E2,E:E)

這種方法，分數相同時名次相同，隨后的名次將空缺。

例如：兩個人99分，并列第2名，則第3名空缺，接下來是第4

名。

（二）、用公式排序(中國式排名)：

假定成績在E列,請在F2輸入公式：

=SUM(IF(E$2:E$1000>E2,1/COUNTIF(E$2:E$1000,E$2:E$100

0)))+1

公式以Ctrl+Shift+Enter三鍵結束。

第二種方法分數相同的名次也相同，不過隨后的名次不會空缺。

十七、什么是單元格的相對引用、絕對引用和混合引用？

相對引用、絕對引用和混合引用是指在公式中使用單元格或單元

格區域的地址時，當將公式向旁邊復制時，地址是如何變化的。

具體情況舉例說明：

1、相對引用，復制公式時地址跟著發生變化，如C1單元格有公

式：=A1+B1

當將公式復制到C2單元格時變為：=A2+B2

當將公式復制到D1單元格時變為：=B1+C1

2、絕對引用，復制公式時地址不會跟著發生變化，如C1單元格

有公式：=$A$1+$B$1

當將公式復制到C2單元格時仍為：=$A$1+$B$1

當將公式復制到D1單元格時仍為：=$A$1+$B$1

3、混合引用，復制公式時地址的部分內容跟著發生變化，如C1

單元格有公式：=$A1+B$1

當將公式復制到C2單元格時變為：=$A2+B$1

當將公式復制到D1單元格時變為：=$A1+C$1

規律：加上了絕對地址符“$”的列標和行號為絕對地址，在公式

向旁邊復制時不會發生變化，沒有加上絕對地址符號的列標和行號為

相對地址，在公式向旁邊復制時會跟著發生變化。混合引用時部分地

址發生變化。

注意：工作薄和工作表都是絕對引用，沒有相對引用。

技巧：在輸入單元格地址后可以按F4鍵切換“絕對引用”、“混

合引用”和“相對引用”狀態。

十八、求某一區域內不重復的數據個數

例如求A1:A100范圍內不重復數據的個數，某個數重復多次出現

只算一個。有兩種計算方法：

一是利用數組公式：

=SUM(1/COUNTIF(A1:A100,A1:A100))

輸入完公式后按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號

"{}"。

二是利用乘積求和函數：

=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))

十九、EXCEL中如何動態地引用某列的最后一個單元格？

在SHEET2中的A1單元格中引用表SHEET1中的A列的最后一

個單元格中的數值(SHEET1中A列的最后一個單元格的數值不確定,隨

時會增加行數)：

=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0,1,1)

或者:

=INDIRECT("sheet1!A"&COUNTA(Sheet1!A:A))

注:要確保你SHEET1的A列中間沒有空格。

二十、如何在一個工作薄中建立幾千個工作表

右擊某個工作表標簽,點"插入",選擇"工作表",點"確定",然后按住

Alt+Enter鍵不放,你要多少個你就按住多久不放,你會看到工作表數量

在不斷增加,幾千個都沒有問題。

二十一、如何知道一個工作薄中有多少個工作表

方法一：

點"工具"->"宏"->"VB編輯器"->"插入"->"模塊",輸入如下內容:

Subsheetcount()

DimnumAsInteger

num=

Sheets(1).Select

Cells(1,1)=num

EndSub

運行該宏，在第一個(排在最左邊的)工作表的A1單元格中的數字

就是sheet的個數。

方法二：

按Ctrl+F3(或者點"插入"->"名稱"->"定義"),打開"定義名稱"對話

框

定義一個X

"引用位置"輸入:

=ok(4)

點"確定"。

然后你在任意單元格輸入=X

出來的結果就是sheet的個數。

二十二、一個工作薄中有許多工作表如何快速整理出一個目錄工

作表

1、用宏3.0取出各工作表的名稱，方法：

Ctrl+F3出現自定義名稱對話框，取名為X，在“引用位置”框中

輸入：

=MID(OK(1),FIND("]",OK(1))+1,1

00)

確定

2、用HYPERLINK函數批量插入連接，方法：

在目錄工作表（一般為第一個sheet）的A2單元格輸入公式：

=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW())

)

將公式向下填充，直到出錯為止，目錄就生成了。

上一篇：Excel經典實用技巧

下一篇：[轉]電子表格Excel操作的一些技巧