スプレッドシート集計関数について

今回はGoogleスプレッドシートの生データを貼り付けた後に集計でよく使いそうな
下記の関数を紹介します！
IMPORTRANGE (インポートレンジ)・・・別スプシのデータを取得
UNIQUE(ユニーク)・・・重複削除
FILTER(フィルター)・・・関数でフィルタ
ARRAYFORMULA(アレイフォーミュラ) ・・・関数のコピー

IMPORTRANGE関数について

IMPORTRANGE関数は他のスプレッドシートを参照する関数です。
Excelの場合はフルパスで書いてあって参照先が移動した際リンクの更新が出るのでちょっと使いづらいですよね。

GoogleスプレッドシートはGoogleドライブ上のファイルIDで紐付けするので
フォルダを移動してもリンクが切れることが有りません。

その為、元データは1ヶ所に設定して他のスプレッドシートはそのデータを参照するような仕組みを作ることができます。

使い方

スプレッドシートはファイルIDが付いていて下記アドレスのオレンジ枠の部分がIDとなっています。

どのシートかも必要なのでシート名もチェックしておきます。

この部分は変更してしまうとリンクが切れるのであらかじめ決めておきましょう。

取込シート1の下記データの取得を行います。

新しいスプレッドシートに=importrange( と入力するとURLと範囲の文字列の設定が表示されます。

スプレッドシートのURLはファイルIDのオレンジの部分です。
範囲の文字列は取り込みたいシート名!範囲を指定します。
行がどこまでかわからない場合はA1:GとGの後ろの数値を指定しないでおくと
行の判定が自動になります。この機能もめちゃ便利！

リンク先のスプレッドシートのアクセスを許可します。

データが反映されました！！特に重くもなくすぐにデータは連携されます。

UNIQUE関数について

ユニーク関数は指定した範囲の重複を除いた結果を返します。

先ほど取得したデータの性別の重複削除した結果を取得してみます。

結果はこの通りで重複を削除した2行のデータを取得することができました。
titleを除く場合はD:DではなくD2:Dとするとタイトルを除いた結果を返すことができます。

FILTER関数について

フィルター関数は範囲を指定して抽出条件を入れるとその結果のみの行を表示することが出来ます。
下記の例ではD列がmenの行だけ抽出するようにしています。

指定した条件のみの結果が表示されました！

ARRAYFORMULA関数について

ARRAYFORMULA関数は普段１つのセルを指定している部分を複数セルの配列として取得して
結果を複数個所に出力できる関数です。

サンプルではARRAYFORMULA VLOOKUPを使った使い方を紹介します。

使い方ですがまず通常の関数の使い方で作成します。

結果が出た後下へコピーすればよいのですが
ARRAYFORMULAで囲って検索値はコピーしたい位置までの範囲にします。

これだけで関数がコピーされます。

次のは値が入ってますがこれはARRAYFORMULAの結果を範囲で返してるだけです。

下の方はどうなってるかというとコピーしたいところよりも下まで結果が表示されています。
これは検索値をI2:Iとした為、下はどこまででも出すようになってしまっています。

関数の結果をちょうどで止めるには？

範囲を変動させるようにするにはINDIRECT COUNTA関数を組み合わせて使います。
最終行の値をcountaで算出して文字列を範囲に変える為、INDIRECT関数を使って範囲に変換を行います。
最終的な関数はこんな感じです。
=ARRAYFORMULA(vlookup(I2:indirect(“I” & counta(I:I)),C:G,5,0))

最終行も隣のセルと同じように止めることが出来ました！

まとめ

いかがでしたでしょうか。
Excelでも365のバージョンでは配列で結果を返す関数が増えてきていますが
Googleスプレッドシートだと色んな関数が有って使いこなすと集計が捗りますよね。

今回は集計で使いそうなGoogleスプレッドシートで使いそうな関数を厳選してみました。