How to quickly convert Excel data? Two methods help you make perfect conversion

How to quickly convert Excel data is a common question. As a powerful office software, Excel can not only create tables but also process data efficiently. If you need to perform data conversion, the following article will provide detailed solutions to help you complete the task easily. PHP editor Youzi is here to bring you a step-by-step guide, allowing you to easily master Excel data conversion skills.

Now there is such a data table, which is recorded one by one.

Now in order to facilitate analysis, the table needs to be converted into the form below:

# #

If it is more troublesome to copy one by one, there are two different ways to solve this problem.

Method 1: Use the formula

The SUMIFS function will be used here, fill in the following in the first cell of the target area Formula:

=SUMIFS($C$2:$C$17,$A$2:$A$17,$G2,$B$2:$B$17,H$1)

# #Among them, $C$2: $C$17 is the statistical data; $A$2: $A$17, $G2 is the condition one, that is, the content must be met for the first quarter; $B$2: $B$17, H$1 is Condition 2 must be met and the content must be Zhang San.

Then use the fill handle to fill in the formula to the right and below. Pay attention to the absolute reference used in the formula. Drag down and $G2 will be updated to $G3, $G4 and $G5 according to the position, which is the content of other quarters. Drag to the right, H$1 will be updated to I$1, J$1 and K$1, which are other people's names.

After filling in the cells, the data conversion is completed.

Method 2: Pivot table

1. Select the data area.

2. Click the Insert tab and select the PivotTable.

3. In the Create Pivot Table window, select a location to insert the new pivot table. Here, select a location in the current workbook to insert, and then click OK.

4. Check the last three fields and put the quarter in the row and the person in the column, as shown in the figure below.

The pivot table looks like this:

5 , then copy it, paste it as text, adjust the order and you're done.

The two methods introduced today are very practical and can be chosen according to your own usage habits.

The above is the detailed content of How to quickly convert Excel data? Two methods help you make perfect conversion. For more information, please follow other related articles on the PHP Chinese website!