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

1、精選優(yōu)質(zhì)文檔-傾情為你奉上(1) 點F(x)函數(shù)，會出函數(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ù)為向下向右；負數(shù)為向上向左(7) 向下復(fù)制單元格的公式,選中有公式的單元格,雙擊右下角,可快速復(fù)制(8) 隱藏和保護公式:將工作表設(shè)置成受保護狀態(tài);選中需要保護的公式選擇”鎖定”和”隱藏”(9) 合并單元格填充序列類似下面的填充品牌編號，A1=”品牌編號”, A2=

2、1,A3=MAX(A$1：A2)+1選中A2和所有需要填充序列的單元格，將光標放在A2處，CTRL+回車，將自動填充序列(10) 快速修改公式當將一個工作表移動復(fù)制到另外一個工作簿時，原來工作表中的公式會添加對應(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ù)，中間條件要同時滿足，可以用OR函數(shù)IF函數(shù)嵌套層數(shù)有限制(12) VLOOKUP(查找值，查找范圍，返回范圍的序列號，精確匹配和模糊匹配的標志)缺省為模糊匹配，精確匹配可以寫一個大于零的正數(shù).TURE為近似匹配，F(xiàn)ALSE為精確匹配。0為精確匹配(13) IS函數(shù)ISODD(A1) 是否為奇數(shù)ISNUMBER(A1)ISTEXT(A1)ISERROR(A1)判斷是否為錯誤值。錯誤值：#N/A，#DIV/0!ISN

4、A(A1),只針對#N/A錯誤碼(14) 避免#N/A類錯誤顯示，如果是#N/A，顯示為空,找不到數(shù)據(jù)時顯示的 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), 返回對應(yīng)區(qū)域內(nèi)包含數(shù)字數(shù)據(jù)的單元格的個數(shù)，空格和字符型的不計數(shù)counta（A1：B100）,返回對應(yīng)區(qū)域非“真空”單元格的個數(shù)，空單元格不計數(shù)區(qū)域可以跨工作表：counta(工作表1：工作表10！A:A),返回這些工作表A列非空單元格數(shù)量之和計

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

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

7、部分高亮反白單元格為選中區(qū)域的“活動單元格”；輸入的數(shù)據(jù)要滿足自定義公式，自定義公式為：countif(M:M,M1)=1，表示在M列中與“活動單元格內(nèi)容”相同的單元格的個數(shù)為1，滿足為1，則合法，否則非法。M1實際是M1：M9999等 (21) 限制某些單元格必須輸入含有”AA”字符的數(shù)據(jù)O1是“活動單元格”，在定義的區(qū)域范圍內(nèi)，隨著輸入位置的不同，計數(shù)的單元格是不同的(22) 刪除重復(fù)數(shù)據(jù)，下面非1的需要刪除(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ū)域自動延后一個，判斷區(qū)域和求和區(qū)域都可以為矩形=SUM(SUMIF(B31:B35,"A","C",C31:C35) sumif按照判斷條件”A”計算一個值，按照”B”計算一個值，由SUM合并起來=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ū)域自動對應(yīng)條件判斷區(qū)域(24) sumproduct(),多條件求和和計數(shù)=SUMPRODUCT(B2:B7,C2:C7)=SUMPRODUCT(B2:B7*C2:C7) 這兩個公式都是B2*C2+B3*C3+.=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A") month（）返回對應(yīng)單元格內(nèi)容的月份數(shù)，(MONTH(A3:A9)=3)等于3時返回TURE，否則返回FALSE，TURE就是1，F(xiàn)ALSE是0，上述公式就是SU

10、M(A*B),但A須為3月，B須為A，由于是邏輯運算，實際求的就是3月份并且A的個數(shù)，是計數(shù)=SUMPRODUCT(MONTH(A3:A9)=3)*(B3:B9="A")*C3:C9)，則是在上述基礎(chǔ)上的求和，滿足上述條件的C列的和計數(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 指定開始進行查找的字符。within_text 中的首字符是編號為 1 的字符。如果忽略 start_num，則假設(shè)其為 1。(25) sumtotal() 分類匯總函數(shù)=SUBTOTAL(104,B2:B6) 第一個參數(shù)是function_num;后面的參數(shù)是范圍，不同的Function_num,對單元格的范圍采取不同的分類匯總方式。104為范圍內(nèi)的最大值（隱藏的單元格不參與運算）(26) 兩個日期之

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

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