Excel common functions and usage

A formula is a combination of single or multiple functions.

The AND operation returns logical false, and only when all the results with parameters are logical true will logical true be returned, and vice versa. Conditional judgment

AVERAGE calculates the arithmetic average of all parameters. Data calculation

Column displays the column label value of the reference cell. display position

CONCATENATE connects multiple character texts or data in a cell and displays them in a cell. Character merging

COUNTIF calculates the number of cells in the cell range that meet the specified conditions. Conditional statistics

DATE gives the date of the specified value. Display date

The DATEDIF calculation returns the difference between two date parameters. Counting days

The date specified in the daily calculation parameter or the number of days in the reference cell. Counting days

DCOUNT returns the number of cells in a column of a database or list that meet the specified conditions and contain numbers. Conditional statistics

FREQUENCY returns the frequency distribution of data in a region as a vertical array. Probability calculation

IF judges whether the result is true or false according to the logic of the specified condition, and returns the calculation result triggered by the corresponding condition. Conditional calculation

INDEX returns the value of an element in a list or array, which is determined by the index value of the row number and column number. Data positioning

INT rounds the value down to the nearest integer. Data calculation

ISERROR is used to test whether the return value of the function is wrong. If there is an error, the function returns TRUE, otherwise it returns FALSE. Logical judgment

LEFT starts with the first character of the text string and intercepts a specified number of characters. Intercept data

LEN counts the number of characters in a text string. Character statistics

MATCH returns the corresponding position of the element in the array that matches the specified value in a specified way. Matching position

MAX looks for the maximum value in a set of numbers. Data calculation

MID starts at the specified position of the text string and intercepts the specified number of characters. Character interception

Find the smallest value in a set of numbers. Data calculation

Find the remainder of the division of two numbers. Data calculation

MONTH finds the month of a specified date or a date in a reference cell. Date calculation

Now give the current system date and time. Show date and time

Or only when all the parameter values are logically false, the logical "false" of the result is returned, otherwise the logical "true" is returned. Logical judgment

RANK The ranking of a value in the list of return values relative to other values. Data classification

RIGHT starts with the last character of the text string and intercepts a specified number of characters. Character interception

SUBTOTAL returns the subtotal in a list or database. subtotal

SUM calculates the sum of a set of values. Data calculation

SUMIF calculates the sum of values in a range of cells that meet the specified conditions. Conditional data calculation

TEXT converts the corresponding number into text according to the specified number format.

Today, the system date display date is given.

VALUE converts a text string representing a numeric value into a numeric value. Text-digital conversion

VLOOKUP looks up the specified numerical value in the first column of the data table and returns the numerical conditional positioning of the specified column in the current row of the data table.

WEEKDAY gives the number of weeks corresponding to the specified date. Weekly calculation

Excel partial function list.

Steps of the function: ① Select the cell where the result is stored.

② Click "=" (edit formula)

③ Find the function (click the "triangle" button. Or directly enter the function name.

④ Selection range

⑤CTRL+ Enter。

① SUM function sum ()

② Average function ()

③ Rank function rank ()

Example: rank (a1:a $1:a $15)

Line numbers and columns are called line numbers, and the symbol "$" is added before them. 1 or 15 is called the column number, indicating that the position of the cell is in column A, and the number is 1 or no. 15.

④ Maximum function MAX ()

⑤ Minimum value function MIN ()

⑥ Statistical function COUNTIF ()

Calculate the number of cells that meet the given requirements.

Example: countif (a 1: b5, "> 60")

For the number of people with statistical scores greater than 60, please note that the conditions should be enclosed in double quotation marks and entered in English.

⑦ Fusion and coagulation of cell contents ()

Combine several text strings into one string.

⑧ Right (A 1, 4)

Extract the rightmost character of the string with a length of 4 digits.

Pet-name ruby left ()

Returns the leftmost character of a string.

⑩MIDB()

Extracts a string of a specified length from the specified position of the text to the right.

1 1, duplicate function report ()

The number of times the cell weight appears.

12, now ()

Returns the system date and time inside the computer.

13, month ()

Convert the serial number to the corresponding month number.

Editor's Note: Excel is a very important software in office automation, and many giant international enterprises rely on Excel for data management. It can not only process tables and analyze graphs conveniently, but also its more powerful function is the automatic processing and calculation of data. However, it is difficult for many people who lack the background of science and engineering or do not understand the powerful data processing function of Excel to go further. The editor thinks that ignorance of the application of Excel functions is an obstacle that hinders ordinary users from fully mastering Excel. However, at present, this part of the teaching articles are rare, so this series of Excel function applications are specially arranged, hoping to help advanced users of Excel. The "Excel Function Application" series will be updated every week, and various Excel functions and their applications will be systematically introduced step by step, so please pay attention!

-

Terminology description

What are parameters? Parameters can be numbers, text, logical values in the form of TRUE or FALSE, arrays, error values in the form of #N/A, or cell references. The given parameters must be able to produce valid values. Parameters can also be constants, formulas or other functions.

Parameters are not only constants, formulas or functions, but also arrays and cell references.

1.array- Use to create a formula that can produce multiple results or operate on a set of parameters stored in rows and columns. There are two kinds of arrays in Microsoft Excel: range array and constant array. The area array is a rectangular cell area, and the cells in this area use formulas; Constant array uses a set of given constants as parameters in the formula.

2. Cell Reference-Used to indicate the coordinate values of cells in the worksheet. For example, the reference form of the cell displayed at the intersection of column B and row 3 is "B3".

3. Constants-Constants are numeric or text values directly entered into cells or formulas, or numeric or text values represented by names. For example, the date 10/9/96, the number 2 10 and the text "quarter year" are all constants. Neither the formula nor the numerical value derived from the formula is a constant.

-

Excel's data processing function can be said to be the best in the existing word processing software, and almost no software can match it. When you learn the basic operation of Excel, do you feel that you have been confined to the operation interface of Excel, while the functions of Excel have been confined to the application of simple functions such as summation and average? Can Excel only do these simple tasks? In fact, as one of the most important means for Excel to process data, function is very powerful. It can have many uses in life and work practice. You can even use Excel to design complex statistical management tables or small database systems.

Please follow the author to open the function tour of Excel. Here, the author first assumes that you have a certain understanding of the basic operation of Excel. First, let's learn something about functions.

I. What is a function

The functions proposed in Excel are actually some predefined formulas, which use some specific values called parameters to calculate in a specific order or structure. Users can directly use them to perform a series of operations on the numerical values in a certain area, such as analyzing and processing date values and time values, determining the payment amount of loans, determining the data types in cells, calculating average values, sorting and displaying, calculating text data and so on. For example, the SUM function adds a cell or a group of cells.

Can a function be multiple? That is, can a function be a parameter of another function? Of course, this is what nested functions mean. The so-called nested function means that in some cases, you may need to use one function as the parameter of another function. For example, the formula shown in figure 1 uses the nested AVERAGE function and compares the result with 50. The meaning of this formula is: If the average value of cells F2 to F5 is greater than 50, find the sum of F2 to F5; Otherwise, a value of 0 is displayed.

Before learning Excel functions, we need to have a necessary understanding of the structure of functions. As shown in Figure 2, the structure of the function begins with the function name, followed by the left parenthesis, comma-separated parameters and the right parenthesis. If the function appears as a formula, type an equal sign (=) before the function name. When you create a formula that contains a function, the formula panel will provide relevant help.

Formula Panel-A tool that helps you create or edit formulas, and also provides information about functions and their parameters. Click the Edit Formula button in the edit bar, or click the Paste Function button in the Standard toolbar, and a formula palette will appear below the edit bar. The whole process is shown in Figure 3.

Second, the steps to use this function

How to use functions in Excel?

1. Click the cell where you want to enter the function, as shown in Figure 4. Click cell C 1 to open the formula bar.

Figure 4 Cell Editing

2. Click the "Edit Formula" button in the edit bar, and a "Formula Palette" will appear below the edit bar. At this point, the Name box will become a Function button, as shown in Figure 3.

3. Click the arrow at the right end of the Function button to open the function list box and select the required function;

Figure 5 Function list box

4. After selecting the function, Excel 2000 will open the formula palette. Users can enter the parameters of the function in this panel. After the parameters are entered, the calculation result of the function will also be displayed in the formula panel.

5. Click "OK" button to complete the function input;

6. If there is no required function in the list, you can click the "Other Functions" option to open the "Paste Function" dialog box, from which you can select the required function, and then click "OK" to return to the "Formula Palette" dialog box.

After understanding the basic knowledge and usage of functions, please follow the author to find various functions provided by Excel. Click "Functions" in the insert bar to see all the functions.

Third, the types of functions.

Excel functions * * * have 1 1 classes, which are database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions.

1. database function-You can use the database worksheet function when you need to analyze whether the values in the data list meet certain conditions. For example, in a data list containing sales information, you can calculate the total number of all rows or records with sales value greater than 1 000 and less than 2,500. Microsoft Excel *** has 12 worksheet functions for analyzing data stored in data lists or databases. The unified name of these functions is Dfunctions, also known as D function, and each function has three identical parameters: database, field and criterion. These parameters point to the worksheet area used by the database function. Among them, the parameter database is the area containing the data list on the worksheet. The parameter field is the flag of the column to be summarized. The parameter criteria is the range on the worksheet that contains the specified conditions.

2. Date and Time Function-Date and time values can be analyzed and processed in formulas through date and time functions.

3. Engineering function-The engineering worksheet function is used for engineering analysis. Most of these functions can be divided into three types: functions for dealing with complex numbers, functions for numerical conversion between different digital systems (such as decimal, hexadecimal, octal and binary), and functions for numerical conversion in different measurement systems.

4. Financial function-The financial function can perform general financial calculations, such as determining the payment amount of loans, the future value or net present value of investments, and the value of bonds or coupons. Common parameters in financial functions:

Future value (fv)-the value of an investment or loan after all payments have been made.

Number of periods (nper)-The total payment period of the investment.

Payment (pmt)-Regular payment amount of an investment or loan.

Present value (pv)-the value of an investment or loan at the beginning of the investment. For example, the present value of a loan is the amount of principal borrowed.

Interest rate)-the interest rate or discount rate of an investment or loan.

Type)-the payment interval within the payment period, such as the beginning or end of the month.

5. Information function-You can use the information worksheet function to determine the type of data stored in the cell. The information function contains a set of worksheet functions named IS, which returns TRUE when the cell meets the condition. For example, if the cell contains even values, the ISEVEN worksheet function will return TRUE. If you need to determine whether there are blank cells in the cell range, you can count the blank cells in the cell range by using the COUNTBLANK worksheet function or determine whether the cells in the range are empty by using the ISBLANK worksheet function.

6. Logical function-Logical function can be used to judge whether the value is true or false, or to carry out compound test. For example, you can use the IF function to determine whether a condition is true or false, thus returning different values.

7. Query and reference function-When you need to find a specific value in a data list or table, or you need to find a reference to a cell, you can use the query and reference worksheet function. For example, if you need to find a value in a table that matches the value in the first column, you can use the VLOOKUP worksheet function. If you need to locate values in a data list, you can use the MATCH worksheet function.

8. Math and trigonometric functions-Through math and trigonometric functions, you can handle simple calculations, such as rounding numbers, calculating the sum of values in a cell range or complex calculations.

9. Statistical function-Statistical worksheet function is used for statistical analysis of data area. For example, the statistical worksheet function can provide information about a straight line drawn by a set of given values, such as the slope and Y-axis intercept of the straight line, or the actual point values that make up the straight line.

10. Text function-Text strings can be processed in formulas through text functions. For example, you can change the case or determine the length of a text string. You can insert or connect a date to a text string. The following formula is an example of how to use the function TODAY and the function TEXT to create a message containing the current date and representing the date in the format of "dd-mm-yy".

1 1. User-defined function-If you want to use a particularly complicated calculation in a formula or calculation, but the worksheet function can't meet your needs, you need to create a user-defined function. These functions are called user-defined functions and can be created using Visual Basic for Applications.

Excel functions and related knowledge are briefly introduced above. In future articles, the author will introduce the usage and application skills of each function one by one. However, because there are quite a few functions in Excel, we can only introduce a few commonly used functions. For other functions, you can learn more detailed information from the online help function of Excel.

Excel is a commonly used software in office applications. It not only has statistical functions, but also has many functions that can simplify our operations in search and calculation. It should be noted that the treatment of Chinese and English is very different. A word in Chinese is counted by two bytes. If you are not careful, you may ignore this and make mistakes. In fact, there are special functions for double-byte characters in Excel functions.

Let's first look at the difference between the function FIND and the function FINDB.

Grammar:

FIND (find text, within text, start numbering)

FINDB (Find Text, Within Text, Start Numbering)

Both functions have the same parameters.

Function: the FIND function is used to find the string (find_text) in the text (within_text) and return the starting position number of find_text starting from the first character of within_text. You can also use SEARCH to FIND strings, but unlike SEARCH, find is case-sensitive and wildcards are not allowed.

The FINDB function is used to find a string (find_text) in text (within_text) and return the starting position number of find_text starting from the first character of within_text according to the number of bytes. This function is used for double-byte characters.

For example, in figure 1, the formula in cell B2 is "=FIND ("rain", A2) ".

The formula in cell B3 is "=FINDB ("rain", A2) ".

The results of the two function formulas are different, because in the FIND function, "rain" is located in the second position in the "Wu Yufeng" string, so it returns "2"; In the FINDB function, each Chinese character counts as 2 bytes, so "rain" starts from the third byte and returns "3".

Similarly, the relationship between LEFT and LEFTB, RIGHT and RIGHTB, LEN and LENB, MID and MIDB, REPLACE and REPLACEB, SEARCH and SEARCHB is also the same. In other words, functions without the letter B are operated with characters, while functions with the letter B are operated with bytes.

We often use a lot of Chinese characters when editing, modifying and calculating workbook data. It will be very convenient if we use the above function plus the letter B to operate Chinese characters.

Learning Excel functions starts with "Mathematics and Trigonometric Functions". After all, this is a function that we are very familiar with. These sine functions, cosine functions, integer functions and so on have been with us since middle school.

First, let's take a look at the mathematics and trigonometric functions provided by Excel. Here, the author lists all the mathematical and trigonometric functions provided by Excel in the form of a list. Please see the attached table for details.

From the table, we can easily find that the mathematics and trigonometric functions provided by Excel basically include all kinds of mathematical formulas and trigonometric functions that we usually use. I won't go into details about the detailed usage of these functions here. Let's demonstrate the usage of these functions from an application perspective.

First, the application of functions related to summation.

SUM function is the most commonly used function in Excel. Can be used to sum cells with text, spaces and other data, and the syntax is simple and easy to use. I believe this is one of the earliest Excel functions that everyone learned to use. But in fact, the SUM function provided by Excel is not only a sum, but also includes functions such as SUBTOTAL, sum, SUMIF, SUMPRODUCT, SUMSQ, SUMX2MY2, SUMX2PY2 and SUMXMY2.

Here, the author will take the payroll of a company as an example, focusing on the use of SUM (calculating the sum of a set of parameters) and SUMIF (summing the cell areas that meet certain conditions). (Note: For simplicity, the calculation of tax is omitted in the example. )

total

1, row or column sum

Take the most common payroll (as shown above) as an example, which is characterized by the need to sum several cells in a row or column.

For example, to find the actual total salary of the company on May 5438+0, 2006, you can enter the formula in H 13:

=SUM(H3:H 12)

2. Regional total

Regional summation is usually used to sum all the data in a worksheet. At this point, you can let the cell pointer stay in the cell where the result is stored, then enter the formula "=SUM ()" in the Excel edit bar, click in the brackets with the mouse, and finally drag all the cells that need to be summed. If these cells are discontinuous, you can hold down the Ctrl key and drag them separately. For the cells that need to be subtracted, you can hold down the Ctrl key to select them one by one, and then manually add a negative sign in front of the cells referenced by the formula. Of course, you can also use the formula palette to complete the above work, but for the SUM function, it is faster to do it by hand. For example, the formula of H 13 can also be written as:

=SUM(D3:D 12，F3:F 12)-SUM(G3:G 12)

Step 3 concentrate

The parameter in the SUM function, that is, the cell or cell range that requires sum, cannot exceed 30. In other words, the SUM function cannot have more than 29 delimiters (commas) in parentheses, otherwise Excel will prompt you that there are too many parameters. Constants that need to participate in summation can be directly referenced in the form of "=SUM (cell area, constant)". Generally, it is not necessary to absolutely reference the cell where the constant is stored.

Sumif

SUMIF function can sum the range of cells that meet certain conditions, which can be numeric, text or expression, and can be applied to personnel, salary and performance statistics.

Still taking the above figure as an example, it is necessary to calculate the salary distribution of each department in the payroll.

To calculate the overtime pay of the sales department in 200 1 may. Then in F 15, enter the formula as follows

= SUMIF($3 Canadian dollars: 65438 Canadian dollars +02, "Sales Department", $3 Canadian dollars: 65438 Canadian dollars +02)

Among them, "$C$3:$C$ 12" is the cell area that provides the logical judgment basis, and "business department" is the judgment condition, that is, only the cells with the department of $C$3:$C$ 12 are counted, and F$3:$F$ 12 is.

Second, functional applications related to functional images

I think you must remember that when we are studying middle school mathematics, we often need to draw various function images. At that time, drawing paper's description was a little bit, and often because of the negligence of calculation, a smooth function curve could not be drawn. Now, we already know that Excel covers almost all kinds of mathematics and trigonometric functions we need. Can you draw a function diagram with Excel function and Excel chart function? Of course.

Third, the common skill of using mathematical functions-rounding

Rounding is a common problem in practical mathematical operations, especially in financial calculation. Although the cell format of excel allows you to define decimal places, in practice, we find that the numbers themselves are not really rounded, but the display results seem to be rounded. If this rounding method is adopted, there will often be an error of a few cents in financial operation, which is not allowed in financial operation. Is there a simple and feasible way to make a real rounding? In fact, Excel has provided this function, that is, the ROUND function, which can return a number rounded to the specified number of digits.

A function named ROUND(number, num_digits) is provided in "Mathematics and Trigonometric Functions" provided by Excel, and its function is to round numbers according to the specified number of digits. This function has two parameters, number and num_digits. Where # is the number to be rounded; Num_digits is the number of digits after the decimal point of the desired number. As shown in Figure 3:

The initial data of unit B2 is 0. 123456, and that of unit B3 is 0.234567. They will be rounded off. Enter "= round (B2,2)" in cell C2, and keep two significant digits after the decimal point to get 0. 12 and 0.23. Enter "= round (B2,4)" in cell D2, and the decimal point will retain four significant digits, resulting in 0. 1235 and 0.2346.

Note: Excel's list of mathematics and trigonometric functions.

The ABS worksheet function returns the absolute value of the parameter.

The ACOS worksheet function returns the arccosine of a number.

The ACOSH worksheet function returns the inverse hyperbolic cosine of the parameter.

The ASIN worksheet function returns the arcsine value of the parameter.

The ASINH worksheet function returns the inverse hyperbolic sine of the parameter.

The ATAN worksheet function returns the arc tangent value of the parameter.

ATAN2 worksheet function returns the arc tangent of the given x and y coordinate values.

The ATANH worksheet function returns the inverse hyperbolic tangent of the parameter.

CEILING worksheet function rounds parameter values to the nearest integer or radix in the direction of increasing absolute value.

The COMBIN worksheet function calculates the combined number of several objects extracted from a given number of object sets.

The COS worksheet function returns the cosine of a given angle.

The COSH worksheet function returns the hyperbolic cosine of the parameter.

The COUNTIF worksheet function calculates the number of cells in a given range that meet certain conditions.

DEGREES worksheet function converts radians into degrees.

The EVEN worksheet function returns the nearest even number rounded in the direction of increasing absolute value.

EXP worksheet function returns the nth power constant e of e equal to 2.7 1828 182845904, which is the base of natural logarithm.

The fact sheet function returns the factorial of a number, and the factorial of a number is equal to 1*2*3*...* number.

The FACTDOUBLE worksheet function returns the half factorial of the parameter number.

The FLOOR worksheet function truncates and rounds the parameter number in the direction of decreasing absolute value to make it equal to the nearest effective multiple.

The GCD worksheet function returns the greatest common divisor of two or more integers.

The INT worksheet function returns an integer value rounded by a real number.

The LCM worksheet function returns the least common multiple of an integer.

The LN worksheet function returns the natural logarithm of a number based on the constant term e (2.71828182845904).

The LOG worksheet function returns the logarithm of a number based on the specified base.

The LOG 10 worksheet function returns the logarithm based on 10.

The MDETERM worksheet function returns the value of the matrix determinant of an array.

The MINVERSE worksheet function returns the inverse of the array matrix.

The MMULT worksheet function returns the matrix product of two arrays.

The MOD worksheet function returns the remainder of the division of two numbers, and the sign of the result is the same as the divisor.

The MROUND worksheet function returns parameter values rounded to the specified radix.

The polynomial worksheet function returns the ratio of the factorial of the sum of parameters to the factorial of each parameter.

The odd worksheet function returns an odd number rounded to the specified value.

The PI worksheet function returns the number 3. 14 159265358979, which is a mathematical constant pi, accurate to 15 digits after the decimal point.

The POWER worksheet function returns the power of a given number.

The PRODUCT worksheet function multiplies all the numbers given as parameters and returns the product value.

The quotient worksheet function returns the integer part of the quotient, which can be used to round the decimal part of the quotient.

The RADIANS worksheet function converts angles into radians.

The RAND worksheet function returns a uniformly distributed random number greater than or equal to 0 and less than 1.

The RANDBETWEEN worksheet function returns a random number between two specified numbers.

Roman worksheet function converts Arabic numerals into Roman numerals in text form.

The ROUND worksheet function returns a number rounded to the specified number of digits.

The ROUNDDOWN worksheet function is close to zero, and the number is rounded down (the direction of decreasing absolute value).

The ROUNDUP worksheet function moves away from zero and rounds the number up (the direction in which the absolute value increases).

SERIESSUM worksheet function returns the sum of power series based on the following formula:

The SIGN worksheet function returns the symbol of a number. Returns 1 when the number is positive, 0 when it is zero and-1 when it is negative.