Excel can also be fun: Workplace story Edition

Excel is also fun: Workplace stories
Basic Information
Author: Wu Yunhui [Translator's introduction]
Press: Electronic Industry Press
ISBN: 9787121183140
Mounting time:
Published on: February 1, October 2012
Start: 16
Page number: 416
Version: 1-1
Category: Computer> office software> Office> Excel

More about "" and "Excel" can also be fun: Workplace story Edition

Introduction
Books
Computer books
Excel can also be fun: Workplace story edition focuses on the career growth experience of a recent graduate. Based on the hero's work experience, this article introduces the application of Excel in daily work from a simple perspective. Enter data in the Excel file, edit the table, and format the worksheet, apply an Excel template, use formulas to process data, use functions to process data, use Excel Charts to create reports, and sort and filter data, I have explained in depth the advanced applications of using Excel macros, VBA programming, usage solutions, planning and solving to predict the data in my work.
Excel can also be fun: Workplace story edition integrates Excel knowledge points into workplace office scenarios, so that readers do not have to read it. Not only for beginners, but also for readers who are familiar with Excel 2003 and earlier versions. It can be used as a reference for all kinds of office automation personnel, computer training school teachers and computer enthusiasts.
Directory
Excel is also fun: Workplace stories
Chapter 1: A gunned gun
Li Lu, who just graduated from the university and first entered the workplace, does not know what skills are needed in the workplace. During the interview, the employer informed her that excel needed to be used at work. Li Lu had to give her a shot to learn the basic usage of Excel and try to get into work on the first day of work.
1.1 Introduction. 2
1.2 understand Excel 20102
1.2.1 What can excel do? 2
1.2.2 Excel version. 3
1.2.3 new functions of Excel 2010 5
1.3 What is Excel table 6
1.3.1 omnipotent functional area 6
1.3.2 what is a workbook? 10
1.3.3 what is a worksheet? 11
1.4 square feet can accommodate thousands of 12
1.4.1 table size. 12
1.4.2 cell capacity 14
1.5 Target 15
1.5.1 select a cell 15
1.5.2 select continuous cell area 17
1.5.3 select the interrupted cell. 18
1.5.4 select the entire row or the entire column 19
1.5.5 select all cells 20
1.5.6 edit column not followed. 21
1.6 first form-graduate score Registration Form. 23
1.6.1 New Start 23
1.6.2 merge cells. 24
1.6.3 how to input symbols★26
1.6.4 allow automatic Excel computing 27
1.6.5 wear beautiful clothes for the Form 29
1.6.6 save labor results 30
1.7 don't forget F1 31
Chapter 3 identity transformation-from school to workplace 34
Li Lu successfully reported for employment. On the first day of work, the supervisor handed in a job to create a table using Excel. With the help of her colleagues and her boyfriend Ma Bo, Li Lu quickly entered the role, successfully produced a "employee roster" table, and learned how to fill the Excel sequence, adjust the Row Height and column width.
2.1 induction report. 35
2.2 The first task assigned by the supervisor-35 in-service employee roster
2.2.1 create a header 36
2.2.2 simple beautification 36
2.3 fast input continuous serial number 37
2.3.1 method of dragging. 37
2.3.2 Ctrl + drag Method 38
2.3.3 Automatic Filling option 39
2.3.4 fill sequence 39
2.4 change the ID card number to 5.13021e + 17 40
2.4.1 Add the symbol '. 41 before the number
2.4.2 set text format 42
2.5 crack character "#" 44
2.5.1 adjust the column width to show more content 44
2.5.2 adjust the Row Height to show the complete row 47
2.6 hold table rows and columns. 48
2.6.1 Insert rows and columns. 48
2.6.2 Delete rows and columns. 51
2.6.3 private data 52
2.6.4 review hidden data on the 54 th day
2.7 secretly lazy, calling existing Department name 55
2.7.1 magic memory input. 55
2.7.2 directly select the Department name. 57
2.8 eight-hour effort. 59
Chapter 2 from trial to personnel specialists 61
Li Lu used EXCEL to create the Department's health value table, learned to use the date sequence filling, text character sequence filling, and master the preparation of common diagonal line headers, as well as the data copy and paste skills in the table to improve your Excel application level.
3.1 turned positive 62
3.2 Create a health value table. 62
3.2.1 date is also a type. 62
3.2.2 input date 63
3.2.3 date display with my intention 64
3.2.4 date is also a value 65
3.3 filling date 66
3.4 names can also be filled in sequence. 68
3.4.1 copy and paste. 68
3.4.2 define sequence 69
3.4.3 custom sequence filling 70
3.4.4 fill in name 71 quickly
3.5 diagonal line header 72
3.5.1 simple diagonal line header 72
3.5.2 complex diagonal line header 73
3.5.3 tabulation is easy to enter 74
Chapter 14 put on a beautiful coat of the Form. 78
For tables in Excel, you must not just make them in the computer, but also take into account the print output effect. Li Lu adjusted the health value table created in the previous chapter and set the format to make the printed table more beautiful.
4.1 The supervisor was dissatisfied and the consequences were serious. 79
4.2 Data Adjustment 81
4.2.1 advantages of selective pasting. 81
4.2.2 date display. 82
4.3 table layout 84
4.3.1 draw a line for the table. 84
4.3.2 fill the table with paper 85
4.3.3 arrange the text for 88
4.4 use shading to separate rows. 88
4.4.1 set shading 89
4.4.2 clever use of format brush to improve efficiency 89
4.5 don't forget the header and footer. 90
4.5.1 Excel view 90
4.5.2 enter the header and footer 92 in the Page Layout View
4.6 I'm glad that the leadership is satisfied. 93
4.7 efficient format setting-Automatic format 94
Chapter 4 improve work efficiency with Excel 97
Using Excel can effectively improve work efficiency. Through studying excel, Li Lu has mastered many ways to improve work efficiency using Excel, for example, you can use techniques such as automatic correction and quick input, the F4 function, fixed header, and quick searching for required files.
5.1 enter 98 faster
5.1.1 use automatic correction to input company information. 98
5.1.2 fast input of the same data 99
5.1.3 select the scattered Area 101
5.2 faster modification. 102
5.2.1 magic f4102
5.2.2 change employee duty, modify duty schedule. 105
5.2.3 The shading can be quickly replaced with 107
5.3 unlock the header 110
5.3.1 Fixed Header 110
5.3.2 print the same header 113 on each page
5.4 Where is the file? 116
5.4.1 quickly find document. 116
5.4.2 Save the document to skydrive119
Chapter 2 How to manage office supplies
Because Li Lu's work attitude and skills have been affirmed by the supervisor, he has been assigned a new job: managing office supplies. With the help of MA Bo, Li Lu learned how to manage the data through the pivot table. At work, he only needs to maintain a worksheet and then generate various tables through the pivot table.
6.1 work CAPTCHA, the supervisor must be 125
6.2 use only one table. 126
6.2.1 simplify the complexity, and make a table. 126
6.2.2 This table is not Table 127
6.3 generate inventory table. 130
6.3.1 what can a pivot table do? 131
6.3.2 create a PivotTable 131
6.3.3 display in Table 132
6.3.4 Category summary not displayed 133
6.3.5 Summary of Summation Methods. 134
6.3.6 redefinition of header name. 135
6.3.7 Add a calculated field. 136
6.3.8 refresh the data of the PivotTable 138
6.4 generate item ledger. 138
Chapter 4 standardize colleagues' work-use of the template 7th
For the work that needs to summarize the data of each department, the template function is very important. Li Lu unified the purchase orders of different departments by making a template for the purchase orders of office supplies, which facilitates the use of colleagues and the statistical summary of the data.
7.1 purchase orders of different departments: 142
7.2 Design purchase order. 142
7.2.1 create a purchase order of 142
7.2.2 automatically calculated amount. 143
7.2.3 automatically display an upper-case amount of 145
7.2.4 hide 0: 147
7.3 unified purchase order with template 149
7.3.1 save as template 149
7.3.2 use a template to create a workbook 151
7.3.3 release template 152
7.4 modify the purchase order template 153
7.4.1 display of. 153
7.4.2 protect worksheet 155
7.5 use a PivotTable to summarize multiple tables. 158
7.5.1 custom functional area. 158
7.5.2 multi-Table summary 160
Chapter 4 Excel increases attendance statistics by 8th
The fingerprint logging machine is convenient for unit management. At the same time, the electronic data generated by the fingerprint logging machine is also convenient for the attendance Department to perform statistical analysis of the data. Li Lu flexibly applies the data formulas and sumproduct functions in Excel to attendance statistics to facilitate and quickly process attendance data.
8.1 New Year's system. 164
8.2 extract punch data. 164
8.2.1 export data to excel.164
8.2.2 Analysis of attendance data. 165
8.3 use Excel to determine if you are late and leave early. 167
8.4 Excel essence-array formula 168
8.4.1 what is array 169
8.4.2 calculation between arrays. 169
8.4.3 use or return the Array Function 171
8.5 magic sumproduct function 172
8.5.1 understanding the sumproduct function. 172
8.5.2 use sumproduct for multi-condition counting 173
8.5.3 use sumproduct to perform multi-condition summation of 173
8.6 use the sumproduct function to process the attendance table. 174
8.6.1 174
8.6.2 process attendance data. 174
8.6.3 prepare attendance statistical table 176
8.6.4 counting Attendance Status. 177
8.6.5 attendance summary data. 180
Chapter 4 expectations of each month: pay-as-you-go day: 9th
Payroll is the most important task of the HR department every month. Through the Excel Formula reference function, Li Lu creates a payroll table, which can be quickly generated every month. Calculate the Personal Income Tax using the look-up table method in the payroll, and quickly generate a payroll using the reference of functions and formulas.
9.1 re-create a payroll 182
9.1.1 build a payroll framework 182
9.1.2 employee basic information table 182
9.1.3 Calculation of length of service salary. 184
9.1.4 job salary calculation. 185
9.1.5 calculation performance: 185
9.1.6 calculate the subtotal to be sent. 186
9.2 Deduction Calculation
9.2.1 deduction for attendance calculation. 187
9.2.2 withholding of three insurances and one gold. 188
9.3 Calculation of withholding taxes 188
9.3.1 how to calculate Personal Income Tax 188
9.3.2 personal income tax calculated in Excel 189
9.3.3 more than 1 cent less than 5000 yuan 194
9.4 cut off formula reference. 195
9.4.1 improve the formula of the payroll by 195
9.4.2 auto-update payroll 196
9.4.3 no longer need link. 197
9.4.4 the payroll also needs to be beautified by 197
9.5 automatically generate a payroll of 198
9.5.1 generate a payroll of 199 million
9.5.2 payroll confidentiality. 200
Chapter 2 Human Resources Department: Service Department 10th
To manage human resources, the human resources department must organize and save all human resources of the Organization. Through excel, Li Lu sorts out employee information, parses many useful information of employees through ID card numbers, checks the entered error information, and intelligently reminds employees of their birthdays.
10.1 chaotic employee information table. 203
10.2 restrict input data. 203
10.2.1 Set Data Validity condition 204
10.2.2 name makes Excel smarter 204
10.2.3 smart name definition. 206
10.2.4 restrict the number of digits of an ID card to 208
10.2.5 the employee ID cannot be the same as 210
10.2.6 automatic table copy format 210
10.3 rich ID card number information 212
10.3.1 get 212 birthday from ID card No.
10.3.2 obtain gender 214 from ID card No.
10.3.3 Calculation of employee age 214
10.4 identify information that does not meet the requirements. 216
10.4.1 locate the wrong ID card number 216
10.4.2 locate inconsistent department names 218
10.4.3 more condition format settings 220
10.5 employee birthday reminder. 225
10.5.1 birthday reminder in condition Format 226
10.5.2 modification of the conditional format. 227
10.6 Labor Contract Management. 229
10.6.1 labor contract management considerations 229
10.6.2 calculate the data of each column by 230
Chapter 5 data processing my maximum line. 11th
With the increasing proficiency in Excel operations and the need for work, Li Lu has transformed from inputting and making data in Excel to analyzing and processing existing Excel Data. In order to complete the preparation before the salary adjustment, Li Lu learned and mastered various data processing skills such as data sorting, filtering, and classification and summarization.
11.1 data must be ordered. 233
11.1.1 preparations before salary adjustment: 233
?? XII ??
11.1.2 sorting by column. 233
11.1.3 sort by multiple conditions. 234
11.1.4 modify the conditional sorting 236
11.1.5 Chinese sorting? 237?
11.1.6 sorting of Chinese strokes 240
11.1.7 more powerful: sort by format. 241
11.2 ask for the required data. 243
11.2.1 simple screening 243
11.2.2 text filtering 245
11.2.3 select employees older than the average age. 246
11.2.4 select employees whose birthdays are in the 1st quarter. 246
11.2.5 faster screening 248
11.2.6 multi-condition filtering 249
11.2.7 clear filtering. 250
11.3 filtering of complex conditions 251
11.3.1 custom Condition Area. 251
11.3.2 multiple conditions in a column 253
11.3.3 multiple conditions in multiple columns 254
11.3.4 multiple condition sets 255
11.3.5 set the wildcard condition. 255
11.3.6 use formulas to define filter conditions 255
11.4 collect statistics by Category summary 256
11.4.1 count the number of people in each department. 256
11.4.2 View Details and summary data 258
11.4.3 copy and summarize data 260
11.4.4 Delete Category summary 262
11.4.5 Multi-Level Classification summary. 262
Chapter 4 Human Resource Analysis 12th
In Excel 2010, a wide range of charts are provided to display the input data. To make the HR analysis report more attractive, Li Lu decided to use more Excel Charts. At the same time of the report, Li Lu mastered the chart type and related techniques for creating charts.
12.1 job composition figure 266
12.1.1 Category summary: 266 employees
12.1.2 create a position chart. 266
12.1.3 make the chart more beautiful 269
12.1.4 use a pie chart to show the position composition: 270
12.2 unknown chart knowledge 272
12.2.1 chart type 272
12.2.2 data 273
12.2.3 chart layout and style. 275
12.2.4 chart position. 275
12.3 chart layout 277
12.3.1 chart labels. 277
12.3.2 axis 280
12.4 other analysis charts. 281
12.4.1 education level 281
12.4.2 employee age distribution chart. 281
12.4.3 contract expiration analysis diagram. 283
Chapter 1 more beautiful employee registration form 13th
For data saved in an Excel table, you can use the formula to convert the data from one row to one page. Li Lu uses the Vlookup function to call data from the "employee information table", generate an employee registration form, and display the employee photos in the employee registration form through certain techniques.
13.1 call data from employee information table 287
13.1.1 Registration Form for employees 287
13.1.2 use the Vlookup function to obtain employee information 287
13.2 insert a photo into the table. 292
13.3 automatically changed photos 293
13.3.1 sort photos. 293
13.3.2 define the name of the referenced photo 294
13.3.3 reference a photo by name. 295
Chapter 2 Let's see my changes! Change! Change !. 297
Excel 2010 provides a wide range of interface customization functions. By clicking a few clicks, you can easily customize the quick access toolbar and control the display content of the status bar. In Excel 2010, you can also add tabs, command groups, and command buttons to the functional area.
14.1 control quick access toolbar 298
14.1.1 adjust the location of the quick access toolbar 298
14.1.2 adds feature 299 to the quick access Toolbar
14.1.3 DELETE command. 302 from the quick access Toolbar
302 import/export settings
14.2 display/hide screen tip 304
14.3 control status bar 305
14.3.1 view calculation result 305 In the status bar
14.3.2 view and display ratio 307
14.4 custom functional area. 307
14.4.1 quickly select the recently used workbook 307
14.4.2 add Common commands to the custom tab 308
14.4.3 customize the layout of the backup functional area. 311
14.4.4 restore the layout of the functional area. 312
Chapter 5 Higher Workplace requirements 15th
The Advanced Application of Excel is VBA. You can use VBA to write programs and perform in-depth customization on Excel. Li Lu, who has never studied programming, first gave a preliminary understanding of VBA and mastered macro-related operations, which laid the foundation for further learning VBA.
What is a 15.1 macro? 315
15.1.1 The macro is code 315
15.1.2 what is vba315
15.2 create and use a macro. 316
15.2.1 show "development tools" tab 316
15.2.2 recording macro 317
15.2.3 compile macro 319 in VBE
15.2.4 use macro 320
15.3 understand VBE 323
15.3.1 VBE composition 323
15.3.2 VBA help 324
15.4 learn more about Macro. 325
15.4.1 modules 325
15.4.2 process 325
15.4.3 function 326
15.4.4 branch statement. 326
15.4.5 loop structure. 329
15.5 common codes for cells and regions. 331
15.5.1 Excel Object Model overview. 331
15.5.2 use VBA to reference cell. 331
15.5.3 use VBA to process cells. 332
15.5.4 use VBA to control the workbook. 333
15.5.5 use VBA to control worksheet. 334
15.6 use VBA to control the employee registration form 335
15.6.1 use VBA to call the next employee information. 335
15.6.2 use VBA to call the previous employee information. 337
Chapter 4 loan repayment amount calculation 16th
Excel provides a wide range of financial computing functions. After being transferred as assistant to the general manager, Li Lu needs these functions to help his work. Therefore, Li Lu first tried to calculate the loan profits using these functions, and then used the functions such as solving the single variable and simulating the two-dimensional table for financial trial calculation.
16.1 transferred to assistant general manager 339
16.2 How Much housing loan is paid off every month 339
16.2.1 understanding of PMT function 339
16.2.2 calculate the monthly repayment amount. 340
16.2.3 what is the sum of profits? 341
16.2.4 determine reasonable loan term 343
16.3 loan calculation with Excel 344
16.3.1 single variable solving 344
16.3.2 single-variable simulation of two table 345
16.3.3 double-variable simulation of two-dimensional table 347
Chapter 4 annual revenue prediction 17th
Annual revenue is a key indicator for enterprise managers. Li Lu uses the solution management function provided by Excel to forecast the company's annual revenue under different circumstances and generate relevant solution reports, for reference and decision-making by leaders.
17.1 sort historical data. 350
17.2 use a scheme to predict 350
17.2.1 solution 351
17.2.2 solution 353 for other scenarios
17.2.3 view solution result. 354
17.3 generate a prediction report. 355
17.3.1 solution summary. 355
17.3.2 adjusted income forecast scheme 357
Chapter 2 The essence of an enterprise is to pursue a profit of 18th
The essence of an enterprise is to pursue profit. In order to maximize the company's profit, Li Lu uses the planning and solving functions provided by Excel to start the company's procurement and output values, make overall planning under the conditions of manpower and material resources, fully tap into the potential of enterprises, and pursue the maximization of profits.
18.1 understand the planning solution. 361
18.1.1 enable planning solution to load macro 361
18.1.2 step 362
18.2 optimized procurement 363
18.2.1 build procurement model 363
18.2.2 optimization with planning and solving 365
18.2.3 management constraints 368
18.2.4 Save the solution result as solution 369
18.2.5 generate computation result report 370
18.3 maximize output value 371
18.3.1 build a Capacity Calculation Model of 371
18.3.2 capacity optimization solution 373
18.3.3 computing results report 375
Chapter 2 employee satisfaction questionnaire 19th
In addition to the common table functions, Excel also provides the form creation function. Li Lu used the Form Control in Excel to create an employee satisfaction questionnaire. After opening the Excel Workbook, the employee clicked a few clicks to complete a survey.
19.1 recognize worksheet form 377
19.2 what is Form Control 377
19.2.1 control category. 377
19.2.2 operate control 378 on a worksheet
19.3 use of Form Controls 380
19.3.1 call macro 380 with buttons
19.3.2 use a combo box to select 381
19.3.3 use the check box for multiple selections. 382
19.3.4 use the Value Adjusting button to enter the value. 383
19.3.5 use the list box to select 384
19.3.6 enter gender 385 with the Option Button
19.3.7 display the option buttons of multiple groups in the group box 386
19.3.8 use the scroll bar to enter a value of 387
19.4 draw ActiveX Control 388
19.4.1 display calendar 388 in the worksheet
19.4.2 ActiveX control operation. 389
19.4.3 set control properties 390
19.5 design employee satisfaction questionnaire. 390
390 control design questionnaire
19.5.2 obtain survey data 392
19.5.3 set worksheet option. 394

Source of this book: China Interactive publishing network