I. Use formulas

Formulas are equations that operate on worksheet data as well as ordinary constants using operators and functions

=SUM(A1:B5)

The composition of a formula

  • The equal sign=: indicates that the following characters are formulas
  • Operator:+ -Etc arithmetic operator, or: .And the reference operator to the cell
  • Functions: Some predefined calculation methods
  • Cell reference:
  • Constant: The constant involved in the calculation

Basic usage of simple formulas

  1. Prepare a set of data, such as entering a set of numbers in cells A1 through B5
  2. Click a blank cell as the target cell for the output of the function’s calculated value
  3. On the Formula toolbar, select the first item auto Sum/Average/maximum… One of the
  4. Adjust the blue box on the interface to the range to be counted
  5. Double – click the blank cell or enter, the calculation result will appear in the target cell

1.1 – Operators in formulas

Excel formulas contain four operators: arithmetic, compare, text, and reference

Arithmetic operator meaning For example,
+ add 1 + 1
- Minus sign/minus 1 or 2-1
* take 2 * 2
/ In addition to 4/2
% The percentage 10%
^ Have been 10^2 (10 squared twice, which is 100)
Comparison operator meaning For example,
= Is equal to the A1=B1
> Is greater than A1>B1
< Less than A1<B1
> = Greater than or equal to A1>=B1
< = Less than or equal to A1<=B1
<> Is not equal to A1<>B1
Text operator meaning For example,
& A hyphen “hello”&” world”&”!” The “hello world!”
Reference operator meaning For example,
: area A1:B2Represents four cells A1, A2, B1 and B2
. The joint SUM(A1:B2,A4:B4)Represents the joint calculation of the sum of two regions
The blank space cross SUM(A1:C2 B2:D2)Compute the intersection of the two regions, which is the sum of B2:C2

1.2 – References to cells

References make it possible to use data in a given cell in a formula

[By address]

methods For example,
A single A1
multiple A1,B2,C3
According to the line 5:10
According to the column A:A
area A1:B2
cross A1:C2 B:B
Across the worksheet SUM(B1,sheet2! A1:B1) — For A1:B1 in B1 and Sheet2

Or the SUM (sheet1: sheet3! A1) — for all A1’s in Sheet1: Sheet3
Across the file SUM(B1,’/Path/[excel2.xlsx]工作表1′!A1)
  • Note if single row or single column is selectedx:xIn the form of
  • If the worksheet name or file name contains Spaces, it must be enclosed in quotation marks

Relative citation, absolute citation, mixed citation

image


  • Relative references
    • Form:A1:D9 A1,B1 等
    • Definition: For a defined formula, the reference address in the formula changes when cells are moved or the formula is copied
    • forA1,B1, drag the value in cell B1 to E5, and the formula automatically changes toA1,E5
    • For the formula defined in the target cell B19A1:B1After it is copied into B12, the formula automatically changes to relative positionA2:B2
  • Absolute reference
    • Form:$A$1:$D$9 $A$1,$B$1 等
    • Definition: The cell reference of a defined formula does not change
  • Mixed reference
    • Form:A$1:$D$9 $A1,$B$1 等
    • A combination of relative and absolute references is used in the direction of a row or column

1.3 – Formula error value

If you enter an error formula, the error value is displayed in the target cell

image


Error value Cause of occurrence
# # # # #! The calculation result is too long to accommodate cells; Increasing the column width can be solved
#DIV/0 The divisor is 0, or the formula is divided by an empty cell
#N/A There are no useful values in the formula, or function parameters are missing
#NAME? An unrecognized name was referenced
#NULL! The wrong range operator was used or the wrong cell reference was used
#NUM! Unacceptable parameters are used where numerical parameters are required, or the results of calculations are too large or too small
#RFF! An invalid cell was referenced
#VALUE! The formula contains an argument or operand of the wrong type

II. Common Excel functions

2.1 – Most commonly used functions

2.1.1 -sum function

Sum the values in the cell

SUM(number1,[number2],...)

image


The parameter name Whether must instructions
number1 Must be The first number to add. The number can be a number like 4, a cell reference like B6, or a cell range like B2:B8
number2-255 optional The second number to add. A maximum of 255 digits can be specified in this manner

For example:

SUM(A2:A10,C2:C10) SUM(A2:A10,C2:C10) SUM(A2:A10,C2:C10)'January Sales Schedule: December Sales Schedule'! SUM(A1:A10)+B1 SUM(A1:A10)-10 SUM(A1:A10)*B1 SUM(A1:A10)/10 Count (A2:L2)/COUNTA(A2:L2) '// Count (A2:L2)'Copy the code

2.1.2 – IF function

Make a logical comparison between values and expected values

IF(logical_test, value_if_true, [value_if_false])

image


The parameter name Whether must instructions
logical_test Must be Comparing conditions
value_if_true Must be Return value if logical_test is true
value_if_false optional Return value if logical_test is false

For example:

IF (C2 = "Yes", 1, 2) IF (C2 = 100, "Yes", "No") IF (C2 > B2, budget "overweight" and "within budget") IF (C2 > B2, C2 - B2, 0) / / in combination with other functions, to determine whether a cell is empty IF (ISBLANK (D2),"Not filled in"."Filled in"IF(D2=1, "YES",IF(D2=2, "No", "Maybe"))Copy the code

2.1.3-lookup function

Queries a row or column and finds the value at the same location in another row or column

LOOKUP(lookup_value, lookup_vector, [result_vector])

image


The parameter name Whether must instructions
lookup_value Must be The reference value to search for can be a number, text, logical value, name, or reference to a value
lookup_vector Must be The area where the reference value is located can contain only one row or column
result_vector optional The range in which the target value is located can contain only one row or column

For example:

LOOKUP(99,A1:A9,C1:C9);Copy the code

2.1.4-Choose function

Select one of a maximum of 254 values

CHOOSE(index_num, value1, [value2], ...)

image


The parameter name Whether must instructions
index_num Must be Specifies the selected numeric parameter or formula or cell reference, ranging from 1 to 256
value1, value2, … Must be Select a value or an action to perform

For example:

// The value of the second list parameter (in cell A3) CHOOSE(2,A2,A3,A4,A5"world"
CHOOSE(3,"Wide", 115,"world"8) // Equivalent to: SUM(B1:B10) SUM(CHOOSE(2,A1:A10,B1:B10,C1:C10)) SUM(A2:CHOOSE(2,A3,A4,A5)) CHOOSE(IF(B2>=90,1,IF(B2>=60,2,3)),"Optimal"."Qualified"."Poor")Copy the code

2.1.5-date function

Take three separate monthly day values and combine them into one date

DATE(year,month,day)

image


For example:

// Combine YEAR 2017 in cell C2, MONTH 01 in cell A2, and DAY 02 in cell B2. // In the target cell, the DATE format is 2017/1/2 DATE(C2,A2,B2). // Combine YEAR, MONTH, and DAY Function to create a new DATE(YEAR(C2)+5,MONTH(C2),DAY(C2)) based on another DATE cell // combine LEFT, MID, and RIGHT functions to add a string'20170102'Convert to DATE(LEFT(C2,4),MID(C2,5,2),RIGHT(C2,2))Copy the code

2.1.6 -days function

Returns the number of days between two dates

DAYS(end_date, start_date)

image


  • If two dates are numeric, DAYS uses EndDate -startDate to calculate the number of DAYS between the two dates
  • If any date argument is text, it is treated as a DATEVALUE(date_text) and returns an integer date, not a time component
  • If the date argument is a value outside the valid date range, DAYS returns #NUM! Error value
  • If the date argument is a valid date that cannot be parsed as a string, DAYS returns #VALUE! Error value

For example:

// When you enter a date directly in a function, you need to quote it with DAYS("2011-3-15"."2011-2-1"// Find the number of DAYS between the end date in A2 and the start date in A3Copy the code

2.1.7 – FIND function

Used to locate the first text string in the second text string and returns the value of the starting position of the first text string, counting from the first character of the second text string

FIND(find_text, within_text, [start_num])

image


The parameter name Whether must instructions
find_text Must be The text to find
within_text Must be It may contain the target text for the text to find
start_num optional Specifies the character index value at which the lookup starts, default is 1
  • FIND is case sensitive and does not allow wildcards (SEARCH, by contrast, has no such restrictions)
  • If find_text is empty (“”), FIND matches the first character in the search string (that is, the character numbered start_num or 1)
  • If there is no find_text in within_text, or if start_num is not greater than 0, or if start_num is greater than the length of within_text, the error #VALUE!

For example:

// locate the first "M" in cell A2"M"// FIND the first "M" from the third character of cell A2"M",A2,3); // Select from the first character in cell A2 to"# "before the textMID (A2, 1, FIND ("#"And A2, 1) - 1)Copy the code

2.1.8-index function

Returns a value or reference to a value in a table or region

image


Array format: INDEX(array, row_num, [column_num])

The parameter name Whether must instructions
array Must be Cell range or array constant
row_num Must be Selects a row in the array from which the function returns a value
column_num optional Selects a column in the array from which the function returns a value
image


Format: INDEX(reference, row_num, [column_num], [area_num])

The parameter name Whether must instructions
reference Must be A reference to one or more cell regions
row_num Must be The line number of a reference line from which the function returns a reference
column_num optional The column label of a column in a reference from which the function returns a reference
area_num optional Select the area in the reference from which you want to return the intersection of Row_num and Column_num

For example:

// INDEX(A2:B3,2,2) at the intersection of row 2 and column 2 in A2:B3 // INDEX(A2:B3,2,2) at the intersection of row 2 and column 2 in A2:B3 3,4},0,2) // return the SUM(B2:INDEX(A2:C6, 5, 2) of the cell region starting from cell B2 and ending at the intersection of row 5 and column 2 in cell region A2:A6) // reference form: INDEX((A1:C6, A8:C11), 2, 2, 2)Copy the code

III. Statistical analysis function practice

Statistical analysis function can be used to extract and screen the needed data from complex and tedious data

3.1 Automatic insertion of team rankings

image


RANK.EQ(L16,$L$16:$L$25)

Functions involved:

  • Rank. EQ: Returns the RANK of a number relative to other numbers in a list of numbers; If multiple values are ranked identically, the best ranking for that set of values is returned

B.

  1. Create a new column to place the ranking
  2. Rank. EQ retrieves the RANK value of the first team in the table
  3. Drag down to quickly copy the rank of all teams, since the first parameter L16 is relative, the other cells are automatically updated to the corresponding value

3.2 Average points of the top 3 teams

image


AVERAGE(LARGE($L$16:$L$25,{1; 2; 3}))

Functions involved:

  • The LARGE function: returns the KTH or first k maximum value in the dataset
  • The AVERAGE function: returns the AVERAGE of its arguments

B.

  1. Use the LARGE function to find the top three values
  2. Use the AVERAGE function to find the mean
  3. Replace LARGE with SMALL to get the average score of the bottom three

3.3 Splicing the names of the top 3 teams

image


CONCATENATE(LOOKUP(1,$M$16:$M$25,$G$16:$G$25),",",LOOKUP(2,$M$16:$M$25,$G$16:$G$25),",",LOOKUP(3,$M$16:$M$25,$G$16:$G$25 ))

Functions involved:

  • CONCATENATE function: Merges multiple text strings into one
  • LOOKUP function: Looks up a value from a single row or column or from an array

B.

  1. Set the ranking column in 3.1 to text format to comply with LOOKUP requirements
  2. According to this column, find 1 to 3 corresponding team names respectivelyLOOKUP(n,$M$16:$M$25,$G$16:$G$25)
  3. Use the CONCATENATE function to combine the team name with the comma for the separator

3.4 Score the highest score for a team with less than 35 points

image


LOOKUP(COUNTIF($L$16:$L$25,">=35")+1,$M$16:$M$25,$L$16:$L$25)

Functions involved:

  • LOOKUP function: Looks up one row or column and finds the value in the same place in another row or column
  • COUNTIF function: counts the number of cells in an area that meet a given condition

B.

  1. Use COUNTIF to count the number of teams in the area with 35 points or more
  2. Find the rank in the ranking column that is 1 larger than this number, and LOOKUP the score in the score column to find the corresponding score

3.5 Use simple VBA to count low-scoring teams

Functions are limited, but VBA scripts can greatly enrich our capabilities. Here demonstrates the use of a simple SECTION of VBA to achieve several team name splicing function, and 3.3 functions, obviously logical and automated a lot

image


01| Public Function TailTeams()
02|    Dim TNames As String 'to define a string traversal 03 | In Worksheets For Each c (" work "In table 1). The Range (" $L $16: $L $25"). The Cells'Traverse the fractional column 04 | If c. alue < 35 Then'if meet the conditions of 05 | TNames = TNames & ActiveSheet. Range (" $$" G & c.R ow), Cells (1). The Value of 06 | TNames = TNames & ", ""Use commas 07 | End If the euro | | Next 09 TNames = Left (TNames, Len (TNames) - 1)'cut off the last extra comma 10 | TailTeams = TNames'The Function return value 11 | EndCopy the code

Matters needing attention:

  • In Excel preferences, go to the View TAB and open the Development Tools TAB
  • Select allow macros when saving the filexslmformat

Here is only a basic demonstration, specific functional steps and more detailed KNOWLEDGE of VBA, please learn by yourself

3.6 Statistics of valid questionnaires

image


  • COUNTA function: counts the number of non-empty cells in a range
image


  • COUNTBLANK function: counts the number of empty cells in a region
image


  • COUNTIFS: Counts the number of cells specified for a given set of conditions

IV. Index of other useful functions

  • Date and time
    • DATE function: Returns the serial number of a specific DATE
    • DATEDIF function: calculates the number of days, months, or years between two dates. This function is useful in formulas used to calculate age.
    • DATEVALUE function: converts a date in text format to a serial number
    • The DAY function: converts the serial number to the month date
    • DAYS360 function: Calculates the number of days in a two-day period based on 360 days in a year
    • EDATE function: Returns the serial number of a date that represents the number of months before or after the start date
    • The EOMONTH function returns the serial number of the last day of the month before or after the specified number of months
    • The HOUR function: converts the sequence number to an HOUR
    • The ISOWEEKNUM function: returns the number of ISO weeks in a year for a given date
    • The MINUTE function: converts the sequence number to minutes
    • MONTH function: converts the serial number to a MONTH
    • NETWORKDAYS function: Returns the number of full workdays between two days
    • The networkdays. INTL function: returns the number of days of the full work day between two dates (using arguments to specify the number of days of the weekend and specify which days)
    • NOW function: returns the serial number of the current date and time
    • SECOND: Converts the sequence number to seconds
    • The TIME function: returns the serial number of a specific TIME
    • The TIMEVALUE function: converts the time of the text format to the serial number
    • TODAY function: returns the serial number of TODAY’s date
    • WEEKDAY function: converts the serial number to the date of the week
    • WEEKNUM function: converts the sequence number to a number representing the week of the year
    • The WORKDAY function: returns the serial number of a date before or after a specified number of business days
    • The wday.INTL function: returns the serial number with a date before or after the specified number of days of the WORKDAY (using the argument to specify and specify the days of the weekend)
    • YEAR function: converts the serial number to YEAR
    • The YEARFRAC function: returns the year fraction representing the number of days between start_date and end_date
  • information
    • CELL function: Returns information about the format, location, or content of a CELL
    • ERROR.TYPE function: Returns the number corresponding to the ERROR TYPE
    • INFO function: Returns information about the current operating environment
    • ISBLANK function: returns TRUE if the value is empty
    • ISERR function: Returns TRUE if the value is any error other than #N/A
    • ISERROR function: Returns TRUE if the value is any error value
    • ISEVEN function: returns TRUE if the number ISEVEN
    • ISFORMULA function: returns TRUE if there is a reference to the cell that contains the formula
    • ISLOGICAL: Returns TRUE if the value is a logical value
    • ISNA function: Returns TRUE if the value is an error #N/A
    • ISNONTEXT function: Returns TRUE if the value is not text
    • ISNUMBER function: returns TRUE if the value is a number
    • ISODD: returns TRUE if the number ISODD
    • ISREF function: Returns TRUE if the value is a reference value
    • ISTEXT function: returns TRUE if the value ISTEXT
    • N function: returns a value converted to a number
    • NA function: return error #N/A
    • The SHEET function: returns the worksheet number that references the worksheet
    • The SHEETS function: returns the number of SHEETS in the reference
    • TYPE function: Returns a number representing the data TYPE of the value
  • logic
    • AND function: returns TRUE if all arguments are TRUE
    • FALSE function: returns the logical value FALSE
    • TRUE function: returns the logical value TRUE
    • IF function: specifies the logical detection to perform
    • IFERROR: Returns the value you specify if the formula evaluates incorrectly; Otherwise return the result of the formula
    • IFNA function: If this expression resolves to #N/A, the specified value is returned; Otherwise, the result of the expression is returned
    • IFS function: checks if one or more conditions are met and returns the value corresponding to the first TRUE condition.
    • NOT function: logical negation of its arguments
    • OR function: returns TRUE if either argument is TRUE
    • SWITCH function: Evaluates an expression based on a list of values and returns the result corresponding to the first matched value. If there is no match, an optional default value may be returned.
    • XOR function: returns logical XOR values for all arguments
  • Find and reference
    • The ADDRESS function: returns the reference value as text to a single cell in the worksheet
    • The AREAS function: returns the number of AREAS involved in the reference
    • COLUMN function: Returns the referenced COLUMN number
    • COLUMNS function: Returns the number of COLUMNS contained in the reference
    • FORMULATEXT function: returns the formula given a reference as text
    • The GETPIVOTDATA function: Returns the data stored in the PivotTable
    • The HLOOKUP function looks up the first row of the array and returns the value of the specified cell
    • HYPERLINK function: Creates a shortcut or jump to open a document stored on a network server, Intranet, or Internet
    • INDEX function: selects values from references or arrays using an INDEX
    • INDIRECT functions: Return a reference specified by a text value
    • MATCH function: finds values in references or arrays
    • OFFSET function: Returns the reference OFFSET from the given reference
    • The ROW function returns the referenced line number
    • The ROWS function: returns the number of ROWS in a reference
    • RTD function: Retrieves real-time data from programs that support COM automation
    • TRANSPOSE function: returns the TRANSPOSE of an array
    • VLOOKUP function: looks in the first column of the array and moves between rows to return the value of the cell
  • Math and trigonometry
    • ABS function: Returns the absolute value of a number
    • ACOS function: returns the inverse cosine of a number
    • ACOSH function: returns the inverse hyperbolic cosine of a number
    • ACOT function: returns the inverse cotangent of a number
    • ACOTH: returns the hyperbolic inverse cotangent of a number
    • AGGREGATE function: Returns an AGGREGATE in a list or database
    • The ARABIC function: converts Roman numerals into ARABIC numerals
    • ASIN function: returns the arcsine of a number
    • ASINH function: returns the inverse hyperbolic sine of a number
    • ATAN function: returns the arctangent of a number
    • ATAN2 function: returns the arctangent of X and Y coordinates
    • ATANH function: returns the inverse hyperbolic tangent of a number
    • BASE function: Converts a number to a textual representation of a given cardinality
    • CEILING function: Rounds a number to the nearest integer or multiple of the specified base
    • MATH function: Rounds up a number to the nearest integer or multiple of the specified base
    • PRECISE functions: rounds numbers to the nearest whole number or to the nearest multiple of the specified base. The number is rounded up regardless of its sign.
    • COMBIN function: returns the number of combinations of a given number of objects
    • COMBINA function: returns the number of combinations of objects with duplicates for a given number
    • Cosine function: returns the cosine of a number
    • COSH function: returns the hyperbolic cosine of a number
    • COT function: returns the cosine of the Angle
    • COTH function: returns the hyperbolic cotangent of a number
    • CSC function: returns the cosecant of an Angle
    • CSCH function: returns the hyperbolic cosecant of an Angle
    • DECIMAL function: Converts the textual representation of a number in a given radix to a DECIMAL number
    • The DEGREES function converts radians to DEGREES
    • EVEN function: Rounds the number up to the nearest EVEN number
    • EXP function: returns e to the n
    • FACT function: Returns the factorial of a number
    • The FACTDOUBLE function returns a double factorial of a number
    • FLOOR function: rounds the number in the direction of decreasing absolute value
    • The flores. MATH function: rounds a number down to the nearest integer or the nearest multiple of the specified base
    • A flood. PRECISE function: rounds numbers down to the nearest integer or the nearest multiple of the specified base. The number is rounded down regardless of its sign.
    • The GCD function: returns the greatest common divisor
    • The INT function: rounds a number down to the nearest integer
    • The ISo. CEILING function: returns a number rounded up to the nearest integer or multiple of the nearest significant bit
    • LCM function: returns the least common multiple
    • The LN function returns the natural logarithm of a number
    • LOG function: returns the logarithm of a number to the specified base
    • LOG10 function: returns the logarithm base 10 of a number
    • The MDETERM function: returns the value of the matrix determinant of an array
    • MINVERSE function: returns the inverse matrix of the array
    • The MMULT function returns the matrix product of two arrays
    • MOD function: returns the remainder of the division
    • MROUND function: returns a number rounded to the required multiple
    • MULTINOMIAL function: A polynomial that returns a set of numbers
    • MUNIT function: returns the identity matrix or specified dimension
    • ODD: Rounds the number up to the nearest ODD number
    • PI function: returns the value of PI
    • POWER function: returns a POWER of a number
    • The PRODUCT function: multiplies its arguments
    • QUOTIENT function: Returns the integer part of the division
    • RADIANS function: convert degrees to RADIANS
    • RAND function: returns a random number between 0 and 1
    • RANDBETWEEN function: Returns a random number between two specified numbers
    • ROMAN function: Converts Arabic numerals to text ROMAN numerals
    • ROUND function: rounds a number to the specified number of digits
    • ROUNDDOWN function: Rounds numbers in the direction of decreasing absolute value
    • The ROUNDUP function: rounds the number in the direction that increases the absolute value
    • Secant function: returns the secant of the Angle
    • SECH function: returns the hyperbolic tangent of an Angle
    • SERIESSUM function: Returns the sum of the power series based on the formula
    • The SIGN function: returns the symbol of a number
    • Sine function: Returns the sine of a given Angle
    • SINH function: Returns the hyperbolic sine of a number
    • SQRT function: returns the positive square root
    • SQRTPI function: returns the square root of the product of a number and PI
    • SUBTOTAL function: Returns a summary of categories in a list or database
    • The SUM function: finds the SUM of arguments
    • SUMIF function: sums the specified cell according to the given condition
    • SUMIFS function: Adds cells to a range that meet multiple criteria
    • SUMPRODUCT function: returns the product sum of the corresponding array elements
    • SUMSQ function: Returns the sum of squares of parameters
    • The SUMX2MY2 function returns the sum of the squares of the corresponding values in two arrays
    • The SUMX2PY2 function returns the sum of the squares of the corresponding values in two arrays
    • The SUMXMY2 function returns the sum of squares of the differences between the corresponding values in two arrays
    • TAN function: returns the tangent of a number
    • TANH function: returns the hyperbolic tangent of a number
    • TRUNC function: truncates a number
  • The text
    • ASC function: Changes a full-corner (two-byte) English letter or katakana in a string to a half-corner (single-byte) character
    • BAHTTEXT function: converts numbers to text using ß (baht) currency format
    • CHAR function: returns the character specified by the code number
    • The CLEAN function: removes all non-print characters from the text
    • The CODE function: returns the numeric CODE for the first character in a text string
    • CONCAT function: Combines the text of multiple areas and/or strings, but does not provide delimiters or IgnoreEmpty arguments.
    • CONCATENATE function: Merges several text items into a single text item
    • DBCS function: Changes a half-corner (single-byte) letter or katakana in a string to a full-corner (double-byte) character
    • DOLLAR function: Converts a number to text using the ￥currency format
    • The EXACT function: checks whether two text values are the same
    • FIND, FINDB functions: FIND a text value within another text value (case sensitive)
    • FIXED function: Formats a number to text with a FIXED decimal number
    • LEFT, LEFTB functions: returns the leftmost character in the text value
    • LEN, LENB functions: Return the number of characters in a text string
    • The LOWER function: converts text to lowercase
    • MID, MIDB functions: Returns a specified number of characters from a specified position in a text string
    • NUMBERVALUE function: Converts text to numbers in a locale-independent way
    • PHONETIC function: extract the PHONETIC characters in the text string
    • PROPER function: capitalize the first letter of each word of a text value
    • REPLACE, REPLACEB: To REPLACE characters in text
    • REPT function: repeats text a given number of times
    • RIGHT, RIGHTB functions: Returns the rightmost character in the text value
    • SEARCH, SEARCHB functions: To find a text value within another text value (case insensitive)
    • SUBSTITUTE function: Replace old text with new text in a text string
    • T function: converts arguments to text
    • TEXT function: Formats a number and converts it to TEXT
    • TEXTJOIN function: Combines text from multiple fields and/or strings, including delimiters that you specify between the text values to be combined. If the delimiter is an empty text string, this function will validly concatenate these regions.
    • TRIM function: Removes whitespace from text
    • UNICHAR function: Returns the Unicode character referenced by the given numeric value
    • UNICODE function: Returns the number corresponding to the first character of the text (code point)
    • UPPER function: converts text to uppercase
    • VALUE function: converts a text argument to a number
  • Web
    • ENCODEURL function: returns a URL-encoded string
    • FILTERXML function: Returns specific data from XML content by using a specified XPath
    • WEBSERVICE function: Returns data in the Web service

The resources

  • Book.douban.com/subject/210…
  • Book.douban.com/subject/315…
  • Jingyan.baidu.com/article/22f…
  • Wenku.baidu.com/view/cc7353…
  • Support.office.com/zh-cn/artic…
  • Majk5168.blog.163.com/blog/static…
  • Stackoverflow.com/questions/3…