Practical Excel skills sharing: the magical use of connection strings!

Using excel to connect strings is a commonly used technique in our daily work. I believe that the most commonly used connection method is "&". But in fact, there are many ways to connect strings in excel, and the seemingly inconspicuous connection string has magical effects in some specific occasions. Are you curious? Hurry up and follow the author's instructions in Picture E and take a look!

[Foreword]

In the actual application of using EXCEL, we often use it for statistical convenience , if the data elements are divided into more details, the statistics will have more dimensions. Similarly, sometimes we also need to split very detailed content and merge it into one content and put it in a cell, maybe for reference, maybe for identification or reading. Let’s take a common small example - such as "Daily Data Report".

For the convenience of statistics, we will definitely make the content in 1:2 lines; but if the leader needs us to make a report, it is recommended to make it in 5:8 lines. This makes it more readable.

[Text]

In order to use EXCEL to deal with such problems more conveniently, EXCEL has prepared many methods for us——&, CONCATENATE and PHONETIC functions are used to handle it, and there will also be some "external force" methods to solve it. Today we will use the same simulation data to introduce it to everyone separately, hoping that students will not be in a hurry when encountering similar problems.

[Data source]

Data source processing requirements: connect the values ​​of each field to form The new string is filled in column H.

Our simulation data adopts the format of "pure numbers". In order to facilitate the versatility of string connection, we also use the "one-digit" method, so that everyone can understand a certain number in it. It can also be a string that needs to be connected. Before looking at the following content, first think about how we will solve it. Learning with thinking will be of great benefit to students in absorbing knowledge and applying functions flexibly.

【Solution】

Method 1

I2 cell function:

=A2&B2&C2&D2&E2&F2&G2

This should be the most commonly used method of connecting strings by students. There is not much to introduce.

Method 2

I2 cell function:

=CONCATENATE(A2,B2, C2,D2,E2,F2,G2)

The CONCATENATE function can connect up to 255 parameters, and the total number of characters must not exceed 8192. In the EXCEL365 version, there are several new functions, among which the CONCAT function is an upgraded version of the CONCATENATE function. However, because higher versions of EXCEL are not yet so popular, we will not talk about these things that cannot be tested by everyone.

In addition, many people say that the EXCEL2016 version has these new functions TEXTJOIN, CONCAT, IFS, DATESTRING, NUMBERSTRING, IFS, MINIFS, and MAXIFS, but according to the author’s E diagram, not all of them The EXCEL2016 version has these functions. It is said that these functions were included in the EXCEL2016 version during testing, but after the EXCEL365 version was released, they were canceled in EXCEL2016. I don’t know, but if you have the conditions, it is still recommended to use a higher version of EXCEL so that you can try many new functions.

Method 3

I2 cell function:

{=SUM(A2:G2 *10^(COUNTA(A2:G2)-COLUMN(A:G)))}

This is an array function. You need to press "CTRL SHIFT ENTER" three times after entering the function. The key ends function entry and only applies to data sources with a single digit in the cell.

Function analysis:

This function uses mathematical thinking, taking the first row of data as an example, the idea is as follows:

Then let’s see what the rules are for the corresponding “0” on each bit, and whether our function perfectly meets my requirements:

From the above table, we can see that the COUNTA(A2:G2)-COLUMN(A:G) function can help us calculate how many 0s there will be in each digit. Using 10^ (^ is The meaning of exponentiation is equivalent to the usage of the POWER function) to determine which digit the number in each field is. For example, 10^6, which is 10 raised to the 6th power, is equal to 1000000. The overall function is listed in the following table:

##1000000 7000000##Field 2Field 34100040000

Are we adding the corresponding products together to get our result? Here is a suggestion for students: If you want to learn array functions well, you must improve your "logical thinking" ability. The three tables above are the calculation process of our array function. The process of writing this function is basically based on This idea is to select the corresponding function to edit. That is to say, it can allow us to run at a speed without a computer, but it must have the same running ideas as a computer.

Method 4

Use other programs to solve the problem.

In order for EXCEL function "newbies" to have a good way to handle this kind of problem every day, the author's E diagram description also summarizes this method for you, which is pure operation, but it is also It is the author's original intention to obtain results in a "flash sale" style, hoping to help students of different levels.

Method 5

I have mentioned so many methods before, but some people will definitely say "the author missed a function".

Yes, for problems such as connecting strings, there is indeed a function that can be used - the PHONETIC function. Do you still remember the problem with this data source we talked about at the beginning? We are using "numbers in numerical format". For data in this format, the PHONETIC function cannot be connected. So the PHONETIC function cannot be used for this question? Of course it can be used.

Do students understand? After we format the cell as text and enter the numbers again, the PHONETIC function can connect the contents. But it is really tiring to enter one by one. At this time, we can select a certain column of data in the source data area and use the "Column" function to convert the data into text column by column, then this problem will be solved.

【Application】

All the EXCEL tutorials written by the author in E diagram are based on One purpose: not to provide general basic tutorials, not to provide tutorials that have no practical significance, and strive to solve students' work problems in a "simple and violent" way. So I think: Tutorials without actual cases are just hooligans! So what is the practical significance of today's "connection string" function?

Case One: Production Daily Data Report

Remember the small case display in the previous article, if you read it carefully and patiently Once here, you can see a more optimized format for daily data reporting. We enter the function in cell A6 (we have merged the cells in the A6:C6 area and indented it by 3 bytes to make it more beautiful when forwarding.)

="Report date:"&

TEXT(A2 1,"yyyy mm month dd day")&

CHAR(10)&

" Turnover on the 28th: ​​"&ROUND(B2/10000,2)&" million yuan, "&

CHAR(10)&

" Last week's turnover: "&ROUND(C2/10000 ,2)&"Ten thousand yuan,"&

CHAR(10)&

" This week has been sold: "&ROUND(D2/10000,2)&

" Ten thousand yuan! "

uses "&" to connect each string. From this, we can find that both function return values ​​and text strings can be connected using "&". Then use CHAR (10) to return the newline character, Make the content to be sent more layered, so that when we want to send data, we can directly copy and paste, then click send and it is done.

What is worth mentioning here is: after string concatenation, the Any content is unformatted, such as date format, accounting-specific format, etc. After string concatenation, only the regular format is retained, so we often need to use other functions to convert, just like the TEXT function in the case to convert the format of the data Converting to date format is the same.

Case 2: Quickly create drop-down content for data validity

Use data verification to create a drop-down menu. I believe many students will know it. If there are only three , five options, we can enter them directly in the data verification window. If there is a lot of content, we can also use the name manager. Then borrowing the theme of today's article, we can also use the PHONETIC function to make it.

Of course, from a modeling perspective, for the drop-down menu approach, the author's E diagram still recommends the method of "custom name" combined with "data verification", which can form Dynamic drop-down menu content. This case is designed to help students who do not know complex operations also have a quick operation experience.

Case 3: Classic nested INDIRECT CONCATENATE

We are still using the previous data. Suppose we want to add the table "Method 1" to the table "Method 5". Sum the data in column A in each table and list it in a summary table (typical total-score formula summary table). As shown in the figure below:

B2 cell input function:

{=SUM(--INDIRECT(CONCATENATE(A2,"!A2 :A16")))}

After completing the input, press the end key of the array function CTRL SHIFT ENTER to end.

Function analysis:

The value of cell A2 is "Method 1", and "!A2:A16" is a string. Use the CONCATENATE function to connect the two to form a Address text value referenced across tables.

Use the INDIRECT function to "activate" this address to form a reference, and finally use the SUM function to sum the contents of this area.

Why use "--"? We don't need to use "--" and "three keys" in daily life, because we just sorted column A in "Method 5" into "text" format, and SUM cannot sum text data, so here we use Add "--" to convert text data into numerical data.

【Editor's Note】

&, CONCATENATE function, PHONETIC function, we all have an overall introduction, and we also give a The display of cases, in actual work, still depends on everyone's understanding of the principle of functions and their ideas for using them. Function syntax and functions are inseparable. Only by understanding the scope of its application can we master it flexibly. That’s it for today’s tutorial, practice more.

Related learning recommendations: excel tutorial

Field name	corresponding value	multiplied by the corresponding number of digits	##corresponding product
Field 1	7
8	##100000	800000
6	10000	##60000	##Field 4
			#Field 5
100	000
Field 6	4	10	40
##Field 7	4	1	4

The above is the detailed content of Practical Excel skills sharing: the magical use of connection strings!. For more information, please follow other related articles on the PHP Chinese website!