The most common function of Excel is to record data and record data by row or column. This part of data is the source data, which is the most primitive flow account in business activities and serves as the basis for subsequent operations. To draw a certain conclusion from the source data, you need to analyze the source data to obtain report data. In the process of data analysis, in addition to the source data that changes according to the actual situation, there are still unchanged data, such as various proportions, bases, and basic information, this type of data is called basic data. In fact, the data in the business system can also be classified by the three types of data, but the business system can provide a friendly user interface, Excel is completely oriented to data. Although Excel cannot be compared with the business system on the user interface, the data classification and processing methods are the same as those of the business system. Following the same standards will make data processing more efficient.
1. Basic dataBasic data refers to the most basic configuration data and basic information data, which will not be changed frequently after being created. For example, the organizational structure, employee information, product classification, and product information are all basic data. There are two types of basic data: Basic data and basic data. A single basic data is generally a single value that exists independently, for example, various base data. This type of data can be used as named cells. You only need to reference the data in the subsequent report by name. Basic data in a list is a set of data with multiple rows or columns, such as organization structure information and product information. You can use a naming table for basic list data. The COLUMN function is introduced in the naming table. If the COLUMN name of a table is specified, the COLUMN function returns the sequence number of the COLUMN starting from 1. However, this sequence number is calculated from column A, and the third function in the VLOOKUP function is specified to start from column 1st in the current table. If multiple tables exist in one row, use the COLUMN function for the second table to obtain the wrong COLUMN number. We recommend that you do not have multiple tables in one row. Vertical sorting also provides the ability to add and delete rows without worrying about deleting data from other tables. Vertical arrangement has a disadvantage, which makes locating data troublesome and requires a large number of operations on the scroll bar. However, this disadvantage can be easily avoided by adding links. The method for adding links will be described in the following article. We recommend that you store multiple tables in one Sheet for a small amount of basic data during actual maintenance. If a certain type of basic data has a large number of rows, we recommend that you put them in a separate Sheet.
2. Source dataSource data is usually input manually or exported from other business systems. If it is manually input, try to reduce the amount of data entered. You can reference the basic data to automatically fill in part of the data. To avoid manual data input errors, we recommend that you set the data type and data validity (drop-down list. When setting columns in the source data table, be sure not to include compound information in a column. There are two types of clothing: the size and the version. For example, the shirt 36A indicates 36 yards. If you want to find out which of the four editions of ABCY is the most popular, you cannot directly use the pivot table for analysis. To provide correct data for a pivottable, the source data must have a column title, no cells to be merged, and no rows to be empty. When designing the source data structure, you must not use dimension items as columns. In particular, you must make multiple dimensions into multiple headers, for example: this is a scaled-down version of a real example I have seen (the real situation is more complex). Multiple sizes and colors are made into columns, which makes it impossible to use pivot tables during subsequent analysis. For example, you need to calculate the number of items sold in each size of product A, or calculate by color. This format can only be calculated manually. The correct method should be as follows: although this increases the number of data rows, it is more convenient for subsequent analysis.
3. Report dataExcel provides a powerful data analysis tool-pivot table. The following data is used as the source data for analysis (using the name table, named Sales Source data). The sales quantity of each product is calculated by size: switch to the Insert menu and click the pivottable. In the displayed dialog box, enter the name of the naming table. If the naming table is not used, use the mouse to select the data area to be analyzed. Note that the column title must be included. We recommend that you place the pivot table in a new worksheet to avoid modifying the Sheet where the source data is located. Click "OK" and the following prompt will be displayed in the new worksheet: the pivottable operation interface will be displayed on the right of the worksheet: drag the product to the row area by dragging, drag and drop the size to the column area, and drag the organization to the value area. The following report is automatically generated in the worksheet: you can change the column label and row label to the number of codes and products. If you want to analyze the color, drag the color to the column area. If you want to analyze both the color and size, you can drag both fields to the column area at the same time. The report format is the incorrect source data format mentioned in the signature. Adjust the size and color order in the column area, and the report will also change. With the standard source data, you can use the pivot table to flexibly obtain the desired report by dragging the table, without manual report. Of course, here is a brief introduction to the usage of the pivot table, which is actually more powerful.
From Weizhi note (Wiz)
Excel should be playing like this-5. Three types of data: Excel is also a system
