By check the salary column a lot of capital K, first by looking for replacement, CTRL + H to replace uppercase K with lowercase K, add a list of the minimum wage, the newly added a column of
C2 cell input:=LEFT (B2, FIND (" k ", B2) - 1) * 1, is multiplied by 1 will be converted to digital text, and then fill down, results are as follows:
1.2 to calculate the average of the minimum wage in Beijing? 1.3 what is the minimum wage of the lowest average?
On calculating the average minimum company in Beijing, there are three kinds of methods:
1. The function formula
Low calculation in the Beijing area total area/Beijing area of posts:=SUMPRODUCT ((A2: A6877="Beijing") * C2: C6877)/COUNTIF (A2: A6877, "Beijing")
2 array functionUsing=AVERAGE (IF (A2: A6877="Beijing", C2: C6877))
Pivot table 3
Behind the pivot table to use, also don't introduced here
It is concluded that effect as shown in figure
1.3 what is the minimum wage of the lowest average?
Copies column A city to A first position, is worth to the city only by removing duplicate values, create A new column and build (I'm in the L column), K2 for Shanghai, L1=business input (IF ($A $2: $A $6877=K2, $C $2: $C $6877)), fill down, used the method of index + match, the smallest city=index (K2: K14, match (MIN (L2: L14), L2: L14,)) effect the following
The smallest city: tianjin
1.4 Beijing undergraduate recruitment, minimum wage between 7 to 11 (greater than 7 less than 11) how many letters are there in the post?
Using two count subtraction algorithm
=first calculation & lt; 7:=COUNTIFS (A2: A6877, "Beijing", B2: B6877, "bachelor", D2: D6877, "& lt; 7 ")
=calculation & lt; 11=COUNTIFS (A2: A6877, "Beijing", B2: B6877, "bachelor", D2: D6877, "& lt; 11 ")
Then both subtraction, of course I can merge two steps as follows: COUNTIFS (A2: A6877, "Beijing", B2: B6877, "bachelor", D2: D6877, "& lt; 11 ") - COUNTIFS (A2: A6877, "Beijing", B2: B6877, "bachelor", D2: D6877, "& lt; 7 ")
Pivot table also can do screening, screening of tag data - between 7 to 11, the result is slightly difference, is closed on the interval, suggest add integer number plus or minus 0.0001, such as 7, written in 7.0001 or 6.9999.
Task 2 will post label (positionLables), contains the "big data" post screening out
Use the sort selection - text selection - contains, after screening, results are as follows:
Task three will minimum wage section, (0, 4] for low, (4, 8] for the 8 above for high
The fastest method is to lookup fuzzy search, an if can also be nested, both try
=the IF (A2 & lt; Eight, IF (A2 & lt;=4, "low", "the"), "high") fill down
Results the following
Vlookup fuzzy search method
C2 input=VLOOKUP (A2, $H $7: $I $9, 2, 1), H7: I9 for input threshold and corresponding including, the critical value that I set [8] 0,4.0001, corresponding high [low,,]
The outcomes of effect and the if