13:58:00
รวมสูตร Excel 2000 ถึง 2013 ทุกหมวดหมู่สุดฮิต ตอนที่ 1
รวมสูตร Excel เราเคยนำเสนอไปแล้วครั้งหนึ่ง คราวก่อนเป็นการ รวมสูตร Excel และความหมายเป็นภาษาไทย คราวนี้ นำ สูตร Excel ที่มีใช้ตั้งแต่ Excel เวอร์ชั่น 2000 และยังคงสามารถใช้ได้ใน เวอร์ชั่นสูงๆได้มารีวิวอีกครั้งเพื่อใช้เป็นเอกสารอ้างอิง โดยคราวนี้เป็น สูตร Excel พร้อมความหมายของ สูตร Excel ในภาษาอังกฤษมาให้ชมกัน
เหตุผลที่นำ รวมสูตร Excel เวอร์ชั่นความหมายภาษาอังกฤษมาลงเพราะว่าการทราบความหมายของสูตรใน ภาษาอังกฤษ จะทำให้คุ้นเคยกับการอ่าน Help Excel ที่เป็นภาษาอังกฤษด้วย จะได้ต่อยอดความเก่งกาจด้าน Excel ของเราให้มากขึ้น จากการค้นคว้าหาข้อมูลเกี่ยวกับสูตรแต่ละสูตรในเว็บไซต์ต่างประเทศด้วย เพื่อไม่ให้เป็นการเสียเวลา เราไปดู รวมสูตร Excel ชุดนี้กันเถอะ เยอะเอาเรื่องเหมือนกัน
TABLE 1 - DATABASE CATEGORY FUNCTIONS
สูตร Excel หรือ
Function Excel
|
What it Does (ความหมายของสูตร)
|
DAVERAGE
|
Returns the average of selected database entries
|
Dcount
|
Counts the cells containing numbers from a specified database ad
criteria
|
DCOUNTA
|
Counts nonblank cells from a specified database and criteria
|
DGET
|
Extracts from a database a single record that matches the specified
criteria
|
DMAX
|
Returns the maximum value from selected database entries
|
DMIN
|
Returns the minimum value from selected database entries
|
DPRODUCT
|
Multiplies the values in a particular field of records that match the
criteria in a database
|
DSTDEV
|
Estimates the standard deviation based on a sample of selected
database entries
|
DSTDEVP
|
Calculates the standard deviation based on the entire population of
selected database entries
|
DSUM
|
Adds the numbers in the field column of records in the database that
match the criteria
|
DVAR
|
Estimates variance based on a sample from selected database entries
|
DVARP
|
Calculates variance based on the entire population of selected
database entries
|
SQL CLOSE**
|
Terminates a SQL.OPEN connection
|
SQLBIND**
|
Specifies where to place on SQL.EXEC.QUERY Results
|
SQL.ERROR**
|
Returns error information on SQL” fuctions
|
SQL.EXEC.QUERY**
|
Executes a SQL statement on a SQL.OPEN connection
|
QUERYGETDATA***
|
Gets external data using Microsoft Query
|
QUERYGETDATA DIALOG***
|
Displays a dialog box to get data using Microsoft Query
|
SQLGETSCHEMA**
|
Returns information on a SQL.OPEN connection
|
SQL.OPEN**
|
Makes a connection to a data source via ODBC
|
QUERYREFRESH***
|
Updates a data range using Microsoft Query
|
SQL.REQUEST**
|
Requests a connection and executes a SQL query
|
SQL.RETRIEVE**
|
Retrieves SQL.EXEC.QUERY results
|
SQL.RETRIEVE.TO.FILE**
|
Retrieves SQLEXECQUERY results to a file
|
** Available only when the ODBC add-in is installed
***Available only when the MS Query add-in is install
TABLE 2 – DATE AND TIME CATEGORY FUNCTIONS
สูตร Excel หรือ
Function
|
What It Does (ความหมายสูตร)
|
DATE
|
Returns the serial number of a particular date
|
DATEVALUE
|
Converts a date in the form of text to a serial number
|
DAY
|
Converts a serial number to a day of the month
|
DAYS360
|
Calculates the number of days between two dates, based on a 360-day
year
|
EDATE”
|
Returns the serial number of the date that is the indicated number of
months before or after the start date
|
EOMONTH”
|
Returns the serial number of the last day of the month before or
after a specified number of months
|
HOUR
|
Converts a serial number to an hour
|
MINUTE
|
Converts a serial number to a minute
|
MONTH
|
Converts a serial number to a month
|
NETWORKDAYS”
|
Returns the number of whole workdays between two dates
|
NOW
|
Returns the serial number of the current date and time
|
SECOND
|
Converts a serial number to a second
|
TIME
|
Returns the serial number of a particular time
|
TIMEVALUE
|
Converts a time in the form of text to a serial number
|
TODAY
|
Returns the serial number of today’s date
|
Weekday
|
Converts a serial number to a day of the week
|
WEEKNUM”
|
Returns the week number in the year
|
WORKDAY”
|
Returns the serial number of the date before or after a specified
number of workdays
|
YEAR
|
Converts a serial number to a year
|
YEARFRAC”
|
Returns the year fraction representing the number of whole days
between start-date and end-date
|
TABLE 3 – ENGINEERING CATEGORY FUNCTIONS
สูตร Excel หรือ
Function
|
What It Does (ความหมายสูตร)
|
BESSELI
|
Returns the modified Bessel function ln(x)
|
BESSEU
|
Returns the Bessl function jn(x)
|
BESSELK
|
Returns the modified Bessel function Kn(x)
|
BESSELY
|
Returns the Bessel function Yn(x)
|
BIN2DEC
|
Converts a binary number to decimal
|
BIN2HEX
|
Converts a binary number to hexadecimal
|
BIN2OCT
|
Converts a binary number to octal
|
COMPLEX
|
Converts real and imaginary coefficients into complex numbers
|
DEC2BIN
|
Converts a decimal number to binary
|
DEC2HEX
|
Converts a decimal number to hexadecimal
|
DEC20CT
|
Converts a decimal number to octal
|
DELTA
|
Tests whether two values are equal
|
ERF
|
Return the error function
|
ERFC
|
Returns the complementary error function
|
FACTDOUBLE
|
Returns the double factorial of a number
|
GESTEP
|
Tests whether a number is greater than a threshold value
|
HEX2BIN
|
Converts a hexadecimal number to binary
|
HEX2DEC
|
Converts a hexadecimal number to decimal
|
HEX2OCT
|
Converts a hexadecimal number to octal
|
IMABS
|
Returns the absolute value (modulus) of a complex number
|
IMAGINARY
|
Returns the imaginary coefficient of a complex number
|
IMARGUMENT
|
Returns the argument theta- and angle expressed in radians
|
IMCONIUGATE
|
Returns the complex conjugate of a complex number
|
LMCOS
|
Returns the cosine of a complex number
|
IMDIV
|
Return the quotient of two complex numbers
|
IMEXP
|
Returns the exponential of a complex number
|
IMLN
|
Returns the natural logarithm of a complex number
|
IMLOG2
|
Returns the base-2 logarithm of a complex number
|
IMLOGIO
|
Returns the base-10 logarithm of a complex number
|
IMPOWER
|
Returns a complex number raised to an integer power
|
IMPRODUCT
|
Returns the product of two complex numbers
|
IMREAL
|
Returns the real coefficient of a complex number
|
IMSIN
|
Returns the sine of a complex number
|
IMSQRT
|
Returns the square root of a complex number
|
IMSUB
|
Returns the difference of two complex numbers
|
IMSUM
|
Returns the sum of complex numbers
|
OCTZBIN
|
Converts an octal number to decimal
|
OCTZHEX
|
Converts an octal number to hexadecimal
|
TABLE 4 – FINANCIAL CATEGORY FUNCTIONS
สูตร Excel หรือ
Function
|
What it does
|
ACCRINT*
|
Returns the accrued interest for a security that pays periodic
interest
|
ACCRINTM*
|
Returns the accrued interest for a security that pays interest a
maturity
|
AMORDEGRC
|
Returns the depreciation for each accounting period
|
AMORLINC*
|
Returns the depreciation for each accounting period
|
COUPDAY BS*
|
Returns the number of days from the beginning of the coupon period to
the settlement date
|
COU PDAYS*
|
Returns the number of days in the coupon period that contains the
settlement date
|
COUPDAYSNC*
|
Returns the number of days from the settlement date to the next
coupon date
|
COU PNCD*
|
Returns the next coupon date after the settlement date
|
COUPNUM*
|
Returns the number of coupons payable between the settlement date and
maturity date
|
COUPPCD*
|
Returns the previous coupon date before the settlement date
|
CUMIPMT*
|
Returns the cumulative interest paid between two periods
|
CUMPRINC*
|
Returns the cumulative principal paid on a loan between two periods
|
DB
|
Return the depreciation of an asset for a specified period, using the
fixed declining balance method
|
DDB
|
Returns the depreciation of an asset for a specified period, using
the double-declining balance method or some other method that you specify
|
DISC*
|
Returns the discount rate for a security
|
DOLLARDE*
|
Converts a dollar price (expressed as a fraction) into a dollar price
(expressed as a decimal number)
|
DOLLARFR*
|
Converts a dollar price (expressed as a decimal number) into a dollar
price (expressed as a fraction)
|
DURATION*
|
Returns the annual duration of a security with periodic interest
payments
|
EFFECT*
|
Returns the effective annual interest rate
|
FVq
|
Return the future value of an investment
|
FVSCHEDULE*
|
Returns the future value of an initial principal after applying a
series of compound interest rates
|
INTRATE*
|
Returns the interest rate of a fully invested security
|
IPMT
|
Returns the interest payment for an investment for a given period
|
IRR
|
Return the internal rate of return for a series of cash flows
|
ISPMT
|
Returns the interest associated with a specific loan payment
|
MDURATION*
|
Returns the Macauley modified duration for a security with an assumed
par value of $1001
|
MIRR
|
Returns the internal rate of return where positive and negative cash
flows are financed at different rates
|
NOMINAL”
|
Returns the annual nominal interest rate
|
NPER
|
Returns the number of periods for an investment
|
NPV
|
Return the net present value of an investment based on a series of
periodic cash flows and a discount rate
|
ODDFPRICE*
|
Returns the price per $100 face value of a security with an odd first
period
|
ODDFYIELD*
|
Returns the yield of a security with an odd first period
|
ODDLPPRICE*
|
Returns the price per $100 face value of a security with an odd last
period
|
ODDLYIELD*
|
Returns the yield of a security with an odd last period
|
PMT
|
Returns the periodic payment for an annuity
|
PPMT
|
Returns the payment on the principal for an investment for a given
period
|
PRICE*
|
Returns the price per $100 face value of a security that pays
periodic interest
|
PRICEDISC*
|
Returns the price per $100 face value of a discounted security
|
PRICEMAT*
|
Returns the price per $100 face value of a security that pays
interest at maturity
|
PV
|
Return the present value of an investment
|
RATE
|
Returns the interest rate per period of an annuity
|
RECEIVED*
|
Returns the amount received a t maturity for a fully invested
security
|
SLN
|
Return the straight-line depreciation of an asset for one period
|
SYD
|
Return the sum-of-years’ digits depreciation of an asset for a
specified period
|
TBILLEQ*
|
Returns the bond-equivalent yield for a Treasury bill
|
TBILLPRICE’
|
Returns the price per $100 face value for a Treasury bill
|
TBILLYIELD”
|
Returns the yield for a Treasury bill
|
VDB
|
Returns the depreciation of an asset for a specified or partial
period using a declining balance method
|
XIRR*
|
Returns the internal rate of return for a schedule of cash flows that
is not necessarily periodic
|
YIELD*
|
Returns the yield on a security that pays periodic interest
|
YIELDDISC*
|
Returns the annual yield for a discounted security; for example, a
Treasury bill
|
YIELDMAT*
|
Returns the annual yield of a security that pays interest a maturity
|
*Available only when the Analysis ToolPak add-in is install
รวมสูตร Excel ฉบับความหมายภาษาอังกฤษ ยังไม่หมดเท่านี้ แต่เนื่องจาก บทความยาวเกินไป เลยขอยกยอดเอามาเสนอให้อ่านอีกในตอนที่ 2 จ๊ะ...โปรดติดตามตอนต่อไป...
