excel how to find the exact location, there is a case of providing a detailed serial number

Take the problem as the content (the subject of the email must contain "excel", I use this as a basis to identify non-spam emails, so as not to delete them by mistake), and the excel sample file (showing the current situation and target effect,) as an attachment to help you see.

First, sort the original table in column B. Insert two columns in column C, divide column C into column C of letters and column D of numbers, set conditional formatting in column D2, and add 1 compared with D1, if not, it will be displayed as a red background; Use the format brush to copy the format of D2 to all columns, then check whether the student number is inserted in the line where the red box is located, insert a blank line if so, fill in a sequential student number in column B, and change the text to red; When complete, delete columns C and D.

Create two new columns, enter the complete class number and serial number (student number) in order, and then use the if function plus the vlookup function formula to find the name and other information corresponding to the serial number, and return a null value if there is no value corresponding to the serial number.

Formula form. if(iserror(vlookup(ordinal cell of the new column, original ordinal column: name ordinal column, 2,))."",vlookup(the ordinal cell of the new column, the original ordinal column: the name ordinal column, 2,))).

If the new class number is listed in column D, the sequence cell of the new column starts from E2 in column E, and the data in the original three columns are columns A, B, and C respectively, and the formula is entered in F2 in column F.

if(iserror(vlookup(e2,b:c,2,))"", vlookup(e2,b:c,2,)) drop-down to copy the formula.

Then select column e, right-click, copy, right-click, selectively paste the --- value, and finally delete the original data. The data left in this way is what you see in Figure 2.

Take a screenshot of the row and column numbers.

Others use vlookup as an example.

...a...b...

c...Serial number. Name.

Age. 6566...King.

22...4556...Plum.

First, there should be a table like the one above as a reference.

--sheet2

..a...b...c...6560...Formula.

Then you enter a sequence number in A1 of Sheet2.

Enter the formula in B1 as follows.

if($a1="","",vlookup($a1,sheet1!$a:$c,column(b1),0))

In this way, B1 will get the search result of the previous A1, and the next is to pull it to the right and fill in the formula.

For example, if the data is in column a, b1 enters =if(find("105",a1),a1,"") formula to fill down.

Select the header row first, click Data - Filter - and then quickly find the desired serial number.

Brother selected these 1000 serial numbers, ctrl+f, don't you find it?

Add a column in front of you. Fill in the following. For example, 1 to 10000 can be compared with the second one. Look at the difference of 1 and you can see it.

Select that column, and then use the location in the search to find it, which is fast.

It is recommended to make three tables, a summary table, all of which are generated by formulas, an outbound table and an inbound table, which are entered as basic data, and then summarized by the sumif function or the multi-condition sumproduct function.

If you only calculate the inventory of a certain product, the receipt is counted as a positive number, the issue is counted as a negative number, and then the pivot table is used, the current inventory of each product can be counted at any time. If you also want to count the quantity of goods received and shipped, add two columns respectively, and identify the receipt and delivery.

Is there a diagram to add to the explanation?

I don't really understand what you mean, you can add me to it.

First establish a data search standard, and then use the vlookup function, and I have put the specific operation in excel to take a screenshot.

Press the Ctrl key and the left mouse button at the same time to pull down.

Use the sumif function to find and sum up.

Enter =sumif(a$2:a$12,d5,b$2:b$12) in the sum cell for mango

For details: Directly sort the first table in ascending and descending order, select the required table for display, and confirm.

What comes out is the details you want.

You can use the menu: Data - Filter - Advanced function, and the effect is as follows:

It is most convenient to use pivot tables, and the troublesome points are to use function formulas.

In this case, it's best to use pivot tables directly.

Material Tools: Excel2010

1. Open excel**, don't number it first.

2. Enter 1 to fill it downward.

3. After screening, it is found that the numbers are not continuous.

4. Enter =n(subtotal(3,c$2:c2)) in a2.

5. Press enter to get the result.

6. Fill down the mouse to get all the results.

7. Then the number is continuous after the month screening.

Use the subtotal function for numbering.

In the figure above, column A is the column to be numbered sequentially after filtering, and A2 is the input formula.

subtotal(103,b$2:b2)

The formula is copied from a drop-down level, and the following is to automatically filter the gender as "female" to see the results, as shown in the figure below, the number is a continuous natural number starting from 1.

Knowledge Extension: subtotal(function num,ref1,[ref2],..)

function num required. A number between 1 and 11 (with hidden values) or 101 to 111 (with no hidden values) that specifies which function to use for subsummary calculations in the list.

In this example, 103 refers to the counta function for categorization.

ref1 required. The first named region or reference for which you want to summarize the calculation.

ref2,..Optional. The 2nd through 254th named region or reference to be calculated for the subtotal.

Description: If you use ref1, ref2....There are other subtotals (nested subtotals), they are ignored to avoid double counting.

When function num is a constant from 1 to 11, the subtotal function includes the values in the rows that are hidden by the Hide Rows command, which is located under the Hide and Unhide submenu of the Format command in the Cells group on the Home tab. Use these constants when you want to sort and summarize hidden and non-hidden numbers in a list. When the function num is a constant from 101 to 111, the subtotal function ignores the values in the rows that are hidden by the Hide Rows command.

Use these constants when you only want to sort and summarize non-hidden numbers in a list.

The subtotal function ignores any rows that are not included in the filtered results, regardless of the function num value used.

The subtotal function is suitable for columns of data or vertical regions. Does not apply to data rows or horizontal areas. For example, when function num is greater than or equal to 101, you need to classify a horizontal area, such as subtotal(109,b2:

g2), hiding a column does not affect the subtotal. However, hiding a row in the vertical area of the subsummary will affect it.

If one of the specified references is a 3D reference, the function subtotal returns an incorrect value of value! 。

18-excel**Serial number is continuous after screening.

Try it with a function, let's say column A is the filtered data, column B is empty, and b2 is entered.

count($b$1:b1)+1

Scroll down, unfilter again, and try.

This is very easy to implement, copy to the new ** after filtering, and then add the serial number and it's OK.