Use Of DGET
This is the Database range.
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
|
Unit Cost
|
Box Quantity
|
Boxes In Stock
|
Value Of Stock
|
Bulb
|
200
|
3000
|
Horizon
|
£4.50
|
4
|
3
|
£54.00
|
Neon
|
100
|
2000
|
Horizon
|
£2.00
|
15
|
2
|
£60.00
|
Spot
|
60
|
£0.00
| |||||
Other
|
10
|
8000
|
Sunbeam
|
£0.80
|
25
|
6
|
£120.00
|
Bulb
|
80
|
1000
|
Horizon
|
£0.20
|
40
|
3
|
£24.00
|
Spot
|
100
|
unknown
|
Horizon
|
£1.25
|
10
|
4
|
£50.00
|
Spot
|
200
|
3000
|
Horizon
|
£2.50
|
15
|
1
|
£37.50
|
Other
|
25
|
unknown
|
Sunbeam
|
£0.50
|
10
|
3
|
£15.00
|
Bulb
|
200
|
3000
|
Sunbeam
|
£5.00
|
3
|
2
|
£30.00
|
Neon
|
100
|
2000
|
Sunbeam
|
£1.80
|
20
|
5
|
£180.00
|
Bulb
|
100
|
unknown
|
Sunbeam
|
£0.25
|
10
|
5
|
£12.50
|
Bulb
|
10
|
800
|
Horizon
|
£0.20
|
25
|
2
|
£10.00
|
Bulb
|
60
|
1000
|
Sunbeam
|
£0.15
|
25
|
1
|
£3.75
|
Bulb
|
80
|
1000
|
Sunbeam
|
£0.20
|
30
|
2
|
£12.00
|
Bulb
|
100
|
2000
|
Horizon
|
£0.80
|
10
|
5
|
£40.00
|
Bulb
|
40
|
1000
|
Horizon
|
£0.10
|
20
|
5
|
£10.00
|
How many boxes of a particular item do we have in stock?
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
| ||||
Bulb
|
100
|
Horizon
| |||||
The number in stock is :
|
5
|
=DGET(B3:I19,H3,C23:F24)
| |||||
What Does It Do ?
| |||||||
This function examines a list of information and produces one result.
| |||||||
If more than one record matches the criteria the error #NUM is shown.
| |||||||
If no records match the criteria the error #VALUE is shown.
| |||||||
Syntax
| |||||||
=DGET(DatabaseRange,FieldName,CriteriaRange)
| |||||||
The DatabaseRange is the entire list of information you need to examine, including the
| |||||||
field names at the top of the columns.
| |||||||
The FieldName is the name, or cell, of the values to Get, such as "Value Of Stock" or I3.
| |||||||
The CriteriaRange is made up of two types of information.
| |||||||
The first set of information is the name, or names, of the Fields(s) to be used as the basis
| |||||||
for selecting the records, such as the category Brand or Wattage.
| |||||||
The second set of information is the actual record which needs to be selected, such
| |||||||
as Horizon as a brand name, or 100 as the wattage.
| |||||||
Formatting
| |||||||
No special formatting is needed.
| |||||||
Example 1
| |||||||
This example extracts information from just one record.
| |||||||
How many boxes of a particular item do we have in stock?
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
| ||||
Bulb
|
100
|
Horizon
| |||||
The number in stock is :
|
5
|
=DGET(B3:I19,H3,C51:F52)
| |||||
Example 2
| |||||||
This example extracts information from multiple records and therefore shows the #NUM error.
| |||||||
How many boxes of a particular item do we have in stock?
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
| ||||
Bulb
|
100
| ||||||
The number in stock is :
|
#NUM!
|
=DGET(B3:I19,H3,C63:F64)
| |||||
Example 3
| |||||||
This example extracts information from no records and therefore shows the #VALUE error.
| |||||||
How many boxes of a particular item do we have in stock?
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
| ||||
Bulb
|
9999
| ||||||
The number in stock is :
|
#VALUE!
|
=DGET(B3:I19,H3,C64:F65)
| |||||
Example 4
| |||||||
This example uses the =IF() function to display a message when an error occurs.
| |||||||
How many boxes of a particular item do we have in stock?
| |||||||
Product
|
Wattage
|
Life Hours
|
Brand
| ||||
Bulb
|
9999
| ||||||
The number in stock is :
|
#VALUE!
|
=DGET(B3:I19,H3,C85:F86)
| |||||
No such product.
| |||||||
=IF(ISERR(F88),CHOOSE(ERROR.TYPE(F88)/3,"No such product.","Duplicates products found."),"One product found.")
| |||||||
कोई टिप्पणी नहीं:
एक टिप्पणी भेजें