Commonly used functions in tables

excel is a very useful office software for organizing data and making tables! It is a commonly used office software for most white-collar workers! Today, the editor will introduce some Excel skills and some commonly used table functions!

IF function

Purpose: Return different results based on logical truth or falsehood. As a table logic judgment function, it can be used everywhere.

Function formula:

=IF (test condition, true value, [false value])

Function explanation:

When the first parameter " When the "test condition" is established, the second parameter is returned, and when it is not established, the third parameter is returned. IF functions can be nested layer by layer to solve multiple branching logic.

02 SUMIF and SUMIFS functions

Purpose: Sum the data of a data table according to set conditions.

SUMIF function

Function formula:

=SUMIF (area, condition, [summation area])

Function explanation:

Parameter 1: area, the cell area for conditional judgment;

Parameter 2: condition, which can be a number, expression, or text;

Parameter 3: [summation area], actual The numerical area of ​​summation. If omitted, parameter 1 "area" will be used as the summation area.

SUMIFS function

Function formula:

=SUMIFS(summation area, area 1, condition 1, [area 2], [condition 2],… …)

Function explanation:

The first parameter is the fixed summing area. Different from the SUMIF function that judges one condition, the SUMIFS function can be followed by adding multiple condition judgments in multiple areas.

03 VLOOKUP function

Purpose: The most commonly used search function, used to search for keywords in a certain area and return the value corresponding to the specified column.

Function formula:

=VLOOKUP (lookup value, data table, column sequence number, [matching condition])

Function explanation:

Equivalent to =VLOOKUP (what to look for, where to look for it, which column to look for, exactly or roughly)

The last parameter [matching condition] is When 0, a precise search is performed, and when it is 1 (or default), a fuzzy search is performed. If the fuzzy search is not found, the maximum value smaller than the first parameter "search value" is returned.

04 MID function

Purpose: to intercept some characters in a string. Some characters in some strings have special meanings. We can intercept them, or perform secondary operations on the intercepted characters to get the results we want.

Function formula:

=MID (string, starting position, number of characters)

Function explanation:

Change the string of parameter 1, Starting from the position indicated by parameter 2, intercept the length indicated by parameter 3 as the result returned by the function.

05 DATEDIF function

Purpose: Calculate the date difference. There are many comparison methods. You can calculate the difference in years, months, days, and every year. Or the number of days difference between fixed dates of each month, and calculation of any date period, etc., flexible and diverse.

Function formula:

=DATEDIF (start date, end date, comparison unit)

Function explanation:

Parameter 1 must be less than parameter 2, that is, the start date is less than the end date, otherwise an error will occur;

Parameter 3 can be the following, representing different calculation methods:

06 WORKDAY function

Purpose: Calculate the corresponding date of a specified date after (or before) N working days. It is generally used to calculate the end date of a job that needs to be completed in N days, etc. .

Function formula:

=WORKDAY (start date, number of days, [holiday])

Function explanation:

The number of days refers to working days. The function will automatically exclude Saturdays and Sundays. Of course, it will not work on holidays. You can list holidays and use parameter 3 to reference them, so that holidays or other specified dates can also be excluded.

07 SUMPRODUCT function

Purpose: Generally used for summation and counting of a certain condition or multiple conditions, is it a bit like SUMIF or COUNTIF? , in fact, it is more flexible than the above two functions.

Function formula:

=SUMPRODUCT (array 1,...)

Function explanation:

Its The parameters are composed of multiple arrays of the same dimensions. The corresponding elements in the arrays are multiplied and the sum of the products is returned.

When the array is a logical array {TRUE,...TRUE,...}, it can be used as a judgment condition or count. When the array is a numerical array {numeric value,...numeric value,...}, the Arrays can be used to implement summation.

08 RANK function

Purpose: used for ranking calculation.

Function formula:

=RANK (value, reference, [ranking method])

Function explanation:

Parameter 1 is involved in ranking Value;

Parameter 2 is the numerical area of ​​ranking;

Parameter 3 indicates ascending or descending order, ranking from high to low when it is 0 or ignored, ranking from low when it is non-0 to a high ranking.

09 FIND function

Purpose: Find whether the specified string is contained in a string, and return the starting position of the found string in the searched string. Generally used to determine whether a certain string is contained.

Function formula:

=FIND (string to be found, string to be found, [start search position])

Function explanation:

in parameters In 2, search parameter 1 and parameter 3 indicate the starting position of the search. If found, the found starting position will be returned. If not found, an error value will be returned.

10 IFERROR function

Purpose: Used to determine whether the results of other functions are error values. The most commonly used method is to use it if an error value is displayed after editing other functions in the table. IFERROR optimizes the formula to display it empty, making the table more beautiful.

Function formula:

=IFERROR (value, error value)

Function explanation:

Parameter 1 is generally a function expression. When parameter 1 Displayed as parameter 2 when returning an error value.

For more Excel-related technical articles, please visit the Excel Basic Tutorial column to learn!

The above is the detailed content of Commonly used functions in tables. For more information, please follow other related articles on the PHP Chinese website!