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

Original title: "3 super-practical WPS table tips, 1 minute to learn, efficiency increased by 50%!" "

Hello, everyone. I'm Xiao Yin.

Recently, the company is sorting out employee information. No, as soon as I arrived at the office this morning, I received a document.

Boss: this is the department table of our company. you merge the same departments, then merge the names of the employees in each department into one cell, and count the number by the way.

This is a piece of cake for me, who has been working hard on WPS recently.

Five minutes later, I sent the finished document and was praised by my boss.

The specific operation is as follows.

1, merge the same departments select the range of cells to be merged, select the "start" tab, click the "merge Center" button, and then click "merge the same cells".

In this way, there is no need for each department to merge separately.

2. Merge employee names ❶ names are generally connected with ",", so first of all, we have to create a new column next to the name column.

Type "," directly in the first cell, and then double-click the fill handle to fill it.

❷ select the cell where you want to display the merged name, and enter the formula in the edit bar:

= PHONETIC (B2:C4) the range of cells in parentheses corresponds to the name to be merged and the range of "," on the right.

It would be fine for ❸ to follow the previous steps and operate each department in turn.

PS. The number of people in each department varies, so fast filling cannot be used here.

3. Counting the number of departments, we can see that in the cell of the merged department name, each name is followed by ",".

Therefore, the number of "," is equal to the number of names.

In the cell where the number of ❶ clicks is located, enter the formula in the edit bar:

= LEN (B2)-LEN (SUBSTITUTE (B2, ",))

The LEN function returns the number of characters in the text string, and the SUBSTITUTE function replaces some characters in the text string.

The above formula can be understood as:

B2 number of all characters-B2 number of all characters after removing the don sign = the number of B2 ton sign (that is, the number of names)

❷ finally double-clicks the filler handle to get the number of people in all departments.

The name of column B is not displayed in the demonstration because of the cell height limit.

The boss over there replied: not bad, Xiao Yin.

If you also need to deal with department information, don't miss these three tips.

This article comes from the official account of Wechat: Akiba Excel (ID:excel100), author: Xiaoyin, 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.