在10月初看到一則科技新聞,說英國公布武漢肺炎(COVID-19)10 月 3 日新增確診 12,872 人,4 日新增確診竄升為 22,961 人,是出現了什麼防疫缺口嗎?不是,據稱是因為作業人員的軟體操作錯誤,漏算了9月底約 16,000筆,所以在10/4補上,造成看似新增確診數破2萬的情況。

究竟是甚麼神奇的失誤? 台灣的科技媒體 ithome – 英國少算逾1.5萬名武漢肺炎確診患者,疑因超過Excel限制 跟當地媒體 The Guardian – Covid: how Excel may have caused loss of 16,000 test results in England,大致都是寫說英國公共衛生部(PHE)負責蒐集來自公立或私有實驗室的每日確診結果,當 PHE 把各實驗室的 CSV 檔匯入 Excel 時,卻把 Excel 撐爆了,然後超過上限的資料就消失了。

上限來自試算表軟體本身,跟 xls 和 xlsx 無關

看新聞長知識,大概就跟郭海皇一樣訝異,如今才知道每次怎麼拉都拉不完的 Excel,其實在各種部份還是有一個上限的。

刃牙道57回 宮本武藏對烈海王
(刃牙道)

做 Excel 相關操作,碰到電腦 lag、當機、Excel 程式沒有回應,或是做匯入匯出 Excel 檔案之類的網頁程式,程式跑到伺服器 Timeout 逾時,以為資料量已經龐大到單機的極限了?殊不知其實距離到達 Excel 的上限都還很遙遠。

這則新聞可能就是碰到了「列數」的限制,找了一下市面上常見的試算表作業軟體,發現各有限制:

軟體欄(column, 直排)上限列(row, 橫排筆數)上限
Microsoft Excel 1997 ~ Excel 200325665,536
Microsoft Excel 2007~ Excel 2013 及更新版本16,3841,048,576
OpenOffice Calc25632, 000
LibreOffice SpreadSheet10241,048,576

所以說,為了節省成本,版本不升級的啊?用免費 Office 的啊?哪一天算錯帳都不知道。
不只欄與列,單個儲存格的字元數、換行數、色彩數、檔名長度、篩選器數量等等也都有上限,不可不慎。

撐爆的 Excel 造成統計失誤,這是軟體 UI 或 UX 設計問題嗎?

各家科技新聞只有提說英國公共衛生部(PHE)把 Excel 撐爆之後,資料儲存後就消失了,這時候腦海中想的是…所以可能英國有一個工作表,在編輯的時候明明有 200多萬列,儲存之後再重新打開,只剩 1,048,576 列?這難道又是軟體 UI 設計問題嗎?

嘗試用 C# 網頁程式實際產生了一個真的有這麼多列的 xlsx 檔,內容沒放什麼資料,用 Windows 和 Mac 上的 Microsoft Excel 軟體開啟,這時候發現每次怎麼拉都拉不完的 Excel,這時候最底下變成灰色了,而且要新增一列時
,也會出現提示訊息:

excel 超出列數限制
(Windows, Office 365 裡面的 Excel 2009 build 13231)

excel 超出列數限制
(Mac 版 Excel 16.42)


(Mac iWork 裡面的 Numbers)

那再試一下新聞中的情況,在 Excel 資料頁籤中操作,導入 CSV 檔案呢?爆表時一樣會有提示,保留一些空行再匯,最多還是只能放到 1,048,576 列。

excel 超出列數限制

會不會是有可能其他新聞沒講的東西,例如就像 007 電影裡面的高科技,或是蘋果 iPad 廣告裡面那些白人和黑人一樣,英國公共衛生部(PHE)都用平板作業之類的?

excel 超出列數限制

先試試 Office365 網頁線上版 Excel,有類似的提示

google sheets

Google Sheets 網頁版最落漆,1,048,576 列的測試檔案才不到 8MB,直接不給開啟!

google sheets

要用複製貼上的,瀏覽器也是會當機,可能要用 Google Apps Script 測試寫入資料才試得出來。

那會不會又有一種情況,程式匯出時有幾千萬筆,但每次編輯完匯回去,只剩 104 萬筆之類的蠢事發生呢?
用 NPOI 套件嘗試產生一個超過列數上限的檔案,竟然直接無法正常產生。

npoi xlsx error

所以開發匯出程式之類的,要記得多寫一個判斷,超出上限的資料,要再建立一個新的工作表(sheet),把多的擺到裡面去?讓一個 xlsx 檔案有好幾個工作表?
不過真有這麼多筆資料,可能要先考慮程式執行跑到逾時的問題。

結論

實驗之後得知,英國 PHE 這個技術問題可能不像表面上看到的這麼單純,更不是有些台灣媒體寫的什麼「檔案太大」,真的把 Excel 撐爆,操作時在軟體介面上應該是看得出來的,除非操作人員看都不看,直接把警告訊息按掉?也有可能推測是使用巨集在跑,出錯了也看不出來。

如果疫情人數想要統計年齡層、性別之類的,用試算表軟體來產生圖表,的確是非常方便。但資料科學界戲稱,用 Excel 就能處理的數據量,不叫大數據,此事件確實是一個好例子。

使用 Excel 之類的試算表軟體管理資料,要注意單個儲存格跟工作表的欄列數等等限制。使用關聯式資料庫的軟體(SQL Server、Access)管理資料,則會注意每種欄位形態的可容納長度,還有查詢時的各種特性,跟 Excel 這種的限制又不一樣了。

寫文章的時候,曾經確診的英國首相和美國總統看起來都好了,但英國確診人數10月中再度大爆發,單日增加 18000~20000 的都有,這次可沒有什麼技術問題造成統計失誤了,究竟是因為有些豁免國家,入境英國不用隔離?還是因為定期普篩,造成人群出門接觸而感染?總之疫情真的可怕。