1. 1 Excel Common Noun 2
1.2 cell reference 3
1.3 Formula Basis 9
1.4 Functional Basis 12
1.5 Error analysis and inspection 22
1.6 Excel data security 24
Chapter II Mathematics and Trigonometric Functions
2. 1 SUM returns the sum of all the numbers in the cell area of 30.
2.2 SUMIFS sums cells in a range that meets multiple conditions 32
2.3 SUMPRODUCT returns the sum of the corresponding products of elements between arrays 36.
2.4 MMULT returns the matrix product 38 of two arrays.
2.5 RAND returns a random value of 44.
2.6 EVEN returns the nearest even number 47, rounded in the direction of increasing absolute value.
2.7 INT returns the integer part of parameter 49.
2.8 CEILING returns the rounding value of 5 1 in the direction of increasing absolute value.
2.9 SUBTOTAL returns a summary of categories in a list or database 53
2. 10 PRODUCT returns the product value of all parameters 58.
2. 1 1 MDETERM returns the value 60 of the matrix determinant of an array.
2. 12 MINVERSE returns the inverse matrix 62 of the matrix stored in the array.
2. 13 FLOOR rounds the number down (towards zero) to the nearest multiple of 64 significant digits.
2. 14 MOD gets the remainder 66 after dividing two numbers.
Chapter 3 Logical Functions
3. 1, and determine whether all the specified conditions are true 70.
3.2 NOT's logical negation of its parameters 73
3.3 If different results are returned according to the specified conditions 76
3.4 OR determines that any specified condition is true, that is, returns true 8 1.
Chapter IV Text Functions
4. 1 series 84
4.2 Find the starting position of the characters in the search string (case-sensitive) 87
4.3 Search to find the starting position of string characters (case-insensitive) 90
4.4 Replace the old text with the new text in the text string 93.
4.5 LEFT returns the leftmost character 96 in the text value.
4.6 MID returns a specific number of characters 98 from the specified position in the text string.
4.7 RIGHT returns the rightmost character 10 1 in the text value.
4.8 FINDB Find the starting position of the string byte 104.
4.9 SEARCHB searches the starting position of string bytes (case-insensitive) 106
4. 10 REPLACEB replaces some characters in the string.
(according to the number of bytes) 109
4. 1 1 REPLACE some characters in the string (according to the number of characters)11.
4. 12 text sets the number format and converts it into text 1 13.
4. 13 MIDB returns a specific number of characters 1 17 from the specified position in the text string.
4. 14 LEFTB returns the leftmost character 1 19 in the text string according to the specified number of bytes.
Chapter V Statistical Functions
5. 1 AVERAGE returns the average value of the parameter 122.
5.2 AVERAGEA calculates the average of the values in the parameter table 127.
5.3 AVERAGEIFS returns the average value of all cells that meet multiple conditions. 13 1.
5.4 COUNT returns the digits of the parameter table 134.
5.5 COUNTA returns the number of cells with non-null values in the parameter list. 138.
5.6 COUNTIF calculates the number of cells in the area 142 that meet the given conditions.
5.7 COUNTIFS calculates the number of pixels that meet multiple conditions in the area 153.
5.8 MAX returns the maximum value in a set of values 157.
5.9 MAXA returns the maximum value 162 in the parameter table.
5. 10 MEDIAN returns the median of the given value 165.
5. 1 1 MIN gives the minimum value 167.
5. 12 SMALL Find the k-th minimum value in a set of values 17 1.
5. 13 LARGE Find the k largest values in a set of values 178.
5. 14 RANK returns the sort order of a set of numbers 182.
5. 15 FREQUENCY returns the vertical array of 186.
5. 16 AVEDEV returns the average value of the absolute deviation between a set of data and its average value 198.
5. 17 GEOMEAN returns the geometric average of a positive array or region 200.
5. 18 COUNTBLANK returns the number of cells with non-null values in the parameter list 202.
5. The19 percentile returns the value of the kth percentile in the range 204.
5.20 PERCENTRANK returns the percentile ranking of a specific value in the data set 206.
5.2 1 quartile returns the quartile of the data set 208.
Chapter 6 Date and Time Functions
6. 1 YEAR returns the year 2 16 corresponding to a certain date.
6.2 TODAY returns the current date 2 18.
6.3 Now return to the current date and time 220
6.4 MONTH returns the month corresponding to a certain date.
6.5 DATE returns the year, month and day of a specific date.
6.6 TIME returns the decimal value of a specific time 227.
Chapter 7 Finding and Referencing Functions
7. 1 line returns the referenced line number 230.
7.2 ROWS returns the number of rows in an array or reference 237
7.3 Column returns the referenced column label 239.
7.4 COLUMNS returns the number of columns in an array or reference 242
7.5 indirectly returns the reference 244 specified by the text string.
7.6 ADDRESS returns the cell reference address 25 1 according to the specified line number and column label.
7.7 OFFSET takes the specified reference as the reference frame, and obtains a new reference 254 by the given offset.
7.8 INDEX returns the value of the specified cell or array of cells (array form) 272.
7.9 LOOKUP returns a value of 279 from a single column or single column area (vector form)
7. 10 VLOOKUP finds the specified value in the first column of the data table.
And returns the value of the specified column in the current row of the data table 284.
7. 1 1 HLOOKUP looks for the specified value in the first row of the data table, and returns 29 1 in the same column of the specified row of the data table.
7. 12 MATCH returns the element position 296 that matches the specified value in the specified pattern.
7. 13 CHOOSE returns the value 30 1 in the specified numerical parameter table.
7. 14 Hyperlink creates a shortcut to open a file stored in a network server, intranet or Internet.
7.15area returns the number of regions contained in reference 307.
Chapter VIII Information Functions
8. 1 is even number judgment 3 10.
8.2 ISTEXT text judgment 3 14
8.3 N returns the converted value of 3 17.
8.4 NAMES returns the specified name defined in the specified workbook 322.
8.5 GET。 The object returns information about other macro formulas that can be used to process object 324.
8.6 EVALUATE evaluates the formula or expression expressed in words and returns the result 329.
Chapter II Mathematics and Trigonometric Functions
Case 1 Find the sum of book ordering prices (SUM) 30
Case 2 only summed up a score of 60 to 80 (SUMIFS) 33
Case 3: Count the Sumifs of Class Two, Grade Three, 34
Case 4 Sum up the number of times a class won the prize (SUMPRODUCT) 36
Case 5 How many times can the first person (MMULT) 38 be used?
Case 6 calculates the number of students (mmult) on the premise that different classes have the same name.
Case 7 Calculate the number of participants in Qianjin Middle School (MMULT) 40
Case 8 Calculate the total number and the number of fractions between 60 and 80 (MMULT) 42
Case 9 Randomly Arrange the Examination Location (RAND) of 20 Students 44
Case 10 randomly divides tree planters into three schools (RAND) 46
Case 1 1 statistical reference number (even number) 47
Case 12 report format conversion (INT) 49
Case 13 report format conversion (upper limit) 5 1
Case 14 Subtotal score as required 54
Case 15 Subtotal) 55
Case 16 only ranks the screener subtotal) 56.
Case 17 Calculate the discount price of each textbook (product) 58
Case 18 solving simultaneous equations of two variables (MDETERM) 60
Case 19 Solving Multivariate Simultaneous Equation (MINVERSE) 62
The lower bound function of case 20 deals with the positive-negative mixed region (lower bound) 64.
Case 2 1 Add the value bit by bit to one digit (MOD) 66.
Chapter 3 Logical Functions
Case 22: Judging whether a student meets the conditions for awarding a scholarship (and) 70
Case 23: If all the judges pass, enter the final (and) 7 1.
There is no referee in case 24, and there is no final (73).
Case 25: Calculating the Number of Numbers in the Score Area (Not) 74
Case 26 Assessing whether students' grades are qualified (IF) 76
Case 27 Automatically generate comments (if any) according to students' grades 77
Case 28 Ranks the scores from largest to smallest (if) 78
Case 29 Exclude Null Value (If) 79
Case 30 Judging whether a faculty member retires or not according to his age 8 1
Chapter IV Text Functions
Case 3 1 Calculate the average score and connect) 84
Case 32: String the names of the top three people 85
Case 33 Extraction of School and Hospital Address (Search) 88
Case 34 Separating Grade or Major from Class Name (Search) 9 1
Case 35 extracts the final sprint score 94
Case 36 Counting the number of primary school students (left) 96
Case 37: Extracting Scores and Calculating the Mean (MID) 99
Case 38: Extract the name of the contestant (MID) 100.
Case 39 ranks all personnel according to the average score (right) 10 1.
Case 40: Extract students' English names (FINDB) 105.
Case 4 1 extract the version number of each software (SEARCHB) 107.
In case 42, the ID number 15 is upgraded to 18 (REPLACEB) 1 10.
Case 43 Maximum time to find (replacement) 1 12.
Case 44 calculates the date of birth of students according to the ID number (text) 1 15.
In case 45, the names of winners of all grades (MIDB) are extracted (1 18).
Case 46: Extract the name of the province and city where the students are located (LEFTB) 120.
Chapter V Statistical Functions
Case 47 Calculate the average score (ignoring absent students) (average) 122.
The average score of case 48 is above 90 (123).
Case 49 calculates the average of all worksheets except the current table.
(average) 125
Case 50 calculates the average grade, and the blank grade also calculates (AVERAGEA) 127.
Case 5 1 Calculate the average winning rate of all the people in Grade Two.
(average) 128
In case 52, the average score of the top three people is 130.
In case 53, the average value 132 is removed.
Case 54 Calculation Pass Rate (Count) 134
Case 55: Count135 of each score.
Case 56 counts how many players (counting) are 136.
Case 57 Abnormal Attendance Statistics (COUNTA) 138
Case 58 Judging whether someone is absent from the exam (COUNTA) 139
Case 59 According to the statistics of graduation schools, the number of middle school graduates (Countif) is 142.
Case 60 calculates the same amount of data (COUNTIF) 143 in two columns.
Case 6 1 Count the number of people who have entered the top ten for three consecutive times (COUNTIF) 145.
Case 62 COUNTIF) 146
Case 63 Number of Non-duplicate Data in COUNTIF 147
Case 64: Countif148.
Example 65 Countif150 of vacuum and false empty cells was calculated.
Case 66 Mixed number (COUNTIF) 15 1 is the roster table.
Case 67: COUNTIFS) 154.
Case 68: Countifs155 of the winners of Class Two and Class Three math competitions.
Case 69 calculates the elimination number of each class according to the height (COUNTIFS) 156.
Case 70 Find the first student's name (MAX) 157.
Case 7 1 Calculate the most failed times of defense (MAX) 158.
Case 72 is lower than the average score 159 (Max).
Case 73: Calculate the maximum total score (MAXA) 162 for those with a language score above 90.
Case 74 calculates the maximum total score (MAXA) of boys whose math score is equal to 100, 164.
Case 75 Calculating median) 165
Case 76 Determine the best score (points) 167
Case 77 Separate subjects from grades (minimum) 168
Case 78 calculates the minimum score (min) of the first person in five classes (170).
The third grade (small) of case 79 is 17 1.
Case 80 calculates the average of the last three scores (small) 172.
Case 8 1 Rank in ascending order (small) 173.
Case 82 lists the first grade of three classes (primary) 174.
Case 83 lists three worksheets, and the last three scores in column B are (small) 175.
Case 84 Number of people between the third and sixth lowest scores (small) 176
Case 85 calculates the number (large) tied with the third largest value (178).
Case 86 List of Students by Grade (Large) 179
Case 87 won the first prize for the last time, which was big) 180.
Case 88 ranking) Student scores 182.
Case 89 calculates the same number of two columns (rank) 183.
Case 90 inquires about the rank185 of someone's grades in three classes.
Case 9 1 Count the number of people in each fraction (frequency) respectively 187.
Case 92: The highest winning frequency (188)
Case 93: How many times can the calculation be successful at most (frequency) 189.
Case 94 calculates the frequency distribution of three discontinuous intervals (190).
Case 95 calculates the number of primary schools plus junior high schools and the number of secondary schools plus universities (frequency) 19 1.
Case 96 Calculating the Frequency Distribution of Text (192)
Case 97 Winning List (Frequency) 193
Case 98 Who won the championship the most times (frequency) 194
Case 99 Chinese ranking (frequency) 195
Case 100 who won the second place (frequency) 196?
Case 10 1 calculate the average deviation (Avedev) of quality coefficient of parts 198.
Case 102 Calculating the geometric growth rate of profits 200
Case 103 COUNTBLANK) 202
Case 104 Calculate the percentage of the specified percentage according to the completion of Exercise 204.
Case 105 calculates the amount of exercises completed by one student among all students.
Percentile ranking) 206
Case 106 Calculate quartile according to the completion of students' exercises.
Chapter 6 Date and Time Functions
Case 107 Calculate the average charge for computer training before 2000 (year) 2 16.
Case 108 How many subjects should I finish this month (today) 2 18.
Case 109 determines whether it is time to discard (now) 220.
Case 1 10 Calculate the purchase quantity of pen container and brush in August (month) 222
Case 1 1 1 Determine the date of Mother's Day this year.
Case 1 12 arranging meeting time) 227
Chapter 7 Finding and Referencing Functions
Case 1 13 What was the last test (platoon) 23 1?
Case 1 14 Calculate the average score of 1 1 to the 30th student (row).
The case 1 15 calculates the score ranking, but it cannot produce a tied ranking (ranking) of 233.
Case 1 16 sorts the classes in ascending order (line 234)
The case 1 17 lists the first student's name (line) 235.
Case 1 18 tests whether each volunteer is the same as the previous (row) 237.
Case 1 19 class score query (column) 239
The case 120 lists the daily absence list (column) 240.
Case 12 1 extract the data (column) in the lower right corner of the reference area 242.
Case 122 Indirect) 245
Case 123 Merging Data from Three Worksheets (Indirect) 246
Case 124 Indirect) 248
Case 125 Calculating the Maximum Indirect Cost of Five Worksheets 249
Case 126 lists the names (addresses) of class scores according to the drop-down list.
Case 127 Query Score (Offset) 255
Case 128 Count how many times 10 Games (offset) 256 broke the record.
Case 129 Calculate the number of students who failed at least two subjects (offset) 257
The case 130 lists the best theme (offset) 258.
Case 13 1 Calculate the number of subjects whose passing rate is less than 50% (offset) 259.
Case 132 Calculate which subject has the highest total score (offset) of 260: Chinese, English, chemistry and politics.
Case 133 lists the names of the students with the highest passing rate (deviation) 26 1.
Case 134 arrangement of trees's most classes (offset) 262.
Case 135 Multi-table Score Query (Offset) 263
Case 136 Calculate whether the total score of each student is higher than the class average score (offset) of 265.
Case 137 Calculate the number of subjects that each student enters the top three (offset) 266.
The total number of subjects (offset) calculated by case 138 is higher than the average value of individual subjects (267)
Case 139 lists the last three classes with an average score (deviation) of 268.
Case 140 Training Chart of Accounts Reorganization (Offset) 269
In the case of 14 1, the score and name (offset) of the first place in each class are calculated respectively.
Case 142 Calculate the number of people (index) between 60 and 95.
Case 143 Ranks students according to their height (index) 274
Case 144 teacher teaching reorganization table (index) 275
Case 145 extracts the intersection (index) of three irregular regions 277.
Case 146 Calculate who won the most first prize in a class for six years (search) 280).
The case 147 lists all the names of each ranking (search) 28 1.
Case 148 search) 282
Case 149 converts the score into VLOOKUP) 285.
Case 150 Find double-column information (VLOOKUP) 286
Case 15 1 listing names by educational background (VLOOKUP) 288
Case 152 Multiple worksheets find the maximum value (VLOOKUP) 289
Case 153 finding data in the same row (HLOOKUP) 292
Case 154 Making an Admission Ticket (HLOOKUP) 293
Case 155 calculating the total number of make-up courses (matching) 297
Case 156 Inserting Separate Rows by Category (Matching) 298
Case 157: The number of people in Class 1 and Class 2 (competitions) is 299.
Case 158 lets VLOOKUP function select in multiple areas) 302.
Case 159 Select hyperlink) 305.
Case 160 Counting the number of branches (regions) 307
Chapter VIII Information Functions
Case 16 1 Calculate the final average score (ISEVEN) 3 10.
Case 162 extract the final score details (ISEVEN) 3 1 1.
Case 163: Select the first list of the year (ISTEXT) 3 14.
Case 164 Statistics on the number of students with scores above 60 in all subjects in each class (N) 3 17.
Case 165 Coloring Data Interval (N) 3 19
The case 166 lists all the names in the workbook 322.
Case 167 extract comments from cells (get. Object) 327
Case 168 Get data by separator and evaluate it) 329