こんにちは！映像・フィジカルAI開発部の山内です。
いま私のいる開発チームにはネパール出身のメンバーがいて、日々のやりとりは日本語と英語を織り交ぜながら進めています。ドキュメントの類はブラウザのページ翻訳機能でお互いなんとかなるのですが、ひとつ困りものがありました。
スプレッドシートです。あれ、ページ翻訳が効かないんですよね。
（スプレッドシートのセル部分は HTML のテキストではなく canvas に「描画」されているため、ページ翻訳が置き換えるべきテキストを見つけられないようです。）
そこで、簡単に使える、関数だけで動く「翻訳シート」を作ってみました。今回はその仕組みをご紹介します。
まずは完成形をご覧ください。
左の日本語シートに対して、右のシートが同じ位置のセルを自動で英訳しています。GAS（Google Apps Script）は使っていません。組み込み関数と名前付き関数だけです。
しかも、現場の専門用語はチームで決めた訳語のとおりに出ます。
コピペ用完成形（お急ぎの方用）
お急ぎの方のために、簡単な作り方から載せます！3手順です。
手順1: 単語帳シートを作る
「用語集」という名前のシートを作り、A列に原語、B列に対訳を並べます。
中身はチームのドメインに合わせてください。私のチームの場合はこんな感じです。
| A列（原語） | B列（確定訳） |
|---|---|
| 墨出し検査 | marking inspection |
| 返り墨 | offset marking line |
| 寸法補助線 | projection line |
| ダメスリーブ | Dame sleeve |
手順2: 名前付き関数を登録する
メニューの「データ → 名前付き関数」から、次の内容で登録します。
- 関数名:
CUSTOMTRANSLATE（※好きな名前でOK） - 引数:
sheet（翻訳したい元シートの名前）/from（翻訳元の言語コード）/to（翻訳先の言語コード） - 数式: 以下をそのままコピペ
|
1 2 3 4 5 6 7 8 9 10 11 12 |
=IFERROR( GOOGLETRANSLATE( REDUCE( INDIRECT(sheet & "!" & ADDRESS(ROW(), COLUMN())), '用語集'!$A:$A, LAMBDA(text, word, IF(word = "", text, SUBSTITUTE(text, word, XLOOKUP(word, '用語集'!$A:$A, '用語集'!$B:$B, ""))))), from, to), "") |
手順3: 翻訳用のシートに敷き詰める
翻訳用の空シートを1枚作り、翻訳したい範囲のセルにこの関数をコピーして敷き詰めます。
|
1 2 |
=CUSTOMTRANSLATE("【翻訳元のシート名】", "ja", "en") |
これだけで、元シートを丸写しに英訳するミラーシートのできあがりです！
ここから先は、この数式が何をしているのかを内側から順に組み上げながら解説していきます。
なぜ単語帳が要るのか
私は普段、スリーブ検査ARアプリ「SLEEVY」の開発に関わっています。
SLEEVY とは、建築工事で設備配管を通すために壁や床へ埋め込む筒状の管「スリーブ」の検査を、iPad Pro 1台で済ませられるようにするアプリです。
建設現場のドメインなので、検査項目のシートには専門用語がバンバン出てきます。手順1の単語帳に並んでいたのが、まさにその専門用語たちです。
なかでも「ダメスリーブ」は少し特殊です。設置位置に「ダメ穴」（後から埋める前提で開けておく穴）が絡んでいて、ダメ穴が埋まった後に通常とは異なるタイミングで施工・検査を行うスリーブのことを、SLEEVY ではこう呼んでいます。
システム特有のラベルで、ぴったりの英訳が存在しないため、チーム内ではあえて “Dame sleeve” を公式訳にしています。
……この時点でお気づきかと思いますが、こういう専門用語って、機械翻訳は汲み取ってくれません。
実際、単語帳なしで検査項目のセルをそのまま GOOGLETRANSLATE に渡すとこうなります。
実際には、損傷を受けたスリーブではないのだが・・・
「ダメ」が「損傷した」と解釈され、”damaged sleeve” のような、もっともらしいけれども意味を取り違えた英文が出てくる。これでは誤解を招きます。
文章の骨格は訳せているのに、肝心の専門用語が訳せない。かといって、誤訳のパターンをすべて予測して後から直すのも困難です。
そこで、誤訳されてから直すのではなく、誤訳される前に正しい訳語へ置き換えておく。これがこの仕組みの設計思想です。
数式を段階的に理解する
ステップ① 土台: GOOGLETRANSLATE ＋事前置換
土台になるのは、スプレッドシート組み込みの翻訳関数 GOOGLETRANSLATE です。
|
1 2 |
=GOOGLETRANSLATE("こんにちは", "ja", "en") // → Hello |
第1引数のテキストを、ある言語から別の言語へ翻訳するシンプルな関数です。（※詳しい仕様は公式ヘルプへ。）
ここに、「翻訳前にある特定の単語を置換する」処理を足します。
|
1 2 3 4 5 6 |
=GOOGLETRANSLATE( SUBSTITUTE("ダメスリーブの墨出し検査を実施", "ダメスリーブ", XLOOKUP("ダメスリーブ", '用語集'!A:A, '用語集'!B:B, "")), "ja", "en") |
内側から読むと、
XLOOKUPが単語帳を検索し、「ダメスリーブ」の確定訳 “Dame sleeve” を引くSUBSTITUTEが原文中の「ダメスリーブ」を “Dame sleeve” に置き換える- 置換済みのテキストが
GOOGLETRANSLATEに渡る
という流れ。
結果的に、GOOGLETRANSLATE関数には「Dame sleeveの墨出し検査を実施」という原文が渡されます。
すでに英語になっている部分を GOOGLETRANSLATE はほぼそのまま通すので、”Dame sleeve” が守られたまま残りの日本語だけが翻訳されるというわけです。
ステップ② REDUCE で単語帳をまるごと適用する
ステップ①だけでは1組の単語しか扱えないので、単語帳の全行に置換を広げます。ここで使うのが REDUCE と LAMBDA です。
|
1 2 3 4 5 6 7 8 9 |
=GOOGLETRANSLATE( REDUCE("ダメスリーブの返り墨と寸法補助線を確認", '用語集'!$A:$A, LAMBDA(text, word, IF(word = "", text, SUBSTITUTE(text, word, XLOOKUP(word, '用語集'!$A:$A, '用語集'!$B:$B, ""))))), "ja", "en") |
REDUCE に馴染みがない方は、「単語帳を上の行から順にたどりながら、原文に置換を積み重ねていくループ」だと思ってください。
- 初期値は原文テキスト
- 単語帳のA列を1行ずつ
wordとして受け取る - 空行なら何もしない（
IF(word = "", text, …)） - 用語が見つかれば
SUBSTITUTEで確定訳に置き換え、次の行へ渡す
これで単語帳に何十語登録されていても、1本の数式で対応できるようになりました。
ステップ③ 参照を「自分と同じ番地」にする
最後に、原文を直接書いている部分を次の参照に差し替えます。
|
1 2 |
INDIRECT(sheet & "!" & ADDRESS(ROW(), COLUMN())) |
ADDRESS(ROW(), COLUMN()) は「この数式が置かれているセルと同じ番地」を返すので、INDIRECT と組み合わせると「元シートの、自分と同じ位置のセル」を参照できます。数式を置いた場所が B3 なら、元シートの B3 を勝手に見にいってくれる、ということです。だからこそ手順3では「敷き詰めるだけ」でミラーシートになったわけです。
仕上げに全体を IFERROR(…, "") で包んで、元シートの空セルや参照エラーを空文字に落とします。これで冒頭に紹介した数式と完全に一致しました。
運用してみて
単語帳は「育てる」もの
このやり方の一番気に入っているところは、誤訳を見つけたときの直し方が「単語帳に1行足す」だけという点です。
数式を直接触ることはないので、エンジニアでなくても、だれでも「この訳語のほうが自然だよ」と気軽に直すことができます。
単語帳をそのままチームの用語集として育てていけるつくりです。
なぜ「翻訳後」ではなく「翻訳前」に置換するのか
用語の置換を翻訳の後にやる方法も考えられますが、それだと「専門用語がどう誤訳されたか」をすべて列挙できないと置換できません。誤訳のパターンは安定しないので、原理的に穴が残ります。翻訳の前に確定訳へ置き換えてしまえば、そもそも誤訳される機会がありません。順序が肝心です。
注意点
運用していて見つけたハマりどころを3つ挙げておきます。
注意点①: SUBSTITUTE は部分一致する
たとえば単語帳に「墨」のような短い語を登録すると、「墨出し検査」の中の「墨」まで置換されて一部分しか訳されません。
|
1 2 3 |
✗ 単語帳の上の行に短い語 → 「墨出し検査」が「ink出し検査」のような中間状態に ✓ 長い語を単語帳の上の行に置く（REDUCE は上から順に置換するため、長い語が先に確定する） |
注意点②: 再計算が重くなりがち
GOOGLETRANSLATE も INDIRECT も軽い関数ではありません。シート全域に敷き詰めると再計算のたびに待たされることがあります。あまりにも重いようなら、翻訳が必要な範囲だけに絞る、内容が確定した行は値貼り付けに変える、といった回避策を考えておくと良いでしょう。
注意点③: 表記ゆれは単語帳側で吸収する
SUBSTITUTE は表記ゆれ（「墨出し」「墨だし」など）や大文字小文字の違いを吸収してくれません。ゆれを見つけたら、それも単語帳に1行足して吸収するのがシンプルです。
まとめ
今回の内容をまとめます。
- スプレッドシートのページ翻訳問題は、GOOGLETRANSLATE＋名前付き関数の「ミラー翻訳シート」で解決できる（GAS 不要）
- 専門用語は翻訳前に単語帳で確定訳へ置換するのがミソ。”Dame sleeve” のようなチーム独自の訳語も守れる
- 単語帳に1行足すだけで賢くなっていくので、チームみんなでメンテできる
ただし、翻訳の本体はあくまで GOOGLETRANSLATE なので、訳文そのものの質は機械翻訳なりです。用語が正確なだけでも日々のやりとりの誤解はぐっと減りますが、対外的な文書の翻訳を丸ごと任せるのはやめておきましょう。
ここまでお読みいただき、ありがとうございました！
