catalogue
The first 1 chapter
Python Basics —— study Python Know and know 1
1.1 What is? Python2
1.2 Why learn to use Python handle Excel form 2
1.3 Teach you how to install Python2
1.3.1 download Python3
1.3.2 install Python4
1.3.3 Verify that the installation was successful 9
1.4 install Python Integrated development tools PyCharm10
1.4.1 download PyCharm10
1.4.2 install PyCharm11
1.4.3 set up PyCharm15
1.4.4 Create project 18
1.5 Python Output and input of 20
1.5.1 output 20
1.5.2 input 21
1.6 Python Code comments for 22
1.6.1 Single-Line Comments 22
1.6.2 multiline comment 23
1.7 Python Object details 23
1.7.1 Class definition 24
1.7.2 Identity of the object 24
1.7.3 Type of object 24
1.7.4 The value of the object 25
1.7.5 Object properties 25
1.7.6 Object method 25
1.7.7 Objects and variables 25
1.8 Python Numbers and strings in 26
1.8.1 number 26
1.8.2 character string 27
1.9 Arithmetic operator 28
1.9.1 plus (+)28
1.9.2 reduce (?)28
1.9.3 ride (*)29
1.9.4 except (/)29
1.9.5 Take mold (%)29
1.9.6 power (**)29
1.9.7 Take integer (//)30
1.10 Comparison operator 30
1.10.1 be equal to (==)30
1.10.2 Not equal to (!=)30
1.10.3 greater than (>)30
1.10.4 less than (1.10.5 Greater than or equal to (>=)31
1.10.6 Less than or equal to (<=)31
1.11 Assignment Operators 31
1.11.1 Assignment operation 32
1.11.2 Cumulative assignment operation 32
1.12 Logical operator 33
1.12.1 and( And )33
1.12.2 or( or )34
1.12.3 not( wrong )34
1.13 member operator 35
1.14 format string 35
1.14.1 Format strings using location and keywords 35
1.14.2 Number formatting 36
1.14.3 Snap settings 36
1.15 breakpoint debugging 37
The first 2 chapter
Python library —— Installation and learning of third-party library 39
2.1 What is a module , package , library 40
2.2 install Excel Read Library xlrd40
2.3 xlrd Module import 42
2.4 read Excel workbook , Worksheet information 43
2.4.1 read Excel workbook 43
2.4.2 read Excel Worksheet 43
2.5 read Excel that 's ok , column , Cell information 44
2.6 install Excel Write stock in xlwt46
2.7 New workbook , Create a new worksheet and write data to cells 47
2.8 install Excel Modify Library xlutils48
2.9 Modify Workbook , Worksheet , Cell 48
The first 3 chapter
Python Process control —— Loop statement and branch statement 50
3.1 for Circular statement 51
3.1.1 Circular string 51
3.1.2 Number of cyclic sequences 52
3.1.3 for Application case of circular statement ： Batch new workbook 53
3.1.4 for Nested loop statement 54
3.1.5 for Application cases of nested loop statements ： Make 99 multiplication table 56
3.2 while Circular statement 57
3.2.1 Number of cyclic sequences 58
3.2.2 Circular string 59
3.2.3 while Application case of circular statement ： Batch new worksheet 59
3.2.4 while Nested loop statement 61
3.2.5 while Application cases of nested loop statements ： Batch new workbook , Worksheet 62
3.3 if Conditional statement 63
3.3.1 if Standard usage of conditional statements 63
3.3.2 if Application cases of conditional statements ： Judge the grade according to the score 64
3.4 if Conditional branch statement 65
3.4.1 if Standard usage of conditional branch statements 66
3.4.2 if Conditional branch statement single line writing 66
3.4.3 if Application case of conditional branch statement ： Sort and count numbers 67
3.5 if Multi conditional branch statement 68
3.5.1 if Standard usage of multi conditional branch statements 69
3.5.2 if Application case of multi conditional branch statement ： Multi level judgment of scores 70
3.6 break sentence 72
3.6.1 break stay while Application of loop statement 72
3.6.2 break stay for Application of loop statement 72
3.6.3 break Statement application case ： The first month when the mark meets the standard 73
3.7 continue sentence 74
3.7.1 continue stay while Application of in loop 74
3.7.2 continue stay for Application of in loop 75
3.7.3 continue Statement application case 75
The first 4 chapter
Python Ordered object —— String processing technology 77
4.1 String slicing 78
4.1.1 Single character slice 78
4.1.2 Multi character slicing 78
4.1.3 String slicing application case ： Gender determination based on ID number 79
4.2 String statistics 81
4.2.1 Statistics string length 81
4.2.2 String statistics by condition 81
4.2.3 String statistics application case ： Count the number of occurrences of each level 82
4.3 String search 84
4.3.1 use index Function search string position 84
4.3.2 use find Function search string position 85
4.3.3 String search application case ： Extract the information of the specified location 86
4.4 String substitution 87
4.4.1 String substitution 87
4.4.2 String replacement application case ： Sort out nonstandard delimiters 88
4.5 String splitting and merging 89
4.5.1 Split string into list 89
4.5.2 Merge list into string 90
4.5.3 Application case of string splitting and merging ： Summarize non-standard data in multiple tables 91
The first 5 chapter
Python Ordered object —— List processing technology 93
5.1 Creating and deleting lists 94
5.2 List slice 94
5.2.1 Single element slice 94
5.2.2 Multi element slice 95
5.2.3 List slicing application case ： Average data by row 96
5.3 Addition of list elements , Delete and modify 97
5.3.1 Modification of list elements 97
5.3.2 Addition of list elements 98
5.3.3 Deletion of list elements 99
5.3.4 List comprehensive application cases ： Sum fractions by row 100
5.4 List operator 102
5.4.1 List operator Basics 102
5.4.2 List operator application case ： Statistics of multi worksheet data by conditions 103
5.5 List derivation 104
5.5.1 Standard list derivation 104
5.5.2 List derivation variation 105
5.5.3 Nested list derivation 106
5.5.4 Conditional list derivation 107
5.5.5 List derived application cases 1： Automatically summarize multi sheet data 107
5.5.6 List derived application cases 2： Summarize multiple workbook data 109
5.6 List conversion operation 110
5.6.1 Class object conversion list111
5.6.2 Reverse list reverse112
5.6.3 List copy copy112
5.6.4 List combination zip114
5.6.5 List conversion application case ： Add serial numbers to names in the list 115
5.7 List common statistical methods 117
5.7.1 Common statistical functions 1117
5.7.2 List statistics application cases 1： Statistics of annual salary information of each person 118
5.7.3 Common statistical functions 2120
5.7.4 List statistics application cases 2： Count and count by level 120
The first 6 chapter
Python Ordered object —— Tuple processing technology 123
6.1 Creation and deletion of tuples 124
6.2 Basic operations of tuples 124
6.2.1 Combination of tuples 124
6.2.2 Duplication of tuples 125
6.2.3 Loop of tuples 127
6.2.4 Class object conversion 128
6.2.5 Tuple application case ： Convert single column data to multi row and multi column data 128
6.3 Tuple common statistical functions 131
The first 7 chapter
Python Unordered object —— Dictionary processing technology 132
7.1 Basic operation of dictionary 133
7.1.1 Creation and deletion of dictionary 134
7.1.2 Acquisition of key values in dictionary 134
7.2 Modification of dictionary key value , Add and delete 135
7.2.1 Increase of dictionary key value 135
7.2.2 Deletion of dictionary key value 136
7.2.3 Modification of dictionary key value 136
7.2.4 Dictionary key value application case 1： Extract the last record of each shift 137
7.2.5 Dictionary key value application case 2： Total and average scores by name 138
7.3 Dictionary conversion operation 140
7.3.1 Class object conversion dict140
7.3.2 dict.fromkeys Conversion method 141
7.3.3 Dictionary conversion application case ： Find unique value for multiple columns 142
7.4 Dictionary comprehensive application case 143
7.4.1 Dictionary comprehensive application case 1： Get incomplete list 144
7.4.2 Dictionary comprehensive application case 2： Multi workbook data merge 145
7.4.3 Dictionary comprehensive application case 3： Data statistics and distribution to different workbooks 147
The first 8 chapter
Python Unordered object —— Set processing technology 151
8.1 Creation and deletion of collections 152
8.2 Addition and deletion of collection elements 152
8.2.1 Addition of collection elements 152
8.2.2 Deletion of collection elements 153
8.2.3 Application case of adding collection elements ： Find unique value for multiple columns 154
8.3 Size comparison between collections 155
8.3.1 Comparison operation of sets 155
8.3.2 Application case of set comparison operation ： Judge whether the specified multiple levels exist 156
8.4 Conversion operation of collection 157
8.4.1 Class object conversion set157
8.4.2 Set conversion application case ： Get a list of non duplicates in each worksheet 158
8.5 Set operation 160
8.5.1 Union operation 160
8.5.2 Intersection operation 161
8.5.3 Difference set operation 163
8.5.4 Symmetric difference set operation 164
8.5.5 Summary of set operation 166
8.6 Application case of set operation 166
8.6.1 Union application case of set ： Find unique values in multiple tables and columns 166
8.6.2 Application case of intersection of sets ： Find the same value for multiple columns 168
8.6.3 Application case of difference set of sets ： Obtain the non-compliance month according to the compliance month 170
The first 9 chapter
Code optimization tool ——Python Custom function 173
9.1 Custom function writing specification 174
9.1.1 Definition of function 174
9.1.2 Creating and calling custom functions 174
9.1.3 Custom function application case ： Definition and application of average function 176
9.2 Writing method and application of required parameters 177
9.2.1 Required parameter ( Location parameters )177
9.2.2 Application case of required parameters of user-defined function ： Segment numbers 178
9.3 Writing method and application of optional parameters 179
9.3.1 Optional parameters ( Default parameters )180
9.3.2 Application cases of optional parameters of custom functions ： simulation vlookup Application of function 180
9.4 Writing method and application of keyword parameters 182
9.4.1 Keyword parameters 182
9.4.2 Application cases of keyword parameters of custom functions ： Sort merge string 184
9.5 Writing method and application of indefinite length parameter 186
9.5.1 Indefinite length parameter 1186
9.5.2 Indefinite length parameter 2187
9.5.3 Application case of indefinite length parameter of user-defined function ： Replacement function enhanced 187
9.6 Writing method and application of anonymous function 189
9.6.1 Syntax structure of anonymous functions 189
9.6.2 Common writing methods of anonymous functions 190
9.6.3 Application case of anonymous function of custom function ： Gender determination based on ID number 191
9.7 Custom functions are stored in .py In the file 192
9.7.1 Functions are defined separately .py In the file 193
9.7.2 Functions are defined in folders 194
The first 10 chapter
Python Advanced function —— Application of common higher order functions 196
10.1 map Conversion function 197
10.1.1 map Second order of function 1 Parameters are built-in functions 197
10.1.2 map Second order of function 1 The first parameter is a custom function 198
10.1.3 map Second order of function 1 Arguments are anonymous functions 198
10.1.4 map Second order of function 1 Multiple parameters 199
10.1.5 Higher order function map Application case ： Convert 2D table to 1D table 199
10.2 filter Filter function 201
10.2.1 use filter Filter function filter list 202
10.2.2 Higher order function filter Application case ： Calculate American ranking , Chinese ranking 202
10.3 Sorting function sort And sorted204
10.3.1 Sorting function sort204
10.3.2 Sorting function sorted206
10.3.3 Higher order function sort Application case ： Sort data in a string 207
10.3.4 Higher order function sorted Application case ： Improve the algorithm of American ranking and Chinese ranking 208
The first 11 chapter
first-class Python Third party Library ——openpyxl library 211
11.1 install openpyxl library 212
11.2 Basic operation of Workbook 212
11.2.1 New workbook 212
11.2.2 Reading of Workbook 213
11.2.3 Workbook application case ： Batch new workbook 214
11.3 Basic operation of worksheet 215
11.3.1 New sheet 215
11.3.2 Reading of worksheet 217
11.3.3 Copy of worksheet 218
11.3.4 Worksheet movement 219
11.3.5 Deletion of worksheet 220
11.3.6 Worksheet application case 1： Batch new worksheet 221
11.3.7 Worksheet application case 2： Batch copy sheet and modify sheet name 222
11.3.8 Worksheet application case 3： Split multiple worksheets into multiple workbooks 223
11.4 Basic operation of cell 224
11.4.1 Reading of cells 224
11.4.2 Reading of cell range 225
11.4.3 Acquisition of line information 227
11.4.4 Acquisition of column information 229
11.4.5 Cell write 231
11.4.6 Cell application case 1： Make 99 multiplication table 233
11.4.7 Cell application case 2： Sum up the scores of each subject 234
11.4.8 Cell application case 3： Multi sheet data consolidation 236
11.4.9 Cell application case 4： Multi workbook data merge 237
11.5 Other operations on the worksheet 239
11.5.1 Insert and delete rows and columns 239
11.5.2 Move cell 240
11.5.3 Delete row and column application cases ： Filter data by criteria 242
11.5.4 Insert row and column application cases ： Batch production of payslips 244
The first 12 chapter
Python And Excel Combined use —— Comprehensive application case 246
12.1 Comprehensive application case 1： Custom sorting 247
12.2 Comprehensive application case 2： Sorting by row 248
12.3 Comprehensive application case 3： Sorting out non-standard data 250
12.4 Comprehensive application case 4： Convert a one-dimensional table to a two-dimensional table 251
12.5 Comprehensive application case 5： Calculation of commission amount based on Performance 253
12.6 Comprehensive application case 6： Query the highest performance record of each month in the performance table 255
12.7 Comprehensive application case 7： Multiple summary methods of two-dimensional tables 256
12.8 Comprehensive application case 8： Group and summarize by multiple columns 258
12.9 Comprehensive application case 9： Multi workbook data summary 260
12.10 Comprehensive application case 10： Comparison between planned enrollment and actual enrollment 262
Technology