五種常用excel技巧?一、求字符串中某字符出現的次數： ，現在小編就來說說關于五種常用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 中當某一單元格符合特定條件，如何在另一單元格顯示特定的顔色

2

比如：

A1〉1 時，C1 顯示紅色

0<A1<1 時，C1 顯示綠色

A1<0 時，C1 顯示黃色

方法如下：

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

公式 =A1=1

2、點“格式”->“字體”->“顔色”，點擊紅色後點“确定”。

條件 2 設為：

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

3、點“格式”->“字體”->“顔色”，點擊綠色後點“确定”。

條件 3 設為：

公式 =A1<0

點“格式”->“字體”->“顔色”，點擊黃色後點“确定”。

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

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

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

用鼠标選定你要輸入的數據範圍，點"數據"->"有效性"->"設置"，"有效性條件"

設成"允許""文本長度""等于""5"（具體條件可根據你的需要改變）。

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

"确定"。

3

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 列無

的數據标記為紅色字體。

4

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

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

許”下面選擇“序列”，在“來源”框中輸入你的下拉列表内容，各項之間用半

角逗号隔開，如：

A,B,C,D

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

八、阿拉伯數字轉換為大寫金額

假定你要在 A5 輸入阿拉佰數字,B5 轉換成中文大寫金額(含元角分),請在 B5 單

元格輸入如下公式:

=IF((INT(A5*10)-INT(A5)*10)=0,TEXT(INT(A5),"[DBNum2]G/通用格式")&"

元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,"整","零

"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分

"),TEXT(INT(A5),"[DBNum2]G/通用格式")&"元

"&IF((INT(A5*100)-INT((A5)*10)*10)=0,TEXT((INT(A5*10)-INT(A5)*10),"[

DBNum2]G/通用格式")&"角整

",TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角

"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"))

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

假定有大量的數據，需要将每一行按從大到小排序，如何操作？

5

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

鍵字排序。所以，這一問題不能用排序來解決。解決方法如下：

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

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

用填充柄将公式向下複制到相應行。

2、用鼠标選定 F 列，用“查找/替換”的方法，将該列的"$A"替換成"$A$"，

"$E"替換成"$E$"。

3、用鼠标選定 F 列所有有公式的單元格，用填充柄将公式向右複制到 J 列。

你原有數據将按行從大到小排序出現在 F 至 J 列。如有需要可用“選擇性粘貼/

數值”複制到其他地方。

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

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

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

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

104，“錄取結果”為“重本”的人數。統計結果存放在本工作表的其他列。

公式如下：

=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))

輸入完公式後按 Ctrl Shift Enter 鍵,讓它自動加上數組公式符号"{}"。

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

6

當某個單元格内有大量文字時，很多人都覺得很難将其行間距按自己的要求進行

調整。現介紹一種方法可以讓你任意調整單元格内文字的行間距：

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

齊"選擇"分散對齊",選中"自動換行"，點“确定”。你再用鼠标将行高根據你要

求的行距調整到适當高度即可。

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

十二、如何在 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)

7

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

=SUM('SHEET1:SHEET100'!A1)

然後按回車。

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

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

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

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

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

在 EXCEL 中查找和替換時，?代表任意單個字符，*代表任意多個字符。如果要

将工作表中的"?"和"*"替換成其他字符，就隻能在查找框中輸入~?~和~*~才能

正确替換。

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

（一）、用 RANK()函數：

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

=RANK(E2,E:E)

這種方法，分數相同時名次相同，随後的名次将空缺。

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

（二）、用排序加公式：

1、先在後面用填充柄增加一列（假定為 G 列）與行号相同的序列數。

8

2、将全表按分數列（E 列）排序，在 F2 單元格輸入 1，在 F3 單元格輸入公式：

=IF(E3=E2,F2,F2 1)

将公式向下複制到相應行。

3、選定公式列，點“複制”，在 F1 單元格點右鍵，點“選擇性粘貼/數值”，

點“确定”。

4、将全表按最後一列（G 列）排序，删除最後一列。

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

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

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

時，當将公式向旁邊複制時，地址是如何變化的。

具體情況舉例說明：

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

9

當将公式複制到 D1 單元格時變為：=$A1 C$1

規律：加上了絕對地址符“$”的列标和行号為絕對地址，在公式向旁邊複

制時不會發生變化，沒有加上絕對地址符号的列标和行号為相對地址，在公式向

旁邊複制時會跟着發生變化。混合引用時部分地址發生變化。

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

十八、求某一區域内不重複的數據個數

例如求 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 列中間沒有空格。

10

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

右擊某個工作表标簽,點"插入",選擇"工作表",點"确定",然後按住 Alt Enter 鍵不

放,你要多少個你就按住多久不放,你會看到工作表數量在不斷增加,幾千個都沒

有問題。

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!