In enterprise application development, business personnel often provide Excel data sources, while developers import Excel data to the database and then process it in the database. In Excel, many blank cells are generated to indicate a hierarchy and Its Relationship. For example, a CRM data contains sales team, sales staff, and customer data. A sales staff belongs to a sales team and a customer belongs to a specific sales team. Therefore, the business staff will provide such data:
Sales Team |
Salesperson |
Customer |
G1 |
S1 |
C1 |
|
|
C2 |
|
|
C3 |
|
|
C4 |
|
|
C5 |
|
|
C6 |
|
|
C7 |
|
S2 |
C8 |
|
|
C9 |
|
|
C10 |
|
|
C11 |
|
S3 |
C12 |
|
|
C13 |
|
|
C14 |
G2 |
S11 |
C15 |
|
|
C16 |
|
|
C17 |
|
|
C18 |
|
|
C19 |
|
|
C20 |
|
S12 |
C21 |
If such Excel Data is directly imported into the database, the sales staff of customers such as C2 are empty and the sales team is empty. Therefore, you need to process the Excel file, so that each customer has a corresponding salesperson and sales team.
The solution is as follows:
1. Select all the table ranges, and press Ctrl + G to display the positioning window.
2. Click "positioning condition", select "null value" single region, and click OK. All empty fields in the table will be selected.
3. The current cursor position is above B3. We can enter "= B2" in B3"
4. After the input is complete, press Ctrl + Enter to fill in all selected null values:
This data can be imported again.
The essence of this is to make each blank cell equal to the value of the above cell, thus realizing the filling of all blank areas.