EXCEL 常用函数公式
免费Excel入门视频课程：
[全套】Excel零基础入门进阶到函数，Excel自学教程从小白到高手超详细实操教程
https://www.bilibili.com/video/BV184411C7Ci?p=35
Logical
Test multiple conditions with AND
logical1logical2...
Generate the logical value FALSE
Test for a specific condition
logical_testvalue_if_truevalue_if_false
Trap and handle errors
valuevalue_if_error
Trap and handle #N/A errors
valuevalue_if_na
Test multiple conditions, return first true
test1value1test2, value2...
Reverse arguments or results
logical
Test multiple conditions with OR
logical1logical2...
Match multiple values, return first match
expressionval1/result1val2/result2...default
Generate the logical value TRUE
Perform exclusive OR
logical1logical2...
Date and time
Create a date with year, month, and day
yearmonthday
Get days, months, or years between two dates
start_dateend_dateunit
Convert a date in text format to a valid date
date_text
Get the day as a number (1-31) from a date
date
Get days between dates
end_datestart_date
Get days between 2 dates in a 360-day year
start_dateend_datemethod
Shift date n months in future or past
start_datemonths
Get last day of month n months in future or past
start_datemonths
Get the hour as a number (0-23) from a Time
serial_number
Get ISO week number for a given date
date
Get minute as a number (0-59) from time
serial_number
Get month as a number (1-12) from a date
serial_number
Get the number of working days between two dates
start_dateend_dateholidays
Get work days between two dates
start_dateend_dateweekendholidays
Get the current date and time
Get the Second as a number (0-59) from a Time
serial_number
Create a time with hours, minutes, and seconds
hourminutesecond
Get a valid time from a text string
time_text
Get the current date
Get the day of the week as a number
serial_numberreturn_type
Get the week number for a given date
serial_numreturn_type
Get a date n working days in the future or past
start_datedaysholidays
Get date n working days in future or past
start_datedaysweekendholidays
Get the year from a date
date
Get the fraction of a year between two dates
start_dateend_datebasis
Lookup and reference
Create a cell address from a row and column number
row_numcol_numabs_numa1sheet
Get the number of areas in a reference.
reference
Get a value from a list based on position
index_numvalue1value2...
Get the column number of a reference.
reference
Get the number of columns in an array or reference.
array
Get the formula in a cell
reference
Retrieve data from a pivot table in a formula
data_fieldpivot_tablefield1, item1...
Look up a value in a table arranged horizontally
lookup_valuetable_arrayrow_indexrange_lookup
Create a clickable link.
link_locationfriendly_name
Get a value in a list or table based on location
arrayrow_numcol_numarea_num
Create a reference from text
ref_texta1
Look up a value in a one-column range
lookup_valuelookup_vectorresult_vector
Get the position of an item in an array
lookup_valuelookup_arraymatch_type
Create a reference offset from given starting point
referencerowscolsheightwidth
Get the row number of a reference
reference
Get the number of rows in an array or reference.
array
Flip the orientation of a range of cells
array
Lookup a value in a table by matching on the first column
valuetablecol_indexrange_lookup
Text
Get a character from a number
number
Strip non-printable characters from text
text
Get the code for a character
text
Join text values without delimiter
text1text2...
Join text together
text1text2text3...
Convert a number to text in currency format
numberdecimals
Compare two text strings
text1text2
Get the location of text in a string
find_textwithin_textstart_num
Format number as text with fixed decimals
numberdecimalsno_commas
Extract text from the left of a string
textnum_chars
Get the length of text.
text
Convert text to lower case
text
Extract text from inside a string
textstart_numnum_chars
Convert text to number with custom separators
textdecimal_separatorgroup_separator
Capitalize the first letter in each word
text
Replace text based on location
old_textstart_numnum_charsnew_text
Repeat text as specified
textnumber_times
Extract text from the right of a string
textnum_chars
Get the location of text in a string
find_textwithin_textstart_num
Replace text based on content
textold_textnew_textinstance
Convert a number to text in a number format
valueformat_text
Join text values with a delimiter
delimiterignore_emptytext1text2...
Remove extra spaces from text
text
Get Unicode character by number
number
Get number from Unicode character
text
Convert text to upper case
text
Convert text to a number
text
Dynamic array
Apply function to column
arraylambda
Apply function to row
arraylambda
Filters range with given criteria
arrayincludeif_empty
Create custom function
parameter...calculation
Assign variables inside formula
name1value1name2/value2...result
Create array with calculated values
rowscolumnslambda
Map array to custom function
array1array2...lambda
Get array of random numbers
rowscolumnsminmaxinteger
Reduce an array
initial_valuearraylambda
Scan array and return intermediate results
initial_valuearraylambda
Get array of list of sequential numbers
rowscolumnsstartstep
Get single value with implicit intersection
value
Sorts range or array
arraysort_indexsort_orderby_col
Sorts range or array by column
arrayby_arraysort_orderarray/order...
Extract unique values from range
arrayby_colexactly_once
Lookup values in range or array
lookuplookup_arrayreturn_arraynot_foundmatch_modesearch_mode
Get the position of an item in a list or table
lookup_valuelookup_arraymatch_modesearch_mode
Engineering
Converts a binary number to decimal
number
Converts a binary number to hexadecimal
numberplaces
Converts a binary number to octal
numberplaces
Returns a 'Bitwise And' of two numbers
number1number2
Returns a number shifted left by some number of bits
numbershift_amount
Returns a 'Bitwise Or' of two numbers
number1number2
Returns a number shifted right by some number of bits
numbershift_amount
Returns a 'Bitwise Xor' of two numbers
number1number2
Convert coefficients to complex number
real_numi_numsuffix
Convert measurement units
numberfrom_unitto_unit
Converts a decimal number to binary
numberplaces
Converts a decimal number to hexadecimal
numberplaces
Converts a decimal number to octal
numberplaces
Test two values are equal
number1number2
Converts a hexadecimal number to binary
numberplaces
Converts a hexadecimal number to decimal
number
Converts a hexadecimal number to octal
numberplaces
Get absolute value of complex number
inumber
Get imaginary coefficient of complex number
inumber
Raise complex number to given power
inumbernumber
Get product of complex numbers
inumber1inumber2...
Get real coefficient of complex number
inumber
Get difference between two complex numbers
inumber1inumber2
Get sum of complex numbers
inumber1inumber2...
Financial
Get accrued interest periodic
idfdsdrateparfreqbasiscalc
Get accrued interest at maturity
idsdrateparbasis
Depreciation for accounting period coefficient
costpurchasefirstsalvageperiodratebasis
Depreciation for accounting period
costpurchasefirstsalvageperiodratebasis
Get days from coupon period to settlement date
settlementmaturityfrequencybasis
Get days in coupon period incl settlement date
settlementmaturityfrequencybasis
Get days from settlement date to next coupon date
settlementmaturityfrequencybasis
Get next coupon date after settlement date
settlementmaturityfrequencybasis
Get number of coupons payable
settlementmaturityfrequencybasis
Get previous coupon date before settlement date
settlementmaturityfrequencybasis
Get cumulative interest paid on a loan
ratenperpvstart_periodend_periodtype
Get cumulative principal paid on a loan
ratenperpvstart_periodend_periodtype
Depreciation - fixed-declining balance
costsalvagelifeperiodmonth
Depreciation - double-declining
costsalvagelifeperiodfactor
Get discount rate for a security
settlementmaturityprredemptionbasis
Convert dollar price as fraction to decimal
fractional_dollarfraction
Convert price to fractional notation
decimal_dollarfraction
Get annual duration with periodic interest
settlementmaturitycouponyldfreqbasis
Get effective annual interest rate
nominal_ratenpery
Get the future value of an investment
ratenperpmtpvtype
Get future value of principal compound interest
principalschedule
Get interest rate for fully invested security
settlementmaturityinvestmentredemptionbasis
Get interest in given period
ratepernperpvfvtype
Calculate internal rate of return
valuesguess
Get interest paid for specific period
ratepernperpv
Get Macauley modified duration par value of $100
settlementmaturitycouponyldfreqbasis
Calculate modified internal rate of return
valuesfinance_ratereinvest_rate
Get annual nominal interest rate
effect_ratenpery
Get number of periods for loan or investment
ratepmtpvfvtype
Calculate net present value
ratevalue1value2...
Get price per $100 odd first period
sdmdidfdrateyldredemfreqbasis
Get yield security with odd first period
sdmdidfdrateprredemfreqbasis
Get price per $100 face value with odd last period
sdmdidrateyldredemfreqbasis
Get yield of security with odd last period
sdmdldrateprredemfreqbasis
Get periods required to reach given value
ratepvfv
Get the periodic payment for a loan
ratenperpvfvtype
Get principal payment in given period
ratepernperpvfvtype
Get price per $100 face value - periodic interest
sdmdrateyldredemptionfrequencybasis
Get price per $100 discounted security
sdmddiscountredemptionbasis
Get price per $100 interest at maturity
sdmdidrateyldbasis
Get the present value of an investment
ratenperpmtfvtype
Get the interest rate per period of an annuity
nperpmtpvfvtypeguess
Get amount received at maturity
settlementmaturityinvestmentdiscountbasis
Get equivalent interest rate for growth
nperpvfv
Depreciation - straight-line
costsalvagelife
Depreciation - sum-of-years
costsalvagelifeperiod
Get bond-equivalent yield for a Treasury bill
settlementmaturitydiscount
Get price per $100 Treasury bill
settlementmaturitydiscount
Get yield for a Treasury bill
settlementmaturityprice
Depreciation - double-declining variable
costsalvagelifestartendfactorno_switch
Calculate internal rate of return for irregular cash flows
valuesdatesguess
Calculate net present value for irregular cash flows
ratevaluesdates
Get yield for security that pays periodic interest
sdmdrateprredemptionfrequencybasis
Get annual yield for discounted security
sdmdprredemptionbasis
Get annual yield of security interest at maturity
sdmdidrateprbasis
Information
Get information about a cell
info_typereference
Test for a specific error value
error_val
Get information about current environment
type_text
Test if a cell is empty
value
Test for any error but #N/A
value
Test for any error
value
Test if a value is even
value
Test if cell contains a formula
reference
Test if a value is logical
value
Test for the #N/A error
value
Test for a non-text value
value
Test for numeric value
value
Test if a value is odd
value
Test for a reference
value
Test for a text value
value
Convert a value to a number
value
Create an #N/A error
Get sheet index number
value
Get number of sheets in a reference
reference
Filter text values only
value
Get the type of value in a cell
value
Math
Find the absolute value of a number
number
Return aggregate calculation
function_numoptionsref1ref2
Converts a Roman numerals to an Arabic numerals
roman_text
Convert number to another base.
numberradixmin_length
Round a number up to nearest multiple
numbersignificance
Round a number up to nearest multiple
numbersignificancemode
Round a number up to nearest multiple
numbersignificance
Get number of combinations without repetitions
numbernumber_chosen
Get number of combinations with repetitions
numbernumber_chosen
Convert a number in a different base to a decimal number
numberradix
Round a number up to the next even integer
number
Find the value of e raised to the power of a number
number
Find the factorial of a number
number
Get double factorial of a number
number
Round a number down to the nearest specified multiple
numbersignificance
Round number down to nearest multiple
numbersignificancemode
Round number down to nearest multiple
numbersignificance
Get the greatest common divisor of numbers
number1number2...
Get the integer part of a number by rounding down
number
Get the least common multiple of numbers
number1number2...
Get the natural logarithm of a number
number
Get the logarithm of a number
numberbase
Get the base-10 logarithm of a number
number
Get matrix determinant of given array
array
Get inverse matrix of array
array
Perform matrix multiplication
array1array2
Get the remainder from division
numberdivisor
Round a number to the nearest specified multiple
numbersignificance
Return unit matrix for a given dimension
dimension
Round a number up to the next odd integer
number
Get the value of π
Raise a number to a power
numberpower
Get the product of supplied numbers
number1number2...
Returns the quotient without a remainder.
numeratordenominator
Get a random number between 0 and 1
Get a random integer between two values
bottomtop
Converts numbers to Roman numerals
numberform
Round a number to a given number of digits
numbernum_digits
Round down to given number of digits
numbernum_digits
Round a number up to a given number of digits
numbernum_digits
Get the sign of a number
number
Find the positive square root of a number
number
Get a subtotal in a list or database
function_numref1ref2...
Add numbers together
number1number2number3...
Sum numbers in a range that meet supplied criteria
rangecriteriasum_range
Sum cells that match multiple criteria
sum_rangerange1criteria1range2criteria2...
Multiply, then sum arrays
array1array2...
Get sum of squares of supplied values
number1number2...
Sum of difference of squares in two arrays
array_xarray_y
Get sum of squares in two arrays
array_xarray_y
Sum of squares of differences in two arrays
array_xarray_y
Truncate a number to a given precision
numbernum_digits
Trigonometry
Get the inverse cosine of a value, in radians.
number
Return the inverse sine of a value in radians.
number
Get arctangent of a number
number
Get arctangent from x- and y-coordinates
x_numy_num
Get the cosine of an angle provided in radians.
number
Get hyperbolic cosine of a number
number
Get the cotangent of an angle.
number
Get cosecant of an angle
number
Converts radians to degrees
angle
Converts degrees into radians
angle
Get secant of an angle
number
Get the sine of an angle provided in radians.
number
Get hyperbolic sine of a number.
number
Get the tangent of an angle
number
Statistical
Get sum of squared deviations
number1number2...
Get the average of a group of numbers
number1number2...
Get the average of a group of numbers and text
value1value2...
Get the average of numbers that meet criteria.
rangecriteriaaverage_range
Average cells that match multiple criteria
avg_rngrange1criteria1range2criteria2...
Get binomial distribution probability
number_strialsprobability_scumulative
Get binomial distribution probability
number_strialsprobability_scumulative
Count numbers
value1value2...
Count the number of non-blank cells
value1value2...
Count cells that are blank
range
Count cells that match criteria
rangecriteria
Count cells that match multiple criteria
range1criteria1range2criteria2...
Get sum of squared deviations
number1number2...
Predict value along a linear trend
xknown_yskown_xs
Predict value with a seasonal trend
target_datevaluestimelineseasonalitydata_completionaggregation
Get confidence interval for forecast value at given date
target_datevaluestimelineconfidence_levelseasonalitydata_completionaggregation
Get length of the seasonal pattern
valuestimelinedata_completionaggregation
Get statistical value related to forecasting
valuestimelinestatistic_typeseasonalitydata_completionaggregation
Predict value along a linear trend
xknown_yskown_xs
Get the frequency of values in a data set
data_arraybins_array
Calculate geometric mean
number1number2...
Calculate harmonic mean
number1number2...
Get intercept of linear regression line
known_ysknown_xs
Get nth largest value
arrayk
Get parameters of linear trend
known_ysknown_xsconststats
Get the largest value
number1number2...
Return largest value.
value1value2...
Get maximum value with criteria
max_rangerange1criteria1range2criteria2...
Get the median of a group of numbers
number1number2...
Get the smallest value.
number1number2...
Return smallest value.
value1value2...
Get minimum value with criteria
min_rangerange1criteria1range2criteria2...
Get most frequently occurring number
number1number2...
Get most frequently occurring numbers
number1number2...
Get most frequently occurring number
number1number2...
Get values and areas for the normal distribution
xmeanstandard_devcumulative
Get the inverse of normal cumulative distribution
probabilitymeanstandard_dev
Get the standard normal CDF and PDF.
zcumulative
Get inverse of the standard normal cumulative distribution
probability
Get kth percentile
arrayk
Get kth percentile
arrayk
Get kth percentile
arrayk
Get percentile rank, inclusive
arrayxsignificance
Get percentile rank, exclusive
arrayxsignificance
Get percentile rank, inclusive
arrayxsignificance
Get number of permutations without repetitions
numbernumber_chosen
Get number of permutations with repetitions
numbernumber_chosen
Get the quartile in a data set
arrayquart
Get the quartile in a data set
arrayquart
Get the quartile in a data set
arrayquart
Rank a number against a range of numbers
numberreforder
Rank a number against a range of numbers
numberreforder
Rank a number against a range of numbers
numberreforder
Get skewness of a distribution
number1number2...
Get skewness of a distribution based on population
number1number2...
Get slope of linear regression line
known_ysknown_xs
Get nth smallest value
arrayk
Calculate a normalized value (z-score)
xmeanstandard_dev
Get the standard deviation in a sample
number1number2...
Get standard deviation of population
number1number2...
Get the standard deviation in a sample
number1number2...
Get standard deviation in a sample
number1number2...
Get standard deviation of population
number1number2...
Get standard deviation for a population
number1number2...
Calculate mean excluding outliers
arraypercent
Get variation of a sample
number1number2...
Get variation of population
number1number2...
Get variation of a sample
number1number2...
Get variation of a sample
number1number2...
Get variation of a population
number1number2...
Get variation of a population
number1number2...
Web
Get URL-encoded string
text
Get data from XML with Xpath
xmlxpath
Get data from a web service
url
Database
Get average from matching records
databasefieldcriteria
Count matching records in a database
databasefieldcriteria
Count matching records in a database
databasefieldcriteria
Get value from matching record
databasefieldcriteria
Get max from matching records
databasefieldcriteria
Get min from matching records
databasefieldcriteria
Get product from matching records
databasefieldcriteria
Get standard deviation of sample in matching records
databasefieldcriteria
Get standard deviation of population in matching records
databasefieldcriteria
Get sum from matching records
databasefieldcriteria
Get sample variance for matching records
databasefieldcriteria
Get population variance for matching records
databasefieldcriteria
本文来自博客园，作者：Slashout，转载请注明原文链接：https://www.cnblogs.com/SlashOut/p/15534914.html
