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 computations, 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 Excel 97 to Excel 2007.

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: www.raqsoft.com/product-esproc

## 没有评论:

## 发表评论