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

Behind the headlines! Hello everyone ~ here is the workplace struggle satellite paste trying to introduce Excel knowledge in the most easy-to-understand language.

Remember the last time we shared the N function?

It's short and unpopular. Is there (portal: click here to jump)

After that, Wei, I, ah, had a strong interest in the unpopular function!

This is not, find me a guy who is cold to the Arctic Circle-- Phonetic function ~

It is a function that is more flexible and easy to use than concatenators and Concatenate on merged cells, don't you believe? Let's see ↓↓↓.

1. Function introduction first, let's take a look at the Phonetic function.

= PHONETIC (reference) it is a text function that is used to extract pinyin from a text string.

Note that the conversion object must be text, and if it is the result of a number or other formula output, it must be converted to text before it can be used!

2. The simplest way to quickly extract pinyin is to extract the corresponding pinyin.

For example, we enter in cell B1:

= PHONETIC (A1) when the enter key is pressed, the pinyin "Qiqiqianyy è" is automatically generated.

Uh. A little chicken rib, huh? I can't even figure out what's the use of this.

Well, the following usage is the correct way to open this function!

3. Merge cell content ▋ merge cell content like this, enter it directly in C2:

= PHONETIC (A2:B2)

You can merge the contents of the two cells!

What What? You say you can use Concatenate or "&". There's no need to learn this function again.

Hem, what would you do if I came up with merging multiple cells?

With so many squares, how tiring it is to use the above two!

= CONCATENATE (A 3, B 3, C 3,... I have to look at the flowers in my eyes.

Or our little Phonetic is easy to use! (in addition, there is another function that can be done, known as the collection of student comment areas.)

Well, this is much cleaner!

Sometimes there are multiple solutions to a problem, but there are different methods.

If you want to master the function features and Excel functions, choose the most concise and efficient one from many solutions, and you don't learn how to get Excel.

The Phonetic function can also...

❶ plus delimiter

= PHONETIC (A1:B7)

❷ ignores empty cells

= PHONETIC (A1:B5)

Thieves are good to use when grouping data or integrating projects!

The ▋ federated zone reference is not over yet!

It can also directly merge the contents of multiple regions ~

Like this, ↓↓↓ doesn't have to worry about how to locate so many areas!

= PHONETIC ((A2 F2rect A10bureau D10))

PS. The numbers here are all in text format.

The nesting of ▋ functions is not a problem.

Although the PHONETIC function does not accept pseudo-text-that is, values generated by other formulas, as long as the formula does not get a value, but a delimited area, then it can run successfully!

For example, with so much data in the following figure, every three lines have to be merged. If you use PHONETIC alone, won't you have to reset a formula every time?

However, if you use the following formula:

= PHONETIC (OFFSET ($ROW ()-1), 0Power3)) as long as you write this one and drag it down, it's convenient!

4. How about writing it at the end? the function that looks like a chicken rib is also very useful!

Today we introduced the unpopular function PHONETIC:

Fast extraction of Pinyin by ❶

❷ merges cell contents

The contents of merged cells have the following special uses:

① plus delimiter

② ignores empty cells

③ references federated area

The nesting use of ④ functions

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

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.