Empower SQL Ability to Spreadsheet

The spreadsheet can implement the visualized calculation to some extent, and the nontechnical people can perform some rather complex calculations without having to learn the SQL. However, as the core of SQL, the relational query is unable to be implemented through common business spreadsheet software, which adds complexity to the apparently simple problems of multi-table join.

For example, the Finance department needs to calculate the salary, and the relevant data is stored in ”standard sheet”, ” Absence sheet”, and ” performance sheet”, as shown in the below figure:

 If these three sheets can be joined, then you can compute it easily via the standardWages*(1+Evaluation-Absence/40)+Bouns, as shown below:

 

However, the common business spreadsheet software like Excel is usually quite inconvenient for such Union and Join actions. The manual data copying is error-prone, and it will be even more exhausting if the data volume is huge. Considering these factors, composing formula is a great method, for example, in D2, E2, and F2, respectively compose the 3 formulas:

=IFERROR(INDIRECT("'Absence'!"&ADDRESS(MATCH(A2,'Absence'!$A:$A,0),2)),0)

=IFERROR(INDIRECT("'Performance'!"&ADDRESS(MATCH(A2,'Performance'!$A:$A,0),2)),0)

=IFERROR(INDIRECT("'Performance'!"&ADDRESS(MATCH(A2,'Performance'!$A:$A,0),3)),0)

The above-mentioned formula requires the strong technical competence and rich user experience with business spreadsheet software. In fact, the qualified capable people would rather import the data to the database and use a simple statement of “relation query” to solve the problem, because this formula is hard to understand and error-prone.

Isn’t there any better business spreadsheet software or Excel alternative? Actually, we’ve got one – esCalc, a good Excel alternative and an innovative desktop BI tool which is capable for the relation query. To join the Absence sheet with the standard sheet, simply use the Join function, as shown in the below figure:

 

Similarly, you are only allowed to perform the Join action for once for the Performance sheet. The ultimate result is the just what we have expected:

 

        

The “perform for once” even includes the formula to calculate the salary. In G2, just enter the formula for once and the formula will be automatically copied to G3, G4, and other cells sharing the common business sense. We call such cells Homocell.

The Join action is dependent on the homocell to some degree. The advantage of group table at multi-levels is to join the data correctly, even those data at various levels. Similarly, in the grouping table at multi-levels, the formula will be copied and pasted to the homocells. For example, the formula in the summary section will be copied and pasted to other summary section, and the data in the details section will not be affected. Therefore, for the huge amount of workload needing adjust before in the business spreadsheet software has been automated in esCalc, the smart desktop BI tool.