4、 Sum formula 1.
Display the error value generated by the formula as null Formula: C2=IFERROR (A2/B2, “”) Description: If it is an error value, it will be displayed as null, otherwise it will be displayed normally.
Use the INDEX function to take a value.
6.
Note that the numbers in the sales volume column must be arranged in ascending order.
Judgment formula 1.
Multi condition fuzzy sum formula: C11=SUMIFS (C2: C7, A2: A7, A11&”*”, B2: B7, B11) Description: wildcard * 5 can be used in sumifs.
Partial formula except for the last 3 digits:=LEFT (D1, LEN (D1) – 3) Description: LEN calculates the total length.
The formula of IF multi condition judgment return value: C2=IF (AND (A2<500, B2="Not Due"), "Make up", "").
3.
2.
The number of occurrences is divided by 1 into the denominator and then added.
Take integer=INT (number) 3.
Formula: see the following figure for details: 0/(condition) can change the unqualified into an error value, while lookup can ignore the error value.
See the following formula for details: VLOOKUP and LOOKUP functions can take values by interval.
6.
The formula for counting the total number of people without repetition: C2=SUMPRODUCT (1/COUNTIF (A2: A8, A2: A8)).
Multi condition search formula: see the following figure for details: the formula principle is the same as the previous formula.
For example, “* A *” indicates that there are any number of characters before and after a, that is, A is included.
Sum formula at the same location for multiple tables: b2=SUM (Sheet1: Sheet19! B2) Description: After deleting or adding a table in the middle of a table, the formula result will be updated automatically.
LEFT truncates the total length from the left by – 3 3.
Sum formula by separating columns: H3=SUMIF ($A $2: $G $2, H $2, A3: G3) or=SUMPRODUCT (MOD (COLUMN (B3: G3), 2)=0) * B3: G3, The asterisk indicates any number of characters.
2.
Find the last qualified record.
Take absolute value=ABS (number) 2.
The last non null value search formula in the specified area; See the following figure for details.
Formula formula for intercepting any segment of a string: B1=TRIM (MID (SUBSTITUTE ($A1, “”, REPT (“”, 20)), 20, 20)) Description: Formula is intercepted by forcing N blank characters..
Multi cell string merging formula: c2=PHONETIC (A2: A7) Note: The Phonetic function can only merge character content, not numbers.
4.
6、 String processing formula 1.
Rounding=ROUND (number, decimal place) 2.
4.
Note: If two conditions are true at the same time, AND is used, and if either condition is true, OR function is used.
Partial formula before truncation: B2=Left (A1, FIND (“-“, A1) – 1) Description: FIND function is used to find the location and LEFT is used to truncate.
2.
You can’t miss the following! 1、 Number processing 1.
4.
The same engineering technicians, no matter they are doing data, tests, reports, etc., will inevitably use Excel tables.
Take the corresponding value formula by number area.
As long as we understand some of its use tips, the work efficiency is soaring up.
3、 Statistical Formula 1.
The formula for calculating the duplicate contents of two tables: B2=COUNTIF (Sheet15! A: A, A2) Description: If the return value is greater than 0, it means that it exists in another table, and 0 does not.
5.
Sum formula by date and product: F2=SUMPRODUCT ((MONTH ($A $2: $A $25)=F $1) * ($B $2: $B $25=$E2) * $C $2: Bidirectional Search Formula Formula:=INDEX (C3: H7, MATCH (B10, B3: B7,0), MATCH (C10, C2: H2,0)) Description: Use the MATCH function to find the location.
Note: COUNTIF is used to count the number of occurrences of each person.
