Avoiding,finding and fixing spreadsheet errors – A survey of automated approaches for spreadsheet QA

Spreadsheet programs can be found everywhere in organizations and they are used for a variety of purposes, including financial calculations, planning, data aggregation and decision making tasks. A number of research surveys have however shown that such programs are particularly prone to errors. Some reasons for the error-proneness of spreadsheets are that spreadsheets are developed by end users and that standard software quality assurance processes are mostly not applied. Correspondingly, during the last two decades, researchers have proposed a number of techniques and automated tools aimed at supporting the end user in the development of error-free spreadsheets. In this paper, we provide a review of the research literature and develop a classification of automated spreadsheet quality assurance (QA) approaches, which range from spreadsheet visualization, static analysis and quality reports, over testing and support to model-based spreadsheet development. Based on this review, we outline possible opportunities for future work in the area of automated spreadsheet QA.     

Spreadsheet development and ‘what-if’ analysis: quantitative versus qualitative errors

Past research has shown that errors are relatively common in all types of spreadsheets. As spreadsheets are used widely by executives in analyzing and supporting their decision making, especially in financial analysis, budgeting and forecasting applications, it is important for spreadsheets to be accurate. Errors undetected in spreadsheets may have undesirable consequences. For example, errors may adversely impact the firm's competitiveness or profitability when the costing of projects is prone to incorrect computation. For this purpose, we investigate the types of errors that may occur even for simple domain-free spreadsheet problems. In addition, we also show that spreadsheet errors are difficult to detect during ‘what-if’ analysis (i.e. when some design parameters are changed) when spreadsheets are not properly designed. The results show that most students do not take due care in designing spreadsheets. It appears that the techniques in teaching spreadsheets should really focus on how to design a comprehensive spreadsheet that is both easy to maintain and debug rather than just demonstrating the many features of spreadsheets.     

The use of spreadsheets in teaching undergraduate mathematics

The use of spreadsheets in teaching undergraduate mathematics is discussed, with examples of specific topics. The range of application includes Calculus, Differential Equations, Probability and Numerical Analysis. The advantages and disadvantages of spreadsheets are discussed.     

Systematic evolution of model-based spreadsheet applications

Using spreadsheets is the preferred method to calculate, display or store anything that fits into a table-like structure. They are often used by end users to create applications, although they have one critical drawback—spreadsheets are very error-prone. Recent research has developed methods to reduce this error-proneness by introducing a new way of object-oriented modeling of spreadsheets before using them. These spreadsheet models, termed ClassSheets, are used to generate concrete spreadsheets on the instance level. By this approach sources of errors are reduced and spreadsheet applications become easier to understand.As usual for almost every other application, requirements on spreadsheets change due to the changing environment. Thus, the problem of evolution of spreadsheets arises. The update and evolution of spreadsheets is the uttermost source of errors that may have severe impact.In this paper, we will introduce a model-based approach to spreadsheet evolution by propagating updates on spreadsheet models (i.e. ClassSheets) to spreadsheets. To this end, update commands for the ClassSheet layer are automatically transformed to those for the spreadsheet layer. We describe spreadsheet model update propagation using a formal framework and present an integrated tool suite that allows the easy creation and safe update of spreadsheet models. The presented approach greatly contributes to the problem of software evolution and maintenance for spreadsheets and thus avoids many errors that might have severe impacts.     

Modeling Spreadsheet Audit: A Rigorous Approach to Automatic Visualization

Computations in spreadsheets are hard to grasp and consequently many errors remain unnoticed. The problem with the hidden errors lies in the invisibility of the structure of calculations. As a result, auditing and visualization tools are required to make spreadsheets easier to comprehend and to make errors easier to detect. This paper presents a theoretical model of spreadsheets and a technique to describe spreadsheet auditing tools. These are then employed to describe and compare various tools. Moreover, two new visualization mechanisms are introduced.The spreadsheet model reflects not only current spreadsheet systems but also the way people actually use spreadsheets. Theoretically, it is impossible to check the correctness of a spreadsheet without a formal definition of its computations, but our hope is to find visualizations that point out parts of spreadsheets that contain anomalies, i.e. potential locations of errors. The model helps us to understand how such anomalies can be defined.     

ENTERPRISE SPREADSHEETS

The more productive use of computing and human resources can increase the usefulness of spreadsheets to the whole organization. One approach is to apply software engineering techniques from the IS programming world to spreadsheet development. The result is spreadsheets that are easy to modify and general enough to be more valuable to the organization.     

Model inference for spreadsheets

Many errors in spreadsheet formulas can be avoided if spreadsheets are built automatically from higher-level models that can encode and enforce consistency constraints in the generated spreadsheets. Employing this strategy for legacy spreadsheets is difficult, because the model has to be reverse engineered from an existing spreadsheet and existing data must be transferred into the new model-generated spreadsheet. We have developed and implemented a technique that automatically infers relational schemas from spreadsheets. This technique uses particularities from the spreadsheet realm to create better schemas. We have evaluated this technique in two ways: first, we have demonstrated its applicability by using it on a set of real-world spreadsheets. Second, we have run an empirical study with users. The study has shown that the results produced by our technique are comparable to the ones developed by experts starting from the same (legacy) spreadsheet data. Although relational schemas are very useful to model data, they do not fit spreadsheets well, as they do not allow expressing layout. Thus, we have also introduced a mapping between relational schemas and ClassSheets. A ClassSheet controls further changes to the spreadsheet and safeguards it against a large class of formula errors. The developed tool is a contribution to spreadsheet (reverse) engineering, because it fills an important gap and allows a promising design method (ClassSheets) to be applied to a huge collection of legacy spreadsheets with minimal effort.     

Measuring the learnability of spreadsheets in inexperienced users and those with previous spreadsheet experience

The issues of 'usability' and 'learnability' are assuming an increasingly important role for both the designers of software and their prospective customers. Objective measures of the interaction between system and user are important for the development of software that is both easy to learn and pleasurable to use. In this study we apply a set of five measures to evaluate users' interactions with spreadsheet software, and to compare two spreadsheet packages. We tested 16 people with no previous experience of spreadsheets and 16 with experience of spreadsheets generally though not of the spreadsheet we gave them. Half were allocated to learn Excel and half to learn Wingz, running on Apple Macintosh computers. A standard task was constructed to assess understanding of the basic concepts involved in the use of • spreadsheets. Users' previous experience of spreadsheet use was the most salient factor in the scores achieved on the task. The brand of spreadsheet had no significant effect on task performance. Implications for designers of software and users of spreadsheet packages are discussed.     

一种基于POI的Web表格生成

互联网技术的飞速发展使得基于Web的应用越来越普遍,同时也更加复杂.基于Web的动态Excel表格就是一个典型的实例.随着开发技术的不断发展,一些开源项目凭借其公开、自由以及免费等众多优势产生了越来越大的影响,很多项目都采用了开源技术.文中介绍了一种开源的基于Java的Excel报表开发组件POI引擎,阐述了其表格显示原理.通过在Web上实现动态Excel表格,给出了一种典型应用,并结合-个实际项目中的例子进行了分析,探索出了一种开发Web动态Excel表格的方法.     

一种基于POI的Web表格生成

互联网技术的飞速发展使得基于Web的应用越来越普遍,同时也更加复杂。基于Web的动态Excel表格就是一个典型的实例。随着开发技术的不断发展,一些开源项目凭借其公开、自由以及免费等众多优势产生了越来越大的影响,很多项目都采用了开源技术。文中介绍了一种开源的基于Java的Excel报表开发组件POI引擎,阐述了其表格显示原理。通过在Web上实现动态Excel表格,给出了一种典型应用,并结合一个实际项目中的例子进行了分析,探索出了一种开发Web动态Excel表格的方法。     

Software Engineering for Spreadsheets

Spreadsheets are popular end-user programming tools. Many people use spreadsheet-computed values to make critical decisions, so spreadsheets must be correct. Proven software engineering principles can assist the construction and maintenance of dependable spreadsheets. However, how can we make this practical for end users? One way is to exploit spreadsheets' idiosyncratic structure to translate software engineering concepts such as type checking and debugging to an end-user programming domain. The simplified computational model and the spatial embedding of formulas, which provides rich contextual information, can simplify these concepts, leading to effective tools for end users.     

Five easy steps to systematic data handling

Without data, you are just another person with an opinion. However, handling or presenting your data poorly can be worse than having no data at all. Collecting and presenting solid data about a problem is one of the most effective ways to convince an audience. Yet it is surprising how often people do not take the time to present their data for maximum effect. Many mistakes can be traced to the widespread use of spreadsheets for data presentation and analysis. Not only does the default spreadsheet formatting make the data hard to read, but by automating the setup and calculation tasks, spreadsheets also can eliminate careful thought about how those tasks should be done. The author considers seven of the most common mistakes he has seen in presentations and in print. He presents five simple steps to follow every time you prepare data for presentation     

一种基于关系数据库的Fix工控系统报表生成方法

本文通过分析当前在Fix工控系统报表制作过程中存在的问题，提出基于关系数据库和Fix Real-TimeODBC SQL接口的报表制作方法，并将这种方法在实践中加以运用，取得了良好的效果。     

UCheck: A spreadsheet type checker for end users

Spreadsheets are widely used, and studies have shown that most end-user spreadsheets contain non-trivial errors. Most of the currently available tools that try to mitigate this problem require varying levels of user intervention. This paper presents a system, called UCheck, that detects errors in spreadsheets automatically. UCheck carries out automatic header and unit inference, and reports unit errors to the users. UCheck is based on two static analyses phases that infer header and unit information for all cells in a spreadsheet.We have tested UCheck on a wide variety of spreadsheets and found that it works accurately and reliably. The system was also used in a continuing education course for high school teachers, conducted through Oregon State University, aimed at making the participants aware of the need for quality control in the creation of spreadsheets.     

VB5.0中图形控件的应用分析

本文介绍了在ＶＢ应用程序中设计动态统计图表的两种方法,一是利用图形控件Ｐｉｎｎａｃｌｃｅ－ＢＰＳ Ｃｒａｐｈ Ｃｏｎｔｒｏｌ,一是利用ＯＬＥ技术链接嵌入电子表格Ｅｘｃｅｌ图表。     

An auditing protocol for spreadsheet models

Errors are prevalent in spreadsheets and can be extremely difficult to find. A number of audits of existing spreadsheets have been reported, but few details have been given about how the audits were performed. We developed and tested a new spreadsheet auditing protocol designed to find errors in operational spreadsheets. Our work showed which auditing procedures, used in what sequence and combination, were most effective across a wide range of spreadsheets. It also provided useful information on the size and complexity of operational spreadsheets, as well as the frequency with which certain types of errors occur.     

Spreadsheet quality assurance: a literature review

Spreadsheets are very common for information processing to support decision making by both professional developers and non-technical end users. Moreover, business intelligence and artificial intelligence are increasingly popular in the industry nowadays, where spreadsheets have been used as, or integrated into, intelligent or expert systems in various application domains. However, it has been repeatedly reported that faults often exist in operational spreadsheets, which could severely compromise the quality of conclusions and decisions based on the spreadsheets. With a view to systematically examining this problem via survey of existing work, we have conducted a comprehensive literature review on the quality issues and related techniques of spreadsheets over a 35.5-year period (from January 1987 to June 2022) for target journals and a 10.5-year period (from January 2012 to June 2022) for target conferences. Among other findings, two major ones are: (a) Spreadsheet quality is best addressed throughout the whole spreadsheet life cycle, rather than just focusing on a few specific stages of the life cycle. (b) Relatively more studies focus on spreadsheet testing and debugging (related to fault detection and removal) when compared with spreadsheet specification, modeling, and design (related to development). As prevention is better than cure, more research should be performed on the early stages of the spreadsheet life cycle. Enlightened by our comprehensive review, we have identified the major research gaps as well as highlighted key research directions for future work in the area.     

Teaching statistics with Excel 2007 and other spreadsheets

This article considers which activities in teaching statistics may be suitable candidates for the application of spreadsheets, and whether spreadsheets in general and Excel 2007 in particular are suitable for these tasks.     

Mutation Operators for Spreadsheets

Based on (1) research into mutation testing for general purpose programming languages, and (2) spreadsheet errors that have been reported in the literature, we have developed a suite of mutation operators for spreadsheets. We present an evaluation of the mutation adequacy of du-adequate test suites generated by a constraint-based automatic test-case generation system we have developed in previous work. The results of the evaluation suggest additional constraints that can be incorporated into the system to target mutation adequacy. In addition to being useful in mutation testing of spreadsheets, the operators can be used in the evaluation of error-detection tools and also for seeding spreadsheets with errors for empirical studies. We describe two case studies where the suite of mutation operators helped us carry out such empirical evaluations. The main contribution of this paper is a suite of mutation operators for spreadsheets that can be used for carrying out empirical evaluations of spreadsheet tools to indicate ways in which the tools can be improved.