Advance Excel
description
Transcript of Advance Excel
Slide 1
Advance Excel1Function IFFunction IF
logical_test value_if_true value_if_false
Function if Microsoft Excel 2007
3
4FunctionFunction Sum Sumif Sumproduct
5< ,,,> , = ... = ,, ,,
6SumIFSumIF
range criteria rangeSum_range
Function Sumif Microsoft Excel 2007
8SumproductSumproduct 2
array1 1array2 2array3 3
9FunctionSumproduct Microsoft Excel 2007
10
11FunctionFunction (Counting) COUNT,COUNTA,COUNTBLANK COUNTIF
12CountIF
range criteria range COUNTA COUNTBLANK
CountIF
13 10000Function Conutif Microsoft Excel 2007
14
15Subtotal SUBTOTAL Subtotal16
Function_num 1 11 () 101 111 () Ref1, ref2 1 254 17 Subtotal Microsoft Excel 2007
18 Ribbon Data
(
19 4 1,2,3 4 20 Data Subtotals At Each Change in Use Function Sum, Average,Max, Min Add Subtotals to 21- Replace Current Subtotals : - Page Break Between Groups : - Summary Below Data : - 222- 1- 3 +7. Data Subtotals Remove All
23FunctionFunction (LOOKUP) VLOOKUP, HLOOKUP INDEX
24Vlookup ( ) Argument VLOOKUP V Verticle VLOOKUP Vlookup25
- Lookup_value (Non-case-sensitive)- Table_array Range Name Lookup_value Table_array- Col_index_num (Table_array) - Range_lookup TRUE FALSE 0 )
26 VLOOKUP (Exact Match) Vlookup Microsoft Excel 2007
27 Range_lookup TRUE - VLOOKUP - VLOOKUP - VLOOKUP (Approximate Match)28 VLOOKUP (Approximate Match) Vlookup Microsoft Excel 2007
29Hlookup ( ) Argument HLOOKUP (H Horizontal) VLOOKUP HLOOKUP ( VLOOKUP )Hlookup30
- Lookup_value (Non-case-sensitive)- Table_array Range Name Lookup_value Table_array- Row_index_num (Table_array) - Range_lookup TRUE FALSE 0
31 HLOOKUP() Hlookup Microsoft Excel 2007
32 HLOOKUP() Hlookup Microsoft Excel 2007
33 INDEX (: )
Index34
- Array row_num column_num row_num column_num INDEX - Row_num row_num column_num- Column_num column_num row_num
35 Index(Array) Index Microsoft Excel 2007
36Function Microsoft Excel Function PMT
37
- rate - nper - pv - fv 0 - type 0 () 1() 0 PMT
38 PMT Microsoft Excel 2007
39FV (Future value ) FV FV40
- Rate - Nper- Pmt pmt pmt pv- Pv pv 0 () pmt- Type 0 1 type 0
41 FV Microsoft Excel 2007
42 NPER43
- Rate Pmt pmt - Pv Fv fv 0 ( 0)- Type 0 1
44 NPER Microsoft Excel 2007
45PV ( present value ) : Pv46
- Rate 10 10%/12 0.83% 10%/12 0.83% 0.0083 - Nper 4*12 ( 48) 48 nper - Pmt
47
pmt $10,000 12 $263.33 -263.33 pmt pmt fv - Fv fv 0 ( 0) $50,000 18 $50,000 fv pmt
48 PV Microsoft Excel 2007
49 RATE 0 RATE 0.0000001 20 RATE #NUM! Rate50
- Nper - Pmt pmt pmt fv - Pv Fv fv 0 ( 0)- Guess guess 10% RATE guess RATE guess 0 1
51 RATE Microsoft Excel 2007
52535455565758596061