Some formulas and VBA scripts that Excel uses to process data

In the recent work with Excel statistics processing some data, just before the self-taught for a period of time of Excel, this time just use. In order to deepen the impression, later easy to read, recorded. This article will be added continuously.

IF multiple conditional Judgment return value

IF (Logical_test, [value_if_true], [Value_if_false])

Example: Less than 500, and not expired, returns "replenishment"

IF (and (a2<500,b2= "not Expired"), "replenishment", "" ")

Title for engineer or engineering, return to "meet"

IF (OR (c3= "engineer", c3= "Engineering"), "Meet", "" ")

Description: Two conditions are set up with and, either set up with or function.

IFS multi-scene judgment return value

IFS (logical_test1,value_if_true1, Logical_test2, value_if_true2, ...)

Example:

In a column, the values that match the criteria are given according to different judging conditions.

IFERROR Displays the error value generated by the formula as a custom value

IFERROR (value, Value_if_error) The first parameter is the formula that needs to be judged, and the second parameter is the value to return when the first parameter is faulted.

Example: Score below 60, fail

IFERROR (a2>=60, "failed")

COUNTIF Single Condition Count

COUNTIF (range, criteria)

Example: Statistics e column number of people with wages greater than 6000

COUNTIF (E:e, ">6000")

COUNTIF (E:e, ">" &M9) assumes that the M9 cell content is 6000

COUNTIFS Multi-condition counting

Countifs (Criteria_range1, Criteria1, criteria_range2, criteria2, ...)

Example: Statistics belong to the sales department, the number of employees with wages greater than 6000

Countifs (D:D, "Sales", E:e, ">6000")

SUMIF is searched by criteria in the lookup area and returns the value in the data area corresponding to the lookup area.

SUMIF (range, criteria, [Sum_range])

Example: When Sum_range is not specified, the range area is summed directly

SUMIF (C:c, ">1000"), summing cells greater than 1000

SUMIF (b:b, "juice", c:c), Sum of C-column sales for the B-listed juice

SUMIFS data summation of multiple conditional combinations

Sumifs (Sum_range, Criteria_range1, Criteria1, criteria_range2, criteria2, ...)

Example: Statistics production department, the sum of male wages

Sumifs (K:k, D:d, "production", C:c, "male")

AverageIf, AverageIfs and SUMIF, sumifs similar

VLOOKUP, HLOOKUP searches for data that satisfies the criteria in the first column (or first row) in the range, and returns the corresponding value based on the specified column number (line number). VLOOKUP the column data by row, hlookup the row data by column lookup

VLOOKUP (lookup_value, table_array, Col_index_num, [range_lookup])

HLOOKUP (lookup_value, table_array, Row_index_num, [range_lookup])

[range_lookup] The default value is 1 or true, and a fuzzy match is used to find it. If set to 0 or false, use exact lookups while supporting unordered lookups.

Example: Find employee name with employee number 2267

VLOOKUP ("2267", A1:d4, 2)

LEN returns the number of characters in a text string

LEN (text)

Substitute replaces the string specified in the target text with a new string

Substitute (text, old_text, New_text, Instance_num)

Example:

Replace the second fox with a tiger

Substitute ("Fox false Fox Wei", "Fox", "Tiger", 2)

Count the number of manual characters in a cell

(LEN (J13)-len (Substitute (J13, "MANUAL",))/len ("MANUAL")

　　

INDEX returns a value based on the row and column number specified in a given range (range reference or array) and returns a cell reference if the source data is a range, or a value in the array if the source range is an array.

INDEX (Reference, Row_num, [Column_num], [Area_num])

INDEX (Array, row_num, [column_num])

Example: extracting from the data table on the left to create a new worksheet

E4:index ($C $: $C $8, ROW (A2) *2-1)

F4:index ($C: $C $8,row (A2))

SMALL takes the specified nth-small value

SMALL (array, K)

Example:

Take the first small value

SMALL ({10,15,20},1.5) equals Smallsmall ({10,15,20},1) with a result of 10

LARGE takes the specified nth-largest value, as opposed to small

Vba

Example:

In one column, fill in the ID number in the other column of the requirement name, the ID number will be duplicated, but in the other othersheet there is already a column B that corresponds to all the ID numbers. The ID number is first blurred and then returned with the cell value.

E17=vaildreq (othersheet! B:B,B17)

VBA code:

FunctionVaildreq (rng1 asRange, S as String) as StringDimArr1DimR as LongR= rng1. End (Xldown). Row-rng1. Row +1Arr1= rng1. Resize (R,1)DimI as Long fori =1  to UBound(ARR1)If InStr(S, Arr1 (i,1)) Then        IfVaildreq =""  ThenVaildreq = ARR1 (i,1)ElseVaildreq =sEnd IfNextEnd Function

When Excel adds VBA code, you save Excel as a macro-enabled workbook (*.xlsm) format that is saved as Excel.

Some formulas and VBA scripts that Excel uses to process data