I haven't written about dry goods for a long time. Today, I would like to share some interesting announcements in Excel. If you find it useful, please share it with your friends or collect it directly. Of course, you'd better pay attention to the public account/headline: "Fantasy Chenfeng", which is the best support for me.

Excel (Microsoft Office Excel) is a spreadsheet software. At present, Microsoft and Kingsoft are commonly used. Microsoft's home is Microsoft Office, such as Office365, Office 2019 and Office 2016. The Jinshan system is now commonly used WPS.

All the formulas I share here can be used by Microsoft Office and WPS. If VBA is available later, it may only support Microsoft Office.

1. Match function

The Match function returns the position of the specified value in the specified array area in Excel. It is generally used for searching.

For example, President Liu gave me a table to check whether the data in this table exists in big data. Normally, we use Ctrl+F to search one by one.

But it is different with Match. Let's take an example.

 =Match ("Value you want to find", "Range")

The value to be searched can be directly used in a specific location, such as whether A3 in Sheet1 and column B in Sheet2 exist. How to write it?

 =match(A3,'Sheet2'!B:B)

If it exists, it will return you to the location in. If it does not exist, it will return directly #N/A 。

2. Len function

The len function is generally used to calculate the length of the text in a table. I basically use it to check whether the ID card number and student ID number are correct.

(Yes, I am also in charge of student status.)

It is worth noting that the len function calculates spaces as characters.

for instance:

 =Len ("The value you want to find")

Very simple, very convenient.

3. Vlookup function

This is my favorite and most commonly used. In the work of student status/popularization, it is used most.

Of course, thanks to the teacher Fu @Mu Ruoxi Use examples provided.

The introduction is as follows:

VLOOKUP function is a vertical search function in Excel. It belongs to the same class of functions as LOOKUP function and HLOOKUP function, and is widely used in work. For example, it can be used to check data, quickly import data between multiple tables and other functions. The function is to search by column, and finally return the value corresponding to the required query sequence of the column; The corresponding HLOOKUP is searched by row.

A usage example:

 =Vlookup ("Value you want to find", "Range", "Which data to return", FALSE)

For example, the school gave me two forms. Form 1 is for me to fill in. For example, A1 is my name and B1 is my ID card number. In Form No. 2, A1 is the name and B1 is the ID card number.

In Table 1 and Table 2, A1 names are basically the same, but the order is different. If you want to fill in at one time, it is impossible to copy and paste one by one, then how to use it? Fill in the following formula in C1 of Form No. 1:

 =Vlookup (A1, [No. 2 table. xlsx] Sheet1! $A: $B, 2, false)

Note here that the value to be searched (A1) must be the first column in the search range (Table 2).

Among them, 2 is the second column of data returned (that is, the ID number).

FALSE requires accurate search. (It's not accurate to play with a hammer.)

4. Combination of functions

Simply share a few:

  • 1. Determine gender by ID number

Assume that the position of the ID number in the table is F2.

 =If (mod (mid (F2,17,1), 2), "male", "female")

Principle:

The number of ID card is 17th. The odd number is male, and the even number is female.

The mid function, mod function and if function will be explained in detail next time.

  • 2. Randomly generate numbers in the specified interval

I don't want to say this usage, but for example:

Generate a random number between 36.3 and 37.2 with decimals

 =randbetween(363,372)/10

Principle:

Divide the random number between 363 and 372 by 10 to get the answer.

If you are not satisfied with the number, press F9 on the keyboard to refresh it.

Next notice:

  • What is an if function? What's the usage?
  • What is the mod function? What can it be used for?
  • In addition to picking up numbers, what kind of sparks can mid make with other functions?
  • Is there any way to calculate the child's age on the specified date through the ID card number? For example, how old is the child on December 31, 2020?

End of full text[ Like this article, reward the author! ]