Googleスプレッドシートで便利な関数を使う方法

TL;DR

Googleスプレッドシートでは、検索にVLOOKUP、条件別集計にSUMIFS、小計を含む集計にSUBTOTAL、行抽出にFILTERを使い分けます。SUMPRODUCTとINDIRECTも含む6関数を、構文と実際の操作例から理解すると、目的に合う処理を組み立てられます。

Transcript

[音楽] みなさんこんにちは リモートワーク研究所いえどもです 本日はスプレッドシートで知っておくべき 関数6選と題して思ったりします スプレッドシートをもっと使いこなしたい でもう関数が多すぎて何から思えるべきか 分からないこういう方多いんではない でしょうか ご安心ください スプレッドシートも作成経験多数の私が フレッドシートを使いこなすために知って おくべき関数をお伝えします この動画を見ることによって スプレッドシートの中でも特に知っておき たい関数が分かります これらの関数を使えるようになることに よって仕事の効率アップ間違いなしですの で是非最後までご覧頂ければと思います また内容ちょっと難しいかもしれないので 是非チャンネル登録しておいてもらえると 見返しやすくていいかなと思い... Read More

Key Insights

• VLOOKUPは、指定範囲の最左列を上から検索し、一致した行にある指定列の値を返す関数である。氏名を手掛かりに、別シートから年齢、性別、居住地などを取得する用途に使える。
• VLOOKUPの検索範囲は、探したい値が必ず最左列に位置するよう設定する必要がある。返す列番号はシート全体の列番号ではなく、指定した検索範囲の左端を1列目として数える。
• VLOOKUPのFALSEは検索値との完全一致を要求し、一致しなければエラーになる設定である。TRUEは数値検索で検索値を超えない最も近い値を返すため、完全一致が必要かどうかで使い分ける。
• SUMIFSは、合計範囲の後に条件範囲と条件を一組ずつ追加し、すべての条件に一致した値だけを合計する関数である。担当者と月を同時に指定する売上集計などに利用できる。
• 絶対参照は、数式を縦横へコピーしても参照範囲を意図せず移動させないための仕組みである。行だけ、列だけ、または行列の両方を固定すると、集計表を効率よく展開できる。
• SUBTOTALは、指定番号によって合計、平均、件数、最大値など異なる集計を実行できる関数である。合計には9を指定し、集計対象となるセル範囲を続けて入力する。
• SUBTOTALは、集計範囲内に存在する別のSUBTOTALによる結果を自動的に無視する関数である。月別小計を含む範囲から総合計を求めても、小計の二重加算を避けられる。
• FILTERは、指定範囲から条件に一致する複数行をまとめて抽出し、別の場所へ展開する関数である。元データの表示を直接絞り込まず、抽出先で追加の計算や確認を行える。
• 🎬 このチャンネルの他の動画:
  • Windows 11の動作を軽くする7つの方法
  • Gmailの受信トレイを効率よく整理する方法
  • Power AutomateでPC作業を自動化する方法
• 📺 このチャンネル: いけともch

Questions & Answers

Q: VLOOKUP関数で別シートの情報を取得する方法は？

検索値、検索範囲、返したい列の番号、検索方法の順に指定します。たとえば氏名から居住地を取得する場合は、氏名が最左列になる範囲を選び、その範囲内で居住地が何列目かを入力します。該当する氏名の行が見つかると、指定列の居住地が返されます。

Q: VLOOKUP関数の検索範囲を設定する際の注意点は？

検索する値が指定範囲の最左列に入るよう設定することが重要です。検索対象の氏名やIDが範囲の途中の列にあると、説明された使い方では正しく検索できません。また、返す列番号は指定範囲の左端から数えるため、範囲を変更した場合は列番号も確認します。

Q: VLOOKUP関数のTRUEとFALSEの違いは何ですか？

FALSEは検索値と完全に一致する値だけを対象とし、一致する値がなければエラーになります。TRUEは数値を検索した場合、検索値を超えない最も近い値を返す設定です。文字やIDを厳密に照合したい場合はFALSE、数値をある程度幅を持たせて探す場合はTRUEを使います。

Q: SUMIFS関数で複数条件の合計を求める方法は？

最初に合計したい数値の範囲を指定し、その後へ条件範囲と条件を一組として追加します。たとえば売上列を合計範囲、担当者列と担当者名を第1条件、月の列と対象月を第2条件にすると、指定した担当者かつ指定月に該当する売上だけを合算できます。

Q: 数式をコピーしたときに参照先がずれるのを防ぐ方法は？

固定したい行番号や列記号にドル記号を付け、絶対参照または複合参照にします。集計元の範囲は行列とも固定し、担当者名は行だけ、月は列だけを固定すると、数式を縦横へコピーしても担当者と月が意図した方向にだけ切り替わる表を作れます。

Q: SUBTOTAL関数で小計の二重集計を防ぐ方法は？

各小計と総合計の両方をSUBTOTALで計算します。SUBTOTALは、指定範囲内にある別のSUBTOTALの結果を自動的に集計対象から外すため、月別小計を含む範囲全体を総合計の対象にしても、小計と明細を重ねて足す二重集計を避けられます。

Q: SUBTOTAL関数で合計や平均を切り替える方法は？

SUBTOTALの最初の引数で集計方法を指定します。説明例では9が合計、1が平均、2がデータ件数として使われ、番号を変更すると同じ範囲に対して異なる集計結果を得られます。その後に集計対象の範囲を指定し、必要な指標に応じて番号を選びます。

Q: FILTER関数で条件に合う行を別シートへ抽出する方法は？

最初に元データから持ってきたい列範囲を指定し、次に担当者列が特定の氏名と等しい、といった条件式を設定します。条件に一致する行は、FILTERを入力したセルを起点としてまとめて展開されます。条件値を別セルから参照すれば、そのセルの変更だけで抽出対象を切り替えられます。

Summary & Key Takeaways

• データ検索にはVLOOKUPを使います。検索値、検索範囲、返す列番号、検索方法を指定し、検索値が範囲の最左列に来るよう整えます。完全一致はFALSE、数値を曖昧に検索して検索値以下の最大値を返す場合はTRUEを使います。横方向へ式を複製すると参照範囲がずれる点にも注意が必要です。

• 集計ではSUMIFSとSUBTOTALを使い分けます。SUMIFSは合計範囲に続けて条件範囲と条件を組にして指定し、担当者や月など複数条件に合う売上だけを合算できます。SUBTOTALは集計方法を番号で指定し、範囲内にある別のSUBTOTAL結果を無視できるため、月別小計を含む表の総合計で二重集計を防げます。

• 抽出にはFILTERを使い、元データの対象範囲と条件式を指定して、一致する複数行を別シートへまとめて展開します。担当者名をセル参照にすれば、値の変更だけで抽出結果を切り替えられます。動画ではこのほかSUMPRODUCTとINDIRECTも取り上げ、合計6関数の機能、構文、操作方法を順に紹介しています。