Google Drive で作成した文書をオフラインでも利用したい

1. Google Drive で作成した文書をインターネットに接続していない状況でも開きたい

 Google Drive でプレゼン用のスライドを作成した。

このスライドを、インターネットに接続できない環境 (オフライン) で開き、プレゼンテーションを再生したい。

 

2. Google Chrome でオフラインの設定を行う

オフラインでファイルにアクセスする - ドライブ ヘルプ によると、

インターネットに接続していないときでも、Chrome ブラウザを使用してファイルで作業することができます。Chrome ではドライブ、ドキュメント、スプレッドシート、スライドのファイルが自動的に同期するため、オフラインでもファイルを閲覧、編集できます。

オフラインでのアクセスは Google Chrome からだけ利用できる。普段、Firefox をメインブラウザとして使っているので、そのような機能があるとは知らなかった。

例えば、Firefox で Google Drive の設定画面を開くと、

「オフライン」に関する設定は表示されない。

これに対して、Google Chrome で設定画面を開くと、「オフライン」の設定が表示される。

この「オフライン」の設定にチェックを入れると、「Google ドライブ」のアプリが追加されるダイアログが表示されるので、「追加」ボタンを押す。

 

3. ドキュメント・スプレットシート・スライドでオフラインの設定をする

次に、念のため以下のサイトにアクセスして、オフラインの設定状況を確認しておく。

• ドキュメント: docs.google.com
• スプレッドシート: docs.google.com/sheets
• スライド: docs.google.com/slides

例えば、スライドにアクセスし、左上のハンバーガーメニューより、設定を選択する。

そして、表示さえるダイアログのオフライン同期において、「オンにする」のリンクをクリックする。

これにより、オフラインの設定がされる。

 

4. オフラインでアクセスしたい文書を開く

最後に、オフラインでアクセスしたい文書を一度開くと、インターネットに接続していないくても文書を開くことができるようになる。

これにより、作成スライドをネット環境がなくても、プレゼンテーションに利用できるようになる。

追記(2015/3/1):  どうしても上手くオフラインでアセクスできない場合、Google ドライブをダウンロード をインストールしておき、上例においては、

• スタートメニュー > すべてのプログラム > Google Drive > Google Slides

を起動し、当該文書を開く。

Easy Copy で不要な文字を削除。複数のリンクを生成し、Google スプレッドシートに貼り付け。

• Firefox でウェブページの「タイトル」と URL を得る Easy Copy。短縮 URL には FireLink。のつづき。

1. 不要な文字列を削除 - Text to remove

 Easy Copy で `Title Link’ を作成するとき、不要な文字を排除することができる。

例えば、自分のブログを引用するとき、

Make Link をキー入力で素早く呼び出す | すぐに忘れる脳みそのためのメモ

の末尾にある

| すぐに忘れる脳みそのためのメモ

を常に削除している。自動で省きたい場合は、

• 右クリック > Easy Copy > Options > Formatting > Text to remove

において、削除して欲しい文字列を列挙する。

一時的に文字を省きたくない場合は、

• Remove unnecessary text from the fron or back of the title

のチェックを外す。

 

2. 複数のリンクを生成 - Copy All Tabs

Easy Copy では、ウィンドウ内に開いているタブを全てから、リンクを作ることができる。

リンクを作成したい対象が一つのウィンドウである場合、予め

• 右クリック > Easy Copy > Copy All Tabs

において、Include the following tabs: で Active Window Only を選択しておく。

そして、対象のウィンドウで

• 右クリック > Copy All Tabs >

において、適当な形式を選ぶ。

 

Amazon のリンクを Google スプレッドシートに貼り付ける

例えば、Amazon でいくつかの商品を調査した後、Google スプレッドシートに貼り付けたいとする。

対象タブを複数開いたままにしておき、前回設定をした

• 右クリック > 右クリック > Copy All Tabs > Google Spreadsheet

を選択する。

接頭辞にある不必要な文字を削除したい場合には、

• Options > Formatting > Text to remove

に以下を列挙しておく。

Amazon.co.jp：
Amazon.co.jp:
Amazon.co.jp |

これで、Google スプレッドシート リンクを貼り付けると、1行1列に納まる。

「新しい Google ドライブ」でドキュメント、スプレッドシートを作成できないので、クッキーを削除した

1. 「新しい Google ドライブ」でドキュメント、スプレッドシートを作成できない

新しい Google ドライブを使うようになった。ファイルをシングルクリックで「選択」し、ダブルクリックで「開く」操作に最初抵抗があったが、しばらくしたら慣れた。

Firefox でいつものように「新規ボタン」より、新しくドキュメントを作ろうとしたら、Googleドキュメント、スプレッドシードが表示されなかった。

• 新規ファイル > アプリを追加

より、Google スプレッドシートを接続しても、何も表示されない。

Ctrl + F5 でスーパーリロードしても、問題は解決されなった。

• cf. ブラウザの更新 スーパーリロード

ただし、Google Chrome で表示したら問題なかった。

 

2. Firefox のクッキーを削除

そこで、Firefox の Google に関するクッキーを削除することにした。

• オプション > プライバシー > 履歴 より、「Cookie を個別に削除」

を選択。検索フィールドに `google’ と入力し、表示された一覧を選択し、「Cookie を削除」をクリック。

Google ドライブに再びアクセスし、「新規」ボタンを押したら、Googleドキュメント、スプレッドシード を選択できるようになった。

Google スプレッドシートで「改行を含むセル」をエディタにコピペ。文字列を囲むダブルクォーテーションを外すには一度 Google ドキュメントにペーストする。

1. Google スプレッドシートで「改行を含むセル」をコピペすると、ダブルクォーテーションで囲まれる

Google スプレッドシートの1つのセルに複数行を入力するには、Alt + Enter で改行する。

カスタム関数で複数行の文字列を返すには `\n’ を用いる。

function multiLine() {
 return "ほげ\n\
ぴよ\n\
ふが";
}

このセルをエディターにコピペすると、文字列全体がダブルクォーテーションで囲まれてしまう。

"ほげ
ぴよ
ふが"

 

2. CSV 形式では改行が含まれると、ダブルクォーテーションで囲まれる

文字列がダブルクォーテーションで囲まれる理由は、Google スプレッドシートのセルをコピーすると、CSV 形式となるため。

How to copy from Google Spreadsheet without it editing the source data - Web Applications Stack Exchange

The "issue" is that Google Spreadsheet is putting valid CSV-formatted data into the clipboard.

CSV 形式では、改行が含まれるとダブルクォーテーションで囲まれる。

Comma-Separated Values – Wikipedia

フィールドがコンマ、ダブルクォート、改行を含む場合は、かならずダブルクォートで囲む。

 

3. Google ドキュメントへコピペしてから、エディタに貼り付ける

ダブルクォーテーションを取り除きたい場合は、コピーしたセルを Google ドキュメントに貼り付け、それを再びコピーした後、エディタに貼り付ける。

つまり、

1. Google スプレッドシートのセルをコピー
2. Google ドキュメントへ貼り付けてから、再びコピー
3. エディタへコピー

この場合、Google ドキュメント以外に、LibreOffice や MS Word を用いても良い。

Google スプレッドシートのカスタム関数に「列」（範囲）を引数として与えた場合、目的の型に変換する必要がある

1. 日付の「列」をカスタム関数に与えたら、 Date 型メソッドの呼び出しで TypeError

a. 日付が入力された「セル」をカスタム関数に与えたら、Date 型メソッドを呼び出せる

Google スプレッドシートで、日付から「日」を得るカスタム関数を以下のように定義した。

function getDate(date){
  return date.getDate();
}

• cf. Date.getDate - JavaScript | MDN

シートの A 列に日付を入力し、B1 セルで上記のカスタム関数を呼び出す。そして、下方向へコピーした。

その結果、B 列に「日」を表示することができた。

 

b. 日付が入力された「列」をカスタム関数に与えたら、Date 型メソッドを呼び出せない

次に、上記の「カスタム関数呼び出しの繰り返し」を配列数式で置きかえるために、以下のように関数を定義しした。

function getDateList(dates){
  var i, result = [];
  for (i = 0; i < dates.length; i++){
    result[i] = [dates[i].getDate()];
  }
  return result;
}

• cf. Google スプレッドシートでカスタム関数を多くのセルで呼び出しエラーが表示される場合、配列を返す関数（配列数式）に変更する。 > 「配列を返す」カスタム関数の例

getDateList 関数を B1 セルで呼び出した結果、TypeError が表示された。

TypeError - JavaScript | MDN とは、

値が期待される型でない場合のエラーを表します。

先ほどの getDate 関数は、日付が入力された「１つのセル」を引数として与えた。関数の中で getDate() メソッド を呼び出すとき、「日付型の値に対してメソッドを呼び出す」と考えてコードを記述した。

これに対して、getDateList 関数では、「日付の列」を与えている。関数の中では、各要素に対して getDate() メソッド を呼び出した。しかし、結果、そのようなメソッドはオブジェクトに定義されていないというエラーが表示された。

どうやら、カスタム関数に「列」（範囲）を引数として与えると、各要素の型情報が失われるようだ。

 

c. JavaScript の配列と比較

JavaScript では日付型の要素を持つ配列の要素に対して、getDate() メソッド を呼び出すことができる。

[
    new Date('2014/1/1'),
    new Date('2014/1/2'),
    new Date('2014/1/3')
].map(function (x) {
    return x.getDate();
});

実行すると、結果は以下のように表示される。

[1, 2, 3]

つまり、Google スプレッドシートで、カスタム関数に「列」（範囲）を引数として与えることは、JavaScript の配列を与えることとは異なる。

 

2. カスタム関数において列（範囲）から要素を取り出したら、目的の型に変換する必要がある

Google スプレッドシートで上記の TypeError を回避するためには、「列」（範囲）から要素を取り出すときに、日付型に変換する必要がある。

function getDateList(dates){
  var i, result = [];
  for (i = 0; i < dates.length; i++){
    result[i] = [new Date(dates[i]).getDate()];
  }
  return result;
}

これにより、Date 型のメソッドを呼び出すことができるようになる。

 

3. シート上の計算と、カスタム関数、配列数式における「文字列」「数値」「日付」の扱われ方の比較

上記の点について、Google スプレッドシートで「文字列」「数値」「日付」がどのように扱われるのか確認しておく。

a. 表計算のシート上で 1 を足す

最初に、表計算のシート上における計算について確認する。

対象の値は、

• 文字列 「ほげ」
• 数値 `100’
• 日付 `2014/1/1’

各値に対して + 演算子を使い、「１を足す」計算を行う。

結果、数値は 1 加算され、日付は１日後が表示された。

ただし、文字列に対して 1 を足すことはできない旨のエラーが表示された。

 

b. 単一のセルを与えるカスタム関数で + 1

次に、セルの値に対して + 1 するカスタム関数を定義する。

function addOne(cell){
 return cell + 1; 
}

この関数を呼び出した結果、文字列 「ほげ」 に `1’ が連結された。これは JavaScript の仕様で、文字列に + 演算子を適用すると文字列の連結となるから。

数値は `100’ に 1 が足され 101 となった。JavaScript では数値に + 演算子を適用すると「加算」が行われる。

「日付」は、日付の文字列表現に 1 が連結された。

このような結果となる理由を探るために、各セルの値の型を検査する関数を定義した。

function testType(obj) {
  return typeof obj
}

• typeof 演算子 - JavaScript | MDN

結果、文字列は string、数値は number、日付は object となった。

日付の型は object と表示されたが、Date 型のメソッドを呼び出すことができる。JavaScript で日付を加算するには、以下のように記述する。

function addOneDate(cell){
  var d = new Date(cell);
  d.setDate(d.getDate() + 1);
  return d;
}

• Date - JavaScript | MDN

この関数を呼び出すと、引数に渡した翌日（１日後）が表示される。

 

c. 列（範囲）を与えるカスタム関数で + 1

上記の + 1 する「カスタム関数の繰り返し」を配列数式で置きかえるために、以下の関数を定義した。

function addOneList(col){
  var i, result = [];
  for(i = 0; i < col.length; i++){
    result[i] = [col[i] + 1]; 
  }
  return result;
}

この関数を呼び出した結果、先ほどと結果が異なった。さっきは数値が数値として扱われ + 1 が加算されたが、今回は数値が文字列として扱われ “100” に “1” が連結される形となった。

つまり、ここでもカスタム関数に「列」（範囲）を与えると、型情報が失われたことが分かる。

配列数式で型を検査する関数を定義して、型を確認してみる。

function testTypeList(col){
  var i, result = [];
  for(i = 0; i < col.length; i++){
    result[i] = [typeof col[i]]; 
  }
  return result;
}

この結果、全て object となった。やはり、カスタム関数に「列」（範囲）を与えた場合、自分で目的の型に変換しなければならないようだ。

 

d. 列（範囲）を与えるカスタム関数で数値を扱う

例えば、A 列に数値があり、B 列に + 1 した値を得たいとする。

このとき、配列数式でカスタム関数を記述する場合、列から要素を取り出した後、数値にキャストする必要がある。

function addOneNumberList(col){
  var i, result = [];
  for(i = 0; i < col.length; i++){
    result[i] = [Number(col[i]) + 1]; 
  }
  return result;
}

これにより、数値による計算が行われる。

数値にキャストしない場合、+ 演算子が文字列の連結として扱われてしまう。

Google スプレッドシートで「配列数式」に与える「範囲」を誤ると、無限ループに陥る

1. カスタム関数で無限ループに陥る場合

Google スプレッドシートで「配列数式」を使うメリットは、「行」を追加した場合、自動的に再計算がされること。

しかし、数式に与える「範囲」、セルの「位置」に注意しないと無限ループに陥る。

例えば、列を与えると、そのコピーを返す関数があるとする。

A 列の値を B 列にコピーするための関数を、以下のように定義した。

function copyColumn(col){
  var result = [];
  for (var i = 0; i < col.length; i++){
    result[i] = [col[i]];
  }
  return result;
}

B1 セルに

=copyColumn(A:A)

を入力する。その結果、A 列の値を B 列にコピーされた。

ここで誤って B2 セルに

=copyColumn(A:A)

を入力したとする。その結果、行が無限に増殖してしまう。

なぜなら、copyColumn に与える範囲は A 列 であり、固定された行数を指定していない。そのため、

1. A1 ~ A9 の値が B2 ~ B10 にコピーされる。
2. シートに一行追加される。
3. A10 セルの値が B11 にコピーされる。
4. A:A の示す範囲は、最初より 1 行多くなった。
5. そのため、copyColumn の処理が更に進む。

このように、無限ループに陥り、行が限りなく増殖してしまう。

 

2. ARRAYFORMUAL で行の増殖

カスタム関数と同じように、ARRAYFORMUAL 関数でも同じことが言える。

例えば、A 列の値を 2 倍した結果を B 列に表示したい。

B1 セルに

=arrayformula(A:A * 2)

と入力する。

これに対して、B2 セルに

=arrayformula(A:A * 2)

と入力すると、B2 セルの右肩に三角のマークが表示される。

注意: この結果の 1 つ以上のエントリを表示できません。表示するには、このセルを選択して Ctrl+E を押してください。

注意書きに従い、Ctrl + E を押すと、行が増殖していく。

 

関連記事

• 3. 「配列を返す」カスタム関数の例

Google スプレッドシートで「配列数式」を使うメリットは、「行」を追加した場合、自動的に再計算がされること。下方向へのコピーを自動で行なってくれるようなもの。

1. 配列数式を多用すると保守しにくい

これまで、「配列を返すカスタム関数」、「配列数式に置きかえるための ARRAYFORMUAL 関数」「行列の積」のように「複数行を計算結果として返す関数」を見てきた。

• カスタム関数を多くのセルで呼び出しエラーが表示される場合、配列を返す関数（配列数式）に変更する。または、新しいスプレッドシートを利用。
• 「数式の繰り返し」を ARRAYFORMULA で置きかえる (1)
• 「数式の繰り返し」を ARRAYFORMULA で置きかえる (2) - VLOOKUP 関数の場合
• SUM 関数の繰り返しを「行列の積」で置きかえる

「数式の繰り返し」を「配列数式」で置き換えると、数式が複雑になる。カスタム関数は、元の関数をラップする関数が必要となる。そのため、保守まで考えると乱用したくない。

加えて、2013年12月以前の Google スプレッドシートでは、配列数式を用いると処理できる計算が多くなった。しかし、現在のバージョンでは、計算できる処理量に差がないように見える。

 

2. 配列数式のメリット

それでも、配列数式を用いるメリットはある。それは、

1. 配列数式を用いると、シートの行が足りなくなり「行を追加する」と、行が増えた分だけ自動で再計算を行なってくれること。
2. そのため、前行の数式を手作業で「下方向へコピー」する手間が省ける。
3. また、配列数式を入力した行以外では、誤って式を消したり、変更されたりする心配がない。

以下の例で説明する。

• Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (2) - VLOOKUP 関数の場合

 

a. 手動で数式を下方向へコピーする場合

例えば、「配列数式」を使わずに、行を追加する度に数式を下方向へコピーする場合。

最初に「行」を追加する。次に、「下方向へコピー」したいセルを選択する。

そして、下方向へのコピーを行わなければならない。

「下方向へコピーしたセル」は数式なので、誤って消してしまったり、意図せず誤入力をしてデータを壊してしまうことがある。

 

b. 「配列数式」を利用する場合

これに対して、配列数式の場合は、行を追加した後、再計算が行われ、自動的にセルに値が入力される。

以下のように、行を追加すると、「処理中」と通知が出た後、計算が必要なセルに自動的に値が入力される。

ただし、これは配列数式の呼び出しで、計算を行う範囲が適切に指定されていることによる。

その後、計算元となる値をセルに入力していくと、すぐに再計算が行われる。

自動的に入力されたセルは、誤って消したり、誤入力をしても、再計算が行われ元のデータに戻る。

Google スプレッドシートで SUM 関数の繰り返しを「行列の積」で置きかえる

• 「Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (2) - VLOOKUP 関数の場合」のつづき

1. SUM 関数の繰り返しを ARRAYFORMULA 関数で置きかえることはできるのか？

ARRAYFORMULA 関数は、単一のセルに値を返す（配列を返さない）「組込みの関数の繰り返し」を、配列を返す形に置きかえることできる。これまで ARRAYFORMUAL 関数の使い方をいくつか試した。

四則演算などの「演算子」、単一のセルに適用する「組み込み関数」、引数が複数ある少し複雑な VLOOKUP 関数に対して ARRAYFORMULA 関数を適用した。

• Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる
• VLOOKUP 関数の場合

次に、「引数に範囲を与える」組込み関数に対して、ARRAYFORMULA が使えるのか考えたい。

引数に範囲を与えることができる関数はいくつかある。例えば、SUM 関数は、範囲を与えると合計を返す。

=sum(A1:C10)

この関数を ARRAYFORMULA に渡すことはできるのだろうか？

 

2. ARRAYFORMUAL の使い方を復習

ARRAYFORMULA の使い方を簡単に復習しておく。

これまでの例では、ARRAYFORMULA 関数を適用する「関数の引数」が「単一のセル」を対象にしていた。このような関数の繰り返しを ARRAYFORMULA で置きかえるには「単一のセル」を「範囲」に変更すればよかった。

例えば、あるセルに

A1 * 2 

という式があり、下方向に式を繰り返しコピーしたとする。これを ARRAYFORMUAL で置きかえるには、

=ARRAYFORMULA(A:A * 2)

という書き方をした。元の計算は、A1 という単一のセルを対象にしている。ARRAYFORMUAL で置きかえるには、「単一のセル」ではなく、「範囲」を指定する。

組込み関数の場合も同じ。例えば、

POWER(A1, 2)

の式があり、下方向に繰り返しコピーされているものを、ARRAYFORMULA で置きかえるには、「単一のセル」を与えていた部分を「範囲」に変更した。

=ARRAYFORMULA(
	POWER(A:A, 2))

VLOOKUP 関数では、引数が多いのでやや複雑になった。

=vlookup(
	weekday(A2), 
	曜日,
	match("曜日名",曜日_属性, 0), 
	false)

第3引数のインデックスの配列を得るために工夫が必要。

=arrayformula(        
	vlookup(                
		weekday(
			offset(A2,0,0,rows(A:A)-1,1)),                 
		曜日,                
		match("曜日名",曜日_属性, 0)                 
			* sign(row(
				offset(A2,0,0,rows(A:A)-1,1))),                
		false))

まとめると、「計算の繰り返し」を ARRAYFORMUAL で置きかえるためには、元の式で「単一のセル」を対象にしていた部分を「範囲」に変更するということ。

 

3. SUM 関数の繰り返しを ARRAYFOMULA で置きかえることができなかった

では、これまでと同じように、SUM 関数の繰り返しを ARRAYFORMUALA で置きかえることができるか試してみる。

例えば、A ~ C 列の各行に対して、SUM 関数を用いて D 列に合計を表示したいとする。

D1 セルには以下の式を入力し、下方向へコピーを繰り返す。

=sum(A1:C1)

この「計算の繰り返し」を ARRAYFORMULA で置き換えるために、次のように記述してみた。

=arrayformula(sum(A:C))

しかし、残念ながら、結果は 1 つのセルに全てのセルの合計が表示されただけだった。

 

4. ARRAYFOMULA で置きかえるのではなく「行列の積」で考える

`google spreadsheets - How can I use ARRAYFORMULA and SUM together to get a column with the sum of the proceeding rows - Web Applications Stack Exchange’ には、SUM 関数の繰り返しを「行列の積」として計算する方法が述べられている。

上例の場合、次のように書くことができる。

=mmult(A:C,
	transpose(
		arrayformula(
			column(A:C)^0)))

ただし、これは行列の積を用いた方法であり、これまでのように SUM 関数の繰り返しを ARRAYFORMULA で置き換えるパターンとは違う。そのため、範囲を引数として受け取る MAX, AVERAGE のような関数に対して、同じような方法で対処できない。

 

a. ARRAYFOMULA を用いて、要素が全て1 の「行ベクトル」を求める

上記で ARRAYFORMULA を適用している部分は、行列の積において、要素が全て 1 の「行ベクトル」を生成していることに相当する。

行列の積の計算については、以下のサイトを参考にすると良い。

• 行列の積 - 高精度計算サイト

式の内容について、関数を１つずつ確認しておく。

arrayformula(
	column(A:C)^0)))

最初に COLUMUN 関数。COLUMN - Drive Help によると、

COLUMN(セル参照)

指定したセルの列番号を返します（A は 1 となります）。

例えば、column 関数に範囲を与えると、

column(A:C))

結果は 1 とのみ表示される。配列が返されるわけではない。 column 関数に範囲を与え、配列を返して欲しい場合、arrayformula 関数を利用する。

=arrayformula(column(A:C))

これにより、対応する結果が表示される。

これは VLOOKUP 関数を ARRAYFORMULA 関数に渡したときの、第 3 引数と同様。ROW, COLUMN 関数に範囲を与え、配列で返してもらうには、ARRAYFORMULA 関数と組み合わせる。

0 乗しているのは、全ての値を 1 にするための工夫。

arrayformula(column(A:C)^0))

冪乗 – Wikipedia によると、

正負を伴う累乗では、整数 x に対して、指数が正の整数 n であれば xⁿ となり、指数が 0 であれば 1 となり、指数が負の整数 −n であれば 1 / xⁿ となる。

 

b. TRANSPOSE 関数で行ベクトルを列ベクトルに変換する

次に、行列の積を行うために、上記で生成した要素１の行ベクトルを列ベクトルに変換する。行と列の入れ替えを行う関数は TRANSPOSE 。

TRANSPOSE - Drive Help によると、

TRANSPOSE(配列または範囲)

配列またはセルの範囲の行と列を入れ替えます。

transpose(
		arrayformula(
			column(A:C)^0)))

これにより、すべての要素が１の列ベクトができる。

最後に行列の積を計算する関数を適用する。

 

c. 行列の積を求める

行列の積を求める関数は MMULT 。

MMULT - Drive Help によると、

MMULT(行列1, 行列2)

配列または範囲として指定した 2 つの行列の行列積を計算します。

 

d. SIGN 関数でも、全ての要素が 1 の配列を求めることができる

ところで、0 乗の計算を行なっている部分は、VLOOKUP 関数を ARRAYFORMULA に渡したときと目的は同じ。全ての要素を 1 にすること。よって、0 乗の代わりに、SIGN 関数を用いても良い。

=mmult(A:C,
	transpose(
		arrayformula(
			sign(column(A:C))))

 

5. SUM 関数の繰り返しを「カスタム関数」で置きかえる

SUM 関数の繰り返しを ARRAYFORMULA で置きかえるのを諦めた。カスタム関数で置き換えるには、以下の関数を定義する。

function sumAry(ary1, ary2, ary3){
  var i, result = [];
  for (i = 0; i < ary1.length; i++){
    result[i] = [Number(ary1[i]) + Number(ary2[i]) + Number(ary3[i])];
  }
  return result;
}

• cf. Google スプレッドシートでカスタム関数を多くのセルで呼び出しエラーが表示される場合、配列を返す関数（配列数式）に変更する。または、新しいスプレッドシートを利用。

D1 セルには、次のように記入すれば良い。

=sumAry(A:A,B:B,C:C)

 

6. 数式のコピーを繰り返す場合と、行列の積で計算を行うときの比較

2013年12月以前の Google スプレッドシートを用いて、SUM 関数の繰り返しと、行列の積を用いた方法でどちらが多く計算できるか比較してみる。

コピーを繰り返す方法では、9,000 行まで数式数制限が表示されなかった。

行列の積を用いた方法では、12,000 行までエラーが表示されなかった。上記のカスタム関数を使った場合も同じくらい。

コピーを繰り返すよりも、行列の計算の方がより多く計算ができるようだ。

 

新しい Google スプレッドシートでは相当計算ができる

2013年12月以降の新しいGoogle スプレッドシートでは、コピーを繰り返した場合でも、200,000 行計算しても数式制限は表示されなかった。

Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (2) - VLOOKUP 関数の場合

• Google スプレッドシートで「数式の繰り返し」を ARRAYFORMULA で置きかえる (1) のつづき

1. 引数に「単一のセル」を与える組み込み関数の繰り返しを ARRAYFORMULA で置きかえる

値を 2 乗する POWER 関数の繰り返し

前回、演算子を利用した「数式の繰り返し」を ARRAYFORMULA で置き換えた。今回は「組み込み関数の繰り返し」を置きかえてみる。

例えば、A 列に入力した値を B 列で 2 乗する計算について考える。

べき乗を計算する「組み込み関数」は、POWER 。

A1 セルの値を２乗するには、B1 セルに

=power(A1, 2)

と入力する。 A 列の値に対して、繰り返し計算を行うには、B1 セルの数式を下方向へコピーする。

 

ARRAYFORMULA で置きかえる

上記の「関数の繰り返し」と同じ結果になるように、ARRAYFORMULA を利用して数式を置きかえたい。

そのためには、B1 セルに次のように記述する。

=ARRAYFORMULA(
	POWER(A:A, 2))

演算子のときと同じように、POWER 関数を呼び出すときに与えた「値」を「範囲」に置き換えた。それを ARRAYFORMULA に与えると、A 列にある各々の値に対して POWER 関数を適用したことになる。

 

2. VLOOKUP 関数の繰り返しを ARRAYFORMULA で置きかえる

VLOOKUP 関数の繰り返し

次に、データを参照するための関数 VLOOKUP の繰り返しを ARRAYFORMULA で置きかえる。

ここでは「日付から曜日を返す」例を用いる。書き方は以下を参照。

• VLOOKUP 関数による値の参照
• 表計算で VLOOKUP 関数の第３引数をハードコードしないで MATCH 関数の結果を与える

A 列に「日付」を入力すると、B 列に「曜日」が返る。１行目には「見出し」行がある。

「日付」を入力した列の隣、B2 セルには以下のように入力する。

=vlookup(
	weekday(A2), 
	曜日,
	match("曜日名",曜日_属性, 0), 
	false)

繰り返し計算を行うには、B2 セルの数式を下方向へコピー。

 

ARRAYFORMULA で置きかえる

さて、「関数の繰り返し」と同じ結果になるように、 ARRAYFORMULA を利用して数式を置きかえたい。

数式の書き方は、以下を参考にした。

• How to use VLOOKUP inside arrayFormula? - Google プロダクト フォーラム 
• vlookup - How can I apply a formula to new, form-inserted rows of data in Google Spreadsheets? - Stack Overflow
• How to use VLOOKUP inside arrayFormula? - Google プロダクト フォーラム

B2 セルに、次のように記述する。

=arrayformula(        
	vlookup(                
		weekday(
			offset(A2,0,0,rows(A:A)-1,1)),                 
		曜日,                
		match("曜日名",曜日_属性, 0)                 
			* sign(row(
				offset(A2,0,0,rows(A:A)-1,1))),                
		false))

 

VLOOKUP の第 1 引数

ARRAYFORMULA で置きかえた式は、直感的に分かりにくいので、1引数ずつ確認する。

最初に vlookup 関数の第１引数。元の呼び出しは、

weekday(A2),

ARRAYFORMULA に与えるには、「値」を「範囲」に変更する。

weekday(A:A),

ただし、今回の例では「見出し」行があるので、１行目を計算対象から除かなければならない。そのためには、範囲を返す OFFSET 関数を利用する。

• cf. OFFSET 関数で計算対象の「範囲」を得る

よって、以下のように記述する。

weekday(offset(A2, 0, 0, rows(A:A) - 1, 1)),

offset 内の ROWS 関数は、指定された範囲の行数を返す。

rows(A:A) - 1

と書くことにより、「見出し」を除いた高さを求めることができる。

 

VLOOKUP の第 2 引数

第２引数の「曜日」は、VLOOKUP で値を探す範囲。

これは変わらないので、そのまま与える。

 

VLOOKUP の第 3 引数

第３引数の元の呼び出しは、範囲「曜日_属性」から “曜日名” のあるインデックスを得た。

match("曜日名", 曜日_属性, 0),

ここでは具体的に 2 が返ってくる。

やっかいなのはこの引数。 ARRAYFORMUAL に与えるには、以下のように複雑な式となる。

match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

• offset 関数が返すのは、上記の weekday 関数に与えた式と同じく、日付が記入された列の「見出し」を除いた範囲。
• ROW 関数は、セルの行番号を返す。

全体の意味が分かりづらいので、 順を追って計算を確認する。

まず、ROW 関数を ARRAYFORMULA に与えたときの結果を表示してみる。

=arrayformula(row(A:A))

行番号に対応した値が表示された。

次に、SIGN 関数を計算に含める。

=arrayformula(sign(row(A:A)))

この関数は、「正の数なら 1 , 負の数なら –1 , 0 なら 0 」を返す。よって、行番号を与えると、行番号は正の数なので、全て 1 が返り、全体では 1 の配列が得られる。

ここで、ARRAYFORMULA の中で「任意の値を掛ける」計算を含めてみる。例えば、2 を掛ける計算を含めると、2 の配列が返される。

=arrayformula(2 * sign(row(A:A)))

つまり、第 3 引数に与えた計算は、match 関数で得られる値の配列を求めたことになる。

match("曜日名",曜日_属性, 0) * sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

繰り返すが、このように配列が必要なのは、ARRAYFORMULA 関数に与える関数の引数は、「値」ではなく「範囲」であるため。

 

VLOOKUP の第 4 引数

上記の第3引数で行なった計算を考えると、第4引数も同じような計算が必要に思えてくる。

しかし、この引数は元のままで変更する必要はない。なぜだろう？

 

3.  「新しい Google スプレッドシート」（2013/12 以降）

ところで、上記の「日付から曜日を返す」例を、「新しい Google スプレッドシート」（2013/12 以降）で試したところ、第3引数で必要だった

* sign(row(offset(A2, 0, 0, rows(A:A) - 1, 1))),

を付けなくても同じ結果が得られた。

=arrayformula(        
        vlookup(                
                weekday(
                        offset(A2,0,0,rows(A:A)-1,1)),                 
                曜日,                
                match("曜日名",曜日_属性, 0),                
                false))

うーん、なんでだろう。。(+_+)

「Google スプレッドシートで SUM 関数の繰り返しを「行列の積」で置きかえる」につづく…