Click "Follow" in the upper right corner to get free dry goods tutorials every day
Some students asked a question about finding the missing invoice number, which is quite representative. The characteristic of this type of question is that it is difficult and not too difficult. Because in the end, when you see the solution, you will suddenly realize that it is so simple!
But this kind of problem is simple and not simple, because it has troubled many veterans in the workplace, there is no way to automatically find the missing ticket number, so first put the question on it and see if you can solve?
Take the following actual case to see, the existing invoice number (part of the example) is placed in column A, and the missing invoice number is required to be automatically counted, as shown in the figure below.
The above picture is only a partial example. In actual work, there are many invoice numbers. If you want to recognize with the naked eye, it will take a lot of trouble.
It is recommended to use 2 minutes, please think for yourself before reading down.
The idea of the solution:
To find the missing invoice number, you must first make a complete list of invoice numbers, so that you have a benchmark for comparison. Click here to find the missing invoice number. There are a lot of them, so you can use the filling technique in Excel to generate them in batches. The animation of
is demonstrated as follows.
With this complete invoice number, you can compare it with the existing invoice number to find the missing invoice.
After the idea is smooth, we can implement it in Excel.
Find the missing Excel formula:
Create a new auxiliary column, first write the formula, and then analyze the principle.
= IF (COUNTIF(A:A,E2),"","missing")
The formula diagram is as follows: Principle analysis:
uses the COUNTIF function to search from the existing invoice number, it will return 1 if it finds it, or 0 if it cannot be found, and then use the IF function to identify the position of 0 and display it as "missing".
In the end, you only need to display the missing invoices. You can use the quick screening technique to do it all at once. The operation process is as follows, look at the animation demonstration:
2p These commonly used The classic excel function formula skills can help you solve problems at critical moments, and those who are interested should hurry up and collect them.
Hope this article can help you! If you can’t remember, you can send it to the circle of friends and mark it yourself.
Students who want to improve the Excel function formula systematically, recommend the following ultra-clear video column↓
For more ultra-clear video system courses
, please see below
.
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-
-