Các hàm trong excel

INDEX

Holiday booking price list.

People
Weeks 1 2 3 4
1 500.00 300.00 250.00 200.00
2 600.00 400.00 300.00 250.00
3 700.00 500.00 350.00 300.00

How many weeks required : 2
How many people in the party : 4

Cost per person is : 250 " =INDEX(D7:G9,G11,G12)"

What Does It Do ?
This function picks a value from a range of data by looking down a specified number
of rows and then across a specified number of columns.
"It can be used with a single block of data, or non-continuos blocks."

Syntax
There are various forms of syntax for this function.

Syntax 1
"=INDEX(RangeToLookIn,Coordinate)"
This is used when the RangeToLookIn is either a single column or row.
The Co-ordinate indicates how far down or across to look when picking the data from the range.
"Both of the examples below use the same syntax, but the Co-ordinate refers to a row when"
the range is vertical and a column when the range is horizontal.

Colours
Red
Green
Blue Size Large Medium Small

"Type either 1, 2 or 3 : " 2 "Type either 1, 2 or 3 : " 2
The colour is : Green The size is : Medium
" =INDEX(D32:D34,D36)" " =INDEX(G34:I34,H36)"

Syntax 2
"=INDEX(RangeToLookIn,RowCoordinate,ColumnColumnCordinate)"
This syntax is used when the range is made up of rows and columns.

Country Currency Population Capitol
England Sterling 50 M London
France Franc 40 M Paris
Germany DM 60 M Bonn
Spain Peseta 30 M Barcelona

"Type 1,2,3 or 4 for the country : " 2
"Type 1,2 or 3 for statistics : " 3

The result is : Paris " =INDEX(D45:F48,F50,F51)"


Syntax 3
"=INDEX(NamedRangeToLookIn,RowCoordinate,ColumnColumnCordinate,AreaToPickFrom)"
Using this syntax the range to look in can be made up of multiple areas.
The easiest way to refer to these areas is to select them and give them a single name.

The AreaToPickFrom indicates which of the multiple areas should be used.

In the following example the figures for North and South have been named as one
range called NorthAndSouth.

NORTH Qtr1 Qtr2 Qtr3 Qtr4
Bricks " 1,000.00 " " 2,000.00 " " 3,000.00 " " 4,000.00 "
Wood " 5,000.00 " " 6,000.00 " " 7,000.00 " " 8,000.00 "
Glass " 9,000.00 " " 10,000.00 " " 11,000.00 " " 12,000.00 "

SOUTH Qtr1 Qtr2 Qtr3 Qtr4
Bricks " 1,500.00 " " 2,500.00 " " 3,500.00 " " 4,500.00 "
Wood " 5,500.00 " " 6,500.00 " " 7,500.00 " " 8,500.00 "
Glass " 9,500.00 " " 10,500.00 " " 11,500.00 " " 12,500.00 "

"Type 1, 2 or 3 for the product : " 1
"Type 1, 2, 3 or 4 for the Qtr : " 3
Type 1 for North or 2 for South : 2

The result is : 3500 " =INDEX(NorthAndSouth,F76,F77,F78)"



Example
This is an extended version of the previous example.
It allows the names of products and the quarters to be entered.
The =MATCH() function is used to find the row and column positions of the names entered.
These positions are then used by the =INDEX() function to look for the data.

EAST Qtr1 Qtr2 Qtr3 Qtr4
Bricks " 1,000.00 " " 2,000.00 " " 3,000.00 " " 4,000.00 "
Wood " 5,000.00 " " 6,000.00 " " 7,000.00 " " 8,000.00 "
Glass " 9,000.00 " " 10,000.00 " " 11,000.00 " " 12,000.00 "

WEST Qtr1 Qtr2 Qtr3 Qtr4
Bricks " 1,500.00 " " 2,500.00 " " 3,500.00 " " 4,500.00 "
Wood " 5,500.00 " " 6,500.00 " " 7,500.00 " " 8,500.00 "
Glass " 9,500.00 " " 10,500.00 " " 11,500.00 " " 12,500.00 "

"Type 1, 2 or 3 for the product : " wood
"Type 1, 2, 3 or 4 for the Qtr : " qtr2
Type 1 for North or 2 for South : west

The result is : 6500

" =INDEX(EastAndWest,MATCH(F100,C91:C93,0),MATCH(F101,D90:G90,0),IF(F102=C90,1,IF(F102=C95,2)))"