Home » » Daftar Fungsi Excel Berdasarkan Kategori

Daftar Fungsi Excel Berdasarkan Kategori

AbahVsan | 19.06 | 0 comments


Date and time functions

FunctionDescription
DATEReturns the serial number of a particular date
DATEVALUEConverts a date in the form of text to a serial number
DAYConverts a serial number to a day of the month
DAYS360Calculates the number of days between two dates based on a 360-day year
EDATEReturns the serial number of the date that is the indicated number of months before or after the start date
EOMONTHReturns the serial number of the last day of the month before or after a specified number of months
HOURConverts a serial number to an hour
MINUTEConverts a serial number to a minute
MONTHConverts a serial number to a month
NETWORKDAYSReturns the number of whole workdays between two dates
NOWReturns the serial number of the current date and time
SECONDConverts a serial number to a second
TIMEReturns the serial number of a particular time
TIMEVALUEConverts a time in the form of text to a serial number
TODAYReturns the serial number of today's date
WEEKDAYConverts a serial number to a day of the week
WEEKNUMConverts a serial number to a number representing where the week falls numerically with a year
WORKDAYReturns the serial number of the date before or after a specified number of workdays
YEARConverts a serial number to a year
YEARFRACReturns the year fraction representing the number of whole days between start_date and end_date

Logical functions

FunctionDescription
ANDReturns TRUE if all of its arguments are TRUE
FALSEReturns the logical value FALSE
IFSpecifies a logical test to perform
IFERRORReturns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula
NOTReverses the logic of its argument
ORReturns TRUE if any argument is TRUE
TRUEReturns the logical value TRUE

Lookup and reference functions

FunctionDescription
ADDRESSReturns a reference as text to a single cell in a worksheet
AREASReturns the number of areas in a reference
CHOOSEChooses a value from a list of values
COLUMNReturns the column number of a reference
COLUMNSReturns the number of columns in a reference
HLOOKUPLooks in the top row of an array and returns the value of the indicated cell
HYPERLINKCreates a shortcut or jump that opens a document stored on a network server, an intranet, or the Internet
INDEXUses an index to choose a value from a reference or array
INDIRECTReturns a reference indicated by a text value
LOOKUPLooks up values in a vector or array
MATCHLooks up values in a reference or array
OFFSETReturns a reference offset from a given reference
ROWReturns the row number of a reference
ROWSReturns the number of rows in a reference
RTDRetrieves real-time data from a program that supports COM automation (Automation: A way to work with an application's objects from another application or development tool. Formerly called OLE Automation, Automation is an industry standard and a feature of the Component Object Model (COM).)
TRANSPOSEReturns the transpose of an array
VLOOKUPLooks in the first column of an array and moves across the row to return the value of a cell

Math and trigonometry functions

FunctionDescription
ABSReturns the absolute value of a number
ACOSReturns the arccosine of a number
ACOSHReturns the inverse hyperbolic cosine of a number
ASINReturns the arcsine of a number
ASINHReturns the inverse hyperbolic sine of a number
ATANReturns the arctangent of a number
ATAN2Returns the arctangent from x- and y-coordinates
ATANHReturns the inverse hyperbolic tangent of a number
CEILINGRounds a number to the nearest integer or to the nearest multiple of significance
COMBINReturns the number of combinations for a given number of objects
COSReturns the cosine of a number
COSHReturns the hyperbolic cosine of a number
DEGREESConverts radians to degrees
EVENRounds a number up to the nearest even integer
EXPReturns e raised to the power of a given number
FACTReturns the factorial of a number
FACTDOUBLEReturns the double factorial of a number
FLOORRounds a number down, toward zero
GCDReturns the greatest common divisor
INTRounds a number down to the nearest integer
LCMReturns the least common multiple
LNReturns the natural logarithm of a number
LOGReturns the logarithm of a number to a specified base
LOG10Returns the base-10 logarithm of a number
MDETERMReturns the matrix determinant of an array
MINVERSEReturns the matrix inverse of an array
MMULTReturns the matrix product of two arrays
MODReturns the remainder from division
MROUNDReturns a number rounded to the desired multiple
MULTINOMIALReturns the multinomial of a set of numbers
ODDRounds a number up to the nearest odd integer
PIReturns the value of pi
POWERReturns the result of a number raised to a power
PRODUCTMultiplies its arguments
QUOTIENTReturns the integer portion of a division
RADIANSConverts degrees to radians
RANDReturns a random number between 0 and 1
RANDBETWEENReturns a random number between the numbers you specify
ROMANConverts an arabic numeral to roman, as text
ROUNDRounds a number to a specified number of digits
ROUNDDOWNRounds a number down, toward zero
ROUNDUPRounds a number up, away from zero
SERIESSUMReturns the sum of a power series based on the formula
SIGNReturns the sign of a number
SINReturns the sine of the given angle
SINHReturns the hyperbolic sine of a number
SQRTReturns a positive square root
SQRTPIReturns the square root of (number * pi)
SUBTOTALReturns a subtotal in a list or database
SUMAdds its arguments
SUMIFAdds the cells specified by a given criteria
SUMIFSAdds the cells in a range that meet multiple criteria
SUMPRODUCTReturns the sum of the products of corresponding array components
SUMSQReturns the sum of the squares of the arguments
SUMX2MY2Returns the sum of the difference of squares of corresponding values in two arrays
SUMX2PY2Returns the sum of the sum of squares of corresponding values in two arrays
SUMXMY2Returns the sum of squares of differences of corresponding values in two arrays
TANReturns the tangent of a number
TANHReturns the hyperbolic tangent of a number
TRUNCTruncates a number to an integer

Statistical functions

FunctionDescription
AVEDEVReturns the average of the absolute deviations of data points from their mean
AVERAGEReturns the average of its arguments
AVERAGEAReturns the average of its arguments, including numbers, text, and logical values
AVERAGEIFReturns the average (arithmetic mean) of all the cells in a range that meet a given criteria
AVERAGEIFSReturns the average (arithmetic mean) of all cells that meet multiple criteria.
BETADISTReturns the beta cumulative distribution function
BETAINVReturns the inverse of the cumulative distribution function for a specified beta distribution
BINOMDISTReturns the individual term binomial distribution probability
CHIDISTReturns the one-tailed probability of the chi-squared distribution
CHIINVReturns the inverse of the one-tailed probability of the chi-squared distribution
CHITESTReturns the test for independence
CONFIDENCEReturns the confidence interval for a population mean
CORRELReturns the correlation coefficient between two data sets
COUNTCounts how many numbers are in the list of arguments
COUNTACounts how many values are in the list of arguments
COUNTBLANKCounts the number of blank cells within a range
COUNTIFCounts the number of cells within a range that meet the given criteria
COUNTIFSCounts the number of cells within a range that meet multiple criteria
COVARReturns covariance, the average of the products of paired deviations
CRITBINOMReturns the smallest value for which the cumulative binomial distribution is less than or equal to a criterion value
DEVSQReturns the sum of squares of deviations
EXPONDISTReturns the exponential distribution
FDISTReturns the F probability distribution
FINVReturns the inverse of the F probability distribution
FISHERReturns the Fisher transformation
FISHERINVReturns the inverse of the Fisher transformation
FORECASTReturns a value along a linear trend
FREQUENCYReturns a frequency distribution as a vertical array
FTESTReturns the result of an F-test
GAMMADISTReturns the gamma distribution
GAMMAINVReturns the inverse of the gamma cumulative distribution
GAMMALNReturns the natural logarithm of the gamma function, Γ(x)
GEOMEANReturns the geometric mean
GROWTHReturns values along an exponential trend
HARMEANReturns the harmonic mean
HYPGEOMDISTReturns the hypergeometric distribution
INTERCEPTReturns the intercept of the linear regression line
KURTReturns the kurtosis of a data set
LARGEReturns the k-th largest value in a data set
LINESTReturns the parameters of a linear trend
LOGESTReturns the parameters of an exponential trend
LOGINVReturns the inverse of the lognormal distribution
LOGNORMDISTReturns the cumulative lognormal distribution
MAXReturns the maximum value in a list of arguments
MAXAReturns the maximum value in a list of arguments, including numbers, text, and logical values
MEDIANReturns the median of the given numbers
MINReturns the minimum value in a list of arguments
MINAReturns the smallest value in a list of arguments, including numbers, text, and logical values
MODEReturns the most common value in a data set
NEGBINOMDISTReturns the negative binomial distribution
NORMDISTReturns the normal cumulative distribution
NORMINVReturns the inverse of the normal cumulative distribution
NORMSDISTReturns the standard normal cumulative distribution
NORMSINVReturns the inverse of the standard normal cumulative distribution
PEARSONReturns the Pearson product moment correlation coefficient
PERCENTILEReturns the k-th percentile of values in a range
PERCENTRANKReturns the percentage rank of a value in a data set
PERMUTReturns the number of permutations for a given number of objects
POISSONReturns the Poisson distribution
PROBReturns the probability that values in a range are between two limits
QUARTILEReturns the quartile of a data set
RANKReturns the rank of a number in a list of numbers
RSQReturns the square of the Pearson product moment correlation coefficient
SKEWReturns the skewness of a distribution
SLOPEReturns the slope of the linear regression line
SMALLReturns the k-th smallest value in a data set
STANDARDIZEReturns a normalized value
STDEVEstimates standard deviation based on a sample
STDEVAEstimates standard deviation based on a sample, including numbers, text, and logical values
STDEVPCalculates standard deviation based on the entire population
STDEVPACalculates standard deviation based on the entire population, including numbers, text, and logical values
STEYXReturns the standard error of the predicted y-value for each x in the regression
TDISTReturns the Student's t-distribution
TINVReturns the inverse of the Student's t-distribution
TRENDReturns values along a linear trend
TRIMMEANReturns the mean of the interior of a data set
TTESTReturns the probability associated with a Student's t-test
VAREstimates variance based on a sample
VARAEstimates variance based on a sample, including numbers, text, and logical values
VARPCalculates variance based on the entire population
VARPACalculates variance based on the entire population, including numbers, text, and logical values
WEIBULLReturns the Weibull distribution
ZTESTReturns the one-tailed probability-value of a z-test

Text functions

FunctionDescription
ASCChanges full-width (double-byte) English letters or katakana within a character string to half-width (single-byte) characters
BAHTTEXTConverts a number to text, using the ß (baht) currency format
CHARReturns the character specified by the code number
CLEANRemoves all nonprintable characters from text
CODEReturns a numeric code for the first character in a text string
CONCATENATEJoins several text items into one text item
DOLLARConverts a number to text, using the $ (dollar) currency format
EXACTChecks to see if two text values are identical
FIND, FINDBFinds one text value within another (case-sensitive)
FIXEDFormats a number as text with a fixed number of decimals
JISChanges half-width (single-byte) English letters or katakana within a character string to full-width (double-byte) characters
LEFT, LEFTBReturns the leftmost characters from a text value
LEN, LENBReturns the number of characters in a text string
LOWERConverts text to lowercase
MID, MIDBReturns a specific number of characters from a text string starting at the position you specify
PHONETICExtracts the phonetic (furigana) characters from a text string
PROPERCapitalizes the first letter in each word of a text value
REPLACE, REPLACEBReplaces characters within text
REPTRepeats text a given number of times
RIGHT, RIGHTBReturns the rightmost characters from a text value
SEARCH, SEARCHBFinds one text value within another (not case-sensitive)
SUBSTITUTESubstitutes new text for old text in a text string
TConverts its arguments to text
TEXTFormats a number and converts it to text
TRIMRemoves spaces from text
UPPERConverts text to uppercase
VALUEConverts a text argument to a number

Cube functions

FunctionDescription
CUBEKPIMEMBERReturns a key performance indicator (KPI) name, property, and measure, and displays the name and property in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, used to monitor an organization's performance.
CUBEMEMBERReturns a member or tuple in a cube hierarchy. Use to validate that the member or tuple exists in the cube.
CUBEMEMBERPROPERTYReturns the value of a member property in the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.
CUBERANKEDMEMBERReturns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or top 10 students.
CUBESETDefines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.
CUBESETCOUNTReturns the number of items in a set.
CUBEVALUEReturns an aggregated value from a cube.

Database functions

FunctionDescription
DAVERAGEReturns the average of selected database entries
DCOUNTCounts the cells that contain numbers in a database
DCOUNTACounts nonblank cells in a database
DGETExtracts from a database a single record that matches the specified criteria
DMAXReturns the maximum value from selected database entries
DMINReturns the minimum value from selected database entries
DPRODUCTMultiplies the values in a particular field of records that match the criteria in a database
DSTDEVEstimates the standard deviation based on a sample of selected database entries
DSTDEVPCalculates the standard deviation based on the entire population of selected database entries
DSUMAdds the numbers in the field column of records in the database that match the criteria
DVAREstimates variance based on a sample from selected database entries
DVARPCalculates variance based on the entire population of selected database entries

Engineering functions

FunctionDescription
BESSELIReturns the modified Bessel function In(x)
BESSELJReturns the Bessel function Jn(x)
BESSELKReturns the modified Bessel function Kn(x)
BESSELYReturns the Bessel function Yn(x)
BIN2DECConverts a binary number to decimal
BIN2HEXConverts a binary number to hexadecimal
BIN2OCTConverts a binary number to octal
COMPLEXConverts real and imaginary coefficients into a complex number
CONVERTConverts a number from one measurement system to another
DEC2BINConverts a decimal number to binary
DEC2HEXConverts a decimal number to hexadecimal
DEC2OCTConverts a decimal number to octal
DELTATests whether two values are equal
ERFReturns the error function
ERFCReturns the complementary error function
GESTEPTests whether a number is greater than a threshold value
HEX2BINConverts a hexadecimal number to binary
HEX2DECConverts a hexadecimal number to decimal
HEX2OCTConverts a hexadecimal number to octal
IMABSReturns the absolute value (modulus) of a complex number
IMAGINARYReturns the imaginary coefficient of a complex number
IMARGUMENTReturns the argument theta, an angle expressed in radians
IMCONJUGATEReturns the complex conjugate of a complex number
IMCOSReturns the cosine of a complex number
IMDIVReturns the quotient of two complex numbers
IMEXPReturns the exponential of a complex number
IMLNReturns the natural logarithm of a complex number
IMLOG10Returns the base-10 logarithm of a complex number
IMLOG2Returns the base-2 logarithm of a complex number
IMPOWERReturns a complex number raised to an integer power
IMPRODUCTReturns the product of complex numbers
IMREALReturns the real coefficient of a complex number
IMSINReturns the sine of a complex number
IMSQRTReturns the square root of a complex number
IMSUBReturns the difference between two complex numbers
IMSUMReturns the sum of complex numbers
OCT2BINConverts an octal number to binary
OCT2DECConverts an octal number to decimal
OCT2HEXConverts an octal number to hexadecimal

Financial functions

FunctionDescription
ACCRINTReturns the accrued interest for a security that pays periodic interest
ACCRINTMReturns the accrued interest for a security that pays interest at maturity
AMORDEGRCReturns the depreciation for each accounting period by using a depreciation coefficient
AMORLINCReturns the depreciation for each accounting period
COUPDAYBSReturns the number of days from the beginning of the coupon period to the settlement date
COUPDAYSReturns the number of days in the coupon period that contains the settlement date
COUPDAYSNCReturns the number of days from the settlement date to the next coupon date
COUPNCDReturns the next coupon date after the settlement date
COUPNUMReturns the number of coupons payable between the settlement date and maturity date
COUPPCDReturns the previous coupon date before the settlement date
CUMIPMTReturns the cumulative interest paid between two periods
CUMPRINCReturns the cumulative principal paid on a loan between two periods
DBReturns the depreciation of an asset for a specified period by using the fixed-declining balance method
DDBReturns the depreciation of an asset for a specified period by using the double-declining balance method or some other method that you specify
DISCReturns the discount rate for a security
DOLLARDEConverts a dollar price, expressed as a fraction, into a dollar price, expressed as a decimal number
DOLLARFRConverts a dollar price, expressed as a decimal number, into a dollar price, expressed as a fraction
DURATIONReturns the annual duration of a security with periodic interest payments
EFFECTReturns the effective annual interest rate
FVReturns the future value of an investment
FVSCHEDULEReturns the future value of an initial principal after applying a series of compound interest rates
INTRATEReturns the interest rate for a fully invested security
IPMTReturns the interest payment for an investment for a given period
IRRReturns the internal rate of return for a series of cash flows
ISPMTCalculates the interest paid during a specific period of an investment
MDURATIONReturns the Macauley modified duration for a security with an assumed par value of $100
MIRRReturns the internal rate of return where positive and negative cash flows are financed at different rates
NOMINALReturns the annual nominal interest rate
NPERReturns the number of periods for an investment
NPVReturns the net present value of an investment based on a series of periodic cash flows and a discount rate
ODDFPRICEReturns the price per $100 face value of a security with an odd first period
ODDFYIELDReturns the yield of a security with an odd first period
ODDLPRICEReturns the price per $100 face value of a security with an odd last period
ODDLYIELDReturns the yield of a security with an odd last period
PMTReturns the periodic payment for an annuity
PPMTReturns the payment on the principal for an investment for a given period
PRICEReturns the price per $100 face value of a security that pays periodic interest
PRICEDISCReturns the price per $100 face value of a discounted security
PRICEMATReturns the price per $100 face value of a security that pays interest at maturity
PVReturns the present value of an investment
RATEReturns the interest rate per period of an annuity
RECEIVEDReturns the amount received at maturity for a fully invested security
SLNReturns the straight-line depreciation of an asset for one period
SYDReturns the sum-of-years' digits depreciation of an asset for a specified period
TBILLEQReturns the bond-equivalent yield for a Treasury bill
TBILLPRICEReturns the price per $100 face value for a Treasury bill
TBILLYIELDReturns the yield for a Treasury bill
VDBReturns the depreciation of an asset for a specified or partial period by using a declining balance method
XIRRReturns the internal rate of return for a schedule of cash flows that is not necessarily periodic
XNPVReturns the net present value for a schedule of cash flows that is not necessarily periodic
YIELDReturns the yield on a security that pays periodic interest
YIELDDISCReturns the annual yield for a discounted security; for example, a Treasury bill
YIELDMATReturns the annual yield of a security that pays interest at maturity

Information functions

FunctionDescription
CELLReturns information about the formatting, location, or contents of a cell
ERROR.TYPEReturns a number corresponding to an error type
INFOReturns information about the current operating environment
ISBLANKReturns TRUE if the value is blank
ISERRReturns TRUE if the value is any error value except #N/A
ISERRORReturns TRUE if the value is any error value
ISEVENReturns TRUE if the number is even
ISLOGICALReturns TRUE if the value is a logical value
ISNAReturns TRUE if the value is the #N/A error value
ISNONTEXTReturns TRUE if the value is not text
ISNUMBERReturns TRUE if the value is a number
ISODDReturns TRUE if the number is odd
ISREFReturns TRUE if the value is a reference
ISTEXTReturns TRUE if the value is text
NReturns a value converted to a number
NAReturns the error value #N/A
TYPEReturns a number indicating the data type of a value

Sumber :
http://office.microsoft.com

Sumber : http://www.umboh.net/2011/03/daftar-fungsi-excel-berdasarkan.html#ixzz2CyAbIujD
Hak Kekayaan Intelektual [www.umboh.net] - Wajib Melampirkan Link Sumber
Under Creative Commons License: Attribution Share Alike
Share this article :

0 comments:

Poskan Komentar

Jangan Lupa Komentarnya Ya !

 
Support : Khiansantang | TipsTrikBlogger | Mas Template
Copyright © 2011. BALE-BALE PALUPUH - All Rights Reserved
Template Modify by Abahvsan
Proudly powered by Blogger