EXCEL日常使用經(jīng)驗(yàn)積累

1、精選優(yōu)質(zhì)文檔-傾情為你奉上(1) 點(diǎn)F(x)函數(shù)，會(huì)出函數(shù)列表(2) 數(shù)組公式：=SUM(A1：A10*B1：B10)(3) “ALT”+”=” 快捷求和(4) TEXT(TODAY(),”YYYY年MM月DD日”)(5) 字符串連接符：&(6) Offset（C5,5,3）.返回C5單元格下方第5行和右邊第3列的引用。正數(shù)為向下向右；負(fù)數(shù)為向上向左(7) 向下復(fù)制單元格的公式,選中有公式的單元格,雙擊右下角,可快速?gòu)?fù)制(8) 隱藏和保護(hù)公式:將工作表設(shè)置成受保護(hù)狀態(tài);選中需要保護(hù)的公式選擇”鎖定”和”隱藏”(9) 合并單元格填充序列類似下面的填充品牌編號(hào)，A1=”品牌編號(hào)”, A2=

2、1,A3=MAX(A$1：A2)+1選中A2和所有需要填充序列的單元格，將光標(biāo)放在A2處，CTRL+回車，將自動(dòng)填充序列(10) 快速修改公式當(dāng)將一個(gè)工作表移動(dòng)復(fù)制到另外一個(gè)工作簿時(shí)，原來(lái)工作表中的公式會(huì)添加對(duì)應(yīng)工作簿的引用名稱，好用！(11) 判斷單元格是否為空白，IF(B2=”,”空白”,”非空白”) ，如果有“空格”也不是“空白”，有空格不能用”判斷IF(Isnumber（A1）,”是數(shù)字”,”不是數(shù)字”) 判斷A1單元格內(nèi)是否是數(shù)字IF(A1,”1234”,”5678”) 如果A1空白，則表示A1=0，即條件FALSE；A1為數(shù)字，則TURE，其他則不和規(guī)矩Sum(IF(A1=”銷售一

3、部”,b1:b10,c1:c10), IF可以返回值；也可以返回區(qū)域；復(fù)合條件判斷IF(AND(A1<>1，A2<>3),”1111”,”222”)AND函數(shù)，中間條件要同時(shí)滿足，可以用OR函數(shù)IF函數(shù)嵌套層數(shù)有限制(12) VLOOKUP(查找值，查找范圍，返回范圍的序列號(hào)，精確匹配和模糊匹配的標(biāo)志)缺省為模糊匹配，精確匹配可以寫一個(gè)大于零的正數(shù).TURE為近似匹配，F(xiàn)ALSE為精確匹配。0為精確匹配(13) IS函數(shù)ISODD(A1) 是否為奇數(shù)ISNUMBER(A1)ISTEXT(A1)ISERROR(A1)判斷是否為錯(cuò)誤值。錯(cuò)誤值：#N/A，#DIV/0!ISN

4、A(A1),只針對(duì)#N/A錯(cuò)誤碼(14) 避免#N/A類錯(cuò)誤顯示，如果是#N/A，顯示為空,找不到數(shù)據(jù)時(shí)顯示的 IF(ISERROR(VLOOKUP(“A”,B1：B10,2,0),”,VLOOKUP(“A”,B1：B10,2,0)(15) type函數(shù) (16) cell函數(shù)(17) count() counta() count(A1:B100), 返回對(duì)應(yīng)區(qū)域內(nèi)包含數(shù)字?jǐn)?shù)據(jù)的單元格的個(gè)數(shù)，空格和字符型的不計(jì)數(shù)counta（A1：B100）,返回對(duì)應(yīng)區(qū)域非“真空”單元格的個(gè)數(shù)，空單元格不計(jì)數(shù)區(qū)域可以跨工作表：counta(工作表1：工作表10！A:A),返回這些工作表A列非空單元格數(shù)量之和計(jì)

5、算某列中特定值的單元格的個(gè)數(shù) =count(1/(A1:A10=”A”)從A1到A10，如果A1=”A”，則1/(A1：A10=”A”)=1/1=1 ,相等則為ture即1，將等于1的計(jì)數(shù)(18) sum()函數(shù)不連續(xù)區(qū)域求和：=sum(a1:a10,c7:c8,f10:f200),最多可以30個(gè)區(qū)域超過(guò)30個(gè)區(qū)域，可以采用定義:選擇區(qū)域，點(diǎn)右鍵，定義名稱，設(shè)多個(gè)區(qū)域定義成了三個(gè)名稱AA、BB、CC,則求和=sum(AA)+SUM(BB)+SUM(CC)選定制定區(qū)域內(nèi)為空的單元格：用鼠標(biāo)選定區(qū)域，ctrl+g,彈出“定位條件”的窗口(19) countif()函數(shù)=COUNTIF(F2:F13

6、,"<" & A21)=COUNTIF(F2:F13,"<" & AVERAGE(F2:F13)， &為字符串連接符=COUNTIF(D2:D13,"*空調(diào)*") 通配符*，匹配多個(gè)字符，含空調(diào)2個(gè)字的單元格的數(shù)量=COUNTIF(C2:C13,"?") 通配符?,匹配一個(gè)字符=COUNTIF(G2:G13,"<20000")-COUNTIF(G2:G13,"<=10000")(20) 限制某列不允許有重復(fù)值選中某列如M列；選中

7、部分高亮反白單元格為選中區(qū)域的“活動(dòng)單元格”；輸入的數(shù)據(jù)要滿足自定義公式，自定義公式為：countif(M:M,M1)=1，表示在M列中與“活動(dòng)單元格內(nèi)容”相同的單元格的個(gè)數(shù)為1，滿足為1，則合法，否則非法。M1實(shí)際是M1：M9999等 (21) 限制某些單元格必須輸入含有”AA”字符的數(shù)據(jù)O1是“活動(dòng)單元格”，在定義的區(qū)域范圍內(nèi)，隨著輸入位置的不同，計(jì)數(shù)的單元格是不同的(22) 刪除重復(fù)數(shù)據(jù)，下面非1的需要?jiǎng)h除(23) Sumif(),橫向隔列求和=SUMIF(A2:A8,B11,C2:C8) 判斷區(qū)域A2:A8，判斷條件B11，求和區(qū)域C2：C8，判斷區(qū)域和求和區(qū)域必須一樣大=SUMIF(

8、C2:C8,">=2000") 判斷區(qū)域和求和區(qū)域相同，則可省略求和區(qū)域參數(shù)=SUMIF(B18:F24,B27,C18:G24) 求和區(qū)域自動(dòng)延后一個(gè)，判斷區(qū)域和求和區(qū)域都可以為矩形=SUM(SUMIF(B31:B35,"A","C",C31:C35) sumif按照判斷條件”A”計(jì)算一個(gè)值，按照”B”計(jì)算一個(gè)值，由SUM合并起來(lái)=SUMIF(A:A,"<" & DATE(2012,H2+1,1),B:B)-SUMIF(A:A,"<" & DATE(2012,

9、H2,1),B:B)A:A是日期型，DATE(2012，3，2)就表示日期2012年3月2日。上述是分月求和隔列求和，求和區(qū)域自動(dòng)對(duì)應(yīng)條件判斷區(qū)域(24) sumproduct(),多條件求和和計(jì)數(shù)=SUMPRODUCT(B2:B7,C2:C7)=SUMPRODUCT(B2:B7*C2:C7) 這兩個(gè)公式都是B2*C2+B3*C3+.=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A") month（）返回對(duì)應(yīng)單元格內(nèi)容的月份數(shù)，(MONTH(A3:A9)=3)等于3時(shí)返回TURE，否則返回FALSE，TURE就是1，F(xiàn)ALSE是0，上述公式就是SU

10、M(A*B),但A須為3月，B須為A，由于是邏輯運(yùn)算，實(shí)際求的就是3月份并且A的個(gè)數(shù)，是計(jì)數(shù)=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)，則是在上述基礎(chǔ)上的求和，滿足上述條件的C列的和計(jì)數(shù) =SUMPRODCUT(條件1)*(條件2)*(條件n)求和 =SUMPRODCUT(條件1)*(條件2)*(條件n)*數(shù)據(jù)區(qū)域)=SUMPRODUCT(MONTH($A$2:$A$17)=G$1)*ISNUMBER(FIND($E4,$B$2:$B$17)*$C$2:$C$17)FIND(find_text,within_text,sta

11、rt_num)FINDB(find_text,within_text,start_num)Find_text 是要查找的文本。Within_text 是包含要查找文本的文本。Start_num 指定開(kāi)始進(jìn)行查找的字符。within_text 中的首字符是編號(hào)為 1 的字符。如果忽略 start_num，則假設(shè)其為 1。(25) sumtotal() 分類匯總函數(shù)=SUBTOTAL(104,B2:B6) 第一個(gè)參數(shù)是function_num;后面的參數(shù)是范圍，不同的Function_num,對(duì)單元格的范圍采取不同的分類匯總方式。104為范圍內(nèi)的最大值（隱藏的單元格不參與運(yùn)算）(26) 兩個(gè)日期之

12、間天數(shù)=DATEDIF("1999/8/31","2012/8/31","D")；”Y”,年，”M”月，”D”日(27) 餅圖中隱藏0數(shù)據(jù)項(xiàng)Excel自定義數(shù)字格式代碼分為四個(gè)部分，分別指定不同數(shù)值的格式，中間用“;”號(hào)分隔，具體如下：  正數(shù)格式;負(fù)數(shù)格式;零格式;文本格式在本例中，因我們只希望出現(xiàn)正數(shù)的數(shù)值，故可以指定格式為：0%;  。 格式 ; 還可隱藏所有值。(28) 消除提示：“此工作簿包含到其他數(shù)據(jù)源的鏈接”在“數(shù)據(jù)”“編輯鏈接”中斷開(kāi)鏈接即可(29) Excel中循環(huán)引用的檢查在

13、“公式”“錯(cuò)誤檢查”“循環(huán)引用”中可以找到循環(huán)引用的單元格進(jìn)行修改(30) 透視表中子類和父類位于不同的列中得到下圖右面的透視表，可以在主分類字段上設(shè)置“不分類匯總”、“以表格方式顯示項(xiàng)目標(biāo)簽實(shí)現(xiàn)”(31) 數(shù)據(jù)有效性控制：跨表引用參考設(shè)置受控單元格的有效條件如下，則對(duì)應(yīng)單元格只能輸入“工作大類”表中A列的數(shù)據(jù)項(xiàng)(32) EXCEL表格奇偶行顯示不同的底色，使用條件格式(33) 為特定的單元格內(nèi)容設(shè)置特定的底色：條件格式，功能很強(qiáng)大(34) 轉(zhuǎn)置，行列變換，如下左邊的表格變成右邊的表格 方法1：選擇復(fù)制；選擇性粘貼，轉(zhuǎn)置；方法2：=tranpose(A1:F5) Ctrl+Shift+Enter,但是需要選中目標(biāo)區(qū)域,過(guò)大有錯(cuò)誤信息，過(guò)小只能轉(zhuǎn)置一部分(35) 矩陣乘積函數(shù)MMULT(ARRAY1，ARRAY2)MMULT(array1, array2), 返回兩個(gè)數(shù)組的矩陣乘積。結(jié)果矩陣的行數(shù)與 array1 的行數(shù)相同，矩陣的列數(shù)與 array2 的列數(shù)相同.第一個(gè)數(shù)組的列數(shù)必須和第二個(gè)數(shù)組的行數(shù)相同C(m,k)=A(m,n)*B(n,k)(36) 最值函數(shù)Max（）;Min（）Larg