基本知識

運算符

• : :范圍運算符,對于一個區(qū)域時使用
• , :單元格區(qū)域并集運算符.
• :(單一空格)單元格區(qū)域交集運算符.例如(B7:D7 C6:C8)返回C7
• & :字符串拼接
• ' :在單元格開頭時標記為字符串而非數(shù)值型
• ".." :表明是字符串
• =和<> :相等和不等判斷
• ^ :求冪 % :百分比
• 4**5: 就是4E5的意思
• *和?: 通配符,尤其在查找等時適用.若要查找*則要使用~*.支持通配函數(shù)如SUMIF,SUMIFS,COUNTIF,AVERAGEIF等.
• ~: 轉義字符…

技巧:

1. 將函數(shù)形式轉為結果: 復制區(qū)域數(shù)據(jù), 然后右鍵選擇性黏貼, 這時選數(shù)值, 函數(shù)的值就被粘過來了.注意hyperlink不行
2. 絕對引用: A88=88, 注意可以固定A或88, 按F4整體改變
3. 轉置(行列轉置)一般使用選擇性黏貼->轉置實現(xiàn),也可以用TRANSPOSE函數(shù)
4. 數(shù)組公式: 有些公式是某些函數(shù)返回值是數(shù)組,顯示上是{=transpose(A1:B3)}這樣.這個大括號表示數(shù)組公式.返回時會對相應選擇的區(qū)域進行相應的對應數(shù)組位置的處理.輸入公式后,需要按下CTRL+SHIFT+ENTER來起效(就是加上這個大括號)!

內(nèi)置函數(shù):

運算和數(shù)值統(tǒng)計

運算對象一般可以是地址,可以是數(shù)值,有些函數(shù)支持區(qū)域.

單數(shù)值處理

• SQRT(number): 求平方根.
• POWER(value,n):求value^n 次方
• ABS(number): 求出相應數(shù)字的絕對值辜荠。
  如果number參數(shù)不是數(shù)值火本，而是一些字符（如A等）件相，則B2中返回錯誤值“#VALUE檩坚！”。
• INT(number)：將數(shù)值向下取整為最接近的整數(shù)崇猫。
• MOD(A,B): 求出兩數(shù)相除A/B的余數(shù)沈条。
  如果B為零，則顯示錯誤值“#DIV/0!”诅炉；MOD函數(shù)可以借用函數(shù)INT來表示：上述公式可以修改為：=13-4*INT(13/4)蜡歹。
• FACT(value):階乘到value. 非整數(shù)會截尾處理.
• GCD/LCM(number1, [number2], …): 求最大公約數(shù)/最小公倍數(shù).
• RAND(): 產(chǎn)生[0,1) 的隨機數(shù).每次計算工作表時都會更新..
• RANDBETWEEN(bottom, top): 產(chǎn)生[bottom, top]之間的隨機整數(shù).
• MOD(number, divisor)：求余,number/divisor. 注意結果的符號與除數(shù)相同. MOD(n, d) = n - d*INT(n/d)
• QUOTIENT(number, divisor): 返回除法number/divisor的整數(shù)部分. 和int(number)有不同:對負數(shù)時(-10/3),前者返回-3,int返回-4.
• CEILING(number, significance): 向上取整到最接近significance倍數(shù)的值.例如整數(shù)取整significance=1,可以0.1,2等(如兩者都是負數(shù),實際向下取整..).

區(qū)域數(shù)值處理

求和和平均

• SUM(Number1,Number2……)：計算所有參數(shù)數(shù)值的和。
  如果參數(shù)為數(shù)組或引用涕烧，只有其中的數(shù)字將被計算月而。數(shù)組或引用中的空白單元格、邏輯值议纯、文本或錯誤值將被忽略父款；如果將上述公式修改為：=SUM(LARGE(D2:D63,{1,2,3,4,5}))，則可以求出前5名成績的和瞻凤。
• SUMSQ(Number1,Number2……)：計算所有參數(shù)數(shù)值的平方的和憨攒。
• SUMPRODUCT(array1, [array2], [array3], …): 在給定的幾組數(shù)組中，將數(shù)組間對應的元素相乘阀参，并返回乘積之和肝集。
• SUMXMY2(array_x, array_y): 兩組數(shù)據(jù)對應值的差的平方和. =SUMSQ(arrXi-arrYi)
• SUMX2PY2(array_x, array_y): 兩組數(shù)據(jù)對應值的平方的和.=SUM(arrXi^2+arrYi2)
• SUMX2MY2(array_x, array_y): 兩組數(shù)據(jù)對應值的平方的差.=SUM(arrXi^2-arrYi2) 數(shù)組必須具有相同維數(shù),否則返回錯誤值.非數(shù)值型元素作0處理.
• SUMIF(條件判斷的區(qū)域,條件表達式,需要計算的數(shù)值所在的單元格區(qū)域）：計算符合指定條件的單元格區(qū)域內(nèi)的數(shù)值和。條件表達式如值,”>10”這樣.
• AVERAGE(number1,number2,……): 求出所有參數(shù)的算術平均值蛛壳。
  如果引用區(qū)域中包含“0”值單元格杏瞻，則計算在內(nèi)所刀；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)捞挥。
• AVEDEV(number1, [number2], …): 返回一組數(shù)據(jù)點到其算術平均值的絕對偏差的平均值,等于AVERGER(Xi-Averger(range)). AVEDEV 是對一組數(shù)據(jù)中變化性的度量浮创。
• DEVSQ(number1, [number2], …):返回各數(shù)據(jù)點與數(shù)據(jù)均值點之差（數(shù)據(jù)偏差）的平方和。即SUMSQ(Xi-Averger(range)).
• STDEV(number1, [number2], …):根據(jù)樣本估計標準偏差砌函。即SQRT(SUMSQ(Xi-Averger(range))/(n-1)).
• VAR(number1, [number2], …):根據(jù)樣本估計方差蒸矛。即SUMSQ(Xi-Averger(range))/(n-1).

統(tǒng)計值

• MAX/MIN(number1,number2……)：求出一組數(shù)中的最大值/最小值。如果參數(shù)中有文本或邏輯值胸嘴，則忽略。
• LARGE/SMALL(array,k)：返回數(shù)據(jù)集中的第 k 個最大/小值斩祭。k為1時等價于max/min
• COUNTIF(Range,Criteria)：統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目劣像。 Range代表要統(tǒng)計的單元格區(qū)域；Criteria表示指定的條件表達式,如”>=80”摧玫。允許引用的單元格區(qū)域中有空白單元格出現(xiàn)耳奕。
• DCOUNT(database,field,criteria)：返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。
  參數(shù)說明：Database表示需要統(tǒng)計的單元格區(qū)域诬像；Field表示函數(shù)所使用的數(shù)據(jù)列（在第一行必須要有標志項）屋群；Criteria包含條件的單元格區(qū)域。 應用舉例：如圖1所示坏挠，在F4單元格中輸入公式：=DCOUNT(A1:D11,”語文”,F1:G2)芍躏，確認后即可求出“語文”列中，成績大于等于70降狠，而小于80的數(shù)值單元格數(shù)目（相當于分數(shù)段人數(shù)）对竣。 特別提醒：如果將上述公式修改為：=DCOUNT(A1:D11,,F1:G2)，也可以達到相同目的榜配。
• FREQUENCY(data_array,bins_array)：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布否纬。
  Data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；Bins_array表示為前面數(shù)組進行分隔的一列界限數(shù)值,例如分別有10,20,30,則表示區(qū)分為<=10,(10-20),[20,30),>=30四個區(qū)間蛋褥。 返回值是垂直數(shù)組,所以要選擇一列(bins數(shù)+1)作為接受結果临燃，輸入完成公式后按下“Ctrl+Shift+Enter”組合鍵進行公式數(shù)組確認，即可求出各段數(shù)值的出現(xiàn)頻率數(shù)目烙心。
• RANK(Number,ref,order）：返回某一數(shù)值在一列數(shù)值中的相對于其他數(shù)值的排位膜廊。
  Number代表需要排序的數(shù)值；ref代表排序數(shù)值所處的單元格區(qū)域弃理；order代表排序方式參數(shù)（如果為“0”或者忽略溃论，則按降序排名；如果為非“0”值痘昌，則按升序排名）钥勋。
• SUBTOTAL(function_num, ref1[, ref2, …])：返回列表或數(shù)據(jù)庫中的分類匯總炬转。參考link
  Function_num為1到11（包含隱藏值）或101到111（忽略隱藏值）之間的數(shù)字，用來指定使用什么函數(shù)在列表中進行分類匯總計算;ref1, ref2,……代表要進行分類匯總區(qū)域或引用算灸，不超過29個扼劈。
  如果采取自動篩選，無論function_num參數(shù)選用什么類型菲驴，SUBTOTAL函數(shù)忽略任何不包括在篩選結果中的行荐吵；SUBTOTAL函數(shù)適用于數(shù)據(jù)列或垂直區(qū)域，不適用于數(shù)據(jù)行或水平區(qū)域赊瞬。

矩陣處理

• TRANSPOSE(array): 轉置矩陣.返回轉置后的數(shù)組.注意這里先選擇放置轉置數(shù)據(jù)的區(qū)域,再輸入公式并選擇原始數(shù)據(jù),最后使用數(shù)組公式處理ctrl+shift+enter.最好使用絕對位置.

文本類

• LEN(text)：統(tǒng)計文本字符串中字符數(shù)目先煎。
  LEN要統(tǒng)計時，無論中全角字符巧涧，還是半角字符薯蝎，每個字符均計為“1”；與之相對應的一個函數(shù)——LENB谤绳，在統(tǒng)計時半角字符計為“1”占锯，全角字符計為“2”。
• CONCATENATE(Text1缩筛，Text……)：將多個字符文本或單元格中的數(shù)據(jù)連接在一起消略，顯示在一個單元格中。效果和 & 基本相同
  如果參數(shù)不是引用的單元格瞎抛，且為文本格式的艺演，請給參數(shù)加上英文狀態(tài)下的雙引號.
• TRIM(text): 除了單詞之間的單個空格外，清除文本中所有的空格婿失。在從其他應用程序中獲取帶有不規(guī)則空格的文本時钞艇，可以使用函數(shù)TRIM。
• CLEAN(text): 刪除字符串中不能打印的字符.
• REPT(text,num): 重復text內(nèi)容num次.
• LEFT(text,num_chars)：從一個文本字符串的第一個字符開始豪硅，截取指定數(shù)目的字符哩照。可參考RIGHT.另有LEFTB
• RIGHT(text,num_chars)：從一個文本字符串的最后一個字符開始懒浮，截取指定數(shù)目的字符飘弧。另有RIGHTB
  參數(shù)說明：text代表要截字符的字符串；num_chars代表給定的截取數(shù)目砚著。** Num_chars參數(shù)必須大于或等于0次伶，如果忽略，則默認其為1稽穆；如果num_chars參數(shù)大于文本長度冠王，則函數(shù)返回整個文本。
• MID(text,start_num,num_chars)：從一個文本字符串的指定位置開始舌镶，截取指定數(shù)目的字符柱彻。另有MIDB
  text代表一個文本字符串豪娜；start_num表示指定的起始位置；num_chars表示要截取的數(shù)目哟楷。
• EXACT(text1,text2): 判斷兩個字符串是否完全一致(不包括顯示格式).區(qū)分大小寫.返回對錯.
• FIND(findtext,intext[,start]): 查找在intext中的findtext的位置,區(qū)分大小寫.返回位置.第三參數(shù)指定開始查找的位置(只能大于0),默認1.找不到返回錯誤值#VALUE!.
• SEARCH(findtext,intext,[start_num]): 查找在intext中的findtext的位置,不區(qū)分大小寫.返回位置.和FIND類似但不區(qū)分大小寫,而且findtext部分支持通配符,FIND不支持.
• REPLACE(oldtext, startnum, numchars, newtext): oldtext中startnum起將numchars個字符替換為newchars.即startnum, numchars定義出替換的地方,newtext為替換內(nèi)容.
• SUBSTITUTE(text, oldtext, newtext, [instance_num]):查找text中的oldtext并替換為newtext.第四參數(shù)為替換最大次數(shù),否則全部替換.

轉換格式

• T(text): 函數(shù)轉為文本 (http://www.360doc.com/content/13/0107/15/83610_258773240.shtml)
• TEXT(value,format_text)：根據(jù)指定的數(shù)值格式將相應的數(shù)字轉換為文本形式瘤载。Text
• VALUE(text): 將字符串轉為數(shù)值,包括數(shù)字,日期時間格式.若不是這些格式會返回錯誤#VALUE!. value代表需要轉換的數(shù)值或引用的單元格；format_text為指定文字形式的數(shù)字格式卖擅。如保存有數(shù)值1280.45鸣奔，輸入公式：=TEXT(B1, “1280.45”.
• LOWER(text): 將一個文字串中的所有大寫字母轉換為小寫字母。
• UPPER(text): 將文本所有字母轉換成大寫形式.
• PROPER(text): 將文字串的首字母及任何非字母字符之后的首字母轉換成大寫惩阶。將其余的字母轉換成小寫挎狸。
• JIS(text): 將字符串中的半角（單字節(jié)）英文字母或片假名更改為全角（雙字節(jié)）字符。
• ASC(text): 將字符串中的全角（雙字節(jié)）英文字母或片假名更改為半角（單字節(jié)）字符断楷。
• WIDECHAR(text): 單字節(jié)字符轉為雙字節(jié)字符.
• CHAR(num): 將ascii數(shù)字轉為字符.
• CODE(text):　返回字符串中第一個字符的數(shù)字代碼伟叛，char的逆運算.
• ENCODEURL(text): 將網(wǎng)址轉為url串,尤其中文處理.
• DOLLAR(value,decimal): 將數(shù)值轉為相應小數(shù)點decimal位的美元格式,會進行四舍五入.如 $1.23
• FIXED(value,decimal[,no_commas]): 將數(shù)組四舍五入到指定小數(shù)位數(shù).以文本形式返回結果.第三個參數(shù)若TRUE是不輸出三位數(shù)輸出時的逗號(默認false).

查找/定位數(shù)據(jù)

• MATCH(查找值,查找的連續(xù)區(qū)域,匹配方式)：返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應位置。
  如果匹配方式為-1脐嫂，查找大于或等于查找值的最小數(shù)值，此時查找區(qū)域必須按降序排列; 若為1紊遵，查找小于或等于查找值的最大數(shù)值账千，此時查找區(qū)域必須按升序排列; 若為0，查找等于查找值 的第一個數(shù)值暗膜，查找區(qū)域可以按任何順序排列匀奏；如果省略match_type，則默認為1学搜。查找區(qū)域只能為一列或一行娃善。
• INDEX(array,行號,列號)：返回列表或數(shù)組中的元素值，此元素由行序號和列序號的索引值進行確定瑞佩。
  Array代表單元格區(qū)域或數(shù)組常量聚磺；如果省略行號，則必須有列號炬丸√鼻蓿可以用match函數(shù)用來定位某行.此處的行序號和列序號是相對于所引用的單元格區(qū)域而言的，不是Excel工作表中的行或列序號稠炬。
  index(范圍2, match(值,范圍1)) 可以查找在范圍1的某列中某值的位置并返回該位置別的行的相應值
• OFFSET(reference, rows, cols, [height], [width]): 對某個單元格進行偏移(下右為正值,上左為負值)獲取偏移后單元格的值.最后兩個參數(shù)是引用的行高和列寬(ref可以是range,默認行列數(shù)和range一致,但也可以另外指定,此時會返回指定大小的range).
• INDIRECT(字符串):將字符串轉換為相應的位置.
  如表示某格,原來是’sheet名’!A6 這樣, 可以用INDIRECT(“‘8-“ & B10 & “’ ! A6”) 去引用,假設B10儲存了部分sheet名,8-是公有sheet名.indirect 函數(shù)只能引用打開的工作薄.
• COLUMN(reference)：顯示所引用單元格的列標號值焕阿。reference為引用的單元格,不輸入為當前列.
  如果在B11單元格中輸入公式：=COLUMN()，顯示出2.
• ROW(reference): 返回行標號值.
• COLUMNS(reference)：顯示區(qū)域的列數(shù)
• ROWS(reference): 返回區(qū)域的行數(shù).
• Hyperlink(link,name): 超鏈接,變量1是連接變量2是顯示下劃線名字. 關于自動超鏈接:在excel選項中找到自動更正選項-> 自動套用格式, 勾選替換為超鏈接. 點擊超鏈接的cell, 然后在數(shù)值欄處enter一下就OK

功能類

邏輯類

• IF(邏輯判斷表達式,正確時的值[,錯誤時的值])：根據(jù)對指定條件的邏輯判斷的真假結果首启，返回相對應的內(nèi)容暮屡。
  邏輯判斷表達式如”C10>80”;正確時的值表示當判斷條件為邏輯“真”時的顯示內(nèi)容，如果忽略返回“TRUE”毅桃；錯誤時的值表示當判斷條件為邏輯“假”時的顯示內(nèi)容褒纲，如果忽略返回“FALSE”准夷。
• OR(logical1,logical2, …): 返回所有值的OR邏輯。
• AND(logical1,logical2, …): 返回所有值的AND邏輯外厂。
• NOT(logical): 取反返回邏輯值. 如果指定的邏輯條件參數(shù)中包含非邏輯值時冕象，則函數(shù)返回錯誤值“#VALUE!”或“#NAME”。
• ISERROR(value)：用于測試函數(shù)式返回的數(shù)值是否有錯汁蝶。如果有錯渐扮，該函數(shù)返回TRUE，反之返回FALSE掖棉。
  Value表示需要測試的值或表達式墓律。此函數(shù)通常與IF函數(shù)配套使用，如果公式為：=IF(ISERROR(A35/B35),”“,A35/B35)幔亥，如果B35為空或“0”耻讽，則相應的單元格顯示為空，反之顯示A35/B35的結果帕棉。
• ISERR(value): 是否任意錯誤值(除去#N/A). ISERROR則包括#N/A
• ISEVEN/ISODD(value): 是否偶數(shù)/基數(shù)
• ISNUMBER/ISTEXT/ISNONTEXT/ISLOGICAL(value): 是否數(shù)字/文本/非文本(空單元格也是TRUE)/邏輯型
• ISBLANK/ISREF/ISNA(value): 是否空單元格/引用/錯誤值#N/A(不存在)

時間日期相關

serial_number是日期格式,例如”2013-8-20”.

• DATE(year,month,day)：給出指定數(shù)值的日期(返回日期格式)针肥。 year為指定的年份數(shù)值（小于9999）；month為指定的月份數(shù)值（可以大于12）香伴；day為指定的天數(shù).(超過公歷值會進位,例如13月=下年1月)
• NOW()：給出當前系統(tǒng)日期和時間慰枕。該函數(shù)不需要參數(shù)。
  顯示出來的日期和時間格式即纲，可以通過單元格格式進行重新設置具帮。如果系統(tǒng)日期和時間發(fā)生了改變，只要按一下F9功能鍵低斋，即可讓其隨之改變蜂厅。
• YEAR(serial_number)：求出指定日期或引用單元格中的日期的年份。(年,y)
• MONTH(serial_number)：求出指定日期或引用單元格中的日期的月份膊畴。(月,m)
• DAY(serial_number)：求出指定日期或引用單元格中的日期的天數(shù)掘猿。(日,d)
  如果是給定的日期，請包含在英文雙引號中唇跨。
• DATEDIF(data1,data2,unit)：計算返回兩個日期參數(shù)的差值术奖。
  unit為”y”,”m”,”d”,即指明相減的單位為年/月/日. 這是Excel中的一個隱藏函數(shù)，在函數(shù)向導中是找不到的轻绞，可以直接輸入使用采记，對于計算年齡、工齡等非常有效政勃。

</article>