In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > IT Information >
Share
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.
Last time I shared with you the filling tips of merging cells and segmented serial numbers.
Don't you remember? It doesn't matter, the portal is here:
Ask a colleague to fill in 1000 lines of discontinuous serial numbers, why did he leave work in 2 minutes?
In it I mentioned a niche function: N.
So how much do you know about this function?
Today, Satellite paste will share with you the skills of using N function in Excel!
The N function is a text function whose formula is:
= N (value)
Enter it, the value will return the value itself
Text returns 0
The logical value also returns the corresponding 0 or 1; the date returns its sequence number.
Although it is short, this guy is very useful!
1. Fill in the segment sequence number. As I said last time, select the cell to enter the formula:
= N (A2) + 1
Then press the shortcut key [CtrI+Enter] to fill in the sequence number of the segment ~
Principle: because the value in cell A2 is the text "eating and drinking part", enter the N function and return the value 0, N (A2) + 1, which is, of course, the serial number 1 ~
It can also be populated in conjunction with the IF function:
Just enter in E3:
= IF (E3 = "name", ", N (D2) + 1)
Just pull down! The serial number will automatically skip the name line.
Principle: IF (E3 = "name", ", N (D2) + 1) can be translated as: if the value of cell F3 is" name ", enter a null value, otherwise enter N (E2) + 1, and as we explained above, N function will let the text return 0, so 0: 1: 1, naturally in D3 will output 1!
2. Formula remarks sometimes we enter a formula into the table, but after a while we forget why we wrote it in the first place.
In fact, you can use the N function to add notes to the formula, and it will not affect the operation of the formula.
Like this:
You can remind yourself why the formula is greater than or equal to 100 without affecting the result of the formula.
3. The cumulative summation problem is sometimes quite annoying. The first line and the second line have to enter a different formula.
But the N function can be done at once:
Enter the following formula, and then drop down the fill:
= N (C1) + B2
In this way, the text is equal to zero, the numerical value is still numerical, and the operation can be carried out smoothly!
4. The combination formula ▋ checks and fills the gaps in the IF function.
There are 500employees in the factory, and 3000 screws are counted as qualified every day. Now we have to count the list of qualified screws. What should we do?
It's easy to think of using the IF function, right?
But we found that people who were absent from work also judged to be qualified!
At this point, we just need to modify the formula:
= IF (N (E2:E29) > = 3000, "qualified", "unqualified")
In this way, the text value will be judged to be 0, and the result will not be wrong.
▋ perfect search Sum Formula
N function can also improve the search and summation function combination of VLOOKUP!
The principle here is the same as in the previous section, solving the calculation problem when the calculated column contains text values.
To find the total amount of work of several people in column H, you can use the formula:
= SUM (N (VLOOKUP (H _ 2 _ 7 ~ D _ 2 ~ E29 ~ 2 ~ 0)
In this way, the text value "absence" will not interfere with the calculation!
Remember to press [Ctrl+Shift+Enter] to finish when you finish the formula.
(note: because you are using an array formula and there is a formula overflow, you have to select multiple cells in all versions except Office 365 to get the correct results. )
5. at the end of the day, we introduce several uses of the N function:
❶ segmented serial number filling
Remarks on ❷ Formula
Cumulative summation of ❸
Application of ❹ combination Formula
Did not expect that such a simple text function can expand so many interesting uses!
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.
Views: 0
*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.