Bonsoir
Utilise ca c'est génial. Le principe est de donner des noms qui ne font pas référence à une plage mais à une formule.
Cordialement
Excel Magic Consolidator(MagicCons.xls)
by David Hager
Copyright @1999 All Rights Reserved
Feel free to use this technique in your Excel projects, as long as you
include a statement as to the original source.
There are no examples of the formulas referred to in this text in the
working xl file, but you should be able to construct your own, based
on the following information.
1) Basic Instructions
a) What does it do?
It allows the user to write formulas on the consolidation worksheet (called
"Summary" by default) that act on the same cell from every worksheet in the
workbook. The results of the formulas change dynamically as sheets are
added/deleted from the workbook. Also, the summary sheet can be located at
any position within the workbook. There is no VBA or xlm macro code used
in this solution. All of the work is done by defined name formulas.
b) Writing the formulas
As an example, if you type the formula =SUM(cCell) in cell B4 on the Summary
worksheet, that formula will return the sum of cell B4 for every worksheet
in the workbook, since cCell as used in cell B4 returns the array of entries
for those worksheets. Information about using arrays that return entries
from cells offset from the cells they are used in can be found in 2a.
c) Changing the consolidation sheet name
To change the consolidation sheet name, go to Insert, Name, Define in the
menu. The named formula called TheSummarySheetName is defined as ="Summary".
This means that the worksheet named "Summary" is the only sheet in the
workbook that can be used with the consolidation formulas. If, for example,
you want change the name to "ConsSheet", then you need to define
TheSummarySheetName as ="ConsSheet". Of course, you must have a worksheet
by that name as well.
d) Exporting to an existing workbook
To export this functionality to another workbook, you need to use the Move
or Copy menu item from the popup menu that is available when you right-click
a worksheet tab. In this case, right-click the Summary tab (or whatever
name you may have changed it to). Then, select the desired workbook and
sheet location from the dialog box and the checkbox named "Create a copy"
and press Enter. All of the defined name formulas will copy over to the
new workbook (and of course it is not necessary for your workbook to be
named MagicCons.xls). Note that a new workbook must first be saved for this
technique to work.
2) How does it work?
a) Understanding the formulas
All of the formulas used to create the consolidation are defined name
formulas. You can view them by selecting Insert, Name, Define from the
menu. Do not change these formulas unless you understand how they work.
There are 4 constants defined for use in the z-relative formulas. By default,
the defined name formulas down, left, right and up have been assigned a
value of 1.
TheSummarySheetName is defined as:
="Summary"
This is a defined name formula that sets the name of the worksheet to be
used as the consolidation worksheet.
ThisSheet is defined as:
=LEFT(GET.DOCUMENT(1),FIND("]",GET.DOCUMENT(1)))&TheSummarySheetName
This formula returns the sheet name of the consolidation worksheet in the
form "[MagicCons.xls]Summary". This string will be different if used in
another workbook and/or with a different consolidation worksheet. This
string will be used to match the same string in the TheSheets formula.
TheSheets is defined as:
=IF(GET.WORKBOOK(1)=ThisSheet,"",GET.WORKBOOK(1))
The GET.WORKBOOK(1) xlm macro function returns an array of names for the
worksheets in the workbook. This formula modifies that array to return an
array with an empty string for the array item corresponding to the
consolidation worksheet. NOTE: You can modify this formula to exclude
worksheets other than the "Summary" sheet (if you know how <g>).
cCell is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()))))
The concatenated string in the formula INDIRECT(TheSheets&"!"&ADDRESS(ROW()
,COLUMN())) creates an array of cell addresses for the cell in which the
formula resides all of the worksheets in the workbook. The worksheet cell
address for the position on the consolidation worksheet is constructed
incorrectly by design so that a circular reference to that cell will not
be created. When that string is acted on by the INDIRECT function, a 3-D or
z-range is created. Due to a glitch in how Excel returns this array, it
must be acted on by the N function to produce a true array.
cCellDown is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()+down,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()+down,COLUMN()))))
cCellLeft is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()-left)))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()-left))))
cCellRight is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()+right)))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN()+right))))
cCellUp is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-up,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-up,COLUMN()))))
Realize that in order to use offset arrays of differing dimensions, you will
have to define you own hard-coded formulas, such as:
cCellUp4 is defined as:
=IF(ISERROR(N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-4,COLUMN())))),"",
N(INDIRECT(TheSheets&"!"&ADDRESS(ROW()-4,COLUMN()))))
b) Using arrays with "non-3D enabled" Excel functions
There are quite a few Excel functions that do not work with the 3D ranges
that are inherent to Excel. For example, the MATCH function cannot be as
shown in the following formula.
=MATCH(2, Sheet1:Sheet7!C1, 0)
However, this formula does work as expected.
=MATCH(2, cCell, 0)
In the former case, the 3D range reference Sheet1:Sheet7!C1 does not give
an array that the MATCH function can operate on. The latter case contains
the readable array cCell (which can be viewed by evaluating that portion
of the formula in the formula bar) that MATCH does work with.
c) Z-relative array formulas
Since real arrays are returned by cCell and its cousins, they can be used
just like any normal range is used in an array formula.
3) Problems
a) Circular references
If you try to use the consolidation formulas on any other worksheet than
the designated consolidation sheet, a circular reference will be created.
Do not use these formulas on other worksheets!
b) Sheets other than worksheets
The presence of charts and Excel5 dialog sheets do not interfere with the
workings of the consolidation formulas. However, an Excel4 macro sheet
will behave as if was a regular worksheet. This should not cause a problem
in most cases, but if you have entries in cells that correspond to the
cell ranges you have chosen for consolidation, they will be used in the
formulas.
c) "Incorrect" result from formulas
The z-relative arrays contain the same number of items as the number of
worksheets in your workbook, and that includes the consolidation worksheet.
As such, the COUNTA function will always return that number when used with
the cCell (and similar) arrays. The value zero is returned from empty cells
and so the COUNT function will count those cells. For the same reason, the
SMALL, AVERAGE AND MIN functions may not return the expected answer. Thus,
it is recommended that these functions not be used in the consolidation
formulas, unless you are sure that each worksheet for a specified cell
contains an entry.
d) Only returns values
These formulas have been constructed to return only arrays of values. This
was done by design, since consolidation is performed on numbers. All text
entries are converted to zero. However, if you would prefer a solution
that does include text entries in the arrays, follow these steps:
Define nCell as =N(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))
Define tCell as =T(INDIRECT(TheSheets&"!"&ADDRESS(ROW(),COLUMN())))
Define cCell as =IF(ISERROR(nCell),"",IF(tCell<>"",tCell,nCell))
Of course you would need to do this for the offset arrays as well. I leave
that as an exercise to the reader.
