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

In the past two days, when I was having fun watching and chatting in the Akiba study group, a long list of messages suddenly appeared, which dazzled my eyes.

The student asked: is there any way to "lose weight" with a long list of formulas?

Of course it's--

Yes! A wildcard can be done directly!

But if you don't know this technique, you'll have to add it one by one.

There are two kinds of wildcards commonly used in Excel:

Question mark?: instead of a character

Asterisk *: instead of multiple characters

Today, let's take a look at how many problems we can solve if we use wildcards well.

To quickly count the data of the same brand, the question raised by the previous student is actually the need to count the number of cells containing a specified string.

For example, if you have some order data for courses, you need to count how many orders there are for Excel courses:

You can do conditional counting in one Excel course after another.

= COUNTIF (BRV B, "Akiba Excel data processing class") + COUNTIF (BRV B, "Akiba Excel Chart course") + COUNTIF (BRV B, "Akiba Excel PivotTable course") + COUNTIF (BRV B, "learn Excel with Akiba") ▲ swipe left and right

You need to COUNTIF several times for a few courses, and then add them up.

But you can also do it with a COUNTIF--

= COUNTIF (Excel B, "* Excel*") you see, directly replace any character before and after "Excel" with the wildcard "*". "* Excel*" can represent all Excel courses.

To quickly unify inconsistent names, we often encounter a situation where a certain category has a unified name.

However, people have to fill in according to their own preferences, resulting in very inconvenient statistics.

So when sorting out the data, you need to unify these inconsistent names into one.

For example, all Excel courses are unified into the "Akiba Excel data processing course":

It's too slow to change one by one! But we can replace all Excel courses with "* Excel*".

[CTRL+H] Open the replacement window, find the content as "* Excel*", and replace it all with "Akiba Excel data processing class".

Got it!

According to the abbreviation to find the full name, we often encounter this kind of question, some project names are written simply, how to correspond the project abbreviation to the full name?

According to the specified data lookup value, you should be very clear that you can use VLOOKUP.

Did you know that VLOOKUP with wildcards can do fuzzy search?

= VLOOKUP ("*" & C2 & "*", $Agg1veAgg8Magne8Magne1 false) the abbreviation to be found is preceded by the & connector plus a "*" to replace the data containing the acronym.

Cross-table summation sometimes we put some data in different worksheets by month or by name of the person in charge.

If you want to summarize the data in these worksheets, what do you usually do?

If you know how to use wildcards, a SUM function formula can be done quickly!

= SUM ('*'! B2)

Using "*" to replace all worksheet names in the current workbook (except the current worksheet), you can sum B2 cells in all worksheets.

If it is a summary of the performance table of such a person in charge, and the data with "Qiu" in the name, what should be done?

I'm sure you already know?

All in all, this is the event caused by a wildcard. Let's take a look at several common scenarios of wildcard characters:

Quickly count the data of the same brand

Quickly unify inconsistent names

Find the full name according to the abbreviation.

Sum across tables.

Have you learned everything?

What is the key to using wildcards?

Grasp the data characteristics or rules, take the rules as the starting point, and use wildcards to replace them in batches.

In Excel, it's the same thing if you want to operate efficiently.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaomin, Editor: Zhu Lan

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.