You should have done this before. Excel Reading notes

1. 1. Excel is used to analyze the data, at least one copy of the original data and for the subtotal data, the two data in a task, should be stored in the same Excel document, in the book, they are called the source data table and the classification summary table. User input source data table, according to the relevant business requirements, using Excel to automatically generate a classification summary table. In this book, it is always emphasized that only one source data table is required, and the summary table is generated automatically. The data entry and processing results separate, in the software development, this is called the isolation change, anyway is summed, I just need to give the original data and sum the relationship between the data, new data added, new summation data automatically generated.
2. The data integrity and coherence of the source data table will directly affect the process and results of data analysis, filtering, sorting, function Reference and automatic summarization of a source data table.
3. 2. In Excel, the header row for the first behavior of a contiguous data region, which represents the properties of each column of data, is the basis for filtering and sorting fields.
4. The title is to let people who read the table know, this is a table, except that there is no function, do not use the title to occupy the first row of the worksheet, to write the title in the worksheet, an Excel default open a workbook, a workbook has three worksheets, divided into Sheet1, Sheet2 and Sheet3.
5. 3. When you are moving rows and columns in a data region, use the following method:
6. Exchange data for J columns and L columns
7. Select a row, place the mouse over the edge of the selected box, show the cross style, and then hold down the SHIFT key, drag the mouse to place it where it should be, and then release the SHIFT key when you have finished dragging. In this way, the data is moved quickly in many rows.

4. When receiving the key data provided by others, we need to do further processing, before processing, we must realize to save a copy of the original form, analysis and processing on the replica, which can not only protect the data security, but also show respect for the results of the work of others.

5. Data for the same attribute should be recorded in the same column. The detail data must have one, record one article.

6. The CTRL key has the function of copying, enter the cell input is complete, the two of them together, is to copy the finished data to the selected cell at the same time. Ctrl+enter, you can enter the same data into multiple scattered cells at the same time.

The first step: Select multiple cells with Ctrl first, either together, or you can scatter

Step two: Release CTRL and enter the desired content directly

Part three: Hold down CTRL and press ENTER to get the desired effect.

7. Merging cells severely destroys the data structure and merges the cells, rather than enlarging the size of the cells. I used to do Excel, often looked at the cell is too small, the merging of cells, although there is no statistical requirements, but in order to professional, to get rid of this bad habit.

Merging and centering, it is useful to make forms for printing (such as recruitment form, application form, registration form, etc.). When you make a source data table, you are forbidden to use it, which destroys the formatting in the source data table and does not facilitate function processing.

8. Data filling, numerical part if there is no data, to fill the 0 value, the text part of the blank unit, the sky needs to be unified to identify the blank text data, so convenient later use formula to statistics. Use the sixth step of a key batch entry function.

9. Convert Chinese numerals to uppercase numbers,

This method only applies to converting integer values, and fractional parts cannot be converted correctly in Chinese logic.

Excel as a professional data processing software, fancy is the data attributes, rather than text description. Attribute this feature, 1 is 2, is the smallest unit of information, such as filling in the logistics address, need to peel off different attributes, broken records, such as Sichuan Province, Chengdu City xx road xx Qingyang District xxx, in Excel, should be divided into finer granularity attributes, into: Sichuan Province, Chengdu, Qingyang District, XX Road XX Building, according to these four attributes, can master province, city, district distribution situation.

11. Cell segmentation, Excel provides two ways of slicing: by separating the symbols or by the fixed width. The feature entry is data-to-break functionality.

"Cut" by separating symbol, requires cell contents to have the same separator symbol

To "cut" by a fixed width, there is a requirement for the text length of the cell.

For example:, analysis of this content, in line with 414N, that is, 4 Chinese characters + 1 digits + 4 Chinese characters +n A number, can be segmented according to fixed width, the operation of the segmentation is very simple, it is not posted out.

Since it can be sliced, it can also be merged, the combination of symbols for &, in the need to merge the cells inside, enter = B2&c2&d2&e2 can complete the corresponding cell merge.

12. Do the classification summary table

Do not use the subtotals table that comes with Excel, which does not meet the user-defined requirements. In the source data table, you do not need to do any summary actions,

Excel default new production blank work shop, by default there are three worksheets, the three tables each have their own role, the first is the source data table, the other two are configuration parameters and subtotals report. The configuration parameter refers to the background data, does not need to operate frequently, the source data through the manual input business detail data, the summary report is the system automatic generation.

Take the bank's credit card system as an example, the customer to apply for credit card filling information is the configuration parameters, once filled out, the possibility of late changes is very small. Customer each credit card consumption details are the original data, the end of the month the credit cards received is a summary report. Classify three points, clearly understand.

In the case of telecom toll system, the identity information of the customer when applying for the calling card is the configuration parameter, the customer's call details are the source data, and the bill is a summary report.

Do Excel, is to do a source data table, and the "Change" N-List of subtotals.

12.1 The data in the parameter table, which can be called by the source data table and the Subtotal table, is the base data, which usually represents the data matching relationship or an attribute detail. To office supplies as an example, can be used as parameters: the name of each department, the type of office supplies and for the single month, the month list.

12.2 Source Data Sheet

In order to limit the input contents of the cell, combined with the data validation and definition of the name of the settings, in the source data table in the selected way to enter the specified content. All work related to data entry in Excel is done in the source data table.

To the processing of any data table, to clear three tables, first of all, determine the data identity, based on the identification of their respective work table, and then design the classification summary table style, and finally design the data association, according to the parameter table and the source data table automatically get the subtotal results.

13. Conditional formatting

Conditional formatting means that when a cell satisfies a certain or certain condition, it is displayed as a formatted cell format, and its day sword can be a formula, text, value,

Conditional formatting is a format that cannot be copied with CTRL + C, but is copied with a format brush

Conditional formatting takes precedence over normal formatting, and formatting overrides the original normal format when the cell satisfies the criteria.

14. In order to manage product classifications uniformly, it is best to use code rather than text to describe a product. For example: "Seamless steel tube A type of high-quality" Such a text description, can be converted to WFGG-A-A:WFGG represents seamless steel tubes, a for a type, and another for high quality. This code name is called the material code, which is the unique identification code of the material. For electronic products, it is called S/n code,

15. Use Excel's three-sheet to solve the problem

First, make it clear that you need only three worksheets, second, identify the data, determine which worksheet they belong to, then design the subtotals table style, and finally, set up the data association to automatically get the subtotal results based on the parameters and the source data.

In the case of electrical stores, the purchasing department needs to purchase every month from various suppliers, and it needs to record the purchase details and analyze them regularly. Analysis of a lot of angles, such as in accordance with the purchase cycle analysis from all over the quantity of goods, according to the supplier level classification of the proportion of similar home appliances purchase quantity. First, with the simplest amount of stock analysis, to see how this procurement table design:

Determine data identity: the details of all the suppliers and the goods supplied in the procurement database are generally not easily changed, so this is the argument.

The data in the source data table is derived from each purchase action, and the field and record order should be:

When you make a summary table, more than one field should be used as a row field and a field with fewer categories to be used as a column field, which makes it more useful to display more content on a page.

What is the use of more management knowledge? Without data, nothing can be done.

16. Pivot Table

A pivot table is what helps us see the meaning behind the data and the truth about management.

Specific detailed operations need to be understood in conjunction with specific examples.

The completed Pivot table integrates the original data, where the summary tables obtained from the pivot table go, where the source data is followed and the secrecy measures are taken care of.

Non-processed through the pivot table production of the summary table, must not be free to spread!!!

17. Input of the date

There are two common, minus sizes to "-" for the separation of the symbol entry date, such as 2014-12-11

The forward slash type, with "/" as the separator symbol entry date, such as 2014/12/11

CTRL + ";": Enter the current date quickly

Ctrl+shift+ ";": fast Input current time

18. Bibliography author for so many years of Excel experience, the most important in Excel is the watchmaking specifications and ideas, the specific skills are still second. This is similar to software development, the same problem, you can use different code to write a solution, the team to combat, to the coding specifications and annotation style, the specific advanced skill is second, focus on writing clear and easy to maintain the code.

The design of an Excel table field, in fact, the most comprehensive reflection of the designer's work experience and wisdom, the relevant business process familiarity determines the design table matching degree.

In the data management, the Chinese recognition is defective, it is very necessary to issue the identity number for the specific data.

19. Quickly Select column cells that contain data

ctrl+shift+ up and down arrows can

20. Table users of the main task is to input data, their work is repetitive, cumbersome, meticulous, in the face of a large number of data entry, re-certification of people will inevitably error, long staring at the same cell, it is easy to use eye fatigue, affect the quality of work and efficiency, users of the mood. To ensure the safety of entry, there are two layers of meaning, one is the security of data entry into the cell, and the second is the data entry security of the worksheet.

Excel has a feature that specializes in cell entry that is not standard, which is called data validity. Only if the contents of the cell meet the preset conditions, can be entered successfully, or error.

Excel shortcut key use, call the first-level menu to use ALT, call the two-level function, select the letter, select the letter to turn on the function.

22. Pick data from huge source data, copy, paste, and then form a new data table, all of which can be completely solved as long as you have VLOOKUP.

VLOOKUP is a lookup and reference function that finds whether a cell data exists in the source database, returns the cell contents of the specified column in the source database if it exists, and returns N/a if it does not exist.

VLOOKUP has four parameters, what are they used for? Where to find it? Found the return value of the first number? Accurate something else is fuzzy search?

Using the VLOOKUP function to match, the field to be matched must be in the first column of the data area, as defined by the VLOOKUP function.

How to use: In an empty cell, enter "=vlookup", and then press CTRL + A to open the VLOOKUP's parameters panel.

23. Text size, for data detail, the 10th font is more refined than the default 12th font, the text is centered in the vertical direction

Font, Chinese with Arial, or times New Roman,

Highlight header row-sets the cell fill color for the header row

24 AutoCorrect Options, this feature in Excel was originally used to correct common typographical errors, such as the input soudn,excel will automatically correct it to sound, why? Because the Excel database contains the contrast text, according to this idea, if we customize the contrast text, it is not possible to use a short text instead of long texts?

Add the abbreviations you need inside,

In the cell, enter ABC and press ENTER to display the replaced text.

Recommendation: The replacement text is best used in three combinations of English letters, so that there can be multiple changes, but also easy to remember, and will not conflict with normal text. It is recommended that the replacement table be printed for quick entry.

After you have configured it in Excel, you can use it in Word and PPT, enter ABC, and press ENTER to replace ABC with the characters you need.

25.

There are three types of charts that are most commonly used in work.

Pie chart, comparing the share, distribution and other information, can be intuitively through the fan size to illustrate the proportional relationship, rather than emphasizing the value itself, two-dimensional positive compact figure is enough.

Column chart, used when comparing values, it is concerned with the size of multiple values, but also the individual value itself, it is important to note that the number of comparisons, need to be comparable to the line. Simple and intuitive, two-dimensional effect is sufficient.

Line chart, pay attention to the trend of numerical changes, used to translate data changes over a period of time, when the data is less than 6, with a column chart of the expressive force will be better, the more the number, the more the line chart can reflect the law of change.

26. If you need to paste the icon in Excel into Word, while maintaining the synchronization of the two chart changes, if you perform a selective paste

In this way, after modifying the data in Excel, the changes will be synchronized to word immediately, pasted into the PPT Also, here, there is a security hazard.

Close the source Excel document, right click on the icon, select Edit Data, the Excel source file will open immediately (if the source file is not moved)

In this, you should choose Paste as a link. Never choose to paste as a chart object.

27. We inevitably have to work with other departments, other companies to deal with people, perhaps the report delivery, perhaps data sharing, cherish every opportunity to communicate with others, through it to show their good professional quality and aesthetic taste. The impression between people is the accumulation of bit by bit, especially a good impression, it takes a long time to accumulate to form, but not easy to get a good image, but can collapse in a moment.

Make Excellence a habit. In the work, the heart of the people, have a deep understanding of work and plugging to sentiment, these experiences, and the word of mouth, rather than record. In the collation, record at the same time, better understand themselves.

Sharing is a virtue and the best way to communicate between people. If the spirit of sharing is lost, even the stories is not, the process of chatting with people itself is the process of sharing information.

Workplace sentiment: treat people with sincerity, treat others sincerely do not calculate, sincere person often can emit a very strong affinity, bring to others trust, and thus get more opportunities, a person whether sincerity and intelligence level irrelevant, also not so called you must be smart enough to let others feel your sincerity.

Get feedback on everything you do, and the feedback on your work schedule is especially important. Feedback refers to the arrangement of workers to the work of the initiative to report the situation, in the workplace, the initiative is very good personal quality, active people, always take the initiative, and passive people always wrong opportunity.

After a matter of command down, we should take the initiative to report to the boss progress, the content of the report is nothing more than two, one can be completed according to the original plan, if not, is caused by what reason, is expected to postpone, second, what difficulties encountered, what help is needed. As for the problems that have already been solved, or the achievements achieved, they should not be accounted for at the time of reporting progress. The initiative to report progress, to the boss convenient, is to give themselves convenience.

In addition to the timely reporting of work progress, but also to pay attention to complete the task on time, although the work has an uncontrolled factor, but still try to finish on time, this can reflect your ability to work and planning.

Enterprise management knowledge to understand more and what is the use, no data, take what to make decisions?

You should have done this before. Excel Reading notes