文档介绍：The great power of Excel resides in its functions and the formulas you can create with
them to develop workbooks, reports, analysis and database interfaces. Personally, I use
Excel to develop reports even if the data are in an Access database. The time and cost
of development are substantially reduced and maintenance of the applications are
made easy. Most important, the users can modify these reports and applications by
themselves or with the help of colleagues with whom they can share their expertise. It
is the ultimate in Learning Enterprise.
Here are the functions I use: AVERAGE, CELL, COUNT, CONCATENATE DATE,
DAY, IF, INDEX, ISERROR, ISNA, INT, LEFT, LEN, MATCH, MID,
MOD, MONTH, NOW, RIGHT, ROUND, SUBTOTAL, SUM, TODAY, TRUNC,
TEXT, VALUE, YEAR, +, -, /, *, &
As unbelievable as it may appear, the 13 principal functions and the secondary ones
presented above allow me to solve almost every problem I can encounter as a
Database Interface Developer.
For example, the following ONE formula (developed with my colleague Éric
Charbonneau from Bell Helicopter /Textron) uses 8 functions and named ranges :
=IF(D8<CurrentYear,0,IF(ISNA(INDEX(rangeHRS,MATCH("wip",rangeColHRS,0),
MATCH(" "&TEXT(D8;0)&"G ",rangeRowHRS;0))),0,(INDEX(rangeHRS,
MATCH("wip",rangeColHRS,0),MATCH(" "&TEXT(D8,0)&"G ",rangeRowHRS
,0))*D11/(1+D10)))+C72)+IF(D8=CurrentYear,IF(ESTNA(INDEX(
rangeHRS,MATCH("wip",rangeColHRS,0),MATCH(" "&TEXT(D8,0)&"Y
",rangeRowHRS,0))),0,(INDEX(rangeHRS,MATCH("wip",rangeColHRS,0),MATCH("
"&TEXT(D8,0)&"Y ",rangeRowHRS,0))*D11/(1+D10)-C32)),0)
Here are some notes
Try to develop a single formula that you will copy/paste in an entire table (It is the
way I usually do it). To do so, you have to e good at using relative or absolute
references. Sometimes you need a relative reference (B4) sometimes you need an
absolute reference (B$4$) and sometimes you need something in between (B$4, B4$).
Click on the reference in the fo