Excel uses excel content to generate reports, how do I do it?

It seems that you are familiar with the database, in fact, Excel also provides some functions similar to Access database software, use this trick well, you can easily deal with your problems The steps are as follows: 1. Build a database When you have completed the structural design of a database, you can create it in the worksheet. First, enter the fields in the first row of the worksheet

Figure 12-1 shows the employee name, region, date, product name, sales quantity, sales amount, and sales amount. Once we have entered the fields, we can enter the data by record in the worksheet. Hint:

The field name is placed in each adjacent cell of a row in the worksheet. It is also possible to establish a format for field names and database records that have not yet been established. The format only makes the database easy to understand and does not affect Excel's ability to manipulate the database.

2. There are two ways to input data into the specified database, one is to type the data directly into the cell, and the other is to use the "record sheet" to enter the data. Using "Record Sheet" is a frequently used method, and its operation steps are as follows: (1) Select any cell in the data list you want to add to the record.

Method steps.

1. First, in the blank space of the computer desktop, right-click the mouse to create a new Microsoft Office Excel sheet (that is, Excel**) and open it.

2. In the blank excel**, enter some characters and numbers to facilitate the demonstration of the following steps. If you already have Excel data, you can skip this step.

3. Select all of this content, click "Filter" in the "Start" in the upper menu to continue (see Figure 1 below), after clicking "Filter", a small arrow in the shape of a drop-down menu will appear in the first row (see Figure 2 below).

4. Filter this item only. Click on the small arrow that appears, a small menu box will appear, the box has been filtered, you can click "Filter only this item" to proceed to the next step (see Figure 1 below), and the automatic screening will be completed after clicking.

5. Filter the "top 10 items". We click on the small arrow, a small menu box appears, click on the filter "Top 10 items" (see Figure 1 below), and enter the maximum value in the pop-up menu box to complete the filter of the top 10 items.

6. Color screening. We click on the small arrow, a small menu box appears, click "Color Filter" to proceed (see Figure 1 below) to select the color we need to filter and click to complete the color filter.

There are two kinds of data filtering operations provided in Excel, namely "Auto Filter" and "Advanced Filter".

Auto-filter is generally used for simple conditional filtering, which temporarily hides data that does not meet the conditions and displays only the data that meets the conditions. Figure 1 shows the salary table of employees in a certain unit, open "Auto Filter" in the "Filter" submenu in the "Data" menu

command, take the "Basic Salary" field as an example, click the list button down on the right side of it, and you can filter out the top 10 records with the highest (lowest) basic salary (the value can be adjusted) according to the requirements. You can also filter out records that meet the requirements of the basic salary within a certain range according to the conditions, and "and" and "or" to constrain the distinguishing conditions. As shown in Figure 2, the records with a basic salary greater than or equal to 300 and less than 350 are filtered out according to the given conditions.

In addition, Auto Filter allows you to filter multiple fields at the same time, and the conditions between each field can only be AND. For example, the records of "basic salary" and "job salary" are both more than 380.

Advanced filtering. "Advanced Filtering" is generally used for more complex filtering operations, and the results of the filtering can be displayed in the original data**, and the records that do not meet the criteria are hidden; It is also possible to display the filter results in a new location, and the records that do not meet the criteria will be kept in the data table without being hidden, making it easier to compare the data.

For example, if we want to filter out eligible records with a "basic salary" or "position salary" of more than 380 and an "actual salary" of more than 700, we can't do it with "automatic screening", but "advanced screening" can easily achieve this operation. As shown in Figure 3, copy the field names of the three fields of "Basic Salary", "Job Salary" and "Actual Payment" to the right side of the data ** (other blank positions in ** can also be used), and enter the condition in the position shown in the figure, and the condition is placed on the same line to represent the relationship of "and", and the condition is not in the same row to represent the relationship of "or". Figure 4 shows the results of the above operation filtering at the new position (the start position of b20).

Comparison of the two filtering operations.

From this, it is not difficult to find that "automatic filtering" is generally used for simple screening operations, and the records that meet the conditions are displayed in the original data**, which is relatively simple to operate, and beginners are more familiar with "automatic filtering". If you want to filter multiple criteria for an OR relationship, or if you need to display the results of the filter in a new location, you can only do so with Advanced Filtering.

In general, the operations that can be completed by Auto Filter can be achieved with Advanced Filtering, but some operations are not suitable for Advanced Filtering, which will complicate the problem, such as filtering the largest or smallest records.

When solving problems such as data screening in practice, as long as we grasp the key to the problem and choose simple and correct operation methods, the problem can be easily solved.

After selecting it, right-click, copy, go to the target location, and paste.

1.Copy and paste the range of cells into another area, it is obvious that not all the formatting is copied after pasting. In particular, the row height and column width. It's cumbersome to readjust.

2.In the paste options, you can choose to keep the source column width.

3.The second method is the paste special, right-click the "Paste Special" command and select "Column Width" in the dialog box.

4.Copy cells to keep cell row height and column width.

The whole row can be copied to retain the row height, and the entire column can be copied to retain the column width.

You want to select both the row and column where the region is located.

Take WPS 2019 as an example.

1. Open the ** that needs to be set.

2. Select the cell data.

3. Click the "+" sign that appears in the lower right corner of the cell, and pull down the "+" sign to automatically number 4. The effect is as follows

Serial number generation --- habits First do a good job of **, make sure that column b has data Then respectively: cell A2 input number: 1; Enter the number in cell A3:

2.Select the cell and place the cursor in the bottom right corner of 2, a black cross will appear. a：

Double-click to automatically populate the sequence corresponding to column B data, for example, there are 21 rows in the example.

3.Hold down the left mouse button to select these two cells, then move the mouse to the bottom right corner of the cell when the black cross, hold down the left mouse button and drag it down to the last row.

4.Release the left mouse button to actively generate the serial number.

5.Another way is to use the formula to generate, and enter the formula "=row()-1" in the first cell under the serial number in the created **.

6.Select the cell and drag it to the last cell when you place the mouse over the black cross in the lower right corner of the cell.

7.When you release the mouse, the sequence number is automatically generated.

**A lot of the serial numbers used in the ordinal number, often one by one to enter more troublesome, you can use the row() function to complete the automatic generation of serial number.

For example, enter 1 in A1, enter 2 in A2, select A1 and A2, move the cursor to the small black dot in the lower right corner of A2, and drag down with the left button when the cursor changes to a cross shape.

Here's how to lock a cell.

Set the style of this column to locked.

set to a new cell;

To generate a chart in Excel, you first have to select the data, and then click Insert - Chart, just make a chart, the common types of charts are column charts, line charts, pie charts, and so on.

Click [Development Tools] - [Insert] - [List Box] in the ribbon. Left-click with the mouse on the worksheet to draw a listbox control.

Enter the titles of each month of the raw data in e2:e4, in this case January, February, and March.

Select the list box, right-click, and select Format Control.

In the [Control] tab, set it as shown in the following figure:

Data Source Region] Select e2:e4 just entered;

Cell Link] to select E1.

Dynamic region references can be implemented using the offset function.

Solution 1:

I give the following method a datatable, then generate excel and open it directly:

public static bool doexport( dt)

throw new exception("excel won't start");

true;workbooks wbs = ;

workbook wb = ;

worksheet ws = (worksheet);

int cnt = ;

int columncnt = ;

*Get Data***

object[,]objdata = new object[cnt + 1, columncnt];Create cached data.

Get the column headers.

for (int i = 0; i < columncnt; i++)

objdata[0, i] = ;

Get specific data.

for (int i = 0; i < cnt; i++)

dr = ;

for (int j = 0; j < columncnt; j++)

objdata[i + 1, j] = dr[j];

**Set cell style***

range myrange = , "h"], 1, "n"]];

"yyyy-mm-dd";Date-based format.

myrange = , "q"], 1, "r"]];

"yyyy-mm-dd";Date-based format.

myrange = , "c"], 1, "c"]];

@";Text formatting.

myrange = , "a"], "s"]];

true;**Write to excel***

range r = , 1], 1, columncnt]];

Songti";objdata;

return true;

Select the data, click Insert Chart, select the bar chart (overlayed), and then change the fill color of the front column to none, and Plato will come out.