Excel Learning 20200317

wanmen.org

PeifengWANG M$ Lecturer

9

VLOOKUP(value, range, column, option)

value: goal

range: data (goal in the 1st column)

column: return value

option: 0 accurate; 1 no more than.

text-->normal: a*1

normal-->text: a&""

10

Match, Index

match(value, range, option)

value: goal

range: data

option: accurate / at least

index(range, row)

range: data

row: line number

multiple unified form construction: rolling bar + index

11

SUMIF(range, criteria, data)

COUNTIF(range, criteria)

SUMIFS(data, range1, criteria1[, range2, criteria2...])

COUNTIFS(range, criteria1)

multiple criteria VLOOKUP

1. build auxilary column of joint criteria

2. VLOOKUP(range, criteria1&criteria2&..., data)

12

DATE Computation

date + period

year

month

day

date(y,m,d)

date1 - date2

datedif(start date, end date, y/m/d)

approaching date

today() + confitionalformat