Excel filter formula setting issue

I need to set up secondary columns to do so.

If your table only has two columns of ab, I need to do auxiliary calculations in columns h to k, and output sorted data in columns d to f. (Of course, if you don't want to see the part that aids the calculation in the data table, you can move it to another page such as sheet2).

1, fill =if(a1<13,if(b1>50,a1,""), a1) pull-down padding.

2. Fill in =if(j1=) in k1"","", b1) pull-down padding.

3. Fill in =if(j1=) in i1"","",value(rank(j1,j:j,0)&(9999-k1)))) drop-down padding Among them, if the eligible values in column A are ranked from largest to smallest, this is the case, and if it is ranked from small to large, you have to change the 0 in the rank to 1.

Also, what is the maximum number of digits in column b? If it is greater than 3 digits, then the 9999 in it will be changed to 99999--- which means that the number of 9 digits is two more digits than the most digit in column b.

4. Fill in =if(i1=) in h1"","",rank(i1,i:i,1)) drop-down padding.

Now it's the Rank Data Output area:

5, fill in 1 in d1, fill in =if(d16 in d2, fill in =vlookup(d1,h:k,3,0) in e1.

7. Fill in =vlookup(d1,h:k,4,0) in f1.

The easiest thing is to filter it, you don't filter to select the numbers, and the data in the other columns doesn't need to correspond?

1. The reason for the failure of the formula.

The source formula of the formula must be regular, if it is misused into text format, the formula cannot start to calculate, after changing the format to regular, press f2 in the hidden hand state, and then press enter to activate the formula.

2. What should I do if a different formula fails?

In practice, there will be many different formulas in a table, and it is time-consuming and laborious to activate and fill a column, is there a way to quickly correct it?

If there are no special formatting requirements, you can change them all to the regular ones.

After changing the formula column format to the regular one, the activation principle is to make the cell again, so we can use CTRL+H, click Replace, and replace the equal sign with the equal sign. This doesn't change anything for the formula, but for excel it is a calculation.

3. After changing, why is the formula still useless?

As long as the formula can display the color, it is a real formula, and you can see that the formula may be the button that displays the formula is turned on, turn it off, and the shortcut key Ctrl+ (can also be used to analyze the formula).

4. How to check the formula.

If you see a formula that works well, how do you analyze it?

When deconstructing other people's formulas and analyzing errors, you must learn to use Excel tools, such as inserting the function FX button, which will pop up the parameters of the function.

Inserting functions and formula evaluations makes it easier to analyze formulas.

If the formula can't be calculated, you can click on the calculation option under the formula to see if it is set to manual, and the default is automatic.

Excel often needs to use filtering formulas to filter out duplicate data, how to use filtering formulas? The following is a tutorial on the use of the filter formula of excel** shared by me for you to read and learn.

Tutorial on how to use filter formulas for excel:

Filter formula using step 1: first we open excel, I use the default installation of excel 2003 in the computer, the following operations are based on this version of excel, if your version is different, the operation steps may be slightly different. To make it easier to test and understand, I created a new worksheet and entered some simple data.

Filter formula using step 2: Let's first introduce a common method to remove heavy items, that is, Excel's built-in filtering method. First select the area we need to filter, then click on the "Data" tab, find the "Filter" option and click on it, and finally we click on the "Advanced Filtering" option.

Step 3 of the filter formula: A warning box as shown in the following figure may appear, which is probably caused by improper use, but it does not affect our use, we can click OK. Then we check "Select non-duplicate records", at this time we have two operation options, one is to display the filter results in the original area, and the other is to copy to other locations, let's look at the original region display first.

Step 4: After clicking OK, we will find that the original data region has changed from duplicate data to no duplicate data. Of course, this is just a filter result, not to overwrite our source data, if you want to restore the data, just click on all to show, if you want to remove duplicates, you need to save the existing filter results.

Filter the formula using step 5: Let's take a look at copying to other locations. First of all, we choose to copy to another location, at this time the "Copy to" box will become available, we just need to click the button on the right (red line marked), select the copied area and click OK, don't forget to check "Select not to duplicate record items".

Screening Formula Using Step 6: In addition to this method, we can also use the formula method. We first sort the existing data once, either in ascending or descending order.

Then we select an empty column (or row, depending on your data) as a helper and enter 1 in the corresponding first blank column.