Spreadsheets can usually carry out grouping on data easily, but the further computing on grouped data is much more difficult. For example, a data (wine inventory) is after shown as below after group and summary :
Say,
if we are to compute the total amount of each wine or even each type. In the
spreadsheet, it is a bit difficult to carry on these calculations or actions based
on the above: calculate the ”UnitPrice*Stock” amount; sort the data in the
group by inventory level; add an additional level of grouping by Name to
calculate the inventory of the same type of wine; calculate the average unit
price of each type of wine; and even highlight the subtotal section such as the
F4 and F10.
To
calculate the resulting amount of ”UnitPrice*Stock”, a new column G need to be
appended along with an expression of “=E4*F4”, and then drag the expression to
every row of this column. Because UnitPrice is unavailable in the subtotal row,
the formula is invalid there and must be removed for each row manually, which
is error-prone since there are many types of wines.
To
sort the data in a group by the inventory level, you can only sort manually on
each group of data since the data is allocated to several irrelevant groups. For
example, the 1st group of data is from the row 4~9. It is error-prone if the
data volume is huge. Another solution is to ungroup and present the data in a
2D sheet without subtotal, sort by Type and Stock, and then summarize in groups.
In this solution, if there is any data in the subtotal row like “proportion of
subtotal to total”, then users will have to recalculate these data once the row
of subtotal disappears in the procedure. All in all, both solutions suffer from
obvious drawbacks.
Add
another level of grouping by Name to calculate the inventory of a same type of
wine. Because the data is not sorted by Name, the data cannot be grouped
directly and we have to go back to solve the previous problem.
To
calculate the average unit price of each type of wine, both the 2 solutions
available are quite cumbersome. The first solution is to ungroup and paste the
data into 2 pieces, either of which will respectively go through the average
unit price calculation or the inventory summary calculation, then join the
summary values of these 2 pieces of data. The second solution is to write the formula
in every subtotal section, for example “=SUBTOTAL(1,E4:E9)” in E3. Since the
data in various groups differ, users will have to write N different formulas
again and again and errors may quite easily occur.
To
highlight the subtotal section, users have no choice but to repeat the simple
action on each group one after another manually. Without any means to automate,
it is so sarcastic when considering the Office Automation.
In
facts, the above problem can be solved with esCalc. esCalc introduces the
concept of homocell to present
the business relations between cells so as to enable the auto-processing on
data of various business senses. For example, if inputting the formula =E4*F4
in G4 for once, then the formula will be pasted to all rows of the details data
except for the summary row, as shown in the below sheet:
The
data in the group is sorted by the inventory level. In the same way, just sort
once in the F4, F5 or F11 cell, the respective positions will be auto-identified,
and the data in each group will also be auto-sorted, as shown in the below sheet:
As
you know, the esCalc homocell mechanism can solve other problems automatically,
and we will not dwell on it for it’s the topic of another essay.
Related Articles:
A Strong Helper Is Available When Excel
Get a Standalone Alternative for Excel at $0
Truth behind Ticket Purchasing Rush: Statistical Analysis Works
Get a Standalone Alternative for Excel at $0
Truth behind Ticket Purchasing Rush: Statistical Analysis Works
No comments:
Post a Comment