Day 20：【高階商務計算機】讓靜態表格自己說話——Excel 動態連動公式與自製原生圓餅圖導引

18th鐵人賽

shaoan_1211

2026-09-23 22:10:33

45 瀏覽

分享至

昨天我們實作了離線 SheetJS 試算表匯出引擎、多維度明細結構化展平與行動端原生檔案分享機制，消除了出差報帳逐筆手動謄寫的痛苦摩擦。然而，當我們將產出的檔案正式交付給主管、會計或自身進行財務檢視時，另一個現實缺陷浮現出來：如果試算表只是一份靜態數字清單，使用者無法一眼洞察各類支出的比例分佈；更麻煩的是，若事後發現某筆品項打錯而手動修改明細金額時，所有的統計數據與加總全成了不會自動更新的死數據。

為了讓匯出的試算表具備如同活體資料庫般的自我演算生命力，今天我們實作 Excel 動態連動公式注入架構與原生圓餅圖導引設計。系統在客戶端生成試算表時，不再只是單純填入靜態純文字，而是動態計算欄位映射座標，精準嵌入原生的條件加總公式；同時在工作表右側構築具備自動擴展能力的動態統計面板，並預先鋪設好一鍵繪製原生互動式圓餅圖的幾何定位導引，讓靜態表格真正學會自己思考與說話。

專案正式上線體驗與開源倉庫

線上體驗 Live Demo：https://410355-collab.github.io/Premium-Business-Calculator/
(強烈建議使用 iPhone Safari 或 Android 手機開啟體驗！)

GitHub 開源倉庫：https://github.com/410355-collab/Premium-Business-Calculator
歡迎提出建議與指導
(開發環境：Google Antigravity IDE + Google Gemini 協同開發)

一、技術痛點：靜態數值孤島與重複計算消耗

在行動端工具匯出財務報表的過程中，傳統實作方式普遍存在兩大技術短板：

第一個問題是靜態寫死數值，喪失試算表的試算靈魂。多數匯出模組在產生報表時，僅僅將 JavaScript 當下計算出來的靜態總和寫入儲存格。這意味著一旦會計需要調整某筆採購項目的折扣、或使用者修正了某一餐的實際費用，右側的統計欄位與總支出金額完全不會響應更新。使用者或財務人員往往被迫在試算表軟體中重新手動拉公式，完全失去了電子試算表的核心價值。

第二個問題是欄位動態增減導致的公式座標漂移。在跨國商務的多幣別架構中，明細資料的欄位寬度與數量並非固定：當整張報表包含外國發票時，必須自動插入外文品名與中文翻譯兩大跨國欄位；而在純本幣計算時，這兩欄則完全隱藏。若公式生成器採用寫死的固定欄位代碼，公式在不同情境下便會參照到錯誤的資料列，造成語法報錯或計算數值全面錯位。要達到企業級的精確度，系統必須具備動態座標幾何感知的公式注入引擎。

二、架構設計：動態公式映射與原生圖表導引

為了解決靜態數據與座標錯位的難題，我們構建了多維度動態試算表架構：

動態座標感知的條件加總公式管線：
在資料渲染階段，系統首先全面掃描當前匯出清單中是否包含跨國外幣與雙語品項。若包含外國收據，資料列自動拓展為十一欄，將換算後之台幣金額精準推移至第九欄；若為純本幣開銷，則收縮至九欄，換算金額對齊於第七欄。公式生成器依據此動態基準，自動推導出右側統計欄位的起始錨點，並精確組裝出參照來源資料列的條件加總公式，實現明細修改、統計即刻連動的響應式生命力。

分類項目動態去重與自動展開：
統計面板並非死板地固定四大常規分類，而是動態提取歷史紀錄中所有自訂標籤與生活類別，並自動排除非報銷性質的純商務草稿。每一項分類標籤在統計區塊中佔據一列獨立儲存格，搭配直覺的圖示與中文說明；公式單元則自動填入對應該列標籤的加總指令，使統計面板無論面對單筆紀錄還是龐大雜項，皆能伸縮自如。

原生圖表導引與工作表範圍校正：
為了在不安裝任何龐大第三方圖表外掛的前提下，讓使用者在電腦開啟檔案後能以最低操作成本產出高質感圖表，我們在版面結構上實作了精準的幾何隔離。在明細資料與統計面板之間刻意留出一欄物理空白作為視覺呼吸區；統計面板則緊密將分類名稱與加總金額相鄰排列，完美契合辦公試算表軟體選取資料直接插入圓餅圖的規範，並動態擴展工作表的有效範圍宣告，徹底杜絕畫面裁切與顯示不全。

三、核心代碼精華：script.js

以下為動態欄位座標推導、條件加總公式組裝與活頁簿範圍校正的關鍵實作：

// 建立包含明細與右側分類統計的完整工作表
// 明細欄位定義：
// A:編號, B:日期時間, C:分類, D:項目/品名
// 若無外語欄位：E:計算結果, F:原始幣別, G:換算金額, H:目標幣別, I:稅率狀態 -> 換算金額在 G 欄
// 若有外語欄位：E:外文品名, F:中文翻譯, G:計算結果, H:原始幣別, I:換算金額, J:目標幣別, K:稅率狀態 -> 換算金額在 I 欄
const convertedColLetter = hasForeignItems ? 'I' : 'G';
const defaultCats = ['food', 'shopping', 'transport', 'other'];

// 動態提取不重複分類並排除純商務計算
const allCatKeys = Array.from(new Set([
    ...defaultCats,
    ...flatDetailItems.map(item => item.category || 'other')
])).filter(c => c !== 'business');

// 將明細陣列轉換為工作表對象
const ws = XLSX.utils.aoa_to_sheet([headers, ...rows]);

// 寫入右側分類統計標題與動態公式
// 若無外語欄位，資料用到 I 欄，留白 J 欄，統計表配置於 K 欄與 L 欄
// 若有外語欄位，資料用到 K 欄，留白 L 欄，統計表配置於 M 欄與 N 欄
const catColKey = hasForeignItems ? 'M' : 'K';
const amtColKey = hasForeignItems ? 'N' : 'L';

ws[`${catColKey}1`] = { v: "分類項目", t: 's' };
ws[`${amtColKey}1`] = { v: "加總金額", t: 's' };

// 注入原生 SUMIF 動態連動公式
allCatKeys.forEach((catKey, idx) => {
    const rowNum = idx + 2;
    const catLabel = `${getCategoryEmoji(catKey)} ${getCategoryName(catKey)}`;
    const formula = `SUMIF(C:C, ${catColKey}${rowNum}, ${convertedColLetter}:${convertedColLetter})`;

    ws[`${catColKey}${rowNum}`] = { v: catLabel, t: 's' };
    ws[`${amtColKey}${rowNum}`] = { f: formula, t: 'n' };
});

// 精準更新工作表範圍宣告，防止邊界遭截斷
const maxRow = Math.max(rows.length + 1, allCatKeys.length + 1, 10);
const endColKey = hasForeignItems ? 'N' : 'L';
ws['!ref'] = `A1:${endColKey}${maxRow}`;

// 建立活頁簿並配置欄寬
const wb = XLSX.utils.book_new();
XLSX.utils.book_append_sheet(wb, ws, detailSheetName);

ws['!cols'] = [
    { wch: 6 },
    { wch: 20 },
    { wch: 14 },
    { wch: 26 },
    ...(hasForeignItems ? [{ wch: 26 }, { wch: 26 }] : []),
    { wch: 14 },
    { wch: 10 },
    { wch: 14 },
    { wch: 10 },
    { wch: 12 },
    { wch: 4 },  // 留白隔開欄（J 或 L）
    { wch: 16 }, // 分類項目欄（K 或 M）
    { wch: 16 }  // 加總金額欄（L 或 N）
];

四、深度調校剖析：公式相容性與視覺工程

在實現純前端公式注入與版面導引的過程中，我們克服了多項跨軟體相容性的工程挑戰：

跨軟體公式語法通用性：
在試算表格式規範中，不同辦公軟體（如桌面端微軟辦公套件、蘋果試算軟體與雲端線上試算表）對儲存格型別宣告有極嚴格的要求。我們在注入公式時，明確將資料型別標記為數值型別，並採用全大寫且跨平台全面支援的標準條件加總指令。這保證了使用者無論用何種辦公應用程式開啟，都能直接正確求值，絕不跳出不支援或毀損修復警告。

雙區域並行排版的留白呼吸區：
若將明細清單與統計摘要緊密貼合，視覺上會產生嚴重的混淆，甚至在使用者手動篩選資料列時隱藏了右側的統計結果。我們在資料尾端與統計區塊之間，強制劃分了一道專屬的四字元留白欄位。這道微小的間距在視覺上形成了清晰的模組邊界，使左側明細與右側儀表板各司其職，維持了專業商務報表的大器美感。

相鄰兩欄契合圖表繪製標準：
在各類主流試算表軟體中，要拉出一張標準的圓餅圖，最佳的資料結構就是相鄰的標籤欄與數值欄。我們將分類名稱與動態加總金額設計為連續的雙直欄排列。使用者開啟檔案後，只需用滑鼠框選統計面板區域，並點擊上方插入圓餅圖，軟體即可在零微調的情況下自動識別標籤與百分比切片，省去了傳統手動設定座標軸與資料序列的繁重操作。