【小技集】意外と知られていないGoogleスプレッドシートを便利にする技まとめ

spreadsheet
Photo by barnimages.com – Working with MacBook outside / Adapted.
エクセルをインポートして変換したり、みんなで共有してリアルタイムに編集したりと、色々と便利なスプレッドシートですが、関数やプルダウン機能、縦書き機能など、使いこなせばもっと便利に使えます。

さらに独自の便利なメニューを追加したり、作業効率をアップするショートカットや、おすすめアドオンなど、実務で使えれる便利な小技集を厳選して紹介します。

Googleスプレッドシートとは

Excelのような表計算や、グラフ作成ができます。オフライン設定もあります。
強力なGoogleスプレッドシート独自の関数やスクリプト等の機能があり、速度こそエクセルに劣りますが、強力なビジネスツールです。

1.複数のセルを「,」で連結したい時に使える関数「ARRAYFORMULA」&「CONCATENATE」

spreadsheet_arrayformula

=concatenate(arrayformula(配列&","))

私は結構な頻度で使っている関数です。ARRAYFORMULA(配列&",")で複数セルに「,」を一括でつけて、CONCATENATE(配列)で複数セルを一つの文字列にします。「,」の部分は自由に変えれます。

2.参照範囲をリアルタイムで配列集計できる関数「QUERY」

spreadsheet_query

=query(参照範囲,"select 列名,count(カウントしたい列名) group by 重複無しにしたい列名")

やや覚えるのが難しい関数ですが、自動集計なので便利です。定期的に必ず集計しないといけない場合などは、エクセルだとcountifやsumifなどでしている項目が増えるたびに修正が必要で手間ですが、SpreadsheetのQUERY関数を使えばピボットテーブルの要領で使えます。

3.選択範囲をリアルタイムで重複削除できる関数「UNIQUE」

spraedsheet_unique

=unique(参照範囲)

エクセルだと重複削除は都度処理が必要ですが、ご覧の通り自動で出来ます。

4.参照範囲をリアルタイムでフィルタできる関数「FILTER」

spraedsheet_filter

=filter(参照範囲,列 比較演算子 条件)

参照範囲から特定の条件でリストを抽出したい場合などに使っています。
通常のフィルターは、反映にフィルタの掛け直しが必要ですが、リアルタイムに反映できます。
フィルタをかけた状態で普通にSUMをするとフィルタ外も集計されますが、この関数を使えばフィルタ結果のみ集計できます。

5.選択範囲をリアルタイムでソートできる関数「SORT」

spraedsheet_sort

=sort(参照範囲,列位置,昇順(1)・降順(0))

6.スプレッドシートでのプルダウン(入力規則)

spreadsheet_pulldown1
・範囲指定でのプルダウンで項目選択
・直接入力のプルダウンで項目選択
・名前範囲のプルダウンで項目選択
・指定項目以外の入力許可、拒否設定

などなど豊富な設定方法が用意されています。設定は上記の通り簡単です。

7.プルダウンのカレンダー選択で、当月以外を入力不可設定

spreadsheet_pulldown_calendar

=date(year(today()),month(today()),1) と =edate(date(year(today()),month(today()),1),1)-1

当月の日付でしか入力が出来なくする設定で、当月分までの入力を必ず当月中にしてもらう時に有効な設定です。

他にも下記の設定ができます。
・有効な日付(日付として認識できる入力)
・次の日と等しい
・次の日より前
・次の日以前
・次の日より後
・次の日以降
・次の日の間にある
・次の日の間にない

8.スプレッドシートで文字を縦表示にする

spreadsheet_tate

function tate() {
  var range = SpreadsheetApp.getActiveRange();
  var cells = range.getValues();
  var rowLen = range.getNumRows();
  var columnLen = range.getNumColumns();
  for (var i = 0; i < rowLen; i++) {
    for (var j = 0; j < columnLen; j++) {
      if (typeof cells[i][j] == 'string') {
        
        
        Logger.log(cells[i][j]);
        cells[i][j] = cells[i][j].replace(/(.{1})/g, "$1n");
        cells[i][j] = cells[i][j].replace(/(n)$/g, "");        
        Logger.log(cells[i][j]);
      }
    }
  }
  range.setValues(cells);
}

function yoko() {
  var range = SpreadsheetApp.getActiveRange();
  var cells = range.getValues();
  var rowLen = range.getNumRows();
  var columnLen = range.getNumColumns();
  for (var i = 0; i < rowLen; i++) {
    for (var j = 0; j < columnLen; j++) {
      if (typeof cells[i][j] == 'string') {
        
        
        Logger.log(cells[i][j]);
        cells[i][j] = cells[i][j].replace(/n/g, "");
        Logger.log(cells[i][j]);
      }
    }
  }
  range.setValues(cells);
}



function onOpen(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menus = [{name: 'たて表記(改行を入れる)', functionName: 'tate'},
               {name: '改行削除', functionName: 'yoko'},
              ];
  ss.addMenu('追加メニュー', menus);
}

上記コードは、スプレッドシートは文字を縦表示にできないので、改行を1文字ずつ入れる独自メニューです。
そのため複数行の横文字を縦表示にしようとすると1行になってしまいますが、それ以外は手軽に使えます。

まとめ

Google スプレッドシートは自動集計や、複数人に共有して同時に入力してもらう時に、エクセルにはない便利な機能があ沢山あり、とても便利です。

他にも使える便利な機能・コードがあれば追加していきます。

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です