[论文解读] What We Don't Know About Spreadsheet Errors Today: The Facts, Why We Don't Believe Them, and What We Need to Do

Raymond R. Panko|arXiv (Cornell University)|Feb 2, 2016

Spreadsheets and End-User Computing参考文献 39被引用 12

一句话总结

本文综合实证证据，指出尽管人们对电子表格的准确性普遍过度自信，但大型模型中仍普遍存在错误，且极难被发现。文章主张采用源自软件工程的严格、大规模测试与代码审查实践，以减少错误，强调人类对错误检测的直觉因认知偏见而严重不可靠。

ABSTRACT

Research on spreadsheet errors is substantial, compelling, and unanimous. It has three simple conclusions. The first is that spreadsheet errors are rare on a per-cell basis, but in large programs, at least one incorrect bottom-line value is very likely to be present. The second is that errors are extremely difficult to detect and correct. The third is that spreadsheet developers and corporations are highly overconfident in the accuracy of their spreadsheets. The disconnect between the first two conclusions and the third appears to be due to the way human cognition works. Most importantly, we are aware of very few of the errors we make. In addition, while we are proudly aware of errors that we fix, we have no idea of how many remain, but like Little Jack Horner we are impressed with our ability to ferret out errors. This paper reviews human cognition processes and shows first that humans cannot be error free no matter how hard they try, and second that our intuition about errors and how we can reduce them is based on appallingly bad knowledge. This paper argues that we should reject any prescription for reducing errors that has not been rigorously proven safe and effective. This paper also argues that our biggest need, based on empirical data, is to do massively more testing than we do now. It suggests that the code inspection methodology developed in software development is likely to apply very well to spreadsheet inspection.

研究动机与目标

• 分析人们对电子表格准确性的普遍过度自信与频繁且难以察觉的错误这一现实之间的差距。
• 考察人类认知偏见（尤其是对错误的无意识）如何破坏错误检测与修正的努力。
• 论证当前的错误减少策略因直觉错误和缺乏实证验证而无效。
• 倡导采用源自软件工程的、系统化的大规模测试与代码审查方法，以提高电子表格的可靠性。
• 呼吁摒弃未经证实的错误减少建议，转而采用经过严格验证的方法。

提出的方法

• 回顾现有关于电子表格错误的实证研究，以识别一致且有充分支持的发现。
• 分析与错误检测相关的人类认知过程，特别是元认知错觉和对未察觉错误的无意识。
• 在电子表格开发与传统软件工程之间建立类比，尤其关注代码审查在错误检测中的应用。
• 提出应将系统化、大规模测试（超越临时调试）作为错误减少的主要策略。
• 拒绝未经实证验证的错误减少方法，强调基于证据的实践的必要性。
• 将软件工程中检查流程的原则应用于电子表格开发，以提升可靠性。

实验结果

研究问题

• RQ1 为何电子表格开发者和组织在存在大量错误证据的情况下，仍对电子表格的准确性抱有高度过度自信？
• RQ2 哪些认知机制导致用户无法识别或察觉其创建的大多数电子表格错误？
• RQ3 为何电子表格错误如此难以检测与修正，即使它们导致了错误的最终结果？
• RQ4 哪些基于证据的策略能显著减少电子表格错误？为何当前方法不足？
• RQ5 如何有效将软件工程中的代码审查等实践适配到电子表格开发中以提升质量？

主要发现

• 虽然每个单元格的错误发生率较低，但在大型电子表格中，出现至少一个错误的最终结果的可能性极高。
• 由于认知局限和无意识，即使经过仔细审查，错误也极难被检测和修正。
• 用户对其检测错误的能力过度自信，这种现象由元认知错觉驱动，且无法感知未被察觉的错误。
• 人类对错误检测的直觉从根本上存在缺陷，导致对准确性的高估和对错误普遍性的低估。
• 最迫切的需求是基于实证证据，实施远超当前实践的系统化测试。
• 若将软件工程中的代码审查方法适配到电子表格开发中，其有效性很可能很高。