Problem Description

In Excel data analysis, remember not to merge cells, which may cause some column problems such as inability to sort. For the sake of good-looking forms, I got into this kind of pit a few days before work. Then we take the following data as an example to see how to cancel the cell merge.

Python solution

① Use pandas to read data.

import pandas as pd

data = pd.read_excel('test.xlsx',sheetname='Sheet1',header=None)
data

We can see that merging cells is terrible. Reading with pandas, there will be missing values.

② Fill in missing values. Actually, we only need to fill in missing values ​​previously.

data[0].fillna(method='pad',inplace=True)
data

Excel solution

Programming can be done, but the conditions are too harsh.

• Requires Python environment
• There are too many processes for reading data-processing data-exporting data.

So we use Excel to solve it. At first, I thought that after canceling the cell merge, fill it manually, but when the amount of data is large, it is very troublesome. Next, we look at the simple way.

① Cancel the cell merge.

② Select the first column of data, use ctrl+g, and select a null value for the positioning condition.

③ Enter the formula =A1, and use the ctrl+enter key to complete.