比較常用的80條Excel技巧1_計(jì)算機(jī)軟件及應(yīng)用_IT計(jì)算機(jī)_專業(yè)資料

1、比較常用的80條excel技巧1、兩列數(shù)據(jù)查找相同值對(duì)應(yīng)的位置=match(b1,a：a, 0)2、已知公式得結(jié)果定義名稱二 evaluate(sheet 1!c1)已知結(jié)杲得公式定義名稱二 get.cell(6,sheet 1 !c1)3、強(qiáng)制換行用 alt+enter4、超過15位數(shù)字輸入這個(gè)問題問的人太多了，也收起來吧。一、單元格設(shè)置為文本;二、在輸入數(shù)字前先 輸入5、如果隱藏了 b列，如果讓它顯示出來？選中a到c列，點(diǎn)擊右鍵，取消隱藏選中a到c列，雙擊選屮任一列寬線或改變?nèi)我涣袑拰⑹髽?biāo)移到到ac列z間，等鼠標(biāo)變?yōu)殡p豎線時(shí)拖動(dòng)z。6、excel中行列互換復(fù)制，選擇性粘貼，選中轉(zhuǎn)置，確定即

2、可7、excel是怎么加密的(1) 、保存吋可以的另存為右上角的”工具”常規(guī)設(shè)置(2) 、工貝選項(xiàng)安全性8、關(guān)于 countifcountif函數(shù)只能有一個(gè)條件，如大于90,為=countif(a 1:a 10,>=90h)介于 80 與 90 之間需用減，為=countif(a1:a10/>80n)-countif(a1:a10,>90h)9、根據(jù)身份證號(hào)提取岀生fl期、=if(len(a 1)= 1 & date(mid(a 1,7,4),mid( a 1,11,2),mid(a 1,13,2),if(len(a 1 )= 15,date(mid(a 1,7,2)

3、,mid(a 1,9,2),mid(al,11,2),”錯(cuò)誤身份證號(hào)”)(2)、=text(mid(a2,7,6+(len(a2)= 18)*2)/#-00-00n)* 1身份證excel補(bǔ)充：a) 提取生日(年月日人=if(len(a 1)= 15 ,text(conc aten ate(n 19”,mid( a1,7,6),”#0000”),text(mid(a 1,7, 8)/#-00-00h)b) 捉取年齡：二year(today()if(len(a 1 )= 15,concatenate(” 19n,mid(a 1,7,2),mid(a 1,7,4)c) 提取性別:=if(mod(i

4、f(len( a1 )= 15,mid( a 1,15,1 ),mid( a 1,17,1 ),2)= 1,n 男”，”女”)10、想在sheet2中完全引用sheet 1輸入的數(shù)據(jù)工作組，按住shift或ctrl鍵，同時(shí)選定sheet 1 > sheet2o11、一列中不輸入重復(fù)數(shù)字?jǐn)?shù)據(jù)卜有效性t自定義t公式輸入=countif(a:a,a 1)= 1如果要查找重復(fù)輸入的數(shù)字條件格式公式二countif(a:a,a5)>1格式選紅色12、直接打開一個(gè)電子表格文件的時(shí)候打不開“文件夾選項(xiàng)” “文件類型”屮找到.xls文件，并在“高級(jí)”中確認(rèn)是否有參數(shù)1%,如果沒有，請(qǐng)手工加上13、

5、excel下拉菜單的實(shí)現(xiàn)數(shù)據(jù)卜冇效性卜序列1410列數(shù)據(jù)合計(jì)成一列=sum(offset($ 1 ,(ro w()2)* 10+1, 10,1)15、查找數(shù)據(jù)公式兩個(gè)(基木查找函數(shù)為vlookup,match)(1) 、根據(jù)符合行列兩個(gè)條件查找對(duì)應(yīng)結(jié)果=vlookup(h1, al： e7, match(i1, al： el, 0), false)(2) 、根據(jù)符合兩列數(shù)據(jù)查找對(duì)應(yīng)結(jié)呆(為數(shù)組公式)=index(c1： c7, match(h 1&i1,a1:a7&b1:b7,o)16、如何隱藏單元格中的0單元格格式自定義0;-0;或選項(xiàng)視圖零值去勾。呵呵，如果用公式就要看 情

6、況了。17、多個(gè)工作表的單元格合并計(jì)算=sheet 1!d4+sheet2!d4+sheet3!d4,更好的=sum(sheetl:sheet3!d4)18、獲得工作表名稱(1) 定義名稱：name二 get. documents)(2) 、定義名稱:path二get.document(2)(3) 、在al中輸入=cell(nfilenameh)得到路徑級(jí)文件名在需耍得到文件名的單元格輸入二mid(a1,find(”*”,substitute(a1,len(a1)len(substitute(a1,",”)+1,len(al)(4) 、自定義函數(shù)public function nam

7、e()dim filename as stringfilename = activewname = filenameend functioni9> a)如何獲取一個(gè)刀的最大天數(shù):”=day(date(2002,3,1)-1)”或 n=day(b 1 -1) ”,b1 為 ”2001 -03-01b) 數(shù)據(jù)區(qū)包含某一字符的項(xiàng)的總和,該用什么公式二sumif(a:a,”*”&”某一字符數(shù)據(jù)區(qū))c) 最后一行為文木：=offset($b$ 1 ,match(char(65535),b:b) 1,)d）最后一行為數(shù)字:=offset($b$ 1 ,match(9.

8、9999e+307,b:b)-1,)或者:=lookup(2, l/(b 1:b 1000<>n ”),b 1 :b 1000)e）評(píng)委打分中，如何去掉兩個(gè)以上最高分，兩個(gè)以上最底分，求剩余人員的 平均分?同時(shí)顯示出被去掉的分?jǐn)?shù)?？纯磘rimmean（）i¥|數(shù)幫助。被去掉的分?jǐn)?shù):最大兩個(gè)：=large(data,l;2)最小兩個(gè)：=small(data, 1;2)f）怎樣很簡(jiǎn)單的判斷最后一位是字母 right(al)*lg）出錯(cuò)的字母=if(isnumber(-right(a 1,1),”數(shù)字”，”字母”)=if(iserr(right(a 1 )* 1),“ 字母”，

9、”數(shù)字”)h）如何設(shè)置單元格，令其不接受包含空格的字符選定a列數(shù)據(jù)有效性自定義公式=iserror(find(" ”,al)數(shù)據(jù)-有效性口定義-公式=len(al)=len(trim(al)注* 單元格消空格用函數(shù)=trim(a1)i) 實(shí)現(xiàn)跳格累加如=a 1 +a4+a7+a 10+a 13+a 16+a 19+a22=sum(n(offset(a 1 ,(row( 1:10)-1)*3,)j）在一個(gè)工作表屮引用其他工作表屮的數(shù)據(jù)，但是被引用的工作表不是固定的, 根據(jù)輸入的工作表名自動(dòng)選擇相應(yīng)的工作表中的數(shù)據(jù)， 請(qǐng)問在公式里怎樣引用?二 indirect a t&'

10、! '&'e 1h)a 1 為工作表名k)奇數(shù)行求和=sumproduct(a1:a1000);j:mod(row(a1:a1000),2)l) 偶 數(shù) 行 求 和=sumproduct(a 1:a1000)*not(mod(row(a 1:a1000),2)m）查看字符串字?jǐn)?shù)=len(a1)n）求非空單元格數(shù)量公式計(jì)算岀來的數(shù)據(jù)，counta不能用的（否則空字符也計(jì)算進(jìn)去了）=countif($e$3e$65536；,?*m)o）動(dòng)態(tài)求和公式，自a列al單元格到當(dāng)前行前面一行的單元格求和.二 sum(indirect(” a1: a” & rowq-1)20、

11、比較好用的excel文檔修復(fù)工具excelrecovery21、excel開方運(yùn)算將8開3次方，可以用這個(gè)公式，在單元格中輸入=8a(l/3)22、單元格中的數(shù)據(jù)分散對(duì)齊文木格式全角輸入23、查找工作表中的鏈接ctrl+或編輯鏈接24、如何讓空單元格門動(dòng)填為0選中需更改的區(qū)域查找空替換025、把word里的數(shù)字轉(zhuǎn)換到excel方法有多種，選中復(fù)制設(shè)置輸入單元格為文本選擇性粘貼值選中表格轉(zhuǎn)換為文本粘貼分列對(duì)分列選項(xiàng)設(shè)置為文本另存為文本文件excel中打開文本文件對(duì)導(dǎo)入文本對(duì)話框進(jìn)行對(duì)應(yīng)設(shè)置26、擴(kuò)展你的命名域excel允許你定義命名的域，這樣你的方程式就能夠更具描述性，例如，你可以使用 “二su

12、m (bonus)” 來替換 “=sum(c2:c25)”。盡管 excel 的命名域通常都是靜態(tài)的單元域，例如“c2:c25”，但你也口j以在添加新數(shù)據(jù)的時(shí)候創(chuàng)建 一個(gè)動(dòng)態(tài)的命名域。要將一個(gè)域更名并讓其變成動(dòng)態(tài)，請(qǐng)注意工作簿的名稱，以及第一個(gè)數(shù)據(jù)單元所在的地 址，它需要不是一個(gè)列標(biāo)題，例如“$c$2”。點(diǎn)擊“插入名稱定義”，在“在當(dāng)前工作簿的名稱”屮輸入工作簿名。在“引用位置”中，輸 入 a=offset(bonus!$c$2,0,0,counta(bonus!$c:$c)w,并注意其中不能有空格，接著按下回車鍵來創(chuàng)建命名域。這個(gè)方程式就能夠定義你的命名域，計(jì)算列中的數(shù)值，并能夠按單元格的數(shù)

13、字延伸這個(gè) 域。27、單雙行簡(jiǎn)單選在a列前插入一空列，a1中輸入1, a2中輸入2,選中a1a2,將光標(biāo)移到a2右 卜角，當(dāng)光標(biāo)變成實(shí)心十字時(shí)，按住ctrl見往下拖動(dòng)到想耍的位置，再按這列排序，就把單雙行分開了28、如何在已有的單元格中批量加入一段固定字符？例如：在單位的人事資料，在excel中輸入后，由于上級(jí)妾求在原來的職稱證書的 號(hào)碼全部再加兩位，即要在每個(gè)人的證書號(hào)碼前再添上兩位數(shù)13,如果一個(gè)一個(gè)改的話實(shí)在太麻煩了，那么我們可以用下而的辦法，省時(shí)又省力:1）假設(shè)證書號(hào)在a列，在a列后點(diǎn)擊鼠標(biāo)右鍵，插入一列，為b列；2）在b2單元格寫入：=”13” &a2后回車；3）看到結(jié)果為13xxxxxxxxxxxxx 了嗎？鼠標(biāo)放到b2位置，單元格的下方不是有 一個(gè)小方點(diǎn)嗎，按著鼠標(biāo)左鍵往下拖動(dòng)直到結(jié)束。當(dāng)你放開鼠標(biāo)左鍵時(shí)就全部都改好了。若是在原證書號(hào)后面加13則在b2單元格中寫入：=a2 & “13”后回車。29、如何設(shè)置文件下拉窗口的最下面的最近運(yùn)行的文件名個(gè)數(shù)？打開“工具”，選“選項(xiàng)”，