Google スプレッドシートで最も役立つ 8 つの数式

ヒント: 予算に取り組んでいる場合、Google Sheetsのこれらの予算管理テンプレートを適用することができます。

Google Sheetsの数式を使う

Google Sheetsで数式を使うのが初めてなら、Excelと似たような使い方ができます。単にセルまたは範囲を選択し、数式を入力します。すべての数式は、同じように等号（=）で始まります。

1. COUNTIF

COUNTIFは、一般的に使用されているCOUNT式を基に作成されています。ある範囲内のすべてのセルを単純に数えるのは便利ですが、必ずしもすべてを数えたいと思うわけではありません。その代わりに、ある数値より上か下か、あるいは特定の名前だけか、といった基準を選択します。

=COUNTIF(cellrange, criteria)

このGoogle Sheetsの数式を使うには、次のようにします。

1. 空のセルを選び、数式を開始します。

=COUNTIF

2. 2. 任意のセル範囲を入力します。
3. 3. 条件を引用符で囲んで入力します。

 この例では、1月の合計が20,000ドルを超えるセルがいくつあるかを確認したいと思います。私の計算式は =COUNTIF(F2:F352,">$20,000") です。数式は、カウントするものと同じ列や行に置く必要はありません。

ヒント: Google Sheetsのセルの色を変更して、フォームをもう少し面白くする方法を紹介します。

2. VLOOKUP

Google Sheetsの数式の中で最も便利で過小評価されているものの1つが、間違いなくVLOOKUPです。垂直方向のルックアップでは、1つの情報に基づいて、一連のデータから情報を引き出すことができます。

=VLOOKUP(lookup_value, table_array, column, range_lookup)

簡単に言うと、検索する値、データのセル範囲、表示するデータの列番号、そして完全一致か近似一致かを指定します。VLOOKUPガイドでより詳しく説明しています。

1. 1. 結果を表示したいセルを選択し、数式を開始します。

=VLOOKUP(

2. 2. 検索の元となるセルを入力します。私の場合、社員IDを使っています。

 3. 検索したい範囲を入力します。VLOOKUPの唯一の欠点は、検索IDを含む列が範囲の最初の列であり、引き出したいデータがその右側になければならないことです。

=vlookup(e2,$a2:$c14,3,false).

異なるシート間でVLOOKUPを使用する場合は、テーブル配列の前にシート名を追加して、 =VLOOKUP(E2,FirstSheet!$A2:$C14,3,FALSE) のようにします。

3. IMPORTRANGE

あなたが頻繁に1つのGoogle Sheetsのスプレッドシートから別のデータをインポートする必要がある場合は、IMPORTRANGEはすぐにあなたの親友になることができます。確かに、あなただけの必要なものをコピーして貼り付けることができますが、大規模なシートのために、これはあなたが唯一のシートの特定の部分をインポートしたい場合は特に、しばらく時間がかかることがあります。この方法は、まったく別のGoogle Sheetsファイル間で行うことができます。

=IMPORTRANGE(spreadsheet_URL, range_string)

1. データをインポートするスプレッドシートを開き、データを配置する最初のセルで数式を開始します。

=IMPORTRANGE(

2. 2. インポート元のスプレッドシートの URL をコピーして貼り付けます。
3. 3. インポートする範囲を選択します。これは、スプレッドシート上のどのシートからでも可能です。デフォルトでは、Google Sheetsは最初のシートを使用します。データが最初のシートにある場合は、「A2:F100」のように範囲を入力するだけです。別のシートにある場合は、AnotherSheet!A2:F100のように入力します。

財務のシートから2023年の予測データだけを取り出すには、次のような数式を使いました。

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1uJEkIK6Qn3AibcQc27BXDX5zo_ECJCdccLIiuD6guNw/", "A299:Q352")

もし、#REFエラーが出たら、シートを接続するように促されます。この作業は一度だけで、「アクセスを許可する」を選択すると、Googleが自動的に行ってくれます。

4. IFERROR

VALUEエラーでいっぱいのスプレッドシートは、誰も好きではありません。これはデータの邪魔をするだけです。これは、数式に問題があるわけではなく、表示すべき結果がないことを意味します。たとえば、列間の計算を行うときに、一部のセルが空白の場合、エラーが発生することがあります。IFERRORを使用すると、代わりに結果として使用する値を選択することができます。

=IFERROR(original_formula, value_if_error)

この例では、売上合計に基づいてアイテムごとの平均価格を計算しています。しかし、定期的な支払いは、アイテムの合計がゼロになる。

1. 元の数式を使用する最初のセルを選択します。
2. 2. シートのデータのすぐ上にある数式/関数ボックスに、「IFERROR」(=記号の直後)を追加します。

 3. 最初のパラメータは、既存の数式です。より複雑な数式では、適切に囲むために余分な括弧を追加する必要がある場合があります。 4. 4.2番目のパラメータとして希望する値を追加し、数式を括弧で閉じます。私の場合、セルを空白にしたいので、2番目のパラメータとして「」を追加しています。

=iferror(a2/b2," ")


5. 配列式

最も便利なGoogle Sheetsの数式の多くと同様に、ARRAYFORMULAはより大きなデータセットで最もうまく機能します。Google Sheetsは多数の数式を処理しなければならないとき、動作が遅くなる傾向があります。ARRAYFORMULAは、セルの範囲に対して単一の数式を使用することで、この問題を解決するのに役立ちます。

1. 1. 数式を使いたい最初のセルを選択します。私の場合、これはD2です。
2. 2. 数式を =ARRAYFORMULA で始める。
3. 3. 通常の数式を入力しますが、個々のセルではなく、セル範囲を使用します。例えば、「B2」の代わりに、「B2:B14」とします。

4. 数式を括弧で囲むと、列全体が一つの数式で埋め尽くされるはずです。

私の最終的な数式は =ARRAYFORMULA(B2:B14+C2:C14) でした。当然ながら、これらはもっと複雑になりますが、これが基本的な構文です。パーセンテージを求めたり、給与からボーナスを引くなど、演算子を簡単に変更することができます。最初のセルでそれを行うだけで、範囲内の他のすべてのセルを変更することができるのです。

6. フィルター

データ」→「フィルタの作成」で簡単にデータのフィルタリングやソートができますが、見たい結果だけを得るには、FILTER式を使う方が簡単です。パラメータはとてもシンプルです。

=FILTER(range, condition1, otherconditions)

otherconditions” の部分はオプションです。これらは、基本的に他のセルと真偽を比較し、結果をさらにフィルタリングするためのものです。

1. 1. 空白のセルで、数式を開始します。理想的には、フィルタリングするデータと同じ列ヘッダーを作成し、最初の列ヘッダーの下の最初のセルで数式を開始します。たとえば、給与が120,000ドル以上の社員IDをフィルタリングする場合、以下のようになります。

 2. 範囲と最初の条件を入力します。ここで数式を閉じてもいいし、カンマで区切ってさらに条件を入力してもいい。私の場合は、F2:F14の範囲で12万円以上の値のみを選択する条件です。

7. JOIN

2つ以上のセルを1つのセルに結合する必要が生じたことはないだろうか。例えば、スプレッドシートに全員の姓と名が別々の列で表示されてしまったとします。このような場合、JOIN式を使用すると、これらのセルを結合することができます。これは、データを一つのセルに結合する必要がある人にとって、Google Sheetsの数式の中でも意外と便利なものの一つです。

=JOIN(区切り文字, 値または配列1, 値または配列2, etc.)

値や配列は2つ以上あってもかまいませんが、少なくとも2つは必要です。単一のセルだけを結合することも、配列全体や範囲を単一のセルに結合することも自由です。

1. 1. 数式を作成するために、空のセルを選択します。
2. 2. 「=JOIN(`)」と入力し、区切り文字を入力します。区切り文字には、カンマ、空白、ハイフン、その他何でもかまいません。ただし、区切り文字を「,」や「-」のように引用符で囲んでください。
3. 3. 1番目と2番目の値/配列をカンマで区切って入力します。私の最終的な姓と名の結合式は、=JOIN(" ",B2,C2) です。

 です。 また、大きなシートの場合は、ARRAYFORMULAを使ってこれを行うこともできます。上記と同じ例で言うと

=arrayformula(b2:b14 & " " & c2:c14)

この場合、最初の列の範囲を追加し、アンパサンド (&) シンボルを使ってそれらを結合します。真ん中のセクションはデリミタ（区切り記号）用です。

ヒント: Windowsでアンパサンドや他の特殊文字を入力する方法について学びましょう。

8. スプリット

逆の問題で、セル内の項目を分割する必要がありますか？SPLITという数式を使えばいいのです。これはJOINの逆です。例えば、データをアルファベット順に並べ替えやすくするために、姓と名を分けたい場合などです。

=SPLIT(text, delimiter, [split_by_each], [remove_empty_text]))

もっと簡単に言うと、“text “は分割したいセル、“delimiter “は分割する場所を指定するための文字、最後の二つはオプションです。split_by_each は、一致する文字ごとに分割するかどうかを指定します。たとえば "character" という単語を、それぞれの "a" に基づいて分割するか、それとも最初の文字だけを分割するかを指定します。Remove_empty_text は、結果から空のテキストを削除する。デフォルトでは TRUE に設定されている。

1. 1. 最初の空セルで、数式を「=SPLIT(`」で開始します。
2. 2. 分割したいセルを入力します。
3. 3. 分割したい区切り文字を入力します。私の場合、空白なので「"」を使っていますが、文字や記号など何でも構いません。
4. 4.数式を閉じると、同じ行の複数のセルが塗りつぶされます。

 ヒント: Apple NumbersのファイルをWindowsに対応させる作業について学びましょう。

Frequently Asked Questions (よくある質問)

範囲をハイライトするだけで、手動で入力する必要はありませんか？

もちろんです。数式を開始して、範囲が必要なパラメータに到達したら、マウスまたは指を使って範囲をハイライトします。Googleが自動的に列と行の情報を入力し、数式の次の部分に進むことができます。

数式についてもっと詳しく知るにはどうしたらいいですか？

例題を調べる以外にも、Google Sheetsは数式に関する素晴らしい解説を提供しています。挿入」→「関数」と進み、関数／数式を選択します。数式がセルに表示されたら、数式名の横にある+記号をクリックし、ドロップダウン矢印をクリックすると、パラメータとそのパラメータの意味が表示されます。

Google SheetsとMicrosoft Excelの数式はすべて互換性があるのですか？

いいえ。例えば、ARRAYFORMULAはGoogle Sheets専用です。両方で使える数式を使いたい場合は、Google Sheetsの関数リストとExcelの関数リストを確認し、同じ数式を共有していることを確認しましょう。