Eighth trick: blind men feeling an elephant (Find function)
This trick is used on the original data string in a position to determine its location. Locating because the move, always starting from the specified location and returns the first matching string found in position, regardless of whether there followed a string of matches, a bit like the blind men feeling an elephant, which he said touched which hence the name "blind elephant."
Syntax
FIND (find_text, within_text, start_num)
Find_text is to find the text.
Within_text to find text that contains the text.
Start_num start looking for the specified character. within_text the first character is numbered 1 characters. If you ignore the start_num, it is assumed to be 1.
Note:
Use start_num to skip a specified number of characters. For example, suppose a text string "AYF0093.YoungMensApparel", if you want to find some of the text string that the first "Y" number, you can be start_num set to 8, so you do not find the serial number of the text part. FIND the first 8 characters from the beginning to find, but you can find at the next character find_text, then return to No. 9. FIND always returns the start within_text character number, if start_num greater than 1, will also skip the count of characters.
If find_text is empty text (), then FIND will return the value 1.
Find_text can not contain wildcards.
If there is no within_text find_text, then FIND returns the error value # VALUE!.
If start_num not greater than 0, then FIND returns the error value # VALUE!.
If start_num greater than the length of within_text, then FIND returns the error value # VALUE!.
Application example: the data containing different places, use "Find" function is very simple to determine the "province" to appear.
Explained
The formula "= FIND (province, A2)", the "Province" means the text you want to find a "province", (actual use, can also be very long string of characters). The object is to find to find the contents of cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ...", because there is no designated starting position, so the system from the first start. Back to the "3", said the "provincial" character in the third. The "Harbin, Heilongjiang Province ..." returns 4.
Find similar and, Search function also has the same function. The difference is, Find is case-sensitive, but Search is not case sensitive (when is the time to find the text is in English.)
In addition, Excel, text processing provides many functions to handle a particular double-byte characters (eg Chinese, Japanese) function, the function is generally added in the original "B", such as FIND, there a FINDB. Spoken before the LEFT, is the corresponding LEFTB and so on. In fact, the practical application, the use of non-"B" function would be sufficient. If you want to use with the "B" function, will have special attention, especially in the combination of the use function, which uses a function with a "B" form, the other has with "B" in the form of the function, all have to use The band "B" form, otherwise the result is likely to be wrong.
2010年12月16日星期四
Excel from the rookie to become a master of the IS function
The third measure: testing the waters (IS function)
This trick is used on the current value of a cell to determine the type, in order to know its type, and then take next course of action, it is called testing the waters.
IS function, a total of nine worksheet functions. Broad class of functions for the IS, you can test the type of numerical values based on parameter returns TRUE or FALSE. For example, if the value is a reference to the blank cells, the function returns a logical value ISBLANK TRUE, otherwise returns FALSE.
Syntax
ISBLANK (value)
ISERR (value)
ISERROR (value)
ISLOGICAL (value)
ISNA (value)
ISNONTEXT (value)
ISNUMBER (value)
ISREF (value)
ISTEXT (value)
Value for the required test values. Are as follows: blank (empty cell), error values, logical values, text, numbers, reference values, or for any of the above parameters the name of the reference.
Application examples:
Explained
The formula "= ISBLANK (A1)", said a cell of A1 to judge whether it is empty. The case is empty, it returns "True" value, if not empty, it returns "False) value.
The above example, the figure, B1 and B2 in the cell A1 and A2 respectively function the cells to determine whether the blank. The results show a true and a false
This trick is used on the current value of a cell to determine the type, in order to know its type, and then take next course of action, it is called testing the waters.
IS function, a total of nine worksheet functions. Broad class of functions for the IS, you can test the type of numerical values based on parameter returns TRUE or FALSE. For example, if the value is a reference to the blank cells, the function returns a logical value ISBLANK TRUE, otherwise returns FALSE.
Syntax
ISBLANK (value)
ISERR (value)
ISERROR (value)
ISLOGICAL (value)
ISNA (value)
ISNONTEXT (value)
ISNUMBER (value)
ISREF (value)
ISTEXT (value)
Value for the required test values. Are as follows: blank (empty cell), error values, logical values, text, numbers, reference values, or for any of the above parameters the name of the reference.
Application examples:
Explained
The formula "= ISBLANK (A1)", said a cell of A1 to judge whether it is empty. The case is empty, it returns "True" value, if not empty, it returns "False) value.
The above example, the figure, B1 and B2 in the cell A1 and A2 respectively function the cells to determine whether the blank. The results show a true and a false
From rookie to become a master of Excel If function
The second measure: it both ways (If the function)
This trick is used to perform a condition to judge the true and false values, calculated according to the logical truth value, return different results. If the result is true, it returns a true, if false, it returns another value, can be described both ways.
Syntax
IF (logical_test, value_if_true, value_if_false)
Logical_test that evaluates to TRUE or FALSE any value or expression. For example, A1> = 60 is a logical expression, if the value in cell A1 is greater than or equal to 60, expression is the TRUE, otherwise FALSE. This argument can use any comparison operator.
Value_if_true logical_test is TRUE, the returned value. For example, if the parameter is the text string "budget"and logical_test parameter is TRUE, then the IF function displays the text "budget. " If TRUE and value_if_true logical_test is empty, this argument returns 0 (zero). If you want to show TRUE, please use the logical value-based argument TRUE. Value_if_true can be another formula.
Value_if_false logical_test is FALSE, the returned value. For example, if the parameter is the text string "within budget"and logical_test parameter is FALSE, then the IF function displays the text "within budget. " Logical_test is FALSE, and if ignored Value_if_false (ie there is no comma after value_if_true), will return the logical value FALSE. If FALSE and Value_if_false logical_test is empty (ie, after value_if_true comma, and followed by closing parenthesis), this argument returns 0 (zero). Value_if_false can be another formula.
Help
Up to seven nested IF functions with value_if_false and value_if_true parameters can construct complex test conditions.
Value_if_true and value_if_false in the calculation of parameters, the IF function returns the appropriate return value after statement execution
This trick is used to perform a condition to judge the true and false values, calculated according to the logical truth value, return different results. If the result is true, it returns a true, if false, it returns another value, can be described both ways.
Syntax
IF (logical_test, value_if_true, value_if_false)
Logical_test that evaluates to TRUE or FALSE any value or expression. For example, A1> = 60 is a logical expression, if the value in cell A1 is greater than or equal to 60, expression is the TRUE, otherwise FALSE. This argument can use any comparison operator.
Value_if_true logical_test is TRUE, the returned value. For example, if the parameter is the text string "budget"and logical_test parameter is TRUE, then the IF function displays the text "budget. " If TRUE and value_if_true logical_test is empty, this argument returns 0 (zero). If you want to show TRUE, please use the logical value-based argument TRUE. Value_if_true can be another formula.
Value_if_false logical_test is FALSE, the returned value. For example, if the parameter is the text string "within budget"and logical_test parameter is FALSE, then the IF function displays the text "within budget. " Logical_test is FALSE, and if ignored Value_if_false (ie there is no comma after value_if_true), will return the logical value FALSE. If FALSE and Value_if_false logical_test is empty (ie, after value_if_true comma, and followed by closing parenthesis), this argument returns 0 (zero). Value_if_false can be another formula.
Help
Up to seven nested IF functions with value_if_false and value_if_true parameters can construct complex test conditions.
Value_if_true and value_if_false in the calculation of parameters, the IF function returns the appropriate return value after statement execution
Excel from the rookie to become a master of the Right function
Sixth move: to the end of the first left (Right function)
This move and the move the opposite way of the interception of the last character from the beginning, back to front interception of the contents of a user-specified length.
Syntax
RIGHT (text, num_chars)
RIGHTB (text, num_bytes)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars specify that you want RIGHT number of characters extracted.
Note: Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then RIGHT to return all the text.
If you ignore the num_chars, is assumed to be 1.
Application examples:
Explained
The formula "= Right (A2, 8)", said to be intercepted in the A2 data contents of the cell A2 "... Dongguan, Guangdong Province Tel: 22222222, ""8 " that started from the last of the interception of 8 characters, Therefore, the system returns "22222222. " Despite the length of the raw data missing, but we only care about the last eight phone numbers.
This move and the move the opposite way of the interception of the last character from the beginning, back to front interception of the contents of a user-specified length.
Syntax
RIGHT (text, num_chars)
RIGHTB (text, num_bytes)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars specify that you want RIGHT number of characters extracted.
Note: Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then RIGHT to return all the text.
If you ignore the num_chars, is assumed to be 1.
Application examples:
Explained
The formula "= Right (A2, 8)", said to be intercepted in the A2 data contents of the cell A2 "... Dongguan, Guangdong Province Tel: 22222222, ""8 " that started from the last of the interception of 8 characters, Therefore, the system returns "22222222. " Despite the length of the raw data missing, but we only care about the last eight phone numbers.
Excel from the rookie to become a master of the Left function
Fifth move: stay head to tail (Left function)
This trick is used to intercept the raw data. Interception approach is a character from the beginning, the interception of the contents of a user-specified length.
For example: In a work table, a column address information is recorded with provincial, city, street and so on. If you want to insert more than one, adding provinces in the data for selected provinces, it can intercept the function automatically, without manual input.
Syntax
LEFT (text, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars up to the LEFT of the specified number of characters extracted.
Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then LEFT returns all of the text.
If you omit num_chars, is assumed to be 1.
Application example: the data containing different places, the use of "Left" function is very simple separation from their provinces.
Explained
The formula "= Left (A2, 3)", said to be intercepted in the A2 data contents of the cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ... ", "3"indicates the beginning from the first of the interception of three characters, so The system returns "Guangdong province. "
This trick is used to intercept the raw data. Interception approach is a character from the beginning, the interception of the contents of a user-specified length.
For example: In a work table, a column address information is recorded with provincial, city, street and so on. If you want to insert more than one, adding provinces in the data for selected provinces, it can intercept the function automatically, without manual input.
Syntax
LEFT (text, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Num_chars up to the LEFT of the specified number of characters extracted.
Num_chars must be greater than or equal to 0.
If num_chars greater than the length of the text, then LEFT returns all of the text.
If you omit num_chars, is assumed to be 1.
Application example: the data containing different places, the use of "Left" function is very simple separation from their provinces.
Explained
The formula "= Left (A2, 3)", said to be intercepted in the A2 data contents of the cell A2 ", Dongcheng District, Dongguan City, Guangdong Province, ... ", "3"indicates the beginning from the first of the interception of three characters, so The system returns "Guangdong province. "
Excel from the rookie to become a master of the MID function
Seventh move: break off both ends (MID functions)
With the above two different strokes, this move not start from the first interception, the interception did not start from the last, but by the beginning of user-specified location and characters in length. Therefore, if the user specified starting from the first, and the Left function will be the same.
Syntax
MID (text, start_num, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Start_num is to extract the text of the first character position. Text start_num first character is 1, and so on.
Num_chars specify that you want MID to return from the text the number of characters.
Note:
If start_num greater than the length of the text, the MID returns empty text ().
If start_num less than the text length, but more than the text start_num num_chars with the length of the MID only returns up until the end of the text characters.
If start_num less than 1, MID returns the # VALUE!.
If num_chars is negative, MID returns the # VALUE!.
If num_bytes is negative, then the MIDB return an error value # VALUE!.
Application examples:
Explained
The formula "= MID (A2, 7,8)" expressed in the A2 A2 to intercept the data contents of the cell "****** 19,851,221 ****"," 7 "that started from 7, a total of Interception of 8 characters, so the system returns the user to intercept birthday time "19851221."
OK, we have studied three strategies, but the reader may have discovered, the actual work, the raw data does not appear so neat, so we are very easy to intercept with the above three strategies. For example, the third measure example, I use the ID number is 18, but in fact, many people still use 15-bit identification number, so that, due to the length of the original data are inconsistent, resulting in the interception will cut wrong. Another example is our first example, we cut the three, but in practice, and some provinces in the name itself is 3, and therefore this situation, simply can not get the right to apply to the content.
As I mentioned in the first, in the actual work use, a single formula often is not enough, the need to use the combination of tactics. For example, identity card numbers have different lengths, we can moves, add to the median of the judgments, if the length is 18 bits, then take an 8-bit, if it is 15, then take 6. Remember that we learned both ways in front of it? But this trick very useful Oh, we often used. In addition, I will introduce the following two strokes, is used to determine the contents of the cell. One is the "blind man feeling an elephant" (Find function), allowing users to specify the contents of the cell to locate the characters to confirm their location. When the position is confirmed, the interception is an easy thing. Another trick is the "Lu Ban foot of God" (Len function), the contents of the cell allows users to measure the length, obtained its length, do the corresponding intercept processing
With the above two different strokes, this move not start from the first interception, the interception did not start from the last, but by the beginning of user-specified location and characters in length. Therefore, if the user specified starting from the first, and the Left function will be the same.
Syntax
MID (text, start_num, num_chars)
Text is included to extract the text string of characters, you can directly enter the cell containing the target name of the text.
Start_num is to extract the text of the first character position. Text start_num first character is 1, and so on.
Num_chars specify that you want MID to return from the text the number of characters.
Note:
If start_num greater than the length of the text, the MID returns empty text ().
If start_num less than the text length, but more than the text start_num num_chars with the length of the MID only returns up until the end of the text characters.
If start_num less than 1, MID returns the # VALUE!.
If num_chars is negative, MID returns the # VALUE!.
If num_bytes is negative, then the MIDB return an error value # VALUE!.
Application examples:
Explained
The formula "= MID (A2, 7,8)" expressed in the A2 A2 to intercept the data contents of the cell "****** 19,851,221 ****"," 7 "that started from 7, a total of Interception of 8 characters, so the system returns the user to intercept birthday time "19851221."
OK, we have studied three strategies, but the reader may have discovered, the actual work, the raw data does not appear so neat, so we are very easy to intercept with the above three strategies. For example, the third measure example, I use the ID number is 18, but in fact, many people still use 15-bit identification number, so that, due to the length of the original data are inconsistent, resulting in the interception will cut wrong. Another example is our first example, we cut the three, but in practice, and some provinces in the name itself is 3, and therefore this situation, simply can not get the right to apply to the content.
As I mentioned in the first, in the actual work use, a single formula often is not enough, the need to use the combination of tactics. For example, identity card numbers have different lengths, we can moves, add to the median of the judgments, if the length is 18 bits, then take an 8-bit, if it is 15, then take 6. Remember that we learned both ways in front of it? But this trick very useful Oh, we often used. In addition, I will introduce the following two strokes, is used to determine the contents of the cell. One is the "blind man feeling an elephant" (Find function), allowing users to specify the contents of the cell to locate the characters to confirm their location. When the position is confirmed, the interception is an easy thing. Another trick is the "Lu Ban foot of God" (Len function), the contents of the cell allows users to measure the length, obtained its length, do the corresponding intercept processing
Xiao Yang Sum function with an example to explain the conditions detailed usage excel SumIF
Welcome to Office College, xiaoyang visit here waiting for you!
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about the conditions sum function SumIF
Features
Under the conditions specified sum on a number of cells.
Syntax
SUMIF (range, criteria, sum_range)
Range is the range of cells for the conditional.
Criteria for determining which cells will be added to the sum of the conditions, the form of a number, expression, or text. For example, the condition can be expressed as 32, "32", "> 32", "apples".
Sum_range sum of the actual need of the cell. Only when the corresponding cell in Range to meet the conditions, the fishes in the cell sum_range sum. If you omit sum_range. Directly on the Range in the sum of the cell.
Help
Microsoft Excel also provides a number of other functions, they can analyze data based on a condition. For example, if you want to calculate a range of cells, text strings or numbers within the number of occurrences, you can use the COUNTIF function. If you want the formula to return two values under a condition of a certain value (for example, sales bonuses based on the specified sales return), you can use the IF worksheet function
Example 1
B C D
1 Product Name Price (unit: yuan) of total sales commission
2 A 11 100
3 B 100 10
4 C 200 30
5 D 20 40
6 E 45 50
7 F 88 80
8
9 products price is greater than 50 the total sales commission 120 = SUMIF (C3: C8, "> 50", D3: D8)
Example 2
B C D
Math 13 gender language
Male 45 68 14
Female 91 81 15
Male 80 70 16
Male 65 87 17
18 M 7279
Female 77 87 19
20
The language scores of 21 boys total 304 = SUMIF (B14: B19, "Male", D14: D19)
The "male" and "women" because it is text-based, needs to be placed under the English double quotes ("man" and "female") in
Today, we continue to learn together with you in all commonly used Excel spreadsheet formulas (functions) of the detailed usage and master the formula, many of our work will greatly improve the efficiency, we must learn yo, there do not understand can go to the Office College Questions (ask.officexy.com) here to ask Oh, we have progress, I will be pleased got:)
This lecture, we learn about the conditions sum function SumIF
Features
Under the conditions specified sum on a number of cells.
Syntax
SUMIF (range, criteria, sum_range)
Range is the range of cells for the conditional.
Criteria for determining which cells will be added to the sum of the conditions, the form of a number, expression, or text. For example, the condition can be expressed as 32, "32", "> 32", "apples".
Sum_range sum of the actual need of the cell. Only when the corresponding cell in Range to meet the conditions, the fishes in the cell sum_range sum. If you omit sum_range. Directly on the Range in the sum of the cell.
Help
Microsoft Excel also provides a number of other functions, they can analyze data based on a condition. For example, if you want to calculate a range of cells, text strings or numbers within the number of occurrences, you can use the COUNTIF function. If you want the formula to return two values under a condition of a certain value (for example, sales bonuses based on the specified sales return), you can use the IF worksheet function
Example 1
B C D
1 Product Name Price (unit: yuan) of total sales commission
2 A 11 100
3 B 100 10
4 C 200 30
5 D 20 40
6 E 45 50
7 F 88 80
8
9 products price is greater than 50 the total sales commission 120 = SUMIF (C3: C8, "> 50", D3: D8)
Example 2
B C D
Math 13 gender language
Male 45 68 14
Female 91 81 15
Male 80 70 16
Male 65 87 17
18 M 7279
Female 77 87 19
20
The language scores of 21 boys total 304 = SUMIF (B14: B19, "Male", D14: D19)
The "male" and "women" because it is text-based, needs to be placed under the English double quotes ("man" and "female") in
订阅：
博文 (Atom)