1. Digital processing 1. Absolute value function formula: =ABS (number) 2. Integer function formula: =INT (number) 3. Rounding function formula: =ROUND (number, decimal place) 2. Judgment formula 1. If the calculated result value is incorrect, it will be displayed as an empty formula: =IFERROR (number/number).
2. Multi-condition judgment AND return value formula of if statement: IF(AND (cell (logical operator) value, specified cell = return value 1), return value 2,) Description: If all conditions are established at the same time, use AND, and any one will use or function. III. Commonly used statistical formula 1, statistics of formulas with the same content in two tables: B2=COUNTIF (data source: location, specified, target location) Description: If the return value is greater than 0, it means that it exists in another table, and 0 does not exist.
If the formula used in this example is B2=COUNTIF(Sheet 15! A, A2)2. Statistical formula of non-repetitive total data: C2 = SUMPRODUCT (1/COUNTIF (A2:A8, A2: A8)) Description: Count the number of occurrences of each person in the source data with the COUNTIF function, divide by1,and finally add up. Fourth, the data summation formula 1 and the application formula of alternating column summation: H3=SUMIF($A$2:$G$2, H$2, A3:G3) or =SUMPRODUCT((MOD(COLUMN(B3:G3)), 2)=0)*B3:G3). 2. Sum formula of single condition application: F2=SUMIF(A:A, C:C) Description: This is the most basic usage of SUMIF function. V. Find and quote the formula 1. Single condition search description: VLOOKUP is the most commonly used search method in excel. Processing formula of intransitive verb string 1. Merging description of multi-cell strings: Phoic function can only merge character data. You can't combine the numerical value 2, and cut off the description of the part other than the three digits of the result: LEN calculates the total length, LEFT cuts off the total length from the left-three sevens, the date calculation is related to 1, and the year, month and day between days are calculated as the start date (201-kloc-0/2-)
Calculation: the formula for calculating the number of days is =datedif(A2, B2, D), the formula for calculating the number of months is =datedif(A2, B2, M), and the formula for calculating the number of years is =datedif(A2, B2, Y). Result: 1, regardless of the number of months between years is: = datedif (A2, B2, Y).
Number of days in d period. Number of days difference of MD date.
Ignore months and years. Month difference of YM date.
Ignore days and years. Days difference of YD date.
Ignore the year. Extended data:
Engineering function Bessel returns modified Bessel function IN(X) Bessel returns Bessel function JN(X) Bessel returns modified Bessel function KN(X) Bessel returns Bessel function yn (x) xlfctbinindex, BIN2DEC converts binary numbers into decimal numbers BIN2HEX converts binary numbers into hexadecimal numbers BIN2OCT converts binary numbers into octal numbers PLEX converts real and imaginary coefficients into complex numbers CONVERT converts numbers in one measurement system into another measurement system DEC2BIN converts decimal numbers into binary numbers DEC2HEX converts decimal numbers into decimal numbers DELTA converts decimal numbers. Convert a number into an octal number DELTA detects whether two values are equal. ERF returns an error function ERF returns a residual function GESTEP detects whether a number is greater than a certain threshold. HEX2BIN converts a hexadecimal number into a binary number. HEX2DEC converts a hexadecimal number into a decimal number. HEX2OCT converts a hexadecimal number into an octal number. The number IMABS returns the absolute value (modulus) of a complex number. Imaginal returns the imaginary coefficient IMARGUMENT of a complex number and the parameter THETA. The angle expressed in radians is not conjugate and returns the * * * value of a complex number. Yoke complex IMCOS returns cosine value of complex number IMDIV returns quotient of two complex numbers IMEXP returns exponent IMLN of complex number IMLOG 10 returns common logarithm of complex number IMLOG2 returns logarithm of complex number with base 2 IMPOWER returns integer power of complex number IMPRODUCT returns 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 between two complex numbers IMSUM returns the sum of two complex numbers OCT2BIN converts octal numbers into binary numbers OCT2DEC converts octal numbers into decimal numbers OCT2HEX converts octal numbers into hexadecimal numbers Reference:
Sogou encyclopedia -excel function.
2.EXCEL function formula and its application
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 Find the arithmetic average of all parameters.
The data calculated column displays the column label value of the reference cell. Display position connection connects multiple character texts or data in cells and displays them in one cell.
Character merge COUNTIF calculates the number of cells in the cell range that meet the specified conditions. Conditional statistical date gives the date of the specified value.
Display date DATEDIF calculation returns the difference between two date parameters. Calculate the number of days. Calculates the date specified in the parameter or the number of days in the reference cell.
Countdays Count returns the number of cells in a column of a database or list that meet the specified conditions and contain numbers. Conditional statistical frequency returns the frequency distribution of data in a region in the form of a vertical array.
Probability calculation IF returns the calculation result triggered by the corresponding condition according to whether the logical judgment result of the specified condition is true or false. Conditional calculation index returns the value of an element in a list or array, which is determined by the index values of row numbers and column numbers.
Data positioning INT rounds the value down to the nearest integer. The 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 from the first character of the text string and intercepts a specified number of characters.
Intercepted data LEN calculates the number of characters in a text string. Character statistical matching returns the corresponding position of the element in the array that matches the specified value in a specified way.
Match the position MAX to find the largest value in a set of numbers. The data calculation MID starts from the specified position of the text string and intercepts a specified number of characters.
Character truncation MIN finds the smallest value in a set of numbers. Data calculation MOD to find the remainder of the division of two numbers.
Data calculation month Find the month of the specified date or the date in the reference cell. Date calculation now gives the current system date and time.
When all parameter values are logically false, displaying the date-time OR only returns the logical false of the result, otherwise it returns the logical true. Logical judgment ranking returns the ranking of one value in a list of values relative to other values.
The data sorting right starts from the last character of the text string and intercepts a specified number of characters. Character Truncated Subtotal Returns the subtotal in a list or database.
Sum by category to calculate the sum of a set of values. Data calculation SUMIF calculates the sum of the values in the range of cells that meet the specified conditions.
Conditional data calculation text converts the corresponding numbers into text form according to the specified number format. Digital text conversion TODAY gives the date value of the system date display and converts the text string representing the numerical value into a numerical value. The text-to-value conversion VLOOKUP looks up the specified value in the first column of the data table and returns the numerical condition of the specified column in the current row of the data table to locate the corresponding week number given by WEEKDAY on the specified date.
Calculate part of Excel function list by week. 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 () ② Average function () ③ RANK function () Example: Rank (a1:$ a1:$ a15) Row numbers and column numbers are preceded by the "$" symbol A.
1 or 15 is called the column number, which indicates the position of the cell. The data cell is in column A, and the number is 1 or No. A,No. 15 ④ Maximum function MAX () ⑤ Minimum function Min () ⑤ Statistical function COUNTIF () calculates the number of cells that meet the requirements. Example: Countif (A 1: B5, "60") counts the number of people with a score greater than 60. Please note that the conditions should be enclosed in double quotation marks and entered in English. ⑦ Cell content merging CONCTENATE () Merges several strings into one string ⑧ RIGHT(A 1, 4) Extracts some rightmost characters of the string with a length of 4 digits ⑨LEFT () returns some leftmost characters of the string ⑩MIDB () Extracts the string with a specified length from the specified position of the characters to the right.
12, NOW () returns the system date and time in the computer, and MONTH () converts the serial number into the corresponding number of months. 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! -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- Used 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 arrays use a given set of constants as.
3.EXCEL function formula and its application
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 Find the arithmetic average of all parameters.
The data calculated column displays the column label value of the reference cell. Display position connection connects multiple character texts or data in cells and displays them in one cell.
Character merge COUNTIF calculates the number of cells in the cell range that meet the specified conditions. Conditional statistical date gives the date of the specified value.
Display date DATEDIF calculation returns the difference between two date parameters. Calculate the number of days. Calculates the date specified in the parameter or the number of days in the reference cell.
Countdays Count returns the number of cells in a column of a database or list that meet the specified conditions and contain numbers. Conditional statistical frequency returns the frequency distribution of data in a region in the form of a vertical array.
Probability calculation IF returns the calculation result triggered by the corresponding condition according to whether the logical judgment result of the specified condition is true or false. Conditional calculation index returns the value of an element in a list or array, which is determined by the index values of row numbers and column numbers.
Data positioning INT rounds the value down to the nearest integer. The 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 from the first character of the text string and intercepts a specified number of characters.
Intercepted data LEN calculates the number of characters in a text string. Character statistical matching returns the corresponding position of the element in the array that matches the specified value in a specified way.
Match the position MAX to find the largest value in a set of numbers. The data calculation MID starts from the specified position of the text string and intercepts a specified number of characters.
Character truncation MIN finds the smallest value in a set of numbers. Data calculation MOD to find the remainder of the division of two numbers.
Data calculation month Find the month of the specified date or the date in the reference cell. Date calculation now gives the current system date and time.
When all parameter values are logically false, displaying the date-time OR only returns the logical false of the result, otherwise it returns the logical true. Logical judgment ranking returns the ranking of one value in a list of values relative to other values.
The data sorting right starts from the last character of the text string and intercepts a specified number of characters. Character Truncated Subtotal Returns the subtotal in a list or database.
Sum by category to calculate the sum of a set of values. Data calculation SUMIF calculates the sum of the values in the range of cells that meet the specified conditions.
Conditional data calculation text converts the corresponding numbers into text form according to the specified number format. Numerical text conversion TODAY gives the system date display date value and converts the text string representing the numerical value into a numerical value. The text-to-value conversion VLOOKUP looks up the specified value in the first column of the data table and returns the numerical condition of the specified column in the current row of the data table to locate the corresponding week number given by WEEKDAY on the specified date.
Calculate part of Excel function list by week. 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 () ② Average function () ③ RANK function () Example: Rank (a1:$ a1:$ a15) Row numbers and column numbers are preceded by the "$" symbol A.
1 or 15 is called the column number, which indicates the position of the cell. The data cell is in column A, and the number is 1 or No. A,No. 15 ④ Maximum function MAX () ⑤ Minimum function Min () ⑤ Statistical function COUNTIF () calculates the number of cells that meet the requirements. Example: Countif (A 1: B5, "60") counts the number of people with a score greater than 60. Please note that the conditions should be enclosed in double quotation marks and entered in English. ⑦ Cell content merging CONCTENATE () Merges several strings into one string ⑧ RIGHT(A 1, 4) Extracts some rightmost characters of the string with a length of 4 digits ⑨LEFT () returns some leftmost characters of the string ⑩MIDB () Extracts the string with a specified length from the specified position of the characters to the right.
12, NOW () returns the system date and time in the computer, and MONTH () converts the serial number into the corresponding number of months. 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! -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- Used 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 for.
4. How to quickly apply the formula to an entire column in an Excel table?
Materials/tools: Excel20 10
1. Let's look at the following example. We need to make the gross profit of the goods, that is, the selling price-warehousing price. However, in line * * with 1489, the drop-down box should not only be on all the time.
2. First, we need to calculate the gross profit of the first commodity, that is, enter "=B2-C2" in the D2 cell and press enter, and the gross profit is 0.32.
3. Then click on cell D2 and enter the cell area we need to fill in at the marked place in the figure, that is, "D2:D 1489".
4. Hold down Ctrl and Enter keys at the same time, and D2:D 1489 will be selected.
5. Then click on the back of the formula in the figure, let the cursor flash behind, and then press Ctrl and Enter to complete the batch automatic filling.
6. You can see that the formula of 1489 is automatically filled in.
5. How does 5.Excel use formulas to calculate?
The calculation formula in EXCEL worksheet editing can be defined manually or through functions.
(1) Define the calculation formula manually. Select the cell where the calculation result is stored and enter the calculation formula (note: the calculation formula must start with =). For example, =A3*B3 means that the value of this cell is the product of the values of A3 cell and B3 cell. (2) Use function calculation to select the cell where the calculation result is stored, click the triangle list button on the right side of the σ button on the toolbar, select other functions from the open list, and select the required function in the pop-up dialog box (or enter the function name or function description, and click the search button to find the required function). At this point, the function of the selected function will be displayed at the bottom of the dialog box, and click the OK button. The Function Parameter Settings dialog box will open. Positioning the insertion point cursor in the parameter text box will display the setting method of the specified parameter. After setting the corresponding parameters, click OK to complete the corresponding operation according to the selected function and the set parameters.
6. Formula application in 6.Excel table
To understand your functions, you must first understand the following three main functions: (PS: typing is too tiring, so I have to use it)
The syntax structure of 1 IF function: if (condition, result 1, result 2).
If =IF(A, b, c) ",which means" if a, then b, otherwise c "
2.=DATEDIF (start date, end date, parameter) The start date and end date can be any legal date format (it is recommended not to use "."as a separator) or cell values.
For example, = datedif ("2005-5-3 ","2008- 1 1-28 ","y ") returns the number of years between 2008-5-3 and 2008- 1-28, and this value will change.
3.=INT () is rounded down to the nearest integer.
For example: INT(3.2)=3 INT(3.9)=3.
=IF(DATEDIF(E64，F64，" D ")/365 & lt； 1，INT(DATEDIF(E64，F64，" D")/365*5)，IF(DATEDIF(E64，F64，" D))/365 & lt； 10，5，IF(DATEDIF(E64，F64，" D ")/365 & lt； 20, 10,20)))
Then the whole paragraph is divided into three layers with IF, which means
=IF (condition, result 1, result 2)
Conditions: datedif (E64, F64, "D")/365
Results 1: int (datedif (E64, F64, "D")/365 * 5) The number of years worked by employees is rounded off, and those less than 2 years are counted as 2 years.
Result 2: If (datedif (E64, F64, "D")/365.
If used again in this paragraph, or it is divided into three layers.
Conditions: datedif (E64, F64, "D")/365
Results 1:5 is greater than 1 year, and less than1year, and rest for 5 days.
Result 2: If (datedif (E64, F64, "D")/365.
If used again in this paragraph, or it is divided into three layers.
Conditions: datedif (E64, F64, "D")/365
Results 1: 10, that is, the annual leave of less than 20 years is 10.
The result is 2:20, that is, more than 20 years, with 20 days of vacation every year.