Excel is the most widely-used spreadsheet tool. The nontechnical persons love to use it for computation and analysis
though, they usually find the formulas and functions available in Excel are
rather poor and the VBA is just double Dutch to them for further
analysis. Thus, a huge volume of data with valuable information has been wasted
in vain.
esProc is introduced to better the
situation. Empowered esProc users can tap the utmost value of Excel by taking the
esProc advantages of powerful computation ability, agile and easy-to-use
analysis style, and programmed running mode.
I Case and Comparison
Description
In an advisement agency, a Sales
Director receives Client Reports from eight Regional Account Managers by every
quarter. The Client Report is an Excel spreadsheet, mainly comprising the
client, sales value, and other information about the respective region, as
given in the below figure:
Suppose that the Sales Director wants to
compare the big client across various regions. For example, regarding the
client of whom the sales value ranks top 10%, 20% or 30%, what’s the average
sales, and which enterprise is among the Top 500?
First, let’s have a look at the attempt to
solve it with Excel formulas.
Excel Formula Solution
The first step is to compute the average
sales of clients whose sales values rank top 10%. We may adopt the following
procedure: firstly, sort the sales value in descending order, and then use
count( ) function to compute the total number of clients. Secondly, multiple
the total number by 10%, and round the result to get the row number with the round
( ) function. Finally, copy these clients onto a new spreadsheet, and compute
the average value. This procedure is not difficult for
those who are familiar with Excel.
Then, let’s proceed with this computation:
How many of these big clients are among Top 500? To solve the problem, you need
to get the intersection of the two datasets. In other words, this is to compute
the common part of big client set and the Top 500 list from the previous step.
The computational expression is:
=INDEX(A:A,SMALL(IF(COUNTIF($B$2:$B$15,$A$2:$A$20),ROW($A$2:$A$20),4^8),ROW(A1)))&""
Since the above formula requires 5 various combinations
of functions, it is a great challenge to compose it.
To make it worse, the computational
procedure of Excel requires the user to carry out manually and only acceptable for
the specific Excel spreadsheet. This is not as universal as a program, for
example, if program, the whole computational procedure will rerun automatically
on receiving different file names. In this case, there are 8 Excel files. Excel
users will have to run the computational procedures for 8 times. Moreover,
there are 3 rankings: 10%, 20%, and 30%, which means the computation will have
to be repeated for 8X3=24 times.
It is obvious that it is too tough to solve
this problem with Excel formula.
Let’s try VBA, the most powerful extension
tool of Excel.
Excel VBA Solution
Function
Collection(a As Range, b As Range)
On
Error Resume Next
Dim arr1(), arr2(), times, tmpindex
Set newcoll =
CreateObject("Scripting.Dictionary")
With Application.WorksheetFunction
arr1 = .Transpose(a.Value)
arr2 = .Transpose(b.Value)
Do
times = .Mode(arr1, arr2)
If IsEmpty(times) Then
Exit Do
Else
newcoll.Add times, Empty
tmpindex = .Match(times, arr1,
0)
arr1(tmpindex) =
arr1(UBound(arr1))
If UBound(arr1) = 1 Then
arr1(1) = Empty
Else
ReDim Preserve arr1(1 To
UBound(arr1) - 1)
End If
tmpindex = .Match(times, arr2,
0)
arr2(tmpindex) =
arr2(UBound(arr2))
If UBound(arr2) = 1 Then
arr2(1) = Empty
Else
ReDim Preserve arr2(1 To
UBound(arr2) - 1)
End If
times = Empty
End If
Loop
End With
arr3 = newcoll.keys
If newcoll.Count = 0 Then
Collection
= False
Else
Collection
= arr3
End If
End
Function
Isn't it unreadable and indigestible? The
above “double Dutch” is only one step to compute the intersection set of
several steps. Undoubtedly, VBA needs a great programming capability and is by
no means suitable for nontechnical persons.
Then, let’s check the impressive esProc
solution below.
esProc
Solution
A1 and A6: Retrieve the “Client Report” of a certain region respectively and “Top 500 list”.
Please note that “rangeFile”
is a parameter, and you can assign various file names
to get various results. In addition, the “percent” in
the B2 is also a parameter, for example, 10%, 20%, and 30%.
A2: Sort the data in A1 by sales value. The
”amount” is column name retrieved automatically, and the ”-1” represents
the descending order.
B2: Compute the row number of clients
ranking the top 10%, 20% or 30% respectively.
A3: Compute the clients from row 1 to B2
that are all big clients. Assume that B2 equals to 3, then “to(B2)” equals to ”1,2,3”.
A4: Compute the
average sales value of big clients
A7: Compute the clients which not only big
clients but also among the Top 500. In other words, this is to compute the
intersection set of the customer column from the Client Report and the 500Name
column from the Top 500. The ”^” represents the intersecting action.
A8: Compute the number of clients in the
intersection set from the previous step.
As we can see, the style of esProc
expression is similar to that of Excel, agile and intuitive but more powerful
in computing and capable to rerun just as a program does. It is a great analysis
tool to empower the nontechnical persons who are familiar the style of Excel.
Perfect! esProc is just the best tool to
solve such problems.
II Features Fit for Excel
Better Usability
esProc provides an operation interface of “cellset”
style with the letter as column name and number as row no. The cells can be
mutually referenced with cell name. Such style is quite friendly to people who
are familiar with Excel.
The cellset allows the business analyst to work
from the business perspective, process and analyze the data intuitively.
Therefore, esProc demands little on technical capability from users, and thus
ideal for business person with no technical background.
esProc can be installed on the normal PC
with common OS , and run in a environment similar to that of Excel.
Strong Analysis Ability
As a tool specially designed to handle
massive data computation, esProc has all capability of SQL statements and
senior languages. On one hand, esProc can be used to query, filter, group, and
collect statistics, just like SQL statements; On the other hand, it can be used
in the loop and branch judgment for the procedure analysis, just like VBA.
In the practical use, esProc over-performs
the SQL and senior languages, thanks should go to the below advantages: esProc
users will never face the dilemma of lengthy and unreadable SQL statements and
the poor computability of senior languages. Even the nontechnical person can
also resort to esProc to complete the complex analysis computation all by
themselves.
Programmed Running Mode
esProc has special optimizations for Excel,
providing the easy-to-use functions for reading from or writing back to Excel
spreadsheets of various versions from Excel97 to Excel2007.
In a programmed running mode, esProc users
can analyze various Excel spreadsheets according to various parameters, which
is ideal for the repetitive computation. It is indeed a timesaving and
effort-saving analysis tool.
III Significance and Value to
Excel
esProc is a powerful analysis tool for
Excel, and particularly suits the need of nontechnical persons to implement
complex computational analysis on data from Excel spreadsheets.
esProc facilitates the data mining on Excel
with the convenience and power for all people to deliver and ensure the
valuable data will truly support the decision-making of enterprises.
About esProc: http://www.raqsoft.com/product-esproc
No comments:
Post a Comment