Tap Utmost Value of Excel

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 Significancy 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.
esProc saves the long-stored Excel® from turning into a legacy over time. esProc will tap the utmost value of Excel®.