홈으로

Excel函数指南

轻松查找和学习常用的Excel函数。按数学、文本、日期和逻辑函数等各种类别进行整理。

📊 完整的Excel函数指南

工作中常用的Excel函数按类别进行整理。查看每个函数的语法、参数和示例，并复制到剪贴板以便立即使用。

SUM

描述

计算数字的总和。

语法

SUM(number1, [number2], ...)

参数

  • number1*:第一个数字或范围
  • number2:附加数字或范围（可选）

示例

=SUM(A1:A10)

A1到A10的总和

AVERAGE

描述

计算数字的平均值。

语法

AVERAGE(number1, [number2], ...)

参数

  • number1*:第一个数字或范围
  • number2:附加数字或范围（可选）

示例

=AVERAGE(A1:A10)

A1到A10的平均值

COUNT

描述

统计包含数字的单元格数量。

语法

COUNT(value1, [value2], ...)

参数

  • value1*:第一个值或范围
  • value2:附加值或范围（可选）

示例

=COUNT(A1:A10)

A1到A10中包含数字的单元格数量

MAX

描述

Returns the largest value.

语法

MAX(number1, [number2], ...)

参数

  • number1*:First number or range
  • number2:Additional numbers or ranges (optional)

示例

=MAX(A1:A10)

Maximum value from A1 to A10

MIN

描述

Returns the smallest value.

语法

MIN(number1, [number2], ...)

参数

  • number1*:First number or range
  • number2:Additional numbers or ranges (optional)

示例

=MIN(A1:A10)

Minimum value from A1 to A10

ROUND

描述

Rounds a number to specified digits.

语法

ROUND(number, num_digits)

参数

  • number*:number
  • num_digits*:num_digits

示例

=ROUND(3.14159, 2)

3.14

CONCATENATE

描述

Joins several text strings into one.

语法

CONCATENATE(text1, [text2], ...)

参数

  • text1*:text1
  • text2:text2

示例

=CONCATENATE(A1, " ", B1)

A1 and B1 joined with space

LEFT

描述

Returns specified number of characters from the left.

语法

LEFT(text, [num_chars])

参数

  • text*:Source text
  • num_chars:Number of characters to extract (default: 1)

示例

=LEFT(A1, 5)

Left 5 characters of A1

RIGHT

描述

Returns specified number of characters from the right.

语法

RIGHT(text, [num_chars])

参数

  • text*:Source text
  • num_chars:Number of characters to extract (default: 1)

示例

=RIGHT(A1, 3)

Right 3 characters of A1

MID

描述

Returns specified number of characters from the middle.

语法

MID(text, start_num, num_chars)

参数

  • text*:Source text
  • start_num*:Starting position
  • num_chars*:Number of characters to extract

示例

=MID(A1, 3, 5)

5 characters from position 3 of A1

LEN

描述

Returns the number of characters in text.

语法

LEN(text)

参数

  • text*:Text to count characters

示例

=LEN(A1)

Number of characters in A1

UPPER

描述

Converts text to uppercase.

语法

UPPER(text)

参数

  • text*:Text to convert

示例

=UPPER(A1)

A1 converted to uppercase

LOWER

描述

Converts text to lowercase.

语法

LOWER(text)

参数

  • text*:Text to convert

示例

=LOWER(A1)

A1 converted to lowercase

TODAY

描述

Returns current date.

语法

TODAY()

示例

=TODAY()

Today's date

NOW

描述

Returns current date and time.

语法

NOW()

示例

=NOW()

Current date and time

YEAR

描述

Extracts the year from a date.

语法

YEAR(serial_number)

参数

  • serial_number*:Date value

示例

=YEAR(A1)

Year of A1 date

MONTH

描述

Extracts the month from a date.

语法

MONTH(serial_number)

参数

  • serial_number*:Date value

示例

=MONTH(A1)

Month of A1 date

DAY

描述

Extracts the day from a date.

语法

DAY(serial_number)

参数

  • serial_number*:Date value

示例

=DAY(A1)

Day of A1 date

DATEDIF

描述

Calculates the difference between two dates.

语法

DATEDIF(start_date, end_date, unit)

参数

  • start_date*:Start date
  • end_date*:End date
  • unit*:Unit (Y, M, D, etc.)

示例

=DATEDIF(A1, B1, "Y")

Years between A1 and B1

IF

描述

根据条件返回不同的值。

语法

IF(logical_test, [value_if_true], [value_if_false])

参数

  • logical_test*:要测试的条件
  • value_if_true:条件为真时的值
  • value_if_false:条件为假时的值

示例

=IF(A1>10, "Large", "Small")

如果A1>10则为'大'，否则为'小'

AND

描述

Returns TRUE if all arguments are TRUE.

语法

AND(logical1, [logical2], ...)

参数

  • logical1*:First condition
  • logical2:Additional conditions (optional)

示例

=AND(A1>5, B1<10)

TRUE if A1>5 AND B1<10

OR

描述

Returns TRUE if any argument is TRUE.

语法

OR(logical1, [logical2], ...)

参数

  • logical1*:First condition
  • logical2:Additional conditions (optional)

示例

=OR(A1>5, B1<10)

TRUE if A1>5 OR B1<10

NOT

描述

Reverses the logical value.

语法

NOT(logical)

参数

  • logical*:Logical value

示例

=NOT(A1>10)

TRUE if A1 is NOT greater than 10

VLOOKUP

描述

在表格中垂直查找并返回值。

语法

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

参数

  • lookup_value*:要查找的值
  • table_array*:要搜索的表格范围
  • col_index_num*:要返回的列号
  • range_lookup:精确匹配（FALSE）或近似匹配（TRUE）

示例

=VLOOKUP(A1, C1:E10, 2, FALSE)

在C1:E10中查找A1并返回第2列的值

HLOOKUP

描述

Searches horizontally in a table and returns a value.

语法

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

参数

  • lookup_value*:Value to search for
  • table_array*:Table range to search
  • row_index_num*:Row number to return
  • range_lookup:Exact match (FALSE) or approximate (TRUE)

示例

=HLOOKUP(A1, C1:G3, 2, FALSE)

Find A1 in C1:G3 and return 2nd row value

INDEX

描述

Returns a value from a specific position in an array.

语法

INDEX(array, row_num, [column_num])

参数

  • array*:Data array
  • row_num*:Row number
  • column_num:Column number (optional)

示例

=INDEX(A1:C10, 3, 2)

Value at row 3, column 2 of A1:C10

MATCH

描述

Finds the position of a value in an array.

语法

MATCH(lookup_value, lookup_array, [match_type])

参数

  • lookup_value*:Value to find
  • lookup_array*:Array to search
  • match_type:Match type (0, 1, -1)

示例

=MATCH(A1, B1:B10, 0)

Position of A1 value in B1:B10

COUNTIF

描述

Counts cells that meet a condition.

语法

COUNTIF(range, criteria)

参数

  • range*:Range to check
  • criteria*:Condition

示例

=COUNTIF(A1:A10, ">5")

Count of values greater than 5 in A1:A10

SUMIF

描述

Sums cells that meet a condition.

语法

SUMIF(range, criteria, [sum_range])

参数

  • range*:Range to check condition
  • criteria*:Condition
  • sum_range:Range to sum (optional)

示例

=SUMIF(A1:A10, ">5")

Sum of values greater than 5 in A1:A10

AVERAGEIF

描述

Averages cells that meet a condition.

语法

AVERAGEIF(range, criteria, [average_range])

参数

  • range*:Range to check condition
  • criteria*:Condition
  • average_range:Range to average (optional)

示例

=AVERAGEIF(A1:A10, ">5")

Average of values greater than 5 in A1:A10

STDEV

描述

Calculates standard deviation (sample).

语法

STDEV(number1, [number2], ...)

参数

  • number1*:First number or range
  • number2:Additional numbers or ranges (optional)

示例

=STDEV(A1:A10)

Standard deviation of A1 to A10

PMT

描述

Calculates loan payment amount.

语法

PMT(rate, nper, pv, [fv], [type])

参数

  • rate*:Interest rate
  • nper*:Total number of payments
  • pv*:Present value (loan principal)
  • fv:Future value (optional)
  • type:Payment timing (optional)

示例

=PMT(5%/12, 60, 100000)

Monthly payment for 5% annual rate, 5-year, $100,000 loan

FV

描述

Calculates future value of investment.

语法

FV(rate, nper, pmt, [pv], [type])

参数

  • rate*:Interest rate
  • nper*:Total periods
  • pmt*:Periodic payment
  • pv:Present value (optional)
  • type:Payment timing (optional)

示例

=FV(8%/12, 120, -1000)

Future value of $1000 monthly savings at 8% annual rate for 10 years

PV

描述

Calculates present value of investment.

语法

PV(rate, nper, pmt, [fv], [type])

参数

  • rate*:Interest rate
  • nper*:Total periods
  • pmt*:Periodic payment
  • fv:Future value (optional)
  • type:Payment timing (optional)

示例

=PV(8%/12, 120, 1000)

Present value of $1000 monthly payments at 8% annual rate for 10 years