ノンプロ研 初心者IT基礎講座スプレッドシート関数コース 第3期 第2回「数式を使った集計」学習メモ
はじめに
ノンプロ研で開催されていた、「初心者IT基礎講座スプレッドシート関数コース 」に参加しています。今回は、第2回「数式を使った集計」の講座の内容、学習したことについてまとめていきます。
1.SUMIFS
SUMIFS関数は、指定した条件に合うデータを合計する関数です。
第1引数に、合計対象範囲を、そのあとの引数には、条件範囲と条件を対にして設定していきます。
SUMIFS関数を使った例
単一条件
複数条件
単一条件、複数条件のどちらも、集計テーブルの見出し（この例の場合、E列）を参照しておくことで、数式内に直値（この場合、担当者名の「森田」さん、「近藤」さんなど）を書く必要がなくなります。
演習2-03
F2セルに、SUMIFS関数の数式を設定し、それをテーブルにコピペすることで、数式を１つずつ入力するのを避けるようにします。
この場合、絶対参照、行だけ絶対参照、列だけ絶対参照を組み合わせることで実現します。
合計対象範囲：C列の見出し行以降全行の絶対参照…$C2:$C
条件範囲1：A列の見出し行以降全行の絶対参照…$A2:$A
条件1：列だけ絶対参照…$E2
条件範囲2：B列の見出し行以降全行の絶対参照…$B2:$B
条件2：行だけ絶対参照…F$1
SUMIF関数とSUMIFS関数の混乱
以前から、単一条件で集計する場合、SUMIF関数、複数ならSUMIFS関数と使い分けていました。
SUMIF関数を使用していて、途中から複数条件に変更するような場合、SUMIFS関数に変更すると、引数の並び順が異なるため、毎回あたふたと混乱していました。
だったら、単一条件、複数条件で使い分けを行わず、いつでもSUMIFS関数に統一すればいい！の提案。なるほど！
2.COUNTIFS
COUNTIFS関数は、指定した条件に合うデータを件数を取得する関数です。
引数は、SUMIFS関数とほぼ一緒です。
条件範囲と条件を並べて設定できるので、直感的でわかりやすいです。
COUNTIFS関数も、単一条件、複数条件のどちらでも使用が可能です。
条件式の設定時の注意
同じ条件を設定するにしても、設定の仕方はさまざまあります。
下記の例だと、F列では条件を直値で、G列では条件をセルの参照で設定しています。
今後、条件の変更がある可能性もあるので、G列のように、条件をセルに設定しておき、そのセルを参照するようにしておくほうが変更箇所が少なくて済みます。
それから、複数条件を指定する場合、すべての条件で範囲を一致させる必要があります。
下記の例だと、1つ目の条件では、A2:Aとしているのに、2つ目の条件では、B2:B45としています。
セルには、エラー表示がされています。
エラーを解消するためには、2つ目の条件は、1つ目の条件と合わせて、B2:Bとする必要があります。
3.UNIQUE
UNIQUE関数は、参照範囲の重複を削除し、重複のないデータのみを取得することができる関数です。
非常に便利！
ポイントとしては、見出し行までを範囲に設定すること。
見出し行を含めたユニークなデータができます。
また、列全体を範囲に指定すると、データが格納されていない空白データまで含めて取得されてしまいます。
その際は、FILTER関数と合わせて使うと空白データを削除することができるそうです。なるほど！
unique関数で第1引数を列ごととったら、null値が入ってくる問題。
— keitaro@イベント企画大好き！！ DATA Saber/GCS認定コーチ (@MaskDePQuery) November 1, 2023
=UNIQUE(FILTER(H:H, H:H<>""))
これで対応できるんやで。#ノンプロ研 #スプシ講座 pic.twitter.com/AiFbavZJvv
4.構造化データ
集計しやすくするためには、構造化データにしておく必要があります。
構造化データと非構造化データ
テーブル
テーブルのルール
テーブルにしていると、見にくいな、というときには、DOVパターンによるシート構成をするとよいとのことでした。
テーブルを配置するシート、そのテーブルを参照して関数を使って演算するシート、人が見やすい形にフォーマットするシートに分けるとよいとのことです。
参照サイトとして、下記サイト紹介いただきました。
5.日付処理
業務の中で日付データはよく出てきます。
日付は、Googleスプレッドシートで扱う4つのデータ型の1つ、数値型になります。
シリアル値と呼ばれる数値で表現され、表示形式を変更することで、日付として表示することができます。
日付に関連する関数
これ以外にもたくさんあるとのこと。
下記サイトでは、日付関数の解説や、使用したいケースごとの関数のレシピが載っています。
6.条件付き書式
条件付き書式とは
使用する機会が多いのが、曜日のセルに土日だったらフォントの色を変える、日付が締め切りを過ぎている場合、背景色を変える、など。
講座の宿題にあった、下記の3つ設定方法をまとめていきます。
曜日のセルが土曜日だったら青、日曜日だったら赤にフォントを切り替える
血圧（上）のセルにカラースケールを設定
退会のセルにチェックがついている場合、行全体をグレイの背景色に設定
条件付き書式の設定方法
曜日が記載されている範囲（もしくは列）を選択します。
2．メニューから「表示形式」→「条件付き書式」をクリックします。
3.「条件付き書式設定ルール」の「条件を追加」をクリックします。
4.「条件付き書式ルール」の「単一色」タブにて、「書式ルール」の「セルの書式設定の条件」をクリックして、「次と等しい」をクリックします。
5．値に「土」（もしくは「日」）を入力します。
6．「書式設定のスタイル」より、「テキストの色」をクリックし、カラーパレットから、それぞれ曜日に合わせた色を選択します。（土曜日なら青、日曜日なら赤）
次に、「塗りつぶし」をクリックし、カラーパレットから「なし」を選択します。「完了」をクリックして、設定完了です。
カラースケールの設定方法
血圧が記載されている範囲（もしくは列）を選択します。
2．メニューから「表示形式」→「条件付き書式」をクリックします。
3.「条件付き書式設定ルール」の「条件を追加」をクリックします。
4．「カラースケール」をクリックします。
5．最小点の塗りつぶしをクリックし、カラーパレットから白を選択します。
次に、最大点の塗りつぶしをクリックし、カラーパレットから赤を選択します。
完了をクリックして、設定完了です。
行全体の条件付き書式の設定方法
見出し行以外テーブル全体の範囲を選択します。
2．メニューから「表示形式」→「条件付き書式」をクリックします。
3．「条件付き書式設定ルール」の「条件を追加」をクリックします。
4．「条件付き書式ルール」の「単一色」タブにて、「書式ルール」の「セルの書式設定の条件」をクリックして、「カスタム数式」をクリックします。
5．「値」には、その行を塗りつぶすときに参照する条件式を設定します。今回は、G列「退会」にチェックが入っている場合とするため、「=$G2」とします。
重要な点は、下記です。
条件式なので、＝から始める
列だけ参照に設定する（$G）
判定するセルは、選択範囲の行のうち一番上の行番号を設定する（$G2）
6．「書式設定スタイル」から「塗りつぶし」をクリックし、カラーパレットからグレイを選択します。
「完了」をクリックして、設定完了です。
講座ポストまとめ
講座では、記憶定着化のため、アウトプットすることが推奨されています。講座中のポストまとめは、下記です。
まとめ
今回は、ノンプロ研 初心者IT基礎講座スプレッドシート関数コース 第3期 第2回「数式を使った集計」について、講座内容と学習のメモまとめました。
講座は2回と短い講座でしたが、学びの多い講座となりました。
今までやりたいことがあれば調べて、なんとなく関数を使っていたのですが、講座内でしっかり学ぶことができ、またこう使ったら便利だよ、というTipsもたくさん教えていただけました。
目次
この記事が気に入ったらサポートをしてみませんか？