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

The original title: "learn this trick, data entry is no longer worried, efficiency is a street away from colleagues!" "

Recently, I received a request from an old classmate Xiaozhu. Her boss gave her a form asking her to register the personal information of the company's employees. Here are the materials in the picture below:

In order to avoid non-standard names registered by colleagues when registering information, and to improve input efficiency, part of the data in the form sent by the boss uses the [data verification] function.

❶ gender and academic qualifications set up "sequence verification"

❷ ID number and phone number are set to "text length" and "error warning"

The ❸ home address is set to "input prompt".

Xiao Zhu was very efficient when entering the first few columns of data, but encountered difficulties in entering "academic qualifications".

If you want to know what problems she has encountered, there is no hurry. Let's take a look at how the boss sets up "data validation". After reading, you will know ~ (pay attention to the setting of the "education" column! )

PS: [data validation], that is, select from the list of rules to limit the types of data that can be entered in the cell.

1, data verification, setting method Let's first take a look at the most commonly used function of [data validation]-"sequence verification".

▋ gender and education setting "sequence validation" setting "gender" column:

❶, select the "gender" column, and click "data" tab-"data tools"-"data validation"-"data validation".

❷ in the pop-up window, find "Settings", allow you to select "sequence", fill in the source "male, female", and then click OK.

Note: "male and female" must be separated by English commas.

Set the "academic qualifications" column:

The setting here is roughly the same as the former, except that the source is filled in: "Primary School, Middle School, Senior High School, undergraduate course".

See here, many students must understand, Xiao Zhu encountered in the end what is the problem!

When the boss set up "sequence verification" for the "education" column, he did not set up "junior college", but the company happened to have employees with this degree.

When Xiaozhu is selected in the drop-down list, there is no this option, but manual input shows:

How to solve the problem? Students who are anxious to see the answers can skip to the next section directly. Students who are not in a hurry can patiently look down, which will help to teach you to set up a complete information entry form (highly recommended)!

▋ ID number and Mobile number set "text length" and "error warning" set "ID number" column:

❶ opens the [data validation] window, finds [Settings], allows "text length", data "equals", and length "18"

❷ click [error warning], select "stop" in the style, enter "ID number" in the title, enter "incorrect number of digits" in the error message (which can be changed), and then [OK]!

After the setting is successful, if the ID card number entered is one more digit or one less digit, it will be displayed:

Set the Mobile number column:

The operation method here is the same as setting the "ID card" column, except that the text length is "11" and the "title" and error message in [error warning] need to be slightly modified.

If I don't tell you the steps, you will, won't you?

▋ home address setting "input prompt" the above methods have learned, here is very simple!

❶ opens the "data validation" window and switches to "input Information"

❷ says "title" and "input information", OK!

Effect picture:

Every time a cell is selected, a prompt appears.

2. In the section on adding new data and solutions, let's help Xiaozhu solve the problem. That is, what if you want to add new data after you set up [data Verification]?

There are two solutions. The first is to delete the "sequence verification" in the "education" column.

Select the "Education" column, open the "data validation" window, and change the permission in [Settings] to "any value".

Then you can enter new data ~ but this method requires a second setting [data validation], which is a bit troublesome.

Let's take a look at the second method: add "college" characters to the "sequence".

Select the "education" column, open the "data validation" window, add "college" to the source, and make sure.

Select the "academic qualifications" column cell again, and you can select "College" in the drop-down list.

3. Summarize that the ❶ [data validation] function can limit and standardize the data types or values entered, such as "sequence validation", "text length verification" and "error warning".

After the ❷ [data validation] setting is complete, if you want to add new data, you can delete [data validation] or add new characters after "sequence".

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