Excel Hacks Ebook [Electronic resources] نسخه متنی

Hack 71 Make the Excel Subtotal Function Dynamic

Although SUBTOTAL is one of
Excel's most convenient functions, you sometimes
want to choose the function it uses, or apply it to data that can
expand and contract.

You
use the SUBTOTAL function in Excel to perform a
specified function on a range of cells that have had AutoFilters
applied to them. When the AutoFilter has been applied, the
SUBTOTAL function will use only the visible cells;
all hidden rows are ignored. The operation it performs depends solely
on the number (between 1 and 11) that you supply to its first
argument, Function_num. For example:

=SUBTOTAL(1,A1:A100)

will average all visible cells in the range A1:A100 after AutoFilters
have been applied. If all rows in A1:A100 are visible, it will simply
average them all and give the same result as:

=AVERAGE(A1:A100)

The number for the first SUBTOTAL argument,
Function_num, and its corresponding functions are
as shown in Table 6-1.

Because you
need to use only a number between 1 and 11, you can have one
SUBTOTAL function perform whatever function you
choose. You even can choose from a drop-down list that resides in any
cell. Here is how to do this.

Add all the function names, in the same order as in Table 6-1, to a range of cells. For this example, we
will use D1:D11. With this range selected, click the Name box (the
white box on the left of the Formula bar) and type the name
Subs. Then click
Enter.

Select column D in its entirety
and then select Format Column Hide. Now select
View Toolbars Forms, click the ComboBox
control, and then click cell C2.

Use the size handles to size the ComboBox so that it can display the
longest function namei.e., AVERAGE.

To have your ComboBox automatically snap to the size of the column and row it resides in, hold down your Alt key at the same time as you size the ComboBox.

Right-click the ComboBox and choose
Format Control, then the Control tab. In the Input range, type
Subs. In the Cell-Link box, type
$C$2. Now change the drop-down lines to
11. In cell C3, enter this formula:

=IF($C$2=",","Result of "&INDEX(Subs,$C$2))

In cell C4, enter this formula:

=IF($C$2=",",SUBTOTAL($C$2,$A$4:$A$100))

where $A$4:$A$100 is the range on which the
SUBTOTAL should act.

Now all you need to do is select the required
SUBTOTAL function from the ComboBox and the
correct result will be displayed, as shown in Figure 6-15.

Figure 6-15. An adjustable SUBTOTAL