Shulou(Shulou.com)11/24 Report--

The original title: "Don't Ctrl+V anymore!" One column of data to more than one column, these two moves can be easily done! "

Hello, everyone. I am lurking in a lot of Excel communication groups. I have a little fun from time to time.

In one of the groups, I saw a question: how to organize titles and links in batches?

I sorted it out briefly (as shown in the table below), and the general requirement is to organize the left table into the right table.

▲ shows only part of the data for presentation. What would you do if it were you?

The partners in the group each expressed their own views:

Using misplaced screening? Mobile phone numbers all start with 1, this method is feasible!

With the help of cell references? This method works!

The above methods are all very good ones.

The only drawback is that once we add or modify the content of the data source, all operations need to be done again.

Therefore, this article shares with you two methods to dynamically update data when adding or modifying data sources. Let's take a look at it with me.

❶ Index function index method

❷ PowerQuery M function method

1. Index function indexing. Let's take a look at the specific operation.

As shown in the following figure, enter the following formula in cell E3:

= INDEX ($Bath2VOBLING BJE11 row (A1) * 2-1)

Enter the following formula in the F3 cell:

= INDEX ($Bath2VOULBULING BLINGROW (A1) * 2)

It only needs two function formulas, and it's done!

Let's briefly talk about the principle of the formula.

INDEX function description: the INDEX function can return a cell reference at the intersection of a specified row and column.

= INDEX (region, number of rows, [number of columns], [number of regions])

The formula in the case is as follows:

In the formula of $INDEX ($Baud2VERVROW (A1) * 2-1), the data area is $B$2:$B$11, and the last name is listed in the green zone. In the position of.

So we need to construct the isometric sequence 1, 3, 4, 5. The corresponding formula is as follows:

= ROW (A1) * 2-1ROW (A1) indicates that the number of rows is 1 row (A1) * 2-1 # 1.

ROW (A2) indicates that the number of rows is 2j row (A2) * 2-1 lines 2-1 lines 3.

ROW (A3) indicates that the number of rows is 3recoverrow (A3) * 2-1cm 3cm 2-1m 5.

By the same token, in order to get the mobile phone number, we need to construct the isometric sequence 2pm 4pm 6.

The isometric formula is = ROW (A1) * 2, so the whole function is:

= INDEX ($Bath2VOBLING BJE11 row (A1) * 2) summary of 👉 ideas:

Through the Row function, the isometric sequence 1, 3, 5, and 5 are constructed. The last name column can be obtained by using the index of Index function, and the isometric sequence is constructed by using Row function. The mobile phone number can be obtained by using the Index function index.

At this point, the method of Index function is introduced, let's introduce an M function method of PowerQuery, we must watch it patiently!

2. PowerQuery M function method Let's first look at the specific operation steps:

▋ step 1: import the data source into the PQ editor

Select the table area-Select the [data] tab-[from Table / region]-the create Table dialog box appears, and press and hold the OK button.

At this point, the table is imported into the PQ editor.

Step 2 of ▋: deepen the data column into a list and select the data column

Right mouse button data column-Select "deepen" or select "convert to list" under the "convert" tab

PS: the purpose of deepening and converting to a list is to extract a column of data from the current table to form a list to facilitate subsequent operations.

Now it becomes a list, ↓↓↓.

Step 3 of ▋: disassemble every two messages into a List

Click, and fx adds the formula to the edit bar on the right, as follows:

= List.Split data column 2)

At this point, the data is divided into two elements and a message.

Step 4 of ▋: convert the split List to Table

The Table.FromList function is used in this step.

The formula is as follows:

= Table.FromList (Custom 1 ~ # each _, {"name", "Mobile number"}) Operation Motion Picture:

Step 5 of ▋: real-time updates

Using PQ method, we can update in real time.

Let's briefly introduce the M function involved in the case.

❶ List.Split: list split

= List.Split (list, several at a time) Split means to separate, and List.Split means to split the list according to every N to form a separate List.

Our data is complete information every 2 elements, so we need to split it with the List.Split function.

❷ Table.FromList: converting from list to table

= Table.FromList (list, optional handling of the list as list, optional-corresponding title, optional-default value of null, optional-additional value handling)

After the split, we need to convert List to Table, so we need to use the Table.FromList function.

= Table.FromList (custom 1 List.Split each _, {"name", "cell phone number"}) the first parameter: it is a List, and the custom 1 here is the column after the split in the previous step.

The second parameter: for the operation of the previous step, since there is nothing to deal with here, write each _ directly.

The third parameter: returns the column name after the table, the column name is {"name", "mobile phone number"}.

The ideas of 👉 are summarized as follows:

The data columns in the table are deepened into lists because every two elements are a piece of information, so you can use the List.Split function to split every two elements into a List.

Finally, the split List is converted to Table with the Table.FromList function.

At this point, the practice of the M function is over.

3. Summarize. This paper introduces two methods of sorting out misplaced data.

The most common practice of ❶ Index function indexing, which is one of the classic uses of Index functions, is to index by constructing regular index values.

The M-function practice of ❷ PQ needs to be carefully studied.

Use deepening to extract a column of data from the query table to form a list; use the List.Split function to split the list so that every 2 elements form a list; to convert List to Table using Table.FromList.

Both of these methods can update data dynamically when adding or modifying data sources!

Test you:

In this case, every two elements are regarded as one message. If there are three elements, what will you do?

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiao Shuang

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.