空白と数字を翻訳しないGoogleSpreadシートでの翻訳する関数例

2020.10.22
learning what I want to introduce

Google translateホント便利だねー。色々便利だけど、Spead SheetでもGoogle Translateが使えるのがすごい。

全部の列に日本語が入ってて、全部英語にするならいいけど、そうじゃないケースもあると思う。そこで私の工夫したことをシェアするね。

そもそも、なんでこんなことしたかというと、日本語で書いた膨大なMoodleのquiz(小テスト）をCSVでアップロードをしたかった。

一つ一つ翻訳したけど、すっごい大変。スプレッドシートで一括で変換すれば楽じゃん！ってことになった。

1. まず基本。Googleスプレッドシートの日本語を英語に翻訳する関数
2. 空欄はエラーになってしまう
3. 数字が英語になってしまう
4. 空白と数字を翻訳しないGoogleSpreadシートでの翻訳する関数

まず基本。Googleスプレッドシートの日本語を英語に翻訳する関数

Googleスプレッドシートの日本語を英語にしたいときは専用の関数がある。

このブログ「Googleスプレッドシートで英作文が捗る！と聞いたので試してみました」を参考にしたのだけど、要するに

=googletranslate(訳したいセル,”ja”,”en”)
https://ej.alc.co.jp/entry/20170303-gnmt-spreadsheet

と書く。セルと元の言語（日本語）と翻訳したい言語（英語）を設定すると、そのセルが翻訳される！コピーして全シートを翻訳すると全部英語になる！

うわーめちゃカンタン！

でもね、いくつか問題がある。

空欄はエラーになってしまう

空欄があると翻訳できないからエラーになる。翻訳したい箇所だけ翻訳する関数を入れれば良いのだけど、それだと隙間があるたびに外さなきゃいけないので大変。CSVファイルにしたら、そのままエラーとなるだろうから、空欄を外す必要がある。

その対策としてIFをつかって空白は空白のままにした

if(翻訳するセル=””,””,(googletranslate(訳したいセル,”ja”,”en”))

これで空欄は””つまり空白のまま。それ以外は翻訳される。

しかし別な問題がある。

数字が英語になってしまう

4を翻訳するとfourになる。123はone two threeになる。
でも数字によってはそのまま数字で翻訳してくれる。

これ、どこまでが数字が数字のままになり、どこからがアルファベットになるかが不明なのだけど、CSVにするにはこれもまた困ってしまう。

数字のセルは数字のままにするにはどうするか。

そこで次の関数を加えた。

ISNUMBER(翻訳するセル)

これをいれると、数字だとTrue 数字じゃないとFalseになるようだ。

これを活用するIF文をつくる。

if(ISNUMBER(翻訳するセル),翻訳するセル,(GOOGLETRANSLATE(翻訳するセル,”ja”,”en”)))

これで、数字が数字のまま、数字以外は翻訳される。