Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced

Tag: Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced  full keygen, full key, full crack, portable, full serial key, lastest, 32 & 64 bit, for windows 7 8 10, free download



n fact, some functions are not much use excel, you only need to master the basic Excel functions that can do different types. However, to calculate optimal, there are many other functions can excel optimal handling as well as the simple formula excel where you may not know. 

Here is a list of functions from basic to excel Advanced is divided into many different types that can be easily applied to the appropriate areas.

Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced
Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced 

Excel math functions - MATH FUNCTIONS 

ABS (number): Calculated absolute value of a number (the absolute value of a number is always positive). 

CEILING (number,-significance): Rounding up some (distant out of 0) to the nearest multiple of-significance. 

COMBIN (number, number_chosen): Returns the number of combinations is selected from a number of elements. Often used to determine the total number of groups from a number of elements. 

EVEN (number) Rounds a number to the nearest integer 

EXP (number): Calculate the base e exponential of a number 

FACT (number) : Calculate the factorial of a number 

FACTDOUBLE (number): Calculate the factorial of a number of secondary 

FLOOR (number,-significance): Rounding down some to the nearest multiple of 

GCD (number1, number2, ...): Find Convention the largest share of the 

INT (number) Rounds a number down to the nearest integer 

LCM (number1, number2, ...): Find least common multiple of the number of 

LN (number): Calculate the natural logarithm of a number 

LOG (number): Calculate the logarithm of a number 

LOG10 (number): Calculate the logarithm base 10 of a number 

MDETERM (array): Calculate the determinant of a matrix 

MINVERSE (array): Find the inverse of a matrix matrix 

MMULT (array1, array2): Calculates two matrices 

MOD (number, divisor): Take the remainder of a division. 

MROUND (number, multiple): Rounds a number to a multiple of some 

MULTINOMIAL (number1 , number2, ...): Calculate the ratio of the factorial factorial sum and the number of 

oDD (number) Rounds a number to the nearest odd integer 

PI (): Returns the number Pi 

POWER (number , power): Calculate the power of some of 

the PRODUCT (number1, number2, ...): Calculates the number 

Quotient (numberator, denominator): Get the integer part of a division 

RAND (): Returns a random number between 0 and 1 

RANDBETWEEN (bottom, top): Returns a random number between one option 

ROMAN (number, form): Move some (Arabic) about the Roman numerical format options 

ROUND (number , NUM_DIGITS): Rounds a number to the specifications 

ROUNDDOWN (number, NUM_DIGITS): Rounding down some under the designation 

ROUNDUP (number, NUM_DIGITS): Rounding up some under the designation 

SERIESSUM (x, n, m, coefficients): Calculate the total power of a sequence of 

sIGN (number): Returns the sign (algebra) of a number 

SQRT (number): Calculate the square root of a number 

SQRTPI (number): Calculate the square root of some are multiplied by Pi 

SUBTOTAL (function_num, REF1, ref2, ...): calculated for a group of children in a list according to the specified calculation 

sUM (number1, number2, ...): calculate the sum of No. 

SUMIF (range, criteria, sum_range): Calculates the sum of cells that a condition specified 

SUMIFS (sum_range, criteria_range1, Criteria1, criteria_range2, criteria2, ...): Calculates the sum of cells that specify conditions [/ URL] 

SUMPRODUCT (array1, array2, ...): Calculate the sum of the product of the elements in the data array 

SUMSQ (number1, number2, ...): Calculate the sum of squares of the number 

SUMX2MY2 (array_x, array_y): Count the effect of squares of the elements in the two arrays value 

SUMX2PY2 (array_x, array_y): Calculates the sum of the sum of squares of the elements in the array values 

​​SUMXMY2 (array_x, array_y): Calculates the sum of the square of the effect of elements in the array value 

TRUNC (number, NUM_DIGITS): Get the integer part of a number (without rounding) 


Excel functions handle text and strings - tEXT fUNCTIONS 

ASC (text): Converts the characters double- byte to single-byte characters. 

BATHTEXT (number): Translation of the word (in Thailand), and then add the suffix "Bath" in the back. 

CHAR (number): Convert a code in ANSI code (with domain values ​​from 1-255) to the corresponding character. 

CLEAN (text): remove all non-printable characters in the string 

cODE (text): Returns the code of the first character string text 

CONCATENATE (text1, Text2, ...): Connects multiple strings into a string 

dOLLAR (number, decimals): Converts a number into a form of currency (US dollars), which together with the thousands separator, and can do round at will. 

EXACT (text1, Text2): Comparing two strings. If the same, then returns TRUE, if different, it returns FALSE. There are case sensitive and lowercase. 

FIND (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a sequence (WITHIN_TEXT), calculated according to the first character 

FIXED (number, decimals, no_commas ): Converts a number to text (text), whether or not accompanied by the thousands separator, and can be rounded according to 

the LEFT (text, NUM_CHARS): Returns the first character or more parties left of a string, according to the specified number 

LEN (text): Count the number of characters in a string 

LOWER (text): Change all the characters in a text string to lowercase 

MID (text, start_num, NUM_CHARS) : Returns one or more consecutive characters within a string, starting at a given position 

PROPER (text): change the first character in the string to uppercase, and change the remaining characters into print often 

REPLACE (OLD_TEXT, start_num, NUM_CHARS, NEW_TEXT): Replace part of a string with another string, with the number of characters specified 

REPT (text, times): Repeat a string with a given number of times 

RIGHT (text, NUM_CHARS): Returns one or more characters from the right of a string, according to the specified number 

SEARCH (find_text, WITHIN_TEXT, start_num): Find the starting position of a substring (find_text) within a string (WITHIN_TEXT), calculated according to the first character 

SUBSTITUTE (text, OLD_TEXT, NEW_TEXT, instance_num): Replace this string with another string 

T (value): Returns a string if the reference value is the string, in turn, will return empty string 

tEXT (value, format_text): Converts a number into text format (text) format specified 

TRIM (text): Remove all the useless white space in a text string, leaving only the spaces used as a space between two letters 

UPPER (text): Change all the characters in the string to capitals 

VALUE (text): Converts a string into a number 


SEARCH tHE CONTENT EXCEL aND REFERENCE - LOOKUP fUNCTIONS 

ADDRESS (ROW_NUM, COLUMN_NUM, abs_num, a1, sheet_text): Create a text box at the address, according to the number of lines and columns are provided indicators 

aREAS (reference): Returns the number of references in a reference. Each reference area can be a discrete cell or a continuous range in the spreadsheet 

CHOOSE (num, value1, value2, ...): Choose a value in a list 

COLUMN (reference): Returns the number the column of the first cell in the upper left corner of the reference range 

cOLUMNS (reference): Returns the column number of the reference range 

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, ITEM2, ...): Returns data stored in a PivotTable. Can use GETPIVOTDATA to retrieve data from a report summarizing the PivotTable, provided that the data to see the summary of the reports. 

HLOOKUP (lookup_value, table_array, row_index_num, range_lookup): Detecting a column containing the price looking for value in the first row (top) of a data table, if found, will look to in this column, and will get the value of the specified goods before 

HYPERLINK (link_location, FRIENDLY_NAME): Used to create a connection, a hyperlink 

INDEX (reference, ROW_NUM, COLUMN_NUM, area_num): Find a value in a table (or array) if known its position in the table (or array), which is based on row number and column number 

INDIRECT (ref_text, a1): Returns a reference from string. Reference is returned immediately to display their content - INDIRECT function can also be used when you want to change the reference to a cell within a formula without changing the formula that 

LOOKUP (lookup_value, lookup_vector, result_vector): vectors - Search in a row or a column, if found will return the value of the cell in the same position on the line (or column) is indicated 

LOOKUP (lookup_value, array): Type ARRAY - Search row (or column), the first of an array value, if found will return the value of the cell in the same position on the line (or column) last in the array 

MATCH (lookup_value, lookup_array, match_type): returns the position of a value in a range of values 

​​OFFSET (reference, rows, cols, height, width) Returns a reference to a particular area, starting from a single cell, or range, to a specified distance 

rOW (reference): Returns the line number of the first cell in the upper left corner of the reference range 

ROWS (reference): Returns the row number of the reference range 

TRANSPOSE (array): Transfer a data area along the horizontal pivot and vice versa (always be entered as an array formula) 

VLOOKUP (lookup_value, table_array, col_index_num, range_lookup): Detect a row (row) contains the lookup value in the first column (left) of a data table, if found, will look to in this restaurant, and will take the value specified in the previous column. 

tHE CONTENT MANAGEMENT DISCUSSION EXCEL - Logical FUNCTION 

aND (logical1, logical2, ...): Returns TRUE if all stakeholders number is TRUE, returns FALSE if one or more argument is FALSE 

IF (logical_test, value_if_true, value_if_false): Used to check the conditions and formula values 

​​IFERROR (value, value_if_error): If an error occurs, .. . doing something 

NOT (logical): reverse the value of the arguments 

oR (logical1, logical2, ...): returns TRUE if one or more argument is TRUE, returns FALSE if all the arguments are FALSE 

FALSE () and TRUE (): You can enter either TRUE FALSE directly into the formula, Excel will understand that it is a valid expression TRUE FALSE or 
without resorting to the syntax of these functions to 


the functions EXCEL iNFORMATION - iNFORMATION FUNCTIONS 

cELL (info_type, reference): for information on the format, location or contents of the box in the upper left corner in a reference 

ERROR.TYPE (error_val): Returns a number corresponding to one of the error values ​​in Excel or returns the #NA! if no errors 

INFO (info_text): Returns the information of the operating environment while working with MS Excel 

IS ... (value): Includes functions: ISBLANK, ISERR, ISERROR, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER , ISREF, ISTEXT. Used to check data in Excel, All returns TRUE if testing shows the true and FALSE if testing shows the wrong 

ISEVEN (number): Returns TRUE if the number is an even number, FALSE if the number is odd 

ISODD (number): Returns TRUE if the number is odd, FALSE if the number is an even number 

N (value): Convert a value of some 

NA (value): Used to generate a # N / a! to mark the blank cells to avoid the problem before using some functions of Excel. When referring to the cell function is highlighted, will return the # N / A! 

TYPE (value): Returns the type of value to lookup 


EXCEL DATE OF TITLES AND TIME - DATE AND TIME FUNCTIONS 

DATE (year . month, day): Returns the number represents a specific date. If the cell format was General before the function was entered, the result will be expressed in the form of date. 

DATEDIF (START_DAY, END_DAY, unit): Used to calculate the number of days, months, years difference between two time values. 

DATEVALUE (DATE_TEXT): Returns the number of days sequentially shown by DATE_TEXT (convert a rich text string date into a date value can be calculated). 

dAY (SERIAL_NUMBER): Returns the day part of a date value, is represented by the sequence. The result returned is an integer from 1 to 31. 

DAYS360 (start_date, end_date, method): Returns the number of days between two dates based on a year of 360 days (12 months, 30 days per month) to use for financial calculations. 

EDATE (start_date, months): Returns the number sequence represents a milestone day from the time before and how this timeline a specified number of months. 

EOMONTH (start_date, months ): Returns the sequential number represents the last day of a given month from the timeline given and how this timeline a specified number of months. 

hOUR (SERIAL_NUMBER): Returns the hour part of a value time. The result returned is an integer from 0 to 23. 

MINUTE (SERIAL_NUMBER): Returns the minutes portion of a time value. The result returned is an integer from 0 to 59. 

MONTH (SERIAL_NUMBER): Returns the month part of a date value, is represented by the sequence. The result returned is an integer from 1 to 12. 

NETWORKDAYS (start_date, end_date, holidays): Returns all working days in a period between start_date and end_date, excluding weekends and holidays (holidays). 

nOW (): Returns the sequential number of the current date and time shown. If the cell format was General before the function was entered, the result will be expressed in the form of date and time moments. 

SECOND (SERIAL_NUMBER): Returns the seconds portion of a time value. The result returned is an integer from 0 to 59. 

TIME (hour, minute, second): Returns the decimal part of a time value (from 0 to less than 1). If the cell format was General before the function was entered, the result will be expressed in the form of hours of seconds. 

TIMEVALUE (TIME_TEXT): Returns the decimal part of the value of time (from 0 to less than 1) represented by TIME_TEXT (convert a rich text string into a value of time period can be calculated.) 

TODAY (): Returns the number of sequential current date shown. If the cell format was General before the function was entered, the result will be expressed in the form of date. 

WEEKDAY (SERIAL_NUMBER, return_type): Returns the day of the week corresponding to the date provided. The result returned is an integer from 1 to 7. 

WEEKNUM (SERIAL_NUMBER, return_type): Returns a few weeks of that year. 

WORKDAY (START_DAY, days, holidays): Returns a sequential number representing the number working days, can be started before or after work, and subtract the weekends and holidays (if any) in that period. 

yEAR (SERIAL_NUMBER): Returns a fifth of the value date , is represented by the sequence. The result returned is an integer from 1900 to 9999 

to be returned (start_date, end_date, basis): Returns the ratio of a period in a year. 


THE CONTENT EXCEL STATISTICS - Statistical FUNCTIONS 

statistical functions can be divided 3 small groups follows: group function on Statistics, the group of probability distribution function, and the function group of correlation and linear regression 

1. TEAM TITLES IN STATISTICS 
AVEDEV (number1, number2, ...): On average absolute deviation of data points from their mean. Often used as a measure of the variability of the data set 

AVERAGE (number1, number2, ...): Calculate the average of 

AVERAGEA (number1, number2, ...): Calculate the average of the values, including both logical values 

​​AVERAGEIF (range, Criteria1): Calculate the average of the values ​​in an array in a condition 

AVERAGEIFS (range, Criteria1, criteria2, ...): Calculate the average of the values ​​in a under many conditions array 

COUNT (value1, value2, ...): Count the number of cells in the list 

COUNTA (value1, value2, ...): Count the number of cells that contain the value (not empty) list 

COUNTBLANK (range ): Counts the blank cells in a region of 

COUNTIF (range, criteria): Count the number of cells that a given condition within a range 

COUNTIFS (range1, Criteria1, RANGE2, criteria2, ...): Count the number of cells that many conditions previous 

DEVSQ (number1, number2, ...): Calculate the squared deviations of data points from their sample mean, and the squared plus that again. 

FREQUENCY (data_array, bins_array): Determine how much value often appear within a range of values, and then returns a vertical array of numbers. Always use this function in an array formula 

GEOMEAN (number1, number2, ...): Returns the average of a sequence of positive numbers. Often used to calculate the average growth rate, which compounding has been given variable rates ... 

HARMEAN (number1, number2, ...): Returns the harmonic mean (arithmetic average of the inverse) of the No. 

KURT (number1, number2, ...): Calculates the tip of the collection of data, indicates how sharp or flat relative level of a distribution compared with normal distribution 

lARGE (array, k): Returns the largest value most k in a set of data 

MAX (number1, number2, ...): Returns the largest value of a set of values 

​​MAXA (number1, number2, ...): Returns the largest value of a collection values, including text and logical values 

​​mEDIAN (number1, number2, ...): Calculate the median of numbers. 

MIN (number1, number2, ...): Returns the minimum value of a value collection 

MINA (number1, number2, ...): Returns the smallest value of a set of values, including text and logical values 

​​MODE (number1, number2, ...): Returns the value most appeared in an array value 

pERCENTILE (array, k): Find the kth percentile of values ​​in a data array 

PERCENTRANK (array, x-significance): Returns the rank (relative position) of a value in a data array, the array is the percentage of data that 

PERMUT (number, number_chosen): Returns the permutations of the object. 

qUARTILE (array, quart): Calculated quartile of the data set. Often used in survey data to divide into groups gathered ... 

RANK (number, ref, order): Calculate the rank of a number in the list of the 

SKEW (number1, number2, ...): Returns deviation of the distribution, describes the asymmetry of distribution around its average value 

sMALL (array, k): Returns the k-th smallest value in a set of 

STDEV (number1, number2, ...) : Estimate the standard deviation based on the sample 

STDEVA (value1, value2, ...): Estimation of the standard deviation based on the sample, including logical values 

​​STDEVP (number1, number2, ...): Calculated Overall standard deviation according gathers 

STDEVPA (value1, value2, ...): Calculate the standard deviation as a whole set, including text and logical values 

​​VAR (number1, number2, ...): Returns variance based on a sample 

VARA (value1, value2, ...): Returns the variance based on a sample, including text and logical values 

​​VARP (number1, number2, ...): Returns the variance based on the entire episode where 

VARPA (value1, value2, ...): Returns the variance based on the entire collection, including the logic and text value. 

TRIMMEAN (array, percent): on average, the interior of a data set, by type the percentage of the data points at the beginning and at the end of the data set. 


TEAM TITLES fOR dISTRIBUTION PROBABILITY EXCEL 
BETADIST (x, alpha, beta, A,: Returns the value of the distribution function for determining the exact density accrued interest beta. 

BETAINV (probability, alpha, beta, A,: Returns the inverse of the function that calculates the probability density distribution of accumulated beta. 

BINOMDIST (number_s, Trials, probability_s, cumulative): Returns the probability of the successful trials of the binary distribution. 

CHIDIST (x, Degrees_freedom): Returns the probability of a chi-squared distribution. 

CHIINV (probability, Degrees_freedom): Returns the inverse of the probability distribution of the expenditure side -squared. 

CHITEST (actual_range, expected_range): Returns the probability value from the chi-squared distribution and the corresponding number of degrees of freedom. 

cONFIDENCE (alpha, standard_dev, size): Calculate the confidence interval for a theoretical expectations 

CRITBINOM (Trials, probability_s, alpha): Returns the smallest value for the cumulative binomial distribution is greater than or equal to the standard value. Often used to ensure quality applications ... 

EXPONDIST (x, lambda, cumulative): Calculate the exponential distribution. Often used to simulate the time between the events ... 

FDIST (x, degrees_freedom1, degrees_freedom2): Calculate the probability distribution F. Often used to find out if the two sets of data there are different degrees or not ... 

FINV (probability, degrees_freedom1, degrees_freedom2): Calculates the inverse of the F probability distribution is often used to compare the variability in two data sets 

FTEST (array1, array2): Returns the results of a test used to determine F. Often whether two samples have different variances or not ... 

FISHER (x): Returns the Fisher transformation at x. Often used to test the hypothesis based on the correlation ... 

FISHERINV (y): Calculates the inverse Fisher transformation. Often used to analyze the correlation between the array data ... 

GAMMADIST (x, alpha, beta, cumulative): Returns the gamma cumulative distribution. Can be used to study skewed distribution 

GAMMAINV (probability, alpha, beta): Returns the inverse of the gamma cumulative distribution. 

GAMMLN (x): Calculate the natural logarithm of the gamma function 

HYPGEOMDIST (number1, number2, .. .): Returns the hypergeometric distribution (probability of a certain number of successes ...) 

LOGINV (probability, mean, standard_dev): Calculates the inverse of the lognormal cumulative distribution function of x (LOGNORMDIST) 

LOGNORMDIST (x, mean, standard_dev): Returns the cumulative lognormal distribution of x, in which the natural logarithm of x is normally distributed with parameters mean and standard_dev. 

NEGBINOMDIST (number_f, number_s, probability_s): Returns the negative binomial distribution (returns the probability that there will be failures number_f number_s times before success, when the constant probability of a success is probability_s times) 

NORMDIST (x, mean, standard_dev, cumulative): returns the normal distribution ( normal distribution). Commonly used in statistics, including hypothesis testing 

NORMINV (probability, mean, standard_dev): Calculates the inverse normal cumulative distribution 

NORMSDIST (z): Returns the cumulative distribution function normal (standard normal cumulative distribution function), is distributed with the mean value is zero (0) and standard deviation is 1 

NORMSINV (probability): Calculates the inverse cumulative distribution function of the normal 

POISSON (x, mean, cumulative): Returns the Poisson distribution. Often used to estimate the number of events that will occur in a certain time period 

PROB (x_range, prob_range, LOWER_LIMIT, UPPER_LIMIT): Calculate the probability of the value in the range between two limits 

STANDARDIZE (x, mean, standard_dev ): Returns a normalized value from the distribution indicated by the mean and standard_dev 

TDIST (x, Degrees_freedom, tails): Returns the probability of Student distribution (distribution t), where x is the value calculated from the t and be used to calculate the probability. 

TINV (probability, Degrees_freedom): Returns the Student's t distribution. 

TTEST (array1, array2, tails, type): calculated probability associated with the Student test. 

WEIBULL (x, alpha, beta, cumulative): Returns the Weibull distribution. Often used in reliability analysis, such as average life calculation of a device. 

ZTEST (array, x, sigma): Returns the probability of a part of the test z. 


TEAM TITLES ON RELATIONSHIPS AND EXCEL REGRESSION LINEAR 

CORREL (array1, array2): calculate the correlation coefficient between two arrays to determine the relationship of two features 

COVAR (array1, array2): Calculates the deviation of each pair of data points, then calculate the average of the volume of which 

FORECAST (x, known_y's, known_x's): calculated or predict a future value by using existing values, by means of linear regression 

GROWTH (known_y's, known_x's, new_x's, const ): Calculate the expected growth exponential, using the data available. 

INTERCEPT (known_y's, known_x's): Find the intersection of a straight line with the y axis using the x and y values given 

LINEST (known_y's, known_x's, const, stats): As the statistics for a line by using the method of least squares (least squares) to calculate a straight line that best fits the data and then returns an array describing path scale. Always use this function in an array formula. 

LOGEST (known_y's, known_x's, const, stats): Used in the regression analysis. The function will calculate exponential curve fit to the data provided, and then returns an array value that describes the curve. Always use this function in an array formula 

PEARSON (array1, array2): Calculate the correlation coefficient Pearson torque area (r), a dimensionless index, in the range of -1 to 1, reflecting the expansion of relations linearly between the two sets of data 

RSQ (known_y's, known_x's): calculate the squared correlation coefficient torque achievement Pearson (r), through data points in known_y's and known_x's 

sLOPE (known_y's, known_x's): calculate the slope of road linear regression through the data points. 

STEYX (known_y's, known_x's): Returns the standard error of the predicted value y for each value x in the regression. 

TREND (known_y's, known_x's, new_x's, const): Returns about the trend of linear 


functions EXCEL DATABASE MANAGEMENT aND LIST - LIST DATABASE aND fUNCTIONS managament 

general of Management functions Database and List 

DAVERAGE (database, field, criteria): calculated the average of the values ​​in a column of the list or the value of a database, according to a specified condition. 

DCOUNT (database, field, criteria): Counting cells containing data in a column of the list or the value of a database, according to a specified condition. 

DCOUNTA (database, field, criteria): Counting of cells "is not empty" in a column of the list or the value of a database, according to a specified condition. 

DGET (database, field, criteria): Extract a value from a column of a list or database, matches the specified conditions. 

DMAX (database, field, criteria) : Returns the largest value in a column of a list or a database, according to a specified condition. 

DMIN (database, field, criteria): Returns the smallest value in a column of a list hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DRODUCT (database, field, criteria) : Nhân các giá trị trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DSTDEV (database, field, criteria) : Ước lượng độ lệch chuẩn của một tập hợp theo mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DSTDEVP (database, field, criteria) : Tính độ lệch chuẩn của một tập hợp theo toàn thể các tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DSUM (database, field, criteria) : Cộng các số trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DVAR (database, field, criteria) : Ước lượng sự biến thiên của một tập hợp dựa trên một mẫu, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

DVARP (database, field , criteria) : Tính toán sự biến thiên của một tập hợp dựa trên toàn thể tập hợp, bằng cách sử dụng các số liệu trong một cột của một danh sách hay của một cơ sở dữ liệu, theo một điều kiện được chỉ định. 

Một số ví dụ về cách dùng Criteria để nhập điều kiện 

GETPIVOTDATA (data_field, pivot_table, field1, item1, field2, item2,...) : Trả về dữ liệu được lưu giữ trong báo cáo PivotTable.


















































IMCOS (inumber): Returns the cosine of a complex number 

IMDIV (inumber1, inumber2): Calculated quotient (result of the division) of two complex numbers 

IMEXP (inumber): Returns the exponential of a complex number 

IMLN (inumber) : Returns logarite complex nature of some 

IMLOG10 (inumber): Returns the decimal logarite of a complex number 

IMLOG2 (inumber): Returns logarite base 2 of a complex number 

IMPOWER (inumber, number): Calculated power of a complex number 

IMPRODUCT (inumber1, inumber2, ...): Calculates the number of 2 to 255 complex numbers together 

IMREAL (inumber): Returns the real coefficient of a complex number 

IMSIN (inumber): Returns the sine of a number complex 

IMSQRT (inumber): Returns the square root of a complex number 2 

IMSUB (inumber1, inumber2): Calculate the difference of two complex numbers 

IMSUM (inumber1, inumber2, ...): Calculate the sum of 2 to 255 complex numbers 

OCT2BIN ( number, places) Converts an octal number to binary number 

OCT2DEC (number): Converts an octal number to decimal 

OCT2HEX (number, places) Converts an octal number to a hexadecimal number 


EXCEL fUNCTIONS oF FINANCIAL - FINANCIAN fUNCTIONS 

ACCRINT (issue, first_interest, settlement, rate, par, frequency, basis, calc_method): Calculates the accrued interest for a securities pay interest periodically 

ACCRINTM (issue, settlement, rate, par, basis): Calculates the accrued interest for securities with interest payment on maturity 

AMORDEGRC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation in accounting depending on the shelf life of the asset (used in the system according to French accounting) 

AMORLINC (cost, date_purchased, first_period, Salvage, period, rate, basis): As per fiscal depreciation accounting (used in the accounting system under French) 

COUPDAYBS (settlement, maturity , frequency, basis): Count the number of days since the beginning of the next interest period settlement date 

COUPDAYS (settlement, maturity, frequency, basis): Count the number of days in the interest period including the settlement date 

COUPDAYSCN (settlement, maturity, frequency, basis ): Calculates the number of days from the settlement date until the next interest 

COUPNCD (settlement, maturity, frequency, basis): Returns a number that represents the next interest date after the settlement date since 

COUPNUM (settlement, maturity, frequency, basis): Count the number of times the interest payable between settlement date and the maturity date 

COUPPCD (settlement, maturity, frequency, basis): returns a number that represents the last interest payment date, first date accounting 

CUMIPMT (rate, nper, pv, start_period, end_period, type): Calculate the cumulative income to pay for loans in the period between start_period and end_period 

CUMPRINC (rate, nper, pv, start_period, end_period, type): returns the accumulated capital to pay for the loan for the period between start_period and end_period 

DB (cost, salvage regimens, life, period, month): Calculate depreciation for an asset using a declining balance method in a fixed rates (fixed-declining balance method) in a defined time period. 

DDB (cost, salvage regimens, life, period, factor): Calculated asset depreciation for a method using the double-declining balance (double- declining balance method), or decrease over a certain percentage, for a defined period of time. 

DISC (settlement, maturity, pr, redemption, basis): Calculate the discount rate of a security 

DOLLARDE (fractional_dollar, fraction ): Convert fractional dollar price to the dollar price in decimal format 

DOLLARFR (decimal_dollar, fraction): Convert the decimal value in the form of dollars to the price of the dollar in the form of fractional 

DURATION (settlement, maturity, coupon, YLD, frequency, basis): Calculated validity period Macauley based on copper face value $ 100 (validity period is the weighted average current value of the flow of cash and is used as a measure of feedback that changed benefits the net value of the bonds) 

EFFECT (nominal_rate, Npery): Calculate the annual real interest rate, anticipated annual nominal interest rate and the total number of payment periods per year compounding 

FV (rate, nper, pmt, pv, type): Calculate the value of the investment period based on periodic fixed payments and a fixed interest rate 

FVSCHEDULE (principal, schedule): Calculate the value of a term of the initial capital after the application of a series of compound interest (calculated value for an investment period with interest rate changes) 

INTRATE (settlement, maturity, investment, redemption, basis): calculated interest rate of investment securities for a whole 

IPMT (rate, per, nper , pv, fv, type): returns the interest payment for an investment based on periodic fixed payments and a constant interest rate based on 

IRR (values, guess): Calculates the internal rate for a sequence of functions cash flow is represented by the value of 

ISPMT (rate, per, nper, pv): Calculate the amount of interest paid in a certain period for a loan with a constant interest rate, after deduction of the amount Original paid for that period. 

MDURATION (settlement, maturity, coupon, YLD, frequency, basis): As of deadline Macauley modified for stock-based co-denomination $ 100 

MIRR (values, finance_rate, reinvest_rate): Calculated margin business intrinsic interest in a series of cash flow in cycles 

nOMINAL (effect_rate, Npery): calculate the annual nominal interest rate, anticipated real interest and compounding periods per year 

NPER (rate, pmt, pv, fv, type): Calculate the number of terms to repay investment loans based on each cycle, the amount paid and the fixed rate of return 

NPV (rate, value1, value2, ...): Calculate the net present value of an investment using discount rate compares with more maturity payments (negative values) and income (positive values) 

ODDFPRICE (settlement, maturity, issue, first_coupon, rate, YLD, redemption, frequency, basis) : Calculate the value per contract face value $ 100 of securities with first semester odd (short or long) 

ODDFYIELD (settlement, maturity, issue, first_coupon, rate, pr, redemption, frequency, basis): Returns profit of a stock and a time to first interest is odd (short or long) 

ODDLPRICE (settlement, maturity, last_interest, rate, YLD, redemption, frequency, basis): calculated value per contract face value $ 100 of stock with coupon interest period end is odd (short or long) 

ODDLYIELD (settlement, maturity, last_interest, rate, pr, redemption, frequency, basis): Calculates the yield of securities is odd last period (short-term or long-term) 

PMT (rate, nper, pv, fv, type): Count money to pay for loans with interest rate constant and regular pay 

PPMT (rate, per, nper, pv, fv, type): calculate payment funds in a given period for an investment, in which the payment is made ​​regularly periodically with a constant interest rate 

PRICE (settlement, maturity, rate, YLD, redemption, frequency , basis): Calculate the value of securities on the contract face value $ 100, interest payments cyclical 

PRICEDISC (settlement, maturity, discount, redemption, basis): Calculated values ​​on copper denominations of $ 100 of a stock has discounted 

PRICEMAT (settlement, maturity, issue, rate, YLD, basis): Calculated values ​​on copper denominations of $ 100 a securities pay interest at maturity 

PV (rate, nper, pmt, fv, type): As prices present value of an investment 

rATE (nper, pmt, pv, fv, type, guess): Calculated interest rate per period of an annuity 

REVEICED (settlement, maturity, investment, discount, basis): Calculate the amount received to maturity investment securities for a whole 

SLN (cost, salvage regimens, life): Calculate the cost of depreciation (straight line basis) of an asset for one period 

SYD (cost, salvage regimens, life, per ): Calculated amortized, the carrying value of assets in the period for determination of 

TBILLEQ (settlement, maturity, discount): Calculates the yield corresponds to bonds for treasury bonds 

TBILLPRICE (settlement, maturity, discount): Calculated the value of par value $ 100 for treasuries 

TBILLYIELD (settlement, maturity, pr): Calculates the yield for treasury bonds 

VDB ​​(cost, salvage regimens, life, start_period, end_period, factor, no_switch): Calculated asset depreciation used in many period 

XIRR (values, dates, guess): Calculated inner yields a roving series non-recurring cash 

XNPV (rate, values, dates): Calculated rates for a range of mobile net cash periodic 

YIELD (settlement, maturity, rate, pr, redemption, frequency, basis): Calculates the yield on securities pay interest periodically 

YIELDDISC (settlement, maturity, pr, redemption, basis): Calculate the annual profit the stock has discounted 

YIELDMAT (settlement, maturity, issue, rate, pr, basis): Calculated annual profits of securities pay interest at maturity 

Excel trigonometric functions - trigonometric fUNCTIONS 

ACOS (number): returns a radian values ​​range from 0 to pi, the arccosine, or inverse cosine of a number in the range of -1 to 1 

ACOSH (number): Returns a radian value, is the inverse hyperbolic cosine-of some greater than or equal to 1 

ASIN (number): Returns a value in the period from -Pi radians / 2 to Pi / 2, is the arcsine, or inverse sine of a number in the range of -1 to 1 

ASINH (number ): Returns a radian value, is the inverse hyperbolic sine-of some 

ATAN (number): Returns a value in the range of -Pi radians / 2 to Pi / 2, is the arctangent, or inverse tangent of some 

ATAN2 (x_num, y_num): Returns a value radian range (but not including) from -Pi to Pi, is tangent, or inverse tangent of a point with coordinates x and y 

ATANH (number) : Returns a radian value, is the inverse of a tang-hyperbolic range from -1 to 1 

COS (number): Returns a radian value, the cosine of a number 

COSH (number): Returns a radian value, the hyperbolic cosine of a number- 

dEGREES (angle): Convert measurements from radians to degrees angle 

rADIANS (angle): Conversion of an angle measurement from degrees to radians 

SIN (number): Returns radian is a sine value of some 

BIRTH (number): Returns a radian value, is of some hyperbolic sine- 

TAN (number): Returns a radian value, is evidence of some 

TANH (number): Returning a radian value, is evidence of some hyperbolic- 
Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced Aggregate functions in Excel 2003 2007 2010 2013 Basic Advanced Reviewed by Duy Tín on 11:42:00 PM Rating: 5

No comments:

Powered by Blogger.