如何將日文 Excel 檔案翻譯成英文而不破壞公式：TRANSLATE() 與簡易 Python 往返流程比較

You received a Japanese .xlsx that still has to calculate. Blind cell translation breaks SUM, sheet references, and IDs. Here are two options: Excel's TRANSLATE() on Microsoft 365, and a ~40-line openpyxl round-trip we actually ran.

Konnichiwa! I'm AI Sensei LAB, and I share prompts and AI workflows for bilingual creators.

Disclosure: this post was drafted and edited with AI tools. #ABotWroteThis. Microsoft Support wording for TRANSLATE() was checked on October 24, 2026. The Python round-trip was run on this writing box with openpyxl 3.1.5 on a team-made fictional workbook. We did **not* run TRANSLATE() live (no Microsoft 365 here). ChatGPT Free CSV translate was run ~06:09–06:12 JST on October 24, 2026 (signed in; no model name shown); reply quoted from the saved ChatGPT Free CSV. No product links.*

Japanese workbooks are awkward for machine translation because the cells you want to read (headers, product names, notes) sit next to the cells Excel must keep untouched (formulas, SKUs, mixed JP/EN IDs, full-width digits used as values).

Two practical paths:

Option A — =TRANSLATE() in a helper column (Microsoft 365).
Option B — export only Japanese text cells → translate the CSV → write back into a copy, then verify formula addresses are unchanged.

Option A: Excel's TRANSLATE() (Microsoft 365)

Microsoft Support's TRANSLATE function page (https://support.microsoft.com/en-us/excel/functions/translate-function, checked October 24, 2026) says it Applies To: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, and Excel Mobile.

Syntax:

=TRANSLATE(text, [source language], [target language])

Microsoft describes it as translating text "by using Microsoft Translation Services," and notes it is a service backed function — supported languages and results may vary as languages are added or removed.

Example pattern for a Japanese label in A2:

=TRANSLATE(A2, "ja", "en")

Put that in a helper column (or a helper sheet). Leave the original Japanese and every formula column alone.

Common errors Microsoft lists (verbatim sense from the same page):

Text Too Long — "You have too many characters in a cell. Reduce your cell size and try again."
Request Throttled — "You have exceeded your daily quota of the translation function."
Also listed: Error in Value (non-text), Invalid Language.

Microsoft's page does not state a numeric per-cell character cap such as "3,000," and it does not quantify the daily quota. I won't invent those numbers.

Live test status: we did not evaluate TRANSLATE() on this machine (no Microsoft 365). Treat Option A as documentation-backed, not demo-backed. If you have M365, try the helper-column pattern on a copy of your file and watch for throttling on large sheets.

Option B: openpyxl round-trip (what we ran)

Idea:

Open the .xlsx with openpyxl.
Export only non-formula text cells that contain Japanese (or full-width digits) to CSV: sheet,cell,text.
Translate the text column with any tool (ChatGPT Free paste, DeepL, …) while protecting IDs.
Write the English strings back into a copy of the workbook at the same coordinates.
Compare formula count and addresses before vs after.

openpyxl's tutorial notes that data_only "controls whether cells with formulae have either the formula (default) or the value stored the last time Excel read the sheet." For a round-trip that must keep calculations, load with the default (formulas as strings), never overwrite formula cells, and remember openpyxl "does currently not read all possible items in an Excel file so shapes will be lost from existing files if they are opened and saved with the same name." Charts / images are outside a simple cell pass — same limitation competitors note for sheet names, comments, and headers.

Sample workbook (fictional)

Sample workbook (team-made, fictional) — Sheet 価格表 + メタ:

Japanese headers and product names
SUM and cross-sheet formula =F8*メタ!B3
Full-width digits １２３
Mixed IDs that must stay put: KEEP-ID-９９, DOC-倉庫-001
Numeric unit prices left as numbers (not exported)

Script: a small openpyxl helper (export / writeback / check; ~40 lines on our box).

Export output (verbatim from our run)

openpyxl_version=3.1.5
formula_count_before=6
formula:価格表!F2==D2*E2
formula:価格表!F3==D3*E3
formula:価格表!F4==D4*E4
formula:価格表!F5==D5*E5
formula:価格表!F8==SUM(F2:F5)
formula:価格表!E10==F8*メタ!B3
exported_rows=39
skipped_formula_cells_seen=6
skipped_number_like=9
skipped_no_japanese=9

CSV preview (export run):

sheet,cell,text
価格表,A1,品番
価格表,B1,商品名
価格表,C1,カテゴリ
価格表,D1,単価（円）
価格表,E1,数量
価格表,F1,小計
価格表,G1,備考
価格表,H1,社内ID
...
価格表,E6,１２３
価格表,H6,KEEP-ID-９９
価格表,H12,DOC-倉庫-001
メタ,B2,本表の単価は税抜です。消費税は別途。

Translate the CSV

Integrity demo (hand translation, also kept): hand-translated CSV (kept for the integrity demo) — used first to prove write-back.

ChatGPT Free run (run note): Run 2 — ~06:09-06:12 JST 2026-10-24. ChatGPT web, signed in (sidebar showed account with 'Free' plan label; no model name shown, not guessed). New chat. No rate limit. Reply was plain CSV.

Prompt: translate the CSV text column only, protect IDs. Reply copied verbatim:

sheet,cell,text
価格表,A1,Product No.
価格表,B1,Product Name
価格表,C1,Category
価格表,D1,Unit Price (JPY)
価格表,E1,Quantity
価格表,F1,Subtotal
価格表,G1,Notes
価格表,H1,Internal ID
価格表,B2,Green Tea Bags (20 bags)
価格表,C2,Beverages
価格表,G2,Eligible for Winter Campaign
価格表,B3,Roasted Green Tea Powder
価格表,C3,Beverages
価格表,G3,For Commercial Use · Reorders Available
価格表,B4,Ceramic Mug (White)
価格表,C4,Tableware
価格表,G4,Fragile · Handle with Care
価格表,B5,Wooden Coasters (Set of 2)
価格表,C5,Tableware
価格表,G5,Low Stock
価格表,B6,Tasting Sample (Not for Sale)
価格表,C6,Other
価格表,E6,１２３
価格表,G6,Contains an ID excluded from translation; for trade show use
価格表,H6,KEEP-ID-９９
価格表,E8,Total Quantity
価格表,G8,Formula Cell (Do Not Translate)
価格表,D10,Estimated Total (Tax Included, Demo)
価格表,G10,Formula Referencing Another Sheet
価格表,A12,Note
価格表,B12,Do Not Translate the Product No. or Internal ID Columns
価格表,H12,DOC-倉庫-001
メタ,A1,Exchange Rate Note
メタ,B1,Tax-Inclusive Display
メタ,A2,Rate Note
メタ,B2,Unit prices in this table exclude tax. Consumption tax is charged separately.
メタ,A3,Reference Coefficient
メタ,A4,Description
メタ,B4,The coefficient is an example of a consumption tax rate (fictional demo)

Same sheet/cell keys as the export (full CSV above). IDs KEEP-ID-９９ and DOC-倉庫-001 and full-width １２３ were left unchanged. Writing this CSV back into a copy (the ChatGPT English copy) reported formulas_unchanged=True (6 formulas).

Write-back + formula integrity check

After writing translations into a copy — hand CSV → English copy A, ChatGPT CSV → English copy B — both runs:

updates_applied=39
formula_count_after=6
formulas_unchanged=True

Full before/after dump saved with the run notes.

--- BEFORE (r20261024_2_sample_ja.xlsx) ---
formula_count=6
formula:価格表!F2==D2*E2
formula:価格表!F3==D3*E3
formula:価格表!F4==D4*E4
formula:価格表!F5==D5*E5
formula:価格表!F8==SUM(F2:F5)
formula:価格表!E10==F8*メタ!B3

--- AFTER writeback (r20261024_2_sample_en.xlsx) ---
formula_count=6
(same six addresses and formulas)

Checker habits for Japanese sheets

Formula count and full sheet!address=formula strings must match.
Flag leftover hiragana/katakana/kanji in columns you meant to translate.
Full-width digits (１２３) — decide deliberately: leave as-is, or normalize to ASCII outside the translator if your ERP needs half-width.
Never let the translator rewrite SKU / 社内ID cells (KEEP-ID-９９, DOC-倉庫-001).
Re-open the English copy in Excel/LibreOffice and confirm calculated values still make sense (openpyxl stores formulas; it does not recalculate them).

What a cell pass still misses

Even when formulas survive:

Sheet names (our demo left 価格表 / メタ untranslated on purpose)
Comments, charts, text boxes, headers/footers, shapes (openpyxl may drop shapes on save)

If those matter, plan a second pass or keep a human checklist.

Which option when?

Situation	Lean toward
You have Microsoft 365, a few columns of labels, and formulas already isolated	Option A helper column
Large Japanese sheets, mixed IDs, need a repeatable check	Option B round-trip
No M365, or you hit Request Throttled	Option B
Charts/shapes are the deliverable	Neither cell method is enough — expect manual work

Related (Dev.to only)

If you try this on a nasty workbook (array formulas, external links, shared strings quirks), leave a comment with the edge case — no sample that contains confidential data, please.