スプレッドシートの作業効率が劇的にアップ！実務で役立つ主要関数を徹底解説

spreadsheet

Posted at 2025-04-24

Googleスプレッドシートを使いこなす上で、関数は欠かせないツールです。しかし、関数の種類は多すぎて「どれから覚えればいいの？」と迷ってしまう方も多いのではないでしょうか。

この記事では、スプレッドシート作成経験豊富な専門家が、特に実務でよく使う、知っておくと仕事の効率が格段にアップする関数を厳選してご紹介します。それぞれの関数の機能、具体的な使用例、式の書き方とその意味を、初心者の方にも分かりやすいように詳細に解説します。

このガイドを読めば、データの集計や抽出、管理が簡単になり、日々の業務時間を大幅に削減できるはずです。ぜひ最後までご覧ください。

関数の基本：絶対参照と相対参照

関数をコピーする際に非常に重要になるのが、セルの参照方法です。「相対参照」と「絶対参照」、そして「混合参照」という考え方を理解しておきましょう。

相対参照: セルをコピーしたときに、参照先が自動的にずれる標準の参照方法です。例えば、セルB1でC1を参照していた式をD1にコピーすると、参照先はE1に自動的に変わります。これは、元のセルから見て「右に1つ離れたセル」という相対的な位置関係が保持されるためです。
絶対参照: セルをコピーしても、参照先が一切ずれない参照方法です。参照したい行と列の両方に$マークをつけます。例えば、$C$1のように指定します。これにより、参照先が固定され、コピーしても常に同じセルを参照します。
混合参照: 行または列のどちらか一方だけを固定する参照方法です。例えば、$C1と指定すると列は固定されますが行はずれ、C$1と指定すると行は固定されますが列がずれます。

絶対参照を設定するには、数式バーで参照したいセルを選択した状態でF4キーを押すのが便利です（Macの場合はShift + Command + Return、Windowsの場合はShift + Control + Enterなど、OSや設定により異なる場合があります）。何度か押すと、相対参照（$なし）→絶対参照（$A$1）→行固定（A$1）→列固定（$A1）→相対参照、と切り替わります。

特に、集計範囲や条件の参照先など、コピーしてもずれてほしくないセルがある場合は、絶対参照（または適切な混合参照）を必ず設定しましょう。

実務で「特に」よく使う関数6選+α

ここからは、動画で紹介されている関数の中から、特に重要で実務で頻繁に使用する関数をピックアップして解説します。

1. VLOOKUP関数: 別の表から条件に合うデータを検索して持ってくる

VLOOKUP関数は、指定した範囲（表）の一番左の列で検索値を探し、一致した行の指定した列にあるデータを返す関数です。縦（Vertical）にデータを検索することから「V」LOOKUPと呼ばれます。

機能: 別のシートや表にある関連情報を、あるキー（検索値）を元に現在のシートに引っ張ってきたいときに使います。顧客リストに担当者の居住地を追加したり、商品コードから単価を引っ張ってきたりする場面などで役立ちます。

式の書き方:

=VLOOKUP(検索する値, 検索の範囲, 列番号, 検索のタイプ)

引数の意味:
- 検索する値: 検索のキーとなる値またはセルを指定します。例：「池田さん」という名前や、その名前が入っているセル（例: B3）。
- 検索の範囲: 検索対象となる表の範囲を指定します。一番左の列に「検索する値」があるように範囲を指定する必要があります。例: A1:D10。
- 列番号: 「検索の範囲」の左から何列目のデータを返したいかを数字で指定します。検索範囲の一番左の列が1列目です。例: 居住地が4列目なら 4。
- 検索のタイプ: 検索方法を指定します。
  - TRUE または省略: 近似一致（数値を検索する場合に、検索値を超えない範囲での最大値を見つける）。範囲が昇順に並んでいる必要があります。
  - FALSE: 完全一致（「検索する値」と完全に一致するものを探す）。文字列を検索する場合は基本的にFALSEを指定します。
具体例: 担当者名から居住地を取得する場合
- 別のシートに「担当者名」「年齢」「性別」「居住地」のリストがある。
- 別のシートで担当者名（例: 池田さん）の横に居住地を表示したい。
- =VLOOKUP(B3, '別の情報'!A1:D10, 4, FALSE)
  - B3: 検索する担当者名「池田さん」が入っているセル。
  - '別の情報'!A1:D10: 担当者リストがあるシート「別の情報」のA1からD10までの範囲。一番左のA列に担当者名が入っている。
  - 4: 居住地が検索範囲の4列目にある。
  - FALSE: 担当者名と完全に一致するものを探す。
注意点:
- 「検索する値」は必ず「検索の範囲」の一番左の列にある必要があります。そうでない場合は、データの並び替えが必要になります。
- 複数の同じ検索値がある場合、VLOOKUP関数は最初に見つかった一致のデータのみを返します。合計などを出したい場合には向きません。
XLOOKUP関数: VLOOKUP関数の「検索値が一番左にないといけない」という欠点を解消した関数です。スプレッドシートではVLOOKUPの代替として推奨される場合があります。

2. SUMIFS関数: 複数の条件を満たすデータの合計を計算する

SUMIFS関数は、指定した範囲（合計対象範囲）の中で、複数の条件をすべて満たす行に対応する値を合計する関数です。

機能: 月ごとの特定の店舗の売上合計、特定の担当者の1月分の売上合計など、複数の条件でデータを絞り込んで集計したい場合に非常に便利です。
SUMIF関数: SUMIFS関数と同様に条件付きで合計を行いますが、こちらは単一の条件のみを指定できます。SUMIFSはSUMIFの上位互換のような関係です。

式の書き方:

=SUMIFS(合計対象範囲, 条件範囲1, 条件1, 条件範囲2, 条件2, ...)

引数の意味:
- 合計対象範囲: 合計したい値が入っている範囲を指定します。
- 条件範囲1: 1つ目の条件の検索対象となる範囲を指定します。
- 条件1: 条件範囲1に適用する条件を指定します。特定の文字列（例: "池田"）や、比較演算子（例: ">1000"）、あるいは条件が入力されているセル（例: G6）を指定できます。文字列を直接入力する場合はダブルクォーテーションで囲みます。
- 条件範囲2, 条件2, ...: 2つ目以降の条件を、範囲と条件のペアで必要なだけ追加します。
具体例: 1月でかつ担当者が池田さんの売上合計を計算する場合
- データに「月」「担当者」「売上」などの列がある。
- =SUMIFS(C1:C10, B1:B10, "池田", A1:A10, "1月")
  - C1:C10: 合計したい売上データが入っている範囲。
  - B1:B10: 担当者名が入っている範囲。
  - "池田": 担当者が「池田」であるという条件。
  - A1:A10: 月が入っている範囲。
  - "1月": 月が「1月」であるという条件。
- 条件をセル参照で指定することも可能です。例: =SUMIFS(C1:C10, B1:B10, G6, A1:A10, E7) （G6セルに「池田」、E7セルに「1月」が入力されている場合）。
注意点:
- 合計対象範囲と条件範囲は、行数が一致している必要があります。
- 関数をコピーして使う場合は、絶対参照を活用すると便利です。合計対象範囲や条件範囲は固定し、条件の参照先セルは横や下にコピーしたときにずれてほしい方向に合わせて混合参照を設定します。

3. COUNTIFS関数: 複数の条件を満たすデータの数を数える

COUNTIFS関数は、指定した範囲の中で、複数の条件をすべて満たす行の数を数える関数です。SUMIFS関数の合計ではなく、カウント版です。

機能: 特定の店舗で在職している人数、特定の職種の男性の人数など、複数の条件に合致するデータの件数を知りたいときに役立ちます。
COUNTIF関数: COUNTIFS関数と同様に条件付きでカウントを行いますが、こちらは単一の条件のみを指定できます。COUNTIFSはCOUNTIFの上位互換です。
COUNT関数: 指定した範囲に含まれる、空白ではないセルの数を数えます。数値以外のセルもカウントできます。

式の書き方:

=COUNTIFS(条件範囲1, 条件1, 条件範囲2, 条件2, ...)

引数の意味:
- 条件範囲1: 1つ目の条件の検索対象となる範囲を指定します。
- 条件1: 条件範囲1に適用する条件を指定します。
- 条件範囲2, 条件2, ...: 2つ目以降の条件を、範囲と条件のペアで必要なだけ追加します。
具体例: 市川店で在職している配達員の人数を計算する場合
- データに「在職/離職」「店舗」「職種」などの列がある。
- =COUNTIFS(A1:A10, "在職", B1:B10, "市川", C1:C10, "配達")
  - A1:A10: 在職/離職が入っている範囲。
  - "在職": 「在職」であるという条件。
  - B1:B10: 店舗が入っている範囲。
  - "市川": 「市川」であるという条件。
  - C1:C10: 職種が入っている範囲。
  - "配達": 「配達」であるという条件。
- 条件をセル参照で指定することも可能です。
注意点:
- すべての条件範囲は、行数が一致している必要があります。
- 関数をコピーして使う場合は、絶対参照や混合参照の活用が重要です。

4. FILTER関数: 条件に一致するデータをまとめて抽出する

FILTER関数は、指定した範囲のデータの中から、条件に一致する行をすべて抽出して新しいリストとして表示する関数です。

機能: 元データを変更せずに、特定の条件（例: 担当者が「池田」のものだけ、売上が2000以上のものだけ）を満たすデータだけを別の場所に一覧表示したい場合に便利です。VLOOKUPが1つのセルに1つのデータを返すのに対し、FILTERは条件に合うデータを丸ごと複数行・複数列抽出します。

式の書き方:

=FILTER(抽出したい範囲, 条件式1, 条件式2, ...)

引数の意味:
- 抽出したい範囲: 条件に一致した場合に抽出したいデータの範囲を指定します。例: A1:G1000。
- 条件式1: データを抽出するための条件を指定します。これはSUMIFSのように「範囲」と「条件」を分けるのではなく、範囲に対する真偽判定の式として記述します。例: C1:C1000="池田"（C列が「池田」である）や G1:G1000>=2000（G列が2000以上である）。条件をセル参照で指定することも可能です。
- 条件式2, ...: 複数の条件を指定したい場合は、追加の条件式をカンマで区切って続けます。すべての条件を満たす行が抽出されます。
具体例: 担当者が池田さんのデータのみを抽出する場合
- データに「担当者」などの列を含む表がある。
- =FILTER(データ!A1:G1000, データ!C1:C1000="池田")
  - データ!A1:G1000: 「データ」シートのA1からG1000までの範囲からデータを抽出したい。
  - データ!C1:C1000="池田": 「データ」シートのC列（担当者）が「池田」であるという条件式。
注意点:
- FILTER関数の結果が表示される範囲は、元のデータの行数や列数に応じて変動します。そのため、FILTER関数を入力したセルの右側や下側に、結果が表示されるのに十分な空白セルを確保しておく必要があります。結果表示範囲に他のデータが入力されていると、エラー（#REF!など）が表示されます。
- 条件式は「範囲比較演算子条件値」のように記述します。範囲は「抽出したい範囲」と同じ行数である必要があります。

5. SUMPRODUCT関数: 指定した範囲の対応する要素の積を合計する

SUMPRODUCT関数は、指定した複数の配列（範囲）の対応する要素同士を掛け合わせ、その積の合計を計算する関数です。

機能: 商品の「個数」と「単価」のリストがあるときに、各商品の個数×単価を計算し、さらにそれらを全て合計して総売上を求めたい場合などに、一時的な計算列を作らずに一度に計算できます。
式の書き方:
```
=SUMPRODUCT(範囲1, 範囲2, ...)
```
または（条件付きの場合）
```
=SUMPRODUCT((条件範囲1=条件1)*(条件範囲2=条件2)*..., 計算対象範囲)
```
※ 掛け算の記号 * は、配列同士の論理積（AND条件）として機能します。
引数の意味:
- 範囲1, 範囲2, ...: 掛け合わせたい数値が入っている範囲を指定します。指定した範囲の行数および列数は同じである必要があります。複数の範囲を指定した場合、対応する位置のセル同士が掛け合わされ、その結果がすべて合計されます。
- 条件範囲1=条件1, 条件範囲2=条件2, ...: 条件付きで計算したい場合に指定します。各条件式はTRUE（1）またはFALSE（0）を返す配列となります。これらの配列を掛け合わせることで、すべての条件を満たす行（結果が1となる行）のみが計算対象となります。
- 計算対象範囲: 条件を満たした行に対して合計したい数値が入っている範囲を指定します。
具体例: 個数と単価のリストから総売上を計算する場合
- データに「個数」と「単価」の列がある。
- =SUMPRODUCT(B2:B6, C2:C6)
  - B2:B6: 個数が入っている範囲。
  - C2:C6: 単価が入っている範囲。
  - この式は (B2*C2) + (B3*C3) + ... + (B6*C6) を計算し、合計を返します。
具体例2: 担当者が池田さんの商品の個数×単価の合計を計算する場合
- データに「担当者」「個数」「単価」の列がある。
- =SUMPRODUCT((A2:A6="池田")*(B2:B6), C2:C6)
  - (A2:A6="池田"): A列（担当者）が「池田」であるかどうかの条件式。結果は {TRUE, FALSE, ...} の配列になります。
  - (B2:B6): 個数が入っている範囲。
  - C2:C6: 単価が入っている範囲。
  - この式は、A列が「池田」である行についてのみ、個数*単価を計算し合計します。
注意点:
- 引数に指定する範囲のサイズ（行数、列数）はすべて一致している必要があります。

6. INDIRECT関数: 文字列でセル参照を指定する

INDIRECT関数は、「参照を指定する文字列」を基に、その文字列が示すセルまたは範囲を参照する関数です。

機能: 通常のセル参照では固定されてしまうものを、文字列として扱うことで動的に参照先を変更したり、他の関数と組み合わせて参照を柔軟に制御したりするために使用します。特に、シートの構造が頻繁に変わる場合や、参照先を別のセルで管理したい場合に役立ちます。

式の書き方:

=INDIRECT(参照文字列, [参照形式])

引数の意味:
- 参照文字列: セル参照を示す文字列を指定します。例: "B2"や"データ!A1:C10"。セル参照が入力されているセルを指定することもできます。例: A1セルに"B2"と入力しておき、INDIRECT(A1)とする。
- [参照形式]: 参照文字列の形式を指定します。TRUE（または省略）はA1参照形式、FALSEはR1C1参照形式を指定します。通常は省略してA1参照形式で使います。
具体例: セルA1に入力された文字列「B2」が示すセルB2を参照する場合
- A1セルに "B2" と入力する。
- 別のセルに =INDIRECT(A1) と入力すると、A1セルに入力された文字列「B2」が参照と解釈され、B2セルの値が返されます。
他の関数との組み合わせ例:
- SUM関数と組み合わせて、合計したい範囲を別のセルに入力した文字列で指定する。例: B1セルに"C1:C5"と入力しておき、=SUM(INDIRECT(B1))とすると、C1からC5までの合計が計算される。
- 特に、横に並んだ複数の範囲を縦に合計したい場合などに、コピー時に参照が適切にずれるようにINDIRECT関数とROW関数やCOLUMN関数を組み合わせて使用する。
注意点:
- INDIRECT関数は、セル参照を文字列として扱うため、他の関数と組み合わせて使うことで真価を発揮することが多いです。
- 別シートの範囲を参照することも可能です。例: "シート2!A1"。

+α SUBTOTAL関数: 集計方法を指定し、二重集計を防ぐ

SUBTOTAL関数は、リストまたはデータベースの小計を返します。特徴として、集計方法を数値で指定できることと、他のSUBTOTAL関数の結果を自動的に無視して二重集計を防ぐ機能があります。

機能: 表の中に小計行があり、その小計を含めずに全体の合計などを計算したい場合に便利です。手動で範囲を選択し直す手間を省き、ミスの可能性を減らします。

式の書き方:

=SUBTOTAL(集計方法, 範囲1, [範囲2], ...)

引数の意味:
- 集計方法: 使用する集計関数を数値で指定します。代表的なものとして、合計（SUM）は 9、平均（AVERAGE）は 1、データの個数（COUNT）は 2、最大値（MAX）は 4、最小値（MIN）は 5 などがあります。
- 範囲1, [範囲2], ...: 集計したいセル範囲を指定します。
具体例: 小計を含む売上リストの総計を計算する場合
- 売上データの中に、月ごとの小計行がSUBTOTAL関数で計算されている。
- 総計を出したいセルに =SUBTOTAL(9, C2:C20) のように入力する。
  - 9: 集計方法として合計（SUM）を指定。
  - C2:C20: 総計を計算したい範囲（小計の行を含む）。
  - この式は、指定された範囲に含まれる他のSUBTOTAL関数の結果を自動的に無視して、純粋なデータ行の値のみを合計します。通常のSUM関数では小計も合算してしまい二重集計になる可能性がありますが、SUBTOTAL関数ならこれを防げます。
注意点:
- 集計方法に指定する数値によって、SUM, AVERAGE, COUNTなど様々な集計が可能です。

ARRAYFORMULA: 複数のセルへの関数入力を省略し、処理を効率化

ARRAYFORMULAは、1つのセルに数式を入力するだけで、指定した範囲（配列）全体にその数式を適用し、複数のセルに結果を表示させることができる関数です。

機能: 通常なら各セルに関数をコピー&ペーストする必要がある場面で、ARRAYFORMULAを使えば入力は1箇所で済みます。これにより、入力の手間が省けるだけでなく、シートの処理が軽量化されるメリットもあります。他の関数と組み合わせて使用することで効果を発揮します。
使用方法: 数式を入力した後、通常のEnterではなく、Shift + Command + Return (Mac) または Shift + Control + Enter (Windows) を押すことで、数式が自動的にARRAYFORMULA関数で囲まれます。このショートカットキーは、どのような関数を入力する際にも使用できます。
式の書き方: 数式バーには =ARRAYFORMULA(適用したい数式) のように表示されますが、手入力するよりも上記のショートカットキーを使うのが一般的です。
適用したい数式: 範囲（配列）を引数として使用した数式を記述します。例えば、掛け算なら =B1:B10 * C1:C10 のように、通常のセル参照ではなく範囲参照を使用します。
具体例1: 個数と単価の列から合計金額を計算し、各行に結果を表示する場合
- 個数と単価の列がある。
- 結果を表示したい列の一番上のセルに =B2:B6 * C2:C6 と入力し、Shift + Command + Return または Shift + Control + Enter を押す。
  - B2:B6 * C2:C6: B2からB6の範囲とC2からC6の範囲の、対応する行の値を掛け合わせるという式。
  - これにより、B2C2, B3C3, ..., B6*C6 の計算結果が、入力したセルとその下のセルに自動的に表示されます。
具体例2: 日付のリストから曜日を自動的に表示する場合 (TEXT関数との組み合わせ)
- 日付の列がある。
- 曜日を表示したい列の一番上のセルに =TEXT(A2:A10, "aaaa") と入力し、Shift + Command + Return または Shift + Control + Enter を押す。
  - A2:A10: 曜日を知りたい日付が入っている範囲。
  - "aaaa": 曜日を漢字で表示するための書式設定。
  - これにより、A2からA10までの日付に対応する曜日が、入力したセルとその下のセルに自動的に表示されます。
注意点:
- ARRAYFORMULAで生成された結果の範囲に手動で値を入力すると、エラーが表示されたり、ARRAYFORMULAの結果が正しく表示されなくなったりします。
- 適用する数式によっては、範囲に空白セルが含まれる場合に意図しない結果（例: 0が表示される）となることがあります。IF関数やIFS関数と組み合わせて、空白の場合の処理を指定することが推奨されます。

まとめ

この記事では、スプレッドシートでの作業効率を上げるために必須の関数として、VLOOKUP, SUMIFS, COUNTIFS, FILTER, SUMPRODUCT, INDIRECT、そして効率化に役立つARRAYFORMULA、二重集計を防ぐSUBTOTALをご紹介しました。

VLOOKUP / XLOOKUP: 別の表から特定の条件でデータを引っ張ってくる。
SUMIFS / SUMIF: 複数の条件を満たすデータの合計を出す。
COUNTIFS / COUNTIF: 複数の条件を満たすデータの個数を数える。
FILTER: 条件に一致するデータをまとめて抽出する。
SUMPRODUCT: 複数の範囲の積の合計を計算する。
INDIRECT: 文字列でセル参照を動的に指定する。
SUBTOTAL: 集計方法を指定し、小計を含むリストの合計などを正確に計算する。
ARRAYFORMULA: 1つの式で複数のセルに計算結果を展開する。

これらの関数を使いこなすことで、手作業での集計やデータ入力の時間が減り、より正確で効率的なデータ管理が可能になります。最初は難しく感じるかもしれませんが、実際にシートを使って練習してみることが習得への一番の近道です。ぜひ、今回ご紹介した関数を日々の業務で活用してみてください。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up