新しいスプレッドシートの変更点確認（2014/4/25）

Google Appsが4/25に大幅アップデートされ、これまでの旧スプレッドシートとは異なり、メジャーバージョンアップということで仕様が変更されている。現状、どのような改変が行われ、既存のファイルへの影響や、新しいスプレッドシートではどのような追加が行われているのか？把握する必要性が出てきたので、ここにその項目を追記する

なお、これから新規作成する場合のスプレッドシートは全て、新しいスプレッドシートとして作成される為（新規作成だろうとコピー作成であろうと）、旧バージョンで作成したい場合には、ここにアクセスする必要性がある。しかし、新しいスプレッドシートのほうが読込や数式解析のスピード、スクロールに関してもスピードアップしている為、あえて旧式で作るメリットは非常に少ないと言える。なお、今回サンプルで作成したスプレッドシートはこちらにアップロード済み。

なお、現在使用しているスプレッドシートが新しいスプレッドシートなのかそうでないのかの判断は、右下にある緑色のアイコンが目印となります。ここをクリックして下記のような表示がなされれば、それは新しいスプレッドシートを持って作成されたものであるとわかります。

根幹部分での機能追加

シートの見出しに色を設定する

シートタブに色を付けられるようになっている。シート名の横の▼をクリックし、[色変更]で可能。カスタムカラーも付けられる。下の図では、２つのシートタブに赤と青の２色を設定している。

行列を入れ替えて貼り付け機能

Excelでは大分前から装備されていた機能ですが、範囲指定をしてコピーした内容の行と列を入れ替えて貼り付ける機能が装備されています。Google Spreadsheetの場合には、転置して貼り付けるという項目がそれに該当する。すると、範囲してした範囲の列の部分が行に、行の部分が列になって貼り付けられるので、これでデータの置き換えが楽になるかもしれません。

カスタムな日付・数式・時刻の表示が可能

これまでは、Spreadsheetに用意されていた形式が利用できなかった表示に関する形式が、自由に設定出来るようになっています。例えば、2000/12/31という値に対して、yyyy"年"m"月"d"日"ddddという式を設定して上げれば、2000年12月31日日曜日というような表示が可能になる。

新タイプのメニューを追加できるようになった

新しいスプレッドシートでは、これまで実現できなかったサブメニューが実装出来るようになっています。既にメニューを２つ用意することは旧スプレッドシートでも出来ていましたが、サブメニューまで扱えるようになると、多機能なスクリプトを実装しても、スッキリコンパクトにまとめることが可能になるので、とても良い機能です。

サンプルコード

function onOpen(e) {

  var ui = SpreadsheetApp.getUi();

  // Or DocumentApp or FormApp.

  ui.createMenu('自分用メニュー')

      .addItem('銀ちゃん', 'gintoki')

      .addSeparator()

      .addSubMenu(ui.createMenu('２個めのメニュー')

          .addItem('サブちゃん', 'sasakiisaburou')

          .addItem('ドラえもん', 'doraemon'))

      .addToUi();

}

その他重要な根幹部分でのアップデート（廃止項目も）

• 読込およびスクロールスピードの向上
• 数式の複雑さに関する制限や、スプレッドシートに設定されていた各種制限（セルの数やコピーできる値の数など）が解除
• データが含まれるセルは200万個まで制限が緩和された。データ蓄積型のシートなどで、セル数の上限に達してしまっていたようなケースでは、これで大分延命できる。
• 関数の入力に関して、Excelの自動補完やVisual Studioのインテリセンスのように自動補完してくれるようになった。
• Ctrl+Y（Macは⌘+Y）キーというショートカットキーで、直前の操作を繰り返せる。例えば色の変更だったり、行の挿入の繰り返しをこのキーで行えるようになっている。
• HYPERLINK関数をいちいち記述しなくても、リンクを設定することが可能になった。
• 正規表現を利用してのスプレッドシート内検索が可能になった。
• スプレッドシートに於いて、Ctrlキーを使った、複数の範囲指定がサポートされた。
• Google Apps Scriptにてサイドバー機能を使えるようになった。
• Google Apps Scriptにてアドオンを作成・公開出来るようになった。
• Google Apps Scriptに於いて、ダイアログの扱いが少し変更になっている。
• Google Apps Scriptに於いて、ScriptDBが廃止指定されました。

関数関係の変更

関数が幾つか新しく追加サポートされていると共に、いくつか削除されています。しかし、削除されている関数は殆どこれまでもこれからもユーザに影響のあるものではなく、また影響が出る関数に関してはきちんとフォローが入っているので、安心して移行することが出来ると思います。今回追加されている関数は、ARRAY_CONSTRAIN、ARRAY_LITERAL、ARRAY_ROW、CELL、CLEAN、DELTA、ISEMAIL、ISURL、TIMEVALUE、LOOKUP、PERCENTRANK.EXC、PERCENTRANK.INC、RANK.AVG、RANK.EQ、TYPE、WEEKNUM、SUMIFS、COUNTIFS、AVERAGEIF、AVERAGEIFS,NETWORKDAYS.INTL、WORKDAY.INTL、SEARCHB、FINDB、TDIST の25関数。削除されてしまった関数は、CONTINUE, GOOGLECLOCK, EXPAND, NOEXPAND, GOOGLETOURNAMNTの5つです。Continue関数は影響がありそうでしたが、Filter関数で使ってみましたが、Continue関数を使っていた場所は、通常の値が入るように変更されていました。

SUMIFS関数

複数条件に合致する要素の合計値を計算するための関数である。単一条件であれば、SUMIF関数を使用するが、複数の条件の場合には、この関数が最適である。なお、条件は範囲で指定ではなく、数式の中に直接記述するタイプなので、非常に扱いやすい（もちろん、セル参照を行うことも可能である）。

数式の例

=sumifs(C4:C8,A4:A8,"くだもの",D4:D8,"あり")

数式の書き方

青い範囲指定： 合計値を計算したい範囲を指定する（C列が金額で金額の合計を取りたい場合）

赤い範囲指定： １つ目の条件範囲を指定する（今回はA列を指定）

オレンヂ色の条件式：赤い範囲指定した１つ目の条件範囲の中で抽出する条件式もしくはワードを入れる

緑色範囲指定：２つ目の条件範囲を指定する（今回はD列を指定）

藍色の条件式：緑色範囲指定した２つ目の条件範囲の中で抽出する条件式もしくはワードを入れる

※それぞれ、カンマで区切り、３つ目４つ目と続けて条件を追加していくことが出来る。

※応用として、１つ目２つ目を同じ範囲を指定し、それぞれに違う条件式を入れることで、特定の範囲（以上未満、以下より上などのレンジに合致する要素）に当てはまる要素を抽出し合計することが可能。

参考URL

• ExcelのSUMIFS関数の使い方

COUNTIFS関数

複数条件に合致する要素の個数をカウントするための関数である。単一条件であれば、COUNTIF関数を使用するが、複数の条件の場合には、この関数が最適である。なお、この関数も条件は範囲で指定ではなく、数式の中に直接記述することができるので扱い易い（セル参照も可能である。）。

数式の例

=countifs(A3:A7,"くだもの",D3:D7,"あり",C3:C7,">350")

数式の書き方

青い範囲指定： １つ目の条件範囲を指定する（今回はA列を指定）

赤い条件式： １つ目の条件範囲に対する条件式もしくはワードを入れる

オレンヂ色の範囲指定： ２つ目の条件範囲を指定する（今回はD列を指定）

緑色条件式：２つ目の条件範囲に対する条件式もしくはワードを入れる

藍色の範囲指定：３つ目の条件範囲を指定する（今回はC列を指定）

黄色の条件式：３つ目の条件範囲に対する条件式もしくはワードを入れる

※COUNTIFSは条件範囲、条件式という形のみで数式を書いていくのでSUMIFSよりは複雑さでは簡単になってる

※今回は３つ目の黄色の条件式で比較演算子を使用しているが、数式であってもダブルコーテーション（” ”）で括るのを忘れずに

参考URL

• ExcelのCOUNTIFS関数の使い方

AVERAGEIF関数

これまで、Google SpreadsheetにはAverageifに関する関数がなかったが、今回よりAVERAGEIFおよびAVERAGEIFS関数が使用可能になっている。AVERAGEIF関数は、単一条件に合致する要素の平均値を計算する関数である。条件範囲も条件式も範囲指定で対応することが可能である。

数式の例

=averageif(A3:A7,"野菜",C3:C7)

数式の書き方

青い範囲指定： 条件の対象になる範囲を指定する。

赤い条件式： 青い範囲指定に対する条件式を入れる。

黄色の条件式：平均値を計算する範囲を指定する。

参考URL

• ExcelのAVERAGEIF関数の使い方

AVERAGEIFS関数

複数条件に合致する要素の平均値を計算するための関数である。単一条件であれば、 AVERAGEIF関数を使用するが、複数の条件の場合には、この関数が最適である。なお、この関数も条件は範囲で指定ではなく、数式の中に直接記述することができるので扱い易い（セル参照も可能である。）。

数式の例

=averageifs(C3:C8,A3:A8,"くだもの",D3:D8,"あり")

数式の書き方

青い範囲指定： 平均値を出す対象の範囲を指定する（今回はC列を指定）

赤い範囲指定： １つ目の条件範囲を指定する（今回はA列を指定）

オレンヂ色の条件式： １つ目の条件範囲に対する条件式を指定

緑色範囲指定：２つ目の条件範囲を指定する（今回はD列を指定）

藍色の条件式：２つ目の条件範囲に対する条件式を指定

参考URL

• ExcelのAVERAGEIFS関数の使い方

非常に大きな機能の追加

条件付き書式設定に数式が追加

これまで搭載されていなかった条件付き書式設定に関して、ついに「数式」での条件指定が出来るようになりました。固定値ではなく変動値を条件付き書式の値として利用することが出来るようになるだけではなく、計算式の結果を持ってして条件付き書式の指定を行うことが出来るようになるため、スクリプトで対応していたようなアクションはこれでほぼ消えることになると思います。

今回使用した条件付き書式設定には、AND関数で複数の条件式をAND条件で特定のセルを参照させて色分けしている。

1. =and(B6<=$A$2,B6>$B$2)
2. =and(B6<=$B$2,B6>$C$2)
3. =and(B6<=$C$2)

また、この条件付き書式設定に於ける数式を使ったものは、少々クセがあり、数式の基準点となる範囲の右上（今回ならB6）が全ての範囲内の条件付き書式設定に入ってる。セルのドラッグと同じで、相対パスで指定されるので、この書き方になっている。よって、個別のセルにいちいちセル番地を変えて書く必要性はない。また、閾値を設定したセルは固定なので、$A$2といったようにセル参照を固定してある。また、数式はどんなものでも使用できるので、自作の関数でもなんでも結果がTRUEなりFALSEになるような関数ならば、使い方次第で複雑な数式を組むことも可能である。

※但し、条件付き書式設定のカスタム関数内で、他のシートを直接参照するような式は使えない。なので、INDIRECT関数を使用しなければならない。（例：=N2>=INDIRECT("'設定'!A1"))。設定シートのA1の値をこれで取得できるが、ダブルコーテーションで括った中で、他のシートのセル参照を入れるのがポイント。これで、他のシートの値を条件付き書式の値として使用することができる。

⇒参考URL(SpreadsheetsでGAS無しで条件によって行に色つける)

※また、複雑な計算式で構築した自作関数だと遅くて判定ができずに、条件判定が出来ないことがある。

オフラインアクセス機能

Google Chrome限定になりますが、スプレッドシートやスライドなどに対して、オフラインアクセス機能が追加されています。しかし、これ自分で有効にしないと利用できませんので要注意。オフラインアクセス機能の美味しい点は以下の通り。

1. 自分がよく使うファイルをオフラインでも利用することが可能。電波の届かない場所でも継続してファイルの編集が可能
2. 4000アイテム、5GBまでオフラインで扱うことが出来る。
3. オンライン接続時に編集結果がマージされる仕組みとなっている。
4. WindowsだけでなくMacOSXでもAndroid、iOSでもChromeが使える環境であれば対応。
5. 表計算ファイルだけじゃなくプレゼンテーションファイルも行けるので便利。

但しデメリットもある。オフライン状態で長く編集したものをマージする際には、共有している他のユーザにも多大な影響がある。また、はじめにオフライン編集を有効にするボタンを押すと、よく使用しているファイルの同期が行われる為、かなり長い時間、ミラーリング作業が行われる為、電源を切ることができなくなる。余裕のある時にやるべきだと思われる。

カスタムフィルタビューを作れる

これまで、スプレッドシート上でのフィルタ機能は全ユーザ共有でしたので、誰かが設定すると全員のフィルタも変化してしまっていました。新バージョンからはユーザ毎にフィルタの設定が出来るようになっているので、他の人に影響を与えることなくスプレッドシートにフィルタを設定することが可能になっています。もちろん、作成したフィルタを誰かと共有することも可能である。

これまでの「フィルタ」は、全員に影響がありますが、フィルタ表示 > 新しいフィルタ表示の作成で作成されたものは、保存しておくことも可能ですが、他人からは見えません。

アドオン追加機能

誰かが作成したり、Third-partyが作成したスプレッドシート用アドオン機能が搭載され、簡単にスプレッドシートに機能を追加することが出来るようになっています。まだまだ数は少ないですが、Google Driveにて「アプリを追加」出来るように、複雑な関数やスクリプトなどを機能として持てるのは嬉しい限り。もともと、スクリプト側には、他人のライブラリなどを参照する機能はありましたが、これはそれよりもよりアプリケーションよりの機能と言えます。

気がついたバグらしきモノ

1. ワープロ（Google DocumentやSites）に於いて、箇条書きや番号付きリストボタンを押した時に、日本語入力の英字とカナ入力の切り替えが出来ずに、英字入力固定のままになる現象。
2. スプレッドシートに於いて、スクリプトを実行するとまだスクリプトが実行途中にも関わらず、最後までスクリプトが実行されてメッセージが先に表示される。
3. スプレッドシートに於いて、行列固定の為の$をつけたセルをドラッグした場合、勝手に固定したくない所にも$がつく。ドラッグ後に値の修正が必要。

参考URL

• 新しい Google スプレッドシートについて
• 新しい Google スプレッドシートでサポートされていない機能
• 条件付き書式設定ルールを適用する
• Google ドキュメント、スプレッドシート、スライド、図形描画のオフライン機能について
• Google スプレッドシートのサイズと複雑さの制限
• Google スプレッドシートの関数リスト
• Custom Menus in Google Apps
• スプレッドシートの新しい Google スプレッドシートへの移動