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

The original title: "4 expert special Excel tips to double your work efficiency!" "

There is a very magical symbol in Excel: an asterisk (*).

Its big name is a wildcard and can represent any number of characters.

Its magic will make all people have to admire it.

So it has another name: "Tong admire".

So what is the magic of it? let's take a look at it together.

1. The ingenious use in the formula is shown in the figure below, which is a payroll.

If there are many subsidiaries in the same group company, there are many people with duplicate names among the subsidiaries.

Then for the group head office, it can be used to add a job number after the person with the same name to show the difference.

For example: now we need to find out what Li Xiaolong's salary is.

Here we can use wildcards (asterisks *) to solve this problem. As shown below:

The formula is as follows:

= VLOOKUP (E2 & "*", BJV CJR 2) Formula Analysis:

Here, the VLOOKUP lookup function is used to find the character at the beginning of the content of the [E2] cell in the [B] column, followed by an asterisk to indicate that it can represent any number of characters, and returns the salary corresponding to the [C] column.

Whether it is Li Xiaolong, or Li Xiaolong 0327 and so on will be found.

I usually see a lot of friends use wildcards like this. As shown below:

The formula is as follows:

= SUM (IF (E2 & "*" = B2 B7, C2RV C7)) seems to be quite right, but the result is wrong.

In fact, wildcards do not support this use in SUM functions.

Functions that can use wildcards (asterisk *) generally have the following common functions:

HLOOKUP, XLOOKUP, MATCH, SEARCH (B), SUMIF (S), COUNTIF (S), AVERAGEIF (S), MAXIF (S), MINIF (S), etc.

In particular, I would like to remind you that using wildcards in functions can only be used for text, not for numeric values.

For example, we want to find a number that starts with 8, as shown in the following figure:

The formula is as follows:

= VLOOKUP ("8*", Corex Cpeng 1) Formula parsing:

Connect a wildcard character (asterisk *) with the number 8, look up the column in [C] and return the salary in the [C] column.

The result shows an error value, which indicates that there is no such number.

2. The wonderful use in finding and replacing, as shown below is a list of decoration projects:

Now you need to delete all the parentheses and everything in them.

At this point, we can solve this problem by finding and replacing.

Press [Ctrl+H] to bring up the find replace dialog box

Enter: [*] in [find what]

Do not enter anything in [replace with].

Click "replace all", and the results are as follows:

All parentheses and the contents in parentheses are replaced with blanks.

What we need to pay attention to here is:

Finding wildcards in substitution (asterisk *) is more extensive than being applied in a function.

It can include not only text, but also numerical values, and even all data such as error values and logical values.

3. Ingenious uses in screening, for example, in the following payroll, we want to screen the list of people whose surnames begin with Chen's surname.

Just type: "Chen *" in the search box.

If you enter only "Chen", all names that contain the old words will be filtered out.

What we need to remind you is:

The wildcard character (asterisk *) in the search box here also supports numeric search.

For example, in the salary column, enter 8 numbers in the search box, and all values starting with 8 will be displayed.

The function range of the asterisk here is the same as that found and replaced above.

In addition, in the payroll column, there is a Digital filter and a Custom filter under this option.

There is a [start with] option.

However, the [beginning] here applies only to text filtering, not to numeric filtering!

Even if you set the beginning to 8 here, the numeric results will not be filtered.

4. Knowledge extension asterisk * in addition to being used as a wildcard above, it also has a magical use in formatting cells.

As shown in the figure below, the sales growth rate in 2022 relative to 2021 needs to be calculated.

If it is an increase, the percentage is displayed in green font and the up arrow symbol is also displayed.

If it is falling, the percentage is displayed in red font and the down arrow symbol is also displayed.

Usually we make the following settings:

Select the [D2:D6] cell and press [Ctrl+1] to bring up the dialog box for formatting the cell:

The custom cell format is as follows:

[green] ↑ 0.005%; [red] ↓ 0.00bp0.00% this customization has three sections:

The positive number of the first paragraph shows green and shows the up arrow.

The second negative number displays in red and displays a down arrow.

The third paragraph 0 value displays the default color black.

The middle is separated by a semicolon.

After this setting, there is a small defect, that is, the position of the arrow is not fixed on the same vertical line, showing a little uneven.

If we add an asterisk + a space after the arrow.

As shown below:

So the arrows are all displayed in the same place. Neatly and beautifully!

Here an asterisk + a space means to repeat the character after the asterisk to fill the entire cell width.

5. at the end of today, we introduce the application of wildcard characters (asterisk *) in functions, search and replacement, filtering, and so on. Some can only be used for text, some can be used for text, and some can also be used for numeric values.

In addition, we also extend its application in cell formatting.

You must remember when this asterisk is used as a wildcard and when it is not used as a wildcard.

In addition, if you want to remove the wildcard in terms of function, search and replace, filtering, and so on, you only need to add a wave character (~) before the asterisk to use the wildcard as a normal character.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Der Spiegel in Heart

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.