excel常用函數匯總-excel-PHP中文網

首頁

專題

excel

excel常用函數匯總

藏色散人

Jun 13, 2019 pm 01:29 PM

excel

excel常用函數匯總

excel常用函數匯總

#第一類：文字處理函數

##●● Trim函數：

作用：除了單字之間的單一空格之外，移除文字中的所有空格。

語法：TRIM(text)，Text為必要項，為要移除空格的文字。

● Concatenate函數：

作用：將兩個或多個文字字串連結為一個字串。

語法：CONCATENATE(text1, [text2], ...)，至少包含一個項目，最多255個項目，最多支援8192個字符，項目可以是文字值、數字、或儲存格參考。

說明：可以利用連接符號&實現相同的功能。

● Replace函數：

作用：將特定位置的字串替換為不同的文字字元。

語法：REPLACE(old_text, start_num, num_chars, new_text)，old_text為需要替換的文本，start_num替換字元的位置，num_chars利用new_text替換的字元數，new_text要替換old_text的新文本。

● Substitue函數：

作用：在某一文字字串中替換指定的文字

和Replace區別：Substitue根據文字內容進行替換，Replace根據字元位置進行替換。

語法：SUBSTITUTE(text, old_text, new_text, [instance_num])，text為包含需要替換的文本，old_text為需要替換的文本，new_text為替換old_text的文本，instance_num為可選參數，指定了數字則只替換相應順序的old_text，否則全部替換。

● Left函數：

作用：從文字字串的第一個字元開始傳回指定個數的字元。

LEFT(text, [num_chars])，text包含要提取的字符，num_chars為指定要提取的自負數量，必須≥0，如果大於文本長度，則返回全部文本，如果省略則假定其值為1。

● Right函數：

用法同Left，只是取數方向相反，從右邊開始取數。

● Mid函數：

作用：從指定位置開始提取特定數目的字元

語法：MID(text, start_num, num_chars)，text包含要提取字元的文本，start_num文本中要提取第一個字元的位置，num_chars希望提取的字元個數。

相關文章推薦：1.
Excel函數所有公式總結 2.
excel財務函數3 .
excel統計函數4.
excel字串函數

#第二類：資訊回饋函數

● Exact函數：

作用：比較兩個文字字串，如果它們完全相同，則傳回TRUE，否則傳回FALSE。函數 EXACT 區分大小寫，但忽略格式上的差異。使用 EXACT 可以檢驗在文件中輸入的文字。

語法：EXACT(text1, text2)，text1和text2兩個需要比較的字串。

● Len函數：

作用：傳回文字中字元的數，一般和其他函數配合使用。

語法：LEN(text)，text為需要查詢長度的文本，空格將作為字元進行計數。

● IS函數：

作用：此類函數可檢驗指定值並根據結果傳回 TRUE 或 FALSE。在對某一值執行計算或執行其他操作之前，可以使用 IS 函數取得該值的相關資訊。

語法：ISBLANK(value)，ISERR(value)，ISERROR(value)，ISLOGICAL(value)，ISNA(value)，ISNONTEXT(value)，ISNUMBER(value)，ISREF(value)，ISTEXT( value)。 value指的是要測試的值。參數 value 可以是空白（空白儲存格）、錯誤值、邏輯值、文字、數字、參考值，或引用要測試的以上任意值的名稱。

第三類：尋找引用函數

● Vlookup函數：

作用：在表格區域中按行尋找對應內容。

語法：VLOOKUP（要尋找的值，要在其中尋找值的區域，區域中包含傳回值的列號，精確匹配或近似匹配 – 指定為 0/FALSE 或 1/TRUE）。

注意事項：要尋找的值需要永遠位於所在區域的第一列

● Hlookup函數：

作用：在表格中按列尋找對應內容。

語法：HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])，參數和VLOOKUP相對應，第三個參數為行號，VLOOKUP第三個參數為列號。

● Index函數：

作用：傳回表格或區域中的值或值的參考

語法：INDEX(array, row_num, [column_num])，array單元格區域或陣列常數，row_num為陣列的某行，column_num為陣列中的某列。除此之外還有引用的形式，有興趣的話可以自行百度或使用微軟來幫助。

說明：如果使用參數row_num和column_num，則INDEX函數傳回行列號交叉處的儲存格值；若將row_num設為0，則傳回整列數值的值，對column同樣適用；若要輸入陣列公式，公式輸入完後，需按Ctrl Shift Enter。

● Match函數：

作用：在範圍儲存格中搜尋特定的項，然後傳回該項在此區域中的相對位置。

語法：MATCH(lookup_value, lookup_array, [match_type])，lookup_value要找的值，look_arrary單元格區域，match_type=1或省略，找出≤lookup_value的最大值，lookup_arrary需要升序排列；=0 ，找出完全等於lookup_value的第一個值；=-1，找出≥lookup_value的最小值，lookup_arrary需要降序排列。

說明：MATCH不區分大小寫字母，可以再lookup_value使用？或*，？匹配任意單個字符，*匹配任意一串字符，如果要查找實際的問號或星號，則需要在字符前添加~。

● Search函數：

作用：函數可在第二個文字字串中尋找第一個文字字串，並傳回第一個文字字串的起始位置的編號，該編號從第二個文字字串的第一個字元算起。

語法：SEARCH(find_text,within_text,[start_num])，find_text為需要尋找的文本，with_text包含要尋找的文本，start_num為從開始搜尋的字元編號。

說明：SEARCH不區分大小寫，FIND函數區分大小寫。 SEARCH支援使用萬用字元？和*，而FIND不支持。

● Find函數：

FIND函數區分大小寫，且不能使用通配符，其他用法和SEARCH函數一致。

● Choose函數：

作用：根據參數傳回數值參數清單中的數值。

語法：CHOOSE(index_num, value1, [value2], ...)，index_num指選定的數值參數，介於1到254之間，index_num為1則返回value1，為2則返回value2...

範例：SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10))=SUM(B1:B10)

● Row / Column函數：

ROW([reference])傳回引用的行號，COLUMN([reference])傳回引用的列號，如果reference省略，則傳回該函數所在位置的行/列號。

● Offset函數：

作用：傳回儲存格或儲存格區域中指定行數和列數的區域的參考。

語法：OFFSET(reference, rows, cols, [height], [width])，reference為偏移量的參考位置，rows偏移的行數，cols偏移的列數，height和width為指定傳回的行高和列寬。

說明：引用的位置不能超過工作表邊緣；省略height和width，則其高寬和reference相同。

● Indirect函數：

作用：傳回文字字串指定的參考

語法：INDIRECT(ref_text, [a1])，ref_text對單元格的引用，如果對另一個工作簿引用，則引用的工作簿必須已開啟。 a1為true或省略，則為A1樣式，否則為R1C1樣式。

● Address函數：

作用：根據指定行號和列號獲得工作表中的某個單元格的位址，如ADDRESS(2,3) 傳回 $C$2。

語法：ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])，row_num為行號，column_num為列號，abs_num可以用來指定傳回的參考類型，=1或省略返回絕對值；=2返回絕對行號，相對列號；=3返回相對行號，絕對列號；=4返回相對值。 a1的用處同INDIRECT函數中參數介紹。 sheet_text用於指定外部引用的工作表的名稱。

第四類：邏輯運算函數

● If函數：

作用：對值和期待值進行邏輯比較

語法：IF(logical_test, value_if_true, [value_if_false])，當logical_test成立時，傳回value_if_true，當logical_test不成立時，傳回value_if_false。 IF函數最多巢狀64個。

● Iferror函數：

作用：如果公式的計算結果錯誤，則傳回您指定的值；否則傳回公式的結果。使用 IFERROR 函數可擷取和處理公式中的錯誤。

語法：IFERROR(value, value_if_error)，value為要檢查的值，value_if_error為value為錯誤時傳回的值，錯誤型別包括：#N/A、#VALUE!、#REF!、 #DIV/0!、#NUM!、 #NAME? 或#NULL!。

● Ifna函數：

作用：如果公式傳回錯誤值 #N/A，則結果會傳回您指定的值；否則傳回公式的結果。

語法：IFNA(value, value_if_na)，和IFERROR語法相同，只是檢查的錯誤值範圍不同。

● And函數：

用於確定測試中的所有條件是否均為 TRUE。

● Or函數：

用於確定測試中的任一條件是否為TRUE。

● Not函數：

對其參數的邏輯求反。

第五類：數學統計函數

● Sum函數：

作用：可以將單一值、單元格引用或是區域相加，或將三者的組合相加。

語法：SUM(number1,[number2],...)

● Sumif函數：

作用：對符合條件的值求和，例如，對B2~B25單元格大於5的值求和，可以使用公式=SUMIF(B2:B25,">5")

#語法：SUMIF(range, criteria,[sum_range])，range為需要計算的區域，字元數不能超過255個；criteria求和的條件，可以使用通配符？和*；sun_range為可選條件，指定實際求和的區域。

影片：微軟SUMIF函數訓練課程

● Sumifs函數：

作用：用於計算其滿足多個條件的全部參數的總量。

語法：SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...) ，sum_range要求和的區域，criteria_range1為條件區域1，criteria1為對區域1進行條件限定的條件1 ，之後的參數以此類推。

● Sumproduct函數：

作用：在給定的幾組陣列中，將陣列間對應的元素相乘，並傳回乘積之和。

語法：SUMPRODUCT(array1, [array2], [array3], ...)，array1 / array2...為對其對應元素進行相乘並求和的幾組數組參數，數組參數需要具有相同的維數，非數值型數組元素將作為0處理。

● Count函數：

作用：計算包含數字的單元格數數以及參數清單中數字的數量。

語法：COUNT(value1, [value2], ...)，value1為要計算數字個數的第一項、單元格應用或區域，value2可選參數，作用同value1。

說明：參數為數字、日期、代表數字的文字（如「1」）、邏輯值和直接鍵入參數清單中的數字將被計算在內。

● Countif函數：

作用：用於統計滿足某個條件的單元格的數量

語法：COUNTIF(range,criteria)，類似SUMIF初級使用方法

● Countifs函數：

作用：將條件套用於跨多個區域的儲存格，然後統計滿足所有條件的次數。

語法：COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2],…)，criteria_range1為條件區域1，criteria1為對criteria_range1指定的限定條件1，其他以此類推。

● Counta函數：

作用：計算不為空的單元格的數量。

語法：COUNTA(value1, [value2], ...)，value1表示要計數區域，value2可選參數，作用同value1。

● Countblank函數：

COUNTBLANK（range）計算選取區域的空白單元格數。

● Max / Min函數：

MAX(number1, [number2], ...)，MIN(number1, [number2], ...)計算選取區域的最大值和最小值。

● Rank函數：

作用：傳回一列數字的數字排位，數字的排位是其相對於清單中其他值的大小。

語法：RANK(number,ref,[order])，number需要排位的數字；ref數字排列的陣列；order可選參數，=0或省略降序排列，=不為零升序排列。

● Rand函數：

作用：傳回大於等於 0 且小於 1 的均勻分佈隨機實數，每次計算工作表時都會傳回一個新的隨機實數。

語法：RAND()，如要產生a與b之間的隨機實數，可用公式RAND()*(b-a) a

● Randbetween函數：

#作用：傳回位於兩個指定數之間的一個隨機整數。每次計算工作表時都會傳回一個新的隨機整數。

語法：RANDBETWEEN(bottom, top)，bottom將傳回的最小整數，top將傳回的最大整數。例如RANDBETWEEN(1,100)=RAND()*99 1將傳回1-100之間的隨機數。

● Average函數：

作用：傳回參數的平均值（算術平均值）。

語法：AVERAGE(number1, [number2], ...)，number1為求平均值的區域，number2為可選參數，作用等同number1。

● Subtotal函數：

作用：傳回清單或資料庫中的分類總和。

語法：SUBTOTAL(function_num,ref1,[ref2],...)，function_num為數字1-11或101-111，用於指定要為分類匯總使用的函數。如果使用1-11，將包括手動隱藏的行；如果使用101-111，則排除手動隱藏的行；始終排除已篩選掉的單元格。

第六類：日期時間函數

● Datedif函數：

作用：計算兩個日期間隔的年數、月數、天數，常用於計算年齡的公式中。

語法：DATEDIF(start_date,end_date,unit)，start_date表示起始日期，end_date表示結束日期。日期值的輸入方式有多種：引號的文字字串（例如："2001/1/30"）、序號（例如36921，在商用1900 日期系統時表示2001 年1 月30 日）或其他公式或函數的結果（例如DATEVALUE("2001/1/30")）。

說明：日期儲存為可用於計算的序號。預設情況下，1899 年 12 月 31 日的序號是 1，而 2008 年 1 月 1 日的序號是 39448，這是因為它距離 1900 年 1 月 1 日有 39448 天。

● Networkdays函數：

NETWORKDAYS(start_date, end_date, [holidays])傳回兩個日期之間的工作日數。

● Now函數：

作用：傳回目前的日期和時間，每次開啟工作表時間都會更新。

語法：NOW()，無參數。

說明：1.Excel 可將日期儲存為序號，以便可以在計算中使用它們。預設情況下，1900 年 1 月 1 日的序號為 1，2008 年 1 月 1 日的序號為 39,448，這是因為它距 1900 年 1 月 1 日有 39,447 天。 2.序號中小數點右邊的數字表示時間，左邊的數字表示日期。例如，序號 0.5 表示時間為中午 12:00。3.NOW 函數的結果僅在計算工作表或運行含有該函數的巨集時才改變。

● Today函數：

作用：傳回目前日期，在開啟工作簿自動更新日期，常用於計算年齡等。

語法：TODAY()，無參數。

● Weekday函數：

作用：傳回對應日期為一週中第幾天

語法：WEEKDAY(serial_number,[return_type])，Serial_number 一個序號，代表嘗試尋找的那一天的日期。應使用 DATE 函數輸入日期，或將日期輸入為其他公式或函數的結果。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。 Return_type可選參數，用於決定傳回值類型的數字。

● Weeknum函數：

作用：傳回日期的周數

語法：WEEKNUM(serial_number,[return_type])，Serial_number 必要。代表一週中的日期。應使用 DATE 函數輸入日期，或將日期輸入為其他公式或函數的結果。例如，使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。 Return_type可選參數，決定星期從哪一天開始，預設值為 1。

● Date函數：

作用：將三個獨立的值合併為一個日期

語法：DATE(year,month,day)，year年，month月，day天

● Year / Month / Day函數：

參數為日期，分別可以得到年月日資訊。

● Hour / Minute / Second函數：

參數為時間，分別可得到小時、分鐘、秒。

● Time函數：

將三個獨立的值合併為一個時間，功能類似DATE函數。

第七類：格式顯示函數

● Text函數：

作用：將數字以指定方式顯示，常和其他函數搭配使用，例如合併文字數值，需要數值以特定的格式顯示，這時候可以使用TEXT函數。