一句话：首次大规模揭示电子表格嵌套 IF 公式的普遍性，并提出基于 AST 去冗余与内置函数重组的自动重构方法。
电子表格是流行的最终用户编程环境，公式中广泛使用的嵌套 IF 表达式可读性差、认知成本高，且在复用和维护中易出错。由于最终用户通常缺乏编程语言知识，难以发现或解决该问题，此前也没有自动方法。本文首次基于两个包含逾 8 万份工业级电子表格的语料库调查嵌套 IF 使用情况，发现其出乎意料地普遍。随后提出自动重构方法：先根据公式 AST 检测并删除逻辑冗余，再识别由碎片化、分散语法表示的高层语义，并用简洁内置函数重组语法。对超过 2800 万个嵌套 IF 公式的评估表明，该方法能缓解 90% 以上公式的坏味。
Spreadsheets are the most popular end-user programming software, where formulae act like programs and also have smells. One well recognized smell is the use of nested-IF expressions, which have low readability and high cognitive cost for users, and are error-prone during reuse or maintenance. End users usually lack essential programming language knowledge to tackle or even realize this problem, yet no automatic approaches are currently available. This paper proposes the first exploration of the nest-if usage status against two large-scale spreadsheet corpora containing over 80,000 industry-level spreadsheets. It turns out the use of nested-IF expressions are surprisingly common among end users. We then present an approach to tackling this problem through automatic formula refactoring. The general idea of the automatic approach is two-fold. First, we detect and remove logic redundancy based on the AST of a formula. Second, we identify higher-level semantics that have been represented with fragmented and scattered syntax, and reassemble the syntax using concise built-in functions. A comprehensive evaluation with over 28 million nested-IF formulae reveals that the approach is able to relieve the smell of over 90% of nested-IF formulae.
DOI 原文 ·
@inproceedings{paperbot4645,
title = {Automated refactoring of nested-IF formulae in spreadsheets},
author = {Jie Zhang and Shi Han and Dan Hao and Lu Zhang and Dongmei Zhang},
booktitle = {FSE},
year = {2018},
doi = {10.1145/3236024.3275532}
}