【 Weak current College 】 EXCEL in some commonly used functions in the application of the statistical work

1. objective: to understand the function of basic grammar and in the application of statistical work, improving the statistical work of electronic levels, increase productivity, improve the quality of their work
2. the function of the type
2.1 find and refer to the VLOOKUP function: expression: Sheet1 cell C1 = VLOOKUP (A1, Sheet2A: Sheet2C, 3, FALSE)
Meaning and purpose: search the first column in the table area that satisfy the conditions of the element, determine the question to retrieve a cell in the row number in the range, and then further returns the value of the selected cell, by default, the table is arranged in ascending order.
Function syntax description: "Sheet1 cell C1" is for displaying search results, "A1" is a question to retrieve a cell that is in the region must search to find or meet the value can be a cell reference or a specific parameter values, the "Sheet2A: Sheet2C" is the search area, that is, A column-to-C as the search area, "3" refers to the retrieved cell corresponding to the column ordinal or will soon need to look up a value returned by the column ordinal. "FALSE" means that if you retrieve the value returns "FALSE" to find the search fails.
2.2 mathematical function SUMIF: expression: Sheet1 cell C1 = SUMIF (Sheet2A: Sheet2A, A1, Sheet2C: Sheet2C)
Meaning and purpose: to specify the sum of the number of cells.
Function syntax description: "Sheet1 cell C1" is for display sum result cells, "Sheet2A: Sheet2A" is the sum of the search area can be a column or a column of a section, the former do not need to add an absolute reference to the "$", the latter must add "$", "A1" is the sum of the specific conditions, can be a parameter value or a cell reference, "Sheet2C: Sheet2C" is the sum of the data region, you can be an entire column or a column of a section, the former do not need to add an absolute reference to the "$", the latter must add "$", but must be the sum that corresponds to the criteria range;
2.3 mathematical function ROUND: expression: SHEET1 cell C1 = ROUND (A1, N)
Meaning and purpose: the value is rounded.
Syntax Note: "SHEET1 cell C1" results of a cell, "A1" original values, you can reference or a specific value, "N" is the specified number of decimal places.
2.3 text functions (Left, Right, Mid, and Len, Concatnate, Replace)
Meaning and usage instructions (from left to right):
Left is for the specified cell, the text and the left n characters, the expression "cell C1 = LEFT (A1, N)";
Right is specified cell, the text on the right n characters, the expression "cell C1 = RIGHT (A1, N)";
Mid is the specified cell, the text from the left n characters beyond X characters, the expression "cell C1 = MID (A1, N, X);"
Len is the calculation of the specified cell, the text contains the number of characters, the expression "cell C1 = LEN (A1)";
Concatnate is a set of text items into one text item, his function is equivalent to "&" function, the expression "cell C1 = Concatnate (A1, B1, C1, ..., ）”
Replace is used to substitute one string for another string specifies the length of the portion of string, formula is "cell C1 = Replace (A1, N, X, LLLL)"
"A1" is specified in the cell reference, or text, "N" is the specified cell or the starting character in the text, "X" number is the number of characters to be replaced or length, "LLLL" is used instead of new strings; the expression
The above function of parameters you can reference or a specific text, text functions used for conditions apply, as a condition of logical functions.
2.4 logical functions (And, False, Or If, Not,, True)
Meaning and usage instructions (from left to right):
And: If all parameter values are both true, returns true; otherwise, if either parameter is false, it returns false, AND the formula is "(A1 = 1, 2, B1 > ...)"; parameters you can reference or a numeric value, or it can be used to calculate values after the judgment of logical equation, etc., or even function nested;
False, TRUE: Returns the logical value FALSE or True;
If true and false values: implementation of the judgement, based on the specified condition is true and false of the logical evaluation and returns a different result, fundamental formula "IF (A1 = B1, N1, N2)",
Meaning if you type in the first condition, type set up, it returns a predetermined value, otherwise it returns the second N1 a predetermined value, conditions can be a reference or a numeric value, or it can be used to calculate values after the judgment of logical equation, etc., or even function nesting. Returns the logical value can be a reference or a specific numerical value;
Not: logical values to parameters, if negates the value of a parameter is true, return FALSE, if the parameter value is FALSE, returns true, basic formula "NOT (A1)," parameters "A1," you can reference or a numeric value, or it can be used to calculate values after the judgment of logical equation, etc., or even function nested;
Or: If you have any items in the argument is TRUE, it returns TRUE only if all arguments are FALSE returns FALSE; the expression is, "OR (A1, A2, A3 ... ..), the parameter" A1 "," you can reference or a numeric value, or it can be used for calculationValue after the judgment of logical equation, etc., or even function nested;
Logical functions used for conditions apply, as a nested function to determine the conditions of entry.
2.5 information functions (ISERR, ISERROR)
Meaning and usage instructions:
ISERR: If the test value is # N/any error other than A value, it returns TRUE, the expression "ISERR (A1)," parameter A1 can be a reference or a numeric value, or it can be used to calculate values after the judgment of logical equation, etc., or even function nested;
ISERROR: If the test value is # N/A, # VALUE, REF #, # DIV/0, # NUM and # NAME?, # NULL in any error values, returns true, otherwise it returns FALSE, the expression "ISERROR (A1),"parameter A1 can be a reference or a numeric value, or it can be used to calculate values after the judgment of logical equation, etc., or even function nested;
Information functions are used to perform logical judgement when determining and implementing the information true and false values, mainly used in nested function, separate use of little significance.
3. the function of application examples
3.1 establishment pedestal debt stock (& or SUMIF Concatnate, VLOOKUP, comprehensive application)
3.2 retrieve coding (IF, ISERROR, VLOOKUP comprehensive application)
3.3 get coding wheel type, size and dimension specifications (LEFT, RIGHT, MID, & or Concatnate comprehensive application)
3.4 set conditional filtering filter criteria (logical function IF\ text functions such as LEN \ information functions of application)
3.5 to bring state of encoding with State letter (& or Concatnate and comprehensive application)
3.6 composite multiple conditional sum (& or Concatnate comprehensive application)
3.7 detection code length (IF, the Len function of application)
Part of two groups have 3.8 same but not exactly the same as the difference between the look of the array (SUMIF comprehensive application)
3.9 subtotal of totals and grand totals (SUMIF applications)
3.10 duplicate detail on a set of data summaries and make every detail only exist (IF\SERROR\VLOOKUP and SUMIF comprehensive application)