XLSX 建立、編輯與分析
| 任務 | 方法 |
|---|---|
| 建立 或 編輯(含公式/格式) | openpyxl — 請參閱下方常見陷阱 |
| 大量資料 匯入/匯出 | pandas(read_excel、to_excel) |
| 快速檢視 工作表 | markitdown file.xlsx — 每個工作表顯示 ## SheetName;也可讀取 .xlsm。不包含儲存格座標,因此不應以此規劃編輯內容 |
| 讀取模型(公式 與 值) | 兩次 load_workbook 傳遞 — 請參閱常見陷阱 |
openpyxl、pandas與markitdown已預先安裝 — 無需執行pip install;直接撰寫腳本並匯入即可。僅在匯入失敗(或markitdown指令不存在)時:執行pip install安裝缺失套件。
以下腳本路徑為相對於此技能目錄。
每個輸出檔案的要求
- 專業字型(Arial、Times New Roman)貫穿全文,除非使用者另有指定。
- 零公式錯誤。 絕不可在
recalc.py回報errors_found時交付檔案。若你認為錯誤在之前就已存在,請證明:使用data_only=True載入 原始 檔案,並查看該儲存格。你所引入的錯誤與繼承而來的錯誤在外觀上完全相同。 - 使用公式,絕不使用硬編碼結果。 請撰寫
sheet['B10'] = '=SUM(B2:B9)',而非 Python 計算出的總和。該工作表必須能在輸入值變更時重新計算。 - 嚴格遵循使用者規格說明。 分頁名稱、欄位標題與使用者指定的公式都必須完全一致。任何重新設計但計算結果不同的做法,無論多精巧,都視為失敗。
- 將每個假設與硬編碼數值記錄在讀者可見的位置 — 儲存格註解,或表格結尾的相鄰儲存格。若有實際來源,請標明(
來源:Company 10-K,FY2024,第 45 頁,收入附註,[SEC EDGAR URL]);若數字來自使用者,請直接說明。 - 一個你為讓他人填寫而建立的活頁簿 需要一個簡短圖例,說明哪些儲存格可供編輯,並提供一個包含實際數值的示範列,展示預期格式。絕不可在你被要求編輯的檔案中新增此類示範列。
- 編輯現有檔案:完全遵循其既有慣例。 這些慣例凌駕於此處的所有指南。請先找出其指定的輸入儲存格 — 通常具有獨特的字型顏色、填滿色彩或底紋標記 — 僅在該處編輯,並保留所有既有的公式不變。
重新計算(若檔案包含公式,則為強制步驟)
openpyxl 將公式以字串形式寫入,不包含快取值。在你重新計算之前,任何讀取快取值的方式 — pandas、load_workbook(data_only=True) 以及大多數預覽器 — 都會將每個公式儲存格讀取為 None。
python scripts/recalc.py output.xlsx [timeout_seconds] # 預設 30
LibreOffice 會計算每個公式,檔案會被原地覆寫,並輸出 JSON:status(success | errors_found)、total_formulas、total_errors,以及一個 error_summary,其中列出每種錯誤類型最多 100 個儲存格(locations_truncated 表示其隱藏了多少個 — 請信任 total_errors,而非清單的長度)。修正它所命名的錯誤,然後再次執行。JSON 中出現 error 鍵而非 status 鍵,表示未進行任何重新計算,且只有這種情況會以非零狀態碼退出 — errors_found 會以 0 狀態碼退出,因此切勿將乾淨的退出視為乾淨的活頁簿。
成功的重新計算僅證明你的公式可以求值,並不代表它們是對的。 範圍差了一行或參照到錯誤的列,都會產生一個乾淨、無錯誤但數字錯誤的檔案。在建立整個網格之前,先撰寫 2–3 個公式並確認它們拉取的數值符合預期。
連結到另一個檔案的活頁簿,若你使用 openpyxl 重新儲存並重新計算,將失去那些連結。此類公式讀取為 ='[1]Returns Analysis'!$B$2 — 其中的 [1] 是活頁簿外部參照清單中的索引,指向磁碟上的另一個檔案,而非工作表。該檔案在此環境中很少存在,因此儲存格的快取值是唯一保留資料的地方。openpyxl 在儲存時會清除該值;LibreOffice 隨後必須實際解析該參照,失敗後會寫入 #NAME?,並刪除所有連結。recalc.py 在該狀態下拒絕執行 — 請在覆寫檔案前,將那些儲存格的值從原始檔案中複製出來(使用 --force 可強制執行,並接受資料遺失)。
選擇能通過驗證的公式
LibreOffice 實作的函數比 Excel 少,且任何它無法求值的函數都會變成 #NAME? 字面值,嵌入你交付的檔案中。
- 優先使用 Excel 2007 時代的函數 —
SUMIFS、INDEX、MATCH、IFERROR、SUMPRODUCT— 這些不需要前綴。 - 六個 2007 之後的函數可以運作,但必須加上
_xlfn.前綴,因為 openpyxl 會將你的公式逐字寫入 XML,而 Excel 會為 2007 之後的名稱加上前綴儲存(其 UI 會隱藏前綴):_xlfn.TEXTJOIN、_xlfn.CONCAT、_xlfn.IFS、_xlfn.SWITCH、_xlfn.MAXIFS、_xlfn.MINIFS。若直接使用裸名,每個都會產生#NAME?。 - 絕對不要使用
XLOOKUP、XMATCH、SORT、FILTER、UNIQUE或SEQUENCE。 執行環境的 LibreOffice 在任何前綴下都無法對其求值。較新的版本確實可以求值,但它們是溢出陣列函數,而由 openpyxl 寫入的檔案沒有溢位元資料,因此只有範圍左上角的儲存格會得到一個值 — 且recalc.py在截斷結果上會回報total_errors: 0。請使用INDEX/MATCH進行查詢,並在將資料寫入儲存格前,先在 Python 中進行排序、篩選與去重。 - LibreOffice 無法解析的公式會以小寫形式寫回 — 這是在
#NAME?旁邊一個快速的判斷線索。
openpyxl 常見陷阱
- 讀取模型需要兩次載入。
data_only=True會產生快取值,但公式會消失;預設載入則會得到公式字串,但沒有值。一次傳遞無法同時取得兩者。 - 如果你儲存,
data_only=True具有破壞性。 該活頁簿中已無公式,因此儲存會將每個公式永久取代為字面值。 - 在 openpyxl 剛寫入的檔案上使用
data_only=True會全部回傳None— 請先執行recalc.py。(結果為""的公式也會回傳為None。) - 合併儲存格:僅寫入左上角錨點。 範圍內的其他每個儲存格都是
MergedCell,其.value為唯讀。 .xlsm會失去其巨集,除非你在load_workbook中傳遞keep_vba=True。- 包含空格的工作表名稱必須加上引號,用於跨工作表參照:
='Assumptions Inputs'!$B$5。若未加引號,會求值為#VALUE!。
財務模型
除非使用者另有指定,或現有檔案已採用不同的做法。
顏色: 藍色文字(0,0,255)用於硬編碼輸入與情境槓桿 · 黑色用於公式 · 綠色(0,128,0)用於連結到其他工作表 · 紅色(255,0,0)用於連結到其他檔案 · 黃色填滿(255,255,0)用於關鍵假設與使用者應填寫的儲存格。
數字: 貨幣 $#,##0,並在標題中標明單位(營收 ($mm)) · 零值顯示為 -,包含百分比($#,##0;($#,##0);-) · 負數以括號顯示 · 百分比 0.0%,以小數儲存(0.15 顯示為 15.0%;儲存 15 會顯示為 1500.0%) · 估值倍數 0.0x · 年份以文字形式("2024",絕不可顯示為 2,024)。
結構: 每個假設都應放在其自己的標籤儲存格中,並由使用該假設的公式參照(=B5*(1+$B$6),絕不可使用 =B5*1.05) · 公式在所有預測期間必須一致,因為單一被編輯的儲存格經常是無聲的錯誤來源 · 對分母可能為零的情況進行保護。
相依套件
openpyxl、pandas、markitdown(pip,已預先安裝 — 僅在匯入失敗或指令不存在時安裝) · LibreOffice(soffice,已透過 scripts/office/soffice.py 為沙箱環境自動設定)