Użytkownik "Tom" napisał w wiadomości
...
In Excel, I use subtotals quite often, but there's got to be a way to use
them more efficiently! I like the tool quite a bit, but sometimes what I
need is JUST the subtotals, not the detailed records themselves. I need
to
create a presentation-quality worksheet with only the totals. Collapsing
the
subtotals by nesting them isn't the answer; the worksheet's reviewers don'
t
need / want the detail records, and the presentation of the collapsed view
is
clunky at best.
What I'm doing requires several steps:
1. After subtotaling, copy and Paste Special - Values (replacing the
subtotal formulas with values)
2. Remove the subtotals - Data, Subtotals, Remove All
3. Sort the range so the "subtotals" are grouped together
4. Delete the detail records
5. Insert a column, and create a formula to strip out the word "Total"
from
each Description
6. In the new column, Copy and Paste Special Values to remove that
formula
Delete the old Description column.
Yuck! Too many steps. Is there a better way to accomplish the goal of
taking a subtotaled range and just displaying the totals?
i think that pivot table should resolve your problem or you could use sum.if
function
look at
http://www.contextures.com/tiptech.html
mcg